-- 3154 Jay-Add ALL Missing orders to receiving

SELECT
od.ProductCode,
scv.MFG_SKU AS EAN,
esc.MFG_SKU AS UPC_code,
p.PIN AS cogs,
vpw.Location AS location,
od.Quantity,
od.QtyOnHold,
od.QtyShipped,
p.Name AS ProductName,
CONCAT('*', od.OrderID, '*') AS Barcode,
CASE
	WHEN ShippingMethodID IN ('142', '950', '951', '1058') THEN 'F'
	ELSE ''
END AS one_rate,
od.ProductNote,
'' AS SKU_total,
od.OrderDetailID,
CASE
	WHEN p.VendorCodes LIKE '%GAR%' THEN
		CASE
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'HM-%' AND gar.MFG_SKU LIKE '%HM-%' THEN CONCAT('HM,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'HM-%' AND gar.MFG_SKU LIKE '%TTI-%' THEN CONCAT('TTI,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'RG-%' AND gar.MFG_SKU LIKE '%HM-%' THEN CONCAT('HM,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'RG-%' AND gar.MFG_SKU LIKE '%TTI-%' THEN CONCAT('TTI,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'RY-%' AND gar.MFG_SKU LIKE '%HM-%' THEN CONCAT('HM,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'RY-%' AND gar.MFG_SKU LIKE '%TTI-%' THEN CONCAT('TTI,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'WLB-%' THEN CONCAT('WL,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'MTD-%' THEN CONCAT('MTD,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'TPC-%' THEN CONCAT('TEC,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'ECH-%' THEN CONCAT('EC,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'KOH-%' THEN CONCAT('KH,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
		-- WHEN vol.Vendor_PartNo != '' AND vol.ProductCode LIKE 'PLN-%' THEN ('HOP,' + RIGHT(vol.Vendor_PartNo, LEN(vol.Vendor_PartNo)-CHARINDEX('-',vol.Vendor_PartNo)))
			WHEN vol.ProductCode LIKE 'PLN-%' THEN
				CASE
					WHEN gar.MFG_SKU IS NOT NULL AND gar.MFG_SKU != '' THEN CONCAT('HOP,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
					ELSE CONCAT('HOP,', SUBSTRING(vol.ProductCode, POSITION('-' IN vol.ProductCode)+1))
				END
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'HUS-%' THEN CONCAT('HOP,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'AGR-%' THEN CONCAT('AGR,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'TUF-%' THEN CONCAT('TUF,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'HG-%' THEN CONCAT('HG,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'GEN-%' THEN CONCAT('GEN,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'DRP-%' THEN CONCAT('DR,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'HON-%' THEN CONCAT('HON,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'NGK-%' THEN CONCAT('NGK,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'CHP-%' THEN CONCAT('CP,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'DEL-%' THEN CONCAT('DEL,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'SUB-%' THEN CONCAT('SUB,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'ARD-%' THEN CONCAT('ARD,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'ZA-%' THEN CONCAT('ZA,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'TI-%' THEN CONCAT('TI,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'PAR-%' THEN CONCAT('PAR,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			WHEN gar.MFG_SKU IS NOT NULL AND vol.ProductCode LIKE 'AME-%' THEN CONCAT('AME,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
		-- vendor partno section
			-- WHEN gar.MFG_SKU LIKE 'OR-%' THEN CONCAT('OR,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			-- WHEN gar.MFG_SKU LIKE 'SI-%' THEN CONCAT('SI,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
			-- WHEN gar.MFG_SKU LIKE 'CLE-%' THEN CONCAT('CLE,', SUBSTRING(gar.MFG_SKU, POSITION('-' IN gar.MFG_SKU)+1))
		-- add new manfs here
			ELSE gar.MFG_SKU
		END
	WHEN p.VendorCodes LIKE '%PWD%' THEN pwd.MFG_SKU
	WHEN p.VendorCodes LIKE '%CHV%' THEN chv.MFG_SKU
	WHEN p.VendorCodes LIKE '%HRZ%' THEN hrz.MFG_SKU
	WHEN vol.ProductCode LIKE 'HON-%' THEN vol.ProductCode
	ELSE ''
END AS Vendor_PartNo,
o.OrderID AS OrderID,
CONCAT(o.ShipFirstName, ' ', o.ShipLastName) AS ToName,  -- This was cast as VarChar before concatenating. Check that if it fails.
o.ShipCompanyName AS ToCompany,
REPLACE(o.ShipAddress1, CHAR(16), '') AS ToAddress1,
REPLACE(o.ShipAddress2, CHAR(16), '') AS ToAddress2,
CONCAT(o.ShipCity, ', ', o.ShipState, ' ', o.ShipPostalCode) AS ShipCityStateZip, -- This was cast as VarChar before concatenating. Check that if it fails.
CASE WHEN StockExists.Multi > 0 THEN LineCount.NumLines +1 ELSE LineCount.NumLines END AS TotalLines,
CONCAT(o.BillingFirstName, ' ', o.BillingLastName) AS BillName, -- This was cast as VarChar before concatenating. Check that if it fails.
o.BillingCompanyName AS BillingCompany,
REPLACE(o.BillingAddress1, CHAR(16), '') AS BillingAddress1,
REPLACE(o.BillingAddress2, CHAR(16), '') AS BillingAddress2,
o.CustomerID,
c.EmailAddress,
o.Order_Comments AS OrderNotes,
CONCAT(o.BillingCity, ', ', o.BillingState, ' ', o.BillingPostalCode) AS BillingCityStateZip,
p.VendorCodes AS vendor_code, --
CASE WHEN p.HasPhoto > 0 THEN '' ELSE 'NO_PRODUCT_IMAGE' END AS Photo_URL,
od.Vendor_Price AS vendor_cost,
'' AS vendor_productnote,
'' AS wh,
'' AS time_received,
'1000-01-01 00:00:00' AS export_time


FROM orderdetails od
JOIN orders o ON o.orderid = od.orderid
JOIN products.client_map vol ON od.productcode = vol.ProductCode AND vol.Client = 'Volusion'
JOIN products.pins p ON p.PIN = vol.PIN
JOIN customers c ON o.CustomerID = c.CustomerID
LEFT JOIN (
	SELECT vm.PIN, pw.MFG_SKU, pw.Location
	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 vol.PIN = scv.PIN AND scv.VendorCode = 'SCV'
LEFT JOIN products.vendor_map esc ON vol.PIN = esc.PIN AND esc.VendorCode = 'ESC'
LEFT JOIN products.vendor_map gar ON vol.PIN = gar.PIN AND gar.VendorCode = 'GAR'
LEFT JOIN products.vendor_map chv ON vol.PIN = chv.PIN AND chv.VendorCode = 'CHV'
LEFT JOIN products.vendor_map pwd ON vol.PIN = pwd.PIN AND pwd.VendorCode = 'PWD'
LEFT JOIN products.vendor_map hrz ON vol.PIN = hrz.PIN AND hrz.VendorCode = 'HRZ'

JOIN (
	SELECT o.OrderID, SUBSTRING(od.ProductNote, LOCATE(' ', od.ProductNote)+1, 8) AS ProductNote, COUNT(*) AS NumLines
	FROM orderdetails od JOIN orders o ON od.OrderID = o.OrderID
	WHERE o.orderstatus IN ('Processing', 'Ready to Ship')
	AND od.ProductNote NOT LIKE '%DS%'
	-- AND od.ProductNote IS NOT NULL
	-- AND od.ProductNote != ''
	AND od.QtyOnHold > 0
	AND od.QtyShipped = 0
	GROUP BY o.OrderID, SUBSTRING(od.ProductNote, LOCATE(' ', od.ProductNote)+1, 8)
) AS LineCount ON od.OrderID = LineCount.OrderID AND SUBSTRING(od.ProductNote, LOCATE(' ', od.ProductNote)+1, 8) = LineCount.ProductNote

JOIN (
	SELECT o.OrderID, SUM(CASE WHEN Productnote LIKE 'STOCK%' THEN 1 ELSE 0 END) AS Multi
	FROM orderdetails od JOIN orders o ON od.OrderID = o.OrderID
	WHERE o.orderstatus IN ('Processing', 'Ready to Ship')
	AND od.ProductNote NOT LIKE '%DS%'
	-- AND od.ProductNote IS NOT NULL
	-- AND od.ProductNote != ''
	AND od.QtyOnHold > 0
	AND od.QtyShipped = 0
	GROUP BY o.OrderID
) AS StockExists ON od.OrderID = StockExists.OrderID

WHERE od.Productnote NOT LIKE '%STOCK%'
-- AND od.Productnote NOT LIKE '%LTBO%' removed on 11/3
-- AND od.Productnote NOT LIKE '%FNLA%'
AND od.Productnote NOT LIKE '%|%'
AND od.QtyShipped = 0
ORDER BY vol.ProductCode