<?xml version="1.0" encoding="UTF-8"?><!--Converted at: Wed May 11 15:49:38 KST 2016-->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="NotificationDAO">

 	<select id="listNotifications" parameterType="NotificationVO" resultType="NotificationVO">
        <![CDATA[
        SELECT 
              A.NOTI_ID
            , A.RECV_USER_ID
            , A.TITLE
            , A.CONTENT
            , A.NOTI_METHOD
            , A.NOTI_TYPE_CD
            , B.S_CODE_NM AS NOTI_TYPE_NM
            , IF(A.READ_DD IS NULL, 'N', 'Y') AS READ_YN
            , DATE_FORMAT(A.READ_DD, '%Y-%m-%d') AS READ_DD
            , DATE_FORMAT(A.REG_DD, '%Y-%m-%d') AS REG_DD
            , COUNT(A.NOTI_ID) OVER(PARTITION BY 1) AS TOT_CNT 
            , A2.RNO
            , A.MNG_ORG_CD
            , C.DEPT_NM AS MNG_ORG_NM
        FROM SM_NOTIFICATION A
        INNER JOIN (
            SELECT S.NOTI_ID
                 , ROW_NUMBER() OVER() AS RNO
            FROM SM_NOTIFICATION S
            WHERE S.RECV_USER_ID = #{recvUserId}
            AND S.REG_DD > DATE_SUB(NOW(), INTERVAL 1 MONTH)
            ORDER BY S.NOTI_ID DESC
            LIMIT #{pageStartRowNum}, #{pageSize}
        ) AS A2
        USING(NOTI_ID)
        JOIN SM_CODE_S B
        ON B.S_CODE_ID = A.NOTI_TYPE_CD
        LEFT OUTER JOIN SM_DEPT C
        ON A.MNG_ORG_CD = C.ORG_CD
        WHERE 1=1 
        AND B.L_CODE_ID = 'NOTI_TYPE_CD'
        AND B.USE_YN = 'Y'
        ORDER BY A.NOTI_ID DESC
        ]]>
    </select>
 	
    <select id="countNotifications" parameterType="String" resultType="Integer">
        <![CDATA[
        SELECT COUNT(1)
        FROM SM_NOTIFICATION A
        WHERE A.RECV_USER_ID = #{recvUserId}
        AND A.REG_DD > DATE_SUB(NOW(), INTERVAL 1 MONTH)
        ]]>
    </select>
 	
 	<select id="countUnreadNotification" parameterType="String" resultType="Integer">
        <![CDATA[
        SELECT COUNT(NOTI_ID) AS UNREAD_CNT 
        FROM SM_NOTIFICATION A 
        WHERE A.RECV_USER_ID = #{recvUserId}
        AND A.READ_DD IS NULL
        ]]>
    </select>
    
    <select id="selectNotification" parameterType="NotificationVO" resultType="NotificationVO">
        <![CDATA[
        SELECT A.*
             , IF(A.READ_DD IS NULL, 'N', 'Y') AS READ_YN
             , B.DEPT_NM AS MNG_ORG_NM
        FROM SM_NOTIFICATION A 
        LEFT OUTER JOIN SM_DEPT B
        ON A.MNG_ORG_CD = B.ORG_CD
        WHERE A.RECV_USER_ID = #{recvUserId}
        AND A.NOTI_ID = #{notiId}
        ]]>
    </select>
    
    <update id="updateRead" parameterType="NotificationVO">
        <![CDATA[
        UPDATE SM_NOTIFICATION 
        SET READ_DD = NOW()
        WHERE RECV_USER_ID = #{recvUserId}
        AND NOTI_ID = #{notiId}
        AND READ_DD IS NULL
        ]]>
    </update>
 	
</mapper>