USP_MKTSMARTFIELD_AVERAGEGIFTVALUE

CREATE procedure dbo.[USP_MKTSMARTFIELD_AVERAGEGIFTVALUE]
(
  @RECORDSOURCEID uniqueidentifier,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @DEFAULTVALUE money = 0,
  @DATEOPTION tinyint = 0,
  @PASTMONTHS tinyint = null,
  @AMOUNTOPTION tinyint = 0,
  @AMOUNTOPTIONSTART money = null,
  @AMOUNTOPTIONEND money = null,
  @IDSETREGISTERID uniqueidentifier = null,
  @ASOF datetime,
  @CURRENCYID uniqueidentifier = null,
  @SITES xml = null
)
as
  set nocount on;

  begin try

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;

    set @CURRENCYID = isnull(@CURRENCYID, @ORGANIZATIONCURRENCYID);

    select
      @DECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
      @ROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
    from dbo.[CURRENCY]
    where [CURRENCY].[ID] = @CURRENCYID;

    declare @USESITEPARAMETER bit = 1;

    --If no sites selected, then do not use the site parameter.

    if @SITES is null or not exists (select * from dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES))
      set @USESITEPARAMETER = 0;

    declare @IDSET sysname;
    declare @IDSETOBJECTTYPE tinyint;

    if @IDSETREGISTERID is not null
      select 
        @IDSET = [DBOBJECTNAME], 
        @IDSETOBJECTTYPE = [OBJECTTYPE] 
      from dbo.[IDSETREGISTER] where [ID] = @IDSETREGISTERID;

    -- get the donor view info based on the record source

    declare @DONORSYSNAME sysname;
    declare @DONORIDFIELD sysname;

    select 
      @DONORSYSNAME = [OBJECTNAME],
      @DONORIDFIELD = [PRIMARYKEYFIELD]
    from dbo.[MKTRECORDSOURCE] 
    left join dbo.[QUERYVIEWCATALOG] on [MKTRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    inner join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCE].[ID] = [MKTRECORDSOURCEFIELDMAPPINGS].[ID]
    where
      [MKTRECORDSOURCE].[ID] = @RECORDSOURCEID and
      dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;

    -- get the gift view info based on the record source

    declare @GIFTSYSNAME sysname;
    declare @GIFTDONORIDFIELD sysname;
    declare @GIFTDATEFIELD sysname;
    declare @GIFTAMOUNT sysname;
    declare @GIFTDATECHANGEDFIELD sysname;
    declare @GIFTIDFIELD sysname;
    declare @GIFTDATEADDEDFIELD sysname;
    declare @DATECHANGEDFIELD sysname;
    declare @ISBBEC bit = (case when dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1 then 1 else 0 end);

    select 
      @GIFTSYSNAME = [OBJECTNAME],
      @GIFTDONORIDFIELD = [DONORIDFIELD],
      @GIFTDATEFIELD = [DATEFIELD],
      @GIFTDATECHANGEDFIELD = isnull([DATECHANGEDFIELD], ''),
      @GIFTDATEADDEDFIELD = isnull([DATEADDEDFIELD], ''),
      @GIFTAMOUNT = [AMOUNTFIELD],
      @GIFTIDFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD]
    from dbo.[MKTGIFTRECORDSOURCE] 
    left join dbo.[QUERYVIEWCATALOG] on [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
    where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;

    --if GIFTDATECHANGEDFIELD and GIFTDATEADDEDFIELD is empty the smart field cannot work

    if @GIFTDATECHANGEDFIELD = '' and @GIFTDATEADDEDFIELD = ''
      raiserror(N'The gift''s ''Date changed'' field must be mapped for this smart field to process.', 15, 4);

    --set the date changed field to what is set

    if @GIFTDATECHANGEDFIELD != ''
      set @DATECHANGEDFIELD = @GIFTDATECHANGEDFIELD;
    else
      set @DATECHANGEDFIELD = @GIFTDATEADDEDFIELD;

    if @ASOF is null
      set @ASOF = '1753-01-01';

    --setup start and end date

    if @DATEOPTION = 0 -- for All Time

    begin
      set @ENDDATE = current_timestamp;
      set @STARTDATE = '1753-01-01';
    end
    if @DATEOPTION = 1 -- for Calendar year

    begin
      set @ENDDATE = current_timestamp;
      set @STARTDATE = cast(YEAR(@ENDDATE) as nvarchar) + '-01-01';
    end
    if @DATEOPTION = 2 --for last N months

    begin
      set @ENDDATE = current_timestamp;
      set @STARTDATE = dateadd(m, -@PASTMONTHS  ,@ENDDATE);
    end

    --build the SQL to execute

    declare @SQL nvarchar(max);
    set @SQL = 'with [VALUES] as ' + char(13) +
                '( ' + char(13) +
                '  select ' + char(13) +
                '    [C].[' + @DONORIDFIELD + '], ' + char(13) +
                '    avg((case when [G].[' + @GIFTDATEFIELD + '] between @STARTDATE and @ENDDATE then ' + 
                  case 
                    when @ISBBEC = 1 then '[REVENUE].[AMOUNTINCURRENCY]' 
                    else 'coalesce([G].[' + @GIFTAMOUNT + '], 0)' end + ' else @DEFAULTVALUE end)) as [MAXAMOUNT] ' + char(13) +
                '  from dbo.[' + @DONORSYSNAME + '] as [C] ' + char(13) +
                '  left outer join [' + @GIFTSYSNAME + '] as [G] on [C].[' + @DONORIDFIELD + '] = [G].[' + @GIFTDONORIDFIELD + '] ' + char(13);

    if @ISBBEC = 1
      set @SQL = @SQL + '  left join dbo.[UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [REVENUE] on [G].[ID] = [REVENUE].[ID]' + char(13);

    if @IDSET is not null
    begin
      if @IDSETOBJECTTYPE = 1
        set @SQL = @SQL + ' inner join dbo.[' + @IDSET + ']() on [G].[' + @GIFTIDFIELD + '] = [' + @IDSET + '].[ID] ' + char(13);
      else
        set @SQL = @SQL + ' inner join dbo.[' + @IDSET + '] on [G].[' + @GIFTIDFIELD + '] = [' + @IDSET + '].[ID] ' + char(13);
    end

    if @USESITEPARAMETER = 1
      set @SQL = @SQL + '  left join dbo.[CONSTITUENTSITE] on [C].[' + @DONORIDFIELD + '] = [CONSTITUENTSITE].[CONSTITUENTID] ' + char(13);

    set @SQL = @SQL + '  where coalesce([G].[' + @DATECHANGEDFIELD + '], ''1754-01-01'') > @ASOF ' + char(13);

    if @USESITEPARAMETER = 1
      set @SQL = @SQL + '    and exists (select [SITEID] from dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) where [SITEID] = [CONSTITUENTSITE].[SITEID]) ' + char(13);

    set @SQL = @SQL + '  group by [C].[' + @DONORIDFIELD + '] ' + char(13) +
                      ') ' + char(13) +
                      'select ' + char(13) +
                      '  [VALUES].[' + @DONORIDFIELD + '], ' + char(13);

    if @AMOUNTOPTION = 1
      set @SQL = @SQL + '  case when [VALUES].[MAXAMOUNT] between @AMOUNTOPTIONSTART and @AMOUNTOPTIONEND then [VALUES].[MAXAMOUNT] else @DEFAULTVALUE end as [MAXAMOUNT]' + char(13);
    else
      set @SQL = @SQL + '  [VALUES].[MAXAMOUNT] ' + char(13);

    set @SQL = @SQL + 'from [VALUES];';

    declare @PARAMDEF nvarchar(max);
    if @ISBBEC = 1
    begin
      set @PARAMDEF = N'@STARTDATE datetime, @ENDDATE datetime, @ASOF datetime, @AMOUNTOPTIONSTART money, @AMOUNTOPTIONEND money, @DEFAULTVALUE money, @CURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @ORGANIZATIONCURRENCYID uniqueidentifier, @SITES xml';

      exec sp_executesql @SQL, @PARAMDEF, 
        @STARTDATE = @STARTDATE, 
        @ENDDATE = @ENDDATE, 
        @ASOF = @ASOF, 
        @AMOUNTOPTIONSTART = @AMOUNTOPTIONSTART, 
        @AMOUNTOPTIONEND = @AMOUNTOPTIONEND, 
        @DEFAULTVALUE = @DEFAULTVALUE,
        @CURRENCYID = @CURRENCYID, 
        @DECIMALDIGITS = @DECIMALDIGITS, 
        @ROUNDINGTYPECODE =@ROUNDINGTYPECODE, 
        @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
        @SITES = @SITES;
    end
    else
    begin
      set @PARAMDEF = N'@STARTDATE datetime, @ENDDATE datetime, @ASOF datetime, @AMOUNTOPTIONSTART money, @AMOUNTOPTIONEND money, @DEFAULTVALUE money';

      exec sp_executesql @SQL, @PARAMDEF, 
        @STARTDATE = @STARTDATE, 
        @ENDDATE = @ENDDATE, 
        @ASOF = @ASOF, 
        @AMOUNTOPTIONSTART = @AMOUNTOPTIONSTART, 
        @AMOUNTOPTIONEND = @AMOUNTOPTIONEND, 
        @DEFAULTVALUE = @DEFAULTVALUE;
    end

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

  return 0;