<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_n"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="bp_code"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_agent_code"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="extra_nam"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="lease_end_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="et_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="pattern"/>
        <bm:field name="machine_number"/>
        <bm:field name="lease_term_month"/>
        <bm:field name="termination_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="billing_way"/>
        <bm:field name="billing_way_n"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select * from
        (select c.contract_id,
       c.contract_number,
       c.lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel = c.lease_channel) lease_channel_n,
       c.business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type = c.business_type) AS business_type_n,
       c.lease_execution_date,
       (select hb.bp_code
          from hls_bp_master hb
         where hb.bp_id = c.bp_id_tenant) bp_code,
       (select hb.bp_name
          from hls_bp_master hb
         where hb.bp_id = c.bp_id_tenant) bp_name,
       (select hb.bp_code
          from hls_bp_master hb
         where hb.bp_id = c.bp_id_agent_level1) bp_agent_code,
       (select hb.bp_name
          from hls_bp_master hb
         where hb.bp_id = c.bp_id_agent_level1) bp_agent_name,
       (select hb.extra_nam
          from hls_bp_master hb
         where hb.bp_id = c.bp_id_agent_level1) extra_nam,
       c.contract_status,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'CON500_CONTRACT_STATUS'
           and v.code_value = c.contract_status) AS contract_status_n,
       c.lease_end_date,
       c.et_date,
       (select i.pattern
          from con_contract_lease_item i
         WHERE i.contract_id = c.contract_id
           AND i.equipment_type = 'MAIN') pattern,
       (select i.machine_number
          from con_contract_lease_item i
         WHERE i.contract_id = c.contract_id
           AND i.equipment_type = 'MAIN') machine_number,
       c.lease_term_month,
       (select cc.termination_date
          from con_contract_et_hd cc
         where cc.contract_id = c.contract_id
         and cc.status = 'APPROVED') termination_date,
       c.billing_way,
        (Select v.code_value_name
          From sys_code_values_v v
         Where v.code = 'DS_INVOICE_CATEGORY'
           And v.code_value = c.billing_way) billing_way_n
  from con_contract c
  where c.data_class ='NORMAL') t1

                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_agent_name" queryOperator="like"/>
        <bm:query-field field="extra_nam" queryOperator="like"/>
        <bm:query-field field="contract_status_n" queryOperator="="/>
        <bm:query-field field="lease_channel_n" queryOperator="="/>
        <bm:query-field field="business_type_n" queryOperator="="/>
        <bm:query-field name="lease_execution_date_from" queryExpression="t1.lease_execution_date &gt;= to_date(${@lease_execution_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_execution_date_to" queryExpression="t1.lease_execution_date &lt;= to_date(${@lease_execution_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
