-- Make sure to Remove Duplicates in Excel because certain items may have
-- inconsistent stock quantities and throw in multiple references for ordering.
-- DECLARE @StartDate VARCHAR(16), @EndDate VARCHAR(16), @LeadTimeInDays DECIMAL, @SalesWindowInDays DECIMAL, @MinimumOrderCount INT, @PONumber VARCHAR(16) 
SET @StartDate = '2024-08-27';
SET @EndDate = '2024-10-27';
SET @LeadTimeInDays = 10;
SET @SalesWindowInDays = 60;
SET @MinimumOrderCount = 5;
SET @PONumber = '11082024';

SELECT
client_map.PIN AS Inventory_Location,
MAX(client_map.ProductCode) AS ProductCode,
MAX(pins.Name) AS ProductName,
IFNULL(_VPW.Stock,0) AS Inv_QtyOnHand,
-- This CASE statement selects 0 for the total unshipped if there were none in the query.
CASE WHEN MAX(UnappliedStock.Total_Unshipped) IS NULL THEN 0 ELSE MAX(UnappliedStock.Total_Unshipped) END AS Unapplied_Stock,
'0' AS Bissell_On_Order,
'0' AS Dist2_On_Order,
-- 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((SUM(od.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) > IFNULL(_VPW.Stock,0) THEN IFNULL(_VPW.Stock,0) ELSE
	ROUND((SUM(od.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) END AS Stock_Attrition,
SUM(od.Quantity) AS Period_Sales,
-- Average cost per item in the given period.
ROUND((SUM(od.Quantity * od.Vendor_Price)/SUM(od.Quantity)),2) AS Avg_Cost,
MAX(pins.Cost) AS Bissell_Cost,
COUNT(od.Quantity) AS times_sold,
ROUND((CAST(SUM(od.Quantity) AS Decimal)/CAST(COUNT(od.Quantity) 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.
'750321' AS Account,
@PONumber AS PONumber,
'' AS SuppliersManf,
' ' AS ItemName,
SUM(od.Quantity) - (
		IFNULL(_VPW.Stock,0)
		- CASE WHEN MAX(UnappliedStock.Total_Unshipped) IS NULL THEN 0 ELSE MAX(UnappliedStock.Total_Unshipped) END
		- CASE WHEN ROUND((SUM(od.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) > IFNULL(_VPW.Stock,0) THEN IFNULL(_VPW.Stock,0) ELSE ROUND((SUM(od.Quantity) * (@LeadTimeInDays/@SalesWindowInDays)),0) END
	) AS Ordered

FROM products.client_map
join products.pins
	on client_map.PIN = pins.PIN
LEFT JOIN products._VPW
	on client_map.PIN = _VPW.MFG_SKU
LEFT OUTER JOIN (
	SELECT client_map.PIN, SUM(od.Quantity) AS Total_Unshipped
	FROM orderdetails od
	JOIN products.client_map
	ON od.ProductCode = client_map.ProductCode
	WHERE od.ProductNote LIKE '%STOCK%'
	AND od.Quantity > 0
	AND od.QtyShipped = 0
	GROUP BY client_map.PIN
	HAVING SUM(od.Quantity) > 0 -- We don't want what's already been applied
) AS UnappliedStock
	ON client_map.PIN = UnappliedStock.PIN
JOIN orderdetails od 
	ON client_map.ProductCode = od.ProductCode
JOIN orders o 
	ON od.OrderID = o.OrderID

WHERE DATEDIFF(@StartDate , o.OrderDate) <= 0 AND DATEDIFF(@EndDate , o.OrderDate) >= 0 
	AND o.OrderStatus <> 'Cancelled' 
	AND od.Quantity != 0
	AND od.Vendor_Price != 0
	AND pins.VendorCodes LIKE '%bis%'
GROUP BY client_map.PIN, IFNULL(_VPW.Stock,0)
HAVING COUNT(od.Quantity) >= @MinimumOrderCount
ORDER BY client_map.PIN ASC