<?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
                    *
                FROM
                    (SELECT
                        cc.created_date,
                        cc.document_type,
                        cc.document_type_desc,
                        cc.staff_name,
                        cc.staff_id,
                        cc.contract_number,
                        (SELECT
                            COUNT(*)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id     = cc.contract_id AND
                            ccc.write_off_flag <> 'FULL' AND
                            ccc.cf_item         = 1 AND
                            ccc.cf_direction    = 'INFLOW' AND
                            ccc.due_date       <= sysdate
                        ) overdue_times,
                        (SELECT
                            NVL(SUM(ccc.due_amount), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            cc.contract_id                   = ccc.contract_id AND
                            ccc.cf_direction                 = 'INFLOW' AND
                            ccc.cf_item                      = 1 AND
                            TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                        ) due_amount,
                        (SELECT
                            NVL(SUM(ccc.due_amount), 0) - NVL(SUM(ccc.received_amount), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            cc.contract_id   = ccc.contract_id AND
                            ccc.cf_direction = 'INFLOW' AND
                            ccc.cf_item      = 1 AND
                            ccc.due_date    <= sysdate
                        ) overdue_amount,
                        ROUND((
                                CASE
                                    WHEN
                                        (
                                            (SELECT
                                                    NVL(SUM(ccc.due_amount), 0)
                                                FROM
                                                    con_contract_cashflow ccc
                                                WHERE
                                                    cc.contract_id                   = ccc.contract_id AND
                                                    ccc.cf_direction                 = 'INFLOW' AND
                                                    ccc.cf_item                      = 1 AND
                                                    TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                                            )
                                            +
                                            (SELECT
                                                NVL(SUM(ccc.due_amount), 0) - NVL(SUM(ccc.received_amount), 0)
                                            FROM
                                                con_contract_cashflow ccc
                                            WHERE
                                                cc.contract_id   = ccc.contract_id AND
                                                ccc.cf_direction = 'INFLOW' AND
                                                ccc.cf_item      = 1 AND
                                                ccc.due_date    <= sysdate
                                            )
                                        )
                                        <> 0
                                    THEN (
                                        (SELECT
                                            NVL(SUM(ccc.due_amount), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id                   = ccc.contract_id AND
                                            ccc.cf_direction                 = 'INFLOW' AND
                                            ccc.cf_item                      = 1 AND
                                            TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                                        ) / (
                                        (SELECT
                                            NVL(SUM(ccc.due_amount), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id                   = ccc.contract_id AND
                                            ccc.cf_direction                 = 'INFLOW' AND
                                            ccc.cf_item                      = 1 AND
                                            TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                                        ) +
                                        (SELECT
                                            NVL(SUM(ccc.due_amount), 0) - NVL(SUM(ccc.received_amount), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id   = ccc.contract_id AND
                                            ccc.cf_direction = 'INFLOW' AND
                                            ccc.cf_item      = 1 AND
                                            ccc.due_date    <= sysdate
                                        ) ))
                                    ELSE 0
                                END ), 4) * 100
                        || '%' collection_rate,
                        ROUND((
                                CASE
                                    WHEN cc.pmt <> 0
                                    THEN
                                        (SELECT
                                            NVL(SUM(ccc.due_amount), 0) - NVL(SUM(ccc.received_amount), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id   = ccc.contract_id AND
                                            ccc.cf_direction = 'INFLOW' AND
                                            ccc.cf_item      = 1 AND
                                            ccc.due_date    <= sysdate
                                        ) / cc.pmt
                                    ELSE 0
                                END ), 2) overdue_number,
                        to_char(ROUND((ROUND((
                                CASE
                                    WHEN cc.pmt <> 0
                                    THEN
                                        (SELECT
                                            NVL(SUM(ccc.due_amount), 0) - NVL(SUM(ccc.received_amount), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id   = ccc.contract_id AND
                                            ccc.cf_direction = 'INFLOW' AND
                                            ccc.cf_item      = 1 AND
                                            ccc.due_date    <= sysdate
                                        ) / cc.pmt
                                    ELSE 0
                                END ), 2) /
                        (SELECT
                            COUNT(1)
                        FROM
                            con_collection_v
                        ) ), 4) * 100,'99,999,990.00')
                        || '%' overdue_rate,
                        ROUND((
                                CASE
                                    WHEN cc.lease_item_amount <> 0
                                    THEN (
                                        (SELECT
                                            NVL(SUM(ccc.principal), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id                   = ccc.contract_id AND
                                            ccc.cf_direction                 = 'INFLOW' AND
                                            ccc.cf_item                      = 1 AND
                                            TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                                        ) +
                                        (SELECT
                                            NVL(SUM(ccc.due_amount), 0) - NVL(SUM(ccc.received_amount), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id   = ccc.contract_id AND
                                            ccc.cf_direction = 'INFLOW' AND
                                            ccc.cf_item      = 1 AND
                                            ccc.due_date    <= sysdate
                                        ) +
                                        (SELECT
                                            NVL(SUM(ccc.due_amount), 0)
                                        FROM
                                            con_contract_cashflow ccc
                                        WHERE
                                            cc.contract_id                   = ccc.contract_id AND
                                            ccc.cf_item                      = 2 AND
                                            TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                                        ) ) /cc.lease_item_amount
                                    ELSE 0
                                END),4)* 100
                        || '%' bad_amount_rate,
                        (
                        (SELECT
                            NVL(SUM(ccc.principal), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            cc.contract_id                   = ccc.contract_id AND
                            ccc.cf_direction                 = 'INFLOW' AND
                            ccc.cf_item                      = 1 AND
                            TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                        ) +
                        (SELECT
                            NVL(SUM(ccc.due_amount), 0) - NVL(SUM(ccc.received_amount), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            cc.contract_id   = ccc.contract_id AND
                            ccc.cf_direction = 'INFLOW' AND
                            ccc.cf_item      = 1 AND
                            ccc.due_date    <= sysdate
                        ) +
                        (SELECT
                            NVL(SUM(ccc.due_amount), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            cc.contract_id                   = ccc.contract_id AND
                            ccc.cf_item                      = 2 AND
                            TO_CHAR(ccc.due_date, 'YYYY-MM') > TO_CHAR(sysdate, 'YYYY-MM')
                        ) ) bad_amount
                    FROM
                        con_collection_v cc
                    ) t #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_number"/>
        <bm:field name="created_date" datatype="java.util.Date"/>
        <bm:field name="document_type_desc"/>
        <bm:field name="staff_name"/>
        <bm:field name="staff_id"/>
        <bm:field name="overdue_times"/>
        <bm:field name="due_amount"/>
        <bm:field name="overdue_amount"/>
        <bm:field name="collection_rate"/>
        <bm:field name="overdue_number"/>
        <bm:field name="overdue_rate"/>
        <bm:field name="bad_amount_rate"/>
        <bm:field name="bad_amount"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="document_type" queryExpression="t.document_type = ${@document_type}"/>
        <bm:query-field name="staff_name" queryExpression="t.staff_name like ${@staff_name}"/>
        <bm:query-field name="created_date_from" queryExpression="t.created_date &gt;= to_date(${@created_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="created_date_to" queryExpression="t.created_date &lt;= to_date(${@created_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
