-- UPDATE orderstatus to Processing
UPDATE orders
SET orderstatus = 'Processing'
WHERE orderstatus = 'Ready to Ship';

-- UPDATE orders that should be Shipped
UPDATE orders
SET orderstatus = 'Shipped'
WHERE orderid IN(
	SELECT * FROM (
	SELECT o.orderid
	FROM custopayted.orders o
	JOIN custopayted.orderdetails od ON od.orderid = o.orderid

	WHERE o.orderstatus NOT IN('Shipped', 'Cancelled', 'Partially Returned', 'Pending', 'Returned')
	AND od.productcode NOT LIKE 'DSC-%'

	GROUP BY o.orderid
	HAVING SUM(CASE WHEN od.qtyonpackingslip = 0 THEN 1 ELSE 0 END) = COUNT(od.orderdetailid)
	)sq
)
;

-- Now part of bring-backs.
UPDATE products.receiving_archive SET Shipped = 'N' WHERE Shipped IS NULL;





-- Receive Discounts
-- UPDATE orderdetails SET QtyOnPackingSlip = 0, QtyOnHold = 0, QtyShipped = 1
-- FROM orderdetails
-- WHERE ProductCode LIKE 'DSC%'
-- AND QtyOnPackingSlip IS NULL
-- AND QtyOnHold = 1
-- AND QtyShipped = 0
-- AND (
	-- Only this first condition needs to be met for truly untouched lines.
	-- QtyOnPackingSlip IS NULL -- OR
	-- QtyOnPackingSlip != 0 OR
	-- QtyOnHold != 0 OR
	-- QtyShipped != 1
-- )

-- UPDATE orderdetails SET QtyOnPackingSlip = 0
-- FROM orderdetails JOIN orders
-- ON orderdetails.OrderID = orders.OrderID
-- WHERE orderdetails.QtyOnPackingSlip = 999 AND orders.orderstatus IN ('Shipped')

-- UPDATE orderdetails SET QtyOnPackingSlip = NULL
-- FROM orderdetails JOIN orders
-- ON orderdetails.OrderID = orders.OrderID
-- WHERE orderdetails.QtyOnPackingSlip > 0 AND orders.orderstatus IN ('Shipped')

-- UPDATE orderdetails SET QtyOnHold = Quantity, QtyShipped = 0
-- FROM orderdetails JOIN orders
-- ON orderdetails.OrderID = orders.OrderID
-- WHERE (orderdetails.QtyOnPackingSlip > 0 OR orderdetails.QtyOnPackingSlip IS NULL) AND orders.orderstatus IN ('Shipped')

-- puts shipped lines back into processing that don't have a pipe, updated by Odis to bring in nulls because they were being excluded.
-- UPDATE orders SET orderstatus = 'Processing'
-- FROM orders JOIN orderdetails
-- ON orders.OrderID = orderdetails.OrderID
-- WHERE orders.orderstatus IN ('Shipped','Pending Shipment')
-- AND (orderdetails.QtyOnPackingSlip NOT IN (0,666) OR orderdetails.QtyOnPackingSlip IS NULL)
-- AND orderdetails.Quantity > 0
-- AND (orderdetails.Productnote NOT LIKE '% | %' OR orderdetails.Productnote IS NULL)

-- puts stock lines that aren't received but marked as shipped back into processing
--  UPDATE orders SET orderstatus = 'Processing'
-- FROM orders JOIN orderdetails
-- ON orders.OrderID = orderdetails.OrderID
-- WHERE orders.orderstatus IN ('Shipped','Pending Shipment')
-- AND (orderdetails.QtyOnPackingSlip NOT IN (0,666) OR orderdetails.QtyOnPackingSlip IS NULL)
-- AND orderdetails.Quantity > 0
-- AND orderdetails.Productnote LIKE 'null | STOCK %'