<?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="sysResource">

	<resultMap id="sysResourceResult"  extends="common.markedObjectResult" type="sysResource" >
		<id property="sysResourceId" column="SYS_RESOURCE_ID"/>
		<result property="sysResourceTypeCodeId" column="SYS_RESOURCE_TYPE_CODE_ID" />
		<result property="sysResourceName" column="SYS_RESOURCE_NAME"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="fromDate" column="FROM_DATE"/>
		<result property="thruDate" column="THRU_DATE"/>
		<result property="sysId" column="SYS_ID"/>
		<result property="upperSysResourceId" column="UPPER_SYS_RESOURCE_ID"/>
		<result property="deleted" column="DELETE_YN"/>
		<result property="sysResourceUrl" column="SYS_RESOURCE_URL"/>
		<association property="sysResourceType" column="SYS_RESOURCE_TYPE_CODE_ID" javaType="commCode" >
			<id property="codeId" column="CODE_ID"/>
			<result property="code" column="CODE"/>
			<result property="hierarchyLevel" column="HIERARCHY_LEVEL"/>
			<result property="label" column="LABEL"/>
			<result property="labelJson" column="LABEL_JSON"/>
			<result property="deleted" column="DELETE_YN"/>
		</association>
		<association property="firstRegrUser" javaType="user" >
				<result property="userName" column="FIRST_REGR_NAME"/>
				<result property="userId" column="FIRST_REGR_ID"/>
		</association>
	</resultMap>

	<sql id="tableSysResource">TN_CF_SYS_RESOURCE</sql>
	<sql id="tableSysResourceMapp">TN_CF_SYS_RESOURCE_MAPP</sql>
	<sql id="tableSysAuthorization">TN_CF_SYS_AUTHORIZATION</sql>
	<sql id="tableCommCode">TC_CF_COMM_CODE</sql>

	<sql id="conditionSysResource">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			COMM.CODE_ID = RES.SYS_RESOURCE_TYPE_CODE_ID
			<if test="@com.samsung.SdlComparator@isNotEmpty(sysResourceId)">
				AND	SYS_RESOURCE_ID = #{sysResourceId}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(sysResourceName)">
				AND	SYS_RESOURCE_NAME = #{sysResourceName}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(sysResourceTypeCodeId)">
				AND	SYS_RESOURCE_TYPE_CODE_ID = #{sysResourceTypeCodeId}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(deleted)">
				AND RES.DELETE_YN = #{deleted}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(sysResourceIds)">
				AND SYS_RESOURCE_ID IN
				<foreach item="sysResourceId" index="index" collection="sysResourceIds" open="(" separator="," close=")">
					#{sysResourceId}
				</foreach>
			</if>
			<if test="_parameter.containsKey('sysResourceTypeCodeIds')">
				<choose>
					<when test="@com.samsung.SdlComparator@isNotEmpty(sysResourceTypeCodeIds)">
						AND SYS_RESOURCE_TYPE_CODE_ID IN
						<foreach item="sysResourceTypeCodeId" index="index" collection="sysResourceTypeCodeIds" open="(" separator="," close=")">
							#{sysResourceTypeCodeId}
						</foreach>
					</when>
					<otherwise>
						AND 1=0
					</otherwise>
				</choose>
			</if>
		</trim>
	</sql>

	<select id="selectSysResourcePagingList" parameterType="java.util.HashMap" resultMap="sysResourceResult">
		SELECT *
		FROM   (SELECT T1.*,
		               CEILING(ROW_NUMBER() OVER (<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>
		                                          <if test="@com.samsung.SdlComparator@isEmpty(orderBy)" >ORDER BY SYS_RESOURCE_NAME</if>) / (#{pageSize} * 1.0) ) AS PAGE
		        FROM   (SELECT SYS_RESOURCE_ID, UPPER_SYS_RESOURCE_ID,
		                       RES.SYS_RESOURCE_TYPE_CODE_ID, COMM.CODE_ID, COMM.CODE,
		                       COMM.HIERARCHY_LEVEL, COMM.LABEL, COMM.LABEL_JSON,
		                       SYS_RESOURCE_NAME, SYS_ID, RES.DESCRIPTION,
		                       RES.FROM_DATE, RES.THRU_DATE,
		                       RES.FIRST_REG_DATETIME, RES.FIRST_REGR_ID,
		                       (SELECT USER_NAME
		                        FROM <include refid="user.tableUser" />
		                        WHERE USER_ID = RES.FIRST_REGR_ID) FIRST_REGR_NAME,
		                       RES.LAST_MOD_DATETIME, RES.LAST_MODR_ID,
		                       SYS_RESOURCE_URL
		                FROM <include refid="tableSysResource" /> RES,
		                     <include refid="tableCommCode" /> COMM
		                <include refid="conditionSysResource" />) T1 ) T
		WHERE  PAGE = #{pageIndex}
	</select>

	<select id="selectSysResource" parameterType="java.util.HashMap" resultMap="sysResourceResult">
		SELECT SYS_RESOURCE_ID, UPPER_SYS_RESOURCE_ID,
		       RES.SYS_RESOURCE_TYPE_CODE_ID, COMM.CODE_ID, COMM.CODE,
		       COMM.HIERARCHY_LEVEL, COMM.LABEL, COMM.LABEL_JSON, COMM.DELETE_YN,
		       SYS_RESOURCE_NAME, SYS_ID, RES.DESCRIPTION,
		       RES.FROM_DATE, RES.THRU_DATE,
		       RES.FIRST_REG_DATETIME, RES.FIRST_REGR_ID, RES.LAST_MOD_DATETIME, RES.LAST_MODR_ID,
		       RES.DELETE_YN, SYS_RESOURCE_URL
		FROM   <include refid="tableSysResource" /> RES,
		       <include refid="tableCommCode" /> COMM
		<include refid="conditionSysResource" />
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
		ORDER BY ${orderBy}
		</if>
	</select>

	<select id="countSysResource" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(*) AS TOTAL
		FROM   <include refid="tableSysResource" /> RES,
		       <include refid="tableCommCode" /> COMM
		<include refid="conditionSysResource" />
	</select>

	<insert id="insertSysResource" parameterType="sysResource">
		INSERT INTO <include refid="tableSysResource" />
		(SYS_RESOURCE_ID, UPPER_SYS_RESOURCE_ID, SYS_ID,
		 SYS_RESOURCE_NAME, SYS_RESOURCE_TYPE_CODE_ID, DELETE_YN,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
		 DESCRIPTION, SYS_RESOURCE_URL)
		VALUES
		(#{sysResourceId}, #{upperSysResourceId}, #{sysId},
		 #{sysResourceName}, #{sysResourceTypeCodeId}, '0',
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId},
		 #{description}, #{sysResourceUrl})
	</insert>
	
	<insert id="insertSysResourceMapp" parameterType="sysResource">
		INSERT INTO <include refid="tableSysResourceMapp" />
		(MAIN_SYS_RESOURCE_ID, SUB_SYS_RESOURCE_ID,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{upperSysResourceId}, #{sysResourceId},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<insert id="insertSysAuthorization" parameterType="sysResource">
		INSERT INTO <include refid="tableSysAuthorization" />
		(SYS_RESOURCE_ID, AUTHORIZATION_ID,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{sysResourceId}, #{authorizationType},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>

	<update id="updateSysResource" parameterType="sysResource">
		UPDATE <include refid="tableSysResource" />
		SET    SYS_RESOURCE_NAME = #{sysResourceName},
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId},
			   DELETE_YN = #{deleted},
			   DESCRIPTION = #{description},
			   SYS_RESOURCE_TYPE_CODE_ID = #{sysResourceTypeCodeId},
			   SYS_RESOURCE_URL = #{sysResourceUrl}
		WHERE  SYS_RESOURCE_ID = #{sysResourceId}
	</update>
	
	<update id="updateSysAuthorization" parameterType="sysResource">
		UPDATE <include refid="tableSysAuthorization" />
		SET    AUTHORIZATION_ID = #{authorizationType},
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  SYS_RESOURCE_ID = #{sysResourceId}
	</update>
	
	<delete id="deleteSysResource" parameterType="java.util.HashMap">
		UPDATE <include refid="tableSysResource" />
		SET    DELETE_YN = '1',
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		<trim prefix="WHERE">
			<if test="@com.samsung.SdlComparator@isNotEmpty(sysResourceId)">
			SYS_RESOURCE_ID = #{sysResourceId}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(upperSysResourceId)">
			UPPER_SYS_RESOURCE_ID = #{upperSysResourceId}
			</if>
		</trim>
	</delete>
	
	<update id="deletePageResourceListByMenuId" parameterType="java.util.HashMap">
		UPDATE <include refid="tableSysResource" />
		SET    DELETE_YN = '1'
		WHERE  EXISTS (SELECT SRM.SUB_SYS_RESOURCE_ID
		               FROM   <include refid="tableSysResourceMapp" /> SRM,
		                      <include refid="page.tablePage" /> P
		               WHERE  SRM.SUB_SYS_RESOURCE_ID = P.PAGE_ID
		               AND    P.PAGE_ID = SYS_RESOURCE_ID
		               AND    SRM.MAIN_SYS_RESOURCE_ID = #{menuId})
	</update>
	
	<update id="deleteApiResourceListByMenuId" parameterType="java.util.HashMap">
		UPDATE <include refid="tableSysResource" />
		SET    DELETE_YN = '1'
		WHERE  EXISTS (SELECT SRM.SUB_SYS_RESOURCE_ID
		               FROM   <include refid="tableSysResourceMapp" /> SRM,
		                      <include refid="api.tableApi" /> A
		               WHERE  SRM.SUB_SYS_RESOURCE_ID = A.API_ID
		               AND    A.API_ID = SYS_RESOURCE_ID
		               AND    SRM.MAIN_SYS_RESOURCE_ID = #{menuId})
	</update>
	
</mapper>