UFN_REVENUEBATCH_GENERATEINSTALLMENTS2

CREATE function dbo.UFN_REVENUEBATCH_GENERATEINSTALLMENTS2
(
    @AMOUNT money,
    @INSTALLMENTFREQUENCYCODE tinyint,
    @INSTALLMENTSTARTDATE datetime,
    @NUMBEROFINSTALLMENTS integer,
    @TRANSACTIONCURRENCYID uniqueidentifier,
    @TOTALBENEFITS xml = null
)
returns xml
with execute as caller
as begin
    declare @INSTALLMENTS xml;
    declare @TRANSACTIONCURRENCYDECIMALDIGITS tinyint = 2;
    declare @i integer = 0;
    declare @TYPICALAMOUNT money;
    declare @INSTALLMENTTABLE table
        (
            DATE datetime,
            AMOUNT money,
            RECEIPTAMOUNT money,
            SEQUENCE int
        );

    select
        @TRANSACTIONCURRENCYDECIMALDIGITS = coalesce(DECIMALDIGITS, 2)
    from dbo.UFN_CURRENCY_GETPROPERTIES(@TRANSACTIONCURRENCYID);

    set @TYPICALAMOUNT = ROUND(@AMOUNT / @NUMBEROFINSTALLMENTS, @TRANSACTIONCURRENCYDECIMALDIGITS);

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

    -- Do not generate installments for Irregular frequency

    if @INSTALLMENTFREQUENCYCODE <> 4
        begin
            while @i < @NUMBEROFINSTALLMENTS
                begin

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

                    if @INSTALLMENTFREQUENCYCODE = 0
                        insert into @INSTALLMENTTABLE values (dateadd(yy, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 1
                        insert into @INSTALLMENTTABLE values (dateadd(mm, @i * 6, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 2
                        insert into @INSTALLMENTTABLE values (dateadd(qq, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 3
                        insert into @INSTALLMENTTABLE values (dateadd(mm, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 5
                        insert into @INSTALLMENTTABLE values (@INSTALLMENTSTARTDATE, @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 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,@INSTALLMENTSTARTDATE);

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

                                    if datepart(dd,@INSTALLMENTSTARTDATE) <= 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,@INSTALLMENTSTARTDATE) > 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
                                                set @NEXTDATE = dateadd(dd,15,@NEXTDATE);
                                        end
                                end

                            insert into @INSTALLMENTTABLE values (@NEXTDATE, @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);

                    end
                    else if @INSTALLMENTFREQUENCYCODE = 8
                        insert into @INSTALLMENTTABLE values (dateadd(ww, @i * 2, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);

                    set @i = @i + 1;
                end

            declare @TOTALBENEFITAMOUNT money = 0

            if @TOTALBENEFITS is not null
            begin
                select @TOTALBENEFITAMOUNT = coalesce(sum(TOTALVALUE), 0)
                from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@TOTALBENEFITS);
            end

            declare @TOTALRECEIPTAMOUNT money = @AMOUNT - @TOTALBENEFITAMOUNT
            set @i = @NUMBEROFINSTALLMENTS

            -- calculate receipt amount for pledge

            while @i > 0
            begin
                declare @INSTALLMENTAMOUNT money
                select @INSTALLMENTAMOUNT = AMOUNT
                from @INSTALLMENTTABLE
                where SEQUENCE = @i

                if @TOTALRECEIPTAMOUNT > @INSTALLMENTAMOUNT
                begin
                    update @INSTALLMENTTABLE
                    set RECEIPTAMOUNT = @INSTALLMENTAMOUNT
                    where SEQUENCE = @i
                end
                else
                begin
                    update @INSTALLMENTTABLE
                    set RECEIPTAMOUNT = @TOTALRECEIPTAMOUNT
                    where SEQUENCE = @i
                end

                set @TOTALRECEIPTAMOUNT = @TOTALRECEIPTAMOUNT - @INSTALLMENTAMOUNT
                if @TOTALRECEIPTAMOUNT < 0
                    set @TOTALRECEIPTAMOUNT = 0

                set @i = @i - 1
            end

            set @INSTALLMENTS = 
            (
                select DATE, AMOUNT, RECEIPTAMOUNT, SEQUENCE
                from @INSTALLMENTTABLE
                for xml raw ('ITEM'), type, elements, root ('INSTALLMENTS'), binary base64    
            );
        end

    return @INSTALLMENTS;
end