<?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="workgroup">
	
	<resultMap id="workgroupResult" extends="common.markedObjectResult" type="workgroup">
		<id property="workgroupId" column="WORKGROUP_ID" />
		<result property="workgroupName" column="WORKGROUP_NAME" />
		<result property="description" column="DESCRIPTION" />
		<result property="label" column="LABEL" />
		<result property="labelJson" column="LABEL_JSON" />
		<result property="deleted" column="DELETE_YN" />
	</resultMap>

	<sql id="tableWorkgroup">TN_CF_WORKGROUP</sql>

	<sql id="columnsWorkgroup">
	WORKGROUP_ID, WORKGROUP_NAME, DESCRIPTION, LABEL, LABEL_JSON,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID, DELETE_YN
	</sql>

	<sql id="conditionWorkgroup">
		WHERE  DELETE_YN = '0'
		AND    WORKGROUP_NAME != 'USER_AUTH_MENU'
		<if test="@com.samsung.SdlComparator@isNotEmpty(workgroupId)">
		AND WORKGROUP_ID = #{workgroupId}
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(workgroupName)">
		AND WORKGROUP_NAME LIKE CONCAT('%', #{workgroupName}, '%')
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(label)">
		AND (LABEL LIKE CONCAT('%', #{label}, '%') OR LABEL_JSON LIKE CONCAT('%', #{label}, '%'))
		</if>
	</sql>
	
	<select id="selectWorkgroupPagingList" parameterType="java.util.HashMap" resultMap="workgroupResult">
		SELECT *
		FROM   (SELECT ROW_NUMBER() OVER (<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>) RNUM,
		        	   <include refid="columnsWorkgroup" />
		        FROM   <include refid="tableWorkgroup" />
		        <include refid="conditionWorkgroup" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
	</select>
	
	<select id="countWorkgroup" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(WORKGROUP_ID) TOTAL
		FROM   <include refid="tableWorkgroup" />
		<include refid="conditionWorkgroup" />
	</select>
	
	<select id="selectWorkgroup" parameterType="java.util.HashMap" resultMap="workgroupResult">
		SELECT <include refid="columnsWorkgroup" />
		FROM   <include refid="tableWorkgroup" />
		<include refid="conditionWorkgroup" />
	</select>
	
	<insert id="insertWorkgroup" parameterType="workgroup">
		INSERT INTO <include refid="tableWorkgroup" />
		(<include refid="columnsWorkgroup" />)
		VALUES
		(#{workgroupId}, #{workgroupName}, #{description}, #{label}, #{labelJson},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId}, '0')
	</insert>
	
	<update id="updateWorkgroup" parameterType="workGroup">
		UPDATE <include refid="tableWorkgroup" />
		SET    WORKGROUP_NAME = #{workgroupName},
		       DESCRIPTION = #{description},
		       LABEL = #{label},
		       LABEL_JSON = #{labelJson},
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  WORKGROUP_ID = #{workgroupId}
	</update>
	
	<delete id="deleteWorkgroup" parameterType="java.util.HashMap">
		UPDATE <include refid="tableWorkgroup" />
		SET    DELETE_YN = '1',
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  WORKGROUP_ID = #{workgroupId}
	</delete>

	<select id="selectUserAuthWorkgroup" resultMap="workgroupResult">
		SELECT <include refid="columnsWorkgroup" />
		FROM   <include refid="tableWorkgroup" />
		WHERE  WORKGROUP_NAME = 'USER_AUTH_MENU'
	</select>

</mapper>