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

	<resultMap id="menuUsePeriodListResult" type="menuUsePeriod">
		<id property="menuUsePeriodCode" column="MENU_USE_PERIOD_CODE" />
		<result property="menuUsePeriodName" column="MENU_USE_PERIOD_NAME" />
		<result property="monthChngUsePeriod" column="MONTH_CHNG_USE_PERIOD" />
	</resultMap>

	<resultMap id="groupCodeResult" type="commCodeType" extends="common.markedObjectResult" >
		<id property="commCodeTypeCode" column="COMM_CODE_TYPE_CODE"/>
		<result property="commCodeTypeName" column="COMM_CODE_TYPE_Name"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="deleted" column="DELETE_YN"/>
		<result property="label" column="LABEL"/>
		<result property="labelJson" column="LABEL_JSON"/>
	</resultMap>

	<resultMap id="commCodeResult" type="commCode" extends="common.markedObjectResult" >
		<id property="codeId" column="CODE_ID" />
		<result property="code" column="CODE"/>
		<result property="commCodeTypeId" column="COMM_CODE_TYPE_CODE"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="hierarchyLevel" column="HIERARCHY_LEVEL"/>
		<result property="upperCodeId" column="UPPER_CODE_ID"/>
		<result property="ord" column="ORD"/>
		<result property="deleted" column="DELETE_YN"/>
		<result property="label" column="LABEL"/>
		<result property="labelJson" column="LABEL_JSON"/>
		<!--공통코드트리 내 계층 이동 시 필요-->
		<!--<association property="parent" column="{codeId=UPPER_CODE_ID}" select="selectCommCode"/>-->
	</resultMap>
	
	<sql id="tableCommCode">TC_CF_COMM_CODE</sql>
	<sql id="tableCommCodeType">TC_CF_COMM_CODE_TYPE</sql>
	<sql id="tableMenuUsePeriod">TC_CF_MENU_USE_PERIOD</sql>
	
	<sql id="columnsCommCode">
	CODE_ID, CODE, UPPER_CODE_ID, DESCRIPTION, COMM_CODE_TYPE_CODE, HIERARCHY_LEVEL,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
	ORD, LABEL, LABEL_JSON, DELETE_YN
	</sql>

	<sql id="columnsCommCodeType">
	COMM_CODE_TYPE_CODE, COMM_CODE_TYPE_NAME, DESCRIPTION,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
	LABEL, LABEL_JSON, DELETE_YN
	</sql>

	<sql id="conditionCommCodeType">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="@com.samsung.SdlComparator@isNotEmpty(searchKeyword)">
				<if test="searchCondition.equals('commCodeTypeCode')">
					AND LOWER(COMM_CODE_TYPE_CODE) LIKE '%' + LOWER(#{searchKeyword}) + '%'
				</if>
				<if test="searchCondition.equals('label')">
					OR (LOWER(LABEL_JSON) LIKE '%' + LOWER(#{searchKeyword}) + '%' OR LOWER(LABEL) LIKE '%' + LOWER(#{searchKeyword}) + '%')
				</if>
				<if test="searchCondition.equals('description')">
					AND LOWER(DESCRIPTION) LIKE '%' + LOWER(#{searchKeyword}) + '%'
				</if>
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(commCodeTypeCode) and @com.samsung.SdlComparator@isEmpty(searchKeyword)">
				AND COMM_CODE_TYPE_CODE = #{commCodeTypeCode}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(commCodeTypeId)">
				AND COMM_CODE_TYPE_CODE = #{commCodeTypeId}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(deleted)">
				AND DELETE_YN = #{deleted}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(description)">
				AND LOWER(DESCRIPTION) LIKE '%' + LOWER(#{description}) + '%'
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(label)">
				AND LOWER(LABEL) LIKE '%' + LOWER(#{Label}) + '%'
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(labelJson)">
				AND LOWER(LABEL_JSON) LIKE '%' + LOWER(#{labelJson}) + '%'
			</if>
		</trim>
	</sql>

	<sql id="conditionCommCode">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="@com.samsung.SdlComparator@isNotEmpty(code)">
				CODE = #{code}
			</if>
			<if test="_parameter.containsKey('codeId')">
				<choose>
					<when test="@com.samsung.SdlComparator@isNotEmpty(codeId)">
						AND CODE_ID = #{codeId}
					</when>
					<otherwise>
						AND CODE_ID IS NULL
					</otherwise>
				</choose>
			</if>
			<if test="_parameter.containsKey('upperCodeId')">
				<if test="@com.samsung.SdlComparator@isNotEmpty(upperCodeId)">
					AND UPPER_CODE_ID = #{upperCodeId}
				</if>
				<if test="@com.samsung.SdlComparator@isEmpty(upperCodeId)">
					AND UPPER_CODE_ID IS NULL
				</if>
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(commCodeTypeId)">
				AND COMM_CODE_TYPE_CODE = #{commCodeTypeId}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(deleted)">
				AND DELETE_YN = #{deleted}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(codeIds)">
				AND CODE_ID IN
				<foreach item="codeId" index="index" collection="codeIds" open="(" separator="," close=")">
					 #{codeId}
				</foreach>
			</if>
		</trim>
	</sql>

	<select id="selectMenuUsePeriodList" resultMap="menuUsePeriodListResult">
		SELECT MENU_USE_PERIOD_CODE, MENU_USE_PERIOD_NAME, MONTH_CHNG_USE_PERIOD
		FROM   <include refid="tableMenuUsePeriod" />
		<if test="@com.samsung.SdlComparator@isNotEmpty(menuUsePeriodCode)">
		WHERE  MENU_USE_PERIOD_CODE = #{menuUsePeriodCode}
		</if>
		ORDER BY MENU_USE_PERIOD_CODE
	</select>

	<select id="selectGroupCode" resultMap="groupCodeResult">
		SELECT COMM_CODE_TYPE_CODE, COMM_CODE_TYPE_NAME, DESCRIPTION,
		       FIRST_REG_DATETIME,
		       <if test="@com.samsung.SdlComparator@isNotEmpty(commCodeTypeCode)">
		       (SELECT USER_NAME
		        FROM <include refid="tableCommCodeType" /> CODE
		            LEFT JOIN <include refid="user.tableUser" /> U
		                ON CODE.FIRST_REGR_ID = U.USER_ID
		        WHERE COMM_CODE_TYPE_CODE = #{commCodeTypeCode}) FIRST_REGR_NAME,
		       </if>
		       LAST_MOD_DATETIME,
		       <if test="@com.samsung.SdlComparator@isNotEmpty(commCodeTypeCode)">
		       (SELECT USER_NAME
		        FROM <include refid="tableCommCodeType" /> CODE
		            LEFT JOIN <include refid="user.tableUser" /> U
		                ON CODE.LAST_MODR_ID = U.USER_ID
		        WHERE COMM_CODE_TYPE_CODE = #{commCodeTypeCode}) LAST_MODR_NAME,
		       </if>
			   LABEL, LABEL_JSON, DELETE_YN
		FROM   <include refid="tableCommCodeType" />
		<include refid="conditionCommCodeType" />
	</select>

	<select id="selectCommCode" resultMap="commCodeResult">
		SELECT CODE_ID, CODE, UPPER_CODE_ID, DESCRIPTION, COMM_CODE_TYPE_CODE,
		       FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
		       ORD, HIERARCHY_LEVEL,
			   LABEL, LABEL_JSON, DELETE_YN
		FROM   <include refid="tableCommCode" />
		<include refid="conditionCommCode" />
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
		ORDER BY ${orderBy}
		</if>
	</select>

	<select id="countGroupCode" parameterType="java.util.Map" resultType="int">
		SELECT COUNT(*) AS TOTAL
		FROM   <include refid="tableCommCodeType" />
		<include refid="conditionCommCodeType" />
	</select>

	<select id="selectGroupCodePagingList" resultMap="groupCodeResult">
		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 COMM_CODE_TYPE_CODE</if>) / (#{pageSize} * 1.0) ) AS PAGE
		        FROM (SELECT <include refid="columnsCommCodeType" />
		              FROM   <include refid="tableCommCodeType" />
		              <include refid="conditionCommCodeType" />) T1 ) T
		WHERE  PAGE = #{pageIndex}
	</select>

	<select id="selectCommCodeListByParentCode" parameterType="java.util.HashMap" resultType="java.util.HashMap">
		WITH CODE AS
		(SELECT CODE_ID, CODE, UPPER_CODE_ID,
		        COMM_CODE_TYPE_CODE, DELETE_YN
		 FROM   <include refid="tableCommCode" />
		 <trim prefix="WHERE" prefixOverrides="AND|OR">
		 <if test="@com.samsung.SdlComparator@isNotEmpty(codeId)" >
		 AND    CODE_ID = #{codeId}
		 </if>
		 <if test="@com.samsung.SdlComparator@isNotEmpty(commCodeTypeId)" >
		 AND    COMM_CODE_TYPE_CODE = #{commCodeTypeId}
		 AND    UPPER_CODE_ID IS NULL
		 </if>
		 </trim>
		 UNION ALL
		 SELECT TC.CODE_ID, TC.CODE, TC.UPPER_CODE_ID,
		        TC.COMM_CODE_TYPE_CODE, TC.DELETE_YN
		 FROM   <include refid="tableCommCode" /> TC,
		        CODE CO
		 WHERE  TC.UPPER_CODE_ID = CO.CODE_ID)
		SELECT CODE_ID AS "codeId", CODE AS "code", UPPER_CODE_ID AS "upperCodeId",
		       COMM_CODE_TYPE_CODE AS "commCodeTypeCode", DELETE_YN AS "deleteYn"
		FROM   CODE
		WHERE  DELETE_YN = '0'
	</select>

	<insert id="insertGroupCode" parameterType="commCodeType">
		INSERT INTO <include refid="tableCommCodeType" />
		(<include refid="columnsCommCodeType" />)
		VALUES
		(#{commCodeTypeCode}, #{commCodeTypeName}, #{description},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId},
	  	 #{label}, #{labelJson}, #{deleted})
	</insert>

	<insert id="insertCommCode" parameterType="commCode">
		INSERT INTO <include refid="tableCommCode" />
		(<include refid="columnsCommCode" />)
		VALUES
		(#{codeId}, #{code}, #{upperCodeId}, #{description}, #{commCodeTypeId}, #{hierarchyLevel},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId},
	 	 #{ord}, #{label}, #{labelJson}, #{deleted})
	</insert>

	<update id="updateGroupCode" parameterType="commCodeType">
		UPDATE <include refid="tableCommCodeType" />
		SET    COMM_CODE_TYPE_NAME	= #{commCodeTypeName},
		       DESCRIPTION = #{description},
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId},
		   	   LABEL = #{label},
		       LABEL_JSON = #{labelJson},
		       DELETE_YN = #{deleted}
		WHERE  COMM_CODE_TYPE_CODE = #{commCodeTypeCode}
	</update>

	<update id="updateCommCode" parameterType="commCode">
		UPDATE <include refid="tableCommCode" />
		SET    CODE = #{code},
		       UPPER_CODE_ID = #{upperCodeId},
		       DESCRIPTION = #{description},
		       COMM_CODE_TYPE_CODE = #{commCodeTypeId},
		       HIERARCHY_LEVEL = #{hierarchyLevel},
		       LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId},
		       ORD = #{ord},
			   LABEL = #{label},
			   LABEL_JSON = #{labelJson},
			   DELETE_YN = #{deleted}
		WHERE  CODE_ID = #{codeId}
	</update>

	<update id="updateCommCodeDeleted" parameterType="commCode">
		UPDATE <include refid="tableCommCode" />
		SET    CODE = #{code},
		       DELETE_YN = #{deleted}
		WHERE  CODE_ID = #{codeId}
	</update>

</mapper>