<?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="role">
	
	<resultMap id="roleResult" extends="common.markedObjectResult" type="role">
		<id property="roleId" column="ROLE_ID" />
		<result property="roleName" column="ROLE_NAME" />
		<result property="description" column="DESCRIPTION" />
		<result property="label" column="LABEL" />
		<result property="labelJson" column="LABEL_JSON" />
	</resultMap>
	
	<sql id="tableRole">TN_CF_ROLE</sql>
	
	<sql id="columnsRole">
	ROLE_ID, ROLE_NAME, DESCRIPTION,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
	LABEL, DELETE_YN, LABEL_JSON
	</sql>
	
	<sql id="conditionRole">
		WHERE DELETE_YN = '0'
		<if test="@com.samsung.SdlComparator@isNotEmpty(roleName)">
		AND ROLE_NAME LIKE CONCAT('%', #{roleName}, '%')
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(label)">
		AND (LABEL LIKE CONCAT('%', #{label}, '%') OR LABEL_JSON LIKE CONCAT('%', #{label}, '%'))
		</if>
	</sql>
	
	<select id="selectRoleNameList" resultMap="roleResult">
		SELECT ROLE_NAME
		FROM   <include refid="tableRole" />
		WHERE  DELETE_YN = '0'
		ORDER BY ROLE_NAME ASC
	</select>
	
	<select id="selectRoleList" parameterType="java.util.HashMap" resultMap="roleResult">
		SELECT <include refid="columnsRole" />
		FROM   <include refid="tableRole" />
		<include refid="conditionRole" />
		ORDER BY ROLE_NAME ASC
	</select>
	
	<select id="selectRolePagingList" parameterType="java.util.HashMap" resultMap="roleResult">
		SELECT *
		FROM   (SELECT ROW_NUMBER() OVER (<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if> ) RNUM,
		        	   <include refid="columnsRole" />
				FROM   <include refid="tableRole"/>
		        <include refid="conditionRole" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
	</select>
	
	<select id="countRole" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(ROLE_ID) TOTAL
		FROM   <include refid="tableRole" />
		<include refid="conditionRole" />
	</select>
	
	<select id="selectRole" parameterType="java.util.HashMap" resultMap="roleResult">
		SELECT <include refid="columnsRole" />
		FROM   <include refid="tableRole" />
		WHERE  ROLE_ID = #{roleId}
		ORDER BY ROLE_NAME ASC
	</select>
	
	<insert id="insertRole" parameterType="role">
		INSERT INTO <include refid="tableRole" />
		(<include refid="columnsRole" />)
		VALUES
		(#{roleId}, #{roleName}, #{description},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId},
		 #{label}, '0', #{labelJson})
	</insert>
	
	<update id="updateRole" parameterType="role">
		UPDATE <include refid="tableRole" />
		SET	   ROLE_NAME = #{roleName},
		       DESCRIPTION = #{description},
		       LABEL = #{label},
		       LABEL_JSON = #{labelJson},
		       LAST_MOD_DATETIME = #{lastModDatetime, jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  ROLE_ID = #{roleId}
	</update>
	
	<update id="deleteRole" parameterType="role">
		UPDATE <include refid="tableRole" />
		SET    DELETE_YN = '1',
		       LAST_MOD_DATETIME = #{lastModDatetime, jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  ROLE_ID = #{roleId}
	</update>
	
	<select id="getRoleIdListByUserId" parameterType="java.util.HashMap" resultType="string">
		SELECT #{userId} ROLE_ID
		UNION
		SELECT 'DEFAULT_ROLE' ROLE_ID
		UNION
		SELECT ROLE_ID
		FROM   <include refid="userRole.tableUserRole" />
		WHERE  USER_ID = #{userId}
	</select>
	
</mapper>