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

    <resultMap id="templateResult" extends="common.markedObjectResult" type="template">
        <id property="id" column="ID"/>
        <result property="templateKey" column="TEMPLATE_KEY"/>
        <result property="title" column="TITLE"/>
        <result property="description" column="DESCRIPTION"/>
        <result property="content" column="CONTENT"/>
        <result property="fileLocation" column="FILE_LOCATION"/>
        <result property="firstRegDatetime"     column="FIRST_REG_DATETIME" jdbcType="TIMESTAMP" />
        <result property="firstRegrId"          column="FIRST_REGR_ID"/>
        <result property="lastModDatetime"      column="LAST_MOD_DATETIME" jdbcType="TIMESTAMP"/>
        <result property="lastModrId"           column="LAST_MODR_ID"/>
        <result property="templateGroupCode" column="TEMPLATE_GROUPT_CODE"/>
    </resultMap>

	<sql id="tableTemplate">TN_CF_TEMPLATE</sql>
	
	<sql id="columnsTemplate">
		ID, TEMPLATE_KEY, TITLE, DESCRIPTION, CONTENT, FILE_LOCATION, FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID, TEMPLATE_GROUP_CODE
	</sql>
	
    <sql id="conditionTemplate">
        <trim prefix="WHERE" prefixOverrides="AND|OR">
            <if test="@com.samsung.SdlComparator@isNotEmpty(title)">
                TITLE LIKE '%' || #{title} || '%'
            </if>
            <if test="@com.samsung.SdlComparator@isNotEmpty(templateGroupCode)">
              AND TEMPLATE_GROUP_CODE = #{templateGroupCode}
            </if>
            <if test="@com.samsung.SdlComparator@isNotEmpty(templateKey)">
              AND TEMPLATE_KEY = #{templateKey}
            </if>
            <if test="@com.samsung.SdlComparator@isNotEmpty(id)">
              AND ID = #{id}
            </if>
        </trim>
    </sql>

	<select id="selectTemplatePagingList" parameterType="templateSearchDto" resultMap="templateResult">
        SELECT *
        FROM (
	        SELECT ROW_NUMBER() OVER (
		        <if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
		            ORDER BY ${orderBy}
		        </if>
		        <if test="@com.samsung.SdlComparator@isEmpty(orderBy)">
		            ORDER BY FIRST_REG_DATETIME DESC
		        </if>
		        ) RNUM,
		        <include refid="columnsTemplate"/>
	        FROM <include refid="tableTemplate"/>
	        <include refid="conditionTemplate"/>
        ) T
        WHERE T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
    </select>
    
    <select id="countTemplate" parameterType="java.util.HashMap" resultType="int">
        SELECT COUNT(*) AS TOTAL
          FROM <include refid="tableTemplate"/>
        <include refid="conditionTemplate"/>
    </select>
    
    <select id="selectTemplateList" parameterType="java.util.HashMap"  resultMap="templateResult">
        SELECT <include refid="columnsTemplate"/>
        FROM <include refid="tableTemplate"/>
        <include refid="conditionTemplate"/>
    </select>

    <select id="selectTemplate" parameterType="java.util.HashMap"  resultMap="templateResult">
        SELECT <include refid="columnsTemplate"/>
          FROM <include refid="tableTemplate"/>
        <include refid="conditionTemplate"/>
    </select>

    <update id="updateTemplate" parameterType="template">
        UPDATE <include refid="tableTemplate"/>
           SET
            	TITLE = #{title},
	            TEMPLATE_KEY = #{templateKey},
	            DESCRIPTION = #{description},
	            <if test="@com.samsung.SdlComparator@isNotEmpty(content)">
	            CONTENT = #{content},
	            </if>
	            <if test="@com.samsung.SdlComparator@isNotEmpty(fileLocation)">
	            FILE_LOCATION = #{fileLocation},
	            </if>
	            LAST_MOD_DATETIME = #{lastModDatetime},
	            LAST_MODR_ID = #{lastModrId}
         WHERE	ID = #{id}
    </update>

    <insert id="insertTemplate" parameterType="template">
        INSERT INTO <include refid="tableTemplate"/>
        ( ID, TEMPLATE_KEY, TITLE, DESCRIPTION, CONTENT, FILE_LOCATION, FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID, TEMPLATE_GROUP_CODE )
        VALUES
        ( #{id}, #{templateKey}, #{title}, #{description}, #{content}, #{fileLocation}, #{firstRegDatetime}, #{firstRegrId,}, #{lastModDatetime}, #{lastModrId}, #{templateGroupCode} )
    </insert>

    <delete id="deleteTemplate"  parameterType="java.util.HashMap">
        DELETE FROM <include refid="tableTemplate"/>
         WHERE ID = #{id}
    </delete>

</mapper>























