PHP Annual Leave Scheduling (JavaScript/HTML5 Frontend, MySQL Database)

Features

• PHP annual leave scheduling and tracking system

• Allows to schedule annual leave for each employee in half-day units

• Displays total annual leave days for every person

• Drag and drop support

• Built using HTML5/JavaScript Scheduler component from DayPilot Pro for JavaScript (trial version)

See also the Angular version of this tutorial:

• Angular: How To Build Annual Leave Scheduling Application (ASP.NET Core Backend)

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.

Annual Leave Scheduler JavaScript Configuration

The annual leave schedule will be displayed using DayPilot JavaScript Scheduler. We will use the following configuration:

1. The scheduler will display the current year:

{
  startDate: DayPilot.Date.today().firstDayOfYear(),
  days: DayPilot.Date.today().daysInYear()
}

2. The grid cell size will be set to half a day (720 minutes):

{
  scale: "CellDuration",
  cellDuration: 720
}

3. The time headers will be displayed in two rows, grouped by by month and day:

{
  timeHeaders: [{"groupBy": "Month"}, {"groupBy": "Day", "format": "d"}],
}

4. The initial scrollbar position will be set to today:

{
  scrollTo: DayPilot.Date.today()
}

5. We will load the employee data (to be displayed as rows) using DayPilot.Scheduler.rows.load() method:

dp.rows.load("backend_employees.php");

The complete Scheduler configuration:

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

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

<script>
  const dp = new DayPilot.Scheduler("dp", {
    timeHeaders: [{"groupBy": "Month"}, {"groupBy": "Day", "format": "d"}],
    scale: "CellDuration",
    cellDuration: 720,
    days: DayPilot.Date.today().daysInYear(),
    startDate: DayPilot.Date.today().firstDayOfYear(),
    eventHeight: 40,
    headerHeight: 40,
    cellWidth: 20,
    scrollTo: DayPilot.Date.today()
  });
  dp.init();
  dp.rows.load("backend_employees.php");
</script>

The backend_employees.php script returns a list of employees in JSON format:

[
  {"id":"1","name":"Emerson, Adam"},
  {"id":"2","name":"Irwin, Cheryl"},
  {"id":"3","name":"Jameson, Emily"},
  {"id":"5","name":"Kingston, Eliah"},
  {"id":"6","name":"Niles, Taylor"},
  {"id":"4","name":"Rodriguez, Eva"},
  {"id":"7","name":"Thomas, Jo"}
]

The structure of the row/resource items is described in DayPilot.Scheduler.resources property docs.

The source code of backend_employees.php:

<?php
require_once '_db.php';

$stmt = $db->prepare("SELECT * FROM person ORDER BY last, first");
$stmt->execute();
$list = $stmt->fetchAll();

class Employee {}

$result = array();

foreach($list as $employee) {
  $r = new Employee();
  $r->id = $employee['id'];
  $r->name = $employee['last'].', '.$employee['first'];
  $result[] = $r;
  
}

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

Day Separators

In order to highlight start and end of each day, we will add separators to the Scheduler. The separators will be located at the start of each day and they will be displayed as a gray (#ddd) vertical line:

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

<script>
  const dp = new DayPilot.Scheduler("dp", {
    timeHeaders: [{"groupBy": "Month"}, {"groupBy": "Day", "format": "d"}],
    // ...
  });
  
  dp.separators - [];
  for (let i = 0; i < dp.days; i++) {
    dp.separators.push({location: dp.startDate.addDays(i), color: "#ddd"});
  }
  
  dp.init();
  dp.rows.load("backend_employees.php");
</script>

Loading Annual Leave Data

The Scheduler provides a shortcut method for loading the event data from a remote location - DayPilot.Scheduler.events.load():

dp.events.load("backend_events.php");

We will use onBeforeEventRender event to customize the event appearance. We will use green background and duration bar color and display the item duration in the event body:

<script>
  const dp = new DayPilot.Scheduler("dp", {
    onBeforeEventRender: function (args) {
      var duration = new DayPilot.Duration(args.data.start, args.data.end);
      args.data.html = duration.totalDays() + " days";

      args.data.backColor = "#d9ead3";
      args.data.barColor = "#6aa84f";
      args.data.barBackColor = args.data.barColor;
    },
    // ...
  });
  dp.init();
</script>

Sample JSON response:

[
  {"start":"2021-06-22T00:00:00","end":"2021-06-26T00:00:00","resource":"2","id":"30"},
  {"start":"2021-06-23T12:00:00","end":"2021-06-26T00:00:00","resource":"3","id":"31"},
  {"start":"2021-06-21T00:00:00","end":"2021-06-23T12:00:00","resource":"6","id":"32"},
  {"start":"2021-06-28T00:00:00","end":"2021-07-01T00:00:00","resource":"4","id":"34"}
]

The structure of the event items is described at DayPilot.Event.data property docs. Note that the value of the resource property matches the row id.

And this is the source code of backend_events.php:

<?php
require_once '_db.php';

$stmt = $db->prepare("SELECT * FROM leave WHERE NOT ((leave_end <= :start) OR (leave_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 = "";
    $e->start = $row['leave_start'];
    $e->end = $row['leave_end'];
    $e->resource = $row['person_id'];
    $events[] = $e;
}

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

Adding a New Record

When you select a date range using drag and drop, the Scheduler fires onTimeRangeSelected event handler. We will use this event handler to add a new record to our annual leave planner.

The event handler opens a modal dialog with the following fields:

• Start (date)

• End (date)

• Employee (drop-down list)

The form is created using DayPilot Modal library. You can design your own modal dialog using the Modal Builder online application.

onTimeRangeSelected: async (args) => {

  const form = [
    { name: "Start", id: "start", dateFormat: "MMMM d, yyyy h:mm tt"},
    { name: "End", id: "end", dateFormat: "MMMM d, yyyy h:mm tt"},
    { name: "Employee", id: "resource", options: dp.resources },
  ];

  const data = {
    start: args.start,
    end: args.end,
    resource: args.resource
  };

  const options = {
    autoFocus: false
  };

  const modal = await DayPilot.Modal.form(form, data, options);
  dp.clearSelection();
  if (modal.canceled) {
    return;
  }

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

},

When the user confirms the new record details, the application saves the record in database using an HTTP call to backend_create.php script.

backend_create.php

<?php
require_once '_db.php';

$json = file_get_contents('php://input');
$params = json_decode($json);

$stmt = $db->prepare("INSERT INTO leave_event (leave_start, leave_end, person_id) VALUES (:start, :end, :person)");
$stmt->bindParam(':start', $params->start);
$stmt->bindParam(':end', $params->end);
$stmt->bindParam(':person', $params->resource);
$stmt->execute();

class Result {}

$response = new Result();
$response->result = 'OK';
$response->message = 'Created with id: '.$db->lastInsertId();
$response->id = $db->lastInsertId();

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

Annual Leave Totals

Now we want to display the annual leave totals for each person in the row headers. First, we define the row header columns using rowHeaderColumns property:

<script>
  const dp = new DayPilot.Scheduler("dp", {
    rowHeaderColumns: [
      {name: "Name", display: "name"},
      {name: "Total"}
    ],
    // ...
  });
  dp.init();
</script>

There will be two columns - the first column will display the employee name (it will use the name property of the resource array items, as defined using display: "name") and the second column will display the total.

We will use the onBeforeRowHeaderRender event to display the total in the second column. The args.row property (which stores a DayPilot.Row object) provides a helper methods that returns the total duration of all events in a given row: args.row.events.totalDuration().

<script>
  const dp = new DayPilot.Scheduler("dp", {
    rowHeaderColumns: [
      {name: "Name", display: "name"},
      {name: "Total"}
    ],
    onBeforeRowHeaderRender: (args) => {
      args.row.columns[1].html = "";
      const totalDuration = args.row.events.totalDuration();
      if (totalDuration.days() > 0) {
        args.row.columns[1].html = args.row.events.totalDuration().totalDays() + " days";
      }
    },
    // ...
  });
  dp.init();
</script>

Disabled Weekends

We will mark weekends as disabled using onBeforeCellRender event - this will prevent drag and drop operation for Saturday and Sunday.

If the users selects a date range that spans a weekend or tries to move an existing annual leave record to Saturday or Sunday the target position will be displayed in red and the drop action will be disabled.

<script>
  const dp = new DayPilot.Scheduler("dp", {
    // ...
    onBeforeCellRender: (args) => {
      var day = args.cell.start.getDayOfWeek();
      if (day === 6 || day === 0) {
        args.cell.disabled = true;
      }
    },
    // ...
  });
  dp.init();
</script>

MySQL Database Schema

Our annual leave application uses the following MySQL database schema. There are two tables, leave_event and person.

The person table stores the employees who will be displayed as rows in the Scheduler:

CREATE TABLE `person` (
	`id` INT(11) NOT NULL,
	`first` VARCHAR(200) NULL DEFAULT NULL,
	`last` VARCHAR(200) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

The leave_event table stores the annual leave records. It identifies the employee (person_id database column) and specifies the start and end date (leave_start and leave_event database columns):

CREATE TABLE `leave_event` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`person_id` INT(11) NULL DEFAULT NULL,
	`leave_start` DATETIME NULL DEFAULT NULL,
	`leave_end` DATETIME NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

The PHP annual leave application uses SQLite as the default storage. This requires no additional configuration and it will let you start quickly (just make sure that the PHP process has permission to write to the application folder). You can switch the storage to MySQL by editing the _db.php file.