Sharepoint Calendar reminder service

I have been tinkering with SharePoint for quite some time now. but only as a secondary system/tool to do some out of the box functionality which was just cheaper to do, rather then writing our own bit of functionality. So I am no Sharepoint expert, by any stretch of the imagination.

After finding the calendar list, I thought wow. I'm going to set this up at home and have all my event emailed to me so I don't forget. Was this too much to ask?? Apparently so. Shaerpoint has a facility to alert me when a new event is entered, so I know that it knows how to email, but why does it not email alert reminders when the event actually happens? This to me, seems like logical out of the box functionality. So why is it not there?

I had a look on google for a solution, but found the only option out there was for paid add-in. As with all other sharepoint add-in's, none of them are free. I wonder why sharepoint doesn't have a good open source community? could it be that sharepoint is such a pain to work with, that after you've finished you're little piece of mastery, you feel you need to be compensated?

My first thought was to create a windows service to poll sharepoint list and do the emailing from there. But I am a sucker for punishment and took on the behemoth which is SharePoint.

First I needed to know which event to hook into. how am I supposed to get a notification to happen, if sharepoint itself doesn't do it? Well is does, and i found a good blog from Andrew Connell on how to wire it all up. It actually worked with no mess, no changes or debugging. So all that was left was to wire up the functionality and plonk it into the execute method and bask in the working functionality. HA.

---

Now I have a plan of attack. Sharepoint (SPJobDefinition) will act as the timer to wake up and execute my function. I just left the installer part from the above blog, so it will wake up every 5 minutes. Next was to define the configuration data source. I immediately thought app.config, not realising that this function will execute in the sharepoint domain, and will not necessarily read my config file associated with the dll. If you read my previous post, you'll see I have an excellent mechanism to bind a class to a section in the config file. Unfortunately this was not an option. I looked at an MSDN article on configuration options and found it really needs to be stored in a custom XML file, and this is the format I chose.

<Settings>
    <SMTPServer>[SMTPServer address]</SMTPServer>
    <EventLogName>Sharepoint.Notifier</EventLogName>
    <FromAdderss>[mailfrom address]</FromAdderss>
    <Notification>
        <ListSetting>
            <Id>BirthdayList</Id>
            <enabled>true</enabled>
            <StripHTML>true</StripHTML>
            <siteName>HOME</siteName>
            <ListName>Birthday Calendar</ListName>
            <EventDate>EventDate</EventDate>
            <SummaryDay>Sunday</SummaryDay>
            <SummaryTime>9:00</SummaryTime>
            <Emails>EmailTo</Emails>
            <DistributionListField>DistributionEmailListName</DistributionListField>
            <DistributionListEmailField>Email</DistributionListEmailField>
            <SubjectField>Title</SubjectField>
            <BodyField>Description</BodyField>
        </ListSetting>
    </Notification>
</Settings>

I have a settings section, which act like the appsettings and then a list of notification, which is basically the configuration for which list in sharepoint it will check for events. With this configuration the service will

1) Check the Birthday Calendar list,
2) Check the EventDate field for the date and time the event needs to fire.
3) it will email to the string content in EmailTo field of this list.
4) if there is a distribution list field and email field specified it will check the named distribution list and concatenate the Email field from this list into a semi-colon list of email addresses. This was done so I could easily change the email list without changing every single event.
5) The email sent will have the Title field as the subject and the Description field as the email body.
6) On Sunday at 9AM it will send a report of what is happening for the week.

I found a few good resources to serialising and deserializing to and from a Class. I also found it really useful to start from the class, create an instance with all the properties needed and serialise the object to xml to see what format you need.
My class look like this;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Serialization;
using System.Collections;
namespace Sharepoint.Notifier.XMLConfig
{
    [XmlRoot("Settings")]
    [Serializable]
    public class Settings
    {
        [XmlElement("SMTPServer")]
        public string SMTPServer { get; set; }
        [XmlElement("EventLogName")]
        public string EventLogName { get; set; }
        [XmlElement("FromAdderss")]
        public string FromAdderss { get; set; }
        private List<ListSetting> _Lists;
        [XmlArray("Notification")]
        public List<ListSetting> Lists
        {
            get
            {
                if (_Lists == null)
                {
                    _Lists = new List<ListSetting>();
                }
                return _Lists;
            }
            set
            {
                _Lists = value;
            }
        }
    }
}

This is the setting class which contains the values from the xml config file. notice it contains the 3 elements from the appsettings like section, and then an XMLArray called notification. which is just a list of ListSettings

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Serialization;
namespace Sharepoint.Notifier.XMLConfig
{
    public class ListSetting
    {
        public string ID { get; set; }
       public bool enabled { get; set; }
        public bool StripHTML { get; set; }
        public string siteName { get; set; }
        public string ListName { get; set; }
        public string EventDate { get; set; }
        public string SummaryDay { get; set; }
        public string SummaryTime { get; set; }
        public string LastSentField { get; set; }
        public string Emails { get; set; }
        public string DistributionListField { get; set; }
        public string DistributionListEmailField { get; set; }
       public string SubjectField { get; set; }
        public string BodyField { get; set; }
        public bool HasDistributionList
        {
            get
            {
                return !string.IsNullOrEmpty(DistributionListField) &&
                    !string.IsNullOrEmpty(DistributionListEmailField);
            }
        }
        public bool IsWeeklyReportDay
        {
            get
            {
                if (DateTime.Now.ToString("dddd").ToLower() == SummaryDay.ToLower())
                {
                    TimeSpan result = new TimeSpan();
                    if (TimeSpan.TryParse(SummaryTime, out result))
                    {
                        if (result <= DateTime.Now.TimeOfDay)
                        {
                            return true;
                        }
                    }
                }
                return false;
            }
        }
    }
}

List settings just has the property name (which matches the xml element name in the config file) and some helper properties which are used later to determine if the config has a distribution list or if its time for the weekly report.

Now we need to bind the xml document to the class to be able to use it and I just created a property which uses a singleton like approach to load the document if it hasn't already been loaded.

private Settings SettingsSection
        {
            get
            {
                if (_SettingsSection == null)
                {
                    _SettingsSection = new Settings();
                    XmlSerializer serializer = new XmlSerializer(typeof(Settings));
                    string fileName = this.GetType().Assembly.Location.Replace("Sharepoint.Notifier.dll", "Configuration.xml");
                    FileStream file = new FileStream(fileName, FileMode.Open);
                    _SettingsSection = serializer.Deserialize(file) as Settings;
                    file.Close();
                }
               return _SettingsSection;
            }
        }

Since this assembly is going to be in the gac and the config file will be sitting right next to it, I create the file name by using reflection to obtain the file location and replace the assemblies file name with the config filename.xml.
I chose to use the singleton approach because this config file will rearly change, and it just reduces the overhead of loading the file every 5 minutes. It does mean you need to reset IIS to reload the config file if you change it. I tried to attach a file watch on the config to force a reload when it was changed, but because the assembly is in the gac, it could not get access. It crapped out when I tried to create a new FileSystemWatcher object pointing to the xml file in the Gac

Configuration setup

---

Now we have the config file set up, we need to set up another mechanism to track which events have already been issued. The event will fire every 5 minutes so it may grab the same event more then once and send the email multiple times. I original started with a property in the config file which pointed to a field in the sharepoint list to keep the last sent date, which would be checked before sending the next email. But this meant you needed to add a new field to the list and it would appear in sharepoint. So next approach was to create a dictionary list in memory which kept track of events where already sent today. But had the limitation of each time IIS was reset the object would be recreated, and the data lost. so the final conclusion was to re-use my new knowledge of xml serialising/deserialise and create another tracking xml file, which sits behind the scenes, so even if IIS was reset, it would just re-read the xml file into memory and continue from there.

So my Last sent class looks like this;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Serialization;
namespace Sharepoint.Notifier.XMLConfig
{
    [XmlRoot("LastSent")]
    [Serializable]
    public class LastSent
    {
        [XmlElement("LastWeeklySend")]
        public DateTime? LastWeeklySend { get; set; }
        private List<ListItem> _LastSent;
        [XmlArray("LastItemSent")]
        public List<ListItem> PreviousSent
        {
            get
            {
                if (_LastSent == null)
                {
                    _LastSent = new List<ListItem>();
                }
                return _LastSent;
            }
        }
        public ListItem this[Guid guid]
        {
            get
            {
                return PreviousSent.Where(p => p.Guid.ToString() == guid.ToString()).FirstOrDefault();
            }
        }
    }
}

LastWeeklySend keeps track of the weekly email report send date, PreviousSent is a list of ListItems which keeps track of the event and when it was last sent, tracked via the Guid sharepoint assignes each event when created.
The default property makes it easy to find the ListItem by guid Id so that the implementation is a little easier. Notice the lamda expression. Lamda expression rock, they make things so much easier.

ListItem looks something like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Sharepoint.Notifier.XMLConfig
{
    public class ListItem
    {
        public ListItem()
        {
        }
        public ListItem(Guid guid, DateTime lastSent)
        {
            Guid = guid.ToString();
            LastSentDate = lastSent;
        }
        public string Guid { get; set; }
        public DateTime LastSentDate { get; set; }
    }
}

Very simple, just keeps track of the guid and last sent date, and the output (not that it really matters as the assembly managers this by itself) looks like this

<?xml version="1.0"?>
<LastSent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <LastWeeklySend xsi:nil="true" />
  <LastItemSent>
    <ListItem>
      <Guid>ae9956c0-36d1-4f1a-8633-68d1902add7c</Guid>
      <LastSentDate>2008-08-28T13:05:42.5056636+10:00</LastSentDate>
    </ListItem>
  </LastItemSent>
</LastSent>

Now for the implementation of the actual functionality
first we call the main method which will go through the configuration file and run the process for each shrepoint list

 public void SendAllTaskNotifications(SPContentDatabase dbContent,Settings settings)
        {
            try
            {
                _dbContent = dbContent;
                _settings = settings;
       
                foreach (ListSetting set in settings.Lists)
                {
                    if (set.enabled)
                    {
                        if (set.IsWeeklyReportDay && (LastSendData.LastWeeklySend.HasValue == false  LastSendData.LastWeeklySend.Value.Date != DateTime.Now.Date))
                        {
                            CheckWeeklyItem(set);
                        }
                        CheckList(set);
                    }
                    SaveXMLSent();
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.ErrorHandle.WriteException(ex);
            }

        }

This composes of a loop to
1) go through each setting then and apply functionality
2) send weekly report if conditions is met
3) send daily list item emails
4) save the Last sent config file so that the system remembers what was sent out.

For the daily alerts I just created a query to query the list. I used U2U which I found was a great tool to extract the CAML query easily, although the software was a bit buggy.

<Where>
  <And>
    <Geq>
      <FieldRef Name='%QFIELD%' />
      <Value Type='DateTime' IncludeTimeValue='TRUE'>#STARTDATE#</Value>
    </Geq>
    <Leq>
      <FieldRef Name='%QFIELD%' />
      <Value Type='DateTime' IncludeTimeValue='TRUE'>#ENDDATE#</Value>
    </Leq>
  </And>
</Where>

This was used for both the daily and weekly report and just substituted the different start and end dates. Then it was just a matter of getting the item from sharepoint, sending the email (if you check my previous post I started building a component library. One of the components which was a email helper) and then update the last sent date.

---

You would think that installation would be fairly straight forward, wouldn't you. well you would be wrong. To install this component I had to move all the code into 1 assembly to make it easy to deploy, as the assembly was finding it hard to find the referenced component library which had my email class in it, and I didn't try very hard to rectify the situation. I also found the gac was very unforgiving. Make a mistake (ie bug) and then you need to uninstall the component, change the version number, re-build and re-deploy. I'm an asp.net developer, so all this extra leg work makes me very cranky. I also found a really useful technique to open the gac in normal folder view, as I needed to copy the configuration file next to the assembly. if you open windows explorer and type in C:\WINDOWS\assembly\ you see the gac in some kind of funny view which only allows you to install and uninstall assemblies, however if you type c:\WINDOWS\assembly\gac in the run window, you can see the actual physical folder view of all the assemblies. However when I installed this component, it was not there, even though it was in the funny gac view, and I could see it in the default view. After some proding and poking, I found there was acually another physical gac loction c:\WINDOWS\assembly\gac_msil could this be the new location for 2.0 assemblies? not sure yet.


To deploy the feature I needed to create a feature.xml file, put it in the sharepoint features directory (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES\[my feature name]\feature.xml) I found a really cool tool, Todd Baginski's Sharepoint Feature manager which composes the stsadm.exe command line. Really simple, really useful. You can install, uninstall, activate, deactivate and reset iis. All the things you need to do to your new feature.

I haven't created a setup project for this component, but it is pretty straight forward to install (once you have done it a million times), but here are the steps.
1) using the gac tool, install the assembly
2) Add your calendar lists to configuration.xml in the same format as the file. (Case is important)
2) open c:\WINDOWS\assembly\gac_msil and copy the configuration.xml file into the Sharepoint.Notifier folder
3) Create a features folder in the FEATURES directory called notifier and copy the feature.xml file into it
4) Install and activate the feature (Use todd's tool, it rocks)

Now you have set up the notifier, you should start getting emails when events arise.

Download the source Code
Download the Assembly Only