USP_RE7SMARTFIELD_GIFTAMOUNTS

create procedure dbo.[USP_RE7SMARTFIELD_GIFTAMOUNTS]
( 
  @AGGREGATETYPE tinyint,
  @GIFTSELECTION uniqueidentifier,
  @ASOF datetime,
  @INCLUDE tinyint = 0
) 
as 
  set nocount on;

  if not exists (select 1 from dbo.[IDSETREGISTER] where [ID] = @GIFTSELECTION)
  begin
    raiserror('The gift selection for this smart field no longer exists.', 13, 1);
    return 1;
  end

  declare @IDSET nvarchar(128);
  declare @SQL nvarchar(max);
  declare @MATH nvarchar(100);
  set @IDSET = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME] (@GIFTSELECTION);

  declare @AMOUNTFIELD sysname;

  if @AGGREGATETYPE in (0,1,4,5,6,7,8)
  begin
    select @MATH = 
      case
      when @AGGREGATETYPE in (0) then 'sum([GIFT].[AMOUNT])/count(distinct [GIFT].[GIFTID])' --Average gift

      when @AGGREGATETYPE in (1) then 'avg([GIFT].[AMOUNT])' --Average gift split

      when @AGGREGATETYPE in (4, 5) then 'min([GIFT].[AMOUNT])' --Smallest gift

      when @AGGREGATETYPE in (6, 7) then 'max([GIFT].[AMOUNT])' --Largest gift

      when @AGGREGATETYPE in (8) then 'sum(isnull([GIFT].[AMOUNT],0))' --Total gift amount

      end,
      @AMOUNTFIELD = 
      case
      when @AGGREGATETYPE in (0, 1, 5, 7, 8) then '[GIFT].[SPLITAMOUNT]'
      when @AGGREGATETYPE in (4, 6) then '[GIFT].[AMOUNT]'
      end

    --Full Refresh 

    if @ASOF is null or @ASOF < (select [DATECHANGED] from dbo.[IDSETREGISTER] where [ID] = @GIFTSELECTION)
    begin
      set @SQL = '';

      if @INCLUDE in (1, 2) -- soft only or both

        set @SQL = 'with [GIFTLEVELIDS] ([ID]) as ' + char(13) +
          '( ' + char(13) +
          'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
          '), ' + char(13);

      if @INCLUDE = 0
        set @SQL = @SQL + 'with ';

      set @SQL = @SQL +
        '[AMOUNTS] ([CONSTITUENTID], [AMOUNT], [GIFTID]) as' + char(13) + 
        '('  + char(13);

      if @INCLUDE in (0, 2) --hard only or both

        set @SQL = @SQL +
        'select [GIFT].[CONSTITUENT_SYSTEMID], '+ @AMOUNTFIELD + ', [GIFTID]' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFT] [GIFT]' + char(13) + 
        '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

      if @INCLUDE = 2 --both so we need to union

        set @SQL = @SQL + 
          'union all' + char(13);

      if @INCLUDE in (1, 2) -- soft only or both

        set @SQL = @SQL + 
        'select [SC].[CONSTITUENT_LOCALID], [SC].[AMOUNT], [SC].[REVENUE_LOCALID]' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC]' + char(13) + 
        '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

      set @SQL = @SQL +
        '), ' + char(13) + 
        '[CALC] ([ID], [VALUE]) as' + char(13) + 
        '(' + char(13) + 
        '  select [GIFT].[CONSTITUENTID], '+ @MATH + char(13) + 
        '  from [AMOUNTS] [GIFT]' + char(13) + 
        '  group by [GIFT].[CONSTITUENTID]' + char(13) + 
        ')' + char(13) + 
        'select [CONSTITUENT].[LOCALID], isnull([CALC].[VALUE],0) [VALUE]' + char(13) + 
        'from dbo.[V_QUERY_RE7_CONSTITUENT] [CONSTITUENT]' + char(13) + 
        'left join [CALC] on [CALC].[ID] = [CONSTITUENT].[LOCALID];' + char(13);  

      exec sp_executesql @SQL;
    end
    --Incremental Refresh

    else
    begin
      set @SQL = '';

      --Find all affected constituents

      if @INCLUDE in (1,2)--soft or both

        set @SQL = @SQL + 
          'with [GIFTLEVELIDS] ([ID]) as ' + char(13) +
          '( ' + char(13) +
          'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
          '), ' + char(13);

      if @INCLUDE = 0 --hard only

        set @SQL = @SQL + 'with ';

      set @SQL = @SQL + 
        '[CONSTITS] ([CONSTITUENT_LOCALID]) as ' + char(13) + 
        '(' + char(13);

      if @INCLUDE in (0,2) --hard or both

        set @SQL = @SQL + 
          '  select isnull([VIEW].[CONSTITUENT_SYSTEMID],[RSD].[CONSTITUENT_LOCALID])' + char(13) + 
          '  from dbo.[V_QUERY_RE7_GIFT] [VIEW]' + char(13) + 
          '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [VIEW].[GIFTSPLITID]' + char(13) + 
          '  full join [RE7_REVENUESPLIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_SYSTEMID]' + char(13) + 
          '  group by [VIEW].[CONSTITUENT_SYSTEMID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) + 
          '  having max([VIEW].[DATELASTCHANGED]) > @ASOF' + char(13) + 
          '  or max([DELETEDDATE]) > @ASOF' + char(13);

      if @INCLUDE = 2 --both so we need to union

        set @SQL = @SQL + 'union all' + char(13);

    if @INCLUDE in (1,2)
      set @SQL = @SQL + 
        '    select isnull([VIEW].[CONSTITUENT_LOCALID],[RSD].[CONSTITUENT_LOCALID])' + char(13) +
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [VIEW]' + char(13) +
        '  inner join [GIFTLEVELIDS] on [VIEW].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13) +
        '  full join [RE7_SOFTCREDIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_LOCALID]' + char(13) +
        '  group by [VIEW].[CONSTITUENT_LOCALID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) +
        '  having (max([VIEW].[REVENUE_DATECHANGED]) > @ASOF) or (max([RSD].[DELETEDDATE]) > @ASOF)' + char(13);

    set @SQL = @SQL +
      '), ' + char(13) +
      '[AMOUNTS] ([CONSTITUENTID], [AMOUNT], [GIFTID]) as ' + char(13) +
      '(' + char(13);

      --Calculate new values with available data

      if @INCLUDE in (0, 2) --hard only or both

        set @SQL = @SQL +
          '  select [GIFT].[CONSTITUENT_SYSTEMID], '+ @AMOUNTFIELD + ', [GIFTID]' + char(13) + 
          '  from [CONSTITS]' + char(13) + 
          '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [CONSTITS].[CONSTITUENT_LOCALID] = [GIFT].[CONSTITUENT_SYSTEMID]' + char(13) + 
          '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

      if @INCLUDE = 2 --both so we must union

        set @SQL = @SQL + 'union all' + char(13);

      if @INCLUDE in (1, 2) -- soft only or both

        set @SQL = @SQL +
          'select [SC].[CONSTITUENT_LOCALID], [SC].[AMOUNT], [SC].[REVENUE_LOCALID]' + char(13) + 
          '  from [CONSTITS]' + char(13) + 
          '  inner join dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC] on [CONSTITS].[CONSTITUENT_LOCALID] = [SC].[CONSTITUENT_LOCALID]' + char(13) + 
          '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

      set @SQL = @SQL + '),' + char(13);

      --Return affected constituents with new values

      set @SQL = @SQL + 
      '[CALC] ([ID], [VALUE]) as' + char(13) + 
      '(' + char(13) + 
      '  select [GIFT].[CONSTITUENTID], '+ @MATH + char(13) + 
      '  from [AMOUNTS] [GIFT]' + char(13) + 
      '  group by [GIFT].[CONSTITUENTID]' + char(13) + 
      ')' + char(13) + 
        'select [CONSTITS].[CONSTITUENT_LOCALID], isnull([CALC].[VALUE],0)' + char(13) + 
        'from [CONSTITS]' + char(13) + 
        'left join [CALC] on [CALC].[ID] = [CONSTITS].[CONSTITUENT_LOCALID];' + char(13); 
        exec sp_executesql @SQL, N'@ASOF datetime', @ASOF = @ASOF;
    end
  end
  else 
  begin 
    select @MATH = 
      case @AGGREGATETYPE
      when 2 then 'asc' --First gift

      when 3 then 'desc' --Last gift

      end

    --Full Refresh

    if @ASOF is null or @ASOF < (select [DATECHANGED] from dbo.[IDSETREGISTER] where [ID] = @GIFTSELECTION)
    begin
      set @SQL = 'with ';

      if @INCLUDE in (1, 2) --soft or both

        set @SQL = @SQL +
          '[GIFTLEVELIDS] ([ID]) as ' + char(13) +
          '( ' + char(13) +
          'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
          '), ' + char(13);

      set @SQL = @SQL +
        '[GIFTRANKS] ([CONSTITUENTID], [AMOUNT], [DATE], [GIFTSPLITID]) as ' + char(13) +
        '(' + char(13);

      if @INCLUDE in (0, 2) --hard only or both

        set @SQL = @SQL +
          '  select [GIFT].[CONSTITUENT_SYSTEMID], [GIFT].[AMOUNT], [GIFT].[DATE], [GIFT].[GIFTSPLITID] ' + char(13) + 
          '  from dbo.[V_QUERY_RE7_GIFT] [GIFT]' + char(13) + 
          '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

      if @INCLUDE = 2
        set @SQL = @SQL + 'union all' + char(13);

      if @INCLUDE in (1, 2) --soft only or both

        set @SQL = @SQL +
        '  select [SC].[CONSTITUENT_LOCALID], [SC].[AMOUNT], [GIFT].[DATE], [GIFT].[LOCALID] ' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC]' + char(13) + 
        '  inner join dbo.[RE7_REVENUE] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[LOCALID]' + char(13) + 
        '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

      set @SQL = @SQL +
        '), ' + char(13) +
        '[CONSTITRANKS] ([CONSTITUENT_LOCALID], [AMOUNT], [RANK]) as' + char(13) + 
        '(' + char(13) + 
        '  select distinct [GIFT].[CONSTITUENTID], [GIFT].[AMOUNT], rank() over(partition by [GIFT].[CONSTITUENTID] order by [GIFT].[DATE] ' + @MATH + ', [GIFT].[GIFTSPLITID] ' + @MATH + ') [RANK]' + char(13) + 
        '  from [GIFTRANKS] [GIFT]' + char(13) + 
        ')' + char(13) + 
        'select [CONSTITUENT].[LOCALID], isnull([CONSTITRANKS].[AMOUNT],0)' + char(13) + 
        'from dbo.[V_QUERY_RE7_CONSTITUENT] [CONSTITUENT]' + char(13) + 
        'left join [CONSTITRANKS] on [CONSTITRANKS].[CONSTITUENT_LOCALID] = [CONSTITUENT].[LOCALID]' + char(13) + 
        'where [RANK] = 1 or [RANK] is null;' + char(13); 

      exec sp_executesql @SQL;
    end
    --Incremental Refresh

    else
    begin
      set @SQL = 'with ';
      if @INCLUDE in (1, 2) --soft or both

        set @SQL = @SQL +
          '[GIFTLEVELIDS] ([ID]) as ' + char(13) +
          '( ' + char(13) +
          'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
          '), ' + char(13);

      --Find all affected constituents

      set @SQL = @SQL +
        '[CONSTITS] ([CONSTITUENT_LOCALID]) as' + char(13) +
        '(' + char(13);

      if @INCLUDE in (0,2) --hard only or both

        set @SQL = @SQL + 
          '  select isnull([VIEW].[CONSTITUENT_SYSTEMID],[RSD].[CONSTITUENT_LOCALID])' + char(13) + 
          '  from dbo.[V_QUERY_RE7_GIFT] [VIEW]' + char(13) + 
          '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [VIEW].[GIFTSPLITID]' + char(13) + 
          '  full join [RE7_REVENUESPLIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_SYSTEMID]' + char(13) + 
          '  group by [VIEW].[CONSTITUENT_SYSTEMID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) + 
          '  having max([VIEW].[DATELASTCHANGED]) > @ASOF' + char(13) + 
          '  or max([DELETEDDATE]) > @ASOF' + char(13);

      if @INCLUDE = 2
        set @SQL = @SQL + 'union all' + char(13);

      if @INCLUDE in (1,2)--soft only or both

        set @SQL = @SQL + 
          '    select isnull([VIEW].[CONSTITUENT_LOCALID],[RSD].[CONSTITUENT_LOCALID])' + char(13) +
          '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [VIEW]' + char(13) +
          '  inner join [GIFTLEVELIDS] on [VIEW].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13) +
          '  full join [RE7_SOFTCREDIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_LOCALID]' + char(13) +
          '  group by [VIEW].[CONSTITUENT_LOCALID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) +
          '  having (max([VIEW].[REVENUE_DATECHANGED]) > @ASOF) or (max([RSD].[DELETEDDATE]) > @ASOF)' + char(13);

      set @SQL = @SQL +
        '), ' + char(13) +
        '[GIFTRANKS] ([CONSTITUENTID], [AMOUNT], [DATE], [GIFTSPLITID]) as' + char(13) +
        '(' + char(13);

      if @INCLUDE in (0, 2) --hard only or both

        set @SQL = @SQL +
          '  select [GIFT].[CONSTITUENT_SYSTEMID], [GIFT].[AMOUNT], [GIFT].[DATE], [GIFT].[GIFTSPLITID] ' + char(13) + 
          '  from [CONSTITS]' + char(13) + 
          '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [CONSTITS].[CONSTITUENT_LOCALID] = [GIFT].[CONSTITUENT_SYSTEMID]' + char(13) + 
          '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

      if @INCLUDE = 2
        set @SQL = @SQL + 'union all' + char(13);

      if @INCLUDE in (1, 2) --soft only or both

        set @SQL = @SQL +
          '  select [SC].[CONSTITUENT_lOCALID], [SC].[AMOUNT], [GIFT].[DATE], [GIFT].[LOCALID] ' + char(13) + 
          '  from [CONSTITS]' + char(13) + 
          '  inner join dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC] on [CONSTITS].[CONSTITUENT_LOCALID] = [SC].[CONSTITUENT_LOCALID]' + char(13) + 
          '  inner join dbo.[RE7_REVENUE] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[LOCALID]' + char(13) +
          '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

      set @SQL = @SQL + 
        '), ' + char(13) +
      --Calculate new values with available data

        'CONSTITRANKS([CONSTITUENT_LOCALID], [AMOUNT], [RANK]) as' + char(13) + 
        '(' + char(13) + 
        '  select distinct [GIFT].[CONSTITUENTID], [GIFT].[AMOUNT], rank() over(partition by [GIFT].[CONSTITUENTID] order by [GIFT].[DATE] ' + @MATH + ', [GIFT].[GIFTSPLITID] ' + @MATH + ') [RANK]' + char(13) + 
        '  from [GIFTRANKS] [GIFT]' + char(13) + 
        ')' + char(13) + 
        'select [CONSTITS].[CONSTITUENT_LOCALID], isnull([CONSTITRANKS].[AMOUNT],0)' + char(13) + 
        'from [CONSTITS] ' + char(13) + 
        'left join [CONSTITRANKS] on [CONSTITRANKS].[CONSTITUENT_LOCALID] = [CONSTITS].[CONSTITUENT_LOCALID]' + char(13) + 
        'where [RANK] = 1 or [RANK] is null;' + char(13);

      exec sp_executesql @SQL, N'@ASOF datetime', @ASOF = @ASOF;
    end
  end

  return 0;