Gantt - Documentation | www.HtmlElements.com

Overview

The following tutorial will teach how to create and setup a NodeJS server with MySQL to perform CRUD operations with Smart.GanttChart.

Prerequisites:

  • NodeJS - NodeJS has to be installed in order to create the beckend server. The following demo is build using NodeJS version 12.15.0.
  • MySQL DB - latest MySQL server has to be installed in order to create the database. The following demo is build using MySQL version 8.0.2
  • Visual Studio Code - an IDE for development.

The next steps will guide you through the process of creating, building and running the application.

Setup

First thing we need to do is create a project.

  1. Create a new project folder called gantt-node-server. From inside the project folder:
  2. Create a package.json file with the following contents:
    {
      "name": "gantt-node-server",
      "version": "1.0.0",
      "description": "",
      "main": "index.js",
      "scripts": {
        "test": "echo \"Error: no test specified\" && exit 1",
        "start": "node server.js"
      },
      "keywords": [],
      "author": "",
      "license": "ISC",
      "dependencies": {
        "express": "^4.17.1",
        "mysql": "^2.18.1",
        "smart-webcomponents": "^7.6.2"
      }
    }

    The folloing config file defines a command called start that will run the server via Node.

    In order to use NodeJS as a backend server and MySQL as the DB for the app we need to have them installed. For the purpose we define their NPM packages as dependencies.

  3. Installing the dependencies. From inside the project folder open a Command Prompt and type the following line:
    npm i

    This command will install all the dependencies that we defined in the package.json file:

    • Express - Express is a NodeJS framework for building web application.
    • MySQL - a NodeJS driver for MySQL service.
    • Smart Webcomponents - the Smart Webcomponents Library containing the Smart.GanttChart custom element.
  4. After installing MySQL Server on your machine you need to create a user in order to create a DB with tables. You can use the default root user that MySQL has or create a new one. In the folloing demo we will create a new user called gantt. In order to do so open a new Command Prompt and enter the following command:
    • mysql -u root -p

      This command will log you into MySQL with the root user who has full privileges.

      NOTE: If the mysql command is not recognized you need to configure your global PATH to the location of your MySQL installation folder or run it from inside the folder.

    • After logging succesfully as Root we need to create a new user via the following command:
      GRANT ALL PRIVILEGES ON *.* TO 'gantt'@'localhost' IDENTIFIED BY 'gantt';

      This command creates a new user called gantt with password gantt.

    • Set privileges to the new user with with the folloing command:
      GRANT SELECT ON *.* TO 'gantt'@'localhost';

      In order to create a new DB with tables and entries we need a user with the appropriate privileges.

    • Now quit the current MySQL session by typing in
      \q
  5. Create a new file called gantt.sql in the project folder with the following content:
    CREATE DATABASE ganttdb;
    USE ganttdb;
    CREATE TABLE `gantt_connections` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `source` int(11) NOT NULL,
      `target` int(11) NOT NULL,
      `type` varchar(1) NOT NULL,
      PRIMARY KEY (`id`)
    );
    CREATE TABLE `gantt_tasks` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `label` varchar(255) NOT NULL,
      `date_start` datetime NOT NULL,
      `duration` int(11) NOT NULL,
      `progress` float NOT NULL,
      `expanded` boolean,
      `parent_id` int(11),
      `order_id` int(11),
      PRIMARY KEY (`id`)
    );
    
    INSERT INTO `gantt_tasks` VALUES ('1', 'Project #1', '2020-04-01 00:00:00', '5', '80', '1', null, '0');
    INSERT INTO `gantt_tasks` VALUES ('2', 'Project #2', '2020-04-06 00:00:00', '4', '50','1', '1','1');
    INSERT INTO `gantt_tasks` VALUES ('3', 'Task #2', '2020-04-05 00:00:00', '6', '70', null, '1', '2');
    INSERT INTO `gantt_tasks` VALUES ('4', 'Task #3', '2020-04-07 00:00:00', '2', '0', null, '1', '3');
    INSERT INTO `gantt_tasks` VALUES ('5', 'Task #1.1', '2020-04-05 00:00:00', '5', '35', null, '2', '4');
    INSERT INTO `gantt_tasks` VALUES ('6', 'Task #1.2', '2020-04-11 13:22:17', '4', '50', null, '2', '5');
    INSERT INTO `gantt_tasks` VALUES ('7', 'Task #2.1', '2020-04-07 00:00:00', '5', '20', null, null, '6');
    INSERT INTO `gantt_tasks` VALUES ('8', 'Task #2.2', '2020-04-06 00:00:00', '4', '90', null, null, '7');
    
    INSERT INTO `gantt_connections` VALUES ('0','3', '4', '1');
    INSERT INTO `gantt_connections` VALUES ('1','7', '8', '0');

    This SQL script will create the new database with the tables and populate them with data.

    In order to execute the script open a Command Prompt and enter the following command:

    mysql -u gantt -pgantt < gantt.sql

    As a result two populated tables will be created:

    • gantt_tasks - containing the tasks. The task properties that will be used as columns for the table will be the following:
      • id - a unique identifier for each task that will be generated by the database.
      • label - the label of the task.
      • date_start - a DATETIME type column that will hold the starting date of the task.
      • duration - the duration of the task in days.
      • progress - the progress of the task.
      • expanded - a boolean(TINYINT) flag that will indicate whether the task(project) is expanded or not.
      • parent_id - the id of the project that is the parent of the task.
      • order_id - the order index of the task inside the Smart.GanttChart. We will have to synchronize the index with the element when inserting/removing tasks.
    • gantt_connections - containing the connections between the tasks. The connection properties that will be used to store them in the table as columns are:
      • id - a unique ID for the connection, auto generated by the database.
      • source - the source ID of the source task.
      • target - the ID of the target task.
      • type - the type of connection.

      Since the Smart.GanttChart connections can be created using the ids of the target and source tasks it will be very easy to keep track of the connections. Remember that GanttChart connections can be created using task ids but they are stored and retrieved by their indexes.

  6. Create a new Javascript file called server.js in the project folder with the following content:
    const express = require('express'),
        mysql = require('mysql'),
        app = express(),
        port = 3000,
        dbConfig = {
            host: 'localhost',
            user: 'gantt',
            password: 'gantt',
            database: 'ganttdb'
        };
    
    //Query the DB as a Promise
    async function query(conn, q, params) {
        return new Promise(
            (resolve, reject) => {
                const handler = (error, result) => {
                    if (error) {
                        reject(error);
                        console.log('Query error: ' + error.message);
                        return;
                    }
                    resolve(result);
                }
    
                conn.query(q, params, handler);
            })
    };
    
    //Connect to DB as a Promise
    async function connection(params) {
        return new Promise(
            (resolve, reject) => {
                const connection = mysql.createConnection(params);
    
                connection.connect(error => {
                    if (error) {
                        reject(error);
                        console.log('Connection error: ' + error.message);
                        return;
                    }
                    resolve(connection);
                })
            })
    };
    
    //Public
    app.get('/', (req, res) => res.sendFile(__dirname + '/public/index.htm'));
    
    //Serve Static Folders
    app.use('/smart-webcomponents', express.static(__dirname + '/node_modules/smart-webcomponents/'));
    app.use('/', express.static(__dirname + '/public/assets/'));
    
    //https://www.npmjs.com/package/body-parser
    app.use(express.json());
    
    //Get all Tasks
    app.get('/data', async (req, res) => {
        const conn = await connection(dbConfig).catch(e => { console.log('Connection to DB sucessfull!') }),
            tasks = await query(conn, 'SELECT id, label, date_start AS dateStart, duration, progress, expanded, parent_id, order_id FROM gantt_tasks ORDER BY order_id').catch(console.log);
        connections = await query(conn, 'SELECT * FROM gantt_connections').catch(console.log);
    
        res.json({ tasks, connections });
    });
    
    // add a new task
    app.post("/data/task", async (req, res) => {
        const task = req.body,
            conn = await connection(dbConfig).catch(e => { console.log('Connection to DB sucessfull!') });
    
        query(conn, 'INSERT INTO gantt_tasks(label, date_start, duration, progress, expanded, parent_id, order_id)' + ' VALUES (?,CONVERT(STR_TO_DATE(?, "%Y-%m-%dT%H:%i:%s.%fZ"), DATETIME),?,?,?,?,?)',
            [task.label, task.dateStart, task.duration, task.progress, task.expanded, task.parent_id, (task.order_id || 0) + 1]).catch(console.log);
    
        let newTask = await query(conn, 'SELECT * FROM gantt_tasks WHERE id = LAST_INSERT_ID()').catch(console.log);
    
        newTask = newTask[0];
    
        //Update order_id all the current tasks in the DB
        query(conn, 'UPDATE gantt_tasks SET order_id = order_id + 1 WHERE id <> ? AND order_id >= ?',
            [newTask.id, newTask.order_id]).catch(console.log);
    
        //Return the new task with DB generated id
        res.json({ newTask });
    });
    
    // Update a task
    app.put("/data/task/:id", async (req, res) => {
        const reqParams = JSON.parse(req.params.id),
            taskId = reqParams.id,
            noReorder = reqParams.noReorder,
            task = req.body,
            conn = await connection(dbConfig).catch(e => { console.log('Connection to DB sucessfull!') });
    
        //id is auto incremented and can't start from 0
        query(conn, 'UPDATE gantt_tasks SET label = ?, date_start = CONVERT(STR_TO_DATE(?, "%Y-%m-%dT%H:%i:%s.%fZ"), DATETIME), duration = ?, progress = ?, expanded = ?, parent_id = ?, order_id = ? WHERE id = ?',
            [task.label, task.dateStart, task.duration, task.progress, task.expanded, task.parent_id, noReorder ? task.order_id : (task.order_id || 0) + 1, taskId]).catch(console.log);
    
        res.end();
    });
    
    // delete a task
    app.delete("/data/task/:id", async (req, res) => {
        const taskId = req.params.id,
            conn = await connection(dbConfig).catch(e => { console.log('Connection to DB sucessfull!') });
        let targetTask = await query(conn, 'SELECT * FROM gantt_tasks WHERE id = ?', [taskId]).catch(console.log);
    
        targetTask = targetTask[0];
    
        //Update order_id all the current tasks in the DB
        query(conn, 'UPDATE gantt_tasks SET order_id = order_id - 1 WHERE id <> ? AND order_id >= ?',
            [targetTask.id, targetTask.order_id]).catch(console.log);
    
        //Delete the task
        query(conn, 'DELETE FROM gantt_tasks WHERE id = ?', [taskId]).catch(console.log);
    
        res.end();
    });
    
    // add a new connection
    app.post("/data/connection", async (req, res) => {
        const taskCon = req.body,
            conn = await connection(dbConfig).catch(e => { console.log('Connection to DB sucessfull!') });
    
        query(conn, 'INSERT INTO gantt_connections(source, target, type)' + ' VALUES (?,?,?)',
            [taskCon.source, taskCon.target, taskCon.type]).catch(console.log);
    
        res.end();
    });
    
    // delete a connection
    app.delete("/data/connection/:con", async (req, res) => {
        const taskConnParams = JSON.parse(req.params.con),
            conn = await connection(dbConfig).catch(e => { console.log('Connection to DB sucessfull!') });
    
        query(conn, 'DELETE FROM gantt_connections WHERE source = ? AND target = ?', [taskConnParams.source, taskConnParams.target]).catch(console.log);
    
        res.end();
    });
    
    app.listen(port, () => console.log('Server is running on port ' + port + '...'));
    

    This file contains the backend configuration for the application.

    In order to make CRUD operations the server has to accept the following requests:

    • GET - retrieves the tasks and connections from the DB to the client.
    • POST - adds a new task/connection to the DB.
    • PUT - updates and existing task/connection in the DB.
    • DELETE - deletes an existing task/connection from the DB.

    The client makes requests to the server by passing arguments when necessary, for example:

    • Retrieving the DB records - when the client sends an empty request with no parameters to the target URL, the server queries the DB and responds with a list of task and connection records(GET).
    • Creating a new task - when creating a new task the client has to send the new task object as JSON to the server as part of the request body(POST). The server adds the new record and returns it to the client. This is done in order to get the unique ID of the task that is auto generated by the DB.
    • Updating a new task - when updating a task the client sends the unique id of the task to the server as part of the request parameters and the updated task object as JSON object as part of the request body(PUT). The server finds the task, if exists and updates the record in the DB.
    • Deleting a task - when deleting a task the client simply sends the unique id of the task to the server as part of the request parameters(DELETE). The server then tries to delete the record from the DB if exists.
    • The dbConfig variable contains the database connection settings.

      We created an additial column in the gantt_tasks table called order_id to keep track of the task order. This is important because we need to know the position of the tasks when inserting or removing a task. That is why the server increments the order_id which represents the index of the task when a new task is added and decrements the order_id when a task is removed. This is handled by the server to avoid unnecessary requests.

    By default when the user runs the demo, the server looks for index page located at /public/index.htm and fetches resources like additional JS and CSS styles from /public/assets/. So the final step is to create those files and folders.

  7. Create index.htm inside a new folder called public. The file should have the following content:
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8" />
        <title>Gantt Chart with Node Backend</title>
    
        <meta charset="utf-8" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge" />
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0" />
    
        <link rel="stylesheet" href="/smart-webcomponents/source/styles/smart.default.css" type="text/css" />
        <link rel="stylesheet" type="text/css" href="styles.css" />
    </head>
    
    <body class="viewport">
        <smart-gantt-chart id="gantt"></smart-gantt-chart>
    
        <!-- scripts -->
        <script type="module" src="smart.ganttchart.js"></script>
    </body>
    </html>

    Any additional Javascript files will be loded as a module from e single file called smart.ganttchart.js located in public/assets

    The assets folder should contain the following files:

    • index.js - the client side Javascript file for the demo that will make request to the server. Here's the content of the file:
      window.Smart('#gantt', class {
          get properties() {
              return {
                  view: 'week',
                  treeSize: '30%',
                  durationUnit: 'day',
                  taskColumns: [
                      {
                          label: 'Task Name',
                          value: 'label',
                          size: '40%'
                      },
                      {
                          label: 'Start Time',
                          value: 'dateStart',
                          size: '30%'
                      },
                      {
                          label: 'Duration',
                          value: 'duration',
                          min: 35,
                          formatFunction: (date) => parseInt(date)
                      },
                      {
                          label: '<span class="add-task-button">+</span>',
                          value: 'addTask',
                          hideResizeBar: true,
                          min: 30,
                          size: 30,
                          formatFunction: function () {
                              return '<span class="add-task-button">+</span>';
                          }
                      }
                  ]
              };
          }
      });
      
      
      function AjaxXMLHttpRequest(dataType, settings, method, url, body) {
          const request = new XMLHttpRequest();
      
          request.open(method, url, true);
      
          request.ontimeout = function () {
              if (settings && settings.loadError) {
                  settings.loadError(408, 'timeout');
              }
          };
      
          request.onload = function () {
              if (request.readyState === 4) {
                  const status = request.status;
                  let data = request.response;
      
                  if (status >= 200 && status <= 299) {
                      if (!data.length) {
                          return;
                      }
      
                      if (dataType === 'json') {
                          data = JSON.parse(data);
                      }
      
                      AjaxComplete(settings, data);
                  }
                  else if (settings && settings.loadError) {
                      settings.loadError(status, data);
                  }
              }
          };
      
          request.onerror = function () {
              if (settings && settings.loadError) {
                  settings.loadError(request.status, request.response);
              }
          };
      
          if (settings && settings.contentType) {
              request.setRequestHeader('Content-Type', settings.contentType);
          }
      
          request.send(body);
      }
      
      function AjaxComplete(settings, data) {
          const ganttChart = document.querySelector('smart-gantt-chart');
      
          if (data.newTask) {
              const newTask = data.newTask;
      
              //Set the DB generated id to the new task
              ganttChart.updateTask(newTask.order_id - 1, newTask);
      
              //Open the Editor to configure
              ganttChart.openWindow(newTask.order_id - 1);
              return;
          }
      
          const tasks = data.tasks,
              connections = data.connections;
      
          //process connections from DB
          for (let i = 0; i < connections.length; i++) {
              let con = connections[i];
              const taskStart = tasks.find(t => t.id === con.source),
                  taskEnd = tasks.find(t => t.id === con.target);
      
              if (!taskStart || !taskEnd) {
                  continue;
              }
      
              if (!taskStart.connections) {
                  taskStart.connections = [];
              }
      
              con.target = taskEnd.id;
      
              taskStart.connections.push(con);
          }
      
          //Process Tasks from DB
          let ganttTasks = [];
      
          for (let i = 0; i < tasks.length; i++) {
              const task = tasks[i],
                  subTasks = tasks.filter(t => t.parent_id === task.id);
      
              //If not a sub-task add it to the list
              if (!task.parent_id) {
                  ganttTasks.push(task);
              }
      
              //Add Sub-tasks
              if (subTasks.length) {
                  task.tasks = (task.tasks || []).concat(subTasks);
              }
          }
      
          data.tasks = ganttTasks;
      
          ganttChart.loadState(data);
      }
      
      window.onload = function () {
          //Load all tasks
          AjaxXMLHttpRequest('json', null, 'GET', '/data');
      
          const ganttChart = document.querySelector('smart-gantt-chart');
      
          //Handle the Add Task Button
          ganttChart.addEventListener('click', function (event) {
              const target = event.target;
      
              if (target.classList.contains('add-task-button')) {
                  const addButtons = Array.from(ganttChart.querySelectorAll('.add-task-button'));
                  let newTaskIndex = addButtons.indexOf(target);
      
                  if (newTaskIndex === 0) {
                      newTaskIndex = newTaskIndex.length - 1;
                  }
      
                  //Add a new Task
                  ganttChart.insertTask(newTaskIndex, { label: 'New Task', dateStart: ganttChart.dateStart });
              }
          });
      
          //Remove a Task
          ganttChart.addEventListener('itemRemove', function (event) {
              const eventDetails = event.detail,
                  itemType = eventDetails.type,
                  item = eventDetails.item;
      
              if (itemType === 'task') {
                  AjaxXMLHttpRequest('json', null, 'DELETE', '/data/task/' + item.id);
              }
              else if (itemType === 'connection') {
                  const tasks = ganttChart.tasks,
                      connection = { source: tasks[parseInt(item.source)].id, target: tasks[parseInt(item.target)].id, type: item.type };
      
                  AjaxXMLHttpRequest('json', { contentType: 'application/json' }, 'DELETE', '/data/connection/' + JSON.stringify(connection));
              }
          });
      
          //Update a Task
          ganttChart.addEventListener('itemUpdate', function (event) {
              const eventDetails = event.detail,
                  itemType = eventDetails.type,
                  item = eventDetails.item;
      
              item.order_id = ganttChart.getTaskIndex(item);
      
              if (itemType === 'task') {
                  AjaxXMLHttpRequest('json', { contentType: 'application/json' }, 'PUT', '/data/task/' + item.id, JSON.stringify(item));
              }
          });
      
          function updateTask(e) {
              const task = ganttChart.tasks[e.detail.index],
                  paramsObj = JSON.stringify({ id: task.id, noReorder: true });
      
              AjaxXMLHttpRequest('json', { contentType: 'application/json' }, 'PUT', '/data/task/' + paramsObj, JSON.stringify(task));
          }
      
          ganttChart.addEventListener('resizeEnd', updateTask);
          ganttChart.addEventListener('dragEnd', updateTask);
      
          //Insert a Task
          ganttChart.addEventListener('itemInsert', function (event) {
              const eventDetails = event.detail,
                  itemType = eventDetails.type,
                  item = eventDetails.item;
      
              if (itemType === 'task') {
                  const itemParent = ganttChart.getTaskProject(item);
      
                  item.parent_id = itemParent ? itemParent.id : null;
                  item.order_id = ganttChart.getTaskIndex(item);
      
                  AjaxXMLHttpRequest('json', { contentType: 'application/json' }, 'POST', '/data/task', JSON.stringify(item));
              }
              else if (itemType === 'connection') {
                  const tasks = ganttChart.tasks,
                      connection = { source: tasks[item.source].id, target: tasks[item.target].id, type: item.type };
      
                  AjaxXMLHttpRequest('json', { contentType: 'application/json' }, 'POST', '/data/connection', JSON.stringify(connection));
              }
          });
      }

      Smart.GanttChart offers many events that are triggered as a result of user interaction with the tasks/connections. When binding to them we can make a request to the server in order to synchronize the changes with the database. The following events have been used in the demo:

      • itemRemove - fired when a task/connection is added.
      • itemUpdate - fired when a task is updated.
      • itemRemove - fired when a task/connection is removed.
      • dragEnd - fired after a task has been dragged.
      • resizeEnd - fired after a task has been resized.

      When a new task is added it's important to retrieve the unique ID of the task that is generated by the database and update it. We are doing this to avoid recreating the dataSource of the GanttChart.

      The loadState method is used to load the processed tasks to the GanttChart. Another approach is to set the new tasks via the dataSource property, but we will use the first instead.

      The AjaxXMLHttpRequest function makes the server requests by passing the appropriate arguments for the type of connection, url, request body, etc.

      AjaxComplete handles the response from the server, processes the tasks and connections and loads them to the Smart.GanttChart via the loadState method.

    • smart.ganttchart.js - a JS module that loads all dependencies for the Smart.GanttChart. Here is the content of the file:
      //Smart JS
      import "../smart-webcomponents/source/smart.element.js";
      import "../smart-webcomponents/source/smart.button.js";
      import "../smart-webcomponents/source/smart.scrollbar.js";
      import "../smart-webcomponents/source/smart.date.js";
      import "../smart-webcomponents/source/smart.listbox.js";
      import "../smart-webcomponents/source/smart.dropdownlist.js";
      import "../smart-webcomponents/source/smart.draw.js";
      import "../smart-webcomponents/source/smart.numeric.js";
      import "../smart-webcomponents/source/smart.math.js";
      import "../smart-webcomponents/source/smart.tooltip.js";
      import "../smart-webcomponents/source/smart.timepicker.js";
      import "../smart-webcomponents/source/smart.calendar.js";
      import "../smart-webcomponents/source/smart.datetimepicker.js";
      import "../smart-webcomponents/source/smart.menu.js";
      import "../smart-webcomponents/source/smart.tree.js";
      import "../smart-webcomponents/source/smart.export.js";
      import "../smart-webcomponents/source/smart.splitter.js";
      import "../smart-webcomponents/source/smart.window.js";
      import "../smart-webcomponents/source/smart.ganttchart.js";
      
      //Demo JS
      import "/index.js";
    • styles.css - additional CSS file containing the styles for the demo:
      smart-gantt-chart {
          height: auto;
          --smart-gantt-chart-task-tree-splitter-bar-fit-size: calc(2 * var(--smart-gantt-chart-task-default-height));
      }
      
      smart-gantt-chart .add-task-button {
          font-size: 1.35rem;
          color: rgba(109, 113, 232, 0.5);
          font-weight: 900;
      }
      
      smart-gantt-chart .add-task-button:hover, smart-gantt-chart .add-task-button:active {
          color: rgba(109, 113, 232, 1);
      }

The project should have the following structure:

  • node_modules/
  • public/
    • /assets/
      • index.js
      • smart.ganttchart.js
      • styles.css
    • index.htm
  • gantt.sql
  • package.json
  • server.js

Run the demo

Run the Demo by opening a Command Prompt from the Project folder and entering the following command:
npm run start

The server will start listening on port 3000.

To view the demo simply navigate to the following url http://localhost:3000/