<?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 t.contract_number,
	    (SELECT sc.code_value_name
                      FROM sys_code_values_v sc
                     WHERE     sc.code = 'CON500_CONTRACT_STATUS'
                           AND sc.code_value = t.contract_status)
                       contract_status_desc,
       t.contract_name,
       t.contract_id,
       t.bp_id_tenant,
       (select hbm.bp_name
          from hls_bp_master hbm
         where hbm.bp_id = t.bp_id_tenant) bp_id_tenant_name,

       (select sum(ccc.principal - nvl(ccc.received_principal, 0))
          from con_contract_cashflow ccc
         where ccc.due_date < trunc(sysdate)
           and ccc.contract_id = t.contract_id
           and ccc.cf_item = 1
           and ccc.cf_direction = 'INFLOW') overdue_principal,
       (select sum(ccc.due_amount - nvl(ccc.received_amount, 0))
          from con_contract_cashflow ccc
         where ccc.due_date < trunc(sysdate)
           and ccc.contract_id = t.contract_id
           and ccc.cf_item = 1
           and ccc.cf_direction = 'INFLOW') overdue_due_amount,
       decode(t.price_list, 'DR_PRICE_10', t.pmt, '') pmt,
       /*(select to_char(ccc.due_date,'dd')
          from con_contract_cashflow ccc
         where ccc.contract_id = t.contract_id
           and ccc.cf_item = 1
           and ccc.cf_direction = 'INFLOW'
           and ccc.times =t.lease_times)*/
           t.repayment_date_day     due_day,

       (select ccc.due_amount
          from con_contract_cashflow ccc
         where ccc.contract_id = t.contract_id
           and ccc.cf_item = 1
           and ccc.cf_direction = 'INFLOW'
            and ccc.cf_status = 'RELEASE'
           and ccc.times = (select min(ccc1.times)
                              from con_contract_cashflow ccc1
                             where ccc1.due_date >= trunc(sysdate)
                               and ccc1.contract_id = t.contract_id
                               and ccc1.cf_item = 1
                               and ccc1.cf_direction = 'INFLOW')) next_due_amount

  from con_contract t

	             #WHERE_CLAUSE#
	        ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_number" forDisplay="true" forQuery="true" prompt="合同号"/>
        <bm:field name="contract_status_desc" forDisplay="true" prompt="合同状态"/>
        <bm:field name="contract_name"  prompt="合同名"/>
        <bm:field name="contract_id"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="bp_id_tenant_name" forDisplay="true" forQuery="true" prompt="客户名"/>
        <bm:field name="overdue_principal" forDisplay="true" prompt="逾期本金"/>
        <bm:field name="overdue_due_amount" forDisplay="true" prompt="逾期租金"/>
        <bm:field name="pmt" forDisplay="true" prompt="月租金"/>
        <bm:field name="due_day" forDisplay="true" prompt="还款日"/>
        <bm:field name="next_due_amount" forDisplay="true" prompt="近期租金"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t.contract_number like ${@contract_number}"/>
        <bm:query-field name="contract_name" queryExpression="t.contract_name like ${@contract_name}"/>
        <bm:query-field name="bp_id_tenant_name" queryExpression="exists (select 1
          from hls_bp_master hbm
         where hbm.bp_id = t.bp_id_tenant and hbm.bp_name like ${@bp_id_tenant_name})"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter expression=" (t.contract_status in ('INCEPT', 'SIGN', 'ET') or      (t.contract_status in ('ET',                               'AGENT_TERMINATE',                               'LOSSSHARE_TERMINATE',                               'VIRTUAL_TERMINATE',                               'TERMINATE') and exists        (select 1            from con_contract_cashflow ccc           where ccc.contract_id = t.contract_id             and ccc.cf_direction = 'INFLOW'             and ccc.cf_status = 'RELEASE'             and ccc.cf_item in (300，832, 831, 833,834))))   and t.data_class = 'NORMAL' "/>
    </bm:data-filters>
</bm:model>
