<?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="userRole">
	
	<resultMap id="userRoleResult" extends="common.markedObjectResult" type="userRole">
		<id property="roleId" column="ROLE_ID" />
		<id property="userId" column="USER_ID" />
		<result property="roleName" column="ROLE_NAME" />
		<result property="description" column="DESCRIPTION" />
		<result property="label" column="LABEL" />
		<result property="labelJson" column="LABEL_JSON" />
		<result property="fromDate" column="FROM_DATE" />
		<result property="thruDate" column="THRU_DATE" />
	</resultMap>
	
	<resultMap id="roleUserResult" extends="common.markedObjectResult" type="userRole">
		<id property="userId" column="USER_ID" />
		<result property="knoxId" column="KNOX_ID" />
		<result property="userName" column="USER_NAME" />
		<result property="grdName" column="GRD_NAME" />
		<result property="deptName" column="DEPT_NAME" />
		<result property="fromDate" column="FROM_DATE" />
		<result property="thruDate" column="THRU_DATE" />
	</resultMap>
	
	<sql id="tableUserRole">TN_CF_USER_ROLE</sql>
	
	<select id="selectRoleUserPagingList" parameterType="java.util.HashMap" resultMap="roleUserResult">
		SELECT *
		FROM   (SELECT ROW_NUMBER() OVER (<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>) RNUM,
	        	   	   U.USER_ID, U.KNOX_ID, U.USER_NAME, U.GRD_NAME, U.DEPT_NAME,
				   	   UR.FROM_DATE, UR.THRU_DATE
                FROM   <include refid="tableUserRole" /> UR,
                       <include refid="user.tableUser" /> U
                WHERE  UR.USER_ID = U.USER_ID
                AND    U.DELETED = '0'
                AND    UR.ROLE_ID = #{roleId}) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
	</select>
	
	<select id="countRoleUser" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(U.USER_ID) TOTAL
		FROM   <include refid="tableUserRole" /> UR,
		       <include refid="user.tableUser" /> U
		WHERE  UR.USER_ID = U.USER_ID
		AND    U.DELETED = '0'
		AND    UR.ROLE_ID = #{roleId}
	</select>
	
	<select id="selectRoleUser" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(USER_ID) CNT
		FROM   <include refid="tableUserRole" />
		WHERE  USER_ID = #{userId}
		AND    ROLE_ID = #{roleId}
	</select>
	
	<select id="selectUserRoleList" parameterType="java.util.HashMap" resultMap="userRoleResult">
		SELECT R.ROLE_ID, UR.USER_ID, R.ROLE_NAME, R.DESCRIPTION,
			   R.LABEL, R.LABEL_JSON,
		       UR.FROM_DATE, UR.THRU_DATE
		FROM   <include refid="tableUserRole" /> UR,
		       <include refid="role.tableRole"/> R
		WHERE  UR.ROLE_ID = R.ROLE_ID
		AND    UR.USER_ID = #{userId}
	</select>
	
	<insert id="insertUserRole" parameterType="userRole">
		INSERT INTO <include refid="tableUserRole" />
		(ROLE_ID, USER_ID, FROM_DATE, THRU_DATE,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{roleId}, #{userId}, #{fromDate}, #{thruDate},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<update id="updateUserRole" parameterType="userRole">
		UPDATE <include refid="tableUserRole" />
		SET
		<if test="@com.samsung.SdlComparator@isNotEmpty(fromDate)">
			   FROM_DATE = #{fromDate},
		</if>
		       THRU_DATE = #{thruDate},
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  USER_ID = #{userId}
		AND    ROLE_ID = #{roleId}
	</update>
	
	<delete id="deleteUserRole" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableUserRole" />
		WHERE  USER_ID = #{userId}
		AND    ROLE_ID = #{roleId}
	</delete>
	
	<select id="countSystemAdminByUser" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(USER_ID) CNT
		FROM   <include refid="tableUserRole" /> UR
		WHERE  ROLE_ID = 'SYSTEM_ADMIN'
		AND    FROM_DATE <![CDATA[ <= ]]> CONVERT(VARCHAR(8), GETDATE(), 112)
		AND    THRU_DATE <![CDATA[ >= ]]> CONVERT(VARCHAR(8), GETDATE(), 112)
		AND    USER_ID = #{userId}
	</select>

	<delete id="deleteUserRoleByUserId" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableUserRole" />
		WHERE  USER_ID = #{userId}
	</delete>
	
	<delete id="deleteUserRoleByThruDate" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableUserRole" />
		WHERE  THRU_DATE <![CDATA[ <= ]]> #{expiredDate}
	</delete>
	
	<select id="selectUserIdFromUserRoleByThruDate" parameterType="java.util.HashMap" resultType="string">
		SELECT USER_ID
		FROM   <include refid="tableUserRole" />
	 	WHERE  THRU_DATE = #{alarmDate}
		GROUP BY USER_ID
	</select>

</mapper>