<?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 pp.virtual_con_number,
			       pp.virtual_con_name,
			       nvl((select '履行中' from dual where exists
	               (select 1 from prj_project tt where tt.project_id = pp.project_id and exists (select 1 from con_contract cc where cc.data_class = 'NORMAL'
	               and cc.project_id = pp.project_id and exists (select 1 from con_contract_cashflow ccc where ccc.contract_id = cc.contract_id 
	               and ccc.write_off_flag <> 'FULL' and ccc.times = (select max(ccca.times) from con_contract_cashflow ccca 
	               where ccca.contract_id = ccc.contract_id))))),'已结清') contract_status,--合同状态
			       (select tt.real_capacity_date from hn_virtual_con_relate_hd tt where tt.project_id = pp.project_id ) real_capacity_date,--实际投产时间
			       (select count(1) from hn_vir_con_lease_item_right hvcl,hn_virtual_con_relate_hd hvcrh 
               where hvcrh.project_id = pp.project_id and hvcl.hd_id = hvcrh.hd_id and hvcl.registed_end_date is not null
                 and (hvcl.registed_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) > 30) registed_amount,     --登记中（1月以上）
             (select count(1) from hn_vir_con_lease_item_right hvcl,hn_virtual_con_relate_hd hvcrh 
               where hvcrh.project_id = pp.project_id and hvcl.hd_id = hvcrh.hd_id and hvcl.registed_end_date is not null
                 and (hvcl.registed_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) <= 30 and (to_date(${@query_date},'yyyy-mm-dd')) <= hvcl.registed_end_date) registed_mon_amount,     --登记中（1月以内）
             (select count(1) from hn_vir_con_lease_item_right hvcl,hn_virtual_con_relate_hd hvcrh 
               where hvcrh.project_id = pp.project_id and hvcl.hd_id = hvcrh.hd_id and (hvcl.registed_end_date is null or hvcl.registed_end_date < (to_date(${@query_date},'yyyy-mm-dd')))) registed_jc_amount,     --已过期
			       pp.project_id
			  from prj_project pp
			 where pp.data_class = 'VIRTUAL_CON'
         and pp.project_status = 'APPROVED'
         and exists (select 1 from hn_virtual_con_relate_hd hvcrh,hn_vir_con_lease_item_right hvclir 
                      where hvcrh.project_id = pp.project_id and hvcrh.approve_status = 'APPROVED' and hvclir.hd_id = hvcrh.hd_id)
              ) t1 #WHERE_CLAUSE# order by t1.virtual_con_number
			]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="virtual_con_number" queryExpression="t1.virtual_con_number like &apos;%&apos;||${@virtual_con_number}||&apos;%&apos;"/>
        <bm:query-field name="virtual_con_name" queryExpression="t1.virtual_con_name like &apos;%&apos;||${@virtual_con_name}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
