UFN_QUERY_RECURRINGGIFTACTIVITY

CREATE function dbo.UFN_QUERY_RECURRINGGIFTACTIVITY (
  @ID uniqueidentifier,
  @INSTALLMENTSFILTERMODE tinyint = 0,
  @AMENDMENTSFILTERMODE tinyint = 0
)
returns table
as
return
  with INSTALLMENT_BASE as (
    select RGI.ID,
           'I:'+ cast(RGI.ID as nvarchar(36)) DETAILID,
           RGI.DATE,
           RGI.TRANSACTIONAMOUNT AMOUNT,
           dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RGI.ID) BALANCE,
           RGI.TRANSACTIONCURRENCYID,
           RGI.STATUSCODE,
           RGI.STATUS,
           (select max(TYPECODE)
            from dbo.RECURRINGGIFTWRITEOFF W
            inner join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF IW on W.ID = IW.WRITEOFFID
            where RECURRINGGIFTINSTALLMENTID = RGI.ID) WRITEOFFTYPECODE,
           --

           -- Details column logic:

           -- No activity - blank

           -- Skipped - the Skip reason

           -- Past due - the most recent CC/DD rejection code/message, otherwise, same logic as below

           -- Otherwise:

           --   If only a single payment or write-off has been applied, the Details column for that record (see UFN_RECURRINGGIFTINSTALLMENTACTIVITY).

           --   If multiple records of a single type have been applied, "Multiple <x> applied", where <x> is the type of record.

           --   If both payments and write-offs have been applied, "Partially paid and partially written off".

           (select isnull(nullif(d.DETAILS,''),
                          (select case when count(*)=0 then null
                                       when count(*)=1 then max(DETAILS)
                                       when count(*)=2 and min(TYPECODE) = 2 and max(TYPECODE) = 9 then 'Paid (amount changed)'
                                       when min(TYPECODE) = max(TYPECODE) then 'Multiple ' + case min(TYPECODE) when 2 then 'payments' else 'write-offs' end + ' applied'
                                       else
                                         case cast(max(case TYPECODE when 2 then 1 else 0 end) as nvarchar(1)) +
                                              cast(max(case TYPECODE when 3 then 1 else 0 end) as nvarchar(1)) +
                                              cast(max(case TYPECODE when 9 then 1 else 0 end) as nvarchar(1))
                                           when '110' then 'Partially paid and partially written off'
                                           when '111' then 'Partially paid and partially written off (amount changed)'
                                           when '101' then 'Multiple payments applied (amount changed)'
                                           when '011' then 'Written off and amount changed'
                                         end
                                  end
                           from dbo.UFN_RECURRINGGIFTINSTALLMENTACTIVITY(ID)
                           where TYPECODE in(2,3,9)))
            from (select case
                           when RGI.STATUSCODE = 3 then (select DETAILS from dbo.UFN_RECURRINGGIFTINSTALLMENTACTIVITY(ID) where TYPECODE = 4)
                           when RGI.STATUSCODE = 1 then (select DETAILS from dbo.UFN_RECURRINGGIFTINSTALLMENTACTIVITY(ID) where TYPECODE = 1)
                         end DETAILS) d
           ) DETAILS
    from dbo.RECURRINGGIFTINSTALLMENT RGI
    where REVENUEID = @ID
    union all
    select null,
           'M:'+cast(R.ID as nvarchar(36))+':'+convert(nvarchar(8),I.DATE,112),
           I.DATE,
           R.TRANSACTIONAMOUNT,
           R.TRANSACTIONAMOUNT,
           R.TRANSACTIONCURRENCYID,
           0,
           'Expected',
           null,
           null
    from dbo.FINANCIALTRANSACTION R
    cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS(R.ID) I
    where R.ID = @ID
  ),
  INSTALLMENTS as (
    select ID,
           DETAILID,
           DATE,
           AMOUNT,
           BALANCE,
           TRANSACTIONCURRENCYID,
      STATUSCODE,
           STATUS,
           WRITEOFFTYPECODE,
           row_number() over(order by DATE) INSTALLMENTNUMBER,
           -- hide the skip option if the installment is skipped or has any payments or write-offs applied to it,

           -- if installment is past due (statuscode = 1)

           -- if the installment date is earlier than 5 days in the past,

           -- or if any later installments have skips, payments, or write-offs (bug 441402)

           case when BALANCE < AMOUNT or DATE < dateadd(d,-5,cast(getdate() as date)) or STATUSCODE = 1
                     or exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT i where i.REVENUEID = @ID and i.DATE > b.DATE and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(i.ID) < i.AMOUNT) then 1
                else 0 end HIDESKIP,
           DETAILS
    from INSTALLMENT_BASE b
  ),
  AMENDMENTS as (
    select a.ID,
           'A:'+cast(a.ID as nvarchar(36)) DETAILID,
           a.DATE,
           a.AMENDMENTTYPE +
            case
              when a.AMENDMENTTYPECODE = 1 then ' to ' + a.STATUS
              when a.AMENDMENTTYPECODE = 2 and a.REVENUEDEVELOPMENTFUNCTIONID is not null then ' - ' + REVENUEDEVELOPMENTFUNCTIONCODE.DESCRIPTION
              else ''
            end ACTIVITY,
           case a.AMENDMENTTYPECODE
             when 1 then case when a.STATUSCHANGETYPECODE = 0 then RECURRINGGIFTSTATUSREASONCODE.DESCRIPTION else STATUSCHANGETYPE end
             /*when 2 then
               case
                 when a.TRANSACTIONAMOUNT <> a.PREVIOUSTRANSACTIONAMOUNT then
                   case when a.FREQUENCYCODE <> a.PREVIOUSFREQUENCYCODE then 'From ' + cast(a.PREVIOUSTRANSACTIONAMOUNT as nvarchar(14)) + ' ' + lower(a.PREVIOUSFREQUENCY) + ' to ' + cast(a.TRANSACTIONAMOUNT as nvarchar(14)) + ' ' + lower(a.FREQUENCY)
                        else 'From ' + cast(a.PREVIOUSTRANSACTIONAMOUNT as nvarchar(14)) + ' to ' + cast(a.TRANSACTIONAMOUNT as nvarchar(14))
                   end
                 when a.FREQUENCYCODE <> a.PREVIOUSFREQUENCYCODE then 'From ' + lower(a.PREVIOUSFREQUENCY) + ' to ' + lower(a.PREVIOUSFREQUENCY)
               end*/
             when 3 then
               case when a.PAYMENTMETHODCODE <> a.PREVIOUSPAYMENTMETHODCODE then 'From ' + lower(a.PREVIOUSPAYMENTMETHOD) + ' to ' + lower(a.PAYMENTMETHOD)
                    when a.PAYMENTMETHODCODE <> 9 then 'New ' + lower(a.PAYMENTMETHOD) + ' information'
               end
             when 4 then
               'From ' + isnull(PREVIOUSCONSTITUENT.NAME, 'Former gift owner') + ' to ' + isnull(CONSTITUENT.NAME, 'Former gift owner')
           end DETAILS,
           a.DATEADDED
    from dbo.RECURRINGGIFTAMENDMENT a
    left join dbo.RECURRINGGIFTSTATUSREASONCODE on RECURRINGGIFTSTATUSREASONCODE.ID = a.RECURRINGGIFTSTATUSREASONCODEID
    left join dbo.REVENUEDEVELOPMENTFUNCTION on REVENUEDEVELOPMENTFUNCTION.ID = a.REVENUEDEVELOPMENTFUNCTIONID
    left join dbo.REVENUEDEVELOPMENTFUNCTIONCODE on REVENUEDEVELOPMENTFUNCTIONCODE.ID = REVENUEDEVELOPMENTFUNCTION.REVENUEDEVELOPMENTFUNCTIONCODEID
    left join dbo.CONSTITUENT PREVIOUSCONSTITUENT on PREVIOUSCONSTITUENT.ID = a.PREVIOUSCONSTITUENTID
    left join dbo.CONSTITUENT on CONSTITUENT.ID = a.CONSTITUENTID
    where a.FINANCIALTRANSACTIONID = @ID
    and (case AMENDMENTTYPECODE when 0 then 0 else AMENDMENTTYPECODE + 1 end = @AMENDMENTSFILTERMODE or @AMENDMENTSFILTERMODE = 0)
  ),
  ALL_ACTIVITY as (
    select ID,
           DETAILID,
           DATE,
           'Installment ' + cast(INSTALLMENTNUMBER as nvarchar(4)) + ' - ' + STATUS as ACTIVITY,
           AMOUNT,
           BALANCE,
           DETAILS,
           TRANSACTIONCURRENCYID,
           2 ACTIVITYTYPECODE,
           null DATEADDED,
           STATUSCODE,
           WRITEOFFTYPECODE,
           INSTALLMENTNUMBER,
           HIDESKIP
    from INSTALLMENTS
    where STATUSCODE = case @INSTALLMENTSFILTERMODE when 0 then STATUSCODE when 1 then 99 else @INSTALLMENTSFILTERMODE-2 end
    union all
    select ID,
           DETAILID,
           DATE,
           ACTIVITY,
           null,
           null,
           DETAILS,
           null,
           case when ACTIVITY = 'Added' then 1 else 3 end ACTIVITYTYPECODE,
           DATEADDED,
           99 STATUSCODE,
           null WRITEOFFTYPECODE,
           null INSTALLMENTNUMBER,
           1 HIDESKIP
    from AMENDMENTS
  )
  select ID,
         DETAILID,
         DATE,
         ACTIVITY,
         AMOUNT,
         BALANCE,
         DETAILS,
         TRANSACTIONCURRENCYID,
         row_number() over(order by DATE, ACTIVITYTYPECODE, DATEADDED) SEQ,
         STATUSCODE,
         WRITEOFFTYPECODE,
         INSTALLMENTNUMBER,
         HIDESKIP
  from ALL_ACTIVITY