USP_REPORT_EVENTSUMMARY

CREATE procedure dbo.USP_REPORT_EVENTSUMMARY
(
    @IDSETREGISTERID uniqueidentifier = null,
    @TO datetime,
    @FROM datetime,
    @INCLUDEINACTIVE bit = 0,
    @CURRENCYCODE smallint = null,
    @CURRENTAPPUSERID uniqueidentifier = null            
)
as
    set nocount on;
    set transaction isolation level read uncommitted;

    begin try

        declare @USERGRANTEDEVENTPAGE bit = 0;

        if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
            begin
                select 
                    @USERGRANTEDEVENTPAGE = 1
            end
        else
            begin
                select 
                    @USERGRANTEDEVENTPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '03E8FBDC-7E2C-496D-8322-405FCFF75854')
            end        

        declare @IS_ENTERPRISE bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046');
        declare @IS_SAME_CURRENCY bit = 1

        set @FROM = dbo.[UFN_DATE_GETEARLIESTTIME](@FROM);
        set @TO = dbo.[UFN_DATE_GETLATESTTIME](@TO);

    if @CURRENCYCODE = 0 --if using base currency check if all events have the same base currency

      begin

        declare @FLAGCURRENCY uniqueidentifier
          set @FLAGCURRENCY = (select top 1(EVENT.BASECURRENCYID) from EVENT where EVENT.STARTDATE between @FROM and @TO
                                                          and (@INCLUDEINACTIVE = 1 or EVENT.ISACTIVE = 1)
                                                          and (@CURRENTAPPUSERID is null or dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)
                                                          and (EVENT.PROGRAMID is null))
          if @FLAGCURRENCY is not null
          begin

                if ((select count(*) from dbo.EVENT where EVENT.STARTDATE between @FROM and @TO
                                                            and (@INCLUDEINACTIVE = 1 or EVENT.ISACTIVE = 1)
                                                            and (@CURRENTAPPUSERID is null or dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)
                                                            and (EVENT.PROGRAMID is null))

                <>

                (select count(*) from dbo.EVENT where EVENT.STARTDATE between @FROM and @TO
                                                            and (@INCLUDEINACTIVE = 1 or EVENT.ISACTIVE = 1)
                                                            and (@CURRENTAPPUSERID is null or dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)
                                                            and (EVENT.PROGRAMID is null)
                                                                                  and EVENT.BASECURRENCYID = @FLAGCURRENCY))
                  begin        
                        set @IS_SAME_CURRENCY = 0
                  end
            end
      end

        if @IDSETREGISTERID is null
            select
                EVENT.NAME as EVENTNAME,
                APPEAL.NAME as APPEAL,
                EVENT.STARTDATE,
                EVENT.CAPACITY,
                (select count(ID) from dbo.INVITEE where EVENTID = EVENT.ID) as INVITED,
                (select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and dbo.[UFN_REGISTRANT_ISCANCELLED](ID) = 0) as REGISTERED,
                (select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and ATTENDED = 1 and dbo.[UFN_REGISTRANT_ISCANCELLED](ID) = 0) as ATTENDED,
                ( --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(CURRENCY.ID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),CURRENCY.DECIMALDIGITS,CURRENCY.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]
                ) + ( --Appeals

                    case when @IS_ENTERPRISE = 1 AND @CURRENCYCODE = 0 then
                            (
                              select coalesce(sum(dbo.UFN_APPEAL_GETTOTALREVENUEINCURRENCY(EVENTAPPEAL.APPEALID, ZEP.BASECURRENCYID)), 0)
                              from dbo.EVENT ZEP
                              inner join dbo.EVENTAPPEAL on EVENTAPPEAL.EVENTID = EVENT.ID
                              where ZEP.ID = EVENT.ID
                            )
                        when @IS_ENTERPRISE = 1 AND @CURRENCYCODE <> 0 then
                            (
                              select coalesce(sum(dbo.UFN_APPEAL_GETTOTALREVENUEINCURRENCY(EVENTAPPEAL.APPEALID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())), 0)
                              from dbo.EVENT ZEP
                              inner join dbo.EVENTAPPEAL on EVENTAPPEAL.EVENTID = EVENT.ID
                              where ZEP.ID = EVENT.ID
                            )
                        else
                            0
                    end
                ) + ( --Team fundraising appeal

                    case when @IS_ENTERPRISE = 1 then
            coalesce(dbo.UFN_APPEAL_GETTOTALREVENUEINCURRENCY(EVENT.APPEALID, CURRENCY.ID), 0)
          else
                        0
                    end
                ) - ( --Team fundraising appeals that are also event registration - donations are double counted

                    select coalesce(sum(RS.AMOUNTINCURRENCY), 0)
                    from dbo.TEAMFUNDRAISINGTEAM
                    inner join dbo.REVENUE on TEAMFUNDRAISINGTEAM.APPEALID = REVENUE.APPEALID
          inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
          inner join dbo.EVENTREGISTRANTPAYMENT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
          inner join dbo.REGISTRANT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID and REGISTRANT.EVENTID = EVENT.ID
                    left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(CURRENCY.ID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),CURRENCY.DECIMALDIGITS,CURRENCY.ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID    
                    where TEAMFUNDRAISINGTEAM.APPEALID = EVENT.APPEALID and (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)
                ) - ( --Appeals that are also event registration - donations are double counted

                    select coalesce(sum(RS.AMOUNTINCURRENCY), 0)
                    from dbo.EVENTAPPEAL
                    inner join dbo.REVENUE with (nolock) on REVENUE.APPEALID = EVENTAPPEAL.APPEALID
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(CURRENCY.ID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),CURRENCY.DECIMALDIGITS,CURRENCY.ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID
                    where EVENTAPPEAL.EVENTID = EVENT.ID and (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)
                ) as [TOTALINCOME],
                (
          select
            coalesce(sum(EE.ACTUALAMOUNTINCURRENCY), 0)
          from dbo.EVENTEXPENSE
          left join dbo.UFN_EVENTEXPENSE_GETINCURRENCY_BULK(CURRENCY.ID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),CURRENCY.DECIMALDIGITS,CURRENCY.ROUNDINGTYPECODE) as EE on EE.ID = EVENTEXPENSE.ID
          where EVENTEXPENSE.EVENTID = EVENT.ID) as ACTUALAMOUNT,
          CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                  CURRENCYPROPERTIES.CURRENCYSYMBOL,
                  CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                  CURRENCY.DECIMALDIGITS,
          @IS_SAME_CURRENCY as SAMECURRENCY,
                    EVENT.ID as EVENTID,
                    @USERGRANTEDEVENTPAGE as USERGRANTEDEVENTPAGE
            from
                dbo.EVENT
            left join
                dbo.APPEAL on EVENT.APPEALID = APPEAL.ID
      left join
        dbo.CURRENCY on (CURRENCY.ID = EVENT.BASECURRENCYID AND @CURRENCYCODE = 0) OR (CURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() AND @CURRENCYCODE <> 0)
      outer apply
        dbo.UFN_CURRENCY_GETPROPERTIES(CURRENCY.ID) CURRENCYPROPERTIES
            where
                EVENT.STARTDATE between @FROM and @TO
                and (@INCLUDEINACTIVE = 1 or EVENT.ISACTIVE = 1)
                and (@CURRENTAPPUSERID is null or dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)
                and (EVENT.PROGRAMID is null)
            order by
                EVENT.NAME
        else
            select
                EVENT.NAME as EVENTNAME,
                APPEAL.NAME as APPEAL,
                EVENT.STARTDATE,
                EVENT.CAPACITY,
                (select count(ID) from dbo.INVITEE where EVENTID = EVENT.ID) as INVITED,
                (select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and dbo.[UFN_REGISTRANT_ISCANCELLED](ID) = 0) as REGISTERED,
                (select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and ATTENDED = 1 and dbo.[UFN_REGISTRANT_ISCANCELLED](ID) = 0) as ATTENDED,
                ( --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(CURRENCY.ID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),CURRENCY.DECIMALDIGITS,CURRENCY.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]
                ) + ( --Appeals

                    case when @IS_ENTERPRISE = 1 AND @CURRENCYCODE = 0 then
                            (
                              select coalesce(sum(dbo.UFN_APPEAL_GETTOTALREVENUEINCURRENCY(EVENTAPPEAL.APPEALID, ZEP.BASECURRENCYID)), 0)
                              from dbo.EVENT ZEP
                              inner join dbo.EVENTAPPEAL on EVENTAPPEAL.EVENTID = EVENT.ID
                              where ZEP.ID = EVENT.ID
                            )
                        when @IS_ENTERPRISE = 1 AND @CURRENCYCODE <> 0 then
                            (
                              select coalesce(sum(dbo.UFN_APPEAL_GETTOTALREVENUEINCURRENCY(EVENTAPPEAL.APPEALID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())), 0)
                              from dbo.EVENT ZEP
                              inner join dbo.EVENTAPPEAL on EVENTAPPEAL.EVENTID = EVENT.ID
                              where ZEP.ID = EVENT.ID
                            )
                        else
                            0
                    end
                ) - ( --Appeals that are also event registration - donations are double counted

                    select coalesce(sum(RS.AMOUNTINCURRENCY), 0)
                    from dbo.EVENTAPPEAL
                    inner join dbo.REVENUE with (nolock) on REVENUE.APPEALID = EVENTAPPEAL.APPEALID
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(CURRENCY.ID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),CURRENCY.DECIMALDIGITS,CURRENCY.ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID
                    where EVENTAPPEAL.EVENTID = EVENT.ID and (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)
                ) as [TOTALINCOME],
                (
          select coalesce(sum(EE.ACTUALAMOUNTINCURRENCY), 0)
          from dbo.EVENTEXPENSE
          left join dbo.UFN_EVENTEXPENSE_GETINCURRENCY_BULK(CURRENCY.ID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),CURRENCY.DECIMALDIGITS,CURRENCY.ROUNDINGTYPECODE) as EE on EE.ID = EVENTEXPENSE.ID
          where EVENTEXPENSE.EVENTID = EVENT.ID
          ) as ACTUALAMOUNT,
                CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                CURRENCYPROPERTIES.CURRENCYSYMBOL,
              CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                CURRENCY.DECIMALDIGITS,
          @IS_SAME_CURRENCY as SAMECURRENCY,
                    EVENT.ID as EVENTID,
                    @USERGRANTEDEVENTPAGE as USERGRANTEDEVENTPAGE
            from
                dbo.EVENT
            inner join
                dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) SELECTION on EVENT.ID = SELECTION.ID and @IDSETREGISTERID is not null
            left join
                dbo.APPEAL on EVENT.APPEALID = APPEAL.ID
      left join
        dbo.CURRENCY on (CURRENCY.ID = EVENT.BASECURRENCYID AND @CURRENCYCODE = 0) OR (CURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() AND @CURRENCYCODE <> 0)
      outer apply
        dbo.UFN_CURRENCY_GETPROPERTIES(CURRENCY.ID) CURRENCYPROPERTIES
            where
                EVENT.STARTDATE between @FROM and @TO
                and (@INCLUDEINACTIVE = 1 or EVENT.ISACTIVE = 1)
                and (@CURRENTAPPUSERID is null or dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)
                and (EVENT.PROGRAMID is null)
            order by
                EVENT.NAME
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;