USP_DATALIST_CONTACTINFORMATIONPHONE

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

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

    with [CONTACTINFO_CTE] as
    (
        --Phones

        select
            PHONE.ID,
            dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER) as CONTACTINFO,
            dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PHONE.PHONETYPECODEID) as TYPE,
            PHONE.ISPRIMARY,
            case when PHONE.DONOTCALL=1 AND PHONE.DONOTTEXT=1 THEN 'Do not call, Do not text'
                when PHONE.DONOTCALL=1 THEN 'Do not call'
                when PHONE.DONOTTEXT=1 THEN 'Do not text' else '' end as DONOTCONTACT,
            PHONE.ISCONFIDENTIAL as ISCONFIDENTIAL,
            PHONE.STARTDATE,
            PHONE.ENDDATE,
            case when PHONE.STARTDATE is null then '0001-01-01' else cast(PHONE.STARTDATE as date) end as [STARTDATE_SORT],
            case when PHONE.ENDDATE is null then '0001-01-01' else cast(PHONE.ENDDATE as date) end as [ENDDATE_SORT],
            PHONE.DATEADDED
        from
            dbo.PHONE
        where
            PHONE.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,
        ISCONFIDENTIAL,
        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'
            else
                'RES:lv_spacer'
        end as IMAGEKEY
    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