117 lines
3.9 KiB
Transact-SQL
117 lines
3.9 KiB
Transact-SQL
USE [CPRNIMS]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[GetInventory] Script Date: 6/11/2026 9:29:29 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
ALTER PROCEDURE [dbo].[GetInventory]
|
|
(
|
|
@UserId VARCHAR(450)='89da2977-c70f-4df9-94d4-9a610aa999ea',
|
|
@SearchPRNo VARCHAR(50) = '',
|
|
@SearchItemNo VARCHAR(50) = '',
|
|
@SearchItemName VARCHAR(100) = '',
|
|
@SearchDept VARCHAR(200) = '',
|
|
@SearchProjectCode VARCHAR(200) = '',
|
|
@PageNumber INT = 1,
|
|
@PageSize INT = 10
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
|
|
|
|
-- ── 1. Full department list — unaffected by any filter ──────────────
|
|
SELECT DISTINCT D.Department
|
|
FROM dbo.Departments D
|
|
INNER JOIN dbo.Users U
|
|
ON U.DepartmentId = D.DepartmentId
|
|
INNER JOIN dbo.Inventory IV
|
|
ON IV.UserId = U.Id
|
|
ORDER BY D.Department;
|
|
|
|
-- ── 2. Filtered temp table ───────────────────────────────────────────
|
|
IF OBJECT_ID('tempdb..#Inventory') IS NOT NULL
|
|
DROP TABLE #Inventory;
|
|
|
|
SELECT
|
|
IV.InventoryId
|
|
, IV.ItemNo
|
|
, IV.QtyOnHand
|
|
, IV.QtyIn
|
|
, IV.QtyOut
|
|
, L.LotName AS LotNo
|
|
, IV.UserId
|
|
, PR.PRNo
|
|
, PRD.ItemName
|
|
, D.Department
|
|
, PRD.ItemDescription
|
|
, ICAT.ItemCategoryName
|
|
, RRD.RemainingQty
|
|
, ITD.CreatedDate
|
|
,COALESCE(PCD.ProjectCode,'N/A') ProjectCode
|
|
INTO #Inventory
|
|
FROM dbo.Inventory IV
|
|
INNER JOIN dbo.InventTrans IT
|
|
ON IV.InventoryId = IT.InventoryId
|
|
INNER JOIN dbo.InventTransDetail ITD
|
|
ON IT.InventTransId = ITD.InventTransId
|
|
INNER JOIN dbo.PRDetails PRD
|
|
ON ITD.PRDetailId = PRD.PRDetailsId
|
|
AND PRD.IsActive = 1
|
|
INNER JOIN dbo.RRDetails RRD
|
|
ON PRD.PRDetailsId = RRD.PRDetailId
|
|
AND RRD.IsActive = 1
|
|
INNER JOIN dbo.ItemCategories ICAT
|
|
ON PRD.ItemCategoryId = ICAT.ItemCategoryId
|
|
INNER JOIN dbo.Lot L
|
|
ON IV.LotId = L.LotId
|
|
INNER JOIN dbo.LotType LT
|
|
ON L.LotTypeId = LT.LotTypeId
|
|
INNER JOIN dbo.PR PR
|
|
ON PR.UserId = IV.UserId
|
|
AND PR.IsActive = 1
|
|
INNER JOIN dbo.ProjectCodes PC
|
|
ON PR.ProjectCodeId = PC.ProjectCodeId
|
|
INNER JOIN dbo.Users U
|
|
ON IV.UserId = U.Id
|
|
INNER JOIN dbo.Departments D
|
|
ON U.DepartmentId = D.DepartmentId
|
|
INNER JOIN dbo.ProjectCodes PCD
|
|
ON PR.ProjectCodeId = PCD.ProjectCodeId
|
|
WHERE ITD.TransTypeId=2 AND IV.IsActive=1 AND ITD.IsActive=1 AND
|
|
(@SearchPRNo = '' OR PR.PRNo = @SearchPRNo)
|
|
AND (@SearchItemNo = '' OR PRD.ItemNo = @SearchItemNo)
|
|
AND (@SearchItemName = '' OR PRD.ItemName LIKE '%' + @SearchItemName + '%')
|
|
AND (@SearchDept = '' OR D.Department LIKE '%' + @SearchDept + '%')
|
|
AND (@SearchProjectCode = '' OR PC.ProjectCode LIKE '%' + @SearchProjectCode + '%')
|
|
GROUP BY
|
|
IV.InventoryId
|
|
, IV.ItemNo
|
|
, IV.QtyOnHand
|
|
, IV.QtyIn
|
|
, IV.QtyOut
|
|
, L.LotName
|
|
, IV.UserId
|
|
, PR.PRNo
|
|
, PRD.ItemName
|
|
, D.Department
|
|
, PRD.ItemDescription
|
|
, ICAT.ItemCategoryName
|
|
, RRD.RemainingQty
|
|
, ITD.CreatedDate
|
|
,PCD.ProjectCode;
|
|
|
|
-- ── 3. Total count of filtered results ──────────────────────────────
|
|
SELECT COUNT(*) AS TotalCount
|
|
FROM #Inventory;
|
|
|
|
-- ── 4. Paged filtered results ────────────────────────────────────────
|
|
SELECT *
|
|
FROM #Inventory
|
|
ORDER BY CreatedDate DESC
|
|
OFFSET @Offset ROWS
|
|
FETCH NEXT @PageSize ROWS ONLY;
|
|
|
|
DROP TABLE #Inventory;
|
|
END |