<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zhangxing5129  
    $Date: 2013-9-6 下午03:29:09  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
             select t1.*, (t1.total_rental - t1.total_received) total_balance
  from (select ct.contract_id,
               ct.contract_number,
               ct.document_category,
               ct.document_category_desc,
               ct.document_type,
               ct.document_type_desc,
               ct.business_type,
               ct.business_type_desc,
               ct.bp_id_tenant,
               ct.lease_start_date,
               ct.bp_name,
              nvl((select sum(nvl(cf.due_amount, 0))
                     from con_contract_cashflow cf
                    where cf.contract_id = ct.contract_id
                      and cf.cf_item = 1
                      and cf.cf_status in ('BLOCK', 'RELEASE')
                      and cf.cf_direction !='NONCASH'),
                   0)  total_rental,
               nvl((select sum(nvl(cf.received_amount, 0))
                     from con_contract_cashflow cf
                    where cf.contract_id = ct.contract_id
                      and cf.cf_item = 1
                      and cf.cf_status in ('BLOCK', 'RELEASE')
                      and cf.cf_direction !='NONCASH'),
                   0) total_received,
               nvl((select sum(nvl(cf2.overdue_amount, 0))
                     from con_contract_cashflow cf2
                    where cf2.overdue_status = 'Y'
                      and cf2.contract_id = ct.contract_id
                      and cf2.cf_item in (1,3,4)
                      and cf2.cf_status in ('BLOCK', 'RELEASE')
                      and cf2.cf_direction ='INFLOW'),
                   0) overdue_amount,
               nvl((select sum(nvl(cf3.due_amount, 0))
                     from con_contract_cashflow cf3
                    where cf3.cf_item = 9
                      and cf3.contract_id = ct.contract_id),
                   0) received_fine_amount,
               ct.contract_status
          from con_contract_v ct
         where  ct.contract_id = ${@contract_id}
           and ct.company_id = ${/session/@company_id}) t1
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_number"/>
        <bm:field name="document_category"/>
        <bm:field name="document_type"/>
        <bm:field name="document_category_desc"/>
        <bm:field name="document_type_desc"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_desc"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_id"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="lease_start_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="bp_name"/>
        <bm:field name="total_received"/>
        <bm:field name="overdue_amount"/>
        <bm:field name="total_rental"/>
        <bm:field name="total_balance"/>
    </bm:fields>
</bm:model>
