<?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.TsaCustomerDao">

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

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

	<!-- 활동회원목록 -->
	<select id="getCustomerActiveListCount" parameterType="CustomerSearch" resultType="int">
		/* TsaCustomer.getCustomerActiveListCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_CUSTOMER
		WHERE  CUST_STAT = 'G104_10'
		<if test="searchGb == 'custNo'">
		AND    CUST_NO = #{searchTxt}
		</if>
		<if test="searchGb == 'custNm'">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		</if>
		<if test="searchGb == 'email'">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		</if>
		<if test="searchGb == 'custId'">
		AND    LOWER(CUST_ID) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		</if>
		<if test="siteCd != null and siteCd != ''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="cellPhnno != null and cellPhnno != ''">
		AND    CELL_PHNNO = #{cellPhnno}
		</if>
		<if test="custGb != null and custGb != ''">
		AND    CUST_GB = #{custGb}
		</if>
		<if test="custGrade != null and custGrade != ''">
		AND    CUST_GRADE = #{custGrade}
		</if>
		<if test="managedRsn != null and managedRsn != ''">
		AND    MANAGED_RSN = #{managedRsn}
		</if>
		<if test="smsAgreeYn != null and smsAgreeYn != ''">
		AND   SMS_AGREE_YN = #{smsAgreeYn}
		</if>
		<if test="emailAgreeYn != null and emailAgreeYn != ''">
		AND   EMAIL_AGREE_YN = #{emailAgreeYn}
		</if>
		<if test="appAgreeYn != null and appAgreeYn != ''">
		AND   APP_AGREE_YN = #{appAgreeYn}
		</if>
		<if test="snsType != null and snsType != ''">
		AND   SNS_TYPE = #{snsType}
		</if>
		<if test="stDate != null and edDate != '' and stDate != null and edDate != ''">
		     <if test="searchDateGb == 'joinDt'">
		AND   JOIN_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND   JOIN_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		     </if>
		     <if test="searchDateGb == 'loginLdt'">
		AND   LOGIN_LDT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND   LOGIN_LDT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		     </if>
		</if>
		<if test="custNm != null and custNm != ''">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{custNm}),'%')
		</if>
		<if test="email != null and email != ''">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{email}),'%')
		</if>
		<if test="custId != null and custId != ''">
		AND    CUST_ID = #{custId}
		</if>
		<if test="condition != null and condition != ''">
			<choose>
				<when test="search != null and search == 'custId'">
					AND
					<foreach collection="conditions" item="item" index="index" separator="or">
						UPPER(CUST_ID) LIKE CONCAT('%',UPPER(#{item}),'%')
					</foreach>
				</when>
				<when test="search != null and search == 'custNm'">
					AND
					<foreach collection="conditions" item="item" index="index" separator="or">
						UPPER(CUST_NM) LIKE CONCAT('%',UPPER(#{item}),'%')
					</foreach>
				</when>
				<when test="search != null and search == 'email'">
					AND
					<foreach collection="conditions" item="item" index="index" separator="or">
						UPPER(EMAIL) LIKE CONCAT('%',UPPER(#{item}),'%')
					</foreach>
				</when>
			</choose>
		</if>
	</select>

	<!-- 활동회원목록 -->
	<select id="getCustomerActiveList" parameterType="CustomerSearch" resultType="Customer">
		/* TsaCustomer.getCustomerActiveList */
		<if test='cpnPubPopYn != null and cpnPubPopYn != "Y"'>
		<include refid="selectForPagingHeader"/>
		</if>
		SELECT CUST_NO
		     , CUST_ID
		     , CUST_NM
		     , PASSWD
		     , BIRTH_YMD
		     , SEX_GB
		     , CELL_PHNNO
		     , APP_AGREE_YN
		     , DATE_FORMAT(APP_AGREE_DT, '%Y%m%d%H%i%S') AS APP_AGREE_DT
		     , SMS_AGREE_YN
		     , DATE_FORMAT(SMS_AGREE_DT, '%Y%m%d%H%i%S') AS SMS_AGREE_DT
		     , EMAIL
		     , EMAIL_AGREE_YN
		     , DATE_FORMAT(EMAIL_AGREE_DT, '%Y%m%d%H%i%S') AS EMAIL_AGREE_DT
		     , MK_AGREE_YN
		     , DATE_FORMAT(MK_AGREE_DT, '%Y%m%d%H%i%S') AS MK_AGREE_DT
		     , HOME_ZIPCODE
		     , HOME_BASE_ADDR
		     , HOME_DTL_ADDR
		     , SITE_CD
		     , FRONT_GB
		     , AF_LINK_CD
		     , CUST_GB
		     , CUST_GRADE
		     , DATE_FORMAT(JOIN_DT, '%Y%m%d%H%i%S') AS JOIN_DT
		     , FOREIGNER_YN
		     , CUST_STAT
		     , DATE_FORMAT(PASSWD_CHG_DT, '%Y%m%d%H%i%S') AS PASSWD_CHG_DT
		     , TEMP_PASSWD_YN
		     , DATE_FORMAT(LOGIN_LDT, '%Y%m%d%H%i%S') AS LOGIN_LDT
		     , CI
		     , DATE_FORMAT(AUTH_DT, '%Y%m%d%H%i%S') AS AUTH_DT
		     , MANAGED_RSN
		     , MANAGED_DTL_RSN
		     , DATE_FORMAT(MANAGED_DT, '%Y%m%d%H%i%S') AS MANAGED_DT
		     , SECEDE_RSN
		     , SECEDE_DTL_RSN
		     , DATE_FORMAT(SECEDE_DT, '%Y%m%d%H%i%S') AS SECEDE_DT
		     <if test="searchDateGb == 'joinDt'">
		     , ROW_NUMBER() OVER(ORDER BY JOIN_DT DESC)     AS NUMB
		     </if>
		     <if test="searchDateGb == 'loginLdt'">
		     , ROW_NUMBER() OVER(ORDER BY LOGIN_LDT DESC)   AS NUMB
		     </if>
		FROM   TB_CUSTOMER
		WHERE  CUST_STAT = 'G104_10'
		<if test="searchGb == 'custNo'">
		AND    CUST_NO = #{searchTxt}
		</if>
		<if test="searchGb == 'custNm'">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		</if>
		<if test="searchGb == 'email'">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		</if>
		<if test="searchGb == 'custId'">
		AND    LOWER(CUST_ID) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		</if>
		<if test="siteCd != null and siteCd != ''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="cellPhnno != null and cellPhnno != ''">
		AND    CELL_PHNNO = #{cellPhnno}
		</if>
		<if test="custGb != null and custGb != ''">
		AND    CUST_GB = #{custGb}
		</if>
		<if test="custGrade != null and custGrade != ''">
		AND    CUST_GRADE = #{custGrade}
		</if>
		<if test="managedRsn != null and managedRsn != ''">
		AND    MANAGED_RSN = #{managedRsn}
		</if>
		<if test="smsAgreeYn != null and smsAgreeYn != ''">
		AND   SMS_AGREE_YN = #{smsAgreeYn}
		</if>
		<if test="emailAgreeYn != null and emailAgreeYn != ''">
		AND   EMAIL_AGREE_YN = #{emailAgreeYn}
		</if>
		<if test="appAgreeYn != null and appAgreeYn != ''">
		AND   APP_AGREE_YN = #{appAgreeYn}
		</if>
		<if test="mkAgreeYn != null and mkAgreeYn != ''">
		AND   MK_AGREE_YN = #{mkAgreeYn}
		</if>
		<if test="snsType != null and snsType != ''">
		AND  SNS_TYPE = #{snsType}
		</if>
		<if test="stDate != null and edDate != '' and stDate != null and edDate != ''">
		     <if test="searchDateGb == 'joinDt'">
		AND   JOIN_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND   JOIN_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		     </if>
		     <if test="searchDateGb == 'loginLdt'">
		AND   LOGIN_LDT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND   LOGIN_LDT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		     </if>
		</if>
		<if test="custNm != null and custNm != ''">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{custNm}),'%')
		</if>
		<if test="email != null and email != ''">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{email}),'%')
		</if>
		<if test="custId != null and custId != ''">
		AND    CUST_ID = #{custId}
		</if>
		<if test="condition != null and condition != ''">
			<choose>
				<when test="search != null and search == 'custId'">
					AND
					<foreach collection="conditions" item="item" index="index" separator="or">
						UPPER(CUST_ID) LIKE CONCAT('%',UPPER(#{item}),'%')
					</foreach>
				</when>
				<when test="search != null and search == 'custNm'">
					AND
					<foreach collection="conditions" item="item" index="index" separator="or">
						UPPER(CUST_NM) LIKE CONCAT('%',UPPER(#{item}),'%')
					</foreach>
				</when>
				<when test="search != null and search == 'email'">
					AND
					<foreach collection="conditions" item="item" index="index" separator="or">
						UPPER(EMAIL) LIKE CONCAT('%',UPPER(#{item}),'%')
					</foreach>
				</when>
			</choose>
		</if>
		<if test='cpnPubPopYn != null and cpnPubPopYn != "Y"'>
		<include refid="selectForPagingFooter"/>
		</if>
	</select>

	<!--회원정보 수정-->
	<update id="updateCustomerInfo" parameterType="Customer">
		/* TsaCustomer.updateCustomerInfo */
		UPDATE TB_CUSTOMER
		SET   CUST_GB = #{custGb}
		    , CUST_GRADE = #{custGrade}
		    <if test="emailAgreeYn != null and emailAgreeYn != ''">
		    , EMAIL_AGREE_DT = NOW()
		    , EMAIL_AGREE_YN = #{emailAgreeYn}
		    </if>
		    <if test="smsAgreeYn != null and smsAgreeYn != ''">
		    , SMS_AGREE_DT = NOW()
		    , SMS_AGREE_YN = #{smsAgreeYn}
		    </if>
		    <if test="encodedHomeZipcode != null and encodedHomeZipcode != ''">
		    , HOME_ZIPCODE = #{encodedHomeZipcode}
		    </if>
		    <if test="encodedHomeBaseAddr != null and encodedHomeBaseAddr != ''">
		    , HOME_BASE_ADDR = #{encodedHomeBaseAddr}
		    </if>
		    <if test="encodedHomeDtlAddr != null and encodedHomeDtlAddr != ''">
		    , HOME_DTL_ADDR = #{encodedHomeDtlAddr}
		    </if>
		    <if test="encodedBirthYmd != null and encodedBirthYmd != ''">
		    , BIRTH_YMD = #{encodedBirthYmd}
		    </if>
		    <if test='emailModifyYn == "Y"'>
			    <if test="encodedEmail != null and encodedEmail != ''">
		    , EMAIL = #{encodedEmail}
		         </if>
		    </if>
		    <if test="managedRsn != null and managedRsn != ''">
		    , MANAGED_DT = CASE WHEN MANAGED_RSN = #{managedRsn}
		                       AND  MANAGED_DTL_RSN = #{managedDtlRsn}
		                  THEN MANAGED_DT
		                  ELSE NOW()
		                  END
		    , MANAGED_RSN = #{managedRsn}
		    , MANAGED_DTL_RSN = #{managedDtlRsn}
		    </if>
		    <if test="managedRsn == null or managedRsn == ''">
		    , MANAGED_RSN = NULL
		    , MANAGED_DTL_RSN =  NULL
		    , MANAGED_DT = NULL
		    </if>
		    , BIRTH_MM = #{encodedBirthMm}
		    , UPD_NO = #{updNo}
		    , UPD_DT = NOW()
		WHERE CUST_NO = #{custNo}
	</update>

	<!-- 회원 휴대전화번호 수정 -->
	<update id="updateCustomerCellPhnno" parameterType="Customer">
		/* TsaCustomer.updateCustomerCellPhnno */
		UPDATE TB_CUSTOMER
		SET    CELL_PHNNO = #{encodedCellPhnno}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
	</update>

	<!-- 회원 주문내역 -->
	<select id="getCustomerOrderListCount" parameterType="CustomerSearch" resultType="int">
		/* TsaCustomer.getCustomerOrderListCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_CUSTOMER C
		INNER JOIN
		       TB_ORDER O
		ON     C.CUST_NO = O.CUST_NO
		INNER JOIN
		       TB_ORDER_DETAIL OD
		ON     O.ORD_NO = OD.ORD_NO
		INNER JOIN
		       TB_ORDER_DETAIL_ITEM ODI
		ON     OD.ORD_NO = ODI.ORD_NO
		AND    OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		INNER JOIN
		       TB_GOODS E
		ON     OD.GOODS_CD = E.GOODS_CD
		INNER JOIN
		       TB_DELIVERY_ADDR DA
		ON     OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ
		WHERE  C.CUST_NO = #{custNo}
		ORDER BY O.ORD_NO DESC, OD.ORD_DTL_NO
	</select>

	<!-- 회원 주문내역 -->
	<select id="getCustomerOrderList" parameterType="CustomerSearch" resultType="Order">
		/* TsaCustomer.getCustomerOrderList */
		<include refid="selectForPagingHeader"/>
		SELECT O.SITE_CD
		     , O.ORD_NO
		     , O.MALL_GB
		     , OD.ORD_DTL_NO
		     , DATE_FORMAT(O.PAY_DT, '%Y%m%d%H%i%S') AS PAY_DT
		     , OD.DELV_STDT
		     , OD.DELV_EDDT
		     , O.ORD_TELNO
		     , O.ORD_PHNNO
		     , OD.ORD_DTL_STAT
		     , OD.GOODS_CD
		     , ODI.OPT_CD1              -- 색상
		     , ODI.OPT_CD2              -- 사이즈
		     , E.GOODS_NM
		     , OD.ORD_QTY
		     , OD.CURR_PRICE
		     , C.CUST_NO
		     , DA.RECIP_NM
		     , ROW_NUMBER() OVER(ORDER BY O.ORD_NO DESC, OD.ORD_DTL_NO) AS NUMB
		FROM   TB_CUSTOMER C
		INNER JOIN
		       TB_ORDER O
		ON     C.CUST_NO = O.CUST_NO
		INNER JOIN
		       TB_ORDER_DETAIL OD
		ON     O.ORD_NO = OD.ORD_NO
		INNER JOIN
		       TB_ORDER_DETAIL_ITEM ODI
		ON     OD.ORD_NO = ODI.ORD_NO
		AND    OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		INNER JOIN
		       TB_GOODS E
		ON     OD.GOODS_CD = E.GOODS_CD
		INNER JOIN
		       TB_DELIVERY_ADDR DA
		ON     OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ
		WHERE  C.CUST_NO = #{custNo}
		<include refid="selectForPagingFooter"/>
	</select>

	<!--회원 쿠폰내역  -->
	<select id="getCustomerCouponList" parameterType="Integer" resultType="CustCoupon">
		/* TsaCustomer.getCustomerCounponList */
		WITH TAB1 AS (
		              SELECT O.ORD_NO             AS ORD_NO
		                   , MAX(OD.GOODS_CPN_SQ) AS GOODS_CPN_SQ
		                   , MAX(OD.CART_CPN_SQ)  AS CART_CPN_SQ
		                   , MAX(OD.CPN1_CPN_SQ)  AS CPN1_CPN_SQ
		              FROM   TB_ORDER O
		              INNER JOIN
		                     TB_ORDER_DETAIL OD
		              ON     O.ORD_NO = OD.ORD_NO
		              WHERE  O.CUST_NO = #{custNo}
		              GROUP BY O.ORD_NO
		)
		, TAB2 AS (
		           SELECT ORD_NO
		                , CPN1_CPN_SQ AS CPN_SQ
		           FROM   TAB1
		           WHERE  CPN1_CPN_SQ > 0
		           AND    CPN1_CPN_SQ IS NOT NULL
		           UNION ALL
		           SELECT ORD_NO
		                , GOODS_CPN_SQ AS CPN_SQ
		           FROM   TAB1
		           WHERE  GOODS_CPN_SQ > 0
		           AND    GOODS_CPN_SQ IS NOT NULL
		           UNION ALL
		           SELECT ORD_NO
		                , CART_CPN_SQ AS CPN_SQ
		           FROM   TAB1
		           WHERE  CART_CPN_SQ > 0
		           AND    CART_CPN_SQ IS NOT NULL
		)
		SELECT C.SITE_CD
		     , CC.CUST_CPN_SQ
		     , CC.CUST_NO
		     , C.CPN_ID
		     , C.CPN_NM
		     , DATE_FORMAT(CC.AVAIL_STDT, '%Y%m%d%H%i%S')            AS AVAIL_STDT
		     , DATE_FORMAT(CC.AVAIL_EDDT, '%Y%m%d%H%i%S')            AS AVAIL_EDDT
		     , CC.PUB_REASON
		     , CC.PUB_REASON_DTL
		     , CC.END_ALIM_SEND_YN
		     , E.ORD_NO
		     , DATE_FORMAT(CC.USED_DT, '%Y%m%d%H%i%S')               AS USED_DT
		     , FN_GET_USER_NM(CC.REG_NO)                             AS REG_NM
		     , CC.UPD_NO                                             AS UPD_NO
		     , DATE_FORMAT(CC.REG_DT, '%Y%m%d%H%i%S')                AS REG_DT
		FROM   TB_COUPON C
		INNER JOIN
		       TB_CUST_COUPON CC
		ON     C.CPN_ID = CC.CPN_ID
		INNER JOIN
		       TB_CUSTOMER CU
		ON     CC.CUST_NO = CU.CUST_NO
		LEFT OUTER JOIN
		       TAB2 E
		ON     CC.CUST_CPN_SQ = E.CPN_SQ
		WHERE  CC.CUST_NO = #{custNo}
		ORDER BY CC.REG_DT DESC
	</select>

	<!--회원 포인트내역  -->
	<select id="getCustomerPoint" parameterType="Integer" resultType="Point">
		/* TsaCustomer.getCustomerPoint */
		SELECT IFNULL((
		               SELECT SUM(RM_PNT_AMT)
		               FROM   TB_CUST_POINT
		               WHERE  CUST_NO = C.CUST_NO
		               AND    EXP_BE_DT > NOW()
		               AND    EXP_CMP_DT IS NULL
		               ), 0)                           AS RM_PNT_AMT -- 가용포인트
		     , IFNULL((
		               SELECT SUM(GV_PNT_AMT)
		               FROM   TB_CUST_POINT
		               WHERE  CUST_NO = C.CUST_NO
		               ), 0)                           AS GV_PNT_AMT -- 누적포인트
		     , IFNULL((
		               SELECT SUM(US_PNT_AMT)
		               FROM   TB_CUST_POINT
		               WHERE  CUST_NO = C.CUST_NO
		               ), 0)                           AS US_PNT_AMT -- 사용포인트
		     , IFNULL((
		               SELECT SUM(RM_PNT_AMT)
		               FROM   TB_CUST_POINT
		               WHERE  CUST_NO = C.CUST_NO
		               AND    (EXP_BE_DT <![CDATA[<]]> NOW() OR EXP_CMP_DT IS NOT NULL)
		               ), 0)                           AS EXPIRE_PNT_AMT -- 소멸포인트
		     , IFNULL((
		               SELECT SUM(CPH.PNT_AMT) AS PNT_AMT
		               FROM   TB_CUST_POINT_HST CPH
		               WHERE  CPH.CUST_NO = C.CUST_NO
		               AND    CPH.PNT_UPLOAD_STAT IN ('G070_10')                 -- 반영예정
		               AND    CPH.OCCUR_GB IN ('G069_12', 'G069_13')             -- 적립예정, 적립취소
		               ), 0)                           AS EXPECTED_PNT_AMT       -- 적립예정포인트
		     , IFNULL((
		               SELECT SUM(RM_PNT_AMT) AS RM_PNT_AMT
		               FROM  TB_CUST_POINT
		               WHERE CUST_NO = C.CUST_NO
		               AND   GV_PNT_AMT > 0
		               AND   EXP_BE_DT > NOW()
		               AND   EXP_BE_DT <![CDATA[<]]> DATE_ADD(NOW(),INTERVAL 31 DAY)
		                ), 0)                          AS EXPECTED_EXPIRE_PNT_AMT -- 소멸예정포인트(30일)
		FROM   TB_CUSTOMER C
		WHERE  C.CUST_NO = #{custNo}
	</select>

	<!-- 회원 포인트내역  -->
	<select id="getCustomerPointList" parameterType="Integer" resultType="Point">
		/* TsaCustomer.getCustomerPointList */
		SELECT  CPH.PNT_HST_SQ
		      , C.CUST_ID
		      , C.CUST_NO
		      , C.SITE_CD
		      , CPH.OCCUR_GB
		      , CPH.OCCUR_DTL_DESC
		      , CPH.PNT_AMT
		      , CPH.ORD_NO
		      , CPH.ORD_DTL_NO
		      , CPH.REVIEW_SQ
		      , DATE_FORMAT(CPH.SWITCH_DUE_DT, '%Y%m%d%H%i%S')  AS SWITCH_DUE_DT
		      , CPH.PNT_UPLOAD_STAT
		      , DATE_FORMAT(CPH.PNT_UPLOAD_DT, '%Y%m%d%H%i%S')  AS PNT_UPLOAD_DT
		      , FN_GET_USER_NM(CPH.REG_NO)                      AS REG_NM
		      , DATE_FORMAT(CPH.REG_DT, '%Y%m%d%H%i%S')         AS REG_DT
		      , CPH.UPD_NO                                      AS UPD_NO
		      , DATE_FORMAT(CPH.UPD_DT, '%Y%m%d%H%i%S')         AS UPD_DT
		      , DATE_FORMAT((
		                     SELECT MAX(CP.EXP_BE_DT) AS EXP_BE_DT
		                     FROM   TB_CUST_POINT CP
		                     WHERE  CP.CUST_PNT_SQ = CPH.CUST_PNT_SQ
		                     AND    CP.CUST_NO = CPH.CUST_NO
		         ), '%Y%m%d%H%i%S') AS EXP_BE_DT
		      , DATE_FORMAT((
		                     SELECT MAX(CP.EXP_CMP_DT) AS EXP_CMP_DT
		                     FROM   TB_CUST_POINT CP
		                     WHERE  CP.CUST_PNT_SQ = CPH.CUST_PNT_SQ
		                     AND    CP.CUST_NO = CPH.CUST_NO
		         ), '%Y%m%d%H%i%S') AS EXP_CMP_DT
		FROM    TB_CUSTOMER C
		INNER JOIN
		        TB_CUST_POINT_HST CPH
		ON      C.CUST_NO = CPH.CUST_NO
		WHERE   C.CUST_NO = #{custNo}
		ORDER BY CPH.REG_DT DESC
	</select>

	<!-- 회원 상품권이력 -->
	<select id="getCustomerGiftCardList" parameterType="Integer" resultType="GiftCard">
		/* TsaCustomer.getCustomerGiftCardList */
		SELECT CGH.GFCD_HST_SQ
		     , CGH.CUST_NO
		     , CGH.OCCUR_GB
		     , CGH.OCCUR_DTL_DESC
		     , CGH.GFCD_AMT
		     , CGH.CUST_GFCD_SQ
		     , CGH.ORD_NO
		     , CGH.ORD_DTL_NO
		     , FN_GET_USER_NM(CGH.REG_NO)                      AS REG_NM
		     , CGH.REG_DT
		     , CGH.UPD_NO
		     , CGH.UPD_DT
		FROM   TB_CUST_GIFTCARD_HST CGH
		INNER JOIN
		       TB_CUSTOMER C
		ON     CGH.CUST_NO = C.CUST_NO
		WHERE  CGH.CUST_NO = #{custNo}
		ORDER BY CGH.REG_DT DESC
	</select>

	<!--회원 상품평내역  -->
	<select id="getCustomerReviewList" parameterType="Integer" resultType="Review">
		/* TsaCustomer.getCustomerReviewList */
		SELECT CASE WHEN REVIEW_GB = 'P'
		            THEN '[포토/동영상]'||R.REVIEW_TITLE
		            ELSE R.REVIEW_TITLE
		       END                          AS REVIEW_TITLE
		     , R.REVIEW_SQ
		     , R.GOODS_CD
		     , R.CUST_NO
		     , R.ORD_NO
		     , R.ORD_DTL_NO
		     , R.REVIEW_TITLE
		     , R.REVIEW_CONTENT
		     , R.SCORE
		     , R.HEIGHT
		     , R.WEIGHT
		     , R.SCORE_SIZE
		     , R.SCORE_COLOR
		     , R.SCORE_FIT
		     , R.SCORE_THICK
		     , R.SCORE_WEIGHT
		     , R.SCORE_BALL
		     , R.GIVE_DUE_PNT
		     , R.PNT_GIVE_STAT
		     , R.DISP_YN
		     , R.DEL_YN
		     , R.CONFIRM_YN
		     , FN_GET_USER_NM(R.CONFIRM_UNO)             AS CONFIRM_UNM
		     , DATE_FORMAT(R.CONFIRM_DT, '%Y%m%d%H%i%S') AS CONFIRM_DT
		     , R.REG_NO
		     , R.REG_DT
		     , R.UPD_NO
		     , R.UPD_DT
		     , R.ADM_RPL
		     , R.ADM_RPL_REG_NO
		     , R.ADM_RPL_DT
		FROM   TB_REVIEW R
		INNER  JOIN
		       TB_CUSTOMER C
		ON     R.CUST_NO = C.CUST_NO
		WHERE  R.CUST_NO = #{custNo}
		ORDER BY R.REG_DT DESC
	</select>

	<!--회원 1:1문의 내역  -->
	<select id="getCustomerCounselList" parameterType="Integer" resultType="Counsel">
		/* TsaCustomer.getCustomerCouncelList */
		SELECT CS.COUNSEL_SQ 
		     , CS.CUST_NO
		     , CS.SITE_CD
		     , CS.COUNSEL_CLSF
		     , CS.COUNSEL_DCLSF
		     , CS.CUST_NO
		     , CS.CELL_PHNNO
		     , CS.EMAIL
		     , CS.EMAIL_REQ_YN
		     , CS.EMAIL_SEND_YN
		     , CS.SMS_REQ_YN
		     , CS.SMS_SEND_YN
		     , CS.REL_ORD_NO
		     , CS.REL_GOODS_CD
		     , CS.COUNSEL_TYPE
		     , CS.QUEST_TITLE
		     , CS.QUEST_CONTENT
		     , CS.QUEST_DT
		     , CS.ORG_FILE_NM1
		     , CS.SYS_FILE_NM1
		     , CS.ORG_FILE_NM2
		     , CS.SYS_FILE_NM2
		     , CS.ANS_STAT
		     , CS.ANS_TRANS_YN
		     , CS.ANS_COMP_CD
		     , CS.ANS_TRANS_NO
		     , CS.ANS_TRANS_DT
		     , CS.ASSIGNED_CS_NO
		     , CS.ASSIGNED_YMD
		     , CS.ASSIGNED_HMS
		     , CS.ANS_TITLE
		     , CS.ANS_CONTENT
		     , CS.ANS_NO				 AS ANS_NO
		     , FN_GET_USER_NM(CS.ANS_NO) AS ANS_NM
		     , DATE_FORMAT(CS.ANS_DT, '%Y%m%d%H%i%S') AS ANS_DT
		     , CS.SECRET_YN
		FROM  TB_COUNSEL CS
		INNER JOIN
		      TB_CUSTOMER C
		ON    CS.CUST_NO = C.CUST_NO
		WHERE CS.COUNSEL_TYPE = 'C'
		AND   CS.CUST_NO = #{custNo}
		ORDER BY CS.REG_DT DESC
	</select>

	<!--회원 상품문의 내역  -->
	<select id="getCustomerGoodsQnaList" parameterType="Integer" resultType="Counsel">
		/* TsaCustomer.getCustomerGoodsQnaList */
		SELECT CS.COUNSEL_SQ 
		     , CS.CUST_NO
		     , CS.SITE_CD
		     , CS.COUNSEL_CLSF
		     , CS.COUNSEL_DCLSF
		     , CS.CUST_NO
		     , CS.CELL_PHNNO
		     , CS.EMAIL
		     , CS.EMAIL_REQ_YN
		     , CS.EMAIL_SEND_YN
		     , CS.SMS_REQ_YN
		     , CS.SMS_SEND_YN
		     , CS.REL_ORD_NO
		     , CS.REL_GOODS_CD
		     , CS.COUNSEL_TYPE
		     , CS.QUEST_TITLE
		     , CS.QUEST_CONTENT
		     , CS.QUEST_DT
		     , CS.ORG_FILE_NM1
		     , CS.SYS_FILE_NM1
		     , CS.ORG_FILE_NM2
		     , CS.SYS_FILE_NM2
		     , CS.ANS_STAT
		     , CS.ANS_TRANS_YN
		     , CS.ANS_COMP_CD
		     , CS.ANS_TRANS_NO
		     , CS.ANS_TRANS_DT
		     , CS.ASSIGNED_CS_NO
		     , CS.ASSIGNED_YMD
		     , CS.ASSIGNED_HMS
		     , CS.ANS_TITLE
		     , CS.ANS_CONTENT
		     , CS.ANS_NO				 AS ANS_NO
		     , FN_GET_USER_NM(CS.ANS_NO) AS ANS_NM
		     , CS.ANS_DT
		     , CS.SECRET_YN
		FROM  TB_COUNSEL CS
		INNER JOIN
		      TB_CUSTOMER C
		ON    CS.CUST_NO = C.CUST_NO
		WHERE CS.COUNSEL_TYPE = 'G'
		AND   CS.CUST_NO = #{custNo}
		ORDER BY CS.REG_DT DESC
	</select>

	<!--회원등급 변경내역  -->
	<select id="getCustomerChangeGradeList" parameterType="Integer" resultType="CustGrade">
		/* TsaCustomer.getCustomerChangeGradeList */
		SELECT CGH.CUST_NO
		     , CGH.APPLY_YMD
		     , CGH.GRADE_BCD
		     , CGH.GRADE_ACD
		     , CGH.REAL_ORD_AMT
		     , CGH.ORD_CNT
		     , CGH.REG_NO
		     , CGH.REG_DT
		FROM   TB_CUST_GRADE_HST CGH
		INNER JOIN
		       TB_CUSTOMER C
		ON     CGH.CUST_NO = C.CUST_NO
		WHERE  CGH.CUST_NO = #{custNo}
		ORDER BY CGH.REG_DT DESC
	</select>

	<!-- 회원접촉내역 -->
	<select id="getCustomerContactList" parameterType="Integer" resultType="CustContactHst">
		/* TsaCustomer.getCustomerContactList */
		SELECT CONTACT_HST_SQ
		     , CONTACT_TYPE
		     , CONTACT_METHOD
		     , CONTACT_CONTENTS
		     , SENDER_NO
		     , DATE_FORMAT(SEND_DT, '%Y%m%d%H%i%S') AS SEND_DT
		     , RECEIVER_NO
		     , FN_GET_USER_NM(REG_NO)               AS REG_NM
		     , DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S')  AS REG_DT
		FROM   TB_CUST_CONTACT_HST
		WHERE  RECEIVER_NO = #{custNo}
		ORDER BY REG_DT DESC
	</select>

	<!-- 탈퇴회원목록 건수 -->
	<select id="getCustomerSecedeListCount" parameterType="CustomerSearch" resultType="int">
		/* TsaCustomer.getCustomerSecedeListCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_SECEDE_CUST
		WHERE  CUST_STAT = 'G104_30'
		<if test="searchTxt != null and searchTxt != ''">
		    <if test="searchGb == 'custNo'">
		AND    CUST_NO = #{searchTxt}
		    </if>
		    <if test="searchGb == 'custNm'">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'email'">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'custId'">
		AND    CUST_ID LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		</if>
		<if test="siteCd != null and siteCd != ''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="cellPhnno != null and cellPhnno != ''">
		AND    CELL_PHNNO = #{cellPhnno}
		</if>
		<if test="secedeRsn != null and secedeRsn != ''">
		AND    SECEDE_RSN = #{secedeRsn}
		</if>
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		AND    SECEDE_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND    SECEDE_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		</if>
	</select>

	<!-- 탈퇴회원목록 -->
	<select id="getCustomerSecedeList" parameterType="CustomerSearch" resultType="Customer">
		/* TsaCustomer.getSecedeCustomerList */
		<include refid="selectForPagingHeader"/>
		SELECT CUST_NO
		     , CUST_ID
		     , CUST_NM
		     , BIRTH_YMD
		     , CELL_PHNNO
		     , EMAIL
		     , SITE_CD
		     , SECEDE_RSN
		     , SECEDE_DTL_RSN
		     , DATE_FORMAT(SECEDE_DT, '%Y%m%d%H%i%S') AS SECEDE_DT
		     , ROW_NUMBER() OVER(ORDER BY SECEDE_DT DESC)   AS NUMB
		FROM   TB_SECEDE_CUST
		WHERE  CUST_STAT = 'G104_30'
		<if test="searchTxt != null and searchTxt != ''">
		    <if test="searchGb == 'custNo'">
		AND    CUST_NO = #{searchTxt}
		    </if>
		    <if test="searchGb == 'custNm'">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'email'">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'custId'">
		AND    LOWER(CUST_ID) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		</if>
		<if test="siteCd != null and siteCd != ''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="cellPhnno != null and cellPhnno != ''">
		AND    CELL_PHNNO = #{cellPhnno}
		</if>
		<if test="secedeRsn != null and secedeRsn != ''">
		AND    SECEDE_RSN = #{secedeRsn}
		</if>
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		AND    SECEDE_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND    SECEDE_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		</if>
		<include refid="selectForPagingFooter"/>
	</select>

	<!-- 휴면회원목록 -->
	<select id="getCustomerDormantListCount" parameterType="CustomerSearch" resultType="int">
		/* TsaCustomer.getCustomerDormantListCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_DORMANT_CUST
		WHERE  CUST_STAT = 'G104_20'
		<if test="searchTxt != null and searchTxt != ''">
		    <if test="searchGb == 'custNo'">
		AND    CUST_NO = #{searchTxt}
		    </if>
		    <if test="searchGb == 'custNm'">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'email'">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'custId'">
		AND    LOWER(CUST_ID) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		</if>
		<if test="siteCd != null and siteCd != ''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="cellPhnno != null and cellPhnno != ''">
		AND    CELL_PHNNO = #{cellPhnno}
		</if>
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		AND    DORMANT_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND    DORMANT_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		</if>
	</select>

	<!-- 휴면회원목록 -->
	<select id="getCustomerDormantList" parameterType="CustomerSearch" resultType="Customer">
		/* TsaCustomer.getDormantCustomerList */
		<include refid="selectForPagingHeader"/>
		SELECT CUST_NO
		     , SITE_CD
		     , CUST_ID
		     , CUST_NM
		     , CELL_PHNNO
		     , EMAIL
		     , BIRTH_YMD
		     , DATE_FORMAT(DORMANT_DT, '%Y%m%d%H%i%S') AS DORMANT_DT
		     , ROW_NUMBER() OVER(ORDER BY DORMANT_DT DESC)   AS NUMB
		FROM   TB_DORMANT_CUST
		WHERE  CUST_STAT = 'G104_20'
		<if test="searchTxt != null and searchTxt != ''">
		    <if test="searchGb == 'custNo'">
		AND    CUST_NO = #{searchTxt}
		    </if>
		    <if test="searchGb == 'custNm'">
		AND    LOWER(CUST_NM) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'email'">
		AND    LOWER(EMAIL) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'custId'">
		AND    LOWER(CUST_ID) LIKE CONCAT('%',LOWER(#{searchTxt}),'%')
		    </if>
		</if>
		<if test="siteCd != null and siteCd != ''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="cellPhnno != null and cellPhnno != ''">
		AND    CELL_PHNNO = #{cellPhnno}
		</if>
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		AND    DORMANT_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND    DORMANT_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		</if>
		<include refid="selectForPagingFooter"/>
	</select>

	<!-- 휴대전화로 고객 조회 -->
	<select id="getCustomerFindByCellPhone" parameterType="CustomerSearch" resultType="int">
		/* TsaCustomer.getCustomerFindByCellPhone */
		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}
		            AND    CELL_PHNNO = #{cellPhnno}
		            UNION ALL
		            SELECT COUNT(*) AS CNT
		            FROM   TB_SECEDE_CUST
		            WHERE  SITE_CD = #{siteCd}
		            AND    CELL_PHNNO = #{cellPhnno}
		            UNION ALL
		            SELECT COUNT(*) AS CNT
		            FROM   TB_DORMANT_CUST
		            WHERE  SITE_CD = #{siteCd}
		            AND    CELL_PHNNO = #{cellPhnno}
		          ) A
		) B
		WHERE NUMB = 1
	</select>

</mapper>