USP_DATALIST_EVENTPROFILEREPORT_NONREPSONDER

CREATE procedure dbo.USP_DATALIST_EVENTPROFILEREPORT_NONREPSONDER
(
  @EVENTID uniqueidentifier,
  @ISVISIBLE bit = 1,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  if @ISVISIBLE = 1
  begin
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;

    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

    with INVITEES as
    (
      select
        EVENTID,
        CONSTITUENTID,
        DECLINED
      from
      (
        select
          ROW_NUMBER() over (partition by INVITEE.EVENTID, INVITEE.CONSTITUENTID order by INVITEE.INVITATIONSENTON desc) as ROWNUMBER,
          INVITEE.EVENTID,
          INVITEE.CONSTITUENTID,
          INVITEE.DECLINED
        from dbo.INVITEE
      ) as INVITEELIST
      where INVITEELIST.ROWNUMBER = 1
    )
    select distinct
      INVITEES.CONSTITUENTID as CONSTITUENTID,
      NF.NAME,
      'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='+convert(nvarchar(36),INVITEES.CONSTITUENTID) as CONSTITUENTLINK,
      PHONE.NUMBER as PHONENUMBER,
      PHONETYPECODE.DESCRIPTION as PHONENUMBERTYPE,
      EMAILADDRESS.EMAILADDRESS as EMAILADDRESS,
      EMAILADDRESSTYPECODE.DESCRIPTION as EMAILADDRESSTYPE
    from INVITEES
      left join dbo.REGISTRANT on REGISTRANT.CONSTITUENTID = INVITEES.CONSTITUENTID and REGISTRANT.EVENTID = @EVENTID
      left join dbo.PHONE on PHONE.CONSTITUENTID = INVITEES.CONSTITUENTID and PHONE.ISPRIMARY = 1
      left join dbo.PHONETYPECODE on PHONETYPECODE.ID = PHONE.PHONETYPECODEID
      left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = INVITEES.CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1
      left join dbo.EMAILADDRESSTYPECODE on EMAILADDRESSTYPECODE.ID = EMAILADDRESS.EMAILADDRESSTYPECODEID
      cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INVITEES.CONSTITUENTID) NF
    where
      INVITEES.EVENTID = @EVENTID and
      INVITEES.DECLINED = 0 and
      REGISTRANT.ID is null and 
      (
        @ISADMIN = 1 or 
        @APPUSER_IN_NONRACROLE = 1 or
        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, INVITEES.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1
      );
  end