USP_FAFRAISEDTOTAL_CALCULATE

CREATE procedure dbo.USP_FAFRAISEDTOTAL_CALCULATE
(
  @EVENTID uniqueidentifier
 ,@CONSTITUENTID uniqueidentifier
)

as
begin
  --limit to participants and teams/groups (with direct credits)

  if exists (select 1 from dbo.REGISTRANT R with (nolock) where R.EVENTID = @EVENTID and R.CONSTITUENTID = @CONSTITUENTID)
        or exists (select 1 from dbo.TEAMEXTENSION where eventID = @EVENTID and TEAMCONSTITUENTID = @CONSTITUENTID)
  begin 

    merge INTO dbo.FAFRAISEDTOTAL as Target
    using (
        select 
          REGAMOUNT
        , RECEIVEDAMOUNT
        , UNPAIDPLEDGEAMOUNT
        , UNPAIDRECURRINGAMOUNT
        , MATCHINGGIFTCLAIMAMOUNT
        , UNCONFIRMEDOFFLINEAMOUNT
        , DONORCOUNT
        , GIFTCOUNT_DONATION
        , GIFTCOUNT_UNPAIDPLEDGES_ON
        , GIFTCOUNT_UNPAIDPLEDGES_OFF
        , GIFTCOUNT_PENDINGMATCHINGGIFTS_ON
        , GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF
        , GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON
        , GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF
        , GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON
        , GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF
        , DATECHANGED = GETDATE()

        from dbo.[UFN_REVENUE_CALCPARTICIPANTRAISEDTOTAL] (@CONSTITUENTID,@EVENTID)
        )
    as source on (target.CONSTITUENTID = @CONSTITUENTID and target.EVENTID = @EVENTID)  
    when matched then 
      update set 
          REGAMOUNT = isnull(source.REGAMOUNT, 0)
        , RECEIVEDAMOUNT = isnull(source.RECEIVEDAMOUNT, 0)
        , UNPAIDPLEDGEAMOUNT = isnull(source.UNPAIDPLEDGEAMOUNT, 0)
        , UNPAIDRECURRINGAMOUNT = isnull(source.UNPAIDRECURRINGAMOUNT, 0)
        , MATCHINGGIFTCLAIMAMOUNT = isnull(source.MATCHINGGIFTCLAIMAMOUNT, 0)
        , UNCONFIRMEDOFFLINEAMOUNT = isnull(source.UNCONFIRMEDOFFLINEAMOUNT, 0)
        , DONORCOUNT = isnull(source.DONORCOUNT, 0)
        , GIFTCOUNT_DONATION = isnull(source.GIFTCOUNT_DONATION, 0)
        , GIFTCOUNT_UNPAIDPLEDGES_ON = isnull(source.GIFTCOUNT_UNPAIDPLEDGES_ON, 0)
        , GIFTCOUNT_UNPAIDPLEDGES_OFF = isnull(source.GIFTCOUNT_UNPAIDPLEDGES_OFF, 0)
        , GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = isnull(source.GIFTCOUNT_PENDINGMATCHINGGIFTS_ON, 0)
        , GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = isnull(source.GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF, 0)
        , GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = isnull(source.GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON, 0)
        , GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = isnull(source.GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF, 0)
        , GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = isnull(source.GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON, 0)
        , GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = isnull(source.GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF, 0)
        , DATECHANGED = source.DATECHANGED

    when not matched then
      insert (EVENTID, CONSTITUENTID
        , REGAMOUNT
        , RECEIVEDAMOUNT
        , UNPAIDPLEDGEAMOUNT
        , UNPAIDRECURRINGAMOUNT
        , MATCHINGGIFTCLAIMAMOUNT
        , UNCONFIRMEDOFFLINEAMOUNT
        , DONORCOUNT
        , GIFTCOUNT_DONATION
        , GIFTCOUNT_UNPAIDPLEDGES_ON
        , GIFTCOUNT_UNPAIDPLEDGES_OFF
        , GIFTCOUNT_PENDINGMATCHINGGIFTS_ON
        , GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF
        , GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON
        , GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF
        , GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON
        , GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF
        , DATECHANGED

     )  values(@EVENTID, @CONSTITUENTID
        , isnull(source.REGAMOUNT, 0)
        , isnull(source.RECEIVEDAMOUNT, 0)
        , isnull(source.UNPAIDPLEDGEAMOUNT, 0)
        , isnull(source.UNPAIDRECURRINGAMOUNT, 0)
        , isnull(source.MATCHINGGIFTCLAIMAMOUNT, 0)
        , isnull(source.UNCONFIRMEDOFFLINEAMOUNT, 0)
        , isnull(source.DONORCOUNT, 0)
        , isnull(source.GIFTCOUNT_DONATION, 0)
        , isnull(source.GIFTCOUNT_UNPAIDPLEDGES_ON, 0)
        , isnull(source.GIFTCOUNT_UNPAIDPLEDGES_OFF, 0)
        , isnull(source.GIFTCOUNT_PENDINGMATCHINGGIFTS_ON, 0)
        , isnull(source.GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF, 0)
        , isnull(source.GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON, 0)
        , isnull(source.GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF, 0)
        , isnull(source.GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON, 0)
        , isnull(source.GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF, 0) 
        , source.DATECHANGED   
      );   
  end
end