<?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="nlib.bbs.service.impl.QnaDAO">

    <sql id="search_condition">
        <![CDATA[
        FROM BD_NLIB_QNA S
        WHERE S.USE_YN = 'Y'
        AND S.BD_TYPE = 'QNA'
        ]]>
        
        <if test='searchType != null and searchType.equals("T")'>
            <if test="searchKeyword != null and !searchKeyword.equals('')">
            AND S.TITLE LIKE CONCAT('%', #{escapeSearchKeyword}, '%')
            </if>
        </if>
        <if test='searchType != null and searchType.equals("C")'>
            <if test="searchKeyword != null and !searchKeyword.equals('')">
            AND CONCAT(S.CONTENT, S.ANSWER) LIKE CONCAT('%', #{escapeSearchKeyword}, '%')
            </if>
        </if>
        <if test='searchMbInfoId != null and !searchMbInfoId.equals("")'>
            AND S.REG_ID = #{searchMbInfoId}
        </if>
        
    </sql>
    
 	<select id="listArticles" parameterType="ArticleVO" resultType="ArticleVO">
        SELECT 
              A.QNA_ID AS ARTICLE_ID
            , A.MNG_ORG_CD
            , B.DEPT_NM AS MNG_ORG_NM
            , A.QUESTION_TYPE
            , A.TITLE
            , A.ANSWER_YN
            , A.USE_YN
            , IFNULL(A.SECRET_YN, 'N') AS SECRET_YN
            , A.VIEW_CNT
            , CASE WHEN A.BD_ATTACH_FILE_ID IS NULL OR A.BD_ATTACH_FILE_ID = '' THEN 'N' ELSE 'Y' END AS ATTACH_YN
            , DATE_FORMAT(A.REG_DD, '%Y-%m-%d') AS REG_DD
            , A2.RNO 
            , C.USER_NM AS REG_NM
            , CASE
                  WHEN #{loginedMbInfoId} IS NULL OR #{loginedMbInfoId} = '' THEN 'N'
                  WHEN A.REG_ID = #{loginedMbInfoId} THEN 'Y'
                  ELSE 'N'
              END AS MY_QNA_YN
        FROM BD_NLIB_QNA A
        INNER JOIN (
            SELECT QNA_ID, RNO
            FROM (
	            SELECT S.QNA_ID
	                 , ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY S.REG_DD, S.TITLE) AS RNO
	                 , ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY S.REG_DD DESC, S.TITLE DESC) AS ONO
	            <include refid="search_condition" />
            ) S2
            WHERE ONO BETWEEN (#{pageStartRowNum} + 1) AND (#{pageStartRowNum} + #{pageSize})
        ) AS A2
        USING(QNA_ID)
        JOIN SM_DEPT B
            ON B.ORG_CD = A.MNG_ORG_CD
        LEFT OUTER JOIN ( 
            SELECT USER_ID
                 , USER_NM
            FROM SM_USER
            UNION ALL 
            SELECT MB_INFO_ID AS USER_ID
                 , NAME AS USER_NM
            FROM MB_INFO ) C
            ON A.REG_ID = C.USER_ID   
        ORDER BY RNO DESC
    </select>
 	
    <select id="countArticles" parameterType="ArticleVO" resultType="Integer">
        SELECT COUNT(1)
        <include refid="search_condition" />
    </select>
 	
    <select id="selectArticle" parameterType="ArticleVO" resultType="ArticleVO">
        <![CDATA[
        SELECT 
              A.QNA_ID AS ARTICLE_ID
            , A.MNG_ORG_CD
            , B.DEPT_NM AS MNG_ORG_NM
            , A.QUESTION_TYPE
            , A.TITLE
            , A.CONTENT 
            , A.ANSWER_YN
            , A.ANSWER
            , A.USE_YN
            , IFNULL(A.SECRET_YN, 'N') AS SECRET_YN
            , A.VIEW_CNT
            , A.BD_ATTACH_FILE_ID
            , CASE WHEN A.BD_ATTACH_FILE_ID IS NULL OR A.BD_ATTACH_FILE_ID = '' THEN 'N' ELSE 'Y' END AS ATTACH_YN
            , DATE_FORMAT(A.REG_DD, '%Y-%m-%d') AS REG_DD
            , C.USER_NM AS REG_NM
            , CASE
                  WHEN #{loginedMbInfoId} IS NULL OR #{loginedMbInfoId} = '' THEN 'N'
                  WHEN A.REG_ID = #{loginedMbInfoId} THEN 'Y'
                  ELSE 'N'
              END AS MY_QNA_YN
        FROM BD_NLIB_QNA A
        LEFT OUTER JOIN SM_DEPT B
            ON B.ORG_CD = A.MNG_ORG_CD
        LEFT OUTER JOIN ( 
            SELECT USER_ID
                 , USER_NM
            FROM SM_USER
            UNION ALL 
            SELECT MB_INFO_ID AS USER_ID
                 , NAME AS USER_NM
            FROM MB_INFO ) C
            ON A.REG_ID = C.USER_ID   
        WHERE A.QNA_ID = #{articleId}
        ORDER BY A.REG_DD DESC
        ]]>
    </select>
    
    <update id="updateRead" parameterType="ArticleVO">
        <![CDATA[
        UPDATE BD_NLIB_QNA 
        SET VIEW_CNT = VIEW_CNT + 1
        WHERE QNA_ID = #{articleId}
        AND BD_TYPE = 'QNA'
        AND USE_YN = 'Y'
        ]]>
    </update>
    
    <insert id="insertArticle" parameterType="ArticleVO">
        <![CDATA[
        INSERT INTO BD_NLIB_QNA (
              QNA_ID
            , MNG_ORG_CD
            , BD_TYPE
            , QUESTION_TYPE
            , TITLE
            , CONTENT
            , ANSWER_YN
            , ANSWER
            , EMAIL_RECV_YN
            , SECRET_YN
            , USE_YN
            , VIEW_CNT
            , BD_ATTACH_FILE_ID
            , REG_ID
            , REG_DD
        )
        VALUES (
              #{articleId}
            , #{mngOrgCd}
            , 'QNA'
            , NULL
            , #{title}
            , #{content}
            , 'N'
            , NULL
            , #{emailRecvYn}
            , #{secretYn}
            , 'Y'
            , 0
            , #{bdAttachFileId}
            , #{regId}
            , NOW()
        )
        ]]>
    </insert>
    
    <update id="updateArticle" parameterType="ArticleVO">
        <![CDATA[
        /* QNA : updateArticle : 삭제되지 않은 건 중 답변이 아직 없는 건에 한하여 수정 가능 */ 
        UPDATE BD_NLIB_QNA
        SET TITLE = #{title}
          , CONTENT = #{content}
          , EMAIL_RECV_YN = #{emailRecvYn}
          , SECRET_YN = #{secretYn}
          , BD_ATTACH_FILE_ID = #{bdAttachFileId}
          , MOD_ID = #{modId}
          , MOD_DD = NOW()
        WHERE QNA_ID = #{articleId}
        AND BD_TYPE = 'QNA'
        AND ANSWER_YN = 'N' 
        AND USE_YN = 'Y'
        ]]>
    </update>
    
    <update id="deleteArticle" parameterType="ArticleVO">
        <![CDATA[
        /* QNA : deleteArticle : 삭제되지 않은 건 중 답변이 아직 없는 건에 한하여 삭제 가능 */ 
        UPDATE BD_NLIB_QNA 
        SET USE_YN = 'N'
          , MOD_ID = #{modId}
          , MOD_DD = NOW()
        WHERE QNA_ID = #{articleId}
        AND BD_TYPE = 'QNA'
        AND ANSWER_YN = 'N' 
        AND USE_YN = 'Y'
        ]]>
    </update>
 	
</mapper>