Billing Tracking System with Exchange and SharePoint
As an owner of a small IT business I am always looking for ways to better manage my time & billing tracking. I also want to track almost real-time how much time my other staff have used against budget. Exchange provides an easy way for every one to record their time as Calendar entries by using the Location to store a Job reference code and set the Category to Work Hours. So no matter where everybody is working they can update their exchange calendar with their times and description and I can then synchronise the data into a SQL Database and summarise the Data in SharePoint.
Below is a Screenshot showing the Job Status in SharePoint and the Jobs that are currently active. These Jobs can be editied in SharePoint and synchronised back to SQL Server with Data Sync.
Requirements
- Exchange Server 2007 User Accounts
- SharePoint Server 2007 (WSS)
- SQL Server Express 2005
- Data Synchronisation Studio
1) Configure Data Synchronisation Studio to download Billing Entries from Exchange Calendars into SQL Server.
The ExchangeGroupCalendarDataSourceReader custom provider can read values from multiple exchange mailbox calendars and takes a single xml configuration file for it's settings.
Example Xml File
<Exchange>
<Server>https://myserver/exchange</Server>
<Username>user@simego.com</Username>
<Password></Password>
<Domain></Domain>
<FormsAuthentication>True</FormsAuthentication>
<Calendar>#SERVER#/#MAILBOX#/Calendar</Calendar>
<DaysBackToReturn>90</DaysBackToReturn>
<Category>Work Hours</Category>
<MailBoxes>
<MailBox>user1@simego.com</MailBox>
<MailBox>user2@simego.com</MailBox>
</MailBoxes>
</Exchange>
Crack open Data Sync and connect to the Exchange Store with the configuration xml file and create a SQL destination table called Billing.
2) Add a Table to SQL Server to list the JOB Codes we have in the System
CREATE TABLE [dbo].[Job] (
[DSID] uniqueidentifier DEFAULT(newid()) NOT NULL,
[Code] nvarchar(20) NOT NULL,
[Company] nvarchar(255) NOT NULL,
[Name] nvarchar(255) NOT NULL,
[Quote] int DEFAULT(0) NOT NULL,
[Active] int DEFAULT(0) NOT NULL
)
3) Create a View in SQL to show the calculated totals
CREATE VIEW Billing_Stats
AS
SELECT Location AS Code, Job.Company, Job.Name, Job.Quote, SUM(Duration)/60/60 AS Total
FROM [dbo].[Billing]
INNER JOIN Job ON Job.Code = Billing.Location
GROUP BY Location, Job.Company, Job.Name, Job.Quote, Job.Active
HAVING Job.Active = 0
4) Configure Data Sync to Synchronise the JOB Codes Table to a SharePoint List
5) Configure Data Sync to Synchronise the Calculated Totals View to a SharePoint List
This new Exchange Data Provider is currently in private beta with a few clients if you would like to know more please contact sales[at]simego.com.
