UFN_RECURRINGGIFTINSTALLMENTACTIVITY

CREATE function dbo.UFN_RECURRINGGIFTINSTALLMENTACTIVITY(@ID uniqueidentifier)
returns @ACTIVITY table
  (DATE date,
   ACTIVITY nvarchar(100),
   AMOUNT money,
   BALANCE money,
   DETAILS nvarchar(500),
   CURRENCYID uniqueidentifier,
   SEQUENCE smallint,
   ID uniqueidentifier,
   TYPECODE tinyint)
as
begin
  declare @ORIGINALAMOUNT money;

  select @ORIGINALAMOUNT = OLDAMOUNT
  from dbo.RECURRINGGIFTINSTALLMENTEVENT
  where RECURRINGGIFTINSTALLMENTID = @ID
  and EVENTCODE = 4;

  with CTE as (
    -- Expected

    select DATE,
           'Expected' ACTIVITY,
           0 AMOUNT,
           isnull(@ORIGINALAMOUNT,TRANSACTIONAMOUNT) BALANCE,
           null DETAILS,
           TRANSACTIONCURRENCYID CURRENCYID,
           null DATEADDED,
           ID,
           0 TYPECODE
    from dbo.RECURRINGGIFTINSTALLMENT
    where ID = @ID
    union all
    -- Past due

    select cast(PASTDUEDATE as date),
           'Past due',
           0 AMOUNT,
           isnull(@ORIGINALAMOUNT,TRANSACTIONAMOUNT) BALANCE,  -- the correct balance will be determined below

           null,
           TRANSACTIONCURRENCYID CURRENCYID,
           PASTDUEDATE DATEADDED,
           ID,
           1
    from dbo.RECURRINGGIFTINSTALLMENT
    where ID = @ID
    and PASTDUEDATE is not null   -- PASTDUEDATE will be filled in if the installment is or ever was Past due

    union all
    -- payments

    select FINANCIALTRANSACTION.DATE,
           'Payment',
           RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT,
           isnull(@ORIGINALAMOUNT,RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT),  -- the correct balance will be determined below

           isnull(case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                    when 1 then case when nullif(CHECKPAYMENTMETHODDETAIL.CHECKNUMBER,'') is null then null else 'Check no. ' + CHECKPAYMENTMETHODDETAIL.CHECKNUMBER end --check

                    when 2 then case when nullif(CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE,'') is null then null else CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE + ' - Authorized' end    --CC

                  end,REVENUEPAYMENTMETHOD.PAYMENTMETHOD),
           RECURRINGGIFTINSTALLMENTPAYMENT.APPLICATIONCURRENCYID,
           RECURRINGGIFTINSTALLMENTPAYMENT.DATEADDED,
           RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID,
           2
    from dbo.RECURRINGGIFTINSTALLMENTPAYMENT
    inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
    left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
    left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
    left join dbo.CHECKPAYMENTMETHODDETAIL on CHECKPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    where RECURRINGGIFTINSTALLMENT.ID = @ID
    union all
    -- skips/writeoffs

    select RECURRINGGIFTWRITEOFF.DATE,
           RECURRINGGIFTWRITEOFF.TYPE,
           RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONAMOUNT,
           isnull(@ORIGINALAMOUNT,RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT),  -- the correct balance will be determined below

           case when RECURRINGGIFTWRITEOFF.REASONTYPECODE = 0 then
                  case when RECURRINGGIFTWRITEOFF.TYPECODE = 0 then WRITEOFFREASONCODE.DESCRIPTION 
                       when RECURRINGGIFTWRITEOFF.TYPECODE = 1 then RECURRINGGIFTSTATUSREASONCODE.DESCRIPTION end
                else RECURRINGGIFTWRITEOFF.REASONTYPE end,
           RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONCURRENCYID,
           RECURRINGGIFTINSTALLMENTWRITEOFF.DATEADDED,
           RECURRINGGIFTINSTALLMENTWRITEOFF.ID,
           case RECURRINGGIFTWRITEOFF.TYPECODE when 0 then 3 else 4 end
      from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF
     inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID
      inner join dbo.RECURRINGGIFTWRITEOFF on RECURRINGGIFTWRITEOFF.ID = RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID
      left join dbo.WRITEOFFREASONCODE on WRITEOFFREASONCODE.ID = RECURRINGGIFTWRITEOFF.REASONCODEID
      left join dbo.RECURRINGGIFTSTATUSREASONCODE on RECURRINGGIFTSTATUSREASONCODE.ID = RECURRINGGIFTWRITEOFF.SKIPREASONCODEID
      where RECURRINGGIFTINSTALLMENT.ID = @ID
    union all
    -- installment events

    select E.DATE,
           case E.EVENTCODE when 4 then replace(E.EVENT,'changed',case when AMOUNTCHANGE > 0 then 'increased' else 'decreased' end) else E.EVENT end,
           case E.EVENTCODE when 4 then -1*E.AMOUNTCHANGE else 0 end AMOUNT,
           isnull(@ORIGINALAMOUNT,I.TRANSACTIONAMOUNT) BALANCE,
           case E.EVENTCODE when 0 then E.PROCESSNAME when 1 then E.RESULTCODE + ' - Authorized' when 2 then E.REJECTIONMESSAGE when 3 then E.RESULTCODE when 4 then 'Recurring gift payment handling' end,
           isnull(E.CURRENCYID,I.TRANSACTIONCURRENCYID) CURRENCYID,
           E.DATEADDED,
           E.ID,
           case E.EVENTCODE when 0 then 5 when 1 then 6 when 2 then 7 when 3 then 8 else 9 end
    from dbo.RECURRINGGIFTINSTALLMENT I
    inner join dbo.RECURRINGGIFTINSTALLMENTEVENT E on E.RECURRINGGIFTINSTALLMENTID = I.ID
    where I.ID = @ID       
  )
  insert into @ACTIVITY 
         (DATE,
         ACTIVITY,
         AMOUNT,
         BALANCE,
         DETAILS,
         CURRENCYID,
         SEQUENCE,
         ID,
         TYPECODE)
  select DATE,
         ACTIVITY,
         AMOUNT,
         BALANCE,
         DETAILS,
         CURRENCYID,
         row_number() over(order by DATE, case TYPECODE when 9 then 2 else 1 end, DATEADDED, case TYPECODE when 3 then 2 else 1 end),
         ID,
         TYPECODE
  from CTE;

  -- Determine the running balance for each line.

  -- Subtract the appropriate amount if it's a payment or write-off.

  -- Set the balance to 0 for skips.

  update a
  set BALANCE = BALANCE -   
      (SELECT top 1 AMOUNT from
       (select isnull(sum(b.AMOUNT),0) AMOUNT, 3 TYPE from @ACTIVITY b where b.SEQUENCE <= a.SEQUENCE and b.TYPECODE in(2,3,9)
        union all
        select a.BALANCE, 2 from @ACTIVITY b where b.SEQUENCE <= a.SEQUENCE and b.TYPECODE = 4) X
        order by X.TYPE)
  from @ACTIVITY a;

  -- Remove Expected and Past due lines if the balance was already $0 at the time of those events.

  delete from @ACTIVITY
  where TYPECODE in(0,1)
  and BALANCE = 0;

  -- Set Details for Past due to the most recent CC/DD rejection code/message.

  update @ACTIVITY
  set DETAILS = (select top 1 DETAILS from @ACTIVITY where TYPECODE in(7,8) order by DATE desc)
  where TYPECODE = 1;

  -- Show amount adjustments as a positive number.  We needed it signed for determining the balance above.

  update @ACTIVITY
  set AMOUNT = abs(AMOUNT)
  where TYPECODE = 9
  and AMOUNT < 0;

  return;
end;