<?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="cokr.xit.base.user.dao.UserMapper">

<resultMap id="userRow" type="cokr.xit.fims.base.FimsUser">
	<result property="id"                 column="USER_ID"/>
	<result property="account"            column="USER_ACNT"/>
	<result property="name"               column="USER_NM"/>
	<result property="password"           column="PASSWD"/>
	<result property="passwordHint"       column="PASSWD_HINT"/>
	<result property="passwordHintAnswer" column="PASSWD_NSR"/>
	<result property="zipCode"            column="ZIP"/>
	<result property="address"            column="ADDR"/>
	<result property="addressDetail"      column="DADDR"/>
	<result property="phoneNo"            column="TELNO"/>
	<result property="mobilePhoneNo"      column="MBL_TELNO"/>
	<result property="faxNo"              column="FXNO"/>
	<result property="emailAddress"       column="EML_ADRS"/>
	<result property="positionName"       column="POS_NM"/>
	<result property="groupID"            column="GRP_ID"/>
	<result property="orgID"              column="ORG_ID"/>
	<result property="institute"          column="NSTT_CD"/>
	<result property="deptCode"           column="DEPT_CD"/>
	<result property="cntnSeCd"           column="CNTN_SE_CD"/>
	<result property="userSeCd"           column="USER_SE_CD"/>
	<result property="ntrisKey"           column="NTRIS_KEY"/>
	<result property="certificateDn"      column="CRTFC_DN"/>
	<result property="locked"             column="LOCK_YN"/>
	<result property="lockCount"          column="LOCK_CNT"/>
	<result property="lockedDate"         column="LOCK_DT"/>
	<result property="createdAt"          column="REG_DT"/>
	<result property="createdBy"          column="RGTR"/>
	<result property="lastModified"       column="MDFCN_DT"/>
	<result property="modifiedBy"         column="MDFR"/>
	<result property="useYN"              column="USE_YN"/>
	<result property="status"             column="STTS"/>
</resultMap>

<sql id="selectUsers">
<include refid="utility.paging-prefix" />
SELECT A.USER_ID
     , UPPER(A.USER_ACNT) AS USER_ACNT
     , A.USER_NM
     , A.USER_SE_CD
     , GET_CODE_NM('CMN008', A.USER_SE_CD) AS USER_SE_NM
     , A.CNTN_SE_CD
     , GET_CODE_NM('CMN009', A.CNTN_SE_CD) AS CNTN_SE_NM
     , A.PASSWD_HINT
     , A.PASSWD_NSR
     , A.ZIP
     , A.ADDR
     , A.DADDR
     , A.TELNO
     , A.MBL_TELNO
     , A.FXNO
     , A.EML_ADRS
     , A.POS_NM
     , A.GRP_ID
     , A.CRTFC_DN
     , A.NTRIS_KEY
     , A.LOCK_YN
     , A.LOCK_CNT
     , A.LOCK_DT
     , A.REG_DT
     , A.STTS
     , (SELECT GET_CODE_NM('CMN004', A.STTS) FROM DUAL) AS STTS_NM
     , A.NSTT_CD                                                          AS NSTT_CD
     , A.NSTT_CD                                                          AS INST_CD
     , B.INST_NM                                                          AS INST_NM
     , A.DEPT_CD                                                          AS DEPT_CD
     , B.DEPT_NM                                                          AS DEPT_NM
     , A.ORG_ID                                                           AS ORG_ID
     , A.ORG_ID                                                           AS SGG_CD
     , B.SGG_NM                                                           AS SGG_NM
  FROM TB_USER A
  LEFT OUTER JOIN (
  <include refid="cokr.xit.base.user.dao.DepartmentMapper.sggDepts" />
  UNION
  <include refid="cokr.xit.base.user.dao.DepartmentMapper.emptyDept" />
  UNION
  <include refid="cokr.xit.base.user.dao.DepartmentMapper.adminSggDept" />
  ) B ON A.NSTT_CD = B.INST_CD AND A.DEPT_CD = B.DEPT_CD 
<where>
	<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(by) and @org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(term)">
		<choose>
			<when test="by == 'userAccount'">
	AND ${by} LIKE CONCAT('%', UPPER(#{term}), '%')
			</when>
			<otherwise>
	AND ${by} LIKE CONCAT('%', #{term}, '%')
			</otherwise>
		</choose>
	</if>
	<if test="userIDs != null">
	USER_ID IN (<foreach collection="userIDs" item="userID" separator=",">#{userID}</foreach>)
	</if>
	<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(status)">
	AND STTS = #{status}
	</if>
	<if test="@org.egovframe.rte.fdl.string.EgovStringUtil@isNotEmpty(type)">
	AND CNTN_SE_CD = #{type}
	</if>
</where>
<include refid="utility.orderBy" />
<include refid="utility.paging-suffix" />
</sql>

<select id="getUserList" parameterType="map" resultType="dataobject">
/* 사용자 목록 조회(userMapper.getUserList) */
<include refid="selectUsers"/>
</select>

<select id="getUsers" parameterType="map" resultMap="userRow">
/* 사용자 가져오기(userMapper.getUsers) */
<include refid="selectUsers"/>
</select>

<select id="getUser" parameterType="map" resultMap="userRow">
/* 사용자 계정 가져오기(userMapper.getUser) */
SELECT USER_ID
     , UPPER(USER_ACNT) AS USER_ACNT
     , USER_NM
     , USER_SE_CD
     , GET_CODE_NM('CMN008', USER_SE_CD) AS USER_SE_NM
     , CNTN_SE_CD
     , GET_CODE_NM('CMN009', CNTN_SE_CD) AS CNTN_SE_NM
     , PASSWD
     , PASSWD_HINT
     , PASSWD_NSR
     , TELNO
     , MBL_TELNO
     , FXNO
     , ZIP
     , ADDR
     , DADDR
     , EML_ADRS
     , ORG_ID
     , GRP_ID
     , NSTT_CD
     , DEPT_CD
     , POS_NM
     , CRTFC_DN
     , NTRIS_KEY
     , LOCK_YN
     , LOCK_CNT
     , LOCK_DT
     , STTS
     , USE_YN
     , REG_DT
     , RGTR
     , MDFCN_DT
     , MDFR
  FROM TB_USER
<where>
<if test="userID != null">AND USER_ID = #{userID}</if>
<if test="account != null">AND UPPER(USER_ACNT) = UPPER(#{account})</if>
<if test="institute != null">AND NSTT_CD = #{institute}</if>
<if test="orgID != null">AND ORG_ID = #{orgID}</if>
<if test="cntnSeCd != null">AND CNTN_SE_CD = #{cntnSeCd}</if>
</where>
</select>

<insert id="insertUser" parameterType="cokr.xit.fims.base.FimsUser">
<selectKey resultType="string" keyProperty="id" keyColumn="NEW_ID" order="BEFORE">
SELECT LPAD(NVL(MAX(USER_ID) + 1, 1), 10, '0') NEW_ID FROM TB_USER
</selectKey>
/* 사용자 정보 등록(userMapper.insertUser) */
INSERT INTO TB_USER (
       USER_ID
     , ORG_ID
     , NSTT_CD
     , DEPT_CD
     , CNTN_SE_CD
     , USER_SE_CD
     , USER_ACNT
     , USER_NM
     , PASSWD
     , PASSWD_HINT
     , PASSWD_NSR
     , ZIP
     , ADDR
     , DADDR
     , TELNO
     , MBL_TELNO
     , FXNO
     , EML_ADRS
     , POS_NM
     , GRP_ID
     , CRTFC_DN
     , NTRIS_KEY
     , LOCK_YN
     , LOCK_CNT
     , LOCK_DT
     , REG_DT
     , RGTR
     , MDFCN_DT
     , MDFR
     , USE_YN
     , STTS
) VALUES (
       #{id}
     , #{orgID}
     , #{institute}
     , #{deptCode}
     , #{cntnSeCd}
     , #{userSeCd}
     , UPPER(#{account})
     , #{name}
     , #{password}
     , #{passwordHint}
     , #{passwordHintAnswer}
     , #{zipCode}
     , #{address}
     , #{addressDetail}
     , #{phoneNo}
     , #{mobilePhoneNo}
     , #{faxNo}
     , #{emailAddress}
     , #{positionName}
     , #{groupID}
     , #{certificateDn}
     , #{ntrisKey}
     , 'N'
     , 0
     , NULL
     ,<include refid="utility.now" />
     , #{createdBy}
     ,<include refid="utility.now" />
     , #{createdBy}
     , 'Y'
     , #{status}
)
</insert>

<update id="updateUser" parameterType="cokr.xit.fims.base.FimsUser">
/* 사용자 정보 수정(userMapper.updateUser) */
UPDATE TB_USER 
   SET USER_SE_CD = #{userSeCd}
     , PASSWD_HINT = #{passwordHint}
     , PASSWD_NSR = #{passwordHintAnswer}
     , ZIP = #{zipCode}
     , ADDR = #{address}
     , DADDR = #{addressDetail}
     , TELNO = #{phoneNo}
     , MBL_TELNO = #{mobilePhoneNo}
     , FXNO = #{faxNo}
     , EML_ADRS = #{emailAddress}
     , POS_NM = #{positionName}
     , GRP_ID = #{groupID}
     , ORG_ID = #{orgID}
     , NSTT_CD = #{institute}
     , DEPT_CD = #{deptCode}
     , CRTFC_DN = #{certificateDn}
     , NTRIS_KEY = #{ntrisKey}
     , STTS = #{status}
     <if test='"D" == status'>, USE_YN = 'N'</if>
     , MDFCN_DT =<include refid="utility.now" />
     , MDFR = #{modifiedBy}
 WHERE USER_ID = #{id}</update>

<update id="changePassword" parameterType="map">
/* 비밀번호 변경(userMapper.changePassword) */
UPDATE TB_USER SET
       PASSWD = CASE USER_ID<foreach collection="userPasswords" item="userPassword" separator=" ">
                WHEN #{userPassword.userID} THEN #{userPassword.password}</foreach>
	            ELSE PASSWD END
     , MDFCN_DT =<include refid="utility.now" />
     , MDFR = #{currentUser.id}
 WHERE USER_ID IN (<foreach collection="userIDs" item="userID" separator=",">#{userID}</foreach>)
</update>

<update id="lockUsers" parameterType="map">
/* 사용자 잠김 해제(userMapper.lockUsers) */
UPDATE TB_USER SET
    <if test='lock == true'>  LOCK_YN = 'Y'
     , LOCK_CNT = LOCK_CNT + 1
     , LOCK_DT =<include refid="utility.now" /></if>
    <if test='lock == false'>  LOCK_YN = 'N'
     , LOCK_CNT = 0
     , LOCK_DT = NULL</if>
     , MDFCN_DT =<include refid="utility.now" />
     , MDFR = #{currentUser.id}
 WHERE USER_ID IN (<foreach collection="userIDs" item="userID" separator=",">#{userID}</foreach>)
</update>

<update id="setStatus" parameterType="map">
/* 사용자 상태 변경(userMapper.setStatus) */
UPDATE TB_USER SET
       STTS = #{status}
    <if test='"D" == status'>, USE_YN = 'N'</if>
     , MDFCN_DT =<include refid="utility.now" />
     , MDFR = #{currentUser.id}
 WHERE USER_ID IN (<foreach collection="userIDs" item="userID" separator=",">#{userID}</foreach>)
   AND STTS != #{status}
</update>

</mapper>