Prestashop - switching to shared stock.

Another useful tid-bit of information.

If you use Prestashop for your online e-commerce, then you may be aware of their "Multistore" feature - basically, you can use the same installation to run completely different shops; the difference being the URL you access them from.

So what, you might think. That's not really much more use thant just running two copies on different virtual hosts. Well, the strength is that you can share products, customers, stock levels, discounts, pretty much everything ... if you want to. Plus, of course, it's only one installation to keep up to date.

Great, this suited an installation I did a while back. Unfortunately, although the products were set as shared across all stores, somehow in the several iterations of testing the option to share stock levels was not. Since the vast majority of products are unique to each store, with only a few cross-overs, nobody noticed, until now.

OK, simple, we thought.  Switch the option on now.

Eek... all quantities will be reset to zero....

Not what we want at all.

Checking the code, we see that yes indeed, when changing this setting, sure enough, it deletes everything from the "stock_available" table for the affected shops.

The stock_available table has two relevant fields in it - id_shop and id_shop_group.

When stock is held for an individual shop, the first is filled in, and the group is zero.  When it's shared between a group of shops, it's the other way around.

Since the only table affected when you do the switch is the stock_available one, it seems logical therefore that this is the only source of data that we need to preserve.

Much testing in my sandbox shop therefore resulted in the following bit of code necessary to preserve the stock across the change.  READ ON BEFORE TRYING THIS OUT!

update ps_stock_available set id_shop_group=1, id_shop = 0;

Yep!  All we need to do is update the group field, and clear the shop field!  Of course, if you have more shops that you don't want affected, you should include a "where" clause to restrict whose products this affects.

You need to do this before you change the shared stock setting.  Also, it will fail if you have any products with stock held individually.  This code will list any of these -

select p.id_product, pa.id_product_attribute, count(sa.id_shop) as shops
from ps_product p LEFT JOIN ps_product_attribute pa ON (pa.id_product = 
p.id_product ) JOIN ps_stock_available sa on p.id_product = sa.id_product 
and sa.id_product_attribute = IF(pa.id_product_attribute IS NULL,0,
pa.id_product_attribute) group by sa.id_product, sa.id_product_attribute  
having count(*) > 1;

Yep, that's a bit longer, sorry!  It's got three tables to cross-reference.

It it comes up with any, this code will tell you more about which stores an item is held in, and how much is in each store, etc -

select * from ps_stock_available where id_product = 782;

(That's for product #782, of course - adjust as necessary!)

If, as I found, the products had zero stock in the secondary stores, you can just delete all but one of the records -

delete from ps_stock_available where id_product = 782 and id_shop <> 2;

Re-run the check again, then do the conversion.

This is easiest, of course, from the mysql command line, but if you use a web-based tool, you should be able to manage if it will accept typed mysql command lines.

I should give you the obvious warnings -

BACKUP YOUR DATABASE FIRST!!  Preferably using outside tools such as mysqldump
Then examine the backup file - does it look like it holds all your data??
PUT YOUR SHOP INTO MAINTENANCE MODE. You don't want customers confusing things.
TEST IT THOROUGHLY. If you are not convinced everything is right, restore from your backup!