Building a Skype for Business Automatic Time Clock - Part II

Last week we began the process of using Skype for Business to create an automated time clock.  We laid out our data structure for a SQL table to store our status changes.  This week we are going to dive into the code in UCMA that will populate that table.

Before we Begin

I am going to make a couple of assumptions here to minimize the amount of code we need to go over today.  I will assuming you know how to created a trusted application, and how to establish a collaboration platform using UCMA.  If you do not, there are articles in this blog that go over that process.  In this example our established collaboration platform is going to be called _collaborationPlatform.

Let's Get Started

In our code we will generate a List<string> with all the endpoints we want to track.  If you are looking to track everyone you can receive those easily with a SQL query against the S4B RTC database instance.

List<string> mylist = new List<string>();

mylist.Add("sip:user1@domain.com");
mylist.Add("sip:user2@domain.com");

etc.

Once we have that we will need to decided on which account we want to user to gather the status updates in our UserEndpoint.  I will highlight that section change in blue.

public void SubscribeToPres(List<string> users)
        {
            try
            {
                List<RemotePresentitySubscriptionTarget> targets = new List<RemotePresentitySubscriptionTarget>();
                RemotePresenceView _presenceView;

                UserEndpointSettings _userEpSettings = new UserEndpointSettings("sip:timeclock@domain.com");
                _userEpSettings.AutomaticPresencePublicationEnabled = true;
                UserEndpoint _userEp = new UserEndpoint(_collaborationPlatform, _userEpSettings);

                _userEp.BeginEstablish(myar =>
                {
                    try
                    {
                        _userEp.EndEstablish(myar);

                    }
                    catch { }

                }, null);

                int x = 0;

                while (_userEp.State != LocalEndpointState.Established)
                {
                    Thread.Sleep(100);
                    x++;

                    if (x > 500)
                    {
                        break;
                    }
                }

                var viewSettings = new RemotePresenceViewSettings();

                foreach (string s in users)
                {
                    try
                    {
                        targets.Add(new RemotePresentitySubscriptionTarget(s));
                    }
                    catch { }
                }

                _presenceView = new RemotePresenceView(_userEp, viewSettings);

                this.WireUpHandlersForView(_presenceView);

                _presenceView.StartSubscribingToPresentities(targets);

            }
            catch (Exception ex) 
            {
                Console.WriteLine(ex.Message);
            }
        }

You can see first we establish the user endpoint, then we wait for it get into the established state.  Following that we begin the process of adding our mylist users into a RemotePresentitySubscriptionTarget.  Once that is complete we use the code below to create the events we are going to handle.

private void WireUpHandlersForView(RemotePresenceView view)
        {
            Console.WriteLine("\nWiring up handlers for view: " + view.ApplicationContext);
            view.SubscriptionStateChanged += RemotePresenceView_SubscriptionStateChanged;
            view.PresenceNotificationReceived += RemotePresenceView_NotificationReceived;
        }

You can see from the code that we will handle 2 events, one for debug logging, and the other (PresenceNotificationReceived) to put the data in SQL.

 private void RemotePresenceView_SubscriptionStateChanged(object sender, RemoteSubscriptionStateChangedEventArgs e)
        {
            try
            {
                RemotePresenceView view = sender as RemotePresenceView;

                foreach (KeyValuePair<RealTimeAddress, RemotePresentityStateChange> stateChanged in e.SubscriptionStateChanges)
                {
                    Console.WriteLine(stateChanged.Key.ToString() + " - " + stateChanged.Value.State.ToString());
                }

            }
            catch { }
        }

Below is the important event to handle.  Please not the SQL connection string in blue will need to modified to point where you created your table.

private void RemotePresenceView_NotificationReceived(object sender, RemotePresentitiesNotificationEventArgs e)
        {
            try
            {
                foreach (RemotePresentityNotification notification in e.Notifications)
                {
                    Console.WriteLine(notification.PresentityUri + " - " + notification.AggregatedPresenceState.Availability.ToString());

                    SqlConnection cn = new SqlConnection("DATABASE=dbname;MAX POOL SIZE=100000;SERVER=serverip;user id=user;password=password");

                    try
                    {
                        cn.Open();
                        SqlCommand mycom = new SqlCommand();
                        mycom.Connection = cn;
                        
                        mycom.CommandText = "update timeclock_table set edate=getdate() where uri='" + notification.PresentityUri +"' and edate is null";
                        mycom.ExecuteNonQuery();

                        mycom.CommandText = "update timeclock_table set seconds = DATEDIFF(s, sdate, edate) where uri='" + notification.PresentityUri + "' and seconds is null";
                        mycom.ExecuteNonQuery();

                        mycom.CommandText = "insert into timeclock_table(uri, state) values ('" + notification.PresentityUri + "','" + notification.AggregatedPresenceState.Availability.ToString() +"')";
                        mycom.ExecuteNonQuery();

                    }
                    catch { }
                    finally
                    {
                        cn.Close();
                        cn.Dispose();
                    }
                }
            }
            catch { }
        }


You can see the process is pretty simple.  We are going to execute 3 queries when a new presence is received.

The Queries

Query #1 sets an end time on the last open presence for the user.  So if the user was away and now they are busy you are setting an end time on the away status, as that is now ended.

Query #2 then looks at the record we just our end time on, and fills out the seconds field with the time difference between start and end.  We could just as easily do this in the reporting query, but this will reduce the overhead and improve the speed by doing it before we try to report.

Query #3 inserts a new record with the user's new state, and a start time (with is defaulted in our sql definition) so the entire process can repeat on the next presence update of this user.

Next week we will look at how to report on this data we are collecting and some of the cool things we can do with it.

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