Extending WSS3 task lists to support recurring reminders

Question

WSS 3.0 will let me send an email to a group when a new task is added to a task list. What I would like to do is to run a weekly task that sends out reminders of tasks due within certain periods, i.e. 2 days, 7 days, 14 days etc. I thought the simplest way would be to build a little C# app that sits on the WS2K3 box and queries the WSS database. Any ideas on which tables I should be checking? More generally is there an overall schema for the WSS3 database system?

If anyone is aware of an existing solution with code please let me know.

Thx++

Jerry

Answer 1

My suggestions:

• don't create a console app, create a class that inherits from SPJobDefinition.
• set SPJobLockType.Job to this timer, this will grant that the job is executed only once in the whole farm, even if you are running multiple front-end servers
• in the, timer job, open the SPSite, SPWeb objects you need, then find the SPList\
• Using SPQuery filter out only the items you need - I believe, you will have to filter out the ones where Status!=Complete
• Loop through the results collection (which will be of type SPListItemCollection, apply your rules, checking the DueDate and Datetime.Now, send the e-mails
• Since a task is simply a SPListItem, it has a Properties property, which is actually a property bag - you can add whatever properties you need. So, add a property My_LastSentReminderDate. Use this property to check if you are not sending too much of "corporate spam" :-)
• To install your SPJobDefinition in a SharePoint farm, you can use a PowerShell script. I can give you examples, if needed.

Don't forget to Threading.Thread.CurrentThread.CurrentCulture = Your_SPWeb_Instance.Locale, otherwise date comparisons may not work if the web has a different locale!

EDIT: This is how a typical reminder looks like in my applications:

Public Class TypicalTimer
    Inherits SPJobDefinition

    Public Sub New(ByVal spJobName As String, ByVal opApplication As SPWebApplication)
        'this way we can explicitly specify we need to lock the JOB
        MyBase.New(spJobName, opApplication, Nothing, SPJobLockType.Job)
    End Sub

    Public Overrides Sub Execute(ByVal opGuid As System.Guid)
        'whatever functionality is there in the base class...
        MyBase.Execute(Guid.Empty)
        Try
            Using oSite As SPSite = New SPSite("http://yourserver/sites/yoursite/subsite")
                Using oWeb As SPWeb = oSite.OpenWeb()
                    Threading.Thread.CurrentThread.CurrentCulture = oWeb.Locale
                    'find the task list and read the "suspects"
                    Dim oTasks As SPList = oWeb.Lists("YourTaskListTitle")
                    Dim oQuery As New SPQuery()
                    oQuery.Query = "<Where><Neq><FieldRef Name='Status'/>" & _
                                    "<Value Type='Choice'>Complete</Value></Neq></Where>"
                    Dim oUndoneTasks As SPListItemCollection = oTasks.GetItems(oQuery)

                    'extra filtering of the suspects.
                    'this can also be done in the query, but I don't know your rules
                    For Each oUndoneTask As SPListItem In oUndoneTasks
                        If oUndoneTask(SPBuiltInFieldId.TaskDueDate) IsNot Nothing AndAlso _
                            CDate(oUndoneTask(SPBuiltInFieldId.TaskDueDate)) < Now().Date Then
                            ' this is where you send the mail
                        End If
                    Next
                End Using
            End Using
        Catch ex As Exception
            MyErrorHelper.LogMessage(ex)
        End Try
    End Sub
End Class

To register a timer job, I typically use this kind of a script:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration")
[System.Reflection.Assembly]::LoadWithPartialName("Your.Assembly.Name.Here")
$spsite= [Microsoft.SharePoint.SPSite]("http://yourserver/sites/yoursite/subsite")

$params = [System.String]("This text shows up in your timer job list (in Central Admin)", $spsite.WebApplication
$newTaskLoggerJob = new-object -type Your.Namespace.TypicalTimer -argumentList $params

$schedule = new-object Microsoft.SharePoint.SPDailySchedule
$schedule.BeginHour = 8
$schedule.BeginMinute = 0
$schedule.BeginSecond = 0
$schedule.EndHour = 8
$schedule.EndMinute = 59
$schedule.EndSecond = 59

$newTaskLoggerJob.Schedule = $schedule
$newTaskLoggerJob.Update()