<?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="customDepartment">

	<resultMap id="customDepartmentResult" extends="common.markedObjectResult" type="customDepartment">
		<id property="selfDeptCode" column="SELF_DEPT_CODE"/>
		<result property="selfDeptName" column="SELF_DEPT_NAME"/>
		<result property="upperSelfDeptCode" column="UPPER_SELF_DEPT_CODE"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="deptLevel" column="DEPT_LEVEL" />
		<result property="deptSequence" column="DEPT_SEQUENCE"/>
		<association property="mappings" column="{selfDeptCode=SELF_DEPT_CODE}" select="selectDeptMapping"/>
		<association property="subDept" column="{upperSelfDeptCode=SELF_DEPT_CODE}" select="selectCustomDepartmentSubList"/>
	</resultMap>

	<resultMap id="deptMappingResult" extends="common.markedObjectResult" type="departmentMapping">
		<id property="selfDeptCode" column="SELF_DEPT_CODE"/>
		<result property="deptCode" column="DEPT_CODE"/>
		<result property="deptName" column="DEPT_NAME"/>
	</resultMap>

	<sql id="tableDepartmentSelf">TN_CF_DEPT_SELF</sql>
	<sql id="tableDeptMapping">TN_CF_DEPT_MAPPING</sql>

	<sql id="columnsCustomDepartment">
	SELF_DEPT_CODE, SELF_DEPT_NAME, UPPER_SELF_DEPT_CODE, DESCRIPTION,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
	DEPT_LEVEL, DEPT_SEQUENCE
	</sql>

	<sql id="columnsDepartmentMapping">
	SELF_DEPT_CODE, DEPT_CODE, DESCRIPTION,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID
	</sql>

	<sql id="conditionCustomDepartment">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="@com.samsung.SdlComparator@isNotEmpty(selfDeptCode)">
				AND SELF_DEPT_CODE = #{selfDeptCode}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(selfDeptName)">
				AND SELF_DEPT_NAME = #{selfDeptName}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(upperSelfDeptCode)">
				AND UPPER_SELF_DEPT_CODE = #{upperSelfDeptCode}
			</if>
		</trim>
	</sql>

	<select id="selectCustomDepartmentList" parameterType="java.util.HashMap" resultMap="customDepartmentResult">
		SELECT <include refid="columnsCustomDepartment" />
		FROM   <include refid="tableDepartmentSelf" />
		<include refid="conditionCustomDepartment" />
		ORDER BY DEPT_LEVEL, DEPT_SEQUENCE
	</select>

	<select id="selectCustomDepartmentSubList" parameterType="java.util.HashMap" resultMap="customDepartmentResult">
		SELECT <include refid="columnsCustomDepartment" />
		FROM   <include refid="tableDepartmentSelf" />
		<include refid="conditionCustomDepartment" />
		ORDER BY DEPT_LEVEL, DEPT_SEQUENCE
	</select>

	<select id="selectDeptMapping" parameterType="java.util.HashMap" resultMap="deptMappingResult">
		SELECT A.SELF_DEPT_CODE, A.DEPT_CODE,
		       A.FIRST_REG_DATETIME, A.FIRST_REGR_ID,
		       A.LAST_MOD_DATETIME, A.LAST_MODR_ID,
		       B.DEPT_NAME
		FROM   <include refid="tableDeptMapping" /> A
		    LEFT OUTER JOIN <include refid="knoxDepartment.tableDepartmentLDAP" /> B
		        ON A.DEPT_CODE = B.DEPT_CODE
		<trim prefix="WHERE" prefixOverrides="AND|OR">
		<if test="@com.samsung.SdlComparator@isNotEmpty(selfDeptCode)">
		AND    A.SELF_DEPT_CODE = #{selfDeptCode}
		</if>
		</trim>
		ORDER BY A.DEPT_CODE, B.DEPT_NAME
	</select>

	<select id="selectDeptListByParentId" parameterType="java.util.HashMap" resultType="java.util.HashMap">
		WITH RECURSIVE TREE_TABLE(UPPER_SELF_DEPT_CODE, SELF_DEPT_CODE, SORT_SEQUENCE) AS
		(SELECT UPPER_SELF_DEPT_CODE, SELF_DEPT_CODE, 1 SORT_SEQUENCE
		 FROM   <include refid="tableDepartmentSelf"/>
		 WHERE  SELF_DEPT_CODE = #{selfDeptCode}
		 UNION ALL
		 SELECT DS.UPPER_SELF_DEPT_CODE, DS.SELF_DEPT_CODE, TT.SORT_SEQUENCE + 1 SORT_SEQUENCE
		 FROM   <include refid="tableDepartmentSelf"/> DS,
		 		TREE_TABLE TT
		 WHERE  DS.UPPER_SELF_DEPT_CODE = TT.SELF_DEPT_CODE)
		SELECT SELF_DEPT_CODE AS "selfDeptCode", CAST(SORT_SEQUENCE AS VARCHAR(3)) AS "sortSequence"
		FROM   	TREE_TABLE
	</select>

	<insert id="insertCustomDepartment" parameterType="customDepartment">
		INSERT INTO <include refid="tableDepartmentSelf" />
		(<include refid="columnsCustomDepartment" />)
		VALUES
		(#{selfDeptCode}, #{selfDeptName}, #{upperSelfDeptCode}, #{description},
		 #{firstRegDatetime} , #{firstRegrId} , #{lastModDatetime} , #{lastModrId},
		 #{deptLevel}, #{deptSequence})
	</insert>

	<insert id="insertDeptMapping" parameterType="departmentMapping">
		INSERT INTO <include refid="tableDeptMapping" />
		(<include refid="columnsDepartmentMapping" />)
		VALUES
		(#{selfDeptCode}, #{deptCode}, #{deptName},
		 #{firstRegDatetime}, #{firstRegrId}, #{lastModDatetime}, #{lastModrId})
	</insert>

	<update id="updateCustomDepartment" parameterType="customDepartment">
		UPDATE <include refid="tableDepartmentSelf" />
		SET    SELF_DEPT_NAME = #{selfDeptName},
		       DESCRIPTION = #{description},
		       DEPT_SEQUENCE = #{deptSequence},
		       LAST_MOD_DATETIME = #{lastModDatetime},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  SELF_DEPT_CODE = #{selfDeptCode}
	</update>

	<update id="updateDeptMapping" parameterType="departmentMapping">
		UPDATE <include refid="tableDeptMapping" />
		SET    LAST_MOD_DATETIME = #{lastModDatetime},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  SELF_DEPT_CODE = #{selfDeptCode}
		AND    DEPT_CODE = #{deptCode}
	</update>

	<update id="updateDeptUpperDeptCode" parameterType="java.util.HashMap">
		UPDATE <include refid="tableDepartmentSelf" />
		SET    UPPER_SELF_DEPT_CODE = #{newParentId},
		       DEPT_SEQUENCE = #{moveToDeptSequence},
		       LAST_MOD_DATETIME = #{lastModDatetime},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  SELF_DEPT_CODE = #{targetId}
	</update>

	<update id="updateDeptSequence" parameterType="java.util.HashMap">
		UPDATE <include refid="tableDepartmentSelf" />
		SET    DEPT_LEVEL = #{newDeptLevel},
		       LAST_MOD_DATETIME = #{lastModDatetime},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  SELF_DEPT_CODE = #{selfDeptCode}
	</update>

	<delete id="deleteCustomDepartment" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableDepartmentSelf" />
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="@com.samsung.SdlComparator@isNotEmpty(selfDeptCode)">
				OR SELF_DEPT_CODE = #{selfDeptCode}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(upperSelfDeptCode)">
				OR UPPER_SELF_DEPT_CODE = #{upperSelfDeptCode}
			</if>
		</trim>
	</delete>

	<delete id="deleteDeptMapping" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableDeptMapping" />
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="@com.samsung.SdlComparator@isNotEmpty(selfDeptCode)">
				AND SELF_DEPT_CODE = #{selfDeptCode}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(deptCode)">
				AND DEPT_CODE = #{deptCode}
			</if>
		</trim>
	</delete>

</mapper>