checking for new orders remotely

[bartman]

We are testing some order integration software that pulls all new/paid orders into a desktop enviroment, we have it working with ebay, amazon and magento, I am now looking to implement prestashop,

what is the easist way way to check for new orders on prestashop

Our plans are to check the table ps_order_history for two conditions where id_employee = 0 and id_order_state = 2

id_order_state should be where payment has been accepted id_employee = 0 is what prestashop sets the employee id to when a order comes in

if my thinking is correct if both these conditions are true this is a new order, then I can run some more queries on the database to get all the order details and finally in this stage update the ps_order_history changing the order state to order processing


does this look feasible and is my logic correct on new orders


regards



Chris

[rocky]

If I wanted to check the database for new orders, I'd just do an SQL query on the ps_order table to SELECT all rows WHERE `date_add` is greater than the time that I last checked for orders.

[bartman]

We had thought of that, and its my fault i should explained more clearly that we only want to check for paid orders, because we also accept bankwire and cheque payment, i do not want to pull these down to the desktop software, until we have manually marked them as paid.

[MrBaseball34]

This may be close

SELECT * FROM ps_orders 
LEFT JOIN ps_order_history ON ps_order_history.id_order = ps_orders.id_order
LEFT JOIN ps_order_state ON ps_order_state.id_order_state = ps_order_history.id_order_state
WHERE ps_order_state.id_order_state = 2

[joho]

This may be close

SELECT * FROM ps_orders 
LEFT JOIN ps_order_history ON ps_order_history.id_order = ps_orders.id_order
LEFT JOIN ps_order_state ON ps_order_state.id_order_state = ps_order_history.id_order_state
WHERE ps_order_state.id_order_state = 2

But what happens if an order later changes state to "Cancelled" or something else?

[MrBaseball34]

Then it won' t be returned in the results, DUH!

[joho]

Then it won' t be returned in the results, DUH!

So it's based on the fact that an order never changes status? Or what was the point you were trying to make with "DUH!"? Since you seem to know your way around SQL, how about trying to help out instead? I was merely trying to point out (to the person you were replying to) that he may not get the result(s) he expected.

[MrBaseball34]

He asked for

check for new orders on prestashop

I gave the SQL to do that and that only. You asked

what happens if an order later changes state to “Cancelled” or something else

So if the state changes, new orders would not be in the results, now would it.
Don't bitch at me, you were the one that asked the stupid question where the answer was obvious.

If you want to check for other states, look at the table ps_order_state_lang and find the one you want and check for the id_order_state that corresponds to the stat you want to check.

[Burhan BVK]

He asked for

check for new orders on prestashop

I gave the SQL to do that and that only. You asked

what happens if an order later changes state to “Cancelled” or something else

So if the state changes, new orders would not be in the results, now would it.
Don't bitch at me, you were the one that asked the stupid question where the answer was obvious.

If you want to check for other states, look at the table ps_order_state_lang and find the one you want and check for the id_order_state that corresponds to the stat you want to check.

Actually he asked a very reasonable question. You don't understand the problem as well as you think. Simply put, a canceled order would have already been downloaded before. And since they only check new orders and don't check if the status of the older orders their software will still think the order is valid even if it is canceled after it was downloaded by their software.

[MrBaseball34]

I believe this should work:

SELECT ps_orders.id_order, ps_order_state.id_order_state FROM ps_orders 
LEFT JOIN ps_order_history ON ps_order_history.id_order = ps_orders.id_order
LEFT JOIN ps_order_state ON ps_order_state.id_order_state = ps_order_history.id_order_state
WHERE ps_order_state.id_order_state = 2 OR ps_orders.id_order NOT IN
 (SELECT ps_orders.id_order 
  FROM ps_orders
  LEFT JOIN ps_order_history ON ps_order_history.id_order = ps_orders.id_order
  WHERE ps_order_history.id_order_state NOT IN (1 /* Awaiting cheque payment*/,
                                                6 /* Canceled*/,
                                                7 /* Refund*/,
                                                8 /* Payment error*/,
                                                9 /* Out of stock*/,
                                                10 /* Awaiting bank wire payment */))