<?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 hcba.contract_id,
			       (select cc.contract_number from con_contract cc where cc.contract_id = hcba.contract_id) contract_number,
			       (select pp.virtual_con_number from con_contract cc,prj_project pp where cc.contract_id = hcba.contract_id and pp.project_id = cc.project_id) virtual_con_number,
			       (select cc.contract_name from con_contract cc where cc.contract_id = hcba.contract_id) contract_name,
			       (SELECT NVL(ccc.due_amount, 0)
			          FROM con_contract_cashflow ccc
			         WHERE hcba.contract_id = ccc.contract_id
			           AND ccc.times = 0
			           AND ccc.cf_item = 0) principal_amount, --本金总额
			       (SELECT hbm.bp_name
			          FROM hls_bp_master hbm,con_contract cc
			         WHERE hbm.bp_id = cc.bp_id_tenant and cc.contract_id = hcba.contract_id) lessee_name, --主承租人 
			       (select vc.name 
			          from exp_employees vc,con_contract cc,prj_project pp 
			         where vc.employee_id = pp.employee_id and pp.project_id = cc.project_id and cc.contract_id = hcba.contract_id) manager, --项目经理
			       (select mc.unit_name 
			          from exp_employee_assigns_v mc,con_contract cc,prj_project pp
			         where mc.employee_id = pp.employee_id and mc.enabled_flag='Y'  and mc.primary_position_flag='Y' and pp.project_id = cc.project_id and cc.contract_id = hcba.contract_id) department, --业务部门 
			       (select cbb.bank_branch_name
			          from csh_bank_branch cbb, csh_bank_account cba
			         where cba.bank_account_id = hcba.bank_account_id
			           and cbb.bank_branch_id = cba.bank_branch_id ) bank_branch_name,  --开户行名称
			       (select cba.bank_account_num
			          from csh_bank_account cba
			         where cba.bank_account_id = hcba.bank_account_id ) bank_account_id_n,  --回款账户
			       hcba.valid_from,
			       hcba.valid_to,
			       hcba.record_id
			  from hn_con_bank_account hcba
			  ) t1 #WHERE_CLAUSE# order by t1.contract_number,t1.valid_from
			]]></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="virtual_con_number" queryExpression="t1.virtual_con_number like &apos;%&apos;||${@virtual_con_number}||&apos;%&apos;"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field name="lessee_name" queryExpression="t1.lessee_name like &apos;%&apos;||${@lessee_name}||&apos;%&apos;"/>
        <bm:query-field name="principal_amount_from" queryExpression="t1.principal_amount &gt;= ${@principal_amount_from}"/>
        <bm:query-field name="principal_amount_to" queryExpression="t1.principal_amount &lt;= ${@principal_amount_to}"/>
        <bm:query-field name="manager" queryExpression="t1.manager like &apos;%&apos;||${@manager}||&apos;%&apos;"/>
        <bm:query-field name="bank_account_id_n" queryExpression="t1.bank_account_id_n like &apos;%&apos;||${@bank_account_id_n}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
