DECLARE @EntityID BIGINT = 902
DECLARE @ItemID BIGINT = 55


DECLARE @Range BIGINT = (SELECT Value FROM tbConfigurationSetting with (nolock) WHERE ConfigurationEnum = 377)
DECLARE @OpeningCountQty FLOAT 
DECLARE @OpeningCountDate DATETIME
DECLARE @EndingCountQty FLOAT
DECLARE @EndingCountDate DATETIME
DECLARE @PurchasesQty FLOAT
DECLARE @TransfersInQty FLOAT
DECLARE @TransfersOutQty FLOAT
DECLARE @NetSalesValue FLOAT

-- Get Opening Count Date & Quantity (based on configurable range of N days
SELECT TOP 1 @OpeningCountDate = tsc.ApplyDate, @OpeningCountQty = tsc.Inventory_qty
FROM tbTransactionStockCount tsc WITH (NOLOCK)
JOIN tbTransactionStockCountLocation tscl WITH (NOLOCK)
ON tscl.TransactionStockCountLocationID = tsc.TransactionStockCountLocationID

WHERE tsc.EntityID = @EntityID
AND tsc.ItemID = @ItemID
AND tscl.AutoGenerated <> 1
AND tsc.ApplyDate >= DATEADD(DAY, @Range * -1, CONVERT(VARCHAR(10), GETDATE(), 101))
ORDER BY tsc.ApplyDate ASC

-- Get Ending Count Date and Quantity (most recent)
SELECT TOP 1 @EndingCountDate = tsc.ApplyDate, @EndingCountQty = tsc.Inventory_qty
FROM tbTransactionStockCount tsc WITH (NOLOCK)
JOIN tbTransactionStockCountLocation tscl WITH (NOLOCK)
ON tscl.TransactionStockCountLocationID = tsc.TransactionStockCountLocationID

WHERE tsc.EntityID = @EntityID
AND tsc.ItemID = @ItemID
AND tscl.AutoGenerated <> 1
AND tsc.ApplyDate >= DATEADD(DAY, @Range * -1, CONVERT(VARCHAR(10), GETDATE(), 101))
ORDER BY tsc.ApplyDate DESC

-- Get Purchases Between Opening Count and Ending Count
SELECT
	@PurchasesQty = ISNULL(SUM(trl.InventoryAmt), 0)
FROM tbTransactionReceiveLog trl WITH (NOLOCK)
WHERE EntityID = @EntityID
AND ItemID = @ItemID
AND ApplyDate >= @OpeningCountDate
AND ApplyDate < @EndingCountDate

-- Get Transfers In
SELECT
	@TransfersInQty = ISNULL(SUM(ttd.TransferQty), 0)
FROM tbTransactionTransferDetail ttd WITH (NOLOCK)
JOIN tbTransactionTransfer tt WITH (NOLOCK)
ON tt.TransactionTransferID = ttd.TransactionTransferID
WHERE tt.TransferToEntityID = @EntityID
AND tt.TransferStatus NOT IN ('Open', 'Returned', 'Rejected', 'Cancelled')
AND tt.ReceiveDate >= @OpeningCountDate
AND tt.ReceiveDate < @EndingCountDate
AND ttd.ItemID = @ItemID

-- Get Transfers Out
SELECT
	@TransfersOutQty = ISNULL(SUM(ttd.TransferQty), 0)
FROM tbTransactionTransferDetail ttd WITH (NOLOCK)
JOIN tbTransactionTransfer tt WITH (NOLOCK)
ON tt.TransactionTransferID = ttd.TransactionTransferID
WHERE tt.TransferFromEntityID = @EntityID
AND tt.TransferStatus NOT IN ('Returned', 'Rejected', 'Cancelled')
AND tt.TransferDate >= @OpeningCountDate
AND tt.TransferDate < @EndingCountDate
AND ttd.ItemID = @ItemID

-- Get Net Sales
SELECT
	@NetSalesValue = ISNULL(SUM(Sales_Excl_GST_Excl), 0)
FROM tbTransactionSummary ts WITH (NOLOCK)
WHERE EntityID = @EntityID
AND TransactionDate >= @OpeningCountDate
AND TransactionDate < @EndingCountDate

-- Output Results
SELECT
	e.Entity as [Restaurant],
	i.ItemCode,
	i.Description,
	ei.UPTQty,
	ei.UPTAutoCalc,
	@OpeningCountDate as [Opening Count],
	@OpeningCountQty as [Opening Qty],
	@PurchasesQty as [Purchases],
	@TransfersInQty as [Transfers In],
	@TransfersOutQty as [Transfers Out],
	@EndingCountDate as [Ending Count],
	@EndingCountQty as [Ending Qty],
	@OpeningCountQty + @PurchasesQty + @TransfersInQty - @TransfersOutQty - @EndingCountQty as [Actual Usage],
	@NetSalesValue as [Net Sales],
	(@OpeningCountQty + @PurchasesQty + @TransfersInQty - @TransfersOutQty - @EndingCountQty)/(@NetSalesValue/1000) as [UPT]
FROM tbEntityItem ei WITH (NOLOCK)
JOIN tbItem i WITH (NOLOCK)
On i.ItemID = ei.ItemID
JOIN tbEntity e WITH (NOLOCK)
On e.EntityID = ei.EntityID

WHERE ei.EntityID = @EntityID
AND ei.ItemID = @ItemID