SQL Help - Listing orders that all stock is available.

[madpugger]

Hi, thanks in advance for any help!

I am trying to come up with a SQL query for order prep that will give a list of un-fulfilled orders that we currently have stock for (so for example we've just had a big delivery, will we now be able to ship some orders?).

One thing to note, I am using (the decidedly awful!) Boostmyshop Advanced Stock Management in 1.7.8.2 since multi-warehouse is not available.

As far as I can figure out, I need to Select all order IDs for orders that are not shipped and where all items in order detail for that order are in stock in a particular warehouse.

The warehouse table in Boostmyshop is:

pr_bms_advancedstock_warehouse_product

And has the tables:

• wi_id
• wi_warehouse_id
• wi_product_id
• wi_attribute_id
• wi_physical_quantity
• wi_available_quantity
• wi_reserved_quantity
• wi_shelf_location
• wi_quantity_to_ship
• wi_warning_stock_level
• wi_use_config_warning_stock_level
• wi_ideal_stock_level
• wi_use_config_ideal_stock_level

Again, thanks for any help!

Edited December 22, 2021 by madpugger
update table entries (see edit history)

[madpugger]

15 minutes ago, ndiaga said:

Hi,

I  think    I   already   solved  a  similar  problem  with  a custom  module .

Take  a look   at  this  one :  

 

Tha ks for your reply!

 

however what I need is a list of orders that now have all items in stock. This may be from a year or more ago as in our industry products are announced and pre ordered well in advance!

[madpugger]

So far I have this:

SELECT
od.id_order,
od.product_id,
od.product_attribute_id,
od.product_name,
od.product_quantity,
asm.wi_physical_quantity
FROM
pr_order_detail od
LEFT JOIN pr_orders o
ON o.id_order = od.id_order
LEFT JOIN  pr_bms_advancedstock_warehouse_product asm
ON od.product_id = asm.wi_product_id AND asm.wi_warehouse_id = 12
ORDER BY od.id_order ASC

So I am making progress, but not entirely sure yet how to only show in stock orders!

[madpugger]

On 12/22/2021 at 1:04 PM, ndiaga said:

You  can  provide  you  a  customized  solution  if   you  want?

You  also  have  this  module  :    Export Orders and customers in a CSV file.

I appreciate that but I am not sure you understand the problem or solution required, as the two offerings you have suggested are a mile away from what is needed?

PS already has the functionality we need but it is only used at order creation (PS will select Payment Accepted or In Back Order as order status depending on stock).

We need to use this functionality to check old orders to see if they can now be processed. I don't mind if that is a module that gives a list of orders, a query that sets all NOW processable order to Payment Accepted (or similar), or if its just an SQL query that gives a CSV of order numbers.