USP_FAFEVENT_DONORDETAILREPORT
Used to display list of donor
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @EVENTID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @DONORNAME | nvarchar(100) | IN | |
| @PARTICIPANTNAME | nvarchar(100) | IN | |
| @DONATIONAMOUNT | money | IN | |
| @GIFTTYPECODE | int | IN | |
| @WEBURL | nvarchar(1200) | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | nvarchar(max) | IN |
Definition
Copy
CREATE procedure dbo.USP_FAFEVENT_DONORDETAILREPORT
(
@EVENTID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@DONORNAME nvarchar(100) = '',
@PARTICIPANTNAME nvarchar(100) = '',
@DONATIONAMOUNT money = 0.0,
@GIFTTYPECODE integer = 1, --0: All three 1:Gift, 2:Pledge, 3:Recurring gift, 4:Matching gift, 5:Pending gift
@WEBURL nvarchar(1200) = '',
@REPORTUSERID nvarchar(128) = null,
@ALTREPORTUSERID nvarchar(128) = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED nvarchar(max) = null
)
with execute as owner
as
set nocount on;
DECLARE @VARREVENUETYPE nvarchar(18) = ''
If @GIFTTYPECODE = 1 --Logic is in UFN_REVENUE_EVENTREVENUEDETAILS designed by DBA group
SET @VARREVENUETYPE = 'Donation'
Else If @GIFTTYPECODE = 2
SET @VARREVENUETYPE = 'Pledge'
Else if @GIFTTYPECODE = 3
SET @VARREVENUETYPE = 'Recurring gift'
Else if @GIFTTYPECODE = 4
SET @VARREVENUETYPE = 'Matching gift'
Else if @GIFTTYPECODE = 5
SET @VARREVENUETYPE = 'Pending gift'
--Else If @GIFTTYPECODE = 0
-- SET @VARREVENUETYPE = 'All'
declare @CURRENTAPPUSERID as uniqueidentifier
set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
declare @ISADMIN bit;
declare @SITESGRANTED table(
SITEID uniqueidentifier
)
insert into @SITESGRANTED
select SITEID
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'78dd575e-8747-4c48-898a-e917303e2559', 21)
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
If @GIFTTYPECODE = 0 --All
Begin
SELECT
CASE WHEN @WEBURL = '' THEN 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36), C.ID)
ELSE @WEBURL + convert(nvarchar(36), C.ID) END as CONSTITUENTID
-- 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36), C.ID) as CONSTITUENTID
,E.ID AS EVENTID
,C.NAME AS [Donor Name]
,RS.AMOUNT as [Donation Amount]
--,REE.[TRANSACTIONTYPE] AS [Gift Type]
--,RS.APPLICATION AS [Gift Type]
,CASE WHEN R.TRANSACTIONTYPE = 'Pledge' THEN R.TRANSACTIONTYPE
WHEN R.TRANSACTIONTYPE = 'Recurring gift' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Donation' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Pending gift' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Matching gift' THEN R.TRANSACTIONTYPE
ELSE RS.APPLICATION END AS [Gift Type]
,CASE WHEN R.CONSTITUENTID IS NOT NULL AND RS.APPLICATION = 'Recurring gift' THEN RA.SCHEDULEDATE
WHEN R.CONSTITUENTID IS NOT NULL AND RS.APPLICATION <> 'Recurring gift' THEN RS.DATEADDED
END AS [Donation Date]
,CASE WHEN EXISTS(SELECT TE.TEAMCONSTITUENTID FROM TEAMEXTENSION TE INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TE.TEAMFUNDRAISINGTEAMID WHERE TE.TEAMCONSTITUENTID=RR.CONSTITUENTID)
-- THEN (SELECT DISTINCT TFT2.NAME FROM TEAMEXTENSION TE2 INNER JOIN TEAMFUNDRAISINGTEAM TFT2 ON TFT2.ID=TE2.TEAMFUNDRAISINGTEAMID WHERE TE2.TEAMCONSTITUENTID=RR.CONSTITUENTID)
THEN ''
WHEN ES.ID is not null then ''
WHEN NCU.ID is not null
then (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)
else '' END AS [Participant Name]
/* , isnull((SELECT CS.NAME FROM dbo.CONSTITUENT CS inner join dbo.REGISTRANT R ON R.CONSTITUENTID = CS.ID WHERE CS.ID=RR.CONSTITUENTID), E.NAME) as [Participant Name] */
,(
CASE WHEN ISNULL(RR.AMOUNT,'') = '' THEN ''
WHEN R.TRANSACTIONTYPE = 'Pledge' AND REG.ID IS NULL THEN ''
WHEN R.TRANSACTIONTYPE = 'Recurring gift' AND REG.ID IS NULL THEN ''
WHEN (RS.APPLICATION = 'Recurring gift' AND R.TRANSACTIONTYPE = 'Payment') AND REG.ID IS NULL THEN ''
WHEN (RS.APPLICATION = 'Matching gift' AND R.TRANSACTIONTYPE = 'Payment') AND REG.ID IS NULL THEN ''
WHEN ES.ID is not null then ''
WHEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID) IS NOT NULL THEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID)
ELSE (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) END
-- ELSE '' END
) AS [Top Level Fundraising Group Name]
/* ,(
CASE WHEN NCU.ID is null THEN ''
when ES.ID is not null then ''
WHEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID) IS NOT NULL THEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID)
ELSE (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) END
) AS [Top Level Fundraising Group Name] */
FROM dbo.CONSTITUENT (NOLOCK) C
INNER JOIN REVENUE R (NOLOCK) ON R.CONSTITUENTID=C.ID
INNER JOIN dbo.REVENUESPLIT RS (NOLOCK) ON R.ID = RS.REVENUEID
LEFT OUTER JOIN REVENUERECOGNITION RR (NOLOCK) ON RR.REVENUESPLITID = RS.ID
INNER JOIN EVENT E ON E.APPEALID=R.APPEALID
LEFT JOIN NETCOMMUNITYCLIENTUSER NCU ON NCU.CONSTITUENTID = RR.CONSTITUENTID
LEFT JOIN dbo.EVENTSPONSOR ES on ES.CONSTITUENTID = NCU.CONSTITUENTID
LEFT JOIN dbo.REGISTRANT REG on REG.CONSTITUENTID = C.ID and REG.EVENTID = E.ID
--INNER JOIN dbo.UFN_FAFEVENT_REVENUEDETAILS(@EVENTID,null,null,0) REE ON REE.CONSTITUENTID = C.ID
LEFT JOIN dbo.RECURRINGGIFTACTIVITY RA (NOLOCK) ON RS.ID = RA.PAYMENTREVENUEID
WHERE E.ID = ISNULL(@EVENTID, E.ID)
AND
(
-- REE.TRANSACTIONTYPE = 'Donation' --AND R.TRANSACTIONTYPE <> 'Recurring gift'
( RS.APPLICATION = 'Donation' )
or
(
-- REE.TRANSACTIONTYPE = 'Pledge' AND
(RS.APPLICATION <> 'Pledge' AND R.TRANSACTIONTYPE <> 'Payment')
)
or
(
-- REE.TRANSACTIONTYPE = 'Recurring gift' AND
(RS.APPLICATION = 'Recurring gift' AND R.TRANSACTIONTYPE = 'Payment')
)
or
(
(RS.APPLICATION = 'Matching gift' AND R.TRANSACTIONTYPE = 'Payment' )
)
or
(
(RS.APPLICATION = 'Pending gift' AND R.TRANSACTIONTYPE = 'Payment' )
)
)
AND (RS.APPLICATION <> 'Event registration') AND (RS.APPLICATION <> 'Event sponsorship' AND R.TRANSACTIONTYPE <> 'Matching gift claim')
AND NOT ( RS.APPLICATION = 'Donation' AND R.TRANSACTIONTYPE = 'Recurring gift')
AND NOT ( RS.APPLICATION = 'Donation' AND R.TRANSACTIONTYPE = 'Pending gift' )
AND RS.DATEADDED BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND ISNULL(@ENDDATE, '12/30/2999')
-- AND R.DATE BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND ISNULL(@ENDDATE, '12/30/2999')
AND C.NAME LIKE ISNULL(@DONORNAME,C.NAME) + '%'
AND
(
(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)) + '%'
OR (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID)) + '%'
)
AND RS.AMOUNT > 0
-- Check site security
and
exists(
select 1 from (select dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) as SITEID) REVENUESITE
where (@ISADMIN = 1 or exists (select 1 from @SITESGRANTED SITESGRANTED where SITESGRANTED.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
)
)
ORDER BY C.NAME, [Donation Amount], [Gift Type], [Donation Date], [Participant Name], [Top Level Fundraising Group Name]
End
Else If @GIFTTYPECODE = 1 --Donation/Payment
Begin
SELECT
CASE WHEN @WEBURL = '' THEN 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36), C.ID)
ELSE @WEBURL + convert(nvarchar(36), C.ID) END as CONSTITUENTID
,E.ID AS EVENTID
,C.NAME AS [Donor Name]
,RS.AMOUNT as [Donation Amount]
--,REE.[TRANSACTIONTYPE] AS [Gift Type]
--,RS.APPLICATION AS [Gift Type]
,CASE WHEN R.TRANSACTIONTYPE = 'Pledge' THEN R.TRANSACTIONTYPE
WHEN R.TRANSACTIONTYPE = 'Recurring gift' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Donation' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Pending gift' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Matching gift' THEN R.TRANSACTIONTYPE
ELSE RS.APPLICATION END AS [Gift Type]
,CASE WHEN R.CONSTITUENTID IS NOT NULL THEN RS.DATEADDED END AS [Donation Date]
,CASE WHEN EXISTS(SELECT TE.TEAMCONSTITUENTID FROM TEAMEXTENSION TE INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TE.TEAMFUNDRAISINGTEAMID WHERE TE.TEAMCONSTITUENTID=RR.CONSTITUENTID)
-- THEN (SELECT DISTINCT TFT2.NAME FROM TEAMEXTENSION TE2 INNER JOIN TEAMFUNDRAISINGTEAM TFT2 ON TFT2.ID=TE2.TEAMFUNDRAISINGTEAMID WHERE TE2.TEAMCONSTITUENTID=RR.CONSTITUENTID)
THEN ''
WHEN ES.ID is not null then ''
WHEN NCU.ID is not null
then (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)
else '' END AS [Participant Name]
-- , isnull((SELECT CS.NAME FROM dbo.CONSTITUENT CS inner join dbo.REGISTRANT R ON R.CONSTITUENTID = CS.ID WHERE CS.ID=RR.CONSTITUENTID), E.NAME) as [Participant Name]
/* ,( CASE WHEN NCU.ID is null THEN ''
when ES.ID is not null then ''
WHEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID) IS NOT NULL THEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID)
ELSE (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) END
) AS [Top Level Fundraising Group Name] */
,(
CASE WHEN ISNULL(RR.AMOUNT,'') = '' THEN ''
WHEN R.TRANSACTIONTYPE = 'Pledge' AND REG.ID IS NULL THEN ''
WHEN R.TRANSACTIONTYPE = 'Recurring gift' AND REG.ID IS NULL THEN ''
WHEN (RS.APPLICATION = 'Recurring gift' AND R.TRANSACTIONTYPE = 'Payment') AND REG.ID IS NULL THEN ''
WHEN (RS.APPLICATION = 'Matching gift' AND R.TRANSACTIONTYPE = 'Payment') AND REG.ID IS NULL THEN ''
WHEN ES.ID is not null then ''
WHEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID) IS NOT NULL THEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID)
ELSE (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) END
) AS [Top Level Fundraising Group Name]
FROM dbo.CONSTITUENT (NOLOCK) C
INNER JOIN REVENUE R (NOLOCK) ON R.CONSTITUENTID=C.ID
INNER JOIN dbo.REVENUESPLIT RS (NOLOCK) ON R.ID = RS.REVENUEID
LEFT JOIN REVENUERECOGNITION RR (NOLOCK) ON RR.REVENUESPLITID = RS.ID
INNER JOIN EVENT E ON E.APPEALID=R.APPEALID
LEFT JOIN NETCOMMUNITYCLIENTUSER NCU ON NCU.CONSTITUENTID = RR.CONSTITUENTID
LEFT JOIN dbo.EVENTSPONSOR ES on ES.CONSTITUENTID = NCU.CONSTITUENTID
LEFT JOIN dbo.REGISTRANT REG on REG.CONSTITUENTID = C.ID and REG.EVENTID = E.ID
--INNER JOIN dbo.UFN_FAFEVENT_REVENUEDETAILS(@EVENTID,null,null,0) REE ON REE.CONSTITUENTID = C.ID
WHERE E.ID = ISNULL(@EVENTID, E.ID)
AND
(
RS.APPLICATION = ISNULL('Donation',RS.APPLICATION)
or
RS.APPLICATION = ISNULL('Matching gift',RS.APPLICATION)
or
RS.APPLICATION = ISNULL('Pending gift',RS.APPLICATION)
or
(
(RS.APPLICATION = 'Pending gift' AND R.TRANSACTIONTYPE = 'Payment' )
)
or
(
(RS.APPLICATION = 'Matching gift' AND R.TRANSACTIONTYPE = 'Payment' )
)
)
AND RS.APPLICATION <> 'Event registration' AND RS.APPLICATION <> 'Event sponsorship' AND RS.APPLICATION <> 'Pledge' AND RS.APPLICATION <> 'Recurring gift'
AND R.TRANSACTIONTYPE <> 'Pledge' AND R.TRANSACTIONTYPE <> 'Matching gift claim'
AND NOT ( RS.APPLICATION = 'Donation' AND R.TRANSACTIONTYPE = 'Recurring gift')
AND NOT ( RS.APPLICATION = 'Donation' AND R.TRANSACTIONTYPE = 'Pending gift' )
AND RS.DATEADDED BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND ISNULL(@ENDDATE, '12/30/2999')
--AND R.DATE BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND DATEADD(dd, 1, ISNULL(@ENDDATE, '12/30/2999'))
AND C.NAME LIKE ISNULL(@DONORNAME,C.NAME) + '%'
AND
(
(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)) + '%'
OR (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID)) + '%'
)
AND RS.AMOUNT > 0
-- Check site security
and
exists(
select 1 from (select dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) as SITEID) REVENUESITE
where (@ISADMIN = 1 or exists (select 1 from @SITESGRANTED SITESGRANTED where SITESGRANTED.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
)
)
ORDER BY C.NAME, [Donation Amount], [Gift Type], [Donation Date], [Participant Name], [Top Level Fundraising Group Name]
End
Else If @GIFTTYPECODE = 2 --Pledge
Begin
SELECT
CASE WHEN @WEBURL = '' THEN 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36), C.ID)
ELSE @WEBURL + convert(nvarchar(36), C.ID) END as CONSTITUENTID
,E.ID AS EVENTID
,C.NAME AS [Donor Name]
,RS.AMOUNT as [Donation Amount]
--,REE.[TRANSACTIONTYPE] AS [Gift Type]
--,RS.APPLICATION AS [Gift Type]
,CASE WHEN R.TRANSACTIONTYPE = 'Pledge' THEN R.TRANSACTIONTYPE
WHEN R.TRANSACTIONTYPE = 'Recurring gift' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Donation' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Pending gift' THEN R.TRANSACTIONTYPE
ELSE RS.APPLICATION END AS [Gift Type]
,CASE WHEN R.CONSTITUENTID IS NOT NULL THEN RS.DATEADDED END AS [Donation Date]
-- , isnull((SELECT CS.NAME FROM dbo.CONSTITUENT CS inner join dbo.REGISTRANT R ON R.CONSTITUENTID = CS.ID WHERE CS.ID=RR.CONSTITUENTID), E.NAME) as [Participant Name]
,CASE WHEN EXISTS(SELECT TE.TEAMCONSTITUENTID FROM TEAMEXTENSION TE INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TE.TEAMFUNDRAISINGTEAMID WHERE TE.TEAMCONSTITUENTID=RR.CONSTITUENTID)
-- THEN (SELECT DISTINCT TFT2.NAME FROM TEAMEXTENSION TE2 INNER JOIN TEAMFUNDRAISINGTEAM TFT2 ON TFT2.ID=TE2.TEAMFUNDRAISINGTEAMID WHERE TE2.TEAMCONSTITUENTID=RR.CONSTITUENTID)
THEN ''
WHEN ES.ID is not null then ''
WHEN NCU.ID is not null
then (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)
else '' END AS [Participant Name]
,(
CASE WHEN ISNULL(RR.AMOUNT,'') = '' THEN ''
WHEN R.TRANSACTIONTYPE = 'Pledge' AND REG.ID IS NULL THEN ''
WHEN R.TRANSACTIONTYPE = 'Recurring gift' AND REG.ID IS NULL THEN ''
WHEN ES.ID is not null then ''
WHEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID) IS NOT NULL THEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID)
ELSE (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) END
) AS [Top Level Fundraising Group Name]
FROM dbo.CONSTITUENT (NOLOCK) C
INNER JOIN REVENUE R (NOLOCK) ON R.CONSTITUENTID=C.ID
INNER JOIN dbo.REVENUESPLIT RS (NOLOCK) ON R.ID = RS.REVENUEID
INNER JOIN REVENUERECOGNITION RR (NOLOCK) ON RR.REVENUESPLITID = RS.ID
INNER JOIN EVENT E ON E.APPEALID=R.APPEALID
LEFT JOIN NETCOMMUNITYCLIENTUSER NCU ON NCU.CONSTITUENTID = RR.CONSTITUENTID
LEFT JOIN dbo.EVENTSPONSOR ES on ES.CONSTITUENTID = NCU.CONSTITUENTID
LEFT JOIN dbo.REGISTRANT REG on REG.CONSTITUENTID = C.ID and REG.EVENTID = E.ID
--INNER JOIN dbo.UFN_FAFEVENT_REVENUEDETAILS(@EVENTID,null,null,0) REE ON REE.CONSTITUENTID = C.ID
WHERE E.ID = ISNULL(@EVENTID, E.ID)
AND R.TRANSACTIONTYPE = ISNULL(@VARREVENUETYPE,R.TRANSACTIONTYPE )
AND (RS.APPLICATION <> 'Pledge' AND R.TRANSACTIONTYPE <> 'Payment')
AND RS.APPLICATION <> 'Event registration' AND RS.APPLICATION <> 'Event sponsorship'
AND R.TRANSACTIONTYPE <> 'Matching gift claim'
AND NOT ( RS.APPLICATION = 'Donation' AND R.TRANSACTIONTYPE = 'Recurring gift')
AND NOT ( RS.APPLICATION = 'Donation' AND R.TRANSACTIONTYPE = 'Pending gift' )
AND RS.DATEADDED BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND ISNULL(@ENDDATE, '12/30/2999')
--AND R.DATE BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND DATEADD(dd, 1, ISNULL(@ENDDATE, '12/30/2999'))
AND C.NAME LIKE ISNULL(@DONORNAME,C.NAME) + '%'
AND
(
(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)) + '%'
OR (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID)) + '%'
)
AND RS.AMOUNT > 0
-- Check site security
and
exists(
select 1 from (select dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) as SITEID) REVENUESITE
where (@ISADMIN = 1 or exists (select 1 from @SITESGRANTED SITESGRANTED where SITESGRANTED.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
)
)
ORDER BY C.NAME, [Donation Amount], [Gift Type], [Donation Date], [Participant Name], [Top Level Fundraising Group Name]
End
Else If @GIFTTYPECODE = 3 OR @GIFTTYPECODE = 4 OR @GIFTTYPECODE = 5 --Recurring gift, Matching gift, Pending gift
Begin
SELECT
CASE WHEN @WEBURL = '' THEN 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36), C.ID)
ELSE @WEBURL + convert(nvarchar(36), C.ID) END as CONSTITUENTID
,E.ID AS EVENTID
,C.NAME AS [Donor Name]
,RS.AMOUNT as [Donation Amount]
--,REE.[TRANSACTIONTYPE] AS [Gift Type]
,CASE WHEN R.TRANSACTIONTYPE = 'Pledge' THEN R.TRANSACTIONTYPE
WHEN R.TRANSACTIONTYPE = 'Recurring gift' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Donation' THEN R.TRANSACTIONTYPE
WHEN RS.APPLICATION = 'Pending gift' THEN R.TRANSACTIONTYPE
ELSE RS.APPLICATION END AS [Gift Type]
,CASE WHEN R.CONSTITUENTID IS NOT NULL AND @GIFTTYPECODE = 3 THEN RA.SCHEDULEDATE
WHEN R.CONSTITUENTID IS NOT NULL AND @GIFTTYPECODE <>3 THEN RS.DATEADDED
END AS [Donation Date]
-- , isnull((SELECT CS.NAME FROM dbo.CONSTITUENT CS inner join dbo.REGISTRANT R ON R.CONSTITUENTID = CS.ID WHERE CS.ID=RR.CONSTITUENTID), E.NAME) as [Participant Name]
,CASE WHEN EXISTS(SELECT TE.TEAMCONSTITUENTID FROM TEAMEXTENSION TE INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TE.TEAMFUNDRAISINGTEAMID WHERE TE.TEAMCONSTITUENTID=RR.CONSTITUENTID)
-- THEN (SELECT DISTINCT TFT2.NAME FROM TEAMEXTENSION TE2 INNER JOIN TEAMFUNDRAISINGTEAM TFT2 ON TFT2.ID=TE2.TEAMFUNDRAISINGTEAMID WHERE TE2.TEAMCONSTITUENTID=RR.CONSTITUENTID)
THEN ''
WHEN ES.ID is not null then ''
WHEN NCU.ID is not null
then (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)
else '' END AS [Participant Name]
,(
CASE WHEN ISNULL(RR.AMOUNT,'') = '' THEN ''
WHEN R.TRANSACTIONTYPE = 'Pledge' AND REG.ID IS NULL THEN ''
WHEN R.TRANSACTIONTYPE = 'Recurring gift' AND REG.ID IS NULL THEN ''
WHEN (RS.APPLICATION = 'Recurring gift' AND R.TRANSACTIONTYPE = 'Payment') AND REG.ID IS NULL THEN ''
WHEN (RS.APPLICATION = 'Matching gift' AND R.TRANSACTIONTYPE = 'Payment') AND REG.ID IS NULL THEN ''
WHEN ES.ID is not null then ''
WHEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID) IS NOT NULL THEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID)
ELSE (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) END
) AS [Top Level Fundraising Group Name]
FROM dbo.CONSTITUENT (NOLOCK) C
INNER JOIN REVENUE R (NOLOCK) ON R.CONSTITUENTID=C.ID
INNER JOIN dbo.REVENUESPLIT RS (NOLOCK) ON R.ID = RS.REVENUEID
INNER JOIN REVENUERECOGNITION RR (NOLOCK) ON RR.REVENUESPLITID = RS.ID
INNER JOIN EVENT E ON E.APPEALID=R.APPEALID
LEFT JOIN NETCOMMUNITYCLIENTUSER NCU ON NCU.CONSTITUENTID = RR.CONSTITUENTID
LEFT JOIN dbo.EVENTSPONSOR ES on ES.CONSTITUENTID = NCU.CONSTITUENTID
LEFT JOIN dbo.REGISTRANT REG on REG.CONSTITUENTID = C.ID and REG.EVENTID = E.ID
-- INNER JOIN dbo.UFN_FAFEVENT_REVENUEDETAILS(@EVENTID,null,null,0) REE ON REE.CONSTITUENTID = C.ID
LEFT JOIN dbo.RECURRINGGIFTACTIVITY RA (NOLOCK) ON RS.ID = RA.PAYMENTREVENUEID
WHERE E.ID = ISNULL(@EVENTID, E.ID)
AND RS.APPLICATION = ISNULL(@VARREVENUETYPE,RS.APPLICATION)
AND (RS.APPLICATION = @VARREVENUETYPE AND R.TRANSACTIONTYPE = 'Payment')
AND R.TRANSACTIONTYPE = 'Payment' AND RS.APPLICATION <> 'Event registration' AND RS.APPLICATION <> 'Event sponsorship'
AND R.TRANSACTIONTYPE <> 'Pledge' AND R.TRANSACTIONTYPE <> 'Matching gift claim'
--AND NOT ( RS.APPLICATION = 'Donation' AND R.TRANSACTIONTYPE = 'Recurring gift')
AND RS.DATEADDED BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND ISNULL(@ENDDATE, '12/30/2999')
--AND R.DATE BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND DATEADD(dd, 1, ISNULL(@ENDDATE, '12/30/2999'))
AND C.NAME LIKE ISNULL(@DONORNAME,C.NAME) + '%'
AND
(
(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID)) + '%'
OR (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID) LIKE ISNULL(@PARTICIPANTNAME,(SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=C.ID)) + '%'
)
AND RS.AMOUNT > 0
-- Check site security
and
exists(
select 1 from (select dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RS.DESIGNATIONID) as SITEID) REVENUESITE
where (@ISADMIN = 1 or exists (select 1 from @SITESGRANTED SITESGRANTED where SITESGRANTED.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
)
)
ORDER BY C.NAME, [Donation Amount], [Gift Type], [Donation Date], [Participant Name], [Top Level Fundraising Group Name]
End