USP_RECURRINGGIFTSTATUSUPDATEPROCESS

CREATE procedure dbo.USP_RECURRINGGIFTSTATUSUPDATEPROCESS (
  @PARAMETERSETID uniqueidentifier,
  @BUSINESSPROCESSSTATUSID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @RECORDSECURITYCTE nvarchar(max),
  @RECORDSPROCESSED int output
)
as
begin
  if @CHANGEAGENTID is null
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime = getdate();
  declare @PROCESSDATE date = @CURRENTDATE;

  declare @RGS table (ID uniqueidentifier,
                      ORIGINALSTATUSCODE tinyint,
                      NEWSTATUSCODE tinyint,
                      ADDMISSINGINSTALLMENTS bit,
                      AMOUNT money,
                      BASECURRENCYID uniqueidentifier,
                      ORGANIZATIONAMOUNT money,
                      ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                      TRANSACTIONAMOUNT money,
                      TRANSACTIONCURRENCYID uniqueidentifier,
                      BASEEXCHANGERATEID uniqueidentifier,
                      TYPECODE tinyint);

  --------------------------------------------------------------------------------------------

  -- get process parameter values


  declare @IDSETREGISTERID uniqueidentifier,
          @PAYMENTMETHODVALUE xml,
          @FREQUENCYVALUE xml,
          @PASTDUECODE tinyint,
          @DAYSPASTDUE smallint,
          @LAPSEDCODE tinyint,
          @LAPSEDINSTALLMENTSPASTDUE smallint,
          @LAPSEDINSTALLMENTSDAYSPAST smallint,
          @TERMINATEDCODE tinyint,
          @TERMINATEDINSTALLMENTSPASTDUE smallint,
          @TERMINATEDINSTALLMENTSDAYSPAST smallint,
          @OTHERPAYMENTMETHODS xml,
          @CHECKLEGACYHELD bit;

  select @IDSETREGISTERID = IDSETREGISTERID,
         @PAYMENTMETHODVALUE = PAYMENTMETHODVALUE,
         @FREQUENCYVALUE = FREQUENCYVALUE,
         @PASTDUECODE = PASTDUECODE,
         @DAYSPASTDUE = DAYSPASTDUE,
         @LAPSEDCODE = LAPSEDCODE,
         @LAPSEDINSTALLMENTSPASTDUE = LAPSEDINSTALLMENTSPASTDUE,
         @LAPSEDINSTALLMENTSDAYSPAST = LAPSEDINSTALLMENTSDAYSPAST,
         @TERMINATEDCODE = TERMINATEDCODE,
         @TERMINATEDINSTALLMENTSPASTDUE = TERMINATEDINSTALLMENTSPASTDUE,
         @TERMINATEDINSTALLMENTSDAYSPAST = TERMINATEDINSTALLMENTSDAYSPAST,
         @OTHERPAYMENTMETHODS = OTHERPAYMENTMETHODS,
         @CHECKLEGACYHELD = CHECKLEGACYHELD
  from dbo.RECURRINGGIFTSTATUSUPDATEPROCESS
  where ID = @PARAMETERSETID;

  declare @PAYMENTMETHODS table (PAYMENTMETHODCODE tinyint);
  declare @FREQUENCIES table (FREQUENCYCODE tinyint);
  declare @OTHERMETHODS table (ID nvarchar(36));  

  if @IDSETREGISTERID is null
  begin
    insert into @PAYMENTMETHODS
    select
      T.c.value('(PAYMENTMETHODCODE)[1]','tinyint') AS 'PAYMENTMETHODCODE'
    from @PAYMENTMETHODVALUE.nodes('/PAYMENTMETHODVALUE/ITEM') T(c);

    insert into @FREQUENCIES
    select
      T.c.value('(FREQUENCYCODE)[1]','tinyint') AS 'FREQUENCYCODE'
    from @FREQUENCYVALUE.nodes('/FREQUENCYVALUE/ITEM') T(c);

    insert into @OTHERMETHODS
    select
      T.c.value('(ID)[1]','nvarchar(36)') AS 'ID'
    from @OTHERPAYMENTMETHODS.nodes('/OTHERPAYMENTMETHODS/ITEM') T(c);
  end

  --------------------------------------------------------------------------------------------

  -- identify recurring gifts to process


  declare @SQL nvarchar(max);

  if isnull(@RECORDSECURITYCTE,'') <> ''
    set @SQL = @RECORDSECURITYCTE;

  set @SQL = isnull(@SQL,'') +  
   'select f.ID,' + char(13) +
   '       s.STATUSCODE ORIGINALSTATUSCODE,' + char(13) +

   '       case when s.ENDDATE <= @PROCESSDATE and t.TYPECODE <> 9 then 3' + char(13) +       -- End date passed = Canceled

   '            when nextinstallment.STATUSCODE = 3 then 1' + char(13) +  -- Next installment is Skipped = Held (may be overruled by Terminated below)

   '            when s.STATUSCODE = 5 then 5' + char(13) +     -- Don't change Lapsed to Active

   '            else 0 end NEWSTATUSCODE,' + char(13) +                   -- Active (may be overruled by Lapsed below)


           -- fields for bringing the installment schedule up to date

   '       case when nextinstallment.STATUSCODE is null then 1 else 0 end ADDMISSINGINSTALLMENTS,' + char(13) +
   '       case when nextinstallment.STATUSCODE is null then r.AMOUNT end,' + char(13) +
   '       case when nextinstallment.STATUSCODE is null then r.BASECURRENCYID end,' + char(13) +
   '       case when nextinstallment.STATUSCODE is null then r.ORGANIZATIONAMOUNT end,' + char(13) +
   '       case when nextinstallment.STATUSCODE is null then r.ORGANIZATIONEXCHANGERATEID end,' + char(13) +
   '       case when nextinstallment.STATUSCODE is null then r.TRANSACTIONAMOUNT end,' + char(13) +
   '       case when nextinstallment.STATUSCODE is null then r.TRANSACTIONCURRENCYID end,' + char(13) +
   '       case when nextinstallment.STATUSCODE is null then r.BASEEXCHANGERATEID end,' + char(13) +
   '       t.TYPECODE' + char(13) +
   'from dbo.FINANCIALTRANSACTION f' + char(13) +
   'inner join dbo.REVENUESCHEDULE s on s.ID = f.ID' + char(13) +
   'outer apply (select top 1 i.STATUSCODE' + char(13) +
   '             from dbo.RECURRINGGIFTINSTALLMENT i' + char(13) +
   '             where i.REVENUEID = f.ID' + char(13) +
   '             and i.DATE >= @PROCESSDATE' + char(13) +

   -- We are expecting one of two scenarios here:

   -- 1. The installments are out of date, and there are NO installments in the future, so this status condition doesn't matter.  (nextinstallment.STATUSCODE is null)

   -- 2. There is an Expected/Past due installment in the future, so we don't need to add missing installments, but we do need to know if the next non-paid, non-written-off

   --    installment is Held or not.  (nextinstallment.STATUSCODE is not null)

   -- The only valid way to have a Paid or Written-off installment in the future, with no installment after it meeting this status condition, is if the RG is Canceled

   -- (or possibly Terminated), in which case it is excluded from this query anyway.

   '             and i.STATUSCODE in(0,1,3)' + char(13) +
   '             order by i.DATE) nextinstallment' + char(13) +
   'cross apply (select min(rs.TYPECODE) TYPECODE' + char(13) +
   '             from dbo.FINANCIALTRANSACTIONLINEITEM fl' + char(13) +
   '             inner join dbo.REVENUESPLIT_EXT rs on rs.ID = fl.ID' + char(13) +
   '             where fl.FINANCIALTRANSACTIONID = f.ID) t' + char(13) +
   'left join ' + case when isnull(@RECORDSECURITYCTE,'') <> '' then 'REVENUE_RACS' else 'dbo.REVENUE' end + ' r on r.ID = f.ID' + char(13);

  declare @ALLPAYMENTMETHODSCOUNT tinyint = 9;

  -- account for standing order if the UK flag is enabled

  if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    set @ALLPAYMENTMETHODSCOUNT = 10;

  -- check if other methods where specified for the Other payment method

  declare @OTHERMETHODSSELECTED bit = case when (select count(*) from @OTHERMETHODS) > 0 then 1 else 0 end;

  declare @DOPAYMENTMETHODS bit = case when @IDSETREGISTERID is null and (select count(*) from @PAYMENTMETHODS) between 1 and (@ALLPAYMENTMETHODSCOUNT- case @OTHERMETHODSSELECTED when 1 then 0 else 1 end) then 1 else 0 end;

  if @DOPAYMENTMETHODS = 1
  begin
    set @SQL = @SQL + 'inner join dbo.REVENUEPAYMENTMETHOD p on p.REVENUEID = f.ID' + char(13);

    if @OTHERMETHODSSELECTED = 1 
      set @SQL = @SQL + 'left join dbo.OTHERPAYMENTMETHODDETAIL d on d.ID = p.ID' + char(13);
  end
  else if @IDSETREGISTERID is not null
    set @SQL = @SQL + 'inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) sel on sel.ID = f.ID' + char(13);

  -- basic conditions

  set @SQL = @SQL +
   'where f.TYPECODE = 2' + char(13) +    -- Recurring gifts

   'and s.ISPENDING = 0' + char(13) +     -- Not in batch

   'and s.STATUSCODE in(0,1,5)';          -- Active, Held, Lapsed


  if isnull(@RECORDSECURITYCTE,'') <> ''
    set @SQL = @SQL + char(13) + 'and r.ID is not null';

  if @IDSETREGISTERID is null
  begin
    if @DOPAYMENTMETHODS = 1
    begin
      declare @PAYMENTMETHODLIST nvarchar(30) = '';

      select @PAYMENTMETHODLIST = @PAYMENTMETHODLIST + ',' + cast(PAYMENTMETHODCODE as nvarchar(3))
      from @PAYMENTMETHODS;

      set @SQL = @SQL + char(13) + 'and case when (p.PAYMENTMETHODCODE = 2 and s.CREDITCARDID is null) then 98 else p.PAYMENTMETHODCODE end in(' + substring(@PAYMENTMETHODLIST,2,len(@PAYMENTMETHODLIST)-1) + ')';

     if @OTHERMETHODSSELECTED = 1
     begin
       declare @OTHERMETHODLIST nvarchar(max) = '';

       select @OTHERMETHODLIST = @OTHERMETHODLIST + ',''''' + ID + ''''
       from @OTHERMETHODS;

       set @SQL = @SQL + char(13) + 'and (p.PAYMENTMETHODCODE <> 10 or d.OTHERPAYMENTMETHODCODEID in(' + substring(@OTHERMETHODLIST,3,len(@OTHERMETHODLIST)-1) + '))';
     end
    end

    declare @ALLFREQUENCIESCOUNT tinyint = 9;

    if (select count(*) from @FREQUENCIES) between 1 and @ALLFREQUENCIESCOUNT-1
    begin
      declare @FREQUENCYLIST nvarchar(30) = '';

      select @FREQUENCYLIST = @FREQUENCYLIST + ',' + cast(FREQUENCYCODE as nvarchar(3))
      from @FREQUENCIES;

      set @SQL = @SQL + char(13) + 'and s.FREQUENCYCODE in(' + substring(@FREQUENCYLIST,2,len(@FREQUENCYLIST)-1) + ')';
    end
  end

  -- Exclude legacy Held RGs so they don't get improperly activated

  if @CHECKLEGACYHELD = 1
  begin
    if exists(select top 1 'x' from dbo.UFN_SELECTION_REVENUE_LEGACYHELDRECURRINGGIFTS())
      set @SQL = @SQL + char(13) + 'and f.ID not in(select ID from dbo.UFN_SELECTION_REVENUE_LEGACYHELDRECURRINGGIFTS())';
    else
      -- When no more records exist in the selection, it is no longer needed, so disable the check for future runs.

      update dbo.RECURRINGGIFTSTATUSUPDATEPROCESS
      set CHECKLEGACYHELD = 0,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
      where ID = @PARAMETERSETID;
  end

  declare @PARAMETERDEF nvarchar(100) = '@PROCESSDATE date, @IDSETREGISTERID uniqueidentifier';

  insert into @RGS
  exec sp_executesql @SQL, @PARAMETERDEF, @PROCESSDATE, @IDSETREGISTERID;

  --------------------------------------------------------------------------------------------

  -- add missing installments

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  insert into dbo.RECURRINGGIFTINSTALLMENT (
    ID,
    REVENUEID,
    AMOUNT,
    DATE,
    BASECURRENCYID,
    ORGANIZATIONAMOUNT,
    ORGANIZATIONEXCHANGERATEID,
    TRANSACTIONAMOUNT,
    TRANSACTIONCURRENCYID,
    BASEEXCHANGERATEID,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    newid(),
    rg.ID,
    rg.AMOUNT,
    i.DATE,
    coalesce(rg.BASECURRENCYID,@ORGANIZATIONCURRENCYID),
    coalesce(rg.ORGANIZATIONAMOUNT,rg.AMOUNT),
    rg.ORGANIZATIONEXCHANGERATEID,
    coalesce(rg.TRANSACTIONAMOUNT,rg.AMOUNT),
    coalesce(rg.TRANSACTIONCURRENCYID,@ORGANIZATIONCURRENCYID),
    rg.BASEEXCHANGERATEID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
  from @RGS rg
  cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS_ASOFDATE(rg.ID,@PROCESSDATE) i
  where rg.ADDMISSINGINSTALLMENTS = 1
  -- don't add the "next" installment here if we already know the RG is going to be Canceled

  and (rg.NEWSTATUSCODE <> 3 or i.DATE <= @PROCESSDATE);

  --------------------------------------------------------------------------------------------

  -- find/update past due installments


  declare @PASTDUECOUNT int = 0;
  if @PASTDUECODE = 1
  begin
    -- unpaid balance x days past installment date

    update i
    set STATUSCODE = 1,
        PASTDUEDATE = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from @RGS rg
    inner join dbo.RECURRINGGIFTINSTALLMENT i on i.REVENUEID = rg.ID
    where i.STATUSCODE = 0
    and datediff(d,i.DATE,@PROCESSDATE) >= @DAYSPASTDUE;

    set @PASTDUECOUNT = @@ROWCOUNT;
  end

  else if @PASTDUECODE = 2
  begin
    -- later installment exists that is on or before the process date

    update i
    set STATUSCODE = 1,
        PASTDUEDATE = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from @RGS rg
    inner join dbo.RECURRINGGIFTINSTALLMENT i on i.REVENUEID = rg.ID
    where i.STATUSCODE = 0
    and exists(select 'x'
               from dbo.RECURRINGGIFTINSTALLMENT i2
               where i2.REVENUEID = rg.ID
               and i2.DATE between dateadd(d,1,i.DATE) and @PROCESSDATE);

    set @PASTDUECOUNT = @@ROWCOUNT;
  end

  --------------------------------------------------------------------------------------------

  -- Update RG status values for Lapsed and Terminated


  if @LAPSEDCODE <> 0 or @TERMINATEDCODE <> 0
  begin
    -- Code values of 1 check for a specified number of past due installments.

    -- Code values of 2 check for an installment with a balance a specified number of days past the installment date.


    declare @TERMINATEDVALUE smallint = case @TERMINATEDCODE when 1 then @TERMINATEDINSTALLMENTSPASTDUE when 2 then @TERMINATEDINSTALLMENTSDAYSPAST end;
    declare @MINIMUMVALUE smallint = case @LAPSEDCODE when 1 then @LAPSEDINSTALLMENTSPASTDUE when 2 then @LAPSEDINSTALLMENTSDAYSPAST else @TERMINATEDVALUE end;

    update rg
    set NEWSTATUSCODE = case when case when @TERMINATEDCODE = 1 then i.PASTDUECOUNT when @TERMINATEDCODE = 2 then i.UNPAIDINSTALLMENTDAYS end >= @TERMINATEDVALUE then 2  -- Terminated

                             when rg.NEWSTATUSCODE = 1 then 1  -- Held supersedes Lapsed

                             else 5 end  -- Lapsed

    from @RGS rg
    cross apply (select count(case when i.STATUSCODE = 1 then 1 end) PASTDUECOUNT,
                        -- The largest differential in days between existing unpaid installments and the process date.

                        isnull(datediff(d,min(i.DATE),@PROCESSDATE),0) UNPAIDINSTALLMENTDAYS
                 from dbo.RECURRINGGIFTINSTALLMENT i
                 where i.REVENUEID = rg.ID
                 and i.STATUSCODE in(0,1)  -- Expected or Past due installments have a balance

                 and i.DATE < @PROCESSDATE) i
    where rg.NEWSTATUSCODE <> 3  -- Not being updated to Canceled

    -- the RG meets the Lapsed condition, or the Terminated condition if we are not setting RGs to Lapsed

    -- the Lapsed condition will always be less than the Terminated condition, so this is the minimum requirement for update

    and case when isnull(nullif(@LAPSEDCODE,0),@TERMINATEDCODE) = 1 then i.PASTDUECOUNT else i.UNPAIDINSTALLMENTDAYS end >= @MINIMUMVALUE
    -- do not update the status of sponsorships

    and rg.TYPECODE <> 9;
  end

  --------------------------------------------------------------------------------------------

  -- START EDIT STATUS SECTION

  -- Bulk versions of logic in USP_RECURRINGGIFT_EDITSTATUS


  --delete future installments with no activity on change to Canceled or Terminated

  declare @CONTEXTCACHE varbinary(128);
  set @CONTEXTCACHE = CONTEXT_INFO();
  set CONTEXT_INFO @CHANGEAGENTID;

  delete i
  from @RGS rg
  inner join dbo.RECURRINGGIFTINSTALLMENT i on i.REVENUEID = rg.ID
  where i.DATE > @PROCESSDATE
  and not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where RECURRINGGIFTINSTALLMENTID = i.ID)
  and not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF where RECURRINGGIFTINSTALLMENTID = i.ID)
  and rg.NEWSTATUSCODE in(2,3);

  if @CONTEXTCACHE is not null
    set CONTEXT_INFO @contextCache; 

  -- I am intentionally excluding the logic for sponsorship recurring additional gifts from USP_RECURRINGGIFT_EDITSTATUS.

  -- That logic is not necessary here since we are never updating RGs from Canceled/Terminated to Active/Held/Lapsed.


  --------------------------------------------------------------------------------------------

  -- edit status

  update rs
  set STATUSCODE = rg.NEWSTATUSCODE,
      NEXTTRANSACTIONDATE = case when rg.NEWSTATUSCODE in(0,1,5) then NEXTTRANSACTIONDATE else null end,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
  from @RGS rg
  inner join dbo.REVENUESCHEDULE rs on rs.ID = rg.ID
  where rg.ORIGINALSTATUSCODE <> rg.NEWSTATUSCODE;

  -- I am intentionally excluding the logic for ensuring a future installment exists from USP_RECURRINGGIFT_EDITSTATUS.

  -- That logic is not necessary here since we are never updating RGs from Canceled/Terminated to Active/Held/Lapsed.

  -- Active/Held/Lapsed RGs will always have a future installment already.

  --------------------------------------------------------------------------------------------

  -- status change amendments

  insert into dbo.RECURRINGGIFTAMENDMENT(ID,
                                         FINANCIALTRANSACTIONID,
                                         AMENDMENTTYPECODE,
                                         DATE,
                                         STATUSCODE,
                                         PREVIOUSSTATUSCODE,
                                         STATUSCHANGETYPECODE,
                                         ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
  select newid(),
         ID,
         1,
         @PROCESSDATE,
         NEWSTATUSCODE,
         ORIGINALSTATUSCODE,
         6,
         @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
  from @RGS
  where ORIGINALSTATUSCODE <> NEWSTATUSCODE;

  --------------------------------------------------------------------------------------------

  -- write results to outcome table

  declare @RESULTTABLE table (RECORDSPROCESSED int);
  insert into dbo.RECURRINGGIFTSTATUSUPDATEPROCESSOUTCOME (
    ID,
    RECORDSPROCESSED,
    PASTDUECOUNT,
    ACTIVECOUNT,
    LAPSEDCOUNT,
    HELDCOUNT,
    TERMINATEDCOUNT,
    CANCELEDCOUNT,
    ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
  )
  output inserted.RECORDSPROCESSED into @RESULTTABLE
  select
    @BUSINESSPROCESSSTATUSID,
    count(*),
    @PASTDUECOUNT,
    count(case when ORIGINALSTATUSCODE <> 0 and NEWSTATUSCODE = 0 then 1 end),
    count(case when ORIGINALSTATUSCODE <> 5 and NEWSTATUSCODE = 5 then 1 end),
    count(case when ORIGINALSTATUSCODE <> 1 and NEWSTATUSCODE = 1 then 1 end),
    count(case when ORIGINALSTATUSCODE <> 2 and NEWSTATUSCODE = 2 then 1 end),
    count(case when ORIGINALSTATUSCODE <> 3 and NEWSTATUSCODE = 3 then 1 end),
    @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
  from @RGS;

  select @RECORDSPROCESSED = RECORDSPROCESSED
  from @RESULTTABLE;
end