USP_MKTSMARTFIELD_SINGLEGIFTVALUES

CREATE procedure dbo.[USP_MKTSMARTFIELD_SINGLEGIFTVALUES]
(
  @RECORDSOURCEID uniqueidentifier,
  @IDSETREGISTERID uniqueidentifier = null,
  @ASOF datetime = null,
  @SITES xml = null
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @IDSETOBJECTNAME sysname;
  declare @DONOROBJECTNAME sysname;
  declare @DONORIDFIELD sysname;
  declare @DONORDATECHANGEDFIELD sysname;
  declare @GIFTOBJECTNAME sysname;
  declare @GIFTDONORIDFIELD sysname;
  declare @GIFTDATEFIELD sysname;
  declare @GIFTDATECHANGEDFIELD sysname;
  declare @GIFTDATEADDEDFIELD sysname;
  declare @DATECHANGEDFIELD sysname;
  declare @ISBBEC as bit = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID);
  declare @SITESFILTER table ([ID] uniqueidentifier primary key);
  declare @PROCESSDATE datetime;
  declare @UFN_MKTSMARTFIELD_SINGLEGIFT nvarchar(max) = 
    '  case when ([CALC].[YEAR1] + [CALC].[YEAR2] + [CALC].[YEAR3] + [CALC].[YEAR4] + [CALC].[YEAR5] + [CALC].[YEAR6] + [CALC].[YEAR7] + [CALC].[YEAR8] + [CALC].[YEAR9] + [CALC].[YEAR10]) = 0 then ''No gifts''' + char(13) +
      '       when ([CALC].[YEAR1] = 1 and [CALC].[YEAR2] = 1) then case when [CALC].[YEAR3] = 1 then case when [CALC].[YEAR4] = 1 then case when [CALC].[YEAR5] = 1 then case when [CALC].[YEAR6] = 1 then' + char(13) +
    '       ''Other'' else ''5 years'' end else ''4 years'' end else ''3 years'' end else ''2 years'' end else ''Other'' end';

  begin try
    if @SITES is not null and not exists(select T.c.value('(SITEID)[1]', 'uniqueidentifier') from @SITES.nodes('/SITES/ITEM') T(c))
      set @SITES = null;

    select 
      @DONOROBJECTNAME = [OBJECTNAME],
      @DONORIDFIELD = [PRIMARYKEYFIELD],
      @DONORDATECHANGEDFIELD = isnull([MKTRECORDSOURCEFIELDMAPPINGS].[DATELASTCHANGEDFIELD], '')
    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;

    if @DONORDATECHANGEDFIELD = ''
      raiserror(N'The record source''s ''Date last changed'' field must be mapped for this smart field to process.', 15, 4);

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

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

    if @GIFTDATECHANGEDFIELD <> ''
      set @DATECHANGEDFIELD = @GIFTDATECHANGEDFIELD;
    else
      set @DATECHANGEDFIELD = @GIFTDATEADDEDFIELD;

    if @IDSETREGISTERID is not null
      select
        @IDSETOBJECTNAME = 'dbo.[' + [DBOBJECTNAME] + ']' + case when [OBJECTTYPE] = 1 then '()' else '' end
      from dbo.[IDSETREGISTER]
      where [ID] = @IDSETREGISTERID;

    insert into @SITESFILTER
    select distinct
      T.c.value('(SITEID)[1]', 'uniqueidentifier')
    from @SITES.nodes('/SITES/ITEM') T(c);

    if object_id('tempdb..#TMP_REVENUESITEFILTER') is not null
      drop table #TMP_REVENUESITEFILTER;

    create table #TMP_REVENUESITEFILTER ([ID] uniqueidentifier);

    if @SITES is not null
      insert into #TMP_REVENUESITEFILTER
      select distinct
        [FINANCIALTRANSACTION].[ID]
      from dbo.[FINANCIALTRANSACTION]
      inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
      cross apply dbo.[UFN_SITEID_MAPFROM_REVENUEID]([FINANCIALTRANSACTION].[ID]) as [SITES]
      inner join @SITESFILTER as [SITESFILTER] on [SITESFILTER].[ID] = [SITES].[SITEID]
      where [FINANCIALTRANSACTION].[DELETEDON] is null;

    set @PROCESSDATE = current_timestamp;
    set @PROCESSDATE = cast(year(@PROCESSDATE) as char(4)) + '-' + cast(month(@PROCESSDATE) as char(2)) + '-' + cast(day(@PROCESSDATE) as char(2));
    set @PROCESSDATE = dateadd(d, 1, @PROCESSDATE);
    set @PROCESSDATE = dateadd(s, -1, @PROCESSDATE);

    /* NOTE:
      this is being done using declare + exec (as opposed to sp_executesql with parameters) on purpose: sp_executesql seems to sometimes cache
       execution plans that are inefficient, and won't be deterred from using them with any "recompile" options */

    set @SQL = 
      'declare @ASOF datetime = ' + case when @ASOF is null then 'null' else '''' + convert(nvarchar(23), @ASOF, 121) + '''' end + ';' + char(13) +
      'declare @SITES xml = ' + case when @SITES is null then 'null' else '''' + convert(nvarchar(max), @SITES) + '''' end + ';' + char(13) + char(13) +
      'declare @END0 datetime = ''' + convert(nvarchar(23), @PROCESSDATE, 121) + ''';' + char(13) +
      'declare @END1 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 1, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END2 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 2, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END3 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 3, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END4 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 4, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END5 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 5, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END6 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 6, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END7 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 7, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END8 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 8, @PROCESSDATE), 121) + ''';' + char(13) +
      'declare @END9 datetime = ''' + convert(nvarchar(23), dateadd(m, -12 * 9, @PROCESSDATE), 121) + ''';' + char(13) + char(13) +
      'declare @START0 datetime = dateadd(s, 1, @END1);' + char(13) +
      'declare @START1 datetime = dateadd(s, 1, @END2);' + char(13) + 
      'declare @START2 datetime = dateadd(s, 1, @END3);' + char(13) +
      'declare @START3 datetime = dateadd(s, 1, @END4);' + char(13) +
      'declare @START4 datetime = dateadd(s, 1, @END5);' + char(13) +
      'declare @START5 datetime = dateadd(s, 1, @END6);' + char(13) +
      'declare @START6 datetime = dateadd(s, 1, @END7);' + char(13) +
      'declare @START7 datetime = dateadd(s, 1, @END8);' + char(13) +
      'declare @START8 datetime = dateadd(s, 1, @END9);' + char(13) +
      'declare @START9 datetime = ''1900-01-01'';' + char(13) + char(13);

    if @ASOF is null
      begin
        set @SQL = @SQL +
          'with [CALC] as (' + char(13) +
          '  select ' + char(13) +
          '    [DONORS].[' + @DONORIDFIELD + '],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START0 and @END0 then 1 else 0 end)) as [YEAR1],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START1 and @END1 then 1 else 0 end)) as [YEAR2],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START2 and @END2 then 1 else 0 end)) as [YEAR3],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START3 and @END3 then 1 else 0 end)) as [YEAR4],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START4 and @END4 then 1 else 0 end)) as [YEAR5],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START5 and @END5 then 1 else 0 end)) as [YEAR6],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START6 and @END6 then 1 else 0 end)) as [YEAR7],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START7 and @END7 then 1 else 0 end)) as [YEAR8],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START8 and @END8 then 1 else 0 end)) as [YEAR9],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START9 and @END9 then 1 else 0 end)) as [YEAR10]' + char(13) +
          '  from dbo.[' + @DONOROBJECTNAME + '] as [DONORS]' + char(13) +
          '  left outer join [' + @GIFTOBJECTNAME + '] as [GIFTS] on [DONORS].[' + @DONORIDFIELD + '] = [GIFTS].[' + @GIFTDONORIDFIELD + ']' + char(13);

        if @IDSETOBJECTNAME is not null set @SQL = @SQL +
          '  inner join ' + @IDSETOBJECTNAME + ' as [IDSET] on [DONORS].[' + @DONORIDFIELD + '] = [IDSET].[ID]' + char(13);

        if @ISBBEC = 1 and @SITES is not null set @SQL = @SQL +
          '  inner join #TMP_REVENUESITEFILTER as [REVENUESITEFILTER] on [REVENUESITEFILTER].[ID] = [GIFTS].[ID]' + char(13);

        if @SITES is not null set @SQL = @SQL +
          '  where @SITES is null or exists (select top 1 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [DONORS].[' + @DONORIDFIELD + '])' + char(13);

        set @SQL = @SQL +
          '  group by [DONORS].[' + @DONORIDFIELD + '] ' + char(13) +
          ') ' + char(13) +
          'select ' + char(13) +
          '  [CALC].[' + @DONORIDFIELD + '], ' + char(13) +
             @UFN_MKTSMARTFIELD_SINGLEGIFT + char(13) +
          'from [CALC]';
      end
    else
      begin
        set @SQL = @SQL +
          'with [DONORS] ([ID]) as (' + char(13) + 
          '  select [GIFTS].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
          '  from dbo.[' + @GIFTOBJECTNAME + '] as [GIFTS]' + char(13);

        if @IDSETOBJECTNAME is not null set @SQL = @SQL +
          '  inner join ' + @IDSETOBJECTNAME + ' as [IDSET] on [GIFTS].[' + @GIFTDONORIDFIELD + '] = [IDSET].[ID]' + char(13);

        if @SITES is not null set @SQL = @SQL +
          '  where @SITES is null or exists (select top 1 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [GIFTS].[' + @GIFTDONORIDFIELD + '])' + char(13);

        set @SQL = @SQL +
          '  group by [GIFTS].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
          '  having max([GIFTS].[' + @GIFTDATECHANGEDFIELD + ']) > @ASOF' + char(13);

        if @ISBBEC = 1
          set @SQL = @SQL + dbo.[UFN_MKTSMARTFIELD_GETEXTRACONSTITS_DYNAMICSQL_2](@SITES);

        set @SQL = @SQL +
          '), [CALC] as (' + char(13) + 
          '  select' + char(13) +
          '    [GIFTS].[' + @GIFTDONORIDFIELD + '],' + char(13) + 
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START0 and @END0 then 1 else 0 end)) as [YEAR1],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START1 and @END1 then 1 else 0 end)) as [YEAR2],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START2 and @END2 then 1 else 0 end)) as [YEAR3],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START3 and @END3 then 1 else 0 end)) as [YEAR4],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START4 and @END4 then 1 else 0 end)) as [YEAR5],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START5 and @END5 then 1 else 0 end)) as [YEAR6],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START6 and @END6 then 1 else 0 end)) as [YEAR7],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START7 and @END7 then 1 else 0 end)) as [YEAR8],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START8 and @END8 then 1 else 0 end)) as [YEAR9],' + char(13) +
          '    sum((case when [GIFTS].[' + @GIFTDATEFIELD + '] between @START9 and @END9 then 1 else 0 end)) as [YEAR10]' + char(13) +
          '  from [DONORS]' + char(13) + 
          '  inner join dbo.[' + @GIFTOBJECTNAME + '] as [GIFTS] on [DONORS].[ID] = [GIFTS].[' + @GIFTDONORIDFIELD + ']' + char(13);

        if @ISBBEC = 1 and @SITES is not null set @SQL = @SQL +
          '  inner join #TMP_REVENUESITEFILTER as [REVENUESITEFILTER] on [REVENUESITEFILTER].[ID] = [GIFTS].[ID]' + char(13);

        set @SQL = @SQL +
          '  group by [GIFTS].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
          ')' + char(13) + 
          'select' + char(13) +
          '  [DONORS].[ID],' + char(13) +
             @UFN_MKTSMARTFIELD_SINGLEGIFT + char(13) +
          'from [DONORS]' + char(13) + 
          'left join [CALC] on [CALC].[' + @GIFTDONORIDFIELD + '] = [DONORS].[ID]' + char(13);
      end

      set @SQL = @SQL +
        'option (recompile);' + char(13);

    exec (@SQL);

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;