USP_MKTSMARTFIELD_YEARSONFILEVALUES

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

  declare @SQL nvarchar(max) = '';
  declare @DONOROBJECTNAME sysname;
  declare @DONORIDFIELD sysname;
  declare @GIFTOBJECTNAME sysname;
  declare @GIFTDONORIDFIELD sysname;
  declare @GIFTDATEFIELD sysname;
  declare @GIFTDATECHANGEDFIELD sysname;

  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]
    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;

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

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

    /* 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);

    if @ASOF is null
      begin
        set @SQL = @SQL +
          'select' + char(13) + 
          '  [' + @DONOROBJECTNAME + '].[' + @DONORIDFIELD + '] as [ID],' + char(13) + 
          '  isnull((year(current_timestamp) - year(min([' + @GIFTOBJECTNAME + '].[' + @GIFTDATEFIELD + ']))), 0) as [YEARSONFILE]' + char(13) +
          'from dbo.[' + @DONOROBJECTNAME + ']' + char(13) +
          'left join [' + @GIFTOBJECTNAME + '] on [' + @DONOROBJECTNAME  + '].[' + @DONORIDFIELD +'] = dbo.[' + @GIFTOBJECTNAME + '].[' + @GIFTDONORIDFIELD + ']' + 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) as [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [' + @DONOROBJECTNAME + '].[' + @DONORIDFIELD + '])' + char(13);

        set @SQL = @SQL +
          'group by [' + @DONOROBJECTNAME + '].[' + @DONORIDFIELD + ']' + char(13);
      end
    else -- @ASOF is not null

      begin
        set @SQL = @SQL +
          'with [DONORS] ([ID]) as (' + char(13) + 
          '  select [GIFTS].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
          '  from dbo.[' + @GIFTOBJECTNAME + '] as [GIFTS]' + 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) as [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 dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1
          set @SQL = @SQL + dbo.[UFN_MKTSMARTFIELD_GETEXTRACONSTITS_DYNAMICSQL_2](@SITES);

        set @SQL = @SQL +
          '), [CALC] ([ID], [VALUE]) as (' + char(13) + 
          '  select' + char(13) + 
          '    [GIFT].[' + @GIFTDONORIDFIELD + '],' + char(13) + 
          '    isnull((year(current_timestamp) - year(min([GIFT].[' + @GIFTDATEFIELD + ']))), 0) as [YEARSONFILE]' + char(13) +
          '  from [DONORS]' + char(13) + 
          '  inner join dbo.[' + @GIFTOBJECTNAME + '] as [GIFT] on [DONORS].[ID] = [GIFT].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
          '  group by [GIFT].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
          ')' + char(13) + 
          'select [DONORS].[ID], isnull([CALC].[VALUE], 0)' + char(13) + 
          'from [DONORS]' + char(13) + 
          'left join [CALC] on [CALC].[ID] = [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;