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:

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

php-annual-leave-scheduling-ui-configuration.png

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>
  var 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

php-annual-leave-scheduling-day-separators.png

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>
  var dp = new DayPilot.Scheduler("dp", {
    timeHeaders: [{"groupBy": "Month"}, {"groupBy": "Day", "format": "d"}],
    // ...
  });
  
  dp.separators - [];
  for (var 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

php-annual-leave-scheduling-javascript-html5-loading-data.png

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>
  var 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:

[
  {"id":"1","text":"","start":"2020-07-02T12:00:00","end":"2020-07-06T00:00:00","resource":"5"},
  {"id":"3","text":"","start":"2020-07-02T00:00:00","end":"2020-07-07T00:00:00","resource":"2"},
  {"id":"4","text":"","start":"2020-06-27T00:00:00","end":"2020-06-30T00:00:00","resource":"5"},
  {"id":"17","text":"","start":"2020-07-02T00:00:00","end":"2020-07-05T12:00:00","resource":"7"}
]

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);

Annual Leave Totals

php-annual-leave-scheduling-totals.png

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

Disabling Weekends

php-annual-leave-scheduling-javascript-html5-weekends.png

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>
  var dp = new DayPilot.Scheduler("dp", {
    // ...
    onBeforeCellRender: function (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.