CAML and DateTime issues and the solution.

Hi Friends,

During one of my projects, I had to work on SPQuery objects, SPList, SPListItemCollection, CAML and date time formats. Let me introduce me business case:

Business case:
Its for the team calendar. If a team member apply for vacations and public holiday falls during his/her vacations then system should deduct that number of public holidays.

Roadblocks:
1) there is no straight forward method for filtering date time functions.
2) SharePoint includes date and time parts. But my case just needs date and not time.

My Solution:
I created a function called as GetBankHolidays with 3 parameters:
1) Start Date for team member (date1)
2) End date for team member (i.e. End date - Start date = total number of days for his vacation) (date2)
3) SPItemEventProperties


4) Firstly, get date range for SPQuery.
It can easily defined like this:

Start date of team member
End date of team member

>> query.Query = "<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>" + date1 + " </Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>" + date2 + "</Value></Leq></And></Where>";


5) Secondly, we need to use SharePoint function. But we need to include one more namespace: using Microsoft.SharePoint.Utilities;
SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1) and
SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2).

>> query.Query = String.Format("<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{1}</Value></Leq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1), SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2));

6) Thirdly, we also need to ignore the time part. This can be done by "IncludeTimeValue" attribute.

>> query.Query = String.Format("<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{1}</Value></Leq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1), SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2));

The final function be obtained by GetBankHolidays.

---

/// <summary>

/// Computes no. of bank holidays during user's vacation and return an INT ( = number of bank holidays)

/// </summary>

/// <param name="date1"></param>

/// <param name="date2"></param>

/// <returns></returns>

public static int GetBankHolidays(DateTime date1, DateTime date2, SPItemEventProperties properties)

{
using (SPWeb web = properties.OpenWeb())
{
// counter for bank holidays

int count = 0;


SPListItemCollection items = null;
SPList holidaysList = web.Lists["Bank Holidays"];



SPQuery query = new SPQuery();



query.Query = String.Format("<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{1}</Value></Leq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1), SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2));




while (date1.Date <= date2.Date)

{
items = holidaysList.GetItems(query);



foreach (SPListItem item in items)
{
if (Convert.ToDateTime(date1.Date) == Convert.ToDateTime(item["Day"]))
count++;
}
date1 = date1.AddDays(1);

}

return count;

}



Happy programming!

Cheers,
Aroh

How to: Creating a SharePoint 2007 feature

In this tutorial,I will show, how to develop a feature for SharePoint 2007 from the scratch. In our case, I will use item event handler and calendar list. What I will be showing is that SharePoint users can add, modify their entries but if they delete an calendar item then they could not delete.

Prerequisite:

1) Download WSPBUILDER, get it here
2) Download SharePoint Utility, get it here

Steps:

1) Create the class library.

>> Open Visual Studio 2008 >> create new class library



Project Name: CalendarEventHandler and click OK.

>> Add Reference to SharePoint services assembly.



>> Locate Sharepoint.dll and click OK.



>> Rename Class1.cs to CalendarEventHandler.cs




2) Writing the event handler code

>> Code the event handler.



We be using WSS API and in CalendarAction.cs file, we inherit from SPItemEventReceiver.

Now, we are creating ItemDeleting method and override the our own custom code. In fact, we are creating a very simple event handler but we can really enhance the event handler and manipulate one list with update another lists. I would be writing a blog about more enhance ItemEventReceiver in few days time.

The ItemDeleting method can be called and the properties parameter contains detailed information about fired that event.

>> When in user deletes an item, then ItemDeleting event is fired asynchroneously i.e. before an item is deleted.


3) Sign the assembly

>> Once when done with the coding, we need to compile and sign the assembly.
>> Follow these steps:
>> Solutions Explorer >> Properties >> Signing >> New.



>> Create a new strong name.



>> Now, we use WSPBuilder to "Copy to GAC".
Right click on Solutions Explorer >> WSPBuilder >> Copy to GAC.




>> We double check if the assembly is successfully installed.

C:\WINDOWS\assembly





4) Creating the resource files

>> SharePoint feature besides compiled dll, requires some other files on the server too. The folder structure (called as "12" hive) and just add into this folder structure.

>> Right click on Solutions Explorer >> Add this folder structure.



>> There 2 xml files in the folder structure.
>> feature.xml and code is as follows:




Create GUID can be obtained from Tools >> Create GUID





>> elements.xml

This file will attach the event handler (CalendarAction.cs code) to a particular list ("Calendar List")

This is the code:



Some important points to be noted:

i) ListTemplateId="106". 106 corresponds to "calendar event". Complete list ListTemplateId could be found here.

ii) In our case we have added just one Receiver i.e. ItemDeleting. But in real buisness solutions we may need more than one receiver like ItemAdding, ItemAdded ItemUpdating, ItemUpdating etc for e.g.




iii) There is one more SharePoint Utility package. Its for ItemDeleting code with corresponding lists. In our case its Calendar list.

5) Building wsp and deploying to server

Once xml files have been done, we need to building wsp (What is WSP?? A solution is a deployable, reusable package that can contain a set of Features,web parts site definitions, and assemblies that apply to sites, and that you can enable or disable individually.It allows the SharePoint administrators to easily deploy the customizations, even scheduled deployments and multi front-end-webserver deployments are supported.)

>> Right click on Solutions Explorer >> WSPBuilder >> Build WSP



>> When WSP has been done, we just need to deploy to server
>> Right click on Solutions Explorer >> WSPBuilder >> Deploy



>> Now, check in Central Administration if WSP has been successfully installed.



>> We go to SharePoint site, and activate the feature.






6) Testing the feature.

>> we can add a new Calendar item and try to delete the same to test our new feature.






CONGRATS!! we have developed a simple Calendar event handler successfully.

These are some other tips for event handlers.

>> If you want to add more one more item event handler like ItemAdding, ItemAdded, ItemUpdating, ItemUpdated to existing Calendar event handler (like we did on this walk through), then we have to follow steps steps:

i) Uninstall CalendarEventHandler.dll (c:\WINDOWS\assembly)
ii) In Central Administration, Retract the CalendarEventHandler.wsp and Delete the solution.
iii) In CalanderAction.cs file, code new event handlers (new methods) like ItemAdding, ItemAdded, ItemUpdating etc.

iv) compile it and copy to GAC using WSPBuilder.
v) Build WSP and deploy to server.

THATS IT!

Happy programming!