<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false" alias="t1">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select
              t1.*,
              (case when
                (t1.SALES_OPERATEION_DATE < to_date('2018-05-01', 'yyyy-MM-dd'))
                then
                  (decode(t1.ticket_type, '1', round(0.02 * t1.ACTUAL_PRICE, 2), round(0.17 * t1.ACTUAL_PRICE, 2)))
               else
                 (decode(t1.ticket_type, '1', round(0.02 * t1.ACTUAL_PRICE, 2), round(0.16 * t1.ACTUAL_PRICE, 2)))
               end) tax_price
            from (select
                    cti.STORAGE_DATE,
                    cti.AGENT_NAME,
                    cti.CNTRT_NO CONTRACT_NUMBER,
                    cti.BP_NAME,
                    cti.MACHINE_NUMBER,
                    cti.PATTERN,
                    1                                MACHINE_COUNTS,
                    cti.STORAGE_AMOUNT,
                    cti.ACTUAL_PRICE,
                    cti.SALES_OPERATEION_DATE,
                    substr(cti.CNTRT_NO, 6, 1) ticket_type,
                    cti.ACCOUNT_NOTES,
                    cti.PHONE_CNO
                  from CON_TRAILER_INFO cti
                  where 1 = 1
                        and cti.CREATE_SALES_JE_FLAG = 'Y'
                        and (cti.SEC_CAR_REVERSE_FLAG != 'Y'
                             or cti.SEC_CAR_REVERSE_FLAG is null)
                        and (cti.SEC_CAR_REVERSE_SALES_FLAG != 'Y'
                             or cti.SEC_CAR_REVERSE_SALES_FLAG is null)
                 ) t1
                                         #WHERE_CLAUSE#

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="agent_name"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_name"/>
        <bm:field name="machine_number"/>
        <bm:field name="pattern"/>
        <bm:field name="storage_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="machine_counts" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="storage_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="actual_price" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="sales_operateion_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="ticket_type"/>
        <bm:field name="account_notes"/>
        <bm:field name="phone_cno"/>
        <bm:field name="tax_price" databaseType="NUMBER" datatype="java.lang.Double"/>


    </bm:fields>


    <bm:query-fields>
        <bm:query-field field="agent_name" queryOperator="like"/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="machine_number" queryOperator="like"/>
        <bm:query-field field="pattern" queryOperator="like"/>
        <bm:query-field field="phone_cno" queryOperator="like"/>
    </bm:query-fields>

</bm:model>
