Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
#824570
Hello everyone,

I have a process which includes the uploading of a .xlsx file to feed the variables inside a grid. This grid has a total of eight columns and must be able to accept a total of 2,500 lines. The issue is that this creates a total of 20,000 values inside the grid and trying to uploading it, while successful, takes between four and five minutes.

Is there a way we can reduce the time BPM takes to upload this large file (31KB or 32KB)?

This is the code the trigger uses to upload the document
Code: Select all
require_once("/bpm/processmaker/shared/PhpSpreadsheet-develop/src/PhpSpreadsheet/Spreadsheet.php");

function getFilePath($appUid, $appDocUid) {
    $appDocument = new AppDocument();
    $result = $appDocument->load($appDocUid);
    $ext = pathinfo($result["APP_DOC_FILENAME"], PATHINFO_EXTENSION);

    $path = PATH_DOCUMENT
            . G::getPathFromUID($appUid)
            . PATH_SEP . $appDocUid
            . '_'
            . $result['DOC_VERSION']
            . '.'
            . $ext;
    return $path;
}

function getDataUsingPhpSpreadsheet($path) {
    $spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load($path);
    $sheet = $spreadsheet->getActiveSheet();
    $data = $sheet->toArray(null, true, true, true, true, true, true, true, true);
    return $data;
}

function getData($appUid, $appDocUid) {
    $path = getFilePath($appUid, $appDocUid);
    $data = getDataUsingPhpSpreadsheet($path);

    //remove headers row
    array_shift($data);

    $clients = [];
    foreach ($data as $key => $value) {
        $clients[$key + 1] = [
            'customerNumber' => $value['A'],
            'creditMemo' => $value['B'],
            'paymentNumber' => $value['C'],
			'invoiceDeduction' => $value['D'],
			'amountinCurrency' => $value['E'],
			'usdConversion' => $value['F'],
			'amountRemaining' => $value['G'],
			'comments' => $value['H'],
        ];
    }
    return $clients;
}

//main
$appUid = @@APPLICATION;
$index = @@INDEX;
$clientsFile = @@clientsFile;
//set to UID of form where file is uploaded
$dynUid = '6392997235b479ad6807a88026307315';

//validations
$clientsFile = json_decode($clientsFile);
//Set the path where the library is located
$file1 = '/bpm/processmaker/shared/PhpSpreadsheet-develop/src/Bootstrap.php';

if (empty($clientsFile[0]) || !file_exists($file1)) {
    PMFRedirectToStep($appUid, $index, 'DYNAFORM', $dynUid);
}

//process
require_once $file1;

$clients = getData($appUid, $clientsFile[0]);
@=clientsList = $clients;
#824575
Have you tried increasing the memory_limit in your env.ini file and in your php.ini file. (You should set the same value in both places).

Also you should check if your server is memory constrained. If so, you should add more RAM.

Also increase your max_input_vars setting in your php.ini file.

Have you tried converting your Excel file to a CSV file? The import of CSV should be faster.

Which version of PHP are you using? If you are using PHP 5, then I recommend that you upgrade to PHP 7.

If none of those things solve the problem, then you can import the file as CSV and use PHP's str_getcsv() to process the file. See this example:
https://www.php.net/manual/en/function. ... .php#99323

Also, you can use ParamQuery to display the grid inside a Dynaform panel, which is much faster than ProcessMaker's grids. You can add a hidden field to your Dynaform and then use a trigger to convert your grid array into JSON and save it in the variable associated with that hidden field. Then use JavaScript in Dynaform to convert from JSON to an array and display the array with ParamQuery.
#824700
Hello amosbatto ,

Thank you for your help, I took all of your recomendations into consideration and opted to upload a .csv file into a panel inside a dynaform to create a datatable. While this reduced the time considerably, is it possible to create a summary value with a panel? to obtain the total of one column.

I can't seem to find any information about reading the panel datatable.
#824701
If you are using DataTables, see:
https://datatables.net/plug-ins/api/sum()

I see that ParamQuery Pro (the paid version) has a summary option, but you have to create the function to calculate the summary:
https://paramquery.com/pro/demos/summary

(I prefer ParamQuery over DataTables, because ParamQuery includes editing of cells in the free version.)

Are you allowing the user to edit the table or is this read only? If read only, the easiest solution is to calculate the sum of a table column with PHP or JavaScript and insert it as the last column in the table.
#824742
fcomijangos wrote: Wed Jun 05, 2019 1:38 pm Do you have an example of using paramquery in a panel on dynaforms? I'm not familiar with it and don't know exactly how to start with it.
Example 1:
Here is a basic example adapted from the ParamQuery tutorial. In a Panel control in your Dynaform, add the following HTML:
<div id="companyProfitsGrid"></div>

Then add the following JavaScript to your Dynaform:
Code: Select all
   var data = [ [1,'Exxon Mobil','339,938.0','36,130.0'],
            [2,'Wal-Mart Stores','315,654.0','11,231.0'],
            [3,'Royal Dutch Shell','306,731.0','25,311.0'],
            [4,'BP','267,600.0','22,341.0'],
            [5,'General Motors','192,604.0','-10,567.0'],
            [6,'Chevron','189,481.0','14,099.0'],
            [7,'DaimlerChrysler','186,106.3','3,536.3'],
            [8,'Toyota Motor','185,805.0','12,119.6'],
            [9,'Ford Motor','177,210.0','2,024.0'],
            [10,'ConocoPhillips','166,683.0','13,529.0'],
            [11,'General Electric','157,153.0','16,353.0'],         
            [12,'Total','152,360.7','15,250.0'],                
            [13,'ING Group','138,235.3','8,958.9'],
            [14,'Citigroup','131,045.0','24,589.0'],
            [15,'AXA','129,839.2','5,186.5'],
            [16,'Allianz','121,406.0','5,442.4'],
            [17,'Volkswagen','118,376.6','1,391.7'],
            [18,'Fortis','112,351.4','4,896.3'],
            [19,'Crédit Agricole','110,764.6','7,434.3'],
            [20,'American Intl. Group','108,905.0','10,477.0']];
             
    var obj = {};
    obj.width = 700;
    obj.height = 400;
    obj.colModel = [{title:"Rank", width:100, dataType:"integer"},
        {title:"Company", width:200, dataType:"string"},
        {title:"Revenues ($ millions)", width:150, dataType:"float", align:"right"},
        {title:"Profits ($ millions)", width:150, dataType:"float", align:"right"}];
    obj.dataModel = {data:data};

    $("#companyProfitsGrid").pqGrid( obj );                                

Example 2:
You could fire a trigger beforehand to query the database and populate the grid. For example:
Code: Select all
$db = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'; //set to ID of database connection
$sql = "SELECT RANK, COMPANY, REVENUES, PROFITS FROM MY_TABLE";
$aRows = executeQuery($sql, $db);
$aPqRows = [];

foreach ($aRows as $aRow) {
    $aPqRows[] = [ $aRow['RANK'], $aRow['COMPANY'], $aRow['REVENUES'], $aRow['PROFITS'] ];
}

@@gridContent = json_encode($aPqRows);
Then add a textarea to your Dynaform which is associated with the "gridContent" variable.

Then, use this JavaScript in your Dynaform:
Code: Select all
$("#gridContent").hide();
var sGrid = $("#gridContent").getValue();

if (sGrid != '') { 
    var data = JSON.parse(sGrid);
    var obj = {};
    obj.width = 700;
    obj.height = 400;
    obj.colModel = [{title:"Rank", width:100, dataType:"integer"},
        {title:"Company", width:200, dataType:"string"},
        {title:"Revenues ($ millions)", width:150, dataType:"float", align:"right"},
        {title:"Profits ($ millions)", width:150, dataType:"float", align:"right"}];
    obj.dataModel = {data:data};

    $("#companyProfitsGrid").pqGrid( obj );            
}
#824743
I forgot to mention that you need to load the following in your "external libs" property of the Dynaform:
bootstable.js, jquery-ui.min.js, jquery-ui.min.css, pqgrid.min.js, pqgrid.min.css

By the way, if you are editing cells, I only got that to work correctly using jQuery UI v1.11.4 in ProcessMaker 3.3.4. If I used JQuery UI v1.12 (the latest version), then I got errors.

Hi, In order to delete a case you can follow the[…]

Hi. Have you tried to use JavaScript on your Dyn[…]

Hi Please, could you send information about the c[…]

Dashboard issue

Can you show me what you were trying to do in orde[…]