[njhajhajha]

hey,
I want to have link in dynaform which has excel with grid data within it.
Where grid data is generated from select query.

thank you!

[amosbatto]

First of all you will have to use PHP to query the database and generate the file, so this can't be done in JavaScript (except if you use JavaScript to call REST endpoints). You will have to install the PHPExcel library on your ProcessMaker server if you want to generate Excel files with PHP, but I recommend using a CSV (comma separated values) file, since it can created without importing external libraries and any spreadsheet program can open it.

Create a trigger something like this:

Code: Select all

function prepareCsvField($str) {
   $str = str_replace('"', '""', $str);
   if (preg_match('/[,;"\n\r]/', $str) or trim($str) != $str) {
      $str = '"' . $str . '"';
   }
   return $str;
} 

$inpDocId = '1234567890abcdef234567890abcdef'; //set to ID of Input Document in process

if (!empty(@@idToFind)) {
   $filePath = tempnam(sys_get_temp_dir(), "pm_") . ".cvs"; 
   $fFile = fopen($filePath, "w");
   if (!$fFile) {
      throw new Exception("Error opening temporary file '$filePath'");
   }

   $id = mysql_real_escape_string(@@idToFind);
   $sql = "SELECT FIELD1, FIELD2, FIELD3 FROM PMT_MYTABLE WHERE ID_FIELD='$id'";
   $aRows = executeQuery($sql);
   if (!empty($aRows)) {
      //write first line with column headers:
      $line = '';
      foreach ($aRows[1] as $fieldName => $value) {
         $line .= ($line == '' ? '' : ',') . prepareCsvField($fieldName);
      }
      fwrite($fFile, $line . "\n");

      //write a line for each record returned by SQL query:
      foreach ($aRows as $aRow) {
         $line = '';
         foreach ($aRow as $field => $value) {
            $line .= ($line == '' ? '' : ',') . prepareCsvField($value);
      }
      fwrite($fFile, $line . "\n");
   }
   fclose($fFile);
   
   $filename = pathinfo($filePath, PATHINFO_BASENAME);
   @@fileId = PMFAddInputDocument($inpDocId, null, 1, 'INPUT', '', 'Add',
     @@APPLICATION, @%INDEX, @@TASK, @@USER_LOGGED, 'file', $filePath);
     
   if (!empty(@@fileId)) {
      $g = new G();
      @@linkUrl = ($g->is_https() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] .
         '/sys' . @@SYS_SYS . '/en/neoclassic/cases/cases_ShowDocument?a=' . @@fileId;
      @@linkLabel = $filename;
   }   

   unlink($filePath);
}

Set this trigger to fire before the Dynaform.

Then, in your Dynaform, create a Link field which has its properties set to:
display text: @@linkLabel
href: @@linkUrl

formToLinkToCsvFile.png (15.38 KiB) Viewed 3101 times

If you want to dynamically generate the CSV file from fields inside the same Dynaform, you should use a Submit button to submit the Dynaform and then use PMFRedirectToStep() to return to the DynaForm. See: Redirection in Triggers with Submit buttons.

If you don't want to use browser redirection with PMFRedirectToStep() because you are using the ProcessMaker Mobile App, then you can use JavaScript to call REST endpoints. First, call POST /cases/{app_uid}/variable to send the "idToFind" variable for the SQL query to the case. Then, call POST /cases/{app_uid}/execute-trigger/{tri_uid} endpoint to execute the above trigger. Then, call the GET /cases/{app_uid}/variables endpoints to get the values of the "linkUrl" and "linkLabel" variables and use the link.setHref() and control.setText() functions in set these properties in the Link field.

[njhajhajha]

thank you it works