Features

  • PHP shift planning web application tutorial
  • The application displays a shift plan using a visual JavaScript Scheduler component
  • Plan shifts for multiple locations/positions
  • Each day is split into three 8-hour shifts
  • See all assignments for a selected location
  • Use drag and drop to move assignments between shifts and employees
  • PHP and MySQL used for the backend REST API
  • The application includes a trial version of DayPilot Pro for JavaScript (see License below)

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.

Shift Planning: How It Works

This PHP shift scheduling system lets you plan shift coverage for multiple locations. You can switch between locations using a drop down menu. Each view displays shifts for the selected locations, together with details of the assignment. All available people are displayed as additional rows - this provides an overview of assignments and availability for each of the employees.

php-shift-planning-tutorial-javascript-html5-mysql-select-location.png

To create a new shift assignment, select the target location using the drop-down list and click the selected cell grid.

php-shift-planning-tutorial-javascript-html5-mysql-click.png

A new shift assignment will be created for the selected person and location.

php-shift-planning-tutorial-javascript-html5-mysql-create-assignment.png

Each shift assignment will be displayed in two rows - in the location row (at the top) and in the row of the selected person.

php-shift-planning-tutorial-javascript-html5-mysql-assignment.png

The Scheduler will display a total for each row (location and people) in a special "Total" columns:

php-shift-planning-tutorial-javascript-html5-mysql-totals.png

If you switch to a different location the existing shift assignments will be displayed in gray color:

php-shift-planning-tutorial-javascript-html5-mysql-location.png

You can easily change the assigned person by dragging the shift assignment vertically to another row:

php-shift-planning-tutorial-javascript-html5-mysql-drag-drop-change-person.png

You can also move the person assignment to another shift by dragging the assignment horizontally in the location row:

php-shift-planning-tutorial-javascript-html5-mysql-drag-drop-change-shift.png

Shift Planner: Building the PHP Application

The PHP application consists of a frontend part (JavaScript/HTML5) and a backend part (PHP/JSON).

The frontend of the application can be found in index.html file. It's implemented using HTML5 and JavaScript and it uses JavaScript Scheduler component to display the shift scheduler. The scheduler communicates with the backend using HTTP calls.

The backend consists of several PHP files that provide database access using simple JSON endpoints:

  • backend_assignments.php
  • backend_create.php
  • backend_delete.php
  • backend_locations.php
  • backend_people.php

Shift Scheduler Configuration

php-shift-planning-tutorial-javascript-html5-mysql-scheduler.png

The frontend of the shift planning application is built using JavaScript Scheduler from DayPilot Pro for JavaScript package. For introduction to using the Scheduler component with PHP please see the basic tutorial:

The starting version of the application frontend was generated using Scheduler UI Builder - an online configurator that lets you customize the Scheduler and generate a zip file with all dependencies.

The Scheduler configuration source code has the following structure:

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

<!-- Scheduler placeholder -->
<div id="dp"></div>

<!-- Scheduler initialization -->
<script>
  var dp = new DayPilot.Scheduler("dp", {
    cellWidth: 40,
    // other configuration options
  });
  dp.init();
</script>

Shift Planner Timeline

php-shift-planning-tutorial-javascript-html5-mysql-timeline.png

Our shift planning application will display three 8-hour shifts per day. The Scheduler component supports several standard scale units (such as hour, day, week, month). There is no special option for 8-hour segments, but we can switch the scheduler to manual mode and generate a completely customized timeline:

var dp = new DayPilot.Scheduler("dp", {
  scale: "Manual",
  timeline: getTimeline(),
  // ...
});
dp.init();

function getTimeline() {
  var days = DayPilot.Date.today().daysInMonth();
  var start = DayPilot.Date.today().firstDayOfMonth();

  var result = [];
  for (var i = 0; i < days; i++) {
    var day = start.addDays(i);
    result.push({
      start: day.addHours(0),
      end: day.addHours(8)
    });
    result.push({
      start: day.addHours(8),
      end: day.addHours(16)
    });
    result.push({
      start: day.addHours(16),
      end: day.addHours(24)
    });
  }
  return result;
}

Our custom timeline will start on the first day of the current month and each day will be split into three 8-hour cells.

We will also set the time header to display the corresponding month, day, and starting hour in three levels:

var dp = new DayPilot.Scheduler("dp", {
  scale: "Manual",
  timeline: getTimeline(),
  timeHeaders: [{groupBy: "Month"}, {groupBy: "Day"}, {groupBy: "Cell"}],
  onBeforeTimeHeaderRender: function (args) {
    if (args.header.level === 2) {
      args.header.html = args.header.start.toString("h") + args.header.start.toString("tt").substring(0, 1).toLowerCase();
    }
  },
  // ...
});

Displaying People as Rows

php-shift-planning-tutorial-javascript-html5-mysql-scheduler-rows.png

We will use rows.load() method of the scheduler component to load the row data from MySQL database:

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

This call invokes an HTTP GET request to backend_people.php script. This script loads the employee data from a database and returns a JSON array:

[
  {"id":"1","name":"Adam"},
  {"id":"2","name":"Cheryl"},
  {"id":"5","name":"Eliah"},
  {"id":"3","name":"Emily"},
  {"id":"4","name":"Eva"},
  {"id":"6","name":"John"},
  {"id":"7","name":"Sally"}
]

The structure of the array items is described in the documentation of DayPilot.Scheduler.resources property. We will only use the minimum required properties (id and name).

backend_people.php

<?php
require_once '_db.php';

$stmt = $db->prepare("SELECT * FROM person ORDER BY name");
$stmt->execute();
$people = $stmt->fetchAll();

class Item {}

$result = array();

foreach($people as $person) {
  $r = new Item();
  $r->id = $person['id'];
  $r->name = $person['name'];
  $result[] = $r;
  
}

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

The layout of our shift planning system requires that there is one more row above the rows with people - that row will display the selected location. We will use the success callback of rows.load() to modify the result and add one more row at the top of the shift scheduler:

dp.rows.load("backend_people.php", function (args) {
  args.data.splice(0, 0, {id: "L" + item.id, name: item.name, type: "location"});
});

The scheduler requires that each row has a unique ID. It's also necessary for clear identification of each row. The locations are stored in a different database table and the location IDs can overlap with people IDs. That's why we will prefix the row ID with "L" string to indicate that this row hold location data.

We want to give users a visual hint that the first row has a special meaning. We will use onBeforeRowHeaderRender event handler to set a custom color of the first row header:

onBeforeRowHeaderRender: function(args) {
  var duration = args.row.events.totalDuration();
  if (duration.totalHours() > 0) {
    args.row.columns[0].html = duration.totalHours() + "h";
  }
  if (args.row.data.type === "location") {
    args.row.backColor = "#e06146";
    args.row.fontColor = "#fff";
    args.row.columns[0].fontColor = "#fff";
  }
},

To customize the scheduler grid cells, we will use onBeforeCellRender event handler:

onBeforeCellRender: function(args) {
  if (args.cell.y === 0) {
    args.cell.backColor = "#e0b4a8";
  }
},

Loading Shift Assignments

php-shift-planning-tutorial-javascript-html5-mysql-loading-assignments.png

The Scheduler allows loading the event data using events.load() method.

dp.events.load("backend_assignments.php?location=" + locationId);

The backend_assignments.php scripts loads the shift assignments from MySQL database. The assignments for the active location (which is passed to the PHP script using query "location" string parameter) are returned as two items:

  • the first one will be displayed in the location row (displayed at the top of the Scheduler)
  • the second one will be displayed in the matching person row

Sample JSON response:

[
  {
    "id":"L3",
    "text":"",
    "start":"2018-07-01T08:00:00",
    "end":"2018-07-01T16:00:00",
    "resource":"L1",
    "join":"3",
    "person":"1",
    "location":"1",
    "type":"location"
  },
  {
    "id":"3",
    "text":"",
    "start":"2018-07-01T08:00:00",
    "end":"2018-07-01T16:00:00",
    "resource":"1",
    "join":"3",
    "person":"1",
    "location":"1"
  }
]

The event items contain the required fields (id, text, start, end, resource, join - see also DayPilot.Event.data for the event structure description) and also a couple of custom fields that will help us when working with the items on the client side (person, location, type).

backend_assigments.php

<?php
require_once '_db.php';

$stmt = $db->prepare("SELECT * FROM assignment WHERE NOT ((assignment_end <= :start) OR (assignment_start >= :end))");
$stmt->bindParam(':start', $_GET['start']);
$stmt->bindParam(':end', $_GET['end']);
$stmt->execute();
$result = $stmt->fetchAll();

class Event {}
$events = array();

$location = $_GET['location'];

foreach($result as $row) {
  $active = $row['location_id'] == $location;

  if ($active) {
    $e = new Event();
    $e->id = $row['id'];
    $e->text = "";
    $e->start = $row['assignment_start'];
    $e->end = $row['assignment_end'];
    $e->resource = $row['person_id'];

    $e->person = $row['person_id'];
    $e->location = $row['location_id'];
    $e->join = $row['id'];
    $events[] = $e;

    $e = new Event();
    $e->id = "L".$row['id'];
    $e->text = "";
    $e->start = $row['assignment_start'];
    $e->end = $row['assignment_end'];
    $e->resource = 'L'.$row['location_id'];

    $e->person = $row['person_id'];
    $e->location = $row['location_id'];
    $e->type = 'location';
    $e->join = $row['id'];
    $events[] = $e;
  }
  else {
    $e = new Event();
    $e->id = $row['id'];
    $e->text = "";
    $e->start = $row['assignment_start'];
    $e->end = $row['assignment_end'];
    $e->resource = $row['person_id'];

    $e->person = $row['person_id'];
    $e->location = $row['location_id'];
    $e->type = 'inactive';
    $e->join = $row['id'];
    $events[] = $e;
  }
}

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

Database Storage

By default, the shift planning application uses file-based SQLite database. It is created automatically (daypilot.sqlite file) in the application directory on startup (make sure that the application has write permissions). This way you can test the application without any additional database configuration.

You can switch to MySQL backend easily by changing the _db.php file as follows:

<?php

// use sqlite
// require_once '_db_sqlite.php';

// use MySQL
require_once '_db_mysql.php';

Make sure that _db_mysql.php uses the correct server address/port, username and password:

<?php
$host = "127.0.0.1";
$port = 3306;
$username = "username";
$password = "password";
$database = "shift-planning";

The database ("shift-planning" by default) will be created and initialized automatically if it doesn't exist.

MySQL Database Schema

Structure of 'person' table:

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

Structure of 'location' table:

CREATE TABLE `location` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(200) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Structure of 'assignment' table:

CREATE TABLE `assignment` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `person_id` INT(11) NULL DEFAULT NULL,
  `location_id` INT(11) NULL DEFAULT NULL,
  `assignment_start` DATETIME NULL DEFAULT NULL,
  `assignment_end` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);