USP_DATALIST_FAFGIFTCLAIMS

CREATE procedure dbo.USP_DATALIST_FAFGIFTCLAIMS
( 
  @EVENTID uniqueidentifier,
  @GIFTCLAIMDATEFROM datetime = null,
  @GIFTCLAIMDATETO datetime = null,
  @DONORNAME nvarchar(210) = null,
  @REGISTRANTNAME nvarchar(210) = null,
  @MAXROWS int = 1000,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SHOWUNPAID  bit = 1
)
as

;with Donors as 
(
    select R.ID as REVENUEID, RS.ID as REVENUESPLITID, DONS.NAME as DONORNAME, RS.AMOUNT ,RPM.PAYMENTMETHOD, R.LOOKUPID, R.DATEADDED, 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 = 9 
    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.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
    where
     -- Check site security    
    (
      exists
      (
        select 1 from (select dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) as SITEID) REVENUESITE
        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVENUESITE].[SITEID] or (SITEID is null and [REVENUESITE].[SITEID] is null)))
      )
    )
    -- Site filter
    and
    (
      @SITEFILTERMODE = 0
      or
      dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) in 
      (        
        select FilterSite.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) FilterSite
      )
    )
),
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

)
    select top (@MAXROWS) D.REVENUEID as ID,
       D.REVENUESPLITID,      
         ROD.EXPECTEDDATE  AS DATESENT,
           D.DONORNAME as DONORNAME,
           D.AMOUNT AS TRANSACTIONAMOUNT,
           D.PAYMENTMETHOD AS paymenttype,
           isnull(tISP.CHECKNUMBER, ROD.CHECKNUMBER) CHECKNUMBER,
           tISP.AMOUNT as TOTALAMOUNTRECEIVED,
       case when tISP.PLEDGEID is not null and tISP.AMOUNT = D.AMOUNT then 1 else 0 end as FULLYAPPLIED, 
           R.NAME as RECOGNITIONREGISTRANT,
           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 GROUPNAME 
    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'
    left join ( select ISP.PLEDGEID, SUM(pRS.AMOUNT) as AMOUNT, dbo.UDA_BUILDLIST(CPM.CHECKNUMBER) CHECKNUMBER from 
          dbo.INSTALLMENTSPLITPAYMENT ISP (nolock)
          join dbo.REVENUESPLIT pRS (nolock) on pRS.ID = ISP.PAYMENTID and pRS.APPLICATIONCODE = 17
      join dbo.REVENUE R (nolock) on R.ID = ISP.PLEDGEID
      join dbo.EVENT E (nolock) on E.APPEALID = R.APPEALID and E.ID = @EVENTID
      left join dbo.REVENUEPAYMENTMETHOD RPM With (NOLOCK) on RPM.REVENUEID = pRS.REVENUEID 
      left join dbo.CHECKPAYMENTMETHODDETAIL CPM With (nolock) on RPM.ID = CPM.ID
          group by ISP.PLEDGEID 
        ) tISP on tISP.PLEDGEID = D.REVENUEID
    left join dbo.REVENUEOFFLINEDONATION ROD on D.REVENUEID = ROD.ID
    left join dbo.ADDRESSBOOKFAF AD (nolock) on AD.ID = ROD.ADDRESSBOOKFAFID
    where (@GIFTCLAIMDATEFROM is null or ([ROD].[EXPECTEDDATE] is not null and convert(datetime,  dbo.fnFormatDateTime([ROD].[EXPECTEDDATE], 'MM/DD/YY')) >= @GIFTCLAIMDATEFROM))
    and (@GIFTCLAIMDATETO is null or ([ROD].[EXPECTEDDATE] is not null and convert(datetime,  dbo.fnFormatDateTime([ROD].[EXPECTEDDATE], 'MM/DD/YY')) <= @GIFTCLAIMDATETO) )
    and (nullif(@DONORNAME, '') is null or ROD.DONORNAME like '%' + @DONORNAME + '%')
    and (nullif(@REGISTRANTNAME, '') is null or R.NAME like '%' + @REGISTRANTNAME + '%')

    -- add filter of show unpaid
    and (@SHOWUNPAID = 0 or (@SHOWUNPAID = 1 and D.AMOUNT > isnull(tISP.AMOUNT, 0)))