I tried to make my own reporting system for production downtime

For several years now, we have been using a “self-made” production downtime control system implemented on the basis of MS SQL, SQL Server Reporting Services and IIS. Here is an example of a report for a certain period:

The principle of operation is as follows: the unit every second (previously it was through a script in the SCADA system, now through the PLC controller via UDP) transfers its state to the SQL database, and as soon as an idle condition appears in the status table (this is processed by a trigger), then in another table in the column “start idle” beats the current time. If the downtime condition disappears, the same table displays the current time in the column “end of downtime”, and its total duration is calculated and sent to the third column. On the same SQL server, the IIS web server is spinning, which, at the request of the user, forms and gives him a table of downtime in the browser for a given period. As I understand it, there is nothing innovative in this approach – MES systems seem to be based on the same principle, except that the software is more advanced and tailored for specific tasks.

In this case, this solution seemed interesting to me, and one of its advantages is “budget”. All the functionality was made by the APCS engineers on their own without the involvement of high-class programmers, and when I encountered this, after a short digression into the principle of operation, I was able to add a couple more tables with downtime by analogy with those that were. However, such a solution still requires a little bit of programming experience, and besides, the transfer from UDP to SQL is implemented through a shim program, which is also crutches, but this is not about that now. As a user, I myself have to deal with these downtime reports, and since this whole solution is tailored for the use of Microsoft products, it is also recommended for the user to use Internet Explorer. That is, the solution is so ancient that some of the functionality is no longer available in a modern browser, in particular, the choice of the report generation date is not a calendar, but a regular field where you need to enter dates manually, which is very annoying. Actually, only this moment made me, for the sake of experiment, try to do something similar, but so that the report template can be edited as I please and always with normal date selection fields. Here is what I got as a result:

It’s very cool that for ease of use (maybe not, it’s how to look) the program can be made into one executable file – this is a web server that returns a table on request, and the database itself in SQlite, and even receiving signals via UDP with subsequent saving can also be attached to the database. Launched one program and everything works. At the moment, the program implements a mechanism for obtaining data from the database and returning them on request via the web. The program code is in my repository ReportsFromDB. The program itself is designed for a table with a specific structure – these are 3 columns: Downtime_start – start of downtime, Downtime_stop – end of downtime, and Downtime_dur – duration. The repository has an example of such a table with ~ 55 thousand records. The choice of the report interval is implemented using html and js, which also seems very convenient to me, because. if you need to change something in the visual design, then you do not need to change the entire program, but only the html file. In the same way, it was possible to add a Russified representation of the date and time in the table without changing and recompiling the program.

The program itself is, in fact, an http Listener that listens on port 8880 and waits for a request, and depending on its content, sends the client the information that he requested. To generate a table for a certain period, you need to submit a request to the server with the following structure: http://ip-addr:8880/;start_time;end_time;clear_options I chose this strange format simply because it is easier to split RawUrl by semicolon in the request, and since the page uses an iframe, the user does not need to remember the request format – it is created automatically in the frame, and a part of the table with 100 rows and several such pages comes in response if there are more than 100 rows in the requested period. This is how it is implemented in the code:

try
{
	string[] req = request.RawUrl.Split(';');
	string reply = "";
	if (req[3] == "clear")
	{
		SQLrows.Clear();
	}
	reply = connectToDb(req[1], req[2]);
	if (reply == "ok" || reply == "full")
	{
		responseString = buildTable();
	}

	else
	{
		responseString = reply;
	}
}
catch
{
	try
	{
		responseString = System.IO.File.ReadAllText(System.IO.Path.Join(path, "/Resources/index.html"));
	}
	catch
	{
		responseString = "add index.html to generate page";
	}
}

At the beginning, the functionality was such that the user received the entire table – the entire huge line with the table code was sent to the browser and it specifically hung like that, especially if you try to display all 55 thousand lines at once. That is why a pagination view was added, with an implementation in the form of a list, but again it is rather crooked, because if 2 people request different dates at the same time, then the table will be recreated each time, because a list with the resulting table array is common to all. If you add a few more options to the query string, you can easily implement, for example, selecting a database or table, which is very convenient if there are several tables and databases. A request without a structure will cause the index.html page in the Resources folder to be served to the client, but if no such page exists, the browser will report it. Index.html, as I said, makes it easier to access the database by using a frame that changes through javascript.

The development was carried out on Windows and, in theory, .NET should also work on Linux, so by installing the .NET SDK on Ubuntu, I checked it myself. However, I encountered an interesting feature – the formation and scrolling of the report on Ubuntu works the second time, and the following picture appears in the developer panel in the browser when scrolling:

It is not entirely clear what it is and because of what it could be, perhaps someone can tell in the comments. In general, I consider this experiment to be effective for myself: at least I did the function of choosing a date according to the calendar, and even added Russification of the date. And for production use, this solution was not intended initially, because. The current system on IIS and MSSQL, though crooked, works, at least users do not complain (perhaps they do not know that something can be improved if asked well). I wrote this note in order to receive constructive criticism and advice on how to do something better, or, perhaps, there are ready-made solutions of the same plan. It is also an opportunity to share such a way to solve the problem of creating downtime reports. It turned out a kind of MES system at the minimum for registering downtime, which for now will remain an experiment, and we will continue to use MS SQL and IIS 🙂