PHP Shift Planning System (JavaScript/HTML5 Frontend, MySQL Database)

Features

• PHP shift planning web application tutorial

• The application displays a shift plan using a visual JavaScript Scheduler component

• You can plan shifts for multiple locations/positions

• Each day is split into three 8-hour shifts

• See all assignments for a selected location

• You can 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.

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

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

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

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

The data for other locations as displayed as well. If you switch to a different location the existing shift assignments will be displayed in gray color:

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

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

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

• backend_update_person.php

• backend_update_time.php

Shift Scheduler Configuration

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

• HTML5 Scheduler Tutorial (PHP)

The first 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

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

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[1].html = duration.totalHours() + "h";
  }
  if (args.row.data.type === "location") {
    args.row.backColor = "#e06146";
    args.row.fontColor = "#fff";
    args.row.columns[1].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

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

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

The backend_assignments.php script loads the shift assignments from MySQL database. The assignments for the active location (which is passed to the PHP script using location  query 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);

For the sake of simplicity, we use the built-in helper methods (rows.load() and events.load()) to load the data using two separate HTTP requests which cause two Scheduler updates. It is also possible to run two parallel HTTP requests, wait for both of them to complete and update the Scheduler just once.

Moving Shift Assignments using Drag and Drop

Each assignment is displayed as two boxes in the shift planning view - once in the location row (the first one) and once in the person row. The Scheduler offers several UI helpers to handle such complex assignments. We will use these features to customize the drag and drop behavior:

1. These two events are marked as joint events (both have the same join property value). This automatically enables event multi-moving - both events will be moved if you start dragging one of them. That helps when the users wants to moving the assignment in time. In this case, both boxes will be moved automatically and provide visual feedback to the user.

2. In the vertical direction (resources axis), we will only move the dragged item (not both of them) by setting multiMoveVerticalMode property to "Master". This will ensure that the box in the first ("Location") row will stay unmodified when we change the assigned person.

3. We will restrict the direction in which the event box can be moved. The event boxes in the first ("Location") row can be moved horizontally (to change the shift). The event boxes in the people rows can be only moved vertically (to change the person assignment). This behavior can be set in onBeforeEventRender event handler by setting moveVDisabled and moveHDisabled properties:

onBeforeEventRender: function(args) {
  var isLocation = args.data.type === "location";

  if (isLocation) {
    // ...
    args.data.moveVDisabled = true;
  }
  else {
    // ...
    args.data.moveHDisabled = true;
  }
}

Changing the shift (time):

Changing the assignment (person):

As soon as the user drops the event at the target location the Scheduler fires onEventMove event handler. We will use it to save the changes in the database:

onEventMove: function (args) {
  // console.log(args);
  var e = args.e;
  if (e.data.type === "location") {
    DayPilot.Http.ajax({
      url: "backend_update_time.php",
      method: "POST",
      data: {
        id: e.data.join,
        start: args.newStart,
        end: args.newEnd
      },
      success: function (rargs) {
        dp.message(rargs.data.message);
      }
    });
  } else {
    DayPilot.Http.ajax({
      url: "backend_update_person.php",
      method: "POST",
      data: {
        id: e.data.join,
        person: args.newResource
      },
      success: function (rargs) {
        dp.message(rargs.data.message);

        var locationAssignment = dp.events.find("L" + e.data.join);
        locationAssignment.data.person = args.newResource;
        dp.events.update(locationAssignment);
      }
    });
  }
},

First, we detect which event type has been dragged. 

• For the location row, we call backend_update_time.php endpoint to change the assignment time.

• For the people rows, we call backend_update_person.php endpoint to change the assignment person.

On the server side, we simply update the shift assigment record in the database:

backend_update_time.php

<?php
require_once '_db.php';

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

$stmt = $db->prepare("UPDATE assignment SET assignment_start = :start, assignment_end = :end WHERE id = :id");
$stmt->bindParam(':start', $params->start);
$stmt->bindParam(':end', $params->end);
$stmt->bindParam(':id', $params->id);
$stmt->execute();

class Result {}

$response = new Result();
$response->result = 'OK';
$response->message = 'Updated, id: '.$params->id;
$response->id = $db->lastInsertId();

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

backend_update_person.php

<?php
require_once '_db.php';

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

$stmt = $db->prepare("UPDATE assignment SET person_id = :person WHERE id = :id");
$stmt->bindParam(':person', $params->person);
$stmt->bindParam(':id', $params->id);
$stmt->execute();

class Result {}

$response = new Result();
$response->result = 'OK';
$response->message = 'Updated, id: '.$params->id;
$response->id = $db->lastInsertId();

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

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

History

• May 11, 2021: DayPilot Pro version upgraded to 2021.2.4972.

• November 6, 2020: DayPilot Pro version upgraded to 2020.4.4736. Updating text of the event in the location row when changing the person assignment. jQuery removed.

• May 21, 2020 - DayPilot Pro version upgraded to 2020.2.4470. Using tabular mode for row header columns.

• September 10, 2019 - DayPilot Pro version upgraded to 2019.3.3999. Assignment moving implemented.

• July 6, 2018 - Initial release