19 hours ago, MajklozaCZ said:Hi, this is such a great thing!
I just found a little bug in products with combination. When i update stocks for the first time everything is ok. But on every other update value of total stock freeze. Luckily value of each single combination change correctly. This bug is just in product list in adminstration as below.
Has anyone know how to fix it?
Thank you very much and sorry for my english.
Didnt think that would be anything important, but i made changes and tested it few times. Thanks for a tip Leo.
Now it updates product list on backoffice.
Also changed to use default db config from prestashop instead of creating another.
<?php set_time_limit(900); #version 3 $database = require_once('../app/config/parameters.php'); // Create connection function connect(){ global $database; try{ $db = new PDO('mysql:host='.$database['parameters']['database_host'].';dbname='.$database['parameters']['database_name'], $database['parameters']['database_user'], $database['parameters']['database_password']); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo 'connected!.<br />'; }catch(PDOException $e){ echo 'connection failed.<br />'; } return $db; } //get product reference codes and current quantity from prestashop Function shop_products($id){ $db = connect(); $array=array(); $array2=array(); $query= $db->prepare('select ps_product_attribute.id_product AS ID, ps_product_attribute.id_product_attribute AS IDpatr, ps_product.id_manufacturer AS manuid,ps_product_attribute.reference AS REF,ps_stock_available.quantity AS QTY from ps_product LEFT JOIN ps_product_attribute on ps_product_attribute.id_product=ps_product.id_product LEFT JOIN ps_stock_available on ps_stock_available.id_product_attribute=ps_product_attribute.id_product_attribute'); try{ $query->execute(); while (($row = $query->fetch(PDO::FETCH_ASSOC)) !== false) { if($id > 0 ){ if(!empty($row['REF']) && $row['ID'] == $id && $row['IDpatr'] != 0){ $array2[]=$row; } }else{ if(!empty($row['REF'])){ $array2[]=$row; } } } echo 'array filled.<br />'; }catch(PDOException $e){ echo 'array error.<br />'; } $query= $db->prepare('SELECT ps_product.reference AS REF, ps_product.id_manufacturer AS manuid,ps_stock_available.id_product_attribute AS BaseP, ps_stock_available.quantity AS QTY FROM ps_product LEFT JOIN ps_stock_available ON ps_stock_available.id_product=ps_product.id_product'); try{ $query->execute(); while (($row = $query->fetch(PDO::FETCH_BOTH)) !== false) { if(!empty($row['REF']) && $row['BaseP'] == 0 && $id == 0){ $array[]=$row; } } echo 'array filled.<br />'; }catch(PDOException $e){ echo 'array error.<br />'; } foreach($array as $f=>$val){ foreach($array2 as $f1=>$val2){ if($val['REF'] == $val2['REF']){ unset($array[$f]); } } } $stock = array_merge($array,$array2); return $stock; } // load csv file into array Function readCSV($csvFile){ $array = $fields = array(); $i = 0; $handle = @fopen( $csvFile, "r"); if ($handle) { while (($row = fgetcsv($handle, 4096, ",")) !== false) { if (empty($fields)) { $fields = $row; continue; } foreach ($row as $k=>$value) { $array[$i][$fields[$k]] = $value; } $i++; } if (!feof($handle)) { echo "Error: unexpected fgets() fail\n"; } fclose($handle); } return $array; } //update prestashop stock based on product reference Function Update(){ $db = connect(); $csvFile = 'path to csv file'; $csv = readCSV($csvFile); $sp = shop_products(0); $plupdate=array(); $product = $db->prepare("UPDATE ps_product LEFT JOIN ps_stock_available on ps_stock_available.id_product=ps_product.id_product SET ps_stock_available.quantity = :avbl WHERE ps_product.reference = :indeks AND ps_stock_available.id_product_attribute = 0"); $combination = $db->prepare("UPDATE ps_product_attribute LEFT JOIN ps_stock_available on ps_stock_available.id_product_attribute=ps_product_attribute.id_product_attribute LEFT JOIN ps_product on ps_product_attribute.id_product=ps_product.id_product SET ps_stock_available.quantity = :avbl WHERE ps_product_attribute.reference = :indeks"); foreach($sp as $ps){ $indeks = $ps['REF']; $qty = $ps['QTY']; $ID = $ps['ID']; $base = $ps['BaseP']; if(!empty($indeks)){ foreach($csv as $stock){ $index = $stock['Reference']; $avbl = $stock['Quantity']; if($indeks == $index){ if($qty != $avbl){ if(isset($base)){ try{ $product->execute([':avbl'=>$avbl,':indeks'=>$indeks]); $log = $indeks.PHP_EOL. "simple product - quantity change from ".$qty." to ".$avbl.PHP_EOL. "-------------------------".PHP_EOL; file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND); }catch(PDOException $e){ $log = $indeks.PHP_EOL. "error ".$e->getMessage().PHP_EOL. "-------------------------".PHP_EOL; file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND); die(); } }else{ $plupdate[]=$ID; try{ $combination->execute([':avbl'=>$avbl,':indeks'=>$indeks]); $log = $indeks.PHP_EOL. "product combination - quantity change from ".$qty." to ".$avbl.PHP_EOL. "-------------------------".PHP_EOL; file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND); }catch(PDOException $e){ $log = $indeks.PHP_EOL. "error ".$e->getMessage().PHP_EOL. "-------------------------".PHP_EOL; file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND); die(); } } } } } } } return $plupdate; } //update product page quantity in backoffice Function updatelist(){ $db = connect(); $plupdate = Update(); $pltup = array_values(array_unique($plupdate)); $query = $db->prepare("UPDATE ps_stock_available SET ps_stock_available.quantity = :tmp WHERE ps_stock_available.id_product = :PID AND ps_stock_available.id_product_attribute = 0"); foreach($pltup as $PID){ $shopproducts = shop_products($PID); foreach($shopproducts as $sp){ $qty = $sp['QTY']; $tmp = $tmp+$qty; } try{ $query->execute([':tmp'=>$tmp,':PID'=>$PID]); }catch(PDOException $e){ echo "error ".$e->getMessage(); die(); } unset($tmp); } } Function oldfile(){ $files = glob('./log/*.log'); $now = time(); foreach ($files as $file) { if (is_file($file)) { if ($now - filemtime($file) >= 60 * 60 * 24 * 3) { // 2 days unlink($file); } } } } $log = "⇊ Update Starts ⇊ ".date("H:i:s").PHP_EOL; file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND); updatelist(); $log = "⇈ Update Finished ⇈ ".date("H:i:s").PHP_EOL; file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND); oldfile(); unset($database); exit("exit"); ?>