Jump to content

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


Paulo Nacif
 Share

Recommended Posts

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 by egomezpe (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by egomezpe (see edit history)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

  • 2 years later...

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?

Share this post


Link to post
Share on other sites

  • 4 years 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
 Share

×
×
  • Create New...

Important Information

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