if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryMMSReportData_SampleSummary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qryMMSReportData_SampleSummary]
GO
CREATE PROCEDURE dbo.qryMMSReportData_SampleSummary
	@EntityID bigint,
	@StartDate datetime,
	@EndDate datetime,
	@AggregateTotals bit = 0
AS
	DECLARE @TradingDayOpen DATETIME
	DECLARE @TradingDayClose DATETIME

	SELECT @TradingDayOpen = DayOpen, @TradingDayClose = DayClose 
	FROM dbo.fn_GetTradingForCalendarDayRange(@EntityID, @StartDate, @EndDate)

	DECLARE @SamplingData Table
	(
		SalesItemId BIGINT,
		Source varchar(25),
		WasteQty float,
		FoodCost decimal(18,2),
		PaperCost decimal(18,2),
		CorporateFoodCost decimal (18, 2),
		CorporatePaperCost decimal (18, 2)
	)

	DECLARE @WasteType VARCHAR(50) = 'Sales Item Sampling'

	;WITH ItemClassGroup AS 
	(
		SELECT TOP 1 WITH TIES ItemClassId, GroupID
		FROM tbItem WITH (NOLOCK)
		WHERE ISNULL(ItemClassId, 0) > 0
		GROUP BY ItemClassId, GroupID
		ORDER BY ROW_NUMBER() Over (Partition By ItemClassId Order By COUNT(*) DESC)
	),
	ItemsAndItemClasses AS
	(
		select TransactionWasteID,
			SUM(FoodCost) AS FoodCost,
			SUM(PaperCost) AS PaperCost 
		from (	SELECT TransactionWasteID,
					Case When ig.ItemGroup = 'Food' then StockAdjust_amt else 0 End AS FoodCost,
					Case When ig.ItemGroup = 'Paper' then StockAdjust_amt else 0 End AS PaperCost
				FROM tbTransactionStockAdjust tsa WITH(NOLOCK)
					JOIN tbItem i with(nolock) on tsa.ItemID = i.ItemID 
					JOIN tbitemGroup ig with(nolock) on ig.ItemGroupID = i.GroupID 
				WHERE tsa.EntityID = @EntityID
					AND ig.ItemGroup in ( 'Food', 'Paper') 
					AND TransactionWasteID is not null
				
				-- this functionality seems to be unused by all customers, as there is no code which inserts into this table:
				UNION ALL 

				Select TransactionWasteID,
					Case When ig.ItemGroup = 'Food' then Cost else 0 End FoodCost,
					Case When ig.ItemGroup = 'Paper' then Cost else 0 End PaperCost
				From tbTransactionItemClassAdjust ta WITH (NOLOCK)
					JOIN ItemClassGroup i ON i.ItemClassID = ta.ItemClassID
					JOIN tbItemGroup ig with (nolock) ON ig.ItemGroupID = i.GroupID
				WHERE ta.EntityID = @EntityID 
					AND ig.ItemGroup in ('Food', 'Paper')
					AND ta.TransactionWasteID IS NOT NULL
			) as tw 
		group by TransactionWasteID
	)
	INSERT INTO @SamplingData (SalesItemId, Source, WasteQty, FoodCost, PaperCost, CorporateFoodCost, CorporatePaperCost)
	SELECT w.TypeId,
		CASE WHEN w.[Source] = 'POS' THEN 'POS' ELSE 'MX' END AS Source,
		SUM(w.WasteQty) AS WasteQty,
		SUM(ItemsAndItemClasses.FoodCost) AS FoodCost,
		SUM(ItemsAndItemClasses.PaperCost) AS PaperCost,
		SUM(Food.SalesCost * w.WasteQty) AS CorporateFoodCost,
		SUM(Paper.SalesCost * w.WasteQty) AS CoroporatePaperCost
	FROM tbTransactionWaste w WITH(NOLOCK)
		LEFT JOIN ItemsAndItemClasses on w.TransactionWasteID = ItemsAndItemClasses.TransactionWasteID
		OUTER APPLY (SELECT TOP 1 SalesCost FROM tbEntitySalesItemCost esic WITH (NOLOCK) WHERE esic.EntityID = @EntityID AND esic.SalesItemId = w.TypeID AND esic.CostType = 1 ORDER BY esic.CostChangeDate DESC) Food
		OUTER APPLY (SELECT TOP 1 SalesCost FROM tbEntitySalesItemCost esic WITH (NOLOCK) WHERE esic.EntityID = @EntityID AND esic.SalesItemId = w.TypeID AND esic.CostType = 2 ORDER BY esic.CostChangeDate DESC) Paper
	WHERE EntityID = @EntityID 
		AND WasteDate >= @TradingDayOpen
		AND WasteDate < @TradingDayClose
		AND w.[Type] = @WasteType
	GROUP BY w.TypeID, CASE WHEN w.[Source] = 'POS' THEN 'POS' ELSE 'MX' END

	IF @AggregateTotals = 1
	BEGIN
		SELECT
			'Aggregate' as Description, 
			0 as ItemCode,
			'Aggregate' as Category,
			'Aggregate' as Type, 
			SUM(CASE Source WHEN 'POS' THEN wasteqty ELSE 0 END) AS RegisterCount,
			SUM(CASE Source WHEN 'MX' THEN wasteqty ELSE 0 END) AS AdjustedCount,
			SUM(foodcost) AS FoodCost,
			SUM(papercost) AS PaperCost,
			SUM(CorporateFoodCost) AS CorporateFoodCost,
			SUM(CorporatePaperCost) AS CorporatePaperCost
		FROM @SamplingData s
			JOIN tbSalesItem si WITH(NOLOCK) on s.SalesItemId = si.SalesItemID 
			JOIN tbItemSalesCategory isc WITH(NOLOCK) on si.SalesCategoryID = isc.ItemSalesCategoryID
		WHERE isc.ItemSalesCategory <> 'Test Product Giveaways'
	END
	ELSE
	BEGIN
		SELECT
			si.Description, 
			si.ItemCode,
			Category = isc.ItemSalesCategory, 
			Type = @WasteType,
			isnull((select SUM(wasteqty) from @SamplingData s2 where s2.SalesItemId = si.SalesItemId and s2.Source='POS'), 0) as RegisterCount,
			isnull((select SUM(wasteqty) from @SamplingData s2 where s2.SalesItemId = si.SalesItemId and s2.Source='MX'), 0) as AdjustedCount,
			isnull((select sum(foodcost) from @SamplingData s2 where s2.SalesItemId = si.SalesItemId), 0) as FoodCost,
			isnull((select sum(papercost) from @SamplingData s2 where s2.SalesItemId = si.SalesItemId), 0) as PaperCost,
			isnull((select sum(CorporateFoodCost) from @SamplingData s2 where s2.SalesItemId = si.SalesItemId), 0) as CorporateFoodCost,
			isnull((select sum(CorporatePaperCost) from @SamplingData s2 where s2.SalesItemId = si.SalesItemId), 0) as CorporatePaperCost
		FROM @SamplingData s
			JOIN tbSalesItem si WITH(NOLOCK) on s.SalesItemId = si.SalesItemID 
			JOIN tbItemSalesCategory isc WITH(NOLOCK) on si.SalesCategoryID = isc.ItemSalesCategoryID
		WHERE isc.ItemSalesCategory <> 'Test Product Giveaways'
		GROUP BY si.SalesItemId, si.[Description], ItemCode, isc.ItemSalesCategory
	END
GO
