Setting up multiple calendars for meeting room bookings & prevent double booking

31. March 2009 16:57 by Eric in calendar, Event Handlers  //  Tags: ,   //   Comments (3)

A common usage for Sharepoint is meeting room bookings.

This article will provide one way of elegantly handling this scenario with some simple configuration but then we'll take it a step further by getting under the hood of WSS and adding some validation so that a room can not be double booked.

 

Add a calendar

Firstly we need to add the calendar list we are going to use.

On the parent page of where the calendar will reside use the site actions menu to create a new Calendar.

 

Give the calendar a descriptive name such as Book Meeting Rooms.

 

Add a new column

From the calendar settings choose "Create column" under the Columns options.

Name this column "Meeting Room" of type "Choice" and add some entries for each meeting room name.

 

This column will be used as a filter so that users can select a specific meeting room view.

 

Create Views

From the settings menu on the calendar choose create view.

 

Click Calendar View.

Name your view after one of the meeting rooms and add filtering criteria as below.

 

Repeat these steps until you have created a view for all Meeting Rooms.

Now the user can select which room they would like to see the availability for by selecting from the view drop down list in the top right of the calendar page.

 

One last touch - Displaying the room name in Month View.

When you are in month view by default the Title of the entry is visible.

  

The calendar month view only displays the title column by default.

In week view and day view you can select a second column to be displayed but in month view for some strange reason this is not possible.

The trick is to create a new calculated column that is a combination of the fields you would like to see.

In this example the Meeting room name followed by the title in a column I have called Summary.

 Then go into the calendar view and select Summary as the Month View Title.

 Now the month view displays the meeting room as well as the title

  

 Stay tuned for part 2 where we get technical and look at event handlers in Sharepoint!!

 

How to Prevent Duplicate Bookings in a Sharepoint Calendar List

We recently had a customer request a feature addition to the Calendar List.  The feature they wanted to add would prevent the user from being able to book a meeting room for a time in which the meeting room was already used.  For more information on adding a Meeting Room column, see the previous article.  For example, we have made a booking for the Earth Room on 25/9/08 at 4:00pm:

 

 

Attempting to create a new item on 25/9/08 from 4:30pm to 5:00pm can be done:

 

 This 'How To' will show you how to prevent this from happening.


Create an Event Listener
1.     Open Visual Studio and create a new class library project.
2.     Add a reference to Microsoft.Sharepoint.dll (the dll can be found in %Program Files%\Common Files\Microsoft Shared\web server extensions\12\ISAPI on the the server running WSS).
3.     Add a new code file and name it PreventDoubleBooking.cs.

 

 4.     In PreventDoubleBooking.cs, add the following code:

using System;
using Microsoft.SharePoint;


namespace Webcoda.WSS.Calendar.Events
{
    class PreventDoubleBooking: SPItemEventReceiver
    {
        /// <summary>
        /// This event is triggered when the user adds a new item
        /// </summary>
        /// <param name="properties"></param>
        public override void ItemAdding(SPItemEventProperties properties)
        {
            //Our query string variable
            string strQuery = null;

            try
            {
                //Get the Sharepoint site instance
                using (SPWeb oWebsite = new SPSite(properties.SiteId).OpenWeb(properties.RelativeWebUrl))
                {
                    
                    //Get the collection of properties for the Booking item
                    SPListItemCollection collItems = oWebsite.Lists[properties.ListTitle].Items;

                    //Get the Calendar List that we will be querying against
                    SPList calendar = oWebsite.Lists[properties.ListId];

                    //Get the internal name of the fields we are querying. 
                    //These are required for the CAML query
                    string start_internal = collItems.List.Fields["Start Time"].InternalName;
                    string end_internal = collItems.List.Fields["End Time"].InternalName;
                    string MeetingRoom_Internal = collItems.List.Fields["Meeting Room"].InternalName;

                    //Get the query string parameters
                    string start_str = properties.AfterProperties[start_internal].ToString();
                    string end_str = properties.AfterProperties[end_internal].ToString();
                    string MeetingRoom_str = properties.AfterProperties[MeetingRoom_Internal].ToString();

                    //Construct a CAML query
                    SPQuery query = new SPQuery();

                    //Create the CAML query string that checks to see if the booking we are attemping
                    //to add will overlap any existing bookings
                    strQuery = string.Format(@"

    <Where>
    
        <And>
            <Or>
            
                <Or>
                    <And>
                       <Leq>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Leq>

                       <Gt>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Gt>
                    </And>

                    <And>
                       <Lt>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Lt>

                       <Geq>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Geq>
                    </And>
                </Or>
                
                <Or>
                    <And>
                       <Leq>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Leq>

                       <Geq>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Geq>
                    </And>

                    <And>
                       <Geq>
                          <FieldRef Name='EventDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                       </Geq>

                       <Leq>
                          <FieldRef Name='EndDate' />
                          <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                       </Leq>
                    </And>
                </Or>
                
            </Or>
        
            <Eq>
                <FieldRef Name='Meeting_x0020_Room' />
                <Value Type='Choice'>{2}</Value>
            </Eq>
            
        </And>
        
    </Where>
    <OrderBy>
        <FieldRef Name='EventDate' />
    </OrderBy>
", start_str, end_str, MeetingRoom_str);

                    //Set the query string for the SPQuery object
                    query.Query = strQuery;

                    //Execute the query against the Calendar List
                    SPListItemCollection existing_events = calendar.GetItems(query);
                    
                    //Check to see if the query returned any overlapping bookings
                    if (existing_events.Count > 0)
                    {
                        //Cancels the ItemAdd action and redirects to error page
                        properties.Cancel = true;

                        //Edit the error message that will display on the error page
                        properties.ErrorMessage += "This booking cannot be made because of one or more bookings in conflict. <BR><BR>";

                        //Here you can loop through the results of the query
                        //foreach (SPListItem oListItem in existing_events)
                        //{
                        //   ....
                        //}

                        properties.ErrorMessage += "Please go back and schedule a new time.";
                    }
                    
                }
            }
            catch (Exception ex)
            {
                //Cancels the ItemAdd action and redirects to error page
                properties.Cancel = true;

                //Edit the error message that will display on the error page
                properties.ErrorMessage = "Error looking for booking conflicts: " + ex.Message;
            }
          
        }

        /// <summary>
        /// This event is triggered when the user edits an calendar item
        /// </summary>
        /// <param name="properties"></param>
        public override void ItemUpdating(SPItemEventProperties properties) {

            string strQuery = null;

            try {

                //Get the Sharepoint site instance
                using (SPWeb oWebsite = new SPSite(properties.SiteId).OpenWeb(properties.RelativeWebUrl)) {

                    //Get the collection of properties for the Booking item
                    SPListItemCollection collItems = oWebsite.Lists[properties.ListTitle].Items;

                    //Get the Calendar List that we will be querying against
                    SPList calendar = oWebsite.Lists[properties.ListId];

                    //Get the internal name of the fields we are querying. 
                    //These are required for the CAML query
                    string start_internal = collItems.List.Fields["Start Time"].InternalName;
                    string end_internal = collItems.List.Fields["End Time"].InternalName;
                    string MeetingRoom_Internal = collItems.List.Fields["Meeting Room"].InternalName;
                    string guid_internal = collItems.List.Fields["GUID"].InternalName;

                    //Get the query string parameters
                    string start_str = properties.AfterProperties[start_internal].ToString();
                    string end_str = properties.AfterProperties[end_internal].ToString();
                    string MeetingRoom_str = properties.AfterProperties[MeetingRoom_Internal].ToString();
                    string guid_str = properties.AfterProperties[guid_internal].ToString();

                    //Construct a CAML query
                    SPQuery query = new SPQuery();

                    //Create the CAML query string that checks to see if the booking we are attemping
                    //to change will overlap any existing bookings, OTHER THAN ITSELF
                    strQuery = string.Format(@"

    <Where>
        <And>
        
            <And>
                <Or>
                
                    <Or>
                        <And>
                           <Leq>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Leq>

                           <Gt>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Gt>
                        </And>

                        <And>
                           <Lt>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Lt>

                           <Geq>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Geq>
                        </And>
                    </Or>
                    
                    <Or>
                        <And>
                           <Leq>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Leq>

                           <Geq>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Geq>
                        </And>

                        <And>
                           <Geq>
                              <FieldRef Name='EventDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{0}</Value>
                           </Geq>

                           <Leq>
                              <FieldRef Name='EndDate' />
                              <Value Type='DateTime' IncludeTimeValue='TRUE'>{1}</Value>
                           </Leq>
                        </And>
                    </Or>
                    
                </Or>
            
                <Eq>
                    <FieldRef Name='Meeting_x0020_Room' />
                    <Value Type='Choice'>{2}</Value>
                </Eq>
                
            </And>
        
            <Neq>
                <FieldRef Name='GUID' />
                <Value Type='GUID'>{3}</Value>
            </Neq>
        
        </And>
        
    </Where>
    
    <OrderBy>
        <FieldRef Name='EventDate' />
    </OrderBy>
", start_str, end_str, MeetingRoom_str, guid_str);

                    //Set the query string for the SPQuery object
                    query.Query = strQuery;

                    //Execute the query against the Calendar List
                    SPListItemCollection existing_events = calendar.GetItems(query);

                    //Check to see if the query returned any overlapping bookings
                    if (existing_events.Count > 0) {

                        //Cancels the ItemAdd action and redirects to error page
                        properties.Cancel = true;

                        //Edit the error message that will display on the error page
                        properties.ErrorMessage += "This booking cannot be made because of one or more bookings in conflict. <BR><BR>";

                        //Here you can loop through the results of the query
                        //foreach (SPListItem oListItem in existing_events)
                        //{
                        //   ....
                        //}

                        properties.ErrorMessage += "Please go back and schedule a new time.";
                    }

                }
            } catch (Exception ex) {

                //Cancels the ItemAdd action and redirects to error page
                properties.Cancel = true;

                //Edit the error message that will display on the error page
                properties.ErrorMessage = "Error looking for booking conflicts: " + ex.Message;
            }

        }

        
    }
}

Register Assembly into GAC

We want the WSS server to call our event code instead of the built-in code that allows us to make a double booking.  In order for our new events to be available to the WSS server, we need to register our assembly into the GAC.  For this to happen, the assembly being registered must be strongly typed and signed. 

1.    To do this, follow the instructions here:  http://www.dotnetspider.com/resources/1620-Strong-Naming-And-Installing-Assembly-into-GAC.aspx.  Once you have added the Key File into the project, your solution should look similar to this:

 

 2.    When you get to the “Installing Assembly into the GAC” section, instead of running the gacutil.exe command line utility, you can simply drag your compiled project assembly into the GAC:

 

Register Events

Now that the assembly is registered into the GAC, we still need to register the events in the WSS Server.  To do this, we can use a tool called EventHandlerExplorer.  Download, source code, and demo instructions can be found here:  http://www.u2u.info/Blogs/Patrick/Lists/Posts/Post.aspx?ID=1547.  

1.    Once you’ve downloaded the tool, run the EventHandlerExplorer.exe on the WSS server.  In the “Enter URL for the site collection you want to explore” field, type the URL of your Sharepoint site, then click “Explore”.  If you get an “Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))” error, then you are probably not running the EventHandlerExplorer app in the context of a Site Collection Administrator account. On the WSS Server, go to Central Administration -> Application Management -> Site Collection Administrators and add yourself as the Secondary site collection administrator. Be sure that you add yourself to the correct site collection.  Alternatively, run the EventHandlerExplorer as the person who is listed as a Site Collection Administrator (right-click on EventHandlerExplorer.exe -> Run as...).

 

 

If you get through without an “Access is dened…” error, you should be able to expand your site, then subsite until you get to the List that contains your Calendar.  In our case, we have a subsite called “Sandbox” and the List containing the Calendar is called “Book Meeting Room”.  So, we would expand the Sandbox tree, then expand the Lists tree node to expose the “Items” node and “Event Handlers” node.

 

To see which events are registered for that list, you must double-click the “Event Handlers” node.  At this point, however, you probably won’t have any events registered and therefore double-clicking will do nothing.

With the Book Meeting Room node highlighted, click the Load Assembly button, and then select the .dll we’ve created.  Then, in the Class drop down, select PreventDoubleBooking.  For sequence, type “0”.  I think this only comes into play if you want to have multiple delegates for the same event, but I’m not 100% sure.  And finally, for Event Type, select ItemAdding.  The values you selected should look like this:

 

Then click the “Add Handler” button.  Once that event has been registered, change the Event Type to ItemUpdating, then click the “Add Handler” button again.  Even though you just registered two events, the Event Handlers node doesn’t show a “plus” sign next to it, indicating it has child nodes.  This is because you must first double-click the Event Handlers node which will then show the “plus” sign.  Expand this node to see the two Events you just registered.

 

Testing Our Code

That’s it!  You’re finished!  Now let’s test our changes.  To test our code, we’re going to book a meeting room for an hour at 4pm on 26/9 and then try to book the same meeting room at 4:30pm.

 

 But look what happens when we try to add the booking at 4:30pm for the same meeting room on the same day.

 

An error is generated and the booking is not made!

 

Download 

You can download the entire project here:  Webcoda_PreventDuplicateBookings.zip (5.41 mb)

 

More Information

Any changes to the code you make will require you to recompile the assembly, reinstall your assembly into the GAC, and finally reregister your events.  If you would like to change the query used in the sample code, a tool that may help is U2U Caml Query Builder which can be found here:  http://www.u2u.info/Blogs/karine/Lists/Posts/Post.aspx?ID=12.  To get familiar with the Microsoft.SharePoint namespace, here is a link to the documentation you will need:  http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.aspx.

Also, here is a link to “Getting Started with Programmatically Customizing a SharePoint Web Site in Visual Studio”:  http://msdn.microsoft.com/en-us/library/ms479423.aspx.

Webcoda, SharePoint Consultants & Web Development

SharePoint Development Sydney is a crack team of SharePoint Consultants and SharePoint Developers.

We can't tell you their names or show their faces on TV but if you need a SharePoint job done right, call them on +61 2 9370 3602 or email us at info@sharepointsydney.com.au

Persecuted by the Government and shunned by society they developed their SharePoint skills in back streets and labor camps where other programmers wouldn't dare to tread. 

During a trek through the Himalayas they stumpled upon the fabled Mossy Yak who shared his SharePoint knowledge of how to attain Nirvana through a series of Workflows and Event Handlers. Their mission is to spread this knowledge through-out the world to bring peace, harmony and document version control to all .

 

Month List