USP_REPORT_EVENTAPPEALSUMMARY

      CREATE procedure dbo.USP_REPORT_EVENTAPPEALSUMMARY
      (
        @EVENTID uniqueidentifier = null,
        @STARTDATE datetime = null,
        @ENDDATE datetime = null,
        @INCLUDESUBEVENTS bit = 0,
        @CURRENCYCODE smallint = 1
      )
      as
        set nocount on;

        begin try
          declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
          declare @SELECTEDCURRENCYID uniqueidentifier;
          declare @DECIMALDIGITS tinyint;
          declare @ROUNDINGTYPECODE tinyint;
          declare @ISO4217 nvarchar(3);
          declare @CURRENCYSYMBOL nvarchar(5);
          declare @SYMBOLDISPLAYSETTINGCODE tinyint;

          declare @EVENTS table
          (
            ID uniqueidentifier
          );

          if @CURRENCYCODE = 0
            select @SELECTEDCURRENCYID = EVENT.BASECURRENCYID
            from dbo.EVENT
            where EVENT.ID = @EVENTID;
          else
            set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;

          select
            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
            @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
            @ISO4217 = CURRENCY.ISO4217,
            @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
            @SYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
          from dbo.CURRENCY
          where CURRENCY.ID = @SELECTEDCURRENCYID;

          if @INCLUDESUBEVENTS = 0
          begin
            insert into @EVENTS
            select @EVENTID;
          end
          else
          begin
            insert into @EVENTS
            select RELATEDEVENT.ID
            from dbo.EVENTHIERARCHY as RELATEDEVENT
              inner join dbo.EVENTHIERARCHY as SOURCEEVENT on SOURCEEVENT.ID = @EVENTID
            where RELATEDEVENT.HIERARCHYPATH.IsDescendantOf(SOURCEEVENT.HIERARCHYPATH) = 1;
          end;

          with [APPEALSINFO] as
          (
            select
              A.ID,
              sum(RSA.AMOUNTINCURRENCY) as AMOUNT,
              R.TYPECODE as TRANSACTIONTYPECODE,
              sum(
                case R.TYPECODE
                  when 1 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(R.ID, @SELECTEDCURRENCYID)
                  else 0.0
                end) as PLEDGED_UNPAID,
              sum(
                case R.TYPECODE
                  when 1 then dbo.UFN_PLEDGE_GETAMOUNTPAIDINCURRENCY(R.ID, @SELECTEDCURRENCYID)
                  else 0.0
                end) as PLEDGED_PAID,
              sum(
                case
                  when EVENTREGISTRANTPAYMENT.ID is null then RSA.AMOUNTINCURRENCY
                  else 0.0
                end) as AMOUNT_NOTREGISTRATION
            from @EVENTS EVENTS
              inner join dbo.EVENTAPPEAL EA on EVENTS.ID = EA.EVENTID
              left join dbo.APPEAL A on EA.APPEALID = A.ID
              left join dbo.REVENUE_EXT REX on EA.APPEALID = REX.APPEALID
              left join dbo.FINANCIALTRANSACTION R on REX.ID = R.ID
              left join dbo.FINANCIALTRANSACTIONLINEITEM RS on R.ID = RS.FINANCIALTRANSACTIONID
              left join dbo.REVENUESPLIT_EXT RSE on RS.ID = RSE.ID
              left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RSA on RSA.ID = RS.ID
              left join dbo.EVENTREGISTRANTPAYMENT on RSE.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
            where 
              EA.EVENTID in (select ID from @EVENTS) and
              R.ID is not null and
              R.DELETEDON is null and
              (
                RSE.APPLICATIONCODE in (0,4) or
                (
                  R.TYPECODE = 0 and
                  RSE.APPLICATIONCODE = 1 and
                  RSE.TYPECODE = 0
                )
              )
            group by A.ID, R.TYPECODE
          )
          select
            A.ID,
            A.NAME,
            G.GOALINCURRENCY,
            coalesce(DONATION.AMOUNT,0) DONATION_AMOUNT,
            case
              when A.GOAL = 0 then 0
              else coalesce(DONATION.AMOUNT,0) / G.GOALINCURRENCY * 100
            end as DONATION_PERCENT,
            coalesce(PLEDGE.PLEDGED_UNPAID,0) PLEDGED_UNPAID,
            case
              when A.GOAL = 0 then 0
              else coalesce(PLEDGE.PLEDGED_UNPAID,0) / G.GOALINCURRENCY * 100
            end as PLEDGED_UNPAID_PERCENT,
            coalesce(PLEDGE.PLEDGED_PAID,0) PLEDGED_PAID,
            case
              when A.GOAL = 0 then 0
              else coalesce(PLEDGE.PLEDGED_PAID,0) / G.GOALINCURRENCY * 100
            end as PLEDGED_PAID_PERCENT,
            coalesce(DONATION.AMOUNT,0) + coalesce(PLEDGE.PLEDGED_UNPAID,0) + coalesce(PLEDGE.PLEDGED_PAID,0) TOTAL,
            case
              when A.GOAL = 0 then 0
              else (coalesce(DONATION.AMOUNT,0) + coalesce(PLEDGE.PLEDGED_UNPAID,0) + coalesce(PLEDGE.PLEDGED_PAID,0)) / dbo.UFN_APPEAL_GETGOALINCURRENCY(A.ID, @SELECTEDCURRENCYID) * 100
            end as TOTAL_PERCENT,
            @ISO4217 [ISOCURRENCYCODE],
            @CURRENCYSYMBOL [CURRENCYSYMBOL],
            @SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
            @DECIMALDIGITS [DECIMALDIGITS],
            coalesce(DONATION.AMOUNT_NOTREGISTRATION, 0) DONATION_AMOUNT_NOTREGISTRATION
          from @EVENTS EVENTS
            inner join dbo.EVENTAPPEAL EA on EVENTS.ID = EA.EVENTID
            left join dbo.APPEAL A on EA.APPEALID = A.ID
            left join APPEALSINFO DONATION on DONATION.ID = A.ID and DONATION.TRANSACTIONTYPECODE in (0,3)
            left join APPEALSINFO PLEDGE on PLEDGE.ID = A.ID and PLEDGE.TRANSACTIONTYPECODE = 1
            left join dbo.UFN_APPEAL_GETGOALINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as G on G.ID = EA.APPEALID
          where EA.EVENTID in (select ID from @EVENTS);
        end try
        begin catch
          exec dbo.USP_RAISE_ERROR;
          return 1;
        end catch

        return 0;