SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

IF EXISTS( SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[dbo].[qryMMSReportData_InventoryActivity_CFA]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[qryMMSReportData_InventoryActivity_CFA]
GO

CREATE PROCEDURE [dbo].[qryMMSReportData_InventoryActivity_CFA]
	@EntityID BIGINT,
	@StartDate DATETIME,
	@EndDate DATETIME,
	@StartCountId BIGINT,
	@EndCountId BIGINT,
	@ItemIdList VARCHAR(MAX) = ''
AS
SET NOCOUNT ON;

-- Set Dates
-- Commented out Assinging startDate and EndDate the opening and closing times from the trading day.
-- CFA is looking for Opening & Closing Inventory to match the Actual Monthly Counts

DECLARE @NetSales AS MONEY

DECLARE @tempBusinessDayList TABLE
		(BusinessDay DATE)

	Insert into @tempBusinessDayList
		select * from  [fn_GetCalendarDayListForTradingDayRange](@EntityID, @StartDate, @EndDate)
		
		

SELECT
    @NetSales = SUM([Sales_Excl_GST_Excl])
FROM [dbo].[tbTransactionSummary] TS WITH(NOLOCK)
WHERE
	TS.[EntityID] = @EntityID
AND TS.[TransactionDate] >= @StartDate
AND TS.[TransactionDate] < @EndDate

DECLARE @Entity VARCHAR(50) = (SELECT Entity FROM tbEntity (NOLOCK) WHERE EntityId = @EntityId)

-- Prepare items table

IF OBJECT_ID('tempdb..#IAR_Items') IS NOT NULL
    TRUNCATE TABLE #IAR_Items
ELSE
	CREATE TABLE #IAR_Items (
		[ItemID] BIGINT PRIMARY KEY,
		[SubGroup] VARCHAR(250),
		[ItemGroupID] BIGINT,
		[ItemGroup] VARCHAR(50),
		[ItemCode] VARCHAR(50),
		[InventoryUnit] VARCHAR(50),
		[PurchaseUnit] VARCHAR(50),
		[ReportingUnit] VARCHAR(50),
		[Description] VARCHAR(50),
		[UnitCost] MONEY,
		[OpeningStock] FLOAT,
		[ClosingStock] FLOAT,
		[IncludedInOpeningStockCount] BIT,
		[IncludedInClosingStockCount] BIT,
		[CountedBetweenStartEnd] BIT,
		[SortOrder] INT
	)

INSERT INTO #IAR_Items
SELECT DISTINCT
    EI.[ItemID],
	[SubGroup] = ISNULL(SG.[Description], 'Undefined'),
	IG.[ItemGroupID],
	IG.[ItemGroup],
	I.[ItemCode],
	I.[InventoryUnit],
	I.[PurchaseUnit],
	[ReportingUnit] = RUOM.[UomName],
	I.[Description],
	[UnitCost] = COALESCE(EIC.[InventoryUnitCost], scClosing.[UnitCost], scOpening.[UnitCost], EI.[InventoryUnitCost], 0),
	[OpeningStock] = scOpening.[StockCount],
	[ClosingStock] = scClosing.[StockCount],
	[IncludedInOpeningStockCount] = CASE WHEN scOpening.[ItemID] IS NULL THEN 0 ELSE 1 END,
	[IncludedInClosingStockCount] = CASE WHEN scClosing.[ItemID] IS NULL THEN 0 ELSE 1 END,
	[CountedBetweenStartEnd] = CASE WHEN CBSE.[ItemID] IS NULL THEN 0 ELSE 1 END,
	EI.[SortOrder]
FROM [dbo].[tbEntityItem] EI WITH(NOLOCK)
INNER JOIN [dbo].[tbItem] I WITH(NOLOCK) ON I.[ItemID] = EI.[ItemID]
INNER JOIN [dbo].[tbItemGroup] IG WITH(NOLOCK) ON IG.[ItemGroupID] = I.[GroupID]
LEFT JOIN [dbo].[tbItemSubGroup] SG WITH(NOLOCK) ON SG.[ItemSubGroupID] = I.[SubGroupID]
LEFT JOIN [dbo].[tbUnitOfMeasure] RUOM WITH(NOLOCK) ON RUOM.[UnitOfMeasureID] = EI.[ReportingUnitID]
LEFT JOIN [dbo].[tbTransactionStockCount] scOpening WITH(NOLOCK) ON scOpening.[EntityID] = @EntityID AND scOpening.[ItemID] = EI.[ItemID] AND scOpening.[TransactionStockCountLocationID] = @StartCountId
LEFT JOIN [dbo].[tbTransactionStockCount] scClosing WITH(NOLOCK) ON scClosing.[EntityID] = @EntityID AND scClosing.[ItemID] = EI.[ItemID] AND scClosing.[TransactionStockCountLocationID] = @EndCountId
LEFT JOIN (
	SELECT TOP 1 WITH TIES
		EIC.[ItemID],
		EIC.[InventoryUnitCost]
	FROM [dbo].[tbEntityItemCost] EIC WITH(NOLOCK)
	WHERE
		EIC.[EntityID] = @EntityID
	AND CONVERT(DATETIME, CONVERT(VARCHAR(16), EIC.[CostChangeDate], 120)) <= @EndDate
	ORDER BY
		ROW_NUMBER() OVER(PARTITION BY EIC.[ItemID] ORDER BY EIC.[CostChangeDate] DESC)
) EIC ON EIC.[ItemID] = I.[ItemID]
LEFT JOIN (
	SELECT DISTINCT
		TSC.[ItemID]
	FROM [dbo].[tbTransactionStockCount] TSC WITH(NOLOCK)
	WHERE
		TSC.[EntityID] = @EntityID
	AND TSC.[ApplyDate] BETWEEN @StartDate AND @EndDate
) CBSE ON CBSE.[ItemID] = I.[ItemID]
WHERE
    EI.[EntityID] = @EntityID
AND EI.[InUse] = 1
AND I.[UPTDepletionItem] = 0
AND I.[ItemType] <> 'BOM'
AND (@ItemIdList = '' OR EI.[ItemID] IN (SELECT [value] FROM [dbo].[fn_Split](@ItemIdList, ',')))
ORDER BY
	EI.[ItemID] ASC

-- Prepare ItemIDs table used to limit rows being aggregated

IF OBJECT_ID('tempdb..#IAR_ItemIDs') IS NOT NULL
    TRUNCATE TABLE #IAR_ItemIDs
ELSE
	CREATE TABLE #IAR_ItemIDs (
		[ItemID] BIGINT PRIMARY KEY
	)

INSERT INTO #IAR_ItemIDs
SELECT
	[ItemID]
FROM #IAR_Items
ORDER BY
	[ItemID] ASC

-- Prepare YCF table used to reverse the YieldCorrectionFactor applied to tbTransactionItem.Quantity

IF OBJECT_ID('tempdb..#IAR_YCF') IS NOT NULL
    TRUNCATE TABLE #IAR_YCF
ELSE
	CREATE TABLE #IAR_YCF (
		[ItemID] BIGINT,
		[ChangeDateFrom] DATETIME,
		[ChangeDateTo] DATETIME,
		[YieldCorrectionFactor] FLOAT,
		PRIMARY KEY( [ItemID] ASC, [ChangeDateFrom] ASC, [ChangeDateTo] ASC )
	)

INSERT INTO #IAR_YCF
SELECT
	[ItemID],
	[ChangeDateFrom] = [ChangeDate],
	[ChangeDateTo] = '3000-12-31',
	[YieldCorrectionFactor]
FROM (
    -- Determine YCF between @StartDate (exclusive) and @EndDate (inclusive)
	SELECT
	    [N] = 1,
		YCF.[ItemID],
		YCF.[ChangeDate],
		YCF.[YieldCorrectionFactor]
	FROM [dbo].[tbEntityItemYieldCorrectionFactor] YCF WITH(NOLOCK)
	INNER JOIN #IAR_ItemIDs I WITH(NOLOCK) ON I.[ItemID] = YCF.[ItemID]
	WHERE
		[EntityID] = @EntityID
	AND YCF.[ChangeDate] > @StartDate
	AND YCF.[ChangeDate] <= @EndDate

	UNION ALL
	--- Determine YCF at @StartDate
	SELECT
		[N] = ROW_NUMBER() OVER(PARTITION BY [ItemID] ORDER BY [ChangeDate] DESC),
		[ItemID],
		[ChangeDate],
		[YieldCorrectionFactor]
	FROM (
		SELECT
			YCF.[ItemID],
			YCF.[ChangeDate],
			YCF.[YieldCorrectionFactor]
		FROM [dbo].[tbEntityItemYieldCorrectionFactor] YCF WITH(NOLOCK)
	    INNER JOIN #IAR_ItemIDs I WITH(NOLOCK) ON I.[ItemID] = YCF.[ItemID]
		WHERE
			YCF.[EntityID] = @EntityID
		AND YCF.[ChangeDate] <= @StartDate

		UNION ALL

		SELECT
			I.[ItemID],
			[ChangeDate] = CONVERT(DATETIME, '1753-01-01'),
			[YieldCorrectionFactor] = 1
		FROM #IAR_ItemIDs I WITH(NOLOCK)
	) X
) Y
WHERE
    [N] = 1
ORDER BY
    [ItemID] ASC,
	[ChangeDate] DESC

-- Update [ChangeDateTo] to reflect the next [ChangeDateFrom]
-- e.g. 1753-01-01 -> 2017-05-03 -> 3000-12-31

UPDATE YCF SET
    [ChangeDateTo] = Y.[ChangeDateFrom]
FROM #IAR_YCF YCF
INNER JOIN (
	SELECT
		[N],
		[ItemID],
		[ChangeDateFrom]
	FROM (
		SELECT
			[N] = ROW_NUMBER() OVER(PARTITION BY [ItemID] ORDER BY [ChangeDateFrom] DESC),
			[ItemID],
			[ChangeDateFrom]
		FROM #IAR_YCF
	) X
	WHERE
		[N] > 1
) X ON X.[ItemID] = YCF.[ItemID] AND X.[ChangeDateFrom] = YCF.[ChangeDateFrom]
INNER JOIN (
    SELECT
	    [N] = ROW_NUMBER() OVER(PARTITION BY [ItemID] ORDER BY [ChangeDateFrom] DESC),
		[ItemID],
		[ChangeDateFrom]
	FROM #IAR_YCF
) Y ON Y.[ItemID] = X.[ItemID] AND Y.[N] = (X.[N] - 1)

IF OBJECT_ID('tempdb..#qry_ListInventoryDetails') IS NOT NULL
    TRUNCATE TABLE #qry_ListInventoryDetails
ELSE
	CREATE TABLE #qry_ListInventoryDetails
	(
		ItemID bigint not null primary key,
		OpeningValue float,
		OpeningQty float,
		ClosingValue float,
		ClosingQty float,
		PurchaseValue float,
		PurchaseQty float,
		PurchaseExclReturnsValue float,
		PurchaseExclReturnsQty float,
		PurchaseReturnsValue float,
		PurchaseReturnsQty float,
		TransferInValue float,
		TransferInQty float,
		TransferOutValue float,
		TransferOutQty float,
		BOMMakeValue float,
		BOMMakeQty float,
		BOMUseValue float,
		BOMUseQty float,
		SalesValue float,
		SalesQty float,
		WasteValue float,
		WasteQty float,
		SamplingValue float,
		SamplingQty float,
		OtherAdjustValue float,
		OtherAdjustQty float,
		DispatchValue float,
		DispatchQty float,
		FinishedWasteValue float,
		FinishedWasteQty float,
		RawWasteValue float,
		RawWasteQty float,
		Expected_Qty float,
		Expected_Amt float,
		Variance_Qty float,
		Variance_Amt float
	); 

INSERT INTO #qry_ListInventoryDetails EXEC [dbo].[qry_ListInventoryDetails] @EntityId, @StartDate, @EndDate

SELECT
    [EntityID] = @EntityId,
	[Entity] = @Entity,
	I.[ItemID],
	I.[UnitCost],
	I.[ItemGroupID],
	I.[ItemGroup],
	I.[ItemCode],
	I.[InventoryUnit],
	I.[PurchaseUnit],
	I.[ReportingUnit],
	I.[Description],
	[OpeningStock] = COALESCE(I.[OpeningStock], S.[OpeningStock], 0),
	[Purchase] = ISNULL(S.[Purchase], 0),
	[TransferIn] = ISNULL(S.[TransferIn], 0),
	[TransferOut] = ISNULL(S.[TransferOut], 0),
	[ClosingStock] = COALESCE(I.[ClosingStock], S.[ClosingStock], 0),
	[Used] = ISNULL(S.[Used], 0),
	[UsedCost] = ISNULL(S.[UsedCost], 0),
	[Sold] = ISNULL(TI.[SoldQuantity], 0) + ISNULL(A.[Sampling], 0),
	[SoldCost] = ISNULL(TI.[SoldQuantity] * I.[UnitCost], 0) + ISNULL(A.[SamplingQuantity] * I.[UnitCost], 0),
	[Waste] = ISNULL(A.[Waste], 0),
	[WasteCost] = ISNULL(A.[WasteQuantity] * I.[UnitCost], 0),
	[NetSales] = @NetSales,
	I.[SortOrder],
	I.[IncludedInOpeningStockCount],
	I.[IncludedInClosingStockCount],
	I.[SubGroup]
FROM #IAR_Items I WITH(NOLOCK)
LEFT JOIN (
	SELECT
		T.[ItemID],
		[OpeningStock] = SUM(ISNULL(T.OpeningQty, 0)),
		[Purchase] = SUM(ISNULL(T.PurchaseQty, 0)),
		[TransferIn] = SUM(ISNULL(T.TransferInQty, 0)),
		[TransferOut] = SUM(ISNULL(-T.TransferOutQty, 0)),
		[ClosingStock] = SUM(ISNULL(T.ClosingQty, 0)),
		[Used] = (
			-- Opening +
			SUM(ISNULL(T.OpeningQty, 0)) +
			-- Purchase +
			SUM(ISNULL(T.PurchaseQty, 0)) +
			-- TransferIn -
			SUM(ISNULL(T.TransferInQty, 0)) +
			-- TransferOut -
			SUM(ISNULL(-T.TransferOutQty, 0)) -
			-- Closing
			SUM(ISNULL(T.ClosingQty, 0))
		),
		[UsedCost] = (
			-- Opening +
			SUM(ISNULL(T.OpeningValue, 0)) +
			-- Purchase +
			SUM(ISNULL(T.[PurchaseValue], 0)) +
			-- TransferIn -
			SUM(ISNULL(T.TransferInValue, 0)) +
			-- TransferOut -
			SUM(ISNULL(-T.TransferOutValue, 0)) -
			-- Closing
			SUM(ISNULL(T.ClosingValue, 0))
		)
	FROM #qry_ListInventoryDetails T WITH(NOLOCK)
	GROUP BY
		T.[ItemID]
) S ON I.[ItemID] = S.[ItemID]
LEFT JOIN (
	SELECT
		T.[ItemID],
		[SoldQuantity] = ISNULL(SUM(T.[Quantity] / YCF.[YieldCorrectionFactor]), 0.0) -- !!! Cost = * ei.InventoryUnitCost
	FROM [dbo].[tbTransactionItem] T WITH(NOLOCK)
	INNER JOIN #IAR_YCF YCF WITH(NOLOCK) ON(
		YCF.[ItemID] = T.[ItemID]
	AND T.[TransactionTime] > YCF.[ChangeDateFrom]
	AND T.[TransactionTime] <= YCF.[ChangeDateTo]
	)
	WHERE
		T.[EntityID] = @EntityId
		AND T.[BusinessDay] in (SELECT BusinessDay from @tempBusinessDayList)
		AND [TransactionTime] BETWEEN @StartDate AND @EndDate
	GROUP BY
		T.[ItemID]
) TI ON I.[ItemID] = TI.[ItemID]
LEFT JOIN (
	SELECT
		T.[ItemID],
		[Waste] = ISNULL(SUM(CASE WHEN [StockAdjustType] IN( 'TRX_INTERNAL_USE_TYPE', 'TRX_SPOILAGE_TYPE' ) THEN [StockAdjust_Qty] ELSE NULL END), 0),
		[WasteQuantity] = ISNULL(SUM(CASE WHEN [StockAdjustType] IN( 'TRX_INTERNAL_USE_TYPE', 'TRX_SPOILAGE_TYPE' ) THEN [StockAdjust_Qty] ELSE NULL END), 0),
		[Sampling] = ISNULL(SUM(CASE WHEN [StockAdjustType] = 'TRX_SAMPLING_TYPE' THEN [StockAdjust_Qty] ELSE NULL END), 0),
		[SamplingQuantity] = ISNULL(SUM(CASE WHEN [StockAdjustType] = 'TRX_SAMPLING_TYPE' THEN [StockAdjust_Qty] ELSE NULL END), 0)
	FROM [dbo].[tbTransactionStockAdjust] T WITH(NOLOCK)
	INNER JOIN #IAR_ItemIDs I WITH(NOLOCK) ON I.[ItemID] = T.[ItemID]
	WHERE
		T.[EntityID] = @EntityId
	AND [ApplyDate] BETWEEN @StartDate AND @EndDate
	AND [StockAdjustType] IN( 'TRX_INTERNAL_USE_TYPE', 'TRX_SPOILAGE_TYPE', 'TRX_SAMPLING_TYPE' )
	GROUP BY
		T.[ItemID]
) A ON A.ItemID = I.ItemID
WHERE
	(I.[IncludedInOpeningStockCount] = 1 OR I.[IncludedInClosingStockCount] = 1)
AND ( -- Only if there is Purchase or Transfer or Waste or StockCount for that Item.
	S.[Purchase] <> 0
OR  S.[TransferIn] <> 0
OR  S.[TransferOut] <> 0
OR  A.[Waste] <> 0
OR  I.[CountedBetweenStartEnd] = 1
)
ORDER BY
	I.[SortOrder],
	I.[Description]
GO
