USP_DATALIST_RECURRINGGIFTINSTALLMENTHISTORY

CREATE procedure dbo.USP_DATALIST_RECURRINGGIFTINSTALLMENTHISTORY(
    @ID uniqueidentifier,
    @PAYMENTSWRITEOFFSONLY bit = 0
)
as
    set nocount on;

    declare @HISTORY table (ID uniqueidentifier,
                            PARENTID uniqueidentifier,
                            ACTIVITYTYPE nvarchar(11),
                            AMOUNT money,
                            BALANCE money,
                            DATE date,
                            PAYMENTID uniqueidentifier,
                            WRITEOFFID uniqueidentifier,
                            TRANSACTIONCURRENCYID uniqueidentifier,
              CHILDORPARENTID uniqueidentifier)

    insert into @HISTORY
    -- existing installments

    select ID,
           null PARENTID,
           'Installment',
           TRANSACTIONAMOUNT,
           dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID),
           DATE,
           null,
           null,
           TRANSACTIONCURRENCYID,
       ID as CHILDORPARENTID
    from dbo.RECURRINGGIFTINSTALLMENT
    where REVENUEID = @ID
    and @PAYMENTSWRITEOFFSONLY = 0
    union all
    -- payments

    select null,
           RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID,
           'Payment',
           RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT,
           null,
           REVENUE.DATE,
           REVENUE.ID,
           null,
           RECURRINGGIFTINSTALLMENTPAYMENT.APPLICATIONCURRENCYID,
       RECURRINGGIFTINSTALLMENTPAYMENT.ID as CHILDORPARENTID
    from dbo.RECURRINGGIFTINSTALLMENTPAYMENT
    inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
    left join dbo.REVENUE on REVENUE.ID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
    where RECURRINGGIFTINSTALLMENT.REVENUEID = @ID
    union all
    -- writeoffs

    select null,
           RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID,
           'Write-off',
           RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONAMOUNT,
           null,
           RECURRINGGIFTWRITEOFF.DATE,
           null,
           RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID,
           RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONCURRENCYID,
       RECURRINGGIFTINSTALLMENTWRITEOFF.ID as CHILDORPARENTID
    from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF
    inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID
    inner join dbo.RECURRINGGIFTWRITEOFF on RECURRINGGIFTWRITEOFF.ID = RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID
    where RECURRINGGIFTINSTALLMENT.REVENUEID = @ID;

    -- add missing installments

    if @PAYMENTSWRITEOFFSONLY = 0
    begin
        insert into @HISTORY (ACTIVITYTYPE, AMOUNT, BALANCE, DATE, TRANSACTIONCURRENCYID)
        select 'Installment', R.TRANSACTIONAMOUNT, R.TRANSACTIONAMOUNT, I.DATE, R.TRANSACTIONCURRENCYID
        from REVENUE R
        cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS(R.ID) I
        where R.ID = @ID;

        select ID, PARENTID, ACTIVITYTYPE, AMOUNT, BALANCE, DATE, PAYMENTID, TRANSACTIONCURRENCYID, CHILDORPARENTID
        from @HISTORY
        order by DATE;
    end
    else
        select null, null, min(ACTIVITYTYPE), sum(AMOUNT), null, min(DATE), PAYMENTID, TRANSACTIONCURRENCYID, CHILDORPARENTID
        from @HISTORY
        group by PAYMENTID, WRITEOFFID, TRANSACTIONCURRENCYID, CHILDORPARENTID
        order by min(DATE);