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