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>controlDisplays 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.
Timesheet 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="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
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
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
Now we want to display a 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
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
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.
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
