Export grid with MultipleFile to PM Table

From pmusers
Revision as of 04:19, 13 August 2019 by Amosbatto (talk | contribs) (Created page with "This example shows how to export a grid containing a textbox, a dropdown box, a checkbox, a datetime and a MultipleFile field to a PM Table. Because MultipleFile fields can co...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

This example shows how to export a grid containing a textbox, a dropdown box, a checkbox, a datetime and a MultipleFile field to a PM Table. Because MultipleFile fields can contain more than one file, a second PM Table is created to hold information about the uploaded files. The trigger code shows how to get the URL and file path on the ProcessMaker server to access the files uploaded to a MultipleFile field in a grid.


Process: Export_grid_with_MultipleFile_to_PM_Tables-1.pmx (right click and select Save Link As)
PM Table: Table_PMT_PRODUCTS_LIST.pmt (right click and select Save Link As)
PM Table: Table_PMT_PRODUCT_FILES.pmt (right click and select Save Link As)
Author: Amos Batto (amos@processmaker.com)
Version: 1.0 (2019-08-12)
Tested in: PM 3.3.10 Community in Debian 9.5 with PHP 5.6.37


Create the following PM Table named "PRODUCTS_LIST", which will hold each row added to a grid:

ExportMultiFileToTable Create PRODUCTS LIST Table.png

Note that ProcessMaker automatically changes the table name from "PRODUCTS_LIST" to "PMT_PRODUCTS_LIST" in the database.

Then, create a second PM Table named "PRODUCT_FILES", which will hold each file uploaded to the MultipleFile field in the grid:

ExportMultiFileToTable Create PRODUCT FILES Table.png

Then, create the following Dynaform with a grid where the user will enter a list of products:

ExportGridToPMTable FormInDesigner.png

The "productsList" grid contains the following fields:

  • Textbox with the ID "productName",
  • Dropdown box with the ID "productType",
  • Checkbox with the ID "inStock",
  • Datetime with the ID "launchDate",
  • MultipleFile with the ID "specFiles"

Then, create the following trigger to export each row in the "productsList" grid to the "PMT_PRODUCTS_LIST" table and each uploaded file to the "PMT_PRODUCT_FILES":

$db = 'workflow';         //set to ID of database connection or 'workflow' if PM Table
$gridId = 'productsList'; //set to name of grid variable
$gridFileId = 'specFiles';//set to ID of MultipleFile field in grid
$aGrid = @=productsList;  //set to grid variable
$caseNo = @@APP_NUMBER;

//function to escape strings for database queries:
//  $str: String to escape.
//  $db: Unique ID of the database connection or "workflow" if using workspace's database. 
function _esc($str, $db = 'workflow') {
   $con = Propel::getConnection( $db );
   $dbType = $con->getDSN()["phptype"];
   if ($dbType == 'mysqli') {
      return mysqli_real_escape_string($con->getResource(), $str);
   }
   else {
      return addslashes($str);
   }
}

if (isset($aGrid) and is_array($aGrid)) {
   //delete any existing records for the current case:
   executeQuery("DELETE FROM PMT_PRODUCTS_LIST WHERE CASE_NUMBER=$caseNo", $db);
   executeQuery("DELETE FROM PMT_PRODUCT_FILES WHERE CASE_NUMBER=$caseNo", $db);

   $g = new G();

   //loop through each row in grid, writing its fields to the database table	
   for ($i = 1; $i <= count($aGrid); $i++) {
      $productName = _esc($aGrid[$i]['productName'], $db);
      $productType = _esc($aGrid[$i]['productType_label'], $db);
      $inStock     = $aGrid[$i]['inStock'] == '1' ? 1 : 0; //checkbox
      $launchDate  = empty($aGrid[$i]['launchDate']) ? 'NULL' : "'".$aGrid[$i]['launchDate']."'";
      
      $filename = $filePath = $fileUrl = '';
      $fileIndex = 0;

      //for each file in the MultipleFile in the grid row, construct the path to the file: 
      foreach ($aGrid[$i][$gridFileId] as $aFile) {
         $fileIndex++;
         $fileId = $aFile['appDocUid'];
         $filename = $aFile['name'];
         $version = $aFile['version'];
        
         $fileUrl = ($g->is_https() ? "https://" : "http://") .
            $_SERVER['HTTP_HOST'] .'/sys'. @@SYS_SYS .'/'. @@SYS_LANG .'/'. @@SYS_SKIN .
            '/cases/cases_ShowDocument?a=' . $fileId . '&v=' . $aFile['version'];
         $fileUrl = _esc($fileUrl, $db);
        
         $filePath = _esc(PATH_DOCUMENT . $g->getPathFromUID(@@APPLICATION) . PATH_SEP . $fileId .
            '_1.' . pathinfo($filename, PATHINFO_EXTENSION), $db);
         $filename = _esc($filename, $db);
		  
         //insert a database record for each file in the grid row:		  
         $sql = "INSERT INTO PMT_PRODUCT_FILES 
            (CASE_NUMBER, ROW_NUMBER, FILE_INDEX, FILE_ID, FILE_VERSION, FILENAME, FILE_URL, FILE_PATH)
            VALUES ($caseNo, $i, $fileIndex, '$fileId', $version, '$filename', '$fileUrl', '$filePath')";
         executeQuery($sql, $db);
     }

     //insert a database record for each row in the grid:
     $sql = "INSERT INTO PMT_PRODUCTS_LIST 
        (CASE_NUMBER, ROW_NUMBER, PRODUCT_NAME, PRODUCT_TYPE, IN_STOCK, LAUNCH_DATE)
        VALUES ($caseNo, $i, '$productName', '$productType', $inStock, $launchDate)";
     @@ret = executeQuery($sql, $db);
   }
}

Notice that it is not possible to insert a value of (empty string) in a datetime field, so an empty string is converted to NULL when inserting in the PMT_PRODUCTS_LIST.LAUNCH_DATE field in the database.

When the following form is filled by the user:

ExportMultiFileTable FilledForm.png

Its grid rows will be exported to the PMT_PRODUCTS_LIST table:

ExportMultiFileTable PRODUCTS LIST table.png

Likewise, its files uploaded to the MultipleFile fields in the grid will be exported to the PMT_PRODUCT_FILES table:

ExportMultiFileToTable PRODUCT FILES table.png