<?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="qna_search_condition">
        <![CDATA[
        FROM BD_NLIB_QNA S
        WHERE IFNULL(S.USE_YN, 'Y') = 'Y'
        AND S.MNG_ORG_CD = #{mngOrgCd} /* 문화원별 표출 조건 : 해당 문화원건만 표출 : 2021.10.20 김진영차장님 요청 */
        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(IFNULL(CONVERT(S.CONTENT_LBL USING UTF8),''), IFNULL(CONVERT(S.ANSWER_LBL USING UTF8),'')) 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
            , IFNULL(A.ANSWER_YN, 'N') AS ANSWER_YN
            , IFNULL(A.USE_YN, 'Y') AS USE_YN
            , IFNULL(A.SECRET_YN, 'N') AS SECRET_YN
            , IFNULL(A.EMAIL_RECV_YN, 'N') AS EMAIL_RECV_YN
            , A.VIEW_CNT
            , CASE WHEN IFNULL((SELECT COUNT(1) FROM BD_FILE F1 LEFT OUTER JOIN BD_FILEDETAIL F2 ON F1.ATTACH_FILE_ID = F2.ATTACH_FILE_ID  WHERE F1.ATTACH_FILE_ID = A.BD_ATTACH_FILE_ID AND IFNULL(F1.USE_AT,'Y') = 'Y' AND IFNULL(F2.USE_AT,'Y') = 'Y'),0) > 0 THEN 'Y' ELSE 'N' 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="qna_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="qna_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
            , CONVERT(A.CONTENT_LBL USING UTF8) AS CONTENT
            , IFNULL(A.ANSWER_YN, 'N') AS ANSWER_YN
            , IFNULL(A.EMAIL_RECV_YN, 'N') AS EMAIL_RECV_YN
            , CONVERT(A.ANSWER_LBL USING UTF8) AS ANSWER
            , IFNULL(A.USE_YN, 'Y') AS USE_YN
            , IFNULL(A.SECRET_YN, 'N') AS SECRET_YN
            , A.VIEW_CNT
            , A.BD_ATTACH_FILE_ID
            , CASE WHEN IFNULL((SELECT COUNT(1) FROM BD_FILE F1 LEFT OUTER JOIN BD_FILEDETAIL F2 ON F1.ATTACH_FILE_ID = F2.ATTACH_FILE_ID  WHERE F1.ATTACH_FILE_ID = A.BD_ATTACH_FILE_ID AND IFNULL(F1.USE_AT,'Y') = 'Y' AND IFNULL(F2.USE_AT,'Y') = 'Y'),0) > 0 THEN 'Y' ELSE 'N' END AS ATTACH_YN 
            , DATE_FORMAT(A.REG_DD, '%Y-%m-%d') AS REG_DD
            , DATE_FORMAT(A.MOD_DD, '%Y-%m-%d') AS MOD_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}
        AND IFNULL(A.USE_YN, 'Y') = 'Y'
        ]]>
    </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 IFNULL(USE_YN, 'Y') = 'Y'
        ]]>
    </update>
    
    <insert id="insertArticle" parameterType="ArticleVO">
        <![CDATA[
        INSERT INTO BD_NLIB_QNA (
              QNA_ID
            , MNG_ORG_CD
            , BD_TYPE
            , QUESTION_TYPE
            , TITLE
            , CONTENT_LBL
            , ANSWER_YN
            , ANSWER_LBL
            , 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_LBL = #{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 REG_ID = #{modId} /* 본인글만 삭제 */ 
        AND IFNULL(ANSWER_YN, 'N') = 'N' 
        AND IFNULL(USE_YN, 'Y') = '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 IFNULL(ANSWER_YN, 'N') = 'N' 
        AND IFNULL(USE_YN, 'Y') = 'Y'
        ]]>
    </update>
 	
</mapper>