Click here to Skip to main content
15,867,594 members
Articles / Programming Languages / XML

SharePoint Reservations

Rate me:
Please Sign up or sign in to vote.
4.93/5 (15 votes)
29 Mar 2009CPOL8 min read 419.7K   4K   47   173
A SharePoint calendar extension that prohibits overlapping appointments

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 Microsoft 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:

featureactivate.JPG

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

createList.JPG

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.

Collision.JPG

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)
  • Check Out (ItemCheckingOut and ItemUncheckedOut)

Unpaired events include:

  • ContextEvent
  • ItemFileConverted
  • ItemFileMoved
  • 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:

XML
<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.

C#
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) // don't 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.

C#
// 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.

XML
<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:

Recurring.gif

The constructor code is shown below:

C#
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:

C#
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
  • March 2009
    • Corrected the issue with editing the page in Sharepoint Designer 
    • Added a filter for invalid recurring XML
    • Due to popular demand, added the ability to add a column to allow multiple Resources to be tracked in the same list. After creating the list, go into Settings -> List Settings and Create Column. The column name must be "Resource" unless you change the code (RESOURCE_COLUMN const defines the column name). The new column is defined as "lookup another list on this site". After selecting the list (a custom list of resources), select the display name or title for "In this column". For some reason, after adding the resource to the CAML query, the query ignored the resource specified with <And> and <Eq> and returned all rows for the date range. As a result, the returned list is filtered manually. I would love to know why if anyone has any ideas.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
SuggestionSeat booking using infopath form Pin
Member 26463797-Nov-17 1:56
Member 26463797-Nov-17 1:56 
QuestionConnected to Outlook, daylight saving is not accounted for in making the collision calculation Pin
Member 1160587314-Apr-15 0:01
Member 1160587314-Apr-15 0:01 
QuestionMy friend, you are a genius! Pin
Jason Dicker17-Jan-14 2:54
Jason Dicker17-Jan-14 2:54 
QuestionUrgent: Application stopped working. Pin
Linley Meslier16-Sep-12 20:21
Linley Meslier16-Sep-12 20:21 
Questionplay ddtank!! Pin
ngeeeee4426-Jul-12 5:05
ngeeeee4426-Jul-12 5:05 
QuestionPrevent duplicate booking in year 2012 Pin
jgohbh27-Apr-12 4:18
jgohbh27-Apr-12 4:18 
QuestionNeed solution Pin
jmpaul01216-Apr-12 3:24
jmpaul01216-Apr-12 3:24 
AnswerRe: Need solution Pin
ngeeeee4417-Apr-12 18:48
ngeeeee4417-Apr-12 18:48 
QuestionLooking for solution for SharePoint 2010 Pin
your_heiness9-Apr-12 8:02
your_heiness9-Apr-12 8:02 
AnswerRe: Looking for solution for SharePoint 2010 Pin
Melli11118-Dec-12 9:40
Melli11118-Dec-12 9:40 
QuestionResource column not working:Solution Pin
yadnesh pawar8-Apr-12 22:54
yadnesh pawar8-Apr-12 22:54 
QuestionWss 3.0 in 2012 Pin
deflo126728-Mar-12 2:56
deflo126728-Mar-12 2:56 
QuestionSharePoint 2010 Pin
Pritesh J Patel17-Mar-12 5:22
Pritesh J Patel17-Mar-12 5:22 
Hi All,
Has anyone managed to get this working in SharePoint 2010. When I install it, it deploys fine but when I go to add a Reservation calendar, it comes up with an error: An unexpected error has occurred.

Has anyone been able to do this sucessfully? Also, has anyone got this project in Visual Studio 2010, i can't open the original project anywhere!

Thanks
GeneralRe: SharePoint 2010 [Confirming problem] Pin
Lassie7017-Mar-12 22:46
Lassie7017-Mar-12 22:46 
GeneralRe: SharePoint 2010 [Confirming problem] Pin
ralphdalton22-May-12 3:35
ralphdalton22-May-12 3:35 
QuestionOMG nobody can help on this???? Pin
ngeeeee4415-Mar-12 20:41
ngeeeee4415-Mar-12 20:41 
AnswerRe: OMG nobody can help on this???? Pin
Melli11116-Mar-12 7:40
Melli11116-Mar-12 7:40 
GeneralRe: OMG nobody can help on this???? Pin
deflo126728-Mar-12 2:54
deflo126728-Mar-12 2:54 
GeneralRe: OMG nobody can help on this???? Pin
Melli11128-Mar-12 3:04
Melli11128-Mar-12 3:04 
GeneralRe: OMG nobody can help on this???? Pin
deflo126728-Mar-12 3:43
deflo126728-Mar-12 3:43 
GeneralRe: OMG nobody can help on this???? Pin
Member 777605113-May-12 7:58
Member 777605113-May-12 7:58 
Questionneed help rush!!!!!!! Pin
ngeeeee4422-Feb-12 1:12
ngeeeee4422-Feb-12 1:12 
AnswerRe: need help rush!!!!!!! Pin
Member 775610128-Feb-12 17:07
Member 775610128-Feb-12 17:07 
BugBug with daily recursive reservations Pin
Dany Hoyek30-Jan-12 23:39
Dany Hoyek30-Jan-12 23:39 
BugBug with daily recursive events Pin
Dany Hoyek30-Jan-12 23:36
Dany Hoyek30-Jan-12 23:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.