-- 3219 Manual Appliance BackOrder Fulfillment report
SELECT
	od.orderdetailid,
	od.orderid,
	DATE(o.orderdate),
	od.totalprice,
	od.quantity,
	od.qtyonhold,
	o.orderstatus,
	od.productnote,
	cm.productcode,
		CASE
		WHEN ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'ENC' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) < 10
		THEN NULL
		ELSE COALESCE(SUM(CASE WHEN vm.vendorcode = 'ENC' THEN e.WV + e.TX + e.ON + e.OH + e.NY + e.NV + e.IN + e.GA + e.FL + e.CA + e.AW ELSE 0 END), 0)
	END AS ENC_TotalStock,
	MAX(CASE WHEN vm.VendorCode = 'ENC' THEN vm.cost ELSE NULL END) AS ENCVendorCost,
	ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'ENC' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) AS ENC_Margin,

		CASE
		WHEN ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'REL' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) < 10
		THEN NULL
		ELSE COALESCE(SUM(CASE WHEN vm.vendorcode = 'REL' THEN r.LA_BatonRouge + r.TX_Richardson_PU + r.FL_NorthTampa + r.TX_Houston + r.AZ_Phoenix + r.NM_Albuquerque + r.TX_SanAntonio + r.TX_FortWorth_PU + r.TX_Irving_PU + r.TX_Mesquite + r.IN_Indianapolis + r.IN_Highland + r.UT_SaltLakeCity + r.CA_Anaheim + r.CA_Berkeley_PU + r.CA_Sacramento ELSE 0 END), 0)
	END AS REL_TotalStock,
	MAX(CASE WHEN vm.VendorCode = 'REL' THEN vm.cost ELSE NULL END) AS RELVendorCost,
	ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'REL' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) AS REL_Margin,

		CASE
		WHEN ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'DEY' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) < 10
		THEN NULL
		ELSE COALESCE(SUM(CASE WHEN vm.vendorcode = 'DEY' THEN d.MI + d.TX + d.WI_Milwaukee + d.CO + d.KS + d.NE + d.ND + d.IA_DesMoines + d.MN_Duluth + d.WI_GreenBay + d.IA_Waterloo + d.SD + d.MN_VadnaisHeights ELSE 0 END), 0)
	END AS DEY_TotalStock,
	MAX(CASE WHEN vm.VendorCode = 'DEY' THEN vm.cost ELSE NULL END) AS DEYVendorCost,
	ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'DEY' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) AS DEY_Margin,

		CASE
		WHEN ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'MRC' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) < 10
		THEN NULL
		ELSE COALESCE(SUM(CASE WHEN vm.vendorcode = 'MRC' THEN m.Stock ELSE 0 END), 0)
	END AS MRC_TotalStock,
	MAX(CASE WHEN vm.VendorCode = 'MRC' THEN vm.cost ELSE NULL END) AS MRCVendorCost,
	ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'MRC' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) AS MRC_Margin,

		CASE
		WHEN ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'UED' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) < 10
		THEN NULL
		ELSE COALESCE(SUM(CASE WHEN vm.vendorcode = 'UED' THEN m.Stock ELSE 0 END), 0)
	END AS UED_TotalStock,
	MAX(CASE WHEN vm.VendorCode = 'UED' THEN vm.cost ELSE NULL END) AS UEDVendorCost,
	ROUND(((od.totalprice - (MAX(CASE WHEN vm.VendorCode = 'UED' THEN vm.cost ELSE NULL END) * od.quantity)) / od.totalprice) * 100, 2) AS UED_Margin

FROM
	custopayted.orders o
JOIN
	custopayted.orderdetails od ON o.orderid = od.orderid
JOIN
	products.client_map cm ON od.productcode = cm.productcode AND cm.Client = 'Volusion'
JOIN
	products.vendor_map vm ON cm.PIN = vm.PIN AND cm.client = 'Volusion' AND cm.Parent = ''
LEFT JOIN
	products._ENC e ON vm.MFG_SKU = e.MFG_SKU
LEFT JOIN
	products._REL r ON vm.MFG_SKU = r.MFG_SKU
LEFT JOIN
	products._DEY d ON vm.MFG_SKU = d.MFG_SKU
LEFT JOIN
	products._MRC m ON vm.MFG_SKU = m.MFG_SKU
LEFT JOIN
	products._UED u ON vm.MFG_SKU = u.MFG_SKU
WHERE
	vm.vendorcode IN ('ENC', 'REL', 'DEY', 'MRC', 'UED')
	AND od.qtyonhold > 0
	AND o.orderstatus = 'processing'
	AND (od.productnote LIKE '%SPC%' OR od.productnote LIKE '%ETA%')
	AND o.orderdate < CURRENT_DATE - INTERVAL 14 DAY
	AND o.ordernotes NOT LIKE '%BACKREV%'
	AND vm.Stock > 1
GROUP BY
	od.orderdetailid
HAVING
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'MRC' THEN m.Stock ELSE 0 END), 0) +
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'ENC' THEN e.WV + e.TX + e.ON + e.OH + e.NY + e.NV + e.IN + e.GA + e.FL + e.CA + e.AW ELSE 0 END), 0) +
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'REL' THEN r.LA_BatonRouge + r.TX_Richardson_PU + r.FL_NorthTampa + r.TX_Houston + r.AZ_Phoenix + r.NM_Albuquerque + r.TX_SanAntonio + r.TX_FortWorth_PU + r.TX_Irving_PU + r.TX_Mesquite + r.IN_Indianapolis + r.IN_Highland + r.UT_SaltLakeCity + r.CA_Anaheim + r.CA_Berkeley_PU + r.CA_Sacramento ELSE 0 END), 0) +
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'DEY' THEN d.MI + d.TX + d.WI_Milwaukee + d.CO + d.KS + d.NE + d.ND + d.IA_DesMoines + d.MN_Duluth + d.WI_GreenBay + d.IA_Waterloo + d.SD + d.MN_VadnaisHeights ELSE 0 END), 0) +
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'UED' THEN u.Stock ELSE 0 END), 0) > 1