<% 'retrieve values from form if user has tried to log in varUsername = Request.Form("txtUsername") varPassword = Request.Form("txtPassword") 'retrieve month and year (for which to display calendar) from the querystring varCalMonth = Request.QueryString("calmonth") varCalYear = Request.QueryString("calyear") 'if the querystring didn't include a month/year then assign today's month/year instead If varCalMonth = "" Then varCalMonth = Month(Now()) If varCalYear = "" Then varCalYear = Year(Now()) varFailedLogon = 0 'if the logged attribute in the querystring is equal to "9" then the user clicked log out and has their session variable annulled If Request.QueryString("logged") = 9 Then session("STAFF_ID") = "NULL" Set objConnect = Server.CreateObject("ADODB.Connection") objConnect.Open "DBQ=" & Server.Mappath("db\SIMSShell_REVISED.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};" If session("STAFF_ID") <> "NULL" Then sqlStatement = "SELECT * FROM UserDetails WHERE STAFF_ID='" & session("STAFF_ID") & "'" set rsConnect = objConnect.execute(sqlStatement) If Not rsConnect.eof Then varUsername = rsConnect("USERNAME") varPassword = rsConnect("PASSWORD") End If End If sqlStatement = "SELECT * FROM UserDetails WHERE USERNAME='" & varUsername & "' AND PASSWORD='" & varPassword & "'" set rsConnect = objConnect.execute(sqlStatement) %> Timetable Main Page
Horsforth School - Teachers' Timetable <% If varFailedLogon = 1 Then Response.Write("
Your username or password is incorrect, please recheck your user details and attempt to login again") varFailedLogon = 0 End If %> <% '############ MAIN CONDITIONAL STATEMENT 1: EITHER TELLS USER THEIR PASSWORD IS INVALID AND SHOWS THEM THE LOGON FORM OR... DISPLAYS A LOGOUT LINK ############ 'If the username and password entered into the form was not present in the database... If rsConnect.bof And rsConnect.eof Then session("STAFF_ID") = "NULL" %>
Username: Password:
<% 'Otherwise set the session variable as the STAFF_ID for the entered username and password Else session("STAFF_ID") = rsConnect("STAFF_ID") 'And display a log out button %> LOG OUT

<% End If '############ END CONDITIONAL STATEMENT 1 ############ %>
<% '############ MAIN CONDITIONAL STATEMENT 2: IF THE USER HAS LOGGED IN... ############ If session("STAFF_ID") <> "NULL" Then %> <% If varCalMonth = 1 Then prevMonth = 12 Else prevMonth = varCalMonth - 1 End If If varCalMonth = 1 Then prevYear = varCalYear - 1 Else prevYear = varCalYear End If If varCalMonth = 12 Then nextMonth = 1 Else nextMonth = varCalMonth + 1 End If If varCalMonth = 12 Then nextYear = varCalYear + 1 Else nextYear = varCalYear End If %>
Today: (<%Response.Write(FormatDateTime(Now(), vbShortDate))%>)
<% sqlStatement = "SELECT * FROM DateWeek WHERE DATE=#" & Month(Now()) & "/" & Day(Now()) & "/" & Year(Now()) & "#" set rsConnect = objConnect.execute(sqlStatement) If rsConnect.eof Then periodMin = 51 Else periodMin = (WeekDay(Now()) - 2)*5 + (rsConnect("WEEK") - 1)*25 + 1 End If sqlStatement = "SELECT * FROM TimeTable WHERE STAFF_ID='" & session("STAFF_ID") & "' AND PERIOD>=" & periodMin & " AND PERIOD<=" & periodMin + 4 & " ORDER BY PERIOD ASC" set rsConnect = objConnect.execute(sqlStatement) tempCount = 1 Do While tempCount < 6 If rsConnect.eof Then %> ") tempCount = tempCount + 1 Loop %>
Today's Timetable: <% 'retrieve alerts and meeting notifications sent to the logged user sqlStatement = "SELECT FORENAME, SURNAME, Calendar.ENTRY_ID, SHORT_DESC, CALENDAR_OF FROM (Calendar LEFT JOIN AlertRecipients ON Calendar.ENTRY_ID = AlertRecipients.ENTRY_ID) LEFT JOIN Staff ON Calendar.ENTERED_BY = Staff.STAFF_ID WHERE (CALENDAR_OF='ALERTS' AND AlertRecipients.STAFF_ID='" & session("STAFF_ID") & "' AND READ=false) OR (CALENDAR_OF='MEETINGS' AND AlertRecipients.STAFF_ID='" & session("STAFF_ID") & "' AND READ=No)" set rsConnect = objConnect.execute(sqlStatement) '############ MAIN CONDITIONAL STATEMENT 3: IF THERE ARE ALERTS AND MEETINGS TO BE DISPLAYED... ############ 'if there are any in the database then... If Not rsConnect.eof Then %> <% End If '############ END CONDITIONAL STATEMENT 3 ############ %>
<% 'Display all meeting notifications and alerts Do Until rsConnect.eof %>','','width=320,height=240,scrollbars=no,resizable=no'); location.href='logon.asp'" style="color: #FFFFFF; cursor: default; cursor: hand;"><% If rsConnect("CALENDAR_OF") = "ALERTS" Then Response.Write("Alert from " & rsConnect("FORENAME") & " " & rsConnect("SURNAME") & ": ") Else Response.Write("Meeting organised by " & rsConnect("FORENAME") & " " & rsConnect("SURNAME") & ": ") End If Response.Write(rsConnect("SHORT_DESC") & " (click to read full text)
") rsConnect.movenext Loop %>
Today |  Alerts |  Meetings  
Things to do today:
<% 'end of today dateEnd = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now()) & " 23:59:59" 'beginning of today dateStart = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now()) & " 00:00:00" 'retrieve all meetings and calendar entries for the logged user on "today" sqlStatement = "SELECT Calendar.*, AlertRecipients.* FROM Calendar LEFT JOIN AlertRecipients ON Calendar.ENTRY_ID = AlertRecipients.ENTRY_ID WHERE ((Calendar.CALENDAR_OF='" & session("STAFF_ID") & "') OR (Calendar.CALENDAR_OF='MEETINGS' AND AlertRecipients.STAFF_ID='" & session("STAFF_ID") & "')) AND DATE_START<=#" & dateEnd & "# AND DATE_START>=#" & dateStart & "# ORDER BY DATE_START ASC" set rsConnect = objConnect.execute(sqlStatement) If rsConnect.eof Then Response.Write("There are no entries on your calendar for today") 'write out all calendar entries for today Do Until rsConnect.eof Response.Write("Description: " & rsConnect("ENTRY_TEXT") & "
Start: " & rsConnect("DATE_START") & " Finish: " & rsConnect("DATE_END") & "

") rsConnect.movenext Loop %>
Period <% Response.Write(tempCount) %>
FREE
<% Else If rsConnect("PERIOD") = periodMin + tempCount - 1 Then %>
Period <% Response.Write(tempCount) %>
<% Response.Write(rsConnect("CLASS") & "
" & rsConnect("ROOM")) %>
<% rsConnect.movenext Else %>
Period <% Response.Write(tempCount) %>
FREE
<% End If End If Response.Write("
<<Prev <%=MonthName(varCalMonth)%> <%=varCalYear%> Next>>    Jump to month:        
<% 'sets daynumber as number of days in month currently being viewed If varCalMonth = 9 Or varCalMonth = 4 Or varCalMonth = 6 Or varCalMonth = 11 Then dayNumber = 30 If varCalMonth = 1 Or varCalMonth = 3 Or varCalMonth = 5 Or varCalMonth = 7 Or varCalMonth = 8 Or varCalMonth = 10 Or varCalMonth = 12 Then dayNumber = 31 If varCalMonth = 2 Then 'Handle leapyears if varCalYear/4 = cint(varCalYear/4) then dayNumber = 29 Else dayNumber = 28 End If End If 'sets monthStart as the beginning of the first day of this month in the format mm/dd/yyyy hh:mm:ss monthStart = varCalMonth & "/01/" & varCalYear & " 00:00:00" 'sets monthEnd as the beginning of the first day of the next month in the format mm/dd/yyyy hh:mm:ss If varCalMonth <> 12 Then monthEnd = varCalMonth + 1 & "/01/" Else monthEnd = "1/01/" End If If varCalMonth = 12 Then monthEnd = monthEnd & varCalYear + 1 & " 00:00:00" Else monthEnd = monthEnd & varCalYear & " 00:00:00" End If 'retrieves all calendar entries relevant to the logged user (including meeting notifications) from the database sqlStatement = "SELECT Calendar.* FROM Calendar LEFT JOIN AlertRecipients ON Calendar.ENTRY_ID = AlertRecipients.ENTRY_ID WHERE ((Calendar.CALENDAR_OF='" & session("STAFF_ID") & "') OR (Calendar.CALENDAR_OF='MAIN') OR (Calendar.CALENDAR_OF='MEETINGS' AND AlertRecipients.STAFF_ID='" & session("STAFF_ID") & "')) AND DATE_START<=#" & monthEnd & "# AND DATE_START>=#" & monthStart & "# ORDER BY DATE_START ASC" set rsConnect = objConnect.execute(sqlStatement) %> <% 'Generates a row of day name column headings For i = 1 to 7 Response.Write("") Next %> <% 'count the number of entries on the calendar this month entryCount = 0 'count of the number of actual days rendered as cells in the calendar table varCalDayCount = 1 'count of the current calendar cell varCalLoopCount = 1 '################ CALENDAR DRAW LOOP ################ Do While varCalDayCount < dayNumber + 1 %> <% varCalCount = 1 'For every day in this week, write the day number in a cell 'If the day is today, make the background of this cell red (#FF0000) Do While varCalCount < 8 'If the current cell represents a day in this month... otherwise leave the cell blank If varCalLoopCount >= WeekDay(MonthName(varCalMonth) & " 01, " & varCalYear) And varCalDayCount < dayNumber + 1 Then If varCalDayCount = Day(Now()) And Cint(varCalMonth) = Month(Now()) And cint(varCalYear) = Year(Now()) Then Response.Write("") varCalCount = varCalCount + 1 varCalLoopCount = varCalLoopCount + 1 'If all the days in this week have been looped through... If varCalCount = 8 Then %> <% varCalCount = 1 varCalLoopCount = varCalLoopCount - 7 'Essentially, this subtracts a large enough number from "varCalDayCount" to send it back to the value that represents the beginning of the week currently being looped through If varCalDayCount = dayNumber + 1 And ((WeekDay(MonthName(varCalMonth) & " 01, " & varCalYear) - 1 + dayNumber) mod 7) <> 0 Then varCalDayCount = varCalDayCount - ((WeekDay(MonthName(varCalMonth) & " 01, " & varCalYear) - 1 + dayNumber) mod 7) Else If varCalDayCount > 7 Then varCalDayCount = varCalDayCount - 7 Else varCalDayCount = 1 End If End If 'Repeat the loop for the second row of cells for the current week Do While varCalCount < 8 If varCalLoopCount >= WeekDay(MonthName(varCalMonth) & " 01, " & varCalYear) And varCalDayCount < dayNumber + 1 Then Response.Write("") varCalCount = varCalCount + 1 varCalLoopCount = varCalLoopCount + 1 Loop End If Loop %> <% Loop '################## END CALENDAR DRAW LOOP ##################### %>
" & WeekDayName(i, True) & "
 ") Else Response.Write(" ") End If Response.Write(varCalDayCount) varCalDayCount = varCalDayCount + 1 Else Response.Write(" ") End If Response.Write("
 ") If Not rsConnect.eof Then currentDay = Left(rsConnect("DATE_START"), InStr(rsConnect("DATE_START"), "/") - 1) Do While varCalDayCount = Cint(currentDay) entryCount = entryCount + 1 %><%=rsConnect("SHORT_DESC")%><% If rsConnect("CALENDAR_OF") = "MEETINGS" And session("STAFF_ID") = rsConnect("ENTERED_BY") Then %> [X]
<% Else If rsConnect("CALENDAR_OF") = "MEETINGS" Then %>
<% Else If rsConnect("CALENDAR_OF") = "MAIN" And session("STAFF_ID") <> rsConnect("ENTERED_BY") Then %>
<% Else %> [X]
<% End If End If End If rsConnect.movenext If rsConnect.eof Then currentDay = "32" Else currentDay = Left(rsConnect("DATE_START"), InStr(rsConnect("DATE_START"), "/") - 1) End If Loop End If If (cint(varCalYear) >= Year(Now()) And cint(varCalMonth) >= Month(Now()) And varCalDayCount >= Day(Now())) Or (cint(varCalYear) > Year(Now())) Or (cint(varCalYear) >= Year(Now()) And cint(varCalMonth) > Month(Now())) Then %>
New entry<% Else Response.Write(" 

") End If varCalDayCount = varCalDayCount + 1 Else Response.Write("		 ") End If Response.Write("
 
<% 'If the user didn't log on, don't show them anything Else %>   <% End If '############# END CONDITIONAL STATEMENT 2 ############# %>
<% 'close database connection objects rsConnect.close set objConnect = nothing %>