Jump to content
  • 0
thijsvk

[Solved] Automatic Stock Update (Csv) Solution

Question

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

Share this post


Link to post
Share on other sites

37 answers to this question

Recommended Posts

  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

Hi thijsvk, great job!!

it works in my store. Only have issue... How can I do to avoid that the scrit set to zero all the ddbb first? I have products form diferents suppliers that I want mantain the same stock.

 

Congratulations!! ;)

Cosmetica Natural

Edited by ken-bcn

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

First,a big thank you for your code, Thijsvk. It helps me a lot.

Maybe I'm asking too much, but would you like to share your Excel macro's too? I'm not that well trained in Excel.

Whatever your answer, I wish you a merry Christmas and all the best for 2016!

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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)

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

Did you actually set a path to your CSV file, or is it set exactly as you posted?

The CSV file and the PHP file are in the same folder (scriptsActualizacion)

Edited by lupradoa

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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. 

Share this post


Link to post
Share on other sites
  • 0

Hmm, I'm still inclined to think that it might have something to do with the permissions, that echo doesn't work because the php file is not actually executed.

Unfortunately, I'm rather limited in my knowledge.

Might be an idea to ask someone like Vekia or ElPatron to have a look.

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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?

Share this post


Link to post
Share on other sites
  • 0
Guest

You should first enable both the PDO and MySQL PDO extension before you can run this snippet.

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0
Guest

Maybe keep using 1.6 then? 1.7 equals headaches

Share this post


Link to post
Share on other sites
  • 0

For me works in PS 1.6 and PS 1.7 too. Maybe have to change some parameters in require_once. Do you have any error code when try to do it? Try to print the variables to see his value in each step and compare the parameters 1.6 vs 1.7

 

Cosmetica Natural | Holle

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

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

Create an account

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

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More