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

    <select id="listAttachFiles" parameterType="String" resultType="AttachFileVO">
        SELECT 
              B.ATTACH_FILE_ID
            , B.FILE_SN
            , B.FILE_STRE_COURS
            , B.STRE_FILE_NM
            , B.ORIGNL_FILE_NM
            , B.FILE_EXTSN
            , B.FILE_CN
            , B.FILE_SIZE
        FROM BD_FILE A
        JOIN BD_FILEDETAIL B ON (A.ATTACH_FILE_ID = B.ATTACH_FILE_ID)
        WHERE A.ATTACH_FILE_ID = #{attachFileId}
        AND IFNULL(A.USE_AT,'Y') = 'Y'
        AND IFNULL(B.USE_AT,'Y') = 'Y'
        ORDER BY B.FILE_SN
    </select>
    
    <select id="selectAttachFile" parameterType="AttachFileVO" resultType="AttachFileVO">
        SELECT 
              B.ATTACH_FILE_ID
            , B.FILE_SN
            , B.FILE_STRE_COURS
            , B.STRE_FILE_NM
            , B.ORIGNL_FILE_NM
            , B.FILE_EXTSN
            , B.FILE_CN
            , B.FILE_SIZE
        FROM BD_FILE A
        JOIN BD_FILEDETAIL B ON (A.ATTACH_FILE_ID = B.ATTACH_FILE_ID)
        WHERE A.ATTACH_FILE_ID = #{attachFileId}
        AND B.FILE_SN = #{fileSn}
        AND IFNULL(A.USE_AT,'Y') = 'Y'
        AND IFNULL(B.USE_AT,'Y') = 'Y'
    </select>
 	
    <select id="checkAttachFileDownload" parameterType="AttachFileVO" resultType="String">
		SELECT 'Y' AS checkResult 
		<if test='bdType != null and bdType.equals("QNA")'>
		    FROM BD_NLIB_QNA
		    WHERE UPPER(BD_TYPE) = 'QNA'
		    AND BD_ATTACH_FILE_ID = #{attachFileId}
		    AND (IFNULL(SECRET_YN,'N') = 'N' OR REG_ID = #{mbInfoId})
		    AND IFNULL(USE_YN,'Y') = 'Y'
		</if>
	    <if test='bdType != null and bdType.equals("FAQ")'>
		    FROM BD_NLIB_QNA
		    WHERE UPPER(BD_TYPE) = 'FAQ'
		    AND BD_ATTACH_FILE_ID = #{attachFileId}
		    AND IFNULL(USE_YN,'Y') = 'Y'
	    </if>
	    <if test='bdType != null and bdType.equals("ANCMNT")'>
			FROM BD_NLIB_ANCMNT
			WHERE BD_ATTACH_FILE_ID = #{attachFileId}
			AND OPEN_YN = 'Y'
			AND IFNULL(DELETE_YN, 'N') = 'N'
			AND DATE_FORMAT(NOW(), '%Y%m%d') BETWEEN IFNULL(POST_START_DATE,'00010101') AND IFNULL(POST_END_DATE, '99991231')
	    </if>
	    LIMIT 1 
    </select>
    
    <insert id="insertAttachFileGroup" parameterType="AttachFileVO">
        INSERT INTO BD_FILE (  
              ATTACH_FILE_ID
            , REG_DD
            , USE_AT
        ) VALUES (
              #{attachFileId}
            , NOW()
            , 'Y'
        )
        ON DUPLICATE KEY
        UPDATE USE_AT = 'Y';
    </insert>
    
    <insert id="insertAttachFile" parameterType="AttachFileVO">
        <selectKey resultType="int" keyProperty="fileSn" order="BEFORE">
            SELECT IFNULL(MAX(FILE_SN) + 1, 1)
            FROM BD_FILEDETAIL 
            WHERE ATTACH_FILE_ID = #{attachFileId}
        </selectKey>
        INSERT INTO BD_FILEDETAIL (  
              ATTACH_FILE_ID
            , FILE_SN
            , FILE_STRE_COURS
            , STRE_FILE_NM
            , ORIGNL_FILE_NM
            , FILE_EXTSN
            , FILE_CN
            , FILE_SIZE
            , USE_AT
        ) VALUES (
              #{attachFileId}
            , IFNULL(#{fileSn}, 1)
            , #{fileStreCours}
            , #{streFileNm}
            , #{orignlFileNm}
            , #{fileExtsn}
            , NULL
            , #{fileSize}
            , 'Y'
        )
    </insert>
    
    <update id="deleteAttachFileGroup" parameterType="String">
        UPDATE BD_FILE SET USE_AT = 'N'
        WHERE ATTACH_FILE_ID = #{attachFileId}
        AND IFNULL(USE_AT, 'Y') = 'Y'
    </update>  
    
    <update id="deleteAllFilesOfAttachFileGroup" parameterType="String">
        UPDATE BD_FILEDETAIL SET USE_AT = 'N'
        WHERE ATTACH_FILE_ID = #{attachFileId}
        AND IFNULL(USE_AT, 'Y') = 'Y'
    </update>  
    
    <update id="deleteAttachFile" parameterType="AttachFileVO">
        UPDATE BD_FILEDETAIL SET USE_AT = 'N'
        WHERE ATTACH_FILE_ID = #{attachFileId}
        AND FILE_SN = #{fileSn}
    </update>  
    
</mapper>
