Trigger alarm messages
The task
Custom databases are an outstanding feature of todo4teams to access a database via scripting.
The big advantage here is that the databases will simplify solutions in a very problem specific way.
In our example we want to create a database from scratch to manage alarms.
Via an e-mail with a specific identifier one or more alarm SMS should be sent to a specific group of users.
However, these alarm SMS will only be sent if this has not already happened within the last 15 minutes.
Step 1: Defining the server properties
In the first step we define a server property that contains the two important properties of the newly created database in its value.
On the one hand that is the name of the database defined by the dbname variable and on the other hand the directory in which the database ist stored
This is determined by the basedir variable:
basedir=/tmp/alarmmessagedb
Step 2: Create the necessary tables
We login to the web frontend of todo4teams and switch to the tab "Custom-DB".
The AlarmMessageDB database should already be available in this tab.
No we will create the table "alarmmessage".
For this purpose we will use the SQL syntax, as is customary in relational databases:
The command SHOW TABLES will display all tables within the database.
Step 3: Access to the database with a script
To check the arrival of a new e-mail with the condition whether in the past 15 minutes an email has already arrived, you can use the following script within the e-mail inbox:
var subject=message.getSubject();
// email received that triggers sending alarm
if (subject.contains("send alarm message") && sender.contains("@bergener-bluma.de"))
{
// fetch last alarm messages from database
var con = helper.getCustomDB("AlarmMessageDB");
var selectStmt = con.createStatement();
var lastMessages = selectStmt.executeQuery("SELECT smssend from alarmmessage " +
"WHERE ABS({fn timestampdiff(SQL_TSI_MINUTE, CURRENT_TIMESTAMP, eventdate)})<15 and smssend=true");
var updateStmt = con.createStatement();
if (lastMessages.next())// no alarm send because we found message younger than 15 minutes
{
updateStmt.execute("INSERT INTO alarmmessage(smssend,eventdate) VALUES (false, CURRENT_TIMESTAMP)");
}
else // sending alarm now
{
helper.sendSMS("foo", "baa", "017123456789", "Alarm email received!", "016123456789");
updateStmt.execute("INSERT INTO alarmmessage(smssend,eventdate) VALUES (true, CURRENT_TIMESTAMP)");
}
selectStmt.close();
updateStmt.close();
}