Tutorial License

Licensed for testing and evaluation purposes. You can use the source code of the tutorial if you are a licensed user of DayPilot Pro for ASP.NET WebForms.

Annual Leave Booking Features

  • Year view

  • One person per row

  • Annual leaves in 0.5 day units

  • Summary column with totals

  • Modal dialog for creating and editing the bookings

Requirements

  • .NET Framework 4.0+

  • Visual Studio

  • SQL Server 2014+

Database Setup

This tutorial includes a sample SQL Server database (daypilot.mdf).

The database contains two tables:

  • Person

  • Reservation

The Reservation table stores the events (annual leave data).

CREATE TABLE [Reservation](
  [ReservationId] [int] IDENTITY(1,1) NOT NULL,
  [PersonId] [int] NOT NULL,
  [ReservationStart] [datetime] NOT NULL,
  [ReservationEnd] [datetime] NOT NULL
);

The Person table stores the person details.

CREATE TABLE [Room](
  [PersonId] [int] IDENTITY(1,1) NOT NULL,
  [PersonFirst] [nvarchar](100) NOT NULL,
  [PersonLast] [nvarchar](100) NOT NULL
);

Scheduler Control

We will use DayPilot ASP.NET Scheduler control to display the annual leave data.

First, we add the Scheduler control tag to the page. We will use the default built-in CSS theme but you can create your own theme using the online theme designer.

<DayPilot:DayPilotScheduler 
  ID="DayPilotScheduler1" 
  runat="server" 

  </DayPilot:DayPilotScheduler>

Loading People

Now we will load people from the [Person] table and display them as Scheduler rows.

protected void Page_Load(object sender, EventArgs e)
{
  // ...
  LoadResources();
}


private void LoadResources()
{
  DayPilotScheduler1.Resources.Clear();

  DateTime start = DayPilotScheduler1.StartDate;
  DateTime end = DayPilotScheduler1.EndDate;

  SqlDataAdapter da = new SqlDataAdapter("SELECT [PersonId], [PersonFirst], [PersonLast] FROM [Person] ORDER BY [PersonLast], [PersonFirst]", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("start", start);
  da.SelectCommand.Parameters.AddWithValue("end", end);
  DataTable dt = new DataTable();
  da.Fill(dt);

  foreach (DataRow r in dt.Rows)
  {
      string first = (string)r["PersonFirst"];
      string last = (string)r["PersonLast"];
      string id = Convert.ToString(r["PersonId"]);

      Resource res = new Resource(last + ", " + first, id);
      res.DataItem = r;

      DayPilotScheduler1.Resources.Add(res);
  }
}

Scheduler Time Scale

We will set the Scheduler to display the current year using StartDate and Days properties:

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
      DayPilotScheduler1.StartDate = new DateTime(DateTime.Today.Year, 1, 1);
      DayPilotScheduler1.Days = Year.Days(DateTime.Today.Year);
  }
}

The cell duration will be set to 720 minutes to allow morning and afternoon bookings.

The time headers rows will be grouped by month (the first row) and day (the second row).

<DayPilot:DayPilotScheduler 
  ID="DayPilotScheduler1" 
  runat="server" 
  
  CellDuration="720" 
  CellWidth="20"
  >
  <TimeHeaders>
      <DayPilot:TimeHeader GroupBy="Month" />
      <DayPilot:TimeHeader GroupBy="Day" />
  </TimeHeaders>
</DayPilot:DayPilotScheduler>

We will highlight the day start and end using custom separators.

protected void Page_Load(object sender, EventArgs e)
{
  // ...
  LoadSeparators();
}

private void LoadSeparators()
{
  DayPilotScheduler1.Separators.Clear();
  for (int i = 0; i < DayPilotScheduler1.Days; i++)
  {
      DateTime start = DayPilotScheduler1.StartDate.AddDays(i);
      DayPilotScheduler1.Separators.Add(start, ColorTranslator.FromHtml("#cccccc"));
  }

  // and one more separator for today
  DayPilotScheduler1.Separators.Add(DateTime.Today, ColorTranslator.FromHtml("#ffaaaa"));

}

It is possible to hide the half-day grid lines by modifying the CSS theme. Comment out or modify the .scheduler_white_matrix_vertical_line style definition:

/*
.scheduler_white_matrix_vertical_line 
{
  background-color: #eee;
}
*/

Loading the Annual Leave Bookings

Add the column mappings (id, start, end, text, resource) to the DayPilotScheduler tag:

<DayPilot:DayPilotScheduler 
  ID="DayPilotScheduler1" 
  runat="server" 
  
  DataStartField="ReservationStart" 
  DataEndField="ReservationEnd" 
  DataTextField="ReservationId" 
  DataValueField="ReservationId" 
  DataResourceField="PersonId" 
  
  >
</DayPilot:DayPilotScheduler> 

Load the bookings and set the DataSource.

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
      DayPilotScheduler1.DataSource = dbGetEvents(DayPilotScheduler1.StartDate, DayPilotScheduler1.Days);
      DayPilotScheduler1.DataBind();
  }
}

private DataTable dbGetEvents(DateTime start, int days)
{
  SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [Reservation] WHERE NOT (([ReservationEnd] <= @start) OR ([ReservationStart] >= @end))", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("start", start);
  da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days));
  DataTable dt = new DataTable();
  da.Fill(dt);
  return dt;
}

Summary Column

Define the row header columns:

<DayPilot:DayPilotScheduler 
  ID="DayPilotScheduler1" 
  runat="server" 
  ...
  >
  <HeaderColumns>
      <DayPilot:RowHeaderColumn Title="Person" Width="80" />
      <DayPilot:RowHeaderColumn Title="Total"  Width="80" />
  </HeaderColumns>
</DayPilot:DayPilotScheduler>

Update the people SELECT command to load the annual leave total in the selected time period:

SELECT 
  [Person].[PersonId], 
  [Person].[PersonFirst], 
  [Person].[PersonLast], 
  sum(datediff(minute, [ReservationStart], [ReservationEnd])) as [Total] 
FROM 
  [Person] 
  left outer join [Reservation] on 
    [Person].[PersonId] = [Reservation].[PersonId] and NOT (([ReservationEnd] <= @start) OR ([ReservationStart] >= @end)) 
GROUP BY 
  [Person].[PersonId], 
  [Person].[PersonFirst], 
  [Person].[PersonLast] 
ORDER BY 
  [PersonLast], 
  [PersonFirst]

Modify the LoadResources() method to display the total in the additional row column:

private void LoadResources()
{
  DayPilotScheduler1.Resources.Clear();

  DateTime start = DayPilotScheduler1.StartDate;
  DateTime end = DayPilotScheduler1.EndDate;

  SqlDataAdapter da = new SqlDataAdapter("SELECT [Person].[PersonId], [Person].[PersonFirst], [Person].[PersonLast], sum(datediff(minute, [ReservationStart], [ReservationEnd])) as [Total] FROM [Person] left outer join [Reservation] on [Person].[PersonId] = [Reservation].[PersonId] and NOT (([ReservationEnd] <= @start) OR ([ReservationStart] >= @end)) GROUP BY [Person].[PersonId], [Person].[PersonFirst], [Person].[PersonLast] ORDER BY [PersonLast], [PersonFirst]", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("start", start);
  da.SelectCommand.Parameters.AddWithValue("end", end);
  DataTable dt = new DataTable();
  da.Fill(dt);

  foreach (DataRow r in dt.Rows)
  {
      string first = (string)r["PersonFirst"];
      string last = (string)r["PersonLast"];
      string id = Convert.ToString(r["PersonId"]);
      double totalMin = 0;
      if (!r.IsNull("Total"))
      {
          object to = r["Total"];
          totalMin = (int)r["Total"];
      }
      TimeSpan total = TimeSpan.FromMinutes(totalMin);

      Resource res = new Resource(last + ", " + first, id);
      res.DataItem = r;

      res.Columns.Add(new ResourceColumn(String.Format("{0:0.0} days", total.TotalDays)));

      DayPilotScheduler1.Resources.Add(res);
  }

}