<?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="termsUse">
	<resultMap id="termsInfo"  type="termsUse" extends="common.markedObjectResult" >
		<id property="termsId" column="TERMS_ID"/>
		<result property="partId" column="PART_ID"/>
		<result property="langType" column="LANG_TYPE"/>
		<result property="checkMsg" column="CHECK_MSG"/>
		<result property="checkLabel" column="CHECK_LABEL"/>
		<result property="content" column="CONTENT"/>
		<result property="startDate" column="START_DATE" />
		<result property="mailForm" column="MAIL_FORM" />
		<result property="policyPage" column="POLICY_PAGE"/>
		<result property="agreePage" column="AGREE_PAGE"/>
		<result property="agreeReq" column="AGREE_REQ"/>
		<association property="termsCode" resultMap="termsCodeResult" />
		<association property="langTypeCode" resultMap="langTypeCodeResult" />
	</resultMap>
	
	<resultMap id="termsCodeResult" type="commCode">
		<id property="codeId" column="PART_ID" />
		<result property="label" column="PART_LABEL" />
		<result property="labelJson" column="PART_LABEL_JSON" />
	</resultMap>
	<resultMap id="langTypeCodeResult" type="commCode">
		<id property="codeId" column="LANG_TYPE" />
		<result property="label" column="LANG_LABEL" />
		<result property="labelJson" column="LANG_LABEL_JSON" />
	</resultMap>
	
	<resultMap id="userTermsAgreeResult" type="userTermsAgreeDto" extends="termsInfo">
		<result property="userId" column="USER_ID" />
		<result property="userName" column="USER_NAME" />
		<result property="termsId" column="TERMS_ID" />
		<result property="agree" column="AGREE" />
		<result property="agreeDatetime" column="AGREE_DATETIME" />
	</resultMap>
	
	<sql id="columnSelectTermsUseList">
		TU.TERMS_ID,
        TU.PART_ID,
        TU.LANG_TYPE,
        TU.CHECK_LABEL,
        TU.CHECK_MSG,
        TU.START_DATE,
        TU.FIRST_REG_DATETIME,
        TU.FIRST_REGR_ID,
        (SELECT USER_NAME FROM TN_CF_USER WHERE USER_ID = TU.FIRST_REGR_ID) AS FIRST_REGR_NAME,
        TU.LAST_MOD_DATETIME,
        TU.LAST_MODR_ID,
        (SELECT USER_NAME FROM TN_CF_USER WHERE USER_ID = TU.LAST_MODR_ID) AS LAST_MODR_NAME,
        TU.POLICY_PAGE,
        TU.AGREE_PAGE,
        TU.AGREE_REQ,
		CC_TERMS.LABEL AS PART_LABEL,
        CC_TERMS.LABEL_JSON AS PART_LABEL_JSON,
        CC_TERMS_LANG.LABEL AS LANG_LABEL,
        CC_TERMS_LANG.LABEL_JSON AS LANG_LABEL_JSON
	</sql>
	<sql id="columnSelectTermsUse">
		TU.TERMS_ID,
        TU.PART_ID,
        TU.LANG_TYPE,
        TU.CHECK_LABEL,
        TU.CHECK_MSG,
        TU.CONTENT,
        TU.START_DATE,
        TU.MAIL_FORM,
        TU.FIRST_REG_DATETIME,
        TU.FIRST_REGR_ID,
        (SELECT USER_NAME FROM TN_CF_USER WHERE USER_ID = TU.FIRST_REGR_ID) AS FIRST_REGR_NAME,
        TU.LAST_MOD_DATETIME,
        TU.LAST_MODR_ID,
        (SELECT USER_NAME FROM TN_CF_USER WHERE USER_ID = TU.LAST_MODR_ID) AS LAST_MODR_NAME,
        TU.POLICY_PAGE,
        TU.AGREE_PAGE,
        TU.AGREE_REQ,
		CC_TERMS.LABEL AS PART_LABEL,
        CC_TERMS.LABEL_JSON AS PART_LABEL_JSON,
        CC_TERMS_LANG.LABEL AS LANG_LABEL,
        CC_TERMS_LANG.LABEL_JSON AS LANG_LABEL_JSON
	</sql>
	<sql id="conditionSelectTermsUse">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
				TU.PART_ID = CC_TERMS.CODE_ID
			AND TU.LANG_TYPE = CC_TERMS_LANG.CODE
		 	<if test="@com.samsung.SdlComparator@isNotEmpty(termsId)">
				AND TU.TERMS_ID = #{termsId}
		 	</if>
		 	<if test="@com.samsung.SdlComparator@isNotEmpty(partId)">
		 		AND TU.PART_ID = #{partId}
		 	</if>
		 	<if test="@com.samsung.SdlComparator@isNotEmpty(langType)">
		 		AND TU.LANG_TYPE = #{langType}
		 	</if>
		</trim>
	</sql>
	<sql id="sqlSelectTermsUseList">
		SELECT 
			<include refid="columnSelectTermsUseList"/>
		FROM TN_CF_TERMS_USE TU,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS' AND DELETE_YN = '0') CC_TERMS,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS_LANG' AND DELETE_YN = '0') CC_TERMS_LANG
		<include refid="conditionSelectTermsUse"/>
	</sql>
	<sql id="sqlSelectTermsUse">
		SELECT 
			<include refid="columnSelectTermsUse"/>
		FROM TN_CF_TERMS_USE TU,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS' AND DELETE_YN = '0') CC_TERMS,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS_LANG' AND DELETE_YN = '0') CC_TERMS_LANG
		<include refid="conditionSelectTermsUse"/>
	</sql>
	<sql id="conditionSelectUserTermsAgree">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
				TU.TERMS_ID = TA.TERMS_ID
			AND	TU.PART_ID = CC_TERMS.CODE_ID
			AND TU.LANG_TYPE = CC_TERMS_LANG.CODE
		 	<if test="@com.samsung.SdlComparator@isNotEmpty(termsId)">
				AND TU.TERMS_ID = #{termsId}
		 	</if>
		 	<if test="@com.samsung.SdlComparator@isNotEmpty(partId)">
		 		AND TU.PART_ID = #{partId}
		 	</if>
		 	<if test="@com.samsung.SdlComparator@isNotEmpty(langType)">
		 		AND TU.LANG_TYPE = #{langType}
		 	</if>
		</trim>
	</sql>
	<sql id="sqlSelectUserTermsAgreeList">
		SELECT 
			TA.USER_ID,
    		(SELECT USER_NAME FROM TN_CF_USER WHERE USER_ID = TA.USER_ID) AS USER_NAME,
    		TA.AGREE,
    		TA.AGREE_DATETIME,
			<include refid="columnSelectTermsUseList"/>
		FROM TN_CF_TERMS_USE TU,
			TN_CF_USER_TERMS_AGREE TA,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS' AND DELETE_YN = '0') CC_TERMS,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS_LANG' AND DELETE_YN = '0') CC_TERMS_LANG
		<include refid="conditionSelectUserTermsAgree"/>
	</sql>
	
	<select id="selectTermsUsePagingList"  parameterType="java.util.HashMap" resultMap="termsInfo">
	select * 
			from ( 
		            select t1.*, ceiling( Row_Number() OVER (
				            <if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
								ORDER BY ${orderBy}
							</if>
							<if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">
				            	ORDER BY t1.START_DATE DESC, t1.FIRST_REG_DATETIME DESC
				            </if>
			            ) / (#{pageSize}*1.0) ) as page 
		            from 
		                (
							<include refid="sqlSelectTermsUseList"/>
						) t1             
                 )t2 where t2.page = #{pageIndex}
	</select>
	
	<select id="selectTermsUseList"  parameterType="java.util.HashMap" resultMap="termsInfo">
		<include refid="sqlSelectTermsUseList"/>
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
			ORDER BY ${orderBy}
		</if>
		<if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">
           	ORDER BY TU.START_DATE DESC, TU.FIRST_REG_DATETIME DESC
		</if>
	</select>
	
	<select id="selectTermsUse" parameterType="java.util.HashMap" resultMap="termsInfo" >
		<include refid="sqlSelectTermsUse"/>
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
			ORDER BY ${orderBy}
		</if>
		<if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">
           	ORDER BY TU.START_DATE DESC, TU.FIRST_REG_DATETIME DESC
		</if>
	</select>
	
	<select id="countTermsUse" parameterType="java.util.HashMap" resultType="int" >
		SELECT count(*) 
		FROM TN_CF_TERMS_USE TU,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS' AND DELETE_YN = '0') CC_TERMS,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS_LANG' AND DELETE_YN = '0') CC_TERMS_LANG
		<include refid="conditionSelectTermsUse"/>
	</select>
	
	<select id="selectTermsUseValid"  parameterType="java.util.HashMap" resultMap="termsInfo">
		SELECT T.*
		FROM   (SELECT ROW_NUMBER() OVER (ORDER BY TU.START_DATE, TU.FIRST_REG_DATETIME DESC) RNUM,
					   TU.TERMS_ID, TU.PART_ID, TU.LANG_TYPE,
					   TU.CHECK_LABEL, TU.CHECK_MSG, TU.CONTENT,
					   TU.START_DATE
				FROM   TN_CF_TERMS_USE TU,
					   TC_CF_COMM_CODE CC
				WHERE  TU.PART_ID = CC.CODE_ID
				AND    CC.COMM_CODE_TYPE_CODE = 'TERMS'
				AND    TU.POLICY_PAGE = '1'
				AND    TU.LANG_TYPE = #{langType}
				AND    CC.CODE = #{termsTypeCode}) T
		WHERE  RNUM = 1
	</select>
	
	<select id="selectTermsUseValidList"  parameterType="java.util.HashMap" resultMap="termsInfo">
		SELECT * FROM 
		(
		    SELECT
		    	ROW_NUMBER() OVER(PARTITION BY PART_ID 
		    	<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
					ORDER BY ${orderBy}
				</if>
				<if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">
		           	ORDER BY START_DATE DESC, FIRST_REG_DATETIME DESC
				</if>
		    	) PART_NUM, 
		    	TERMS_ID, PART_ID, LANG_TYPE, CHECK_LABEL, CHECK_MSG, CONTENT, START_DATE, POLICY_PAGE, AGREE_PAGE, AGREE_REQ
		      FROM TN_CF_TERMS_USE 
		    <trim prefix="WHERE" prefixOverrides="AND|OR">
		    		POLICY_PAGE  = '1'
			 	<if test="@com.samsung.SdlComparator@isNotEmpty(partId)">
			 		AND PART_ID = #{partId}
			 	</if>
			 	<if test="@com.samsung.SdlComparator@isNotEmpty(langType)">
			 		AND LANG_TYPE = #{langType}
			 	</if>
			 	<if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
			 		AND START_DATE <![CDATA[ <= ]]> #{startDate}
			 	</if>
			</trim>
		) X
	</select>
	
	<insert id="insertTermsUse" parameterType="termsUse">
		INSERT INTO TN_CF_TERMS_USE 
		(
			TERMS_ID, PART_ID, LANG_TYPE, CHECK_LABEL, CHECK_MSG, CONTENT, START_DATE,
			MAIL_FORM, FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID, POLICY_PAGE, AGREE_PAGE, AGREE_REQ
		) values (
			#{termsId}, #{partId}, #{langType}, #{checkLabel}, #{checkMsg}, #{content}, #{startDate},
			#{mailForm}, #{firstRegDatetime}, #{firstRegrId}, #{lastModDatetime}, #{lastModrId}, #{policyPage}, #{agreePage}, #{agreeReq}
		)
	</insert>
	
	<update id="updateTermsUse" parameterType="termsUse">
		UPDATE TN_CF_TERMS_USE 
		SET 
			PART_ID = #{partId}
			, LANG_TYPE = #{langType}
			, CHECK_LABEL = #{checkLabel}
			, CHECK_MSG = #{checkMsg}
			, START_DATE = #{startDate}
			, CONTENT = #{content}
			<if test="@com.samsung.SdlComparator@isNotEmpty(mailForm)">
			, MAIL_FORM = #{mailForm}
			</if>
			, LAST_MOD_DATETIME = #{lastModDatetime}
			, LAST_MODR_ID = #{lastModrId}
			, POLICY_PAGE = #{policyPage}
			, AGREE_PAGE = #{agreePage}
			, AGREE_REQ = #{agreeReq}
		WHERE TERMS_ID = #{termsId}
	</update>
	
	<insert id="insertUserTermsAgree" parameterType="userTermsAgree">
		INSERT INTO TN_CF_USER_TERMS_AGREE ( 
			USER_ID, TERMS_ID, AGREE, AGREE_DATETIME
		) VALUES (
			#{userId}, #{termsId}, #{agree}, #{agreeDatetime}
		)
	</insert>

	<delete id="deleteUserTermsAgree" parameterType="java.util.HashMap">
		DELETE FROM TN_CF_USER_TERMS_AGREE 
		 WHERE USER_ID = #{userId}
	</delete>
	
	<select id="selectUserTermsAgreeList" parameterType="java.util.HashMap" resultMap="userTermsAgreeResult">
		<include refid="sqlSelectUserTermsAgreeList"/>
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
			ORDER BY ${orderBy}
		</if>
		<if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">
           	ORDER BY TU.START_DATE DESC, TU.FIRST_REG_DATETIME DESC
		</if>
	</select>
	
	<select id="countUserTermsAgree" parameterType="java.util.HashMap" resultType="int" >
		SELECT count(*) 
		FROM TN_CF_TERMS_USE TU,
			TN_CF_USER_TERMS_AGREE TA,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS' AND DELETE_YN = '0') CC_TERMS,
			(SELECT * FROM TC_CF_COMM_CODE WHERE COMM_CODE_TYPE_CODE = 'TERMS_LANG' AND DELETE_YN = '0') CC_TERMS_LANG
		<include refid="conditionSelectUserTermsAgree"/>
	</select>
	
	<select id="selectUserTermsAgreePagingList"  parameterType="java.util.HashMap" resultMap="userTermsAgreeResult">
	select * 
			from ( 
		            select t1.*, ceiling( Row_Number() OVER (
				            <if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
								ORDER BY ${orderBy}
							</if>
							<if test="@com.samsung.SdlComparator@isEmptyForDynamicSql(orderBy)">
				            	ORDER BY t1.START_DATE DESC, t1.FIRST_REG_DATETIME DESC
				            </if>
			            ) / (#{pageSize}*1.0) ) as page 
		            from 
		                (
							<include refid="sqlSelectUserTermsAgreeList"/>
						) t1             
                 )t2 where t2.page = #{pageIndex}
	</select>
</mapper>