USP_REPORT_VOLUNTEERPERFORMANCESUMMARY

            CREATE procedure dbo.USP_REPORT_VOLUNTEERPERFORMANCESUMMARY
            (    
                @VOLUNTEERQUERY uniqueidentifier = null,
                @VOLUNTEERID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @SITEID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @ALTREPORTUSERID nvarchar(128) = null,
                @CURRENCYCODE tinyint = null
            )
            with execute as caller
            as

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

                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;

                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                declare @CURRENCYID uniqueidentifier;
                if @CURRENCYCODE = 0 begin
                    select @CURRENCYID=ID from CURRENCY where ID=(select dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID));
                end
                else if @CURRENCYCODE is null or @CURRENCYCODE = 1 begin
                    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                end

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

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

                declare @LIFETIMETABLE table
                (
                    VOLUNTEERID uniqueidentifier,
                    HOURS decimal(20,2),
                    VALUE money
                )

                declare @PERIODTABLE table
                (
                    VOLUNTEERID uniqueidentifier,
                    HOURS decimal(20,2),
                    VALUE money
                )

                set @SQLTOEXEC = '
                    select 
                        TIMESHEET.VOLUNTEERID as VOLUNTEERID,
                        TIMESHEET.HOURSWORKED as HOURSWORKED,
                        case 
                            when JOBOCCURRENCE.BASECURRENCYID = @CURRENCYID
                                then TIMESHEET.HOURSWORKED * JOBOCCURRENCE.ESTIMATEDVALUE
                            else
                                dbo.UFN_CURRENCY_CONVERT(TIMESHEET.HOURSWORKED * JOBOCCURRENCE.ESTIMATEDVALUE, 
                                    dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(JOBOCCURRENCE.BASECURRENCYID, @CURRENCYID, getdate(), 1, null))
                            end    as VALUE
                    from dbo.TIMESHEET '

                if @VOLUNTEERQUERY is not null
                    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on TIMESHEET.VOLUNTEERID = SELECTION.ID ';

                set @SQLTOEXEC = @SQLTOEXEC + '
                    inner join dbo.JOBOCCURRENCE on TIMESHEET.JOBOCCURRENCEID = JOBOCCURRENCE.ID
                    inner join JOB on JOBOCCURRENCE.JOBID = JOB.ID
                    where (@VOLUNTEERID is null or TIMESHEET.VOLUNTEERID = @VOLUNTEERID)
                    and (@SITEID is null or @SITEID = dbo.UFN_SITEID_MAPFROM_JOBOCCURRENCEID(TIMESHEET.JOBOCCURRENCEID))
                    and (@SITEID is null or @SITEID = dbo.UFN_SITEID_MAPFROM_JOBID(JOBOCCURRENCE.JOBID))
                    and (@ISADMIN = 1 or 
                         @APPUSER_IN_NONRACROLE = 1 or
                         dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, TIMESHEET.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)'

                insert into @LIFETIMETABLE (VOLUNTEERID, HOURS, VALUE)
                    exec sp_executesql @SQLTOEXEC,
                        N'@VOLUNTEERID uniqueidentifier, @SITEID uniqueidentifier, @CURRENCYID uniqueidentifier, 
                            @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
                        @VOLUNTEERID=@VOLUNTEERID, @SITEID=@SITEID, @CURRENCYID=@CURRENCYID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, 
                            @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;

                set @SQLTOEXEC = '
                    select 
                        TIMESHEET.VOLUNTEERID as VOLUNTEERID,
                        TIMESHEET.HOURSWORKED as HOURSWORKED,
                        case 
                            when JOBOCCURRENCE.BASECURRENCYID = @CURRENCYID
                                then TIMESHEET.HOURSWORKED * JOBOCCURRENCE.ESTIMATEDVALUE
                            else
                                dbo.UFN_CURRENCY_CONVERT(TIMESHEET.HOURSWORKED * JOBOCCURRENCE.ESTIMATEDVALUE, 
                                    dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(JOBOCCURRENCE.BASECURRENCYID, @CURRENCYID, getdate(), 1, null))
                            end    as VALUE
                    from dbo.TIMESHEET '

                if @VOLUNTEERQUERY is not null
                    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on TIMESHEET.VOLUNTEERID = SELECTION.ID ';

                set @SQLTOEXEC = @SQLTOEXEC + '
                    inner join dbo.JOBOCCURRENCE on TIMESHEET.JOBOCCURRENCEID = JOBOCCURRENCE.ID
                    inner join JOB on JOBOCCURRENCE.JOBID = JOB.ID
                    where (@VOLUNTEERID is null or TIMESHEET.VOLUNTEERID = @VOLUNTEERID)
                    and (@SITEID is null or @SITEID = dbo.UFN_SITEID_MAPFROM_JOBOCCURRENCEID(TIMESHEET.JOBOCCURRENCEID))
                    and (@SITEID is null or @SITEID = dbo.UFN_SITEID_MAPFROM_JOBID(JOBOCCURRENCE.JOBID))
                    and (@STARTDATE is null or TIMESHEET.STARTDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                    and (@ENDDATE is null or TIMESHEET.STARTDATE <= dbo.UFN_DATE_GETEARLIESTTIME(@ENDDATE))
                    and (@ISADMIN = 1 or 
                         @APPUSER_IN_NONRACROLE = 1 or
                         dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, TIMESHEET.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)'

                insert into @PERIODTABLE (VOLUNTEERID, HOURS, VALUE)
                    exec sp_executesql @SQLTOEXEC,
                        N'@VOLUNTEERID uniqueidentifier, @SITEID uniqueidentifier, @CURRENCYID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime,
                            @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
                        @VOLUNTEERID=@VOLUNTEERID, @SITEID=@SITEID, @CURRENCYID=@CURRENCYID, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @ISADMIN=@ISADMIN, 
                            @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, 
                            @CURRENTAPPUSERID=@CURRENTAPPUSERID;

                declare @AGGREGATEPERIOD table
                (
                    VOLUNTEERID uniqueidentifier,
                    PERIODHOURS decimal(20,2),
                    PERIODVALUE money
                )

                insert into @AGGREGATEPERIOD (VOLUNTEERID, PERIODHOURS, PERIODVALUE)
                select 
                    PERIOD.VOLUNTEERID,
                    sum(PERIOD.HOURS) as PERIODHOURS,
                    sum(PERIOD.VALUE) as PERIODVALUE
                from @PERIODTABLE PERIOD
                group by PERIOD.VOLUNTEERID;

                declare @AGGREGATELIFETIME table
                (
                    VOLUNTEERID uniqueidentifier,
                    LIFETIMEHOURS decimal(20,2),
                    LIFETIMEVALUE money
                )

                insert into @AGGREGATELIFETIME (VOLUNTEERID, LIFETIMEHOURS, LIFETIMEVALUE)
                select 
                    LIFETIME.VOLUNTEERID,
                    sum(LIFETIME.HOURS) as LIFETIMEHOURS,
                    sum(LIFETIME.VALUE) as LIFETIMEVALUE
                from @LIFETIMETABLE LIFETIME
                group by LIFETIME.VOLUNTEERID;

                select
                    PERIOD.VOLUNTEERID,
                    'http://www.blackbaud.com/VOLUNTEERID?VOLUNTEERID=' + CONVERT(nvarchar(36),PERIOD.VOLUNTEERID) as VOLUNTEERLINK,
                    NF.NAME as VOLUNTEERNAME,
                    coalesce(PERIOD.PERIODHOURS, 0) as PERIODHOURS,
                    coalesce(PERIOD.PERIODVALUE, 0) as PERIODVALUE,
                    coalesce(LIFETIME.LIFETIMEHOURS, 0) as LIFETIMEHOURS,
                    coalesce(LIFETIME.LIFETIMEVALUE, 0) as LIFETIMEVALUE,
                    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCYPROPERTIES.DECIMALDIGITS
                from @AGGREGATEPERIOD PERIOD
                inner join @AGGREGATELIFETIME LIFETIME on PERIOD.VOLUNTEERID = LIFETIME.VOLUNTEERID
                outer apply
                    dbo.UFN_CURRENCY_GETPROPERTIES(@CURRENCYID) CURRENCYPROPERTIES
                outer apply
                    dbo.UFN_CONSTITUENT_DISPLAYNAME(PERIOD.VOLUNTEERID) NF
                order by VOLUNTEERNAME;