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

    <sql id="ancmnt_search_condition">
        <![CDATA[
        FROM BD_NLIB_ANCMNT S
        WHERE S.OPEN_YN = 'Y'
        AND (S.MNG_ORG_CD = #{mngOrgCd} OR (S.MNG_ORG_CD = 'KCCF' AND REG_ID = 'kadmin')) /* 공통공지사항 : 관리문화원이 KCCF이면서 등록자ID가 'kadmin'인 건으로 한정 : 2021.10.20 김진영차장님 요청 */
        AND DATE_FORMAT(NOW(), '%Y%m%d') BETWEEN IFNULL(S.POST_START_DATE,'00010101') AND IFNULL(S.POST_END_DATE, '99991231')
        ]]>
        
        <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 IFNULL(CONVERT(S.CONTENT_LBL USING UTF8),'') LIKE CONCAT('%', #{escapeSearchKeyword}, '%')
            </if>
        </if>
        AND IFNULL(S.DELETE_YN, 'N') = 'N' 
    </sql>
    
 	<select id="listArticles" parameterType="ArticleVO" resultType="ArticleVO">
        SELECT 
              A.ANCMNT_ID AS ARTICLE_ID
            , A.MNG_ORG_CD
            , B.DEPT_NM AS MNG_ORG_NM
            , A.TITLE
            , IF(IFNULL(A.NOTI_YN, 'N') = '', 'N', A.NOTI_YN) AS NOTI_YN
            , A.OPEN_YN
            , A.POST_START_DATE
            , A.POST_END_DATE
            , 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 
            , IF(IFNULL(C.USER_NM,'') = '', '관리자', C.USER_NM) AS REG_NM
        FROM BD_NLIB_ANCMNT A
        INNER JOIN (
            SELECT ANCMNT_ID, RNO
            FROM (
                SELECT S.ANCMNT_ID
                     , ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY IF(IFNULL(S.NOTI_YN, 'N') = '', 'N', S.NOTI_YN), S.REG_DD, S.TITLE, S.ANCMNT_ID) AS RNO
                     , ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY IF(IFNULL(S.NOTI_YN, 'N') = '', 'N', S.NOTI_YN) DESC, S.REG_DD DESC, S.TITLE desc, S.ANCMNT_ID DESC) AS ONO
                <include refid="ancmnt_search_condition" />
            ) S2
            WHERE ONO BETWEEN (#{pageStartRowNum} + 1) AND (#{pageStartRowNum} + #{pageSize})
        ) AS A2
        USING(ANCMNT_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
        WHERE IFNULL(A.DELETE_YN, 'N') = 'N'    
        ORDER BY RNO DESC
    </select>
 	
    <select id="countArticles" parameterType="ArticleVO" resultType="Integer">
        SELECT COUNT(1)
        <include refid="ancmnt_search_condition" />
    </select>
 	
    <select id="selectArticle" parameterType="ArticleVO" resultType="ArticleVO">
        <![CDATA[
        SELECT 
              A.ANCMNT_ID AS ARTICLE_ID
            , A.MNG_ORG_CD
            , B.DEPT_NM AS MNG_ORG_NM
            , A.TITLE
            , CONVERT(A.CONTENT_LBL USING UTF8) AS CONTENT
            , IF(IFNULL(A.NOTI_YN, 'N') = '', 'N', A.NOTI_YN) AS NOTI_YN
            , A.OPEN_YN
            , A.POST_START_DATE
            , A.POST_END_DATE
            , 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 
            , A.BD_ATTACH_FILE_ID
            , DATE_FORMAT(A.REG_DD, '%Y-%m-%d') AS REG_DD
            , IF(IFNULL(C.USER_NM,'') = '', '관리자', C.USER_NM) AS REG_NM
        FROM BD_NLIB_ANCMNT A
        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   
        WHERE A.ANCMNT_ID = #{articleId}
        AND IFNULL(A.DELETE_YN, 'N') = 'N'   
        ]]>
    </select>
    
    <update id="updateRead" parameterType="ArticleVO">
        <![CDATA[
        UPDATE BD_NLIB_ANCMNT 
        SET VIEW_CNT = VIEW_CNT + 1
        WHERE ANCMNT_ID = #{articleId}
        AND IFNULL(DELETE_YN, 'N') = 'N'
        ]]>
    </update>
 	
</mapper>