Features

  • JavaScript/HTML5 monthly timesheet (one day per row)

  • Uses DayPilot JavaScript Scheduler component

  • Displays a timesheet for multiple users - you can switch the employee using a <select> control

  • Displays a day summary (total time) in day headers

  • Highlights business hours

  • Sample PHP backend with support for MySQL/SQLite database

  • 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

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">
  const 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

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

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

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

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

  dp.init();
</script>

Timesheet Headers

javascript html5 timesheet time headers

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">
  const 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

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:

async function loadResources() {
  const {data} = await DayPilot.Http.get("backend_resources.php");
  var select = document.querySelector("#employee");
  data.forEach(function(item) {
    var option = document.createElement("option");
    option.value = item.id;
    option.innerText = item.name;
    select.appendChild(option);
  });
  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

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() {
  const employee = document.querySelector("#employee").value;
  const start = dp.visibleStart();
  const end = dp.visibleEnd();
  const url = `backend_events.php?resource=${employee}&start=${start}&end=${end}`;
  dp.events.load(url, () => {
    dp.message("Events 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": "2020-09-03T04:00:00",
    "end": "2020-09-03T09:00:00",
    "resource": "1",
    "bubbleHtml": "Event details: <br/>Event"
  }
]

Calculating Day Totals

javascript html5 timesheet day totals

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 = (args) => {
  const duration = args.row.events.totalDuration();
  let str;
  if (duration.totalDays() >= 1) {
    str = Math.floor(duration.totalHours()) + ":" + duration.toString("mm");
  }
  else {
    str = duration.toString("H:mm");
  }

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

Timesheet Database Storage

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

  • June 11, 2021: DayPilot Pro 2021.2.5000; using system fonts; async/await

  • November 24, 2020: DayPilot Pro 2020.4.4766

  • August 5, 2020: DayPilot Pro 2020.3.4610. jQuery dependency removed.

  • 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