Query to update order current_state and order_history

[patrmich]

Hello,

My shop is built with Prestashop 1.5.6.2.

I would like to use an SQL UPDATE query to do the following :

1-Change the state of a list of orders to state 100 (100 is the state related to shipped order)

So, in the ps_orders Table, I need to update the current_state value to 100
This first part is easy.

2- But, I would like also to do simultaneously the following :
- create entries in the ps_order_history table as follows :
id_order_history : a value to be incremented for each new entry
id_employee : always the same, as 0, for instance
id_order : list to be included in the query
id_order_state : 100
date-add : the current date

Would anyone know how to make this query ?

I thank you in advance for any reply.

Patrick

Edited May 10, 2020 by patrmich (see edit history)

[Guest]

SQL query will not do it.
I gave an example of a php script on how to do it.
Just search 😉

[Guest]

Updates all orders that have a current status of 4 and changes them to a status of 100.

Save the script as update-state.php in the root of your prestashop.

Then call the script from the URL, eg https://mydomain.com/update-state.php

 

<?php
    require(dirname(__FILE__).'/config/config.inc.php');
    
    $old_order_state = 4;
    $new_order_state = 100;
    
    $get_orders = Db::getInstance()->ExecuteS('SELECT id_order FROM '._DB_PREFIX_.'orders WHERE current_state = '.$old_order_state.' ORDER BY id_order');
    
    if (isset($get_orders)) {
        foreach ($get_orders as $orders) {
            $order = new Order($orders['id_order']);
            $history  = new OrderHistory();
            $history->id_order = (int)$order->id;
            $history->changeIdOrderState((int) $new_order_state, $order->id);
            
            if ($history->save()) 
                {echo 'OK - update order: '.$orders['id_order'].'<br />';} 
              else 
                {echo 'ERROR - update order: '.$orders['id_order'].'<br />';}
        } 
    }
?>

 

[patrmich]

Hi

I thank you very much for your kind reply.

1- I would like to change the state of a list of orders, according to the id_order value.
The id_order list could be as follows :
10201, 10202, 10204, 10206, 10310,...

In your script where could I add this list ?

2- In your script, you assume that the previous state is equal to 4

$old_order_state = 4;

In fact the previous value could have different value, such as 4 or 5 for instance.

I thank you again in advance for any reply.

Patrick

[Guest]

https://www.php.net/manual/en/control-structures.foreach.php

$get_orders = array();

 

<?php
    require(dirname(__FILE__).'/config/config.inc.php');
    
    $new_order_state = 100;
    
    $get_orders = array(10201,10202,10205,10210);
    
        foreach ($get_orders as $orders) {
            $order = new Order($orders);
            $history  = new OrderHistory();
            $history->id_order = (int)$order->id;
            $history->changeIdOrderState((int) $new_order_state, $order->id);
            
            if ($history->save()) 
                {echo 'OK - update order: '.$orders.'<br />';} 
              else 
                {echo 'ERROR - update order: '.$orders.'<br />';}
        } 

?>

 

Edited May 11, 2020 by Guest (see edit history)

[Guest]

It would also be good to add to the treatment codes if the current status of the order is not already written.

 

<?php
    require(dirname(__FILE__).'/config/config.inc.php');
    
    $new_order_state = 100;
    
    $get_orders = array(10201,10202,10205,10210);
    
        foreach ($get_orders as $orders) {
            $order = new Order($orders);
            $_state = (int) $order->current_state;
            if ($_state == $new_order_state)
                {Continue;}
            else
                { 
                    $history  = new OrderHistory();
                    $history->id_order = (int)$order->id;
                    $history->changeIdOrderState((int) $new_order_state, $order->id);
            
                    if ($history->save()) 
                        {echo 'OK - update order: '.$orders.'<br />';} 
                    else 
                        {echo 'ERROR - update order: '.$orders.'<br />';}
             } 
          }

?>

No more advice needed.

Edited May 11, 2020 by Guest
No more advice needed. (see edit history)

[patrmich]

Hello,

Thank you very much for your posts.

This evening I used the php file several times.

Everything worked well

But, later on, I made a mistake in the array content.

I put a comma at the beginning of the list, such as 

 $get_orders = array(,11201,11202,11205,11210);

When running the php file, I got an error.

I deleted the comma, but now when running the php file I am always getting a blank page.

Would you know what happened ?

I hope I did not alter anything important in the data base.

I thank again for your help.

 

Patrick

 

 

 

 

[Guest]

To thank you is a gray heart under the posts.

 

Check in the administration whether the status of orders has changed.

This mistake is being made by a person, so it is better to solve everything programmatically.
Of course, you can also do bug fixes if you're wrong.

 

But you probably don't even know the basics of PHP.
I just can't go back to the issue indefinitely.
Everyone can copy the script, but realize that I spend some time on it and during that time I could have made money elsewhere.

I end up in this thread.

Edited May 11, 2020 by Guest (see edit history)