USP_REVENUEUPDATEBATCH_BUILDINSTALLMENTS

CREATE procedure dbo.USP_REVENUEUPDATEBATCH_BUILDINSTALLMENTS
(
    @REVENUEID uniqueidentifier,
    @AMOUNT money,
    @PREVIOUSAMOUNT money,
    @FREQUENCYCODE tinyint,
    @NUMBEROFINSTALLMENTS int,
    @TRANSACTIONCURRENCYID uniqueidentifier,
    @SPLITS xml,
    @INSTALLMENTS xml = null output, 
    @DATE datetime, 
    @PREVIOUSDATE datetime
)
as
begin
    declare @APPLIEDAMOUNT money;
    declare @LASTINSTALLMENTPAID uniqueidentifier;
    declare @TOTALNUMBERINSTALLMENTS int;
    declare @i int;
   declare @TYPICALAMOUNT money;
    declare @INSTALLMENTSPLITS xml;
   declare @PREVIOUSFREQUENCYCODE tinyint;
    declare @PREVIOUSNUMBEROFINSTALLMENTS int;
    declare @OUTSTANDINGAMOUNT money;

    declare @INSTALLMENTTABLE table
    (
      ID uniqueidentifier,
      DATE datetime,
      AMOUNT money,
      RECEIPTAMOUNT money,
      BALANCE money,
      APPLIED money,
      SEQUENCE int,
      TRANSACTIONCURRENCYID uniqueidentifier
    );

    select 
        @PREVIOUSFREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE,
        @PREVIOUSNUMBEROFINSTALLMENTS = REVENUESCHEDULE.NUMBEROFINSTALLMENTS
    from dbo.REVENUESCHEDULE
    where ID = @REVENUEID;

    -- load the existing installments

    insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, APPLIED, SEQUENCE, TRANSACTIONCURRENCYID)
        select INSTALLMENT.ID, 
               INSTALLMENT.DATE, 
               INSTALLMENT.AMOUNT,
               INSTALLMENT.BALANCE,
               INSTALLMENT.APPLIED,
               INSTALLMENT.SEQUENCE,
               INSTALLMENT.TRANSACTIONCURRENCYID
          from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2(@REVENUEID) INSTALLMENT
         order by INSTALLMENT.DATE, INSTALLMENT.SEQUENCE;

    set @TOTALNUMBERINSTALLMENTS = (select count(*) from @INSTALLMENTTABLE);

    set @APPLIEDAMOUNT = dbo.UFN_PLEDGE_GETAMOUNTPAID(@REVENUEID)

    -- set the amount to the balance

    if @AMOUNT > 0 
    begin
        set @OUTSTANDINGAMOUNT = @AMOUNT - @APPLIEDAMOUNT;
    end

    -- only update the @INSTALLMENTTABLE if the amount or frequency or number of installments or pledge date has changed

    if @PREVIOUSAMOUNT <> @AMOUNT or @PREVIOUSFREQUENCYCODE <> @FREQUENCYCODE or @PREVIOUSNUMBEROFINSTALLMENTS <> @NUMBEROFINSTALLMENTS or @DATE <> @PREVIOUSDATE
    begin
        declare @NEXTTRANSACTIONDATE datetime;
        declare @NEXTSEQUENCE int;
        declare @NUMBERINSTALLMENTSAPPLIED int;

        select @NUMBERINSTALLMENTSAPPLIED = count(*)
        from @INSTALLMENTTABLE
        where applied <> 0;

        select top 1
            @NEXTTRANSACTIONDATE = DATE, 
            @NEXTSEQUENCE = SEQUENCE
        from @INSTALLMENTTABLE
        where APPLIED = 0
        order by DATE, SEQUENCE

        if @DATE <> @PREVIOUSDATE
        begin
            set @NEXTTRANSACTIONDATE = @DATE
        end

        set @TOTALNUMBERINSTALLMENTS  =  @NUMBERINSTALLMENTSAPPLIED + @NUMBEROFINSTALLMENTS;

        set @AMOUNT = @OUTSTANDINGAMOUNT;

        set @i = 0;
        set @TYPICALAMOUNT = ROUND(@AMOUNT / @NUMBEROFINSTALLMENTS, 2);

        if @AMOUNT > 0
        begin
            if @AMOUNT - (@TYPICALAMOUNT * (@NUMBEROFINSTALLMENTS - 1)) < 0 
                set @TYPICALAMOUNT = @TYPICALAMOUNT - power(10, -2); 
        end
        --

        -- remove the installments that have not been paid

        --

        delete from @INSTALLMENTTABLE
        where APPLIED = 0;
        --

        -- for the remaining records, set the installment amount to what was applied to it.

        --

        update @INSTALLMENTTABLE
        set AMOUNT = APPLIED;

        -- loop through and create the new installments if amount > 0

        if @AMOUNT > 0
        begin
            while @i < @NUMBEROFINSTALLMENTS
              begin

                if @NEXTSEQUENCE = @TOTALNUMBERINSTALLMENTS
                  set @TYPICALAMOUNT = @AMOUNT - (@TYPICALAMOUNT * (@NUMBEROFINSTALLMENTS - 1));

                if @FREQUENCYCODE = 0
                  insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, SEQUENCE, TRANSACTIONCURRENCYID) 
                    values (newid(), dateadd(yy, @i, @NEXTTRANSACTIONDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @NEXTSEQUENCE, @TRANSACTIONCURRENCYID);
                else if @FREQUENCYCODE = 1
                  insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, SEQUENCE, TRANSACTIONCURRENCYID) 
                    values (newid(), dateadd(mm, @i * 6, @NEXTTRANSACTIONDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @NEXTSEQUENCE, @TRANSACTIONCURRENCYID);
                else if @FREQUENCYCODE = 2
                  insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, SEQUENCE, TRANSACTIONCURRENCYID) 
                    values (newid(), dateadd(qq, @i, @NEXTTRANSACTIONDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @NEXTSEQUENCE, @TRANSACTIONCURRENCYID);
                else if @FREQUENCYCODE = 3
                  insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, SEQUENCE, TRANSACTIONCURRENCYID) 
                    values (newid(), dateadd(mm, @i, @NEXTTRANSACTIONDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @NEXTSEQUENCE, @TRANSACTIONCURRENCYID);
                else if @FREQUENCYCODE = 5
                  insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, SEQUENCE, TRANSACTIONCURRENCYID) 
                    values (newid(), @NEXTTRANSACTIONDATE, @TYPICALAMOUNT, @TYPICALAMOUNT, @NEXTSEQUENCE, @TRANSACTIONCURRENCYID);
                else if @FREQUENCYCODE = 7
                begin
                  -- This is based on the algorithm for calculating semi-monthly dates found in RecurringGiftScheduleDataList.vb

                  declare @NUMMONTHS int;
                  declare @NEXTDATE datetime;

                  set @NUMMONTHS = @i / 2;
                  set @NEXTDATE = dateadd(mm,@NUMMONTHS,@NEXTTRANSACTIONDATE);

                  if @i % 2 <> 0
                  begin
                    declare @NEXTDATE2 datetime;

                    if datepart(dd,@NEXTTRANSACTIONDATE) <= 15
                    begin

                      set @NEXTDATE2 = dateadd(dd,15,@NEXTDATE);

                      if datepart(mm,@NEXTDATE2) > datepart(mm,@NEXTDATE)
                        set @NEXTDATE2 = dbo.UFN_DATE_THISMONTH_LASTDAY(@NEXTDATE,0)

                      set @NEXTDATE = @NEXTDATE2;
                    end
                    else
                    begin
                      if datepart(dd,@NEXTTRANSACTIONDATE) > 15
                      begin

                        set @NEXTDATE2 = dateadd(dd,15,@NEXTDATE);

                        if datepart(mm,@NEXTDATE2) = datepart(mm,@NEXTDATE) and datepart(dd,@NEXTDATE2) = 31
                          set @NEXTDATE2 = dateadd(dd,1,@NEXTDATE2);        

                        set @NEXTDATE = @NEXTDATE2;

                      end
                      else
                      begin
                        set @NEXTDATE = dateadd(dd,15,@NEXTDATE);
                      end
                    end
                  end

                  insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, SEQUENCE, TRANSACTIONCURRENCYID) 
                    values (newid(),@NEXTDATE, @TYPICALAMOUNT, @TYPICALAMOUNT, @NEXTSEQUENCE, @TRANSACTIONCURRENCYID);

                end
                else if @FREQUENCYCODE = 8
                  insert into @INSTALLMENTTABLE (ID, DATE, AMOUNT, BALANCE, SEQUENCE, TRANSACTIONCURRENCYID)
                    values (newid(), dateadd(ww, @i * 2, @NEXTTRANSACTIONDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @NEXTSEQUENCE, @TRANSACTIONCURRENCYID);

                set @i = @i + 1;
                set @NEXTSEQUENCE = @NEXTSEQUENCE + 1;
              end
        end -- installments > 0

    end -- installment fields changed

    else
    begin
        set @AMOUNT = @OUTSTANDINGAMOUNT;
    end
    --

    -- build installment xml to pass to installment splits function.

    --

    set @INSTALLMENTS = (
        select 
            INSTALLMENT.ID,
            INSTALLMENT.AMOUNT,
            INSTALLMENT.RECEIPTAMOUNT,
            INSTALLMENT.SEQUENCE,
            INSTALLMENT.TRANSACTIONCURRENCYID
        from @INSTALLMENTTABLE INSTALLMENT
        for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
    );
    --

    -- set up the installment splits

    --


    select @INSTALLMENTSPLITS = dbo.UFN_REVENUEUPDATEBATCH_BUILDINSTALLMENTSPLITS
                                (@REVENUEID, @AMOUNT, @SPLITS, @TOTALNUMBERINSTALLMENTS, @INSTALLMENTS, @TRANSACTIONCURRENCYID, @TOTALNUMBERINSTALLMENTS)


    set @INSTALLMENTS = (
        select
            INSTALLMENT.ID,
            INSTALLMENT.DATE,
            INSTALLMENT.AMOUNT,
            INSTALLMENT.RECEIPTAMOUNT,
            INSTALLMENT.BALANCE,
            INSTALLMENT.APPLIED,
            INSTALLMENT.SEQUENCE,
            (select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                T.c.value('(INSTALLMENTID)[1]','uniqueidentifier') AS 'INSTALLMENTID',
                T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'DESIGNATIONID',
                T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
                T.c.value('(APPLIED)[1]','money') AS 'APPLIED',
                T.c.value('(TRANSACTIONCURRENCYID)[1]','uniqueidentifier') AS 'TRANSACTIONCURRENCYID'
            from @INSTALLMENTSPLITS.nodes('/ITEM') T(c)
            where T.c.value('(INSTALLMENTID)[1]','uniqueidentifier') = INSTALLMENT.ID
            for xml raw('ITEM'),type,elements,BINARY BASE64
            ) as INSTALLMENTSPLITS,
            INSTALLMENT.TRANSACTIONCURRENCYID
        from @INSTALLMENTTABLE INSTALLMENT
        order by INSTALLMENT.DATE
        for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
    );

end