/****** Object: StoredProcedure [arvy].[GetLocalDispatchByDateBranch] Script Date: 10/13/2023 11:30:00 AM ******/
CREATE PROCEDURE [arvy].[GetCrewByLocalService]
    @inLocalServiceID INT,
    @inPageNo INT,
    @RowsPerPage INT
AS
BEGIN
    SET NOCOUNT ON;

    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 AS LocalServiceID
    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 = @inLocalServiceID
    ORDER BY 1 ASC
    OFFSET (@inPageNo-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY
    FOR XML RAW
END
GO
GRANT EXECUTE ON  [arvy].[GetCrewByLocalService] TO [MssExec]
GO