USP_DATALIST_CONTACTINFORMATIONEMAILADDRESS

CREATE procedure dbo.USP_DATALIST_CONTACTINFORMATIONEMAILADDRESS
(
    @CONSTITUENTID uniqueidentifier, 
    @INCLUDEFORMER bit    = 0
)
as
    set nocount on;

    declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    with [CONTACTINFO_CTE] as
    (
        --Emails

        select
            EMAILADDRESS.ID,
            EMAILADDRESS.EMAILADDRESS as CONTACTINFO,
            dbo.UFN_EMAILADDRESSTYPECODE_GETDESCRIPTION(EMAILADDRESS.EMAILADDRESSTYPECODEID) as TYPE,
            EMAILADDRESS.ISPRIMARY,
            case when EMAILADDRESS.DONOTEMAIL = 0 then '' else 'Do not email' end as DONOTCONTACT,
            EMAILADDRESS.STARTDATE,
            EMAILADDRESS.ENDDATE,
            case when EMAILADDRESS.STARTDATE is null then '0001-01-01' else cast(EMAILADDRESS.STARTDATE as date) end as [STARTDATE_SORT],
            case when EMAILADDRESS.ENDDATE is null then '0001-01-01' else cast(EMAILADDRESS.ENDDATE as date) end as [ENDDATE_SORT],
            EMAILADDRESS.DATEADDED,
            case when EXISTS(SELECT ID FROM dbo.EMAILINVALIDRECIPIENT WHERE EMAILINVALIDRECIPIENT.ADDRESS = EMAILADDRESS.EMAILADDRESS AND EMAILINVALIDRECIPIENT.ISBLACKLISTED = 1) then 1 else 0 end as INVALIDEMAIL,
            EMAILADDRESS.ISCONFIDENTIAL
        from
            dbo.EMAILADDRESS
        where
            EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID
    )
    select 
        ID,
        case
            when ISCONFIDENTIAL = 0
                then CONTACTINFO
            else
                N'(Confidential) ' + CONTACTINFO
        end as CONTACTINFO,
        case
            when ((ENDDATE is null) or (ENDDATE > @CURRENTDATE))
                then coalesce(TYPE, N'') + N' (Current)'
            else
                coalesce(TYPE, N'') + N' (Former)'
        end as TYPE,
        case when ISPRIMARY = 0 then '' else 'Yes' end as ISPRIMARY,
        DONOTCONTACT,
        case when ((ENDDATE is null) or (ENDDATE > @CURRENTDATE)) then 0 else 1 end as ISFORMER,
        STARTDATE,
        ENDDATE,
        case
            when ISCONFIDENTIAL = 1
                then 'RES:padlock'
            when Len(DONOTCONTACT) > 0
                then 'RES:warning'
            when INVALIDEMAIL = 1  
                then 'RES:do_not_symbol'
            else
                'RES:lv_spacer'
        end as IMAGEKEY,
        INVALIDEMAIL,
        ISCONFIDENTIAL
    from
        [CONTACTINFO_CTE]
    where
        @INCLUDEFORMER = 1 or ((ENDDATE is null) or (ENDDATE > @CURRENTDATE))
    order by
        ISPRIMARY desc, ISFORMER asc, ENDDATE_SORT desc, STARTDATE_SORT desc, DATEADDED desc