<?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.front.biz.dao.TsfCustomerDao">

	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM   (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>

	<!-- 임시비밀번호 조회 -->
	<select id="getTemporaryPassword" parameterType="int" resultType="String">
		/* TsfCustomer.getTemporaryPassword */
		SELECT CONVERT(TRUNCATE(RAND() * CAST(CONCAT(1,LPAD(0,(#{length} - 1),'0')) AS UNSIGNED),0),CHAR) AS PASSWD FROM DUAL
	</select>

	<!-- 가입된 고객이 있는지 확인 -->
	<select id="getCustomerInfoCount" parameterType="Customer" resultType="int">
		/* TsfCustomer.getCustomerInfoCount */
		SELECT B.CNT
		FROM (
		      SELECT A.CNT
		           , ROW_NUMBER() OVER (ORDER BY A.CNT DESC) AS NUMB
		      FROM (
		            SELECT COUNT(*) AS CNT
		            FROM   TB_CUSTOMER
		            WHERE  CUST_STAT = 'G104_10'   /* 활동회원*/
		            AND    SITE_CD = #{siteCd}
		           <if test="encodedEmail != null and encodedEmail != ''">
		            AND    EMAIL = #{encodedEmail}
		           </if>
		           <if test="custId != null and custId !=''">
		            AND    CUST_ID = #{custId}
		           </if>
		           <if test="ci != null and ci != ''">
		            AND    CI = #{ci}
		           </if>
		           <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		            AND    CELL_PHNNO = #{encodedCellPhnno}
		           </if>

		            UNION ALL

		            SELECT COUNT(*) AS CNT
		            FROM   TB_SECEDE_CUST
		            WHERE  SITE_CD = #{siteCd}
		           <if test="encodedEmail != null and encodedEmail != ''">
		            AND    EMAIL = #{encodedEmail}
		           </if>
		           <if test="custId != null and custId !=''">
		            AND    CUST_ID = #{custId}
		           </if>
		           <if test="ci != null and ci != ''">
		            AND    CI = #{ci}
		           </if>
		           <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		            AND    CELL_PHNNO = #{encodedCellPhnno}
		           </if>

		            UNION ALL

		            SELECT COUNT(*) AS CNT
		            FROM   TB_DORMANT_CUST
		            WHERE  SITE_CD = #{siteCd}
		           <if test="encodedEmail != null and encodedEmail != ''">
		            AND    EMAIL = #{encodedEmail}
		           </if>
		           <if test="custId != null and custId !=''">
		            AND    CUST_ID = #{custId}
		           </if>
		           <if test="ci != null and ci != ''">
		            AND    CI = #{ci}
		           </if>
		           <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		            AND    CELL_PHNNO = #{encodedCellPhnno}
		           </if>
		           ) A
		     ) B
		WHERE NUMB = 1
	</select>

	<!-- 활동, 탈퇴, 휴면 테이블 조회 -->
	<select id="getCustomerInfo" parameterType="Customer" resultType="Customer">
		/* TsfCustomer.getCustomerInfo */
		SELECT A.CUST_NO
		     , A.CUST_ID
		     , A.EMAIL
		     , A.CELL_PHNNO
		     , A.CUST_STAT
		     , A.JOIN_DT
		     , A.SECEDE_DT
		     , A.DORMANT_DT
		FROM (
		      SELECT CUST_NO
		           , CUST_ID
		           , EMAIL
		           , CELL_PHNNO
		           , CUST_STAT
		           , DATE_FORMAT(JOIN_DT, '%Y%m%d%H%i%S')   AS JOIN_DT
		           , ''                                     AS SECEDE_DT
		           , ''                                     AS DORMANT_DT
		      FROM   TB_CUSTOMER
		      WHERE  CUST_STAT = 'G104_10'   /* 활동회원*/
		      AND    SITE_CD = #{siteCd}
		     <if test="ci != null and ci != ''">
		      AND    CI = #{ci}
		     </if>
		     <if test="custNo != null and custNo != ''">
		      AND    CUST_NO = #{custNo}
		     </if>
		     <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		      AND    CELL_PHNNO = #{encodedCellPhnno}
		     </if>

		      UNION ALL
		      
		      SELECT CUST_NO
		           , CUST_ID
		           , EMAIL
		           , CELL_PHNNO
		           , CUST_STAT
		           , ''                                     AS JOIN_DT
		           , DATE_FORMAT(SECEDE_DT, '%Y%m%d%H%i%S') AS SECEDE_DT
		           , ''                                     AS DORMANT_DT
		      FROM   TB_SECEDE_CUST
		      WHERE  SITE_CD = #{siteCd}
		      <if test="ci != null and ci != ''">
		      AND    CI = #{ci}
		      </if>
		      <if test="custNo != null and custNo != ''">
		      AND    CUST_NO = #{custNo}
		     </if>
		      <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		      AND    CELL_PHNNO = #{encodedCellPhnno}
		      </if>

		      UNION ALL

		      SELECT CUST_NO
		           , CUST_ID
		           , EMAIL
		           , CELL_PHNNO
		           , CUST_STAT
		           , ''                                      AS JOIN_DT
		           , ''                                      AS SECEDE_DT
		           , DATE_FORMAT(DORMANT_DT, '%Y%m%d%H%i%S') AS DORMANT_DT
		      FROM   TB_DORMANT_CUST
		      WHERE  SITE_CD = #{siteCd}
		      <if test="ci != null and ci != ''">
		      AND    CI = #{ci}
		      </if>
		      <if test="custNo != null and custNo != ''">
		      AND    CUST_NO = #{custNo}
		      </if>
		      <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		      AND    CELL_PHNNO = #{encodedCellPhnno}
		      </if>
		     ) A
	</select>

	<!--고객정보 생성-->
	<insert id="createCustomer" parameterType="Customer" keyProperty="custNo">
		/* TsfCustomer.createCustomer */
		INSERT INTO TB_CUSTOMER (
		       CUST_ID
		     , CUST_NM
		     , PASSWD
		     , BIRTH_YMD
		     , SEX_GB
		     , CELL_PHNNO
		     , APP_AGREE_YN
		     , APP_AGREE_DT
		     , SMS_AGREE_YN
		     , SMS_AGREE_DT
		     , EMAIL
		     , EMAIL_AGREE_YN
		     , EMAIL_AGREE_DT
		     , MK_AGREE_YN
		     , MK_AGREE_DT
		     , HOME_ZIPCODE
		     , HOME_BASE_ADDR
		     , HOME_DTL_ADDR
		     , SITE_CD
		     , FRONT_GB
		     , AF_LINK_CD
		     , CUST_GB
		     , CUST_GRADE
		     , JOIN_DT
		     , FOREIGNER_YN
		     , CUST_STAT
		     , PASSWD_CHG_DT
		     , TEMP_PASSWD_YN
		     , LOGIN_LDT
		     , CI
		     , AUTH_DT
		     , SNS_TYPE
		     , MANAGED_RSN
		     , MANAGED_DTL_RSN
		     , MANAGED_DT
		     , SECEDE_RSN
		     , SECEDE_DTL_RSN
		     , SECEDE_DT
		     , BIRTH_MM
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		SELECT #{custId}                           AS CUST_ID
		     , #{encodedCustNm}                    AS CUST_NM
		     , #{encodedPasswd}                    AS PASSWD
		     , #{encodedBirthYmd}                  AS BIRTH_YMD
		     , #{encodedSexGb}                     AS SEX_GB
		     , #{encodedCellPhnno}                 AS CELL_PHNNO
		     , IFNULL(#{appAgreeYn}, 'N')          AS APP_AGREE_YN
		     , CASE WHEN #{appAgreeYn} = 'Y' THEN
		            NOW()
		       ELSE NULL
		       END                                 AS APP_AGREE_DT
		     , IFNULL(#{smsAgreeYn}, 'N')          AS SMS_AGREE_YN
		     , CASE WHEN #{smsAgreeYn} = 'Y' THEN
		            NOW()
		       ELSE NULL
		       END                                 AS SMS_AGREE_DT
		     , #{encodedEmail}                     AS EMAIL
		     , IFNULL(#{emailAgreeYn}, 'N')        AS EMAIL_AGREE_YN
		     , CASE WHEN #{emailAgreeYn} = 'Y' THEN
		            NOW()
		       ELSE NULL
		       END                                 AS EMAIL_AGREE_DT
		     , IFNULL(#{mkAgreeYn}, 'N')           AS MK_AGREE_YN
		     , CASE WHEN #{mkAgreeYn} = 'Y' THEN
		            NOW()
		            ELSE NULL
		       END                                 AS MK_AGREE_DT
		     , #{homeZipcode}                      AS HOME_ZIPCODE
		     , #{encodedHomeBaseAddr}              AS HOME_BASE_ADDR
		     , #{encodedHomeDtlAddr}               AS HOME_DTL_ADDR
		     , #{siteCd}                           AS SITE_CD
		     , #{frontGb}                          AS FRONT_GB
		     , #{afLinkCd}                         AS AF_LINK_CD
		     , #{custGb}                           AS CUST_GB
		     , #{custGrade}                        AS CUST_GRADE
		     , NOW()                               AS JOIN_DT
		     , IFNULL(#{foreignerYn}, 'N')         AS FOREIGNER_YN
		     , #{custStat}                         AS CUST_STAT
		     , NOW()                               AS PASSWD_CHG_DT
		     , 'N'                                 AS TEMP_PASSWD_YN
		     , NOW()                               AS LOGIN_LDT
		     , #{ci}                               AS CI
		     , CASE WHEN #{ci} != '' THEN
		            NOW()
		       ELSE NULL
		       END                                 AS AUTH_DT
		     , #{snsType}                          AS SNS_TYPE
		     , #{managedRsn}                       AS MANAGED_RSN
		     , #{managedDtlRsn}                    AS MANAGED_DTL_RSN
		     , #{managedDt}                        AS MANAGED_DT
		     , #{secedeRsn}                        AS SECEDE_RSN
		     , #{secedeDtlRsn}                     AS SECEDE_DTL_RSN
		     , #{secedeDt}                         AS SECEDE_DT
		     , #{encodedBirthMm}                   AS BIRTH_MM
		     , 0                                   AS REG_NO
		     , NOW()                               AS REG_DT
		     , 0                                   AS UPD_NO
		     , NOW()                               AS UPD_DT
	</insert>

	<!--고객소셜정보 생성-->
	<insert id="createCustomerSns" parameterType="Customer">
		/* TsfCustomer.createCustomerSns */
		INSERT INTO TB_CUSTOMER_SNS (
		       CUST_NO
		     , SNS_TYPE
		     , SNS_ID
		     , REG_NO
		     , REG_DT
		) VALUES (
		       #{custNo}
		     , #{snsType}
		     , #{snsId}
		     , #{custNo}
		     , NOW()
		)
	</insert>

	<!--활동, 휴면 회원만 조회-->
	<select id="getCustomerActiveAndDormant" parameterType="Customer" resultType="Customer">
		/* TsfCustomer.getCustomerActiveAndDormant */
		SELECT C.CUST_NO
		     , C.CUST_ID
		     , C.EMAIL
		     , C.CELL_PHNNO
		     , C.CUST_STAT
		     , DATE_FORMAT(C.JOIN_DT, '%Y%m%d%H%i%S')   AS JOIN_DT
		     , ''                                       AS DORMANT_DT
		     , (
		        SELECT DATE_FORMAT(MAX(REG_DT), '%Y%m%d%H%i%S')
		        FROM   TB_CUSTOMER_SNS
		        WHERE  CUST_NO = C.CUST_NO
		        AND    SNS_TYPE = 'NV'
		       )                                        AS NV_JOIN_DT
		     , (
		        SELECT DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S')
		        FROM   TB_CUSTOMER_SNS
		        WHERE  CUST_NO = C.CUST_NO
		        AND    SNS_TYPE = 'KK'
		       )                                        AS KK_JOIN_DT
		     , (
		        SELECT DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S')
		        FROM   TB_CUSTOMER_SNS
		        WHERE  CUST_NO = C.CUST_NO
		        AND    SNS_TYPE = 'YS'
		       )                                        AS YS_JOIN_DT
		FROM   TB_CUSTOMER C
		WHERE  C.CUST_STAT = 'G104_10'
		<if test="custId != null and custId !=''">
		AND    C.CUST_ID = #{custId}
		</if>
		<if test="encodedCustNm != null and encodedCustNm != ''">
		AND    C.CUST_NM = #{encodedCustNm}
		</if>
		<if test="encodedEmail != null and encodedEmail != ''">
		AND    C.EMAIL = #{encodedEmail}
		</if>
		<if test="encodedBirthYmd != null and encodedBirthYmd != ''">
		AND    C.BIRTH_YMD = #{encodedBirthYmd}
		</if>
		<if test="ci != null and ci != ''">
		AND    C.CI = #{ci}
		</if>
		
		UNION ALL
		
		SELECT DC.CUST_NO
		     , DC.CUST_ID
		     , DC.EMAIL
		     , DC.CELL_PHNNO
		     , DC.CUST_STAT
		     , ''                                      AS JOIN_DT
		     , DATE_FORMAT(DC.DORMANT_DT, '%Y%m%d%H%i%S') AS DORMANT_DT
		     , (
		        SELECT DATE_FORMAT(MAX(REG_DT), '%Y%m%d%H%i%S')
		        FROM   TB_CUSTOMER_SNS
		        WHERE  CUST_NO = DC.CUST_NO
		        AND    SNS_TYPE = 'NV'
		       )                                       AS NV_JOIN_DT
		     , (
		        SELECT DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S')
		        FROM   TB_CUSTOMER_SNS
		        WHERE  CUST_NO = DC.CUST_NO
		        AND    SNS_TYPE = 'KK'
		       )                                       AS KK_JOIN_DT
		     , (
		        SELECT DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S')
		        FROM   TB_CUSTOMER_SNS
		        WHERE  CUST_NO = DC.CUST_NO
		        AND    SNS_TYPE = 'YS'
		       )                                       AS YS_JOIN_DT
		FROM   TB_DORMANT_CUST DC
		WHERE  1 = 1
		<if test="custId != null and custId !=''">
		AND    DC.CUST_ID = #{custId}
		</if>
		<if test="encodedCustNm != null and encodedCustNm != ''">
		AND    DC.CUST_NM = #{encodedCustNm}
		</if>
		<if test="encodedEmail != null and encodedEmail != ''">
		AND    DC.EMAIL = #{encodedEmail}
		</if>
		<if test="encodedBirthYmd != null and encodedBirthYmd != ''">
		AND    DC.BIRTH_YMD = #{encodedBirthYmd}
		</if>
		<if test="ci != null and ci != ''">
		AND    DC.CI = #{ci}
		</if>
	</select>

	<!--SNS 연동 계정 확인-->
	<select id="getCusomterSnsFind" parameterType="CustSnsInfo" resultType="Customer">
		/* TsfCustomer.getCusomterSnsFind */
		SELECT C.CUST_NO
		     , C.CUST_ID
		     , C.CUST_NM
		     , C.CELL_PHNNO
		     , C.EMAIL
		     , C.CUST_STAT
		     , C.CI
		FROM   TB_CUSTOMER C
		LEFT JOIN
		       TB_CUSTOMER_SNS CS
		ON     C.CUST_NO = CS.CUST_NO
		WHERE  CS.SNS_TYPE = #{snsType}
		AND    CS.SNS_ID = #{snsId}
	</select>

	<!-- 가입된 고객이 있는지 확인 -->
	<select id="getCustomerMaxCustId" parameterType="Customer" resultType="String">
		/* TsfCustomer.getCustomerMaxCustId */
		SELECT CUST_ID
		FROM (
		      SELECT MAX(CUST_ID)   AS CUST_ID
		      FROM (
		            SELECT CUST_ID
		            FROM   TB_CUSTOMER
		            WHERE  CUST_STAT = 'G104_10'   /* 활동회원*/
		            AND    SITE_CD = #{siteCd}
		            <if test="encodedEmail != null and encodedEmail != ''">
		            AND    EMAIL = #{encodedEmail}
		            </if>
		            <if test="custId != null and custId !=''">
		            AND    CUST_ID = #{custId}
		            </if>
		            <if test="ci != null and ci != ''">
		            AND    CI = #{ci}
		            </if>
		            <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		            AND    CELL_PHNNO = #{encodedCellPhnno}
		            </if>

		            UNION ALL

		            SELECT CUST_ID
		            FROM   TB_SECEDE_CUST
		            WHERE  SITE_CD = #{siteCd}
		            <if test="encodedEmail != null and encodedEmail != ''">
		            AND    EMAIL = #{encodedEmail}
		            </if>
		            <if test="custId != null and custId !=''">
		            AND    CUST_ID = #{custId}
		            </if>
		            <if test="ci != null and ci != ''">
		            AND    CI = #{ci}
		            </if>
		            <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		            AND    CELL_PHNNO = #{encodedCellPhnno}
		            </if>

		            UNION ALL

		            SELECT CUST_ID
		            FROM   TB_DORMANT_CUST
		            WHERE  SITE_CD = #{siteCd}
		            <if test="encodedEmail != null and encodedEmail != ''">
		            AND    EMAIL = #{encodedEmail}
		            </if>
		            <if test="custId != null and custId !=''">
		            AND    CUST_ID = #{custId}
		            </if>
		            <if test="ci != null and ci != ''">
		            AND    CI = #{ci}
		            </if>
		            <if test="encodedCellPhnno != null and encodedCellPhnno != ''" >
		            AND    CELL_PHNNO = #{encodedCellPhnno}
		            </if>
		           ) A
		      ) B
	</select>

	<!-- 위시리스트 등록 -->
	<insert id="createWishList" parameterType="WishList">
		/* TsfCustomer.createWishList */
		INSERT INTO TB_WISHLIST (
		       CUST_NO
		     , GOODS_CD
		     , AF_LINK_CD
		     , ITHR_CD
		     , CONTENTS_LOC
		     , PLAN_DTL_SQ
		     , REG_NO
		     , REG_DT
		) VALUES (
		       #{custNo}
		     , #{goodsCd}
		     , #{afLinkCd}
		     , #{ithrCd}
		     , #{contentsLoc}
		     , #{planDtlSq}
		     , #{regNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       REG_DT = NOW()
	</insert>

	<!-- 위시리스트 삭제 -->
	<delete id="deleteWishList" parameterType="WishList">
		/* TsfCustomer.deleteWishInfo */
		DELETE FROM TB_WISHLIST
		WHERE  CUST_NO = #{custNo}
		<choose>
		<when test="arrGoodsCd != null and arrGoodsCd.length>0">
		AND UPPER(GOODS_CD) IN
		    <foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
		UPPER(#{item})
		    </foreach>
		</when>
		<otherwise>
		AND    GOODS_CD = #{goodsCd}
		</otherwise>
		</choose>
	</delete>

	<!-- 위시리스트 삭제 상품 조회 -->
	<select id="getDeleteGoodsWish" parameterType="int" resultType="String">
		/* TsfCustomer.getDeleteGoodsWish */
		SELECT GROUP_CONCAT(GOODS_CD) AS GOODS_CD
		FROM (
		      SELECT GOODS_CD , REG_DT
		           , RANK() OVER(ORDER BY REG_DT DESC) AS RNUM
		      FROM TB_WISHLIST
		      WHERE CUST_NO = #{custNo}
		     ) Z
		WHERE RNUM > 50
	</select>


	<!--본인인증처리-->
	<update id="updateCustomerCi" parameterType="Customer">
		/* TsfCustomer.updateCustomerCi */
		UPDATE TB_CUSTOMER
		SET    CI = #{ci}
		     , CUST_NM = #{encodedCustNm}
		     , CELL_PHNNO = #{encodedCellPhnno}
		     , SEX_GB = #{encodedSexGb}
		     , BIRTH_YMD = #{encodedBirthYmd}
		     , BIRTH_MM = #{encodedBirthMm}
		     , AUTH_DT = NOW()
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
	</update>

	<!--비밀번호 변경 날짜 업데이트-->
	<update id="updatePasswordDate" parameterType="Customer">
		/* TsfCustomer.updatePasswordDate */
		UPDATE TB_CUSTOMER
		SET    PASSWD_CHG_DT = DATE_ADD(NOW(), INTERVAL -#{pwdChangeDay} DAY )
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
	</update>

	<!--이름 휴대전화 번호 변경-->
	<update id="updateCustomerAuth" parameterType="Customer">
		/* TsfCustomer.updateCustomerAuth */
		UPDATE TB_CUSTOMER
		SET    CUST_NM = #{encodedCustNm}
		     , CELL_PHNNO = #{encodedCellPhnno}
		     , SEX_GB = #{encodedSexGb}
		     , BIRTH_YMD = #{encodedBirthYmd}
		     , BIRTH_MM = #{encodedBirthMm}
		     , UPD_DT = NOW()
		     , UPD_NO = #{updNo}
		WHERE  CUST_NO = #{custNo}
	</update>

	<!--고객정보 수정-->
	<update id="updateCustomerInfo" parameterType="Customer">
		/* TsfCustomer.updateCustomerInfo */
		UPDATE TB_CUSTOMER
		SET    UPD_NO = #{updNo}
		    ,  UPD_DT = NOW()
		    <if test='emailModifyYn == "Y" '>
		    ,  EMAIL = #{encodedEmail}
		    </if>
		    ,  SMS_AGREE_DT = CASE WHEN #{smsAgreeYn} != SMS_AGREE_YN THEN NOW() ELSE SMS_AGREE_DT END
		    ,  SMS_AGREE_YN = #{smsAgreeYn}
		    ,  EMAIL_AGREE_DT = CASE WHEN #{emailAgreeYn} != EMAIL_AGREE_YN THEN NOW() ELSE EMAIL_AGREE_DT END
		    ,  EMAIL_AGREE_YN = #{emailAgreeYn}
		WHERE  CUST_NO = #{custNo}
	</update>

	<!--고객등급 정책조회-->
	<select id="getCustGradePolicy" parameterType="CustGrade" resultType="CustGrade">
		/* TsfCustomer.getCustGradePolicy */
		SELECT SITE_CD
		     , GRADE_CD
		     , FN_GET_CODE_NM('G110', GRADE_CD) AS GRADE_CD_NM
		     , SUBSTRING(GRADE_CD, 6, 2) AS GRADE_CD_NO
		     , 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
		FROM  TB_CUST_GRADE_POLICY
		WHERE SITE_CD = #{siteCd}
		AND   USE_YN = 'Y'
		<if test="gradeCd != null and gradeCd != ''">
		AND   GRADE_CD = #{gradeCd}
		</if>
		ORDER BY GRADE_CD
	</select>
	
	<!--고객 예상등급 조회-->
	<select id="getExpectedCustGrde" parameterType="CustGrade" resultType="CustGrade">
		/* TsfCustomer.getExpectedCustGrde */
		SELECT B.ORD_NO_CNT
		     , B.SUM_REAL_ORD_AMT
		     , #{gradeCd}                         AS GRADE_CD
		     , FN_GET_CODE_NM('G110', #{gradeCd}) AS GRADE_CD_NM
		     , SUBSTRING(#{gradeCd}, 6, 2)        AS GRADE_CD_NO
		FROM  (
		       SELECT COUNT(DISTINCT A.ORD_NO) AS ORD_NO_CNT
		            , SUM(A.SUM_REAL_ORD_AMT)  AS SUM_REAL_ORD_AMT
		       FROM (
		              SELECT O.ORD_NO
		                   , SUM(OD.REAL_ORD_AMT + OD.GFCD_USE_AMT) AS SUM_REAL_ORD_AMT
		              FROM   TB_ORDER O
		              INNER JOIN
		                     TB_ORDER_DETAIL OD
		              ON     O.ORD_NO = OD.ORD_NO
		              INNER JOIN
		                     TB_CUSTOMER C
		              ON     O.CUST_NO = C.CUST_NO
		              WHERE  OD.ORD_DTL_STAT = 'G013_70' /*구매확정*/
		              AND    C.CUST_NO = #{custNo}
		              AND    O.ORD_DT >= DATE_ADD(DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY), INTERVAL -#{calMonths} MONTH)
		              AND    O.ORD_DT <![CDATA[<]]> DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) /*익월 1일*/
		              GROUP BY O.ORD_NO
		              HAVING SUM(OD.REAL_ORD_AMT + OD.GFCD_USE_AMT) >= #{buyExceptAmt}
		            ) A
		       ) B
		WHERE B.ORD_NO_CNT >= #{minBuyCnt}
		OR    B.SUM_REAL_ORD_AMT >= #{minBuyAmt}
	</select>

	<!--고객 등급 기준 주문정보-->
	<select id="getCustGradeOrderInfo" parameterType="CustGrade" resultType="CustGrade">
		/* TsfCustomer.getCustGradeOrderInfo */
		SELECT   COUNT(DISTINCT A.ORD_NO) AS ORD_CNT
		       , SUM(A.SUM_REAL_ORD_AMT)  AS SUM_REAL_ORD_AMT
		FROM (
		      SELECT O.ORD_NO
		           , SUM(OD.REAL_ORD_AMT + OD.GFCD_USE_AMT) AS SUM_REAL_ORD_AMT
		      FROM   TB_ORDER O
		      INNER JOIN
		             TB_ORDER_DETAIL OD
		      ON     O.ORD_NO = OD.ORD_NO
		      INNER JOIN
		             TB_CUSTOMER C
		      ON     O.CUST_NO = C.CUST_NO
		      WHERE  OD.ORD_DTL_STAT = 'G013_70' /*구매확정*/
		      AND    C.CUST_NO = #{custNo}
		      AND    O.ORD_DT >= DATE_ADD(DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY), INTERVAL -#{calMonths} MONTH)
		      AND    O.ORD_DT <![CDATA[<]]> DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) /*익월 1일*/
		      GROUP BY O.ORD_NO
		      HAVING SUM(OD.REAL_ORD_AMT + OD.GFCD_USE_AMT) >= #{buyExceptAmt}
		) A
	</select>

	<!-- SMS수신거부 처리 -->
	<update id="updateSmsReceptionRefuse" parameterType="Integer">
		/* TsfCustomer.updateSmsReceptionRefuse */
		UPDATE TB_CUSTOMER
		SET    SMS_AGREE_YN = 'N'
		     , SMS_AGREE_DT = NOW()
		     , UPD_NO = #{custNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
		AND    SMS_AGREE_YN = 'Y' /*현재수신동의인경우*/
	</update>
	
	<!-- 메일수신거부 처리 -->
	<update id="updateEmailReceptionRefuse" parameterType="Integer">
		/* TsfCustomer.updateEmailReceptionRefuse */
		UPDATE TB_CUSTOMER
		SET    EMAIL_AGREE_YN = 'N'
		     , EMAIL_AGREE_DT = NOW()
		     , UPD_NO = #{custNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
		AND    EMAIL_AGREE_YN = 'Y' /*현재수신동의인경우*/
	</update>

	<!--앱수신여부-->
	<update id="updateAppAgreeYn" parameterType="Customer">
		/* TsfCustomer.updateAppAgreeYn */
		UPDATE TB_CUSTOMER
		SET    APP_AGREE_YN = #{appAgreeYn}
		     , APP_AGREE_DT = NOW()
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
	</update>

	<!--앱마케팅 수신여부-->
	<update id="updateMkAgreeYn" parameterType="Customer">
		/* TsfCustomer.updateMkAgreeYn */
		UPDATE TB_CUSTOMER
		SET    MK_AGREE_YN = #{mkAgreeYn}
		     , MK_AGREE_DT = NOW()
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
	</update>

	<!-- 선물하기 알림톡 발송 정보 조회 -->
	<select id="getGiftKakaoSendInfo" parameterType="CustContactHst" resultType="int">
		/* TsfCustomer.getGiftKakaoSendInfo */
		SELECT COUNT(*) AS CNT
		  FROM (SELECT CONTACT_HST_SQ
		             , CONTACT_TYPE
		             , CONTACT_METHOD
		             , CONTACT_CONTENTS
		             , CONTACT_MEMO
		             , SENDER_NO
		             , SEND_DT
		             , IF(TIMESTAMPDIFF(MINUTE, SEND_DT, NOW()) > 10, 'N', 'Y') AS RESEND_CAN_YN
		             , RECEIVER_NO
		             , REG_NO
		             , REG_DT
		          FROM TB_CUST_CONTACT_HST
		         WHERE CONTACT_TYPE = #{contactType}
		           AND CONTACT_METHOD = #{contactMethod}
		           AND CONTACT_MEMO = #{contactMemo}) Z
		 <if test="contactType == 'G054_308'">
		 WHERE RESEND_CAN_YN = 'Y'
		 </if>
	</select>
</mapper>