
IF  EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[qryMMSInventory_ItemActivityDetails]') AND TYPE IN (N'P', N'PC'))
	DROP PROCEDURE [qryMMSInventory_ItemActivityDetails]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[qryMMSInventory_ItemActivityDetails]
	@entityId AS BIGINT
	, @itemId AS BIGINT
	, @startDate AS DATETIME
	, @endDate AS DATETIME
	, @includeGiveaways AS BIT
AS

BEGIN

	DECLARE @ItemTransactions TABLE ( 
	Id BIGINT IDENTITY(1,1) PRIMARY KEY
	, ItemId BIGINT
	, LineTypeId INT
	, LineType VARCHAR(255)
	, Details VARCHAR(255)
	, Amount FLOAT
	, AmountIn FLOAT
	, AmountOut FLOAT
	, TransactionDate DATETIME
	, PopupMessage VARCHAR(512)
	, ShowAmount BIT
	, InOnHandTotal INT
	, BusinessDay DATE )

	DECLARE @currentStoreTime DATETIME = [dbo].[fn_LocalTime](@entityId, GETUTCDATE(), GETDATE())

	DECLARE @isToday BIT
	SET @isToday = CASE WHEN DATEDIFF(DAY, @endDate, @currentStoreTime) = 0 THEN 1 ELSE 0 END
	
	DECLARE @endDateTime DATETIME
	SET @endDateTime = CASE @isToday WHEN 1 THEN @currentStoreTime ELSE DATEADD(SECOND, 86399, CAST(@endDate AS DATETIME)) END

	-- START BEGINING balances.

	-- Add opening balance by getting the closing stock qty at start date -1 ... or
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 0 -- LineTypeID (0:Open;1:Close;2:Discontinued;3:DailySales;4:Receiving;5;TransIn;6:TransOut;7:Sample;8;Waste;9:Counts;10:UptUsage;11:Giveaways)
		, 'BeginningInventoryRowKey' -- LineType
		, '' -- Details
		, sbd.ClosingStockQty --[Amount]
		, sbd.ClosingStockQty -- AmountIn
		, 0 -- AmountOut
		, @startDate -- TransactionDate
		, '' -- PopupMessage
		, 0 -- ShowAmount
		, 1 -- InOnHandTotal
		, sbd.BusinessDay -- BusinessDay
	FROM [dbo].[tbStockBalanceDaily] sbd WITH(NOLOCK)
	WHERE sbd.EntityID = @entityId
	AND sbd.ItemId = @itemId
	AND sbd.BusinessDay = DATEADD(DAY, -1, @startDate)
	AND DATEPART(WEEKDAY, @startDate) != 2

	-- Add opening balance by getting the stock qty of the current date if today is a monday (no movement on sundays)
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 0 -- LineTypeID (0:Open;19:Close;2:Discontinued;3:DailySales;4:Receiving;5;TransIn;6:TransOut;7:Sample;8;Waste;9:Counts;10:UptUsage)
		, 'BeginningInventoryRowKey' -- LineType
		, '' -- Details
		, sbd.ClosingStockQty --[Amount]
		, sbd.ClosingStockQty -- AmountIn
		, 0 -- AmountOut
		, @startDate -- TransactionDate
		, '' -- PopupMessage
		, 0 -- ShowAmount
		, 1 -- InOnHandTotal
		, sbd.BusinessDay
	FROM [dbo].[tbStockBalanceDaily] sbd WITH(NOLOCK)
	WHERE sbd.EntityID = @entityId
	AND sbd.ItemId = @itemId
	AND sbd.BusinessDay = @startDate
	AND DATEPART(WEEKDAY, @startDate) = 2

	-- Add opening balance from the last available day before the start date if there is no record for start date -1.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 0 -- LineTypeID
		, 'BeginningInventoryRowKey' -- LineType
		, '' -- Details
		, sbd.ClosingStockQty --[Amount]
		, sbd.ClosingStockQty -- AmountIn
		, 0 -- AmountOut
		, @startDate -- TransactionDate
		, '' -- PopupMessage
		, 0 -- ShowAmount
		, 1 -- InOnHandTotal
		, sbd.BusinessDay -- BusinessDay
	FROM [dbo].[tbStockBalanceDaily] sbd WITH(NOLOCK)
	WHERE sbd.EntityID = @entityId
	AND sbd.ItemId = @itemId
	AND sbd.BusinessDay = ( SELECT MAX(BusinessDay) FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityID = @entityId AND ItemId = @itemId )
	AND NOT EXISTS (SELECT 1 FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityID = @entityId AND ItemId = @itemId AND BusinessDay = DATEADD(DAY, -1, @startDate))

	-- START Check if the item was discontinued.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal)
	SELECT @itemId -- ItemID 
		, 2 -- LineTypeID
		, 'ItemDiscontinuedRowKey' -- LineType
		, '' -- Details
		, 0 -- [Amount]
		, 0 -- AmountIn
		, 0 -- AmountOut
		, ei.DiscontinueDate -- TransactionDate
		, '' -- PopupMessage
		, 0 -- ShowAmount
		, 0 -- InOnHandTotal
	FROM [dbo].[tbEntityItem] ei WITH(NOLOCK)
	WHERE ei.EntityID = @entityId
	AND ei.ItemID = @itemId
	AND (ei.DiscontinueDate >= @startDate AND ei.DiscontinueDate <= @endDateTime)



    DECLARE @tempBusinessDayList TABLE
		(BusinessDay DATE)

	Insert into @tempBusinessDayList
		select * from  [fn_GetCalendarDayListForTradingDayRange](@EntityID,DATEADD(DAY, 1, @startDate) , @endDate)

	-- START Daily Sales (temporarily insert all the sales rows)
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, -3 -- LineTypeID
		, 'TmpDailySalesRowKey' -- LineType
		, '' -- Details
		, -ti.Quantity -- [Amount]
		, 0 -- AmountIn
		, ti.Quantity -- AmountOut
		, ti.TransactionTime -- TransactionDate
		, '' -- PopupMessage
		, 1 -- ShowAmount
		, 1 -- InOnHandTotal
		, ti.BusinessDay -- BusinessDay
	FROM [dbo].[tbTransactionItem] ti WITH(NOLOCK)	
	WHERE ti.EntityID = @entityId
	AND ti.ItemId = @itemId
	AND ti.BusinessDay In (SELECT BusinessDay from @tempBusinessDayList)

	-- START > Receivings <

	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 4 -- LineTypeID
		,'ReceivingRowKey' -- LineType
		, 'OrderNumber=' + CAST(trl.iTransaction_num AS VARCHAR(10)) + 
			CASE WHEN tso.CreatedFromOrder > 0 THEN '|OriginalOrderNumber=' + CAST(tso.CreatedFromOrder AS VARCHAR(10)) ELSE '' END +
			CASE WHEN tsod.OriginalReceivedQty IS NOT NULL AND tsod.OriginalReceivedQty != trl.PurchaseAmt THEN '|OriginalReceivedQty=' + CAST(tsod.OriginalReceivedQty AS VARCHAR(10)) ELSE '' END + 
			CASE WHEN ISNULL(tso.DistributionGoodsReceivedBy, '') != '' THEN '|ReceivedBy=' + tso.DistributionGoodsReceivedBy ELSE '' END -- Details
		, trl.InventoryAmt -- [Amount]
		, trl.InventoryAmt -- AmountIn
		, 0 -- AmountOut
		, trl.ApplyDate -- TransactionDate
		, CASE
			WHEN tsod.OriginalReceivedQty IS NOT NULL AND tsod.OriginalReceivedQty != trl.PurchaseAmt AND tso.CreatedFromOrder > 0 THEN 'ReceivingRowPopupApplyDateAndAmountChangeMessageKey'
			WHEN tsod.OriginalReceivedQty IS NOT NULL AND tsod.OriginalReceivedQty != trl.PurchaseAmt AND tso.CreatedFromOrder = 0 THEN 'ReceivingRowOriginalAmountPopupRowKey'
			WHEN tso.CreatedFromOrder > 0 THEN 'ReceivingRowApplyDateChangedPopupRowKey'
		ELSE 'ReceivingRowPopupMessageKey' END -- PopupMessage
		, 1 -- ShowAmount
		, 1 -- InOnHandTotal
		, trl.BusinessDay -- BusinessDay
	FROM [dbo].[tbTransactionReceiveLog] trl WITH(NOLOCK)
	JOIN [dbo].[tbTransactionSupplyOrderDetail] tsod WITH(NOLOCK) ON (trl.TransactionSupplyOrderDetailID = tsod.TransactionSupplyOrderDetailID)
	JOIN [dbo].[tbTransactionSupplyOrder] tso WITH(NOLOCK) ON (trl.iTransaction_num = tso.iTransaction_num)
	WHERE trl.EntityID = @entityId
	AND trl.ItemID = @itemId
	AND trl.BusinessDay >= @startDate AND trl.BusinessDay <= trl.BusinessDay
	AND NOT EXISTS ( SELECT 1 FROM [dbo].[tbTransactionSupplyOrder] WITH(NOLOCK) WHERE CreatedFromOrder = tsod.iTransaction_num)

	-- START > TRANSFERS <

	-- Transfers into the store.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal)
	SELECT @itemId -- ItemID 
		, 5 -- LineTypeID
		, CASE tt.TransferStatus
			 WHEN 'Transfered' THEN 'TransferInPendingRowKey'
			ELSE 'TransferInRowKey'
			END -- LineType
		, 'TransferId=' + CAST(tt.TransactionTransferID AS VARCHAR(10)) + 
			'|FromStore=' + fe.Entity +
			CASE WHEN ISNULL(tt.TransferedBy, '') != '' THEN '|FromUser=' + SUBSTRING(ISNULL(tt.TransferedBy,''), 0, PATINDEX('%- [0-9]%', ISNULL(tt.TransferedBy,''))) ELSE '' END +
			CASE WHEN ISNULL(tt.ReceivedBy, '') != '' THEN '|ToUser=' + SUBSTRING(ISNULL(tt.ReceivedBy, ''), 0, PATINDEX('%- [0-9]%', ISNULL(tt.ReceivedBy, ''))) ELSE '' END -- Details
		, ttd.TransferQty -- [Amount]
		, ttd.TransferQty -- AmountIn
		, 0 -- AmountOut
		, tt.ReceiveStoreDateTime -- TransactionDate
		, 'TransferInRowPopupMessageKey' -- PopupMessage
		, 1 -- ShowAmount
		, 1 -- InOnHandTotal
	FROM [dbo].[tbTransactionTransferDetail] ttd WITH(NOLOCK)
	JOIN [dbo].[tbTransactionTransfer] tt WITH(NOLOCK) ON (tt.TransactionTransferID = ttd.TransactionTransferID)
	JOIN [dbo].[tbEntity] fe WITH(NOLOCK) ON (fe.EntityID = tt.TransferFromEntityID)
	WHERE tt.TransferToEntityID = @entityId
	AND ttd.ItemID = @itemId
	AND tt.ReceiveStoreDateTime >= @startDate AND tt.ReceiveStoreDateTime <= @endDateTime
	AND tt.TransferStatus != 'Declined'

	-- Transfers out of the store.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal)
	SELECT @itemId -- ItemID 
		, 6 -- LineTypeID
		, CASE tt.TransferStatus
			 WHEN 'Transfered' THEN 'TransferOutPendingRowKey'
			ELSE 'TransferOutRowKey'
			END -- LineType
		, 'TransferId=' + CAST(tt.TransactionTransferID AS VARCHAR(10)) + 
			'|ToStore=' + te.Entity +
			CASE WHEN ISNULL(tt.TransferedBy, '') != '' THEN '|FromUser=' + SUBSTRING(ISNULL(tt.TransferedBy,''), 0, PATINDEX('%- [0-9]%', ISNULL(tt.TransferedBy,''))) ELSE '' END +
			CASE WHEN ISNULL(tt.ReceivedBy, '') != '' THEN '|ToUser=' + SUBSTRING(ISNULL(tt.ReceivedBy, ''), 0, PATINDEX('%- [0-9]%', ISNULL(tt.ReceivedBy, ''))) ELSE '' END -- Details
		, -(ttd.TransferQty) -- [Amount]
		, 0 -- AmountIn
		, ttd.TransferQty -- AmountOut
		, tt.TransferStoreDateTime -- TransactionDate
		, 'TransferOutRowPopupMessageKey' -- PopupMessage
		, 1 -- ShowAmount
		, 1 -- InOnHandTotal
	FROM [dbo].[tbTransactionTransferDetail] ttd WITH(NOLOCK)
	JOIN [dbo].[tbTransactionTransfer] tt WITH(NOLOCK) ON (tt.TransactionTransferID = ttd.TransactionTransferID)
	JOIN [dbo].[tbEntity] te WITH(NOLOCK) ON (te.EntityID = tt.TransferToEntityID)
	WHERE tt.TransferFromEntityID = @entityId
	AND ttd.ItemID = @itemId
	AND tt.TransferStoreDateTime >= @startDate AND tt.TransferStoreDateTime <= @endDateTime		
	AND tt.TransferStatus != 'Declined'

	-- Get cancelled transfers (they won't have a balance in tbStockBalanceDaily afaik)
	-- NOT Required as per AC

	-- START > ADJUSTMENTS <	
	-- Sample transactions.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal)
	SELECT @itemId -- ItemID 
		, 7 -- LineTypeID
		, 'SampleRowKey' -- LineType
		, CASE WHEN ISNULL(tw.Source, '') != '' THEN 'Source=' + tw.Source ELSE 'Source=InFORM' END +
			CASE WHEN tsa.TransactionWasteID > 0 THEN '|TransactionWasteId=' + CAST(tsa.TransactionWasteID AS VARCHAR(20)) ELSE '' END + 
			CASE WHEN ISNULL(tsa.Reason, '') != '' THEN '|Reason=' + tsa.Reason ELSE '|Reason=Samples' END + 
			CASE WHEN u.UserId IS NOT NULL THEN '|User=' + u.FirstName + ' ' + u.LastName ELSE CASE WHEN tsa.ClerkName IS NOT NULL THEN '|User=' + tsa.ClerkName ELSE '' END END -- Details
		, -(tsa.StockAdjust_qty) -- [Amount]
		, 0 -- AmountIn
		, tsa.StockAdjust_qty -- AmountOut
		, tsa.ApplyDate -- TransactionDate
		, CASE WHEN tsa.TransactionWasteID > 0 THEN 'SamplesRowPopupMessageKey' 
			ELSE 'SamplesNoReferenceRowPopupMessageKey' END -- PopupMessage
		, 1 -- ShowAmount
		, 1 -- InOnHandTotal
	FROM [dbo].[tbTransactionStockAdjust] tsa WITH(NOLOCK)
	LEFT JOIN [dbo].[tbUser] u WITH(NOLOCK) ON (u.UserID = tsa.ClerkID)
	LEFT JOIN [dbo].[tbTransactionWaste] tw WITH(NOLOCK) ON (tw.TransactionWasteID = tsa.TransactionWasteID)
	WHERE tsa.EntityID = @entityId
	AND tsa.ItemID = @itemId
	AND tsa.ApplyDate >= @startDate AND tsa.ApplyDate <= @endDateTime
	AND StockAdjustType = 'TRX_SAMPLING_TYPE'

	-- Waste transactions.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal)
	SELECT @itemId -- ItemID 
		, 8 -- LineTypeID
		, 'WasteRowKey' -- LineType
		, CASE WHEN ISNULL(tw.Source, '') != '' THEN 'Source=' + tw.Source ELSE 'Source=InFORM' END +
			CASE WHEN tsa.TransactionWasteID > 0 THEN '|TransactionWasteId=' + CAST(tsa.TransactionWasteID AS VARCHAR(20)) ELSE '' END + 
			CASE WHEN tsa.Reason IS NOT NULL THEN '|Reason=' + tsa.Reason ELSE '' END + 
			CASE WHEN u.UserId IS NOT NULL THEN '|User=' + u.FirstName + ' ' + u.LastName ELSE CASE WHEN tsa.ClerkName IS NOT NULL THEN '|User=' + tsa.ClerkName ELSE '' END END -- Details
		, -(tsa.StockAdjust_qty) -- [Amount]
		, 0 -- AmountIn
		, tsa.StockAdjust_qty -- AmountOut
		, tsa.ApplyDate -- TransactionDate
		, CASE WHEN tsa.TransactionWasteID IS NULL THEN 'WasteNoReferenceRowPopupMessageKey' 
			ELSE 'WasteRowPopupMessageKey' END -- PopupMessage
		, 1 -- ShowAmount
		, 1 -- InOnHandTotal
	FROM [dbo].[tbTransactionStockAdjust] tsa WITH(NOLOCK)
	LEFT JOIN [dbo].[tbUser] u WITH(NOLOCK) ON (u.UserID = tsa.ClerkID)
	LEFT JOIN [dbo].[tbTransactionWaste] tw WITH(NOLOCK) ON (tw.TransactionWasteID = tsa.TransactionWasteID)
	WHERE tsa.EntityID = @entityId
	AND tsa.ItemID = @itemId
	AND tsa.ApplyDate >= @startDate AND tsa.ApplyDate <= @endDateTime
	AND StockAdjustType = 'TRX_SPOILAGE_TYPE'

	-- Inventory Counts.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 9 -- LineTypeID
		, CASE tscl.StockCountTypeID
			WHEN 0 THEN 'CountSpotRowKey'
			WHEN 1 THEN 'CountDailyRowKey'
			WHEN 2 THEN 'CountWeeklyRowKey'
			WHEN 3 THEN 'CountPeriodicRowKey'
			WHEN 4 THEN 'CountMonthlyRowKey'
			ELSE 'CountCustomRowKey' -- Custom Counts 5-7
			END -- LineType 
		, 'User=' + (u.FirstName + ' ' + u.LastName) + 
			CASE WHEN (tscl.StockCountTypeID IN (5,6,7)) THEN '|CustomCountName=' + ISNULL(cc.CustomName, 'Custom') ELSE '' END +
			CASE WHEN (tscl.StockCountTypeID = 0 AND tscl.StockCountSubTypeID = 1 AND tso.iTransaction_num IS NOT NULL) THEN '|ReferenceNumber=' + CAST(tso.iTransaction_num AS VARCHAR(20)) ELSE '' END -- Details
		, tsc.StockCount -- [Amount]
		, 0 -- AmountIn
		, 0 -- AmountOut
		, tsc.ApplyDate -- TransactionDate
		, CASE 
			WHEN (tscl.StockCountTypeID IN (5,6,7)) THEN 'CountRowCustomCountPopupMessageKey'
			WHEN tscl.ReferenceNumber IS NULL THEN 'CountRowNoReferencePopupMessageKey'
			ELSE 'CountRowPopupMessageKey'
			END -- PopupMessage
		, 0 -- ShowAmount
		, 1 -- InOnHandTotal
		, tsc.BusinessDay -- BusinessDay
	FROM [dbo].[tbTransactionStockCount] tsc WITH(NOLOCK)
	JOIN [dbo].[tbTransactionStockCountLocation] tscl WITH(NOLOCK) ON (tscl.TransactionStockCountLocationID = tsc.TransactionStockCountLocationID)
	JOIN [dbo].[tbUser] u WITH(NOLOCK) ON (u.UserId = tsc.ClerkID)
	LEFT JOIN [dbo].[tbCustomCountType] cc WITH(NOLOCK) ON (cc.CountTypeEnum = tscl.StockCountTypeID AND cc.EntityID = tscl.EntityId)
	LEFT JOIN [dbo].[tbTransactionSupplyOrder] tso WITH(NOLOCK) ON (tso.TransactionSalesOrderID = tscl.ReferenceNumber)
	WHERE tsc.EntityID = @entityId
	AND tsc.ItemID = @itemId
	AND tsc.BusinessDay >= @startDate AND tsc.BusinessDay <= @endDate
	AND tscl.AutoGenerated = 0
	AND tscl.ApplyStatus > 0

	-- START UPT Usage

	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal)
	SELECT @itemId -- ItemID 
		, 10 -- LineTypeID
		, 'UptUsageRowKey' -- LineType 
		, '' -- Details
		, -(tsc.ApplyAdjust_qty) -- [Amount]
		, 0 -- AmountIn
		, tsc.ApplyAdjust_qty -- AmountOut
		, tsc.ApplyDate -- TransactionDate
		, '' -- PopupMessage
		, 1 -- ShowAmount
		, 1 -- InOnHandTotal
	FROM [dbo].[tbTransactionStockCount] tsc WITH(NOLOCK)
	JOIN [dbo].[tbTransactionStockCountLocation] tscl WITH(NOLOCK) ON (tscl.TransactionStockCountLocationID = tsc.TransactionStockCountLocationID)
	WHERE tsc.EntityId= @entityId
	AND tsc.ItemId = @itemId
	AND tsc.ApplyDate >= @startDate AND tsc.ApplyDate <= @endDateTime
	AND tscl.AutoGenerated = 1
	

	-- START Closing balance

	-- Add closing balance for a day in the past that has an existing record we can use, or ...
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 19 -- LineTypeID
		, 'ClosingInventoryRowKey' -- LineType
		, '' -- Details
		, sbd.ClosingStockQty -- [Amount]
		, 0 -- AmountIn
		, 0 -- AmountOut
		, DATEADD(SECOND, 86399, @endDate) -- TransactionDate
		, '' -- PopupMessage
		, 0 -- ShowAmount
		, 0 -- InOnHandTotal
		, sbd.BusinessDay -- BusinessDay
	FROM [dbo].[tbStockBalanceDaily] sbd WITH(NOLOCK)
	WHERE sbd.EntityID = @entityId
	AND sbd.ItemId = @itemId
	AND sbd.BusinessDay = @endDate

	-- Add closing balance for the last found past date, or ....
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 19 -- LineTypeID
		, 'ClosingInventoryRowKey' -- LineType
		, '' -- Details
		, sbd.ClosingStockQty -- [Amount]
		, 0 -- AmountIn
		, 0 -- AmountOut
		, DATEADD(SECOND, 86399, @endDate) -- TransactionDate
		, '' -- PopupMessage
		, 0 -- ShowAmount
		, 0 -- InOnHandTotal
		, sbd.BusinessDay -- BusinessDay
	FROM [dbo].[tbStockBalanceDaily] sbd WITH(NOLOCK)
	WHERE sbd.EntityID = @entityId
	AND sbd.ItemId = @itemId
	AND NOT EXISTS (SELECT 1 FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityID = @entityId AND ItemId = @itemId AND BusinessDay = @endDate)
	AND EXISTS (SELECT 1 FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityId = @entityId AND ItemId = @itemId )
	AND sbd.BusinessDay = (SELECT MAX(BusinessDay) FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityID = @entityId AND ItemId = @itemId AND BusinessDay < @endDate)
	AND DATEDIFF(DAY, @endDate, GETDATE()) != 0

	-- Add closing balance for the current date (where the tbStockBalanceDaily does not already exist)
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal)
	SELECT @itemId -- ItemID 
		, 19 -- LineTypeID
		, 'ClosingInventoryRowKey' -- LineType
		, '' -- Details
		, iq.fOnHand_qty -- [Amount]
		, 0 -- AmountIn
		, 0 -- AmountOut
		, @currentStoreTime -- TransactionDate
		, '' -- PopupMessage
		, 0 -- ShowAmount
		, 0 -- InOnHandTotal
	FROM [dbo].[tbItemQty] iq WITH(NOLOCK)
	WHERE iq.EntityID = @entityId
	AND iq.ItemID = @itemId
	AND NOT EXISTS (SELECT 1 FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityID = @entityId AND ItemId = @itemId AND BusinessDay = @endDate)
	AND EXISTS (SELECT 1 FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityId = @entityId AND ItemId = @itemId )
	AND DATEDIFF(DAY, @endDate, GETDATE()) = 0 AND @endDate < GETDATE()


	-- Get the conversion rate
	DECLARE	@reportingUnitConversion FLOAT = 1

	SELECT @reportingUnitConversion = CASE 
			WHEN u.fConversionRate IS NOT NULL THEN u.fConversionRate
			WHEN ur.fConversionRate IS NOT NULL AND ur.fConversionRate != 0 THEN (1 / ur.fConversionRate)
		ELSE 1
	END 
	FROM [dbo].[tbItem] i WITH(NOLOCK)
	JOIN [dbo].[tbEntityItem] ei WITH(NOLOCK) ON (ei.ItemId = i.ItemId AND ei.EntityId = @entityID)
	JOIN [dbo].[tbUnitOfMeasure] fu WITH(NOLOCK) ON (fu.UomName = i.InventoryUnit)
	LEFT OUTER JOIN [dbo].[tbUnitOfMeasure] tu WITH(NOLOCK) ON (tu.UnitOfMeasureID = ei.ReportingUnitID)
	LEFT OUTER JOIN [dbo].[tbUnit] u ON (u.FromUnit = fu.UnitOfMeasureID AND u.ToUnit = tu.UnitOfMeasureID)
	LEFT OUTER JOIN [dbo].[tbUnit] ur ON (ur.FromUnit = tu.UnitOfMeasureID AND ur.ToUnit = fu.UnitOfMeasureID)
	WHERE i.ItemID = @itemId

	-- Reset the partitions in order to get our running total reset when a count has reset the values
	DECLARE @countsKeys TABLE (TransactionId BIGINT, CountID BIGINT)
	INSERT INTO @countsKeys 
	SELECT Id
		, CountId = (ROW_NUMBER() OVER (ORDER BY TransactionDate)) + 1
	FROM @ItemTransactions
	WHERE LineTypeId = 9

	UPDATE t
	SET InOnHandTotal = c.CountID
	FROM @ItemTransactions t
	JOIN @countsKeys c ON (c.TransactionId = t.Id) 

	-- Update the partitions from the original values.
	DECLARE @partition int = 1
		, @partitionStartDate DATETIME
		, @partitionEndDate DATETIME
		, @partitionDate DATETIME
		, @done INT = 1

	SET @endDate = DATEADD(SECOND, 86399, @endDate) -- Add time protion (23h59 59s) for our partition calculations
	SET @partitionEndDate = CASE WHEN DATEDIFF(DAY, @endDate, @currentStoreTime) = 0 THEN @currentStoreTime ELSE @endDate END

	WHILE (@done != 0)
	BEGIN
	
		SELECT @partitionStartDate = MIN(TransactionDate) FROM @ItemTransactions WHERE InOnHandTotal = @partition
	 
		SELECT @done = ISNULL(MAX(InOnHandTotal), 0)
			, @partitionDate = ISNULL(MIN(TransactionDate), @partitionEndDate)
		FROM @ItemTransactions WHERE InOnHandTotal = @partition + 1

		UPDATE @ItemTransactions
		SET InOnHandTotal = @partition
		WHERE TransactionDate >= @partitionStartDate AND TransactionDate <= @partitionDate
		AND LineTypeId NOT IN (19, 9) -- Don't shuffle close and counts

		SET @partition = @partition + 1
	END

	-- Group the temporary Daily Sales rows by BusinessDay, and split by counts if any are present on the BusinessDay. 
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, AmountIn, AmountOut, TransactionDate, PopupMessage, ShowAmount, InOnHandTotal, BusinessDay)
	SELECT @itemId -- ItemID 
		, 3 -- LineTypeID
		, 'DailySalesRowKey' -- LineType
		, '' -- Details
		, SUM(ti.Amount) -- [Amount]
		, 0 -- AmountIn
		, SUM(ABS(ti.Amount)) -- AmountOut
		, MAX(ti.TransactionDate) -- TransactionDate
		, '' -- PopupMessage
		, 1 -- ShowAmount
		, ti.InOnHandTotal -- InOnHandTotal
		, ti.BusinessDay -- BusinessDay
	FROM @ItemTransactions ti
	WHERE ti.LineTypeId = -3
	GROUP BY ti.InOnHandTotal, BusinessDay

	-- Delete all the temporary ungrouped daily sales records (LineTypeId -3) after the groups lines have been inserted above.
	DELETE FROM @ItemTransactions
	WHERE LineTypeId = -3

	-- Update the last occurance of a daily sale for the BusinessDay to be at 23h59 and ignore today.
	UPDATE @ItemTransactions
	SET TransactionDate = DATEADD(SECOND, 86399, CAST(BusinessDay AS DATETIME))
	WHERE Id IN (
		SELECT MAX(Id) FROM @ItemTransactions
		WHERE LineTypeId = 3
		AND DATEDIFF(DAY, GETDATE(), BusinessDay) != 0
		GROUP BY BusinessDay
	)

	-- Select all items for the built up table and calculate the running onhand total
	SELECT Id
		, ItemId
		, LineTypeId
		, LineType
		, ISNULL(Details, '') AS Details
		, ROUND((Amount * @reportingUnitConversion), 4) AS Amount
		, ROUND((AmountIn * @reportingUnitConversion), 4) AS AmountIn
		, ROUND((AmountOut * @reportingUnitConversion), 4) AS AmountOut
		, TransactionDate
		, PopupMessage
		, ShowAmount
		, SUM(ROUND((Amount * @reportingUnitConversion), 4)) OVER (PARTITION BY InOnHandTotal ORDER BY TransactionDate, Id) AS OnHand
	FROM @ItemTransactions
	ORDER BY TransactionDate ASC, LineTypeId

END
GO
