Hey folks,
Im working on my final year project with a partner and we have hit a serious stumbling block with ColdFusion. we're fairly new to it, but taking to it nicely, unfortunately, i have created a page that does not seem to be doing what I think I have programmed it to - im sure you've heard that before.
Basically the page is displaying an 'IN OUT' calendar. I can get the information posted to the database - so i haven't included that seperate file - its just when its reading it back that it doesn't seem to want to work.
The code for the entire page is below, sorry its a bit lengthy, but i didnt want to leave anything out:
The query 'get_schedule' (which i have marked with an asterix at start and finish of the query, its near the top) is supposed to be verifying whether or not there is a record for this month, this year in the database, if there is, then display it, if not, then allow the user to add one. Unfortunately, it always says there isn't one, and so lets the user add one, this means that the user could potentially add multiple calendars for the same month.
The Date/Time format in the table is in the format dd/mm/yyyy all integer values.
Any help would be greatly appreciated, even if you can give me an easier way to do it, that would be great, as i said, im quite new to ColdFusion.
Im working on my final year project with a partner and we have hit a serious stumbling block with ColdFusion. we're fairly new to it, but taking to it nicely, unfortunately, i have created a page that does not seem to be doing what I think I have programmed it to - im sure you've heard that before.
Basically the page is displaying an 'IN OUT' calendar. I can get the information posted to the database - so i haven't included that seperate file - its just when its reading it back that it doesn't seem to want to work.
The code for the entire page is below, sorry its a bit lengthy, but i didnt want to leave anything out:
Code:
<CFOUTPUT>
<!--- Set the month and year parameters to equal the current values if they do not exist.
Otherwise pass via url --->
<CFPARAM NAME = "month" DEFAULT = "#DatePart('m', Now())#">
<CFPARAM NAME = "year" DEFAULT = "#DatePart('yyyy', Now())#">
<CFPARAM NAME = "Operator_USERS_key" DEFAULT = "#request.session.user#">
<CFPARAM NAME = "Ones_dept" DEFAULT = "">
<cfif isdefined("Ones_Users_key")>
<CFPARAM NAME = "USERS_key" DEFAULT = "#Ones_Users_key#">
<cfelse>
<CFPARAM NAME = "USERS_key" DEFAULT = "#request.session.user#">
</cfif>
<!--- get the dept of the logged in user --->
<cfquery name="getOperatorDept" datasource="intranet" maxrows="1">
Select DEPARTMENT_KEY
from USERS
WHERE USERS_KEY = #request.session.user#
</cfquery>
<cfset Operator_dept = #getOperatorDept.DEPARTMENT_KEY#>
<!--- If a user is look at someone else's schedule - get their dept --->
<cfif USERS_key neq request.session.user>
<cfquery name="getOnesDept" datasource="intranet" maxrows="1">
Select DEPARTMENT_KEY
from USERS
WHERE USERS_KEY = #USERS_key#
</cfquery>
<cfset Ones_dept = #getOnesDept.DEPARTMENT_KEY#>
</cfif>
<!--- Set the requested (or current) month/year date and determine the number of days in the month. --->
<!--- if next/previous links are not used - month is supplied by form as string--->
<cfif isdefined("btn_editSchedule") or isdefined("btn_addSchedule") or isdefined("btn_editOnesSchedule") or isdefined("btn_addOnesSchedule")>
<CFSET month = monthInt>
</cfif>
<CFSET ThisMonthYear = CreateDate(year, month, '1')>
<CFSET Days = DaysInMonth(ThisMonthYear)>
<CFSET ThisMonth = (MonthAsString(Month(ThisMonthYear)))>
<CFSET ThisMonthInt = DatePart('m', ThisMonthYear)>
<CFSET ThisYear = DatePart('yyyy', ThisMonthYear)>
<CFSET ThisDate = "01"&"/"&#ThisMonthInt#&"/"&#ThisYear#>
*
<cfquery name="get_schedule" datasource="intranet">
select USERS_key
from IN_OUT_STATUS
where USERS_key = #request.session.user#
and IN_OUT_STATUS_date = #ThisDate#
</cfquery>
*
<!--- must pass month, year in form /use session for user/ --->
<cfif get_schedule.recordcount eq 1 or isdefined("btn_addSchedule") or isdefined("btn_editSchedule") or isdefined("btn_addOnesSchedule")>
<cfset show_calendar = "yes">
<!--- show radios if adding or editing a schedule --->
<cfelse>
<!--- may still show calendar if add schedule selected --->
<cfset show_calendar = "no">
</cfif>
<cfif isdefined("btn_addSchedule") or isdefined("btn_editSchedule")>
<cfif (get_schedule.recordcount eq 1) and (get_schedule.USERS_key eq request.session.user )> <!--- or request.session.privilege eq 1> --->
<cfset action = "Edit">
<cfelse>
<cfset action = "Add">
</cfif>
<cfelse>
<cfset action = "View">
</cfif>
<cfif isdefined("btn_editOnesSchedule")>
<cfset action = "Edit">
</cfif>
<cfif isdefined("btn_AddOnesSchedule")>
<cfset action = "Add">
</cfif>
<table width="98%" height="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="7"><img src="../images/frame_topleft.gif" width="7" height="31"></td>
<td width="98%" height="1%" background="../images/frame_top.gif"> <table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="30"><img src="../images/icon_Contacts.gif" width="33" height="31"></td>
<td class="FrameTitle"> #action# <cfif USERS_key eq request.session.user>Your<cfelse><cfinclude template="../common/inc_user_detail.cfm">'s</cfif> Schedule for #MonthAsString(month)# #year#</td>
</tr>
</table></td>
<td width="7"><img src="../images/frame_topright.gif" width="7" height="31"></td>
</tr>
<tr>
<td background="../images/frame_left.gif"> </td>
<td height="98%" valign="top" bgcolor="##FFFFFF">
<!--- edit mode when link is clicked, default is show schedule, colors --->
<CFSET TodayDay = day(now())>
<CFSET TodayMonth = left(MonthAsString(Month(now())),3)>
<CFSET TodayYear = DatePart('yyyy', now())>
<!--- Set the values for the previous and next months for the back/next links. --->
<CFSET LastMonthYear = DateAdd('m', -1, ThisMonthYear)>
<CFSET LastMonth = DatePart('m', LastMonthYear)>
<CFSET LastYear = DatePart('yyyy', LastMonthYear)>
<CFSET NextMonthYear = DateAdd('m', 1, ThisMonthYear)>
<CFSET NextMonth = DatePart('m', NextMonthYear)>
<CFSET NextYear = DatePart('yyyy', NextMonthYear)>
<!--- show the forward and back month links even if schedule not set for month - allow to skip a month --->
<table width="100%" height="100%" border="0" cellpadding="2" cellspacing="1">
<tr>
<td height="1%">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><div align="center" class="LargeText"><A HREF = "#cgi.script_name#?month=#LastMonth#&year=#LastYear#&USERS_key=#USERS_key#<CFIF ISDEFINED("keyword") AND KEYWORD NEQ "">&keyword=#keyword#</CFIF><CFIF ISDEFINED("USERS_fname") AND USERS_fname NEQ "">&USERS_fname=#USERS_fname#</CFIF><CFIF ISDEFINED("USERS_lname") AND USERS_lname NEQ "">&USERS_lname=#USERS_lname#</CFIF><CFIF ISDEFINED("OFFICE_key") AND OFFICE_KEY NEQ 0>&OFFICE_key=#OFFICE_key#</CFIF><CFIF ISDEFINED("DEPARTMENT_key") AND DEPARTMENT_key NEQ 0>&DEPARTMENT_key=#DEPARTMENT_key#</CFIF><CFIF ISDEFINED("USERS_job_title") AND USERS_job_title NEQ "">&USERS_job_title=#USERS_job_title#</CFIF><CFIF ISDEFINED("GROUPS_key") AND GROUPS_key NEQ 0>&GROUPS_key=#GROUPS_key#</CFIF><CFIF ISDEFINED("USERSLEVEL_key") AND USERSLEVEL_key NEQ 0>&USERSLEVEL_key=#USERSLEVEL_key#</CFIF>">«</a> </div></td>
<td width="34%">
<div align="center" class="smallText">#MonthAsString(month)# #year#</div>
</td>
<td><div align="center" class="LargeText"><a href="#cgi.script_name#?month=#NextMonth#&year=#NextYear#&USERS_key=#USERS_key#<CFIF ISDEFINED("keyword") AND KEYWORD NEQ "">&keyword=#keyword#</CFIF><CFIF ISDEFINED("USERS_fname") AND USERS_fname NEQ "">&USERS_fname=#USERS_fname#</CFIF><CFIF ISDEFINED("USERS_lname") AND USERS_lname NEQ "">&USERS_lname=#USERS_lname#</CFIF><CFIF ISDEFINED("OFFICE_key") AND OFFICE_KEY NEQ 0>&OFFICE_key=#OFFICE_key#</CFIF><CFIF ISDEFINED("DEPARTMENT_key") AND DEPARTMENT_key NEQ 0>&DEPARTMENT_key=#DEPARTMENT_key#</CFIF><CFIF ISDEFINED("USERS_job_title") AND USERS_job_title NEQ "">&USERS_job_title=#USERS_job_title#</CFIF><CFIF ISDEFINED("GROUPS_key") AND GROUPS_key NEQ 0>&GROUPS_key=#GROUPS_key#</CFIF><CFIF ISDEFINED("USERSLEVEL_key") AND USERSLEVEL_key NEQ 0>&USERSLEVEL_key=#USERSLEVEL_key#</CFIF>">»</a></div></td>
</tr>
</table>
</td>
</tr>
<tr>
<td height="98%" valign="top">
<cfif USERS_key eq request.session.user or request.session.privilege eq 1 or (request.session.privilege eq '2' and Operator_dept eq Ones_dept)> <!--- or (request.session.privilege eq 1 eq '2' and Operator_dept eq Ones_dept) --->
<form action="<cfif (action eq "Edit") or (action eq "Add")>act_whos_in_out_month.cfm<cfelse>index.cfm</cfif>" method="post" name="form">
<input type="hidden" name="days" value="#Days#">
<input type="hidden" name="month" value="#ThisMonth#">
<input type="hidden" name="monthInt" value="#ThisMonthInt#">
<input type="hidden" name="year" value="#ThisYear#">
<input type="hidden" name="action" value="#action#">
<!---If admin is changing another users schedule that users ID is passed in --->
<cfif isdefined("Ones_Users_key")>
<input type="hidden" name="Ones_Users_key" value="#Ones_Users_key#">
</cfif>
</cfif>
<cfif show_calendar eq "yes">
<table width="80%" height="100%" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<CFLOOP FROM="1" TO="7" INDEX="LoopDay">
<TD width="14%" class="smallText"><div align="center">#Left(DayOfWeekAsString(LoopDay), 3)#</div></td>
</CFLOOP>
</tr>
<!--- Set the ThisDay variable to 0. This value will remain 0 until the day of the week on which the --->
<!--- first day of the month falls on is reached. --->
<CFSET ThisDay = 0>
<!--- Loop through until the number of days in the month is reached. --->
<CFLOOP CONDITION = "ThisDay LTE Days">
<TR>
<!--- Loop through each day of the week. --->
<CFLOOP FROM = "1" TO = "7" INDEX = "LoopDay">
<!--- If ThisDay is still 0, check to see if the current day of the week in the loop --->
<!--- matches the day of the week for the first day of the month. --->
<!--- If the values match, set ThisDay to 1. --->
<!--- Otherwise, the value will remain 0 until the correct day of the week is found. --->
<CFIF ThisDay IS 0>
<CFIF DayOfWeek(ThisMonthYear) IS LoopDay>
<CFSET ThisDay = 1>
</CFIF>
</CFIF>
<!--- If the ThisDay value is still 0, or it is greater than the number of days in the month, --->
<!--- display nothing in the column. --->
<!--- Otherwise, display the day of the month and increment the value. --->
<CFIF (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
<cfset CurrentDate = createODBCdatetime(createdate(#Year#,#Month#,#ThisDay#))>
<cfset CurrentDateStart = createODBCdatetime(createdatetime(#Year#,#Month#,#ThisDay#,00,00,00))>
<cfset CurrentDateEnd = createODBCdatetime(createdatetime(#Year#,#Month#,#ThisDay#,23,59,59))>
<cfset C_Date = #ThisDay# & "/" & #Month# & "/" & #Year#>
<cfquery name="get_status" datasource="intranet">
select USERS_key, IN_OUT_STATUS_am, IN_OUT_STATUS_pm from IN_OUT_STATUS
where USERS_key = #USERS_key#
and IN_OUT_STATUS_date = #C_Date#
</cfquery>
<TD align="center" valign="middle" class="<cfif (TodayDay eq ThisDay) and (TodayMonth eq ThisMonth) and (TodayYear eq ThisYear)>CalendarTodayNoEventText<cfelse>CalendarNoEventText</cfif>" valign="top">#ThisDay#
<table align="left" border="0" cellspacing="1" cellpadding="1">
<cfif ((action eq "Add") or (action eq "Edit"))>
<tr>
<td> </td>
<td><center>In</center></td>
<td><center>Out</center></td>
</tr>
<tr>
<td> AM</td>
<td><input type="radio" name="AM#thisday#" value="1" <cfif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_am eq 1) or (get_status.recordcount eq 0 and (LoopDay neq 1 or LoopDay neq 7))>checked="checked"</cfif>></td>
<td><input type="radio" name="AM#thisday#" value="0" <cfif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_am eq 0) or (get_status.recordcount eq 0 and (LoopDay eq 1 or LoopDay eq 7))>checked="checked"</cfif>></td>
<!--- <td><input type="radio" name="AM#thisday#" value="0" <cfif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_am eq 0)>checked="checked"</cfif>></td> --->
</tr>
<tr>
<td> PM</td>
<td><input type="radio" name="PM#thisday#" value="1" <cfif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_pm eq 1) or (get_status.recordcount eq 0 and (LoopDay neq 1 or LoopDay neq 7))>checked="checked"</cfif>></td>
<td><input type="radio" name="PM#thisday#" value="0" <cfif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_pm eq 0) or (get_status.recordcount eq 0 and (LoopDay eq 1 or LoopDay eq 7))>checked="checked"</cfif>></td>
</tr>
<cfelse>
<tr>
<td> AM</td>
<td <cfif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_am eq 1)>bgcolor = "green"><div align="center"><font color="white">IN</font></div><cfelseif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_am eq 0)>bgcolor = "##A70000"><div align="center"><font color="white">OUT</font></div></cfif></td>
</tr>
<tr>
<td> PM</td>
<td width="25" <cfif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_pm eq 1)>bgcolor = "green"><div align="center"><font color="white">IN</font></div><cfelseif (get_status.recordcount eq 1 and get_status.IN_OUT_STATUS_pm eq 0)>bgcolor = "##A70000"><div align="center"><font color="white">OUT</font></div></cfif></td>
</tr>
</cfif>
</table>
</td>
<CFSET ThisDay = ThisDay + 1>
<CFELSE>
<TD align="center" valign="middle" class="CalendarInactive"> </TD>
</CFIF>
</CFLOOP>
</TR>
</CFLOOP>
<tr><td colspan="7"><br>
<div align="center">
<cfif USERS_key neq request.session.user and request.session.privilege eq 1 or (request.session.privilege eq '2' and Operator_dept eq Ones_dept)>
<input type="Hidden" name="Ones_Users_key" value="#USERS_key#">
<cfif isdefined("Ones_Users_key")>
<input type="submit" name="btn_editOnesSchedule" value="Submit #getuser.USERS_fname#'s Schedule" class="formButton">
<cfelse>
<input type="submit" name="btn_editOnesSchedule" value="Edit #getuser.USERS_fname#'s Schedule" class="formButton">
</cfif>
</cfif>
<cfif USERS_key eq request.session.user>
<cfif ((action eq "Add") or (action eq "Edit"))>
<input type="submit" name="btnSubmit" value="Submit" class="formButton">
<input type="reset" name="btnReset" value="Reset" class="formButton">
<cfelse>
<input type="submit" name="btn_editSchedule" value="Edit Schedule" class="formButton">
</cfif>
</cfif>
</div>
</td></tr>
</table>
<cfelse>
<cfif USERS_key eq request.session.user>Your<cfelse><cfinclude template="../common/inc_user_detail_link.cfm">'s</cfif> Schedule
for #MonthAsString(month)# #year# has not been set.
<cfif USERS_key eq request.session.user>
<br><br><div align="center"><input type="submit" name="btn_addSchedule" value="Set Schedule" class="formButton"></div>
</cfif>
<table align="center">
<tr>
<td align="center">
<cfif USERS_key neq request.session.user and request.session.privilege eq 1 or (request.session.privilege eq '2' and Operator_dept eq Ones_dept)>
<input type="Hidden" name="Ones_Users_key" value="#USERS_key#"><br><br>
<cfif isdefined("Ones_Users_key")>
<input type="submit" name="btn_addOnesSchedule" value="Submit #getuser.USERS_fname#'s Schedule" class="formButton">
<cfelse>
<input type="submit" name="btn_addOnesSchedule" value="Set #getuser.USERS_fname#'s Schedule" class="formButton">
</cfif>
</cfif>
</td>
</tr>
</table>
</cfif>
</form>
</td>
</tr>
</table>
</td>
<td background="../images/frame_right.gif"> </td>
</tr>
<tr>
<td height="2" valign="top"><img src="../images/frame_bottomleft.gif" width="7" height="15"></td>
<td height="2" background="../images/frame_bottom.gif"> </td>
<td height="2" valign="top"><img src="../images/frame_bottomright.gif" width="7" height="15"></td>
</tr>
</table>
</cfoutput>The query 'get_schedule' (which i have marked with an asterix at start and finish of the query, its near the top) is supposed to be verifying whether or not there is a record for this month, this year in the database, if there is, then display it, if not, then allow the user to add one. Unfortunately, it always says there isn't one, and so lets the user add one, this means that the user could potentially add multiple calendars for the same month.
The Date/Time format in the table is in the format dd/mm/yyyy all integer values.
Any help would be greatly appreciated, even if you can give me an easier way to do it, that would be great, as i said, im quite new to ColdFusion.
(