/****** Object: StoredProcedure [arvy].[GetLocalDispatchByDateBranch] Script Date: 10/07/2024 11:30:00 AM ******/
CREATE PROCEDURE [arvy].[GetCrewByLocalService]
    @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
            LSCrew.CrewID,
            SysUser.EMPNO,
            CASE WHEN LSCrew.LeadMan = 1 THEN ''Lead Man'' ELSE ''Crew'' END AS CrewTYpe,
            isnull( SysUser.LastName + '' '', '''' ) + isnull( SysUser.FirstName, '''' ) AS FullName,
            SysUser.EMAIL,
            Status.Status,
            LaborType.LaborType,
            LaborType.IsLocalDispatch,
            CASE WHEN SysUserPhoneNumber.SysUserPhoneTypeFID = 3 AND (SysUserPhoneNumber.LocalNumber) != ''''
            THEN dbo.GetFormattedPhoneNumber(CountryCodeStandard.CountryCodeStandardID,SysUserPhoneNumber.AreaCode,SysUserPhoneNumber.LocalNumber)
            ELSE ''''
            END AS cellphone,
            CASE WHEN home.SysUserPhoneTypeFID = 1 AND (home.LocalNumber) != ''''
            THEN dbo.GetFormattedPhoneNumber(CountryCodeStandard.CountryCodeStandardID,home.AreaCode,home.LocalNumber)
            ELSE ''''
            END AS homephone,
            CASE WHEN sms.SysUserPhoneTypeFID = 7 AND (sms.LocalNumber) != ''''
            THEN dbo.GetFormattedPhoneNumber(CountryCodeStandard.CountryCodeStandardID,sms.AreaCode,sms.LocalNumber)
            ELSE ''''
            END AS smsphone,
            SysuserBranch.BranchPriKey AS BranchID,
            LSCrew.LSPriKey , LSCrew.LeadMan
            FROM LSCrew
            JOIN SysUser ON SysUser.SysUserID = LSCrew.CrewID
            JOIN Status ON Status.PriKey =Sysuser.STATUS
            JOIN LaborType ON LaborType.PriKey=LSCrew.LaborTypeFID
            LEFT JOIN SysUserPhoneNumber ON SysUserPhoneNumber.SysUserFID=SysUser.SysUserID AND SysUserPhoneTypeFID=3
            LEFT JOIN SysUserPhoneNumber AS home ON home.SysUserFID=SysUser.SysUserID AND home.SysUserPhoneTypeFID=1
            LEFT JOIN SysUserPhoneNumber AS sms ON sms.SysUserFID=SysUser.SysUserID AND sms.SysUserPhoneTypeFID=7
            LEFT JOIN CountryCodeStandard ON CountryCodeStandard.CountryCodeStandardID=SysUserPhoneNumber.CountryCodeStandardFID
            LEFT OUTER JOIN SysuserBranch ON SysuserBranch.SysuserPriKey = SysUser.SysUserID AND SysuserBranch.DefaultBranch = 1
            WHERE LSCrew.LSPriKey IN (' + @inLocalServiceID +  ')
            )
            SELECT
            CrewID,
            EMPNO,
            CrewTYpe,
            EMAIL,
            FullName,
            Status,
            LaborType,
            IsLocalDispatch,
            cellphone,
            homephone,
            smsphone,
            BranchID,LeadMan
            ,STUFF((
            SELECT '','' + CAST(LSC.LSPriKey AS VARCHAR(10))
            FROM LSCrew AS LSC
            WHERE  LSC.CrewID = X.CrewID AND LSC.LeadMan = X.LeadMan AND LSC.LSPriKey IN (' + @inLocalServiceID +  ')
            FOR XML PATH('''')),1,1,'''') AS LOCALSERVICEIDS
            FROM X
            GROUP BY CrewID,EMPNO,CrewTYpe,EMAIL,FullName,Status,LaborType,IsLocalDispatch,cellphone,homephone,smsphone,BranchID, LeadMan
            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].[GetCrewByLocalService] TO [MssExec]
GO