USP_REPORT_EVENTCOMPARISON

      create procedure dbo.USP_REPORT_EVENTCOMPARISON
       (@EVENTID1 as uniqueidentifier = null,
         @EVENTID2 as uniqueidentifier = null,
         @IDSETREGISTERID as uniqueidentifier = null,
         @USESELECTION as bit = null,
         @ENTERPRISEINSTALLED as bit = null,
         @REPORTUSERID as nvarchar(100) = null,
         @CURRENCYCODE smallint = null, --3 = My base, (null, 1) = Organization;

         @ALTREPORTUSERID nvarchar(128) = null
        )

        with execute as owner
        as
        set nocount on;

      declare @EVENTFILTER table(ID uniqueidentifier primary key);
      declare @EVENTTEAMFUNDRAISINGINFO table(ID uniqueidentifier, AMOUNTSOLICITED money, PAID bit)
      declare @EVENTAPPEALINFO table(ID uniqueidentifier, AMOUNTSOLICITED money)
      declare @EVENTEXPENSES table(EVENTID uniqueidentifier, BUDGETEDAMOUNT money, AGREEDAMOUNT money, ACTUALAMOUNT money);
      declare @SELECTEDCURRENCYID uniqueidentifier;
      declare @CURRENTAPPUSERID uniqueidentifier;
      declare @DECIMALDIGITS tinyint;
      declare @ROUNDINGTYPECODE tinyint;

      -- SHL BBIS Bug 325325; FAF does not use the EventAppeal table so we're going to account for that by saving all appeals into @EVENTAPPEALSTABLE

      declare @EVENTAPPEALTABLE table(EVENTID uniqueidentifier, APPEALID uniqueidentifier);

      set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

      if @IDSETREGISTERID is null
        begin
         set @IDSETREGISTERID = newid()
        end

      if @USESELECTION = 0
        begin
          insert into @EVENTFILTER(ID) values (@EVENTID1)
          insert into @EVENTFILTER(ID) values (@EVENTID2)
        end
      else
        begin
          insert into @EVENTFILTER(ID)
             (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID))
        end
      if @CURRENCYCODE = 3
        begin
           if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
             begin                                       
                select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                from dbo.CURRENCYSET
                where
                CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
             end
           else
              begin
                 select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                 from dbo.CURRENCYSET
                 where CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
              end
        end
      else
        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

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

      insert into @EVENTTEAMFUNDRAISINGINFO(ID,AMOUNTSOLICITED,PAID)
        select
          EVENT.ID,
          case when REVENUE.ID is null then 0 

      else 
         sum(coalesce(RSOL.AMOUNTINCURRENCY,0)) end TOTAL,
         case when count(case when EVENTREGISTRANTPAYMENT.ID is null then 0 else 1 end) > 0 then 1 else 0 end PAID
         from dbo.EVENT 
           inner join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.APPEALID = EVENT.APPEALID
           inner join @EVENTFILTER EVENTFILTER on EVENTFILTER.ID = EVENT.ID
           left join dbo.TEAMFUNDRAISINGTEAMMEMBER on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID 
           left join dbo.TEAMFUNDRAISER 
         on TEAMFUNDRAISER.APPEALID = EVENT.APPEALID and TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
            left join dbo.REVENUESOLICITOR on REVENUESOLICITOR.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
            left join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
            left join dbo.REVENUE with (nolock) on REVENUE.APPEALID = EVENT.APPEALID and REVENUESPLIT.REVENUEID = REVENUE.ID 
            and (
                  (
                    (REVENUE.TRANSACTIONTYPECODE = 1 
                       or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,3))
                       --adding event registration donation which will be removed when counting total income

                       or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)
                    )   --Revenue is a pledge, gift, and recurring gift payment

                  )    --Fundraiser has raised money for the event/appeal in question

                  or REVENUESOLICITOR.ID is null    --Fundraiser has not raised anything yet

                  or ((not REVENUESOLICITOR.ID is null) and REVENUE.ID is null)    --Fundraiser has raised money, but it is not for the appeal/event in question

                )
            left join dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as RSOL on RSOL.ID = REVENUESOLICITOR.ID         
            left join dbo.EVENTREGISTRANTPAYMENT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
            group by EVENT.ID, REVENUE.ID;

      -- SHL BBIS Bug 325325; Fill @EVENTAPPEALTABLE table with all of the appeals for the 2 events

      insert into @EVENTAPPEALTABLE(EVENTID, APPEALID)
      -- grab the single appeal assigned to the event in the Event table (deprecated but still used in NGFAF)

      select Event.ID, Event.APPEALID from dbo.Event inner join @EVENTFILTER EVENTFILTER on Event.ID = EVENTFILTER.ID
      union
      -- grab the appeals assigned to the events in the newer EventAppeal table

      select EVENTAPPEAL.EVENTID, EVENTAPPEAL.APPEALID from dbo.EVENTAPPEAL inner join @EVENTFILTER EVENTFILTER on EVENTAPPEAL.EVENTID = EVENTFILTER.ID;


      insert into @EVENTAPPEALINFO(ID,AMOUNTSOLICITED)
        select
           EVENT.ID,
           case when REVENUE.ID is null then 0 
            else 
              coalesce(sum(RS.AMOUNTINCURRENCY), 0) 
              end TOTAL
              from dbo.EVENT
                 inner join @EVENTFILTER EVENTFILTER on EVENTFILTER.ID = EVENT.ID
                 inner join @EVENTAPPEALTABLE EVENTAPPEAL on EVENTAPPEAL.EVENTID = EVENT.ID
                 inner join dbo.REVENUE with (nolock) on REVENUE.APPEALID = EVENTAPPEAL.APPEALID
                 inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                 and (
                       (
                         (REVENUE.TRANSACTIONTYPECODE = 1 
                          or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,3))
                         )   --Revenue is a pledge, gift, and recurring gift payment

                       )    --Fundraiser has raised money for the event/appeal in question

                            --Event registration donation doesn't need to be added because it's already counted in payment

                      )
                 left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID  
                 group by EVENT.ID,REVENUE.ID

        insert into @EVENTEXPENSES(EVENTID,BUDGETEDAMOUNT, AGREEDAMOUNT, ACTUALAMOUNT)
          select
             E.ID as EVENTID,
             coalesce(sum(EE.BUDGETEDAMOUNTINCURRENCY), 0) as BUDGETEDAMOUNT,
             coalesce(sum(EE.ACTUALAMOUNTINCURRENCY), 0) as AGREEDAMOUNT,
             coalesce(sum(EE.AMOUNTPAIDINCURRENCY), 0) as ACTUALAMOUNT                          
          from @EVENTFILTER as E
            inner join dbo.UFN_EVENTEXPENSE_GETINCURRENCY_BULK(@SELECTEDCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as EE on E.ID = EE.EVENTID
          group by E.ID

        select distinct
          'http://www.blackbaud.com/EVENTID?EVENTID='+convert(nvarchar(36),EVENT.ID) as EVENTID,
          EVENT.ID,
          EVENT.NAME,
          coalesce(EE.ACTUALAMOUNT, 0) as ACTUALAMOUNT,
                       ( --Payments

                                select coalesce(sum(RS.AMOUNTINCURRENCY), 0) 
                                         from dbo.EVENTREGISTRANTPAYMENT P 
                                         inner join dbo.REGISTRANT on P.REGISTRANTID = REGISTRANT.ID
                                         inner join dbo.REVENUESPLIT on P.PAYMENTID = REVENUESPLIT.ID 
                                         left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID
                                         where REGISTRANT.EVENTID = EVENT.ID
                                         ) - 
                       ( --Credits

                                select coalesce(sum([CREDITITEM].[TOTAL]), 0) 
                                         from dbo.[CREDITITEM]
                                         inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [CREDITITEM].[SALESORDERITEMID]
                                         inner join dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
                                         inner join dbo.[REGISTRANT] on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
                                         where [REGISTRANT].[EVENTID] = [EVENT].[ID]
                       ) +
          ( -- Teamfundraising totals

            coalesce((select sum(AMOUNTSOLICITED) from @EVENTTEAMFUNDRAISINGINFO EVENTTEAMFUNDRAISINGINFO where EVENTTEAMFUNDRAISINGINFO.ID = EVENT.ID and PAID = 0),0)
          ) +
          ( -- Appeal income

            coalesce((select sum(AMOUNTSOLICITED) from @EVENTAPPEALINFO EVENTAPPEALINFO where EVENTAPPEALINFO.ID = EVENT.ID),0)
          )
          as [TOTALINCOME],
                       (select count(ID) 
                                from dbo.REGISTRANT 
                                where EVENTID = EVENT.ID  
                                and dbo.[UFN_REGISTRANT_ISCANCELLED](REGISTRANT.ID) = 0) as REGISTERED,
                       (select count(ID) 
                                from dbo.REGISTRANT 
                                where EVENTID = EVENT.ID 
                                and ATTENDED = 1  and dbo.[UFN_REGISTRANT_ISCANCELLED](REGISTRANT.ID) = 0) as ATTENDED,
          coalesce((select COUNT(TEAMFUNDRAISINGTEAM.ID)
                        from EVENT as INNER_EVENT
                        left join TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.APPEALID = INNER_EVENT.APPEALID
                        where INNER_EVENT.ID = EVENT.ID),0) as TEAMCOUNT,   
          coalesce((select sum(AMOUNTSOLICITED) from @EVENTTEAMFUNDRAISINGINFO EVENTTEAMFUNDRAISINGINFO where EVENTTEAMFUNDRAISINGINFO.ID = EVENT.ID),0) as TEAMTOTALS,
          case coalesce((select COUNT(TEAMFUNDRAISINGTEAM.ID)
                        from EVENT as INNER_EVENT
                        left join TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.APPEALID = INNER_EVENT.APPEALID
                        where INNER_EVENT.ID = EVENT.ID),0)
            when 0 then 0
            else coalesce((select sum(AMOUNTSOLICITED) from @EVENTTEAMFUNDRAISINGINFO EVENTTEAMFUNDRAISINGINFO where EVENTTEAMFUNDRAISINGINFO.ID = EVENT.ID),0)/coalesce((select COUNT(TEAMFUNDRAISINGTEAM.ID)
                        from EVENT as INNER_EVENT
                        left join TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.APPEALID = INNER_EVENT.APPEALID
                        where INNER_EVENT.ID = EVENT.ID),0)
end as AVERAGEPERTEAM,
          EVENT.STARTDATE,
                   CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                   CURRENCYPROPERTIES.CURRENCYSYMBOL,
                   CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                   CURRENCYPROPERTIES.DECIMALDIGITS,
                   coalesce(EE.AGREEDAMOUNT, 0) as AGREEDAMOUNT,
                   coalesce(EE.BUDGETEDAMOUNT, 0) as BUDGETEDAMOUNT
        from dbo.EVENT
        inner join @EVENTFILTER EVENTFILTER on EVENTFILTER.ID = EVENT.ID
        left join @EVENTEXPENSES EE on EE.EVENTID = EVENTFILTER.ID
        left join TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.APPEALID = EVENT.APPEALID
        left join TEAMFUNDRAISINGTEAMMEMBER on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
        left join dbo.TEAMFUNDRAISER
          on TEAMFUNDRAISER.APPEALID = EVENT.APPEALID and TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
        left join REVENUESOLICITOR on REVENUESOLICITOR.CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
        left join REVENUESPLIT on REVENUESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
        left join dbo.REVENUE with (nolock) on REVENUE.APPEALID = EVENT.APPEALID and REVENUESPLIT.REVENUEID = REVENUE.ID
        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
        --left join dbo.UFN_EVENTEXPENSE_GETINCURRENCY_BULK(@SELECTEDCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as EE on EE.EVENTID = EVENT.ID

        where EVENT.PROGRAMID is null
        group by EVENT.STARTDATE,EVENT.ID,EVENT.NAME, CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS, EE.ACTUALAMOUNT, EE.AGREEDAMOUNT, EE.BUDGETEDAMOUNT
        order by EVENT.STARTDATE DESC