Generate reports as files with the Data Reporting Tools plugin

From pmusers
Jump to: navigation, search

Unfortunately the Data Reporting Tools plugin does not support exporting its reports as files, so it is not possible to send reports as file attachments in emails or automatically generate reports and store them. To add this functionality, the source code of the plugin needs to be changed.

This page explains how to alter the plugin's source code to generate reports as files and then create a trigger that will generate a report and send it as an attachment to all the users who have participated in the current case.


Tested in: ProcessMaker Enterprise Edition 3.2.2 with dataReportingTools plugin 1.1.4 in Debian 9.5 with PHP 5.6.37



Rename the workflow/engine/plugins/dataReportingTools/classes/class.ReportExporter.php to workflow/engine/plugins/dataReportingTools/classes/class.ReportExporter.php.old and then create a new version of the file with the following content:

<?php

namespace dataReportingTools\classes;

require_once(PATH_THIRDPARTY . "tcpdf" . PATH_SEP . "config" . PATH_SEP . "lang" . PATH_SEP . "eng.php");
require_once(PATH_THIRDPARTY . "tcpdf" . PATH_SEP . "tcpdf.php");

set_include_path(PATH_PLUGINS . 'dataReportingTools' . PATH_SEPARATOR . get_include_path());

require_once PATH_PLUGINS . 'dataReportingTools' . PATH_SEP . 'FPDF' . PATH_SEP . 'fpdf2File.php';

require_once 'classes/model/CrpReports.php';
require_once 'classes/model/CrpReports.php';
require_once 'classes/model/CrpUsersReport.php';
require_once 'classes/model/CrpUsersScope.php';

require_once 'classes/class.QueryStringScope.php';
require_once 'classes/class.QueryStringReport.php';
require_once 'classes/class.ReportExporterPDF.php';
require_once 'src/Services/BusinessModel/DataReportingTools/Reports.php';


class ReportExporter
{
    public function export(
        $reportId,
        $format,
        $usrUid,
        $sort,
        $filter,
        $group,
        $skip = null,
        $take = null,
        $columns = null,
        $outputPath = null   //if not set to null, then write the file to the path.
    )
    {
        $reportsBm = new \Services\BusinessModel\DataReportingTools\Reports();

        $reportProxy = new \CrpReports();
        $report = $reportProxy->load($reportId);

        $allMetadata = $reportsBm->getMetaDataReport($reportId, $usrUid);
        $metadata = $allMetadata;

        if ($columns !== null) {
            $metadata = [];
            foreach ($columns as $column) {
                foreach ($allMetadata as $metaCol) {
                    if ($metaCol['field'] === $column) {
                        array_push($metadata, $metaCol);
                    }
                }
            }
        }

        $rs = $reportsBm->getReportRecordSet(
            $reportId,
            $report['SCP_ID'],
            $usrUid,
            $sort,
            $filter,
            $group,
            $skip,
            $take
        );

        switch ($format) {
            case "PDF":
                $this->exportPDF($metadata, $rs['data'], $group, $outputPath);
                break;
            case "XLS":
                $this->exportExcel($metadata, $rs['data'], $group, $outputPath);
                break;
            case "CSV":
                $this->exportCSV($metadata, $rs['data'], $group, $outputPath);
                break;
            default:
                throw new Exception("Not supported format for export");
        }
    }

    function getHtml($metaColumns, $tableData)
    {
        $html = '<h1>' . $tableData['Name'] . '</h1> <h2></h2>';
        $html .= '<table border="1" cellspacing="0">';

        $html .= '<tr>';
        foreach ($metaColumns as $colData) {
            $html .= '<th style="font-weight: bold;">';
            $html .= (strlen($colData['title']) === 0)
                ? $colData['field']
                : $colData['title'];
            $html .= '</th>';
        }
        $html .= '</tr>';

        foreach ($tableData['Result'] as $row) {
            $html .= '<tr>';
            foreach ($row as $column => $value) {
                if ($column == strcasecmp($column, "Id")) {
                    continue;
                }
                $html .= '<td>';
                $html .= $value;
                $html .= '</td>';
            }
            $html .= '</tr>';
        }
        $html .= '</table>';

        return $html;
    }

    function temporalFile($extension)
    {
        $base = sys_get_temp_dir();
        $filename = uniqid('pm_report', true) . $extension;
        $retval = $base . DIRECTORY_SEPARATOR . $filename;
        return $retval;
    }

    function exportPDF($metadata, $rs, $group, $outputPath=null)
    {


        $fileName = $this->temporalFile('.pdf');
        $exporter = new ReportExporterPDF($fileName, $rs, $metadata, $group);
        $exporter->generatePDF()->Output();
        
        if ($outputPath) {
            //rename $outputPath to always have the .pdf extension:
            $aPath = pathinfo($outputPath);
            $newPath = ($aPath['dirname'] ? $aPath['dirname'] : sys_get_temp_dir()) .
                PATH_SEP. $aPath['filename'] . '.pdf';
            //Warning: this will overwrite any existing file:
            rename($fileName, $newPath); 
        }
        else {
            header('Content-type:  application/pdf');
            header('Content-Length: ' . filesize($fileName));
            header('Content-Disposition: attachment; filename=report.pdf');
            readfile($fileName);
            ignore_user_abort(true);
            unlink($fileName);
        }
    }

    function exportExcel($metadata, $rs, $group, $outputPath=null)
    {
        $fileName = $this->temporalFile('.xls');
        $colsInMeta = [];
        foreach ($metadata as $colMeta) {
            array_push($colsInMeta, $colMeta['field']);
        }

        $fp = fopen($fileName, "w");
        fwrite($fp, '<table border="1">');
        $this->exportExcelWriteTitles($fp, $metadata);

        $currentGroups = [];
        if ($group !== null) {
            foreach ($group as $g) {
                $columnName = trim(trim(trim($g['field'], "[]"), "'"), '"');
                $currentGroups[$columnName] = '_____';
            }
        }

        while ($rs->next()) {
            $row = $rs->getRow();
            if ($group !== null) {
                $this->exportExcelWriteGroup($fp, $currentGroups, $row, $metadata);
            }

            $rowString = '<tr>';

            foreach ($metadata as $metaCol) {
                $value = $row[$metaCol['columnName']];
                $valToWrite = trim(htmlentities($value));
                $rowString .= "<td>$valToWrite</td>";
            }

            $rowString .= '</tr>';
            fwrite($fp, $rowString);
        };

        fwrite($fp, '</table>');
        fclose($fp);
        
        if ($outputPath) {
            //rename $outputPath to always have the .xls extension:
            $aPath = pathinfo($outputPath);
            $newPath = ($aPath['dirname'] ? $aPath['dirname'] : sys_get_temp_dir()) .
                PATH_SEP. $aPath['filename'] . '.xls';
            //Warning: this will overwrite any existing file:
            rename($fileName, $newPath); 
        }
        else {
            header('Content-type: application/zip');
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=report.xls");
            readfile($fileName);
            ignore_user_abort(true);
            unlink($fileName);
        }
    }

    function exportExcelWriteTitles($fp, $metadata)
    {
        $rowString = '<tr>';
        foreach ($metadata as $columnData) {
            $valToWrite = empty($columnData['title'])
                ? trim($columnData['columnName'])
                : trim($columnData['title']);
            $valToWrite = htmlentities($valToWrite);
            $rowString .= "<th>$valToWrite</th>";
        }
        $rowString .= '</tr>';
        fwrite($fp, $rowString);
    }

    function exportExcelWriteGroup($fp, &$currentGroups, $row, $metadata)
    {
        $index = 0;
        foreach ($currentGroups as $column => $current) {
            $tab = '';
            $columnName = trim(trim(trim($column, "[]"), "'"), '"');
            for ($i = 0; $i < $index; $i++) {
                $tab .= str_repeat('&nbsp;', 7);
            }
            if ($row[$columnName] != $current) {
                $rowString = '<tr style="font-weight: bold">';
                $rowString .= '<td colspan="' . count($row) . '">';
                $titles = array_filter($metadata,
                    function ($metaCol) use ($columnName) {
                        return $metaCol['field'] == $columnName;
                    }
                );
                $titles = array_values($titles);
                $title = count($titles) > 0 ? $titles[0]['title'] : $columnName;
                $rowString .= $tab . htmlentities($title) . ": " . htmlentities($row[$columnName]);
                $rowString .= '</td>';
                $rowString .= '</tr>';
                fwrite($fp, $rowString);
                $currentGroups[$columnName] = $row[$columnName];
            }
            $index++;
        }
    }

    function exportCSV($metadata, $rs, $group, $outputPath=null)
    {
        $colsInMeta = [];
        foreach ($metadata as $colMeta) {
            array_push($colsInMeta, $colMeta['field']);
        }

        $fileName = $this->temporalFile('.csv');

        $fp = fopen($fileName, "w");

        $headerWritten = false;
        while ($rs->next()) {
            $dbRow = $rs->getRow();
            $row = $this->setRowColumnsAsInMetadata($dbRow, $metadata);

            if (!$headerWritten) {
                $titles = [];
                foreach ($metadata as $metaCol) {
                    array_push($titles, $metaCol['title']);
                }
                fputcsv($fp, $titles);
                $headerWritten = true;
            }

            fputcsv($fp, $row);
        };
        fclose($fp);
        
        if ($outputPath) {
            //rename $outputPath to always have the .csv extension:
            $aPath = pathinfo($outputPath); 
            $newPath = ($aPath['dirname'] ? $aPath['dirname'] : sys_get_temp_dir()) .
                PATH_SEP. $aPath['filename'] . '.csv';
            
            //Warning: this will overwrite any existing file:
            rename($fileName, $newPath);
        }
        else {
            header('Content-Type: text/csv; charset=utf-8');
            header("Content-Disposition: attachment; filename=report.csv");
            readfile($fileName);
            ignore_user_abort(true);
            unlink($fileName);
        }
    }

    function setRowColumnsAsInMetadata($dbRow, $metadata)
    {
        $row = [];
        foreach ($metadata as $metaCol) {
            $field = $metaCol['field'];
            $row[$field] = $dbRow[$field];
        }
        return $row;
    }
}

Then, create a trigger like the following that calls the ReportExporter::export() function to generate a report and save to a file on the ProcessMaker server:

//lookup the Report ID with this query in the database:
//SELECT REP_ID FROM CRP_REPORTS WHERE REP_NAME='report name';

$reportId = '2784944505b99bfe39f6044071279937'; //set to ID of the report

$type   = 'CSV';         //set to "PDF", "XLS" or "CSV"
$userId = @@USER_LOGGED;
$sort   = null;
$filter = null;
$group  = null;
$take   = null;
$skip   = null;
$columns= null;

$outputFilePath = '/scratchpad/dataReport.csv';

require_once PATH_PLUGINS.'dataReportingTools'.PATH_SEP.'classes'.PATH_SEP.'class.ReportExporter.php';
$repExp = new \dataReportingTools\classes\ReportExporter();

$repExp->export($reportId, $type, $userId, $sort, $filter, $group, $skip, $take, $columns, $outputFilePath);

$c = new Cases();
$aUsers = $c->getUsersParticipatedInCase(@@APPLICATION);
$to = '';
foreach ($aUsers['array'] as $userId => $userInfo) {
   $to .= (empty($to) ? '' : ', ') . $userInfo['USR_EMAIL'];
}

$aAttachments = array(
	basename($outputFilePath) => $outputFilePath
);

PMFSendMessage(@@APPLICATION, 'admin@example.com', $to, '', '', 'Report Available', 'newReport.html', 
			   array(), $aAttachments);
//if wishing to delete the file:
//unlink(outputFilePath);

Note: Some Linux systems don't allow PHP executed through Apache to write to the /tmp, so it may be necessary to create the report files in another directory. Make sure to give the directory write permissions so it can be accessed by the Apache user.

If just wishing the user to download the report file, then use trigger code like the following:

//lookup the Report ID with this query in the database:
//SELECT REP_ID FROM CRP_REPORTS WHERE REP_NAME='report name';

$reportId = '2784944505b99bfe39f6044071279937'; //set to ID of the report

$type   = 'CSV';         //set to "PDF", "XLS" or "CSV"
$userId = @@USER_LOGGED;
$sort   = null;
$filter = null;
$group  = null;
$take   = null;
$skip   = null;
$columns= null;

require_once PATH_PLUGINS.'dataReportingTools'.PATH_SEP.'classes'.PATH_SEP.'class.ReportExporter.php';
$repExp = new \dataReportingTools\classes\ReportExporter();

$repExp->export($reportId, $type, $userId, $sort, $filter, $group, $skip, $take, $columns);
die;