<?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="workgroupMenu">
	
	<resultMap id="workgroupMenuResult" extends="common.markedObjectResult" type="workgroupMenu">
		<id property="workgroupId" column="WORKGROUP_ID" />
		<id property="sysResourceId" column="SYS_RESOURCE_ID" />
		<id property="authorizationId" column="AUTHORIZATION_ID" />
		<association property="menu" javaType="menu">
			<result property="label" column="LABEL" />
			<result property="labelJson" column="LABEL_JSON" />
			<result property="used" column="USE_YN" />
			<result property="deleted" column="DELETE_YN" />
		</association>
	</resultMap>
	
	<sql id="tableWorkAuthorization">TN_CF_WORK_AUTHORIZATION</sql>
	
	<select id="selectWorkgroupMenuList" parameterType="java.util.HashMap" resultMap="workgroupMenuResult">
		WITH RECURSIVE TREE_TABLE(UPPER_SYS_RESOURCE_ID, MENU_ID, LABEL, LABEL_JSON, USE_YN, DELETE_YN) AS
		(SELECT SR.UPPER_SYS_RESOURCE_ID, M.MENU_ID,
		        CAST(M.LABEL AS NVARCHAR(100)) "LABEL", M.LABEL_JSON,
		        M.USE_YN, M.DELETE_YN
		 FROM   <include refid="menu.tableMenu" /> M,
		        <include refid="sysResource.tableSysResource" /> SR
		 WHERE  M.MENU_ID = SR.SYS_RESOURCE_ID
		 AND    M.MENU_LEVEL = 1
		 UNION ALL
		 SELECT SR.UPPER_SYS_RESOURCE_ID, M.MENU_ID,
		        CAST(TT.LABEL || ' > ' || M.LABEL AS NVARCHAR(100)) "LABEL", M.LABEL_JSON,
		        M.USE_YN, M.DELETE_YN
		 FROM   <include refid="menu.tableMenu" /> M,
		        <include refid="sysResource.tableSysResource" /> SR,
		        TREE_TABLE TT
		 WHERE  M.MENU_ID = SR.SYS_RESOURCE_ID
		 AND    SR.UPPER_SYS_RESOURCE_ID = TT.MENU_ID)
		SELECT WM.WORKGROUP_ID, WM.SYS_RESOURCE_ID, WM.AUTHORIZATION_ID,
		       TT.LABEL, TT.LABEL_JSON,
		       TT.USE_YN, TT.DELETE_YN
		FROM   <include refid="tableWorkAuthorization" /> WM,
		       TREE_TABLE TT
		WHERE  WM.SYS_RESOURCE_ID = TT.MENU_ID
		AND    WM.WORKGROUP_ID = #{workgroupId}
		ORDER BY TT.LABEL ASC, WM.AUTHORIZATION_ID DESC
	</select>
	
	<select id="selectWorkgroupMenuCntByAuthId" parameterType="workgroupMenu" resultType="int">
		SELECT COUNT(WORKGROUP_ID) CNT
		FROM   <include refid="tableWorkAuthorization" />
		WHERE  WORKGROUP_ID = #{workgroupId}
		AND    SYS_RESOURCE_ID = #{sysResourceId}
		AND    AUTHORIZATION_ID = #{authorizationId}
	</select>
	
	<insert id="insertWorkgroupMenu" parameterType="workgroupMenu">
		INSERT INTO <include refid="tableWorkAuthorization" />
		(WORKGROUP_ID, SYS_RESOURCE_ID, AUTHORIZATION_ID,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{workgroupId}, #{sysResourceId}, #{authorizationId},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<delete id="deleteWorkgroupMenu" parameterType="workgroupMenu">
		DELETE FROM <include refid="tableWorkAuthorization" />
		WHERE  SYS_RESOURCE_ID = #{sysResourceId}
		<if test="@com.samsung.SdlComparator@isNotEmpty(workgroupId)">
		AND    WORKGROUP_ID = #{workgroupId}
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(authorizationId)">
		AND    AUTHORIZATION_ID = #{authorizationId}
		</if>
	</delete>
	
</mapper>