Filter Junk E-Mails

Task

Custom databases are an outstanding feature of todo4teams to get access to a database based on scripts.
The great advantage of this lies in the fact that the databases simplify very much a problem specific solution. In our example we will create a database to suppress SPAM emails predefined by their sender of the email. The job created by an incoming email should be closed automatically when the email comes from an address marked as a SPAM source.

Step 1: Defining the server property: In the first step we will define a server property that contains two important attributes within it's value. We will therefore define a new database.

This is on the one hand the name of the new database, defined by the dbname variable, and the directory in which the database files are stored. This is set by the variable basedir:

dbname=SpamlistDB
basedir=/tmp/spamlistdb

Step 2: Defining the necessary tables

Then log on to the web front end of todo4teams and change to the tab "Custom-DB".
The database SpamlistDB should already be offered there.
Now define a table, for example the table spamlist. To do this, the SQL syntax is used, as it is most always the case dealing with relational databases:

CREATE TABLE spamlist (taskid BIGINT, eventdate TIMESTAMP, email VARCHAR(256))

The command SHOW TABLES will display all tables within the current database.

Step 3: Get access to the database using a script.

The isSpam function is used to search for incoming emails within the mailbox using an input script that filters the emails corresponding to the values of the table spamlist.  If this is the case and a defined value is found by the script, the already created job will be closed automatically and the finishing comment "Email is blacklisted." will be added.

function isSpam()
{
   var conny  = helper.getCustomDB("SpamlistDB");
   var selectStmt = conny.createStatement();
   var numberOfHits = selectStmt.executeQuery("SELECT COUNT(*) FROM spamlist WHERE '"+fromEmail+"' LIKE '%' || email ||'%'");
  if (numberOfHits.next())
  {
        var hits=numberOfHits.getInt(1);
        selectStmt.close();
       return hits>0;
  }

  return false;
}

println("Checking for spam in Custom DB:"+fromEmail);

if (isSpam())
{
    println("Closing task with id "+task.id+" for sender "+fromEmail);
    task.startWorking();
    task.setDoneComment("Email is blacklisted.");
    task.finishWorking(true);

}
else
{
       println("No Spam detected for task with id "+task.id+" for sender "+fromEmail);
}

Step 4: End-Action of a group to add new email addresses

In addition to the requirements described above, it should be possible for certain groups to mark a job generated from an email as SPAM. For this purpose the end-action of the group checks whether the word SPAM occurs. If so, the email address, if not already present, is included in the spamlist table.

function emailExists()
{
     var conny  = helper.getCustomDB("SpamlistDB");
     var selectStmt = conny.createStatement();
     var numberOfHits = selectStmt.executeQuery("SELECT COUNT(*) FROM spamlist WHERE email='"+fromEmail+"'");

    if (numberOfHits.next())
    {
         var hits=numberOfHits.getInt(1);
         selectStmt.close();
        return hits>0;
    }
    return false;
}

function insertEmail()
{
    var conny  = helper.getCustomDB("SpamlistDB");
    var insertStmt = conny.createStatement();
    var query = "INSERT INTO spamlist (taskid,creationdate,email) VALUES("+task.id+",CURRENT_TIMESTAMP,'"+fromEmail+"')";
    println("Insert email with query:"+query);
    insertStmt.execute(query);
    insertStmt.close();
}

if (task.getEmail() != null && task.getEmail().trim().length()>0)
{
    var fromEmail=task.getEmail().toLowerCase();

   if (task.getDoneComment().toLowerCase().contains("spam"))
   {
       if (emailExists())
       {
            print("Ignoring existing spam email from "+fromEmail+" for task id "+task.id)
       }
       else
       {
            insertEmail();
       }
   }
}