/****** Object:  StoredProcedure [rdld].[GetSysUserRecords] ******/
-- =============================================
-- Author:      Bryan Johnston
-- Date:        2024-08-12
-- Description:
-- =============================================

CREATE PROCEDURE [rdld].[GetSysUserRecords]
AS
BEGIN
    SET NOCOUNT ON;
    SET DEADLOCK_PRIORITY LOW;

    DECLARE @AreFlagsProvided BIT = 1;

    SELECT DISTINCT
        @AreFlagsProvided AreFlagsProvided,

        su.LastName,
        su.FirstName,
        su.HireDate,
        su.EffectDate,
        su.SysUserId,
        
        (SysUserPhoneNumber.AreaCode + SysUserPhoneNumber.LocalNumber) as PhoneNumber,
        su.Email,

        ltm.SysUserLaborTypeMapID,
        ltm.VendorId,
        LaborType.PriKey as LaborTypePriKey,
        LaborType.IsLongDistance,
        LaborType.IsSalesTeamType,

        ltm2.SysUserLaborTypeMapID as OASysUserLaborTypeMapID,
        ltm2.LaborTypeFID as OALaborTypePriKey,

        status.Status
    FROM SysUser su
        INNER JOIN STATUS
                ON su.Status = STATUS.PriKey
        INNER JOIN SysUserLaborTypeMap ltm
                ON su.SysUserId = ltm.SysUserFID
        INNER JOIN LaborType
                ON LaborType.PriKey = ltm.LaborTypeFID

        -- Yes, pulls too many rows.
        -- Exists to find OA salesperson/surveyor combination folks.
        LEFT JOIN SysUserLaborTypeMap ltm2
                ON su.SysUserId = ltm2.SysUserFID

        LEFT JOIN SysUserPhoneNumber ON SysUserPhoneNumber.SysUserFID = su.SysUserID
        LEFT JOIN SysUserPhoneType
                ON (SysUserPhoneType.SysUserPhoneTypeID = SysUserPhoneNumber.SysUserPhoneTypeFID
                    AND SysUserPhoneType.TypeName = 'Cell')
    WHERE
        (SysUserPhoneNumber.AreaCode + SysUserPhoneNumber.LocalNumber) IS NOT NULL
        OR su.Email IS NOT NULL

    FOR XML RAW;
    RETURN 0;

END;
GO
GRANT EXECUTE ON  [rdld].[GetSysUserRecords] TO [MssExec]
GO