Delete Product automatic after being with the stock 0 and time 30 days

[Paulo Nacif]

As create in my module?

What hooks?

When put cod php?

[Enrique Gómez]

The easiest way is triggers

http://mypresta.eu/e...ck-product.html

 

CREATE TRIGGER change_active_after_update AFTER UPDATE ON ps_stock_available
FOR EACH ROW
BEGIN
UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0);
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
END

 

For the 30 days modify the condiction of the WHERE

 

CREATE TRIGGER `change_active_after_update` AFTER UPDATE ON `ps_stock_available`
FOR EACH ROW BEGIN
UPDATE ps_product_shop  SET active=0
WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0)  
AND DATEDIFF(NOW(),date_upd)>30;
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
END

Edited July 3, 2013 by egomezpe (see edit history)

[vekia]

that's right, beautiful code

i tested it for products older than 1 day and disabled it properly

 

one question:

you want to remove or disable? this is main question i think in this case

[Enrique Gómez]

that's right, beautiful code

i tested it for products older than 1 day and disabled it properly

 

Nice tutorials on your page Vekia, by the way, it's possible to subscribe via RSS to your articles?

Thanks

[Paulo Nacif]

Good, but Erro in line 4 '' ....

[vekia]

Good, but Erro in line 4 '' ....

 

what you mean? please paste here whole error code that you've got. it will be really helpfull

[Enrique Gómez]

It depends on the tool you use for inserting the trigger In phpmyadmin you have to specify the delim (I've used |)

 

[Paulo Nacif]

[Enrique Gómez]

Use this with the delimiter

 

DELIMITER $$
CREATE TRIGGER `change_active_after_update` AFTER UPDATE ON `ps_stock_available`
FOR EACH ROW BEGIN
UPDATE ps_product_shop  SET active=0
WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0)
AND DATEDIFF(NOW(),date_upd)>30;
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
END $$
/* Reset the delimiter back to ";" */
DELIMITER ;

Edited July 6, 2013 by egomezpe (see edit history)

[Paulo Nacif]

Use this with the delimiter

 

DELIMITER $$
CREATE TRIGGER `change_active_after_update` AFTER UPDATE ON `ps_stock_available`
FOR EACH ROW BEGIN
UPDATE ps_product_shop  SET active=0
WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0)
AND DATEDIFF(NOW(),date_upd)>30;
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
END $$
/* Reset the delimiter back to ";" */
DELIMITER ;

Thanks, was perfect

[Vosteen]

Hope it's okay to dig out this old thread, but it seems very useful to me.

 

Two question:

- Do I have to create two triggers (Auto deactivate and auto delete), or can I do in one step?

 

- What's the smarter move: auto deleting or just auto deactivating an old product?

I mean, what if a a customer looks at an old order? Or will statistics be effected somehow?

[Inter Svetainė]

It is always advisable not to delete an item that is no longer in trade. Just because the buyer can always see previous orders and view purchased goods in his ORDERS account.