SELECT
o.OrderID,
MAX(LEFT(CONCAT(o.ShipFirstName, ' ', o.ShipLastName), 61)) AS ToName,
MAX(o.ShipCompanyName) AS ToCompany,
MAX(LEFT(o.ShipAddress1, 30)) AS ToAddress1,
MAX(LEFT(o.ShipAddress2, 30)) AS ToAddress2,
MAX(o.ShipCity) AS ToCity,
MAX(o.ShipState) AS ToState,
MAX(o.ShipPostalCode) AS ToPostalCode,
CASE
	WHEN MAX(o.ShipState) = 'GU' THEN 'Guam'
	ELSE MAX(o.ShipCountry)
END AS ToCountry,
MAX(od.Productcode) AS Productcode,
CASE
	WHEN MAX(o.ShipPhoneNumber) IS NULL OR MAX(o.ShipPhoneNumber) = '' THEN '8662432721'
	ELSE MAX(o.ShipPhoneNumber)
END AS ToPhoneNumber,
CASE
	WHEN (
		   MAX(o.ShipPostalCode)	REGEXP	'^71[0-2].*'
		OR MAX(o.ShipPostalCode)	LIKE	 '714%'
		OR MAX(o.ShipPostalCode)	LIKE	 '718%'
		OR MAX(o.ShipPostalCode)	REGEXP	'^7[3-6][0-9].*'
		OR MAX(o.ShipPostalCode)	REGEXP	'^79[0-9].*'
		OR MAX(o.ShipPostalCode)	LIKE	 '879%'
		OR MAX(o.ShipPostalCode)	REGEXP	'^88[0-3].*'
		) AND MAX(o.ShippingMethodID) NOT IN (141,142,950,951,952) THEN 777
	WHEN MAX(o.ShippingMethodID) = 0 AND MAX(o.Order_Entry_System) = 'MARKETPLACE' THEN 301
	ELSE MAX(o.ShippingMethodID)
END AS ShippingMethodID,
MAX(IFNULL(Customs.DeclaredValue, 0)) AS DeclaredValue,
0.00 AS Weight

FROM custopayted.orders o
JOIN custopayted.orderdetails od ON o.OrderID = od.OrderID
LEFT JOIN (
	SELECT od.OrderID, SUM(od.TotalPrice) AS DeclaredValue
	FROM orderdetails od
	WHERE od.ProductCode NOT LIKE ('DSC-%')
	-- AND (od.QtyOnPackingSlip IS NULL OR od.QtyOnPackingSlip = 0)
	AND od.QtyOnHold > 0
	GROUP BY od.OrderID
) AS Customs ON o.OrderID = Customs.OrderID

WHERE o.OrderStatus IN ('Processing','Pending Shipment','Ready to Ship','Overnight Hold')
	AND od.ProductCode NOT LIKE ('DSC-%')
	AND od.ProductNote IS NOT NULL
	AND od.ProductNote != ''
	AND o.ShippingMethodID != 502
--	  AND od.QtyOnHold > 0
--	  AND od.QtyShipped = 0
GROUP BY o.OrderID
ORDER BY o.OrderID ASC
