/****** Object: StoredProcedure [arvy].[GetLocalDispatchByDateBranch] Script Date: 10/07/2024 11:30:00 AM ******/
CREATE PROCEDURE [arvy].[GetEquipmentByLocalService]
    @inLocalServiceID NVARCHAR(4000),
    @inPageNo INT,
    @RowsPerPage INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);

    DECLARE @ParmDefinition NVARCHAR(500);

    SET @ParmDefinition = N' @inPageNo INT, @RowsPerPage INT'

    SET @SQL = '
        WITH X AS (
        SELECT
            Equip.PriKey AS EquipID,
            EqType.Type AS Type,
            Equip.EqNo AS EquipmentNumber,
            Equip.Descrip AS EquipmentDescription,
            Equip.Notes AS Detail,
            Branch.Name AS DefaultBranchName,
            Branch.BranchID,
            Branch.BranchPriKey,
            CASE WHEN Equip.EqStatus = 2 THEN 1 ELSE 0 END AS InShop,
            SafetySystemEquipmentStatus.ColorCode,
            ServEq.LSPriKey
        FROM Equip
        INNER JOIN EqType ON EqType.TypeNumber = Equip.Type
        LEFT OUTER JOIN EquipBranches ON EquipBranches.EquipPriKey = Equip.PriKey AND EquipBranches.DefaultBranch = 1
        LEFT OUTER JOIN Branch ON EquipBranches.BranchPriKey = Branch.BranchPriKey
        LEFT OUTER JOIN SafetySystemEquipmentStatus ON Equip.PriKey = SafetySystemEquipmentStatus.EquipmentFID
        INNER JOIN ServEq ON Equip.PriKey = ServEq.EqPriKey
        WHERE ServEq.LSPriKey IN (' + @inLocalServiceID + ')
        )
        SELECT
        EquipID,
        Type,
        EquipmentNumber,
        EquipmentDescription,
        Detail,
        DefaultBranchName,
        BranchID,
        BranchPriKey,
        InShop,
        ColorCode
        ,STUFF((
        SELECT '','' + CAST(SEQ.LSPriKey AS VARCHAR(10))
        FROM ServEq AS SEQ
        WHERE  SEQ.EqPriKey = X.EquipID AND SEQ.LSPRIKEY IN (' + @inLocalServiceID + ')
        FOR XML PATH('''')),1,1,'''') AS LOCALSERVICEIDS
        FROM X
        GROUP BY EquipID,Type,EquipmentNumber,EquipmentDescription,Detail,DefaultBranchName,BranchID,BranchPriKey,InShop,ColorCode
        ORDER BY 1 ASC '

    SET @SQL =    @SQL +     N'  OFFSET (@inPageNo-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY FOR XML RAW';

    EXECUTE sp_executesql @SQL, @ParmDefinition, @inPageNo, @RowsPerPage
END
GO
GRANT EXECUTE ON  [arvy].[GetEquipmentByLocalService] TO [MssExec]
GO