UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2

Provide participant raised total in a given FAF event, similar to UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL but more details based on revenue rule

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN

Definition

Copy




CREATE function [dbo].[UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2]
(
    @EVENTID    uniqueidentifier
)
RETURNS TABLE 
AS  
return

    select
        CONSTITUENTID,
        AMOUNTRAISED = RECEIVEDAMOUNT
            + CASE WHEN opt.ISREGISTRATIONREVENUE = 1 then REGAMOUNT else 0 end
            + CASE WHEN opt.ISUNPAIDPLEDGES = 1 then UNPAIDPLEDGEAMOUNT else 0 end
            + CASE WHEN opt.ISUNPAIDRECURRINGGIFTS = 1 then UNPAIDRECURRINGAMOUNT else 0 end
            + CASE WHEN opt.ISPENDINGMATCHINGGIFTS = 1 then MATCHINGGIFTCLAIMAMOUNT else 0 end
            + CASE WHEN opt.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then UNCONFIRMEDOFFLINEAMOUNT else 0 end,
        DONORCOUNT,
        GIFTCOUNT = GIFTCOUNT_DONATION
            + CASE WHEN opt.ISUNPAIDPLEDGES = 1 then GIFTCOUNT_UNPAIDPLEDGES_ON else GIFTCOUNT_UNPAIDPLEDGES_OFF end
            + CASE WHEN opt.ISUNPAIDRECURRINGGIFTS = 1 then GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
            + CASE WHEN opt.ISPENDINGMATCHINGGIFTS = 1 then GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
            + CASE WHEN opt.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end
    from dbo.FAFRAISEDTOTAL fr (nolock)
    inner join dbo.FAFEVENTDONATIONOPTIONSCONFIG opt on opt.EVENTID = fr.EVENTID
    where fr.EVENTID = @EVENTID