[Solved] SQL query to mark orders as shipped?

[Eutanasio]

Hi,

Does anyone know how to write an SQL query I can use on PHPmyadmin to change something in all orders with a particular order status?

I want all orders with status ID = 5 to be considered as "shipped". Actual PS version is 1.7.6, but the orders I want to change are from 2016 or earlier, I used to have different PS versions by then, don't know if that affects anything.

Thanks!

[Gerardo Martinez G]

In my case my id  shipped is the id_order_state=4
check your id
first make backup this table ps_orders

for only 2016

UPDATE `ps_orders` SET `current_state` = '4' WHERE `current_state` = 5 AND `date_add` > '2016-01-01 00:00:00'  AND `date_add` < '2016-12-31 23:59:59'

for 2016 or earlier

UPDATE `ps_orders` SET `current_state` = '4' WHERE `current_state` = 5 AND `date_add` < '2016-12-31 23:59:59'

 

[Eutanasio]

Thanks @Gerardo Martinez G! that would be my plan B, because I don't know if by only changing the status using a query will trigger the system to change all other values linked to that status (meaning marking those orders as "shipped"), what do you think?

Otherwise I'd rather prefer tu try a query that doesn't change the order status itself, but find those orders with `current_state` = 5 and set them as shipped, but I couldn't find this parameter on ps_orders

Thanks!

[Eutanasio]

16 hours ago, Gerardo Martinez G said:

In my case my id  shipped is the id_order_state=4
check your id
first make backup this table ps_orders

for only 2016

UPDATE `ps_orders` SET `current_state` = '4' WHERE `current_state` = 5 AND `date_add` > '2016-01-01 00:00:00'  AND `date_add` < '2016-12-31 23:59:59'

for 2016 or earlier

UPDATE `ps_orders` SET `current_state` = '4' WHERE `current_state` = 5 AND `date_add` < '2016-12-31 23:59:59'

 

It doesn't work, I've tried changing 1 order on this table manually, you save it and then it somehow gets back to the previous status. It has to be linked to another table, maybe the information on order_history is avoiding the status change.

[Gerardo Martinez G]

ok, I don't think I realized what you said
it will be that we will also have to add the status to ps_order_history

then we would first have a query to get the id of the order and then create a query with those id of the orders and add them also to ps_order_history

[Eutanasio]

Thanks for the help, but this is not doing what I need. I've done it now on ps_order_history, it changes indeed the status but just in appearance, it doesn't do any of the special functions of the status (consider order as validated, consider it as shipped...). My final objective by doing this is to make all orders with the status "Delivered" to be considered as "shipped" so the ASM system corrects itself, because I have plenty of products with fake reserved and physical stock because of this, for having this order status back then without the "consider order as shipped" option marked.

[Eutanasio]

Solved, after running on mode debug found the problem which was weird and solved on Github:
https://github.com/PrestaShop/PrestaShop/issues/14595#issuecomment-510884182

Edited February 15, 2020 by omar2886
wrong language (see edit history)