Create a Timesheet Application with SharePoint and Exchange

8 May 2009

Create a Timesheet Application with SharePoint and Exchange

Overview

This example shows how you can build a real-time (almost) timesheet reporting system by using Exchange Server, SQL Server and SharePoint Server along with Data Synchronisation Studio. Each employee will record their time within their Exchange calendar and define the entry as a Work Hours category. Data Sync will then aggregate all the entries from each user calendar and present a summary view within SharePoint.

Preview of Summary Page

Preview of Exchange Calendar Item

Recording time is as simple as creating a calendar item and setting a few properties.

Set the item Category to Work Hours
Set the value of Location to the Job identification Code
Set the body of the message to the description of the work completed

This data will then be synchronised via Data Synchronisation Studio to a SQL Server Table which can then be used for reporting.

Step 1 - Create Database

Create a SQL database with SQL Server called TimesheetDB to store the timesheet records from Exchange Server and job detail Records.

USE [TimesheetDB]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE TABLE [dbo].[Job] (
    [DSID] uniqueidentifier DEFAULT(newid()) NOT NULL,
    [Code] nvarchar(255) NOT NULL,
    [Company] nvarchar(255) NOT NULL,
    [Name] nvarchar(255) NOT NULL,
    [Quote] int NOT NULL,
    [Active] int NOT NULL
)
GO
ALTER TABLE [dbo].[Job] ADD CONSTRAINT [PK_1AD0E075] PRIMARY KEY CLUSTERED ([DSID] ASC)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE TABLE [dbo].[Billing] (
    [CalendarID] nvarchar(50) NOT NULL,
    [CreationDate] datetime NULL,
    [LastModified] datetime NULL,
    [StartDate] datetime NULL,
    [EndDate] datetime NULL,
    [Duration] int NULL,
    [Importance] ntext NULL,
    [Priority] int NULL,
    [Subject] nvarchar(255) NULL,
    [Body] ntext NULL,
    [Location] nvarchar(255) NULL,
    [Category] nvarchar(255) NULL,
    [MailBoxName] nvarchar(255) NULL
)
GO
ALTER TABLE [dbo].[Billing] ADD CONSTRAINT [PK_2FA475F7] PRIMARY KEY CLUSTERED ([CalendarID] ASC)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW Timesheet_Stats

SELECT
    [Billing].Location AS Code,
    Job.Company,
    Job.Name,
    Job.Quote,
    ROUND(SUM(CAST(Billing.Duration as float))/60/60, 2) AS Total,
    ROUND(CAST(LAST_MONTH.Duration as float)/60/60, 2) AS [Last Month],
    ROUND(CAST(THIS_MONTH.Duration as float)/60/60, 2) AS [This Month]
FROM [Billing]
INNER JOIN Job ON Job.Code = [Billing].Location
LEFT JOIN ( SELECT [Location], SUM([Duration]) AS [Duration] FROM [dbo].[Billing] WHERE Month([StartDate]) = Month(DATEADD(m, -1, GETDATE())) GROUP BY [Location]) LAST_MONTH ON LAST_MONTH.Location = [Billing].[Location]
LEFT JOIN ( SELECT [Location], SUM([Duration]) AS [Duration] FROM [dbo].[Billing] WHERE Month([StartDate]) = Month(GETDATE()) GROUP BY [Location]) THIS_MONTH ON THIS_MONTH.Location = [Billing].[Location]
GROUP BY [Billing].Location, Job.Company, Job.Name, Job.Quote, Job.Active, LAST_MONTH.Duration, THIS_MONTH.Duration
HAVING Job.Active = 0
GO

Step 2 - Create Exchange Sync Project

To connect Data Sync to your exchange server you will need to define a control file that contains the connection details to your server and the mailboxes that should be included in the synchronisation.

Create an Xml file call Timesheet.xml that is similar to the example below.

<Exchange>
<Server>https://yourserver/exchange</Server>
<Username></Username>
<Password></Password>
<Domain></Domain>
<FormsAuthentication>False</FormsAuthentication>
<Calendar>#SERVER#/#MAILBOX#/Calendar</Calendar>
<DaysBackToReturn>90</DaysBackToReturn>
<Category>Work Hours</Category>
<MailBoxes>
    <MailBox>user1@domain.com</MailBox>
    <MailBox>user2@domain.com</MailBox>
    <MailBox>user3@domain.com</MailBox>
</MailBoxes>
</Exchange>

Now connect Data Source A of Data Sync to this Xml file via the Exchange Calendars data provider.

Preview of Data Sync Job Project Options

Then connect Data source B to the Billing Table in the Timesheet Database and your Data Sync project should now look like this.

Step 3 - Create Job List

Connect Data Sync to the Job Table and then create a SharePoint List called Job to Sync the table.

Screenshot of the Job project in Data Sync

The Job list should be configured for 2-way sync so that new Jobs added to the SharePoint List are synchronised with the Database. To do this go to the File menu and choose properties and set the sync option to SyncAandB.

Step 4 - Create Summary List

Connect Data Sync to the Timesheet_Stats View and then create a SharePoint List called Summary to Sync the view.

Step 5 - Setup Schedule

This project requires that the synchronisation is run in a particular order for example.

Sync Job List
Sync Exchange Data
Sync Summary List

To execute the synchronisation in this order you need to setup the Simego.DataSync.Run.exe command line application like this

Simego.DataSync.Run.exe Job.dsprj Exchange.dsprj Summary.dsprj

Now when the Synchronisation process is started each project will be processed in order.

Note - Mailbox Security

For data sync to read the entries from each user calendar the account that is used to run the sync requires Reviewer access at the Mailbox and Calendar level. Which can be configured via the Outlook Client.