Create a spreadsheet file from a database query and email it

From pmusers
Jump to: navigation, search

The following example shows how to query a Report Table or another database table to create a comma separated value (CSV) file which can be opened in any spreadsheet program such as Excel, LibreOffice Calc or gnumeric. A separate process will query the database every day and email the CSV to a designated user.


Process: CSV_file_from_database_query_and_email_daily-1.pmx (right click and select Save Link As)
Author: Amos Batto <amos[at]processmaker.com>
Version: 1.0 (2019-09-24)


First, create a separate process that will loop endlessly so that every day it executes a trigger which queries the database, constructs a CSV file, and sends out an email containing the CSV file:

CsvFile SeparateProcessToExecuteDaily.png

In the properties of the intermediate timer event, set the timing so the script task will execute once per day:

CsvFile TimerExecuteOncePerDay.png

Create a email template named "dailyReport.html":

CsvFile CreateTemplate.png

Then, create the following trigger:

//function to create a comma-separated-value for a CSV file
function prepareCsvField($str) {
   $str = str_replace('"', '""', $str);
   if (preg_match('/[,;"\n\r]/', $str) or trim($str) != $str) {
      $str = '"' . $str . '"';
   }
   return $str;
} 

//create a temporary CSV file to export with today's date in the filename 
$filePath = tempnam(sys_get_temp_dir(), 'report_'.date('Y-m-d').'_') . ".csv"; 
$fFile = fopen($filePath, "w");
   
if (!$fFile) {
   throw new Exception("Error opening file '$filePath' for writing.");
}

//set the names of the fields that will be returned by the SQL query:
$aFields = [
   'ID', 'APPLICATION', 'CASEID', 
   'BRANCH_X',   'BRANCH_Y',   'BRANCH_Z', 
   'HELPDESK_X', 'HELPDESK_X', 'HELPDESK_Y', 
   'MODIFIER_X', 'MODIFIER_Y', 'MODIFIER_Z'
];

//change the following for your SQL query:
$someVar = addslashes(@@someVariable);

$sql = "SELECT SOME_ID AS ID, APP_UID AS APPLICATION, APP_NUMBER AS CASEID, 
   BRANCH_X, BRANCH_Y, BRANCH_Z, HELPDESK_X, HELPDESK_X, HELPDESK_Y, 
   MODIFIER_X, MODIFIER_Y, MODIFIER_Z
   FROM PMT_SOME_TABLE
   WHERE SOME_FIELD='$someVar' ";   
$aRows = executeQuery($sql);

if (!is_array($aRows)) {
   throw new Exception("Error in query: $sql");
}

//set to IDs of fields inside grid:	

$sHeaders = '';
foreach ($aFields as $header) {
   $sHeaders .= ($sHeaders == '' ? '' : ';') . prepareCsvField($header);
}

//write the headers (IDs of grid fields) as the first line in CSV file:
fwrite($fFile, $sHeaders . "\n");
	
//write a line in the CSV file for each row in grid:
foreach ($aRows as $aRow) {
   $sRow = '';
	
   foreach ($aFields as $fieldId) {
      $sRow .= ($sRow == '' ? '' : ';') . prepareCsvField( $aRow[$fieldId] );
   }
	
   fwrite($fFile, $sRow . "\n");
}

fclose($fFile);

$emailFrom = 'manager@example.com'; //change to the same email address set under Admin > Email Servers
$emailToUser = 'johndoe';           //change to the username who will receive the email
$aUserTo = userInfo($emailToUser);

$aAttachment = array(
   'Report_' . date('Y-m-d') . '.csv' => $filePath
);

@@ret = PMFSendMessage(@@APPLICATION, $emailFrom , $aUserTo['mail'], '', '',
   'Report ' . date('Y-m-d'), 'dailyReport.html', array(), $aAttachment);
   
//delete the temporary CSV file:
unlink($filePath);

Adjust the query and variables to match the setup in your organization.

Then, set the trigger to be executed by the script task:

CsvFile SetTriggerInScriptTask.png

Finally, start a case and let it run endlessly so it will execute once per day.