Sometimes to get what you want out of Microsoft Project Server, you have no choice but to turn to programming. In this article I describe how we handled a timekeeping-related challenge at my company by tapping into Project Server's ability to work with event handlers.
First, some basics. An event handler is simply a programming routine that handles input from an event. In the case of Project Server, you can use server-side events -- a mechanism for developers to extend Project Server by adding new business logic. Project Server raises events whenever particular data changes on the server. If you associate an event handler with an event, then the handler is executed when that particular event occurs. There are two types of events: pre-events and post-events. Pre-events are raised before data is saved to the database. These events can be canceled by event handlers. Post-events can't be canceled; they're raised after the data changes have been saved to the database.
Now for some background. I work for a high tech company in the telecommunications industry. Fanamoj has almost 200 employees, about three-quarters of which are working on research and development projects. In order to be able to track project costs, our financial department has defined cost centers for each project. Traditionally, staff filled out and submitted an Excel worksheet as a timesheet that included their daily arrival and departure times and also the time spent on working for each cost center (or project). For instance, on February 22 I filled-out that I entered the building at 08:30, left at 18:00 and spent four hours working on cost center code 457 and the rest of my time on cost center code 127.
At the end of the period (we define monthly periods for submitting timesheets), employees printed out their Excel sheets and submitted it to their direct manager to be signed and sent to the human resources and financial departments.
Moving from Cost Centers to Tasks
However, our manager wanted to track time on a task basis -- not just by project -- a challenge he gave to the IT department to figure out. We recognized that there were considerable time costs related to filling out timesheets and having the HR staff transfer the data into their system and prepare reports for the financial department. Cutting down such costs could have great repercussions on total overhead expense. We also knew that our access control system -- a time recorder machine -- was used only to open and close the door of main entrances, and its data had no effect elsewhere. Last, the legacy timesheet system was based on honesty; however, a few members of the staff sometimes misused this trust and damaged the culture. So, we decided to introduce a new timesheet management system.
At first, we figured we could build something from scratch. Initially, monthly payments to staff are based on their timesheets and can't exceed their attendance time. In other words, a functional manager who spends 45 hours for project A and 140 hours for project B has to submit a timesheet with 185 total hours at the end of the period. This can't be greater than his or her total attendance time. For the sake of convenience our top management accepted one hour for timesheet tolerance; if total attendance time were 185 hours, the employee could submit any timesheet up to 186 hours. Ultimately, building a new timesheet management program from scratch with the same functionality as the existing system was estimated to be too costly.
To comply with the various rules, we decided to manipulate Microsoft Project Server timesheet functionality and integrate it with the time recording device. Therefore, we manipulated the "Save" and "Save & Submit" operations, which are key aspects of the Project Web Access (PWA) timesheet features.
Manipulating the OnUpdated Event
Figure 1 shows the process for the On_Updated event handler, the key to the process.
Figure 1. The On_Updated event handler.
To handle the timesheet save event, we created "FanamojTimesheetEventHandler.dll" which overrides the OnUpdated method. Afterwards, we configured operational polices of the server (the server-side event handler configuration) to enable the server to run our custom code after users save their timesheets.
The following steps will allow you to sample the On Update event:
Step 1. Create new class library project using Microsoft Visual Studio 2008.
Step 2. Reference the namespaces below at the top of your code:
using Microsoft.Office.Project.Server.Library;
using Microsoft.Office.Project.Server.Events;
using System.Diagnostics; //To log events
using System.Web.Services; //To connect, use PSI web services.
using System.Data.OleDb; //To connect your event handler to your
//time recorder database.
using System.Data;
using System.Data.SqlClient;
Step 3. Modify the OnUpdated method of your class library as follows:
namespace YouCompanyNameTimesheetHandler
{
public class Main : TimesheetEventReceiver
{
public override void OnUpdated(PSContextInfo contextInfo, TimesheetPostEventArgs e)
{
const string Project_Server_URI = "http://Your Project Server Web
Application/PWA/";
const string Timesheet_Service_Path = "_vti_bin/PSI/Timesheet.asmx";
const string Resource_Service_Path = "_vti_bin/PSI/Resource.asmx";
const string Admin_Service_Path = "_vti_bin/PSI/Admin.asmx";
//Preparation for using Timesheet.asmx to read timesheet data
WebSrvTimesheet.TimeSheet MyTimesheetSrv = new WebSrvTimesheet.TimeSheet();
MyTimesheetSrv.UseDefaultCredentials = true;
MyTimesheetSrv.Url = Project_Server_URI + Timesheet_Service_Path;
//Preparation for using Resource.asmx to read Resource data
WebSrvResource.ResourceDataSet MyResourceDS;
WebSrvResource.Resource MyResourceSrv = new WebSrvResource.Resource();
MyResourceSrv.UseDefaultCredentials = true;
MyResourceSrv.Url = Project_Server_URI + Resource_Service_Path;
MyResourceDS = MyResourceSrv.ReadResource(contextInfo.UserGuid);
//Preparation for using Admin.asmx to read timesheet period data
WebSrvAdmin.Admin MyAdminSrv = new WebSrvAdmin.Admin();
MyAdminSrv.UseDefaultCredentials = true;
MyAdminSrv.Url = Project_Server_URI + Admin_Service_Path;
WebSrvAdmin.TimePeriodDataSet MyTimePeriodDS;
MyTimePeriodDS = MyAdminSrv.ReadPeriods(WebSrvAdmin.PeriodState.All);
EventLog TimesheetLog = new EventLog();
TimesheetLog.Source = "Fanamoj Timesheet";
int EventID = 3652;
string LogEntry;
decimal TotalValue;
string Username = MyResourceDS.Resources[0].RES_EXTERNAL_ID;
//Resource External ID is resource identification card code
// which is different
//from employee ID.
Guid CTSGuid = e.TsUID; //Gets current timesheet UID
Guid CurrentWRPD_ID;
DateTime PeriodStartDate = System.DateTime.Now;
DateTime PeriodFinishDate = System.DateTime.Now;
//Reads period UID of current timesheet
CurrentWRPD_ID = MyTimesheetSrv.ReadTimesheet(CTSGuid).Headers[0].WPRD_UID;
//Reads period start and finish dates for the current timesheet period.
for (int i = 0; i < MyTimePeriodDS.TimePeriods.Count; i++)
{
if (MyTimePeriodDS.TimePeriods[i].WPRD_UID == CurrentWRPD_ID)
{
PeriodStartDate = MyTimePeriodDS.TimePeriods[i].WPRD_START_DATE;
PeriodFinishDate = MyTimePeriodDS.TimePeriods[i].WPRD_FINISH_DATE;
}
}
//Reads current timesheet total actual work reported by user.
TotalValue = MyTimesheetSrv.ReadTimesheet(CTSGuid).
Headers[0].TS_TOTAL_ACT_VALUE;
//Represents timesheet total actual work in hours.
decimal Timesheet_Total = TotalValue / 60000;
decimal Time_Card_Total;
//Here, you need to connect to your time recording system database and set
//Time_Card_Total (Defined above) to total attendance time of current user.
//Remember, we have resource external ID and period start and finish dates
//to query this data.
//Now, Prepare your handler to show timesheet total data and
//attendance system
//data to current user.
string TS_Comment_Text;
TS_Comment_Text = "Time Recording System Total: " + " " +
Math.Round(Time_Card_Total,
1).ToString() + " \n" + "Timesheet Total:" + " " +
Math.Round(Timesheet_Total,
1).ToString() + " \n";
if (Timesheet_Total > (Time_Card_Total + 1)) //User has entered more
//than his/her
// attendance time
{
TS_Comment_Text = TS_Comment_Text + "Since Time Recording System total
is less than Timesheet total your timesheet can't be sent to
your manager.";
}
//Updates command text of current timesheet.
string Connection_String = "data source=Your PWA Database Server;Initial
catalog=ProjectServer_Published;User Id=user;Password=password;";
string Update_SQL_String;
SqlCommand Comment_Update_Command = new SqlCommand();
SqlConnection Cnn = new SqlConnection(Connection_String);
Cnn.Open();
Update_SQL_String = "Update dbo.MSP_TIMESHEETS";
Update_SQL_String = Update_SQL_String + " Set";
Update_SQL_String = Update_SQL_String + " TS_COMMENTS = '" +
TS_Comment_Text + "'";
Update_SQL_String = Update_SQL_String + " Where";
Update_SQL_String = Update_SQL_String + " (TS_UID = '" +
e.TsUID.ToString() + "')";
Comment_Update_Command.Connection = Cnn;
Comment_Update_Command.CommandText = Update_SQL_String;
Comment_Update_Command.ExecuteNonQuery();
}
//Logs current activities to windows events.
LogEntry = "User: " + Username + "\n Total Submitted Time: " + TotalValue +
"\n Start Time = " + PeriodStartDate.Date.ToString() + "\n Finish Time =
" + PeriodFinishDate.Date.ToString() + "\n TimeCard = " + ddd;
TimesheetLog.WriteEntry(LogEntry, EventLogEntryType.Information,EventID);
}
Step 4. Build your solution, add it to global assembly cache (GAC), and open the server side event handler configuration in your PWA Server Settings.
Step 5. Select timesheet updated event from the event list and set the properties of your assembly name.
Figure 2. The user alert that shows up in the event of a conflict.
As Figure 2 shows, the comment field of the timesheet will be updated after each save. However, if the timesheet total exceeds the time recording system total + 1, the handler will alert users with the note, "Since Time Recording System total is less than Timesheet total, your timesheet may not be sent to your manager." The alert also lists the hours in conflict.
Unfortunately, Project Server 2007 doesn't offer any web service to update the timesheet's comment. So, we had to update the TS_COMMENTS field of the dbo.TIMESHEETS table in the Projectserver_Published database using SQL commands.
Manipulating the OnSubmitting Event
To prevent users from submitting their timesheets while there are nonconformities with the time recorder machine, we put a bunch of code in the OnSubmitting method of our handler to override the submitting action in Project Server. The sequences of events are shown in Figure 3.
Figure 3. Where the comparison of submitted time is compared to the number shown from the time recording machine.
Step 6. To complete your event handler project, you just need to add an OnSubmitting operation after the OnUpdated operation.
public override void OnSubmitting(PSContextInfo contextInfo,
TimesheetPreSubmitEventArgs e)
Step 7. Then include the following code to override the default PWA submit event.
public override void OnSubmitting(PSContextInfo contextInfo,
TimesheetPreSubmitEventArgs e)
{
const string Project_Server_URI = "http://Your Project Server
Web Application/PWA/";
const string Timesheet_Service_Path = "_vti_bin/PSI/Timesheet.asmx";
const string Resource_Service_Path = "_vti_bin/PSI/Resource.asmx";
const string Admin_Service_Path = "_vti_bin/PSI/Admin.asmx";
//Preparation for using Timesheet.asmx to read timesheet data
WebSrvTimesheet.TimeSheet MyTimesheetSrv = new WebSrvTimesheet.TimeSheet();
MyTimesheetSrv.UseDefaultCredentials = true;
MyTimesheetSrv.Url = Project_Server_URI + Timesheet_Service_Path;
//Preparation for using Resource.asmx to read Resource data
WebSrvResource.ResourceDataSet MyResourceDS;
WebSrvResource.Resource MyResourceSrv = new WebSrvResource.Resource();
MyResourceSrv.UseDefaultCredentials = true;
MyResourceSrv.Url = Project_Server_URI + Resource_Service_Path;
MyResourceDS = MyResourceSrv.ReadResource(contextInfo.UserGuid);
//Preparation for using Admin.asmx to read timesheet period data
WebSrvAdmin.Admin MyAdminSrv = new WebSrvAdmin.Admin();
MyAdminSrv.UseDefaultCredentials = true;
MyAdminSrv.Url = Project_Server_URI + Admin_Service_Path;
WebSrvAdmin.TimePeriodDataSet MyTimePeriodDS;
MyTimePeriodDS = MyAdminSrv.ReadPeriods(WebSrvAdmin.PeriodState.All);
EventLog TimesheetLog = new EventLog();
TimesheetLog.Source = "Fanamoj Timesheet";
int EventID = 3652;
string LogEntry;
decimal TotalValue;
string Username = MyResourceDS.Resources[0].RES_EXTERNAL_ID;
//Resource External ID is resource identification card code which is
//different from employee ID.
Guid CTSGuid = e.TsUID; //Gets current timesheet UID
Guid CurrentWRPD_ID;
DateTime PeriodStartDate = System.DateTime.Now;
DateTime PeriodFinishDate = System.DateTime.Now;
//Reads period UID of current timesheet
CurrentWRPD_ID = MyTimesheetSrv.ReadTimesheet(CTSGuid).Headers[0].WPRD_UID;
//Reads period start and finish dates for the current timesheet period.
for (int i = 0; i < MyTimePeriodDS.TimePeriods.Count; i++)
{
if (MyTimePeriodDS.TimePeriods[i].WPRD_UID == CurrentWRPD_ID)
{
PeriodStartDate = MyTimePeriodDS.TimePeriods[i].WPRD_START_DATE;
PeriodFinishDate = MyTimePeriodDS.TimePeriods[i].WPRD_FINISH_DATE;
}
}
//Reads current timesheet total actual work reported by user.
TotalValue = MyTimesheetSrv.ReadTimesheet(CTSGuid).Headers[0].
TS_TOTAL_ACT_VALUE;
//Represents timesheet total actual work in hours.
decimal Timesheet_Total = TotalValue / 60000;
decimal Time_Card_Total;
//Here, you need to connect to your time recording system database and
//set Time_Card_Total (Defined above) to total attendance time of
//current user.
// Remember, we have resource external ID and period start and finish dates
//to query this data.
if (Timesheet_Total > (Time_Card_Total + 1)) //User has entered more than
//his/her attendance time
{
e.Cancel = true; //*** Cancels the submitting operation and notifies user. ***
}
Gaining Greater Accuracy with a Bit of Code
Attendance and access control systems are widely used in business. So it's useful to be able to make actual work submitted on projects more accurate by checking the database to which these devices record before those hours are submitted to the timesheet manager. But the first requirement is to install Microsoft Project Server 2007!
Farhad Naderipour, based in Tehran, Iran, works as the IT manager for Fanamoj Co. There he has established a project management office in order to train and help R&D project managers prepare monthly reports of the projects, deployed Project Server 2007 in the organization, analyzed and implemented a document management system using Project Server 2007 and SharePoint 2007, and established security using Microsoft Right Management Server. He's the author of A Guide for Delphi Programmers published by Naghoos Press in Persian. Farhad has a B.S. in industrial engineering from Islamic Azad University. Contact him at f_naderipour@yahoo.com.