UFN_CONSTITUENT_GETCURRENTCONTACTS

CREATE function dbo.UFN_CONSTITUENT_GETCURRENTCONTACTS
(
    @RELATIONSHIPCONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @DATAFORMINSTANCEID uniqueidentifier = null
)
returns table
as
return
  -- This function serves the ConstituentSummaryOrganizationContactsTile.View.xml, which limits the number of records returned.

  -- If you change the number of records returned here, you must also update the tile's UIModel to allow one less than the new limit.

    select top 501
        RELATIONSHIP.ID,
        NF.NAME as CONTACTNAME,
        CONTACTTYPECODE.DESCRIPTION as CONTACTTYPE,
        dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER) as PHONENUMBER,
        PHONE.DONOTCALL,
        PHONE.ISCONFIDENTIAL,
        EMAILADDRESS.EMAILADDRESS,
        EMAILADDRESS.DONOTEMAIL,
        RELATIONSHIP.ISPRIMARYCONTACT,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        case 
            when (SYSADMIN.ISSYSADMIN = 1) then 1
            when
            (
                (dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @DATAFORMINSTANCEID, RELATIONSHIP.RECIPROCALCONSTITUENTID) = 1)
                and exists 
                (
                    select 1 
                    from dbo.CONSTITUENT 
                    left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID
                    where CONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID 
                    and dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @DATAFORMINSTANCEID, CONSTITUENTSITE.SITEID) = 1
                )
            )
            then 1 else 0
        end as USERHASPERMISSIONTOVIEWCONTACT,
        CONSTITUENT.ISINACTIVE as CONTACTISINACTIVE,
        case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end as CONTACTISDECEASED
    from
        dbo.RELATIONSHIP
        inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF
        left join dbo.CONTACTTYPECODE on RELATIONSHIP.CONTACTTYPECODEID = CONTACTTYPECODE.ID 
        left join dbo.PHONE on RELATIONSHIP.ID = PHONE.RELATIONSHIPID
        left join dbo.EMAILADDRESS on RELATIONSHIP.ID = EMAILADDRESS.RELATIONSHIPID
        left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
        outer apply (select dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) as ISSYSADMIN) SYSADMIN
    where 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @RELATIONSHIPCONSTITUENTID
        and RELATIONSHIP.ISCONTACT = 1
        and (RELATIONSHIP.STARTDATE is null or RELATIONSHIP.STARTDATE <= dbo.UFN_DATE_GETEARLIESTTIME(getdate()))
        and    (RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(getdate()))