Features

  • This machine job scheduling application uses 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 backed is implemented in PHP.

  • Includes a trial version of DayPilot Pro for JavaScript

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.

Generating Project Prototype

html5-machine-production-job-scheduling-php-mysql-configurator.png

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"

  • Event Height: "40"

  • 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: 40,
  crosshairType: "Header",
  autoScroll: "Drag",
  eventHeight: 40,
  floatingEvents: true,
  eventMovingStartEndEnabled: false,
  eventResizingStartEndEnabled: false,
  timeRangeSelectingStartEndStartEndEnabled: false,
  allowEventOverlap: true,
  groupConcurrentEvents: false,
  eventStackingLineHeight: 100,
  timeRangeSelectedHandling: "Enabled",
  onTimeRangeSelected: function (args) {
    DayPilot.Modal.prompt("Create a new event:", "Event 1").then(function(modal) {
      var dp = args.control;
      dp.clearSelection();
      if (!modal.result) { return; }
      dp.events.add(new DayPilot.Event({
        start: args.start,
        end: args.end,
        id: DayPilot.guid(),
        resource: args.resource,
        text: modal.result
      }));
    });
  },
  eventMoveHandling: "Update",
  onEventMoved: function (args) {
    var dp = args.control;
    dp.message("Event moved");
  },
  eventResizeHandling: "Update",
  onEventResized: function (args) {
    var dp = args.control;
    dp.message("Event resized");
  },
  eventDeleteHandling: "Disabled",
  eventClickHandling: "Enabled",
  onEventClicked: function (args) {
    // scheduler
    var dp = args.control;
    dp.message("Event clicked");
  },
  eventHoverHandling: "Disabled",
  contextMenu: new DayPilot.Menu({
    items: [
      { text: "Delete", onClick: function(args) { var dp = args.source.calendar; dp.events.remove(args.source); } }
    ]
  }),
}

Loading Machine Data using PHP

html5-machine-production-job-scheduling-php-mysql-resources.png

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 MySQL database and returns the result in JSON format (backend_resources.php).

Our sample database stores machines 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 resources database table. The nodes can be used as resources when scheduling the production jobs.

<?php
require_once '_db.php';

$scheduler_groups = loadGroups();

class Group {}
class Resource {}

$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'];
    $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"},
    {"id":"2","name":"Cutting Machine 2"}
  ]},
  {"id":"group_3","name":"Sandblasting","expanded":true,"children":[
    {"id":"6","name":"Blast Booth 1"}
  ]},
  {"id":"group_2","name":"Welding","expanded":true,"children":[
    {"id":"3","name":"Welding Cell 1"},
    {"id":"4","name":"Welding Cell 2"},
    {"id":"5","name":"Welding Cell 3"}
   ]}
]

Scheduling a New Production Job

html5-machine-production-job-scheduling-php-mysql-job-creating.png

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: function (args) {
    DayPilot.Modal.prompt("Create a new job:", "Job").then(function (modal) {
        var dp = args.control;
        dp.clearSelection();
        if (!modal.result) {
            return;
        }
        var params = {
            start: args.start.toString(),
            end: args.end.toString(),
            text: modal.result,
            resource: args.resource
        };
        $.ajax({
            type: 'POST',
            url: '/backend_events_create.php',
            data: JSON.stringify(params),
            success: function (data) {
                var list = data.events;
                list.forEach(function(data) {
                    var e = dp.events.find(data.id);
                    if (e) {
                        e.data.text = text;
                        dp.events.update(e);
                    }
                    else {
                        dp.events.add(new DayPilot.Event(data));
                    }
                });
                dp.message("Job created");
            },
            contentType: "application/json",
            dataType: 'json'
        });
    });
}

This updated event handler calls 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).

backend_events_create.php

<?php
require_once '_db.php';

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

class Result {}
$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

html5-machine-production-job-scheduling-php-mysql-next-link.png

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: function(args) {
  args.data.barColor = args.data.color;

  var 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: 2,
          bottom: 2,
          width: 16,
          height: 16,
          backColor: "#fff",
          style: "box-sizing: border-box; border-radius: 7px; padding-left: 3px; border: 1px solid #ccc;font-size: 14px;line-height: 14px;color: #999;",
          html: "&raquo;",
          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.

html5-machine-production-job-scheduling-php-mysql-follow-up.png

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

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

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: function (args) {
  if (args.areaData === "event-copy") {
      args.preventDefault();

      var params = {
          start: args.newStart.toString(),
          end: args.newEnd.toString(),
          text: args.e.text(),
          resource: args.newResource,
          link: {
              from: args.e.id()
          }
      };
      $.ajax({
          type: 'POST',
          url: 'backend_events_create.php',
          data: JSON.stringify(params),
          success: function (data) {
              var list = data.events;
              list.forEach(function(data) {
                  var e = dp.events.find(data.id);
                  if (e) {
                      e.data.hasNext = data.hasNext;
                      dp.events.update(e);
                  }
                  else {
                      dp.events.add(new DayPilot.Event(data));
                      dp.links.add({
                          from: args.e.id(),
                          to: data.id
                      });
                  }
              });

              dp.message("Job created");
          },
          contentType: "application/json",
          dataType: 'json'
      });

  }
  else {
      var params = {
          id: args.e.id(),
          start: args.newStart.toString(),
          end: args.newEnd.toString(),
          resource: args.newResource
      };
      $.ajax({
          type: 'POST',
          url: 'backend_events_move.php',
          data: JSON.stringify(params),
          success: function (data) {
              dp.message("Job moved");
          },
          contentType: "application/json",
          dataType: 'json'
      });
  }

}

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

html5-machine-production-job-scheduling-php-mysql-color.png

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: function(args) {
              var params = {
                  id: args.source.id(),
              };
              $.ajax({
                  type: 'POST',
                  url: 'backend_events_delete.php',
                  data: JSON.stringify(params),
                  success: function (data) {
                      data.deleted.forEach(function(id) {
                          dp.events.removeById(id);
                      });
                      data.updated.forEach(function(data) {
                          var e = dp.events.find(data.id);
                          if (e) {
                              e.data.hasNext = data.hasNext;
                              dp.events.update(e);
                          }
                      });

                      dp.message("Job deleted");
                  },
                  contentType: "application/json",
                  dataType: 'json'
              });

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

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

function updateColor(e, color) {
    var params = {
        join: e.data.join,
        color: color
    };
    $.ajax({
        type: 'POST',
        url: 'backend_events_setcolor.php',
        data: JSON.stringify(params),
        success: function (data) {
            var list = data.events;
            list.forEach(function(data) {
                var e = dp.events.find(data.id);
                if (e) {
                    e.data.color = color;
                    dp.events.update(e);
                }
            });
            dp.message("Color updated");
        },
        contentType: "application/json",
        dataType: 'json'
    });
}

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

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