<?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
                        cc.contract_id,
                        cc.project_id,
                        cc.calc_session_id,
                        cc.document_category,
                        pp.ref_project_id,
                        cc.contract_number,
                        cc.business_type,
                        cc.hn_industry_classification, --行业分类
                        (select v.code_value_name from sys_code_values_v v where v.code = 'HN_INDUSTRY_CLASSIFICATION' 
                        and v.code_value = cc.hn_industry_classification) hn_industry_classification_n,
                        cc.inception_of_lease,
                        (SELECT
                            hbt.description
                        FROM
                            hls_business_type hbt
                        WHERE
                            hbt.business_type = cc.business_type
                        ) business_type_n,
                        hn_report_pkg.get_tre_factoring_con_times(p_contract_id => cc.contract_id) factoring_contract_info,
                        pp.project_name,
                        cc.bp_id_tenant,
                        hbm.bp_name
                        ||
                        (SELECT
                            '、'
                            || ccb.bp_name
                        FROM
                            con_contract_bp ccb
                        WHERE
                            ccb.contract_id = cc.contract_id AND
                            ccb.bp_category = 'TENANT' AND
                            ccb.bp_type     = 'TENANT_SEC'
                        ) bp_name,
                        pp.binary_classification,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code      IN ('WITHIN_GROUP', 'MARKETIZATION') AND
                            v.code_value = pp.binary_classification
                        ) binary_classification_n,
                        sdic_report_pkg.get_contract_bp_name(p_contract_id => cc.contract_id, p_bp_category => 'VENDER', p_bp_type => 'VENDER') vender_bp_name,
                        hbm.bp_name bp_name_tenant,
                        sdic_report_pkg.get_contract_bp_name(p_contract_id => cc.contract_id, p_bp_category => 'GUARANTOR', p_bp_type => '') guarantor_bp_name,
                        (SELECT
                            NVL(SUM(ccm.total_value), 0)
                        FROM
                            con_contract_mortgage ccm
                        WHERE
                            ccm.contract_id = cc.contract_id
                        ) mortgage_value,
                        cc.lease_form,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'LEASE_FORM' AND
                            v.code_value = cc.lease_form
                        ) lease_form_n,
                        cc.lease_term,
                        TO_CHAR(cc.lease_term,'FM990.00')
                        || '年' lease_term_n,
                        (
                            CASE
                                WHEN
                                    (
                                        add_months(cc.inception_of_lease, cc.lease_times * (12 / cc.annual_pay_times)) - TRUNC(sysdate)
                                    )
                                    > 0
                                THEN ROUND((add_months(cc.inception_of_lease, cc.lease_times * (12 / cc.annual_pay_times)) - TRUNC(sysdate)) / 365, 2)
                                ELSE 0
                            END) remain_lease_term,
                        (
                            CASE
                                WHEN
                                    (
                                        add_months(cc.inception_of_lease, cc.lease_times * (12 / cc.annual_pay_times)) - TRUNC(sysdate)
                                    )
                                    > 0
                                THEN TO_CHAR(ROUND((add_months(cc.inception_of_lease, cc.lease_times * (12 / cc.annual_pay_times)) - TRUNC(sysdate)) / 365, 2), 'FM990.00')
                                    || '年'
                                ELSE '0'
                            END) remain_lease_term_n,
                        ROUND(
                        (SELECT
                            SUM(NVL(ccc.due_amount, 0))
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_item      = 1 AND
                            ccc.cf_type      = 1 AND
                            ccc.cf_direction = 'INFLOW' AND
                            ccc.cf_status    = 'RELEASE'
                        ) / 100000000, 2) sum_due_amount,
                        (SELECT
                            SUM(NVL(ccc.due_amount, 0))
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_item      = 1 AND
                            ccc.cf_type      = 1 AND
                            ccc.cf_direction = 'INFLOW' AND
                            ccc.cf_status    = 'RELEASE'
                        ) -
                        (SELECT
                            NVL(SUM(NVL(ccc.received_amount, 0)), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id                 = cc.contract_id AND
                            ccc.cf_item                     = 1 AND
                            ccc.cf_type                     = 1 AND
                            ccc.cf_direction                = 'INFLOW' AND
                            ccc.cf_status                   = 'RELEASE' AND
                            NVL(ccc.write_off_flag, 'NOT') <> 'NOT' AND
                            (
                                to_date(${@date_to},'yyyy-mm-dd') IS NULL OR
                                ccc.due_date                      <= to_date(${@date_to},'yyyy-mm-dd')
                            )
                        ) unreceived_amount,
                        (SELECT
                            SUM(NVL(ccc.due_amount, 0))
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_direction = 'INFLOW' AND
                            ccc.cf_type     <> 5 AND
                            ccc.cf_status    = 'RELEASE'
                        ) sum_accounts_receivable,
                        (SELECT
                            SUM(NVL(ccc.due_amount, 0))
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_direction = 'INFLOW' AND
                            ccc.cf_type     <> 5 AND
                            ccc.cf_status    = 'RELEASE'
                        ) -
                        (SELECT
                            NVL(SUM(NVL(ccc.received_amount, 0)), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id                 = cc.contract_id AND
                            ccc.cf_direction                = 'INFLOW' AND
                            ccc.cf_type                    <> 5 AND
                            ccc.cf_status                   = 'RELEASE' AND
                            NVL(ccc.write_off_flag, 'NOT') <> 'NOT' AND
                            (
                                to_date(${@date_to},'yyyy-mm-dd') IS NULL OR
                                ccc.due_date                      <= to_date(${@date_to},'yyyy-mm-dd')
                            )
                        ) unreceived_accounts,
                        cc.lease_item_amount,
                        cc.lease_item_amount -
                        (SELECT
                            NVL(SUM(NVL(ccc.received_principal, 0)), 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id                 = cc.contract_id AND
                            ccc.cf_item                     = 1 AND
                            ccc.cf_type                     = 1 AND
                            ccc.cf_direction                = 'INFLOW' AND
                            ccc.cf_status                   = 'RELEASE' AND
                            NVL(ccc.write_off_flag, 'NOT') <> 'NOT' AND
                            (
                                to_date(${@date_to},'yyyy-mm-dd') IS NULL OR
                                ccc.due_date                      <= to_date(${@date_to},'yyyy-mm-dd')
                            )
                        ) unreceived_principal,
                        cc.annual_pay_times,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HLS500_ANNUAL_PAY_TIMES' AND
                            v.code_value = cc.annual_pay_times
                        ) annual_pay_times_n,
                        /*add_months(cc.inception_of_lease, cc.lease_times * (12 / cc.annual_pay_times)) lease_end_date*/
                        (
                        SELECT
                            MAX(ccc.due_date)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_item      = 1 AND
                            ccc.cf_direction = 'INFLOW'
                        ) lease_end_date,
                        TO_CHAR(ROUND(months_between(
                        (SELECT
                            MAX(ccc.due_date)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_item      = 1 AND
                            ccc.cf_direction = 'INFLOW'
                        ),cc.inception_of_lease) / 12,2), 'FM990.00')
                        || '年' lease_term_2_n,
                       (
                            CASE
                                when nvl(to_date(${@date_to},'yyyy-mm-dd'),trunc(sysdate)) < cc.inception_of_lease
                                then
                                 TO_CHAR(ROUND(months_between(
                        (SELECT
                            MAX(ccc.due_date)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_item      = 1 AND
                            ccc.cf_direction = 'INFLOW'
                        ),cc.inception_of_lease) / 12,2), 'FM990.00')
                        || '年' 
                                WHEN
                                    (
                                        (SELECT
                                                MAX(ccc.due_date)
                                            FROM
                                                con_contract_cashflow ccc
                                            WHERE
                                                ccc.contract_id  = cc.contract_id AND
                                                ccc.cf_item      = 1 AND
                                                ccc.cf_direction = 'INFLOW'
                                        )
                                        - nvl(to_date(${@date_to},'yyyy-mm-dd'),trunc(sysdate))
                                    )
                                    > 0 
                                THEN 
                                  TO_CHAR(ROUND(months_between(
                        (SELECT
                            MAX(ccc.due_date)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id  = cc.contract_id AND
                            ccc.cf_item      = 1 AND
                            ccc.cf_direction = 'INFLOW'
                        ),nvl(to_date(${@date_to},'yyyy-mm-dd'),trunc(sysdate))) / 12,2), 'FM990.00')
                         || '年'
                                ELSE '0'
                            END) remain_lease_term_2_n
                    FROM
                        con_contract cc,
                        prj_project pp,
                        hls_bp_master hbm
                    WHERE
                        cc.data_class                      = 'NORMAL' AND
                        NVL(cc.hn_adjust_review_flag, 'N') = 'Y' AND
                        cc.contract_status                IN ('INCEPT', 'SIGN','PENDING','TERMINATE') AND
                        cc.contract_id                    IN
                        (SELECT
                            t.contract_id
                        FROM
                            csh_write_off t
                        WHERE
                            t.write_off_type          = 'PAYMENT_DEBT' AND
                            NVL(t.reversed_flag, 'N') = 'N' AND
                            t.times                   = 0 AND
                            t.cf_type                 = 0 AND
                            t.cf_item                 = 0
                        ) AND
                        cc.project_id   = pp.project_id AND
                        cc.bp_id_tenant = hbm.bp_id
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.contract_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
        <bm:query-field name="project_name" queryExpression="t1.project_name like &apos;%&apos;||${@project_name}||&apos;%&apos;"/>
        <bm:query-field name="business_type_n" queryExpression="t1.business_type=${@business_type}"/>
        <bm:query-field name="vender_bp_name" queryExpression="t1.vender_bp_name like &apos;%&apos;||${@vender_bp_name}||&apos;%&apos;"/>
        <bm:query-field name="bp_name_tenant" queryExpression="t1.bp_name_tenant like &apos;%&apos;||${@bp_name_tenant}||&apos;%&apos;"/>
        <bm:query-field name="guarantor_bp_name" queryExpression="t1.guarantor_bp_name like &apos;%&apos;||${@guarantor_bp_name}||&apos;%&apos;"/>
        <bm:query-field name="binary_classification_n" queryExpression="t1.binary_classification=${@binary_classification}"/>
        <bm:query-field name="lease_term_from" queryExpression="t1.lease_term &gt;=${@lease_term_from}"/>
        <bm:query-field name="lease_term_to" queryExpression="t1.lease_term &lt;=${@lease_term_to}"/>
        <bm:query-field name="remain_lease_term_from" queryExpression="t1.remain_lease_term &gt;=${@remain_lease_term_from}"/>
        <bm:query-field name="remain_lease_term_to" queryExpression="t1.remain_lease_term &lt;=${@remain_lease_term_to}"/>
        <bm:query-field name="sum_due_amount_from" queryExpression="t1.sum_due_amount &gt;=${@sum_due_amount_from}"/>
        <bm:query-field name="sum_due_amount_to" queryExpression="t1.sum_due_amount &lt;=${@sum_due_amount_to}"/>
        <bm:query-field name="unreceived_amount_from" queryExpression="t1.unreceived_amount &gt;=${@unreceived_amount_from}"/>
        <bm:query-field name="unreceived_amount_to" queryExpression="t1.unreceived_amount &lt;=${@unreceived_amount_to}"/>
        <bm:query-field name="lease_item_amount_from" queryExpression="t1.lease_item_amount &gt;=${@lease_item_amount_from}"/>
        <bm:query-field name="lease_item_amount_to" queryExpression="t1.lease_item_amount &lt;=${@lease_item_amount_to}"/>
        <bm:query-field name="unreceived_principal_from" queryExpression="t1.unreceived_principal &gt;=${@unreceived_principal_from}"/>
        <bm:query-field name="unreceived_principal_to" queryExpression="t1.unreceived_principal &lt;=${@unreceived_principal_to}"/>
        <bm:query-field name="inception_of_lease_from" queryExpression="t1.inception_of_lease &gt;=to_date(${@inception_of_lease_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="inception_of_lease_to" queryExpression="t1.inception_of_lease &lt;=to_date(${@inception_of_lease_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="lease_end_date_from" queryExpression="t1.lease_end_date &gt;=to_date(${@lease_end_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="lease_end_date_to" queryExpression="t1.lease_end_date &lt;=to_date(${@lease_end_date_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="annual_pay_times_n" queryExpression="t1.annual_pay_times=${@annual_pay_times}"/>
        <bm:query-field name="project_id" queryExpression="t1.project_id = ${@project_id}"/>
        <bm:query-field name="hn_industry_classification" queryExpression="t1.hn_industry_classification = ${@hn_industry_classification}"/>
    </bm:query-fields>
</bm:model>
