Features

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

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.

Loading the rows:

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

The server-side REST endpoint returns the row data in JSON format (backend_resources.php):

<?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();
  $g->eventHeight = 20;
  $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"}
  ],"eventHeight":20},
  {"id":"group_3","name":"Sandblasting","expanded":true,"children":[
    {"id":"6","name":"Blast Booth 1"}
  ],"eventHeight":20},
  {"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"}
   ],"eventHeight":20}
]

Creating a New 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 database. It assigns the selected time (start and end) and a machine ID to the new job.

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

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 = "<b>" + args.data.text + "</b><br>" + duration.toString("h") + " hours";

  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.

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

Now we can 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.addData({
                          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 (call backend_events_create.php endpoint)
  • otherwise move the original event to the new position (call backend_events_move.php endpoint)

Changing Scheduled Job Color 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)

_db_functions.php

<?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. It automatically creates daypilot.sqlite file and initializes 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`)
);