Forum_Admin
Haha, no offence taken, I was, in fact, admitting that there is no way I could ever be called a good developer, I'm a hack at best.
I understand that documentation can be in many cases harder than the development, however, it's not just for developers though, there are many WP users like myself who with a little light documentation or a point in the right direction can manage enough code to get a solution without doing too much development.
In fact, there was enough in your answer to lead me to a solution. Here it is for anyone wondering:
My Product in question is ID "5374".
This query looks up all the ATUM inbound purchase orders for this item ((as there may be multiple inbound stock shipments scheduled)
Looks up the current inventory quantity for the product in question.
Identifies from which incoming shipment any next order of this product would be allocated from and returns the expected ship date from the 'Expected at location date' of the purchase order. Which assumes I could unpack a shipment and get it shipped same day which is unlikely.
So If I have 3 shipments of 10 items coming and my current stock level is -15, the next order placed for this product will be fulfilled from the second shipment and so the 'Expected at location date' of this PO is returned.
SET @runtot:=0;
Select ETA From
(
Select ETA,(@runtot := @runtot + q1.QtyInbound) AS QtyInbound_rt, (q1.SOH * -1) as InverseSOH FROM
(
Select pm1.meta_value AS ETA, OIM1.meta_value AS QtyInbound, pm2.meta_value as SOH
FROM wp_posts p
LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID AND pm1.meta_key = 'expected_at_location_date')
LEFT JOIN wp_atum_order_items OI ON ( OI.order_id = p.id)
LEFT JOIN wp_atum_order_itemmeta OIM1 ON (OIM1.order_item_id =OI.order_item_id AND OIM1.meta_key='qty' )
LEFT JOIN wp_atum_order_itemmeta OIM2 ON (OIM2.order_item_id =OI.order_item_id AND OIM2.meta_key='product_id' )
LEFT JOIN wp_postmeta pm2 ON ( pm2.post_id = OIM2.meta_value and pm2.meta_key = 'stock')
Where OIM2.meta_value = 5734 and p.post_status='atum_pending'
Order By ETA ASC
) as q1
) as q2
WHERE QtyInbound_rt > InverseSOH
LIMIT 1
I will use this to load the ETA date into a countdown timer that displays on the product page so people know if they buy now how far away the product is.
I will also need to revise the add to cart functionality to disable refuse back orders, remove the countdown timer, and email me if there is both no stock and no PO's with 'Expected at location date', or if the inventory quantity would go below the inverse of the sum of stock coming in. That or make sure I always have either a PO or stock in place.