USP_DATALIST_FAFGIFTS3

CREATE procedure dbo.USP_DATALIST_FAFGIFTS3(
  @EVENTID uniqueidentifier,
  @TRANSACTIONTYPECODE tinyint = null,
  @DONORNAME nvarchar(210) = null,
  @REGISTRANTNAME nvarchar(210) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

;with EventMembers as 
(
    select TFT.ID as RECORDID, TFT.NAME, TX.TEAMCONSTITUENTID as CONSTITUENTID
    from dbo.TEAMEXTENSION TX With (NOLOCK)
    join dbo.TEAMFUNDRAISINGTEAM TFT With (NOLOCK) on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
    where TX.EVENTID = @EVENTID

    union all

    select R.ID as RECORDID, C.NAME, C.ID as CONSTITUENTID 
    from dbo.REGISTRANT R With (NOLOCK)
    join dbo.CONSTITUENT C With (NOLOCK) on R.CONSTITUENTID = C.ID 
    where R.EVENTID = @EVENTID

 union all

    select S.ID as RECORDID, C.NAME, C.ID as CONSTITUENTID 
    from dbo.EVENTSPONSOR S With (NOLOCK)
    join dbo.CONSTITUENT C With (NOLOCK) on S.CONSTITUENTID = C.ID 
    where S.EVENTID = @EVENTID and C.ID not in (select CONSTITUENTID from  dbo.REGISTRANT R where R.EVENTID = @EVENTID)
),
Donors as 
(
    select R.ID as REVENUEID, RS.ID as REVENUESPLITID, DONS.NAME as DONORNAME, RS.AMOUNT, R.TRANSACTIONTYPECODE, R.TRANSACTIONTYPE, RS.[APPLICATION] ,RPM.PAYMENTMETHOD, R.[DATE], R.LOOKUPID, RR.CONSTITUENTID AS RECOGNITIONCONSTITUENTID, R.CONSTITUENTID
    from dbo.EVENT E With (NOLOCK)
    join dbo.REVENUE R With (NOLOCK) on E.APPEALID = R.APPEALID and E.ID = @EVENTID 
    join dbo.REVENUESPLIT RS With (NOLOCK) on R.ID = RS.REVENUEID and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 7, 17) OR R.TRANSACTIONTYPECODE IN (1,2) )
    join dbo.CONSTITUENT DONS With (NOLOCK) on R.CONSTITUENTID = DONS.ID 
    left join dbo.REVENUEPAYMENTMETHOD RPM With (NOLOCK) on RPM.REVENUEID = R.ID 
  left join dbo.REVENUERECEIPT RC With (NOLOCK) on RC.REVENUEID = R.ID   
  left join dbo.REVENUERECOGNITION RR With (NOLOCK) on RR.REVENUESPLITID = RS.ID  
  left join dbo.V_SITEID_MAPFROM_DESIGNATIONID RSD (nolock) on RSD.DESIGNATIONID = RS.DESIGNATIONID 
    where
      (
        @SITEFILTERMODE = 0
        or 
        RSD.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
      )
  -- Check site security

    and (
          dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
              or isnull(RSD.SITEID,cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) 
                      in (select isnull(SITEID, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) 
                              from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
                 )

        )
) 

 select 
      D.REVENUEID as ID, 
      D.DONORNAME, 
      R.NAME AS RECOGNITION,
      D.AMOUNT, 
    D.TRANSACTIONTYPE,
    D.[APPLICATION],
      D.PAYMENTMETHOD, 
    D.[DATE] as DATE,   
    D.LOOKUPID as LOOKUPID,
    D.REVENUESPLITID,
    D.CONSTITUENTID
    from Donors D
    left join EventMembers R on D.RECOGNITIONCONSTITUENTID = R.CONSTITUENTID
    --left join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) RL ON RL.REGISTRANTID = R.RECORDID AND R.[TYPE] = 'REGISTRANT'

    where (D.TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE or @TRANSACTIONTYPECODE is null)   
    and (D.DONORNAME like @DONORNAME + '%' or nullif(@DONORNAME, '') is null)
    and (R.NAME like @REGISTRANTNAME + '%' or nullif(@REGISTRANTNAME,'') is null)