<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from (
                SELECT 1 contract_id, NULL contract_name, 'calc_date' column_name,
                '日期' column_prompt,
                'center' column_align,
                100 column_width,
                'Leaf.formatDate' column_renderer,
                'true' column_lock,
                1 order_seq FROM dual
                UNION
                SELECT
                    cc.contract_id,
                    cc.contract_name,
                    'contract_number_'
                    || cc.contract_id AS column_name,
                    '合同编号' column_prompt,
                    'left' column_align,
                    130 column_width,
                    null column_renderer,
                    'false' column_lock,
                    2 order_seq
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                    EXISTS
                    (SELECT
                        1
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type  = 'PAYMENT_DEBT' AND
                        cwo.cf_item         = 0 AND
                        cwo.reversed_flag   = 'N' AND
                        cwo.contract_id     = cc.contract_id AND
                        cwo.write_off_date <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                    )
                UNION
                SELECT
                    cc.contract_id,
                    cc.contract_name,
                    'remain_pricipal_'
                    || cc.contract_id AS column_name,
                    '本金余额' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    3 order_seq
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                    EXISTS
                    (SELECT
                        1
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type  = 'PAYMENT_DEBT' AND
                        cwo.cf_item         = 0 AND
                        cwo.reversed_flag   = 'N' AND
                        cwo.contract_id     = cc.contract_id AND
                        cwo.write_off_date <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                    )
                UNION
                SELECT
                    cc.contract_id,
                    cc.contract_name,
                    'sum_principal_'
                    || cc.contract_id AS column_name,
                    '投放本金' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    4 order_seq 
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                    EXISTS
                    (SELECT
                        1
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type  = 'PAYMENT_DEBT' AND
                        cwo.cf_item         = 0 AND
                        cwo.reversed_flag   = 'N' AND
                        cwo.contract_id     = cc.contract_id AND
                        cwo.write_off_date <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                    )
                UNION
                SELECT
                    cc.contract_id,
                    cc.contract_name,
                    'receive_principal_'
                    || cc.contract_id AS column_name,
                    '偿还本金' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    5 order_seq
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                    EXISTS
                    (SELECT
                        1
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type  = 'PAYMENT_DEBT' AND
                        cwo.cf_item         = 0 AND
                        cwo.reversed_flag   = 'N' AND
                        cwo.contract_id     = cc.contract_id AND
                        cwo.write_off_date <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                    )
                UNION
                SELECT
                    cc.contract_id,
                    cc.contract_name,
                    'receive_interest_'
                    || cc.contract_id AS column_name,
                    '利息流入' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    6 order_seq
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                    EXISTS
                    (SELECT
                        1
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type  = 'PAYMENT_DEBT' AND
                        cwo.cf_item         = 0 AND
                        cwo.reversed_flag   = 'N' AND
                        cwo.contract_id     = cc.contract_id AND
                        cwo.write_off_date <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                    )
                UNION
                SELECT
                    cc.contract_id,
                    cc.contract_name,
                    'other_fee_'
                    || cc.contract_id AS column_name,
                    '其他费用流入合计' column_prompt,
                    'right' column_align,
                    120 column_width,
                    'Leaf.formatMoney' column_renderer,
                    'false' column_lock,
                    7 order_seq
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                    EXISTS
                    (SELECT
                        1
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type  = 'PAYMENT_DEBT' AND
                        cwo.cf_item         = 0 AND
                        cwo.reversed_flag   = 'N' AND
                        cwo.contract_id     = cc.contract_id AND
                        cwo.write_off_date <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                    )
                 UNION
                SELECT
                    cc.contract_id,
                    cc.contract_name,
                    'int_rate_'
                    || cc.contract_id AS column_name,
                    '执行利率(%)' column_prompt,
                    'right' column_align,
                    100 column_width,
                    null column_renderer,
                    'false' column_lock,
                    8 order_seq
                FROM
                    con_contract cc
                WHERE
                    cc.data_class                      = 'NORMAL' AND
                    NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                    EXISTS
                    (SELECT
                        1
                    FROM
                        csh_write_off cwo
                    WHERE
                        cwo.write_off_type  = 'PAYMENT_DEBT' AND
                        cwo.cf_item         = 0 AND
                        cwo.reversed_flag   = 'N' AND
                        cwo.contract_id     = cc.contract_id AND
                        cwo.write_off_date <= to_date(${/parameter/@calc_end_date}, 'YYYY-MM-DD')
                    )
                    ) t1
                    order by t1.contract_id,order_seq
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
