con_collection_calc.lwm 2.43 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t1.*
                FROM
                    (SELECT DISTINCT
                        cci.contract_lease_item_id,
                        cc.contract_id,
                        NVL(cci.price, 0) sin_car_finance_amount,
                        NVL(cc.finance_amount, 0) con_finance_amount,
                        NVL(cc.deposit, 0) deposit, --保证金
                        (SELECT
                            SUM(NVL(cf.principal, 0)) - SUM(NVL(cf.received_principal, 0))
                        FROM
                            con_contract_cashflow cf
                        WHERE
                            cf.contract_id  = cc.contract_id AND
                            cf.cf_item      = 1 AND
                            cf.cf_direction = 'INFLOW'
                        ) over_principal, -- 剩余本金
                        (SELECT
                            SUM(NVL(cf.due_amount, 0)) - SUM(NVL(cf.received_amount, 0))
                        FROM
                            con_contract_cashflow cf
                        WHERE
                            cf.contract_id  = cc.contract_id AND
                            cf.cf_item      = 1 AND
                            cf.cf_direction = 'INFLOW'
                        ) over_due_amount, -- 剩余租金
                        (SELECT
                            NVL(SUM(NVL(cf.due_amount, 0)), 0)
                        FROM
                            con_contract_cashflow cf
                        WHERE
                            cf.contract_id  = cc.contract_id AND
                            cf.cf_item      = 9 AND
                            cf.cf_direction = 'INFLOW'
                        ) interest -- 支付表罚息
                    FROM
                        con_contract_lease_item cci,
                        con_contract cc,
                        con_contract_cashflow ccc
                    WHERE
                        cci.contract_id = cc.contract_id AND
                        cc.contract_id  = ccc.contract_id and 
                        cci.contract_lease_item_id = ${@contract_lease_item_id}
                    ) t1
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>