<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="file">

	<resultMap id="fileMap"  type="file" >
		<result property="fileId" column="FILE_ID"/>
		<result property="fileName" column="FILE_NAME"/>
		<result property="filePathName" column="FILE_PATH_NAME"/>
		<result property="fileSize" column="FILE_SIZE"/>
		<result property="ownerObjectPkId" column="OWNER_OBJECT_PK_ID"/>
		<result property="fileExtensionName" column="FILE_EXTENSION_NAME"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="fromDate" column="FROM_DATE"/>
		<result property="thruDate" column="THRU_DATE"/>
		<result property="firstRegDatetime" column="FIRST_REG_DATETIME"/>
		<result property="firstRegrId" column="FIRST_REGR_ID"/>
		<result property="lastModDatetime" column="LAST_MOD_DATETIME"/>
		<result property="lastModrId" column="LAST_MODR_ID"/>
		<result property="fileMimeTypeName" column="FILE_MIME_TYPE_NAME"/>
		<result property="deleted" column="DELETE_YN"/>
		<result property="downloadType" column="DOWNLOAD_TYPE"/>
		<result property="orderIdx" column="ORDER_IDX"/>
	</resultMap> 
	
	<sql id="tableAttachFile">TN_CF_ATTACH_FILE</sql>
	
	<select id="selectFileInfo" resultMap="fileMap">
		SELECT FILE_ID, FILE_NAME, FILE_PATH_NAME, FILE_SIZE, OWNER_OBJECT_PK_ID, FILE_EXTENSION_NAME,
		       DESCRIPTION, FROM_DATE, THRU_DATE, FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME,
		       LAST_MODR_ID, FILE_MIME_TYPE_NAME, DELETE_YN, DOWNLOAD_TYPE, ORDER_IDX
		FROM   <include refid="tableAttachFile" />
		WHERE  (DELETE_YN IS NULL OR DELETE_YN !='1')
		<if test="@com.samsung.SdlComparator@isNotEmpty(fileId)">
		AND    FILE_ID = #{fileId}
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(refId)">
		AND    OWNER_OBJECT_PK_ID = #{refId}
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(downloadType)">
		AND    DOWNLOAD_TYPE = #{downloadType}
		</if>
		ORDER BY ORDER_IDX ASC
	</select>
	
	<select id="selectDeleteFileInfo" parameterType="fileManagerSearchDto" resultMap="fileMap">
        SELECT *
        FROM   (SELECT ROW_NUMBER() OVER (<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>
                                          <if test="@com.samsung.SdlComparator@isEmpty(orderBy)">ORDER BY FIRST_REG_DATETIME DESC, FIRST_REGR_ID, FILE_NAME, FILE_ID</if>) RNUM,
					   FILE_ID, FILE_NAME, FILE_PATH_NAME, FILE_SIZE, OWNER_OBJECT_PK_ID, FILE_EXTENSION_NAME,
					   DESCRIPTION, FROM_DATE, THRU_DATE, FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME,
					   LAST_MODR_ID, FILE_MIME_TYPE_NAME, DELETE_YN, DOWNLOAD_TYPE, ORDER_IDX
				FROM   <include refid="tableAttachFile" />
				WHERE    DELETE_YN = '1'
				<if test="@com.samsung.SdlComparator@isNotEmpty(fileName)">
				AND    FILE_NAME LIKE '%' || #{fileName} || '%'
				</if>
				AND    CONVERT(VARCHAR(8), FIRST_REG_DATETIME, 112) BETWEEN #{startDate} AND #{endDate} ) T
        WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}        
    </select>
    
    <select id="countDeleteFile"  parameterType="fileManagerSearchDto" resultType="int">
    	SELECT COUNT(*) AS TOTAL
        FROM   <include refid="tableAttachFile" />
        WHERE    DELETE_YN = '1'
		<if test="@com.samsung.SdlComparator@isNotEmpty(fileName)">
		AND    FILE_NAME LIKE '%' || #{fileName} || '%'
		</if>
        AND    CONVERT(VARCHAR(8), FIRST_REG_DATETIME, 112) BETWEEN #{startDate} AND #{endDate}
    </select>

	<insert id="insertAttachFile" parameterType="file">
		INSERT INTO <include refid="tableAttachFile" />
		(FILE_ID, FILE_NAME, FILE_SIZE, FILE_PATH_NAME,
	     OWNER_OBJECT_PK_ID, FILE_EXTENSION_NAME,
	     FIRST_REG_DATETIME, FIRST_REGR_ID, FILE_MIME_TYPE_NAME,
	     DELETE_YN, DOWNLOAD_TYPE, ORDER_IDX)
		VALUES
		(#{fileId}, #{fileName}, #{fileSize}, #{filePathName},
		 #{ownerObjectPkId}, #{fileExtensionName},
		 #{firstRegDatetime}, #{firstRegrId}, #{fileMimeTypeName},
	     '0', #{downloadType}, #{orderIdx})
	</insert>

	<update id="updateFileRefInfo" parameterType="java.util.HashMap">
		UPDATE <include refid="tableAttachFile" />
		SET    OWNER_OBJECT_PK_ID = #{ownerObjectPkId},
		   	   ORDER_IDX = #{orderIdx}
		WHERE  FILE_ID = #{fileId}
		<if test="@com.samsung.SdlComparator@isNotEmpty(downloadType)" >
		AND    DOWNLOAD_TYPE = #{downloadType}
		</if>
	</update>

	<update id="updateDeleteFileInfo" parameterType="java.util.HashMap">
		UPDATE <include refid="tableAttachFile" />
		SET    DELETE_YN='1'
		WHERE  OWNER_OBJECT_PK_ID = #{ownerObjectPkId}
		<if test="@com.samsung.SdlComparator@isNotEmpty(files)">
		AND    FILE_ID NOT IN
		      <foreach item="file" index="index" collection="files" open="(" separator="," close=")">#{file.fileId}</foreach>
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(downloadType)" >
		AND    DOWNLOAD_TYPE = #{downloadType}
		</if>
	</update>
	
	<delete id="deleteFileRow">
		DELETE FROM <include refid="tableAttachFile" />
		WHERE  DELETE_YN = '1'
		AND    FILE_ID = #{fileId}
	</delete>

</mapper>