-- 3081 NEW RECEIVING FROM STOCK x4 (OH) - Stock Label and Receiving File

SELECT
	p.PIN AS quickbooks_item_cogsaccnt,
	vpw.Location AS quickbooks_item_assetaccnt,
	vpw.Location AS 'quickbooks_item_assetaccnt ',
	od.Quantity,
	od.QtyOnHold,
	od.QtyShipped,
	IFNULL(p.Name, od.ProductName) AS ProductName,
	CONCAT('*', od.OrderID, '*') AS OrderID,
	CASE
		WHEN o.ShippingMethodID IN('142', '950', '951', '1058') THEN 'F'
		ELSE ''
	END AS ShippingMethodID,
	od.ProductNote,
	'' AS SKU_total,
	od.OrderDetailID,
	p.PIN AS 'quickbooks_item_cogsaccnt ',
	o.OrderID AS orderID,
	CONCAT(o.ShipFirstName, ' ', o.ShipLastName) AS ToName,
	o.ShipCompanyName AS ToCompany,
	o.ShipAddress1 AS ToAddress1,
	o.ShipAddress2 AS ToAddress2,
	CONCAT(o.ShipCity, ', ', o.ShipState, ' ', o.ShipPostalCode) AS ShipCityStateZip,
	LineCount.NumLines AS TotalLines,
	CONCAT(o.BillingFirstName, ' ', o.BillingLastName) AS BillName,
	o.BillingCompanyName AS BillingCompany,
	o.BillingAddress1 AS BillingAddress1,
	o.BillingAddress2 AS BillingAddress2,
	o.CustomerID,
	c.EmailAddress,
	o.Order_Comments AS OrderNotes,
	CONCAT(o.BillingCity, ', ', o.BillingState, ' ', o.BillingPostalCode) AS BillingCityStateZip,
	'' AS Warehouses,
	vol.ProductCode AS productcode,
	CASE WHEN p.HasPhoto > 0 THEN '' ELSE 'NO_PRODUCT_IMAGE' END AS photos_cloned_from,
	'' AS Photo_Url,
	'' AS Picture_found,
	CASE
		WHEN LineCount.NumLines = 1 Then '998'
		ELSE '999'
	END AS QtyOnPackingSlip

FROM orderdetails od
JOIN orders o ON o.orderID = od.orderID
JOIN customers c ON o.CustomerID = c.CustomerID
JOIN products.client_map vol ON od.productcode = vol.ProductCode AND vol.Client = 'Volusion'
JOIN products.pins p ON p.PIN = vol.PIN
LEFT JOIN (
	SELECT vm.PIN, 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 = vol.PIN

JOIN (
	SELECT od.OrderID,
	SUM(CASE
			WHEN (
					od.QtyOnHold > 0 AND od.QtyShipped = 0 AND
					(
						od.Productnote LIKE '%STOCK OH%'
						OR od.Productnote NOT LIKE '% | %'
					)
				)
				OR	(QtyOnPackingSlip = 999 AND od.QtyOnHold = 0 AND od.QtyShipped > 0)
			THEN 1 ELSE 0
		END) AS NumLines
	FROM orderdetails od JOIN orders o ON od.OrderID = o.OrderID
	WHERE o.OrderStatus IN ('Processing', 'Ready to Ship')
		AND od.ProductCode NOT LIKE ('DSC-%')
 		AND od.ProductNote NOT LIKE '% DS %'
		AND od.ProductNote NOT LIKE 'NOO % | % STOCK %'
		AND od.ProductNote NOT LIKE '% ETA % | % STOCK %'
		AND od.ProductNote NOT LIKE '% SPC % | % STOCK %'
	GROUP BY od.OrderID
	HAVING SUM(CASE WHEN od.QtyOnHold > 0 AND
						(
							od.Productnote LIKE '%STOCK OH%'
							OR od.Productnote NOT LIKE '% | %'
						) THEN 1 ELSE 0 END) > 0
) AS LineCount ON od.OrderID = LineCount.OrderID

WHERE o.OrderStatus IN ('Processing', 'Ready to Ship')
AND od.Productnote LIKE '%STOCK OH%'
AND od.QtyOnHold > 0

ORDER BY vpw.Location ASC, p.PIN