Disable out of stock products except those products set to “When out of stock - Allow orders”

[kacti]

Hi everyone,

 

I'm trying to get Prestashop to hide out of stock products EXCEPT those that can be ordered when out of stock and those that currently have stock. 

 

I have searched and tried all the MYSQL triggers I can find but all have errors. I would prefer to use a trigger as this seems a much cleaner approach.

 

Does anyone else have any MYSQL based solutions? It would be great to finally resolve this issue. 

 

If anyone needs more information please message me...

Thanks.

 

 

My settings:

Prestashop version: 1.6.0.8

Products | Products Stock | Allow ordering of out-of-stock products: NO

 

I have a few products configured Quantities | When out of stock: Allow Orders (the vast majority are set to Default)

 

I am using “Advanced Stock Management” and “specify available quantities manually”

 

I am using vekia’s “How to automatically disable out of stock products in PrestaShop” trigger (excellent trigger). This is working 100%.

 

I have tried using pascal’s trigger (as below) but am receiving a syntax error.

 

Create a trigger in the ps_stock_available:

Trigger name: change_active_allow_oos

Table: ps_stock_available

Time: after

Event: Update

Definition:

 

BEGIN
DECLARE SHOPWIDEOOS TINYINT(1) DEFAULT 0;
SET SHOPWIDEOOS = (SELECT CAST(`value` AS SIGNED) AS num
                FROM `ps_configuration`
                WHERE `name` = 'PS_ORDER_OUT_OF_STOCK'
                limit 1);

UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity<=0 AND (out_of_stock =0 OR (out_of_stock = 2 AND SHOPWIDEOOS = 0)));
END

 

There is a syntax error with TINYINT – and I know nothing about MYSQL

 

I have tried using ambitos’s trigger (as below) but am receiving a syntax error.

 

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `change_active_after_update` AFTER UPDATE  
    ON `ps_stock_available`
    FOR EACH ROW BEGIN
UPDATE ps_product_shop SET active = IF(NEW.quantity <= 0,0,1) WHERE id_product = OLD.id_product;
 

There is a syntax error on the UPDATE line – again I know nothing about MYSQL

Edited July 4, 2014 by kacti (see edit history)

[PascalVG]

Hi Kacti,

I saw you error message here:  https://www.dropbox.com/s/x2avfgpa4os79j3/change_active_allow_oosERROR.png

 

Just checking, but did you also add the BEGIN and END around the code? This is part of the code!

 

 

BEGIN
DECLARE SHOPWIDEOOS TINYINT(1) DEFAULT 0;
SET SHOPWIDEOOS = (SELECT CAST(`value` AS SIGNED) AS num
                FROM `ps_configuration`
                WHERE `name` = 'PS_ORDER_OUT_OF_STOCK'
                limit 1);
 
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity<=0 AND (out_of_stock =0 OR (out_of_stock = 2 AND SHOPWIDEOOS = 0)));
END

 

Let me know,

pascal

[kacti]

Hi pascal

Yes i did leave the BEGIN and END out of the code (thanks for getting back to me)

 

If I want to use your trigger and have "Allow ordering of out-of-stock products" set to NO (global setting) and for individual products I want to sell set to "When out of stock - allow orders" what would the trigger be then?

 

From their respective tables

Products

out_of_stock =0 (DENY)

out_of_stock =1 (ALLOW) - this is what I have set

out_of_stock =2 (DEFAULT)

Site Wide

PS_ORDER_OUT_OF_STOCK=0 (DENY) - this is what I have set

PS_ORDER_OUT_OF_STOCK=1 (ALLOW)

 

So I changed (red section) out_of_stock from 0 (DENY) to 1 (ALLOW) and SHOPWIDEOOS (purple section) from 1 (ALLOW) to 0 (DENY)

 

This is what I came up with:

 

BEGIN
DECLARE SHOPWIDEOOS TINYINT(1) DEFAULT 1;
SET SHOPWIDEOOS = (SELECT CAST(`value` AS SIGNED) AS num
                FROM `ps_configuration`
                WHERE `name` = 'PS_ORDER_OUT_OF_STOCK'
                limit 1);
 
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity<=0 AND (out_of_stock =1 OR (out_of_stock = 2 AND SHOPWIDEOOS = 0)));
END

 

Is this correct?

 

Thanks heaps for all your help and support for such a wonderful product. And all your comments and suggestions on the forums. It's people like you who make these products into what they are - world leading software applications available for everyone in the world to use

Edited July 7, 2014 by kacti (see edit history)

[PascalVG]

Hi Kacti,

 

Quote:

If I want to use your trigger and have "Allow ordering of out-of-stock products" set to NO (global setting) and for individual products I want to sell set to "When out of stock - allow orders" what would the trigger be then?

 

If you want this, just use the trigger I wrote, this will take care of this.

When global setting NO allow ordering (setting to 0)

And some individual products are allowed (local product setting to 1), then my trigger does exactly this:

 

... WHERE quantity<=0 AND (<local product> out_of_stock = 0 OR (<local product> out_of_stock = 2 AND <global> out_of_stock = 0 <I?è no allow out of stock order>

 

This means, when the <local product> out_of_stock = 1 (I.e. Allow this individual. Product to be ordered when out of stock), the product will NOT be disabled, so that is what you want.

 

So, whatever choice you make (global 0 or 1, local 0,1,2) it will all be taken care of with my trigger. Just choose your global setting, close any local setting and it will be done as desired. You don't need to change anything when just playing with those settings.

 

So in short, just use my trigger should do the trick in your case,

Pascal

[Eolia]

 

See here : http://www.prestashop.com/forums/topic/344050-free-update-elements-display-only-active-products-categories/

 

This module allows orders when quantity is out of stock if you have checked this setting on