<?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="com.style24.admin.biz.dao.TsaEnvsetDao">

	<!-- 환경설정 생성 -->
	<insert id="createEnvset" parameterType="Envset">
		/* TsaEnvset.createEnvset */
		INSERT INTO TB_ENVSET (
		       ENVSET_SQ
		     , SITE_CD
		     , ENVSET_TYPE
		     , ENVSET_NM
		     , STR_SET_VAL1
		     , STR_SET_VAL2
		     , STR_SET_VAL3
		     , STR_SET_VAL4
		     , STR_SET_VAL5
		     , STR_SET_VAL6
		     , STR_SET_VAL7
		     , STR_SET_VAL8
		     , REG_NO
		     , REG_DT
		)
		VALUES (
		        NULL
		     , #{siteCd}
		     , #{envsetType}
		     , #{envsetNm}
		     , #{strSetVal1}
		     , #{strSetVal2}
		     , #{strSetVal3}
		     , #{strSetVal4}
		     , #{strSetVal5}
		     , #{strSetVal6}
		     , #{strSetVal7}
		     , #{strSetVal8}
		     , #{regNo}
		     , NOW()
		)
	</insert>

	<!-- 환경설정 목록 -->
	<select id="getEnvsetList" parameterType="Envset" resultType="Envset">
		/* TsaEnvset.getEnvsetList */
		SELECT SITE_CD
		     , ENVSET_TYPE
		     , ENVSET_NM
		     , STR_SET_VAL1
		     , STR_SET_VAL2
		     , STR_SET_VAL3
		     , STR_SET_VAL4
		     , STR_SET_VAL5
		     , STR_SET_VAL6
		     , STR_SET_VAL7
		     , STR_SET_VAL8
		     , FN_GET_USER_NM(REG_NO)             AS REG_NM
		     , DATE_FORMAT(REG_DT,'%Y%m%d%H%i%S') AS REG_DT
		FROM   TB_ENVSET
		WHERE  SITE_CD = #{siteCd}
		AND    ENVSET_TYPE = #{envsetType}
		ORDER  BY ENVSET_SQ DESC
	</select>

	<!-- 회원등급정책PK 건수 -->
	<select id="getCustomerGradePolicyPrimaryKeyCount" parameterType="CustGradePolicy" resultType="int">
		/* TsaSystem.getCustomerGradePolicyPrimaryKeyCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_CUST_GRADE_POLICY
		WHERE  SITE_CD = #{siteCd}
		AND    GRADE_CD = #{gradeCd}
	</select>
	
	<!-- 회원등급정책 생성 -->
	<insert id="createCustomerGradePolicy" parameterType="CustGradePolicy">
		/* TsaEnvset.createCustomerGradePolicy */
		INSERT INTO TB_CUST_GRADE_POLICY (
		       SITE_CD
		     , GRADE_CD
		     , ICON_NM
		     , CAL_MONTHS
		     , MIN_BUY_AMT
		     , MIN_BUY_CNT
		     , BUY_EXCEPT_AMT
		     , GRADE_CPN_ID1
		     , GRADE_CPN_ID2
		     , GRADE_CPN_ID3
		     , DISP_ORD
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{siteCd}
		     , #{gradeCd}
		     , #{iconNm}
		     , #{calMonths}
		     , #{minBuyAmt}
		     , #{minBuyCnt}
		     , #{buyExceptAmt}
		     , #{gradeCpnId1}
		     , #{gradeCpnId2}
		     , #{gradeCpnId3}
		     , #{dispOrd}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       ICON_NM = #{iconNm}
		     , CAL_MONTHS = #{calMonths}
		     , MIN_BUY_AMT = #{minBuyAmt}
		     , MIN_BUY_CNT = #{minBuyCnt}
		     , BUY_EXCEPT_AMT = #{buyExceptAmt}
		     , GRADE_CPN_ID1 = #{gradeCpnId1}
		     , GRADE_CPN_ID2 = #{gradeCpnId2}
		     , GRADE_CPN_ID3 = #{gradeCpnId3}
		     , DISP_ORD = #{dispOrd}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>

	<!-- 회원등급정책 목록 -->
	<select id="getCustomerGradePolicyList" parameterType="CustGradePolicy" resultType="CustGradePolicy">
		/* TsaEnvset.getCustomerGradePolicyList */
		SELECT SITE_CD
		     , GRADE_CD
		     , ICON_NM
		     , CAL_MONTHS
		     , MIN_BUY_AMT
		     , MIN_BUY_CNT
		     , BUY_EXCEPT_AMT
		     , GRADE_CPN_ID1
		     , FN_GET_COUPON_NM(GRADE_CPN_ID1)    AS GRADE_CPN_NM1
		     , GRADE_CPN_ID2
		     , FN_GET_COUPON_NM(GRADE_CPN_ID2)    AS GRADE_CPN_NM2
		     , GRADE_CPN_ID3
		     , FN_GET_COUPON_NM(GRADE_CPN_ID3)    AS GRADE_CPN_NM3
		     , DISP_ORD
		     , USE_YN
		     , FN_GET_USER_NM(UPD_NO)             AS UPD_NM
		     , DATE_FORMAT(UPD_DT,'%Y%m%d%H%i%S') AS UPD_DT
		FROM   TB_CUST_GRADE_POLICY A
		WHERE  SITE_CD = #{siteCd}
		<if test="gradeCd != null and gradeCd != ''">
		AND    GRADE_CD = #{gradeCd}
		</if>
		ORDER  BY SITE_CD, GRADE_CD
	</select>

</mapper>