[arvy].[GetCrewByLocalService]
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