Features

  • JavaScript/HTML5 monthly timesheet, one day per row
  • Uses DayPilot JavaScript Scheduler control
  • Displays a timesheet for multiple users - you can switch the employee using a <select> control
  • Displays day summary (total time) in day headers
  • Highlights business hours
  • Sample PHP backend with support for MySQL/SQLite database
  • Compatible with jQuery
  • Drag and drop record creating, moving and resizing

License

Licensed for testing and evaluation purposes. Please see the license agreement included in the sample project. You can use the source code of the tutorial if you are a licensed user of DayPilot Pro for JavaScript. Buy a license.

Timesheet Initialization

javascript-html5-timesheet-initialization.png

We will display the timesheet using DayPilot Scheduler switched to timesheet view (viewType="Days"). The timesheet view displays days on the vertical (Y) axis and hours of day on the horizontal (X) axis.

<div id="dp"></div>

<script type="text/javascript">
  var dp = new DayPilot.Scheduler("dp");
  dp.viewType = "Days";
  dp.init();
</script>

This code will display the timesheet using the default configuration:

  • A single row showing today.
  • Fixed cell width (40 pixels).

Monthly Timesheet

javascript-html5-timesheet-monthly-view.png

We want to adjust the timesheet to display the current month. We will use startDate and days properties:

<div id="dp"></div>

<script type="text/javascript">
  var dp = new DayPilot.Scheduler("dp");
  dp.viewType = "Days";

  dp.startDate = new DayPilot.Date().firstDayOfMonth();
  dp.days = dp.startDate.daysInMonth();

  dp.init();
</script>

Timesheet Headers

javascript-html5-timesheet-time-headers.png

The first row of the X axis header displays the first day (9/1/2019). We will modify the timesheet headers to display the current month (September 2019) in the first row and hours in the second row.

<div id="dp"></div>

<script type="text/javascript">
  var dp = new DayPilot.Scheduler("dp");
  dp.viewType = "Days";

  dp.startDate = new DayPilot.Date().firstDayOfMonth();
  dp.days = dp.startDate.daysInMonth();

  dp.timeHeaders = [
    { groupBy: "Day", format: "MMMM yyyy" },
    { groupBy: "Hour"}
  ];

  dp.init();
</script>

Loading List of Employees from MySQL Database

javascript-html5-timesheet-loading-employees-php-mysql.png

Now we want to display the timesheet for a specific employee. Let's add a simple <select> control:

<div class="space">
  Employee:
  <select id="employee"></select>
</div>

And fill it with data:

$(document).ready(function() {
  loadResources();
  $("#employee").change(function() {
      loadEvents();
  });
});

function loadResources() {
  $.post("backend_resources.php", function(data) {
      for (var i = 0; i < data.length; i++) {
          var item = data[i];
          $("#employee").append($('<option/>', { 
              value: item.id,
              text : item.name
          }));
      }
      loadEvents();
  });
}

The backend_resources.php PHP script returns a JSON array:

<?php
require_once '_db.php';
    
class Resource {}

$resources = array();

$stmt = $db->prepare('SELECT * FROM [resources] ORDER BY [name]');
$stmt->execute();
$timesheet_resources = $stmt->fetchAll();  

foreach($timesheet_resources as $resource) {
  $r = new Resource();
  $r->id = $resource['id'];
  $r->name = $resource['name'];
  $resources[] = $r;
}

header('Content-Type: application/json');
echo json_encode($resources);

?>

Example JSON array:

[
  {"id":"1","name":"Person 1"},
  {"id":"2","name":"Person 2"},
  {"id":"3","name":"Person 3"},
  {"id":"4","name":"Person 4"}
]

Loading Timesheet Data from MySQL Database

javascript-html5-timesheet-loading-event-data-php-mysql.png

Now we can load the employee data from MySQL database. We pass the selected employee id and visible start and end to the AJAX endpoint so the records can be selected from the database:

function loadEvents() {
  var url = "backend_events.php?resource=" + $("#employee").val() + "&start=" + dp.visibleStart() + "&end=" + dp.visibleEnd();
  dp.events.load(url, function() {
      dp.message("Events for " + $("#employee option:selected").text() + " loaded");
  });
}

The backend_events.php script returns a JSON array with event details.

<?php
require_once '_db.php';

if ($_GET["resource"]) {
    $stmt = $db->prepare('SELECT * FROM events WHERE resource_id = :resource AND NOT ((end <= :start) OR (start >= :end))');
    $stmt->bindParam(':resource', $_GET['resource']);
}
else {
    $stmt = $db->prepare('SELECT * FROM events WHERE NOT ((end <= :start) OR (start >= :end))');
}
$stmt->bindParam(':start', $_GET['start']);
$stmt->bindParam(':end', $_GET['end']);
$stmt->execute();
$result = $stmt->fetchAll();

class Event {}
$events = array();

foreach($result as $row) {
  $e = new Event();
  $e->id = $row['id'];
  $e->text = $row['name'];
  $e->start = $row['start'];
  $e->end = $row['end'];
  $e->resource = $row['resource_id'];
  $e->bubbleHtml = "Event details: <br/>".$e->text;
  $events[] = $e;
}

header('Content-Type: application/json');
echo json_encode($events);

?>

Example JSON output:

[
  {
    "id": "1",
    "text": "Event",
    "start": "2019-09-03T04:00:00",
    "end": "2019-09-03T09:00:00",
    "resource": "1",
    "bubbleHtml": "Event details: <br/>Event"
  }
]

Calculating Day Totals

javascript-html5-timesheet-day-totals.png

We want to display the totals for each day (row) in an additional header column of the timesheet component. You can define the header columns using rowHeaderColumns property.

dp.rowHeaderColumns = [
  { title: "Day", width: 100},
  { title: "Total", width: 100}
];

The row totals are calculated and displayed using onBeforeRowHeaderRender event handler:

dp.onBeforeRowHeaderRender = function(args) {
    var duration = args.row.events.totalDuration();
    var str;
    if (duration.totalDays() >= 1) {
        str = Math.floor(duration.totalHours()) + ":" + duration.toString("mm");
    }
    else {
        str = duration.toString("H:mm");
    }

    if (args.row.columns[0]) {
      args.row.columns[0].html = str + " hours";
    }
};

Database

By default, the timesheet project uses SQLite database to store the data. This allows easy testing of the project without additional configuration. The database file (daypilot.sqlite) will be created automatically on startup. Just make sure that the process has permissions to write to the project folder.

You can also switch to MySQL by editing the _db.php file like this:

<?php

// use sqlite
//require_once '_db_sqlite.php';

// use MySQL
require_once '_db_mysql.php';

The MySQL db connection details are specified in _db_mysql.php. You'll need to update the username and password (and make sure the the server name and port are correct). If the specified database user has sufficient permissions, the project will create and initialize the database automatically (by default the db name is "timesheet").

<?php
$host = "127.0.0.1";
$port = 3306;
$username = "username";
$password = "password";
$database = "timesheet";

// ...

MySQL Database Schema

CREATE TABLE `events` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` TEXT NULL,
	`resource_id` INT(11) NULL DEFAULT NULL,
	`start` DATETIME NULL DEFAULT NULL,
	`end` DATETIME NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `resources` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(200) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

SQLite Database Schema

CREATE TABLE events (
        id          INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
        name        TEXT,
        start       DATETIME,
        [end]       DATETIME,
        resource_id INTEGER
);

CREATE TABLE resources (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        name VARCHAR (200)
);

History

  • September 9, 2019: MySQL support added. DayPilot Pro version upgraded (2019.3.3999). Using modal dialog for event creating and editing. The frontend doesn't require PHP (renamed from index.php to index.html).
  • November 21, 2017: Correct _db.php version included
  • June 27, 2017: DayPilot Pro version updated (8.4.2911)
  • June 19, 2014: Initial release