<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.munjaon.client.oracle.mapper.OracleMapper">
    <select id="checkTime" resultType="String">
        /* OracleMapper.checkTime */
        SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') FROM DUAL
    </select>
    <select id="checkTableForMessage" resultType="int">
        /* OracleMapper.checkTableForMessage */
        <![CDATA[
        SELECT COUNT(MSG_ID) FROM MUNJAON_MSG WHERE 1=1 AND rownum <= 1
        ]]>
    </select>
    <select id="checkTableForLog" resultType="int">
        /* OracleMapper.checkTableForLog */
        <![CDATA[
        SELECT COUNT(MSG_ID) FROM MUNJAON_MSG_LOG WHERE 1=1 AND rownum <= 1
        ]]>
    </select>
    <select id="selectToDeliver" resultType="com.munjaon.client.model.MunjaonMsg">
        /* OracleMapper.selectToDeliver */
        <![CDATA[
        SELECT
            MSG_ID
            , MSG_TYPE
            , TO_CHAR(REQUEST_DATE,'YYYYMMDDHH24MISS') RESERVE_DATE
            , TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') REQUEST_DATE
            , RECV_PHONE
            , SEND_PHONE
            , SUBJECT
            , MESSAGE
            , FILENAME01
            , FILENAME02
            , FILENAME03
            , KAKAO_SENDER_KEY
            , KAKAO_TEMPLATE_CODE
            , KAKAO_JSON_FILE
        FROM MUNJAON_MSG
        WHERE SEND_STATUS = '0'
            AND MSG_TYPE = #{msgType}
            AND REQUEST_DATE <= SYSDATE
            AND ROWNUM <= 100
        ORDER BY REQUEST_DATE ASC
        ]]>
    </select>
    <update id="updateToDeliver">
        /* OracleMapper.updateToDeliver */
        UPDATE MUNJAON_MSG SET
            SEND_STATUS = '1'
            , DELIVER_DATE = SYSDATE
        WHERE MSG_ID = #{msgId}
    </update>
    <update id="updateDeliverForList">
        /* OracleMapper.updateToDeliver */
        UPDATE MUNJAON_MSG SET
            SEND_STATUS = '1'
            , DELIVER_DATE = SYSDATE
        WHERE SEND_STATUS = '0'
        AND (
        <foreach separator="OR" item="item" collection="list">
            MSG_ID = #{item}
        </foreach>
        )
    </update>
    <update id="updateToReport">
        /* OracleMapper.updateToReport */
        UPDATE MUNJAON_MSG SET
            SEND_STATUS = #{sendStatus}
            , SENT_DATE = TO_DATE(#{sendDate}, 'YYYYMMDDHH24MISS')
            , REPORT_DATE = SYSDATE
            , TELECOM = #{telecom}
        WHERE MSG_ID = #{msgId}
    </update>
    <select id="selectToMove" resultType="com.munjaon.client.model.MunjaonMsg">
        /* OracleMapper.selectToMove */
        <![CDATA[
        SELECT MSG_ID, TO_CHAR(REQUEST_DATE, 'YYYYMM') REQUEST_DATE
        FROM MUNJAON_MSG
        WHERE SEND_STATUS NOT IN ('0', '1')
            AND ROWNUM <= 100
        ]]>
    </select>
    <select id="selectToExpire" resultType="com.munjaon.client.model.MunjaonMsg">
        /* OracleMapper.selectToExpire */
        <![CDATA[
            SELECT MSG_ID, TO_CHAR(REQUEST_DATE, 'YYYYMM') REQUEST_DATE
            FROM MUNJAON_MSG
            WHERE REQUEST_DATE < TO_DATE(#{expireTime}, 'YYYYMMDDHH24MISS')
                AND SEND_STATUS = '1'
                AND ROWNUM <= 100
        ]]>
    </select>
    <insert id="insertToLog">
        /* OracleMapper.insertToLog */
        INSERT INTO MUNJAON_MSG_LOG
        (
            MSG_ID, MSG_TYPE, SEND_STATUS, REQUEST_DATE,
            RECV_PHONE, SEND_PHONE, SUBJECT, MESSAGE, TELECOM,
            FILENAME01, FILENAME02, FILENAME03, DELIVER_DATE, SENT_DATE, REPORT_DATE
        )
        SELECT
            MSG_ID, MSG_TYPE, SEND_STATUS, REQUEST_DATE,
            RECV_PHONE, SEND_PHONE, SUBJECT, MESSAGE, TELECOM,
            FILENAME01, FILENAME02, FILENAME03, DELIVER_DATE, SENT_DATE, REPORT_DATE
        FROM MUNJAON_MSG
        WHERE MSG_ID = #{msgId}
    </insert>
    <delete id="deleteFromDeliver">
        /* OracleMapper.deleteFromDeliver */
        DELETE FROM MUNJAON_MSG
        WHERE MSG_ID = #{msgId}
    </delete>
</mapper>