Building a Skype for Business Automatic Time Clock - Part I

In today's modern world it has gotten harder to track the work habits or your peers, or team.  Many employees work for home now, and very few punch the time clock.  In this series I am going to use a few great Microsoft technologies to build an automatic time clock.  My primary purpose for creating this was to track how many hours a week I was working, more for my curiosity sake than anything.  The same principals however, can be used to build a system to track the hours of your employees.  The example we are going to build will generate reports based on start and stop time of user status changes, and we will assume that by excluding status duration of away and offline will yield us the time a user spent working.

Our end goal is to be able to see something like this.

What we will need.

1.  A UCMA application
2.  A SQL table
3.  A website or program to report the results

Today we are going to start with the SQL table since without it, we have no where to store our data.  Here is what I propose for a table to store our data.

CREATE TABLE [dbo].[timeclock_table](
[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_timeclock_table_rowguid]  DEFAULT (newid()),
[uri] [varchar](250) NULL,
[sdate] [datetime] NULL CONSTRAINT [DF_timeclock_table_sdate]  DEFAULT (getdate()),
[edate] [datetime] NULL,
[seconds] [int] NULL,
[state] [varchar](50) NULL,
 CONSTRAINT [PK_timeclock_table] PRIMARY KEY CLUSTERED 
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

We will add 3 indexes to our table.

/****** Object:  Index [PK_timeclock_table]    Script Date: 3/5/2016 7:02:47 AM ******/
ALTER TABLE [dbo].[timeclock_table] ADD  CONSTRAINT [PK_timeclock_table] PRIMARY KEY CLUSTERED 
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

/****** Object:  Index [NonClusteredIndex-20160303-064705]    Script Date: 3/5/2016 7:03:18 AM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160303-064705] ON [dbo].[timeclock_table]
(
[uri] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

/****** Object:  Index [NonClusteredIndex-20160303-082645]    Script Date: 3/5/2016 7:03:46 AM ******/

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160303-082645] ON [dbo].[timeclock_table]

(

[state] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Things of note on the SQL table;

1.  We are setting a default on sdate so when the record is inserted (a new status is detected) it is stamped with the default getdate() which is NOW.

2.  Indexes on URI and STATE are created to ensure fast updates and reporting.

Next week will dive into the UCMA application that will populate our table.

Doug Routledge, C# Lync, Skype for Business, SQL, Exchange, UC Developer  BridgeOC Twitter - @droutledge @ndbridge