USP_RE7SMARTFIELD_GIFTCOUNTS

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

  declare @IDSET nvarchar(128);
  declare @SQL nvarchar(max);
  declare @MATH nvarchar(100);

  set @IDSET = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME] (@GIFTSELECTION);
  select @MATH = 
    case @COUNTTYPE
    when 0 then 'count(distinct [GIFT].[GIFTID])' --Total gifts given

    when 1 then 'count([GIFT].[GIFTID])' --Total gift splits given

    when 2 then '(datediff(month, min([GIFT].[DATE]), max([GIFT].[DATE]))/12) + 1' --Total years given

    when 3 then 'count(distinct datepart(year,[GIFT].[DATE]))' --Distinct years given

    end

  --Consecutive years given

  --Does full refresh (no @ASOF)

  --Year down to the month (not the day)

  if @COUNTTYPE = 4
  begin
    set @SQL = 'with ';

    if @INCLUDE in (1, 2) -- soft only 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) +
        '), ';

    set @SQL = @SQL +
      '[BANDING]([CONSTITUENT_LOCALID],[BAND]) as' + char(13) + 
      '(' + char(13) + 
      '  select [LOCALID], 0' + char(13) + 
      '  from dbo.[V_QUERY_RE7_CONSTITUENT]' + char(13);

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

    set @SQL = @SQL +
      '  union all ' + char(13) + 
      '  select [GIFT].[CONSTITUENT_SYSTEMID], (datediff(month, [GIFT].[DATE], getdate())/12) + 1' + 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 in (1, 2) -- soft only or both

    set @SQL = @SQL + 
      '  union all ' + char(13) + 
      '  select [SC].[CONSTITUENT_LOCALID], (datediff(month, [GIFT].[DATE], getdate())/12) + 1' + char(13) + 
      '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC]' + char(13) + 
      '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[GIFTID]' + char(13) + 
      '  inner join [GIFTLEVELIDS] [GIFTIDS] on [SC].[REVENUE_LOCALID] = [GIFTIDS].[ID]' + char(13);

  set @SQL = @SQL + 
      '),' + char(13) + 
      'GAPS([CONSTITUENT_LOCALID],[BAND]) as' + char(13) + 
      '(   ' + char(13) + 
      '  select distinct [B1].[CONSTITUENT_LOCALID], [B1].[BAND]' + char(13) + 
      '  from [BANDING] [B1]' + char(13) + 
      '  where not exists (select 1 from [BANDING] [B2] where [B2].[CONSTITUENT_LOCALID] = [B1].[CONSTITUENT_LOCALID] and [B2].[BAND] = [B1].[BAND]+1)' + char(13) + 
      ')' + char(13) + 
      'select [CONSTITUENT_LOCALID], min([BAND])' + char(13) + 
      'from [GAPS]' + char(13) + 
      'group by [CONSTITUENT_LOCALID];';  
    exec sp_executesql @SQL;
  end
  else if @ASOF is null or @ASOF < (select [DATECHANGED] from dbo.[IDSETREGISTER] where [ID] = @GIFTSELECTION)
  begin
    set @SQL = 'with ';
    if @INCLUDE in (1, 2) -- soft only 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 + 
      '[COUNTS] ([CONSTITUENTID], [GIFTID], [DATE]) as' + char(13) +
      '(' + char(13);

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

      set @SQL = @SQL +
        'select [GIFT].[CONSTITUENT_SYSTEMID], [GIFT].[GIFTID], [GIFT].[DATE]' + 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

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

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

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

    set @SQL = @SQL +
      '), ' + char(13) +
      '[CALC] ([ID], [VALUE]) as' + char(13) + 
      '(' + char(13) + 
      '  select [GIFT].[CONSTITUENTID], '+ @MATH + char(13) + 
      '  from [COUNTS] [GIFT]' + char(13) + 
      '  group by [GIFT].[CONSTITUENTID]' + char(13) + 
      ')' + char(13) + 
      'select [CONSTITUENT].[LOCALID], isnull([CALC].[VALUE],0)' + 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
  else
  begin
    set @SQL = 'with ';

    if @INCLUDE in (1,2)--soft only 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--both

      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 + 
      '), ' +
      '[COUNTS] ([CONSTITUENTID], [GIFTID], [DATE]) as' + char(13) +
      '(' + char(13);

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

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

    if @INCLUDE = 2--both

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

    if @INCLUDE in (1,2)
      set @SQL = @SQL +
        'select [SC].[CONSTITUENT_LOCALID], [SC].[REVENUE_LOCALID], [GIFT].[DATE]' + 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) +
        '  inner join dbo.[RE7_REVENUE] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[LOCALID]' + char(13);

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

      '[CALC] ([ID], [VALUE]) as' + char(13) + 
      '(' + char(13) + 
      ' select [GIFT].[CONSTITUENTID], ' + @MATH + char(13) + 
      '  from [CONSTITS]' + char(13) + 
      '  inner join [COUNTS] [GIFT] on [CONSTITS].[CONSTITUENT_LOCALID] = [GIFT].[CONSTITUENTID]' + char(13) + 
      '  group by [GIFT].[CONSTITUENTID]' + char(13) + 
      ')' + char(13) + 
      --Return affected constituents with new values

      '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
  return 0;