<?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="mailGroup">
	
	<resultMap id="mailGroupResult" extends="common.markedObjectResult" type="mailGroup">
		<id property="mailGroupId" column="MAIL_GROUP_ID" />
		<result property="description" column="DESCRIPTION" />
		<result property="used" column="USED" />
		<result property="deleted" column="DELETED" />
		<result property="firstRegrName" column="FIRST_REGR_NAME" />
		<result property="label" column="LABEL" />
		<result property="labelJson" column="LABEL_JSON" />
	</resultMap>
	
	<resultMap id="mailGroupMappResult" type="mailGroupMapp">
		<id property="mailGroupId" column="MAIL_GROUP_ID" />
		<result property="type" column="TYPE" />
		<result property="mappId" column="MAPP_ID" />
		<result property="userId" column="USER_ID" />
		<result property="roleId" column="ROLE_ID" />
		<result property="workgroupId" column="WORKGROUP_ID" />
		<result property="userName" column="USER_NAME" />
		<result property="knoxId" column="KNOX_ID" />
		<result property="email" column="EMAIL" />
		<association property="role" column="{roleId=ROLE_ID}" select="role.selectRole" />
		<association property="workgroup" column="{workgroupId=WORKGROUP_ID}" select="workgroup.selectWorkgroup" />
	</resultMap>
	
	<sql id="tableMailGroup">TN_CF_MAIL_GROUP</sql>
	<sql id="tableMailGroupMapp">TN_CF_MAIL_GROUP_MAPP</sql>
	
	<sql id="columnMailGroup">
	MAIL_GROUP_ID, LABEL, LABEL_JSON,
	DESCRIPTION, USED, DELETED, 
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID
	</sql>
	
	<sql id="columnMailGroupMapp">
	MAIL_GROUP_ID, TYPE, MAPP_ID,
	USER_ID, ROLE_ID, WORKGROUP_ID,
	USER_NAME, KNOX_ID, EMAIL, REMARK
	</sql>
	
	<sql id="conditionMailGroup">
		<if test="@com.samsung.SdlComparator@isNotEmpty(label)">
		AND (LABEL LIKE CONCAT('%', #{label}, '%') OR LABEL_JSON LIKE CONCAT('%', #{label}, '%'))
		</if>
	</sql>
	
	<select id="selectMailGroupPagingList" parameterType="java.util.HashMap" resultMap="mailGroupResult">
		SELECT T.*
		FROM   (SELECT ROW_NUMBER() OVER(ORDER BY LABEL ASC) RNUM,
		               <include refid="columnMailGroup" />,
		               (SELECT USER_NAME
		                FROM   <include refid="user.tableUser" /> U
		                WHERE  U.USER_ID = M.FIRST_REGR_ID) FIRST_REGR_NAME
		        FROM   <include refid="tableMailGroup" /> M
		        WHERE  DELETED = '0'
		        <include refid="conditionMailGroup" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
	</select>
	
	<select id="countMailGroupList" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(MAIL_GROUP_ID) TOTAL
		FROM   <include refid="tableMailGroup" />
		WHERE  DELETED = '0'
		<include refid="conditionMailGroup" />
	</select>
	
	<select id="selectMailGroup" parameterType="java.util.HashMap" resultMap="mailGroupResult">
		SELECT <include refid="columnMailGroup" />,
		       (SELECT USER_NAME
		        FROM   <include refid="user.tableUser" /> U
		        WHERE  U.USER_ID = M.FIRST_REGR_ID) FIRST_REGR_NAME
		FROM   <include refid="tableMailGroup" /> M
		WHERE  MAIL_GROUP_ID = #{mailGroupId}
	</select>
	
	<insert id="insertMailGroup" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tableMailGroup" />
		(<include refid="columnMailGroup" />)
		VALUES
		(#{mailGroupId}, #{label}, #{labelJson},
		 #{description}, '1', '0',
		 #{firstRegDatetime}, #{firstRegrId}, #{lastModDatetime}, #{lastModrId})
	</insert>
	
	<update id="updateMailGroup" parameterType="java.util.HashMap">
		UPDATE <include refid="tableMailGroup" />
		SET    LABEL = #{label},
		       LABEL_JSON = #{labelJson},
		       DESCRIPTION = #{description},
		       USED = #{used},
		       LAST_MOD_DATETIME = #{lastModDatetime},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  MAIL_GROUP_ID = #{mailGroupId}
	</update>
	
	<delete id="deleteMailGroup" parameterType="java.util.HashMap">
		UPDATE <include refid="tableMailGroup" />
		SET    DELETED = '1',
		       LAST_MOD_DATETIME = #{lastModDatetime},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  MAIL_GROUP_ID = #{mailGroupId}
	</delete>
	
	<select id="selectMailGroupMappPagingList" parameterType="java.util.HashMap" resultMap="mailGroupMappResult">
		SELECT T.*
		FROM   (SELECT ROW_NUMBER() OVER(ORDER BY TYPE ASC) RNUM,
		               <include refid="columnMailGroupMapp" />
		        FROM   <include refid="tableMailGroupMapp" />
		        WHERE  MAIL_GROUP_ID = #{mailGroupId}) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
	</select>
	
	<select id="countMailGroupMappList" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(MAIL_GROUP_ID) TOTAL
		FROM   <include refid="tableMailGroupMapp" />
		WHERE  MAIL_GROUP_ID = #{mailGroupId}
	</select>
	
	<delete id="deleteMailGroupMapp" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableMailGroupMapp" />
		WHERE  MAIL_GROUP_ID = #{mailGroupId}
		AND    TYPE = #{type}
		AND    MAPP_ID = #{mappId}
	</delete>
	
	<insert id="insertMailGroupMapp" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tableMailGroupMapp" />
		(<include refid="columnMailGroupMapp" />)
		VALUES
		(#{mailGroupId}, #{type}, #{mappId},
		 #{userId}, #{roleId}, #{workgroupId},
		 #{knoxId}, #{userName}, #{email}, #{remark})
	</insert>
	
</mapper>