<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zhangxing5129  
    $Date: 2013-9-5 下午05:04:37  
    $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 a.*
  from (select ct.contract_id,
               ct.contract_number,
               ct.document_category,
               (select dc.description
                  from hls_document_category dc
                 where dc.document_category = ct.document_category) document_category_desc,
               ct.document_type,
               (select dt.document_type_desc
                  from hls_document_type_v dt
                 where dt.document_type = ct.document_type) document_type_desc,
               ct.bp_id_tenant,
               ct.lease_start_date,
               (select bpv.bp_name
                  from hls_bp_master_v bpv
                 where bpv.bp_id = ct.bp_id_tenant) bp_name,
               nvl(ct.total_rental, 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_type = 1
                      and cf.cf_item = 1
                      and cf.cf_status in ('BLOCK','RELEASE')),
                   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 = 1
                      and cf2.cf_type = 1
                      and cf2.cf_status in ('BLOCK','RELEASE')),
                   0) overdue_amount,
               ct.contract_status
          from con_contract ct
         where ct.contract_status = 'INCEPT'
            and ct.company_id = ${/session/@company_id}) a
            
			#WHERE_CLAUSE#
            
 
]]></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="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:fields>
    <bm:query-fields>
        <bm:query-field name="lease_start_date_from" queryExpression="a.lease_start_date &gt;=to_date(${@lease_start_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="a.lease_start_date &lt;=to_date(${@lease_start_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="document_category" queryOperator="like"/>
        <bm:query-field field="document_type" queryOperator="like"/>
        <bm:query-field field="contract_status" queryOperator="like"/>
    </bm:query-fields>
</bm:model>
