
-- easy scenario in past, all should be from sbd

declare @entityId bigint = 875
	, @itemId bigint = 2087
	, @startDate datetime = '20190621'
	, @endDate datetime = '20190703' 


select top 1000 i.Description, sbd.*
from tbStockBalanceDaily sbd with(nolock)
join tbItem i with(nolock) on (i.ItemID = sbd.ItemId)
where EntityID = @entityId
and sbd.ItemId = @itemId
and BusinessDay >= @startDate
and BusinessDay <= @endDate
order by 1 desc

go

select top 1000 * from tbStockBalanceDaily with(nolock)
where EntityID = 875 and ItemId = 1664

select top 500 b.ItemId, Description from tbStockBalanceDaily b with(nolock)
join tbEntityItem ei with(nolock) on (ei.EntityID = b.EntityID and ei.ItemID = b.ItemId and ei.InUse = 1)
join tbItem i with(nolock) on (i.ItemID = b.ItemId)
where b.EntityID = 875
Group By b.ItemId, Description
Order by b.ItemId 

select * from tbItem with(nolock)
where ItemID = 607

go


declare @entityId bigint = 875
	, @itemId bigint = 2087
	, @startDate datetime = '20190621'
	, @endDate datetime = '20190703' 


	DECLARE @ItemTransactions TABLE ( 
	Id BIGINT IDENTITY(1,1) PRIMARY KEY
	, ItemId BIGINT
	, LineTypeId INT
	, LineType VARCHAR(255)
	, Details VARCHAR(255)
	, Amount FLOAT
	, TransactionDate DATETIME
	, OnHand FLOAT )


	-- START Beginning and closing balances.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, TransactionDate, OnHand)
	SELECT @itemId -- ItemID 
		, 0 -- LineTypeID
		, 'BeginningInventoryRowKey' -- LineType
		, '' -- Details
		, 0 --[Amount]
		, @startDate -- TransactionDate
		, sbd.ClosingStockQty -- OnHand
	FROM [dbo].[tbStockBalanceDaily] sbd WITH(NOLOCK)
	WHERE sbd.EntityID = @entityId
	AND sbd.ItemId = @itemId
	AND sbd.BusinessDay = DATEADD(DAY, -1, @startDate)
	AND EXISTS (SELECT 1 FROM [dbo].[tbStockBalanceDaily] WITH(NOLOCK) WHERE EntityId = @entityId AND ItemID = @itemID AND BusinessDay = DATEADD(DAY, -1, @startDate))

	-- Add opening balance from the last day before the start date if there is no record.
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, TransactionDate, OnHand)
	SELECT @itemId -- ItemID 
		, 0 -- LineTypeID
		, 'BeginningInventoryRowKey' -- LineType
		, '' -- Details
		, 0 --[Amount]
		, @startDate -- TransactionDate
		, sbd.ClosingStockQty -- OnHand
	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))

	-- Add closing balance for a day in the past,
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, TransactionDate, OnHand)
	SELECT @itemId -- ItemID 
		, 1 -- LineTypeID
		, 'ClosingInventoryRowKey' -- LineType
		, '' -- Details
		, 0 -- [Amount]
		, DATEADD(SECOND, 86399, @endDate) -- TransactionDate
		, sbd.ClosingStockQty
	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
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, TransactionDate, OnHand)
	SELECT @itemId -- ItemID 
		, 1 -- LineTypeID
		, 'ClosingInventoryRowKey' -- LineType
		, '' -- Details
		, 0 -- [Amount]
		, DATEADD(SECOND, 86399, @endDate) -- TransactionDate
		, sbd.ClosingStockQty -- OnHand
	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
	INSERT INTO @ItemTransactions (ItemId, LineTypeId, LineType, Details, Amount, TransactionDate, OnHand)
	SELECT @itemId -- ItemID 
		, 1 -- LineTypeID
		, 'ClosingInventoryRowKey' -- LineType
		, '' -- Details
		, 0 -- [Amount]
		, GETDATE() -- TransactionDate
		, iq.fOnHand_qty -- OnHand
	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

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

	-- START (Next item)



	-- Select all items for the built up table
	SELECT *
	FROM @ItemTransactions
	ORDER BY TransactionDate
GO

-- 
select top 100 * from tbItem with(nolock)
where Description like '%chic%'

select * from tbItem with(nolock)
where itemId in (488, 2087)

select top 100 * from tbItemQty with(nolock)
where EntityID = 875 and ItemID = 2087

select top 10 * from tbEntityItem ei with(nolock)
where ei.EntityId = 875 and ei.ItemId = 2087

-- non found
exec qryMMSInventory_ItemActivityDetails 875, 488, '20190620', '20190703' -- 3366
go

-- valid
exec qryMMSInventory_ItemActivityDetails 875, 2087, '20190620', '20190704' -- 3366
go
exec qryMMSInventory_ItemActivityDetails 875, 2087, '20190620', '20190703' -- 3366
go


exec qryMMSLocalisation_GetSpecifiedLocalisationValues '', 'ItemActivity'
go