metaMapper.xml
<?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="com.joang.filemanager.meta.metaMapper">
<!-- ########## ============================================= COMMON START
============================================= ########## -->
<!-- ### For Paging ### -->
<sql id="pagingHeader">
SELECT A.* FROM (
SELECT row_number() over() AS rownumber, A.* FROM (
</sql>
<sql id="pagingFooter">
) A
) A WHERE A.rownumber BETWEEN #{start} AND #{last}
</sql>
<!-- ### For Paging ### -->
<!-- ########## ============================================= COMMON END
============================================= ########## -->
<!-- ########## ============================================= Meta List
Select ============================================= ########## -->
<select id="selectMetaListCnt"
parameterType="com.joang.filemanager.vo.MetaVO" resultType="int">
<![CDATA[
SELECT COUNT( DISTINCT A.META ) AS total_pages
FROM TB_FILEMANAGER_META A, TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META_FILE C, TB_USERS_MAPPING D
WHERE 1=1
AND A.IDX = B.META_IDX
AND B.FILE_IDX = C.IDX
AND C.IDX = D.FILE_IDX
]]>
AND D.USERNAME = #{userId}
AND A.DEL_GB = 'N'
AND C.DEL_GB = 'N'
<!-- 전체 -->
<if
test="positiveSearchKey != null and positiveSearchKey.size != 0 ">
<foreach collection="positiveSearchKey" item="item"
open="AND (" close=")" separator="AND">
A.META LIKE CONCAT('%',#{item},'%')
</foreach>
</if>
<if
test="negativeSearchKey != null and negativeSearchKey.size != 0 ">
AND C.IDX NOT IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="negativeSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<if
test="andSearchKey != null and andSearchKey.size != 0 ">
AND C.IDX IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="andSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
</select>
<select id="selectMetaList"
parameterType="com.joang.filemanager.vo.MetaVO"
resultType="com.joang.filemanager.vo.MetaVO">
<include refid="pagingHeader"></include>
<![CDATA[
/* com.joang.filemanager.meta.metaMapper.selectMetaList */
SELECT
A.IDX,
A.META,
A.CREA_DTM,
A.CREA_ID,
COUNT( A.META ) AS COUNT_META
FROM
TB_FILEMANAGER_META A, TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META_FILE C, TB_USERS_MAPPING D
WHERE
A.DEL_GB = 'N'
AND C.DEL_GB = 'N'
AND A.IDX = B.META_IDX
AND B.FILE_IDX = C.IDX
AND C.IDX = D.FILE_IDX
]]>
AND D.USERNAME = #{userId}
<!-- 전체 -->
<if
test="positiveSearchKey != null and positiveSearchKey.size != 0 ">
<foreach collection="positiveSearchKey" item="item"
open="AND (" close=")" separator="AND">
A.META LIKE CONCAT('%',#{item},'%')
</foreach>
</if>
<if
test="negativeSearchKey != null and negativeSearchKey.size != 0 ">
AND C.IDX NOT IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="negativeSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<if
test="andSearchKey != null and andSearchKey.size != 0 ">
AND C.IDX IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="andSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<![CDATA[
GROUP BY A.IDX,
A.META,
A.CREA_DTM,
A.CREA_ID
ORDER BY A.IDX DESC
]]>
<include refid="pagingFooter"></include>
</select>
<select id="selectMetaAutocomplete"
parameterType="com.joang.filemanager.vo.MetaVO" resultType="map">
<![CDATA[
/* com.joang.filemanager.meta.metaMapper.selectMetaAutocomplete */
SELECT
A.IDX,
A.META,
A.FILE_CNT,
A.TO_CNT,
COUNT(E.FROM_IDX) AS FROM_CNT
FROM(
SELECT A.IDX,
A.META,
A.FILE_CNT,
COUNT(E.FROM_IDX) AS TO_CNT
FROM (
SELECT
A.IDX,
A.META,
COUNT( A.META ) AS FILE_CNT
FROM
TB_FILEMANAGER_META A, TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META_FILE C, TB_USERS_MAPPING D
WHERE
A.DEL_GB = 'N'
AND C.DEL_GB = 'N'
AND A.IDX = B.META_IDX
AND B.FILE_IDX = C.IDX
AND C.IDX = D.FILE_IDX
]]>
AND D.USERNAME = #{userId}
AND A.META LIKE '%'|| #{searchKey} || '%'
<![CDATA[
GROUP BY A.IDX, A.META
) A
left join TB_FILEMANAGER_META_REF E on A.IDX = E.FROM_IDX
GROUP BY A.IDX, A.META, A.FILE_CNT, E.FROM_IDX
) A
left join TB_FILEMANAGER_META_REF E on A.IDX = E.TO_IDX
GROUP BY A.IDX, A.META, A.FILE_CNT, A.TO_CNT, E.TO_IDX
ORDER BY A.IDX DESC
]]>
</select>
<select id="selectUserAutocomplete"
parameterType="com.joang.common.vo.UserVO" resultType="map">
<![CDATA[
/* com.joang.filemanager.meta.metaMapper.selectUserAutocomplete */
SELECT
tu.username
, tu.name
FROM tb_users tu
WHERE 1=1
AND ( tu.name LIKE '%'|| #{username} || '%' OR tu.username LIKE '%'|| #{username} || '%' )
AND tu.del_gb = 'N'
]]>
</select>
<select id="selectRefMetaList"
parameterType="com.joang.filemanager.vo.MetaVO"
resultType="com.joang.filemanager.vo.RefMetaVO">
<![CDATA[
/* com.joang.filemanager.meta.metaMapper.selectFromRefMetaList */
SELECT A.IDX, A.META, B.FROM_IDX, B.TO_IDX, B.META_REF
FROM TB_FILEMANAGER_META A,
(
SELECT TO_IDX AS IDX, FROM_IDX, B.TO_IDX, B.META_REF
FROM TB_FILEMANAGER_META A, TB_FILEMANAGER_META_REF B, TB_FILEMANAGER_META_FILE C
WHERE
A.DEL_GB = 'N'
AND A.IDX = C.IDX
AND C.DEL_GB = 'N'
]]>
<!-- 전체 -->
<if
test="positiveSearchKey != null and positiveSearchKey.size != 0 ">
<foreach collection="positiveSearchKey" item="item"
open="AND (" close=")" separator="OR">
A.META LIKE CONCAT('%',#{item},'%')
</foreach>
</if>
<if
test="negativeSearchKey != null and negativeSearchKey.size != 0 ">
AND C.IDX NOT IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="negativeSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<if
test="andSearchKey != null and andSearchKey.size != 0 ">
AND C.IDX IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="andSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<![CDATA[
AND A.IDX = B.FROM_IDX
UNION
SELECT FROM_IDX AS IDX, FROM_IDX, B.TO_IDX, B.META_REF
FROM TB_FILEMANAGER_META A, TB_FILEMANAGER_META_REF B, TB_FILEMANAGER_META_FILE C
WHERE
A.DEL_GB = 'N'
AND A.IDX = C.IDX
AND C.DEL_GB = 'N'
]]>
<!-- 전체 -->
<if
test="positiveSearchKey != null and positiveSearchKey.size != 0 ">
<foreach collection="positiveSearchKey" item="item"
open="AND (" close=")" separator="OR">
A.META LIKE CONCAT('%',#{item},'%')
</foreach>
</if>
<if
test="negativeSearchKey != null and negativeSearchKey.size != 0 ">
AND C.IDX NOT IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="negativeSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<if
test="andSearchKey != null and andSearchKey.size != 0 ">
AND C.IDX IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="andSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<![CDATA[
AND A.IDX = B.TO_IDX
) B
WHERE A.IDX = B.IDX
]]>
</select>
<select id="selectMetaValueByMetaIdx" parameterType="int"
resultType="string">
SELECT META FROM TB_FILEMANAGER_META
WHERE IDX = #{meta_idx}
</select>
<!-- ########## ============================================= Meta List
Select ============================================= ########## -->
<!-- ########## ============================================= File List/Detail
Select ============================================= ########## -->
<select id="selectFileListCnt"
parameterType="com.joang.filemanager.vo.FileVO" resultType="int">
<![CDATA[
SELECT COUNT( DISTINCT A.IDX ) AS TOTAL_PAGES
]]>
<choose>
<when test="searchKey != null and searchKey != '' ">
FROM TB_FILEMANAGER_META_FILE A, TB_FILEMANAGER_META_MAPPING B,
TB_FILEMANAGER_META C, TB_USERS_MAPPING D
</when>
<otherwise>
FROM
TB_FILEMANAGER_META_FILE A, TB_USERS_MAPPING D
</otherwise>
</choose>
WHERE
A.DEL_GB ='N'
AND A.IDX = D.FILE_IDX
AND D.USERNAME = #{creaId}
<choose>
<when test="searchKey != null and searchKey != '' ">
AND B.FILE_IDX = A.IDX
AND C.IDX = B.META_IDX
</when>
</choose>
<!-- 전체 -->
<if
test="positiveSearchKey != null and positiveSearchKey.size != 0 ">
<foreach collection="positiveSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
</if>
<if
test="negativeSearchKey != null and negativeSearchKey.size != 0 ">
AND A.IDX NOT IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="negativeSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<if
test="andSearchKey != null and andSearchKey.size != 0 ">
AND A.IDX IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="andSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
</select>
<select id="selectFileList"
parameterType="com.joang.filemanager.vo.FileVO"
resultType="com.joang.filemanager.vo.FileVO">
<include refid="pagingHeader"></include>
<![CDATA[
/* com.joang.filemanager.meta.metaMapper.selectFileList */
SELECT
A.IDX,
E.OPN_CNT,
A.FILE_TITLE,
A.FILE_TYPE,
SUBSTRING(A.FILE_DETAIL::varchar,0,100) || '...' AS FILE_DETAIL,
A.ORIGINAL_FILE_NAME,
A.STORED_FILE_NAME,
A.FILE_SIZE,
A.CREA_DTM,
A.CREA_ID,
A.DEL_GB,
A.DOWN_YN,
A.STAR_LEVEL
]]>
<choose>
<when test="searchKey != null and searchKey != '' ">
, C.META AS SEARCH_KEY
FROM
TB_FILEMANAGER_META_FILE A, TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C, TB_USERS_MAPPING D
</when>
<otherwise>
FROM
TB_FILEMANAGER_META_FILE A, TB_USERS_MAPPING D
</otherwise>
</choose>
, (SELECT COUNT(IDX) AS OPN_CNT, FILE_IDX FROM TB_USERS_MAPPING TUM GROUP BY FILE_IDX ) E
<![CDATA[
WHERE
A.DEL_GB ='N'
AND A.IDX = D.FILE_IDX
AND A.IDX = E.FILE_IDX
]]>
AND D.USERNAME = #{creaId}
<choose>
<when test="searchKey != null and searchKey != '' ">
AND B.FILE_IDX = A.IDX
AND C.IDX = B.META_IDX
</when>
</choose>
<!-- 전체 -->
<if
test="positiveSearchKey != null and positiveSearchKey.size != 0 ">
<foreach collection="positiveSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
</if>
<if
test="negativeSearchKey != null and negativeSearchKey.size != 0 ">
AND A.IDX NOT IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="negativeSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<if
test="andSearchKey != null and andSearchKey.size != 0 ">
AND A.IDX IN (
SELECT DISTINCT B.FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META C
WHERE C.IDX = B.META_IDX
<foreach collection="andSearchKey" item="item"
open="AND (" close=")" separator="OR">
C.META LIKE CONCAT('%',#{item},'%')
</foreach>
)
</if>
<choose>
<when test="searchKey != null and searchKey != '' ">
<![CDATA[
ORDER BY CONCAT(A.STAR_LEVEL, A.IDX)
]]>
</when>
<otherwise>
<![CDATA[
ORDER BY A.IDX
]]>
</otherwise>
</choose>
<choose>
<when test="orderBy == 'ASC'">
<![CDATA[
ASC
]]>
</when>
<otherwise>
<![CDATA[
DESC
]]>
</otherwise>
</choose>
<include refid="pagingFooter"></include>
</select>
<!-- file detail -->
<select id="selectFileDetail" parameterType="int"
resultType="com.joang.filemanager.vo.FileVO">
<![CDATA[
/* com.joang.filemanager.meta.metaMapper.selectFileDetail */
SELECT
IDX,
E.OPN_CNT,
FILE_TITLE,
FILE_TYPE,
FILE_DETAIL,
ORIGINAL_FILE_NAME,
STORED_FILE_NAME,
FILE_SIZE,
CREA_DTM,
CREA_ID,
DEL_GB,
DOWN_YN,
ERROR,
STAR_LEVEL
FROM TB_FILEMANAGER_META_FILE, (SELECT COUNT(IDX) AS OPN_CNT, FILE_IDX FROM TB_USERS_MAPPING TUM GROUP BY FILE_IDX ) E
WHERE IDX = #{idx}
AND E.FILE_IDX = #{idx}
AND DEL_GB = 'N'
]]>
</select>
<select id="selectFileShareUserListByFile" parameterType="int"
resultType="com.joang.filemanager.vo.FileShareUserVO">
<![CDATA[
SELECT
B.USERNAME,
C.NAME,
B.FILE_IDX,
B.CREA_DTM,
B.CREA_ID
FROM TB_FILEMANAGER_META_FILE A, TB_USERS_MAPPING B, TB_USERS C
WHERE 1=1
AND A.IDX = #{idx}
AND A.IDX = B.FILE_IDX
AND C.USERNAME = B.USERNAME
]]>
</select>
<select id="getTargetMetaIdx" resultType="string">
SELECT COALESCE(MAX(
IDX ), 0) FROM TB_FILEMANAGER_META_FILE
</select>
<!-- ########## ============================================= File List/Detail
Select ============================================= ########## -->
<!-- ########## ============================================= Meta INSERT/UPDATE
Select ============================================= ########## -->
<select id="checkMeta" parameterType="String"
resultType="hashmap">
<![CDATA[
SELECT
idx
FROM
TB_FILEMANAGER_META
WHERE
DEL_GB ='N'
AND TRIM(META) = TRIM(#{meta})
]]>
</select>
<insert id="insertMeta" parameterType="hashmap">
<![CDATA[
INSERT INTO TB_FILEMANAGER_META
(
USER_ID, META, CREA_DTM, CREA_ID, DEL_GB
)
VALUES
(
#{userId},
#{meta},
NOW(),
#{userId},
'N'
)
]]>
<selectKey keyProperty="meta_idx" resultType="int"
order="AFTER">
SELECT COALESCE(MAX( IDX ), 0) AS META_IDX FROM TB_FILEMANAGER_META
</selectKey>
</insert>
<insert id="insertMetaMapping" parameterType="hashmap">
<selectKey keyProperty="idx" resultType="int" order="BEFORE">
SELECT COALESCE(MAX( IDX ), 0) FROM TB_FILEMANAGER_META_FILE
</selectKey>
<![CDATA[
INSERT INTO TB_FILEMANAGER_META_MAPPING
(
USER_ID, META_IDX, FILE_IDX, CREA_DTM, CREA_ID, DEL_GB
)
VALUES
(
#{userId},
#{meta_idx}::INTEGER,
]]>
<choose>
<when test="file_idx != null">
<!-- 변경되어야할 파일의 file_idx -->
\#{file_idx}::INTEGER,
</when>
<otherwise>
<!-- 새 파일 업로드인 경우 idx -->
\#{idx}::INTEGER,
</otherwise>
</choose>
<![CDATA[
NOW(),
#{userId},
'N'
)
]]>
</insert>
<insert id="insertMetaExistMapping" parameterType="hashmap">
<![CDATA[
INSERT INTO TB_FILEMANAGER_META_MAPPING
(
USER_ID, META_IDX, FILE_IDX, CREA_DTM, CREA_ID, DEL_GB
)
VALUES
(
#{userId},
#{meta_idx}::INTEGER,
#{idx}::INTEGER,
NOW(),
#{userId},
'N'
)
]]>
</insert>
<insert id="insertShareUser" parameterType="com.joang.filemanager.vo.FileShareUserVO">
<![CDATA[
INSERT INTO TB_USERS_MAPPING
(
USERNAME, FILE_IDX, CREA_DTM, CREA_ID
)
VALUES
(
#{username},
#{file_idx}::INTEGER,
NOW(),
#{crea_id}
)
]]>
</insert>
<!-- Meta Merge -->
<insert id="updateMergeMetaMapping" parameterType="hashmap">
/*
updateMergeMetaMapping */
UPDATE TB_FILEMANAGER_META_MAPPING SET
META_IDX = #{targetMeta}::INTEGER WHERE META_IDX =
\#{sourceMeta}::INTEGER;
</insert>
<insert id="updateMergeMeta" parameterType="hashmap">
UPDATE
TB_FILEMANAGER_META SET
DEL_GB = 'Y' WHERE IDX = #{sourceMeta}::INTEGER;
</insert>
<!-- Delete Meta Detail -->
<insert id="deleteMetaDetail" parameterType="int">
DELETE FROM
TB_FILEMANAGER_META
WHERE IDX = #{meta_idx};
</insert>
<!-- ########## ============================================= Meta INSERT/UPDATE
Select ============================================= ########## -->
<!-- ########## ============================================= File INSERT/UPDATE
Select ============================================= ########## -->
<insert id="insertFile" parameterType="hashmap">
<![CDATA[
INSERT INTO TB_FILEMANAGER_META_FILE
(
FILE_TITLE,
FILE_DETAIL,
ORIGINAL_FILE_NAME,
STORED_FILE_NAME,
FILE_SIZE,
FILE_TYPE,
CREA_DTM,
CREA_ID,
DOWN_YN,
STAR_LEVEL
)
VALUES
(
#{file_title},
#{file_detail},
#{original_file_name},
#{stored_file_name},
#{file_size},
#{file_type},
NOW(),
#{crea_id},
#{down_yn},
#{star_level}::INTEGER
)
]]>
</insert>
<insert id="updateFile" parameterType="hashmap">
<![CDATA[
UPDATE TB_FILEMANAGER_META_FILE SET
]]>
<if test="file_title != null and file_title != ''">
FILE_TITLE = #{file_title} ,
</if>
<if test="file_type != null and file_type != ''">
FILE_TYPE = #{file_type} ,
</if>
<if test="file_detail != null and file_detail != ''">
FILE_DETAIL = #{file_detail} ,
</if>
<if test="crea_id != null and crea_id != ''">
CREA_ID = #{crea_id} ,
</if>
<if test="star_level != null and star_level != ''">
STAR_LEVEL = #{star_level}::INTEGER ,
</if>
<![CDATA[
CREA_DTM = NOW()
WHERE IDX = #{idx}::INTEGER;
]]>
</insert>
<delete id="deleteShareUsers" parameterType="int">
<![CDATA[
DELETE FROM TB_USERS_MAPPING
WHERE FILE_IDX = #{file_idx}
]]>
</delete>
<select id="selectMetaListByFile" parameterType="int"
resultType="com.joang.filemanager.vo.MetaVO">
<![CDATA[
SELECT
A.IDX,
A.USER_ID,
A.META,
A.CREA_DTM,
A.CREA_ID,
A.DEL_GB
FROM TB_FILEMANAGER_META A, TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META_FILE C
WHERE A.IDX = B.META_IDX
AND B.FILE_IDX = C.IDX
AND C.DEL_GB = 'N'
AND C.IDX = #{idx}
]]>
</select>
<!-- update File Detail -->
<insert id="updateFileDetail" parameterType="hashmap">
UPDATE
TB_FILEMANAGER_META_FILE SET
DEL_GB = 'Y' WHERE IDX = #{idx}::INTEGER;
</insert>
<!-- File Mapping update -->
<insert id="updateFileMetaMapping" parameterType="hashmap">
UPDATE
TB_FILEMANAGER_META_MAPPING SET
FILE_IDX = #{targetMeta}::INTEGER WHERE FILE_IDX = #{sourceMeta}::INTEGER;
</insert>
<!-- Delete File detail -->
<delete id="deleteFileDetail" parameterType="int">
<![CDATA[
DELETE
FROM
TB_FILEMANAGER_META_FILE
WHERE
IDX = #{file_idx}::INTEGER;
]]>
</delete>
<update id="updateYouTuBeDownloadYn" parameterType="int">
/*
updateYouTuBeDownloadYn */
UPDATE TB_FILEMANAGER_META_FILE SET
DOWN_YN = 'N'
WHERE
IDX = #{file_idx};
</update>
<!-- ########## ============================================= File INSERT/UPDATE
Select ============================================= ########## -->
<!-- ########## ============================================= MApping Management
============================================= ########## -->
<!-- ########## Meta Ref ########## -->
<select id="selectMetaRefList"
parameterType="com.joang.filemanager.vo.MetaRefVO"
resultType="com.joang.filemanager.vo.MetaRefVO">
<![CDATA[
SELECT
A.FROM_IDX,
A.TO_IDX,
A.META_REF,
A.CREA_DTM,
A.CREA_ID
FROM TB_FILEMANAGER_META_REF A, TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META_FILE C, TB_USERS_MAPPING D
WHERE 1=1
]]>
<if test="fromIdx != null and fromIdx != '' and fromIdx != 0">
<![CDATA[
AND A.FROM_IDX = #{fromIdx}
]]>
</if>
<if test="toIdx != null and toIdx != '' and toIdx != 0">
<![CDATA[
AND A.TO_IDX = #{toIdx}
]]>
</if>
<![CDATA[
AND A.FROM_IDX = B.META_IDX
AND B.FILE_IDX = C.IDX
AND A.DEL_GB = 'N'
AND C.IDX = D.FILE_IDX
AND D.USERNAME = #{crea_id}
UNION
SELECT
A.FROM_IDX,
A.TO_IDX,
A.META_REF,
A.CREA_DTM,
A.CREA_ID
FROM TB_FILEMANAGER_META_REF A, TB_FILEMANAGER_META_MAPPING B, TB_FILEMANAGER_META_FILE C, TB_USERS_MAPPING D
WHERE 1=1
]]>
<if test="fromIdx != null and fromIdx != '' and fromIdx != 0">
<![CDATA[
AND A.FROM_IDX = #{fromIdx}
]]>
</if>
<if test="toIdx != null and toIdx != '' and toIdx != 0">
<![CDATA[
AND A.TO_IDX = #{toIdx}
]]>
</if>
<![CDATA[
AND A.TO_IDX = B.META_IDX
AND B.FILE_IDX = C.IDX
AND A.DEL_GB = 'N'
AND C.IDX = D.FILE_IDX
AND D.USERNAME = #{crea_id}
]]>
</select>
<!-- Check mapping -->
<select id="checkMetaMapping" parameterType="hashmap"
resultType="hashmap">
<![CDATA[
SELECT
meta_idx
FROM
TB_FILEMANAGER_META_MAPPING
WHERE
DEL_GB ='N'
AND META_IDX = #{meta_idx}::INTEGER
AND FILE_IDX = #{file_idx}::INTEGER
]]>
</select>
<!-- Delete meta mapping -->
<delete id="deleteMetaMapping" parameterType="hashmap">
<![CDATA[
DELETE
FROM
TB_FILEMANAGER_META_MAPPING
WHERE
META_IDX =#{meta_idx}::INTEGER
AND FILE_IDX = #{file_idx}::INTEGER
]]>
</delete>
<select id="selectMappingListByMetaIdx" parameterType="int"
resultType="com.joang.filemanager.vo.MetaVO">
<![CDATA[
SELECT
IDX,
META_IDX,
FILE_IDX
FROM TB_FILEMANAGER_META_MAPPING
WHERE META_IDX = #{idx}
]]>
</select>
<!-- Delete Mapping -->
<delete id="deleteMetaMappingByFileIdx" parameterType="int">
<![CDATA[
DELETE
FROM
TB_FILEMANAGER_META_MAPPING
WHERE
FILE_IDX = #{file_idx}
]]>
</delete>
<insert id="insertMetaRef"
parameterType="com.joang.filemanager.vo.MetaRefVO">
<![CDATA[
INSERT INTO TB_FILEMANAGER_META_REF (FROM_IDX, TO_IDX, META_REF, CREA_DTM, CREA_ID, UPDT_DTM, DEL_GB) VALUES
(
\#{fromIdx}, #{toIdx}, #{metaRef}, NOW(), #{creaId}, NOW(), 'N'
)
]]>
</insert>
<insert id="updateMetaRef"
parameterType="com.joang.filemanager.vo.MetaRefVO">
<![CDATA[
UPDATE TB_FILEMANAGER_META_REF SET
META_REF = #{metaRef},
UPDT_DTM = NOW()
WHERE
FROM_IDX = #{fromIdx}
AND TO_IDX = #{toIdx}
AND CREA_ID = #{crea_id}
]]>
</insert>
<insert id="deleteMetaRef"
parameterType="com.joang.filemanager.vo.MetaRefVO">
<![CDATA[
DELETE FROM TB_FILEMANAGER_META_REF
WHERE
FROM_IDX = #{fromIdx}
AND TO_IDX = #{toIdx}
AND CREA_ID = #{crea_id}
]]>
</insert>
<update id="updateMergeMetaFromRef" parameterType="hashmap">
/*
updateMergeMetaRef */
UPDATE TB_FILEMANAGER_META_REF SET
FROM_IDX = #{targetFromMeta}::INTEGER
WHERE
FROM_IDX = #{sourceFromMeta}::INTEGER AND TO_IDX = #{sourceToMeta}::INTEGER
AND CREA_ID = #{crea_id}
</update>
<update id="updateMergeMetaToRef" parameterType="hashmap">
/*
updateMergeMetaRef */
UPDATE TB_FILEMANAGER_META_REF SET
TO_IDX = #{targetToMeta}::INTEGER
WHERE
TO_IDX = #{sourceToMeta}::INTEGER AND FROM_IDX = #{sourceFromMeta}::INTEGER
AND CREA_ID = #{crea_id}
</update>
<delete id="daleteMergeMetaRef" parameterType="hashmap">
/*
daleteMergeMetaFromRef */
DELETE
FROM
TB_FILEMANAGER_META_REF
WHERE
FROM_IDX = #{sourceFromMeta}::INTEGER AND TO_IDX = #{sourceToMeta}::INTEGER
AND CREA_ID = #{crea_id}
</delete>
<delete id="deleteMetaMappingFROMRef" parameterType="int">
/*
deleteMetaMappingFROMRef */
DELETE
FROM
TB_FILEMANAGER_META_REF
WHERE
FROM_IDX = #{deleteMeta}
</delete>
<delete id="deleteMetaMappingTORef" parameterType="int">
/*
deleteMetaMappingTORef */
DELETE
FROM
TB_FILEMANAGER_META_REF
WHERE
TO_IDX = #{deleteMeta}
</delete>
<!-- ########## ============================================= Mapping Management
============================================= ########## -->
</mapper>