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

	<!-- 사용자 정보 조회 -->
    <select id="selectUserInfo" parameterType="String" resultType="java.util.HashMap">
    	/* [CLMS] selectUserInfo */
    	
		select USER_ID
			 , PASSWORD
			 , ACTIVE_FLAG
			 , EXTERNAL_USER_FLAG
			 , SYS_ID
			 , EMP_NO
			 , RECENT_LOGIN_IP
			 , RECENT_LOGIN_DATETIME
			 , FIRST_REG_DATETIME
			 , FIRST_REGR_ID
			 , LAST_MOD_DATETIME
			 , LAST_MODR_ID
			 , KNOX_ID
			 , FIRST_NAME
			 , LAST_NAME
			 , DISPLAY_NAME
			 , USER_NAME
			 , COMP_NAME
			 , COMP_CODE
			 ,(SELECT COMP_CD FROM TB_COM_DEPT WHERE DEPT_CD = DEPT_CODE) AS COMP_CD_HR
			 , DEPT_NAME
			 , DEPT_CODE
			 , GRD_NAME
			 , GRD_CODE
			 , NICK_NAME
			 , EN_USER_NAME
			 , EN_FIRST_NAME
			 , EN_LAST_NAME
			 , EN_COMP_NAME
			 , EN_DEPT_NAME
			 , EN_GRD_NAME
			 , COMP_TEL_NO
			 , MOBILE_TEL_NO
			 , EMAIL_ADDRESS
			 , LANGUAGE_CODE
			 , TIME_ZONE_CODE
			 , TIME_ZONE_ID
			 , LAST_LOGIN_DATETIME
			 , LAST_LOGIN_IP
			 , LAST_LOGOUT_DATETIME
			 , EXPIRE_STATUS_CODE
			 , DESCRIPTION
			 , USER_TYPE
			 , PRIVACY_POLICY
			 , PRIVACY_POLICY_DATE
			 , EU_GDPR_POLICY
			 , EU_GDPR_POLICY_DATE
			 , DELETED
			 , LOGIN_ERROR_COUNT
			 , LAST_PW_CHANGE_DATETIME
			 , SERVER_LOCATION
			 , JWT
			 , LAST_ACTIVITY_TIME
			 , BLNGT_ORGNZ
			 , BLNGT_ORGNZ AS CLMS_USER_ORGNZ
			 , LAS_MULTI_AUTH
			 , RESP_OPERDIV
			 , IN_DEPT_CD
			 , DUTY_APBTMAN_ID
			 , AUTH_APNT_YN
			 , USER_SRT
			 , GTHR_CNST_YN
			 , GTHR_CNST_DT
			 , AUTH_APNT_DEPT
			 , AUTO_RNEW_YN
			 , EMAIL_RCV_YN
			 , DIVISION_CD
			 , DIVISION_NM
			 , DIVISION_NM_ENG
		  FROM TN_CF_USER
		 WHERE USER_ID = #{user_id}

    </select>


    <!-- 사용자 조직명 조회 -->
    <select id="selectOrgnzNmAbbrInfo" parameterType="String" resultType="String">
        /* [CLMS] selectOrgnzNmAbbrInfo */
         SELECT  T.ORGNZ_NM_ABBR 
		   FROM  (
		           SELECT C.ORGNZ_NM_ABBR, CDCRTN_VAL, ROW_NUMBER() OVER(ORDER BY C.BLNGT_SRT) AS RN
		             FROM (
		                    SELECT  A.ORGNZ_NM_ABBR,  A.CDCRTN_VAL, A.BLNGT_SRT
		                      FROM  TN_CLM_UNITORGNZ A,
		                         (
				                    SELECT  D.DEPT_CD
				                      FROM  TB_COM_DEPT D
				                     START  WITH D.DEPT_CD = (SELECT DEPT_CODE  FROM TN_CF_USER WHERE USER_ID = #{user_id})
				                   CONNECT  BY PRIOR D.UP_DEPT_CD = D.DEPT_CD
			                     ) B
		                     WHERE A.DEPT_CD = B.DEPT_CD
		 
		                     UNION
		                     
		                    SELECT ORGNZ_NM_ABBR,  CDCRTN_VAL, 9999 AS BLNGT_SRT  
		                      FROM TN_CLM_UNITORGNZ 
		                     WHERE ORGNZ_CD = 'B00000014'		  
		                 ) C
		       )  T
		   WHERE  T.RN = 1
  </select>

	<!-- 사용자의 소속조직코드 가져오기 -->
    <select id="selectUserOrgnzCd" parameterType="String" resultType="java.util.HashMap">
    	/* [CLMS] selectUserOrgnzCd */
    	
		SELECT
		    A.ORGNZ_CD
		FROM
		    TN_CLM_UNITORGNZ A
		    ,(
		        SELECT DEPT_CD
		        FROM TB_COM_DEPT
		        START WITH DEPT_CD = #{dept_code}
		        CONNECT BY PRIOR UP_DEPT_CD = DEPT_CD
		    ) B
		WHERE A.DEPT_CD = B.DEPT_CD
		ORDER BY BLNGT_SRT

    </select>

    <!-- 로그인로직 제외자 검색(SDS 및 TF인원) -->
    <select id="selectExceptUserInfo" parameterType="String" resultType="java.util.HashMap">
    	/* [CLMS] selectExceptUserInfo */
    	
		SELECT
			USER_ID
		FROM
			TB_COM_EXCEPT_USER
		WHERE
			USER_ID = #{user_id}

    </select>

	<!-- 사용자의 부서가 법무,컴플라이언스 부서 여부 조회 -->
    <select id="selectUserRoleInfo" parameterType="String" resultType="java.util.HashMap">
    	/* [CLMS] selectUserRoleInfo */
    	
		SELECT R.ROLE_ID
		     , UR.USER_ID
		     , R.ROLE_NAME
		     , R.DESCRIPTION
		     , R.LABEL
		     , R.LABEL_JSON
		     , UR.FROM_DATE
		     , UR.THRU_DATE
		  FROM TN_CF_USER_ROLE UR
		     , TN_CF_ROLE R
		 WHERE UR.ROLE_ID = R.ROLE_ID
		   AND R.ROLE_NAME IN ('RA00','RA01','RA02','RA03','RC01','RM00','RD01')
		   AND UR.USER_ID = #{user_id}

    </select>

	<!-- Locale 변경 -->
	<update id="updateUserLocale" parameterType="java.util.HashMap">
		/* [CLMS] updateUserLocale */
		
		UPDATE TN_CF_USER
		   SET LANGUAGE_CODE = #{language_code}
		     , GRD_NAME = #[grd_name}
		     , EN_GRD_NAME = #{en_grd_name}
		 WHERE USER_ID = #{user_id}
	</update>

	<!-- 사용자의 상위부서들 정보 가져오기 -->
	<select id="selectListUpDeptInfoUser" parameterType="java.util.HashMap" resultType="java.util.HashMap">
    	/* [CLMS] selectListUpDeptInfoUser */
		SELECT
			DEPT_CD, DEPT_NM, DEPT_LEVEL, UP_DEPT_CD
		FROM
			TB_COM_DEPT
		START WITH DEPT_CD = #{dept_cd}
		CONNECT BY PRIOR UP_DEPT_CD = DEPT_CD
		ORDER BY DEPT_LEVEL DESC
    </select>

	<!-- 사용자의 소속조직코드 가져오기 -->
	<select id="selectListOrgnzList" parameterType="java.util.HashMap" resultType="java.util.HashMap">
    /* [CLMS] QUERY ID : selectListOrgnzList */
	
		SELECT
			A.ORGNZ_CD
			,CASE
				WHEN #{locale} = 'ko_KR' THEN A.ORGNZ_NM
				ELSE A.ORGNZ_NM_ENG
			END ORGNZ_NM
			,A.ORGNZ_NM_ABBR
		FROM
			TN_CLM_UNITORGNZ A
		    ,(
				SELECT DEPT_CD
				FROM TB_COM_DEPT
				START WITH DEPT_CD = #{dept_cd}
				CONNECT BY PRIOR UP_DEPT_CD = DEPT_CD
			) B
		WHERE A.DEPT_CD = B.DEPT_CD
		ORDER BY BLNGT_SRT

    </select>

	<!-- 사용자 정보 중 소속조직코드 업데이트 -->
	<update id="updateBlngtOrgnz" parameterType="java.util.HashMap">
		/* [CLMS] updateBlngtOrgnz */
		
		UPDATE TN_CF_USER
		SET
			BLNGT_ORGNZ = #{blngt_orgnz}
			,LAST_MOD_DATETIME = SYSDATE
		WHERE
			USER_ID = #{user_id}
	</update>

	<!-- 소속조직코드로 소속조직명 조회 -->
	<select id="selectListOrgnzNameList" parameterType="java.util.HashMap" resultType="java.util.HashMap">
    	/* [CLMS] selectListOrgnzNameList */
    	
		SELECT
			CASE
				WHEN #{locale} = 'ko_KR' THEN ORGNZ_NM
				ELSE ORGNZ_NM_ENG
			END ORGNZ_NM
			,ORGNZ_NM_ABBR
		FROM
			TN_CLM_UNITORGNZ
		WHERE ORGNZ_CD = #{orgnz_cd}

    </select>

	<!-- 조직장 여부 조회 -->
	<select id="selectManagerYNList" parameterType="java.util.HashMap" resultType="java.util.HashMap">
    	/* [CLMS] selectManagerYNList */
    	
		SELECT
			DEPT_CD
		FROM TB_COM_DEPT
		WHERE
			DEPT_CD = #{dept_cd}
			AND DEPT_MGR_EMP_NO = #{dept_mgr_emp_no}
    </select>

	<!-- 지원부서 여부 조회 -->
	<select id="selectSupportYNList" parameterType="java.util.HashMap" resultType="java.util.HashMap">
    	/* [CLMS] selectSupportYNList */
		SELECT
			ORGNZ_CD
		FROM TN_CLM_SURTORGNZ
		WHERE
			ORGNZ_CD = #{blngt_orgnz}
			AND SURT_DEPT_CD = #{dept_cd}

    </select>

	<!-- 해당 역활 여부 조회-->
	<select id="existsRoleUser" parameterType="java.util.HashMap" resultType="java.util.HashMap">
	/* [CLMS] QUERY ID : existsRoleUser */
	
		SELECT NVL(MAX('Y'),'N') AS EXISTS_YN
		FROM TB_COM_ROLE_USER
		WHERE
			SYS_CD = #{sys_cd}
		   	AND ROLE_CD = #{role_cd}
		   	AND USER_ID = #{user_id	}

	</select>

	<!-- 역할별 사용자 삭제후 등록-->
	<insert id="insertRoleUser">
	/* [CLMS] QUERY ID : insertRoleUser */
	
		INSERT INTO TB_COM_ROLE_USER
		      (
		        SYS_CD
		       ,ROLE_CD
			   ,USER_ID
			   ,REG_ID
			   ,REG_DT
			  )
		VALUES (
		        #{sys_cd}
		       ,#{role_cd}
			   ,#{user_id}
			   ,'system'
			   ,SYSDATE
			  )
	</insert>

</mapper>