Databases for controlling RCS messages
Custom databases can be used effectively to manage business processes and perform basic controlling. In this tutorial, we'll show you how to collect key performance indicators (KPIs) such as the number of RCS messages sent per day. This approach can easily be adapted to a wide variety of scenarios. We assume you are familiar with the basics of RCS messages and custom databases.
Once you've created a database in todo4teams, you first generate a table for the relevant controlling purpose. In this example, we'll store the number of RCS messages sent per day, along with an optional comment. First, we create a table:
creationdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
rcsCounter INT,
description VARCHAR(32000));
In a planned script for sending RCS messages, we then insert the corresponding data. We assume the database is named "RCSQuittungsDB":
rcsCounter = ....
description = .....
var con = helper.getCustomDB("RCSQuittungsDB");
var stmt = con.prepareStatement("INSERT INTO RCSQuittungen (rcsCounter, description) VALUES (?,?)");
stmt.setInt(1, rcsCounter);
stmt.setString(2, doneComment);
stmt.executeUpdate();
stmt.close();
This code snippet allows you to create an entry in the RCS Receipts table at the specified interval within the corresponding scheduled script. You can then use the appropriate SELECT statements on this table to, for example, check the number of RCS messages sent per day or per month.
To make this process more user-friendly and allow users without administrative rights to access this functionality, you can use a form:
You can specify the date range and optionally a filter. The search button then displays the results directly in the form's table. Here's the callback for the search button:
var con = helper.getCustomDB("RCSQuittungsDB");
var sd = helper.getFormValue(form.getMetaName(), "Startdate");
var ed = helper.getFormValue(form.getMetaName(), "Enddate");
var stmt = null;
var sqlResult = null;
try
{
if (sd != null && ed != null)
{
var startdate = new java.text.SimpleDateFormat("yyyy-MM-dd 00:00:00.000").format(sd);
var enddate = new java.text.SimpleDateFormat("yyyy-MM-dd 23:59:59.999").format(ed);
var searchterm = helper.getFormValue(form.getMetaName(), "Suchbegriff");
var maxHits = helper.getFormValue(form.getMetaName(), "maxHits");
var queryStart = "SELECT creationdate,rcsCounter,description FROM RCSQuittungen WHERE ";
var queryEnd = " ORDER BY creationdate DESC OFFSET 0 ROWS FETCH NEXT "+maxHits+" ROWS ONLY";
if (searchterm != null && !searchterm.isBlank())
{
stmt = con.prepareStatement(queryStart + "description LIKE ? AND creationdate>='"+startdate+"' AND creationdate<='"+enddate+"'"+queryEnd);
stmt.setString(1, "%"+searchterm+"%");
}
else
{
stmt = con.prepareStatement(queryStart +"creationdate>='"+startdate+"' AND creationdate<='"+enddate+"'"+queryEnd);
}
sqlResult = stmt.executeQuery();
var tableResult = new java.util.HashMap();
while (sqlResult.next())
{
var values = new StringArray(3);
values[0] = sqlResult.getString("creationdate");
values[1] = sqlResult.getString("rcsCounter");
values[2] = sqlResult.getString("description");
tableResult.put(values[0], values)
}
helper.setFormValue(form.getMetaName(), "Suchergebnis", tableResult);
if (tableResult.size() == 0)
{
helper.infoMessage("Keine Treffer");
}
if (tableResult.size() == maxHits)
{
helper.infoMessage("Maximale Anzahl Treffer erreicht: "+maxHits);
}
}
else
{
helper.infoMessage("Bitte Start- und Enddatum angeben!");
}
}
catch(de)
{
println(de);
}
finally
{
if (sqlResult) sqlResult.close();
if (stmt) stmt.close();
}
The results of the query can be downloaded directly as a CSV file using the download button:
var con = helper.getCustomDB("RCSQuittungsDB");
var sd = helper.getFormValue(form.getMetaName(), "Startdate");
var ed = helper.getFormValue(form.getMetaName(), "Enddate");
var stmt = null;
var sqlResult = null;
try
{
if (sd != null && ed != null)
{
var startdate = new java.text.SimpleDateFormat("yyyy-MM-dd 00:00:00.000").format(sd);
var enddate = new java.text.SimpleDateFormat("yyyy-MM-dd 23:59:59.999").format(ed);
var searchterm = helper.getFormValue(form.getMetaName(), "Suchbegriff");
var maxHits = helper.getFormValue(form.getMetaName(), "maxHits");
var queryStart = "SELECT creationdate,rcsCounter,description FROM RCSQuittungen WHERE ";
var queryEnd = " ORDER BY creationdate DESC OFFSET 0 ROWS FETCH NEXT "+maxHits+" ROWS ONLY";
if (searchterm != null && !searchterm.isBlank())
{
stmt = con.prepareStatement(queryStart + "description LIKE ? AND creationdate>='"+startdate+"' AND creationdate<='"+enddate+"'"+queryEnd);
stmt.setString(1, "%"+searchterm+"%");
}
else
{
stmt = con.prepareStatement(queryStart +"creationdate>='"+startdate+"' AND creationdate<='"+enddate+"'"+queryEnd);
}
sqlResult = stmt.executeQuery();
var StringArray = Java.type("java.lang.String[]");
var sw = new java.io.StringWriter();
var w = new com.proxemo.xutl.csv.CsvWriter(sw, ';');
var head = new StringArray(3);
head[0] = "Datum";
head[1] = "RCSCounter";
head[2] = "Quittung";
w.writeRecord(head);
var rows = 0;
while (sqlResult.next())
{
var values = new StringArray(3);
values[0] = sqlResult.getString("creationdate");
values[1] = sqlResult.getString("rcsCounter");
values[2] = sqlResult.getString("description");
w.writeRecord(values);
++rows;
}
w.flush();
var csv = sw.toString();
w.close();
if (rows == 0)
{
helper.infoMessage("Keine Treffer");
}
else
{
if (rows == maxHits)
{
helper.infoMessage("Maximale Anzahl Treffer erreicht!");
}
helper.download("report_"+java.lang.System.currentTimeMillis()+".csv", csv.getBytes());
}
}
else
{
helper.infoMessage("Bitte Start- und Enddatum angeben!");
}
}
catch(de)
{
println(de);
}
finally
{
if (sqlResult) sqlResult.close();
if (stmt) stmt.close();
}