USP_DONOR_INFO_HELPER_ADDTORESULTS

CREATE procedure [dbo].[USP_DONOR_INFO_HELPER_ADDTORESULTS] (
  @SQL nvarchar(max)
  ,@FROM datetime
  ,@TO as datetime
  ,@REVENUEFILTERID uniqueidentifier
  )
  with execute as owner
as
set nocount off;

declare @FROM2 datetime = @FROM
  ,@TO2 datetime = @TO
  ,@REVENUEFILTERID2 uniqueidentifier = @REVENUEFILTERID;

--declare @debugging bit = 0

--  ,@debugMsg nvarchar(1000)

--  ,@debugStartTime datetime = getdate();

if len(@SQL) > 0
begin
  --if @debugging = 1

  --begin

  --  set @debugMsg = '**BEFORE executing dynamic SQL to populate temp table ';

  --  set @debugStartTime = getdate();

  --  raiserror (@debugMsg,0,1)

  --  with nowait;

  --end

  exec sp_executeSQL @SQL
    ,N'@FROM2 datetime, @TO2 datetime, @REVENUEFILTERID2 uniqueidentifier'
    ,@FROM2
    ,@TO2
    ,@REVENUEFILTERID2;
    --if @debugging = 1

    --begin

    --  set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' executing dynamic SQL to populate temp table ';

    --  set @debugStartTime = getdate();

    --  raiserror (@debugMsg,0,1)

    --  with nowait;

    --end;

end;

--if @debugging = 1

--begin

--  set @debugMsg = '**BEFORE adding aggregates from temp table to results ';

--  set @debugStartTime = getdate();

--  raiserror (@debugMsg,0,1)

--  with nowait;

--end;

with CAMPAIGNS_CTE
as (
  select TEMPLINEITEMS.TRANSACTIONID
    ,dbo.UDA_BUILDLIST(distinct CAMPAIGN.[NAME]) CAMPAIGNNAMES
  from dbo.REVENUESPLITCAMPAIGN RSC
  inner join dbo.CAMPAIGN on RSC.CAMPAIGNID = CAMPAIGN.ID
  inner join #TMP_DONOR_INFO_DATA_TO_AGGREGATE TEMPLINEITEMS on RSC.REVENUESPLITID = TEMPLINEITEMS.LINEITEMID
    and TEMPLINEITEMS.SPID = @@SPID
  group by TEMPLINEITEMS.TRANSACTIONID
  )
insert into #TMP_DATA_DONOR_INFO_RESULTS (
  [SPID]
  ,[TRANSACTIONID]
  ,[CONSTITUENTID]
  ,[DATE]
  ,[TRANSACTIONTYPE]
  ,[CAMPAIGNS]
  ,[ISSPLIT]
  ,[TYPECODE]
  ,[AMOUNT]
  ,[TOTALPAID]
  ,[WRITEOFFS]
  ,[BALANCE]
  ,[NUMPAYMENTS]
  ,[LASTPAYMENTDATE]
  ,[DESIGNATION]
  ,[APPLICATION]
  ,[TRANSACTIONCURRENCYID]
  ,[ORIGTRANSACTIONAMOUNT]
  ,[GIVENANONYMOUSLY]
  )
select [SPID] = @@SPID
  ,[TRANSACTIONID] = FT.ID
  ,[CONSTITUENTID] = FT.[CONSTITUENTID]
  ,[DATE] = cast(case 
      when FT.TYPECODE = 2
        then (
            select top 1 [STARTDATE]
            from dbo.REVENUESCHEDULE
            where ID = FT.ID
            )
      else FT.[DATE]
      end as datetime) --NOTE: this could result in a date outside the date range specified - this is by design for recurring gifts    ,[TRANSACTIONTYPE]=FT.[TYPE]

  ,[TRANSACTIONTYPE] = FT.[TYPE]
  ,[CAMPAIGNS] = CAMPAIGNS_CTE.CAMPAIGNNAMES
  ,[ISSPLIT] = TT.[DEFINITELYSPLIT]
  ,[TYPECODE] = FT.[TYPECODE]
  ,[AMOUNT] = case 
    when FT.TYPECODE = 0
      then - TT.TOTALPAID
    else TT.AMOUNT
    end
  ,[TOTALPAID] = case 
    when FT.TYPECODE in (0,7)
      then null
    else TT.TOTALPAID
    end
  ,[WRITEOFFS] = case 
    when FT.TYPECODE in (
        0
        ,2
        ,3
        ,8
        )
      then null
    else TT.WRITEOFFS
    end
  ,[BALANCE] = case 
    when FT.TYPECODE in (
        0
        ,2
        ,7
        )
      then null
    else TT.BALANCE
    end
  ,[NUMPAYMENTS] = case 
    when FT.TYPECODE in (0,7)
      then null
    else TT.NUMPAYMENTS
    end
  ,[LASTPAYMENTDATE] = case 
    when FT.TYPECODE in (0,7)
      then null
    else TT.LASTPAYMENTDATE
    end
  ,[DESIGNATION] = TT.DESIGNATIONS
  ,[APPLICATION] = TT.[APPLICATION]
  ,[TRANSACTIONCURRENCYID] = FT.[TRANSACTIONCURRENCYID]
  ,[ORIGTRANSACTIONAMOUNT] = case 
    when FT.TYPECODE in (
        0
        ,1
        )
      then FT.TRANSACTIONAMOUNT
    else null
    end
  ,[GIVENANONYMOUSLY] = isnull(REVENUE_EXT.GIVENANONYMOUSLY, 0)
from (
  select TRANSACTIONID
    ,[AMOUNT] = sum(case 
        when T.[TYPECODE] in (
            0
            ,20
            )
          then null
        else T.[AMOUNT]
        end)
    ,[TOTALPAID] = sum(case 
        when T.[TYPECODE] = 0
          then - T.[AMOUNT]
        else 0
        end)
    ,[WRITEOFFS] = sum(case 
        when T.[TYPECODE] = 20
          then - T.[AMOUNT]
        else 0
        end)
    ,[BALANCE] = sum(T.[AMOUNT])
    ,[NUMPAYMENTS] = count(distinct T.PAYMENTID)
    ,[LASTPAYMENTDATE] = MAX(case 
        when T.[TYPECODE] = 0
          then T.[DATE]
        else null
        end)
    ,[APPLICATION] = dbo.UDA_BUILDLIST(distinct T.[APPLICATION])
    ,[DESIGNATIONS] = dbo.UDA_BUILDLIST(distinct D.USERID)
    ,[DEFINITELYSPLIT] = cast(case 
        when count(distinct T.[DESIGNATIONID]) > 1
          then 1
        else 0
        end as bit)
    ,[MEETSCRITERIACOUNT] = SUM(case 
        when T.MEETSCRITERIA = 1
          then 1
        else 0
        end)
  from #TMP_DONOR_INFO_DATA_TO_AGGREGATE T
  inner join dbo.DESIGNATION D on T.DESIGNATIONID = D.ID
    and T.SPID = @@SPID
  group by T.[TRANSACTIONID]
  ) TT
inner join dbo.FINANCIALTRANSACTION FT on TT.TRANSACTIONID = FT.ID
  and TT.[MEETSCRITERIACOUNT] > 0
inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
left join CAMPAIGNS_CTE on FT.ID = CAMPAIGNS_CTE.TRANSACTIONID;

--if @debugging = 1

--begin

--  set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' >> selected stuff ';

--  set @debugStartTime = getdate();

--  raiserror (@debugMsg,0,1)

--  with nowait;

--end;

--get rid of the temporary data since we have already used it

--truncate table #TMP_DONOR_INFO_DATA_TO_AGGREGATE

delete
from #TMP_DONOR_INFO_DATA_TO_AGGREGATE
where SPID = @@SPID;
  --if @debugging = 1

  --begin

  --  set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' deleting rows from temp table ';

  --  set @debugStartTime = getdate();

  --  raiserror (@debugMsg,0,1)

  --  with nowait;

  --end;