USP_DATALIST_MARRIAGECONTACTINFORMATION

CREATE procedure dbo.USP_DATALIST_MARRIAGECONTACTINFORMATION 
(
  @CONSTITUENTID uniqueidentifier, 
  @INCLUDEFORMER bit    = 0,
  @INCLUDEADDRESSES bit = 1,
  @INCLUDEPHONES bit = 1,
  @INCLUDEEMAIL 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';

  with [CONSTITUENTS_CTE] as
    (
      select @CONSTITUENTID CONSTITUENTID,
             0 as ISHOUSEHOLD

      union all

    select RELATIONSHIP.RECIPROCALCONSTITUENTID  CONSTITUENTID,
             0 as ISHOUSEHOLD
    from dbo.RELATIONSHIP 
    where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1

      union all

    select GM.GROUPID  CONSTITUENTID,
             1 as ISHOUSEHOLD
    from dbo.GROUPMEMBER as GM
    inner join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
    where 
      GM.MEMBERID = @CONSTITUENTID and 
      dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1 and 
      GD.GROUPTYPECODE = 0

    ),
    [CONTACTINFO_CTE] as
  (
    --Addresses

    select 
      ADDRESS.ID,
      CONSTITUENT.ID CONSTITUENTID, 
      NF.NAME CONSTITUENTNAME,
      CONSTITUENTS_CTE.ISHOUSEHOLD,
      'Address' as CONTACTTYPE,
      0 as CONTACTTYPECODE,
      @ADDRESSCONTEXTVIEWFORM as VIEWFORMID,
      ADDRESS.DESCRIPTION as CONTACTINFO,
      dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESS.ADDRESSTYPECODEID) as TYPE,
      ADDRESS.ISPRIMARY,
      case when ADDRESS.DONOTMAIL = 0 then '' else 'Do not mail' end as DONOTCONTACT,
      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(cast('645E6BE7-459C-402b-A03C-67587CA72B94' as uniqueidentifier), @CONSTITUENTID, ADDRESS.ID) as [MAPCONTEXTID]
    from dbo.ADDRESS
    inner join CONSTITUENTS_CTE on ADDRESS.CONSTITUENTID = CONSTITUENTS_CTE. CONSTITUENTID
    inner join dbo.CONSTITUENT on CONSTITUENTS_CTE.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where @INCLUDEADDRESSES = 1

    union all

    --Emails

    select
      EMAILADDRESS.ID,
      CONSTITUENT.ID CONSTITUENTID, 
      NF.NAME CONSTITUENTNAME, 
      CONSTITUENTS_CTE.ISHOUSEHOLD,
      'Email' as CONTACTTYPE,
      1 as CONTACTTYPECODE,
      @EMAILADDRESSCONTEXTVIEWFORM as VIEWFORMID,
      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,
      0 as ISCONFIDENTIAL,
      EMAILADDRESS.STARTDATE as STARTDATE,
      EMAILADDRESS.ENDDATE as ENDDATE,
      '0001-01-01' as STARTDATE_SORT,
      '0001-01-01' as ENDDATE_SORT,
      EMAILADDRESS.DATEADDED,
      null as ADDRESSCOORDINATESID,
      null as PENDINGGEOCODE,
      null as INVALIDGEOCODE,
      null as MAPCONTEXTID
    from dbo.EMAILADDRESS
    inner join CONSTITUENTS_CTE on EMAILADDRESS.CONSTITUENTID = CONSTITUENTS_CTE. CONSTITUENTID
    inner join dbo.CONSTITUENT on CONSTITUENTS_CTE.CONSTITUENTID = CONSTITUENT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where @INCLUDEEMAIL = 1 

    union all

    --Phones

    select
      PHONE.ID,
      CONSTITUENT.ID CONSTITUENTID, 
      NF.NAME CONSTITUENTNAME, 
      CONSTITUENTS_CTE.ISHOUSEHOLD,
      'Phone number' as CONTACTTYPE,
      2 as CONTACTTYPECODE,
      @PHONECONTEXTVIEWFORM as VIEWFORMID,
      dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER) as CONTACTINFO,
      dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PHONE.PHONETYPECODEID) as TYPE,
      PHONE.ISPRIMARY,
      case when PHONE.DONOTCALL = 0 then '' else 'Do not call' 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,
      null as ADDRESSCOORDINATESID,
      null as PENDINGGEOCODE,
      null as INVALIDGEOCODE,
      null as MAPCONTEXTID
    from dbo.PHONE
    inner join CONSTITUENTS_CTE on PHONE.CONSTITUENTID = CONSTITUENTS_CTE. CONSTITUENTID
    inner join dbo.CONSTITUENT on CONSTITUENTS_CTE.CONSTITUENTID = CONSTITUENT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where @INCLUDEPHONES = 1 
  )

  select 
    ID,
    CONSTITUENTID, 
    CONSTITUENTNAME, 
    ISHOUSEHOLD,
    CONTACTTYPE,
    CONTACTTYPECODE,
    VIEWFORMID,
    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 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 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 CONTACTTYPE, ISPRIMARY desc, ISFORMER asc, ENDDATE_SORT desc, STARTDATE_SORT desc, DATEADDED desc