USP_DATAFORMTEMPLATE_VIEW_VOLUNTEERPERFORMANCESUMMARY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_VOLUNTEERPERFORMANCESUMMARY
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @HOURSLIFETIME decimal(20, 2) = null output,
  @ESTIMATEDVALUELIFETIME money = null output,
  @HOURSTHISYEAR decimal(20, 2) = null output,
  @ESTIMATEDVALUETHISYEAR money = null output,
  @LASTJOB nvarchar(500) = null output,
  @NEXTJOB nvarchar(500) = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @VOLUNTEERSTARTDATE datetime = null output
)
as
  set nocount on;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  set @DATALOADED = 0;

  set @BASECURRENCYID = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);
  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  /*
  totals will be expressed in the user's base currency

  a volunteer may be assigned to jobs of varying base currencies, so...

  calculating totals per http://meebee/BBSites/Products/InfinityDevelopment/Wiki/Wiki%20Pages/How%20we%20use%20exchange%20rates%20for%20currency%20conversions.aspx
  1. If the user's base currency is the organization currency, use the organization amount.
  2. If the user's base currency is the base currency of the job occurrence, use the base currency.
  3. Otherwise, look for a corporate exchange rate from the organization currency to the user's base currency and apply this rate to the organization amount.
  4. If this rate does not exist, look for a corporate exchange rate from the user's base currency to the organization currency and apply the _inverse_ of this rate to the organization amount.
  */

  -- if constituent exists, set dataloaded = 1 because the volunteer may not have volunteered any hours
  if exists(select ID from dbo.CONSTITUENT where ID = @ID)  
    set @DATALOADED = 1;

  -- calculate lifetime values
  select 
    @HOURSLIFETIME = SUM(TIMESHEET.HOURSWORKED),
    @ESTIMATEDVALUELIFETIME = SUM(TIMESHEET.HOURSWORKED * 
           -- 1. If the user's base currency is the organization currency, use the organization amount.
      case when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
           then JOBOCCURRENCE.ORGANIZATIONESTIMATEDVALUE
           -- 2. If the user's base currency is the base currency of the job occurrence, use the base currency.
           when @BASECURRENCYID = JOBOCCURRENCE.BASECURRENCYID
           then JOBOCCURRENCE.ESTIMATEDVALUE
           -- 3. Otherwise, look for a corporate exchange rate from the organization currency to the user's base currency and apply this rate to the organization amount.
           when dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED](@ORGANIZATIONCURRENCYID, @BASECURRENCYID, JOBOCCURRENCE.DATEADDED, 0, null) is not null
           then dbo.[UFN_CURRENCY_CONVERT](JOBOCCURRENCE.ORGANIZATIONESTIMATEDVALUE, dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED](@ORGANIZATIONCURRENCYID, @BASECURRENCYID, JOBOCCURRENCE.DATEADDED, 0, null))
           -- 4. If this rate does not exist, look for a corporate exchange rate from the user's base currency to the organization currency and apply the _inverse_ of this rate to the organization amount.
           else dbo.[UFN_CURRENCY_CONVERTINVERSE](JOBOCCURRENCE.ORGANIZATIONESTIMATEDVALUE, dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, JOBOCCURRENCE.DATEADDED, 0, null))
      end)
  from dbo.TIMESHEET
  inner join dbo.JOBOCCURRENCE
    on TIMESHEET.JOBOCCURRENCEID = JOBOCCURRENCE.ID
  inner join dbo.JOB
    on JOBOCCURRENCE.JOBID = JOB.ID
  where TIMESHEET.VOLUNTEERID = @ID;

  -- calculate this year values --> Current calendar year
  declare @STARTDATE datetime = '01/01/' + cast(YEAR(getDate()) as nvarchar);
  declare @ENDDATE datetime = '12/31/' + cast(YEAR(getDate()) as nvarchar);

  select 
    @HOURSTHISYEAR = SUM(TIMESHEET.HOURSWORKED),
    @ESTIMATEDVALUETHISYEAR = SUM(TIMESHEET.HOURSWORKED * 
           -- 1. If the user's base currency is the organization currency, use the organization amount.
      case when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
           then JOBOCCURRENCE.ORGANIZATIONESTIMATEDVALUE
           -- 2. If the user's base currency is the base currency of the job occurrence, use the base currency.
           when @BASECURRENCYID = JOBOCCURRENCE.BASECURRENCYID
           then JOBOCCURRENCE.ESTIMATEDVALUE
           -- 3. Otherwise, look for a corporate exchange rate from the organization currency to the user's base currency and apply this rate to the organization amount.
           when dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED](@ORGANIZATIONCURRENCYID, @BASECURRENCYID, JOBOCCURRENCE.DATEADDED, 0, null) is not null
           then dbo.[UFN_CURRENCY_CONVERT](JOBOCCURRENCE.ORGANIZATIONESTIMATEDVALUE, dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED](@ORGANIZATIONCURRENCYID, @BASECURRENCYID, JOBOCCURRENCE.DATEADDED, 0, null))
           -- 4. If this rate does not exist, look for a corporate exchange rate from the user's base currency to the organization currency and apply the _inverse_ of this rate to the organization amount.
           else dbo.[UFN_CURRENCY_CONVERTINVERSE](JOBOCCURRENCE.ORGANIZATIONESTIMATEDVALUE, dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, JOBOCCURRENCE.DATEADDED, 0, null))
      end)
  from dbo.TIMESHEET
  inner join dbo.JOBOCCURRENCE
    on TIMESHEET.JOBOCCURRENCEID = JOBOCCURRENCE.ID
  inner join dbo.JOB
    on JOBOCCURRENCE.JOBID = JOB.ID
  where TIMESHEET.VOLUNTEERID = @ID
    and TIMESHEET.STARTDATE >= @STARTDATE
    and TIMESHEET.STARTDATE <= @ENDDATE;

  -- Get last assigned job
  select top 1
    @LASTJOB = JOB.NAME + ' - ' + JOBOCCURRENCE.DESCRIPTION + ' (' + convert(nvarchar(10), VOLUNTEERASSIGNMENT.DATE, 101) + ')'
  from dbo.VOLUNTEERASSIGNMENT
  inner join dbo.JOBOCCURRENCE
    on VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = JOBOCCURRENCE.ID
  inner join dbo.JOB
    on JOBOCCURRENCE.JOBID = JOB.ID
  where VOLUNTEERASSIGNMENT.VOLUNTEERID = @ID
    and VOLUNTEERASSIGNMENT.DATE <= getDate()
  order by VOLUNTEERASSIGNMENT.DATE desc;

  -- Get next assigned job
  select top 1
    @NEXTJOB = JOB.NAME + ' - ' + JOBOCCURRENCE.DESCRIPTION + ' (' + convert(nvarchar(10), VOLUNTEERASSIGNMENT.DATE, 101) + ')'
  from dbo.VOLUNTEERASSIGNMENT
  inner join dbo.JOBOCCURRENCE
    on VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = JOBOCCURRENCE.ID
  inner join dbo.JOB
    on JOBOCCURRENCE.JOBID = JOB.ID
  where VOLUNTEERASSIGNMENT.VOLUNTEERID = @ID
    and VOLUNTEERASSIGNMENT.DATE >= getDate()
  order by VOLUNTEERASSIGNMENT.DATE asc;

  select
    @VOLUNTEERSTARTDATE = min(DATEFROM)
  from
    dbo.VOLUNTEERDATERANGE
  where
    CONSTITUENTID = @ID
  group by
    CONSTITUENTID;

  return 0;