<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: DJ  
    $Date: 2013-11-13 下午2:18:51  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="SYS_SMS_LIST">
    <bm:fields>
        <bm:field name="sms_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SMS_ID" prompt="SYS_SMS_LIST.SMS_ID"/>
        <bm:field name="sms_date" databaseType="DATE" datatype="java.util.Date" physicalName="SMS_DATE" prompt="SYS_SMS_LIST.SMS_DATE"/>
        <bm:field name="sms_date_str" forInsert="false" forUpdate="false"/>
        <bm:field name="priority" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PRIORITY" prompt="SYS_SMS_LIST.PRIORITY"/>
        <bm:field name="priority_desc" forInsert="false" forUpdate="false"/>
        <bm:field name="phone_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PHONE_NUMBER" prompt="SYS_SMS_LIST.PHONE_NUMBER"/>
        <bm:field name="text" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TEXT" prompt="SYS_SMS_LIST.TEXT"/>
        <bm:field name="error_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ERROR_STATUS" prompt="SYS_SMS_LIST.ERROR_STATUS"/>
        <bm:field name="error_msg" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ERROR_MSG" prompt="SYS_SMS_LIST.ERROR_MSG"/>
        <bm:field name="status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="STATUS" prompt="SYS_SMS_LIST.STATUS"/>
        <bm:field name="status_desc" forInsert="false" forQuery="true" forUpdate="false"/>
        <bm:field name="sent_date_str" forInsert="false" forQuery="true" forUpdate="false"/>
        <bm:field name="send_date" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t1.sms_id,
                    t1.sms_date,
                    TO_CHAR(t1.sms_date,'yyyy-mm-dd hh24:mi:ss') AS sms_date_str,
                    TO_CHAR(t1.sms_date, 'yyyy-mm-dd hh24:mi:ss') AS sms_date_str,
                    (SELECT
                        TO_CHAR(ss.sent_date, 'yyyy-mm-dd hh24:mi:ss')
                    FROM
                        sys_sms_history ss
                    WHERE
                        ss.sms_id = t1.sms_id
                    ) AS sent_date_str,
                    t1.priority,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'SYS402_SMS_PRIORITY' AND
                        v.code_value = t1.priority
                    ) AS priority_desc,
                    t1.phone_number,
                    t1.text,
                    t1.error_status,
                    t1.error_msg,
                    t1.status,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'SYS402_SMS_STATUS' AND
                        v.code_value = t1.status
                    ) AS status_desc,
                    t1.send_date,
                    t1.contract_number
                FROM
                    sys_sms_list t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.sms_id DESC
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
    			begin
	    			null;
    			end;
    		]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
    			begin
	    			  sys401_save_pkg.re_submit_sms(p_sms_id			  => ${@sms_id},
	    											p_user_id			  => ${/session/@user_id});
    			end;
    		]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="delete">
            <bm:update-sql><![CDATA[
    			begin
	    			delete from SYS_SMS_LIST  where sms_id = ${@sms_id};
    			end;
    		]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
    			begin
	    			sys401_save_pkg.submit_sms(p_all_phone_number => ${@all_phone_number},
						                       p_text             => ${@text},
						                       p_priority         => 2,
						                       p_user_id		  => ${/session/@user_id});
    			end;
    		]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:query-fields>
        <bm:query-field name="phone_number" queryExpression="phone_number like ${@phone_number}"/>
        <bm:query-field name="contract_number" queryExpression="contract_number like ${@contract_number}"/>
        <bm:query-field name="sms_date_f" queryExpression="trunc(sms_date) &gt;= to_date(${@sms_date_f},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="sms_date_t" queryExpression="trunc(sms_date) &lt;= to_date(${@sms_date_t},&apos;yyyy-mm-dd&apos;)"/>
        <!--<bm:query-field name="sent_date_f" queryExpression="to_date(send_date,&apos;yyyy-mm-dd&apos;) &gt;= to_date(${@sent_date_f},&apos;yyyy-mm-dd&apos;)"/>-->
        <!--<bm:query-field name="sent_date_t" queryExpression="to_date(send_date,&apos;yyyy-mm-dd&apos;) &lt;= to_date(${@sent_date_t},&apos;yyyy-mm-dd&apos;)"/>-->
        <bm:query-field name="sms_id" queryExpression="sms_id = ${@sms_id}"/>
        <bm:query-field name="error_count_check" queryExpression="(${@error_count_check} = &apos;Y&apos; and nvl(error_count,0) &lt; 5)"/>
    </bm:query-fields>
</bm:model>
