UFN_PLEDGE_GETREVALUEDINSTALLMENTSPLITBALANCES_INDATERANGE

CREATE function dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTSPLITBALANCES_INDATERANGE(
    @PLEDGEID uniqueidentifier,
    @ASOFDATE datetime,
    @USEORIGINALRATE bit = 0,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
) 
returns @BALANCE table (
    ID uniqueidentifier,
    TRANSACTIONBALANCE money,
    BASEBALANCE money,
    ORGANIZATIONBALANCE money,
    DATE datetime,
    DESIGNATIONID uniqueidentifier
)
with execute as caller
as begin

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @BASEDECIMALDIGITS int;
    declare @ORGANIZATIONDECIMALDIGITS int;

    select
        @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
        @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
    from dbo.FINANCIALTRANSACTION
        inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID                
    where FINANCIALTRANSACTION.ID = @PLEDGEID;

    select @BASEDECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where ID = @BASECURRENCYID;
    select @ORGANIZATIONDECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;

    --If all the currencies are the same, we can skip the conversions and just return transaction currency for all values.

    if @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID and @BASECURRENCYID = @ORGANIZATIONCURRENCYID
    begin
        with CTE_BALANCE
        as(
            select
                INSTALLMENTSPLIT.ID, 
                INSTALLMENTSPLIT.TRANSACTIONAMOUNT - (
                    coalesce(
                        (select
                            sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                        from dbo.INSTALLMENTSPLITPAYMENT
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                        where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                            and FINANCIALTRANSACTION.DATE <= @ASOFDATE)
                    , 0)
                    + coalesce(
                        (select
                            sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
                        from dbo.INSTALLMENTSPLITWRITEOFF 
                            inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                        where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                            and WRITEOFF.DATE <= @ASOFDATE)
                    , 0)
                ) as TRANSACTIONBALANCE,
                INSTALLMENTSPLIT.INSTALLMENTID,
                INSTALLMENTSPLIT.DESIGNATIONID
            from dbo.INSTALLMENTSPLIT
            where INSTALLMENTSPLIT.PLEDGEID = @PLEDGEID
        )
        insert into @BALANCE (
            ID,
            TRANSACTIONBALANCE,
            BASEBALANCE,
            ORGANIZATIONBALANCE,
            DATE,
            DESIGNATIONID
        )
        select
            CTE_BALANCE.ID, 
            CTE_BALANCE.TRANSACTIONBALANCE,
            CTE_BALANCE.TRANSACTIONBALANCE,
            CTE_BALANCE.TRANSACTIONBALANCE,
            INSTALLMENT.DATE,
            CTE_BALANCE.DESIGNATIONID
        from CTE_BALANCE
            inner join dbo.INSTALLMENT on INSTALLMENT.ID = CTE_BALANCE.INSTALLMENTID
    end
    else
    begin

        declare @DESIGNATIONAMOUNTLIST table
        (
            DESIGNATIONID uniqueidentifier,
            SEQUENCE int,
            TRANSACTIONBALANCE money,
            BASEBALANCE money,
            ORGANIZATIONBALANCE money,
            TSLONG bigint
        );

        declare @INSTALLMENTAMOUNTLIST table
        (
            INSTALLMENTID uniqueidentifier,
            SEQUENCE int,
            TRANSACTIONBALANCE money,
            BASEBALANCE money,
            ORGANIZATIONBALANCE money,
            DATE datetime,
            TSLONG bigint
        );

        declare @INSTALLMENTMAX integer;    
        select @INSTALLMENTMAX = max(SEQUENCE)
        from dbo.INSTALLMENT where INSTALLMENT.REVENUEID = @PLEDGEID;

        --Finding the date of the max installment will let us be smart about how much math to do.

        declare @INSTALLMENTMAXDATE datetime
        select @INSTALLMENTMAXDATE = DATE
        from dbo.INSTALLMENT 
        where INSTALLMENT.REVENUEID = @PLEDGEID
            and INSTALLMENT.SEQUENCE = @INSTALLMENTMAX;        

        --If we have a start date and it is after the revenue's last installment, don't do anything.

        if @STARTDATE is not null and @INSTALLMENTMAXDATE < @STARTDATE
        begin
            return
        end
        else
        begin
            --If the last installment falls inside out date range, our math is more complicated, 

            --    so we need different values to work with

            if @ENDDATE is not null and @INSTALLMENTMAXDATE <= @ENDDATE
            begin
                --Get split information, including their total balances.

                insert into @DESIGNATIONAMOUNTLIST(DESIGNATIONID, SEQUENCE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, TSLONG) 
                select
                    REVENUESPLIT_EXT.DESIGNATIONID,
                    FINANCIALTRANSACTIONLINEITEM.SEQUENCE,
                    REVALUED.TRANSACTIONBALANCE,
                    REVALUED.BASEBALANCE,
                    REVALUED.ORGANIZATIONBALANCE,
                    FINANCIALTRANSACTIONLINEITEM.TSLONG
                from dbo.FINANCIALTRANSACTIONLINEITEM
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCES(
                        @PLEDGEID, 
                        @ASOFDATE, 
                        @USEORIGINALRATE
                    ) as REVALUED on REVALUED.ID = FINANCIALTRANSACTIONLINEITEM.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0;

                --Get info for all the installments on the given pledge.

                insert into @INSTALLMENTAMOUNTLIST(INSTALLMENTID, SEQUENCE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, DATE, TSLONG) 
                select
                    INSTALLMENT.ID,
                    INSTALLMENT.SEQUENCE,
                    REVALUED.TRANSACTIONBALANCE,
                    REVALUED.BASEBALANCE,
                    REVALUED.ORGANIZATIONBALANCE,
                    INSTALLMENT.DATE,
                    INSTALLMENT.TSLONG
                from dbo.INSTALLMENT
                    inner join dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES(
                        @PLEDGEID, 
                        @ASOFDATE, 
                        @USEORIGINALRATE
                    ) as REVALUED on REVALUED.ID = INSTALLMENT.ID
                where INSTALLMENT.REVENUEID = @PLEDGEID;    

            end
            else
            begin
                --If the date range doesn't include the last installment on the pledge...


                --Get split info, sans balances.

                insert into @DESIGNATIONAMOUNTLIST(DESIGNATIONID, SEQUENCE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, TSLONG) 
                select
                    REVENUESPLIT_EXT.DESIGNATIONID,
                    FINANCIALTRANSACTIONLINEITEM.SEQUENCE,
                    0,
                    0,
                    0,
                    FINANCIALTRANSACTIONLINEITEM.TSLONG
                from dbo.FINANCIALTRANSACTIONLINEITEM
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0;

                --Get installment info for up to the end of our date range.

                insert into @INSTALLMENTAMOUNTLIST(INSTALLMENTID, SEQUENCE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, DATE, TSLONG) 
                select
                    INSTALLMENT.ID,
                    INSTALLMENT.SEQUENCE,
                    REVALUED.TRANSACTIONBALANCE,
                    REVALUED.BASEBALANCE,
                    REVALUED.ORGANIZATIONBALANCE,
                    INSTALLMENT.DATE,
                    INSTALLMENT.TSLONG
                from dbo.INSTALLMENT
                    inner join dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES(
                        @PLEDGEID, 
                        @ASOFDATE, 
                        @USEORIGINALRATE
                    ) as REVALUED on REVALUED.ID = INSTALLMENT.ID
                where INSTALLMENT.REVENUEID = @PLEDGEID
                    and(INSTALLMENT.DATE <= @ENDDATE
                        or @ENDDATE is null
                    )
            end
        end

        --Join the installment and split lists to build out info on installment splits.

        declare @INSTALLMENTSPLIT table(
            INSTALLMENTID uniqueidentifier,
            INSTALLMENTSEQUENCE int,
            TRANSACTIONINSTALLMENTBALANCE money,
            BASEINSTALLMENTBALANCE money,
            ORGANIZATIONINSTALLMENTBALANCE money,
            INSTALLMENTSPLITID uniqueidentifier,
            TRANSACTIONBALANCE money,
            DATE datetime,
            DESIGNATIONID uniqueidentifier,
            DESIGNATIONSEQUENCE int,
            DESIGNATIONTSLONG bigint,
            BASEDESIGNATIONBALANCE money,
            ORGANIZATIONDESIGNATIONBALANCE money
        )        
        insert into @INSTALLMENTSPLIT    
        select 
            INSTALLMENTLIST.INSTALLMENTID,
            INSTALLMENTLIST.SEQUENCE INSTALLMENTSEQUENCE,
            INSTALLMENTLIST.TRANSACTIONBALANCE as TRANSACTIONINSTALLMENTBALANCE,
            INSTALLMENTLIST.BASEBALANCE as BASEINSTALLMENTBALANCE,
            INSTALLMENTLIST.ORGANIZATIONBALANCE as ORGANIZATIONINSTALLMENTBALANCE,
            INSTALLMENTSPLIT.ID as INSTALLMENTSPLITID,
            INSTALLMENTSPLIT.TRANSACTIONAMOUNT - (
                coalesce(
                    (select
                        sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                    from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                    where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        and FINANCIALTRANSACTION.DATE <= @ASOFDATE)
                , 0)
                + coalesce(
                    (select
                        sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
                    from dbo.INSTALLMENTSPLITWRITEOFF 
                        inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                    where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        and WRITEOFF.DATE <= @ASOFDATE)
                , 0)
            ) as TRANSACTIONBALANCE,
            INSTALLMENTLIST.DATE,
            DESIGNATIONLIST.DESIGNATIONID,
            DESIGNATIONLIST.SEQUENCE as DESIGNATIONSEQUENCE,
            DESIGNATIONLIST.TSLONG as DESIGNATIONTSLONG,
            DESIGNATIONLIST.BASEBALANCE as BASEDESIGNATIONBALANCE,
            DESIGNATIONLIST.ORGANIZATIONBALANCE as ORGANIZATIONDESIGNATIONBALANCE
        from @INSTALLMENTAMOUNTLIST INSTALLMENTLIST
            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTLIST.INSTALLMENTID = INSTALLMENTSPLIT.INSTALLMENTID
            inner join @DESIGNATIONAMOUNTLIST DESIGNATIONLIST on DESIGNATIONLIST.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID

        --For each included installment split, calculate rollups of all previous installments splits 

        --    that share the installment, when ordered by split.  We'll need rollups both including and excluding 

        --    the current installment splits.

        declare @INSTALLMENTROLLUP table(
            INSTALLMENTID uniqueidentifier,
            INSTALLMENTSEQUENCE bigint,
            TRANSACTIONINSTALLMENTBALANCE money,
            BASEINSTALLMENTBALANCE money,
            ORGANIZATIONINSTALLMENTBALANCE money,
            DESIGNATIONID uniqueidentifier,
            INSTALLMENTSPLITID uniqueidentifier,
            DATE datetime,
            TRANSACTIONBALANCE money,
            PREVIOUSTRANSACTIONRUNNINGTOTAL money,
            TRANSACTIONRUNNINGTOTAL money    
        )
        insert into @INSTALLMENTROLLUP
        select
            CURRENTINSTALLMENTSPLIT.INSTALLMENTID,
            CURRENTINSTALLMENTSPLIT.INSTALLMENTSEQUENCE,
            CURRENTINSTALLMENTSPLIT.TRANSACTIONINSTALLMENTBALANCE,
            CURRENTINSTALLMENTSPLIT.BASEINSTALLMENTBALANCE,
            CURRENTINSTALLMENTSPLIT.ORGANIZATIONINSTALLMENTBALANCE,
            CURRENTINSTALLMENTSPLIT.DESIGNATIONID,
            CURRENTINSTALLMENTSPLIT.INSTALLMENTSPLITID,
            CURRENTINSTALLMENTSPLIT.DATE,
            CURRENTINSTALLMENTSPLIT.TRANSACTIONBALANCE,
            coalesce(SUM(PREVIOUSINSTALLMENTSPLIT.TRANSACTIONBALANCE),0) 
                - CURRENTINSTALLMENTSPLIT.TRANSACTIONBALANCE PREVIOUSTRANSACTIONRUNNINGTOTAL,
            coalesce(SUM(PREVIOUSINSTALLMENTSPLIT.TRANSACTIONBALANCE),0) TRANSACTIONRUNNINGTOTAL        
        from @INSTALLMENTSPLIT CURRENTINSTALLMENTSPLIT
            cross join @INSTALLMENTSPLIT PREVIOUSINSTALLMENTSPLIT
        where PREVIOUSINSTALLMENTSPLIT.INSTALLMENTID = CURRENTINSTALLMENTSPLIT.INSTALLMENTID
            and(
                PREVIOUSINSTALLMENTSPLIT.DESIGNATIONSEQUENCE < CURRENTINSTALLMENTSPLIT.DESIGNATIONSEQUENCE
                or(
                    PREVIOUSINSTALLMENTSPLIT.DESIGNATIONSEQUENCE = CURRENTINSTALLMENTSPLIT.DESIGNATIONSEQUENCE
                    and PREVIOUSINSTALLMENTSPLIT.DESIGNATIONTSLONG <= CURRENTINSTALLMENTSPLIT.DESIGNATIONTSLONG
                )
            )
            and(@INSTALLMENTMAXDATE <= @ENDDATE
                or @ENDDATE is null
                or @STARTDATE is null
                or CURRENTINSTALLMENTSPLIT.DATE >=@STARTDATE
            )
        group by 
            CURRENTINSTALLMENTSPLIT.INSTALLMENTID,
            CURRENTINSTALLMENTSPLIT.INSTALLMENTSEQUENCE,
            CURRENTINSTALLMENTSPLIT.TRANSACTIONINSTALLMENTBALANCE,
            CURRENTINSTALLMENTSPLIT.BASEINSTALLMENTBALANCE,
            CURRENTINSTALLMENTSPLIT.ORGANIZATIONINSTALLMENTBALANCE,
            CURRENTINSTALLMENTSPLIT.DESIGNATIONID,
            CURRENTINSTALLMENTSPLIT.INSTALLMENTSPLITID,
            CURRENTINSTALLMENTSPLIT.DATE,
            CURRENTINSTALLMENTSPLIT.TRANSACTIONBALANCE

        --Calculate the balances on the installment splits

        declare @INTERMEDIATEBALANCE table(
            INSTALLMENTSPLITID uniqueidentifier,
            INSTALLMENTSEQUENCE bigint,
            DATE datetime,
            DESIGNATIONID uniqueidentifier,
            TRANSACTIONBALANCE money,
            BASEBALANCE money,
            ORGANIZATIONBALANCE money
        )
        insert into @INTERMEDIATEBALANCE
        --For all installments but the last, we convert by proportion the calculated rollups

        --    and take their difference to get the balance for each installment split.

        select
            INSTALLMENTSPLITID,
            INSTALLMENTROLLUP.INSTALLMENTSEQUENCE,
            INSTALLMENTROLLUP.DATE,
            INSTALLMENTROLLUP.DESIGNATIONID,
            INSTALLMENTROLLUP.TRANSACTIONBALANCE,
            CURRENTRUNNINGTOTAL.BASEAMOUNT - PREVIOUSRUNNINGTOTAL.BASEAMOUNT BASEBALANCE,
            CURRENTRUNNINGTOTAL.ORGANIZATIONAMOUNT - PREVIOUSRUNNINGTOTAL.ORGANIZATIONAMOUNT ORGANIZATIONBALANCE
        from @INSTALLMENTROLLUP INSTALLMENTROLLUP
            cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
                INSTALLMENTROLLUP.TRANSACTIONRUNNINGTOTAL,
                INSTALLMENTROLLUP.TRANSACTIONINSTALLMENTBALANCE,
                @BASECURRENCYID,
                INSTALLMENTROLLUP.BASEINSTALLMENTBALANCE,
                @BASEDECIMALDIGITS,
                @TRANSACTIONCURRENCYID,
                default,
                @ORGANIZATIONCURRENCYID,
                INSTALLMENTROLLUP.ORGANIZATIONINSTALLMENTBALANCE,
                @ORGANIZATIONDECIMALDIGITS,
                default
            ) CURRENTRUNNINGTOTAL
            cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
                INSTALLMENTROLLUP.PREVIOUSTRANSACTIONRUNNINGTOTAL,
                INSTALLMENTROLLUP.TRANSACTIONINSTALLMENTBALANCE,
                @BASECURRENCYID,
                INSTALLMENTROLLUP.BASEINSTALLMENTBALANCE,
                @BASEDECIMALDIGITS,
                @TRANSACTIONCURRENCYID,
                default,
                @ORGANIZATIONCURRENCYID,
                INSTALLMENTROLLUP.ORGANIZATIONINSTALLMENTBALANCE,
                @ORGANIZATIONDECIMALDIGITS,
                default
            ) PREVIOUSRUNNINGTOTAL
        where INSTALLMENTROLLUP.INSTALLMENTSEQUENCE < @INSTALLMENTMAX

        --For the installment that is the last on the pledge, include a row in the table, but don't calculate anything yet.

        union 
        select
            INSTALLMENTSPLITID,
            INSTALLMENTROLLUP.INSTALLMENTSEQUENCE,
            INSTALLMENTROLLUP.DATE,
            INSTALLMENTROLLUP.DESIGNATIONID,
            INSTALLMENTROLLUP.TRANSACTIONBALANCE,
            0 BASEBALANCE,
            0 ORGANIZATIONBALANCE
        from @INSTALLMENTROLLUP INSTALLMENTROLLUP
        where INSTALLMENTROLLUP.INSTALLMENTSEQUENCE = @INSTALLMENTMAX

        --Pump balances for all but the last installment into the output table.

        insert into @BALANCE(ID, DATE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, DESIGNATIONID)
        select
            INTERMEDIATEBALANCE.INSTALLMENTSPLITID,
            INTERMEDIATEBALANCE.DATE,
            INTERMEDIATEBALANCE.TRANSACTIONBALANCE,
            INTERMEDIATEBALANCE.BASEBALANCE,
            INTERMEDIATEBALANCE.ORGANIZATIONBALANCE,
            INTERMEDIATEBALANCE.DESIGNATIONID
        from @INTERMEDIATEBALANCE INTERMEDIATEBALANCE
        where  INTERMEDIATEBALANCE.INSTALLMENTSEQUENCE < @INSTALLMENTMAX

        --If our date range includes the last installment...

        if @ENDDATE is not null and @INSTALLMENTMAXDATE <= @ENDDATE
        begin
            --For each split, calculate the total converted balances of the installment splits within it.  Then,

            --    subtract that from the split total balance to get the balances for the installment splits of

            --    the last installment

            with CTE_SPLITROLLUP
            as(
                select
                    INTERMEDIATEBALANCE.DESIGNATIONID,
                    sum(INTERMEDIATEBALANCE.BASEBALANCE) BASEROLLUP,
                    sum(INTERMEDIATEBALANCE.ORGANIZATIONBALANCE) ORGANIZATIONROLLUP
                from @INTERMEDIATEBALANCE INTERMEDIATEBALANCE
                group by INTERMEDIATEBALANCE.DESIGNATIONID
            )
            insert into @BALANCE(ID, DATE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, DESIGNATIONID)
            select
                INSTALLMENTSPLIT.INSTALLMENTSPLITID,
                INSTALLMENTSPLIT.DATE,
                INSTALLMENTSPLIT.TRANSACTIONBALANCE,        
                INSTALLMENTSPLIT.BASEDESIGNATIONBALANCE - CTE_SPLITROLLUP.BASEROLLUP,
                INSTALLMENTSPLIT.ORGANIZATIONDESIGNATIONBALANCE - CTE_SPLITROLLUP.ORGANIZATIONROLLUP,
                INSTALLMENTSPLIT.DESIGNATIONID
            from @INSTALLMENTSPLIT INSTALLMENTSPLIT
                inner join CTE_SPLITROLLUP on CTE_SPLITROLLUP.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
            where INSTALLMENTSPLIT.INSTALLMENTSEQUENCE = @INSTALLMENTMAX
        end
    end

    return

end