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

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

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	
	<!-- 사용가능 포인트조회-->
	<select id="getUsablePointInfo" parameterType="Point" resultType="int">
		/* TsfPoint.getUsablePointInfo */
		SELECT IFNULL(SUM(RM_PNT_AMT), 0) AS RM_PNT_AMT
		FROM   TB_CUST_POINT
		WHERE  CUST_NO = #{custNo}
		AND    EXP_BE_DT > NOW()
		AND    EXP_CMP_DT IS NULL
	</select>
	
	<!-- 적립예정 포인트조회-->
	<select id="getExpectedPointInfo" parameterType="Point" resultType="int">
		/* TsfPoint.getExpectedPointInfo */
		SELECT IFNULL(SUM(CPH.PNT_AMT), 0) AS PNT_AMT
		FROM   TB_CUST_POINT_HST CPH
		WHERE  CPH.OCCUR_GB IN ('G069_12', 'G069_13')             -- 적립예정, 적립취소
		AND    CPH.PNT_UPLOAD_STAT IN ('G070_10')                 -- 반영예정
		AND    CPH.CUST_NO = #{custNo}
	</select>
	
	<!-- 한달 이내 소멸예정 포인트조회-->
	<select id="getExtinctPointInfo" parameterType="Point" resultType="int">
		/* TsfPoint.getExtinctPointInfo */
		SELECT IFNULL(SUM(RM_PNT_AMT),0) AS EXPECTED_EXPIRE_PNT_AMT
		FROM   TB_CUST_POINT
		WHERE  CUST_NO = #{custNo}
		AND    EXP_BE_DT > NOW()
		AND    GV_PNT_AMT > 0
		AND    EXP_BE_DT <![CDATA[<]]> DATE_ADD(NOW(),INTERVAL 31 DAY)
	</select>
	
	<!-- 적립내역 조회-->
	<select id="getAccumulatePointList" parameterType="Point" resultType="Point">
	/* TsfPoint.getAccumulatePointList */
		SELECT Z.*
		FROM (
		       SELECT  CASE CPH.OCCUR_GB
		                    WHEN 'G069_20' THEN '리뷰'
		                    WHEN 'G069_30' THEN '이벤트'
		                    WHEN 'G069_31' THEN '회원가입'
		                    WHEN 'G069_32' THEN '생일'
		                    WHEN 'G069_35' THEN 'APP'
		                    WHEN 'G069_36' THEN '상품 구매'
		                    WHEN 'G069_37' THEN '이벤트'
		                    WHEN 'G069_40' THEN '상품 구매'
		                    WHEN 'G069_41' THEN '구매 취소'
		                    WHEN 'G069_44' THEN '관리자'
		                    WHEN 'G069_45' THEN '관리자'
		                    WHEN 'G069_46' THEN '적립취소'
		                    WHEN 'G069_90' THEN '기타'
		                    WHEN 'G069_99' THEN '소멸'
		                    END                                          AS OCCUR_GB
		               ,CASE CPH.OCCUR_GB
		                   WHEN 'G069_20' THEN G.GOODS_NM
		                   WHEN 'G069_30' THEN '이벤트'
		                   WHEN 'G069_31' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_32' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_35' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_36' THEN G.GOODS_NM
		                   WHEN 'G069_37' THEN '출석체크'
		                   WHEN 'G069_40' THEN G.GOODS_NM
		                   WHEN 'G069_41' THEN G.GOODS_NM
		                   WHEN 'G069_44' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_45' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_46' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_90' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_99' THEN '유효기간 경과 소멸'
		                   END                                          AS  OCCUR_DTL_DESC
		            , CPH.PNT_AMT
		            , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S') AS PNT_UPLOAD_DT
		            , TCP.EXP_BE_DT 
		       FROM TB_CUST_POINT_HST CPH
		       LEFT JOIN TB_ORDER_DETAIL OD
		       ON  CPH.ORD_NO = OD.ORD_NO
		       AND CPH.ORD_DTL_NO = OD.ORD_DTL_NO
		       LEFT JOIN TB_GOODS G
		       ON  OD.GOODS_CD = G.GOODS_CD
		       INNER JOIN TB_CUST_POINT TCP 
		       ON   CPH.CUST_PNT_SQ = TCP.CUST_PNT_SQ
		       WHERE CPH.CUST_NO = #{custNo}
		       AND CPH.OCCUR_GB NOT IN ('G069_12','G069_13', 'G069_42', 'G069_43')
		       AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		       AND CPH.PNT_AMT <![CDATA[>]]> 0
		UNION ALL
		      SELECT  CASE CPH.OCCUR_GB
		                  WHEN 'G069_12' THEN '상품 구매'
		                  WHEN 'G069_13' THEN '구매 취소'
		                 END                                          AS OCCUR_GB
		            , CASE CPH.OCCUR_GB
		                  WHEN 'G069_12' THEN G.GOODS_NM
		                  WHEN 'G069_13' THEN G.GOODS_NM
		                 END                                          AS  OCCUR_DTL_DESC
		           , SUM(CPH.PNT_AMT)
		           , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S') AS PNT_UPLOAD_DT
		           , TCP.EXP_BE_DT 
		      FROM TB_CUST_POINT_HST CPH
		      LEFT JOIN TB_ORDER_DETAIL OD
		      ON  CPH.ORD_NO = OD.ORD_NO
		      AND CPH.ORD_DTL_NO = OD.ORD_DTL_NO
		      LEFT JOIN TB_GOODS G
		      ON  OD.GOODS_CD = G.GOODS_CD
		      INNER JOIN TB_CUST_POINT TCP 
		      ON   CPH.CUST_PNT_SQ = TCP.CUST_PNT_SQ
		      WHERE CPH.CUST_NO = #{custNo}
		        AND CPH.OCCUR_GB IN ('G069_12','G069_13')
		        AND CPH.PNT_UPLOAD_STAT = 'G070_30'
		        AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		        AND CPH.PNT_AMT <![CDATA[>]]> 0
		      GROUP BY CPH.OCCUR_GB, G.GOODS_NM,CPH.UPD_DT,TCP.EXP_BE_DT
		UNION ALL
		      SELECT CASE CPH.OCCUR_GB WHEN 'G069_42' THEN '사은품'
		                               WHEN 'G069_43' THEN '사은품'
		              END                                          AS OCCUR_GB
		           , CASE CPH.OCCUR_GB WHEN 'G069_42' THEN FG.GOODS_NM
		                               WHEN 'G069_43' THEN FG.GOODS_NM
		              END                                          AS  OCCUR_DTL_DESC
		           , CPH.PNT_AMT
		           , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S')  AS PNT_UPLOAD_DT
		           , CP.EXP_BE_DT
		        FROM TB_CUST_POINT_HST CPH
		       INNER JOIN TB_CUST_POINT CP
		          ON CPH.CUST_PNT_SQ = CP.CUST_PNT_SQ
		       INNER JOIN TB_ORD_FREEGIFT_VAL OFV
		          ON OFV.ORD_NO = CPH.ORD_NO
		         AND OFV.ORD_FREEGIFT_VAL_SQ = CPH.ORD_FREEGIFT_VAL_SQ
		       INNER JOIN TB_FREE_GOODS FG
		          ON OFV.PRODUCT_CODE = FG.PRODUCT_CODE
		         AND OFV.PRODUCT_NO = FG.PRODUCT_NO
		       WHERE CPH.CUST_NO = #{custNo}
		         AND CPH.OCCUR_GB IN ('G069_42','G069_43')
		         AND CPH.PNT_UPLOAD_STAT = 'G070_30'
		         AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		         AND CPH.PNT_AMT <![CDATA[>]]> 0
		) Z
		WHERE Z.PNT_UPLOAD_DT IS NOT NULL
		ORDER BY Z.PNT_UPLOAD_DT DESC	
	</select>
	
	<!-- 사용내역 조회-->
	<select id="getUsePointList" parameterType="Point" resultType="Point">
	/* TsfPoint.getUsePointList */
		SELECT Z.*
		FROM (
		       SELECT  CASE CPH.OCCUR_GB
		                    WHEN 'G069_20' THEN '리뷰'
		                    WHEN 'G069_30' THEN '이벤트'
		                    WHEN 'G069_31' THEN '회원가입'
		                    WHEN 'G069_32' THEN '생일'
		                    WHEN 'G069_35' THEN 'APP'
		                    WHEN 'G069_36' THEN '상품 구매'
		                    WHEN 'G069_37' THEN '이벤트'
		                    WHEN 'G069_40' THEN '상품 구매'
		                    WHEN 'G069_41' THEN '구매 취소'
		                    WHEN 'G069_44' THEN '관리자'
		                    WHEN 'G069_45' THEN '관리자'
		                    WHEN 'G069_46' THEN '적립취소'
		                    WHEN 'G069_90' THEN '기타'
		                    WHEN 'G069_99' THEN '소멸'
		                    END                                          AS OCCUR_GB
		               ,CASE CPH.OCCUR_GB
		                   WHEN 'G069_20' THEN G.GOODS_NM
		                   WHEN 'G069_30' THEN '이벤트'
		                   WHEN 'G069_31' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_32' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_35' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_36' THEN G.GOODS_NM
		                   WHEN 'G069_37' THEN '출석체크'
		                   WHEN 'G069_40' THEN G.GOODS_NM
		                   WHEN 'G069_41' THEN G.GOODS_NM
		                   WHEN 'G069_44' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_45' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_46' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_90' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_99' THEN '유효기간 경과 소멸'
		                   END                                          AS  OCCUR_DTL_DESC
		            , CPH.PNT_AMT
		            , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S') AS PNT_UPLOAD_DT
		            , TCP.EXP_BE_DT 
		       FROM TB_CUST_POINT_HST CPH
		       LEFT JOIN TB_ORDER_DETAIL OD
		       ON  CPH.ORD_NO = OD.ORD_NO
		       AND CPH.ORD_DTL_NO = OD.ORD_DTL_NO
		       LEFT JOIN TB_GOODS G
		       ON  OD.GOODS_CD = G.GOODS_CD
		       INNER JOIN TB_CUST_POINT TCP 
		       ON   CPH.CUST_PNT_SQ = TCP.CUST_PNT_SQ
		       WHERE CPH.CUST_NO = #{custNo}
		       AND CPH.OCCUR_GB NOT IN ('G069_12','G069_13', 'G069_42', 'G069_43')
		       AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		       AND CPH.PNT_AMT <![CDATA[<]]> 0
		UNION ALL
		      SELECT  CASE CPH.OCCUR_GB
		                  WHEN 'G069_12' THEN '상품 구매'
		                  WHEN 'G069_13' THEN '구매 취소'
		                 END                                          AS OCCUR_GB
		            , CASE CPH.OCCUR_GB
		                  WHEN 'G069_12' THEN G.GOODS_NM
		                  WHEN 'G069_13' THEN G.GOODS_NM
		                 END                                          AS  OCCUR_DTL_DESC
		           , SUM(CPH.PNT_AMT)
		           , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S') AS PNT_UPLOAD_DT
		           , TCP.EXP_BE_DT 
		      FROM TB_CUST_POINT_HST CPH
		      LEFT JOIN TB_ORDER_DETAIL OD
		      ON  CPH.ORD_NO = OD.ORD_NO
		      AND CPH.ORD_DTL_NO = OD.ORD_DTL_NO
		      LEFT JOIN TB_GOODS G
		      ON  OD.GOODS_CD = G.GOODS_CD
		      INNER JOIN TB_CUST_POINT TCP 
		      ON   CPH.CUST_PNT_SQ = TCP.CUST_PNT_SQ
		      WHERE CPH.CUST_NO = #{custNo}
		        AND CPH.OCCUR_GB IN ('G069_12','G069_13')
		        AND CPH.PNT_UPLOAD_STAT = 'G070_30'
		        AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		        AND CPH.PNT_AMT <![CDATA[<]]> 0
		      GROUP BY CPH.OCCUR_GB, G.GOODS_NM,CPH.UPD_DT,TCP.EXP_BE_DT
		UNION ALL
		      SELECT CASE CPH.OCCUR_GB WHEN 'G069_42' THEN '사은품'
		                               WHEN 'G069_43' THEN '사은품'
		              END                                          AS OCCUR_GB
		           , CASE CPH.OCCUR_GB WHEN 'G069_42' THEN FG.GOODS_NM
		                               WHEN 'G069_43' THEN FG.GOODS_NM
		              END                                          AS  OCCUR_DTL_DESC
		            , CPH.PNT_AMT
		            , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S')  AS PNT_UPLOAD_DT
		            , CP.EXP_BE_DT
		         FROM TB_CUST_POINT_HST CPH
		        INNER JOIN TB_CUST_POINT CP
		           ON CPH.CUST_PNT_SQ = CP.CUST_PNT_SQ
		        INNER JOIN TB_ORD_FREEGIFT_VAL OFV
		           ON OFV.ORD_NO = CPH.ORD_NO
		          AND OFV.ORD_FREEGIFT_VAL_SQ = CPH.ORD_FREEGIFT_VAL_SQ
		        INNER JOIN TB_FREE_GOODS FG
		           ON OFV.PRODUCT_CODE = FG.PRODUCT_CODE
		          AND OFV.PRODUCT_NO = FG.PRODUCT_NO
		        WHERE CPH.CUST_NO = #{custNo}
		          AND CPH.OCCUR_GB IN ('G069_42','G069_43')
		          AND CPH.PNT_UPLOAD_STAT = 'G070_30'
		          AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		          AND CPH.PNT_AMT <![CDATA[<]]> 0
		) Z
		WHERE Z.PNT_UPLOAD_DT IS NOT NULL
		ORDER BY Z.PNT_UPLOAD_DT DESC	
	</select>
	
	<!-- 전체내역 조회-->
	<select id="getAllPointList" parameterType="Point" resultType="Point">
	/* TsfPoint.getAllPointList */
		SELECT Z.*
		FROM (
		       SELECT  CASE CPH.OCCUR_GB
		                    WHEN 'G069_20' THEN '리뷰'
		                    WHEN 'G069_30' THEN '이벤트'
		                    WHEN 'G069_31' THEN '회원가입'
		                    WHEN 'G069_32' THEN '생일'
		                    WHEN 'G069_35' THEN 'APP'
		                    WHEN 'G069_36' THEN '상품 구매'
		                    WHEN 'G069_37' THEN '이벤트'
		                    WHEN 'G069_40' THEN '상품 구매'
		                    WHEN 'G069_41' THEN '구매 취소'
		                    WHEN 'G069_44' THEN '관리자'
		                    WHEN 'G069_45' THEN '관리자'
		                    WHEN 'G069_46' THEN '적립취소'
		                    WHEN 'G069_90' THEN '기타'
		                    WHEN 'G069_99' THEN '소멸'
		                    END                                          AS OCCUR_GB
		               ,CASE CPH.OCCUR_GB
		                   WHEN 'G069_20' THEN G.GOODS_NM
		                   WHEN 'G069_30' THEN '이벤트'
		                   WHEN 'G069_31' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_32' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_35' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_36' THEN G.GOODS_NM
		                   WHEN 'G069_37' THEN '출석체크'
		                   WHEN 'G069_40' THEN G.GOODS_NM
		                   WHEN 'G069_41' THEN G.GOODS_NM
		                   WHEN 'G069_44' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_45' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_46' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_90' THEN CPH.OCCUR_DTL_DESC
		                   WHEN 'G069_99' THEN '유효기간 경과 소멸'
		                   END                                          AS  OCCUR_DTL_DESC
		            , CPH.PNT_AMT
		            , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S') AS PNT_UPLOAD_DT
		            , TCP.EXP_BE_DT 
		       FROM TB_CUST_POINT_HST CPH
		       LEFT JOIN TB_ORDER_DETAIL OD
		       ON  CPH.ORD_NO = OD.ORD_NO
		       AND CPH.ORD_DTL_NO = OD.ORD_DTL_NO
		       LEFT JOIN TB_GOODS G
		       ON  OD.GOODS_CD = G.GOODS_CD
		       INNER JOIN TB_CUST_POINT TCP 
		       ON   CPH.CUST_PNT_SQ = TCP.CUST_PNT_SQ
		       WHERE CPH.CUST_NO = #{custNo}
		       AND CPH.OCCUR_GB NOT IN ('G069_12','G069_13', 'G069_42', 'G069_43')
		       AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		UNION ALL
		      SELECT  CASE CPH.OCCUR_GB
		                  WHEN 'G069_12' THEN '상품 구매'
		                  WHEN 'G069_13' THEN '구매 취소'
		                 END                                          AS OCCUR_GB
		            , CASE CPH.OCCUR_GB
		                  WHEN 'G069_12' THEN G.GOODS_NM
		                  WHEN 'G069_13' THEN G.GOODS_NM
		                 END                                          AS  OCCUR_DTL_DESC
		           , SUM(CPH.PNT_AMT)
		           , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S') AS PNT_UPLOAD_DT
		           , TCP.EXP_BE_DT 
		      FROM TB_CUST_POINT_HST CPH
		      LEFT JOIN TB_ORDER_DETAIL OD
		      ON  CPH.ORD_NO = OD.ORD_NO
		      AND CPH.ORD_DTL_NO = OD.ORD_DTL_NO
		      LEFT JOIN TB_GOODS G
		      ON  OD.GOODS_CD = G.GOODS_CD
		      INNER JOIN TB_CUST_POINT TCP 
		      ON   CPH.CUST_PNT_SQ = TCP.CUST_PNT_SQ
		      WHERE CPH.CUST_NO = #{custNo}
		        AND CPH.OCCUR_GB IN ('G069_12','G069_13')
		        AND CPH.PNT_UPLOAD_STAT = 'G070_30'
		        AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		       GROUP BY CPH.OCCUR_GB, G.GOODS_NM,CPH.UPD_DT,TCP.EXP_BE_DT
		UNION ALL
		      SELECT CASE CPH.OCCUR_GB WHEN 'G069_42' THEN '사은품'
		                               WHEN 'G069_43' THEN '사은품'
		              END                                          AS OCCUR_GB
		           , CASE CPH.OCCUR_GB WHEN 'G069_42' THEN FG.GOODS_NM
		                               WHEN 'G069_43' THEN FG.GOODS_NM
		              END                                          AS  OCCUR_DTL_DESC
		            , CPH.PNT_AMT
		            , DATE_FORMAT(CPH.UPD_DT, '%Y.%m.%d %H:%i:%S')  AS PNT_UPLOAD_DT
		            , CP.EXP_BE_DT
		         FROM TB_CUST_POINT_HST CPH
		        INNER JOIN TB_CUST_POINT CP
		           ON CPH.CUST_PNT_SQ = CP.CUST_PNT_SQ
		        INNER JOIN TB_ORD_FREEGIFT_VAL OFV
		           ON OFV.ORD_NO = CPH.ORD_NO
		          AND OFV.ORD_FREEGIFT_VAL_SQ = CPH.ORD_FREEGIFT_VAL_SQ
		        INNER JOIN TB_FREE_GOODS FG
		           ON OFV.PRODUCT_CODE = FG.PRODUCT_CODE
		          AND OFV.PRODUCT_NO = FG.PRODUCT_NO
		        WHERE CPH.CUST_NO = #{custNo}
		          AND CPH.OCCUR_GB IN ('G069_42','G069_43')
		          AND CPH.PNT_UPLOAD_STAT = 'G070_30'
		          AND DATE_FORMAT(CPH.UPD_DT,'%Y%m') = #{searchDt}
		) Z
		WHERE Z.PNT_UPLOAD_DT IS NOT NULL
		ORDER BY Z.PNT_UPLOAD_DT DESC	
		</select>

	<!-- 고객 포인트 내역 -->
	<select id="getCustomerPoint" parameterType="Integer" resultType="Point">
		/* TsfCustomer.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    GV_PNT_AMT > 0
		               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   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="getRecentlyDate" resultType="Point">
		SELECT A.*
		FROM
		    (		
		      SELECT DATE_FORMAT( NOW(), '%Y') AS YEAR ,DATE_FORMAT( NOW(), '%m') AS MONTH
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 1 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 1 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 2 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 2 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 3 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 3 MONTH), '%m')      
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 4 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 4 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 5 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 5 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 6 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 6 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 7 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 7 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 8 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 8 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 9 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 9 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 10 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 10 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 11 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 11 MONTH), '%m')
		    )A	
	</select>
</mapper>