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

		<!-- 임직원 I/F 테이블 Truncate -->
		<delete id="deleteTnInfHrEmpMas">
			TRUNCATE TABLE TN_INF_HR_EMP_MAS
		</delete>


		<!-- 임직원 I/F 테이블 Insert -->
		<insert id="insertTnInfHrEmpMas" parameterType="java.util.List">
			/* [CLMS] QUERY ID : insertTnInfHrEmpMas */

			INSERT INTO TN_INF_HR_EMP_MAS
			(EMP_NO, PRF_FLLNM, EMP_STTS_CD, MGMT_LEVEL_CD, MGMT_LEVEL_NM, MGMT_LEVEL_ENG_NM, 
			JOB_CLSF_CD, JOB_CLSF_NM, MARG_STTS_ENG_NM, JOB_CD, JOB_NM, SPRVR_ORG_ID, SPRVR_ORG_NM, 
			SPRVR_ORG_ENG_NM, SCTN_CD, DIV_CD, LOC_CD, LOC_NM, DSP_SPRVR_ORG_ID, DSPI_SPRVR_ORG_NM, 
			DSP_LOC_CD, DSP_LOC_NM, PRF_ENG_LSTNM, PRF_ENG_FSTNM, PRF_ENG_MIDNM, PRF_ENG_FLLNM, 
			EP_ID, IF_DT, IF_RESULT_CONT, IF_RESULT_FLAG)
			VALUES
			<foreach collection="list" item="item" separator=",">
			(
			  #{item.EMP_NO}
			, #{item.PRF_FLLNM}
			, #{item.EMP_STTS_CD}
			, #{item.MGMT_LEVEL_CD}
			, #{item.MGMT_LEVEL_NM}
			, #{item.MGMT_LEVEL_ENG_NM}
			, #{item.JOB_CLSF_CD}
			, #{item.JOB_CLSF_NM}
			, #{item.MARG_STTS_ENG_NM}
			, #{item.JOB_CD}
			, #{item.JOB_NM}
			, #{item.SPRVR_ORG_ID}
			, #{item.SPRVR_ORG_NM}
			, #{item.SPRVR_ORG_ENG_NM}
			, #{item.SCTN_CD}
			, #{item.DIV_CD}
			, #{item.LOC_CD}
			, #{item.LOC_NM}
			, #{item.DSP_SPRVR_ORG_ID}
			, #{item.DSPI_SPRVR_ORG_NM}
			, #{item.DSP_LOC_CD}
			, #{item.DSP_LOC_NM}
			, #{item.PRF_ENG_LSTNM}
			, #{item.PRF_ENG_FSTNM}
			, #{item.PRF_ENG_MIDNM}
			, #{item.PRF_ENG_FLLNM}
			, #{item.EP_ID}
			, GETDATE()
			, ''
			, 'Y'
			)
			</foreach>
		</insert>
		<!-- 부서 I/F 데이타입력 SMDM 으로 수정 20240514-->
		<select id="selectTnInfHrEmpMasSMDMCnt" resultType="_int">
			/* [CLMS] QUERY ID : selectTnInfHrEmpMasSMDMCnt */
			SELECT COUNT(*) AS CNT
			FROM TI_ZTO_EMPLOYEE_SMDM
		</select>
		<insert id="insertTnInfHrEmpMasSMDM" parameterType="java.util.List">
			/* [CLMS] QUERY ID : insertTnInfHrEmpMasSMDM */
			INSERT INTO TN_INF_HR_EMP_MAS
			(EMP_NO, PRF_FLLNM, EMP_STTS_CD, MGMT_LEVEL_CD, MGMT_LEVEL_NM, MGMT_LEVEL_ENG_NM, JOB_CLSF_CD, JOB_CLSF_NM, MARG_STTS_ENG_NM, JOB_CD, JOB_NM
				, SPRVR_ORG_ID, SPRVR_ORG_NM, SPRVR_ORG_ENG_NM, SCTN_CD, DIV_CD, LOC_CD, LOC_NM, DSP_SPRVR_ORG_ID, DSPI_SPRVR_ORG_NM, DSP_LOC_CD, DSP_LOC_NM
				, PRF_ENG_LSTNM, PRF_ENG_FSTNM, PRF_ENG_MIDNM, PRF_ENG_FLLNM, EP_ID, SSO_ID, EMP_DISTN_CODE, COMPANY_CODE, COMPANY_ENG_NM, BIZ_EMAIL
				, IF_DT, IF_RESULT_CONT, IF_RESULT_FLAG)
			(SELECT EMP_NO, EMP_PRF_FLLNM, EMP_STTS_CD, CLTTL_CD, CLTTL_WDY_CD_NM, '', JOB_CLSF_CD, JOB_CLSF_WDY_CD_NM, '', JOB_CD, JOB_WDY_CD_NM
				, SPRVR_ORG_ID, ORG_DEPT_KOR_NM, ORG_DEPT_ENG_NM, '', '', LOC_CD, '', DSP_SPRVR_ORG_ID, DISPATCH_DEPT_ENG_NM, '', ''
				, '', '', '', EMP_PRF_ENG_FLLNM, UNIQUE_ID, SSO_ID, EMP_DISTN_CODE, COMPANY_CODE, COMPANY_ENG_NM, BIZ_EMAIL 
				, IF_DATE, '', 'Y'
			 FROM TI_ZTO_EMPLOYEE_SMDM)
		</insert>

		

		<!-- 사업부 코드 세팅 -->
		<insert id="insertTnClmDeptOrgnz">
			/* [CLMS] QUERY ID : insertTnClmDeptOrgnz */

			DECLARE CURSOR CUR_ORG IS
			  SELECT DEPT_CD
			    FROM TB_COM_DEPT;

			BEGIN
			  FOR R IN CUR_ORG
			  LOOP

				INSERT INTO TN_CLM_DEPT_ORGNZ
			    SELECT R.DEPT_CD, A.ORGNZ_CD
			  		FROM
			  			TN_CLM_UNITORGNZ A
			  		    ,(
			  				SELECT DEPT_CD
			  				FROM TB_COM_DEPT
			  				START WITH DEPT_CD = R.DEPT_CD
			  				CONNECT BY PRIOR UP_DEPT_CD = DEPT_CD
			  			) B
			  		WHERE A.DEPT_CD = B.DEPT_CD
			        ORDER BY BLNGT_SRT
			        FETCH FIRST 1 ROW ONLY;
			  END LOOP;
			END;
		</insert>


		<!-- 운영테이블로 데이타 이관 (Temp Table -> Real Table) -->
		<insert id="insertTnCfUser">
			/* [CLMS] QUERY ID : insertTnCfUser  */
			MERGE INTO  TN_CF_USER U
			USING (
		    	SELECT * FROM TN_INF_HR_EMP_MAS WHERE EP_ID IS NOT NULL
 	        ) T
		  ON (U.USER_ID = T.EP_ID )
		  WHEN MATCHED THEN
		  UPDATE
		    SET  U.ACTIVE_FLAG          = 1
				,U.EXTERNAL_USER_FLAG   = 0
				,U.EMP_NO               = T.EMP_NO
				,U.DISPLAY_NAME         = T.PRF_FLLNM
				,U.USER_NAME            = T.PRF_FLLNM
				,U.DEPT_NAME            = T.SPRVR_ORG_NM
				,U.DEPT_CODE            = T.SPRVR_ORG_ID
				,U.GRD_NAME             = T.MGMT_LEVEL_NM
				,U.GRD_CODE             = T.MGMT_LEVEL_CD
				,U.EN_USER_NAME         = T.PRF_ENG_FLLNM
				,U.EN_FIRST_NAME        = concat(T.PRF_ENG_FSTNM , ' ' , T.PRF_ENG_MIDNM)
				,U.EN_LAST_NAME         = T.PRF_ENG_LSTNM
				,U.EN_DEPT_NAME         = T.SPRVR_ORG_ENG_NM
				,U.EN_GRD_NAME          = T.MGMT_LEVEL_ENG_NM
				,U.EXPIRE_STATUS_CODE   = CASE WHEN T.EMP_STTS_CD = '0' THEN  NULL
											WHEN T.EMP_STTS_CD = '3' THEN 'LONG_TERM'
											WHEN T.EMP_STTS_CD = 'R' THEN 'RETIRE'
											WHEN T.EMP_STTS_CD = '9' THEN 'DELETED'
										END
				,LAST_MOD_DATETIME      = GETDATE()
				,LAST_MODR_ID           = 'Batch IF'				
		  WHEN NOT MATCHED THEN
		  INSERT
		  (USER_ID, ACTIVE_FLAG, EXTERNAL_USER_FLAG, EMP_NO, DISPLAY_NAME, USER_NAME, DEPT_NAME, DEPT_CODE, GRD_NAME, GRD_CODE, EN_USER_NAME, EN_FIRST_NAME, EN_LAST_NAME, EN_DEPT_NAME, EN_GRD_NAME, EXPIRE_STATUS_CODE, FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID, DELETED)
		  VALUES (
				   T.EP_ID
				 , 1
				 , 0
				 , T.EMP_NO
				 , T.PRF_FLLNM
				 , T.PRF_FLLNM
				 , T.SPRVR_ORG_NM
				 , T.SPRVR_ORG_ID
				 , T.MGMT_LEVEL_NM
				 , T.MGMT_LEVEL_CD
				 , T.PRF_ENG_FLLNM
				 , concat(T.PRF_ENG_FSTNM , ' ' , T.PRF_ENG_MIDNM)
				 , T.PRF_ENG_LSTNM
				 , T.SPRVR_ORG_ENG_NM
				 , T.MGMT_LEVEL_ENG_NM
				 , CASE WHEN T.EMP_STTS_CD = 'AA' THEN  NULL
				        WHEN T.EMP_STTS_CD = 'CA' THEN 'LONG_TERM'
				        WHEN T.EMP_STTS_CD = 'DA' THEN 'RETIRE'
				        WHEN T.EMP_STTS_CD = 'RC' THEN 'DELETED'
				   END
				 , GETDATE()
				 , 'Batch IF'
				 , GETDATE()
				 , 'Batch IF'
				 , 0
		  );
		</insert>
		
		<!-- 기본 권한 세팅 -->
		<insert id="insertTnCfUserRole">
			/* [CLMS] QUERY ID : insertTnCfUserRole */

			INSERT INTO TN_CF_USER_ROLE
			(
	 			USER_ID,
	 			ROLE_ID ,
	 			DESCRIPTION,
	 			FROM_DATE,
	 			THRU_DATE,
	 			FIRST_REG_DATETIME,
	 			FIRST_REGR_ID,
	 			LAST_MOD_DATETIME,
	 			LAST_MODR_ID
 			)
 			SELECT A.USER_ID ,
			       B.ROLE_ID ,
			       B.DESCRIPTION,			       
			       CONVERT(CHAR(8), GETDATE(), 112) AS FROM_DATE,
			       '20991231' AS THRU_DATE,
			       GETDATE() AS FIRST_REG_DATETIME,
			       'system' AS FIRST_REGR_ID,
			       '' AS LAST_MOD_DATETIME,
			       '' AS LAST_MODR_ID
			FROM TN_CF_USER A, TN_CF_ROLE B
			WHERE B.ROLE_NAME IN ('DEFAULT_ROLE')
			AND A.USER_ID + B.ROLE_ID NOT IN (
												SELECT A.USER_ID + A.ROLE_ID
												FROM TN_CF_USER_ROLE A, TN_CF_ROLE B
												WHERE A.ROLE_ID = B.ROLE_ID
												AND B.ROLE_NAME IN ('DEFAULT_ROLE')
												)
		</insert>

	<update id="updateTnCfUserDeptName">
	  MERGE INTO TN_CF_USER TAR
	  USING ( SELECT DISTINCT
	              U.DEPT_CODE   AS DEPT_CD,
	              D.DEPT_NM     AS DEPT_NM,
	              D.DEPT_NM_ENG AS EN_DEPT_NM
	          FROM TN_CF_USER U, TB_COM_DEPT D
	          WHERE 1 = 1
	          AND   U.DEPT_CODE = D.DEPT_CD
	          AND   U.ACTIVE_FLAG = '1' 
	          
	          AND   U.DEPT_NAME <![CDATA[ <> ]]> D.DEPT_NM ) SRC
	  ON ( 1 = 1
	  AND  TAR.DEPT_CODE    = SRC.DEPT_CD
	  AND  TAR.ACTIVE_FLAG  = '1' 
	 
	  AND  (TAR.DEPT_NAME <![CDATA[ <> ]]> SRC.DEPT_NM OR TAR.EN_DEPT_NAME <![CDATA[ <> ]]> SRC.EN_DEPT_NM) )
	  WHEN MATCHED THEN UPDATE SET
	    TAR.DEPT_NAME    = SRC.DEPT_NM,
	    TAR.EN_DEPT_NAME = SRC.EN_DEPT_NM;
	</update>
	
	
</mapper>