USP_DATALIST_MKTCONTACT

CREATE procedure [dbo].[USP_DATALIST_MKTCONTACT]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = 0,
  @RECIPROCALTYPECODEID uniqueidentifier = null,
  @PRIMARYONLY bit = 0,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
as
  set nocount on;

  select
    [RELATIONSHIP].[RECIPROCALCONSTITUENTID] as [CONTACTID],
    [RELATIONSHIP].[ID],
    [CONTACT_NAME].[NAME] as [CONTACTNAME],
    [VENDOR_NAME].[NAME] as [VENDORNAME],
    [RELATIONSHIPTYPECODE].[DESCRIPTION] as [RELATIONSHIP],
    isnull([CONTACTTYPECODE].[DESCRIPTION], '') as [CONTACTTYPE],
    [RELATIONSHIP].[ISPRIMARYCONTACT],
    dbo.[UFN_RELATIONSHIPJOBINFO_GETCURRENTJOBTITLE]([RELATIONSHIP].[ID]) as [POSITION],
    (select count([R].[ID])
     from dbo.[RELATIONSHIP] as [R] 
     inner join dbo.[VENDOR] as [V] on [V].[ID] = [R].[RELATIONSHIPCONSTITUENTID]
     where [R].[ISCONTACT] = 1
     and [R].[RECIPROCALCONSTITUENTID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]) as [RELATIONSHIPCOUNT],
    cast(0 as bit) as [USERWILLHAVEERRORONCONTACTPAGE], -- unused field, leave in for backwards compatibility

    case when dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1
         then cast(1 as bit)
         else
           case when dbo.[UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE](@CURRENTAPPUSERID, '5DF92861-001A-4B67-800F-D598F9CF334B') = 1 -- ContactInformation.DataList.xml

                then
                  case when exists (select top 1 1 
                                    from dbo.[UFN_SITEID_MAPFROM_CONSTITUENTID]([CONTACT].[ID])
                                    where dbo.[UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORSITE](@CURRENTAPPUSERID, '5DF92861-001A-4B67-800F-D598F9CF334B', [SITEID]) = 1) -- ContactInformation.DataList.xml

                       then cast(1 as bit)
                       else cast(0 as bit)
                  end
                else cast(0 as bit)
           end
    end as [USERHASFULLRIGHTSTOCONTACT],
    stuff((select ', ' + [SITE].[NAME]
        from dbo.[SITE]
        inner join dbo.[CONSTITUENTSITE] on [CONSTITUENTSITE].[SITEID] = [SITE].[ID]
        where [CONSTITUENTSITE].[CONSTITUENTID] = [CONTACT].[ID]
        order by [SITE].[NAME]
        for xml path(''), type
        ).value('.', 'varchar(max)') , 1, 2, '') as [SITES]
  from dbo.[RELATIONSHIP]
  inner join dbo.[RELATIONSHIPTYPECODE] on [RELATIONSHIPTYPECODE].[ID] = [RELATIONSHIP].[RECIPROCALTYPECODEID]
  left outer join dbo.[CONTACTTYPECODE] on [CONTACTTYPECODE].[ID] = [RELATIONSHIP].[CONTACTTYPECODEID]
  inner join dbo.[CONSTITUENT] as [CONTACT] on [CONTACT].[ID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
  inner join dbo.[CONSTITUENT] as [VENDOR] on [VENDOR].[ID] = [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID]
  inner join dbo.[VENDOR] as [MKTVENDOR] on [MKTVENDOR].[ID] = [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID]
  outer apply dbo.[UFN_CONSTITUENT_DISPLAYNAME]([CONTACT].[ID]) as [CONTACT_NAME]
  outer apply dbo.[UFN_CONSTITUENT_DISPLAYNAME]([VENDOR].[ID]) as [VENDOR_NAME]
  where [RELATIONSHIP].[ISCONTACT] = 1
  and (@RECIPROCALTYPECODEID is null or [RELATIONSHIP].[RECIPROCALTYPECODEID] = @RECIPROCALTYPECODEID)
  and (@PRIMARYONLY = 0 or [RELATIONSHIP].[ISPRIMARYCONTACT] = @PRIMARYONLY)
  and exists (select top 1 1
              from dbo.[CONSTITUENT] as [C]
              left join dbo.[CONSTITUENTSITE] on [CONSTITUENTSITE].[CONSTITUENTID] = [C].[ID]
              where [C].[ID] = [CONTACT].[ID]
              and
              (
                  dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                  or
                  exists(
                          select 1
                          from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
                          where
                              SITEID=CONSTITUENTSITE.SITEID
                or
                              (
                                  SITEID is null and CONSTITUENTSITE.SITEID is null
                              )
                        )
              ))
  and exists (select top 1 1
              from dbo.[CONSTITUENT] as [C]
              left join dbo.[CONSTITUENTSITE] on [CONSTITUENTSITE].[CONSTITUENTID] = [C].[ID]
              where [C].[ID] = [VENDOR].[ID]
              and
              (
                  dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                  or
                  exists(
                          select 1
                          from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
                          where
                              SITEID=CONSTITUENTSITE.SITEID
                              or
                              (
                                  SITEID is null and CONSTITUENTSITE.SITEID is null
                              )
                        )
              ))
  and (@SITEFILTERMODE = 0
       or exists (select top 1 1 from dbo.[CONSTITUENTSITE] with (nolock)
                  inner join dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID]
                  where [CONSTITUENTSITE].[CONSTITUENTID] = [CONTACT].[ID]))
  order by [CONTACT].[KEYNAME], [CONTACT].[FIRSTNAME], [CONTACT].[MIDDLENAME], [VENDOR].[KEYNAME], [RELATIONSHIPTYPECODE].[DESCRIPTION];

  return 0;