smart-html-elements

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 >=16.17.1 LTS
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.31
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.

Create a new project folder called gantt-node-server. From inside the project folder:

Create a package.json file with the following contents:

{
  "name": "gannt-chart-server",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node server.js",
    "dev": "nodemon server.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.18.1",
    "mysql": "^2.18.1",
    "nodemon": "^2.0.20",
    "smart-webcomponents": "^14.4.0"
  }
}

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.

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.
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
```
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 index of the target and source tasks it will be very easy to keep track of the connections. We will save the tasks ids and retrieve the index of the task to create a connection.

Create a new folder 'src' and in it a new file called database.js

const mysql = require('mysql');

exports.createConnection = async () => {

    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'gantt',
        password: 'gantt',
        database: 'ganttdb'
    });

    return connection;
}

exports.query = (connection, query, params) =>
    new Promise((resolve, reject) => {
        
        connection.query(query, params, (error, results) => {

            if (error) {

                reject(error);
                console.log(`Query Error: ${error.message}`);

            }

            resolve(results);

        })
    })

exports.connect = () => new Promise(async (resolve, reject) => {

    const connection = await this.createConnection();

    connection.connect((error) => {

        if (error) {

            reject(error);
            console.log(`Query Error: ${error.message}`);

        }

        resolve(connection);

    })
})

exports.close = (connection) => new Promise((resolve, reject) => {

    connection.end();

    resolve();

})

Create a new Javascript file called server.js in the project folder with the following content:

const express = require('express');
const path = require('path');

const app = express();
const PORT = 3000;

app.use(
    '/smart-webcomponents',
    express.static(path.resolve(__dirname, './node_modules/smart-webcomponents'))
);
app.use(express.static(path.resolve(__dirname, './public')));

app.use(express.json());
app.use(express.urlencoded({ extended: false }));

const { connect, query, close } = require('./src/database');

app.get('/', (req, res) => {

    res.sendFile(__dirname + '/public/index.html');

});

app.get('/tasks', async (req, res) => {

    let connection;

    try {
        
        connection = await connect();

        const tasks = await query(
            connection,
            `
            SELECT id, label, date_start AS dateStart, duration, progress, expanded, parent_id, order_id 
            FROM gantt_tasks 
            ORDER BY order_id
            `
        );

        const connections = await query(
            connection,
            `SELECT * FROM gantt_connections`
        );

        await close(connection);
        res.json({ tasks, connections });

    } catch (error) {

        await close(connection);
        res.status(500).json(JSON.stringify([]));

    }

});

app.post('/tasks', async (req, res) => {

    const task = req.body;
    const connection = await connect();

    let newTask = {};

    try {

        await query(
            connection,
            `
            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]
        )

        newTask = await query(connection, 'SELECT * FROM gantt_tasks WHERE id = LAST_INSERT_ID()');

        newTask = newTask[0];

        //Update order_id all the current tasks in the DB
        await query(
            connection,
            'UPDATE gantt_tasks SET order_id = order_id + 1 WHERE id <> ? AND order_id >= ?',
            [newTask.id, newTask.order_id]
        )

    } catch (error) {

        console.log(error);

        await close(connection);
        res.status(500).end();

    }

    await close(connection);
    //Return the new task with DB generated id
    res.json(newTask);
});

app.put("/tasks/:id", async (req, res) => {

    const reqParams = JSON.parse(req.params.id);
    const noReorder = reqParams.noReorder;
    const task = req.body;

    const connection = await connect();
    //id is auto incremented and can't start from 0

    try {
        await query(
            connection,
            `
            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, task.id]
        )
    } catch (error) { console.log(error); }

    await close(connection);
    res.end();
});

app.delete('/tasks/:id', async (req, res) => {

    const connection = await connect();

    let targetTask;

    try {

        targetTask = await query(connection, 'SELECT * FROM gantt_tasks WHERE id = ?', [req.params.id]);
        targetTask = targetTask[0];

    } catch (error) {

        console.log(error);

        await close(connection);
        res.status(500).end();;
    }

    //Update order_id all the current tasks in the DB
    try {

        await query(
            connection,
            'UPDATE gantt_tasks SET order_id = order_id - 1 WHERE id <> ? AND order_id >= ?',
            [targetTask.id, targetTask.order_id]
        )

        await query(connection, 'DELETE FROM gantt_connections WHERE source = ?', req.params.id)

        await query(connection, 'DELETE FROM gantt_tasks WHERE id = ?', req.params.id)

    } catch (error) { console.log(error); }

    await close(connection);
    res.end()
});

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

    const taskConnection = req.body;
    const connection = await connect();

    try {

        await query(
            connection,
            'INSERT INTO gantt_connections(source, target, type)' + ' VALUES (?,?,?)',
            [taskConnection.source, taskConnection.target, taskConnection.type]
        )

    } catch (error) {
        console.log(error);
        await close(connection);
        res.status(500).end();
    }

    await close(connection);
    res.end();
});

app.delete("/connections", async (req, res) => {

    const taskConnection = req.body;
    const connection = await connect();

    try {

        await query(
            connection,
            'DELETE FROM gantt_connections WHERE source = ? AND target = ?',
            [taskConnection.source, taskConnection.target]
        )

    } catch (error) {
        console.log(error);
        await close(connection);
        res.status(500).end();
    }

    await close(connection);
    res.end();
});

app.listen(
    PORT,
    () => console.log(`Server listening 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 database.js contains the database config and functions parsed to promises.

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.

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="assets/styles.css" />
    </head>

    <body class="viewport">
        <smart-gantt-chart id="gantt"></smart-gantt-chart>

        <script type="module" src="smart-webcomponents/source/modules/smart.ganttchart.js"></script>
        <script type="module" src="assets/index.js"></script>
    </body>
</html>

Adding the Gantt chart with the file smart.ganttchart.js located in /smart-webcomponents

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.onload = () => {

    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: '+',
                        value: 'addTask',
                        hideResizeBar: true,
                        min: 30,
                        size: 30,
                        formatFunction: function () {
                            return '+';
                        }
                    }
                ]
            };
        }
    });

    setupGantt();
}

async function setupGantt() {

    const gantt = document.querySelector('#gantt');

    await getTasks();

    gantt.addEventListener('click', (e) => {

        if (e.target.classList.contains('add-task-button')) {

            const addButtons = Array.from(gantt.querySelectorAll('.add-task-button'));
            let newTaskIndex = addButtons.indexOf(e.target);

            if (newTaskIndex === 0) {
                newTaskIndex = newTaskIndex.length - 1;
            }

            //Add a new Task
            gantt.insertTask({ label: 'New Task', dateStart: gantt.dateStart }, null, newTaskIndex);
        }
    });

    gantt.addEventListener('itemInsert', async function (e) {

        const itemType = event.detail.type;
        const item = event.detail.item;

        if (itemType === 'task') {

            const itemParent = gantt.getTaskProject(item);
            item.parent_id = itemParent ? itemParent.id : null;
            item.order_id = gantt.getTaskIndex(item);

            const newTask = await (await fetch('/tasks', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify(item)
            })).json();

            gantt.updateTask(gantt.tasks[gantt.tasks.length - 1], { id: newTask.id });

        }
        else if (itemType === 'connection') {

            const tasks = gantt.tasks;
            console.log(gantt.tasks);
            const connection = { source: tasks[item.source].id, target: tasks[item.target].id, type: item.type };

            console.log(connection);

            fetch('/connections', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify(connection)
            })

        }
    });

    //Update a Task
    gantt.addEventListener('itemUpdate', function (event) {

        const item = event.detail.item;

        item.order_id = gantt.getTaskIndex(item);

        if (event.detail.type === 'task') {

            fetch(`/tasks/${item.id}`, {
                method: 'PUT',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify(item)
            })

        }

    });

    function updateTask(e) {

        const task = e.detail.item;
        const paramsObj = JSON.stringify({ id: task.id, noReorder: true });

        fetch(`/tasks/${paramsObj}`, {
            method: 'PUT',
            headers: {
                'Content-Type': 'application/json'
            },
            body: JSON.stringify(task)
        })
    }

    gantt.addEventListener('resizeEnd', updateTask);
    gantt.addEventListener('dragEnd', updateTask);

    //Remove tasks or connection
    gantt.addEventListener('itemRemove', function (event) {
        const eventDetails = event.detail;
        const itemType = eventDetails.type;
        const item = eventDetails.item;

        if (itemType === 'task') {

            fetch(`/tasks/${item.id}`, {
                method: 'DELETE',
            })
        }
        else if (itemType === 'connection') {

            const tasks = gantt.tasks;
            const connection = { source: tasks[parseInt(item.source)].id, target: tasks[parseInt(item.target)].id, type: item.type };

            fetch(`/connections`, {
                method: 'DELETE',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify(connection)
            })

        }
    });

    async function getTasks() {

        const data = await (await fetch('/tasks')).json();

        const tasks = data.tasks || [];
        const connections = data.connections || [];

        //Process Tasks from DB
        let ganttTasks = [];

        for (let i = 0; i < tasks.length; i++) {
            const task = tasks[i];
            const 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;
        gantt.dataSource = data.tasks;

        connections.forEach(async (connection) => {

            const taskStart = gantt.getTask(connection.source);
            const taskEnd = gantt.getTask(connection.target);

            const taskStartIndex = gantt.getTaskIndex(taskStart);
            const taskEndIndex = gantt.getTaskIndex(taskEnd);

            gantt.createConnection(`${taskStartIndex}`, `${taskEndIndex}`, connection.type);

        });

    }
}

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.

We are creating request to the server with the fetch() API

smart.ganttchart.js - a JS module that loads all dependencies for the Smart.GanttChart. It is loaded from the node_modules/smart-webcomponents

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
  - styles.css
- index.html
src/
- database.js
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/

Complex Components

Core Components

Smart.GanttChart - NodeJS CRUD

Overview

Setup

Run the demo