USP_REPORT_TEAMFUNDRAISINGEVENTPERFORMANCE

            CREATE procedure dbo.USP_REPORT_TEAMFUNDRAISINGEVENTPERFORMANCE
            (
                @EVENTQUERYID uniqueidentifier = null,
                @EVENTID uniqueidentifier = null,
                @QUICKCOMPEVENTS nvarchar(max) = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @REPORTUSERID nvarchar(128),
        @CURRENCYCODE tinyint = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            as

                set nocount on;                

                begin try
                    create table #FUNDRAISERINFO
                    (
                        EVENTID uniqueidentifier,
                        TEAMID uniqueidentifier,
                        PARENTTEAMID uniqueidentifier,
                        FUNDRAISERID uniqueidentifier,
                        REVENUEID uniqueidentifier,
                        AMOUNTSOLICITED money
                    )

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

              declare @SELECTEDCURRENCYID uniqueidentifier;

              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();

                    -- Retrieve the table for the selection, if there is one.

                    declare @DBOBJECTNAME nvarchar(128);
                    declare @DBOBJECTTYPE smallint;

                    if @EVENTQUERYID is not null begin
                        if not exists(select ID from dbo.IDSETREGISTER where ID = @EVENTQUERYID) raiserror('ID set does not exist in the database.', 15, 1);
                        select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @EVENTQUERYID;
                        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @EVENTQUERYID) + ''')';
                    end

                    declare @SQLTOEXEC nvarchar(max);
                    set @SQLTOEXEC = 
                        '-- Build a table to contain those events that are to

                        -- be included if the user selected a specific

                        -- event and some of its quick comparisons

                        declare @INCLUDEDEVENTS table(
                            ID uniqueidentifier
                        );

                        insert into @INCLUDEDEVENTS (ID)
                        values (@EVENTID);

                        declare @TEMPID uniqueidentifier

                        while len(@QUICKCOMPEVENTS) > 0
                        begin
                            set @TEMPID = convert( uniqueidentifier, substring(@QUICKCOMPEVENTS, 1, 36 ));

                            if (@TEMPID <> ''00000000-0000-0000-0000-000000000000'' and @TEMPID <> ''00000000-0000-0000-0000-000000000001'') and not exists(select ID from @INCLUDEDEVENTS where ID=@TEMPID)
                            begin
                                insert into @INCLUDEDEVENTS (ID)
                                values (@TEMPID);
                            end

                            set @QUICKCOMPEVENTS = substring(@QUICKCOMPEVENTS, 37, len(@QUICKCOMPEVENTS) - 36 );
                        end

                        -- Retrieve all the data that will be need to do

                        -- the report calculations

                        insert into #FUNDRAISERINFO
                        (
                            EVENTID,
                            TEAMID,
                            PARENTTEAMID,
                            FUNDRAISERID,
                            REVENUEID,
                            AMOUNTSOLICITED
                        )
                        select
                            EVENT.ID,
                            TEAMFUNDRAISINGTEAM.ID,
                            TEAMFUNDRAISINGTEAM.PARENTTEAMID,
                            TEAMFUNDRAISER.ID,
                            REVENUE.ID,
                            case when REVENUE.ID is null then 0 else sum(coalesce(dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY(REVENUESOLICITOR.ID, @SELECTEDCURRENCYID),0)) end
                        from dbo.EVENT 
                            inner join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.APPEALID=EVENT.APPEALID
                            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 
                            ' + nchar(13);

                    if not @EVENTID is null
                    begin                        
                        set @SQLTOEXEC = @SQLTOEXEC +
                            'inner join @INCLUDEDEVENTS INCLUDEDEVENTS on INCLUDEDEVENTS.ID=EVENT.ID' + nchar(13);                        
                    end
                    else
                    begin
                        if @EVENTQUERYID is not null
                        begin
                            set @SQLTOEXEC = @SQLTOEXEC + 
                                'inner join dbo.' + @DBOBJECTNAME + ' as EVENTQUERY on EVENT.[ID] = EVENTQUERY.[ID]' + nchar(13);
                        end
                    end

                    set @SQLTOEXEC = @SQLTOEXEC + 
                        'where 
                            (EVENT.STARTDATE >= @STARTDATE or @STARTDATE is null)   --On or after start date

                                and (EVENT.STARTDATE <= @ENDDATE or @ENDDATE is null)   --On or before end date

                                and (
                                        (
                                            (REVENUE.TRANSACTIONTYPECODE = 1 
                                                or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,3))
                                                --adding event registration donation

                                                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

                                )
                                and (@CURRENTAPPUSERID is null or dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)    --Check site security on event

                        group by EVENT.ID, TEAMFUNDRAISINGTEAM.ID, TEAMFUNDRAISER.ID, REVENUE.ID,
                            TEAMFUNDRAISINGTEAM.PARENTTEAMID' + nchar(13);

                    exec sp_executesql @SQLTOEXEC, 
                        N'@EVENTQUERYID uniqueidentifier, @EVENTID uniqueidentifier, @QUICKCOMPEVENTS nvarchar(max), @STARTDATE datetime, @ENDDATE datetime, @CURRENTAPPUSERID uniqueidentifier, @SELECTEDCURRENCYID uniqueidentifier',
                        @EVENTQUERYID=@EVENTQUERYID, @EVENTID=@EVENTID, @QUICKCOMPEVENTS=@QUICKCOMPEVENTS, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE,@CURRENTAPPUSERID=@CURRENTAPPUSERID, @SELECTEDCURRENCYID=@SELECTEDCURRENCYID;


                    --Filter collected data for calculating event totals

                    declare @EVENTINFO table(
                        EVENTID uniqueidentifier,
                        REVENUEID uniqueidentifier,
                        AMOUNTSOLICITED money
                    )
                    insert into @EVENTINFO
                    (
                        EVENTID,
                        REVENUEID,
                        AMOUNTSOLICITED
                    )
                    select distinct EVENTID, REVENUEID, AMOUNTSOLICITED
                    from #FUNDRAISERINFO

                    --Filter collected data for calculating team totals

                    declare @TEAMINFO table(
                        EVENTID uniqueidentifier,
                        TEAMID uniqueidentifier,
                        PARENTTEAMID uniqueidentifier,
                        TOTAL money,
                        NUMGIFTS int,
                        MAXGIFT money
                    )
                    insert into @TEAMINFO
                    (
                        EVENTID,
                        TEAMID,
                        PARENTTEAMID,
                        TOTAL,
                        NUMGIFTS,
                        MAXGIFT
                    )
                    select
                        EVENTID, 
                        TEAMID, 
                        PARENTTEAMID,
                        sum(AMOUNTSOLICITED) TOTAL,
                        count(REVENUEID) NUMGIFTS,
                        max(AMOUNTSOLICITED) MAXGIFT
                    from (
                        select distinct 
                            EVENTID, 
                            TEAMID, 
                            PARENTTEAMID,
                            REVENUEID, 
                            AMOUNTSOLICITED
                        from #FUNDRAISERINFO
                    ) TEAMINFO
                    group by EVENTID, TEAMID, PARENTTEAMID;

                    -- Calculate totals and build dataset

                    with CTE_TEAMCHILDREN( TEAMID, ANCESTORTEAMID, TEAMREVENUE ) as
                    (
                        select TEAMID, TEAMID, TOTAL
                        from @TEAMINFO
                        where TEAMID in (
                            select PARENTTEAMID
                            from @TEAMINFO
                        )
                        union all
                        select TEAMINFO.TEAMID, CTE_TEAMCHILDREN.ANCESTORTEAMID, TEAMINFO.TOTAL
                        from @TEAMINFO TEAMINFO
                            inner join CTE_TEAMCHILDREN on TEAMINFO.PARENTTEAMID=CTE_TEAMCHILDREN.TEAMID
                    )
                    select distinct 
                        'http://www.blackbaud.com/EVENTID?EVENTID=' + convert(nvarchar(36),EVENT.ID) as EVENTID,
                        EVENT.NAME EVENTNAME, 
                        EVENTINFO.TOTAL EVENTTOTAL, 
                        dbo.UFN_APPEAL_GETGOALINCURRENCY(APPEAL.ID, @SELECTEDCURRENCYID) EVENTGOAL, 
                        EVENTINFO.NUMGIFTS EVENTNUMGIFTS, 
                        EVENTINFO.MAXGIFT EVENTMAXGIFT,

                        dbo.UFN_TEAMFUNDRAISINGTEAM_BUILDFULLNAME(TEAMINFO.TEAMID) TEAMNAME, 
                        coalesce(TEAMROLLUPTOTALS.ROLLUPTOTAL,TEAMINFO.TOTAL) TEAMROLLUPTOTAL,
                        TEAMINFO.TOTAL TEAMTOTAL, 
                        case
              when @SELECTEDCURRENCYID = TEAMFUNDRAISINGTEAM.BASECURRENCYID then
                  TEAMFUNDRAISINGTEAM.GOAL
              when (@CURRENCYCODE = 3)  and (@SELECTEDCURRENCYID <> dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) then
                  dbo.UFN_CURRENCY_CONVERT(TEAMFUNDRAISINGTEAM.ORGANIZATIONGOAL, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @SELECTEDCURRENCYID, (select top 1 [INNERAPPEAL].DATEADDED from dbo.APPEAL [INNERAPPEAL] where [INNERAPPEAL].ID = TEAMFUNDRAISINGTEAM.APPEALID), 1, null))
              else
                TEAMFUNDRAISINGTEAM.ORGANIZATIONGOAL
            end TEAMGOAL,
            TEAMINFO.NUMGIFTS TEAMNUMGIFTS, 
                        TEAMINFO.MAXGIFT TEAMMAXGIFT,

                        CONSTITUENT.NAME FUNDRAISERNAME, 
                        FUNDRAISERINFO.TOTAL FUNDRAISERTOTAL,
            case
              when @SELECTEDCURRENCYID = TEAMFUNDRAISER.BASECURRENCYID then
                  TEAMFUNDRAISER.GOAL
              when (@CURRENCYCODE = 3)  and (@SELECTEDCURRENCYID <> dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) then
                  dbo.UFN_CURRENCY_CONVERT(TEAMFUNDRAISER.ORGANIZATIONGOAL, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @SELECTEDCURRENCYID, (select top 1 [INNERAPPEAL].DATEADDED from dbo.APPEAL [INNERAPPEAL] where [INNERAPPEAL].ID = TEAMFUNDRAISER.APPEALID), 1, null))
              else
                TEAMFUNDRAISER.ORGANIZATIONGOAL
            end FUNDRAISERGOAL,
                        FUNDRAISERINFO.NUMGIFTS FUNDRAISERNUMGIFTS, 
                        FUNDRAISERINFO.MAXGIFT FUNDRAISERMAXGIFT,

                      CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                      CURRENCYPROPERTIES.CURRENCYSYMBOL,
                      CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                      CURRENCYPROPERTIES.DECIMALDIGITS
                    from (
                                select
                                    EVENTID, 
                                    TEAMID, 
                                    FUNDRAISERID ID, 
                                    sum(AMOUNTSOLICITED) TOTAL,
                                    count(REVENUEID) NUMGIFTS,
                                    max(AMOUNTSOLICITED) MAXGIFT
                                from #FUNDRAISERINFO
                                group by EVENTID, TEAMID, FUNDRAISERID
                            ) FUNDRAISERINFO
                        inner join dbo.EVENT on EVENT.ID = FUNDRAISERINFO.EVENTID
                        inner join(
                                select
                                    EVENTID ID, 
                                    sum(AMOUNTSOLICITED) TOTAL,
                                    count(REVENUEID) NUMGIFTS,
                                    max(AMOUNTSOLICITED) MAXGIFT
                                from @EVENTINFO
                                group by EVENTID
                            ) EVENTINFO on EVENTINFO.ID = EVENT.ID
                        inner join dbo.APPEAL on APPEAL.ID = EVENT.APPEALID
                        left join dbo.TEAMFUNDRAISER on TEAMFUNDRAISER.ID = FUNDRAISERINFO.ID
                        left join dbo.CONSTITUENT with (nolock) on CONSTITUENT.ID = TEAMFUNDRAISER.CONSTITUENTID
                        left join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.ID = FUNDRAISERINFO.TEAMID
                        left join @TEAMINFO TEAMINFO on TEAMINFO.TEAMID = TEAMFUNDRAISINGTEAM.ID and TEAMINFO.EVENTID = EVENT.ID
                        left join(
                            select
                                ANCESTORTEAMID TEAMID,
                                sum(TEAMREVENUE) ROLLUPTOTAL
                            from CTE_TEAMCHILDREN
                            group by ANCESTORTEAMID
                        ) TEAMROLLUPTOTALS on TEAMROLLUPTOTALS.TEAMID=TEAMINFO.TEAMID
        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES

                end try

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

                return 0;