ColdFusion Calendar Event Scripting (Inspired By Peter Bell)
Last week, Peter Bell started blogging about building an object oriented calendar system. I have done some blog posts on the display of event calendars, but I was inspired by Pete's OOP posts to look more deeply into the actual setup and querying of events in ColdFusion and SQL server. Naturally, I will not be doing any OOP in this - I leave that up to Peter. Who knows, maybe when I am done with this stuff, I can work with Pete to put this into his OOP world of magic.
When it comes to scripting an event calendar system in ColdFusion, we don't really care about single-day events. Well, we care about them, but they are just not interesting; anyone can build a calendar system that has only single-day events. It's only when we get repeating events that things start to get really interesting. These are events that start on one date, end on another date, and repeat in a given pattern for all the dates in between.
These are the event repetition types that I decided to tackle in my first go-round (yes, there will be more posts about this topic):
- Daily
- Weekly
- Bi-weekly (every two weeks)
- Monthly
- Yearly
- Monday - Friday
- Saturday - Sunday
After I started coding, I came up with another one:
Every Nth day of the month (ex. Every Second Tuesday), but I figured I would address that in my next phase of development.
The database for a simple ColdFusion event calendar system can be quite easy. Assuming you are not dealing with things like user-specific calendars, security, event exceptions, and all that jazz (to be addressed in later posts), all you really need is one table, Event:
ID - Primary Key / Auto incrementer.
Name - VARCHAR( 100 ); Name of event.
Description - TEXT; Description of event.
Date_Started - SMALLDATETIME; Date on which the event starts.
Date_Ended - SMALLDATETIME; Date on which the event ends.
Time_Started - VARCHAR( 5 ); Time of day at which the event starts in the format of (TT:mm).
Time_Ended - VARCHAR( 5 ); Time of day at which the event ends in the format of (TT:mm).
Is_All_Day - TINYINT; Short-hand flag for no-time events.
Repeat_Type - TINYINT; The type of repeating this event will do (ie. Daily, Weekly, Monthly, etc.).
Date_Updated - SMALLDATETIME; The timestamp of the last update.
Date_Created - SMALLDATETIME; The timestamp on which the event was created.
I added the Is_All_Day column so that I wouldn't always have to check the time started/ended columns, but I actually haven't even used it yet in the code. It might get taken out eventually. I am storing the Time Started and Time Ended in 24-hour format VARCHAR field because I feel it will be used less often and I don't want to store an incorrect date in that field. This might be a bad practice, but it has worked fine for me so far and does not require "default dates"
Before we get into the code, you might want to check out the online demo:
Warning: The Add Event page was mad quick and dirty and might not have the best error handling. The labelling also sucks! I really just needed that page to populate a database table to test with.
Now, onto the code. Let me talk about it a bit first, so you can take notice of some things. I wanted to code the algorithm in such a way that I would easily be able to add new repeat-types if they ever presented themselves. As such, I am sacrificing some more efficient code for certain repeat types in favor of some code that was more easily factored out. For example, I am using a Conditional CFLoop which would be less efficient than a straight up Index CFLoop for daily repetition; however, since not all repeat types can do that, I went for the Conditional loop on all repeat types.
To try and be as efficient as possible, I am doing my best to only examine events that might occur during the given time period (the current month of a month-view). This is not always easy; for example, events that repeat every year, I found it easier to start looping from the year before. It was just easier than trying to determine the logic of getting the next year that was still within (if at all) the given time period.
The real magic was finding the "Start Date" for the Conditional loop of each type of repeating event. Once I had that down, I could then factor out the code for populating the event query by using a Start Date, End Date, Increment Type (ex. m, d, yyyy), and Increment Value (ex. 1, 7) variable.
Ok, enough jabbering, let's take a look at the code. I am only showing you the Month View code as the code to add the events was totally Ghetto. I just wrote it fast so I could populate the database table.
<!--- 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" />
As you can see (maybe) I am using a combination of ColdFusion query of queries as well as event-day-indexing. ColdFusion query of queries, while being one of the best things since sliced bread and the Maria Bello nude scene, are not exactly the most efficient actions to be taking many times on a single page. Therefore, I am also using event-day-indexing to make sure I only perform the ColdFusion query of queries on days that definitely have events.
Also, I grab the basic event definitions out of the database via the Raw Event query. These events, while useful from a theoretical standpoint, do very little to help us actually populate the month-view of the calendar. Therefore, I loop over the raw events and then populate an Event query that takes the raw event data and duplicates it as many times as is necessary for the given time period. Not sure if this is the best way to go, but it is the best that I could come up with over the weekend.
So that's where I am now. In my next post, I will try to tackle more repeat types and event exception handling - that's where it get's really exciting.
Want to use code from this post? Check out the license.
Reader Comments
Ben,
Very well-written and I like your approach. In the past, I've used an event table and a linked event date table. My thought at the time was that it would allow forward computability if I ever wanted to add recurring events.
For example, suppose we want to schedule a weekly status meeting. Now suppose we want to post a different agenda for each week's status meeting? Microsoft Outlook allows us to do this - you edit an event and it asks if you want to change every event in the series or if you just want to change this particular event. The latter choice lets us set the agenda for this particular meeting, the former sets the agenda for every meeting in the series.
One way to tackle this would be to have seperate tables for event "meta" information (e.g., event name, location, etc.), and another table for event details information (e.g., the date, description info).
Is there a better way to do this?
@Christian,
I may be completely wrong on this, but this is something I want to try in my next iteration. I think when you edit "Just this occurrence", it is actually creating a NEW event for just that day and then adding an exception for the series. So, the series then becomes (for ex.):
- Every Friday EXCEPT 7/27
- 7/27 is new event with different agenda
I think it happens this way, because I believe (though have not really tested it) is that if you change the start TIME for entire series, it will NOT change the start TIME of the single event (I assume because it is not technically part of the series anymore).
Of course, this is all theory :) Let's see what I come up with.
@Christian,
Also, I think the same thing applies to "all forward instances". That doesn't actually create excpetions for the forward events - I think it actually splits the event into two new events (or created a new event with that start date and updates the old event with the given end date). ... but again, I will be exploring this stuff shortly (hopefully).
Ben, this is very handy and an interesting approach. I have been pondering this topic for the next version of our CMS and have been looking into the multi table approach that Christian mentions. The tough part about having a single record is the searching (with recurring events), which you seem to have done well with. Why is it that calendaring always seems so easy at first, but then all the exceptions and aberrations crop up to slop it up? :-)
@Joshua,
One of the things I have tried to do here is define all of my queries not as "month start" or "month end" but as "first day" and "last day". This way, the time frame we are examining shouldn't make any difference to the algorithm, which should make our lives a whole lot easier.
As far as all the exceptions that crop up to muck up the works... that's the battle I will fight next :D
I happen to have a public-facing calendaring application, so I have had the pleasure of running up against all of these issues and more.
The toughest one is a recurrence type of what I call "Monthly, same days of the week." That's not the greatest description, but it takes into account things like "1st and 3rd Friday each month" or "Last Thursday of the month".
You have to worry about things like "does this month have a fifth Friday" or "which week in the month has the last Tuesday in it." There is just no way to make this code look elegant.
I do have two tables to contain events, one for the actual single day events, the other a linked table which contains metadata for the recurring event groups.
When editing an event that is part of a recurring group, I offer the option to "detach" it from the recurring group, or keep it "attached". If you detach it, subsequent changes to the recurring group will not affect the edited event.
Anyway Ben, nice post and don't drive yourself too crazy, it's easy to do when dealing with calendars.
@Josh,
I like the idea of giving people the option to "detach" exceptions from the series OR not to. I had always just assumed detach by default, but I guess this is not a requirement.
Here's a follow up question: Let's say you have an event that repeats every Friday. Then, one Friday, you detach one event to be on Thursday. So, now, you have a repeating even on Friday except for one week. Then, let's say you edit the "entire" series and set the repeat day to Monday instead of Friday. Does the original exception still exist? Or does the fact that the updated series is now on a different date then void the series exceptions that were based on dates?
I am sure I will help answer these for myself, but I would love some feedback :)
Ben,
I will definately be following this series. Remember the shift calendar item we, well rather I should say you, figured out? That is an example of the repeating every nth day. The shift name would be a parent event for the day and then you could have child events (for example who is off duty) for that day and then the meta data for the event (from when to when, was it vacation, sick leave, comp time etc).
I am still trying to piece togethor every thing for this calendar program. The multiple variables will drive me nuts before I am done.
I eagerly await your posts on this subject
Jim
@Jim,
I am working right now on piecing all the parts of this post together as well. Right now, I am putting a nice interface on the calendar and adding some different views (year / week). Then I have to clean up the logic and then I will just make the code available.
Ben - You are my hero. This is fantastic. I google a problem I'm having and your blog ALWAYS comes up.
@Ben,
Awesome my man!! Glad to be there when needed.