React: Activity Planning System (Node, Express, PostgreSQL)

Overview

• Resources organized by groups

• Reorder and move groups/resources using drag and drop

• Add events and milestones

• Row context menu with resource operations (add, edit, delete)

• Event context menu with event operations (edit, delete)

• Move events and milestones using drag and drop

• Edit events and milestone details using a modal dialog

• Built using the React Scheduler component from DayPilot Pro for JavaScript scheduling library.

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.

Activity Planning System: Resources as Rows

The activity planning application lets you define your own resources (organized in groups) that will be displayed on the vertical axis.

The rows can display:

• departments and people

• locations and facilities

• machines and devices

• areas of responsibility and activity types

Activity Planning: Timeline

The horizontal axis displays time. In our application, the timeline displays months and days but the scheduler component can be configured to display years, quarters, months, weeks, days, hours, minutes or custom units.

Activity Planning: Events and Milestones

The activity planning application lets you display events and milestones for your resources.

You can create new events and milestones using drag and drop. You can also move or resize existing events and milestones using drag and drop.

Application Structure

The application consists of two projects:

• the root project is a React frontend application

• the server directory contains the backend application that provides access to the PostgreSQL database using an API

If you run npm start in the root project, the script will build the React application and run it using the server. The server defines the API endpoints and serves the React application.

Frontend: React Application

The boilerplate of the React Scheduler project was generated using DayPilot UI Builder. You can use this online application to create and download a new React project with a pre-configured React Scheduler component.

For an introduction to using the React Scheduler component, please see the basic tutorial:

• React Scheduler Tutorial

Backend: Node/Express Application

You can find the Node/Express application that defines the API in the server directory.

Before running the backend project, it is necessary to create and initialize the PostgreSQL database manually (see the DDL below).

You’ll also need to adjust the PostgreSQL database connection parameters defined at the top of the index.js file of the server project:

const pool = new pg.Pool({
  host: '127.0.0.1',
  database: 'daypilot',
  user: 'username',
  password: 'password',
  port: 5432,
});

Loading Activity Planning Data

The event and resource data are loaded using two API calls. We run these HTTP requests in parallel and wait for both of them to complete.

When we have the data, we update the React Scheduler using the direct API by calling the update() method. It is also possible to update the Scheduler by adding the resource and event data to the config but the direct update is more efficient.

componentDidMount() {
  this.loadData();
}

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

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

}

The backend Node/Express project defines the /api/events and /api/resources API endpoints as follows:

The /api/events endpoint returns a JSON array with event and milestone data which are stored in the events PostgreSQL database table.

app.get("/api/events", async (req, res) => {
  const start = req.query.start;
  const end = req.query.end;

  const query = 'SELECT * FROM event WHERE NOT (("end" <= $1) OR (start >= $2))';
  const params = [start, end];

  try {
    const result = await pool.query(query, params);
    const data = result.rows.map(r => ({
      ...r,
      resource: r.resource_id,
      start: localDateTimeISOString(r.start),
      end:  localDateTimeISOString(r.end),
    }));
    res.status(200).json(data)
  }
  catch (e) {
    console.error(e);
    res.status(500).json(e);
  }

});

The /api/resources endpoint returns a JSON tree with a group/resource nodes.

app.get("/api/resources", async (req, res) => {

  try {
    const groups = await pool.query('SELECT * FROM resource_group ORDER BY ordinal ASC, ordinal_priority DESC');
    const resources = await pool.query("SELECT * FROM resource ORDER BY ordinal ASC, ordinal_priority DESC");

    const result = groups.rows.map(g => ({
      id: "G" + g.id,
      groupId: g.id,
      name: g.name
    }));
    result.forEach(g => {
      g.expanded = true;
      g.children = resources.rows.filter(r => r.group_id === g.groupId);
    });

    res.status(200).json(result);

  }
  catch (e) {
    console.error(e);
    res.status(500).json(e);
  }

});

Plan Events

You can create new events using drag and drop, by selecting the desired date range in the target row. The onTimeRangeSelected event handler opens a modal dialog where you can enter the event details:

onTimeRangeSelected: async args => {
  var dp = this.scheduler;

  const form = [
    {name: "Text", id: "text"}
  ];

  const data = {
    text: "Event 1",
    start: args.start,
    end: args.end,
    resource: args.resource,
    type: "Event"
  };

  const modal = await DayPilot.Modal.form(form, data);
  dp.clearSelection();
  if (modal.canceled) { return; }

  const {data: e} = await axios.post("/api/createEvent", modal.result);
  dp.events.add(e);
},

The modal dialog is created using DayPilot.Modal.form() from DayPilot Modal which lets you define the form fields programmatically. You can design your own modal dialog using Modal Dialog Builder app.

As soon as you confirm the event details, the event handler calls /api/createEvent endpoint to create the event in the PostgreSQL database:

app.post("/api/createEvent", async (req, res) => {

  const query = 'INSERT INTO event (text, start, "end", type, resource_id) VALUES ($1, $2, $3, $4, $5) RETURNING *';
  const params = [req.body.text, req.body.start, req.body.end, req.body.type, req.body.resource];

  try {
    const result = await pool.query(query, params);
    const data = result.rows.map(r => ({
      ...r,
      resource: r.resource_id,
      start: localDateTimeISOString(r.start),
      end:  localDateTimeISOString(r.end),
    }));
    res.status(200).json(data[0])
  }
  catch (e) {
    console.error(e);
    res.status(500).json(e);
  }

});

Plan Milestones

The milestone is a special kind of event with an adjusted behavior. In order to display a milestone, the data record needs to specify type: "Milestone" in its properties.

The milestones don’t have a duration (the end date is ignored) and they are displayed as a diamond at the specified location. The milestone can display additional text on the left or on the right side.

You can move milestones using drag and drop.

Because of the limited space, the milestones don’t display the context menu icon on hover but the context menu is still available using right mouse click.

New milestones can be created by right-clicking the scheduler grid. The onTimeRangeRightClick event handler opens a modal dialog with milestone details and sends a request to the server.

onTimeRangeRightClick: async args => {
  var dp = this.scheduler;

  const form = [
    {name: "Text", id: "text"}
  ];

  const data = {
    text: "Event 1",
    start: args.start.addHours(12),
    end: args.start.addHours(12),
    resource: args.resource,
    type: "Milestone"
  };

  const modal = await DayPilot.Modal.form(form, data);
  dp.clearSelection();
  if (modal.canceled) { return; }

  const {data: e} = await axios.post("/api/createEvent", modal.result);
  dp.events.add(e);
},

The event handler uses the same endpoint (/api/createEvent) to store the new milestone in the database.

Managing Rows (People, Departments, Facilities, Areas)

The scheduler rows can display resources (such as people, departments, facilities or areas of responsibility) which are organized in groups.

You can use a context menu to add, edit and delete rows. The context menu is defined using contextMenuResource property of the React Scheduler component. The same context menu is used for groups and resources but resources don’t show the “Add resource…” item.

contextMenuResource: new DayPilot.Menu({
  onShow: args => {
    const row = args.source;
    const hasParent = !!row.parent();
    this.scheduler.contextMenuResource.items[0].hidden = hasParent;
  },
  items: [
    {
      text: "Add resource...",
      onClick: args => {
        this.clickAddResource(args.source);
      }
    },
    {
      text: "Edit...",
      onClick: async args => {
        const form = [
          { name: "Name", id: "name" }
        ];
        const isGroup = !!args.source.data.groupId;
        const id = isGroup ? args.source.data.groupId : args.source.data.id;
        const name = args.source.data.name;
        const data = {
          id,
          name
        };
        const modal = await DayPilot.Modal.form(form, data);
        if (modal.canceled) {
          return;
        }
        const url = isGroup ? "/api/updateGroup" : "/api/updateResource";
        const {data: updated} = await axios.post(url, {id: modal.result.id, name: modal.result.name});
        const row = this.scheduler.rows.find(args.source.data.id);
        row.data.name = updated.name;
        this.scheduler.update();
      }
    },
    {
      text: "Delete",
      onClick: async args => {
        const row = args.source;
        if (row.data.groupId) {
          await axios.post("/api/deleteGroup", {id: row.data.groupId});
        }
        else {
          await axios.post("/api/deleteResource", {id: row.data.id});
        }
        this.scheduler.rows.remove(row.data.id);
      }
    }
  ]
})

Move Resources using Drag and Drop

The row order can be modified using drag and drop. Just drag the resource to the target location (within the current groups or in a different group).

The Scheduler customizes the drag and drop behavior using onRowMoving event handler. Certain target positions need to be forbidden since they are not supported by the data model (e.g. dragging a group to a child position):

onRowMoving: args => {
  const sourceIsGroup = !!args.source.data.groupId;
  const targetIsGroup = !!args.target.data.groupId;
  if (sourceIsGroup) {
    switch (args.position) {
      case "before":
        if (!targetIsGroup) {
          args.position = "forbidden";
        }
        break;
      case "after":
        if (!targetIsGroup) {
          args.position = "forbidden";
        }
        break;
      case "child":
        args.position = "forbidden";
        break;
    }
  }
  else {
    switch (args.position) {
      case "before":
        if (targetIsGroup) {
          args.position = "child";
        }
        break;
      case "after":
        if (targetIsGroup) {
          args.position = "child";
        }
        break;
      case "child":
        if (!targetIsGroup) {
          args.position = "forbidden";
        }
        break;
    }
  }
}

PostgreSQL Database Schema (DDL)

Here is the schema of the PostgreSQL database that is used by the server project to store the activity planning data.

event table

create table event
(
    id          serial    not null
        constraint event_pk
            primary key,
    text        varchar,
    start       timestamp not null,
    "end"       timestamp not null,
    type        varchar   not null,
    resource_id integer
);

create index event_end_index
    on event ("end");

create index event_start_index
    on event (start);

create index event_resource_id_index
    on event (resource_id);

resource_group table

create table resource_group
(
    id               serial            not null
        constraint group_pk
            primary key,
    name             varchar,
    ordinal          integer default 0 not null,
    ordinal_priority timestamp
);

create index resource_group_ordinal_index
    on resource_group (ordinal);

create index resource_group_ordinal_priority_index
    on resource_group (ordinal_priority);

resource table

create table resource
(
    id               serial            not null
        constraint resource_pk
            primary key,
    name             varchar,
    group_id         integer           not null,
    ordinal          integer default 0 not null,
    ordinal_priority timestamp
);

create index resource_ordinal_index
    on resource (ordinal);

create index resource_ordinal_priority_index
    on resource (ordinal_priority);

create index resource_group_id_index
    on resource (group_id);