<?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.FaqDAO">

    <sql id="faq_search_condition">
        <![CDATA[
        FROM BD_NLIB_QNA S
        WHERE S.MNG_ORG_CD IN (#{mngOrgCd}, 'KCCF')  /* 문화원별 표출 조건 : 해당 문화원이거나 KCCF인 것으로 한정 : 2021.10.20 김진영차장님 요청 */
        AND IFNULL(S.USE_YN,'Y') = 'Y'
        AND IFNULL(S.ANSWER_YN,'N') = 'Y'
        AND S.BD_TYPE = 'FAQ'
        ]]>
        
        <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(S.CONTENT,''), IFNULL(S.ANSWER,'')) LIKE CONCAT('%', #{escapeSearchKeyword}, '%')
            </if>
        </if>
        <if test='searchQuestionType != null and !searchQuestionType.equals("")'>
            AND S.QUESTION_TYPE = #{searchQuestionType}
        </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
            , D.S_CODE_NM AS QUESTION_TYPE_NAME 
            , A.TITLE
            , IFNULL(A.ANSWER_YN, 'N') AS ANSWER_YN
            , IFNULL(A.USE_YN,'Y') AS USE_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 F1.USE_AT = 'Y' AND F2.USE_AT = '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
        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="faq_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 SM_USER C
            ON A.REG_ID = C.USER_ID   
        LEFT OUTER JOIN SM_CODE_S D
            ON A.QUESTION_TYPE = D.S_CODE_ID
        WHERE D.L_CODE_ID = 'NLIB_FAQ_TYPE_CD'
        AND IFNULL(D.USE_YN, 'Y') = 'Y'
        ORDER BY RNO DESC
    </select>
 	
    <select id="countArticles" parameterType="ArticleVO" resultType="Integer">
        SELECT COUNT(1)
        <include refid="faq_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
            , D.S_CODE_NM AS QUESTION_TYPE_NAME 
            , A.TITLE
            , A.CONTENT 
            , IFNULL(A.ANSWER_YN, 'N') AS ANSWER_YN
            , A.ANSWER
            , IFNULL(A.USE_YN, 'Y') AS USE_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 F1.USE_AT = 'Y' AND F2.USE_AT = 'Y'),0) > 0 THEN 'Y' ELSE 'N' END AS ATTACH_YN 
            , DATE_FORMAT(A.REG_DD, '%Y-%m-%d') AS REG_DD
            , C.USER_NM AS REG_NM
        FROM BD_NLIB_QNA A
        LEFT OUTER JOIN SM_DEPT B
            ON B.ORG_CD = A.MNG_ORG_CD
        LEFT OUTER JOIN SM_USER C
            ON A.REG_ID = C.USER_ID   
        LEFT OUTER JOIN SM_CODE_S D
            ON A.QUESTION_TYPE = D.S_CODE_ID
        WHERE A.QNA_ID = #{articleId}
        AND D.L_CODE_ID = 'NLIB_FAQ_TYPE_CD'
        AND IFNULL(D.USE_YN, 'Y') = 'Y'
        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 IFNULL(USE_YN, 'Y') = 'Y'
        ]]>
    </update>
 	
</mapper>