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

		<!-- 부서목록조회(Tree) -->
		<select id="selectDeptTree" parameterType="com.samsung.embo.common.dept.dto.DeptVO" resultType="CamelHashMap">
			SELECT LVL
                  ,DEPT_CD
		          ,DEPT_NM
		          ,DEPT_NM_ENG
		          ,DEPT_LEVEL
		          ,DEPT_ORDER
		          ,IN_DEPT_CD
		          ,UP_DEPT_NM
		          ,UP_DEPT_CD
		          ,COMP_NM
		          ,COMP_CD
		          ,BUSINESS_CD
		          ,BUSINESS_NM
		          ,DOWN_DEPT_YN
		          ,DEPT_MGR_EMP_NO
		          ,DEPT_MGR_NM
		          ,DEPT_MGR_JIKGUP_NM
		          ,ISLEAF
		          ,DEPT_CD         AS TREE_KEY
                  ,DEPT_NM         AS TREE_TITLE
                  ,DEPT_NM_ENG     AS TREE_TITLE_ENG
                  ,UP_DEPT_CD      AS TREE_UP_KEY
                  ,LVL             AS TREE_LEVEL
                  ,ISLEAF          AS TREE_ISLEAF
                  ,(MAX(LVL) OVER() - (MAX(LVL) OVER() - (LVL - MIN(LVL) OVER()))) AS ARRAY_LOC
                  ,MAX(LVL) OVER() AS TREE_MAX_LEVEL
		     FROM ( SELECT LEVEL AS LVL
                          ,DEPT_CD
				          ,DEPT_NM
				          ,DEPT_NM_ENG
				          ,DEPT_LEVEL
				          ,DEPT_ORDER
				          ,IN_DEPT_CD
				          ,UP_DEPT_NM
				          ,UP_DEPT_CD
				          ,COMP_NM
				          ,COMP_CD
				          ,BUSINESS_CD
				          ,BUSINESS_NM
				          ,DOWN_DEPT_YN
				          ,DEPT_MGR_EMP_NO
				          ,DEPT_MGR_NM
				          ,DEPT_MGR_JIKGUP_NM
				          ,CASE WHEN LEAD(LEVEL) OVER(ORDER BY LEVEL, IN_DEPT_CD) <![CDATA[ < ]]> LEVEL THEN 'Y' ELSE 'N' END  AS ISLEAF
				    FROM (SELECT *
					    FROM TB_COM_DEPT)
				   START WITH DEPT_CD = #{tree_dept_cd}
				 CONNECT BY PRIOR DEPT_CD = UP_DEPT_CD
				 ORDER SIBLINGS BY IN_DEPT_CD
			)

		</select>

		<!-- 부서 I/F 테이블 데이타삭제 -->
		<delete id="deleteTnInfHrDept">
			TRUNCATE TABLE TN_INF_HR_DEPT
		</delete>

		<!-- 부서 테이블 Truncate -->
		<delete id="deleteComDept">
			TRUNCATE TABLE TB_COM_DEPT
		</delete>

		<!-- 부서 I/F 데이타입력 -->
		<insert id="insertTnInfHrDept" parameterType="java.util.List">
			INSERT INTO TN_INF_HR_DEPT
			(SPRVR_ORG_ID, SPRVR_ORG_NM, SPRVR_ORG_ENG_NM, ORG_LEVEL_SEQ, DEPT_BLD_NO, ORG_TYP_CLSF_CD, UPR_ORG_ID, UPR_ORG_NM, MGR_EMP_NO, MGR_EMP_NM, MGMT_LEVEL_CD, APPLY_STRT_DATE, DIV_CD, DIV_NM, DIV_ENG_NM, COST_CNTR_CD, COMP_CD, IF_DT, IF_RESULT_CONT, IF_RESULT_FLAG, LR_DEPT_EXST_YN)
			VALUES
			<foreach collection="list" item="item" separator=",">
			(
				  #{item.SPRVR_ORG_ID}
				, #{item.SPRVR_ORG_NM}
				, #{item.SPRVR_ORG_ENG_NM}
				, #{item.ORG_LEVEL_SEQ}
				, #{item.DEPT_BLD_NO}
				, #{item.ORG_TYP_CLSF_CD}
				, #{item.UPR_ORG_ID}
				, #{item.UPR_ORG_NM}
				, #{item.MGR_EMP_NO}
				, #{item.MGR_EMP_NM}
				, #{item.MGMT_LEVEL_CD}
				, #{item.APPLY_STRT_DATE}
				, #{item.DIV_CD}
				, #{item.DIV_NM}
				, #{item.DIV_ENG_NM}
				, #{item.COST_CNTR_CD}
				, #{item.COMP_CD}
				, GETDATE()
				, ''
				, 'Y'
				, #{item.LR_DEPT_EXST_YN}
			)
			</foreach>
		</insert>

		

		<!-- 운영테이블로 데이타 이관 (I/F Table -> Real Table) -->
		<insert id="insertComDept">
		/* [CLMS] QUERY ID : insertComDept */

			INSERT INTO TB_COM_DEPT
			(
				DEPT_CD,
				DEPT_NM,
				DEPT_NM_ENG,
				DEPT_LEVEL,
				DEPT_ORDER,
				UP_DEPT_CD,
				UP_DEPT_NM,
				DEPT_MGR_EMP_NO,
				DEPT_MGR_NM,
				DEPT_MGR_JIKGUP_CD,
				REORG_YMD,
				BUSINESS_CD,
				BUSINESS_NM,
				COMP_CD,
				LOAD_VERSION,
				DOWN_DEPT_YN
			)
			SELECT
				SPRVR_ORG_ID,
				SPRVR_ORG_NM,
				SPRVR_ORG_ENG_NM,
				ORG_LEVEL_SEQ,
				DEPT_BLD_NO,
				UPR_ORG_ID,
				UPR_ORG_NM,
				MGR_EMP_NO,
				MGR_EMP_NM,
				MGMT_LEVEL_CD,
				APPLY_STRT_DATE,
				DIV_CD,
				DIV_NM,
				COMP_CD,
				GETDATE(),
				LR_DEPT_EXST_YN
			FROM  TN_INF_HR_DEPT
		</insert>

		<!-- 삼성전자_기타 부서 생성 -->
		<insert id="insertEtcDept">
		/* [CLMS] QUERY ID : insertEtcDept */

			INSERT INTO TB_COM_DEPT
			(
				DEPT_CD,
				DEPT_NM,
				DEPT_NM_ENG,
				DEPT_LEVEL,
				DEPT_ORDER,
				IN_DEPT_CD,
				UP_DEPT_CD,
				UP_DEPT_NM,
				UP_DEPT_NM_ENG,
				COMP_CD,
				COMP_NM,
				COMP_NM_ENG,
				BUSINESS_CD,
				BUSINESS_NM,
				DOWN_DEPT_YN,
				DEPT_MGR_EMP_NO,
				DEPT_MGR_NM,
				DEPT_MGR_JIKGUP_CD,
				DEPT_MGR_JIKGUP_NM,
				DEPT_MGR_JIKGUP_NM_ENG,
				REORG_YMD,
				LOAD_VERSION
			)
			VALUES
			(
				'SEC_ETC',
				'삼성전자_기타',
				'삼성전자_기타',
				2,
				4,
				'',
				'C10S1000',
				'삼성전자',
				'',
				'C100',
				'',
				'',
				'',
				'',
				'N',
				'',
				'',
				'',
				'',
				'',
				'20240404',
				GETDATE()
			)
		</insert>

</mapper>