<?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="email">

    <resultMap id="msgSend" extends="common.markedObjectResult" type="msgSend">
        <id property="messageId" column="MESSAGE_ID"/>
        <result property="messageSendDatetime" column="MESSAGE_SEND_DATETIME"/>
        <result property="messageTitle" column="MESSAGE_TITLE"/>
        <result property="messageDetail" column="MESSAGE_DETAIL"/>
        <result property="mhtDetail" column="MHT_DETAIL"/>
        <result property="messageSndrId" column="MESSAGE_SNDR_ID"/>
        <result property="sendEmailAddr" column="SNDR_EMAIL_ADDRESS"/>
        <result property="messageKey" column="MESSAGE_KEY"/>
        <result property="recipients" column="RECIPIENTS"/>
        <result property="userName" column="USER_NAME"/>
        <result property="senderName" column="SENDER_NAME"/>
        <result property="grdName" column="GRD_NAME"/>
        <result property="deptName" column="DEPT_NAME"/>
        <association property="msgRcvrs" select="selectMsgRcvrs" column="{messageId=MESSAGE_ID}" />
    </resultMap>

    <resultMap id="msgRcvr" extends="common.markedObjectResult" type="msgRcvr">
        <id property="messageId" column="MESSAGE_ID"/>
        <result property="rcvrEmailAddress" column="RCVR_EMAIL_ADDRESS"/>
        <result property="messageDeliverDatetime" column="MESSAGE_DELIVER_DATETIME"/>
        <result property="messageOpenDatetime" column="MESSAGE_OPEN_DATETIME"/>
        <result property="messageRcvrId" column="MESSAGE_RCVR_ID"/>
        <result property="recipientStatus" column="RECIPIENT_STATUS"/>
        <result property="messageKey" column="MESSAGE_KEY"/>
        <result property="userName" column="USER_NAME"/>
        <result property="grdName" column="GRD_NAME"/>
        <result property="deptName" column="DEPT_NAME"/>
        <result property="rcvrType" column="RCVR_TYPE"/>
        <result property="senderId" column="MESSAGE_SNDR_ID"/>
        <result property="sendEmail" column="SNDR_EMAIL_ADDRESS"/>
    </resultMap>

    <resultMap id="sendMail" type="com.samsung.knox.mail.vo.SendMail">
        <id property="mailId" column="MAIL_ID"/>
        <result property="mailDto.subject" column="SUBJECT"/>
        <result property="contentMhtml" column="CONTENT_MHTML"/>
        <result property="contentHtml" column="CONTENT_HTML"/>
        <result property="senderId" column="SENDER_ID"/>
        <result property="senderName" column="SENDER_NAME"/>
        <result property="senderPosition" column="SENDER_POSITION"/>
        <result property="senderDepartment" column="SENDER_DEPARTMENT"/>
        <result property="senderCompany" column="SENDER_COMPANY"/>
        <result property="mailDto.sender.emailAddress" column="SENDER_EMAIL"/>
        <result property="sendDateTime" column="SEND_DATETIME"/>
        <result property="mailDto.contents" column="CONTENT_TEXT"/>
        <result property="mailDto.contentType" column="CONTENT_TYPE"/>
        <result property="mailDto.docSecuType" column="DOC_SECU_TYPE"/>
        <result property="recipientStr" column="RECIPENTS"/>
    </resultMap>

    <resultMap id="recipient" type="com.samsung.knox.mail.vo.Recipient">
        <id property="name" column="RECIPIENT_NAME"/>
        <result property="position" column="POSITION"/>
        <result property="department" column="DEPARTMENT"/>
        <result property="company" column="COMPANY"/>
        <result property="enName" column="EN_NAME"/>
        <result property="enPosition" column="EN_POSITION"/>
        <result property="enDepartment" column="EN_DEPARTMENT"/>
        <result property="enCompany" column="SENDER_COMPANY"/>
        <result property="email" column="EMAIL"/>
        <result property="userEmail" column="USER_EMAIL"/>
        <result property="epID" column="EP_ID"/>
        <result property="userID" column="USER_ID"/>
        <result property="recipientType" column="RECIPIENT_TYPE"/>
        <result property="sentTime" column="SENT_TIME"/>
        <result property="delvTime" column="DELV_TIME"/>
        <result property="updateTime" column="UPDATE_TIME"/>
        <result property="status" column="RECIPIENT_STATUS"/>
        <result property="statusTime" column="RECIPIENT_STATUS_TIME"/>
        <result property="statusKoTime" column="RECIPIENT_STATUS_KO_TIME"/>
        <result property="rcvrName" column="RCVR_NAME"/>
        <result property="displayName" column="DISPLAY_NAME"/>
        <result property="mailId" column="MAIL_ID"/>
    </resultMap>

    <sql id="tableMsgSend">TN_CF_MSG_SEND</sql>
    <sql id="tableMsgRcvr">TN_CF_MSG_RCVR</sql>
    <sql id="tableMail">TN_CF_MAIL</sql>
    <sql id="tableRecipient">TN_CF_RECIPIENT</sql>

    <select id="selectSentMail" parameterType="java.util.HashMap" resultMap="sendMail">
        SELECT MAIL_ID, CONTENT_TEXT, SUBJECT,
               CONTENT_TYPE, DOC_SECU_TYPE,
               SENDER_EMAIL, SENDER_ID, SENDER_NAME,
               SENDER_POSITION, SENDER_DEPARTMENT, SENDER_COMPANY,
               SEND_DATETIME, CONTENT_MHTML, CONTENT_HTML
        FROM   <include refid="tableMail" />
        WHERE  MAIL_ID = #{mailId}
    </select>

    <insert id="insertMail" parameterType="sendMail">
		INSERT INTO <include refid="tableMail" />
		(MAIL_ID, CONTENT_TEXT, SUBJECT,
		 CONTENT_TYPE, DOC_SECU_TYPE,
		 SENDER_EMAIL, SENDER_ID, SENDER_NAME,
		 SENDER_POSITION, SENDER_DEPARTMENT, SENDER_COMPANY,
		 SEND_DATETIME, CONTENT_MHTML, CONTENT_HTML)
		VALUES
		(#{mailId}, #{mailDto.contents}, #{mailDto.subject},
		 #{mailDto.contentType}, #{mailDto.docSecuType},
		 #{mailDto.sender.emailAddress}, #{senderId}, #{senderName},
		 #{senderPosition}, #{senderDepartment}, #{senderCompany},
		 #{sendDateTime}, #{contentMhtml},
		 #{contentHtml})
	</insert>

    <insert id="insertRecipient" parameterType="recipient">
		INSERT INTO <include refid="tableRecipient" />
		(EMAIL, RECIPIENT_NAME, POSITION, COMPANY, DEPARTMENT,
		 EN_NAME, EN_POSITION, EN_DEPARTMENT,
		 USER_EMAIL, EP_ID, USER_ID,
		 RECIPIENT_TYPE, RCVR_NAME, DISPLAY_NAME, MAIL_ID)
		VALUES
		(#{email}, #{name}, #{position}, #{company}, #{department},
		 #{enName}, #{enPosition}, #{enDepartment},
		 #{userEmail}, #{epID}, #{userID},
		 #{recipientType}, #{rcvrName}, #{displayName}, #{mailId})
	</insert>

    <update id="updateMail" parameterType="sendMail">
        UPDATE <include refid="tableMail" />
        SET    CONTENT_TEXT = #{contentText},
               SUBJECT = #{subject},
               CONTENT_TYPE = #{contentType},
               DOC_SECU_TYPE = #{docSecuType},
               SENDER_EMAIL = #{senderEmail},
               SENDER_ID = #{senderId},
               SENDER_NAME = #{senderName},
               SENDER_POSITION = #{senderPosition},
               SENDER_DEPARTMENT = #{senderDepartment},
               SENDER_COMPANY = #{senderCompany},
               CONTENT_MHTML = #{contentMhtml}
        WHERE  MAIL_ID = #{mailId}
    </update>

    <update id="updateRecipient" parameterType="recipient">
        UPDATE <include refid="tableRecipient" />
        SET    RECIPIENT_NAME = #{name},
               POSITION = #{position},
               COMPANY = #{company},
               DEPARTMENT = #{department},
               EN_NAME = #{enName},
               EN_POSITION = #{enPosition},
               EN_DEPARTMENT = #{enDepartment},
               USER_EMAIL = #{userEmail},
               EP_ID = #{epID},
               USER_ID = #{userID},
               RECIPIENT_TYPE = #{recipientType},
               SENT_TIME = #{sentTime},
               DELV_TIME = #{delvTime},
               UPDATE_TIME = #{updateTime},
               RECIPIENT_STATUS = #{status},
               RECIPIENT_STATUS_KO_TIME = #{statusKoTime},
               RECIPIENT_STATUS_TIME = #{statusTime},
               RCVR_NAME = #{rcvrName},
               DISPLAY_NAME = #{displayName}
        WHERE  EMAIL = #{email}
        AND    MAIL_ID = #{mailId}
    </update>

    <select id="selectRecipientList" parameterType="java.util.HashMap" resultMap="recipient">
        SELECT
            MAIL_ID,
            EMAIL,
            RECIPIENT_NAME,
            RECIPIENT_TYPE,
            RECIPIENT_STATUS,
            UPDATE_TIME,
            "POSITION",
            DEPARTMENT,
            COMPANY,
            EN_NAME,
            EN_POSITION,
            EN_DEPARTMENT
        FROM
            <include refid="tableRecipient" />
        WHERE
            MAIL_ID = #{mailId}
    </select>

    <select id="selectSentMailHistoryList" parameterType="java.util.HashMap" resultMap="sendMail">
        SELECT *
        FROM   (SELECT T1.*,
                       CEIL(ROW_NUMBER() OVER ( ORDER BY SEND_DATETIME DESC ) / (10 * 1.0)) AS PAGE
                FROM   (SELECT A.MAIL_ID, A.SUBJECT,
                               A.CONTENT_TYPE, A.DOC_SECU_TYPE,
                               A.SENDER_EMAIL, A.SENDER_ID, A.SENDER_NAME,
                               A.SENDER_POSITION, A.SENDER_DEPARTMENT, A.SENDER_COMPANY,
                               A.SEND_DATETIME, C.RECIPENTS
                        FROM   <include refid="tableMail" /> A
                            LEFT OUTER JOIN (SELECT MAIL_ID,
                                                    (MAX(USER_NAME) || ' 외 ' || SUM(PCONT) || '명') AS RECIPENTS
                                             FROM   (SELECT R.MAIL_ID, (COUNT(*) - 1) PCONT,
                                                            NULL USER_NAME
                                                     FROM   <include refid="tableRecipient" /> R
                                                     GROUP BY R.MAIL_ID
                                                     UNION ALL
                                                     SELECT R.MAIL_ID, 0,
                                                            CASE
                                                                WHEN MIN(R.RECIPIENT_NAME) IS NULL THEN MIN(R.EMAIL)
                                                                ELSE MIN(R.RECIPIENT_NAME)
                                                            END AS USER_NAME
                                                     FROM   <include refid="tableRecipient" /> R
                                                     GROUP BY MAIL_ID) AA
                                             GROUP BY MAIL_ID) C
                                ON A.MAIL_ID = C.MAIL_ID
                        <trim prefix="WHERE" prefixOverrides="AND|OR">
                            <if test="@com.samsung.SdlComparator@isNotEmpty(startDate) and @com.samsung.SdlComparator@isNotEmpty(endDate)">
                            TO_CHAR(A.SEND_DATETIME, 'YYYY-MM-DD') <![CDATA[ >= ]]> #{startDate}
                            AND TO_CHAR(A.SEND_DATETIME, 'YYYY-MM-DD') <![CDATA[ <= ]]> #{endDate}
                            </if>
                            <if test="@com.samsung.SdlComparator@isNotEmpty(title)">
                            AND LOWER(A.SUBJECT) LIKE '%' || LOWER(#{title}) || '%'
                            </if>
                            <if test="@com.samsung.SdlComparator@isNotEmpty(senderId)">
                            AND A.SENDER_ID = #{senderId}
                            </if>
                            <if test="@com.samsung.SdlComparator@isNotEmpty(senderName)">
                            AND LOWER(A.SENDER_NAME) LIKE '%' || LOWER(#{senderName}) || '%'
                            </if>
                        </trim>) T1 ) T
        WHERE PAGE = #{pageIndex}
    </select>

    <select id="countSentMailHistoryList" parameterType="java.util.HashMap" resultType="int">
        SELECT COUNT(*)
        FROM   (SELECT A.MAIL_ID, A.SUBJECT,
                       A.CONTENT_TYPE, A.DOC_SECU_TYPE,
                       A.SENDER_EMAIL, A.SENDER_ID, A.SENDER_NAME,
                       A.SENDER_POSITION, A.SENDER_DEPARTMENT, A.SENDER_COMPANY,
                       A.SEND_DATETIME, C.RECIPENTS
                FROM   <include refid="tableMail" /> A
                    LEFT OUTER JOIN (SELECT MAIL_ID,
                                            (MAX(USER_NAME) || ' 외 ' || SUM(pcont) || '명') AS RECIPENTS
                                     FROM   (SELECT R.MAIL_ID, (COUNT(*) - 1) PCONT,
                                                    NULL USER_NAME
                                             FROM   <include refid="tableRecipient" /> R
                                             GROUP BY R.MAIL_ID
                                             UNION ALL
                                             SELECT R.MAIL_ID, 0,
                                                    CASE
                                                        WHEN MIN(R.RECIPIENT_NAME) IS NULL THEN MIN(R.EMAIL)
                                                        ELSE MIN(R.RECIPIENT_NAME)
                                                    END AS USER_NAME
                                             FROM   <include refid="tableRecipient" /> R
                                             GROUP BY MAIL_ID) AA
                                     GROUP BY MAIL_ID) C
                        ON A.MAIL_ID = C.MAIL_ID
                <trim prefix="WHERE" prefixOverrides="AND|OR">
                    <if test="@com.samsung.SdlComparator@isNotEmpty(startDate) and @com.samsung.SdlComparator@isNotEmpty(endDate)">
                    TO_CHAR(A.SEND_DATETIME, 'YYYY-MM-DD') <![CDATA[ >= ]]> #{startDate}
                    AND TO_CHAR(A.SEND_DATETIME, 'YYYY-MM-DD') <![CDATA[ <= ]]> #{endDate}
                    </if>
                    <if test="@com.samsung.SdlComparator@isNotEmpty(title)">
                    AND LOWER(A.SUBJECT) LIKE '%' || LOWER(#{title}) || '%'
                    </if>
                    <if test="@com.samsung.SdlComparator@isNotEmpty(senderId)">
                    AND A.SENDER_ID = #{senderId}
                    </if>
                    <if test="@com.samsung.SdlComparator@isNotEmpty(senderName)">
                    AND LOWER(A.SENDER_NAME) LIKE '%' || LOWER(#{senderName}) || '%'
                    </if>
                </trim>) T1
    </select>

</mapper>