- Accountability
- Alt MSA Window
- Assessment Training
- Curriculum & Instruction
- Athletic Directors
- General Curriculum Committee
For this post, I am going to describe the component of the solutions with subsequent posts on the details of the customization required.
The components of this solution consist of the following:
SQL Database
The SQL database consists of a single table for maintaining the list of user subscription records. (You could also use a SharePoint list to maintain this data)CREATE TABLE [dbo].[MC_Subscriptions](IDENTITY(1,1) NOT NULL,NOT NULL,NOT NULL,(50) NULL,(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,CONSTRAINT [PK_MC_Subscriptions] PRIMARY KEY CLUSTERED[SubscriptionId] [int] [SubscriptionUserEid] [int] [SubscriptionCategory] [int] [TimeStamp] [binary] [SubscriptionUserEmail] [nvarchar] |
SharePoint Site and Lists
A SharePoint Site created from the Blank site template. This site contains two lists: CalendarCategories and Master Calendar.CalendarCategories List: Custom SharePoint List for managing the Category and Category Grouping values
| Type | Required | ||
| Title | Single line of text | required | |
| Group | Choice | required | |
| CategoryChoice | Calculated (calculation based on other columns) =CONCATENATE(Group,": ",Title) | ||
| CategoryDescription | Multiple lines of text | ||
| Created By | Person or Group | ||
| Modified By | Person or Group | ||
| Column | Type | Used in |
| BGColor | Calculated (calculation based on other columns) =IF(StatusType=0,"#FFFFFF",IF(StatusType=1,"#DEDEDE",IF(StatusType=2,"#FFFF00",IF(StatusType=3,"#FFFFEE",IF(StatusType=4,"#DDEEFF","#EEDDFF"))))) | Event |
| Border | Calculated (calculation based on other columns) =IF(StatusType=1,"#FF0000","#CCCCCC") | Event |
| Category | Lookup | Event |
| Color | Calculated (calculation based on other columns) =IF(StatusType=1,"#A6A6A","#000000") | Event |
| Description | Multiple lines of text | Event |
| Display | Calculated (calculation based on other columns) ="<DIV style='border: 1px "&Border&" solid; padding:5px; margin: 0px; color:"&Color&"; background-color:"&BGColor&";'/>"&Title&"</DIV>" | Event |
| End Time | Date and Time | Event |
| Location | Single line of text | Event |
| Start Time | Date and Time | Event |
| StatusType | Number | Event |
| Title | Single line of text | Event |
| WeekDisplay | Calculated (calculation based on other columns) ="<SPAN style='border: none; padding:1px 10px 1px 10px; margin: 0px;color:"&Color&"; background-color:"&BGColor&";'/>"&Title&"</SPAN>" | Event |
| Created By | Person or Group | |
| Modified By | Person or Group |
List Content Type: Event
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Workflow
A workflow is used on the Master Calendar list to set the StatusType based on that status of the event: Approved, Pending, or Rejected. The StatusType field is required due to the fact that you cannot access the Approval Status field in a formula for a calculated field. The calculated fields BGColor, Border, and Color use the StatusType field for applying the appropriate color based on the event's Approval Status.Web Parts
There are two Web Parts used in this solution. The first web part is used to display a summary of the user's category subscriptions. I placed this web part on the home page of the Master Calendar site along side the Month View of the Master Calendar list. The second web part allows the user to manage their subscription alerts. This Manage Alert subscriptions web part resides on a page within the AppPages document library. when creating this library, select Web Part Page as the default document template type.Custom E-mail Alert Event Handler
In order to fire off emails to users who are subscribe to a category, the solution intercepts alerts that are triggered based on an alert that is set up for a designated user. The interception occurs in a custom e-mail alert notification handler that was create according to the information provided in kb948321. Rather than modifying the Events alert template, I also created a new alert template within the customalerttemplates.xml file and registered this template with my Master Calendar event list so that it specifically used this custom event template, but other lists used the standard template.I wrote a simple console app to register the alert template to my list:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
namespace RegisterAlertTemplateToList
{
class Program
{
static void Main(string[] args)
{
using (SPSite site = new SPSite("http://mysite"))
{
using (SPWeb web = site.OpenWeb("mc"))
{
SPList list = web.Lists["Master Calendar"];
SPAlertTemplateCollection atc = new SPAlertTemplateCollection((SPWebService)site.WebApplication.Parent);
SPAlertTemplate newTemplate = atc["SPAlertTemplateType.CustomMasterCalendar"];
if (newTemplate == null)
{
throw new Exception(String.Format("Alert Template not found. List: {0}", list.Title));
}
else
{
list.AlertTemplate = newTemplate;
list.ParentWeb.AllowUnsafeUpdates = true;
list.Update();
}
}
}
}
} 
