<?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="workgroupRole">
	
	<resultMap id="workgroupRoleResult" extends="common.markedObjectResult" type="workgroupRole">
		<id property="userRoleId" column="ROLE_ID" />
		<result property="type" column="TYPE" />
		<result property="fromDate" column="FROM_DATE" />
		<result property="thruDate" column="THRU_DATE" />
		<association property="role" javaType="role">
			<id property="roleId" column="ROLE_ID" />
			<result property="roleName" column="ROLE_NAME" />
			<result property="label" column="LABEL" />
			<result property="labelJson" column="LABEL_JSON" />
		</association>
	</resultMap>
	
	<resultMap id="workgroupUserResult" extends="common.markedObjectResult" type="workgroupRole">
		<id property="userRoleId" column="USER_ID" />
		<result property="type" column="TYPE" />
		<result property="fromDate" column="FROM_DATE" />
		<result property="thruDate" column="THRU_DATE" />
		<association property="user" javaType="user">
			<id property="userId" column="USER_ID" />
			<id property="knoxId" column="KNOX_ID" />
			<result property="userName" column="USER_NAME" />
		</association>
	</resultMap>
	
	<sql id="tableWorkgroupRole">TN_CF_WORKGROUP_ROLE</sql>
	
	<select id="selectRoleWorkgroupList" parameterType="java.util.HashMap" resultMap="workgroup.workgroupResult">
		SELECT W.WORKGROUP_ID, W.WORKGROUP_NAME, W.DESCRIPTION,
		       W.LABEL, W.LABEL_JSON
		FROM   <include refid="workgroup.tableWorkgroup" /> W,
		       <include refid="tableWorkgroupRole" /> WR
		WHERE  W.WORKGROUP_ID = WR.WORKGROUP_ID
		AND    WR.ROLE_ID = #{roleId}
		ORDER BY ${orderBy} ASC
	</select>
	
	<insert id="insertWorkgroupRole" parameterType="workgroupRole">
		INSERT INTO <include refid="tableWorkgroupRole" />
		(WORKGROUP_ID, USER_ROLE_ID, FROM_DATE, THRU_DATE, ROLE_ID, USER_ID,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{workgroupId}, #{userRoleId}, NVL(#{fromDate}, TO_CHAR(SYSDATE, 'YYYYMMDD')), #{thruDate}, #{roleId}, #{userId},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<update id="updateWorkgroupRole" parameterType="workgroupRole">
		UPDATE <include refid="tableWorkgroupRole" />
		SET    FROM_DATE = NVL(#{fromDate}, TO_CHAR(SYSDATE, 'YYYYMMDD')),
		       THRU_DATE = #{thruDate},
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  WORKGROUP_ID = #{workgroupId}
		AND    USER_ROLE_ID = #{userRoleId}
	</update>
	
	<delete id="deleteWorkgroupRole" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableWorkgroupRole" />
		WHERE  WORKGROUP_ID = #{workgroupId}
		AND    USER_ROLE_ID = #{userRoleId}
	</delete>
	
	<select id="selectWorkgroupRoleList" parameterType="java.util.HashMap" resultMap="workgroupRoleResult">
		SELECT 'ROLE' TYPE, R.ROLE_ID, R.ROLE_NAME,
		       R.LABEL, R.LABEL_JSON,
		       WR.FROM_DATE, WR.THRU_DATE
		FROM   <include refid="tableWorkgroupRole" /> WR,
		       <include refid="role.tableRole" /> R
		WHERE  WR.USER_ROLE_ID = R.ROLE_ID
		AND    R.DELETE_YN = '0'
		AND    WR.WORKGROUP_ID = #{workgroupId}
	</select>
	
	<select id="selectWorkgroupUserList" parameterType="java.util.HashMap" resultMap="workgroupUserResult">
		SELECT 'USER' TYPE, U.USER_ID, U.KNOX_ID, U.USER_NAME,
		       WR.FROM_DATE, WR.THRU_DATE
		FROM   <include refid="tableWorkgroupRole" /> WR,
		       <include refid="user.tableUser" /> U
		WHERE  WR.USER_ROLE_ID = U.USER_ID
		AND    WR.WORKGROUP_ID = #{workgroupId}
	</select>
	
	<delete id="deleteWorkgroupRoleByUserRoleId" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableWorkgroupRole" />
		WHERE  USER_ROLE_ID = #{userRoleId}
	</delete>
	
	<delete id="deleteWorkgroupRoleByThruDate" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableWorkgroupRole" />
		WHERE  USER_ID IS NOT NULL
		AND    THRU_DATE <![CDATA[ <= ]]> #{expiredDate}
	</delete>
	
	<select id="selectUserIdFromWorkgroupRoleByThruDate" parameterType="java.util.HashMap" resultType="String">
		SELECT USER_ID
		FROM   <include refid="tableWorkgroupRole" />
		WHERE  USER_ID IS NOT NULL
		AND    THRU_DATE = #{alarmDate}
		GROUP BY USER_ID
	</select>

</mapper>