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

	<resultMap id="userSyncResult" extends="common.markedObjectResult" type="userSync">
		<id property="userId" column="USER_ID"/>
		<result property="password" column="PASSWORD"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="activeFlag" column="ACTIVE_FLAG"/>
		<result property="sysId" column="SYS_ID"/>
		<result property="empNo" column="EMP_NO"/>
		<result property="recentLoginIp" column="RECENT_LOGIN_IP"/>
		<result property="recentLoginDatetime" column="RECENT_LOGIN_DATETIME" jdbcType="TIMESTAMP"/>
		<result property="firstRegDatetime" column="FIRST_REG_DATETIME" jdbcType="TIMESTAMP"/>
		<result property="firstRegrId" column="FIRST_REGR_ID"  />
		<result property="lastModDatetime" column="LAST_MOD_DATETIME" jdbcType="TIMESTAMP"/>
		<result property="lastModrId" column="LAST_MODR_ID" />
		<result property="knoxId" column="KNOX_ID" />
		<result property="nickName" column="NICK_NAME" />
		<result property="firstName" column="FIRST_NAME" />
		<result property="lastName" column="LAST_NAME" />
		<result property="userNameEn" column="EN_USER_NAME" />
		<result property="firstNameEn" column="EN_FIRST_NAME" />
		<result property="lastNameEn" column="EN_LAST_NAME" />
		<result property="language" column="LANGUAGE_CODE" />
		<result property="displayName" column="DISPLAY_NAME" />
		<result property="userName" column="USER_NAME" />
		<result property="compName" column="COMP_NAME" />
		<result property="compNameEn" column="EN_COMP_NAME" />
		<result property="compCode" column="COMP_CODE" />
		<result property="deptName" column="DEPT_NAME" />
		<result property="deptNameEn" column="EN_DEPT_NAME" />
		<result property="deptCode" column="DEPT_CODE" />
		<result property="grdName" column="GRD_NAME" />
		<result property="grdNameEn" column="EN_GRD_NAME" />
		<result property="grdCode" column="GRD_CODE" />
		<result property="compTel" column="COMP_TEL_NO" />
		<result property="mobile" column="MOBILE_TEL_NO" />
		<result property="email" column="EMAIL_ADDRESS" />
		<result property="externalUser" column="EXTERNAL_USER_FLAG" />
	</resultMap>

	<sql id="tableUserSync">TN_CF_USER_SYNC</sql>

	<sql id="columnsUserSync">
	USER_ID, PASSWORD, DESCRIPTION, ACTIVE_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, EN_USER_NAME, EN_FIRST_NAME, EN_LAST_NAME,
	LANGUAGE_CODE, DISPLAY_NAME, USER_NAME, COMP_NAME, EN_COMP_NAME, COMP_CODE, DEPT_NAME, EN_DEPT_NAME, DEPT_CODE, GRD_NAME,
	EN_GRD_NAME, GRD_CODE, COMP_TEL_NO, MOBILE_TEL_NO, EMAIL_ADDRESS, NICK_NAME, EXTERNAL_USER_FLAG
	</sql> 
	
	<sql id="conditionUserSync">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="_parameter.containsKey('userId')">
				<choose>
					<when test="@com.samsung.SdlComparator@isNotEmpty(userId)">
						USER_ID = #{userId}
					</when>
					<otherwise>
						USER_ID = NULL
					</otherwise>
				</choose>				
			</if>		
			<if test="@com.samsung.SdlComparator@isNotEmpty(knoxId)">
				AND LOWER(KNOX_ID) LIKE '%' || LOWER(#{knoxId}) || '%'
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(knoxIdNotLike)">
				AND KNOX_ID = #{knoxIdNotLike}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(userName)">
				AND LOWER(USER_NAME) LIKE '%' || LOWER(#{userName}) || '%'
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(deptName)">
				AND LOWER(DEPT_NAME) LIKE '%' || LOWER(#{deptName}) || '%'
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(activeFlag)">
				AND ACTIVE_FLAG = #{activeFlag}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(externalUser)">
				AND EXTERNAL_USER_FLAG = #{externalUser} 
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(userType)">
				AND USER_TYPE = #{userType}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(passWord)">
				AND PASSWORD = #{passWord}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(empNo)">
				AND EMP_NO = #{empNo}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(deptCode)">
				AND DEPT_CODE = #{deptCode}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(compName)">
				AND COMP_NAME = #{compName}
			</if>
		</trim>
	</sql>

	<select id="selectUserSync" parameterType="java.util.HashMap" resultMap="userSyncResult">
		SELECT <include refid="columnsUserSync" />
		FROM   <include refid="tableUserSync" />
		<include refid="conditionUserSync" />
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
		ORDER BY ${orderBy}
		</if>
	</select>

	<select id="selectAllUserSyncList" parameterType="java.util.HashMap" resultMap="userSyncResult">
		SELECT <include refid="columnsUserSync" />
		FROM   <include refid="tableUserSync" />
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
		ORDER BY ${orderBy}
		</if>
	</select>

	<select id="countUserSync" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(*) AS TOTAL
		FROM   <include refid="tableUserSync" />
		<include refid="conditionUserSync" />
	</select>

	<insert id="insertUserSync"  parameterType="userSync">
		INSERT INTO <include refid="tableUserSync" />
		(<include refid="columnsUserSync" />)
		VALUES
		(#{userId}, #{password}, #{description}, #{activeFlag}, #{sysId}, #{empNo}, #{recentLoginIp},
		 #{recentLoginDatetime}, #{firstRegDatetime}, #{userId}, #{lastModDatetime}, #{userId}, #{knoxId},
		 #{firstName}, #{lastName}, #{userNameEn}, #{firstNameEn}, #{lastNameEn}, #{language},
		 #{displayName}, #{userName}, #{compName}, #{compNameEn}, #{compCode}, #{deptName}, #{deptNameEn},
		 #{deptCode}, #{grdName}, #{grdNameEn}, #{grdCode}, #{compTel}, #{mobile}, #{email}, #{nickName}, #{externalUser})
	</insert>

	<update id="updateUserSync" parameterType="userSync">
		UPDATE <include refid="tableUserSync" />
		SET	   DESCRIPTION = #{description},
		       ACTIVE_FLAG = #{activeFlag},
		       EMP_NO = #{empNo},
		       RECENT_LOGIN_IP = #{recentLoginIp},
		       RECENT_LOGIN_DATETIME = #{recentLoginDatetime},
		       LAST_MOD_DATETIME = #{lastModDatetime},
		       LAST_MODR_ID = #{lastModrId},
		       KNOX_ID = #{knoxId},
		       NICK_NAME = #{nickName},
		       FIRST_NAME = #{firstName},
		       LAST_NAME = #{lastName},
		       EN_USER_NAME = #{userNameEn},
		       EN_FIRST_NAME = #{firstNameEn},
		       EN_LAST_NAME = #{lastNameEn},
		       LANGUAGE_CODE = #{language},
		       DISPLAY_NAME = #{displayName},
		       USER_NAME = #{userName},
		       COMP_NAME = #{compName},
		       EN_COMP_NAME = #{compNameEn},
		       COMP_CODE = #{compCode},
		       DEPT_NAME = #{deptName},
		       EN_DEPT_NAME = #{deptNameEn},
		       DEPT_CODE = #{deptCode},
		       GRD_NAME = #{grdName},
		       EN_GRD_NAME = #{grdNameEn},
		       GRD_CODE = #{grdCode},
		       COMP_TEL_NO = #{compTel},
		       MOBILE_TEL_NO = #{mobile},
		       EMAIL_ADDRESS = #{email}
		WHERE  USER_ID = #{userId}
	</update>

	<delete id="deleteUserSync" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableUserSync" />
		WHERE  USER_ID = #{userId}
	</delete>

</mapper>