Overview

  • Assign work orders to employees

  • Employees are organized in groups

  • See a queue of unscheduled work orders

  • Schedule a work order using drag and drop

  • Unschedule a work order and return it back to the queue

  • React frontend created using DayPilot React Scheduler component

  • PHP backend with MySQL storage

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.

Queue of Unscheduled Work Orders

Our planning system will display a queue of unscheduled work orders on the left side. You can add a new work order using the “Add Task” button and specify a name and duration.

react work order planning system php mysql unscheduled queue

The new work order will be added to the bottom of the queue. You can change the task priority by dragging it to a higher position within the queue.

Schedule a Work Order using Drag and Drop

You can schedule a task by dragging it from the queue to the schedule grid on the right side.

react work order planning system php mysql schedule drag drop

Assign the person and time by dragging the task over the schedule grid:

react work order planning system php mysql assign person

The task will be saved at the target position on drop:

react work order planning system php mysql drop

If you want to unschedule the task again you can drag it back to the queue.

Running the Application

The application consists of two projects:

  • React frontend project

  • PHP backend project

When deploying the application, the React frontend project needs to be in the application root and the PHP backend needs to be in the /api directory.

In order to make development and debugging easier, the React project is configured to proxy the /api/* requests to the PHP project running at port 8090:

setupProxy.js

const { createProxyMiddleware } = require('http-proxy-middleware');

module.exports = function(app) {
  app.use(
    '/api',
    createProxyMiddleware({
      target: 'http://localhost:8090',
      changeOrigin: true,
    })
  );
};

To run the PHP project, use the following command:

Linux

php -S 127.0.0.1:8090 -t /home/daypilot/tutorials/react-work-order-php

Windows

php.exe -S 127.0.0.1:8090 -t C:\Users\daypilot\tutorials\react-work-order-php

To run the React project, make sure that the dependencies are loaded first:

npm install

Now you can run the React project:

npm run start

Work Order Queue

react work order planning system php mysql task queue

The queue of unscheduled work orders is implemented using a new Queue component which has been introduced in DayPilot Pro for JavaScript 2021.2.4972. The queue uses an API similar to the React Scheduler component API which makes it easier to integrate it into your application.

You can add the Queue component to the React application using the <DayPilotQueue> tag to the JSX:

<div className={"queue"}>
  <button onClick={ev => this.clickAdd(ev)}>Add Task</button>
  <DayPilotQueue
    {...this.state.config}
    ref={component => this.queue = component && component.control}
  />
</div>

The Queue component behavior is controlled by properties which you can specify using <DayPilotQueue> attributes. In this example, we set the properties using this.state.config object and assign them to the Queue component using the spread syntax (three dots).

Our configuration object specifies 3 event handlers:

  • onEventClick - this event is fired when the user clicks a queue item

  • onEventMove - this event is fired when a queue item is moved using drag and drop (that can be either a new position within the queue or an item dragged from the React Scheduler component)

  • onBeforeEventRender - this event is fired during rendering and it lets you customize the event appearance

The queue is ordered - it respects the original order of tasks as supplied using the events property. We load the unscheduled work order data using a call to /api/work_order_unscheduled_list.php script. It returns the task data in JSON format. For the JSON array item structure please see the DayPilot.Event.data property documentation.

  async componentDidMount() {

    const {data: unscheduled} = await axios.get("/api/work_order_unscheduled_list.php");

    const events = unscheduled.map(data => {
      return {
        ...data,
        duration: DayPilot.Duration.ofMinutes(data.duration)
      };
    });

    this.queue.update({
      events
    });

  }

The <DayPilotQueue> component automatically activates the items so that can be dragged to the React Scheduler (see below).

This is the complete source of the Queue component (Queue.js):

import React, {Component} from 'react';
import axios from "axios";
import {DayPilot, DayPilotQueue} from "daypilot-pro-react";

export class Queue extends Component {

  // ...

  constructor(props) {
    super(props);

    this.state = {
      config: {
        contextMenu: this.menu,
        onEventClick: args => {
          this.clickQueueEdit(args.e.data);
        },
        onEventMove: async args => {
          const {data: item} = await axios.post("/api/work_order_move.php", {
            id: args.e.data.id,
            position: args.position
          });
          if (args.external) {
            args.source.events.remove(args.e);
          }
        },
        onBeforeEventRender: args => {
          const duration = new DayPilot.Duration(args.data.start, args.data.end);
          args.data.html = "";

          args.data.areas = [
            {
              top: 11,
              right: 5,
              height: 16,
              width: 16,
              fontColor: "#999",
              symbol: "icons/daypilot.svg#minichevron-down-4",
              visibility: "Visible",
              action: "ContextMenu",
              menu: this.menu,
              style: "background-color: rgba(255, 255, 255, .5); border: 1px solid #aaa; box-sizing: border-box; cursor:pointer;"
            },
            {
              top: 0,
              left: 6,
              bottom: 0,
              width: 12,
              fontColor: "#999",
              symbol: "icons/daypilot.svg#move-vertical",
              style: "cursor: move",
              visibility: "Hover",
              toolTip: "Drag task to the scheduler"
            },
            {
              top: 3,
              left: 20,
              text: args.data.text
            },
            {
              bottom: 3,
              left: 20,
              text: this.formatDuration(duration)
            }
          ];
        }
      }
    };

  }
  
  // ...

  async componentDidMount() {

    const {data: unscheduled} = await axios.get("/api/work_order_unscheduled_list.php");

    const events = unscheduled.map(data => {
      return {
        ...data,
        duration: DayPilot.Duration.ofMinutes(data.duration)
      };
    });

    this.queue.update({
      events
    });

  }

  render() {
    return (
      <div className={"queue"}>
        <button onClick={ev => this.clickAdd(ev)}>Add Task</button>
        <DayPilotQueue
          {...this.state.config}
          ref={component => this.queue = component && component.control}
        />
      </div>
    );
  }
}

The work_order_unscheduled_list.php PHP script returns the work order data in JSON format:

<?php
require_once '_db.php';

$result = db_get_unscheduled();

class Event {}
$events = array();

foreach($result as $row) {
  $e = new Event();
  $e->id = $row['id'];
  $e->text = $row['name'];
  $e->start = $row['start'];
  $e->end = $row['end'];

  $events[] = $e;
}

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

Work Order Schedule

react work order planning system php mysql schedule grid

The main schedule grid is created using React Scheduler component. It displays the employees as rows (they can be organized in teams) and time slots as columns. The Scheduler grid uses 30-minute cells but it can be adjusted as needed (cellDuration property).

Scheduler JSX

<DayPilotScheduler
  {...this.state.config}
/>

The configuration is stored in the config property of the component state. It uses the Scheduler properties (and events) to customize the appearance and behavior.

this.state = {
  config: {
    eventHeight: 40,
    cellWidth: 60,
    timeHeaders: [{groupBy: "Day"}, {groupBy: "Hour"}],
    scale: "CellDuration",
    cellDuration: 30,
    showNonBusiness: false,
    treePreventParentUsage: true,
    days: DayPilot.Date.today().daysInMonth(),
    startDate: DayPilot.Date.today().firstDayOfMonth(),
    timeRangeSelectedHandling: "Enabled",
    treeEnabled: true,
    // ....
  }
};

We load the people/employees and work order data in componentDidMount(). The API calls are made using axios.get(). We run them in parallel and wait for both of them to complete. As soon as the API calls are complete, we update the Scheduler using the update() method.

componentDidMount() {
  this.loadData();
  this.scheduler.scrollTo(DayPilot.Date.today());
}

async loadData() {

  const start = this.scheduler.visibleStart();
  const end = this.scheduler.visibleEnd();
  const promiseResources = axios.get(`/api/work_order_resources.php`);
  const promiseEvents = axios.get(`/api/work_order_list.php?start=${start}&end=${end}`);
  const [{data: resources}, {data: events}] = await Promise.all([promiseResources, promiseEvents]);

  this.scheduler.update({
    resources,
    events
  });

}

This is how our React Scheduler component looks now:

import React, {Component} from 'react';
import {DayPilot, DayPilotScheduler} from "daypilot-pro-react";
import axios from "axios";
import {Queue} from "./Queue";

export class Scheduler extends Component {

  constructor(props) {
    super(props);

    this.state = {
      config: {
        eventHeight: 40,
        cellWidth: 60,
        timeHeaders: [{groupBy: "Day"}, {groupBy: "Hour"}],
        scale: "CellDuration",
        cellDuration: 30,
        showNonBusiness: false,
        treePreventParentUsage: true,
        days: DayPilot.Date.today().daysInMonth(),
        startDate: DayPilot.Date.today().firstDayOfMonth(),
        timeRangeSelectedHandling: "Enabled",
        treeEnabled: true,
        // ....
      }
    };
  }

  componentDidMount() {
    this.loadData();
    this.scheduler.scrollTo(DayPilot.Date.today());
  }

  async loadData() {

    const start = this.scheduler.visibleStart();
    const end = this.scheduler.visibleEnd();
    const promiseResources = axios.get(`/api/work_order_resources.php`);
    const promiseEvents = axios.get(`/api/work_order_list.php?start=${start}&end=${end}`);
    const [{data: resources}, {data: events}] = await Promise.all([promiseResources, promiseEvents]);

    this.scheduler.update({
      resources,
      events
    });

  }

  render() {
    return (
      <div className={"wrap"}>
        <div className={"left"}>
          <Queue ref={component => this.queue = component}/>
        </div>
        <div className={"right"}>
          <DayPilotScheduler
            {...this.state.config}
            ref={component => this.scheduler = component && component.control}
          />
        </div>
      </div>
    );
  }
}

New Work Order

react work order planning system php mysql new work order

There are two ways to schedule a work order:

  • You can add a work order to the queue of unscheduled tasks and drag it to the schedule later

  • You can create a work order by selecting a time range directly in the Schedule

Now we will add the logic for the second option - creation of a scheduled work order directly in the grid.

We need to handle onTimeRangeSelected event handler which is fired when a time range selection is complete. Our event handler will open a modal dialog (to ask for work order details) and make an API call to create the work order in the database. As soon as the API call is complete, we add the new task to the Scheduler using events.add(). This is more efficient than reloading all the work order data.

this.state = {
  config: {
    // ...
    onTimeRangeSelected: async args => {
      const modal = await DayPilot.Modal.prompt("Create a new task:", "Task 1");

      let dp = args.control;
      dp.clearSelection();
      if (!modal.result) {
        return;
      }

      let params = {
        text: modal.result,
        start: args.start,
        end: args.end,
        resource: args.resource
      };

      const {data: result} = await axios.post("/api/work_order_create.php", params);

      dp.events.add(result);

    },
    // ...
  }
}

This is the source code of work_order_create.php script which saves the work order in the database:

<?php
require_once '_db.php';

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

$start = new DateTime($params->start);
$end = new DateTime($params->end);
$diff = $start->diff($end);
$duration = $diff->h*60 + $diff->i;

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

class Result {}

$response = new Result();
$response->start = $params->start;
$response->end = $params->end;
$response->resource = $params->resource;
$response->text = $params->text;
$response->id = $db->lastInsertId();
$response->duration = $duration;

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

Schedule a Queued Work Order

react work order planning system php mysql schedule queued task

You can move the unscheduled tasks from the Queue to the Scheduler to assign a person and time.

This action is handled using onEventMove event handler of the Scheduler. In the event handler, we notify the server (/api/work_order_move.php endpoint) and as soon as the HTTP call is complete we check if the event came from the Queue and remove it from there.

Scheduler.js

onEventMove: async args => {

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

  await axios.post("/api/work_order_move.php", params);

  if (args.external) {
    this.queue.remove(args.e.id());
  }

},

Here is the source of the /api/work_order_move.php script:

<?php
require_once '_db.php';

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

if (!isset($params->resource)) {

  $now = (new DateTime("now"))->format('Y-m-d H:i:s');

  $stmt = $db->prepare("UPDATE events SET resource_id = null, ordinal = :ordinal, ordinal_priority = :priority WHERE id = :id");
  $stmt->bindParam(':id', $params->id);
  $stmt->bindParam(':ordinal', $params->position);
  $stmt->bindParam(':priority', $now);
  $stmt->execute();
}
else {
  $stmt = $db->prepare("UPDATE events SET start = :start, end = :end, resource_id = :resource, ordinal = null, ordinal_priority = null WHERE id = :id");
  $stmt->bindParam(':id', $params->id);
  $stmt->bindParam(':start', $params->start);
  $stmt->bindParam(':end', $params->end);
  $stmt->bindParam(':resource', $params->resource);
  $stmt->execute();
}

db_compact_ordinals();

class Result {}

$response = $params;

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

Database Storage

By default, the PHP API backend uses an automatically-created SQLite database so you can try the project without any DB configuration. The database file (daypilot.sqlite) is created automatically in the PHP project root (just make sure that the process has permissions to write there).

You may want to switch to MySQL database. You can do that by editing the _db.php file and changing the file header as follows:

<?php

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

// use MySQL
require_once '_db_mysql.php';


// ... rest of the file unchanged

MySQL Database Schema

The MySQL database includes three tables (events, groups, and resources):

CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  start DATETIME,
  end DATETIME,
  resource_id VARCHAR(30),
  ordinal integer,
  ordinal_priority datetime
  );

CREATE TABLE groups (
  id INTEGER  NOT NULL PRIMARY KEY,
  name VARCHAR(200)  NULL);

CREATE TABLE resources (
  id INTEGER  PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(200)  NULL,
  group_id INTEGER  NULL);

The groups and resources tables define the Scheduler rows. The events table includes the work orders. Unscheduled work orders will have NULL value in the the resource_id field.