Shift Scheduling Tutorial (ASP.NET, SQL Server, C#, VB.NET)

Downloads

• TutorialShiftScheduling.VS2010.20130629.zip 1228 kB
  Visual Studio 2010 solution with the tutorial source code. Microsoft SQL Server 2008 database. Includes C# and VB projects.

Sample Project

The sample project includes:

• DayPilot Pro 7.3.2871 Trial
• C# Source Code
• VB.NET Source Code
• Visual Studio 2010 Solution

Online Demo

• Shift Scheduling Demo

Shift Scheduling Tutorial (ASP.NET, SQL Server, C#, VB.NET)

This tutorial shows how to display a shift schedule for multiple  locations/positions.

Features:

• Overview page displaying all locations and assignments
• Easy assignment creating and moving using drag&drop
• Recurring assignments
• Asynchronouse detection of conflicting assignments

1. Shift Schedule Overview Page

The shift schedule overview page uses DayPilot Scheduler to show assignments  for all locations.

Locations are displayed on the Y axis.

C#

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    LoadResources();
    DayPilotScheduler1.StartDate = Week.FirstDayOfWeek(DateTime.Now);
    DayPilotScheduler1.DataSource = new DataManager().GetAssignments(DayPilotScheduler1);
    DayPilotScheduler1.DataBind();

    DayPilotScheduler1.SetScrollX(DateTime.Today);
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  If Not IsPostBack Then
    LoadResources()
    DayPilotScheduler1.StartDate = Week.FirstDayOfWeek(Date.Now)
    DayPilotScheduler1.DataSource = (New DataManager()).GetAssignments(DayPilotScheduler1)
    DayPilotScheduler1.DataBind()

    DayPilotScheduler1.SetScrollX(Date.Today)
  End If
End Sub

The Resources collection is filled using data from  [Location] table:

C#

private void LoadResources()
{
  DataTable locations = new DataManager().GetLocations();
  DayPilotScheduler1.Resources.Clear();
  foreach(DataRow dr in locations.Rows)
  {
    DayPilotScheduler1.Resources.Add((string)dr["LocationName"], Convert.ToString(dr["LocationId"]));
  }
} 

VB.NET

Private Sub LoadResources()
  Dim locations As DataTable = (New DataManager()).GetLocations()
  DayPilotScheduler1.Resources.Clear()
  For Each dr As DataRow In locations.Rows
    DayPilotScheduler1.Resources.Add(CStr(dr("LocationName")), Convert.ToString(dr("LocationId")))
  Next dr
End Sub

2. Assignments by Location

This page shows assignment for a location selected using a  DropDownList.

It uses delayed loading - the selected location is detected  on the client side using jQuery and a calendar refresh is requested:

<script type="text/javascript">
  function updateCalendar() {
    dp.clientState.location = parseInt($("#MainContent_DropDownListLocation").val());
    dp.commandCallBack('refresh');
  }

  $(document).ready(function () {
    updateCalendar();
  });
</script>

The selected location is stored in the ClientState property (ClientState["location"]) and is persisted during the subsequent callbacks.

The ClientState is updated and a refresh is requested whenever the users  selects a new location using the DropDownList (see onchange attribute).

 <div>Location: 
  <asp:DropDownList 
    ID="DropDownListLocation" 
    runat="server"
    DataTextField="LocationName"
    DataValueField="LocationId"
    onchange="updateCalendar();"
  />
</div> 

The JavaScript code uses commandCallBack() method to fire Command event on the server side. 

C#

protected void DayPilotCalendar1_Command(object sender, CommandEventArgs e)
{
  switch (e.Command)
  {
    // ...
    case "refresh":
      DayPilotCalendar1.DataSource = new DataManager().GetAssignmentsForLocation(DayPilotCalendar1);
      DayPilotCalendar1.DataBind();
      DayPilotCalendar1.Update();
      break;
    // ...
  }
}

VB.NET

Protected Sub DayPilotCalendar1_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
  Select Case e.Command
    '...
    Case "refresh"
      DayPilotCalendar1.DataSource = (New DataManager()).GetAssignmentsForLocation(DayPilotCalendar1)
      DayPilotCalendar1.DataBind()
      DayPilotCalendar1.Update()
    End Select
End Sub

The selected location is loaded from the ClientState property in GetAssignmentsForLocation() method:

C#

public DataTable GetAssignmentsForLocation(DayPilotCalendar calendar)
{
  DataTable dt = new DataTable();

  // ...

  SqlDataAdapter da = new SqlDataAdapter("select * from [Assignment] join [Person] on [Assignment].[PersonId] = [Person].[PersonId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [LocationId] = @location and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate);
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1));
  da.SelectCommand.Parameters.AddWithValue("location", (int) calendar.ClientState["location"]);
  da.Fill(dt);
  return dt;
}

VB.NET

Public Function GetAssignmentsForLocation(ByVal calendar As DayPilotCalendar) As DataTable
  Dim dt As New DataTable()

  ' ...

  Dim da As New SqlDataAdapter("select * from [Assignment] join [Person] on [Assignment].[PersonId] = [Person].[PersonId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [LocationId] = @location and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate)
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1))
  da.SelectCommand.Parameters.AddWithValue("location", CInt(calendar.ClientState("location")))
  da.Fill(dt)
  Return dt
End Function

3. Assignments by Person

This page shows assignments for the selected person (DropDownList) in a week view using DayPilot Calendar control.

Again, we are storing the selected person id in the ClientState and calling  commandCallBack('refresh') after the first page load.

<script type="text/javascript">
  function updateCalendar() {
    dp.clientState.person = parseInt($("#MainContent_DropDownListPerson").val());
    dp.commandCallBack('refresh');
  }

  $(document).ready(function () {
    updateCalendar();
  });
</script>

We send a request for update after every DropDownListPerson  change:

 <div>Person: 
  <asp:DropDownList 
    ID="DropDownListPerson" 
    runat="server"
    DataTextField="PersonFullName"
    DataValueField="PersonId"
    onchange="updateCalendar();"
/>
</div> 

The Command event handler reloads the events.

C#

protected void DayPilotCalendar1_Command(object sender, CommandEventArgs e)
{
  switch (e.Command)
  {
    // ...
    case "refresh":
      DayPilotCalendar1.DataSource = new DataManager().GetAssignmentsForPerson(DayPilotCalendar1);
      DayPilotCalendar1.DataBind();
      DayPilotCalendar1.Update();
      break;
  }
}

VB.NET

Protected Sub DayPilotCalendar1_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
  Select Case e.Command
    ' ...
    Case "refresh"
      DayPilotCalendar1.DataSource = (New DataManager()).GetAssignmentsForPerson(DayPilotCalendar1)
      DayPilotCalendar1.DataBind()
      DayPilotCalendar1.Update()

  End Select
End Sub

The selected person id is loaded from the ClientState:

C#

public DataTable GetAssignmentsForPerson(DayPilotCalendar calendar)
{
  DataTable dt = new DataTable();

  // ...

  SqlDataAdapter da = new SqlDataAdapter("select * from [Assignment] join [Location] on [Assignment].[LocationId] = [Location].[LocationId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [PersonId] = @person and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate);
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1));
  da.SelectCommand.Parameters.AddWithValue("person", (int)calendar.ClientState["person"]);
  da.Fill(dt);
  return dt;
}

VB.NET

Public Function GetAssignmentsForPerson(ByVal calendar As DayPilotCalendar) As DataTable
  Dim dt As New DataTable()

  ' ...

  Dim da As New SqlDataAdapter("select * from [Assignment] join [Location] on [Assignment].[LocationId] = [Location].[LocationId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [PersonId] = @person and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate)
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1))
  da.SelectCommand.Parameters.AddWithValue("person", CInt(calendar.ClientState("person")))
  da.Fill(dt)
  Return dt
End Function

4. Agenda

Agenda is a simple GridView of the upcoming assignments for  the selected person.

<div>Person: 
  <asp:DropDownList 
    ID="DropDownListPerson" 
    runat="server"
    DataTextField="PersonFullName"
    DataValueField="PersonId" 
    onselectedindexchanged="DropDownListPerson_SelectedIndexChanged"
    AutoPostBack="true"
    AppendDataBoundItems="true">
  </asp:DropDownList>
</div> 

<asp:GridView ID="GridViewAgenda" runat="server" AutoGenerateColumns="False">
  <Columns>
    <asp:BoundField DataField="AssignmentStart" HeaderText="Start" />
    <asp:BoundField DataField="AssignmentEnd" HeaderText="End" />
    <asp:BoundField DataField="LocationName" HeaderText="Location" />
    <asp:BoundField DataField="AssignmentNote" HeaderText="Note" />
  </Columns>
</asp:GridView>

It is updated using traditional PostBack for simplicity:

C#

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    DropDownListPerson.DataSource = new DataManager().GetPeople();

    if (IsSelected)
    {
      DropDownListPerson.SelectedValue = Request.QueryString["id"];
    }
    else
    {
      DropDownListPerson.SelectedIndex = 0;
    }
    DropDownListPerson.DataBind();

    DataTable dt = new DataManager().GetAssignmentsForPersonExpanded(PersonId, DateTime.Now, DateTime.Now.AddMonths(1));
    GridViewAgenda.DataSource = dt;

    Label1.Text = dt.Rows.Count.ToString();

    GridViewAgenda.DataBind();
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  If Not IsPostBack Then
    DropDownListPerson.DataSource = (New DataManager()).GetPeople()

    If IsSelected Then
      DropDownListPerson.SelectedValue = Request.QueryString("id")
    Else
      DropDownListPerson.SelectedIndex = 0
    End If
    DropDownListPerson.DataBind()

    Dim dt As DataTable = (New DataManager()).GetAssignmentsForPersonExpanded(PersonId, Date.Now, Date.Now.AddMonths(1))
    GridViewAgenda.DataSource = dt

    Label1.Text = dt.Rows.Count.ToString()

    GridViewAgenda.DataBind()
  End If
End Sub

5. Conflict Warning

Conflict means there are two or more concurrent assignments for the same  person.

The conflicts are not being detected before an assignment change is stored in  the database but asynchronously after every page call. Pages  with DayPilot controls run the afterRender() function using AfterRenderJavaScript:

<DayPilot:DayPilotScheduler
  ID="DayPilotScheduler1" 
  runat="server" 
  ...
  AfterRenderJavaScript="afterRender();"
/>

All other pages run it using jQuery:

 <script type="text/javascript">
  $(document).ready(function () {
    afterRender();
  });
</script>

The afterRender() function sends an AJAX request to  ConflictCount.aspx and updates the menu with the returned HTML.

function afterRender() {
  $.get('ConflictCount.aspx', function (data) {
    $('#conflicts').html(data);
  });
}

ConflictCount.aspx uses the ConflictDetector class to check  for blocks of overlapping events and return the number of conflicts.

C#

protected void Page_Load(object sender, EventArgs e)
{
  Response.Cache.SetCacheability(HttpCacheability.NoCache);

  DateTime start = DateTime.Today;
  DateTime end = DateTime.Today.AddMonths(1);

  DataTable data = new DataManager().GetAssignmentsExpanded(start, end);
  var detector = new ConflictDetector();
  detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, end);

  Response.Clear();
  if (detector.Count > 0)
  {
    Response.Write(String.Format("<span style='background-color:red; color:white;'>({0})</span>", detector.Count));
  }
  else
  {
    Response.Write(String.Format("<span>({0})</span>", detector.Count));
  }
} 

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  Response.Cache.SetCacheability(HttpCacheability.NoCache)

  Dim start As Date = Date.Today
  Dim [end] As Date = Date.Today.AddMonths(1)

  Dim data As DataTable = (New DataManager()).GetAssignmentsExpanded(start, [end])
  Dim detector = New ConflictDetector()
  detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, [end])

  Response.Clear()
  If detector.Count > 0 Then
    Response.Write(String.Format("<span style='background-color:red; color:white;'>({0})</span>", detector.Count))
  Else
    Response.Write(String.Format("<span>({0})</span>", detector.Count))
  End If
End Sub

6. List of Conflicts

The Conflicts page uses ConflictDetector class to analyze a data set  for overlapping events.

C#

protected void Page_Load(object sender, EventArgs e)
{

  if (!IsPostBack)
  {
    DateTime start = DateTime.Today;
    DateTime end = DateTime.Today.AddMonths(1);

    DataTable data = new DataManager().GetAssignmentsExpanded(start, end);

    var detector = new ConflictDetector();
    detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, end);
    GridView1.DataSource = detector.List;

    Label1.Text = detector.List.Count.ToString();
    DataBind();
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

  If Not IsPostBack Then
    Dim start As Date = Date.Today
    Dim [end] As Date = Date.Today.AddMonths(1)

    Dim data As DataTable = (New DataManager()).GetAssignmentsExpanded(start, [end])

    Dim detector = New ConflictDetector()
    detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, [end])
    GridView1.DataSource = detector.List

    Label1.Text = detector.List.Count.ToString()
    DataBind()
  End If
End Sub

The ConflictDetector accepts a DataTable with event records.  You need to specify the column with assignment start ("AssignmentStart"),  assignment end ("AssignmentEnd"), and with resource identifier ("PersonId"). It  will check for overlapping events with the same resource id, within the range  specified by "start" and "end" variables.

ConflictDetector.List holds a list of Conflict classes which  contain details about the conflicts:

public class Conflict
{
  public List<ConflictEvent> Events;  // List of conflicting events
  public DateTime Start; // Start of the conflict (start of the first conflicting event in this block).
  public DateTime End; // End of the conflict (end of the last conflicting event in this block).
  public int Level; // Conflict level. The max parallel event count.
  public string Resource; // Resource id.
}

ConflictEvent class has information about the source event:

 public class ConflictEvent
{
  public DateTime Start; // Event start
  public DateTime End;  // Event end
  public string Value; // Event id
  public int Position; // Position of the event in a block of overlapping events, corresponds to the column number when displayed in the Calendar control
  public DataItemWrapper DataItem; // Source data object (DataRow)
}

The detected conflicts are displayed using a simple GridView:

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
  <Columns>
    <asp:TemplateField HeaderText="Conflict">
      <ItemTemplate>
        <a href='People.aspx?person=<%# Eval("Resource") %>&date=<%# Convert.ToDateTime(Eval("Start")).ToString("s") %>'>Show</a>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Person">
      <ItemTemplate>
        <%# Eval("Events[0].DataItem.Source[\"PersonFullName\"]")%>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="Start" HeaderText="Start" />
    <asp:BoundField DataField="End" HeaderText="End" />
    <asp:TemplateField HeaderText="Events in Conflict">
      <ItemTemplate>
        <%# Eval("Events.Count") %>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>

8. Recurring Events

Adding recurrence support complicated the situation on all levels. Allowing  recurring events requires special handling at these places:

1. Adding [AssignmentRecurrence] field to the [Assignment] database table.
2. Extending the New.aspx and Edit.aspx dialogs with the recurrence rule definition support (UI and database storage).
3. Extending all database loading methods to load recurrence definitions. Regular events can be loaded using the StartDate and EndDate range but the recurrence definitions must be loaded from the past as well.
4. Detecting the recurring events in the update events (EventResize, EventMove).
5. Expanding the rules into individual occurrences manually before showing the data in a GridView (Agenda.aspx).

For simplicity, the exceptions from the recurrence rule and not allowed.

See also:

• Recurring Events Tutorial (Calendar Control)

7. Database Schema (SQL Server)