USP_DATALIST_FAFGIFTS

CREATE procedure dbo.USP_DATALIST_FAFGIFTS(
  @EVENTID uniqueidentifier,
  @TRANSACTIONTYPECODE tinyint = null,
  @DONORNAME nvarchar(210) = null,
  @REGISTRANTNAME nvarchar(210) = null,
  @MAXROWS int = 1000,
  @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.TYPECODE, TX.[TYPE], 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, 0 as TYPECODE, 'REGISTRANT' as [TYPE], 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, 0 as TYPECODE, 'SPONSOR' as [TYPE], 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)
),
/*RecognitionRegistrants as 
(
    select REG.RECORDID, REG.TYPECODE, REG.[TYPE], RR.CONSTITUENTID, REG.NAME as RECOGNITIONNAME, RR.REVENUESPLITID  
    from dbo.REVENUERECOGNITION RR With (NOLOCK)
    join EventMembers REG on REG.CONSTITUENTID = RR.CONSTITUENTID 

),*/
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, RC.RECEIPTNUMBER, RR.CONSTITUENTID AS RECOGNITIONCONSTITUENTID
    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
    where
      (
        @SITEFILTERMODE = 0
        or 
        ( dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
      )
  -- Check site security
    and (
          exists(
            select 1 from (select dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) as SITEID) DESIGNATIONSITE
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[DESIGNATIONSITE].[SITEID] or (SITEID is null and [DESIGNATIONSITE].[SITEID] is null))) )
        )
) 

 select top (@MAXROWS)
      D.REVENUEID as ID, 
      D.DONORNAME, 
      R.NAME AS RECOGNITION,
      case when R.TYPECODE = 0 then RL.Role else R.[TYPE] end as [ROLE],
        case when R.TYPECODE <> 0 then R.NAME else (Select TEAMNAME from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(R.CONSTITUENTID,@EVENTID)) end as GROUPNAME, 
      D.AMOUNT, 
    D.TRANSACTIONTYPE,
    D.[APPLICATION],
      D.PAYMENTMETHOD, 
    D.[DATE] as DATE,   
    D.LOOKUPID as LOOKUPID,
    D.RECEIPTNUMBER,
    D.REVENUESPLITID
    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)