Get available time intervals for an employee

Published May 03, 2018Last updated May 14, 2018

Introduction
It is quite common to have an entity in the application, for example an employee, who has a certain working scheduler and to be able to show this scheduler in the user interface, like an Outlook calendar for example. Some client third parties used for displaying Outlook-like calendars request some time intervals in where the entity(employee) is available and gray out the rest of intervals.
In this article I will show how to obtain the available time intervals which could be used as data feed for a client third party, for example FullCalendar.io.
Let’s name the working application entity – employee.
All the code snippets are written using C#.

What is our goal?

Usually we store in data base the scheduling working template for an employee and this template refers a specific day and a time interval in where he/she is available or not. So you could have, for example, an employee who works every Monday from 09:00 to 18:00 but has a lunch break between 13:00 and 14:00. The goal in this case is to intersect the entire working period with the period in which the employee is not available and to obtain just time intervals in where the employee is available:

  1. From 09:00 – 13:00
  2. From 14:00 – 18:00
    We need to get available intervals for all existing employee’s templates.
    Using FullCalendar.io we need to obtain a user interface like this assuming that the worker has the following working template:
  3. Monday from 08:40 – 17:00
  4. Tuesday from 09:00 – 17:00
  5. Friday from 08:00 – 14:30

Untitled.png

Prepare data structures
We need to be able to transmit resulting data to the client third party responsible with rendering so we need to create an entity like the one below:
public class BusinessHours
{
public string start { get; set; }

    public string end { get; set; }

    private List<int> _dow;
    public List<int> dow
    {
        get
        {
            if (_dow == null)
            {
                return new List<int>();
            }
            return _dow;
        }
        set
        {
            _dow = value;
        }
    }

}
This class holds the formatted start/end time of the available interval together with a list of DayOfWeek.
The employee’s working template is got from data base in a flat collection of entities called “CalendarWorkTimeEntity” which has the following important, for this case, properties:
public class CalendarWorkTimeEntity
{
public System.Int32 CalendarWorkTimeId
{
get
{
return this._CalendarWorkTimeId;
}
set
{
this._CalendarWorkTimeId = value;
}
}
public System.DateTime Date
{
get
{
return this._Date;
}
set
{
this._Date = value;
}
}
public System.Int32 Day
{
get
{
return this._Day;
}
set
{
this._Day = value;
}
}
public System.DateTime EndTime
{
get
{
return this._EndTime;
}
set
{
this._EndTime = value;
}
}
public System.DateTime StartTime
{
get
{
return this._StartTime;
}
set
{
this._StartTime = value;
}
}
public System.Int32 WorkTimeTypeId
{
get
{
return this._WorkTimeTypeId;
}
set
{
this._WorkTimeTypeId = value;
}
}
}
CalendarWorkTimeId represents the primary key from data base, Date it’s used in relation with WorkTimeTypeId which represents the type of interval like Available or Not Available, Day represents the Day Of Week index value(in this special case I am writing about, the week start from Monday so 0==Monday and so on), EndTime and StartTime represents the ends of working time.

Implement the solution

We have as input a flat collection of working templates, which could contain also intervals in which the employee is not available. We need to transform the input collection in a collection of intervals in where the employee is available.
For that we need to iterate through input templates and try to obtain available intervals for every working template.
For a specific template we know that it’s applicable to a one day of week (through Day property), it has a type (Available or not) and time endings (StartTime, EndTime or Date).
We start with one template which is of type available (WorkTimeTypeId==Available) and try to find other different templates for the same day as the current one but which are not available (WorkTimeTypeId!=Available) and we store them in a temporary collection.
Then traverse each minute from available interval and check if it is included in a not available interval. In this way we create segments of availability which don’t intersect with segments of not availability. The C# code snippet is the following:
private List<BusinessHours> CreateBusinessHoursWithGaps(CalendarWorkTimeEntity availableTimeInterval, List<CalendarWorkTimeEntity> notAvailableGaps)
{
List<BusinessHours> businessHours = new List<BusinessHours>();
var startWorkerAvailableInterval = availableTimeInterval.StartTime;
var endWorkerAvailableInterval = availableTimeInterval.EndTime;
BusinessHours pivotBusinessInterval = null;
for (DateTime timeIncrementor = startWorkerAvailableInterval; timeIncrementor < endWorkerAvailableInterval; timeIncrementor = timeIncrementor.AddMinutes(1))
{
bool isWorkerAvailableInCurrentTime = IsCurrentTimeAnAvailableTimeSlot(timeIncrementor, notAvailableGaps);
if (isWorkerAvailableInCurrentTime && pivotBusinessInterval == null)
{
pivotBusinessInterval = new BusinessHours()
{
dow = new List<int>() { GetDayOfWeek(availableTimeInterval.Day) }
};
SetupStartBussinesInterval(startWorkerAvailableInterval, pivotBusinessInterval, timeIncrementor);
businessHours.Add(pivotBusinessInterval);
}
if (!isWorkerAvailableInCurrentTime && pivotBusinessInterval != null)
{
SetupEndBusinessInterval(pivotBusinessInterval, timeIncrementor);
pivotBusinessInterval = null;
}
}
if (pivotBusinessInterval != null && pivotBusinessInterval.end == null)
{
pivotBusinessInterval.end = availableTimeInterval.EndTime.TimeOfDay.ToString(FULL_CALENDAR_BUSINESS_HOURS_FORMAT);
}
return businessHours;
}
private bool IsCurrentTimeAnAvailableTimeSlot(DateTime timeIncrementor, List<CalendarWorkTimeEntity> notAvailableGaps)
{
return !notAvailableGaps.Any(p => timeIncrementor.TimeOfDay >= p.StartTime.TimeOfDay && timeIncrementor.TimeOfDay <= p.EndTime.TimeOfDay);
}

private void SetupStartBussinesInterval(DateTime startWorkerAvailableInterval, BusinessHours pivotBusinessInterval, DateTime timeIncrementor)
{
if (timeIncrementor.TimeOfDay > startWorkerAvailableInterval.TimeOfDay)
{
pivotBusinessInterval.start = timeIncrementor.AddMinutes(-1).TimeOfDay.ToString(FULL_CALENDAR_BUSINESS_HOURS_FORMAT);
}
else
{
pivotBusinessInterval.start = timeIncrementor.TimeOfDay.ToString(FULL_CALENDAR_BUSINESS_HOURS_FORMAT);
}
}
private void SetupEndBusinessInterval(BusinessHours pivotBusinessInterval, DateTime timeIncrementor)
{
pivotBusinessInterval.end = timeIncrementor.TimeOfDay.ToString(FULL_CALENDAR_BUSINESS_HOURS_FORMAT);
}

So we iterate through availability interval minute by minute, we check if the current minute is inside a forbidden interval and if it’s not, we adjust the availability segment (local variable called “pivotBusinessinterval”. When the current minute is inside a forbidden interval, we complete the segment and reinitialize it. When a further minute is available, then we setup the start of segment and we increment the minute.
After we reached the end of interval and we have the segment not ended, we end it with the current end of interval.

Unit tests

For being able to test the algorithm, we need to create several unit tests with all kind of scenarios and since the method could be a state method, we used a trick by altering it’s access modifier from “private” to “protected” and we created, in the unit test class, a child class which rewrites the method but it is “public” and can be called from a unit test. The code snippet is below:
public class TestingWorkerSchedulerController : WorkerSchedulerController

public new List<BusinessHours> GetWorkerBusinessHours(List<CalendarWorkTimeEntity> workerCalendarWorkTime)
{
return base.GetWorkerBusinessHours(workerCalendarWorkTime);
}

One the calling unit tests look like this:
[TestMethod, TestCategory("UnitTest")]
public void TestGetWorkerBusinessHours_TwoIntervalsOneNotAvailableGap()
{
List<CalendarWorkTimeEntity> workerAvailability = new List<CalendarWorkTimeEntity>()
{
new CalendarWorkTimeEntity()
{
Day = 1,
StartTime =new DateTime(2017,1,1,9,0,0),
EndTime =new DateTime(2017,1,1,13,00,0),
WorkTimeTypeId =(int)CalendarAdjustmentType.Working,
CalendarWorkTimeId = 2
},
new CalendarWorkTimeEntity()
{
Day = 1,
StartTime =new DateTime(2017,1,1,14,0,0),
EndTime =new DateTime(2017,1,1,17,00,0),
WorkTimeTypeId =(int)CalendarAdjustmentType.Working,
CalendarWorkTimeId = 3
},
new CalendarWorkTimeEntity()
{
Day = 1,
StartTime =new DateTime(2017,1,1,13,0,0),
EndTime =new DateTime(2017,1,1,14,00,0),
WorkTimeTypeId =(int)CalendarAdjustmentType.Busy,
CalendarWorkTimeId = 4
}
};

        TestingWorkerSchedulerController controller = new TestingWorkerSchedulerController();
        var workerBusinessHours = controller.GetWorkerBusinessHours(workerAvailability);
        Assert.IsTrue(workerBusinessHours.Count == 2);

        CheckBusinessHoursInterval(workerBusinessHours[0], DayOfWeek.Tuesday, 9, 0, 13, 0);
        CheckBusinessHoursInterval(workerBusinessHours[1], DayOfWeek.Tuesday, 14, 0, 17, 0);
    }
Discover and read more posts from Vlad Daraban
get started