<?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="InterestDAO">
    
    <select id="listInterests" parameterType="CollectionVO" resultType="CollectionVO">
    /* CollectionDAO.listInterests 관심자료 목록조회 */
        SELECT 
             MI.MB_INTEREST_ID
            ,RM.MASTER_ID 
            ,RM.TITLE
            ,IFNULL(RM.RPRS_THUMB_URL,"") RPRS_THUMB_URL
            ,IFNULL(SD.DEPT_NM,"") MNG_ORG_NM
           FROM
               RG_MASTER RM 
            INNER JOIN 
               MB_INTEREST MI
             ON RM.MASTER_ID =MI.MASTER_ID
            LEFT OUTER JOIN
                SM_DEPT SD
             ON SD.ORG_CD = RM.MNG_ORG_CD 
             WHERE 1=1
             AND MI.MB_INFO_ID = #{mbInfoId} 
             AND MI.USE_YN = "Y"
            <if test='mngOrgCd != null and !mngOrgCd.equals("")'>
             AND MNG_ORG_CD = #{mngOrgCd}
            </if>
           ORDER BY MI.MB_INTEREST_ID DESC
           LIMIT #{pageStart},#{pageSize}
    </select>
    
    <select id="countListInterests" parameterType="CollectionVO" resultType="Integer">
    /* CollectionDAO.countListInterests 관심자료 목록 개수 조회 */
        SELECT 
             COUNT(*)
           FROM
               RG_MASTER RM 
            INNER JOIN 
               MB_INTEREST MI
            ON RM.MASTER_ID =MI.MASTER_ID
             WHERE 1=1
             AND MI.MB_INFO_ID = #{mbInfoId} 
             AND USE_YN = "Y"
            <if test='mngOrgCd != null and !mngOrgCd.equals("")'>
             AND MNG_ORG_CD = #{mngOrgCd}
            </if>
    </select>
    
    <update id="deleteInterests" parameterType="CollectionVO">
    /* CollectionDAO.deleteInterests 관심자료 삭제 */
        UPDATE
            MB_INTEREST
         SET
             USE_YN="N"
            WHERE 1=1
            AND MASTER_ID = #{masterId} 
            AND MB_INFO_ID = #{mbInfoId} 
    </update>
    
    <insert id="insertInterest" parameterType="CollectionVO">
    /* CollectionDAO.insertInterest 관심자료 추가 */
        <selectKey resultType="Integer" keyProperty="mbInterestId" order="BEFORE">
            SELECT MAX(MB_INTEREST_ID)+1 FROM MB_INTEREST;      
        </selectKey> 
        INSERT INTO 
                MB_INTEREST
            VALUES
                (#{mbInterestId},#{mbInfoId},#{masterId},NOW(),"Y");
    </insert>
    
    <select id="countInterest" parameterType="CollectionVO" resultType="Integer">
    /* CollectionDAO.countInterest 관심자료 count(중복확인) */
        SELECT 
            COUNT(*)
          FROM
               RG_MASTER RM 
            INNER JOIN 
               MB_INTEREST MI
            ON RM.MASTER_ID = MI.MASTER_ID
           WHERE 1=1
              AND MI.MASTER_ID = #{masterId}
              AND MI.MB_INFO_ID = #{mbInfoId}
              AND MI.USE_YN = "Y"
    </select>
    
    <select id="selectOrgList" parameterType="CollectionVO" resultType="DeptVO" >
    /* InterestDAO.selectOrgList 문화원 목록 조회 */
       <![CDATA[  
       SELECT 
              ORG_CD
             ,DEPT_NM
        FROM
            SM_DEPT SD
            INNER JOIN
            RG_MASTER RM 
            ON SD.ORG_CD = RM.MNG_ORG_CD 
            INNER JOIN 
            MB_INTEREST MI 
            ON RM.MASTER_ID = MI.MASTER_ID 
          WHERE 1=1
           AND SD.USE_YN = "Y"
           AND SD.CLOSE_FLAG = "0"
           AND IFNULL(NULLIF(SD.APPLY_START_YMD,''),"99999999") < date_format(NOW(), '%Y%m%d')
           AND IFNULL(NULLIF(SD.APPLY_CLOSE_YMD,''),"99999999") > date_format(NOW(), '%Y%m%d')
           AND MI.USE_YN = "Y"
           AND MI.MB_INFO_ID = #{mbInfoId}
           GROUP BY ORG_CD,DEPT_NM
     ]]>
    </select>
</mapper>