UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_REGISTRANTONLY

create function [dbo].[UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_REGISTRANTONLY]
(
    @EVENTID    uniqueidentifier
)
RETURNS TABLE 
AS  
return

    select
        R.CONSTITUENTID,
        AMOUNTRAISED = ISNULL(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,0),
        DONORCOUNT = ISNULL(DONORCOUNT,0),
        GIFTCOUNT = ISNULL(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,0)
        , R.ID
    from Registrant R
    inner join dbo.FAFEVENTDONATIONOPTIONSCONFIG opt on opt.EVENTID = R.EVENTID    
    left outer join dbo.FAFRAISEDTOTAL fr ON R.eventid = fr.eventID AND R.constituentID = fr.constituentid
    where R.EVENTID = @EVENTID