[marcosfpa]

I have the following demand: I need to export a .csv or .txt file weekly with data from a PM TABLE and save this file to a folder on the network here outside the server where ProcessMaker is installed.
I have already created the process to run every Monday, capture the data and put it in a GRID. Now I needed help to create the CSV file and save the generated file to a folder on the network ...

[amosbatto]

You can use this trigger code something like this:

Code: Select all

//set to path where CSV file will be written:
$pathCsv = '/tmp/PMT_MY_TABLE.csv';
if (file_exists($pathCsv)) {
  if (unlink($pathCsv) === false) {
     throw new Exception("Unable to delete file '$pathCsv'");
  }
}
$sql = "SELECT * FROM PMT_MY_TABLE INTO OUTFILE '$pathCsv' FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ";
executeQuery($sql);
if (!file_exists($pathCsv)) {
  throw new Exception("Unable to export CSV file with query: $sql");
}
$connection = ssh2_connect('shell.example.com', 22);  //set to the domain of the remote server
ssh2_auth_password($connection, 'username', 'password'); //set to the username and password on the remote server
ssh2_scp_send($connection, $pathCsv, '/remote/MY_TABLE.csv', 0644); //set to the path on the remote server

If your server doesn't require remote login with SSH and is addressable (for example a NAT), then you can just use the copy() function.

[marcosfpa]

I am having doubts to generate the CSV file, since it must be composed in the following way: a header line and 2 (two) lines of movement as each block of information in the CSV. Being that each line of movement comes from different PM_TABLES ...
Any help with that?

[amosbatto]

I am having doubts to generate the CSV file, since it must be composed in the following way: a header line and 2 (two) lines of movement as each block of information in the CSV. Being that each line of movement comes from different PM_TABLES ...
Any help with that?

You can use UNION or UNION ALL to join multiple queries. Remember that you can't join two queries which return a different number of fields. Also, there is no easy way to automatically output the names of the fields, without calling a custom function.

You can use a query like this:

Code: Select all

$sql = "SELECT 'ID','NAME','SALARY','SAL1','SAL2','SAL3'
UNION ALL
SELECT * FROM PMT_MY_TABLE1
UNION ALL
SELECT * FROM PMT_MY_TABLE2
INTO OUTFILE 'E:\\JOSE DATA\\addstock7.csv'
FIELDS TERMINATED BY ';'
ENCLOSED BY '\"' ";

See: https://stackoverflow.com/questions/227 ... a-csv-file

[marcosfpa]

And how could I insert the header that is only the first line of the CSV?

[programerboy]

Hi,

In the amosbatto's answer, he is written in query first select header for your exported csv.

Code: Select all

$sql = "SELECT 'ID','NAME','SALARY','SAL1','SAL2','SAL3'
UNION ALL 
...
"

Thanks

[marcosfpa]

I'm doing something wrong, by TRIGGER in ProcessMaker the file is not generated in the / tmp folder, but using the same MySQL code by PHPMYADMIN the file is created successfully.
What can it be? Any permissions on the / tmp folder?

[amosbatto]

On my system (Debian , the permissions in the tmp directory are rwxrwxrw, so the Apache user can write to the directory, but your system might be different. If the file /tmp/PMT_MY_TABLE.csv already exists, then the command will fail. If you don't specify the complete path, then a trigger will try to write to a file in a protected directory in the processmaker installation. Also check your PM debugger and /var/log/php_errors.log to see if there are any errors.