<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="loan_con_bp_n"/>
        <bm:field name="loan_contract_number"/>
        <bm:field name="loan_contract_name"/>
        <bm:field name="signing_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="loan_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="loan_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="withdraw_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="repayment_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="repayment_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="remaining_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="interest_rate"/>
        <bm:field name="base_rate"/>
        <bm:field name="int_rate_fixing_range"/>
        <bm:field name="int_rate_fixing_way"/>
        <bm:field name="change_money_rate"/>
        <bm:field name="nominal_price" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from (
                SELECT t1.loan_contract_id,
       t1.loan_contract_status,
       (SELECT sc.code_value_name
          FROM sys_code_values_v sc
         WHERE sc.code = 'TRE_LOAN_CONTRACT_STATUS'
           AND sc.code_value = t1.loan_contract_status) loan_contract_status_n,
       nvl(t1.loan_con_wfl_status,
           'WFL_NEW') loan_con_wfl_status,
       (SELECT c.code_value_name
          FROM sys_code_values_v c
         WHERE c.code_value = nvl(t1.loan_con_wfl_status,
                                  'WFL_NEW')
           AND c.code = 'TRE_LOAN_CON_WFL_STATUS') loan_con_wfl_status_n, --借款合同审批状态
       t1.loan_con_bp,
       (SELECT bp.bp_name
          FROM hls_bp_master bp
         WHERE bp.bp_id = t1.loan_con_bp) loan_con_bp_n, --借款机构
       t1.loan_contract_number, --约定编号
       t1.Loan_Contract_Name,--金融机构合同编号
       t1.signing_date, --约定日期
       t1.loan_date_to loan_date, --结束如期
       t1.loan_amount, --借款金额
       NVL((SELECT SUM(nvl(t2.principal,
                       0))
          FROM tre_loan_con_repayment_plan_lv  t2,
               tre_loan_con_withdrawal_plan tc
         WHERE t2.widthdrawal_plan_id = tc.widthdrawal_plan_id
           AND tc.loan_contract_id = t1.loan_contract_id
           AND t2.repayment_type = 'RENTAL'
           AND tc.widthdrawal_wfl_status = 'CONFIRM'),0) withdraw_amount, --取款金额   就是提款金额（仅含租金的本金）
       NVL((SELECT SUM(nvl(t2.principal,
                       0))
          FROM tre_loan_con_repayment_plan_lv  t2,
               tre_loan_con_withdrawal_plan tc
         WHERE t2.widthdrawal_plan_id = tc.widthdrawal_plan_id
           AND tc.loan_contract_id = t1.loan_contract_id
           AND t2.repayment_type = 'RENTAL'
           AND t2.maturity_date <= to_date(${@base_date},'YYYY-MM-DD')
           AND tc.widthdrawal_wfl_status = 'CONFIRM'),0) repayment_amount, --还款金额   就是按照还款计划当前时点还款的本金（按照导入的还款计划，当前时点落在哪一期，取之前期数的应还本金之和）
       NVL((SELECT SUM(nvl(t2.interest,
                       0))
          FROM tre_loan_con_repayment_plan_lv  t2,
               tre_loan_con_withdrawal_plan tc
         WHERE t2.widthdrawal_plan_id = tc.widthdrawal_plan_id
           AND tc.loan_contract_id = t1.loan_contract_id
           AND t2.repayment_type = 'RENTAL'
           AND t2.maturity_date <= to_date(${@base_date},'YYYY-MM-DD')
           AND tc.widthdrawal_wfl_status = 'CONFIRM'),0)repayment_interest,--还款利息   取之前期数的应还利息之和
       NVL((SELECT SUM(nvl(t2.principal,
                       0))
          FROM tre_loan_con_repayment_plan_lv  t2,
               tre_loan_con_withdrawal_plan tc
         WHERE t2.widthdrawal_plan_id = tc.widthdrawal_plan_id
           AND tc.loan_contract_id = t1.loan_contract_id
           AND t2.repayment_type = 'RENTAL'
           AND t2.maturity_date > to_date(${@base_date},'YYYY-MM-DD')
           AND tc.widthdrawal_wfl_status = 'CONFIRM'),0) remaining_amount, --取款余额   用取款金额减去取款余额
       t1.interest_rate, --执行利率
       round(t1.change_money_rate*100,4) change_money_rate,--手续费率
       t1.nominal_price, --名义货价
       round(t1.base_rate*100,4) base_rate,
       round(t1.int_rate_fixing_range*100,4) int_rate_fixing_range,
       (SELECT v.code_value_name AS value_name
          FROM sys_code_values_v v
         WHERE v.code = 'TRE502_INT_RATE_FIXING_WAY'
           AND v.code_enabled_flag = 'Y'
           AND v.code_value_enabled_flag = 'Y'
           AND v.code_value = t1.int_rate_fixing_way) int_rate_fixing_way
  FROM tre_loan_contract t1
  ORDER BY t1.loan_con_bp) t1 #WHERE_CLAUSE#
                    #ORDER_BY_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="loan_con_bp_n" queryOperator="like"/>
        <bm:query-field field="loan_contract_number" queryOperator="like"/>
        <bm:query-field field="loan_contract_name" queryOperator="like"/>
        <bm:query-field name="signing_date_from" queryExpression="t1.signing_date &gt;=to_date(${@signing_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="signing_date_to" queryExpression="t1.signing_date &lt;=to_date(${@signing_date_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="loan_date_from" queryExpression="t1.loan_date &gt;=to_date(${@loan_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="loan_date_to" queryExpression="t1.loan_date &lt;=to_date(${@loan_date_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="loan_amount_from" queryExpression="t1.loan_amount &gt;= ${@loan_amount_from}"/>
        <bm:query-field name="loan_amount_to"  queryExpression="t1.loan_amount &lt;= ${@loan_amount_to}"/>
        <bm:query-field name="withdraw_amount_from" queryExpression="t1.withdraw_amount &gt;= ${@withdraw_amount_from}"/>
        <bm:query-field name="withdraw_amount_to"  queryExpression="t1.withdraw_amount &lt;= ${@withdraw_amount_to}"/>
        <bm:query-field name="remaining_amount_from" queryExpression="t1.remaining_amount &gt;= ${@remaining_amount_from}"/>
        <bm:query-field name="remaining_amount_to"  queryExpression="t1.remaining_amount &lt;= ${@remaining_amount_to}"/>
    </bm:query-fields>
</bm:model>
