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

    <resultMap id="htmlTemplateResult" extends="common.markedObjectResult" type="htmlTemplate">
        <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="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="tableHtmlTemplate">TN_CF_HTML_TEMPLATE</sql>
	
	<sql id="columnsHtmlTemplate">
	ID, TEMPLATE_KEY, TITLE, DESCRIPTION, CONTENT,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
	TEMPLATE_GROUP_CODE
	</sql>
	
    <sql id="conditionHtmlTemplate">
        <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="selectHtmlTemplatePagingList" parameterType="htmlTemplateSearchDto" resultMap="htmlTemplateResult">
        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="columnsHtmlTemplate" />
                FROM   <include refid="tableHtmlTemplate" />
                <include refid="conditionHtmlTemplate" />) T
        WHERE T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
    </select>
    
    <select id="countHtmlTemplate" parameterType="java.util.HashMap" resultType="int">
        SELECT COUNT(*) AS TOTAL
        FROM   <include refid="tableHtmlTemplate" />
        <include refid="conditionHtmlTemplate" />
    </select>
    
    <select id="selectHtmlTemplateList" parameterType="java.util.HashMap"  resultMap="htmlTemplateResult">
        SELECT <include refid="columnsHtmlTemplate" />
        FROM   <include refid="tableHtmlTemplate" />
        <include refid="conditionHtmlTemplate" />
    </select>

    <select id="selectHtmlTemplate" parameterType="java.util.HashMap"  resultMap="htmlTemplateResult">
        SELECT <include refid="columnsHtmlTemplate" />
        FROM   <include refid="tableHtmlTemplate" />
        <include refid="conditionHtmlTemplate" />
    </select>

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

    <insert id="insertHtmlTemplate" parameterType="htmlTemplate">
        INSERT INTO <include refid="tableHtmlTemplate" />
        (<include refid="columnsHtmlTemplate" />)
        VALUES
        (#{id}, #{templateKey}, #{title}, #{description}, #{content},
         #{firstRegDatetime}, #{firstRegrId,}, #{lastModDatetime}, #{lastModrId},
         #{templateGroupCode})
    </insert>

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

</mapper>