<?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 (select cc.contract_number
				          from con_contract cc
				         where cc.contract_id = ccc.contract_id) contract_number, --合同号 
				       (select cc.contract_name
				          from con_contract cc
				         where cc.contract_id = ccc.contract_id) contract_name, --合同号 
				       ccc.times, --期数
				       nvl((select cccf.due_date
				             from con_contract_cashflow cccf
				            where cccf.contract_id = ccc.contract_id
				              and cccf.cf_item = 1
				              and cccf.times = ccc.times - 1),
				           (select ccca.due_date
				              from con_contract_cashflow ccca
				             where ccca.contract_id = ccc.contract_id
				               and ccca.cf_item = 0)) start_due_date, --本期起始日
				       ccc.due_date, --本期终止日
				       NVL(ccc.received_amount, 0) received_amount, --当期已经偿付租金
				       ccc.due_amount, --应还的总额
				       NVL((NVL(ccc.due_amount, 0) -
				           NVL((select SUM(cwo.write_off_due_amount)
				                  from csh_write_off cwo
				                 where cwo.write_off_type = 'RECEIPT_CREDIT'
				                   and cwo.contract_id = ccc.contract_id
				                   and cwo.cashflow_id = ccc.cashflow_id
				                   and nvl(cwo.reversed_flag, 'N') = 'N'
				                   and cwo.write_off_date < ccc.due_date + 30),
				                0)),
				           0) not_payment_amount, --当期到期未支付租金
				       (select MAX(cwo.write_off_date)
				          from csh_write_off cwo
				         where cwo.write_off_type = 'RECEIPT_CREDIT'
				           and cwo.contract_id = ccc.contract_id
				           and cwo.cashflow_id = ccc.cashflow_id
				           and nvl(cwo.reversed_flag, 'N') = 'N') write_off_date, --租金实际偿讨日
				       (case
				         when nvl(ccc.due_date,sysdate) >
				              (select MAX(cwo.write_off_date)
				                 from csh_write_off cwo
				                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                  and cwo.contract_id = ccc.contract_id
				                  and cwo.cashflow_id = ccc.cashflow_id
				                  and nvl(cwo.reversed_flag, 'N') = 'N') - 30 then
				          0
				         else
				          ((nvl((select SUM(cwo.write_off_due_amount)
				                 from csh_write_off cwo
				                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                  and cwo.contract_id = ccc.contract_id
				                  and cwo.cashflow_id = ccc.cashflow_id
				                  and nvl(cwo.reversed_flag, 'N') = 'N'),0)) +
				          (NVL((select ccca.received_amount
				             from con_contract_cashflow ccca
				            where ccca.times = ccc.times
				              and ccca.contract_id = ccc.contract_id
				              and ccca.cf_item = 3),
				           0)))  
				       end) recycle_amount, --承租人违约时相应回收款
				       NVL(ccc.received_principal,0) received_principal, --本金回收款
				       NVL(ccc.received_interest,0) received_interest, --利息回收款
				       NVL((select ccca.received_amount
				             from con_contract_cashflow ccca
				            where ccca.times = ccc.times
				              and ccca.contract_id = ccc.contract_id
				              and ccca.cf_item = 3),
				           0) charge_amount --手续费回收
				  from con_contract_cashflow ccc, hn_abs_contract hac
				 where hac.contract_id = ccc.contract_id
				   and ccc.times <= hac.end_times
				   and ccc.times >= hac.start_times
				   and ccc.cf_item = 1
				   and hac.abs_contract_id = ${@abs_contract_id}
				   and ccc.contract_id = ${@contract_id}
			      )t1 #WHERE_CLAUSE#
			      ORDER BY t1.times
			      ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="contract_id"/>
                <bm:parameter name="abs_contract_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
