USP_REPORT_EVENTDONORS

      CREATE procedure dbo.USP_REPORT_EVENTDONORS
      (
        @EVENTID uniqueidentifier = null,
        @STARTDATE datetime = null,
        @ENDDATE datetime = null,
        @INCLUDESUBEVENTS bit = 0,
        @CURRENCYCODE tinyint = 0
      )
      as
      begin
        begin try
          declare @TOTALDONORS int = 0;
          declare @NEWDONORS int = 0;
          declare @RECURRINGDONORS int = 0;

          declare @TOTALAMOUNT money = 0;
          declare @NEWAMOUNT money = 0;
          declare @RECURRINGAMOUNT money = 0;

          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

          declare @EARLIESTEVENTTIME datetime;
          select @EARLIESTEVENTTIME = MIN(EVENT.STARTDATE)
          from @EVENTS EVENTS
            inner join dbo.EVENT on EVENTS.ID = EVENT.ID;

          -- Drop temp table if needed

          if object_id('tempdb..#REPORT_EVENTDONORS_REVENUE') is not null
            drop table #REPORT_EVENTDONORS_REVENUE;

          create table #REPORT_EVENTDONORS_REVENUE
          (
            ID uniqueidentifier,
            AMOUNT money
          );

          --Get all event registration amounts paid

          insert into #REPORT_EVENTDONORS_REVENUE
          select
            REGISTRANT.CONSTITUENTID,
            coalesce(RS.AMOUNTINCURRENCY, 0)
          from @EVENTS EVENTS
            inner join dbo.REGISTRANT on EVENTS.ID = REGISTRANT.EVENTID
            inner join dbo.EVENTREGISTRANTPAYMENT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID
          where 
            REVENUESPLIT.DELETEDON is null and
            REGISTRANT.EVENTID in (select ID from @EVENTS) and
            REGISTRANT.CONSTITUENTID is not null;

          --Get all event appeal donations from registrants

          insert into #REPORT_EVENTDONORS_REVENUE
          select
            REVENUE.CONSTITUENTID,
            coalesce(R.AMOUNTINCURRENCY, 0)
          from @EVENTS EVENTS
            inner join dbo.EVENTAPPEAL on EVENTS.ID = EVENTAPPEAL.EVENTID
            inner join dbo.REVENUE_EXT on EVENTAPPEAL.APPEALID = REVENUE_EXT.APPEALID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE_EXT.ID = REVENUE.ID
            inner join dbo.REGISTRANT on REVENUE.CONSTITUENTID = REGISTRANT.CONSTITUENTID and EVENTAPPEAL.EVENTID = REGISTRANT.EVENTID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUE.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            left join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as R on R.ID = REVENUE.ID
          where
            REVENUE.DELETEDON is null and
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
            EVENTAPPEAL.EVENTID in (select ID from @EVENTS) and
            --Event registration donation doesn't need to be added because it's already counted in payment

            (
              REVENUE.TYPECODE in (0,1,3) and
              REVENUESPLIT_EXT.APPLICATIONCODE in (0,4)
            );

          --Get all event appeal donations from non-registrants

          insert into #REPORT_EVENTDONORS_REVENUE
          select
            REVENUE.CONSTITUENTID,
            coalesce(R.AMOUNTINCURRENCY, 0)
          from @EVENTS EVENTS
            inner join dbo.EVENTAPPEAL on EVENTS.ID = EVENTAPPEAL.EVENTID
            inner join dbo.REVENUE_EXT on EVENTAPPEAL.APPEALID = REVENUE_EXT.APPEALID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE_EXT.ID = REVENUE.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUE.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            left join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as R on R.ID = REVENUE.ID
          where
            REVENUE.DELETEDON is null and
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
            EVENTAPPEAL.EVENTID in (select ID from @EVENTS) and
            REVENUE.CONSTITUENTID not in 
            (
              select CONSTITUENTID
              from @EVENTS EVENTS
                inner join dbo.REGISTRANT on EVENTS.ID = REGISTRANT.EVENTID
              where
                EVENTID in (select ID from @EVENTS) and
                CONSTITUENTID is not null
            ) and
            --Event registration donation doesn't need to be added because it's already counted in payment

            (
              REVENUE.TYPECODE in (0,1,3) and
              REVENUESPLIT_EXT.APPLICATIONCODE  in (0,4)
            );

          -- Drop temp table if needed

          if object_id('tempdb..#REPORT_EVENTDONORS_RECURRINGIDS') is not null
            drop table #REPORT_EVENTDONORS_RECURRINGIDS;

          create table #REPORT_EVENTDONORS_RECURRINGIDS
          (
            ID uniqueidentifier
          );

          --Determine if any of the registrants who have paid their registration are a recurring donor

          insert into #REPORT_EVENTDONORS_RECURRINGIDS
          select distinct REGISTRANT.CONSTITUENTID
          from dbo.EVENTREGISTRANTPAYMENT
            inner join dbo.REGISTRANT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
            inner join dbo.REGISTRANT CURRENTEVENT on 
              REGISTRANT.CONSTITUENTID = CURRENTEVENT.CONSTITUENTID and
              CURRENTEVENT.EVENTID in (select ID from @EVENTS) and
              CURRENTEVENT.CONSTITUENTID is not null
          where
            REGISTRANT.EVENTID not in (select ID from @EVENTS) and
            REGISTRANT.DATEADDED < CURRENTEVENT.DATEADDED;

          --Determine if any registrants who have donated to the event appeal are recurring donors

          insert into #REPORT_EVENTDONORS_RECURRINGIDS
          select distinct REVENUE.CONSTITUENTID
          from @EVENTS EVENTS
            inner join dbo.REGISTRANT on EVENTS.ID = REGISTRANT.EVENTID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REGISTRANT.CONSTITUENTID = REVENUE.CONSTITUENTID
          where
            REVENUE.DELETEDON is null and
            REVENUE.ID not in
            (
              select FINANCIALTRANSACTION.ID
              from dbo.FINANCIALTRANSACTION
                inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                inner join dbo.EVENTAPPEAL on REVENUE_EXT.APPEALID = EVENTAPPEAL.APPEALID
                inner join dbo.REGISTRANT on  REVENUE.CONSTITUENTID = REGISTRANT.CONSTITUENTID and EVENTAPPEAL.EVENTID = REGISTRANT.EVENTID
                inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
            ) and
            REGISTRANT.EVENTID in (select ID from @EVENTS) and
            REVENUE.DATE < @EARLIESTEVENTTIME and
            REVENUE.CONSTITUENTID not in (select ID from #REPORT_EVENTDONORS_RECURRINGIDS);

          --Determine if any non-registrant who has donated to the event appeal is a recurring donor

          insert into #REPORT_EVENTDONORS_RECURRINGIDS
          select distinct REVENUE.CONSTITUENTID
          from #REPORT_EVENTDONORS_REVENUE CONSTITUENTS
            inner join dbo.FINANCIALTRANSACTION REVENUE on CONSTITUENTS.ID = REVENUE.CONSTITUENTID
          where
            REVENUE.ID not in
            (
              select CURRENTREVENUE.ID
              from @EVENTS EVENTS
                inner join dbo.EVENTAPPEAL on EVENTS.ID = EVENTAPPEAL.EVENTID
                inner join dbo.REVENUE_EXT on EVENTAPPEAL.APPEALID = REVENUE_EXT.APPEALID
                inner join dbo.FINANCIALTRANSACTION CURRENTREVENUE on REVENUE_EXT.ID = CURRENTREVENUE.ID
              where
                EVENTAPPEAL.EVENTID in (select ID from @EVENTS) and
                CURRENTREVENUE.CONSTITUENTID not in
                (
                  select REGISTRANT.CONSTITUENTID
                  from @EVENTS EVENTS
                    inner join dbo.REGISTRANT on EVENTS.ID = REGISTRANT.EVENTID
                  where
                    REGISTRANT.EVENTID in (select ID from @EVENTS) and
                    REGISTRANT.CONSTITUENTID is not null
                )
            ) and
            REVENUE.DATE < @EARLIESTEVENTTIME and
            REVENUE.CONSTITUENTID not in (select ID from #REPORT_EVENTDONORS_RECURRINGIDS) and
            REVENUE.CONSTITUENTID in (select ID from #REPORT_EVENTDONORS_REVENUE);

          -- Drop temp table if needed

          if object_id('tempdb..#REPORT_EVENTDONORS_RECURRINGREVENUE') is not null
            drop table #REPORT_EVENTDONORS_RECURRINGREVENUE;

          create  table #REPORT_EVENTDONORS_RECURRINGREVENUE
          (
            ID uniqueidentifier,
            AMOUNT money
          );

          insert into #REPORT_EVENTDONORS_RECURRINGREVENUE
          select
            REGISTRANT.CONSTITUENTID,
            coalesce(RS.AMOUNTINCURRENCY, 0.0)
          from dbo.EVENTREGISTRANTPAYMENT
            inner join dbo.REGISTRANT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID
          where
            REVENUESPLIT.DELETEDON is null and
            REGISTRANT.EVENTID in (select ID from @EVENTS) and
            REGISTRANT.CONSTITUENTID is not null and
            REGISTRANT.CONSTITUENTID in (select ID from #REPORT_EVENTDONORS_RECURRINGIDS);

          insert into #REPORT_EVENTDONORS_RECURRINGREVENUE
          select
            REGISTRANT.CONSTITUENTID,
            coalesce(R.AMOUNTINCURRENCY, 0.0)
          from @EVENTS EVENTS
            inner join dbo.EVENTAPPEAL on EVENTS.ID = EVENTAPPEAL.EVENTID
            inner join dbo.REGISTRANT on EVENTAPPEAL.EVENTID = REGISTRANT.EVENTID
            inner join #REPORT_EVENTDONORS_RECURRINGIDS CONSTITUENTS on REGISTRANT.CONSTITUENTID = CONSTITUENTS.ID
            inner join dbo.REVENUE_EXT on EVENTAPPEAL.APPEALID = REVENUE_EXT.APPEALID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE_EXT.ID = REVENUE.ID and REGISTRANT.CONSTITUENTID = REVENUE.CONSTITUENTID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUE.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            left join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as R on R.ID = REVENUE.ID
          where
            REVENUE.DELETEDON is null and
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
            REGISTRANT.EVENTID in (select ID from @EVENTS) and
            REGISTRANT.CONSTITUENTID is not null and
            --Event registration donation doesn't need to be added because it's already counted in payment

            (
              REVENUE.TYPECODE in (0,1,3) and
              REVENUESPLIT_EXT.APPLICATIONCODE  in (0,3,4)
            );

          insert into #REPORT_EVENTDONORS_RECURRINGREVENUE
          select
            REVENUE.CONSTITUENTID,
            coalesce(R.AMOUNTINCURRENCY, 0)
          from @EVENTS EVENTS
            inner join dbo.EVENTAPPEAL on EVENTS.ID = EVENTAPPEAL.EVENTID
            inner join dbo.REVENUE_EXT on EVENTAPPEAL.APPEALID = REVENUE_EXT.APPEALID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE_EXT.ID = REVENUE.ID
            inner join #REPORT_EVENTDONORS_RECURRINGIDS CONSTITUENTS on REVENUE.CONSTITUENTID = CONSTITUENTS.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUE.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            left join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as R on R.ID = REVENUE.ID
          where
            REVENUE.DELETEDON is null and
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
            EVENTAPPEAL.EVENTID in (select ID from @EVENTS) and
            REVENUE.CONSTITUENTID in (select ID from #REPORT_EVENTDONORS_RECURRINGIDS) and
            REVENUE.CONSTITUENTID not in
            (
              select CONSTITUENTID
              from @EVENTS EVENTS
                inner join dbo.REGISTRANT on EVENTS.ID = REGISTRANT.EVENTID
              where
                EVENTID in (select ID from @EVENTS) and
                CONSTITUENTID is not null
            ) and
            --Event registration donation doesn't need to be added because it's already counted in payment

            (
              REVENUE.TYPECODE in (0,1,3) and
              REVENUESPLIT_EXT.APPLICATIONCODE  in (0,3,4)
            );

          select
            @TOTALDONORS = coalesce(count(distinct ID), 0),
            @TOTALAMOUNT = coalesce(SUM(AMOUNT), 0)
          from #REPORT_EVENTDONORS_REVENUE;

          select 
            @RECURRINGDONORS = coalesce(COUNT(distinct ID), 0),
            @RECURRINGAMOUNT = coalesce(SUM(AMOUNT), 0)
          from #REPORT_EVENTDONORS_RECURRINGREVENUE;

          set @NEWDONORS = @TOTALDONORS - @RECURRINGDONORS;
          set @NEWAMOUNT = @TOTALAMOUNT - @RECURRINGAMOUNT;

          drop table #REPORT_EVENTDONORS_REVENUE;
          drop table #REPORT_EVENTDONORS_RECURRINGIDS;
          drop table #REPORT_EVENTDONORS_RECURRINGREVENUE;

          select
            @NEWDONORS as NEWDONORS,
            @NEWAMOUNT as NEWAMOUNT,
            case
              when @NEWDONORS = 0 then 0
              else @NEWAMOUNT / @NEWDONORS
            end as NEWAVG,
            @RECURRINGDONORS as RECURRINGDONORS,
            @RECURRINGAMOUNT as RECURRINGAMOUNT,
            case
              when @RECURRINGDONORS = 0 then 0
              else @RECURRINGAMOUNT / @RECURRINGDONORS
            end as RECURRINGAVG,
            @NEWDONORS + @RECURRINGDONORS as TOTALDONORS,
            @ISO4217 [ISOCURRENCYCODE],
            @CURRENCYSYMBOL [CURRENCYSYMBOL],
            @SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
            @DECIMALDIGITS [DECIMALDIGITS];
        end try
        begin catch
          exec dbo.USP_RAISE_ERROR;
          return 1;
        end catch

        return 0;
      end