In Part 1 of my article I described and provided an ASP based framework to dynamically generate a calendar framework for web applications. In this second part I will show how to add event display/adding/editing of appointments or "reserved blocks" of time. Many of you have emailed me about the calendar code and I've added a few of the requested features as well as fixing a few bugs that were discovered. And now on to the code!

DB Architecture

Firstly, if you are going to be using this calendar on a large scale enterprise type web site, then I would strongly advocate modifying the code base to use SQL Server or Oracle as a DB backend.

For ease of deployment and setup for the readers of this article, I have used a simple Access database for the storing and retrieving of data. If you take a quick look at the database tables, you'll see that there are only two tables - one to store the event information, and another to store a list of resources. If you want to increase the amount and type of data stored, it might be wise to study the architecture and normalize the DB tables to fit in with the additional data that you might need to store. I've inserted some dummy data for you to view. You might want to manually go in and change the Event dates in the Events table so that when you bring up the calendar application, you don't have to scroll back through weeks to view the sample data.

Dynamic Calendar Generation

A lot of people have emailed me in response to the first article saying how hard a time they've had trying to add event handling to the previous calendar code. Some even said that they'd given up and were just going to wait to read the second part of the article. Well, it is a difficult task, but it is manageable if you take a moment to think about it. Basically we are trying to display some data visually in the appropriate cells within the calendar. Problems arise because there are so many table cells to display. And for each cell in the table, we need to decide if it's in a reservation and if it's the start or end of a reservation. Row spanning is also a problem.

If you have a reservation that is 45 minutes long and the table is broken up into 15 minute increments, how do you make sure that the columns all line up when there are multiple reservations on different days of the week, or for different resources? Another problem that arises is the load time of the calendar views. A lot of comparison operations and queries have to occur to determine the appropriate actions to take when generating the view. It is very easy to do something inefficient that works, but takes too long to display. In the rest of this article, I will explain and present to you the code that has been written to solve all of these problems.

Day View

I've created 7 different "Resources" or users for use in this example. The day view consists of the appointments/reservations for each of those 7 resources for the day in question. If you take a look at the screenshot you will see that the resource names are displayed across the top of the header row and the individual appointments are displayed shaded with the display string text inserted into the table cell. You should also note that clicking on any of the cells will lead you to an add/edit screen for that time. If you click on an existing reservation, you will be able to edit its details.

So, how did I display all of this data? First, I query the Events table for all of the data that occurs on the given day:

<%
sSQL = "Select * From Events where EventDate = '" & selectedDate & "'"
set rsEventRecordset = GetRecordset(sSQL)
%>

Now I have the data that I need to display the appointments. As you can see from the next bit of code, I rewrote the display mechanism to loop through the columns (there are 7, 1 for each resource) and populate the table cells. I also wrote two helper functions that I will be calling throughout the different calendar views to determine whether or not a cell should be considered part of a reservation. Those two helper functions are CheckForEvent and IsInEvent . CheckForEvent determines based on a resource name, time and duration, whether or not the cell is involved in a reservation from the recordset. IsInEvent checks to see if the cell exists within a reservation. For example, if a reservation starts at 9:00 and lasts 45 minutes, I need to be able to tell which cells also belong in that reservation for the purpose of spanning multiple rows in the display.

<%
Function CheckForEvent(sRS,sResource,dTime,sTitle,Duration)
   with sRs
      if .recordcount <> 0 then
        'set recordset filter
        .filter = ("ResourceName = '" & sResource & "' AND 
                    StartTime = '" & FormatTimeCompare(dTime) & "'")
   
         if .recordcount = 1 then
           CheckForEvent = cint(.fields("Duration")/15)
           sTitle = cstr(.fields("DisplayString"))
           Duration = cint(.fields("Duration"))
         end if 
      else
         CheckForEvent = 0
         sTitle = ""
         Duration = 0
      end if
      'take out filter
      .filter = ""
   end with 
End Function
Function IsInEvent(sRS,sResource,dTime)
   dim dEnd
   with sRs
   if .recordcount <> 0 then
     'set recordset filter
     .filter = ("ResourceName = '" & sResource & "'")
     if .recordcount >=1 then
       dEnd = dateadd("n",cint(.fields("Duration")),  
                          cdate(.fields("StartTime")))
       if cdate(dTime) >= cdate(.fields("StartTime")) 
                   AND cdate(dTime) < cdate(dEnd) then
         IsInEvent = True
       else
         IsInEvent = False
       end if 
     end if 
   end if 
   'take out filter
   .filter = ""
   end with 
End Function
%>

It would be of great benefit to examine the code in the calendar_d.asp page very carefully and see how the two above functions allow me to determine the status of the table cells as the table is being built. To avoid using for loops to cycle through the recordsets, I opted to use ADO's built in Filter function to quickly filter out the records that I want from the recordset being passed in. This enhances the speed at which it takes to determine if a cell contains an event for a specified resource. The load time of this page, even with a bunch of reservations is rather quick.

One suggestion for the day view would be to allow selection of events for just one resource or more than 7 resources. In its current implementation, the calendar is limited to 7 resources (mostly because that's how many columns I had to work with to maintain similarity between the day and week views). A worthwhile enhancement to this view would to allow paging through resources. You could display left and right navigation arrows on the calendar that would allow you to display resources in groups. For example, you could display 5 at a time and page through them much like paging through a recordset. That code might be important, as I'm sure that some users might want to have more than the 7 possible resources as I've included in this application.

Week View

Composing the week view is almost a direct copy of the day view. But on the week view I've added the option of switching the view to display the reservations for a specific user. In the upper right hand corner, you can see a drop down box that I've filled with the resource names that are in the Resource table in the database. I've also added some JavaScript code that will reload the page with the appropriate data depending on which resource has been selected.

To build the list of resources, I simply queried the database and then added the resource names to an array that I can continue to use. I performed this same query on the day view to keep the resources around for the queries to CheckForEvent and IsInEvent .

<%
Dim aResources(7)
set rsResourceNames = GetRecordset("Select * From Resources")
rsResourceNames.movefirst
   for i = 1 to 7
      aResources(i) = cstr(rsResourceNames("ResourceName"))
      rsResourceNames.movenext
   next
%>

The challenge in building this page is cycling through the different days in the week and querying for data that might need to be displayed. To simplify things and reduce the number of database queries needed, I query for all events during the week, from the start of the week to the end of the week. I could have done one query for each day of the week, but that would take longer than the time needed to cycle through the whole result set, so I decided to grab all the records at once.

<%
sSQL = "Select * From Events where EventDate between #" & cdate(firstDate) &  
       "# and #" & cdate(lastDate) & _ 
       "# and ResourceName = '" & _
       CurrentResource & "'"
set rsEventRecordset = GetRecordset(sSQL)
%>

Other than the difference with the query, the logic for building the table is almost exactly the same as the logic used in generating the day view. Except here the data being shown is only for one resource for the whole week. There are also two slightly modified functions called CheckForEventWeek and IsInEventWeek that mimic the functionality that the corresponding functions provided for the day view, but with a few slight differences that account for the week instead of the day.

Month View

The month view is much easier to display compared to the day and week views, although you might want to change what gets displayed on the month view for reservations. In my code I simply color the cell a different color if there is ANY reservation on that day. It doesn't matter who has a reservation. If at least one resource is booked on the given day, I color the cell. You might want to change this so that like the week view, you can select the resource in question via a drop down box and then dynamically reload the month view to display data only for the selected resource.

To determine whether or not a day contains any events, I wrote a function that I call CheckForEventMonthYear . You can see its contents below:

<%
Function CheckForEventMonthYear(sRS,dDate)
   with sRS
      if .recordcount <> 0 then
        'set recordset filter
        .filter = ("EventDate = '" & cstr(dDate) & "'")
         if .recordcount >= 1 then
           CheckForEventMonthYear = True
           exit function
         end if 
      else
         CheckForEventMonthYear = False
      end if
      
      'take out filter
      .filter = ""
   end with
End Function
%>

This function simply filters the events table based on the current date. If there is at least one event that occurs on the date filtered by, the function returns true. The filter operation allows us to quickly grab the information we need from the recordset without having to do looping through the recordset to determine if events exist. By grabbing the whole recordset from the events table, we reduce the number of queries that need to be made to the database.

Year View

The year view is much like the month view. Actually, the method that I use is exactly the same function as the month view. Since the year view is basically just an iteration of all the month views, the logic is exactly the same in determining whether or not an event occurs on a selected day. Again, you may want to modify this view to selectively display events based on a resource selection. You might also want to create some sort of special event type to designate holidays and such.

Room for Improvement

One idea that comes to mind is to create an ASP component that can be used to generate the calendars and provide any backend functionality you might require. You could then easily get rid of the current .asp pages and encapsulate the functionality into method calls of a VB component. Why would you want to create a VB component instead? Building a component will help in scaling your application. Compiled VB .dlls run much faster than the interpreted VBScript in the current asp pages. Depending on the complexity of your calendar, the load time may become longer if all the logic is left in ASP pages. Another way we could improve performance is by porting the calendar database to SQL Server and utilizing stored procedures in the database calls.

Depending on your specific needs in a calendar system for your website, you might want to make some modifications to the linking structure. For example, you might want the result of clicking on a day in the month view to go to the day instead of the week as I've implemented. I've tried to make this as generic and flexible as possible to allow for ease of expansion and modifications.

Right away, I can think of a bunch of worthwhile additions. You could add email addresses to the resources and automatically email them links to appointment information. You could implement a reporting system to list out pending reservations for the day/week/month etc. The possibilities for improvement are endless.

What's Included in the Sample Application

The sample application I've written to demonstrate the calendar generation consists of 10 ASP pages as well as the Access DB that make up the full functionality as described in this article.

• calendar_m.asp
• calendar_w.asp
• calendar_m.asp
• calendar_y.asp
• addevent.asp
• editevent.asp
• process_action.asp
• common.asp
• config.asp

To install the application and test it out, you must first have IIS or Personal Web Server installed on your computer. Unzip the zip file and copy the contents to a virtual directory that you created on the web server. There is also a default.asp page that exists as a framework for running the sample application. After you have completed the set up, open up your browser and point it to the directory that you just created.

Conclusion

In this article I have described and presented an ASP calendar framework that you can use and integrate into your web site. I've presented a DB framework that you can use to dynamically add and edit events in the calendar. Again, I hope that this second installment of this article is what you've been waiting for!