USP_DATALIST_CONSTITUENTRELATIONSHIPMAP

CREATE procedure dbo.USP_DATALIST_CONSTITUENTRELATIONSHIPMAP(
    @CONSTITUENTID uniqueidentifier,
    @ONLYCURRENT bit = 0,
    @RECIPROCALTYPECODEID uniqueidentifier = null,
    @ONLYCONTACTS bit = 0,
    @SHOWINDIVIDUALS bit = 1,
    @SHOWORGANIZATIONS bit = 1,
    @SHOWGROUPS bit = 1,
    @SHOWHOUSEHOLDS bit = 1,
    @ROOTID uniqueidentifier = null)
as
set nocount on;

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

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,
    ADDRESS.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
    left join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where CONSTITUENT.ID = @CONSTITUENTID
union all
select
    RELATIONSHIP.RECIPROCALCONSTITUENTID 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,
    ADDRESS.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,
    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.RELATIONSHIP
    inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
    left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
    left join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
where 
    RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
        and (@CURRENTDATE is null
            or RELATIONSHIP.STARTDATE is null
            or RELATIONSHIP.STARTDATE <= @CURRENTDATE)
        and (@CURRENTDATE is null
            or RELATIONSHIP.ENDDATE is null
            or RELATIONSHIP.ENDDATE >= @CURRENTDATE)
        and (@RECIPROCALTYPECODEID is null
            or RELATIONSHIP.RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID)
        and 
            (@ONLYCONTACTS = 0
            or RELATIONSHIP.ISCONTACT = @ONLYCONTACTS)
        and (@SHOWINDIVIDUALS = 1 or CONSTITUENT.ISORGANIZATION = 1 or CONSTITUENT.ISGROUP = 1)
        and (@SHOWORGANIZATIONS = 1 or CONSTITUENT.ISORGANIZATION = 0)
        and (@SHOWGROUPS = 1 or CONSTITUENT.ISGROUP = 0 or GROUPDATA.GROUPTYPECODE = 0)
        and (@SHOWHOUSEHOLDS = 1 or CONSTITUENT.ISGROUP = 0 or GROUPDATA.GROUPTYPECODE = 1)
        and (@ROOTID is null or RELATIONSHIP.RECIPROCALCONSTITUENTID in (
                    select
                        ROOTRELATIONSHIP.RECIPROCALCONSTITUENTID
                    from
                        RELATIONSHIP as ROOTRELATIONSHIP
                    where
                        ROOTRELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ROOTID
                )
            )
order by
    STRENGTH, 
    CONSTITUENT.KEYNAME, 
    NAME;