PHP Machine/Production Job Scheduling Application (MySQL)

This tutorial shows how to create a PHP web application for machine/production job scheduling. The visual HTML5 scheduling grid displays task assignments, duration and dependencies. Users can use drag and drop to change the schedule.

PHP Machine/Production Job Scheduling Application (MySQL)

Downloads	Download Source PHP web application with source code for download. 290 kB
Languages	PHP, HTML5, JavaScript
Technologies	MySQL

Features

This machine job scheduling application uses the JavaScript Scheduler component from DayPilot Pro for JavaScript to display the scheduling grid.
The machines displayed on the Y axis, grouped by type.
The production jobs (tasks) can be assigned to a given machine and a specific time.
The Scheduler displays job dependencies using links.
Users can edit the job details and change the job color.
The schedule data are stored a MySQL database.
REST backend is implemented in PHP (PHP 8+).

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.

Generating a Project Prototype

Online Scheduler Component Configurator and Project Generator

The prototype of this project has been generated using DayPilot Scheduler UI Builder. This online app lets you configure the Scheduler component using a visual interface and download a generated project including all dependencies.

This project was generated using the following options:

Range: "Week"
Time Headers: "Days/Hours"
Non-Business Time: "Hide"
Business Begins: "9 AM"
Business Ends: "6 PM"
Cell Width: "80"
Event Height: "50"
Event Click Action: "Message"
Event Context Menu: "Enabled"
Event Deleting: "Disabled"

We will use the generated Scheduler config object and adjust it as needed.

The generated Scheduler config looks like this:

{
  locale: "en-us",
  days: 7,
  startDate: DayPilot.Date.today().firstDayOfWeek(),
  timeHeaders: [
    { groupBy: "Day" },
    { groupBy: "Hour" }
  ],
  scale: "Hour",
  showNonBusiness: false,
  businessBeginsHour: 9,
  businessEndsHour: 18,
  businessWeekends: false,
  cellWidthSpec: "Fixed",
  cellWidth: 80,
  crosshairType: "Header",
  autoScroll: "Drag",
  eventHeight: 50,
  floatingEvents: true,
  eventMovingStartEndEnabled: false,
  eventResizingStartEndEnabled: false,
  timeRangeSelectingStartEndStartEndEnabled: false,
  allowEventOverlap: true,
  groupConcurrentEvents: false,
  eventStackingLineHeight: 100,
  timeRangeSelectedHandling: "Enabled",
  onTimeRangeSelected: async (args) => {
    const modal = await DayPilot.Modal.prompt("Create a new event:", "Event 1");
    const dp = args.control;
    dp.clearSelection();
    if (modal.canceled) { return; }
    dp.events.add({
      start: args.start,
      end: args.end,
      id: DayPilot.guid(),
      resource: args.resource,
      text: modal.result
    });
  },
  eventMoveHandling: "Update",
  onEventMoved: (args) => {
    const dp = args.control;
    dp.message("Event moved");
  },
  eventResizeHandling: "Update",
  onEventResized: (args) => {
    const dp = args.control;
    dp.message("Event resized");
  },
  eventDeleteHandling: "Disabled",
  eventClickHandling: "Enabled",
  onEventClicked: (args) => {
    const dp = args.control;
    dp.message("Event clicked");
  },
  eventHoverHandling: "Disabled",
  contextMenu: new DayPilot.Menu({
    items: [
      { text: "Delete", onClick: (args) => { const dp = args.source.calendar; dp.events.remove(args.source); } }
    ]
  }),
}

Loading Machine Data using PHP

Production Machines and Groups (PHP Machiine Job Scheduling Application)

We will load the machine data to Scheduler rows using dp.rows.load() method. This method load the row data from the specified URL using an AJAX call. As soon as the data is available it updates the Scheduler and displays the machines as rows (grouped by machine type).

Loading the rows:

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

The server-side REST endpoint loads the machine data from a MySQL database and returns the result in a JSON format (backend_resources.php).

Our sample database stores machine groups in a special MySQL table (groups). This table contains defines three machine groups (“Cutting”, “Sandblasting”, “Welding”).

Each group contains the machines as nodes. The machines are stored in the resources database table. The nodes can be used as resources when scheduling the production jobs.

This is the source code of the backend_resources.php script:

<?php
require_once '_db.php';

$scheduler_groups = loadGroups();

class Group {
  public $id;
  public $name = "";
  public $expanded = false;
  public $children = array();
}
class Resource {
  public $id;
  public $name = "";
  public $location = "";
  public $state = "";
}

$groups = array();

foreach($scheduler_groups as $group) {
  $g = new Group();
  $g->id = "group_".$group['id'];
  $g->name = $group['name'];
  $g->expanded = true;
  $g->children = array();
  $groups[] = $g;

  $scheduler_resources = loadResources($group['id']);

  foreach($scheduler_resources as $resource) {
    $r = new Resource();
    $r->id = $resource['id'];
    $r->name = $resource['name'];
    $r->location = $resource['location'];
    $r->state = $resource['state'];
    $g->children[] = $r;
  }
}

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

Sample JSON response:

[
    {
        "id": "group_1",
        "name": "Cutting",
        "expanded": true,
        "children": [
            {
                "id": 1,
                "name": "Cutting Machine 1",
                "location": "Building A",
                "state": "Ready"
            },
            {
                "id": 2,
                "name": "Cutting Machine 2",
                "location": "Building A",
                "state": "Ready"
            }
        ]
    },
    {
        "id": "group_3",
        "name": "Sandblasting",
        "expanded": true,
        "children": [
            {
                "id": 6,
                "name": "Blast Booth 1",
                "location": "Building C",
                "state": "Maintenance"
            }
        ]
    },
    {
        "id": "group_2",
        "name": "Welding",
        "expanded": true,
        "children": [
            {
                "id": 3,
                "name": "Welding Cell 1",
                "location": "Building B",
                "state": "Maintenance"
            },
            {
                "id": 4,
                "name": "Welding Cell 2",
                "location": "Building B",
                "state": "Ready"
            },
            {
                "id": 5,
                "name": "Welding Cell 3",
                "location": "Building B",
                "state": "Ready"
            }
        ]
    }
]

Scheduling a New Production Job

Scheduling a New Production Job (PHP Machine Job Scheduling Application)

A new job can be created by selecting a time range using drag and drop.

In order to enable this feature we need to extend the onTimeRangeSelected event handler in the Scheduler config object:

onTimeRangeSelected: async (args) => {
    const modal = await DayPilot.Modal.prompt("Create a new job:", "Job");

    scheduler.clearSelection();
    if (modal.canceled) {
        return;
    }
    const params = {
        start: args.start,
        end: args.end,
        text: modal.result,
        resource: args.resource
    };

    const {data: result} = await DayPilot.Http.post("backend_events_create.php", params);
    const list = result.events;
    list.forEach(data => {
        const e = scheduler.events.find(data.id);
        if (e) {
            e.data.text = data.text;
            scheduler.events.update(e);
        }
        else {
            scheduler.events.add(data);
        }
    });
    scheduler.message("Job created");
}

This updated event handler calls the backend_events_create.php endpoint to create a new job in the MySQL database. This PHP script creates a new record and assigns the selected time (start and end) and a machine ID (resource_id).

Here is the backend_events_create.php implementation:

<?php
require_once '_db.php';

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

class Result {
    public $events = array();
}
$response = new Result();
$response->events = array();


$id = createEvent($params->start, $params->end, $params->text, $params->resource);

if (isset($params->link)) {

    updateEventHasNext($params->link->from, true);

    $from = loadEvent($params->link->from);
    
    $response->events[] =  $from;

    createLink($params->link->from, $id);

    updateEventProperties($id, $from->text, $from->join, $from->color);

}

$response->events[] = loadEvent($id);

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

Creating a Follow-Up Job on a Different Machine

Creating a Follow-Up Job (PHP Machine Job Scheduling Application)

In this step we will customize the job ("event", as it is called in the Scheduler) to display an icon for creating follow-up jobs using drag and drop.

We will add a new onBeforeEventRender event handler to the Scheduler config:

onBeforeEventRender: (args) => {
    args.data.barColor = args.data.color;

    const duration = new DayPilot.Duration(args.data.start, args.data.end);
    args.data.html = `<div><b>${args.data.text}</b><br>${duration.toString("h")} hours</div>`;

    if (args.data.hasNext) {
        return;
    }
    args.data.areas = [
        {
            right: 5,
            top: 15,
            width: 20,
            height: 20,
            padding: 2,
            backColor: "#ffffff",
            fontColor: "#999999",
            style: "border-radius: 50%; border: 1px solid #ccc;",
            symbol: "/icons/daypilot.svg#minichevron-right-2",
            toolTip: "Drag to schedule next step",
            action: "Move",
            data: "event-copy"
        }
    ];
}

This event handler adjusts the event HTML (to display the duration) and creates an icon in the lower-right corner using an active area. This active area will serve as a drag handle for creating follow-up jobs.

Dragging the follow-up icon triggers event moving action (action: "Move") and passes custom status code to the event moving event handler (data: "event-copy") so we can detect it later.

Follow-Up Job Drag and Drop (PHP Machine Job Scheduling Application)

Now we need to customize the event moving action using onEventMoving event handler:

onEventMoving: (args) => {
    if (args.areaData && args.areaData === "event-copy") {
        args.start = args.end.addHours(-1);
        if (args.e.end() > args.start) {
            args.allowed = false;
        }
        if (args.allowed) {
            args.link = {
                from: args.e,
                color: "#666"
            };
        }
    }
}

If a follow-up drag is detected using args.areaData it adjusts the target position (the target position will have a duration of 1 hour) and displays a link from the source (args.link).

As soon as the the user finishes the dragging onEventMove handler will be fired:

onEventMove: async (args) => {
    if (args.areaData === "event-copy") {
        args.preventDefault();

        const params = {
            start: args.newStart,
            end: args.newEnd,
            text: args.e.text(),
            resource: args.newResource,
            link: {
                from: args.e.id()
            }
        };

        const {data: result} = await DayPilot.Http.post("backend_events_create.php", params);
        const list = result.events;
        list.forEach(data => {
            const e = scheduler.events.find(data.id);
            if (e) {
                e.data.hasNext = data.hasNext;
                scheduler.events.update(e);
            }
            else {
                scheduler.events.add(data);
                scheduler.links.add({
                    from: args.e.id(),
                    to: data.id
                });
            }
        });

        scheduler.message("Job created");

    }
    else {
        const params = {
            id: args.e.id(),
            start: args.newStart,
            end: args.newEnd,
            resource: args.newResource
        };

        const {data: result} = await DayPilot.Http.post("backend_events_move.php", params);
        scheduler.message("Job moved");
    }

}

The event handler checks the moving mode:

If args.areaData is set to "event-copy" it means a follow-up event needs to be created (the handler will call backend_events_create.php endpoint).
In other cases, move the original event to the new position (the handler will call backend_events_move.php endpoint).

Changing Color of Scheduled Jobs using Context Menu

Changing Color of Scheduled Jobs (PHP Machine Job Scheduling Application)

We will also add a context menu to the job box that will provide additional options, including an option to change color:

contextMenu: new DayPilot.Menu({
    items: [
        {
            text: "Delete",
            onClick: async (args) => {
                const params = {
                    id: args.source.id(),
                };

                const {data: result} = await DayPilot.Http.post("backend_events_delete.php", params);

                result.deleted.forEach(id => {
                    scheduler.events.removeById(id);
                });
                result.updated.forEach(data => {
                    const e = scheduler.events.find(data.id);
                    if (e) {
                        e.data.hasNext = data.hasNext;
                        scheduler.events.update(e);
                    }
                });

                scheduler.links.list.forEach(link => {
                    if (result.deleted.includes(link.to)) {
                        scheduler.links.remove(link);
                    }
                });

                scheduler.message("Job deleted");
            }
        },
        {
            text: "-"
        },
        {
            text: "Blue",
            icon: "icon icon-blue",
            color: "#1155cc",
            onClick: (args) => { app.updateColor(args.source, args.item.color); }
        },
        {
            text: "Green",
            icon: "icon icon-green",
            color: "#6aa84f",
            onClick: (args) => { app.updateColor(args.source, args.item.color); }
        },
        {
            text: "Yellow",
            icon: "icon icon-yellow",
            color: "#f1c232",
            onClick: (args) => { app.updateColor(args.source, args.item.color); }
        },
        {
            text: "Red",
            icon: "icon icon-red",
            color: "#cc0000",
            onClick: (args) => { app.updateColor(args.source, args.item.color); }
        }
    ]
})

The context menu items with color options call the app.pdateColor() method to record the change in the database using the backend_events_setcolor.php endpoint:

const app = {
    async updateColor(e, color) {
        const params = {
            join: e.data.join,
            color: color
        };
        const {data: result} = await DayPilot.Http.post("backend_events_setcolor.php", params);
        const list = result.events;
        list.forEach(data => {
            const e = scheduler.events.find(data.id);
            if (e) {
                e.data.color = color;
                scheduler.events.update(e);
            }
        });
        scheduler.message("Color updated");
    },
    
    // ...
    
};

PHP Backend (Database Functions)

The _db_functions.php file defines data layer helper functions, such as loadEvent(), loadEvents(), deleteEventWithLinks(), loadLinkByTo(), loadLinksByFrom(), etc. These functions are used by the REST endpoints to access the database.

<?php

class EventData {
    public $id;
    public $text = "";
    public $start;
    public $end;
    public $resource;
    public $color = "";
    public $join;
    public $hasNext = false;
}

function loadEvent($id) {
    global $db;

    $stmt = $db->prepare('SELECT * FROM events WHERE id = :id');
    $stmt->bindParam(':id', $id);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    $e = new EventData();
    $e->id = $row['id'];
    $e->text = $row['name'];
    $e->start = $row['start'];
    $e->end = $row['end'];
    $e->resource = $row['resource_id'];
    $e->color = $row['color'];
    $e->join = $row['join_id'];
    $e->hasNext = $row['has_next'] != 0;

    return $e;
}

function loadEvents($start, $end) {
    global $db;

    $stmt = $db->prepare('SELECT * FROM events WHERE NOT ((end <= :start) OR (start >= :end))');
    $stmt->bindParam(':start', $start);
    $stmt->bindParam(':end', $end);
    $stmt->execute();
    $result = $stmt->fetchAll();

    $events = array();

    foreach($result as $row) {
      $e = new EventData();
      $e->id = $row['id'];
      $e->text = $row['name'];
      $e->start = $row['start'];
      $e->end = $row['end'];
      $e->resource = $row['resource_id'];
      $e->color = $row['color'];
      $e->join = $row['join_id'];
      $e->hasNext = $row['has_next'] != 0;

      $events[] = $e;
    }

    return $events;
}

// returns an array of deleted event IDs
function deleteEventWithLinks($id) {

    $previous = loadLinksByTo($id);
    foreach($previous as $link) {
        deleteLink($link['id']);
    }

    $next = loadLinksByFrom($id);

    $result = array();

    foreach($next as $link) {
        $deleted = deleteEventWithLinks($link['to_id']);
        foreach($deleted as $eid) {
            $result[] = $eid;
        }
    }

    deleteEvent($id);
    $result[] = $id;

    return $result;
}

function loadLinksByTo($id) {

    global $db;

    $stmt = $db->prepare('SELECT * FROM links WHERE to_id = :id');
    $stmt->bindParam(':id', $id);
    $stmt->execute();
    return $stmt->fetchAll();
}

function loadLinksByFrom($id) {
    global $db;
    $stmt = $db->prepare('SELECT * FROM links WHERE from_id = :id');
    $stmt->bindParam(':id', $id);
    $stmt->execute();
    return $stmt->fetchAll();
}

function deleteLink($id) {
    global $db;
    $stmt = $db->prepare('DELETE FROM links WHERE id = :id');
    $stmt->bindParam(':id', $id);
    $stmt->execute();
}

function deleteEvent($id) {
    global $db;
    $stmt = $db->prepare('DELETE FROM events WHERE id = :id');
    $stmt->bindParam(':id', $id);
    $stmt->execute();
}

function loadEventsByJoin($join) {
    global $db;
    $stmt = $db->prepare('SELECT * FROM events WHERE join_id = :join');
    $stmt->bindParam(':join', $join);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function createEvent($start, $end, $text, $resource) {
    global $db;

    $stmt = $db->prepare("INSERT INTO events (name, start, end, resource_id) VALUES (:name, :start, :end, :resource)");
    $stmt->bindParam(':name', $text);
    $stmt->bindParam(':start', $start);
    $stmt->bindParam(':end', $end);
    $stmt->bindParam(':resource', $resource);
    $stmt->execute();

    $id = $db->lastInsertId();

    $stmt = $db->prepare("UPDATE events SET join_id = :id WHERE id = :id");
    $stmt->bindParam(":id", $id);
    $stmt->execute();

    return $id;
}

function updateEventHasNext($id, $hasNext) {
    global $db;

    $hn = +$hasNext;

    $stmt = $db->prepare("UPDATE events SET has_next = :hasnext WHERE id = :id");
    $stmt->bindParam(":id", $id);
    $stmt->bindParam(":hasnext", $hn);
    $stmt->execute();
}

function createLink($from, $to) {
    global $db;

    $stmt = $db->prepare("INSERT INTO links (from_id, to_id) VALUES (:from, :to)");
    $stmt->bindParam(':from', $from);
    $stmt->bindParam(':to', $to);
    $stmt->execute();
}


function updateEventProperties($id, $text, $join, $color) {
    global $db;

    $stmt = $db->prepare("UPDATE events SET name = :name, join_id = :join, color = :color WHERE id = :id");
    $stmt->bindParam(':name', $text);
    $stmt->bindParam(':join', $join);
    $stmt->bindParam(':color', $color);
    $stmt->bindParam(':id', $id);
    $stmt->execute();

}

function updateEventPosition($id, $start, $end, $resource) {
    global $db;

    $stmt = $db->prepare("UPDATE events SET start = :start, end = :end, resource_id = :resource WHERE id = :id");
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':start', $start);
    $stmt->bindParam(':end', $end);
    $stmt->bindParam(':resource', $resource);
    $stmt->execute();
}

function updateEventColor($id, $color) {
    global $db;

    $stmt = $db->prepare("UPDATE events SET color = :color WHERE id = :id");
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':color', $color);
    $stmt->execute();
}

function updateEventText($id, $text) {
    global $db;

    $stmt = $db->prepare("UPDATE events SET name = :name WHERE id = :id");
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':name', $text);
    $stmt->execute();
}

function loadLinks() {
    global $db;

    $stmt = $db->prepare('SELECT * FROM links');
    $stmt->execute();
    return $stmt->fetchAll();
}


function loadGroups() {
    global $db;
    return $db->query('SELECT * FROM groups ORDER BY name');
}


function loadResources($group) {
    global $db;

    $stmt = $db->prepare('SELECT * FROM resources WHERE group_id = :group ORDER BY name');
    $stmt->bindParam(':group', $group);
    $stmt->execute();
    return $stmt->fetchAll();
}

Backend Database

By default, the project uses an embedded SQLite database. This allows testing the project without setting up MySQL server. The application will automatically create daypilot.sqlite file and initialize it using the DB schema (just make sure the application has permissions to write to the project directory).

You can switch the backend to use MySQL database by editing _db.php file to look like this:

<?php

// 1. use sqlite
// require_once '_db_sqlite.php';
// 2. use MySQL
require_once '_db_mysql.php';

// shared db functions
require_once '_db_functions.php';

MySQL Database Schema

resources table

CREATE TABLE `resources` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(200) NULL DEFAULT NULL,
        `location` VARCHAR(200) NULL DEFAULT NULL,
        `state` VARCHAR(200) NULL DEFAULT NULL,
	`group_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;

links table

CREATE TABLE `links` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`from_id` INT(11) NULL DEFAULT NULL,
	`to_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

groups table

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

events table

CREATE TABLE `events` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` TEXT NULL,
	`start` DATETIME NULL DEFAULT NULL,
	`end` DATETIME NULL DEFAULT NULL,
	`resource_id` INT(11) NULL DEFAULT NULL,
	`color` VARCHAR(200) NULL DEFAULT NULL,
	`join_id` INT(11) NULL DEFAULT NULL,
	`has_next` TINYINT(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
);