USP_DONOR_INFO_HELPER_BUILDTRANSQL

CREATE procedure [dbo].[USP_DONOR_INFO_HELPER_BUILDTRANSQL] (
  @TRANTYPECODE tinyint
  ,@FILTERCRITERIA nvarchar(1000)
  ,@SQL nvarchar(max) output
  )
  with execute as owner
as
set nocount on;

set @SQL = '';

declare @APPLICATIONFILTER nvarchar(100) = ''
  ,@TRANFILTER nvarchar(50) = ''

set @TRANFILTER = 'COMMITMENT.TYPECODE=' + cast(@TRANTYPECODE as nvarchar(4))
set @APPLICATIONFILTER = case @TRANTYPECODE
    when (0) --payments

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE in (0,1,4,9,10,11,15,16,18)'
    when (1) --pledges

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 2'
    when (2) --recurring gift

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE in (3, 5)'
    when (3) --matching gift claim

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 7'
    when (4) --planned gift

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 6'
        --when (5)

        --  then N'Order'

    when (6) --grant award

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 8'
    when (7) --auction donation

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 12'
    when (8) --donor challenge claim

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 13'
        --when (28)

        --  then N'Computer check'

    when (9) --pending gift

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 17'
        --when (10)

        --  then N'Deposit'

        --when (11)

        --  then N'Adjustment deposit'

        --when (12)

        --  then N'Adjustment payment'

        --when (13)

        --  then N'Adjustment transfer out'

        --when (14)

        --  then N'Adjustment transfer in'

    when (15) --membership installment plan

      then 'SPLITAPPLICATIONINFO.APPLICATIONCODE = 19'
        --when (20)

        --  then N'Write off'

        --when (21)

        --  then N'Sold stock'

        --when (22)

        --  then N'Sold property'

        --when (23)

        --  then N'Refund'

        --when (24)

        --  then N'Deposit Correction Short'

        --when (25)

        --  then N'Deposit Correction Over'

        --when (26)

        --  then N'Payout'

        --when (27)

        --  then N'Sold Gift In Kind'

        --when (99)

        --  then N'Deleted Revenue'

    end

--payments that meet criteria 

set @SQL += 
  'insert into #TMP_DATA_DONOR_INFO
    select SOURCELINEITEM.FINANCIALTRANSACTIONID ID
      ,PAYMENTSPLIT.ID FTLID
      ,PAYMENT.CONSTITUENTID
      ,PAYMENT.[DATE]
      ,SPLITAPPLICATIONINFO.DESIGNATIONID
      ,0 [BASEAMOUNT]
      ,PAYMENTSPLIT.BASEAMOUNT [TOTALPAID]
      ,1 [NUMPAYMENTS]
      ,PAYMENT.[DATE] [LASTPAYMENTDATE]
      ,null [WRITEOFFS]
      ,SOURCELINEITEM.ID [SOURCEFTLID]
      ,PAYMENT.ID [PAYMENTID]
      ,null
    from 
      dbo.FINANCIALTRANSACTION PAYMENT 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on PAYMENT.ID=PAYMENTSPLIT.FINANCIALTRANSACTIONID
      inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO on PAYMENTSPLIT.ID = SPLITAPPLICATIONINFO.ID
      inner join #TMP_DONOR_INFO_FILTEREDIDS DESIGNATION on SPLITAPPLICATIONINFO.DESIGNATIONID = DESIGNATION.ID and DESIGNATION.RECORDTYPE=0
      inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELINEITEM on PAYMENTSPLIT.SOURCELINEITEMID = SOURCELINEITEM.ID
    where  (
        PAYMENT.DELETEDON is null
        and PAYMENTSPLIT.DELETEDON is null
        )
      and (PAYMENT.[DATE] between @FROM2 and @TO2)
      and PAYMENT.TYPECODE=0 AND ' + @APPLICATIONFILTER 
set @SQL += replace(replace(@FILTERCRITERIA,'#CONSTITUENTID','PAYMENT.CONSTITUENTID'),'#SPLITID','PAYMENTSPLIT.ID')

--writeoffs that meet criteria 

set @SQL += 
  '; insert into #TMP_DATA_DONOR_INFO
    select COMMITMENT.ID ID
      ,WRITEOFFSPLIT.ID FTLID
      ,COMMITMENT.CONSTITUENTID
      ,WRITEOFF.[DATE] --REVISIT question whether this should be getting the breakdown from the installmentsplits... 

      ,SPLITAPPLICATIONINFO.DESIGNATIONID
      ,0 [BASEAMOUNT]
      ,0 [TOTALPAID]
      ,null [NUMPAYMENTS]
      ,null [LASTPAYMENTDATE]
      ,WRITEOFFSPLIT.BASEAMOUNT [WRITEOFFS]
      ,COMMITMENTSPLIT.ID [SOURCEFTLID]
      --,WRITEOFFSPLIT.FINANCIALTRANSACTIONID [PAYMENTID]

      ,null [PAYMENTID]
      ,null
    from 
      dbo.FINANCIALTRANSACTION WRITEOFF 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT on WRITEOFF.ID=WRITEOFFSPLIT.FINANCIALTRANSACTIONID
      inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO on WRITEOFFSPLIT.ID = SPLITAPPLICATIONINFO.ID
      inner join #TMP_DONOR_INFO_FILTEREDIDS DESIGNATION on SPLITAPPLICATIONINFO.DESIGNATIONID = DESIGNATION.ID and DESIGNATION.RECORDTYPE=0      
      inner join dbo.FINANCIALTRANSACTION COMMITMENT on WRITEOFF.PARENTID=COMMITMENT.ID and COMMITMENT.TYPECODE=' + cast(@TRANTYPECODE as nvarchar(4)) + '
      left join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTSPLIT on WRITEOFFSPLIT.SOURCELINEITEMID = COMMITMENTSPLIT.ID
    where  (
        WRITEOFF.DELETEDON is null
        and WRITEOFFSPLIT.DELETEDON is null
        and COMMITMENT.DELETEDON is null
        )
      and (WRITEOFF.[DATE] between @FROM2 and @TO2)
      and WRITEOFF.TYPECODE=20'
set @SQL += replace(replace(@FILTERCRITERIA,'#CONSTITUENTID','COMMITMENT.CONSTITUENTID'),'#SPLITID','WRITEOFFSPLIT.ID')

--parents of the payments added above

set @SQL += '; insert into #TMP_DATA_DONOR_INFO
    select COMMITMENT.ID
      ,COMMITMENTSPLIT.ID FTLID
      ,COMMITMENT.CONSTITUENTID
      ,COMMITMENT.[DATE]
      ,SPLITAPPLICATIONINFO.DESIGNATIONID
      ,COMMITMENTSPLIT.BASEAMOUNT
      ,0 [TOTALPAID]
      ,0 [NUMPAYMENTS]
      ,null [LASTPAYMENTDATE]
      ,null [WRITEOFFS]
      ,NULL
      ,null
      ,SPLITAPPLICATIONINFO.APPLICATION
    from 
      dbo.FINANCIALTRANSACTION COMMITMENT 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTSPLIT on COMMITMENT.ID=COMMITMENTSPLIT.FINANCIALTRANSACTIONID
      inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO on COMMITMENTSPLIT.ID=SPLITAPPLICATIONINFO.ID 
    where ' + @TRANFILTER + '
      and COMMITMENTSPLIT.DELETEDON is null and COMMITMENT.DELETEDON is null
      and exists(select top 1 1 from #TMP_DATA_DONOR_INFO T where T.SOURCEFTLID = COMMITMENTSPLIT.ID)'

--Get parents that meet criteria, that were not already added above

set @SQL += '; insert into #TMP_DATA_DONOR_INFO
    select COMMITMENT.ID
      ,COMMITMENTSPLIT.ID FTLID
      ,COMMITMENT.CONSTITUENTID
      ,COMMITMENT.[DATE]
      ,SPLITAPPLICATIONINFO.DESIGNATIONID
      ,COMMITMENTSPLIT.BASEAMOUNT
      ,0 [TOTALPAID]
      ,0 [NUMPAYMENTS]
      ,null [LASTPAYMENTDATE]
      ,null [WRITEOFFS]
      ,null
      ,null
      ,SPLITAPPLICATIONINFO.APPLICATION
    from 
      dbo.FINANCIALTRANSACTION COMMITMENT 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTSPLIT on COMMITMENT.ID=COMMITMENTSPLIT.FINANCIALTRANSACTIONID
      inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO on COMMITMENTSPLIT.ID = SPLITAPPLICATIONINFO.ID
      inner join #TMP_DONOR_INFO_FILTEREDIDS DESIGNATION on SPLITAPPLICATIONINFO.DESIGNATIONID = DESIGNATION.ID and DESIGNATION.RECORDTYPE=0
    where (
        COMMITMENT.DELETEDON is null
        and COMMITMENTSPLIT.DELETEDON is null
        )
      and (COMMITMENT.[DATE] between @FROM2 and @TO2)
      and ' + @TRANFILTER + ' and not exists(select top 1 1 from #TMP_DATA_DONOR_INFO T where T.FTLID=COMMITMENTSPLIT.ID) '
set @SQL += replace(replace(@FILTERCRITERIA,'#CONSTITUENTID','COMMITMENT.CONSTITUENTID'),'#SPLITID','COMMITMENTSPLIT.ID')