<?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_number"/>
        <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="division_n"/>
        <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="pattern"/>
        <bm:field name="machine_number"/>
        <bm:field name="equipment_number"/>
        <bm:field name="base_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="penalty_reduction"/>
        <bm:field name="penalty_reduction_n"/>
        <bm:field name="reduce_amount_total" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="created_by"/>
        <bm:field name="created_by_n"/>
        <bm:field name="penalty_batch_number"/>
        <bm:field name="active_flag"/>
        <bm:field name="active_flag_n"/>
        <bm:field name="reduce_amount_total_ratio"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select * from
        (SELECT (SELECT t1.contract_number
          FROM con_contract t1
         WHERE t1.contract_id = t.contract_id) contract_number,
       (SELECT hbm.bp_code
          FROM hls_bp_master hbm
         WHERE hbm.bp_id =
               (SELECT bp_id_tenant
                  FROM con_contract t1
                 WHERE t1.contract_id = t.contract_id)) bp_code,
       (SELECT hbm.bp_name
          FROM hls_bp_master hbm
         WHERE hbm.bp_id =
               (SELECT bp_id_tenant
                  FROM con_contract t1
                 WHERE t1.contract_id = t.contract_id)) bp_name,
       (SELECT hbm.bp_code
          FROM hls_bp_master hbm
         WHERE hbm.bp_id =
               (SELECT t1.bp_id_agent_level1
                  FROM con_contract t1
                 WHERE t1.contract_id = t.contract_id)) bp_agent_code,
       (SELECT hbm.bp_name
          FROM hls_bp_master hbm
         WHERE hbm.bp_id =
               (SELECT t1.bp_id_agent_level1
                  FROM con_contract t1
                 WHERE t1.contract_id = t.contract_id)) bp_agent_name,
       (SELECT t1.description AS value_name
          FROM hls_division t1
         WHERE t1.division =
               (SELECT t1.division
                  FROM con_contract t1
                 WHERE t1.contract_id = t.contract_id)) division_n,
       (SELECT t1.lease_channel
          FROM con_contract t1
         WHERE t1.contract_id = t.contract_id) lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (SELECT t1.lease_channel
                  FROM con_contract t1
                 WHERE t1.contract_id = t.contract_id)) lease_channel_n,
       (SELECT t1.business_type
          FROM con_contract t1
         WHERE t1.contract_id = t.contract_id) business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type =
               (SELECT t1.business_type
                  FROM con_contract t1
                 WHERE t1.contract_id = t.contract_id)) AS business_type_n,
       (SELECT i.pattern
          FROM con_contract_lease_item i
         WHERE i.contract_id = t.contract_id
           AND i.equipment_type = 'MAIN') pattern, --机型
       (SELECT i.machine_number
          FROM con_contract_lease_item i
         WHERE i.contract_id = t.contract_id
           AND i.equipment_type = 'MAIN') machine_number, --机号
       (SELECT i.equipment_number
          FROM con_contract_lease_item i
         WHERE i.contract_id = t.contract_id
           AND i.equipment_type = 'MAIN') equipment_number, --装备号
       t.base_date,
       t.penalty_reduction,
       (SELECT scv.code_value_name
          FROM sys_code_values_v scv
         WHERE scv.code = 'PENALTY_REDUCTION'
           AND scv.code_value = t.penalty_reduction) penalty_reduction_n,
       t.reduce_amount_total,
       (select cc.created_by
          from con_contract_case_record cc
         WHERE cc.record_id = t.record_id) created_by,
       (select t1.description
          from sys_user t1
         where t1.user_id = (select cc.created_by
                               from con_contract_case_record cc
                              WHERE cc.record_id = t.record_id)) created_by_n,
       (select cc.penalty_batch_number
          from con_contract_case_record cc
         WHERE cc.record_id = t.record_id) penalty_batch_number,
          t.active_flag,
       (SELECT v.code_value_name AS value_name
          FROM sys_code_values_v v
         WHERE v.code = 'PENALTY_ACTIVE_FLAG'
           AND v.code_value = t.active_flag) active_flag_n,
           to_char(t.reduce_amount_total_ratio*100,'FM990.00')||decode(reduce_amount_total_ratio,null,null,'%') reduce_amount_total_ratio
  FROM con_contract_penalty_reduce t) 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_name" queryOperator="like"/>
        <bm:query-field field="bp_agent_name" queryOperator="like"/>
        <bm:query-field name="base_date_from" queryExpression="t1.base_date &gt;= to_date(${@base_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="base_date_to" queryExpression="t1.base_date &lt;= to_date(${@base_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field field="penalty_reduction_n" queryOperator="="/>
        <bm:query-field field="created_by_n" queryOperator="like"/>
        <bm:query-field field="active_flag_n" queryOperator="="/>
        <bm:query-field name="reduce_amount_total_from" queryExpression="t1.reduce_amount_total &gt;= ${@reduce_amount_total_from}"/>
        <bm:query-field name="reduce_amount_total_to" queryExpression="t1.reduce_amount_total &lt;= ${@reduce_amount_total_to}"/>
    </bm:query-fields>
</bm:model>
