SET @StartDate = '2025-03-01';
SET @EndDate = '2025-09-30';
SET @LeadTimeInDays = 3;
SET @SalesWindowInDays = 90;
SET @MinimumOrderCount = 5;
SET @PONumber = '10072025';
SET @VendorCode = 'HUS';
SET @AccountNumber = '750321';

-- Select only the order ids we want
WITH orderids AS (
	SELECT 
		OrderID
    FROM custopayted.orders
    WHERE 
		OrderDate >= @StartDate 
        AND (OrderDate <= @EndDate + INTERVAL 1 DAY)
		AND OrderStatus != 'Cancelled'
),

-- Select the productcodes that we want
products AS (
	SELECT 
		ProductCode, 
		SUM(Quantity) AS TotalQuantity, 
		COUNT(DISTINCT OrderDetailID) AS TotalCount, 
		ROUND((SUM(Quantity * Vendor_Price)/SUM(Quantity)),2) AS Avg_Cost, 
		COUNT(DISTINCT OrderID) AS TotalOrders
    FROM custopayted.orderdetails
    WHERE 
		OrderID IN (SELECT OrderID FROM orderids)
		AND Quantity > 0
		AND Vendor_Price > 0
    GROUP BY ProductCode
    HAVING TotalOrders >= @MinimumOrderCount
)



SELECT 
	pins.PIN AS Inventory_Location,
	pins.ProductCode,
	pins.Name AS ProductName, 
	IFNULL(_VPW.Stock,0) AS Inv_QtyOnHand,
	IFNULL(UnappliedStock.Total_Unshipped, 0) AS Unapplied_Stock,
	-- This CASE statement selects the estimated number of stock which will be used in the given period
	-- unless there are fewer than that in stock. In that case, the lower number (the inventory count)
	-- will be used instead. This will hopefully prevent unnecessary over-buying.
	CASE 
		WHEN ROUND((products.TotalQuantity * (@LeadTimeInDays/@SalesWindowInDays)),0) > IFNULL(_VPW.Stock,0) THEN IFNULL(_VPW.Stock,0) 
        ELSE ROUND((products.TotalQuantity * (@LeadTimeInDays/@SalesWindowInDays)),0) 
	END AS Stock_Attrition,
	products.TotalQuantity AS Period_Sales,
	-- Average cost per item in the given period.
	products.Avg_Cost,
	pins.Cost,
	products.TotalOrders AS Times_Sold,
	ROUND((CAST(products.TotalQuantity AS Decimal)/CAST(products.TotalCount AS DECIMAL)),2) AS Items_Per_Line,
	-- This is the order quantity recommendation algorithim.
	-- Begin by taking the current available inventory level (on shelf minus unapplied inventory)
	-- and subtracting the expected number of item which will sell in the given period.
	-- This is the total inventory which is expected to be there once the given order is placed.
	-- Subtract this from the total sold in the given period, and that should leave you with
	-- a reasonable number of items to order.
	-- For more detailed reasoning on the CASE statements, read above to see their analogues.
	@AccountNumber AS Account, 
	@PONumber AS PONumber,
	vendor_map.MFG_SKU AS SuppliersSKU,
	products.TotalQuantity - (
		IFNULL(_VPW.Stock,0)
		- CASE 
			WHEN MAX(UnappliedStock.Total_Unshipped) IS NULL THEN 0 
            ELSE MAX(UnappliedStock.Total_Unshipped) 
		END
		- CASE 
			WHEN ROUND((products.TotalQuantity * (@LeadTimeInDays/@SalesWindowInDays)),0) > IFNULL(_VPW.Stock,0) THEN IFNULL(_VPW.Stock,0) 
            ELSE ROUND((products.TotalQuantity * (@LeadTimeInDays/@SalesWindowInDays)),0) 
		END
	) AS Ordered

FROM products.pins
JOIN products.vendor_map ON vendor_map.ProductCode = pins.ProductCode AND vendor_map.VendorCode = @VendorCode
LEFT JOIN products._VPW
	on pins.PIN = _VPW.MFG_SKU
LEFT JOIN (
	SELECT pins.PIN, JustApplied.Total_Unshipped
	FROM custopayted.orders
	JOIN custopayted.orderdetails
		ON orders.OrderID = orderdetails.OrderID
	JOIN products.pins
		ON orderdetails.ProductCode = pins.ProductCode
	JOIN (
		SELECT pins.PIN AS Location, SUM(orderdetails.Quantity) AS Total_Unshipped
		FROM custopayted.orderdetails
		JOIN products.pins
			ON orderdetails.ProductCode = pins.ProductCode
		WHERE orderdetails.ProductNote LIKE '%STOCK%'
		AND orderdetails.Quantity > 0
		AND orderdetails.QtyShipped = 0
		GROUP BY pins.PIN
	) AS JustApplied
		ON pins.PIN = JustApplied.Location
	WHERE JustApplied.Total_Unshipped > 0
	GROUP BY pins.PIN, JustApplied.Total_Unshipped
) AS UnappliedStock
	ON pins.PIN = UnappliedStock.PIN	
JOIN products
	ON pins.ProductCode = products.ProductCode
GROUP BY Inventory_Location, pins.ProductCode, ProductName, Inv_QtyOnHand, SuppliersSKU
ORDER BY Inventory_Location ASC