Jump to content

[Solved] Automatic Stock Update (Csv) Solution


thijsvk

Recommended Posts

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):

 

post-711849-0-80324200-1445354571_thumb.jpg 

 

post-711849-0-45828500-1445354802_thumb.jpg

 

post-711849-0-84616500-1445354601_thumb.jpg

Edited by thijsvk (see edit history)
Link to comment
Share on other sites

  • 4 weeks later...

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 :)

Link to comment
Share on other sites

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 by thijsvk (see edit history)
Link to comment
Share on other sites

  • 1 month later...

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 :)

 

Hello thisjsvk thank you for this contribution, it is something that I have been looking for, could you possibly guide me with what I need to do in order to make this work?  :)

  • Like 2
Link to comment
Share on other sites

Hey TACS,

 

it's not that hard really, just follow these steps:

 

Prepare your csv upload, if you are lucky, then you can just use the information as provided by your supplier, otherwise you will need do some more work.

Assuming your supplier does not send you a csv file with just 'product_reference' and 'quantity', you will need to create an excel file with a macro that will extract that information and copy it to a new file, and save that file as a csv.

 

Basically, open a new excel file > record macro > while recording, open the supplier file, select the columns you need, copy them, open a new excel file, paste the columns, save as something like 'stockinfo.csv' in a location of your choice > go back to the first excel file and click 'stop recording to finish the macro. Save this file as stockupdatemacro.xls(x).

Add a job to Windows System Scheduler to run that macro file as often as you need, to regenerate the CSV file.

 

You will need a program like AutoFTP to automatically (scheduled) upload the CSV file to your webhost, in a location of your choice.

Copy the php cron job above and save it as something like cronstockupdate.php and upload this to a location of your choice on your webhost (remember where you upload the files). The cron task manager doesn't really work, so your best bet is to use a site like easycron.com and set it to run the cronjob that you created (you need the location of the cron job for this) and then set it to run as often as needed.

 

And that's it really.

If you are able to download the supplier stock info through ftp, you can automate that as well, so it downloads it when you tell it to, and then have the macro job run a few minutes afterwards, followed by the AutoFTP upload a few minutes after that, and then the cronjob a few minutes after that.

  • Like 1
Link to comment
Share on other sites

Hey TACS,

 

it's not that hard really, just follow these steps:

 

Prepare your csv upload, if you are lucky, then you can just use the information as provided by your supplier, otherwise you will need do some more work.

Assuming your supplier does not send you a csv file with just 'product_reference' and 'quantity', you will need to create an excel file with a macro that will extract that information and copy it to a new file, and save that file as a csv.

 

Basically, open a new excel file > record macro > while recording, open the supplier file, select the columns you need, copy them, open a new excel file, paste the columns, save as something like 'stockinfo.csv' in a location of your choice > go back to the first excel file and click 'stop recording to finish the macro. Save this file as stockupdatemacro.xls(x).

Add a job to Windows System Scheduler to run that macro file as often as you need, to regenerate the CSV file.

 

You will need a program like AutoFTP to automatically (scheduled) upload the CSV file to your webhost, in a location of your choice.

Copy the php cron job above and save it as something like cronstockupdate.php and upload this to a location of your choice on your webhost (remember where you upload the files). The cron task manager doesn't really work, so your best bet is to use a site like easycron.com and set it to run the cronjob that you created (you need the location of the cron job for this) and then set it to run as often as needed.

 

And that's it really.

If you are able to download the supplier stock info through ftp, you can automate that as well, so it downloads it when you tell it to, and then have the macro job run a few minutes afterwards, followed by the AutoFTP upload a few minutes after that, and then the cronjob a few minutes after that.

 

Thanks for the top notch advice  :)

 

Ive just found a program called notepad ++ its useful because it links via ftp to my server, so i can in theory open csv, save and it will write to the file on the server. it does have a macro function too.

 

I would like to ask too, what happens say, if there are more fields in the place you're extracting from than there is where you're extracting too, what will prestashop do about that?

 

so for e.g a CSV containing 200 products on a remote server, website only has 190 products, will it just disregard the non matches when syncing (assuming it syncs with the reference fields) or does it write these to the websites DB?

 

Thanks in advance for your help

Link to comment
Share on other sites

Hi TACS,

 

thanks for the comments, I know, Notepad++ is awesome, it never occurred to me to use it for this exercise though, I'll have to look into that.

with regards to the excess products, If I remember correctly, it will add them, but they won't show up on the front end.

However, I prefer to keep the DB clean.

For me, the simplest solution is to have all your products in an excel sheet (product column and quantity column, in the same workbook, start another sheet, and import the csv file from your supplier in that sheet.

Using Vlookup, you can then have the first sheet check the available quantities for just your products. When you create the (new) macro template, you can record it to update the quantities, and then export the first sheet as the stock csv which will be uploaded.

Link to comment
Share on other sites

Hi ken-bcn,

 

you have a few options if I understand you correctly.

Correct me if I'm wrong, but you have different suppliers, and you don't want the quantities to be set to zero.

 

Well, if the script works for you as is, without having to reset to zero, then you can just comment it out, so it doesn't run, just put // in front of each line from '//RESET CYCLE' to '//END'.

Do you have stock information from all of your suppliers?

If so, you can just use the solution above.

Make a master excel stock file, put all the products from all of your suppliers in the first sheet, and then create separate sheets for each of your suppliers and populate them using the import function.

Create/record a macro that updates all the different sheets and again with Vlookup, the quantities on the first sheet will automatically update as well. If you want you can put a button on that first sheet so that if you click it, it will call the macro and run the updates. You can then export the first sheet using the stockupdatemacro.xls (make sure you incorporate running the update macro in your stock workbook.

That should do it, everything will run as it should in one go.

 

Alternatively, you can take the original process and use it to set up individual cronjobs for each of your suppliers.

Each cronjob will then update an individual supplier and its products.

 

Good luck!!!

  • Like 1
Link to comment
Share on other sites

Hi thijsvk

 

thanks for the great advice, although it sounds a little bit complex lol, unfortunately I don't have excel on the machine where the records are held and ideally want to keep it simple, I'm not actually planning on using the script to import suppliers data, but to sync a seperate stock file to keep more accurate records.

 

the stock file has around 1200 records, whereas the website only has around half of that figure.

 

I'm curious now that you've said it will write the extra fields, if those fields would appear in PS BO? basically we have a large number of products yet to add to the website, but they will eventually be added at a later date.

Link to comment
Share on other sites

Hi TACS,

 

thanks for the comments, I know, Notepad++ is awesome, it never occurred to me to use it for this exercise though, I'll have to look into that.

with regards to the excess products, If I remember correctly, it will add them, but they won't show up on the front end.

However, I prefer to keep the DB clean.

For me, the simplest solution is to have all your products in an excel sheet (product column and quantity column, in the same workbook, start another sheet, and import the csv file from your supplier in that sheet.

Using Vlookup, you can then have the first sheet check the available quantities for just your products. When you create the (new) macro template, you can record it to update the quantities, and then export the first sheet as the stock csv which will be uploaded.

 

Hi thijsvk, I have attempted to use this script, it has set all the websites stock quantities back to zero, but it isnt then updating the stock from the CSV file, any ideas whats going wrong?..

Link to comment
Share on other sites

Hi thijsvk, I have attempted to use this script, it has set all the websites stock quantities back to zero, but it isnt then updating the stock from the CSV file, any ideas whats going wrong?..

 

solved for anyone who may have the same issue, the tab delimiter is set on line 32 

 

while (($data = fgetcsv($handle, 1000, ",")) !== false) {

 

for example

change "," (comma)

to ";" (semicolon)

Link to comment
Share on other sites

Hi all,

 

sorry for the delay in replying, holidays and all that. Happy Holidays!!!

Only just got back to the office, and really shouldn't be here, damn holiday flu.

Glad everything is working out for you TACS, in my experience it usually is a bit of a pain to figure out and adjust someone else's code, or that might just be me.

Yes, I use comma separation, but you can use any delimiter really, as long as you change it in line 32.

 

thrillmetoo I will upload the macro files as soon as I have time to sanitize them, I have some end of year dead lines, so it will probably be next week before I get around to it.

 

Again, I can't really take credit for this, other than finding it and sharing it here, but it is nice to see it being beneficial to others :)

Link to comment
Share on other sites

  • 2 months later...

Hi all.
I’m new with all this php staff.

Fist of all, I want to thank you for this script.
I have a CSV file with “reference number” in column 8, and “quantity” in column 11.
Can you show me the way, where I need to make a changes.
Thank you for your good job.

Link to comment
Share on other sites

  • 1 month later...

I have to say thank you verry much too. :) It saves a lot of time. 

 

@Ramagrey:

You only need to change the 'data-arrays' matching to your columns. In Line 39 chnage 'data[0]' to 'data[7]' and 'data[1]' to 'data[10]'

if ($data[7] == '' || !is_numeric($data[10])) {
      // SKIP EMPTY VALUES
      continue;
	}

Than in line 46 you have to change the array the same as above.

	$reference = trim($data[7]);
    $quantity  = ($data[10] >= 0) ? $data[10] : 0;

PHP reads every column as data-array, starting with data[0] for the first column. The second column of your CSV is data[1and so on.

Edited by MarcoSch (see edit history)
Link to comment
Share on other sites

  • 4 months later...
  • 4 months later...

Hi,

 

I'm trying to update the stock in a bookshop.

 

First in a local installation: 

  • created a new folder
  • placed the script and the csv (increased quantity in a given reference to 600 units to check) file into the folder
  • url pasted in web browser and nothing happens, even with an "echo" in the first line in the php file.

routes:

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


// REMOTE CSV FILE (CUSTOMIZE YOURCSVFILEPATH, CAN BE AN URL OR A LOCAL PATH)
$remote_csv_file = 'uno.csv';

Any suggestion to fix it?

 

 

post-1084449-0-67233300-1486560329_thumb.jpg

Link to comment
Share on other sites

Have you tested it with the path set in the script?

$remote_csv_file = './scriptsActualizacion/uno.csv';

In theory you shouldn't have to if they're in the same location, but theory...

 

It doesn't work. 

 

I think I'm gonna try on my hosting with a CSV file to update the stock for only one product.

I'll keep you posted.

Thanks! 

 

Update: both files uploaded to the server, no results at all.

Edited by lupradoa (see edit history)
Link to comment
Share on other sites

Are the products set to have the quantities entered manually?

Also, what are the chmod settings on the files?

 

It could be that they don't have the right permissions to be executed

Yes, the stock is managed manually. Permissions are ok

 

I'll give a try this afternoon again.

 

Thanks for your suggestions.

 

 

It's funny the php "echo" doesn't work on either host. 

Link to comment
Share on other sites

  • 5 months later...

The script is properly updating database tables (product & stock_available), but frontend is not updated, thas is:

I search for an updated product and stock is still 0, so it doesn't appear. Searching for the same product at backend, it properly reflects the updated stock (3,5,...)

Only when manually update stock again at backend, frontend reflects the changes.

I've tried changing cache type, clearing cache, disabling cache... None is working.

Prestashop version is 1.6.1.12.

This is a complete headache for me, specially because it doesn't seem to be a cache problem, and database updates are ok.

Any ideas?.

Thx

Edited by john_m (see edit history)
Link to comment
Share on other sites

  • 2 weeks later...

Hi - I've used this to test update of stock in 1.6.1.11 Prestashop

 

Works well but with an issue!

 

When going back and editing a product opening associations the categories are all blank:

 

On Saving 2 errors:

2 errors 

  1. Products must be in at least one category.
  2. This product must be in the default category.

Once saved the categories appear back!

 

Anyone know how to solve this???

 

I presume it's here in controllers/admin/AdminImportController.php - but not sure how to solve it:

 

 // Will update default category if there is none set here. Home if no category at all.
            if (!isset($product->id_category_default) || !$product->id_category_default) {
                // this if will avoid ereasing default category if category column is not present in the CSV file (or ignored)
                if (isset($product->id_category[0])) {
                    $product->id_category_default = (int)$product->id_category[0];
                } else {
                    $defaultProductShop = new Shop($product->id_shop_default);
                    $product->id_category_default = Category::getRootCategory(null, Validate::isLoadedObject($defaultProductShop)?$defaultProductShop:null)->id;
                }
            }
Link to comment
Share on other sites

  • 11 months later...
  • 2 weeks later...
  • 4 weeks later...

PS 1.7.x

I changed 2 lines to connect to the database - but it does not work

require_once ('app/config/parameters.php');

and

$db = new PDO("mysql:host=localhost;port=3306;dbname=database_name", database_user, database_password);

 

does anyone have any other ideas to make it work with ps 1.7?

Link to comment
Share on other sites

  • 3 weeks later...

For me it works too in 1.6x and 1.7x.

 

I have adapted the above script to delete combinations that no longer availible. It works for me on my 1.6.1.17 productiv shop after i testet the script on a cloned shop. So i will post the code, maybe some one can use them.

But use them carefully and on your own risk because i'm not responsible for any damage. If you not sure, test it with a temporary clone of your DB.

 

config.php is the settings.inc.php. Since i have moved the script in a separate folder i have also copied the settings there to run the script. So you can also use your setting.inc.php form prestashop

The script checks only the reference of the deleted combinations. In my case it's in my CSV data[0]

So if your references isn't in the first columne you have to change ist (it begins always by data[0], the second is data[1] .... and so on)

After running the script i used prestashop cleaner (free module at Prestashop Addons)to check integrity and clean the database.

 

<?php
error_reporting(E_ALL); 

// PRESTASHOP SETTINGS FILE
require_once ('configs.php');
 
// REMOTE CSV FILE (CUSTOMIZE YOURCSVFILEPATH, CAN BE AN URL OR A LOCAL PATH)
$remote_csv_file = 'deleted_products.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);
 
 
// MAIN CYCLE
$row_num = 0;
if (($handle = fopen($remote_csv_file, "r")) !== false) {
  while (($data = fgetcsv($handle, 60000, ";")) !== false) {
    $row_num++;
    if ($row_num == 1) {
      // SKIP FIRST LINE (HEADER)
      continue;
    }
    if ($data[0] == '') {
      // SKIP EMPTY VALUES
      continue;
	}
    
	$reference = trim($data[0]);

 
    try {
      $res4 = $db->prepare("DELETE FROM "._DB_PREFIX_."product_attribute WHERE reference = :reference");
      $res4->execute(array(':reference'=>$reference));
 
    
    } catch (PDOException $e) {
      echo 'Sql Error: '. $e->getMessage() .'<br /><br />';
    }
  }
  fclose($handle);
}
?>

 

I also tried to delete single products, what should work the same way.

But in my case unfortunately some Main Products have the same reference as a combination of them.

By example 

Main Product is 12345-S

And combinations are

12345-S = Small

12345-M = Medium

12345-L = Large

12345-XL = XL

So if i use the above script (adapted to ps_product) for 'single products' it will also delete the main product which has combination and the whole product is gone.

I didn't figure out yet how i can tell the script that it only delete products that has no combination.

Maybe some one has a clue to handle that. :)

 

 

 

Edited by MarcoSch (see edit history)
Link to comment
Share on other sites

  • 4 months later...
On 8/27/2018 at 2:31 PM, zimmer-media said:

On the same server with a PS 1.6. Version it works without any prolems.
Only with PS 1.7 does it appear, despite the changes, not to connect to the database.

I do not know if the changes are correct. My knowledge with php and myswql is not so good.

Paste this and show errors:

error_reporting(E_ALL);
ini_set('display_errors', true);

 

On PS 1.7 works but I must writed settings to database manualy:

$database_host = 'localhost';
$database_port = '';
$database_name = 'XXXX';
$database_user = 'XXXXXXX';
$database_password = 'XXXXXXX';

and

$db = new PDO('mysql:host=localhost;dbname=XXXX', $database_user , $database_password);

 

Edited by Miroo (see edit history)
Link to comment
Share on other sites

  • 4 weeks later...

I use Prestashop 1.7.5.0

now on my import.php (with 755 permission) I've:

// PRESTASHOP SETTINGS FILE
require_once ('app/config/parameters.php');

// REMOTE CSV FILE (CUSTOMIZE YOURCSVFILEPATH, CAN BE AN URL OR A LOCAL PATH)
$remote_csv_file = 'import.csv';

// DB CONNECTION (CUSTOMIZE YOURDBHOSTNAME AND YOURDBPORT)
//$db = new PDO("mysql:host=localhost;port=3306;dbname="._DB_NAME_."", _DB_USER_, _DB_PASSWD_);
$db = new PDO('mysql:host=localhost;dbname=prestashop', $database_user , $database_password);

but always error 500

test also commented require_once ('config/settings.inc.php'); and add

$database_host = 'localhost';
$database_port = '';
$database_name = 'XXXX';
$database_user = 'XXXXXXX';
$database_password = 'XXXXXXX';

always error 500

Edited by centoasa (see edit history)
Link to comment
Share on other sites

  • 1 month later...

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

Edited by WisQQ (see edit history)
Link to comment
Share on other sites

  • 7 months later...
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.

Edited by tapukatata (see edit history)
Link to comment
Share on other sites

  • 3 weeks later...

Excellent work thijsvk - saved me some time

Only thing I recommend is moving all prepare statements to before processing any data i.e not in any loops - once they are prepared they are persistent so you can keep calling them.

If you have a large import this will speed it up enormously

 

Tx

Link to comment
Share on other sites

3 hours ago, fritzelly said:

Excellent work thijsvk - saved me some time

Only thing I recommend is moving all prepare statements to before processing any data i.e not in any loops - once they are prepared they are persistent so you can keep calling them.

If you have a large import this will speed it up enormously

 

Tx

I know, i switched to prepared statement after i made this work. I use it to  call multiple api's to update stock. Works smoothly even for big database.

Link to comment
Share on other sites

Only issue I've found with the code is it doesn't update the quantity for products with attributes in 1.7 (made there was a DB change from 1.6), simple products are working fine but if your csv file contains something like 

some_reference_number, some_attribute, quantity
12345, Red, 10    
12345, Blue, 5
12345, White, 2    

Then it doesn't work, currently writing some code to pull the attribute values to know what to update

Link to comment
Share on other sites

Its based on reference, so you need to have attribute reference set in product attributes. 

I'm using this, should help you.

$query= $db2->prepare("Select 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");

And here is update.

$query = $db2->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 = '".$newqty."' WHERE ps_product_attribute.reference = '".$indeks."'");

 

Edited by WisQQ (see edit history)
Link to comment
Share on other sites

2 minutes ago, fritzelly said:

Attribute names are stored in attribute_lang - unless you know and set the id of each attribute it's cumbersome

If you dont have reference set in each product attribute, then yes. You need to work around. It all depends how your products are set.

Link to comment
Share on other sites

1 minute ago, WisQQ said:

If you dont have reference set in each product attribute, then yes. You need to work around. It all depends how your products are set.

I'm not following you. The only table that references the attribute name is attribute_lang

You don't call that table in your SQL so how can the code know what attribute value's stock to update?

Link to comment
Share on other sites

9 hours ago, fritzelly said:

I'm not following you. The only table that references the attribute name is attribute_lang

You don't call that table in your SQL so how can the code know what attribute value's stock to update?

Using Join in query you get value from tables. Attribute stock is updated based on attribute reference set in product combination. Example:
Product have attributes like this.

Size:
S,M,L
Color:
White,Red,Blue

Combinations will be like:

S White, S Red, S Blue
M White, M Red, M Blue
L White, L Red, L Blue

Each of these combinations is separate value in data base, so you assign unique reference. 
S White, reference: S-White-tshirt
M Blue, reference: M-Blue-tshirt
Doesnt matter how you set your reference could be like the above or with spaces. Then you can update stock based on this with query, i posted earlier.
 

Edited by WisQQ (see edit history)
Link to comment
Share on other sites

Can you post an example of your product and combination csv?

I'm failing to see how you can have individual references for each combination and still all linked to the main product. When you import combinations it uses the reference to link the combinations to the product, so if they're all different how are they tied to the product.

Link to comment
Share on other sites

2 minutes ago, fritzelly said:

Can you post an example of your product and combination csv?

I'm failing to see how you can have individual references for each combination and still all linked to the main product. When you import combinations it uses the reference to link the combinations to the product, so if they're all different how are they tied to the product.

When you import combinations it links them using product ID, not reference.  I'll share example csv later.

Link to comment
Share on other sites

22 minutes ago, fritzelly said:

Ahh, but your stock file, from say a supplier, won't have a product ID - that's internal to PS

Yes, but i was talking about creating combinations for products with import builded in ps. Update is based on reference, which is provided in csv. So all i need from my supplier are 2 things: Quantity and Combination reference.

Link to comment
Share on other sites

16 minutes ago, fritzelly said:

Unfortunately one of my suppliers only gives a reference for the product and not the combinations

Can you share file provided by your supplier. Maybe i could help you with it. Try to modify product reference and use it in combinations just for your side of stock update. For example, if product reference is "tshirt_bear" change it just for combinations by adding value like size: "tshirt_bear_XL".

After that u will be able to update your data base easier. Then you need to figure out how to read file from your supplier so you can update your stock.

Link to comment
Share on other sites

Unless I'm missing something there is no way to specify different reference numbers for a product unless you have the product ID to start with 

I could specify the ID manually when adding products but that will still require cross referencing the the stock reference with the product details to get the product ID

I'm just finishing up the code to search by reference/product number based on the product reference

Link to comment
Share on other sites

  • 1 month later...
On 11/13/2019 at 9:11 PM, fritzelly said:

Excellent work thijsvk - saved me some time

Only thing I recommend is moving all prepare statements to before processing any data i.e not in any loops - once they are prepared they are persistent so you can keep calling them.

If you have a large import this will speed it up enormously

 

Tx

I'm a user of this script and it works fine. But it takes about 4 minutes to run. As the number of products is still growing, I'd like to know how I should change the script as you suggested. Where should I move the prepare statements?

Many thanks in advance.

Link to comment
Share on other sites

  • 3 weeks later...

Hi all,

I can't seem to get this working, what am I doing wrong. I'm, on 1.7 and gettnig this in my error log:

PHP Warning:  mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /public_html/updater.php on line 34
Line 34: while($row = mysqli_fetch_assoc($query)){

and

PHP Notice:  Undefined offset: 4 in /public_html/updater.php on line 45

Line 45:     if(!empty($csv[$x]['Reference']) && !empty($csv[$x]['Quantity']) || $csv[$x]['Quantity'] !==0 ){

Thanks

Link to comment
Share on other sites

3 hours ago, djinni said:

Hi all,

I can't seem to get this working, what am I doing wrong. I'm, on 1.7 and gettnig this in my error log:

PHP Warning:  mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /public_html/updater.php on line 34
Line 34: while($row = mysqli_fetch_assoc($query)){

and

PHP Notice:  Undefined offset: 4 in /public_html/updater.php on line 45

Line 45:     if(!empty($csv[$x]['Reference']) && !empty($csv[$x]['Quantity']) || $csv[$x]['Quantity'] !==0 ){

Thanks

Try this. Switched to PDO, also what query do you use to get stock from ypour shop. Here you update only products with combinations. If you want to do simple products and products with combinations you need to modify it.
 

<?php
set_time_limit(900);
#version 2
$database = require_once('config/configup.php');
// Create connection
function connect(){
	global $database;
	try{
		$db = new PDO('mysql:host='.$database['database_host'].';dbname='.$database['database_name'], $database['database_user'], $database['database_password']);
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		echo 'connected!.<br />';
	}catch(PDOException $e){
		echo 'connection failed.<br />';
	}
	return $db;
}
Function shop_products(){
	$db = connect();
	$array=array();
	$query= $db->prepare('select 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_BOTH)) !== false) {
			if(!empty($row['REF']) && $row['manuid'] == 6){
				$array[]=$row;
			}		
		}
		echo 'array filled.<br />';
	}catch(PDOException $e){
		echo 'array error.<br />';
	}
	return $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;
}


Function Update(){
	$db = connect();
	$csvFile = 'path to csv file';
	$csv = readCSV($csvFile);
	$sp = shop_products();
	$query = $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 $s){
		$indeks = $s['REF'];
		$qty = $s['QTY'];
		foreach($csv as $stock){
			$index = $stock['Reference'];
			$avbl = $stock['Quantity'];
			if($indeks == $index){
				if($qty != $avbl){
					try{
						$query->execute();
						$log  = $indeks.PHP_EOL.
						"quantity changed 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();
					}
				}
			}
		}		
	}
}


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);
Update();
$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");
?>

 

Edited by WisQQ (see edit history)
Link to comment
Share on other sites

Thank you. I gave it a try and get this:
connected!.
connected!.
array error.
exit

I Just have a simple test csv, with reference & quantity.

I would like to get both simple and combinations working to adjust quantities, but I'm still learning, so one step at a time.

Link to comment
Share on other sites

6 hours ago, djinni said:

Thank you. I gave it a try and get this:
connected!.
connected!.
array error.
exit

I Just have a simple test csv, with reference & quantity.

I would like to get both simple and combinations working to adjust quantities, but I'm still learning, so one step at a time.

Ah sorry, i gave you wrong query :)

17 hours ago, WisQQ said:

Function shop_products(){ $db = connect(); $array=array(); $query= $db->prepare('select 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_BOTH)) !== false) { if(!empty($row['REF']) && $row['manuid'] == 6){ $array[]=$row; } } echo 'array filled.<br />'; }catch(PDOException $e){ echo 'array error.<br />'; } return $array; }

change this to

Function shop_products(){
	$db = connect();	
	$array=array();
	$array2=array();
	$query= $db->prepare('select 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_BOTH)) !== false) {
			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){
					$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;
}

this function gets all products and product attributes with reference from your shop.
BaseP is base product. In my case some products have same reference for base product and the default combination. So after it gets all products it removes duplicates(BaseP and leaves combination) and merge arrays. 
 

The update function to update simple and products with combination looks like this.

Function Update(){
	$db = connect();
	$csvFile = 'path to csv file';
	$csv = readCSV($csvFile);
	$sp = shop_products();
	foreach($sp as $ps){
		$indeks = $ps['REF'];
		$qty = $ps['QTY'];
		$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{
								$query = $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");
								$query->execute();
								$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{
							try{
								$query = $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."'");
								$query->execute();
								$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();
							}							
						}
					}
				}
			}
		}	
	}
}

 

Edited by WisQQ (see edit history)
  • Thanks 1
Link to comment
Share on other sites

14 hours ago, djinni said:

Thanks for your help, really appreciate it.

I've run the new one and am currently getting:

connected!.
connected!.
array error.
array error.
exit

Just trying to figure it out 

Change echo 'array error.<br />'; with echo $e->getMessage();

echo $e->getMessage();

and post what error you get. There is some issue with querying db.

Link to comment
Share on other sites

  • 3 weeks later...

hi everybody,

I am trying to use this in 1.7.6

Just a few questions:

- how to set some password to execute this script? (I understand, if someone knows url to execute this script from server could manipulate it)

- how about cron jobs in linux system?

I am newbie coding, and I have not huge experience with SQL.

Thanks a lot

Link to comment
Share on other sites

On 2/24/2020 at 8:49 AM, Calleja said:

hi everybody,

I am trying to use this in 1.7.6

Just a few questions:

- how to set some password to execute this script? (I understand, if someone knows url to execute this script from server could manipulate it)

- how about cron jobs in linux system?

I am newbie coding, and I have not huge experience with SQL.

Thanks a lot

I'm using it with cron jobs myself. You can set password if you want to but if you gonna use cron then simply put it above public_html on your server.
As for manipulation thats why you have prepared statements to prevent such things.

Link to comment
Share on other sites

18 hours ago, WisQQ said:

I'm using it with cron jobs myself. You can set password if you want to but if you gonna use cron then simply put it above public_html on your server.
As for manipulation thats why you have prepared statements to prevent such things.

thank you, I need to read about that, do you have any documentation or tutorial for begginers?

Link to comment
Share on other sites

1 hour ago, Calleja said:

thank you, I need to read about that, do you have any documentation or tutorial for begginers?

Hi Calleja, this is a tutorial fro cronjobs https://www.hostinger.com/tutorials/cron-job

Any way you have to think that every hosting company have there "own way" to do this. In my for example the called server services and when i get in it is just a GUI to set the cron job.

Regarding the  setting some password is the same, your hosting company will help yoo for sure but is most be some kind of GUI to do that also.

 

Hope this info help.

 

Best regards!

  • Like 1
Link to comment
Share on other sites

35 minutes ago, gabgar said:

Hi Calleja, this is a tutorial fro cronjobs https://www.hostinger.com/tutorials/cron-job

Any way you have to think that every hosting company have there "own way" to do this. In my for example the called server services and when i get in it is just a GUI to set the cron job.

Regarding the  setting some password is the same, your hosting company will help yoo for sure but is most be some kind of GUI to do that also.

 

Hope this info help.

 

Best regards!

I will try

thank you very much!!

 

  • Like 1
Link to comment
Share on other sites

8 hours ago, gabgar said:

Now is me with the problem

[503]

Service temporarily unavailable

 

Anyone knows how to solve this error!!!!!

503 is server issue, maybe to many requests. can be really anything.

  • Sad 1
Link to comment
Share on other sites

11 hours ago, WisQQ said:

503 is server issue, maybe to many requests. can be really anything.

I just find that if i move the csv to another location and not the same as de php the problem is solved

Yes, i was worry to read something like this...I will try to figure out why this!!!!:.

Thanks a lot.

Edited by gabgar
find the solution (see edit history)
Link to comment
Share on other sites

  • 3 weeks later...

hello everyone I created a php file in which I execute sql requests and another csv to do the automatic update of the stock I test it locally in prestashop all is well
but in FTP I have an error message 504 getway time out ngnix even if I ogmented the execution time, I deleted the cokkies, I generated the sitemap module but the message remains the same<<504 Gateway Time-out  nginx>>
any one help me please :)

Edited by Interl421 (see edit history)
Link to comment
Share on other sites

2 hours ago, Interl421 said:

hello everyone I created a php file in which I execute sql requests and another csv to do the automatic update of the stock I test it locally in prestashop all is well
but in FTP I have an error message 504 getway time out ngnix even if I ogmented the execution time, I deleted the cokkies, I generated the sitemap module but the message remains the same<<504 Gateway Time-out  nginx>>
any one help me please :)

I had same issue recently after moving to vps. It simply runs out of time. I had to modify script to make it work again, but it all depends how you use it.

Try to split csv into chunks and then update, after each chunk echo something so script wont terminate.

Edited by WisQQ (see edit history)
Link to comment
Share on other sites

7 hours ago, Interl421 said:

the problem is that every day I need to update the stock with a new csv  file and if I take each piece after the other it takes a lot of time
Any help 

If you can, try to split it inside the scrips. Upload one big csv and just chunk the array u have before you execute final update.

$requestindex = array_chunk($idx,10);

array_chunk function splits array into chunks. In the example above its 10 references per request.

  • Thanks 1
Link to comment
Share on other sites

  • 4 months later...
17 hours ago, cristib said:

Hi, thank you for the script.

I'm trying to use this on PS 1.7.6.7, it seems to run, but product quantity doesn't actually update.

connected!.
connected!.
array filled.
array filled.
exit

It puts updated data into log file on server. First check if log file is created and its not empty. If your product quantity doesnt get updated then there is something missing.

Link to comment
Share on other sites

Log file is created but it's empty.

⇊ Update Starts ⇊ 06:06:51
⇈ Update Finished ⇈ 06:07:01

This is my CSV.

 

"Reference";"Quantity"
"A500RERXL";999
"DRG 3355002";999

First is a combination reference, the second is a simple product.

My database uses a different prefix, i have replaced "ps_" with "pslk_ "

thank you, and apologies in advance, my php knowledge is very limited :)

 

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

function shop_products(){
	$db = connect();	
	$array=array();
	$array2=array();
	$query= $db->prepare('select pslk_product.id_manufacturer AS manuid,pslk_product_attribute.reference AS REF,pslk_stock_available.quantity AS QTY from pslk_product LEFT JOIN pslk_product_attribute on pslk_product_attribute.id_product=pslk_product.id_product LEFT JOIN pslk_stock_available on pslk_stock_available.id_product_attribute=pslk_product_attribute.id_product_attribute');
	try{
		$query->execute();
		while (($row = $query->fetch(PDO::FETCH_BOTH)) !== false) {
			if(!empty($row['REF'])){
				$array2[]=$row;
			}
		}
		echo 'array filled.<br />';	
	}catch(PDOException $e){
		echo $e->getMessage(); 
	}
	$query= $db->prepare('SELECT pslk_product.reference AS REF, pslk_product.id_manufacturer AS manuid,pslk_stock_available.id_product_attribute AS BaseP, pslk_stock_available.quantity AS QTY FROM pslk_product LEFT JOIN pslk_stock_available ON pslk_stock_available.id_product=pslk_product.id_product');
	try{
		$query->execute();
		while (($row = $query->fetch(PDO::FETCH_BOTH)) !== false) {
			if(!empty($row['REF']) && $row['BaseP'] == 0){
					$array[]=$row;
			}
		}
		echo 'array filled.<br />';		
	}catch(PDOException $e){
		echo $e->getMessage(); 
	}
	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;
}

function Update(){
	$db = connect();
	$csvFile = '/pathtomy/Update.csv';
	$csv = readCSV($csvFile);
	$sp = shop_products();
	foreach($sp as $ps){
		$indeks = $ps['REF'];
		$qty = $ps['QTY'];
		$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{
								$query = $db->prepare("UPDATE pslk_product LEFT JOIN pslk_stock_available on pslk_stock_available.id_product=pslk_product.id_product SET pslk_stock_available.quantity = '".$avbl."' WHERE pslk_product.reference = '".$indeks."' AND pslk_stock_available.id_product_attribute = 0");
								$query->execute();
								$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{
							try{
								$query = $db->prepare("UPDATE pslk_product_attribute LEFT JOIN pslk_stock_available on pslk_stock_available.id_product_attribute=pslk_product_attribute.id_product_attribute LEFT JOIN pslk_product on pslk_product_attribute.id_product=pslk_product.id_product SET pslk_stock_available.quantity = '".$avbl."' WHERE pslk_product_attribute.reference = '".$indeks."'");
								$query->execute();
								$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();
							}							
						}
					}
				}
			}
		}	
	}
}


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);
Update();
$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");

 

Link to comment
Share on other sites

9 hours ago, cristib said:

Log file is created but it's empty.


⇊ Update Starts ⇊ 06:06:51
⇈ Update Finished ⇈ 06:07:01

This is my CSV.

 


"Reference";"Quantity"
"A500RERXL";999
"DRG 3355002";999

First is a combination reference, the second is a simple product.

My database uses a different prefix, i have replaced "ps_" with "pslk_ "

thank you, and apologies in advance, my php knowledge is very limited :)

 


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

function shop_products(){
	$db = connect();	
	$array=array();
	$array2=array();
	$query= $db->prepare('select pslk_product.id_manufacturer AS manuid,pslk_product_attribute.reference AS REF,pslk_stock_available.quantity AS QTY from pslk_product LEFT JOIN pslk_product_attribute on pslk_product_attribute.id_product=pslk_product.id_product LEFT JOIN pslk_stock_available on pslk_stock_available.id_product_attribute=pslk_product_attribute.id_product_attribute');
	try{
		$query->execute();
		while (($row = $query->fetch(PDO::FETCH_BOTH)) !== false) {
			if(!empty($row['REF'])){
				$array2[]=$row;
			}
		}
		echo 'array filled.<br />';	
	}catch(PDOException $e){
		echo $e->getMessage(); 
	}
	$query= $db->prepare('SELECT pslk_product.reference AS REF, pslk_product.id_manufacturer AS manuid,pslk_stock_available.id_product_attribute AS BaseP, pslk_stock_available.quantity AS QTY FROM pslk_product LEFT JOIN pslk_stock_available ON pslk_stock_available.id_product=pslk_product.id_product');
	try{
		$query->execute();
		while (($row = $query->fetch(PDO::FETCH_BOTH)) !== false) {
			if(!empty($row['REF']) && $row['BaseP'] == 0){
					$array[]=$row;
			}
		}
		echo 'array filled.<br />';		
	}catch(PDOException $e){
		echo $e->getMessage(); 
	}
	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;
}

function Update(){
	$db = connect();
	$csvFile = '/pathtomy/Update.csv';
	$csv = readCSV($csvFile);
	$sp = shop_products();
	foreach($sp as $ps){
		$indeks = $ps['REF'];
		$qty = $ps['QTY'];
		$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{
								$query = $db->prepare("UPDATE pslk_product LEFT JOIN pslk_stock_available on pslk_stock_available.id_product=pslk_product.id_product SET pslk_stock_available.quantity = '".$avbl."' WHERE pslk_product.reference = '".$indeks."' AND pslk_stock_available.id_product_attribute = 0");
								$query->execute();
								$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{
							try{
								$query = $db->prepare("UPDATE pslk_product_attribute LEFT JOIN pslk_stock_available on pslk_stock_available.id_product_attribute=pslk_product_attribute.id_product_attribute LEFT JOIN pslk_product on pslk_product_attribute.id_product=pslk_product.id_product SET pslk_stock_available.quantity = '".$avbl."' WHERE pslk_product_attribute.reference = '".$indeks."'");
								$query->execute();
								$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();
							}							
						}
					}
				}
			}
		}	
	}
}


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);
Update();
$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");

 

replace

while (($row = fgetcsv($handle, 4096, ",")) !== false) {

with this

while (($row = fgetcsv($handle, 4096, ";")) !== false) {

 

  • Like 1
Link to comment
Share on other sites

On 7/24/2020 at 7:10 PM, WisQQ said:

replace


while (($row = fgetcsv($handle, 4096, ",")) !== false) {

with this


while (($row = fgetcsv($handle, 4096, ";")) !== false) {

 

Awesome, it works! Thank you!

Must have messed with it before and replaced the semicolon when trying different csv's.

 

Link to comment
Share on other sites

  • 5 months later...

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.

Snímek obrazovky 2021-01-06 v 1.38.29.png

Edited by MajklozaCZ (see edit history)
Link to comment
Share on other sites

Hi,

i your code, after you're changing/updating the combination quantity in the pslk_stock_available  table, then you should update the product's quantity by summing up the quantities of all it's combinations, and update that in the table pslk_stock_available, where the id_product_attribute = 0 , this row contains the product's main quantity which is displayed in Backoffice product listing as main product quantity.

Kind regards, Leo

Link to comment
Share on other sites

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.

Snímek obrazovky 2021-01-06 v 1.38.29.png

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");
?>

 

Edited by WisQQ (see edit history)
Link to comment
Share on other sites

  • 3 months later...

https://www.whiletrue.it/update-prestashop-product-quantities-csv-file/

I have PS 1.6.23. I tried this code, but it does not work for me correctly. The main quantity of combinations is always increased by the quantity from the CSV file, and it is not equal with the sum of the combinations quantity. I cannot reset all quantities to zero as my CSV file contains only the changes, not the full stock.

How do I fix this code?

And other question: I have to multiply the stock from CSV with a number stored in the UPC field of the product (the CSV contains pieces while in the webshop we sell packages and I store the pcs/package number in the UPC field). How do I modify the code the achieve this?

Edited by Feri77 (see edit history)
Link to comment
Share on other sites

Hi ,

after updating your combinations of a product, you could calculate the total quantity of all combinations and update it with this SQL (where id_product = 2 in my example) :


UPDATE `ps_stock_available` 
set quantity = ( SELECT SUM(quantity)  FROM  (select * from `ps_stock_available`) AS m2  WHERE m2.`id_product` = 2 AND m2.id_product_attribute !=0 )
WHERE `id_product` = 2 AND id_product_attribute =0

 

In order to update your code multiplied by the UPC field, you should replace in your update the quantity field from the CSV with the UPC value and use this value for updating the stock.

Kind regards, Leo

Link to comment
Share on other sites

  • 10 months later...
  • 2 weeks later...
On 3/5/2022 at 10:50 AM, VERCVIII said:

Hi everybody, thanks for this script, it's working like a charm !

I would like to update the product price at the same time, anyone could help me to add some code lines to do it ? 

 

Thanks a lot !

It is possible, but first you need to have new net price. Also why do you want to do it with quantity update? It's better to do it in separate script, since yod dont change price everyday.
 

Do you have price impact on combinations or they all have the same base price? 

Link to comment
Share on other sites

On 3/5/2022 at 10:50 AM, VERCVIII said:

Hi everybody, thanks for this script, it's working like a charm !

I would like to update the product price at the same time, anyone could help me to add some code lines to do it ? 

 

Thanks a lot !

Hi thanks for you reply ! 

I would like to do it with quantity update because my CRM can automatically create and export only one csv file..

I already use it for this quantity script with cron job and it works perfectly fine.

If it could be possible to add the "product price tax excluded " to the csv file, and make it works like the quantity update ( it update it only when the value change ) it will help me a lot !

Once or twice a year I have prices updates, as I have 50 000 references, I use an import product add-on to update prices. But we often modify some prices manually between this big updates and this script could help me to just modify them on my crm..  you know what I mean ? I don't know if it's clear ( please forgive my English ! ) ...

I don't have any combinations product ! 

 

Thanks for your help !

 

Link to comment
Share on other sites

8 minutes ago, VERCVIII said:

Hi thanks for you reply ! 

I would like to do it with quantity update because my CRM can automatically create and export only one csv file..

I already use it for this quantity script with cron job and it works perfectly fine.

If it could be possible to add the "product price tax excluded " to the csv file, and make it works like the quantity update ( it update it only when the value change ) it will help me a lot !

Once or twice a year I have prices updates, as I have 50 000 references, I use an import product add-on to update prices. But we often modify some prices manually between this big updates and this script could help me to just modify them on my crm..  you know what I mean ? I don't know if it's clear ( please forgive my English ! ) ...

I don't have any combinations product ! 

 

Thanks for your help !

 

Okay you can work with one export file, but still I would recommend using separate cronjob for the price update.

here is query for fetching product prices without tax:

$query= $db->prepare('SELECT ps_product.reference AS REF, ps_product_shop.price AS Price FROM ps_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product=ps_product.id_product');

And here is price update function

Function UpdatePrices(){
	$db = connect();
	$csvFile = 'path to csv file';
	$csv = readCSV($csvFile);
	$sp = shop_products(0);
	$query = $db->prepare("UPDATE ps_product_shop LEFT JOIN ps_product on ps_product_shop.id_product=ps_product.id_product SET ps_product_shop.price = ':newprice' WHERE ps_product.reference = ':indeks'");
	foreach($sp as $s){
		$indeks = $s['REF'];
		$price = $s['Price'];
		if(!empty($indeks)){
			foreach($csv as $st){
				$index = $st['Reference'];
				$newprice = $st['Price'];
				if($indeks == $index){
					if($price != $newprice){
						try{
								$query->execute([':newprice'=>$newprice,':indeks'=>$indeks]);
								$log  = $indeks.PHP_EOL.
									"Price change - from ".round($prc,2)." to ".round($nwprc,2).PHP_EOL.
									"-------------------------".PHP_EOL;
								file_put_contents('./log/log_price_'.date("j.n.Y").'.log', $log, FILE_APPEND);
							}catch(PDOException $e){
								$log  = $indeks.PHP_EOL.
									$e->getMessage()."error".PHP_EOL.
									"-------------------------".PHP_EOL;
								file_put_contents('./log/log_price_'.date("j.n.Y").'.log', $log, FILE_APPEND);
								die();
							}
					}
				}
			}
		}
	}
}

Try it on your localhost site if it's working correctly before you run it on main site.

Link to comment
Share on other sites

wow thanks for your help ! 

Any chance you could help me to add this to my existing code ? 

<?php
set_time_limit(0);
#version 3
$database = require_once('../app/config/parameters.php');
// Creation 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;
}
// recup ref
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;
}
// telechargement du fichier csv
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 stock basé sur référence
Function Update(){
	$db = connect();
	$csvFile = 'fichier/StockInternet.csv';
	$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['Quantite'];
				if($indeks == $index){
					if($qty != $avbl){
						if(isset($base)){
							try{
								$product->execute([':avbl'=>$avbl,':indeks'=>$indeks]);
								$log  = $indeks.PHP_EOL.
									"simple produit - quantite changee de ".$qty." a ".$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");
?>

thanks a lot !

Link to comment
Share on other sites

On 3/17/2022 at 1:43 PM, VERCVIII said:

wow thanks for your help ! 

Any chance you could help me to add this to my existing code ? 

<?php
set_time_limit(0);
#version 3
$database = require_once('../app/config/parameters.php');
// Creation 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;
}
// recup ref
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;
}
// telechargement du fichier csv
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 stock basé sur référence
Function Update(){
	$db = connect();
	$csvFile = 'fichier/StockInternet.csv';
	$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['Quantite'];
				if($indeks == $index){
					if($qty != $avbl){
						if(isset($base)){
							try{
								$product->execute([':avbl'=>$avbl,':indeks'=>$indeks]);
								$log  = $indeks.PHP_EOL.
									"simple produit - quantite changee de ".$qty." a ".$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");
?>

thanks a lot !

 

On 3/17/2022 at 1:43 PM, VERCVIII said:
$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');

 

First change above query to include price from product_shop table(remember that pricer are without tax).

$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,ps_product_shop.price AS Price FROM ps_product LEFT JOIN ps_stock_available ON ps_stock_available.id_product=ps_product.id_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product=ps_product.id_product');

Then you can use function updateprices or modify existing stockupdate to include price comparsion. Also in that price comparsion you should check if new price isnt set to negative value or is not set to 0 just to be sure, because you can allways miss something inside your csv.

New prices will be rounded up to 6 decimal palces inside database, so you can set them like that inside CSV so the comparsion will be accurate.

Link to comment
Share on other sites

  • 2 weeks later...

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...