BI-GLPI. Or my view on CRM in the context of JS
Part 1. General description of the approach
The first time I encountered CRM systems was completely unexpected for me. When I came to a new company, I discovered the dominant system for recording applications in the dispatching office – GLPI. I had never heard of it before and had no dealings with systems of this kind. However, after some time, a task came to think about and implement some dashboards or visual reports based on the following criteria:
total daily number of applications
distribution by departments
other similar metrics and their derivatives
GLPI offers both default reporting tools and the ability to install additional plugins from a special marketplace (for a fee). But I was not impressed by all this, as it seemed rather boring. In addition, it was interesting to solve the problem on my own, without resorting to ready-made products. I started thinking about how to come up with something like this and not “reinvent the wheel”. I wanted to interact with the program as little as possible. To be able to quickly and easily receive data and do whatever I wanted with it. And most importantly, not to understand the API documentation and not to dig into PHP. I wanted to use the knowledge I had and, based on it, create a web application that could perform the tasks set.
The decision was made soon. I realized that it was enough for me to be able to interact exclusively with the GLPI database and its structure of tables and their relations. The stack that seemed most suitable for this task looked like this:
I recommend starting your acquaintance with the Express framework with Ethan Brown's book – “Web Development with NODE Js and Express”. I continue to use it as a guide and mentor. I use Java Script in Arduino with the help of Johnny-Five. I actively use JS to solve cases at work. And, of course, I hope that in the foreseeable future I will be able to fully use the accumulated experience and knowledge in this area in order to further harmonize industrial automation with JavaScript.
The most accurate description of the Express framework, in my opinion, is given by Ethan Brown himself in the book mentioned above:
…It just gets in your way less, allowing you to express your ideas more fully.. – Itan Braun [1]
A little descriptive part of the application directory structure…
Typically, a catalog src — the main one for the project. “public” — was used in the early days for storing files css, js, images and testing. Folder “views” is about handlebars and the template system. There is also a file “accessMain.log». Its extension makes its “purpose in life” clear:) Also file with a strange name «indexV3..2_mine.js» – is the main and most important.
Let's return to our solution, which will implement the interaction between the client application and the GLPI system.
What I love about Node JS is the variety of NPM packages. So to say, for all occasions you can find something suitable for yourself among 1000 NPMs. Indeed, almost always I find the optimal package to solve the next task. And this time I didn’t have to look for the right one for long, and I adopted npm “mysql” . Link to plastic bag.
Quite easy to use library allows flexible interaction with the database. The package description provides detailed main methods of interaction with the database.
Ideally, we just need to open a connection and get data in response to a chain of SQL queries. That's what we'll do. Let's set up the connection parameters according to the package description file:
Example from offdoc:
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'me',
password : 'secret',
database : 'my_db'
});
connection.connect();
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results[0].solution);
});
connection.end();To present data on the client side, we will form an object and pass it to some template engine for subsequent HTML rendering in response to the user's GET request.
Hidden text
We will do the same for the POST request, however, with the difference that we will pass the parameters from the form on the client's page to the data receiving function.
The task set before me clearly stated what the end user wanted to see as a result of the request. The following information was required:
Application Title
Application status
Number of applications by departments
Total number of applications for the selected period
Extracting the name of the application, its content, and the total number of applications directly does not seem difficult. At the same time, in order to run the obtained data through the calculation and statistics scripts, it is first necessary to create a complex SQL query. Having studied the GLPI table a little, I decided that in order to obtain data on the application belonging to a certain department, I will compare the initiator's id with the group id to which he belongs in the GLPI user system.
connection.query(`SELECT COUNT(*) FROM glpi_tickets WHERE date BETWEEN '${date[0]}' AND '${date[1]}'`, function (err, rows1) {
if (err) throw err;
connection.query(`SELECT DATE_FORMAT(date, '%d/%m/%Y %H:%i'), name, users_id_recipient, status FROM glpi_tickets WHERE date BETWEEN '${date[0]}' AND '${date[1]}'`, function (err, rows2) {
if (err) throw err;
connection.query(`SELECT * FROM glpi_tickets
INNER JOIN glpi_tickets_users ON glpi_tickets.id=glpi_tickets_users.tickets_id
INNER JOIN glpi_groups_users ON glpi_tickets_users.users_id=glpi_groups_users.users_id
INNER JOIN glpi_groups ON glpi_groups_users.groups_id=glpi_groups.id WHERE date BETWEEN '${date[0]}' AND '${date[1]}'`, function (err, rows3)
{ ...As you can see from the code fragment above, all requests are combined into a callback chain, which provides for displaying an error in the console if it occurs at the transaction stage.
Hidden text
To correctly display the date in a readable format, we had to resort to embedding a time format conversion function into the SQL query – DATE_FORMAT(date, '%d/%m/%Y %H:%i');
In response to our persistent request, the DB will respond with data of the following type: Row Data Packet Object.
After the data is received, we need to put it in order, as they say. Comb it, “wash it” and distribute it according to the structure of our main object, which will be used for the process performances. This can be done by simply filling the object, looping through the received data.
As a result, we get the main structured object, which we will pass to the template engine.
Client-side presentation
As I mentioned above, I will use a templating approach to present data on the client side. You can read more about it here here. And also here.
In general, the philosophy of presentation templating always comes down to the same intentions:
don't write the same code multiple times
replication of edits made to one component
However, we must remember one detail of this approach. There are many template engines with different levels of abstraction. That is, the level of how much we abstract or, as it seems to me, better to say, “move away” from classic HTML. Handlebars has a clear advantage in this regard. Also, there is the following fact, which I find important:
Handlebars compiles templates into JavaScript functions. This makes template execution faster than most other template engines.
— https://handlebarsjs.com
It would be appropriate to introduce to my reader the method of connecting Handlebars to Express, as well as the structure of its directories.
This code snippet is located in the main application file:
...
/*HANDLEBARS*/
const expressHandlebars = require("express-handlebars").engine;
app.engine('handlebars', expressHandlebars({
defaultLayout: 'main',
}));
app.set('view engine', 'handlebars');
...Below is the directory structure of Handlebars folders and files.
File Contents main.handlebars. This is the basic framework of a custom page. For example, it takes the value of a field from a previously created object for presentation count and is inserted into the table cell
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>GLPI Analytics</title>
</head>
<style>
...
</style>
<body>
<h1>GLPIReport [beta]</h1>
...
<table id="count">
{{#each this.count}}
<tr>
<td>
Заявок всего
</td>
<h2>
<td>
{{this}}
</td>
</h2>
{{/each}}
</tr>
</table>
</div>
<table cellspacing="0" cellpadding="0" class="layout">
<tr>
<td id="left_col">
{{{body}}}
</td>
</tr>
</table>
</body>
</html>
To draw a table with all the requests, a second file is required- home copy.handlebars.
<h2>{{title}}</h2>
<div style="overflow: auto; width:1300px; height:900px;">
<table id="tickets">
{{#each ticketS}}
<tr id="someRowTickets">
<td>
{{date}}
</td>
<td>
{{name}}
</td>
<td id="status">
{{status}}
</td>
</tr>
{{/each}}
</td>
</table>
</td>
<td>
</div>
<table id="groups">
{{#each groupCount}}
<tr>
<td>
{{maintenance}}
</td>
<td>
{{count}}
</td>
</tr>
{{/each}}
</td>
</table>
</td>
</tr>
</table>This is where all the magic of presentation templating comes in. The data passed to the template engine, as in the first case, is converted into ready-made HTML and issued when the user accesses a specific route.
At the address where our web server is running, we see the following:
In general, this method is not only a simple extraction of the necessary data, but also numerous possibilities for their presentation and processing. The main GLPI continues to work on the desktop of hotliners, and our small and lightweight web application is launched in parallel, as a kind of additional service. In the future, I plan the second part of the article. In it, we will talk about data dynamization, visualization and manipulation of DOM elements. As well as other aspects of improving presentations and user experience.
