[fcomijangos]

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;

[amosbatto]

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.

[fcomijangos]

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.

[amosbatto]

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.

[fcomijangos]

Hello amosbatto,

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.

Regards

[amosbatto]

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 );            
}

[amosbatto]

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.

[fcomijangos]

Hello amosbato,

Trying to retake this conversation instead of opening a new topic... we have implemented the .csv file upload and display using DataTables. The summary was solved using arrays, no problem at all there. My question now is... how can I display the Datatable I have uploaded via .csv file on an output document?

I tried using your solution here: https://forum.processmaker.com/viewtopic.php?t=730352 but it doesn't seem to be working using DataTables where the values change in each case depending on the file uploaded. We aren't using a grid because of the great amount of rows that the users want to upload (over 2,500 rows).

Regards

[ajcosta]

Hi team,

Just came across this post and I am actually using Datatables with Editor. I have tried to use use editor inside the Dynaform + Panel, but I haven't found out how to save the changed cell or data back to the hidden textarea variable, so I can then use a trigger to save it in DB.

$("#gridContent").hide();
var sGrid = $("#gridContent").getValue();

Can anyone help me with it?

AJ