Introduction
Currently SharePoint only supports alerts when specific event occurs. For instance when an item is created or modified. But what if we want to send a reminder/alert at 3pm every day to notify users that they have had a file check out for over a week. The development of this SharePoint feature is a solution to this well known problem.
Design
So where do we start with our Architecture? Are we going to use a windows service? How are we going to configure our reminders? There are many different ways that I could have designed this application but I decided to take advantage of all the cool features SharePoint has to offer.
Configuration
So SharePoint is pretty flexible and it allows us to add action items to the settings menu in our list views. So that will help users easily navigate to our settings page. Since these are going to be pretty complex settings we are also going to only allow administrators to see this setting.
Now all we'll have to do is point this menu item to send the user to our configuration page. The next step is to create our settings pages and drop them in the layouts directory. The path to the layouts directory is C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\LAYOUTS. Once they are in that directory then they can be accessed in the browser by going to http://<yourserver>/<yoursite>/_layouts/<yourpage>.aspx This is very important because it is going to allow us to access the page from any subsite by just using the subsites url plus _layouts/<yourpage>.aspx. So we'll get http://<yourserver>/<yoursite>/<yoursubsite>/_layouts/<yourpage>.aspx. So just the clearify, this page only exists once on the file system.
But wait!!?!?!? Were are we going to STORE our configuration? Crap... I thought long and hard about this one and I decided to go away from creating a database schema and coding all the CRUD that needs to happen. So I went ahead and decided to store everything in the parent SPWeb object to the current list that is being configured. We'll do this by using xml serialization to serialize our configuration object and storing it in the Property Bag on the SPWeb object. We'll go over this more later. Just keep in mind there is no extra database tacked on. Yeah!
Services
So are we going to need a windows service? Absolutely not!! SharePoint already has a service that is running called "Windows SharePoint Services Timer". This service runs "Jobs" that are set up on a schedule. All we have to do is Inherit from SPJobDefinition, implement the Execute method, drop our dll in the gac and then activate our job on the same server that a central admin is on. It is in this job that we will crawl every list in the site-collection to see if it has any reminder/alert settings. If it does then we'll see if there are any reminders that need to execute.
Flexibility
So now we need to come up with a way for our alerts and reminders to be flexible. How are we going to allow our users this feature to be flexible enough so that it can be reused on different lists for different purposes? Well, we are going to be quering the lists for list items. That will require the user to configure a CAML query that will be used to pull back the items they want. That query will then give us a collection of SPItem. That's great because the collection itself and the item have properties that represent them in a XML string format. So how do we turn this XML into something that is readable by every day SharePoint users who don't know what XML is? Also, we need it to be flexible so that we can do calculations and such on the results. Ah yes, XSLT transformation. It requires quit a bit of knowledge but it's a powerful way to transform that XML into viewable HTML pages that can be seen in an email. Sweet!!!
Installation
GAC the following dlls (Located in the Build Folder)
Mullivan.Shared.dll
Mullivan.SharePoint.dll
Mullivan.SharePoint.Pages.dll
Mullivan.SharePoint.Reminders.dll
Mullivan.SharePoint.WebParts.dll
Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\LAYOUTS
Copy Build\Layouts\ReminderSettings.aspx to this dir
Copy Build\Layouts\EditReminderSettings.aspx to this dir
Copy Build\Layouts\QueryBuilder.aspx to this dir
Copy Build\Layouts\FieldValueDialog.aspx to this dir
Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\1033
Copy Build\Layouts\1033\QueryBuilder.js to this dir
Copy Build\Layouts\1033\MullivanUtility.js to this dir
Copy Build\Layouts\1033\FieldValueDialog.js to this dir
Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\Images
Copy Build\Mullivan.SharePoint.Pages\ReminderSettings.gif to this dir
Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\Features
Copy directory Mullivan.SharePoint.Reminders\Features\Reminder to this directory
Now go and open up Command Propmt
type -> cd "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\BIN"
type -> stsadm -o installfeature -filename "reminder\feature.xml"
type -> stsadm -o activatefeature -filename "reminder\feature.xml" -url "http://<yourserver>"
Restart
Go to Start -> Administrative Tools -> Services and restart Windows SharePoint Timer Service
Go to Start -> Run and type iisreset and click ok
Sweet!!! You should be good to go now. Navigate to any list and you should see the Reminder Settings item in the Settings menu.
Just to make sure you the Scheduled Reminder Job installed successfully, go to your central admin page. Go to Operations -> Timer Job Status. Make sure "Scheduled Reminder Job" exists and is either initialized or succeeded.
If it's not there then you did not install this feature on your central admin/application server.
Configuration
So the first thing we are going to do is go to Central Admin. Navigate to Application Management -> Web application out-going email settings.
In order for email to be sent you need to configure you web applications email settings. You'll need to point it to you smtp server so that emails can be sent out to the users.
Now you can go to any list and go to the Reminder Settings.
Now you see the following page.
On this page you'll be able to view, edit, edit, remove alerts/reminders. I don't know why I put the up and down functionality. I realized after I did it that the order is purposeless. I guess I drank too much coffee.
So click add or edit to get the edit reminder settings page.
Name - This field is of course our name of our alert. This is just a display name and is not used as a unique key. The unique key is a guid that is used behind the scenes.
Type - This field is either one of two choices: Item or Bulk. Item means that each item pulled back in the query will be in a separate email and your XSLT that you configure at the bottom of the page will only have to transform one item. Bulk means that there will only be one email sent and it is up to you XSLT to transform all the items at once.
Recurrence - This field represents how often your query is going to occur and on what days or time. There are four options: Hourly, Daily, Weekly, and Monthly. For the Hourly option, you will be displayed a Start Minute field that represents the minute (0 - 59) of that our the query will run. The query will then run every hour just after that minute occurs. The Daily option will display a Start Hour field that represents the military hour (0 - 23) that our query will run. The query will then run every day just after that hour occurs. The weekly options will display two fields, one being the day it should run, and the other being the military hour that it should run just after. The Monthly option will display two fields, one being the day number (1 - 31) that it should run on and the other being the hour it should run just after. If you put 31 on a day that only has 28 - 30 days then the query will run on the last day of the month. This is a way to ensure that you'll run the query on the last day.
View Fields - This field is the XML that is used by the query object to know what fields you want back. On the right of the View Field you'll see an option to pull in the XML configuration for any of the views that you have configured for this list.
Query - This field represents the where and orderby clause in XML. Known as CAML query, this XML is used to by the SPQuery object to generate it's SQL statements for pulling back data for this list. Make sure when you're constructing you're queries that you use the internal name for you're fields. There are a lot of tool out there that will help you build CAML queries. Just run a google search and I'm sure you'll find one quickly. You may also just click the fill button and fill the field with the CAML query for any of your views.
Mail To - This field is a semi-colon delimited field that represents all of the email addresses that you want to send this alert/reminder to. There are several place holders that I've built in to allow flexibilty on who the email is sent to. These fields are all User fields in SharePoint. This feature checks for this place holder and checks to see if there is a value in the Item for this field. If so then it queries the SharePoint profile list to get an email address back for that user. If it finds a user then it appends them to the list of recipients. These placeholders will only work if the reminder/alert is set up to be in Item mode. If it is in Bulk mode, they won't work because Items may have different users for one of the fields.
[#assignedto#] - Person that is set to the Site field "Assign To"
[#author#] - Person that created the item
[#editor#] - Person that last modified the item
[#checkoutuser#] - Person that currently has the item checked out
[#<insert user field here(internal name)>#] - Person that currently fills this field
Subject - This is the field that is used as the subject text for you alert/reminder in the email.
XSL Transform - This is the field that you use to drop your XSLT transform string into to generate the HTML that is going to be sent into the body of your email. The xml uses special namespaces and also passes in parameters that can be used to help generate your html. That is why the header for you're XSLT should always look like the following:
="1.0"="utf-8"
<xsl:stylesheet version="1.0"
xmlns:xsl="<a href="%22%22%22%22%22%22%22http://www.w3.org/1999/XSL/Transform%22%22%22%22%22%22%22">http://www.w3.org/1999/XSL/Transform</a>"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<xsl:param name="listId" />
<xsl:param name="listTitle" />
<xsl:param name="listUrl" />
<xsl:param name="listImageUrl" />
<xsl:param name="listEditFormUrl" />
<xsl:param name="listDefaultViewUrl" />
<xsl:param name="siteUrl" />
<xsl:template match="/">
<div style="width:100%;text-align:left">
Go to
<a border="0">
<xsl:attribute name="href">
<xsl:value-of select="$listDefaultViewUrl"></xsl:value-of>
</xsl:attribute>
<img border="0">
<xsl:attribute name="src">
<xsl:value-of select="$listImageUrl"></xsl:value-of>
</xsl:attribute>
</img>
 
<xsl:value-of select="$listTitle"></xsl:value-of>
</a>
<div style="text-align:center;font-size:12px;">
<a target="_blank">
<xsl:attribute name="href">
<xsl:value-of select="$siteUrl"></xsl:value-of>/Site%20Pages/Calendar_Scheduled.aspx
</xsl:attribute>
Go to Calender
</a>
</div>
<div style="text-align:center;font-size:12px;">
<a target="_blank">
<xsl:attribute name="href">
<xsl:value-of select="$siteUrl"></xsl:value-of>/Site%20Pages/Implemented_RFCs_Dashboard_New.aspx
</xsl:attribute>
Go to List
</a>
</div>
<br/>
<br/>
</div>
</xsl:template>
</xsl:stylesheet>
Send reminder even if there are no results - Check this option if you are using Bulk mode and would like to send an email even though there are no results returned from your query. This is important because it will let users know that this feature still works, there was just no data returned.
Test Button - Now that you have all of your configurations set you can click the test button to see what you email is going to look like. This will NOT send any emails out. This is only going to display to you what the email will look like when it is sent. IMPORTANT!!! If you would like to view the raw XML that you're XSLT needs to transform, just leave the XSL Transform field empty and click the test button.
IMPORTANT!!! Keep in mind that all alerts/reminders will run right away for the first time. That is because they have never been run before and alert/reminder will always always pass the validation that says it needs to run.
Code
So I'll first go over the RmdJobDefinition class that Inherits from SPJobDefinition. Again, this is the object that the SharePoint Timer Service uses to execute our scheduled alerts/reminders.
namespace Mullivan.SharePoint.Reminders
{
public class RmdJobDefinition : SPJobDefinition
{
internal const string JOBNAME = "Scheduled Reminder Job";
public RmdJobDefinition () : base() { }
public RmdJobDefinition(SPWebApplication webApp)
: base(JOBNAME, webApp, null, SPJobLockType.Job)
{
this.Title = JOBNAME;
}
Above, you'll see that we are passing in SPJobLockType.Job. That means that the SharePoint Timer service is only going to run one instance of this job at a time in your farm.
public override void Execute(Guid targetInstanceId)
{
int poolCount = 0;
DateTime dtStart = DateTime.Now;
RmdProcessCounter counter = new RmdProcessCounter();
try
{
if (this.WebApplication.OutboundMailServiceInstance == null)
throw new Exception("E-mail is not set up for this Web Application.");
if (string.IsNullOrEmpty(this.WebApplication.OutboundMailSenderAddress))
throw new Exception("Outbound mail sender address is required for this feature.");
foreach (SPSite spSite in this.WebApplication.Sites)
{
poolCount++;
RmdProcessSPSiteContext context = new RmdProcessSPSiteContext();
context.Counter = counter;
context.Site = spSite;
ThreadPool.QueueUserWorkItem(ProcessSPSite, context);
}
while (counter.Count < poolCount)
{
Thread.Sleep(1000);
}
}
catch (Exception ex)
{
Logging.ServiceLog.LogException(ex);
}
base.Execute(targetInstanceId);
}
The Execute method is our intial method that the Timer Service calls. In here we will want to loop through every Site in the web application and start a new thread that will process it. We want to do this so that we can get better performance.
private void ProcessSPSite(object state)
{
RmdProcessSPSiteContext context = (RmdProcessSPSiteContext)state;
SPSite spSite = null;
try
{
spSite = context.Site;
foreach (SPWeb spWeb in spSite.AllWebs)
{
ProcessSPWeb(spWeb, context);
spWeb.Dispose();
}
}
catch (Exception ex)
{
Logging.ServiceLog.LogException(ex);
}
finally
{
context.Counter.Increment();
spSite.RootWeb.Dispose();
spSite.Dispose();
}
}
Above, you'll see that we want to go through each SPWeb in the spSite.AllWebs property. AllWebs represents every web in the site collection no matter what sub site it is located in.
private void ProcessSPWeb(SPWeb spWeb, RmdProcessSPSiteContext context)
{
foreach (SPList list in spWeb.Lists)
{
try
{
ProcessList(list, context);
}
catch (Exception ex)
{
Logging.ServiceLog.LogException(ex);
}
}
}
Now process each list in the web.
private void ProcessList(SPList list, RmdProcessSPSiteContext context)
{
string emailBody = string.Empty;
string strXml = string.Empty;
XsltArgumentList argList = null;
MailAddress[] mailToAddresses = null;
List<RmdConfiguration> rmdConfigs = null;
SPListItemCollection items = null;
rmdConfigs = RmdUtil.GetRmdConfigs(list);
if (rmdConfigs == null)
return;
foreach (RmdConfiguration rmdConfig in rmdConfigs)
{
if (rmdConfig.Recurrence.CanRun(rmdConfig.LastRun))
{
items = RmdUtil.ExecuteQuery(list, rmdConfig.Query, rmdConfig.ViewFields);
if (items.Count > 0 || rmdConfig.SendNoResults)
{
argList = RmdUtil.GetXsltArgList(list);
if (rmdConfig.Type == RmdType.Bulk)
{
strXml = RmdUtil.GetXml(items);
mailToAddresses = RmdUtil.GetMailToAddresses(rmdConfig.MailTo, null, list);
emailBody = RmdUtil.RenderHtml(strXml, argList, rmdConfig.XslTransform);
SendEmail(mailToAddresses, rmdConfig.Subject, emailBody);
}
else
{
for (int i = 0; i < items.Count; i++)
{
SPListItem spItem = items[i];
strXml = RmdUtil.GetXml(spItem);
mailToAddresses = RmdUtil.GetMailToAddresses(rmdConfig.MailTo, spItem, list);
emailBody = RmdUtil.RenderHtml(strXml, argList, rmdConfig.XslTransform);
SendEmail(mailToAddresses, rmdConfig.Subject, emailBody);
}
}
}
rmdConfig.LastRun = DateTime.Now;
RmdUtil.SetRmdConfigs(list, rmdConfigs);
}
}
}
The above method is a little more complicated because it is where we pull out our configuration from the list and determine if the recurrence is set to run.
private void SendEmail(MailAddress[] masTo, string subject, string body)
{
using (MailMessage mail = new MailMessage())
{
mail.From = new MailAddress(this.WebApplication.OutboundMailSenderAddress);
foreach (MailAddress ma in masTo)
mail.To.Add(ma);
mail.Subject = subject;
mail.IsBodyHtml = true;
mail.Body = body;
SmtpClient smtp = new SmtpClient(this.WebApplication.OutboundMailServiceInstance.Server.Address);
smtp.UseDefaultCredentials = (smtp.Credentials == null);
smtp.Send(mail);
}
}
The final method is our SendEmail method that uses our SharePoint web application settings to send the email. As you can see it will provide us with the address to the SMTP server and the email address that it needs to send from.
OK.. So now lets take a look at a couple methods in our RmdUtil class.
public static SPListItemCollection ExecuteQuery(SPList list, string query, string viewFields)
{
SPQuery spQuery = new SPQuery();
if (query != null)
query = query.Trim();
if(viewFields != null)
viewFields = viewFields.Trim();
if (!string.IsNullOrEmpty(query))
spQuery.Query = query;
spQuery.IncludeAttachmentUrls = true;
spQuery.ViewFields = viewFields;
spQuery.ViewAttributes = "Scope=\"Recursive\"";
return list.GetItems(spQuery);
}
Above, you'll see how the query execute's on the list. I just wanted to point out the line that says spQuery.ViewAttributes = "Scope=\"Recursive\"";. This line is important because it tells the query object to display items in all folders.
public static string RenderHtml(string strXml, XsltArgumentList argList, string strXsl)
{
StringBuilder sb = new StringBuilder();
sb.Append("<html>");
using (TextWriter textWriter = new StringWriter(sb))
{
using (HtmlTextWriter writer = new HtmlTextWriter(textWriter))
{
using (StringReader srXsl = new StringReader(strXsl))
{
using (XmlTextReader xrXsl = new XmlTextReader(srXsl))
{
using (StringReader srXml = new StringReader(strXml))
{
using (XmlTextReader xrXml = new XmlTextReader(srXml))
{
XslCompiledTransform cmplTrans = new XslCompiledTransform();
cmplTrans.Load(xrXsl);
XmlWriterSettings settings = new XmlWriterSettings();
settings.CheckCharacters = false;
settings.ConformanceLevel = ConformanceLevel.Fragment;
using (XmlWriter xwHtml = XmlWriter.Create(writer, settings))
{
cmplTrans.Transform(xrXml, argList, xwHtml);
}
}
}
}
}
}
textWriter.Flush();
}
sb.Append("</html>");
return sb.ToString();
}
If you are curious on how to take an XML string and an XSLT string and convert them to HTML. Well, here is you answer. This method also shows how to pass in an Argument list so that the XSLT can have access to more values that may not be provided in the XML.
private static MailAddress GetMailAddress(string strAddress, SPListItem item, SPList list)
{
try
{
string userName = string.Empty;
int id = -1;
string fieldName = strAddress.Substring(2, strAddress.Length - 2);
fieldName = fieldName.Substring(0, fieldName.Length - 2);
switch (fieldName.ToLower())
{
case "checkoutuser":
if (item.File != null && item.File.CheckedOutBy != null)
return new MailAddress(item.File.CheckedOutBy.Email);
break;
case "author":
userName = item.Properties["vti_author"].ToString();
break;
case "editor":
userName = item.Properties["vti_modifiedby"].ToString();
break;
default:
foreach (DictionaryEntry entry in item.Properties)
{
string key = entry.Key.ToString();
if (key.Equals(fieldName, StringComparison.InvariantCultureIgnoreCase))
{
if(!int.TryParse(entry.Value.ToString(), out id))
return null;;
break;
}
}
break;
}
using (SPWeb web = list.ParentWeb)
{
SPListItem user = null;
if (id < 0)
user = GetUserInfo(web, userName);
else
user = GetUserInfo(web, id);
if(user == null)
return null;
object email = user["EMail"];
if (email != null)
return new MailAddress(email.ToString());
else
return null;
}
}
catch (Exception ex)
{
Logging.ServiceLog.LogException(ex);
return null;
}
}
public static SPListItem GetUserInfo(SPWeb web, string loginName)
{
try
{
SPUser u = web.SiteUsers[loginName];
if (u == null)
return null;
return GetUserInfo(web, u.ID);
}
catch (Exception ex)
{
Logging.ServiceLog.LogException(ex);
}
return null;
}
public static SPListItem GetUserInfo(SPWeb web, int id)
{
try
{
SPList userList = web.SiteUserInfoList;
SPQuery query = new SPQuery();
query.ViewFields =
@"<FieldRef Name=\"ContentTypeId\"/><FieldRef Name=\"ContentType\"/>
<FieldRef Name=\"Title\"/><FieldRef Name=\"Name\"/><FieldRef Name=\"EMail\"/>
<FieldRef Name=\"JobTitle\"/><FieldRef Name=\"WorkCity\"/>
<FieldRef Name=\"WorkState\"/><FieldRef Name=\"WorkCountry\"/>";
query.Query = string.Format(
@"<Where><Eq><FieldRef Name='ID'/><Value Type='Int'>{0}</Value></Eq></Where>", id);
SPListItemCollection items = userList.GetItems(query);
if (items.Count > 0)
return items[0];
}
catch (Exception ex)
{
Logging.ServiceLog.LogException(ex);
}
return null;
}
Curious on how to get the SPUser object from an Items Assign To, Created By, or Modified By fields. The above code pulls the users login name out of the results and queries the user profile list to get the users email.
public static List<RmdConfiguration> GetRmdConfigs(SPList list)
{
string configName = GetConfigName(list.ID);
string configuration = string.Empty;
using (SPWeb web = list.ParentWeb)
{
if (web.Properties.ContainsKey(configName))
configuration = list.ParentWeb.Properties[configName];
}
if (string.IsNullOrEmpty(configuration))
return null;
return RmdConfiguration.ReadConfigurations(configuration);
}
public static void SetRmdConfigs(SPList list, List<RmdConfiguration> rmdConfigs)
{
string configName = GetConfigName(list.ID);
string configuration = string.Empty;
configuration = RmdConfiguration.WriteConfigurations(rmdConfigs);
using (SPWeb web = list.ParentWeb)
{
if (!web.Properties.ContainsKey(configName))
web.Properties.Add(configName, configuration);
else
web.Properties[configName] = configuration;
web.Properties.Update();
web.Update();
}
}
The above code serializes/deserializes our configuration object and stores them into the Lists parent SPWeb Properties collection. This is known as the Property Bag.
Conclusion
Hopefully this feature is going to help you with your SharePoint implementation. Let me know if you have any problems or would like to suggest any new features to be built in. Thanks
