[Solved] Automatic Stock Update (Csv) Solution

thijsvk · October 20, 2015

Hi all,

like many of us here, I too need/want a solution to update stock levels automagically, rather than having to do it manually every day.

I came across this page: http://www.whiletrue.it/update-prestashop-product-quantities-csv-file/ and decided to give it a go (currently working on a new site) on my development machine, i.e. local installation.

I combined it with a cron job to make it run regularly, and it does work.

CSV file is fine and loads correctly and updates run (after adjusting the time limit because of earlier time out issues)

However, not all the values are updated correctly, specifically products with combinations.

Random quantities appear if the combination (reference based) is not present in the CSV or if the combination has 0 stock.

Or the total on the catalogue page (BO) shows far more than the actual quantities.

Unfortunately, I don't have the PHP knowledge to figure this out.

Does anyone have any idea what could be the cause and more importantly the solution to the issue?

I know there is a bug with regards to the quantities in the back office (you have to select 'I want to specify available quantities manually.' even though it is already selected, before PS actually 'applies' the quantities.

If 'we' are able to sort this out, it would mean a major leap in having automatic stock updates based on stock information from suppliers.

Attached are images of the CSV upload quantities, the quantities as in the catalogue page (BO) and the quantities in the product page (BO):

Edited December 22, 2015 by thijsvk (see edit history)

thijsvk · November 13, 2015

I hate to bump, but BUMP.

Does anyone have any ideas, thoughts or suggestions?

Our new site has gone from developing to production two days ago, and today I looked into this issue again.

The stock movements from our order system, are dumped into a csv file, this file is automatically uploaded to the website, and a cron job runs to update the stock levels on the site.

As mentioned previously, that all works flawlessly, the issue is still the random stock amounts.

The original source has some comments about issues with combinations, and that the quantities need to be set to zero first.

AT line 26

//START
$default_qta = 0;
$updateAll = $db->prepare(“UPDATE “._DB_PREFIX_.”product SET quantity = :default_qta”);
$updateAll->execute(array(‘:default_qta’=>$default_qta));

$updateAll = $db->prepare(“UPDATE “._DB_PREFIX_.”product_attribute SET quantity = :default_qta”);
$updateAll->execute(array(‘:default_qta’=>$default_qta));

$updateAll = $db->prepare(“UPDATE “._DB_PREFIX_.”stock_available SET quantity = :default_qta”);
$updateAll->execute(array(‘:default_qta’=>$default_qta));
// END

That code has not worked for me, yet, I'm now considering setting up a separate cron job to reset the quantities to zero (with the above code) and then have the original cron run to update the stock levels.

If anyone has any suggestions while I'm testing, please don't hold back

thijsvk · November 16, 2015

SORTED!!!!

The problem was that the reset to zero code didn't execute properly / at all.

Whether this had to do with the location within the rest of the code, I have no idea.

This resulted in the quantities in the csv file being added to the quantities present in the DB, rather than overwriting them (only for product combination quantities)

So after messing with it for several hours, I managed to correct the code, well to execute as intended with the desired result.

Not by writing code or anything, don't have the knowledge (not enough time to learn really, and yes, it is rather essential, but tell it to my boss).

Below is the entire cron job php code, for use with a csv file consisting of two columns: 'reference' and 'quantity'.

The code resets all the quantities in the DB to zero first, and then updates the DB with the quantities from the DB.

Of course, the csv needs to be uploaded to the defined location, we use Auto FTP Manager for that.

So, if you get a daily csv file with product quantities from your supplier, this would be the way to have 'live' stock information on your site (depending on the frequency with which you receive the information from your supplier(s)).

<?php
 
// PRESTASHOP SETTINGS FILE
require_once ('../config/settings.inc.php');
 
// REMOTE CSV FILE (CUSTOMIZE YOURCSVFILEPATH, CAN BE AN URL OR A LOCAL PATH)
$remote_csv_file = '../upload/stock/XXXXXX.csv'; //MY PATH; CHANGE TO YOUR NEED, SAME FOR THE FILE NAME
 
// DB CONNECTION (CUSTOMIZE YOURDBHOSTNAME AND YOURDBPORT)
$db = new PDO("mysql:host=localhost;port=3306;dbname="._DB_NAME_."", _DB_USER_, _DB_PASSWD_);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

set_time_limit(600);
 
// RESET CYCLE

//START
$default_qta = 0;
$updateAll = $db->prepare("update "._DB_PREFIX_."product SET quantity = :default_qta");
$updateAll->execute(array(':default_qta'=>$default_qta));

$updateAll = $db->prepare("update "._DB_PREFIX_."product_attribute SET quantity = :default_qta");
$updateAll->execute(array(':default_qta'=>$default_qta));

$updateAll = $db->prepare("update "._DB_PREFIX_."stock_available SET quantity = :default_qta");
$updateAll->execute(array(':default_qta'=>$default_qta));
// END
 
// MAIN CYCLE
$row_num = 0;
if (($handle = fopen($remote_csv_file, "r")) !== false) {
  while (($data = fgetcsv($handle, 1000, ",")) !== false) {
    $row_num++;
    if ($row_num == 1) {
      // SKIP FIRST LINE (HEADER)
      continue;
    }
    if ($data[0] == '' || !is_numeric($data[1])) {
      // SKIP EMPTY VALUES
      continue;
	}
    // INPUT SANITIZATION
	//$reference =':reference';
    //$quantity  =':quantity';
	$reference = trim($data[0]);
    $quantity  = ($data[1] >= 0) ? $data[1] : 0;
 
    try {
      $res4 = $db->prepare("SELECT id_product, id_product_attribute from "._DB_PREFIX_."product_attribute WHERE reference = :reference");
      $res4->execute(array(':reference'=>$reference));
 
      if ($res4->rowCount() > 0) {
 
        // IT'S A PRODUCT COMBINATION
        
        $row4 = $res4->fetch();
    
        $res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = :q where id_product_attribute = :id_product_attribute");
        $res->execute(array(':q'=>$quantity, ':id_product_attribute'=>$row4['id_product_attribute']));
            
        $res = $db->prepare("update "._DB_PREFIX_."product_attribute set quantity = :q where id_product_attribute = :id_product_attribute");
        $res->execute(array(':q'=>$quantity, ':id_product_attribute'=>$row4['id_product_attribute']));
 
        $res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = quantity + :q where id_product = :id_product and id_product_attribute = 0");
        $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
        
        $res = $db->prepare("update "._DB_PREFIX_."product set quantity = quantity + :q where id_product = :id_product");
        $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
 
      } else {
 
        // IT'S A SIMPLE PRODUCT
        
        $res4 = $db->prepare("SELECT id_product from "._DB_PREFIX_."product WHERE reference = :reference");
        $res4->execute(array(':reference'=>$reference));
        if ($res4->rowCount() > 0) {
          $row4 = $res4->fetch();
    
          $res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = :q where id_product = :id_product and id_product_attribute = 0");
          $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
 
          $res = $db->prepare("update "._DB_PREFIX_."product set quantity = :q where id_product = :id_product");
          $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
        }
 
      }
    } catch (PDOException $e) {
      echo 'Sql Error: '. $e->getMessage() .'<br /><br />';
    }
  }
  fclose($handle);
}
?>

It's impossible for me to take real credit for this, since I can't code, if it hadn't been for the original solution and comments here I would still be stuck

Edited November 16, 2015 by thijsvk (see edit history)

TACS · December 18, 2015