Features

This tutorials shows how to create a simple timesheet web application in ASP.NET MVC using DayPilot ASP.NET MVC scheduler control. The scheduler control supports a special timesheet view that displays one day per row.

  • Timesheet view (1 day per row)
  • Switching full day/business hours views
  • Employee filter using a drop-down list
  • Drag and drop event creating
  • Drag and drop event moving and resizing
  • Event editing using a modal dialog
  • Event text for recording details (like project name or job description)
  • Column with daily total (hours spent)
  • ASP.NET MVC 5
  • SQL Server sample database (LocalDB)
  • Visual Studio 2013 solution
  • C# source code
  • VB source code
  • Includes DayPilot Pro for ASP.NET MVC Trial version

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 MVC. Buy a license.

Displaying an Empty Timesheet

asp.net-mvc-5-timesheet-empty.png

Add the scheduler control to the view and switch it to the timesheet mode using ViewType=ViewType.Days:

C#

@Html.DayPilotScheduler("dps", new DayPilotSchedulerConfig
{
    BackendUrl = Url.Action("Backend", "Scheduler"),
    ViewType = ViewType.Days
})

VB

@Html.DayPilotScheduler("dps", New DayPilotSchedulerConfig With
{
    .BackendUrl = Url.Action("Backend", "Scheduler"),
    .ViewType = ViewType.Days
})

Add a backend controller action that will handle the AJAX requests:

C#

public class SchedulerController : Controller
{
  public ActionResult Backend()
  {
      return new Dps().CallBack(this);
  }

  class Dps : DayPilotScheduler
  {
      protected override void OnInit(InitArgs e)
      {
          UpdateWithMessage("Welcome!", CallBackUpdateType.Full);
      }
  }
}

VB

Public Class SchedulerController
  Inherits Controller

  Public Function Backend() As ActionResult
    Return (New Dps()).CallBack(Me)
  End Function

  Private Class Dps
    Inherits DayPilotScheduler

    Private dc As New TimesheetDataContext()

    Protected Overrides Sub OnInit(ByVal e As InitArgs)
      UpdateWithMessage("Welcome!", CallBackUpdateType.Full)
    End Sub

  End Class

End Class

Monthly Timesheet

asp.net-mvc-5-timesheet-monthly.png

In order to display the current month, set StartDate and Days properties in OnInit method:

C#

protected override void OnInit(InitArgs e)
{
  StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
  Days = DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month);
  UpdateWithMessage("Welcome!", CallBackUpdateType.Full);
}

VB

Protected Overrides Sub OnInit(ByVal e As InitArgs)
  StartDate = New Date(Date.Today.Year, Date.Today.Month, 1)
  Days = Date.DaysInMonth(Date.Today.Year, Date.Today.Month)
  UpdateWithMessage("Welcome!", CallBackUpdateType.Full)
End Sub

Timesheet Scale and Time Headers

asp.net-mvc-5-timesheet-hours-minutes.png

By default, the timesheet displays one cell per hour. We want to display the timesheet with 15-minute granularity. This can be set using CellDuration property of the config class:

C#

@Html.DayPilotScheduler("dps", new DayPilotSchedulerConfig
{
    BackendUrl = Url.Action("Backend", "Scheduler"),
    ViewType = ViewType.Days,
    CellDuration = 15
})

VB

@Html.DayPilotScheduler("dps", New DayPilotSchedulerConfig With
{
    .BackendUrl = Url.Action("Backend", "Scheduler"),
    .ViewType = ViewType.Days,
    .CellDuration = 15
})

We will also adjust the time header rows so that they display hours and minutes:

C#

@Html.DayPilotScheduler("dps", new DayPilotSchedulerConfig
{
    BackendUrl = Url.Action("Backend", "Scheduler"),
    ViewType = ViewType.Days,
    CellDuration = 15,
    TimeHeaders = new TimeHeaderCollection
    {
        new TimeHeader { GroupBy = GroupBy.Hour, Format = "h tt"},
        new TimeHeader { GroupBy = GroupBy.Cell, Format = "%m"}
    }
})

VB

@Html.DayPilotScheduler("dps", New DayPilotSchedulerConfig With
{
    .BackendUrl = Url.Action("Backend", "Scheduler"),
    .ViewType = ViewType.Days,
    .CellDuration = 15,
    .TimeHeaders = New TimeHeaderCollection From
    {
        New TimeHeader With { .GroupBy = GroupBy.Hour, .Format = "h tt"},
        New TimeHeader With { .GroupBy = GroupBy.Cell, .Format = "%m"}
    }
})

Timesheet Width

asp.net-mvc-5-timesheet-auto-width.png

By default, the cell width is set to 40 pixels. This makes the whole timesheet 3840 pixels wide (40 pixels * 24 hours * 4 cells per hour). We want the total width to match the available space so we will add CellWidthSpec = Auto option:

C#

@Html.DayPilotScheduler("dps", new DayPilotSchedulerConfig
{
    BackendUrl = Url.Action("Backend", "Scheduler"),
    // ...
    CellWidthSpec = CellWidthSpec.Auto
})

VB

@Html.DayPilotScheduler("dps", New DayPilotSchedulerConfig With
{
    .BackendUrl = Url.Action("Backend", "Scheduler"),
    ' ...
    .CellWidthSpec = CellWidthSpec.Auto
})

Loading Timesheet Data

We will use an SQL Server database with a simple schema:

[Employee] Table

CREATE TABLE [dbo].[Employee] (
    [EmployeeId]   INT            IDENTITY (1, 1) NOT NULL,
    [EmployeeName] NVARCHAR (100) NOT NULL
);

[Timesheet] Table

CREATE TABLE [dbo].[Timesheet] (
    [TimesheetId]   INT             IDENTITY (1, 1) NOT NULL,
    [TimesheetIn]   DATETIME        NULL,
    [TimesheetOut]  DATETIME        NULL,
    [EmployeeId]    INT             NOT NULL,
    [TimesheetText] NVARCHAR (2000) NULL
);

We will generate SQL to LINQ classes for these tables:

asp.net-mvc-5-timesheet-linq-to-sql.png

Next, we update the SchedulerController to load the timesheet data using LINQ:

C#

class Dps : DayPilotScheduler
{
    TimesheetDataContext dc = new TimesheetDataContext();

    // ...

    protected override void OnFinish()
    {
      if (UpdateType == CallBackUpdateType.None)
      {
          return;
      }

      Events = from e in dc.TimesheetRecords select e;

      DataIdField = "TimesheetId";
      DataTextField = "TimesheetText";
      DataStartField = "TimesheetIn";
      DataEndField = "TimesheetOut";
    }
    
}

VB

Private Class Dps
  Inherits DayPilotScheduler

  Private dc As New TimesheetDataContext()

  Protected Overrides Sub OnFinish()
    If UpdateType = CallBackUpdateType.None Then
      Return
    End If

    Events = From e In dc.TimesheetRecords Select e

    DataIdField = "TimesheetId"
    DataTextField = "TimesheetText"
    DataStartField = "TimesheetIn"
    DataEndField = "TimesheetOut"
  End Sub

End Class

The records are loaded to "Events" property and the database table fields are mapped using Data*Field properties.

The OnFinish() method is called at the end of every callback. Whenever we call Update() in one of the event handlers the OnFinish() method will automatically load the updated records and send them to the client side.

Employee Filter

asp.net-mvc-5-timesheet-employee-filter.png

We will use a simple drop-down list to filter the timesheet data by employee:

C#

<div class="space">
Employee: @Html.DropDownList("employee", new SelectList(ViewBag.Employees, "EmployeeId", "EmployeeName"))
</div>

VB

<div class="space">
Employee: @Html.DropDownList("employee", New SelectList(CType(ViewBag.Employees, IEnumerable), "EmployeeId", "EmployeeName"))
</div>

The list items are loaded in the HomeController class:

C#

public class HomeController : Controller
{
    public ActionResult Index()
    {
        TimesheetDataContext dc = new TimesheetDataContext();
        ViewBag.Employees = from e in dc.Employees orderby e.EmployeeName select e;
        return View();
    }
}

VB

Public Class HomeController
  Inherits Controller

  Public Function Index() As ActionResult
    Dim dc As New TimesheetDataContext()

    ViewBag.Employees = From e In dc.Employees _
                        Order By e.EmployeeName _
                        Select e

    Return View()
  End Function

End Class

The selected employee number will be stored in the ClientState. This property is synchronized with the server during every callback so we can use it to filter the data on the server side:

<script type="text/javascript">

    $("#employee").change(function () {
        dps.clientState.employee = $(this).val();
        dps.commandCallBack("refresh");
    });

    dps.clientState.employee = $("#employee").val();

</script>

We need to update the event loading code in OnFinish() to apply the filter:

C#

class Dps : DayPilotScheduler
{
  TimesheetDataContext dc = new TimesheetDataContext();

  // ...

  protected override void OnFinish()
  {
      if (UpdateType == CallBackUpdateType.None)
      {
          return;
      }

      int employee = SelectedEmployee();

      Events = from e in dc.TimesheetRecords where e.EmployeeId == employee select e;

      DataIdField = "TimesheetId";
      DataTextField = "TimesheetText";
      DataStartField = "TimesheetIn";
      DataEndField = "TimesheetOut";
  }

  private int SelectedEmployee()
  {
      if (ClientState["employee"] != null)
      {
          return Convert.ToInt32((string) ClientState["employee"]);
      }
      return (from e in dc.Employees orderby e.EmployeeName select e.EmployeeId).First();
  }
}

VB

Private Class Dps
  Inherits DayPilotScheduler

  Private dc As New TimesheetDataContext()
  
  ' ...

  Protected Overrides Sub OnFinish()
    If UpdateType = CallBackUpdateType.None Then
      Return
    End If

    Dim employee As Integer = SelectedEmployee()

    Events = From e In dc.TimesheetRecords _
             Where e.EmployeeId = employee _
             Select e

    DataIdField = "TimesheetId"
    DataTextField = "TimesheetText"
    DataStartField = "TimesheetIn"
    DataEndField = "TimesheetOut"
  End Sub

  Private Function SelectedEmployee() As Integer
    If ClientState("employee") IsNot Nothing Then
      Return Convert.ToInt32(CStr(ClientState("employee")))
    End If
    Return ( _
        From e In dc.Employees _
        Order By e.EmployeeName _
        Select e.EmployeeId).First()
  End Function
End Class

Business Hours

asp.net-mvc-5-timesheet-business-hours.png

The current view displays 24 hours in each row. Normally, business hours only take 8 hours. In such case a large part (2/3) of the timesheet is mostly empty so we will add an option to hide the non-business hours.

View: <a href="javascript:dps.commandCallBack('businessOnly');">business hours</a> | <a href="javascript:dps.commandCallBack('fullDay')">full day</a>

These hyperlinks will fire Command event on the server side:

C#

class Dps : DayPilotScheduler
{
  protected override void OnCommand(CommandArgs e)
  {
      switch (e.Command)
      {
          case "businessOnly":
              ShowNonBusiness = false;
              Update(CallBackUpdateType.Full);
              break;
          case "fullDay":
              ShowNonBusiness = true;
              Update(CallBackUpdateType.Full);
              break;
      }
  }
}

VB

Private Class Dps
  Inherits DayPilotScheduler
  
  ' ...

  Protected Overrides Sub OnCommand(ByVal e As CommandArgs)
    Select Case e.Command
      Case "refresh"
        Update(CallBackUpdateType.Full)
      Case "businessOnly"
        ShowNonBusiness = False
        Update(CallBackUpdateType.Full)
      Case "fullDay"
        ShowNonBusiness = True
        Update(CallBackUpdateType.Full)
    End Select
  End Sub

End Class

Summary Column

asp.net-mvc-5-timesheet-total-duration.png

We will add a special column that will display the total duration of all events in a given day. The columns can be defined in the MVC view:

C#

@Html.DayPilotScheduler("dps", new DayPilotSchedulerConfig
{
    BackendUrl = Url.Action("Backend", "Scheduler"),
    // ...
    HeaderColumns = new RowHeaderColumnCollection
    {
        new RowHeaderColumn("Day", 100),
        new RowHeaderColumn("Total", 100)
    }
})

VB

@Html.DayPilotScheduler("dps", New DayPilotSchedulerConfig With
{
    .BackendUrl = Url.Action("Backend", "Scheduler"),
    ' ...
    .HeaderColumns = New RowHeaderColumnCollection From
    {
        New RowHeaderColumn("Day", 100),
        New RowHeaderColumn("Total", 100)
    }
})

And calculate the data in the MVC controller, in BeforeResHeaderRender event:

C#

class Dps : DayPilotScheduler
{
    TimesheetDataContext dc = new TimesheetDataContext();
    
    // ...

    protected override void OnBeforeResHeaderRender(BeforeResHeaderRenderArgs e)
    {
        if (e.Columns.Count > 0)
        {
            var start = e.Date;
            var end = start.AddDays(1);
            var employee = SelectedEmployee();

            TimeSpan total = TimeSpan.Zero;
            (from tr in dc.TimesheetRecords where tr.EmployeeId == employee && !((tr.TimesheetOut <= start) || (tr.TimesheetIn >= end)) select tr).ToList().ForEach(tr => total = total.Add(tr.TimesheetOut - tr.TimesheetIn));

            e.Columns[0].Html = total.ToString("hh\\:mm");
        }

    }
    
}

VB

Private Class Dps
  Inherits DayPilotScheduler

  Private dc As New TimesheetDataContext()
  
  ' ...

  Protected Overrides Sub OnBeforeResHeaderRender(ByVal e As BeforeResHeaderRenderArgs)
    If e.Columns.Count > 0 Then
      Dim start = e.Date
      Dim [end] = start.AddDays(1)
      Dim employee = SelectedEmployee()

                Dim total As TimeSpan = TimeSpan.Zero
                Dim records = (From tr In dc.TimesheetRecords _
                    Where tr.EmployeeId = employee AndAlso Not ((tr.TimesheetOut <= start) OrElse (tr.TimesheetIn >= [end])) _
                    Select tr)
                records.ToList().ForEach(Sub(tr) total = total.Add(tr.TimesheetOut - tr.TimesheetIn))

      e.Columns(0).Html = total.ToString("hh\:mm")
    End If

  End Sub

End Class