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)

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 thin 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 duratino 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":"2018-07-02T12:00:00","end":"2018-07-06T00:00:00","resource":"5"},
  {"id":"3","text":"","start":"2018-07-02T00:00:00","end":"2018-07-07T00:00:00","resource":"2"},
  {"id":"4","text":"","start":"2018-06-27T00:00:00","end":"2018-06-30T00:00:00","resource":"5"},
  {"id":"17","text":"","start":"2018-07-02T00:00:00","end":"2018-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.

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

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

<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

leave_event table:

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

person table:

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