USP_DATALIST_CONSTITUENTRELATIONSHIPMINIMAP

CREATE procedure dbo.USP_DATALIST_CONSTITUENTRELATIONSHIPMINIMAP( 
    @CONSTITUENTID uniqueidentifier,
    @ROOTID uniqueidentifier = null)
as
set nocount on;

declare @TOPN integer = 20;

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

declare @LOCALEID int;

select top 1 @LOCALEID = LOCALE.LOCALEID 
from dbo.CURRENCY
inner join dbo.LOCALE on CURRENCY.LOCALEID = LOCALE.ID
where CURRENCY.ISORGANIZATIONCURRENCY = 1;

with RELATIONSHIPSET_CTE as
(
  select distinct
      RELATIONSHIP.RECIPROCALCONSTITUENTID ID,
    RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
    RELATIONSHIP.RECIPROCALCONSTITUENTID,
      (
          rtrim(NF.NAME + ' ' +
              coalesce(
                  replace(
                      stuff(
                          (select '' + STATUS from
                          (select ',(Deceased)' STATUS
                              from dbo.DECEASEDCONSTITUENT DC
                              where DC.ID = CONSTITUENT.ID
                          union all
                          select ',(Inactive)' STATUS
                              from dbo.CONSTITUENT C
                              where C.ID = CONSTITUENT.ID
                              and C.ISINACTIVE=1
                      ) as SUBQ for xml path(''))
                      ,1,1,'')
                  , '),(', ', ')
              ,'')
          )
      ) as NAME,
    case when ROOT_RTC.ID is null 
      then RELATIONSHIPTYPECODE.DESCRIPTION
      else ROOT_RTC.DESCRIPTION
      end RELATIONSHIPTYPE,
      case when ESTIMATEDWEALTH.MINIMUMAMOUNT is null then '' else dbo.UFN_FORMAT_CURRENCY(coalesce(ESTIMATEDWEALTH.MINIMUMAMOUNT, 0), @LOCALEID) + ' and up' end WEALTHDESCRIPTION,
      case
          when CONSTITUENT.ISORGANIZATION = 1 then 'catalog:Blackbaud.AppFx.Constituent.Catalog.dll,Blackbaud.AppFx.Constituent.Catalog.organization.png'
          when CONSTITUENT.ISGROUP = 1 then 
              case
                  when GROUPDATA.GROUPTYPECODE = 0 then 'catalog:Blackbaud.AppFx.ConstituentGroups.Catalog.dll,Blackbaud.AppFx.ConstituentGroups.Catalog.households_32.png'
              else 'catalog:Blackbaud.AppFx.ConstituentGroups.Catalog.dll,Blackbaud.AppFx.ConstituentGroups.Catalog.groups_32.png'
              end
          else 'catalog:Blackbaud.AppFx.Constituent.Catalog.dll,Blackbaud.AppFx.Constituent.Catalog.individual.png'
      end IMAGEKEY,
      case
          when CONSTITUENT.ISORGANIZATION = 1 then 'Hot'
          when CONSTITUENT.ISGROUP = 1 then 
              case
                  when GROUPDATA.GROUPTYPECODE = 0 then 'Warm'
                  else 'Cold'
              end
          else 'Cold'
      end STRENGTH,
      cast(0 as bit) ISROOT,
      CONSTITUENT.KEYNAME
      from 
      dbo.UFN_RELATIONSHIPMAP_GETRELATIONSHIPS(@CONSTITUENTID, @CURRENTDATE, @TOPN) RSET
      inner join dbo.RELATIONSHIP on RELATIONSHIP.ID = RSET.ID
      inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
      left join dbo.RELATIONSHIPTYPECODE on RECIPROCALTYPECODEID = RELATIONSHIPTYPECODE.ID
      left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
      left join dbo.WEALTHCAPACITY on CONSTITUENT.ID = WEALTHCAPACITY.ID
      left join dbo.ESTIMATEDWEALTH on WEALTHCAPACITY.ESTIMATEDWEALTHID = ESTIMATEDWEALTH.ID
    left join dbo.RELATIONSHIP ROOT_REL on ROOT_REL.RELATIONSHIPCONSTITUENTID = @ROOTID and ROOT_REL.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
    left join dbo.RELATIONSHIPTYPECODE ROOT_RTC on ROOT_REL.reciprocaltypecodeID = ROOT_RTC.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
  where 
          (@ROOTID is null or RELATIONSHIP.RECIPROCALCONSTITUENTID in (
                      select
                          ROOTRELATIONSHIP.RECIPROCALCONSTITUENTID
                      from
                          dbo.UFN_RELATIONSHIPMAP_GETRELATIONSHIPS(@ROOTID, @CURRENTDATE, @TOPN) as ROOTRELATIONSHIP
            )
    )
),
RELATIONSHIPS_CTE as
(
  select
      ROW_NUMBER() OVER(PARTITION BY RELATIONSHIPCONSTITUENTID, RECIPROCALCONSTITUENTID ORDER BY RELATIONSHIPTYPE DESC) as ROWNUM,
      ID,
      NAME,
      RELATIONSHIPTYPE,
      WEALTHDESCRIPTION,
      IMAGEKEY,
      STRENGTH,
      ISROOT,
      KEYNAME
      from RELATIONSHIPSET_CTE
)
select    
    CONSTITUENT.ID,
    (
        rtrim(NF.NAME + ' ' +
            coalesce(
                replace(
                    stuff(
                        (select '' + STATUS from
                        (select ',(Deceased)' STATUS
                            from dbo.DECEASEDCONSTITUENT DC
                            where DC.ID = CONSTITUENT.ID
                        union all
                        select ',(Inactive)' STATUS
                            from dbo.CONSTITUENT C
                            where C.ID = CONSTITUENT.ID
                            and C.ISINACTIVE=1
                    ) as SUBQ for xml path(''))
                    ,1,1,'')
                , '),(', ', ')
            ,'')
        )
    ) as NAME,
    '(Prospect)' as DESCRIPTION,
    case
        when CONSTITUENT.ISORGANIZATION = 1 then 'catalog:Blackbaud.AppFx.Constituent.Catalog.dll,Blackbaud.AppFx.Constituent.Catalog.organization.png'
        when CONSTITUENT.ISGROUP = 1 then 
            case
                when GROUPDATA.GROUPTYPECODE = 0 then 'catalog:Blackbaud.AppFx.ConstituentGroups.Catalog.dll,Blackbaud.AppFx.ConstituentGroups.Catalog.households_32.png'
            else 'catalog:Blackbaud.AppFx.ConstituentGroups.Catalog.dll,Blackbaud.AppFx.ConstituentGroups.Catalog.groups_32.png'
            end
        else 'catalog:Blackbaud.AppFx.Constituent.Catalog.dll,Blackbaud.AppFx.Constituent.Catalog.individual.png'
    end IMAGEKEY,
    null STRENGTH,
    cast(1 as bit) ISROOT,
    CONSTITUENT.KEYNAME
    from dbo.CONSTITUENT
    left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where CONSTITUENT.ID = @CONSTITUENTID
union all

 select
      ID,
      NAME,
      '(' + dbo.UDA_BUILDLISTWITHDELIMITER(RELATIONSHIPTYPE, char(10))+ ')' + char(10) + WEALTHDESCRIPTION as DESCRIPTION,
      IMAGEKEY,
      STRENGTH,
      ISROOT,
      KEYNAME
 from RELATIONSHIPS_CTE
 where ROWNUM <= 3
   group by 
      ID,
      NAME,
      WEALTHDESCRIPTION,
      IMAGEKEY,
      STRENGTH,
      ISROOT,
      KEYNAME

order by
    STRENGTH, 
    CONSTITUENT.KEYNAME, 
    NAME;