-- 2251 MARKETPLACES 1. Check for OrderDate of unshipped orders

SET @thisDay = NOW();

SELECT o.OrderID, o.OrderDate,

DATE_ADD(
CASE
	WHEN DATE_FORMAT(o.OrderDate, '%W') = 'Friday' THEN DATE_ADD(o.OrderDate, INTERVAL 3 DAY)
	WHEN DATE_FORMAT(o.OrderDate, '%W') = 'Saturday' THEN DATE_ADD(o.OrderDate, INTERVAL 2 DAY)
	ELSE DATE_ADD(o.OrderDate, INTERVAL 1 DAY)
END, INTERVAL
CASE
	WHEN
		((scv.MFG_SKU IS NOT NULL AND scv.MFG_SKU != '') OR (esc.MFG_SKU IS NOT NULL AND esc.MFG_SKU != '')) -- If it's a SCV or ESC part,
		AND IFNULL(vpw.Stock, 0) = 0 -- and we have no stock,
		AND o.creditcardtransactionid LIKE 'Amazon%' -- and it was ordered from Amazon,
	THEN 1 -- add a day, because the handling time would be 2 days.
	ELSE 0 -- It's not an SCV or ESC part, we don't have stock, or it's not an Amazon order.
END DAY) AS ExpectedShipDate,

o.OrderStatus, od.ProductCode, od.ProductNote,
CASE
	WHEN o.creditcardtransactionid LIKE 'Amazon%' THEN 'Amazon'
	WHEN o.creditcardtransactionid LIKE 'eBay%' THEN 'eBay'
	WHEN o.creditcardtransactionid LIKE 'Walmart%' THEN 'Walmart'
END AS Marketplace,
CASE
	WHEN o.creditcardtransactionid LIKE 'Amazon%' THEN SUBSTR(o.creditcardtransactionid, 7)
	WHEN o.creditcardtransactionid LIKE 'eBay%' THEN SUBSTR(o.creditcardtransactionid, 5)
	WHEN o.creditcardtransactionid LIKE 'Walmart%' THEN SUBSTR(o.creditcardtransactionid, 8)
END AS 'Marketplace_Order#',

SUBSTRING_INDEX(
	TIMEDIFF(
	DATE_ADD(
		CASE
			WHEN DATE_FORMAT(o.OrderDate, '%W') = 'Friday' THEN DATE_ADD(o.OrderDate, INTERVAL 3 DAY)
			WHEN DATE_FORMAT(o.OrderDate, '%W') = 'Saturday' THEN DATE_ADD(o.OrderDate, INTERVAL 2 DAY)
			ELSE DATE_ADD(o.OrderDate, INTERVAL 1 DAY)
		END, INTERVAL
		CASE
			WHEN
				((scv.MFG_SKU IS NOT NULL AND scv.MFG_SKU != '') OR (esc.MFG_SKU IS NOT NULL AND esc.MFG_SKU != '')) -- If it's a SCV or ESC part,
				AND IFNULL(vpw.Stock, 0) = 0 -- and we have no stock
				AND o.creditcardtransactionid LIKE 'Amazon%' -- and it was ordered from Amazon,
			THEN 1 -- add a day, because the handling time would be 2 days.
			ELSE 0 -- It's not an SCV or ESC part, we don't have stock, or it's not an Amazon order.
		END DAY),
		@thisDay
	),
':', 1) AS Hours_Remaining
-- CASE WHEN (pj.Custom_Label3 LIKE '%SCV%' OR pj.Custom_Label3 LIKE '%ESC%') AND od.ProductNote NOT LIKE '%| STOCK%' THEN 24

FROM orders o
JOIN orderdetails od ON o.OrderID = od.OrderID
JOIN customers c  ON o.CustomerID = c.CustomerID
JOIN products.pins p ON od.productcode = p.ProductCode
LEFT JOIN (
	SELECT vm.PIN, pw.Stock
	FROM products.vendor_map vm
	JOIN products._VPW pw ON pw.MFG_SKU = vm.MFG_SKU AND vm.VendorCode = 'VPW'
) vpw ON vpw.PIN = p.PIN
LEFT JOIN products.vendor_map scv ON p.ProductCode = scv.ProductCode AND scv.VendorCode = 'SCV'
LEFT JOIN products.vendor_map esc ON p.ProductCode = esc.ProductCode AND esc.VendorCode = 'ESC'

WHERE o.Order_Entry_System = 'MARKETPLACE'
AND o.OrderStatus IN ('Processing', 'Pending', 'Ready to Ship', 'Overnight Hold')

ORDER BY o.OrderDate ASC
;