I have updated my code but nothing happens (no update).
I’m not sure about this part:
$table_pss_product = $ps->getTableName('pssProduct');//className
$updateStatement = $ps->prepare("UPDATE " . $table_pss_product . "
SET `active`=:active
WHERE `supplier_reference`=:supplier_reference");
$updateStatement->bindParam(':active', $active, PDO::PARAM_INT);//$active is int (tinyint(1))
$updateStatement->bindParam(':supplier_reference', $supplier_reference);//string (varchar(32) )
The table is in a remote database. I don’t know if getTableName can works outside MODX.
The complete code:
<?php
$debug = true;
$notifTech = "xxx@xxxx.fr";
$movefiles = false;//pour faciliter les tests
/******************************
* Verifications
* Clé présente et correcte
* Des fichiers XML existent
* */
if(isset($_GET['k']) && $_GET['k'] == 'xxxxxxx') {
//continuer
$private = dirname(MODX_CORE_PATH);///var/www/clients/client1/web118/private
$dl_path = $private . '/tools/workflow/fos';
if($debug==true){ $output .= "<br> dl_path: " . $dl_path . "<br>"; }
/******************
* mode de connexion ftp
* $sftp = false | true
**/
$sftp = true;
if($sftp==false){
/******************
* connexion ftp
**/
$ftp_conn = ftp_connect("xxxxx.fr") or die("Cannot connect");
ftp_login($ftp_conn, "xxxxxx", "xxxxxx") or die("Cannot login");
ftp_pasv($ftp_conn, true) or die("Cannot change to passive mode");
$files_remote = ftp_nlist($ftp_conn, "/EMI/*.txt");
foreach ($files_remote as $file_remote){
// download with ftp_get
// download server file
$local_file = $dl_path . $file_remote;
//$local_file = $dl_path . "test.txt";
if($debug==true){
//echo "echo";
$output .= "Found file_remote: ' . $file_remote\n";
$output .= "<br>local_file: " . $local_file;
}
if (ftp_get($ftp_conn, $local_file, $file_remote, FTP_ASCII ))
{
if($debug==true){ $output .= "<br>Successfully written to $local_file."; }
}
else
{
if($debug==true){ $output .= "<br>Error downloading $file_remote."; }
}
}
} else {
/******************
* connexion SFTP
* https://www.php.net/manual/en/function.ssh2-sftp.php
* https://randomdrake.com/2012/02/08/listing-and-downloading-files-over-sftp-with-php-and-ssh2/
**/
//preprod
$host = 'xxx-xxxx-xxxx.xxxx.fr';
$username = 'xxxx';
$password = "xxxxx";
$port = 22;
//dossier où les fichiers FT doivent être récupérés
if($debug==true){$remoteDir = '/ftp/out/fd/';} else {$remoteDir = '/ftp/out/fd/';}
//dossier d'archive
if($debug==true){$remoteArcDir = '/ftp/arc/';} else {$remoteArcDir = '/ftp/arc/';}
$localDir = $dl_path.'/'.$remoteDir.'/';
//Gestion des imprevus
if (!function_exists("ssh2_connect")){
die('Function ssh2_connect does not exist.');
}
if (!$connection = ssh2_connect($host, $port)){
die('Failed to connect.');
}
if (!ssh2_auth_password($connection, $username, $password)){
die('Failed to authenticate.');
}
if (!$sftp_conn = ssh2_sftp($connection)){
die('Failed to create a sftp connection.');
}
if (!$dir = opendir("ssh2.sftp://$sftp_conn$remoteDir")){
die('Failed to open the directory.');
}
$files_remote = array();
while ( ($file_remote = readdir($dir)) !== false)
{
if(substr($file_remote, -4)==".csv")
{
$files_remote[]=$file_remote;
}
}
closedir($dir);
foreach ($files_remote as $file_remote)
{
if($debug==true){ $output .= "Copying file: $file_remote\n"; }
if (!$remote = fopen("ssh2.sftp://$sftp_conn$remoteDir$file_remote", 'r'))
{
if($debug==true){ $output .= "Failed to open remote file: $file_remote\n"; }
continue;
}
if (!$local = fopen($localDir . $file_remote, 'w'))
{
if($debug==true){ $output .= "Failed to create local file: $file_remote\n"; }
continue;
}
$read = 0;
$filesize = filesize("ssh2.sftp://$sftp_conn/$remoteDir$file_remote");
while ( ($read < $filesize) && ($buffer = fread($remote, $filesize - $read)) )
{
$read += strlen($buffer);
if (fwrite($local, $buffer) === FALSE)
{
if($debug==true){ $output .= "Failed to write to local file: $file_remote\n"; }
break;
}
}
fclose($local);
fclose($remote);
}
}
/************ Definition des chemins **********/
/* cas distant : recuperetation de fichies sur un serveur */
//$ftp_path = dirname(MODX_CORE_PATH) . '/tools/sandbox/sandbox_ftp1/';
//$ftp_path = $dl_path;
// Chemin FTP dédié pour la réception ds fichiers XML
//$ftp_in = $dl_path . '/EMI/';
$ftp_in = $dl_path . $remoteDir;
// Chemin pour les fichiers traités a archiver (deplacement)
//$ftp_out = '/ARC/';
//Get XML files
$ext = "*.csv";//extension
$files = glob($ftp_in . "*.csv");
if($debug==true){
$output .= "<br><strong>Dossier scanné :</strong> " . $ftp_in . '<br>';
}
/************ Verifi. si des fichiers XML existent **********/
if (!$files) {
if($debug==true){
$output .= "<br><strong>Aucun fichier ". $ext." à traiter ! </strong><br>";
} else {
//die();
}
}
} else {
// clé non présente ou incorrecte : renvoyer 401
$modx->sendUnauthorizedPage();
/*
header('HTTP/1.1 401 Unauthorized');
@session_write_close();
exit(0);
*/
}
$modx->invokeEvent('OnHandleRequest');
$ps_database_host = $modx->getOption('sconf_psdb-host');
$ps_database_charset = $modx->getOption('sconf_psdb-charset');
$ps_database_name = $modx->getOption('sconf_psdb-name');
$ps_database_prefix = $modx->getOption('sconf_psdb-prefix');
$ps_database_username = $modx->getOption('sconf_psdb-username');
$ps_database_password = $modx->getOption('sconf_psdb-pw');
$ps = new xPDO('mysql:host=' . $ps_database_host .
';dbname=' . $ps_database_name .
';charset=' . $ps_database_charset,
$ps_database_username,
$ps_database_password );
// text connex
//echo $o = ($ps->connect()) ? 'Connected' : 'Not Connected';
if($debug==true){
echo "echo";
$output .= "********************** MODE DEBUF ACTIF *********************** <br>";
$o = ($ps->connect()) ? 'Connected' : 'Not Connected';
$output .= 'Test connexion base de données : ' . $o . '<br>';
}
// load package
$can_work_ps = $ps->addPackage('presta',MODX_CORE_PATH.'components/',$ps_database_prefix);
if (!$can_work_ps) die('Prestashop Package could not be loaded.');
if (is_array($files)) {
/*******************************************************
* TEST parameterized, prepared query statement
* https://bobsguides.com/blog.html/2014/12/17/using-parameterized-prepared-statements-to-retrieve-data/
*/
$table_pss_product = $ps->getTableName('pssProduct');//className
$updateStatement = $ps->prepare("UPDATE " . $table_pss_product . "
SET `active`=:active
WHERE `supplier_reference`=:supplier_reference");
$updateStatement->bindParam(':active', $active, PDO::PARAM_INT);//$active is int (tinyint(1))
$updateStatement->bindParam(':supplier_reference', $supplier_reference);//string (varchar(32) )
$output .= "is_array files exists";
$line_count = 0;
foreach($files as $file) {
//loop files
// Read a CSV file
$handle = fopen($file, "r");
// Optionally, you can keep the number of the line where
// the loop its currently iterating over
$lineNumber = 1;
// Iterate over every line of the file
while (($raw_string = fgets($handle)) !== false) {
if($lineNumber > 1){
// Parse the raw csv string: "1, a, b, c"
$row = str_getcsv($raw_string);
// into an array: ['1', 'a', 'b', 'c']
// And do what you need to do with every line
//var_dump($row);
//print_r($row);
$line=$row[0];
$proddispo_ar = explode(";", $line);
$prod_refsupplier = $proddispo_ar[0];//normalement non prefixee, TODO: prevoir cas avec prefixe?
$prod_status = $proddispo_ar[1];
$output .= '<br>ref fournisseur: ' . $prod_refsupplier;
$output .= ' prod_status: ' . $prod_status . '<br>';
//new https://bobsguides.com/blog.html/2014/12/17/using-parameterized-prepared-statements-to-retrieve-data/
$supplier_reference = $proddispo_ar[0];
$active = $proddispo_ar[1];
// Execute update
$updateStatement->execute();
}
// Increase the current line
$lineNumber++;
}
fclose($handle);
//print_r($rows_ar);//Array ( [id_shop] => 40 ) Array ( [id_shop] => 41 )
//die("333");//ok works until here
$output .= "line_count: " . $lineNumber;
//anciennes position
//deplacer les fichier dans /ARC (cas traitement sur FTP distant)
$filename = basename($file);
if($debug==true){
$output .= '<br><br><strong>déplacement du fichier distant dans le dossier d\'archivage en cours......';
$output .= '<br>from: ' . $remoteDir . $filename;
$output .= '<br>to: ' . $remoteArcDir . $filename;
}
if($movefiles==true){
/*******************
* Deplacement des fichiers distants dans le repertoire archives
*/
if($sftp==false){
//en mode FTP
ftp_rename($ftp_conn, $remoteDir . $filename, $remoteArcDir . $filename);
} else {
//en mode SFTP
ssh2_sftp_rename($sftp_conn, $remoteDir . $filename, $remoteArcDir . $filename);
//ssh2_sftp_unlink($sftp_conn, $remoteDir . $filename, $remoteArcDir . $filename);
}
/*********************
* Deplacement fichier local : necessaire sinon sera traité à nouveau
**/
if($debug==true){
$output .= '<br><br>déplacement du fichier local dans le dossier d\'archivage en cours...';
}
rename($dl_path . $remoteDir . $filename, $dl_path . $remoteArcDir . $filename);
}
}
}
if($debug==true){
return $output;
}