<?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_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) > 30 and hvcm.mortgage_asset_detail = 'STOCK' and hvcm.mortgage_type = 'PLEDGE') stock_amount,--股权质押
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) > 30 and (hvcm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE' or hvcm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE_THIRD')
	                 and hvcm.mortgage_type = 'PLEDGE') acounts_amount,--应收账款质押 
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) > 30 and (hvcm.mortgage_asset_detail = 'NORMAL' or hvcm.mortgage_asset_detail = 'UNORMAL')
	                 and hvcm.mortgage_type = 'MORTGAGE') normal_amount,--动产抵押
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) > 30 and (hvcm.mortgage_asset_detail = 'HOUSE' or hvcm.mortgage_asset_detail = 'LAND')
	                 and hvcm.mortgage_type = 'MORTGAGE') hl_amount,--动产抵押
	                 
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) <= 30 and hvcm.mortgage_asset_detail = 'STOCK' 
	                 and hvcm.mortgage_type = 'PLEDGE' and hvcm.mortgage_end_date >= to_date(${@query_date},'yyyy-mm-dd')) stock_mon_amount,--股权质押(一月以内)
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) <= 30 and (hvcm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE' or hvcm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE_THIRD')
	                 and hvcm.mortgage_type = 'PLEDGE' and hvcm.mortgage_end_date >= to_date(${@query_date},'yyyy-mm-dd')) acounts_mon_amount,--应收账款质押(一月以内)
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) <= 30 and (hvcm.mortgage_asset_detail = 'NORMAL' or hvcm.mortgage_asset_detail = 'UNORMAL')
	                 and hvcm.mortgage_type = 'MORTGAGE' and hvcm.mortgage_end_date >= to_date(${@query_date},'yyyy-mm-dd')) normal_mon_amount,--动产抵押 (一月以内) 
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and hvcm.mortgage_end_date is not null
	                 and (hvcm.mortgage_end_date - (to_date(${@query_date},'yyyy-mm-dd'))) <= 30 and (hvcm.mortgage_asset_detail = 'HOUSE' or hvcm.mortgage_asset_detail = 'LAND')
	                 and hvcm.mortgage_type = 'MORTGAGE' and hvcm.mortgage_end_date >= to_date(${@query_date},'yyyy-mm-dd')) hl_mon_amount,--动产抵押 (一月以内) 
	                 
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and (hvcm.mortgage_end_date is null or hvcm.mortgage_end_date < to_date(${@query_date},'yyyy-mm-dd'))
	                 and hvcm.mortgage_asset_detail = 'STOCK' and hvcm.mortgage_type = 'PLEDGE') stock_jc_amount,--股权质押(已解除)
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and (hvcm.mortgage_end_date is null or hvcm.mortgage_end_date < to_date(${@query_date},'yyyy-mm-dd'))
	                 and (hvcm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE' or hvcm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE_THIRD')
	                 and hvcm.mortgage_type = 'PLEDGE') acounts_jc_amount,--应收账款质押 (已解除)
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
	               where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and (hvcm.mortgage_end_date is null or hvcm.mortgage_end_date < to_date(${@query_date},'yyyy-mm-dd'))
	                 and (hvcm.mortgage_asset_detail = 'NORMAL' or hvcm.mortgage_asset_detail = 'UNORMAL')
	                 and hvcm.mortgage_type = 'MORTGAGE') normal_jc_amount,--动产抵押 (已解除)
	             (select count(1) from hn_virtual_con_mortgage hvcm,hn_virtual_con_relate_hd hnvc 
                   where hnvc.project_id = pp.project_id and hnvc.hd_id = hvcm.hd_id and (hvcm.mortgage_end_date is null or hvcm.mortgage_end_date < to_date(${@query_date},'yyyy-mm-dd'))
                     and (hvcm.mortgage_asset_detail = 'HOUSE' or hvcm.mortgage_asset_detail = 'LAND')
                     and hvcm.mortgage_type = 'MORTGAGE') hl_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_virtual_con_mortgage hvcm 
	                      where hvcrh.project_id = pp.project_id and hvcrh.approve_status = 'APPROVED' and hvcm.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>
