CodeProject: SharePoint Reservations. Free source code and programming help

Report Article

Discuss

Send to a friend

2 votes for this Article.

Popularity: 1.51 Rating: 5.00 out of 5

Introduction

I needed a conference room scheduler that prevented duplicate assignments, in SharePoint 2007, to replace a classic ASP application that depended upon a different authentication model than we were planning to use in our SharePoint upgrade. I also wanted to remove anything specific to our building layout from the existing conference room scheduler, so it could be applied to other resources.

Microsoft’s Room and Equipment template, at first, seemed to be the perfect solution, but its shortcomings were soon evident. The biggest issues were: the user interface did not follow SharePoint standards, and details of the reservation were not visible. It also did not support well known calendar features like recurring appointments and Outlook integration.

After rejecting the Room and Equipment template, I decided to create a new custom SharePoint list based on the Calendar so I could leverage the existing features and functionality. The interface is well known to my users, so extending it would result in minimal training requirements. My biggest requirement was to prevent collisions. Since I wanted to keep things generic, I called the project Reservations. Once a user reserves a time slot, no other user could create an overlapping reservation. Additional requirements included: a custom POC field, and the start date has to be before the end date. The POC field is used when the user doing the scheduling is not the same as the person using the resource, but it can be removed if necessary. As additional resources need to be tracked, new reservation lists can be created by the site administrators.

Visual Studio 2005 extensions for Windows SharePoint Services 3.0, version 1.1 (VSeWSSv11.exe) did the bulk of the work when I created a project based on the list definition template. The list is based on the Event List (Type=106), so all the Calendar features were available automatically. The features necessary to make the list available within a site and the setup.bat that performs the deployment were automatically generated.

To use the code, run setup.bat in the sample application zip file on an MS SharePoint 2007 server, activate the Reservation feature in the desired site, and then create a Reservation List on that site. The setup needs to be run once per server, and the feature activation needs to be run once per site. To activate the feature, select site settings and then site features. In the page that appears with the list of features, find the feature labeled Reservation, and press the Activate button as shown below:

Once the feature is activated, the Reservation option will appear on the Create screen as shown below:

When the item validation fails, a generic exception screen is displayed to the user, and almost all formatting is removed from the error message displayed, as shown below. The user can use the Back button to return to the edit form and adjust the values or cancel. Incidentally, the Room and Equipment template handles collisions the same way.

Since the collision detection occurs as a result of the current user querying the list for an overlapping entry, enabling content approval could result in problems. Two users could each create a reservation that was only visible to themselves and the content administrator. When the content administrator tries to approve a reservation, the other reservation will conflict. The imperfect solution is to delete one of the reservations before approving the other.

Using the code

I created a content type to add my custom POC field, and the Item Event receiver is linked to the content type. The methods of the Item Event receiver are called upon specific user interface action related to a specific item in the list. They tend to be separated into two stages: before the action occurs (-ing) and after the action occurs (-ed). The available event pairs are Add (ItemAdding and ItemAdded), Update (ItemUpdating and ItemUpdated), Delete (ItemDeleting and ItemDeleted), Attachment Add (ItemAttachmentAdding and ItemAttachmentAdded), Attachment Delete (ItemAttachmentDeleting and ItemAttachmentDeleted), Check In (ItemCheckingIn and ItemCheckedIn), and Check Out (ItemCheckingOut and ItemUncheckedOut). Unpaired events include: ContextEvent, ItemFileConverted, ItemFileMoved, and ItemFileMoving.

Most of the code is in the ItemEventReceiver class, except the recurring handling described below in Points of interest. To handle the validation, the ItemAdding and ItemUpdating methods of the Item Event Receiver were overloaded. Both methods take a SPItemEventProperties object (properties) as a parameter, and all communications to SharePoint occur by modifying this properties object. To prevent the reservation from being saved, the Cancel property of the properties object is set to true. Any information returned to the user is done through the ErrorMessage property. Due to the similarity of the functionality, both the ItemAdding and ItemUpdating methods delegate their functionality to the TimeConflict method. The only difference is that when an overlapping appointment is discovered, the currently updated appointment is filtered from the conflict list by comparing the existing ItemID with the ItemID from the properties object.

The TimeConflict method verifies the start date is less than the end date, and then calls the FindOverlaps method. FindOverlaps creates a SPQuery object, and sets up a DataTable for the conflicting appointments. I find DataTables a convenient way to pass multiple rows of data around, but I could have stopped when I reached the first conflict. FindOverlaps delegates the population of the SPQuery object to the populateQuery method. It then calls AddQueryResultsToTable, which retrieves the current list values based on the SPQuery object and adds them to the conflict list. If the conflict table contains any entries, TimeConflict calls formatErrorTable to format the ErrorMessage and sets the cancel property to true. populateQuery creates a Collaborative Application Markup Language (CAML) query as shown below.

<where>
   <daterangesoverlap>
      <fieldref name=""EventDate"">
        <fieldref name=""EndDate"">
          <fieldref name=""RecurrenceID"">
             <value type=""DateTime"">
               <week>
               </week>
              </value>
          </fieldref>
        </fieldref>
      </fieldref>
    </daterangesoverlap>
</where>

To include recurring events, the query “where” clause was restricted to DateRangesOverlap which takes a single date and a value of week or month. The results returned are then filtered to isolate the truly conflicting entries. If the start date and end date fall on the same day, the entries for the week are retrieved. If the start date/end date spanned days within the same month, then a month of entries are retrieved. If the span is larger, then multiple passes are made through the date range, with each query serving a single month. AddQueryResultsToTable (shown below) performs a query on the current list using the prepared query. It traverses the returned items to determine which items overlap the current event’s start date and end date. Overlapping entries are added to the conflict table.

aSPListItemCollection calendarItems = theList.GetItems(query);

// go through the list returned by the query and check for overlaps
foreach (SPListItem item in calendarItems)
{
   if (item.ID != ItemID) // dont check the current item
   {
      DateTime eventStart =
         (DateTime)item[item.Fields.GetFieldByInternalName("EventDate").Id];
      DateTime eventEnd = 
         (DateTime)item[item.Fields.GetFieldByInternalName("EndDate").Id];

      // does item overlap the start/end dates
      if (!((startDate >= eventEnd) || (endDate <= eventStart)))
      {
         // this item overlaps the start/end dates

         // already got enough overlaps
         if (overlapList.Rows.Count > MAX_DUPS)
         {
            return;
         }

         // adding and populating a row to the overlapList DataTable
         DataRow theRow = overlapList.NewRow();
         theRow["Title"] = item.Title;
         try
         {
            theRow["Start"] = eventStart;
            theRow["End"] = eventEnd;

            // extract user's display name
            string modBy = item[item.Fields.GetFieldByInternalName("Editor").Id].ToString();
            if (modBy.IndexOf("#") > 0)
            {
               modBy = modBy.Substring(modBy.IndexOf("#") + 1);
            }
            theRow["Modified By"] = modBy;
         }
         catch (Exception ex) // general exception handler
         {
            errorMsg += ex.Message;
         }
         overlapList.Rows.Add(theRow);
      } // if overlaps
   } // if not current item
} // foreach

formatErrorTable is a utility method that converts the conflict table to a string to be assigned to the ErrorMessage property.

// used to build the error message
System.Text.StringBuilder sBuilder = new System.Text.StringBuilder(); 
sBuilder.Append("The following reservation");
if (overlapList.Rows.Count == 1)
{
   sBuilder.Append(" conflicts");
}
else
{
   sBuilder.Append("s conflict");
}
sBuilder.Append("  with the desired reservation.
");

// go through the row collection and add the 
// contents to the error message
foreach (DataRow row in overlapList.Rows)
{
   sBuilder.Append("Title: " + row["Title"].ToString() + "\t from " +
   row["Start"].ToString() + " to " + row["End"].ToString() +
   " by " + row["Modified By"].ToString() + "
");
}

// converts the message to a string to return it.
return (sBuilder.ToString());

Points of interest

The most difficult aspect required recreating the recurring appointment behavior. When a recurring appointment is saved, a query must be performed on the series of appointments that would result. When a recurring appointment is selected, the properties contain recurrence data in the form of an Extensible Markup Language (XML) scrap in AfterProperties["RecurrenceData"]. The example below indicates that the appointment occurs daily for five days.

<recurrence>
   <rule>
      <firstdayofweek>su</firstDayOfWeek>
      <repeat>
         <daily dayFrequency=”1” />
      </repeat>
       <repeatInstances>5</repeatInstances>
   </rule>
</recurrence>

There are two aspects to the behavior: repeat frequency and stopping behavior. Repeat frequency includes: daily, weekly, monthly, and annually. Monthly and annual frequencies can be based on a fixed day or a relative day. An example of a fixed day would be the second of the month or October second of each year. A relative day may be the first Monday of the month or the first Monday in October of each year. The stopping behavior can include: a set number of occurrences, a cut-off date, or repeat forever. In the example above, the appointment repeats five times. I set an end point in the repeat forever case of five years from today. I thought that would be far enough in the future to resolve conflicts by hand. When a specific cut-off date is provided for a recurring appointment, SharePoint adjusts the appointment end date to match the cut-off date. When this occurs, the end date is adjusted to match the date portion of the start date. To simplify the interface, I created a Recurring class, and the constructor takes the start date, end date, and an XML fragment as parameters. It parses the XML, and creates handlers for the repeat frequency and stopping behavior in a classic Factory pattern, as shown below:

The constructor code is shown below:

XmlNode firstDayOfWeekNode = null;
XmlNode repeatNode = null;
XmlNode recurrNode = null;

// parses the recurringXML string into 3 XmlNodes
ParseXML(RecurringXML, ref firstDayOfWeekNode, ref repeatNode,
   ref recurrNode);

XmlNode typeRepeatNode = repeatNode.FirstChild;
string typeRepeat = typeRepeatNode.Name;

// create the repeating handler based on the typeRepeat node name
switch (typeRepeat)
{
   case "daily":
      oRecurPeriod = new DailyRecurPeriod(ref StartDate, ref EndDate,
         typeRepeatNode);
      break;
   case "weekly":
      oRecurPeriod = new WeeklyRecurPeriod(ref StartDate, ref EndDate,
         typeRepeatNode);
      break;
   case "monthly":
      oRecurPeriod = new MonthlyRecurPeriod(ref StartDate, ref EndDate,
         typeRepeatNode);
      break;
   case "monthlyByDay":
      oRecurPeriod = new MonthlyByDayRecurPeriod(ref StartDate, 
         ref EndDate, typeRepeatNode);
      break;
   case "yearly":
      oRecurPeriod = new YearlyRecurPeriod(ref StartDate, ref EndDate,
         typeRepeatNode);
      break;
   case "yearlyByDay":
      oRecurPeriod = new YearlyByDayRecurPeriod(ref StartDate, 
         ref EndDate, typeRepeatNode);
      break;
}

// create the completion handler based on the recurrNode.name
string recurName = recurrNode.Name;
switch (recurName)
{
   case "repeatInstances":
      oCompletion = new RecurCompletionCount(ref StartDate, 
         ref EndDate, recurrNode);
      break;
   case "repeatForever":
      oCompletion = new RecurForever(ref StartDate, ref EndDate,
         recurrNode);
      break;
   case "windowEnd":
      oCompletion = new RecurCutOff(ref StartDate, ref EndDate,
         recurrNode);
      break;
}

// If recurName == windowEnd, EndDate is updated with the cut-off date
// need to adjust the EndDate and store the appointment EndDate 
// separately from the cut-off date.
if (oCompletion.EndDateOverridden)
{
   oRecurPeriod.AdjustEndDate(oCompletion.GetUpdatedEndDate());
}

The user interface allows the creation of an appointment before the recurring rules would allow it to exist. For example, if the entered start date is 10/15/2008 and the pattern is the first Monday of the month, the first start date is adjusted to 11/3/2008. So, this behavior is mimicked by the frequency handler, where appropriate.

The recurring object delegates the calculation of the next date to the handlers in Recurring.GetNext(), as shown below:

bool returnValue = oRecurPeriod.GetNext(ref StartDate, ref EndDate);
if(returnValue)
{
   returnValue = oCompletion.ProcessedAll(StartDate, EndDate);
}
return (returnValue);

Summary

In conclusion, I used a simple approach to a common problem that was based on existing standards. Since standards were followed, the resulting tool can be extended with typical SharePoint tools. Training requirements were reduced to a message about how to handle collisions since the user interface was unchanged.

History

November 2008: Initial release.
December 2008: Catalin discovered two bugs in this project related to recurring appointments that I have incorporated and fixed in the downloads