USP_MKTSMARTFIELD_LIFETIMEGIFTSONFILEVALUES

CREATE procedure dbo.[USP_MKTSMARTFIELD_LIFETIMEGIFTSONFILEVALUES]
(
  @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;
  declare @GIFTIDFIELD sysname;
  declare @GIFTDATEADDEDFIELD sysname;
  declare @DATECHANGEDFIELD sysname;
  declare @ISBBEC as bit = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID);
  declare @SITESFILTER table ([ID] uniqueidentifier primary key);

  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 = isnull([DATECHANGEDFIELD], ''),
      @GIFTDATEADDEDFIELD = isnull([DATEADDEDFIELD], ''),
      @GIFTIDFIELD = [PRIMARYKEYFIELD]
    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;

    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 ([ID])
      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;

    /* 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 +
          'with [CALC] ([ID], [VALUE]) as (' + char(13) + 
          '  select' + char(13) +
          '    [GIFTS].[' + @GIFTDONORIDFIELD + '],' + char(13) +
          '    count(distinct [GIFTS].[' + @GIFTIDFIELD + ']) ' + char(13) + 
          '  from dbo.[' + @GIFTOBJECTNAME + '] as [GIFTS]' + char(13);

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

        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) + 
          ')' + char(13) + 
          'select' + char(13) +
          '  [DONORS].[' + @DONORIDFIELD + '],' + char(13) +
          '  isnull([CALC].[VALUE], 0)' + char(13) + 
          'from dbo.[' + @DONOROBJECTNAME+'] as [DONORS]' + char(13) +
          'left join [CALC] on [CALC].[ID] = [DONORS].[' + @DONORIDFIELD + ']' + 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);
      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 @ISBBEC = 1 and @SITES is not null set @SQL = @SQL +
          '  left join #TMP_REVENUESITEFILTER as [REVENUESITEFILTER] on [REVENUESITEFILTER].[ID] = [GIFTS].[' + @GIFTIDFIELD + ']';

        set @SQL = @SQL + 
          ' where [GIFTS].['+@GIFTDONORIDFIELD+'] is not null' + char(13);

        if @SITES is not null set @SQL = @SQL +
          '  and @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] ([ID], [VALUE]) as (' + char(13) + 
          '  select' + char(13) +
          '    [GIFTS].[' + @GIFTDONORIDFIELD + '],' + char(13) +
          '    count(distinct [GIFTS].[' + @GIFTIDFIELD + '])' + 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].[' + @GIFTIDFIELD + ']';

        set @SQL = @SQL +
          '  group by [GIFTS].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
          ')' + char(13) + 
          'select' + char(13) +
          '  [DONORS].[ID],' + char(13) +
          '  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;