-- QB ID: 3212

SELECT
vol.PIN,
o.Order_Entry_System,
p.Manufacturer,
p.PricingType,
od.ProductCode,
od.Quantity,
od.QtyOnHold,
vm.Stock,
vm.Stock - IFNULL(Already_Applied.Total_Unshipped, 0) AS Available_Inv,
p.Name AS ProductName,
o.OrderID,
ol.LineCount,
od.ProductNote,
od.OrderDetailID,
o.OrderStatus,
vm.Cost,
od.QtyOnHold * vm.Cost AS TotalCost,
od.Quantity * CASE WHEN /*od.ProductWeight = 0.251 OR*/ od.ProductWeight = 0 THEN 0.1 ELSE od.ProductWeight END AS TotalWeight,
p.Popularity,
o.OrderDate,
o.ShipState,
o.Custom_Field_CATStatus,
o.Custom_Field_CATReason


-- SELECT
-- o.OrderID,
-- o.Order_Entry_System,
-- o.OrderStatus,
-- ol.LineCount,
-- o.OrderDate,
-- o.ShipState,
-- o.Custom_Field_CATStatus,
-- o.Custom_Field_CATReason,
-- vol.PIN,
-- p.Name AS ProductName,
-- p.Manufacturer,
-- p.PricingType,
-- vm.Stock,
-- vm.Stock - IFNULL(Already_Applied.Total_Unshipped, 0) AS Available_Inv,
-- p.Popularity,
-- od.OrderDetailID,
-- od.ProductCode,
-- od.Quantity,
-- od.QtyOnHold,
-- od.ProductNote,
-- vm.Cost,
-- od.QtyOnHold * vm.Cost AS TotalCost,
-- od.Quantity * CASE WHEN /*od.ProductWeight = 0.251 OR*/ od.ProductWeight = 0 THEN 0.1 ELSE od.ProductWeight END AS TotalWeight


FROM custopayted.orderdetails od
JOIN custopayted.orders o ON o.OrderID = od.OrderID
JOIN products.client_map vol ON od.ProductCode = vol.ProductCode
JOIN products.pins p ON vol.PIN = p.PIN
JOIN (
	SELECT od.OrderID, COUNT(od.OrderDetailID) AS LineCount
	FROM custopayted.orderdetails od
	JOIN custopayted.orders o ON o.OrderID = od.OrderID
	WHERE o.OrderStatus = 'Processing'
	GROUP BY od.OrderID
) ol ON ol.OrderID = o.OrderID

LEFT JOIN (
	SELECT vol.PIN, SUM(od.quantity) AS Total_Unshipped
	FROM custopayted.orderdetails od
	JOIN custopayted.orders o ON od.orderid = o.orderid
	JOIN products.client_map vol ON od.productcode = vol.ProductCode
	WHERE o.orderstatus = 'Processing'
	AND od.productnote LIKE '%STOCK%'
	AND od.quantity > 0
	AND od.qtyshipped = 0
	GROUP BY vol.PIN
) AS Already_Applied ON vol.PIN = Already_Applied.PIN
JOIN products.vendor_map vm ON vol.PIN = vm.PIN
	AND (vm.Stock - IFNULL(Already_Applied.Total_Unshipped, 0) ) >= od.QtyOnHold -- This is looking at total stock vs line level qty, so over-marking/ordering is possible.


WHERE o.OrderStatus = 'Processing'
AND vm.VendorCode = 'VPW' -- Only VPW
AND od.QtyOnHold > 0
AND vm.Stock > 0
AND (
	-- od.productnote LIKE '%SPC%' OR od.productnote LIKE '%ETA%'
	od.ProductNote NOT LIKE '%STOCK%' AND (od.productnote NOT LIKE '%DS%' OR od.productnote LIKE '%SPC%')
)
AND ol.LineCount = 1
AND DATE(o.OrderDate) = CURDATE()
AND od.lastmodified > CURDATE()

ORDER BY o.OrderID DESC
;