Send an email when a task is overdue

From pmusers
Jump to: navigation, search

The following example shows how to automatically send an email notification when a task is overdue.



Process: Send email when overdue task-1.pmx (right click and select Save link as)
Author: Amos Batto (amos@processmaker.com)
Version: 1.0 (2018-09-14)
Tested in: ProcessMaker Enterprise 3.2.2 in Debian 9.5 with PHP 5.6.37



Create a separate process that loops endlessly to execute a script forever:

LooparoundProcessToExecuteScript.png

Set the intermediate timer event to execute once per day (or however often a new email should be sent out to remind the assigned user that he/she has an overdue task to complete):

TimerOncePerDay.png

Then, create the email template named "overdueCase.html" to send out the email:

Dear @#assignedUser,

The following case which is assigned to you is overdue:
Case No: @#caseNo
Case Title: @#caseTitle
Case Status: @#caseStatus
Current Task: @#taskName
Process: @#processName
When Assigned: @#taskAssignedDate
When Due: @#taskDueDate
Case Created By: @#caseCreatedBy
Case Start Date: @#caseStartDate
Case Last Updated: @#caseUpdatedDate
Link to Case: @#caseLink

Please open the case and finish the task.

The variables which are used in this email template will be set by the trigger which is executed by the script task.

Then, create the following trigger that will query the database for any cases with an overdue task and use PMFSendMessage() to send out an email to the assigned user to the task. In addition, it will send a carbon copy of the email to the manager of assigned user, if he/she has one in a department.

//set to the ID of the task which should send an email when overdue.
//this ID can be found by running a case in Debug Mode and looking at the TASK system variable:
$taskId = '3839855285b99bcad876958079357667';

//set to the same email address used in Admin > Settings > Email Servers:
$emailFrom = 'admin@example.com';

//Search for cases where the task is overdue:
$now = date("Y-m-d H:i:s");
$query= "SELECT * FROM APP_CACHE_VIEW WHERE TAS_UID='$taskId' AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND '$now' > DEL_TASK_DUE_DATE";

$aCases = executeQuery($query);

if (!is_array($aCases)) {
  die("Error: Bad Query: $query\n");
}
$g = new G();

foreach ($aCases as $aCase) {
  $aUser = userInfo($aCase['USR_UID']);	
  $c = new Cases();
  $aCaseInfo = $c->loadCase($aCase['APP_UID'], $aCase['DEL_INDEX']);

  $aData = array(
     'assignedUser'     => $aUser['firstname'] .' '. $aUser['lastname'],
     'caseNo'           => $aCase['APP_NUMBER'],
     'caseTitle'        => $aCase['APP_TITLE'],
     'caseStatus'       => $aCase['APP_STATUS'],
     'taskName'         => $aCase['APP_TAS_TITLE'],
     'processName'      => $aCase['APP_PRO_TITLE'],
     'taskAssignedDate' => $aCase['DEL_DELEGATE_DATE'], 
     'taskDueDate'      => $aCase['DEL_TASK_DUE_DATE'],
     'caseCreatedBy'    => $aCaseInfo['CREATOR'],
     'caseStartDate'    => $aCaseInfo['CREATE_DATE'],
     'caseUpdatedDate'  => $aCaseInfo['UPDATE_DATE'],
     'caseLink' => ($g->is_https() ? "https://" : "http://") . $_SERVER['HTTP_HOST'] .
        '/sys'.@@SYS_SYS.'/'.@@SYS_LANG.'/'.@@SYS_SKIN.'/cases/opencase/'.$aCase['APP_UID']
  );
  
	
  //carbon copy the manager if the assigned user has one:
  $cc = '';
  if ($aUser['reportsto']) {
	 $cc = userInfo($aUser['reportsto'])['mail'];
  }

  @@ret = PMFSendMessage(@@APPLICATION, $emailFrom, $aUser['mail'], $cc, '', 
	  'Overdue case ' . $aCase['APP_TITLE'], 'overdueCase.html', $aData);
}

Set the $taskId variable to the ID of a task in another process which will send out an email when it is overdue. Debug Mode can be used when running a case to find the task's ID or look it up in the TASK.TAS_UID field in the database.

Set the above trigger to be executed by the script task. It is also a good idea to set the above trigger to be executed by the "dummy task" in order to test it. Remember that the script task will run forever, so it is a good idea to debug it before letting it run forever.

Make sure that the server is configured to periodically execute the timereventcron.php script as a cron job in Linux or a Scheduled Task in Windows.

Finally, start a case in the process to let it loop forever.

Emails should arrive in the mailboxes of the assigned users like the following one:

EmailWhenOver.png


The script can be altered to send the email at a different time. For example, to send out an email notification when the task is over two days overdue, change these lines of the trigger from:

$now = date("Y-m-d H:i:s");
$query= "SELECT * FROM APP_CACHE_VIEW WHERE TAS_UID='$taskId' AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND '$now' > DEL_TASK_DUE_DATE";

To:

$twoDaysAgo = date('Y-m-d H:i:s', strtotime('-2 days'));
$query= "SELECT * FROM APP_CACHE_VIEW WHERE TAS_UID='$taskId' AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND DEL_TASK_DUE_DATE > '$twoDaysAgo' ";

To send out the email once per day for 5 days in a row after the due date, change the database query to this code:

$now = date("Y-m-d H:i:s");
$query= "SELECT * FROM APP_CACHE_VIEW WHERE TAS_UID='$taskId' AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND 
  '$now' > DEL_TASK_DUE_DATE  AND '$now' <  DATE_ADD(DEL_TASK_DUE_DATE, INTERVAL 5 DAY)";
</source

The script can also be altered to search for multiple tasks. Change the task ID in the database query from:
<source lang=php>
$query= "SELECT * FROM APP_CACHE_VIEW WHERE TAS_UID='$taskId' AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND '$now' > DEL_TASK_DUE_DATE";

To a list of tasks to search for:

$taskIDs = "'1272003815b8f3547cc9535064264588', '8842091725b7cb77f9d6906015609322', '9139066105b7cf3e6b909a1094180193'";
$query= "SELECT * FROM APP_CACHE_VIEW WHERE TAS_UID in ($taskIDs) AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND '$now' > DEL_TASK_DUE_DATE";

If needing to send out an email notification for all the overdue tasks in a process, then search for the process ID instead of the task ID:

$processId = "6481098795b72453af333d7049711002";
$query= "SELECT * FROM APP_CACHE_VIEW WHERE PRO_UID='$processId' AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND '$now' > DEL_TASK_DUE_DATE";

It is also possible to search for a list of processes where there are overdue tasks:

$processIDs = "'1272003815b8f3547cc9535064264588', '8842091725b7cb77f9d6906015609322', '9139066105b7cf3e6b909a1094180193'";
$query= "SELECT * FROM APP_CACHE_VIEW WHERE PRO_UID in ($processIDs) AND
  (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN' AND '$now' > DEL_TASK_DUE_DATE";

If only wanting to send out an email once, rather than every day, then use the following code to write a variable to the case when the email is sent, which can checked by the trigger:

foreach ($aCases as $aCase) {
   $aUser = userInfo($aCase['USR_UID']);	
   $c = new Cases();
   $aCaseInfo = $c->loadCase($aCase['APP_UID'], $aCase['DEL_INDEX']);

   //check if an email has already been sent:
   if (!isset($aCaseInfo['APP_DATA']['overdueEmailSent'])) { 

      $aData = array(
         'assignedUser'     => $aUser['firstname'] .' '. $aUser['lastname'],
         'caseNo'           => $aCase['APP_NUMBER'],
         'caseTitle'        => $aCase['APP_TITLE'],
         'caseStatus'       => $aCase['APP_STATUS'],
         'taskName'         => $aCase['APP_TAS_TITLE'],
         'processName'      => $aCase['APP_PRO_TITLE'],
         'taskAssignedDate' => $aCase['DEL_DELEGATE_DATE'], 
         'taskDueDate'      => $aCase['DEL_TASK_DUE_DATE'],
         'caseCreatedBy'    => $aCaseInfo['CREATOR'],
         'caseStartDate'    => $aCaseInfo['CREATE_DATE'],
         'caseUpdatedDate'  => $aCaseInfo['UPDATE_DATE'],
         'caseLink' => ($g->is_https() ? "https://" : "http://") . $_SERVER['HTTP_HOST'] .
            '/sys'.@@SYS_SYS.'/'.@@SYS_LANG.'/'.@@SYS_SKIN.'/cases/opencase/'.$aCase['APP_UID']
      );
  
	
      //carbon copy the manager if the assigned user has one:
      $cc = '';
      if ($aUser['reportsto']) {
         $cc = userInfo($aUser['reportsto'])['mail'];
      }

      @@ret = PMFSendMessage(@@APPLICATION, $emailFrom, $aUser['mail'], $cc, '', 
        'Overdue case ' . $aCase['APP_TITLE'], 'overdueCase.html', $aData);
      
      //set variable in case to indicate that an email was already sent:
      $aVars = array( 
          'overdueEmailSent' => date("Y-m-d H:i:s")
      ); 
      PMFSendVariables($aCase['APP_UID'], $aVars); 
   }
}