Jump to content
thirty bees forum

Update quantity via CSV


zimmer-media

Recommended Posts

Through my various dropshipping wholesalers, I've been using a separate php script for a few years now. I found it at that time after a long search in the PS forum.

With this script all quantities are emptied during the update and then re-imported. If, for example, items are not listed in the CSV file, they will automatically receive the amount zero (may be irritating if the sub item number variant is not correct).

It works for products with or without variants, if the article number (sub-article number of the variant is correct).

Upload the additional script (php) once into the main directory (eg httpdocs). Upload the current CSV file (semicolon separated) to the main directory before each update.

To update the quantity in the browser, enter the link and wait until it has run through. There is no extra notice after the update, only a white page and the favicon appears in the browser line.

The link to the update: http://yourwebsite.com/update.php

I myself have not tried to make any changes with the script, since it is sufficient for me so far.

Personal note: for the top line of the CSV file - column 1 = "reference", column 2 = "quantity". With the renaming of the php file and the CSV file I had no problems so far. It is important only when renaming the CSV file to change this in the php file ($remotecsvfile = 'update.csv'; //MY PATH; CHANGE TO YOUR NEED, SAME FOR THE FILE NAME).

update.php ``` <?php

// PRESTASHOP SETTINGS FILE require_once ('config/settings.inc.php');

// REMOTE CSV FILE (CUSTOMIZE YOURCSVFILEPATH, CAN BE AN URL OR A LOCAL PATH) $remotecsvfile = 'update.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=".DBNAME."", _DBUSER_, DBPASSWD); $db->setAttribute(PDO::ATTRERRMODE, PDO::ERRMODE_EXCEPTION);

settimelimit(600);

// RESET CYCLE

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

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

$updateAll = $db->prepare("update ".DBPREFIX."stockavailable SET quantity = :defaultqta"); $updateAll->execute(array(':defaultqta'=>$default_qta)); // END

// MAIN CYCLE $rownum = 0; if (($handle = fopen($remotecsvfile, "r")) !== false) { while (($data = fgetcsv($handle, 1000, ";")) !== false) { $rownum++; if ($rownum == 1) { // SKIP FIRST LINE (HEADER) continue; } if ($data[0] == '' || !isnumeric($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); } ?> ```

example update.csv reference;quantity 39034;43 39035;39 43591-52;4 43591-54;7 43591-56;4 43591-58;8 43591-60;6 43591-62;9 38490;38 37941;36 45399;17 45411;17 44998;16 34897;16 34896;9 40247;9 40717;9 43275;9 46685-50;2 47314;1 43100;1 23667;1 25992;0 48971-54;0 48971-56;0 48971-58;18 48971-60;0 48971-62;0 48971-64;0 48971-66;0 Unfortunately, I no longer have permission to upload files (except images). Hence these variants.

Link to comment
Share on other sites

@zimmer-media said in Update quantity via CSV:

With this script all quantities are emptied during the update and then re-imported. If, for example, items are not listed in the CSV file, they will automatically receive the amount zero (may be irritating if the sub item number variant is not correct).

in this way, if the script fails all the quantities are set to zero?! right?

Link to comment
Share on other sites

  • 3 weeks later...

Addendum, as it is already a kind of habit for me: For this script, the "maxexecutiontime" should be increased many times depending on the data volume of the lines etc in the php settings. In my Plesk servers, the default number is 30. To be on the safe side, I always entered 90,000 in advance, if it took a few minutes longer.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...