Edit History

On 3/20/2019 at 4:48 PM, WisQQ said:

I'd like to share small thing with you. Its CSV stock update based on reference. It builds 2 arrays, one with values from CSV file and the other with values from database. Then loop compares reference's from csv with reference from DB. After that it either update quantity or just go to next reference if quantity is the same in both arrays. You can easly run it with cron and it works on ps 1.7.5. Also it creates logs with products that were updated.

I'm not good with php so feel free to improve it and share with others. 

updater.php

<?php
$database = require_once('config/configup.php');

$con = mysqli_connect($database['database_host'],$database['database_user'],$database['database_password'],$database['database_name']);


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;
}
 


$query = mysqli_query($con,"select ps_product_attribute.reference,ps_stock_available.quantity from ps_product_attribute LEFT JOIN ps_stock_available on ps_stock_available.id_product_attribute=ps_product_attribute.id_product_attribute");

// set array
$indeks = array();

while($row = mysqli_fetch_assoc($query)){
  $indeks[] = $row;

}


$csvFile = 'path to csv file';
$csv = readCSV($csvFile);
$c = count ($csv);
$z = count ($indeks);
	for($x = 0; $x <= $c; $x++){
	if(!empty($csv[$x]['Reference']) && !empty($csv[$x]['Quantity']) || $csv[$x]['Quantity'] !==0 ){
				for($i = 0; $i <= $z-1; $i++){
					if($csv[$x]['Reference'] == $indeks[$i]['reference']){
						if($csv[$x]['Quantity'] !== $indeks[$i]['quantity']){
							$link = mysqli_real_escape_string($con, $csv[$x]['Reference']);
							$qty = mysqli_real_escape_string($con, ($csv[$x]['Quantity']));
							$query = "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 = '".$qty."' WHERE ps_product_attribute.reference = '".$link."'";
							mysqli_query($con, $query) or die('Error querying database.');
							$log  = $csv[$x]['Reference'].PHP_EOL.
									"quantity changed from ".$indeks[$i]['quantity']." to ".$csv[$x]['Quantity'].PHP_EOL.
									"-------------------------".PHP_EOL;
							file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND);
						}
					}
				}
			
		}
			usleep(0.1);
	}



mysqli_close($con);

/*

update ps_product left join ps_stock_available on ps_product.id_product = ps_stock_available.id_product set ps_stock_available.quantity = 12 WHERE ps_product.ean13 = '5901721716223'

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 = 3 WHERE ps_product.reference='75E6-1540A36' AND ps_product_attribute.reference='75E6-1540A38';


"update ps_product_shop left join ps_product on ps_product_shop.id_product=ps_product.id_product SET ps_product_shop.price = '".mysql_real_escape_string($url)."', ps_product.price = ps_product_shop.price WHERE ps_product.ean13 = '".$ean13."'";

 
select ps_product.reference,ps_,ps_stock_available.quantity from ps_product left join ps_product_shop on ps_product.id_product=ps_product_shop.id_product LEFT JOIN ps_stock_available on ps_product.id_product=ps_stock_available.id_product*/
/*select ps_product_attribute.id_product_attribute,ps_product_attribute.reference,ps_product_attribute.default_on,ps_product.reference,ps_product.id_product,ps_stock_available.id_product,ps_stock_available.quantity from 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*/



/*select ps_product_attribute.reference,ps_stock_available.quantity from 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;
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 = 3 WHERE ps_product_attribute.reference='4691-00_01';*/
?>

config.php

<?php return array(
    'database_host' => 'dbhost',
    'database_name' => 'dbname',
    'database_user' => 'dbuser',
    'database_password' => 'dbpw',
);
?>

.htaccess for config folder

# Apache 2.2
<IfModule !mod_authz_core.c>
    Order deny,allow
    Deny from all
</IfModule>

# Apache 2.4
<IfModule mod_authz_core.c>
    Require all denied
</IfModule>

 

 

updateCSV.zip

Could you share CSV sample? Also that I would want to ask how to export my products + QTY in CSV file.