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

    <resultMap id="linkSiteResult" extends="common.markedObjectResult" type="linkSite">
        <id property="linkSiteId" column="LiNK_SITE_ID"/>
        <result property="label" column="LABEL" />
        <result property="labelJson" column="LABEL_JSON" />
        <result property="description" column="DESCRIPTION"/>
        <result property="url" column="URL"/>
        <association property="linkType" resultMap="linkTypeCodeResult" />
    </resultMap>
    
    <resultMap id="linkTypeCodeResult" type="commCode">
		<id property="codeId" column="LINK_TYPE_ID" />
        <result property="label" column="LINK_TYPE_LABEL" />
        <result property="labelJson" column="LINK_TYPE_LABEL_JSON" />
	</resultMap>

    <sql id="tableLinkSite">
		TN_CF_LINK_SITE
	</sql>

    <sql id="columnsLinkSite">
        LINK_SITE_ID, LINK_TYPE_ID,
        <include refid="tableLinkSite"/>.DESCRIPTION,
        <include refid="tableLinkSite"/>.URL,
        <include refid="tableLinkSite"/>.FIRST_REG_DATETIME,
        <include refid="tableLinkSite"/>.FIRST_REGR_ID,
        <include refid="tableLinkSite"/>.LAST_MOD_DATETIME,
        <include refid="tableLinkSite"/>.LAST_MODR_ID,
        <include refid="tableLinkSite"/>.LABEL,
        <include refid="tableLinkSite"/>.LABEL_JSON
    </sql>
    <sql id="conditionLinkSite">
        <trim prefix="WHERE" prefixOverrides="AND|OR">
            <include refid="commCode.tableCommCode"/>.CODE_ID =<include refid="tableLinkSite"/>.LINK_TYPE_ID
            <if test="@com.samsung.SdlComparator@isNotEmpty(linkSiteId)">
                AND LINK_SITE_ID = #{linkSiteId}
            </if>
            <if test="@com.samsung.SdlComparator@isNotEmpty(linkSiteIds)">
                AND LINK_SITE_ID IN
                <foreach item="linkSiteId" index="index" collection="linkSiteIds" open="(" separator="," close=")">
                    #{linkSiteId}
                </foreach>
            </if>
            <if test="@com.samsung.SdlComparator@isNotEmpty(linkTypeId)">
                AND LINK_TYPE_ID = #{linkTypeId}
            </if>
        </trim>
    </sql>
    <select id="selectLinkSite" parameterType="java.util.HashMap" resultMap="linkSiteResult">
        SELECT
        <include refid="columnsLinkSite"/>,
        TC_CF_COMM_CODE.LABEL as LINK_TYPE_LABEL,
        TC_CF_COMM_CODE.LABEL_JSON as LINK_TYPE_LABEL_JSON
        FROM<include refid="tableLinkSite"/>,
        <include refid="commCode.tableCommCode"/>
        <include refid="conditionLinkSite"/>
        <if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
            ORDER BY ${orderBy}
        </if>
        <if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">
            ORDER BY FIRST_REG_DATETIME DESC
        </if>
    </select>
    <select id="selectLinkSitePagingList" parameterType="java.util.HashMap" resultMap="linkSiteResult">
		SELECT *
		FROM   (SELECT T.*,
		               CEILING(ROW_NUMBER() OVER(
		               <if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>
		               <if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">ORDER BY FIRST_REG_DATETIME DESC</if>
		               )/(#{pageSize}*1.0)) PAGE
		        FROM   (SELECT <include refid="columnsLinkSite" />,
                            TC_CF_COMM_CODE.LABEL as LINK_TYPE_LABEL,
				        	TC_CF_COMM_CODE.LABEL_JSON as LINK_TYPE_LABEL_JSON
						FROM <include refid="tableLinkSite"/>,
        					<include refid="commCode.tableCommCode"/>
		                	<include refid="conditionLinkSite" />) T) P
		WHERE  P.PAGE = #{pageIndex}
	</select>
    <select id="countLinkSite" parameterType="java.util.HashMap" resultType="int">
        SELECT count(*) as total
        FROM
        <include refid="tableLinkSite"/>,
        <include refid="commCode.tableCommCode"/>
        <include refid="conditionLinkSite"/>
    </select>
    <insert id="insertLinkSite" parameterType="linkSite">
        INSERT INTO
        	<include refid="tableLinkSite"/>
            (   LINK_SITE_ID, LINK_TYPE_ID, LABEL, LABEL_JSON, DESCRIPTION,
                URL, FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID
            )
            VALUES (
	        #{linkSiteId},
	        #{linkType.codeId},
            #{label},
            #{labelJson},
	        #{description},
	        #{url},
	        #{firstRegDatetime,jdbcType=TIMESTAMP},
	        #{firstRegrId},
	        #{lastModDatetime,jdbcType=TIMESTAMP},
	        #{lastModrId}
        )
    </insert>
    <update id="updateLinkSite" parameterType="linkSite">
        UPDATE
        	<include refid="tableLinkSite"/>
        SET
	        LINK_TYPE_ID=#{linkType.codeId},
	        DESCRIPTION=#{description},
            LABEL=#{label},
            LABEL_JSON=#{labelJson},
	        URL=#{url},
	        LAST_MOD_DATETIME=#{lastModDatetime,jdbcType=TIMESTAMP},
	        LAST_MODR_ID=#{lastModrId}
        WHERE LINK_SITE_ID=#{linkSiteId}
    </update>
    <delete id="deleteLinkSite" parameterType="java.util.HashMap">
        DELETE FROM
        <include refid="tableLinkSite"/>
        <trim prefix="WHERE" prefixOverrides="AND|OR">
            <if test="@com.samsung.SdlComparator@isNotEmpty(linkSiteId)">
                LINK_SITE_ID = #{linkSiteId}
            </if>
        </trim>
    </delete>
</mapper>
