/****** Object: StoredProcedure [arvy].[GetLocalDispatchByDateBranch] Script Date: 12/10/2024 00:00:00 AM ******/
CREATE PROCEDURE [arvy].[GetCrewByLocalService]
    @inLocalServiceID NVARCHAR(MAX),
    @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 = N'
            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.PriKey AS LTPriKey,
            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 X.LTPriKey    = LSC.LaborTypeFID AND LSC.LSPriKey IN (' + @inLocalServiceID +  ')
            FOR XML PATH('''')),1,1,'''') AS LOCALSERVICEIDS
            FROM X
            GROUP BY X.CrewID,X.EMPNO,X.CrewTYpe,X.EMAIL,X.FullName,X.Status,X.LaborType,X.IsLocalDispatch,X.cellphone,X.homephone,X.smsphone,X.BranchID, X.LeadMan,X.LTPriKey
            ORDER BY 1 ASC
            OFFSET (@inPageNo-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY  FOR XML RAW ';

        SET @SQL =    @SQL

        EXECUTE sp_executesql @SQL, @ParmDefinition, @inPageNo, @RowsPerPage

END
GO
GRANT EXECUTE ON  [arvy].[GetCrewByLocalService] TO [MssExec]
GO