USP_MKTSMARTFIELD_LIFETIMEGIVINGVALUES

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

  declare @SQL nvarchar(max);

  begin try
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;

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

    -- get the donor view info based on the record source

    declare @DONORSYSNAME sysname;
    declare @DONORIDFIELD sysname;

    select 
      @DONORSYSNAME = [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;

    -- get the gift view info based on the record source

    declare @GIFTSYSNAME sysname;
    declare @GIFTDONORIDFIELD sysname;
    declare @GIFTAMOUNT sysname;
    declare @GIFTDATEFIELD sysname;
    declare @GIFTDATECHANGEDFIELD sysname;
    declare @GIFTDATEADDEDFIELD sysname;
    declare @DATECHANGEDFIELD sysname;

    select 
      @GIFTSYSNAME = [OBJECTNAME],
      @GIFTDONORIDFIELD = [DONORIDFIELD],
      @GIFTAMOUNT = [AMOUNTFIELD],
      @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 is empty the smart field cannot work

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

    --set the date changed field to what is set

    if @GIFTDATECHANGEDFIELD != ''
      set @DATECHANGEDFIELD = @GIFTDATECHANGEDFIELD;
    else
      set @DATECHANGEDFIELD = @GIFTDATEADDEDFIELD;

    declare @RECORDSOURCE_VALIDFORBBEC as bit = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID);
    declare @SITESFILTER table ([ID] uniqueidentifier primary key);

    insert into @SITESFILTER ([ID]) 
      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
      begin
        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;
      end

    if @RECORDSOURCE_VALIDFORBBEC = 1
      begin
        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        if @CURRENCYID is null
          set @CURRENCYID = @ORGANIZATIONCURRENCYID;

        select
          @DECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
          @ROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
        from dbo.[CURRENCY]
        where [CURRENCY].[ID] = @CURRENCYID;
      end

    if @ASOF is null
begin
        if @RECORDSOURCE_VALIDFORBBEC = 1
          begin
            set @SQL = 'with [CALC] ([ID], [VALUE]) as' + char(13) + 
                        '(' + char(13) + 
                        '  select [GIFT].[CONSTITUENTID], sum([GIFT].[AMOUNTINCURRENCY])' + char(13) + 
                        '  from dbo.[UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK](''' + cast(@CURRENCYID as nvarchar(36)) + ''', ''' + cast(@ORGANIZATIONCURRENCYID as nvarchar(36)) + ''', ' + cast(@DECIMALDIGITS as nvarchar) + ', ' + cast(@ROUNDINGTYPECODE as nvarchar) + ') [GIFT]' + char(13);

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

              set @SQL += '  where (([GIFT].[TRANSACTIONTYPECODE] in (0, 5) and exists(select * from dbo.[REVENUESPLIT_EXT] inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID] where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [GIFT].[ID] and [REVENUESPLIT_EXT].[APPLICATIONCODE] in (0, 1, 3, 5, 6, 7)))' + char(13) +
                          '    or ([GIFT].[TRANSACTIONTYPECODE] = 1 and exists(select * from dbo.[REVENUESPLIT_EXT] inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID] where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [GIFT].[ID] and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 0))' + char(13) + 
                          '    or ([GIFT].[TRANSACTIONTYPECODE] = 6 and exists(select * from dbo.[REVENUESPLIT_EXT] inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID] where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [GIFT].[ID] and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 8)))' + char(13);

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

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

            if @SITES is not null
              set @SQL += 'where ((@SITES is null) or exists(select 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [CONSTITUENT].[' + @DONORIDFIELD + ']))' + char(13);
          end
        else
          begin
            set @SQL = 'with [CALC] ([ID], [VALUE]) as' + char(13) + 
                        '(' + char(13) + 
                        '  select [GIFT].['+@GIFTDONORIDFIELD+'], sum([GIFT].['+@GIFTAMOUNT+'])' + char(13) + 
                        '  from dbo.['+@GIFTSYSNAME+'] [GIFT]' + char(13);

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

            set @SQL += '  group by [GIFT].['+@GIFTDONORIDFIELD+']' + char(13) + 
                        ')' + char(13) + 
                        'select [CONSTITUENT].['+@DONORIDFIELD+'], isnull([CALC].[VALUE],0)' + char(13) + 
                        'from dbo.['+@DONORSYSNAME+'] [CONSTITUENT]' + char(13) + 
                        'left join [CALC] on [CALC].[ID] = [CONSTITUENT].['+@DONORIDFIELD+']' + char(13);

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

        if @SITES is null
          exec sp_executesql @SQL;
        else
          exec sp_executesql @SQL, N'@SITES xml', @SITES;
      end
    else
      begin
        --Find all affected constituents

        set @SQL = 'with CONSTITS ([ID]) as' + char(13) + 
                   '(' + char(13) + 
                   '  select [VIEW].['+@GIFTDONORIDFIELD+']' + char(13) + 
                   '  from dbo.['+@GIFTSYSNAME+'] [VIEW]' + char(13) +
             '  where [VIEW].['+@GIFTDONORIDFIELD+'] is not null' + char(13);

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

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

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

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

                    ', [CALC] ([ID], [VALUE]) as' + char(13) + 
                    '(' + char(13);

        if @RECORDSOURCE_VALIDFORBBEC = 1
          begin
            set @SQL += '  select [GIFT].[CONSTITUENTID], sum([GIFT].[AMOUNTINCURRENCY])' + char(13) + 
                        '  from dbo.[UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK](''' + cast(@CURRENCYID as nvarchar(36)) + ''', ''' + cast(@ORGANIZATIONCURRENCYID as nvarchar(36)) + ''', ' + cast(@DECIMALDIGITS as nvarchar) + ', ' + cast(@ROUNDINGTYPECODE as nvarchar) + ') [GIFT]' + char(13);

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

            set @SQL += '  where (([GIFT].[TRANSACTIONTYPECODE] in (0, 5) and exists(select * from dbo.[REVENUESPLIT_EXT] inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID] where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [GIFT].[ID] and [REVENUESPLIT_EXT].[APPLICATIONCODE] in (0, 1, 3, 5, 6, 7)))' + char(13) +
                        '    or ([GIFT].[TRANSACTIONTYPECODE] = 1 and exists(select * from dbo.[REVENUESPLIT_EXT] inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID] where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [GIFT].[ID] and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 0))' + char(13) + 
                        '    or ([GIFT].[TRANSACTIONTYPECODE] = 6 and exists(select * from dbo.[REVENUESPLIT_EXT] inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID] where [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [GIFT].[ID] and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 8)))' + char(13);

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

            set @SQL += '  group by [GIFT].[CONSTITUENTID]' + char(13) + 
                        ')' + char(13);
          end
        else
          begin
            set @SQL += ' select [GIFT].[' + @GIFTDONORIDFIELD + '], sum([GIFT].[' + @GIFTAMOUNT + '])' + char(13) + 
                        '  from [CONSTITS]' + char(13) + 
                        '  inner join dbo.[' + @GIFTSYSNAME + '] [GIFT] on [CONSTITS].[ID] = [GIFT].[' + @GIFTDONORIDFIELD + ']' + char(13);

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

            set @SQL += '  group by [GIFT].[' + @GIFTDONORIDFIELD + ']' + char(13) + 
                        ')' + char(13);
          end

        --Return affected constituents with new values

        set @SQL += 'select [CONSTITS].[ID], isnull([CALC].[VALUE],0)' + char(13) + 
                    'from [CONSTITS]' + char(13) + 
                    'left join [CALC] on [CALC].[ID] = [CONSTITS].[ID];' + char(13);

        if @SITES is null
          exec sp_executesql @SQL, N'@ASOF datetime', @ASOF = @ASOF;
        else
          exec sp_executesql @SQL, N'@ASOF datetime, @SITES xml', @ASOF = @ASOF, @SITES = @SITES;
      end
  end try

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

  return 0;