<!--- Kill extra output. ---> <cfsilent> <!--- Param the URL attributes. ---> <cftry> <cfparam name="URL.month" type="numeric" default="#Month( Now() )#" /> <cfcatch> <cfset URL.month = Month( Now() ) /> </cfcatch> </cftry> <cftry> <cfparam name="URL.year" type="numeric" default="#Year( Now() )#" /> <cfcatch> <cfset URL.year = Year( Now() ) /> </cfcatch> </cftry> <!--- Based on the month and year, let's get the first day of this month. In case the year or month are not valid, put this in a try / catch. ---> <cftry> <cfset dtThisMonth = CreateDate( URL.year, URL.month, 1 ) /> <cfcatch> <!--- If there was an error, just default the month view to be the current month. ---> <cfset dtThisMonth = CreateDate( Year( Now() ), Month( Now() ), 1 ) /> </cfcatch> </cftry> <!--- Get the last day of the month. ---> <cfset dtLastDayOfMonth = ( DateAdd( "m", 1, dtThisMonth ) - 1 ) /> <!--- Now that we have the first day of the month, let's get the first day of the calendar month - this is the first graphical day of the calendar page, which may be in the previous month (date-wise). ---> <cfset dtFirstDay = ( dtThisMonth - DayOfWeek( dtThisMonth ) + 1 ) /> <!--- Get the last day of the calendar month. This is the last graphical day of the calendar page, which may be in the next month (date-wise). ---> <cfset dtLastDay = ( dtLastDayOfMonth + 7 - DayOfWeek( dtLastDayOfMonth ) ) /> <!--- Query for raw events. This is raw because it does not fully define all events that need to be displayed, but rather defines the events in theory. ---> <cfquery name="qRawEvent" datasource="#REQUEST.DSN.Source#"> SELECT e.id, e.name, e.date_started, e.date_ended, e.time_started, e.time_ended, e.repeat_type FROM event e WHERE <!--- Check end date constraints. ---> ( e.date_ended >= <cfqueryparam value="#dtFirstDay#" cfsqltype="CF_SQL_TIMESTAMP" /> OR e.date_ended IS NULL ) AND <!--- Check start date constraints. ---> e.date_started <= <cfqueryparam value="#dtLastDay#" cfsqltype="CF_SQL_TIMESTAMP" /> </cfquery> <!--- Now that we have our raw events, let's put together a query that will contain all the events that actually need to be displayed (repeated events will be repeated in this query). Since our raw query may contain events that only display once, let's build this query by performing a query of query to get those single-day events. ---> <cfquery name="qEvent" dbtype="query"> SELECT *, <!--- We are going to add a column that will be the lookup index for the day - remember the date_started may be different than the display date for most repeating events. ---> ( 0 ) AS day_index FROM qRawEvent WHERE repeat_type = 0 </cfquery> <!--- Update the day index and event index of the events that we just put into our event query. ---> <cfloop query="qEvent"> <!--- Update query. ---> <cfset qEvent[ "day_index" ][ qEvent.CurrentRow ] = Fix( qEvent.date_started ) /> </cfloop> <!--- Now, we will loop over the raw events and populate the calculated events query. This way, when we are rendering the calednar itself, we won't have to worry about repeat types or anything of that nature. ---> <cfloop query="qRawEvent"> <!--- No matter what kind of repeating event type we are dealing with, the TO date will always be calculated in the same manner (it the Starting date that get's hairy). If there is an end date for the event, the the TO date is the minumium of the end date and the end of the time period we are examining. If there is no end date on the event, then the TO date is the end of the time period we are examining. ---> <cfif IsDate( qRawEvent.date_ended )> <!--- Since the event has an end date, get what ever is most efficient for the future loop evaluation - the end of the time period or the end date of the event. ---> <cfset dtTo = Min( qRawEvent.date_ended, dtLastDay ) /> <cfelse> <!--- If there is no end date, then naturally, we only want to go as far as the last day of the month. ---> <cfset dtTo = dtLastDay /> </cfif> <!--- Set the default loop type and increment. We are going to default to 1 day at a time. ---> <cfset strLoopType = "d" /> <cfset intLoopIncrement = 1 /> <!--- Set additional conditions to be met. We are going to default to allowing all days of the week. ---> <cfset lstDaysOfWeek = "" /> <!--- Check to see what kind of event we have - is it a single day event or an event that repeats. If we have an event repeat, we are going to flesh it out directly into the event query by adding rows. The point of this switch statement is to use the repeat type to figure out what the START date, the type of loop skipping (ie. day, week, month), and the number of items we need to skip per loop iteration. ---> <cfswitch expression="#qRawEvent.repeat_type#"> <!--- Repeat daily. ---> <cfcase value="1"> <!--- Set the start date of the loop. For efficiency's sake, we don't want to loop from the very beginning of the event; we can get the max of the start date and first day of the calendar month. ---> <cfset dtFrom = Max( qRawEvent.date_started, dtFirstDay ) /> <!--- Set the loop type and increment. ---> <cfset strLoopType = "d" /> <cfset intLoopIncrement = 1 /> </cfcase> <!--- Repeat weekly. ---> <cfcase value="2"> <!--- Set the start date of the loop. For efficiency's sake, we don't want to loop from the very beginning of the event; we can get the max of the start date and first day of the calendar month. ---> <cfset dtFrom = Max( qRawEvent.date_started, dtFirstDay ) /> <!--- Since this event repeats weekly, we want to make sure to start on a day that might be in the event series. Therefore, adjust the start day to be on the closest day of the week. ---> <cfset dtFrom = ( dtFrom - DayOfWeek( dtFrom ) + DayOfWeek( qRawEvent.date_started ) ) /> <!--- Set the loop type and increment. ---> <cfset strLoopType = "d" /> <cfset intLoopIncrement = 7 /> </cfcase> <!--- Repeat bi-weekly. ---> <cfcase value="3"> <!--- Set the start date of the loop. For efficiency's sake, we don't want to loop from the very beginning of the event; we can get the max of the start date and first day of the calendar month. ---> <cfset dtFrom = Max( qRawEvent.date_started, dtFirstDay ) /> <!--- Since this event repeats weekly, we want to make sure to start on a day that might be in the event series. Therefore, adjust the start day to be on the closest day of the week. ---> <cfset dtFrom = ( dtFrom - DayOfWeek( dtFrom ) + DayOfWeek( qRawEvent.date_started ) ) /> <!--- Now, we have to make sure that our start date is NOT in the middle of the bi-week period. Therefore, subtract the mod of the day difference over 14 days. ---> <cfset dtFrom = ( dtFrom - ((dtFrom - qRawEvent.date_started) MOD 14) ) /> <!--- Set the loop type and increment. ---> <cfset strLoopType = "d" /> <cfset intLoopIncrement = 14 /> </cfcase> <!--- Repeat monthly. ---> <cfcase value="4"> <!--- When dealing with the start date of a monthly repeating, we have to be very careful not to try tro create a date that doesnt' exists. Therefore, we are simply going to go back a year from the current year and start counting up. Not the most efficient, but the easist way of dealing with it. ---> <cfset dtFrom = Max( CreateDate( (Year( dtFirstDay ) - 1), Month( qRawEvent.date_started ), Day( qRawEvent.date_started ) ), qRawEvent.date_started ) /> <!--- Set the loop type and increment. ---> <cfset strLoopType = "m" /> <cfset intLoopIncrement = 1 /> </cfcase> <!--- Repeat yearly. ---> <cfcase value="5"> <!--- When dealing with the start date of a yearly repeating, we have to be very careful not to try tro create a date that doesnt' exists. Therefore, we are simply going to go back a year from the current year and start counting up. Not the most efficient, but the easist way of dealing with it. ---> <cfset dtFrom = Max( CreateDate( (Year( dtFirstDay ) - 1), Month( qRawEvent.date_started ), Day( qRawEvent.date_started ) ), qRawEvent.date_started ) /> <!--- Set the loop type and increment. ---> <cfset strLoopType = "yyyy" /> <cfset intLoopIncrement = 1 /> </cfcase> <!--- Repeat monday - friday. ---> <cfcase value="6"> <!--- Set the start date of the loop. For efficiency's sake, we don't want to loop from the very beginning of the event; we can get the max of the start date and first day of the calendar month. ---> <cfset dtFrom = Max( qRawEvent.date_started, dtFirstDay ) /> <!--- Set the loop type and increment. ---> <cfset strLoopType = "d" /> <cfset intLoopIncrement = 1 /> <cfset lstDaysOfWeek = "2,3,4,5,6" /> </cfcase> <!--- Repeat saturday - sunday. ---> <cfcase value="7"> <!--- Set the start date of the loop. For efficiency's sake, we don't want to loop from the very beginning of the event; we can get the max of the start date and first day of the calendar month. ---> <cfset dtFrom = Max( qRawEvent.date_started, dtFirstDay ) /> <!--- Set the loop type and increment. ---> <cfset strLoopType = "d" /> <cfset intLoopIncrement = 1 /> <cfset lstDaysOfWeek = "1,7" /> </cfcase> <!--- The default case will be the non-repeating day.Since this event is non-repeating, we don't have to do anything to the envets query as these were the events that we gathered in our ColdFusion query of queries. ---> <cfdefaultcase> <!--- Leave query as-is. ---> </cfdefaultcase> </cfswitch> <!--- Check to see if we are looking at an event that need to be fleshed it (ie. it has a repeat type). ---> <cfif qRawEvent.repeat_type> <!--- Set the offset. This is the number of iterations we are away from the start date. ---> <cfset intOffset = 0 /> <!--- Get the initial date to look at when it comes to fleshing out the events. ---> <cfset dtDay = Fix( DateAdd( strLoopType, (intOffset * intLoopIncrement), dtFrom ) ) /> <!--- Now, keep looping over the incrementing date until we are past the cut off for this time period of potential events. ---> <cfloop condition="(dtDay LTE dtTo)"> <!--- Check to make sure that this day is in the appropriate date range and that we meet any days-of-the-week criteria that have been defined. Remember, to ensure proper looping, our FROM date (dtFrom) may be earlier than the window in which we are looking. ---> <cfif ( <!--- Within window. ---> (dtFirstDay LTE dtDay) AND (dtDay LTE dtTo) AND <!--- Within allowable days. ---> ( (NOT Len( lstDaysOfWeek )) OR ListFind( lstDaysOfWeek, DayOfWeek( dtDay ) ) ))> <!--- Populate the event query. Add a row to the query and then copy over the data. ---> <cfset QueryAddRow( qEvent ) /> <!--- Set query data in the event query. ---> <cfset qEvent[ "id" ][ qEvent.RecordCount ] = qRawEvent.id /> <cfset qEvent[ "name" ][ qEvent.RecordCount ] = qRawEvent.name /> <cfset qEvent[ "date_started" ][ qEvent.RecordCount ] = qRawEvent.date_started /> <cfset qEvent[ "date_ended" ][ qEvent.RecordCount ] = qRawEvent.date_ended /> <cfset qEvent[ "time_started" ][ qEvent.RecordCount ] = qRawEvent.time_started /> <cfset qEvent[ "time_ended" ][ qEvent.RecordCount ] = qRawEvent.time_ended /> <cfset qEvent[ "repeat_type" ][ qEvent.RecordCount ] = qRawEvent.repeat_type /> <!--- Set the date index to this day. This is the value we will use to display the same event on different days of the calendar. ---> <cfset qEvent[ "day_index" ][ qEvent.RecordCount ] = Fix( dtDay ) /> </cfif> <!--- Add one to the offset. ---> <cfset intOffset = (intOffset + 1) /> <!--- Set the next day to look at. ---> <cfset dtDay = Fix( DateAdd( strLoopType, (intOffset * intLoopIncrement), dtFrom ) ) /> </cfloop> </cfif> </cfloop> <!--- The display of the calendar is going to be based on ColdFusion query of queries. While these rock harder than all you can eat buffets, they are not the most efficient. Therefore, in order to minimize the query of query acitivity, we are going to maintain an index of days that even have events. ---> <cfset objEventIndex = StructNew() /> <!--- Loop over the event query to populate the event index with the day indexes. ---> <cfloop query="qEvent"> <cfset objEventIndex[ Fix( qEvent.day_index ) ] = 1 /> </cfloop> </cfsilent> <cfinclude template="_header.cfm"> <cfoutput> <h2> #MonthAsString( Month( dtThisMonth ) )# Events </h2> <p> <a href="#CGI.script_name#?action=add">Add Event</a> </p> <form action="#CGI.script_name#" method="get"> <select name="month"> <cfloop index="intMonth" from="1" to="12" step="1"> <option value="#intMonth#" <cfif (Month( dtThisMonth ) EQ intMonth)>selected="true"</cfif> >#MonthAsString( intMonth )#</option> </cfloop> </select> <select name="year"> <cfloop index="intYear" from="#(Year( dtThisMonth ) - 5)#" to="#(Year( dtThisMonth ) + 5)#" step="1"> <option value="#intYear#" <cfif (Year( dtThisMonth ) EQ intYear)>selected="true"</cfif> >#intYear#</option> </cfloop> </select> <input type="submit" value="Go" /> </form> <table id="calendar" width="100%" cellspacing="1" cellpadding="0" border="0"> <colgroup> <col width="10%" /> <col width="16%" /> <col width="16%" /> <col width="16%" /> <col width="16%" /> <col width="16%" /> <col width="10%" /> </colgroup> <tr class="header"> <td> Sunday </td> <td> Monday </td> <td> Tuesday </td> <td> Wednesday </td> <td> Thursday </td> <td> Friday </td> <td> Saturday </td> </tr> <!--- Loop over all the days. ---> <cfloop index="dtDay" from="#dtFirstDay#" to="#dtLastDay#" step="1"> <!--- If we are on the first day of the week, then start the current table fow. ---> <cfif ((DayOfWeek( dtDay ) MOD 7) EQ 1)> <tr class="days"> </cfif> <td> <a href="##" title="#DateFormat( dtDay, "mmmm d, yyyy" )#" class="daynumber" >#Day( dtDay )#</a> <!--- Since query of queries are expensive, we only want to get events on days that we KNOW have events. Check to see if there are any events on this day. ---> <cfif StructKeyExists( objEventIndex, dtDay )> <!--- Query for events for the day. ---> <cfquery name="qEventSub" dbtype="query"> SELECT id, name, time_started, time_ended FROM qEvent WHERE day_index = <cfqueryparam value="#dtDay#" cfsqltype="CF_SQL_INTEGER" /> ORDER BY time_started ASC </cfquery> <!--- Loop over events. ---> <cfloop query="qEventSub"> <a href="##" class="event" >#qEventSub.name#</a> </cfloop> </cfif> </td> <!--- If we are on the last day, then close the current table row. ---> <cfif NOT (DayOfWeek( dtDay ) MOD 7)> </td> </cfif> </cfloop> </table> </cfoutput> <cfinclude template="_footer.cfm" />