USP_DATAFORMTEMPLATE_VIEW_FAFEVENTGIFTSUMMARY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FAFEVENTGIFTSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ADDRESS nvarchar(max) = null output,
    @EMAIL nvarchar(100) = null output,
    @PHONE nvarchar(20) = null output,
  @PREVIOUSDONOR bit = null output,
  @AMOUNT money = null output,
  @RECOGNITION nvarchar(max) = null output,
  @COMMCHANNEL nvarchar(max) = null output,
  @INBOUNDCHANNEL nvarchar(max) = null output,
  @PAYMENTMETHOD nvarchar(max) = null output,
  @TRANSACTIONTYPE nvarchar(max) = null output,
  @MATCHINGGIFTCLAIM money = null output,
  @MATCHINGGIFTPAID money = null output,
  @REVENUEID uniqueidentifier = null output,
  @RECOGNITIONCONSTITUENTID uniqueidentifier = null output,
  @MATCHINGGIFTCLAIMID uniqueidentifier = null output,
  @DONORCONSTITUENTID uniqueidentifier = null output,
  @REVENUESPLITID uniqueidentifier = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

  declare @EVENTID uniqueidentifier

    SELECT @EVENTID=EVENT.ID FROM REVENUE LEFT JOIN EVENT ON EVENT.APPEALID=REVENUE.APPEALID WHERE REVENUE.ID=@ID

    ;with 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

     union all

        select S.ID as RECORDID, C.NAME, 0 as TYPECODE, 'SPONSOR' as [TYPE], C.ID as CONSTITUENTID 
        from dbo.EVENTSPONSOR S With (NOLOCK)
        join dbo.CONSTITUENT C With (NOLOCK) on S.CONSTITUENTID = C.ID 
        where S.EVENTID = @EVENTID and C.ID not in (select CONSTITUENTID from  dbo.REGISTRANT R where R.EVENTID = @EVENTID)
    )

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.
    select @DATALOADED = 1,
      @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
      @EMAIL = EMAILADDRESS.EMAILADDRESS,
      @PHONE = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
      @PREVIOUSDONOR= case when PRIOREVENTREVENUE.ID is null then 0 else 1 end,
      @AMOUNT=RS.AMOUNT,
      @RECOGNITION=EM.NAME,
      @COMMCHANNEL=FECC.CHANNEL,
      @INBOUNDCHANNEL=CHANNELCODE.DESCRIPTION,
      @PAYMENTMETHOD=RPM.PAYMENTMETHOD,
      @TRANSACTIONTYPE=REVENUE.TRANSACTIONTYPE,
      @REVENUEID=REVENUE.ID,
      @RECOGNITIONCONSTITUENTID=EM.CONSTITUENTID,
      @DONORCONSTITUENTID=REVENUE.CONSTITUENTID,
      @REVENUESPLITID=RS.ID

      from dbo.REVENUE
          left join dbo.REVENUESPLIT RS (NOLOCK) on REVENUE.ID = RS.REVENUEID and (REVENUE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 7, 17) OR REVENUE.TRANSACTIONTYPECODE IN (1,2) )
          left join dbo.REVENUEPAYMENTMETHOD RPM With (NOLOCK) on RPM.REVENUEID = REVENUE.ID 
        left join dbo.REVENUERECEIPT RC With (NOLOCK) on RC.REVENUEID = REVENUE.ID   
        left join dbo.REVENUERECOGNITION RR With (NOLOCK) on RR.REVENUESPLITID = RS.ID
        left join dbo.CHANNELCODE on CHANNELCODE.ID=REVENUE.CHANNELCODEID
        left join dbo.FAFEVENTCOMMUNICATIONCHANNEL FECC on FECC.TYPEGUID=REVENUE.ID
                left join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
                left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
        left join dbo.EVENTEXTENSION on EVENTEXTENSION.EVENTID = @EVENTID
        left join dbo.EVENT PRIOREVENT on PRIOREVENT.ID=EVENTEXTENSION.PRIORYEAREVENTID
        left join dbo.REVENUE PRIOREVENTREVENUE on PRIOREVENTREVENUE.APPEALID=PRIOREVENT.APPEALID and PRIOREVENTREVENUE.CONSTITUENTID=CONSTITUENT.ID
        left join EventMembers EM on RR.CONSTITUENTID = EM.CONSTITUENTID
            where REVENUE.ID = @ID;

    ;with MatchingGiftClaims as
    (
      select
        coalesce((select sum(INSTALLMENTPAYMENT.AMOUNT) from dbo.INSTALLMENTPAYMENT where INSTALLMENTPAYMENT.PLEDGEID=MATCHINGCLAIM.ID), 0) As MATCHINGGIFTPAIDAMOUNT,
        MATCHINGCLAIM.Amount as MATCHINGGIFTCLAIMAMOUNT
        from dbo.REVENUEMATCHINGGIFT RMG
        inner join REVENUE MATCHINGCLAIM on RMG.ID=MATCHINGCLAIM.ID
        where RMG.MGSOURCEREVENUEID=@ID
    )

      --show an aggregate for matching gift claim and paid amounts (crm allows 1-n claims to be made on the same donation)
      select
        @MATCHINGGIFTCLAIM=sum(MATCHINGGIFTCLAIMAMOUNT),
        @MATCHINGGIFTPAID=sum(MATCHINGGIFTPAIDAMOUNT)
        from MatchingGiftClaims

    return 0;