<?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.cmm.service.impl.MainDAO">

    <!-- 추천키워드 목록 조회  -->
 	<select id="listKeywords" parameterType="String" resultType="EgovMap">
        <![CDATA[ 	  
		SELECT T.KEY_NAME, COUNT(KEY_NAME) AS CNT 
        FROM (
            SELECT          
                 TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(replace(RM.KEYWORD,',',';'), ';', NUMBERS.N), ';', -1)) AS KEY_NAME    
            FROM ( /* 키워드 길이 1000 이므로, 최대 가능 키워드수 500으로 맞춤 */ 
                 WITH RECURSIVE W1 AS (
	                 SELECT 1 AS N
	                 UNION ALL
	                 SELECT 1+W1.N AS N
	                 FROM W1
	                 WHERE W1.N < 500
                 )
                 SELECT N FROM W1
            ) NUMBERS       
            INNER JOIN RG_MASTER RM
            ON CHAR_LENGTH(RM.KEYWORD)-CHAR_LENGTH(REPLACE(replace(RM.KEYWORD,',',';'), ';', '')) >= NUMBERS.N-1 
	            AND RM.USE_FLAG = 'Y'
	            AND RM.MNG_ORG_CD = #{mngOrgCd} 
	            AND RM.OPEN_DIV_CD != '3'
	            AND RM.REG_STATUS = 'REG_OK'
	            AND RM.OPER_READ_RANGE_CD != '03' /* 전체열람, 목록열람으로 한정 : 열람불가 제외 */  
        ) T
        GROUP BY T.KEY_NAME
        ORDER BY CNT DESC, T.KEY_NAME
        LIMIT 30
		]]>
 	</select>
 	
    <!-- 문화원 PICK -->
    <select id="listOrgPicks" parameterType="String" resultType="CollectionVO">
        <![CDATA[     
		SELECT 
              MASTER_ID
		    , TITLE
		    , TYPE_DIV_NM
		    , RPRS_THUMB_URL
		FROM (
			(/* 문화원 PICK한 자료 */
			SELECT 
			    1 AS RNK 
			  , M1.MASTER_ID
			  , M1.TITLE
			  , M1.TYPE_DIV_CD
			  , TDC.S_CODE_NM AS TYPE_DIV_NM
			  , M1.RPRS_THUMB_URL 
			  , M1.REG_DD
			  , M1.MOD_DD
			FROM BD_NLIB_RECOM_MASTER R
			INNER JOIN RG_MASTER M1 
			ON R.MASTER_ID = M1.MASTER_ID 
			JOIN SM_CODE_S TDC ON M1.TYPE_DIV_CD = TDC.S_CODE_ID AND TDC.L_CODE_ID = 'MASTER_TYPE_DIV_CD'
			WHERE R.MNG_ORG_CD = #{mngOrgCd} 
            AND M1.OPEN_DIV_CD = '1'             /* 공개구분 : 공개 */
            AND M1.REG_STATUS = 'REG_OK'         /* 등록상태 : 등록완료 */
            AND M1.USE_FLAG = 'Y'
            ORDER BY M1.MOD_DD DESC, M1.REG_DD DESC
            LIMIT 10)
			UNION  
			(/* 문화원 PICK한 자료가 없거나 충분치 않은 경우, 대출/열람/온라인 가능 자료 중 최근 변경된 자료로 대체 */ 
			SELECT 
			    2 AS RNK 
			  , M2.MASTER_ID
			  , M2.TITLE
			  , M2.TYPE_DIV_CD
			  , TDC.S_CODE_NM AS TYPE_DIV_NM
			  , M2.RPRS_THUMB_URL 
              , M2.REG_DD
              , M2.MOD_DD
			FROM RG_MASTER M2 
			JOIN SM_CODE_S TDC ON M2.TYPE_DIV_CD = TDC.S_CODE_ID AND TDC.L_CODE_ID = 'MASTER_TYPE_DIV_CD'
			WHERE M2.MNG_ORG_CD = #{mngOrgCd} 
            AND M2.USE_FLAG = 'Y'
			AND M2.OPEN_DIV_CD != '3'               /* 공개구분 : 공개, 부분공개 */
			AND M2.REG_STATUS = 'REG_OK'            /* 등록상태 : 등록완료 */
			AND (M2.OPER_OUT_RANGE_CD = '01'        /* 대출구분 : 대출가능 */ 
			    OR M2.OPER_READ_RANGE_CD = '01'     /* 열람 : 전체열람 */
                OR M2.DIGITAL_READ_YN = 'Y')        /* 디지탈열람여부 */ 
			AND IFNULL(M2.PE_STATUS, '') = ''    /* 보존상태 */ 
			ORDER BY M2.MOD_DD DESC, M2.REG_DD DESC 
			LIMIT 10 )
		) T 
		ORDER BY RNK, T.MOD_DD DESC, T.REG_DD DESC  
		LIMIT 10
        ]]>
    </select>
     
    <!-- 유형별인기자료 -->
    <select id="listPops" parameterType="CollectionVO" resultType="CollectionVO">
        <![CDATA[   
        /* listPops */   
        SELECT 
            M.MASTER_ID
          , M.TITLE
          , M.TYPE_DIV_CD
          , C1.S_CODE_NM AS TYPE_DIV_NM 
          , M.DTLS_TYPE_DIV_CD
          , C2.S_CODE_NM DTLS_TYPE_DIV_NM
          , M.SUBJECT_CODE 
          , CONCAT(
              NVL((
               SELECT CONCAT(CLSF_NM, ' > ') 
               FROM CT_CLSF
               WHERE CLSF_ID = (
                   SELECT UP_CLSF_ID FROM CT_CLSF
                   WHERE CLSF_ID = CAST(M.SUBJECT_CODE AS CHAR(35)) )
               AND UP_CLSF_ID = '2') , '')
               , (SELECT CLSF_NM FROM CT_CLSF WHERE CLSF_ID = CAST(M.SUBJECT_CODE AS CHAR(35)))
            ) AS SUBJECT_NM
          , M.MOD_DD 
          , M.ORG_NM
          , M.CREAT_YYYY
        FROM RG_MASTER M 
        INNER JOIN (
            /* 자료 상세조회 이력 현황 */ 
	        SELECT A.MASTER_ID , COUNT(1) AS CNT 
	        FROM MB_VIEW_LOG A
	        INNER JOIN RG_MASTER B ON (A.MASTER_ID = B.MASTER_ID)
	        WHERE A.MNG_ORG_CD = #{mngOrgCd}
	        GROUP BY A.MASTER_ID 
            ORDER BY COUNT(1) DESC) M2 ON (M.MASTER_ID = M2.MASTER_ID)        
        LEFT OUTER JOIN SM_CODE_S C1 ON M.TYPE_DIV_CD = C1.S_CODE_ID AND C1.L_CODE_ID = 'MASTER_TYPE_DIV_CD'
        LEFT OUTER JOIN SM_CODE_S C2 ON M.DTLS_TYPE_DIV_CD = C2.S_CODE_ID AND C2.L_CODE_ID = 'DTLS_TYPE_DIV_CD'
		WHERE M.MNG_ORG_CD = #{mngOrgCd}
		AND M.TYPE_DIV_CD = #{typeDivCd}
        AND M.USE_FLAG = 'Y'
		AND M.OPEN_DIV_CD != '3'            /* 공개구분 : 공개, 부분공개 */
		AND M.REG_STATUS = 'REG_OK'         /* 등록상태 : 등록완료 */
		ORDER BY M2.CNT DESC, M.MOD_DD DESC, M.REG_DD DESC  
		LIMIT 4
        ]]>
    </select>
          
    <!-- 온라인자료관 -->
    <select id="listOnline" parameterType="String" resultType="CollectionVO">
        <![CDATA[     
		SELECT 
		    M.MASTER_ID
		  , M.TITLE
		  , M.TYPE_DIV_CD
		  , C1.S_CODE_NM AS TYPE_DIV_NM 
		  , M.DTLS_TYPE_DIV_CD
		  , C2.S_CODE_NM DTLS_TYPE_DIV_NM
		  , M.SUBJECT_CODE 
          , (  SELECT CLSF_NM 
               FROM CT_CLSF
               WHERE CLSF_ID = (
                   SELECT UP_CLSF_ID FROM CT_CLSF
                   WHERE CLSF_ID = CAST(M.SUBJECT_CODE AS CHAR(35)) )
               AND UP_CLSF_ID = '2') AS SUBJECT_NM_UP
          , (SELECT CLSF_NM FROM CT_CLSF WHERE CLSF_ID = CAST(M.SUBJECT_CODE AS CHAR(35))) AS SUBJECT_NM
		  , M.MOD_DD 
          , M.ORG_NM
          , M.CREAT_YYYY
          , M.RPRS_THUMB_URL
          , IFNULL(M.M_INTERFACE_ID, '') AS INTERFACE_ID
		FROM RG_MASTER M 
		LEFT OUTER JOIN SM_CODE_S C1 ON M.TYPE_DIV_CD = C1.S_CODE_ID AND C1.L_CODE_ID = 'MASTER_TYPE_DIV_CD'
		LEFT OUTER JOIN SM_CODE_S C2 ON M.DTLS_TYPE_DIV_CD = C2.S_CODE_ID AND C2.L_CODE_ID = 'DTLS_TYPE_DIV_CD'
		WHERE M.MNG_ORG_CD = #{mngOrgCd}
        AND M.USE_FLAG = 'Y'
		AND M.OPEN_DIV_CD != '3'               /* 공개구분 : 공개 */
		AND M.REG_STATUS = 'REG_OK'            /* 등록상태 : 등록완료 */
		AND IFNULL(M.PE_STATUS, '') = ''       /* 보존상태 */ 
		AND M.DIGITAL_READ_YN = 'Y'            /* 디지탈열람여부 */ 
		AND IFNULL(M.M_INTERFACE_ID, '') != '' /* IF존재하는 건 */
		AND IFNULL(M.RPRS_THUMB_URL, '') != '' /* 썸네일 존재하는 자료에 한정 : 2021.11.15 엑티버 김혜준대리 내부근무 과장님통해 요청 */
		ORDER BY M.MOD_DD DESC, M.REG_DD DESC
		LIMIT 10
        ]]>
    </select>

</mapper>