117 lines
4.1 KiB
Transact-SQL
117 lines
4.1 KiB
Transact-SQL
USE [CPRNIMS]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[GetInventory] Script Date: 6/18/2026 3:09:49 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
ALTER PROCEDURE [dbo].[GetInventory]
|
|
(
|
|
@UserId VARCHAR(450)='16b37c00-131f-4205-b8f6-ad4d0f9f3a32',
|
|
@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;
|
|
DECLARE @HasFullAccess BIT = 0;
|
|
DECLARE @UserDepartmentId INT,@UserName VARCHAR(50);
|
|
|
|
SELECT @UserDepartmentId = DepartmentId,@UserName=UserName
|
|
FROM dbo.Users
|
|
WHERE Id = @UserId;
|
|
|
|
IF @UserName IN ('LSKRISUR24','LSCYNDIZ25','LSJONTAN25','LHRIOCAS24')
|
|
SET @HasFullAccess = 1;
|
|
|
|
IF @UserDepartmentId = 6
|
|
SET @HasFullAccess = 1;
|
|
|
|
-- ── 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(PC.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.PR ON PR.PRId=PRD.PRId AND PR.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
|
|
LEFT JOIN dbo.Lot L ON IV.LotId = L.LotId
|
|
LEFT JOIN dbo.LotType LT ON L.LotTypeId = LT.LotTypeId
|
|
LEFT 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
|
|
WHERE ITD.TransTypeId = 2 AND IV.IsActive = 1 AND ITD.IsActive = 1
|
|
AND (
|
|
@HasFullAccess = 1
|
|
OR D.DepartmentId = @UserDepartmentId
|
|
)
|
|
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
|
|
,PC.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 |