<?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.contract_number,
			       cc.contract_name,
			       (select MIN(cwo.write_off_date) from csh_write_off cwo where cwo.contract_id = cc.contract_id and cwo.times = 0
			           and NVL(cwo.reversed_flag, 'N') = 'N' and cwo.write_off_type='PAYMENT_DEBT') start_rent_date,--(起租日)
			       (SELECT NVL(ccc.due_amount, 0)
					      FROM con_contract_cashflow ccc
					     WHERE cc.contract_id = ccc.contract_id
					       AND ccc.times = 0
					       AND ccc.cf_item = 0) principal_amount, --本金总额(融资本金)
			       cc.bp_id_tenant,
					   (SELECT hbm.bp_name
					      FROM hls_bp_master hbm
					     WHERE hbm.bp_id = cc.bp_id_tenant) lessee_name, --主承租人
			       (SELECT eeav.unit_name
					      FROM exp_employee_assigns_v eeav
					     WHERE eeav.employee_id  = cc.employee_id 
					     and eeav.primary_position_flag = 'Y'
					       AND eeav.enabled_flag = 'Y' ) operating_department, --业务部门
			       cc.employee_id,
			       (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = cc.employee_id) manager, --项目经理
			       (CASE WHEN exists (select 1 from con_contract_change_req cccr where cccr.req_status = 'APPROVED' and cccr.ccr_type is not null and cccr.contract_id = cc.contract_id)
			             THEN (select max(cccr.approved_date) from con_contract_change_req cccr where cccr.req_status = 'APPROVED' and cccr.ccr_type is not null 
			                      and cccr.contract_id = cc.contract_id )
			               ELSE (select hcq.last_update_date from hn_contract_quotation hcq where hcq.contract_id = cc.contract_id and hcq.wfl_status = 'APPROVED')
			                 END) change_date,               -- 变动日期
			       (CASE WHEN exists (select 1 from con_contract_change_req cccr where cccr.req_status = 'APPROVED' and cccr.ccr_type is not null and cccr.contract_id = cc.contract_id)
			             THEN '支付表变更'
			               ELSE '支付表确认' END) change_status,             -- 变更状态
			       cc.calc_session_id
			  from con_contract cc
			 where cc.data_class = 'NORMAL'
			   AND NVL(cc.hn_adjust_review_flag, 'N') = 'Y'
			   AND (CASE WHEN exists (select 1 from con_contract_change_req cccr where cccr.req_status = 'APPROVED' and cccr.ccr_type is not null and cccr.contract_id = cc.contract_id)
			             THEN (select trunc(max(cccr.approved_date)) from con_contract_change_req cccr where cccr.req_status = 'APPROVED' and cccr.ccr_type is not null 
			                      and cccr.contract_id = cc.contract_id )
			               ELSE (select trunc(hcq.last_update_date) from hn_contract_quotation hcq where hcq.contract_id = cc.contract_id and hcq.wfl_status = 'APPROVED')
			                 END) < = to_date(${@end_query_date},'yyyy-mm-dd')
			   AND (CASE WHEN exists (select 1 from con_contract_change_req cccr where cccr.req_status = 'APPROVED' and cccr.ccr_type is not null and cccr.contract_id = cc.contract_id)
			             THEN (select trunc(max(cccr.approved_date)) from con_contract_change_req cccr where cccr.req_status = 'APPROVED' and cccr.ccr_type is not null 
			                      and cccr.contract_id = cc.contract_id )
			               ELSE (select trunc(hcq.last_update_date) from hn_contract_quotation hcq where hcq.contract_id = cc.contract_id and hcq.wfl_status = 'APPROVED')
			                 END) > = to_date(${@start_query_date},'yyyy-mm-dd')
			   ) 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="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
