USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFTINSTALLMENTSKIP_PRELOAD

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFTINSTALLMENTSKIP_PRELOAD
(
     @INSTALLMENTCONTEXT nvarchar(85),
     @INSTALLMENTNUMBER int = null output,
     @INSTALLMENTDATE datetime= null output,
     @INSTALLMENTBALANCE money = null output,
     @INSTALLMENTS xml = null output,
     @SKIPPEDINSTALLMENTS tinyint = null output,
     @TRANSACTIONCURRENCYID uniqueidentifier = null output,
     @RGFREQUENCYCODE tinyint = null output,
     @RGENDDATE date = null output,
     @RGSEEDDATE date = null output
)
as
set nocount on;     

-- if no other installments precede this one then this will be the first installment
set @INSTALLMENTNUMBER = 1

declare @REVENUEID uniqueidentifier = cast(substring(@INSTALLMENTCONTEXT,1,36) as uniqueidentifier);    
set @INSTALLMENTDATE = cast(substring(@INSTALLMENTCONTEXT,38,10) as date);  

select @RGENDDATE = ENDDATE,
       @RGSEEDDATE = SCHEDULESEEDDATE
from dbo.REVENUESCHEDULE where ID = @REVENUEID;

declare @INSTALLMENTID uniqueidentifier = null;
if len(@INSTALLMENTCONTEXT) > 48
  set @INSTALLMENTID = cast(substring(@INSTALLMENTCONTEXT,49,36) as uniqueidentifier);  

if @INSTALLMENTID is not null --installment exists so use the ID to retrieve the data
  begin            
     select @INSTALLMENTBALANCE = dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(@INSTALLMENTID),
                @TRANSACTIONCURRENCYID = I.TRANSACTIONCURRENCYID,
                @RGFREQUENCYCODE = RS.FREQUENCYCODE
     from dbo.RECURRINGGIFTINSTALLMENT I
     inner join dbo.REVENUESCHEDULE RS on I.REVENUEID = RS.ID
     where I.ID = @INSTALLMENTID        
  end
else --use the date and revenue ID to retrieve the data
  begin          
     select @INSTALLMENTBALANCE = R.TRANSACTIONAMOUNT,
                @TRANSACTIONCURRENCYID = R.TRANSACTIONCURRENCYID,
                @RGFREQUENCYCODE = RS.FREQUENCYCODE
        from FINANCIALTRANSACTION R 
     inner join dbo.REVENUESCHEDULE RS on R.ID = RS.ID           
        where R.ID = @REVENUEID
  end

set @SKIPPEDINSTALLMENTS = 1  

-- add the installment to be skipped along with most recent installment to the collection. The rest of the installments to be skipped (based on user input) are added in the uiModel code
set @INSTALLMENTS =
     (
     select ID, DATE, WRITEOFFAMOUNT, BALANCE, NUMBER, ACTIVITY
     from 
     (select 
        isnull(@INSTALLMENTID,'00000000-0000-0000-0000-000000000000') ID,           
        @INSTALLMENTDATE DATE,
        @INSTALLMENTBALANCE WRITEOFFAMOUNT,
        @INSTALLMENTBALANCE BALANCE,
        0 NUMBER,  
        ' ' ACTIVITY
     union all            
     select top 1 isnull(ID,'00000000-0000-0000-0000-000000000000'), 
          DATE,
          0 WRITEOFFAMOUNT,
          BALANCE,
          INSTALLMENTNUMBER NUMBER,
          ACTIVITY
from dbo.UFN_QUERY_RECURRINGGIFTACTIVITY(@REVENUEID, 0, 0)  
where DATE < @INSTALLMENTDATE
and INSTALLMENTNUMBER is not null
order by DATE desc) X
  for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
     );

--set the installment to be skipped number based on the previous (most recent) installment's number      
select @INSTALLMENTNUMBER = max(T.c.value('(NUMBER)[1]', 'int')) + 1
     from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c);        

  return 0;