Features

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

  • Uses the JavaScript Scheduler component from DayPilot Pro for JavaScript

  • 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.

JavaScript Timesheet Component Initialization

JavaScript Timesheet Component Initialization

We will display the timesheet using DayPilot Scheduler switched to the 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="timesheet"></div>

<script>
  const timesheet = new DayPilot.Scheduler("timesheet", {
    viewType: "Days"
  });
  timesheet.init();
</script>

This code will display the timesheet using the default configuration:

  • A single row showing the current day

  • Fixed cell width (40 pixels)

Monthly Timesheet with 30-Minute Slots

JavaScript Monthly Timesheet with 30-Minute Slots

Now we are going to update the timesheet configuration so that it displays the current month.

We will use the startDate and days properties to specify the visible range.

The scale is configured using the scale and cellDuration properties. Our timesheet component will use 30-minute time slots.

<div id="timesheet"></div>

<script>
  const timesheet = new DayPilot.Scheduler("timesheet", {
    viewType: "Days",
    startDate: DayPilot.Date.today().firstDayOfMonth(),
    days: DayPilot.Date.today().daysInMonth(),
    scale: "CellDuration",
    cellDuration: 30,
    timeHeaders: [
      { groupBy: "Cell", format: "HH:mm" }
    ]
  });
  timesheet.init();
</script>

Timesheet Headers with Time Slot Names

JavaScript Timesheet Headers with Time Slot Names

Now we will configure the horizontal headers of the timesheet component to display three rows:

  1. Current month (e.g., “September 2024”)

  2. Hours (e.g. “8 AM”)

  3. Starting minute of a time slot (e.g., “00”)

The time header rows can be defined using the timeHeaders property.

<div id="timesheet"></div>

<script>
  const timesheet = new DayPilot.Scheduler("timesheet", {
    viewType: "Days",
    startDate: DayPilot.Date.today().firstDayOfMonth(),
    days: DayPilot.Date.today().daysInMonth(),
    scale: "CellDuration",
    cellDuration: 30,
    timeHeaders: [
      { groupBy: "Month", format: "MMMM yyyy" },
      { groupBy: "Hour"},
      { groupBy: "Cell", format: "mm" }
    ]
  });

  dp.init();
</script>

Loading Employees from MySQL Database

JavaScript Timesheet - Loading Employees from Database

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

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

And fill it with data:

const app = {
  elements: {
    employee: document.querySelector("#employee")
  },
  async loadResources() {
    const {data} = await DayPilot.Http.get("backend_resources.php");
    const select = app.elements.employee;
    data.forEach(function(item) {
      const option = document.createElement("option");
      option.value = item.id;
      option.innerText = item.name;
      select.appendChild(option);
    });
  },
  // ...
};

The backend_resources.php PHP script returns a JSON array with employee data:

<?php
require_once '_db.php';
    
class Resource {
  public $id;
  public $name;
}

$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 Records from MySQL Database

Loading Timesheet Records from MySQL Database

Let’s create a loadEvents() function that loads the employee data from MySQL database and updated the timesheet view.

First, we get the ID of the employee that is selected (employee) and the date range displayed in the timesheet (start and end).

Then we can load the timesheet records using the events.load() method that uses an HTTP call to get the data from the specified URL.

const app = {
  loadEvents() {
    const employee = app.elements.employee.value;
    const start = timesheet.visibleStart();
    const end = timesheet.visibleEnd();
    const url = `backend_events.php?resource=${employee}&start=${start}&end=${end}`;
    timesheet.events.load(url, () => {
      timesheet.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 ((event_end <= :start) OR (event_start >= :end))');
    $stmt->bindParam(':resource', $_GET['resource']);
}
else {
    $stmt = $db->prepare('SELECT * FROM events WHERE NOT ((event_end <= :start) OR (event_start >= :end))');
}
$stmt->bindParam(':start', $_GET['start']);
$stmt->bindParam(':end', $_GET['end']);
$stmt->execute();
$result = $stmt->fetchAll();

class Event {
  public $id;
  public $text;
  public $start;
  public $end;
  public $resource;
  public $project;
  public $bubbleHtml;
}
$events = array();

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

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

Example JSON output:

[
  {
    "id": 2,
    "text": "Task #1",
    "start": "2024-07-01T10:00:00",
    "end": "2024-07-01T14:00:00",
    "resource": 1,
    "project": 1,
    "bubbleHtml": "Event details: \u003Cbr/\u003ETask #1"
  },
  {
    "id": 3,
    "text": "Task #5",
    "start": "2024-07-02T09:00:00",
    "end": "2024-07-02T13:00:00",
    "resource": 1,
    "project": 2,
    "bubbleHtml": "Event details: \u003Cbr/\u003ETask #5"
  },
  {
    "id": 4,
    "text": "Task #10",
    "start": "2024-07-03T11:00:00",
    "end": "2024-07-03T13:30:00",
    "resource": 1,
    "project": 3,
    "bubbleHtml": "Event details: \u003Cbr/\u003ETask #10"
  }
]

Customizing Appearance of Timesheet Records

Customizing Appearance of Timesheet Records

The JavaScript Scheduler component provides the ability to customize event appearance. You can add custom icons, set CSS classes, modify the default HTML, and adjust behavior, such as making an event read-only.

To customize timesheet events, you can use the onBeforeEventRender event handler.

We will use this event to define a custom event bar color that depends on the related project and to add a context menu icon that uses the same color.

onBeforeEventRender: (args) => {

  const project = app.findProject(args.data.project);
  const projectName = project?.name || "n/a";

  args.data.barColor = project?.color || "#999999";
  args.data.html = args.data.text + "<br />" + projectName;

  args.data.areas = [
    {
      top: 12,
      right: 6,
      width: 24,
      height: 24,
      padding: 4,
      action: "ContextMenu",
      symbol: "icons/daypilot.svg#threedots-h",
      backColor: args.data.barColor,
      fontColor: "#ffffff",
      style: "cursor:pointer; border-radius: 50%; background-color: #e3e3e3;"
    }
  ];
},

The active area defined using args.data.areas displays an icon that opens the default event context menu on click.

The context menu is defined in the timesheet config using the contextMenu property:

contextMenu: new DayPilot.Menu({
  items: [
    { text: "Edit...",
      onClick: async (args) => {
        const e = args.source;
        await app.editEvent(e);
      }
    },
    { text: "Delete",
      onClick: async (args) => {
        const e = args.source;
        const data = {
          id: e.id()
        };
        await DayPilot.Http.post("backend_delete.php", data);
        timesheet.events.remove(e);
        timesheet.message("Deleted.");
      }
    }
  ]
}),

Displaying Daily Totals in Timesheet Headers

Displaying Daily Totals in Timesheet Headers

We want to display totals for each day (row) in an additional header column of the timesheet component.

You can define the header columns using rowHeaderColumns property:

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

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

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

Here is the MySQL database schema for our timesheet app.

It defines three tables:

  • events

  • resources

  • projects

CREATE TABLE `events` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` TEXT NULL,
	`event_start` DATETIME NULL DEFAULT NULL,
	`event_end` DATETIME NULL DEFAULT NULL,
	`resource_id` INT(11) NULL DEFAULT NULL,
        `project_id` INT(11) 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`)
);

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

SQLite Database Schema

And here is the SQLite database schema:

CREATE TABLE events (
        id          INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
        name        TEXT,
        event_start DATETIME,
        event_end   DATETIME,
        resource_id INTEGER,
        project_id  INTEGER
);

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

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

Full Source Code of the JavaScript Frontend

Here is the full source code of the JavaScript/HTML part of our timesheet application:

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8" />
  <title>JavaScript/HTML5 Timesheet (PHP, MySQL)</title>
  <meta name="viewport" content="width=device-width, initial-scale=1" />

  <link type="text/css" rel="stylesheet" href="toolbar.css" />

  <!-- DayPilot library -->
  <script src="js/daypilot/daypilot-all.min.js"></script>

</head>
<body>
<div class="header">
  <h1><a href='https://code.daypilot.org/46581/html5-timesheet-javascript-php'>JavaScript/HTML5 Timesheet (PHP, MySQL)</a></h1>
  <div><a href="https://javascript.daypilot.org/">DayPilot for JavaScript</a> - HTML5 Calendar/Scheduling Components for JavaScript/Angular/React/Vue</div>
</div>

<div class="main">
  <div class="toolbar">
    Employee:
    &nbsp;
    <select id="employee"></select>
  </div>

  <div id="timesheet"></div>

  <div class="generated">Generated using <a href="https://builder.daypilot.org/">DayPilot UI Builder</a>.</div>
</div>

<script type="text/javascript">

  const timesheet = new DayPilot.Scheduler("timesheet", {
    viewType: "Days",
    timeHeaders: [
      { groupBy: "Month", format: "MMMM yyyy" },
      { groupBy: "Hour"},
      { groupBy: "Cell", format: "mm" }
    ],
    scale: "CellDuration",
    cellDuration: 30,
    startDate: DayPilot.Date.today().firstDayOfMonth(),
    days: DayPilot.Date.today().daysInMonth(),
    heightSpec: "Max",
    height: 400,
    cellWidthSpec: "Auto",
    cellWidthMin: 50,
    eventHeight: 50,
    rowMarginBottom: 5,
    rowMarginTop: 5,
    durationBarHeight: 4,
    rowHeaderColumns: [
      { title: "Day", width: 100},
      { title: "Total", width: 100}
    ],
    contextMenu: new DayPilot.Menu({
      items: [
        { text: "Edit...",
          onClick: async (args) => {
            const e = args.source;
            await app.editEvent(e);
          }
        },
        { text: "Delete",
          onClick: async (args) => {
            const e = args.source;
            const data = {
              id: e.id()
            };
            await DayPilot.Http.post("backend_delete.php", data);
            timesheet.events.remove(e);
            timesheet.message("Deleted.");
          }
        }
      ]
    }),
    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";
      }
    },
    onBeforeEventRender: (args) => {

      const project = app.findProject(args.data.project);
      const projectName = project?.name || "n/a";

      args.data.barColor = project?.color || "#999999";
      args.data.html = args.data.text + "<br />" + projectName;

      args.data.areas = [
        {
          top: 12,
          right: 6,
          width: 24,
          height: 24,
          padding: 4,
          action: "ContextMenu",
          symbol: "icons/daypilot.svg#threedots-h",
          backColor: args.data.barColor,
          fontColor: "#ffffff",
          style: "cursor:pointer; border-radius: 50%; background-color: #e3e3e3;"
        }
      ];
    },
    onEventMoved: async (args) => {
      const data = {
        id: args.e.id(),
        newStart: args.newStart,
        newEnd: args.newEnd,
      };
      await DayPilot.Http.post("backend_move.php", data);
      timesheet.message("Moved.");
    },
    onEventResized: async (args) => {
      const data = {
        id: args.e.id(),
        newStart: args.newStart,
        newEnd: args.newEnd
      };
      await DayPilot.Http.post("backend_resize.php", data);
      timesheet.message("Resized.");
    },
    onTimeRangeSelected: async (args) => {

      const form = [
        {name: "Name", id: "text"},
        {name: "Project", id: "project", type: "select", options: app.projects},
      ];
      const data = {
        text: "Task",
        project: app.projects[0].id,
        resource: app.elements.employee.value,
        start: args.start,
        end: args.end
      };

      const modal = await DayPilot.Modal.form(form, data);

      timesheet.clearSelection();
      if (modal.canceled) {
        return;
      }

      const {data: result} = await DayPilot.Http.post("backend_create.php", modal.result);

      const e = {
        ...modal.result,
        id: result.id,
      };
      timesheet.events.add(e);
      timesheet.message(result.message);
    },
    onEventClick: async (args) => {
      await app.editEvent(args.e);
    }
  });
  timesheet.init();

  const app = {
    elements: {
      employee: document.querySelector("#employee")
    },
    projects: [],
    findProject(id) {
      return app.projects.find(function(item) {
        return item.id === id;
      });
    },
    async loadProjects() {
      const {data} = await DayPilot.Http.get("backend_projects.php");
      app.projects = data;
    },
    async loadResources() {
      const {data} = await DayPilot.Http.get("backend_resources.php");
      const select = app.elements.employee;
      data.forEach(function(item) {
        const option = document.createElement("option");
        option.value = item.id;
        option.innerText = item.name;
        select.appendChild(option);
      });
    },
    loadEvents() {
      const employee = app.elements.employee.value;
      const start = timesheet.visibleStart();
      const end = timesheet.visibleEnd();
      const url = `backend_events.php?resource=${employee}&start=${start}&end=${end}`;
      timesheet.events.load(url, () => {
        timesheet.message("Events loaded");
      });
    },
    async editEvent(e) {
      const form = [
        {name: "Name", id: "text"},
        {name: "Project", id: "project", type: "select", options: app.projects},
      ];

      const modal = await DayPilot.Modal.form(form, e.data);

      if (modal.canceled) {
        return;
      }

      const data = {
        id: e.id(),
        name: modal.result.text,
        project: modal.result.project
      };
      const {data: result} = await DayPilot.Http.post("backend_update.php", data);
      e.data.text = modal.result.text;
      e.data.project = modal.result.project;
      timesheet.events.update(e);
      timesheet.message(result.message);
    },
    async init() {
      app.elements.employee.addEventListener("change", (ev) => {
        app.loadEvents();
      });

      timesheet.scrollTo(DayPilot.Date.today().firstDayOfMonth().addHours(8));

      await app.loadResources();
      await app.loadProjects();

      app.loadEvents();
    }
  };
  app.init();

</script>

</body>
</html>

History

  • July 9, 2024: Upgraded to DayPilot Pro 2024.3.5970; edit dialogs using DayPilot.Modal.form() that allows defining multiple form fields; project color added to events; context menu added to events; PHP 8+ compatibility.

  • 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