USP_DATALIST_WPCONTACTINFORMATION

CREATE procedure dbo.USP_DATALIST_WPCONTACTINFORMATION 
(
    @CONSTITUENTID uniqueidentifier, 
    @INCLUDEFORMER bit    = 0,
    @INCLUDEADDRESSES bit = 1,
    @INCLUDEPHONES bit = 1,
    @INCLUDEEMAIL bit = 1,
  @INCLUDESOCIAL bit = 1
)
as
    set nocount on;

    declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
    declare @ADDRESSCONTEXTVIEWFORM uniqueidentifier = '78f27fdf-6696-48cc-b6dc-85da47616c1b';
    declare @EMAILADDRESSCONTEXTVIEWFORM uniqueidentifier = 'e7c71f82-6faa-47f2-a3c3-3320f3cc6630';
    declare @PHONECONTEXTVIEWFORM uniqueidentifier = 'e40ebe2f-bbbd-485e-80bf-107a8e2cdf2b';
    declare @MAPENTITYID uniqueidentifier = N'645E6BE7-459C-402b-A03C-67587CA72B94';

    if (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('6f77d512-d0d1-444f-9b46-b8603a6fe5f1') = 1)
        set @MAPENTITYID = N'78102CE5-F217-4429-9175-D99A6B55A4EE';


    with [CONTACTINFO_CTE] as
    (
        --Addresses

        select 
            ADDRESS.ID,
            'Address' as CONTACTTYPE,
            0 as CONTACTTYPECODE,
            @ADDRESSCONTEXTVIEWFORM as VIEWFORMID,
            ADDRESS.DESCRIPTION as CONTACTINFO,
            ADDRESS.ISPRIMARY,
            ADDRESS.ISCONFIDENTIAL,
            ADDRESS.HISTORICALSTARTDATE as STARTDATE,
            ADDRESS.HISTORICALENDDATE as ENDDATE,
            case when ADDRESS.HISTORICALSTARTDATE is null then '0001-01-01' else ADDRESS.HISTORICALSTARTDATE end as [STARTDATE_SORT],
            case when ADDRESS.HISTORICALENDDATE is null then '0001-01-01' else ADDRESS.HISTORICALENDDATE end as [ENDDATE_SORT],
            ADDRESS.DATEADDED,
            ADDRESSCOORDINATES.ID as ADDRESSCOORDINATESID,
            ADDRESSCOORDINATES.PENDINGGEOCODE,
            ADDRESSCOORDINATES.INVALIDGEOCODE,
            dbo.UFN_MAPPING_BUILDCONTEXTRECORDID(@MAPENTITYID, @CONSTITUENTID, ADDRESS.ID) as [MAPCONTEXTID],
            0 as INVALIDEMAIL,
      null as MEDIAURL,
      ADDRESS.DONOTMAIL as DONOTCONTACT
        from dbo.ADDRESS
            left join dbo.ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID
        where @INCLUDEADDRESSES = 1 
            and ADDRESS.CONSTITUENTID = @CONSTITUENTID

        union all

        --Emails

        select
            EMAILADDRESS.ID,
            'Email' as CONTACTTYPE,
            1 as CONTACTTYPECODE,
            @EMAILADDRESSCONTEXTVIEWFORM as VIEWFORMID,
            EMAILADDRESS.EMAILADDRESS as CONTACTINFO,
            EMAILADDRESS.ISPRIMARY,
            0 as ISCONFIDENTIAL,
            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,
            null as ADDRESSCOORDINATESID,
            null as PENDINGGEOCODE,
            null as INVALIDGEOCODE,
            null as MAPCONTEXTID,
            case when EXISTS(SELECT ID FROM dbo.EMAILINVALIDRECIPIENT WHERE EMAILINVALIDRECIPIENT.ADDRESS = EMAILADDRESS.EMAILADDRESS AND EMAILINVALIDRECIPIENT.ISBLACKLISTED = 1) then 1 else 0 end as INVALIDEMAIL,
      null as MEDIAURL,
      EMAILADDRESS.DONOTEMAIL as DONOTCONTACT
        from dbo.EMAILADDRESS
        where @INCLUDEEMAIL = 1 
            and EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID

        union all

        --Phones

        select
            PHONE.ID,
            'Phone number' as CONTACTTYPE,
            2 as CONTACTTYPECODE,
            @PHONECONTEXTVIEWFORM as VIEWFORMID,
            dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER) as CONTACTINFO,
            PHONE.ISPRIMARY,
            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,
            null as ADDRESSCOORDINATESID,
            null as PENDINGGEOCODE,
            null as INVALIDGEOCODE,
            null as MAPCONTEXTID,
            0 as INVALIDEMAIL,
      null as MEDIAURL,
      PHONE.DONOTCALL as DONOTCONTACT
        from dbo.PHONE
        where @INCLUDEPHONES = 1 
            and PHONE.CONSTITUENTID = @CONSTITUENTID

        union all

        --Social

        select
            SOCIALMEDIAACCOUNT.ID,
            'Social media account' as CONTACTTYPE,
            3 as CONTACTTYPECODE,
            null as VIEWFORMID,
          case when len(SOCIALMEDIAACCOUNT.USERID) = 0 
              then SOCIALMEDIASERVICE.NAME
              else SOCIALMEDIASERVICE.NAME + N' (' + SOCIALMEDIAACCOUNT.USERID + N') ' 
        end as CONTACTINFO,
            0 ISPRIMARY,
            0 as ISCONFIDENTIAL,
            null as STARTDATE,
            null as ENDDATE,
            '0001-01-01' as [STARTDATE_SORT],
            '0001-01-01' as [ENDDATE_SORT],
            SOCIALMEDIAACCOUNT.DATEADDED,
            null as ADDRESSCOORDINATESID,
            null as PENDINGGEOCODE,
            null as INVALIDGEOCODE,
            null as MAPCONTEXTID,
            0 as INVALIDEMAIL,
      SOCIALMEDIAACCOUNT.URL as MEDIAURL,
      SOCIALMEDIAACCOUNT.DONOTCONTACT as DONOTCONTACT
        from dbo.SOCIALMEDIAACCOUNT
    inner join dbo.SOCIALMEDIASERVICE on SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID = SOCIALMEDIASERVICE.ID
    left join dbo.SOCIALMEDIAACCOUNTTYPECODE on SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID = SOCIALMEDIAACCOUNTTYPECODE.ID
        where @INCLUDESOCIAL = 1 
            and SOCIALMEDIAACCOUNT.CONSTITUENTID = @CONSTITUENTID

    )

    select 
        ID,
        CONTACTTYPE,
        CONTACTTYPECODE,
        VIEWFORMID,
        case when ISCONFIDENTIAL = 0
            then CONTACTINFO
            else N'(Confidential) ' + CONTACTINFO
        end as CONTACTINFO,
        case when ISPRIMARY = 0 then '' else 'Yes' end as ISPRIMARY,
        ISCONFIDENTIAL,
        case when ((ENDDATE is null) or (ENDDATE > @CURRENTDATE)) then 0 else 1 end as ISFORMER,
        STARTDATE,
        ENDDATE,
        case when ADDRESSCOORDINATESID is null or PENDINGGEOCODE = 1 or INVALIDGEOCODE = 1 then 0 else 1 end as ISGEOCODED,
        case when ADDRESSCOORDINATESID is null then 0 else PENDINGGEOCODE end as PENDINGGEOCODE,
        case when ADDRESSCOORDINATESID is null then 0 else INVALIDGEOCODE end as INVALIDGEOCODE,
        MAPCONTEXTID,        
        case when ISCONFIDENTIAL = 1
                then 'RES:padlock'
            when DONOTCONTACT = 1
                then 'RES:warning'
            when INVALIDEMAIL = 1  
                then 'RES:do_not_symbol'
            else 'RES:lv_spacer'
        end as IMAGEKEY,
        INVALIDEMAIL,
    MEDIAURL
    from [CONTACTINFO_CTE]
    where (@INCLUDEFORMER = 1 or ((ENDDATE is null) or (ENDDATE > @CURRENTDATE)))
    order by CONTACTTYPE, ISPRIMARY desc, ISFORMER asc, ENDDATE_SORT desc, STARTDATE_SORT desc, DATEADDED desc