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

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

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		<choose>
		<when test="pageable != null">
		    ) ORIGINAL
		WHERE RNUM BETWEEN  #{pageable.startRow} AND #{pageable.endRow}
		</when>
		<otherwise>
		    ) ORIGINAL
		</otherwise>
		</choose>
	</sql>
	
		<sql id="selectForPagingFooter2">
		<choose>
		<when test="pageable2 != null">
		    ) ORIGINAL
		WHERE RNUM BETWEEN  #{pageable2.startRow} AND #{pageable2.endRow}
		</when>
		<otherwise>
		    ) ORIGINAL
		</otherwise>
		</choose>
	</sql>
	
	
	<!-- 상품 리뷰 목록 count -->
	<select id="getReviewTotalCount" parameterType="Review" resultType="int">
		/* TsfReivew.getReviewTotalCount */
		SELECT COUNT(*)
		FROM TB_REVIEW R 
		INNER JOIN TB_GOODS G ON R.GOODS_CD = G.GOODS_CD
		INNER JOIN TB_ORDER_DETAIL OD ON R.ORD_NO = OD.ORD_NO 
		                              AND R.ORD_DTL_NO  = OD.ORD_DTL_NO
		                              AND R.GOODS_CD = OD.GOODS_CD
		INNER JOIN TB_CUSTOMER C ON R.REG_NO  = C.CUST_NO
		<if test="goodsType != null and goodsType == 'G056_D'">
		INNER JOIN TB_GOODS_COMPOSE GC ON R.GOODS_CD  = GC.COMPS_GOODS_CD
		</if>
		WHERE 1 = 1
		AND R.CONFIRM_YN = 'Y'  -- 승인된 상품평만 조회
		<choose>
		    <when test="goodsType != null and goodsType == 'G056_D'">
		AND    R.GOODS_CD = GC.COMPS_GOODS_CD
		AND    GC.GOODS_CD = #{goodsCd}
		    </when>
		    <otherwise>
		AND R.GOODS_CD = #{goodsCd}
		    </otherwise>
		</choose>
		
		<if test='bestYn != null and bestYn == "Y"'>
		AND R.BEST_YN = 'Y'
		</if>
		<if test='photoYn != null and photoYn == "Y"'>
		AND EXISTS (SELECT 1 
		            FROM TB_REVIEW_ATTACH 
		            WHERE DEL_YN = 'N' 
		            AND REVIEW_SQ = R.REVIEW_SQ
		            AND (FILE_GB  = 'I'
		                 OR
		                 FILE_GB = 'M' AND R.CONFIRM_YN = 'Y' AND NULLIF(KMC_KEY,'') IS NOT NULL 
		                )
		            )
		</if>
		<if test="reviewScore != null and reviewScore != ''">
		AND R.SCORE = #{reviewScore}
		</if>
		<if test="reviewOption != null and reviewOption != ''">
		<choose>
		    <when test='selfGoodsYn != null and selfGoodsYn == "Y"'>
		AND EXISTS (SELECT 1
		            FROM TB_ORDER_DETAIL_ITEM ODI 
		            WHERE ODI.ORD_NO = R.ORD_NO 
		            AND ODI.ORD_DTL_NO  = R.ORD_DTL_NO
		            AND ODI.OPT_CD2 = #{reviewOption}
		           )
		    </when>
		    <otherwise>
		AND EXISTS (SELECT 1
		            FROM TB_ORDER_DETAIL_ITEM ODI 
		            WHERE ODI.ORD_NO = R.ORD_NO 
		            AND ODI.ORD_DTL_NO  = R.ORD_DTL_NO
		            AND ODI.OPT_CD1 = #{reviewOption}
		           )
		    </otherwise>
		</choose>
		</if>
		<if test="reviewHeight != null and reviewHeight != ''">
		 <![CDATA[
		AND R.HEIGHT >= #{reviewHeightSt}
		AND R.HEIGHT < #{reviewHeightEd}
		]]>
		</if>
		<if test="reviewWeight != null and reviewWeight != ''">
		<![CDATA[
		AND R.WEIGHT >= #{reviewWeightSt}
		AND R.WEIGHT < #{reviewWeightEd}
		]]>
		</if>
		AND R.DEL_YN = 'N'
		AND R.DISP_YN = 'Y'
	</select>
		
	<!-- 상품 리뷰 목록 -->
	<select id="getReviewList" parameterType="Review" resultType="Review">
		/* TsfReivew.getReviewList */
		<include refid="selectForPagingHeader"/>
		SELECT *
		FROM (
		SELECT R.REVIEW_SQ
		     , R.GOODS_CD
		     , R.REVIEW_GB
		     , R.CUST_NO
		     , R.ORD_NO
		     , R.ORD_DTL_NO
		     , R.REVIEW_TITLE
		     , R.REVIEW_CONTENT
		     , R.SCORE
		     , CAST(R.SCORE AS UNSIGNED) AS ISCORE
		     , R.HEIGHT
		     , R.WEIGHT
		     , R.SCORE_SIZE
		     -- , CAST(R.SCORE_SIZE AS UNSIGNED) AS SCORE_SIZE
		     , FN_GET_CODE_NM('G040',R.SCORE_SIZE) AS SCORE_SIZE_NM
		     , R.SCORE_COLOR
		     -- , CAST(R.SCORE_COLOR AS UNSIGNED) AS SCORE_COLOR
		     , FN_GET_CODE_NM('G039',R.SCORE_COLOR) AS SCORE_COLOR_NM
		     , R.SCORE_FIT
		     -- , CAST(IFNULL(NULLIF(R.SCORE_FIT,'') , 0) AS UNSIGNED) AS SCORE_FIT
		     , FN_GET_CODE_NM('G042',R.SCORE_FIT) AS SCORE_FIT_NM
		     , R.SCORE_THICK
		     -- ,  CAST(IFNULL(NULLIF(R.SCORE_THICK,'') , 0) AS UNSIGNED) AS SCORE_THICK
		     , FN_GET_CODE_NM('G079',R.SCORE_THICK) AS SCORE_THICK_NM
		     , R.SCORE_WEIGHT
		     -- ,  CAST(IFNULL(NULLIF(R.SCORE_WEIGHT,'') , 0) AS UNSIGNED) AS SCORE_WEIGHT
		     , FN_GET_CODE_NM('G080',R.SCORE_WEIGHT) AS SCORE_WEIGHT_NM
		     , R.SCORE_BALL
		     -- ,  CAST(IFNULL(NULLIF(R.SCORE_BALL,'') , 0) AS UNSIGNED) AS SCORE_BALL
		     , FN_GET_CODE_NM('G041',R.SCORE_BALL) AS SCORE_BALL_NM
		     , R.GIVE_DUE_PNT
		     , R.PNT_GIVE_STAT
		     , R.BEST_YN
		     , R.GIVE_DUE_BPNT
		     , R.BPNT_GIVE_YN
		     , R.DISP_YN
		     , R.DEL_YN
		     , R.CONFIRM_YN
		     , R.CONFIRM_UNO
		     , R.CONFIRM_DT
		     , R.REG_NO
		     , DATE_FORMAT(R.REG_DT,'%Y.%m.%d') AS REG_DT
		     , R.ADM_RPL
		     , R.ADM_RPL_REG_NO
		     , DATE_FORMAT(R.ADM_RPL_DT,'%Y.%m.%d') AS ADM_RPL_DT
		     , C.CUST_ID 
		     , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') AS SYS_IMG_NM
		     , (SELECT TI.SIZE_GB FROM TB_ITEMKIND TI WHERE TI.ITEMKIND_CD = G.ITEMKIND_CD) AS SIZE_GB
		     , (SELECT MAX(RP.REVIEW_SQ) 
		        FROM TB_REVIEW RP
		        WHERE RP.REVIEW_SQ <![CDATA[<]]> #{reviewSq} 
		        AND RP.GOODS_CD = R.GOODS_CD 
		        AND RP.DEL_YN = 'N'
		        <if test='bestYn != null and bestYn == "Y"'>
		        AND RP.BEST_YN = 'Y'
		        </if>
		        <if test='photoYn != null and photoYn == "Y"'>
		        AND EXISTS (SELECT 1 
		                    FROM TB_REVIEW_ATTACH RPA
		                    WHERE RPA.DEL_YN = 'N' 
		                    AND RPA.REVIEW_SQ = RP.REVIEW_SQ
		                    AND (RPA.FILE_GB  = 'I'
		                         OR
		                         RPA.FILE_GB = 'M' AND RP.CONFIRM_YN = 'Y'  AND  NULLIF(RPA.KMC_KEY,'') IS NOT NULL
		                        )
		                    )
		        </if>
		        ) AS NEXT_REVIEW_SQ       -- DESC순 
		     , (SELECT MIN(RN.REVIEW_SQ) 
		        FROM TB_REVIEW RN
		        WHERE RN.REVIEW_SQ > #{reviewSq}
		        AND RN.GOODS_CD = R.GOODS_CD 
		        AND RN.DEL_YN = 'N'
		        <if test='bestYn != null and bestYn == "Y"'>
		        AND RN.BEST_YN = 'Y'
		        </if>
		        <if test='photoYn != null and photoYn == "Y"'>
		        AND EXISTS (SELECT 1 
		                    FROM TB_REVIEW_ATTACH RNA
		                    WHERE RNA.DEL_YN = 'N' 
		                    AND RNA.REVIEW_SQ = RN.REVIEW_SQ
		                    AND (RNA.FILE_GB  = 'I'
		                         OR
		                         RNA.FILE_GB = 'M' AND RN.CONFIRM_YN = 'Y'  AND  RNA.KMC_KEY IS NOT NULL
		                        )
		                    )
		        </if>
		        ) AS PREV_REVIEW_SQ       -- DESC순
		     , RANK() OVER(ORDER BY R.REVIEW_SQ DESC) AS RNUM
		FROM TB_REVIEW R 
		INNER JOIN TB_GOODS G ON R.GOODS_CD = G.GOODS_CD
		INNER JOIN TB_ORDER_DETAIL OD ON R.ORD_NO = OD.ORD_NO 
		                              AND R.ORD_DTL_NO  = OD.ORD_DTL_NO 
		                              AND R.GOODS_CD = OD.GOODS_CD
		INNER JOIN TB_CUSTOMER C ON R.REG_NO  = C.CUST_NO
		WHERE 1 = 1
		AND R.GOODS_CD = #{goodsCd}
		AND R.CONFIRM_YN = 'Y'  -- 승인된 상품평만 조회
		<if test="reviewSq != null and reviewSq != ''" >
		AND R.REVIEW_SQ = #{reviewSq}
		</if>
		<if test='bestYn != null and bestYn == "Y"'>
		AND R.BEST_YN = 'Y'
		</if>
		<if test='photoYn != null and photoYn == "Y"'>
		AND EXISTS (SELECT 1 
		            FROM TB_REVIEW_ATTACH 
		            WHERE DEL_YN = 'N' 
		            AND REVIEW_SQ = R.REVIEW_SQ
		            AND (FILE_GB  = 'I'
		                 OR
		                 FILE_GB = 'M' AND R.CONFIRM_YN = 'Y' AND  KMC_KEY IS NOT NULL
		                )
		            )
		</if>
		AND R.DEL_YN = 'N'
		AND R.DISP_YN = 'Y'
		<if test="reviewScore != null and reviewScore != ''">
		AND R.SCORE = #{reviewScore}
		</if>
		<if test="reviewOption != null and reviewOption != ''">
		<choose>
		    <when test='selfGoodsYn != null and selfGoodsYn == "Y"'>
		AND EXISTS (SELECT 1
		            FROM TB_ORDER_DETAIL_ITEM ODI 
		            WHERE ODI.ORD_NO = R.ORD_NO 
		            AND ODI.ORD_DTL_NO  = R.ORD_DTL_NO
		            AND ODI.OPT_CD2 = #{reviewOption}
		           )
		    </when>
		    <otherwise>
		AND EXISTS (SELECT 1
		            FROM TB_ORDER_DETAIL_ITEM ODI 
		            WHERE ODI.ORD_NO = R.ORD_NO 
		            AND ODI.ORD_DTL_NO  = R.ORD_DTL_NO
		            AND ODI.OPT_CD1 = #{reviewOption}
		           )
		    </otherwise>
		</choose>
		</if>
		<if test="reviewHeight != null and reviewHeight != ''">
		 <![CDATA[
		AND R.HEIGHT >= #{reviewHeightSt}
		AND R.HEIGHT <= #{reviewHeightEd}
		]]>
		</if>
		<if test="reviewWeight != null and reviewWeight != ''">
		<![CDATA[
		AND R.WEIGHT >= #{reviewWeightSt}
		AND R.WEIGHT <= #{reviewWeightEd}
		]]>
		</if>
		)Z
		<if test="rownum != null and rownum > 0 ">
		WHERE RNUM = 1
		</if>
		<include refid="selectForPagingFooter"/>
	</select>
	
	<!-- 상품평 첨부파일 목록 -->
	<select id="getReviewAttachList" parameterType="Review" resultType="ReviewAttach">
		/* TsfReivew.getReviewAttachList */
		SELECT A.RV_ATC_SQ
		     , A.REVIEW_SQ
		     , A.FILE_GB
		     , A.SYS_FILE_NM
		     , A.KMC_KEY
		     , A.KUF_KEY
		     , A.DEL_YN 
		     , RANK() OVER(ORDER BY A.FILE_GB DESC) AS NUMB
		FROM  TB_REVIEW R
		INNER JOIN TB_REVIEW_ATTACH A ON R.REVIEW_SQ = A.REVIEW_SQ
		                              AND A.DEL_YN = 'N'
		WHERE R.DISP_YN = 'Y'
		AND R.DEL_YN = 'N'
		AND R.REVIEW_SQ = #{reviewSq}
		AND (FILE_GB  = 'I'
		     OR
		     FILE_GB = 'M' AND R.CONFIRM_YN = 'Y'  AND  NULLIF(KMC_KEY,'') IS NOT NULL
		    )
		<!-- <if test="rvAtcSq != null and rvAtcSq != ''">
		ORDER BY FIELD(A.RV_ATC_SQ, #{rvAtcSq})DESC
		        ,RV_ATC_SQ ASC 
		</if> -->
	</select>
	
	<!-- 상품평 상품 옵션 목록 -->
	<select id="getReviewGoodsOptionList" parameterType="Review" resultType="Goods">
		/* TsfReivew.getReviewGoodsOptionList */
		SELECT R.REVIEW_SQ
		     , R.GOODS_CD
		    -- , R.ORD_NO 
		    -- , R.ORD_DTL_NO 
		     , G.GOODS_NM 
		     , ODI.ITEM_CD 
		     , ODI.OPT_CD1 
		     , ODI.OPT_CD2
		     , IFNULL(C.COLOR_KNM, ODI.OPT_CD1) AS OPT_CD1_NM
		FROM TB_REVIEW R 
		INNER JOIN TB_ORDER_DETAIL OD ON R.ORD_NO = OD.ORD_NO 
		                              AND R.ORD_DTL_NO  = OD.ORD_DTL_NO
		                              AND R.GOODS_CD  = OD.GOODS_CD 
		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 G ON ODI.ITEM_CD = G.GOODS_CD
		LEFT OUTER JOIN TB_COLOR C ON ODI.OPT_CD1 = C.COLOR_CD
		                           AND C.USE_YN = 'Y'
		WHERE R.REVIEW_SQ = #{reviewSq} 
		AND R.GOODS_CD = #{goodsCd}
	</select>
	
	<!-- 상품평 옵션 목록 - 입점 -->
	<select id="getReviewOption1List" parameterType="String" resultType="Goods">
		/* TsfReivew.getReviewOption1List */
		SELECT OPT_CD1 AS OPT_CD
		FROM (
		    SELECT DISTINCT ODI.OPT_CD1
		    FROM TB_REVIEW R 
		    INNER JOIN TB_ORDER_DETAIL OD ON R.ORD_NO = OD.ORD_NO 
		                                  AND R.ORD_DTL_NO  = OD.ORD_DTL_NO
		                                  AND R.GOODS_CD  = OD.GOODS_CD 
		    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 G ON ODI.ITEM_CD = G.GOODS_CD
		    WHERE R.GOODS_CD = #{goodsCd}
		) Z
	</select>
	
	<select id="getReviewOption2List" parameterType="String" resultType="Goods">
		/* TsfReivew.getReviewOption2List */
		SELECT OPT_CD2 AS OPT_CD
		FROM (
		    SELECT DISTINCT ODI.OPT_CD2
		    FROM TB_REVIEW R 
		    INNER JOIN TB_ORDER_DETAIL OD ON R.ORD_NO = OD.ORD_NO 
		                                  AND R.ORD_DTL_NO  = OD.ORD_DTL_NO
		                                  AND R.GOODS_CD  = OD.GOODS_CD 
		    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 G ON ODI.ITEM_CD = G.GOODS_CD
		    WHERE R.GOODS_CD = #{goodsCd}
		) Z    
	</select>

	<!-- 마이페이지 작성가능한 리뷰 -->
	<select id="getCompleteReviewList" parameterType="Review" resultType="Review">
		/* TsfReivew.getCompleteReviewList */
		<include refid="selectForPagingHeader"/>
		SELECT  GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
		     -- , GROUP_CONCAT(Z.COLOR_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		        , GROUP_CONCAT(' ',CONCAT(OPT_CD1_NM,' / ', OPT_CD2) ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		        , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1
		        , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD2
		        , GROUP_CONCAT(Z.OPT_CD1_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1_NM
		        , Z.ORD_NO
		        , Z.ORD_DT
		        , Z.PAY_DT
		        , Z.ORD_DTL_NO
		        , Z.GOODS_CD
		        , Z.GOODS_NM
		        , Z.LIST_PRICE
		        , Z.GOODS_TYPE
		        , Z.SYS_IMG_NM
		        , Z.BRAND_NM
		        , Z.BRAND_ENM
		        , Z.REMAIN_DT
		        , ROW_NUMBER() OVER(ORDER BY Z.REG_DT DESC) AS RNUM
		  FROM (
		        SELECT O.ORD_NO
		             , O.REG_DT 
		             , DATE_FORMAT(O.ORD_DT, '%Y.%m.%d') AS ORD_DT
		             , DATE_FORMAT(O.PAY_DT, '%Y.%m.%d') AS PAY_DT 
		             , DATE_FORMAT(OD.DELV_EDDT, '%m/%d')								AS DELV_EDDT
		             , OD.ORD_DTL_NO
		             , OD.GOODS_CD
		             , G.GOODS_NM
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), ODI.OPT_CD1) AS COLOR_NM
		             , (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = ODI.ITEM_CD)		AS ITEM_NM
		             , ODI.ORD_DTL_ITEM_SQ
		             , ODI.OPT_CD1
		             , ODI.OPT_CD2
		             , IFNULL(C.COLOR_KNM, ODI.OPT_CD1) AS OPT_CD1_NM
		             , G.LIST_PRICE
		             , G.GOODS_TYPE
		             , CASE WHEN G.GOODS_TYPE != 'G056_S' AND G.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
		                    ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
		             , CASE BG.DISP_NM_LANG WHEN 'EN' THEN BG.BRAND_GROUP_ENM
		                       ELSE BG.BRAND_GROUP_KNM
		               END                                                             AS BRAND_NM
		             , B.BRAND_ENM
		             ,DATEDIFF(DATE_ADD(O.ORD_DT,INTERVAL  90 /**P*/ DAY),NOW()) AS REMAIN_DT -- 남은시간
				  FROM TB_ORDER O
				 INNER JOIN TB_ORDER_DETAIL OD
				    ON O.ORD_NO = OD.ORD_NO
				   AND OD.ORD_DTL_STAT = 'G013_70' -- 구매 확정인것만
				 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 G
				    ON OD.GOODS_CD = G.GOODS_CD
				 INNER JOIN TB_BRAND B
				    ON B.BRAND_CD = G.BRAND_CD
				 INNER JOIN TB_BRAND_GROUP BG
		            ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
				 LEFT OUTER JOIN TB_COLOR C ON ODI.OPT_CD1 = C.COLOR_CD
		                                   AND C.USE_YN = 'Y'
				  WHERE 1=1
				-- AND O.ORD_NO NOT IN (SELECT R.ORD_NO FROM TB_REVIEW R WHERE R.DEL_YN = 'N' AND DISP_YN = 'Y')
				AND OD.ORD_DTL_NO NOT IN (SELECT R.ORD_DTL_NO FROM TB_REVIEW R WHERE R.DEL_YN = 'N' AND DISP_YN = 'Y' AND R.ORD_NO = O.ORD_NO AND R.ORD_DTL_NO = OD.ORD_DTL_NO AND R.CUST_NO = #{custNo})
				AND O.CUST_NO = #{custNo}
				AND O.DISP_YN = 'Y'
			 	AND O.SITE_CD = #{siteCd}
				) Z		 
		 WHERE Z.REMAIN_DT > 0
		 GROUP BY   Z.ORD_NO
		          , Z.ORD_DT
		          , Z.PAY_DT
		          , Z.ORD_DTL_NO
		          , Z.GOODS_CD
		          , Z.GOODS_NM
		          , Z.LIST_PRICE
		          , Z.GOODS_TYPE
		          , Z.SYS_IMG_NM
		          , Z.BRAND_NM
		          , Z.BRAND_ENM
                  , Z.REMAIN_DT
		ORDER BY Z.ORD_NO DESC
		<include refid="selectForPagingFooter"/>
	</select>
	
	<!-- 마이페이지 리뷰작성 상품데이타 -->
	<select id="getReviewGoodsInfo" parameterType="Review" resultType="Review">
		/* TsfReivew.getReviewGoodsInfo */
		SELECT GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
		  -- , GROUP_CONCAT(Z.COLOR_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		     , GROUP_CONCAT(CONCAT(' ',OPT_CD1_NM,' / ', OPT_CD2) ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		     , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1
		     , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD2
		     , GROUP_CONCAT(Z.OPT_CD1_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1_NM
		     , Z.ORD_NO
		     , Z.ORD_DT
		     , Z.PAY_DT
		     , Z.DELV_EDDT
		     , Z.ORD_DTL_NO
		     , Z.ORD_EXCH_GB
		     , Z.GOODS_CD
		     , Z.GOODS_NM
		     , Z.LIST_PRICE
		     , Z.GOODS_TYPE
		     , Z.SYS_IMG_NM
		     , Z.ORD_DTL_STAT
		     , Z.ORD_QTY
		     , Z.CNCL_RTN_QTY
		     , Z.ORD_AMT
		     , Z.REAL_ORD_AMT
		     , Z.SAVE_PNT_AMT
		     , Z.CNCL_RTN_AMT
		     , Z.PNT_DC_AMT
		     , Z.GFCD_USE_AMT
		     , Z.INVOICE_NO
		     , Z.SUPPLY_COMP_CD
		     , Z.DELV_FEE_CD
		     , Z.SHOT_DELV_YN
		     , Z.CHANGEABLE_YN
		     , Z.RETURNABLE_YN
		     , Z.REG_DT
		     , Z.SELF_GOODS_YN
		     , Z.BRAND_NM
		     , Z.BRAND_ENM
		     , Z.ORD_DTL_STAT_NM
		     , Z.ORD_REQ_CHG_QTY
		     , Z.ORD_CAN_CHG_QTY
		     , Z.ITEMKIND_NM 
		   	 , Z.ITEMKIND_CD 
		     , Z.SIZE_GB 
		     , Z.REMAIN_DT
		     <if test="reviewStat == 'u'.toString()">
		     , Z.REVIEW_SQ        
			 , Z.REVIEW_GB        
			 , Z.REVIEW_TITLE     
			 , Z.REVIEW_CONTENT   
			 , Z.SCORE            
			 , Z.HEIGHT           
			 , Z.WEIGHT           
			 , Z.SCORE_SIZE       
			 , Z.SCORE_COLOR      
			 , Z.SCORE_FIT        
			 , Z.SCORE_THICK      
			 , Z.SCORE_WEIGHT     
			 , Z.SCORE_BALL       
			 , Z.GIVE_DUE_PNT     
			 , Z.PNT_GIVE_STAT    
			 , Z.BEST_YN          
			 , Z.GIVE_DUE_BPNT    
			 , Z.BPNT_GIVE_YN     
			 , Z.DISP_YN          
			 , Z.DEL_YN           
			 , Z.CONFIRM_YN       
			 , Z.CONFIRM_UNO      
			 , Z.CONFIRM_DT       
			 , Z.REG_NO           
			 , Z.UPD_NO           
			 , Z.UPD_DT           
			 , Z.ADM_RPL          
			 , Z.ADM_RPL_REG_NO   
			 , Z.ADM_RPL_DT 
			 </if>
		  FROM (SELECT O.ORD_NO
					 , DATE_FORMAT(O.ORD_DT, '%Y.%m.%d') AS ORD_DT
					 , DATE_FORMAT(O.PAY_DT, '%Y.%m.%d') AS PAY_DT 
					 , DATE_FORMAT(OD.DELV_EDDT, '%m/%d')								AS DELV_EDDT
					 , OD.ORD_DTL_NO
					 , OD.ORD_EXCH_GB
					 , OD.GOODS_CD
		             , OD.GIFT_ADDR_INP_YN
					 , G.GOODS_NM
					 , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), ODI.OPT_CD1) AS COLOR_NM
					 , (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = ODI.ITEM_CD)		AS ITEM_NM
		             , ODI.ORD_DTL_ITEM_SQ
					 , ODI.OPT_CD1
					 , ODI.OPT_CD2
					 , IFNULL(C.COLOR_KNM, ODI.OPT_CD1) AS OPT_CD1_NM
					 , G.LIST_PRICE
		             , G.GOODS_TYPE
		             , CASE WHEN G.GOODS_TYPE != 'G056_S' AND G.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
		                    ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
					 , OD.ORD_DTL_STAT
					 , OD.ORD_QTY
					 , OD.CNCL_RTN_QTY
					 , OD.ORD_AMT
					 , OD.REAL_ORD_AMT
					 , OD.SAVE_PNT_AMT
					 , OD.CNCL_RTN_AMT
					 , OD.PNT_DC_AMT
					 , OD.GFCD_USE_AMT
					 , OD.INVOICE_NO
					 , OD.SUPPLY_COMP_CD
					 , OD.DELV_FEE_CD
					 , OD.SHOT_DELV_YN
					 , OD.CHANGEABLE_YN
					 , OD.RETURNABLE_YN
					 , DATE_FORMAT(OD.REG_DT, '%Y.%m.%d') AS REG_DT 
					 , G.SELF_GOODS_YN
				     , CASE BG.DISP_NM_LANG WHEN 'EN' THEN BG.BRAND_GROUP_ENM
		                       ELSE BG.BRAND_GROUP_KNM
		               END                                                             AS BRAND_NM
		             , B.BRAND_ENM
				     , FN_GET_CODE_NM('G013', OD.ORD_DTL_STAT)							AS ORD_DTL_STAT_NM
					 -- , IFNULL(OCD.ORD_REQ_CHG_QTY, 0) AS ORD_REQ_CHG_QTY
					 -- , OD.ORD_QTY - OD.CNCL_RTN_QTY - IFNULL(OCD.ORD_REQ_CHG_QTY, 0)    AS ORD_CAN_CHG_QTY
					 , IFNULL((SELECT SUM(OCD.CHG_QTY) FROM TB_ORDER_CHANGE_DETAIL OCD WHERE OCD.ORD_DTL_NO = OD.ORD_DTL_NO GROUP BY OCD.ORD_DTL_NO), 0) AS ORD_REQ_CHG_QTY
		             , OD.ORD_QTY - OD.CNCL_RTN_QTY - IFNULL((SELECT SUM(OCD.CHG_QTY) FROM TB_ORDER_CHANGE_DETAIL OCD WHERE OCD.ORD_DTL_NO = OD.ORD_DTL_NO GROUP BY OCD.ORD_DTL_NO), 0) AS ORD_CAN_CHG_QTY
					 , I.ITEMKIND_NM 
		   	         , I.ITEMKIND_CD 
			         , I.SIZE_GB 
			         ,DATEDIFF( DATE_ADD(O.PAY_DT,INTERVAL #{reviewExpireDay} DAY),NOW()) AS REMAIN_DT -- 남은시간
			          <if test="reviewStat == 'u'.toString()">
			         , R.REVIEW_SQ        
			         , R.REVIEW_GB        
				     , 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.BEST_YN          
				     , R.GIVE_DUE_BPNT    
				     , R.BPNT_GIVE_YN     
				     , R.DISP_YN          
				     , R.DEL_YN           
				     , R.CONFIRM_YN       
				     , R.CONFIRM_UNO      
				     , R.CONFIRM_DT       
				     , R.REG_NO           
				     , R.UPD_NO           
				     , R.UPD_DT           
				     , R.ADM_RPL          
				     , R.ADM_RPL_REG_NO   
				     , R.ADM_RPL_DT   
				     </if>
				  FROM TB_ORDER O
				 INNER JOIN TB_ORDER_DETAIL OD
				    ON O.ORD_NO = OD.ORD_NO
				   AND OD.ORD_DTL_STAT = 'G013_70' -- 구매 확정인것만
				 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 G
				    ON OD.GOODS_CD = G.GOODS_CD
				 INNER JOIN TB_BRAND B
				    ON B.BRAND_CD = G.BRAND_CD
				 INNER JOIN TB_BRAND_GROUP BG
		            ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
				 LEFT JOIN TB_ITEMKIND I 
				    ON G.ITEMKIND_CD = I.ITEMKIND_CD 
				 <if test="reviewStat == 'u'.toString()">
				 INNER JOIN TB_REVIEW R ON R.ORD_NO = O.ORD_NO 
			                                      AND R.ORD_NO = O.ORD_NO
			                                      AND R.ORD_DTL_NO = OD.ORD_DTL_NO
			                                      AND R.DEL_YN = 'N'
			      </if>
				LEFT OUTER JOIN TB_COLOR C ON ODI.OPT_CD1 = C.COLOR_CD
		                                   AND C.USE_YN = 'Y'
				  WHERE 1=1
				AND O.PAY_DT >  DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL -#{reviewExpireDay} DAY), '%Y%M%D%H%I%S') 
			<!-- 	<choose>
					<when test="reviewStat == 'u'.toString()">
					AND O.ORD_NO IN (SELECT R.ORD_NO FROM TB_REVIEW R WHERE R.DEL_YN = 'N' AND DISP_YN = 'Y')
					</when>
					<otherwise>
					AND O.ORD_NO NOT IN (SELECT R.ORD_NO FROM TB_REVIEW R WHERE R.DEL_YN = 'N' AND DISP_YN = 'Y')
					</otherwise>
				</choose> -->
				AND O.CUST_NO = #{custNo}
				AND O.DISP_YN = 'Y'
			 	AND O.SITE_CD = 'G000_10'
     			<if test="ordNo!=null and ordNo != ''">
				AND O.ORD_NO = #{ordNo}
				</if>
				<if test="ordDtlNo!=null and ordDtlNo != ''">
				AND OD.ORD_DTL_NO = #{ordDtlNo}
				</if>
				<if test="goodsCd!=null and goodsCd!=''">
				AND G.GOODS_CD = #{goodsCd}
				</if>
				) Z
		 GROUP BY Z.ORD_NO, Z.ORD_DT, Z.DELV_EDDT, Z.ORD_DTL_NO, Z.ORD_EXCH_GB
				, Z.GOODS_CD, Z.GOODS_NM, Z.LIST_PRICE, Z.SYS_IMG_NM, Z.ORD_DTL_STAT, Z.ORD_QTY, Z.CNCL_RTN_QTY, Z.ORD_AMT, Z.REAL_ORD_AMT
				, Z.SAVE_PNT_AMT, Z.CNCL_RTN_AMT, Z.PNT_DC_AMT, Z.GFCD_USE_AMT, Z.INVOICE_NO
				, Z.SUPPLY_COMP_CD, Z.DELV_FEE_CD, Z.SHOT_DELV_YN, Z.CHANGEABLE_YN, Z.SELF_GOODS_YN, Z.BRAND_NM, Z.BRAND_ENM,Z.ORD_DTL_STAT_NM
				, Z.ORD_REQ_CHG_QTY, Z.ORD_CAN_CHG_QTY
				 <if test="reviewStat == 'u'.toString()">
				, Z.REVIEW_SQ , Z.REVIEW_SQ , Z.REVIEW_GB , Z.REVIEW_TITLE , Z.REVIEW_CONTENT , Z.SCORE , Z.HEIGHT           
				, Z.WEIGHT  , Z.SCORE_SIZE , Z.SCORE_COLOR , Z.SCORE_FIT , Z.SCORE_THICK , Z.SCORE_WEIGHT , Z.SCORE_BALL , Z.GIVE_DUE_PNT , Z.PNT_GIVE_STAT    
			    , Z.BEST_YN, Z.GIVE_DUE_BPNT , Z.BPNT_GIVE_YN , Z.DISP_YN , Z.DEL_YN, Z.CONFIRM_YN, Z.CONFIRM_UNO, Z.CONFIRM_DT, Z.REG_NO
			    , Z.UPD_NO, Z.UPD_DT, Z.ADM_RPL, Z.ADM_RPL_REG_NO, Z.ADM_RPL_DT  
			    </if>          
		ORDER BY Z.ORD_NO DESC, Z.ORD_DTL_STAT, Z.SELF_GOODS_YN DESC, Z.SHOT_DELV_YN DESC, Z.SUPPLY_COMP_CD
	</select>
	
	<!-- 마이페이지 작성가능한 리뷰 -->
	<select id="getCompleteReviewCount" parameterType="Review" resultType="int">
		/* TsfReivew.getCompleteReviewCount */
		SELECT  COUNT(*) AS CNT 
		FROM
		(
			SELECT O.ORD_NO 
			      ,O.ORD_NM 
			      ,O.PAY_DT 
			      ,O.CUST_NO 
			      ,OD.ORD_DTL_NO
			      ,OD.ORD_EXCH_GB 
			      ,OD.GOODS_CD 
			      ,OD.REAL_ORD_AMT 
			      ,OD.SAVE_PNT_AMT 
			      ,OD.GFCD_USE_AMT 
			      ,ODI.ORD_DTL_ITEM_SQ 
			      ,ODI.ITEM_CD 
			      ,ODI.OPT_CD 
			      ,ODI.OPT_CD1
			      ,ODI.OPT_CD2
			      ,ODI.ITEM_QTY 
			      ,ODI.ITEM_PRICE
			      ,G.GOODS_NM 
			      ,G.MAIN_COLOR_CD
			      ,B.BRAND_ENM 
			      ,DATEDIFF( DATE_ADD(O.PAY_DT,INTERVAL #{reviewExpireDay} DAY),NOW()) AS DT -- 남은시간
			FROM TB_ORDER O INNER JOIN TB_ORDER_DETAIL OD ON O.ORD_NO = OD.ORD_NO 
								 				 AND OD.ORD_DTL_STAT = 'G013_70' --  구매 확정인것만
			                INNER JOIN TB_ORDER_DETAIL_ITEM ODI ON OD.ORD_DTL_NO = ODI.ORD_DTL_NO 
			                  					 AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
			                INNER JOIN TB_GOODS G ON G.GOODS_CD = OD.GOODS_CD 
			                INNER JOIN TB_BRAND B ON B.BRAND_CD = G.BRAND_CD
			WHERE 1=1
				AND O.PAY_DT <![CDATA[>]]>  DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL  -#{reviewExpireDay} DAY), '%Y%M%D%H%I%S') <!--  60 일이내것만 -->
				AND O.ORD_NO NOT IN (SELECT R.ORD_NO FROM TB_REVIEW R WHERE R.DEL_YN = 'N' AND DISP_YN = 'Y')
				AND O.CUST_NO = #{custNo}
				AND O.DISP_YN = 'Y'
				AND O.SITE_CD = #{siteCd}
			GROUP BY O.ORD_NO 
			      ,O.ORD_NM 
			      ,O.PAY_DT 
			      ,O.CUST_NO 
			      ,OD.ORD_DTL_NO
			      ,OD.ORD_EXCH_GB 
			      ,OD.GOODS_CD 
			      ,OD.REAL_ORD_AMT 
			      ,OD.SAVE_PNT_AMT 
			      ,OD.GFCD_USE_AMT 
			      ,ODI.ORD_DTL_ITEM_SQ 
			      ,ODI.ITEM_CD 
			      ,ODI.OPT_CD 
			      ,ODI.OPT_CD1
			      ,ODI.OPT_CD2
			      ,ODI.ITEM_QTY 
			      ,ODI.ITEM_PRICE
			      ,G.GOODS_NM 
			      ,G.MAIN_COLOR_CD
			      ,B.BRAND_ENM 
		)F
		ORDER BY F.PAY_DT DESC
	</select>
	
	<insert id="saveMypageReview" parameterType="Review" useGeneratedKeys="true" keyProperty="reviewSq">
		/* TsfReivew.saveMypageReview */
		INSERT INTO TB_REVIEW (
		                         REVIEW_SQ
		                       , GOODS_CD
		                       , REVIEW_GB
		                       , CUST_NO
		                       , ORD_NO
		                       , ORD_DTL_NO
		                       , REVIEW_TITLE
		                       , REVIEW_CONTENT
		                       , SCORE
		                       , HEIGHT
		                       , WEIGHT
		                       , SCORE_SIZE
		                       , SCORE_COLOR
		                       , SCORE_FIT
		                       , SCORE_THICK
		                       , SCORE_WEIGHT
		                       , SCORE_BALL
		                       , PNT_GIVE_STAT
		                       , DISP_YN
		                       , DEL_YN
		                       , REG_NO
		                       , REG_DT
		                       , UPD_NO
		                       , UPD_DT
		                      )
		               VALUES (
		                          #{reviewSq}
		                        , #{goodsCd}
		                        , #{reviewGb}
		                        , #{custNo}
		                        , #{ordNo}
		                        , #{ordDtlNo}
		                        , #{reviewTitle}
		                        , #{reviewContent}
		                        , #{score}
		                        , #{height}
		                        , #{weight}
		                        , #{scoreSize}
		                        , #{scoreColor}
		                        , #{scoreFit}
		                        , #{scoreThick}
		                        , #{scoreWeight}
		                        , #{scoreBall}
		                        , #{pntGiveStat}
		                        , #{dispYn}
		                        , #{delYn}
		                        , #{regNo}
		                        , now()
		                        , #{updNo}
		                        , now()
		                      )
	</insert>
	
	<update id="updateMypageReview" parameterType="Review">
		/* TsfReivew.updateMypageReview */
		UPDATE TB_REVIEW
		SET REVIEW_CONTENT =  #{reviewContent} 
		   ,SCORE          =  #{score}           
		   ,HEIGHT         =  #{height}          
		   ,WEIGHT         =  #{weight}          
		   ,SCORE_SIZE     =  #{scoreSize}       
		   ,SCORE_COLOR    =  #{scoreColor}      
		   ,SCORE_FIT      =  #{scoreFit}        
		   ,SCORE_THICK    =  #{scoreThick}      
		   ,SCORE_WEIGHT   =  #{scoreWeight}     
		   ,SCORE_BALL     =  #{scoreBall}  
		   , DEL_YN        = 'N'     
		   ,UPD_DT         =  now()  
		   ,CONFIRM_YN     = 'Y'
		   ,CONFIRM_DT     = NOW()   
		WHERE REVIEW_SQ  = #{reviewSq}
		AND   ORD_NO     = #{ordNo}
		AND   ORD_DTL_NO = #{ordDtlNo}
		AND   CUST_NO    = #{custNo}
		AND   GOODS_CD   = #{goodsCd}
	</update>
	
	<update id="updateMypageReviewConfimYn" parameterType="Review">
		/* TsfReivew.updateMypageReviewConfimYn */
		UPDATE TB_REVIEW
		<if test='confirmYn == "Y"'>
		SET CONFIRM_DT = NOW()
		   ,CONFIRM_YN = 'Y'
		   ,REVIEW_GB = #{reviewGb}
		   </if>
		   <if test='confirmYn == "N"'>
		 SET CONFIRM_DT = NULL
		    ,CONFIRM_YN = 'N'
		    ,REVIEW_GB = #{reviewGb}
		   </if>
		WHERE REVIEW_SQ  = #{reviewSq}
	</update>
	
	<select id="getAlreadyReviewCount" parameterType="Review" resultType="int">
		/* TsfReivew.getAlreadyReviewCount */
		SELECT  COUNT(*) AS CNT 
		FROM
		(
			SELECT O.ORD_NO 
			      ,O.ORD_NM 
			      ,O.PAY_DT 
			      ,O.CUST_NO 
			      ,OD.ORD_DTL_NO
			      ,OD.ORD_EXCH_GB 
			      ,OD.GOODS_CD 
			      ,OD.REAL_ORD_AMT 
			      ,OD.SAVE_PNT_AMT 
			      ,OD.GFCD_USE_AMT 
			      ,ODI.ORD_DTL_ITEM_SQ 
			      ,ODI.ITEM_CD 
			      ,ODI.OPT_CD 
			      ,ODI.OPT_CD1
			      ,ODI.OPT_CD2
			      ,ODI.ITEM_QTY 
			      ,ODI.ITEM_PRICE
			      ,G.GOODS_NM 
			      ,G.MAIN_COLOR_CD
			      ,B.BRAND_ENM 
			      ,DATEDIFF( DATE_ADD(O.PAY_DT,INTERVAL 90 DAY),NOW()) AS DT -- 남은시간
			FROM TB_ORDER O INNER JOIN TB_ORDER_DETAIL OD ON O.ORD_NO = OD.ORD_NO 
								 				 AND OD.ORD_DTL_STAT = 'G013_70' --  구매 확정인것만
			                INNER JOIN TB_ORDER_DETAIL_ITEM ODI ON OD.ORD_DTL_NO = ODI.ORD_DTL_NO 
			                  					 AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
			                INNER JOIN TB_GOODS G ON G.GOODS_CD = OD.GOODS_CD 
			                INNER JOIN TB_BRAND B ON B.BRAND_CD = G.BRAND_CD
			                INNER JOIN TB_REVIEW R ON R.ORD_NO = O.ORD_NO 
			                                      AND R.ORD_NO = O.ORD_NO
			                                      AND R.ORD_DTL_NO = OD.ORD_DTL_NO
			           		                      AND R.DEL_YN = 'N'
			WHERE 1=1
				AND O.PAY_DT >  DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY), '%Y%M%D%H%I%S')
				AND O.CUST_NO = #{custNo}
				AND O.DISP_YN = 'Y'
				AND R.CONFIRM_YN = 'Y'
 				AND O.SITE_CD = #{siteCd}
			GROUP BY O.ORD_NO 
			      ,O.ORD_NM 
			      ,O.PAY_DT 
			      ,O.CUST_NO 
			      ,OD.ORD_DTL_NO
			      ,OD.ORD_EXCH_GB 
			      ,OD.GOODS_CD 
			      ,OD.REAL_ORD_AMT 
			      ,OD.SAVE_PNT_AMT 
			      ,OD.GFCD_USE_AMT 
			      ,ODI.ORD_DTL_ITEM_SQ 
			      ,ODI.ITEM_CD 
			      ,ODI.OPT_CD 
			      ,ODI.OPT_CD1
			      ,ODI.OPT_CD2
			      ,ODI.ITEM_QTY 
			      ,ODI.ITEM_PRICE
			      ,G.GOODS_NM 
			      ,G.MAIN_COLOR_CD
			      ,B.BRAND_ENM 
		)F
	</select>
	
	<select id="getAlreadyReviewList" parameterType="Review" resultType="Review">
		/* TsfReivew.getAlreadyReviewList */
		<include refid="selectForPagingHeader"/>
		SELECT GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
		     , GROUP_CONCAT(' ',CONCAT(OPT_CD1_NM,' / ', OPT_CD2) ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		     , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1
		     , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD2
		     , GROUP_CONCAT(Z.OPT_CD1_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1_NM
		     , Z.ORD_NO
		     , Z.ORD_DT
		     , Z.PAY_DT
		     , Z.ORD_DTL_NO
		     , Z.GOODS_CD
		     , Z.GOODS_NM
		     , Z.LIST_PRICE
		     , Z.GOODS_TYPE
		     , Z.SYS_IMG_NM
		     , Z.BRAND_NM
		     , Z.BRAND_ENM
		     , Z.REMAIN_DT
		     , Z.REVIEW_SQ
		     , Z.REVIEW_GB
		     , Z.REVIEW_CONTENT
		     , Z.SCORE
		     , Z.HEIGHT
		     , Z.WEIGHT
		     , Z.SCORE_SIZE
		     , Z.SCORE_COLOR
		     , Z.SCORE_FIT
		     , Z.SCORE_THICK
		     , Z.SCORE_WEIGHT
		     , Z.SCORE_BALL
		     , Z.BEST_YN
		     , Z.DISP_YN
		     , Z.DEL_YN
		     , Z.CONFIRM_YN
		     , Z.CONFIRM_UNO
		     , Z.CONFIRM_DT
		     , Z.REG_NO
		     , Z.REG_DT
		     , Z.UPD_NO
		     , Z.UPD_DT
		     , Z.ADM_RPL
		     , Z.ADM_RPL_REG_NO
		     , Z.ADM_RPL_DT
		     , Z.SIZE_GB
		     , ROW_NUMBER() OVER(ORDER BY Z.ORD_DT DESC) AS RNUM
		       FROM (    
		             SELECT O.ORD_NO
		                  , DATE_FORMAT(O.ORD_DT, '%Y.%m.%d') AS ORD_DT
		                  , DATE_FORMAT(O.PAY_DT, '%Y.%m.%d') AS PAY_DT
		                  , DATE_FORMAT(OD.DELV_EDDT, '%m/%d')                                                          AS DELV_EDDT
		                  , OD.ORD_DTL_NO
		                  , OD.GOODS_CD
		                  , G.GOODS_NM
		                  , (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = ODI.ITEM_CD)                AS ITEM_NM
		                  , ODI.ORD_DTL_ITEM_SQ
		                  , ODI.OPT_CD1
		                  , ODI.OPT_CD2
		                  , IFNULL(C.COLOR_KNM, ODI.OPT_CD1) AS OPT_CD1_NM
		                  , G.LIST_PRICE
		                  , G.GOODS_TYPE
		                  , CASE WHEN G.GOODS_TYPE != 'G056_S' AND G.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
		                         ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
		                  , CASE BG.DISP_NM_LANG WHEN 'EN' THEN BG.BRAND_GROUP_ENM
		                       ELSE BG.BRAND_GROUP_KNM
		                        END                                                             AS BRAND_NM
		                  , B.BRAND_ENM
		                  , R.REVIEW_SQ
		                  , R.REVIEW_GB
		                  , R.REVIEW_CONTENT
		                  , R.SCORE
		                  , R.HEIGHT
		                  , R.WEIGHT
		                  , (CASE R.SCORE_SIZE   WHEN 1 THEN '작음'
		                                          WHEN 2 THEN '딱맞음'
		                                         WHEN 3 THEN '큼'
		                                                            END) AS SCORE_SIZE
		                  , (CASE R.SCORE_COLOR  WHEN 1 THEN '밝음'
		                                         WHEN 2 THEN '똑같음'
		                                         WHEN 3 THEN '어두움'
		                                                            END) AS SCORE_COLOR
		                  , (CASE R.SCORE_FIT    WHEN 1 THEN '슬림'
		                                         WHEN 2 THEN '레귤러'
		                                         WHEN 3 THEN '오버'
		                                                            END) AS SCORE_FIT
		                  , (CASE R.SCORE_THICK  WHEN 1 THEN '얇음'
		                                         WHEN 2 THEN '적당함'
		                                         WHEN 3 THEN '도톰함'
		                                                            END) AS SCORE_THICK
		                   , (CASE R.SCORE_WEIGHT WHEN 1 THEN '가벼움'
		                                         WHEN 2 THEN '적당함'
		                                         WHEN 3 THEN '무거움'
		                                                            END) AS SCORE_WEIGHT
		                   , (CASE R.SCORE_BALL   WHEN 1 THEN '작음'
		                                          WHEN 2 THEN '딱맞음'
		                                          WHEN 3 THEN '큼'
		                                                            END) AS SCORE_BALL
		                   , R.BEST_YN
		                   , R.DISP_YN
		                   , R.DEL_YN
		                   , R.CONFIRM_YN
		                   , R.CONFIRM_UNO
		                   , R.CONFIRM_DT
		                   , R.REG_NO
		                   , DATE_FORMAT(R.REG_DT , '%Y.%m.%d') AS REG_DT
		                   , R.UPD_NO
		                   , DATE_FORMAT(R.UPD_DT , '%Y.%m.%d') AS UPD_DT
		                   , R.ADM_RPL
		                   , R.ADM_RPL_REG_NO
		                   , I.SIZE_GB
		                   , DATE_FORMAT(R.ADM_RPL_DT , '%Y.%m.%d') AS ADM_RPL_DT
		                   , DATEDIFF( DATE_ADD(O.PAY_DT,INTERVAL   365 /**P*/ DAY),NOW()) AS REMAIN_DT -- 남은시간
		    FROM TB_ORDER O
		   INNER JOIN TB_ORDER_DETAIL OD
		      ON O.ORD_NO = OD.ORD_NO
		      AND OD.ORD_DTL_STAT = 'G013_70' -- 구매 확정인것만
		   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 G
		      ON OD.GOODS_CD = G.GOODS_CD
		   INNER JOIN TB_BRAND B
		      ON B.BRAND_CD = G.BRAND_CD
		    INNER JOIN TB_BRAND_GROUP BG
		      ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		   INNER JOIN TB_REVIEW R 
		      ON R.ORD_NO = O.ORD_NO 
		      AND R.ORD_DTL_NO = OD.ORD_DTL_NO 
		   LEFT JOIN TB_ITEMKIND I 
		      ON G.ITEMKIND_CD = I.ITEMKIND_CD 
		  LEFT OUTER JOIN TB_COLOR C ON ODI.OPT_CD1 = C.COLOR_CD
		                               AND C.USE_YN = 'Y'
		     WHERE 1=1
		   AND O.ORD_DT >  DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL  #{reviewExpireDay} DAY), '%Y%M%D%H%I%S') 
		   AND O.CUST_NO =  #{custNo}
		   AND R.CONFIRM_YN = 'Y'
		   AND R.ORD_NO = O.ORD_NO
		   AND R.DEL_YN = 'N'
		   AND R.DISP_YN = 'Y'
		   AND OD.ORD_DTL_NO IN (SELECT R.ORD_DTL_NO FROM TB_REVIEW R WHERE R.DEL_YN = 'N' AND DISP_YN = 'Y' AND R.ORD_NO = O.ORD_NO AND R.ORD_DTL_NO = OD.ORD_DTL_NO AND R.CUST_NO = #{custNo})
		   AND O.SITE_CD =  #{siteCd}
		   ) Z
		 GROUP BY     Z.ORD_NO
		            , Z.ORD_DT
		            , Z.PAY_DT
		            , Z.ORD_DTL_NO
		            , Z.GOODS_CD
		            , Z.GOODS_NM
		            , Z.LIST_PRICE
		            , Z.GOODS_TYPE
		            , Z.SYS_IMG_NM
		            , Z.BRAND_NM
		            , Z.BRAND_ENM
		            , Z.REMAIN_DT
		            , Z.REVIEW_SQ
		            , Z.REVIEW_GB
		            , Z.REVIEW_CONTENT
		            , Z.SCORE
		            , Z.HEIGHT
		            , Z.WEIGHT
		            , Z.SCORE_SIZE
		            , Z.SCORE_COLOR
		            , Z.SCORE_FIT
		            , Z.SCORE_THICK
		            , Z.SCORE_WEIGHT
		            , Z.SCORE_BALL
		            , Z.BEST_YN
		            , Z.DISP_YN
		            , Z.DEL_YN
		            , Z.CONFIRM_YN
		            , Z.CONFIRM_UNO
		            , Z.CONFIRM_DT
		            , Z.REG_NO
		            , Z.REG_DT
		            , Z.UPD_NO
		            , Z.UPD_DT
		            , Z.ADM_RPL
		            , Z.ADM_RPL_REG_NO
		            , Z.ADM_RPL_DT
		            , Z.SIZE_GB
		ORDER BY Z.ORD_NO DESC
		<include refid="selectForPagingFooter"/>
		</select>
	
	<select id="getReviewAttach" parameterType="Review" resultType="Review">
		/* TsfReivew.getReviewAttach */
		SELECT R.REVIEW_SQ
			 , R.GOODS_CD
			 , R.REVIEW_GB
			 , 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.BEST_YN
			 , R.GIVE_DUE_BPNT
			 , R.BPNT_GIVE_YN
			 , R.DISP_YN
			 , R.DEL_YN
			 , R.CONFIRM_YN
			 , R.CONFIRM_UNO
			 , R.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
			 , RA.RV_ATC_SQ
			 , RA.REVIEW_SQ
			 , RA.FILE_GB
			 , RA.ORG_FILE_NM
			 , RA.SYS_FILE_NM
			 , RA.KMC_KEY
			 , RA.KUF_KEY
			 , RA.FILE_GB
			 , RA.DEL_YN
			 , RANK() OVER(ORDER BY RA.FILE_GB DESC) AS NUMB
		FROM TB_REVIEW R LEFT JOIN TB_REVIEW_ATTACH RA ON R.REVIEW_SQ = RA.REVIEW_SQ 
		WHERE 1=1
		 AND R.REVIEW_SQ = RA.REVIEW_SQ 
		 AND R.CUST_NO = #{custNo}
		 AND R.DEL_YN = 'N'
		 AND R.DISP_YN = 'Y'
		 AND RA.DEL_YN = 'N'
		 AND (RA.FILE_GB  = 'I'
		      OR
		      RA.FILE_GB = 'M' AND R.CONFIRM_YN = 'Y'  AND  NULLIF(RA.KMC_KEY,'') IS NOT NULL
		     )
	</select>
	
	<select id="getAdminConfirmCount" parameterType="Review" resultType="Review">
		/* TsfReivew.getAdminConfirmCount */
		SELECT COUNT(*) AS COUNT
		     , R.REVIEW_SQ
		     , MAX(ADM_RPL_DT) AS ADM_RPL_DT
		FROM TB_REVIEW R
		WHERE 1=1
		 AND CUST_NO = #{custNo}
		 AND DEL_YN = 'N'
		 AND DISP_YN = 'Y'
		 AND RPL_CFM_YN ='N'
		 AND ADM_RPL IS NOT NULL
		GROUP BY R.REVIEW_SQ, ADM_RPL_DT
		ORDER BY  R.ADM_RPL_DT DESC, R.REVIEW_SQ LIMIT 1
	</select>
	
	<update id="reviewDelete" parameterType="Review">
		/* TsfReivew.reviewDelete */
		UPDATE TB_REVIEW R
		SET R.DEL_YN = 'Y'
		WHERE 1=1
 		AND R.REVIEW_SQ = #{reviewSq}
 		AND R.CUST_NO = #{custNo}
	</update>
	
	<update id="reviewAttachDelete" parameterType="Review">
	   /* TsfReivew.reviewAttachDelete */
		UPDATE TB_REVIEW_ATTACH RA
		SET RA.DEL_YN = 'Y'
		WHERE 1=1
 		AND RA.REVIEW_SQ = #{reviewSq}
	</update>
	
	<select id="reviewDeleteConfirm" parameterType="Review" resultType="Review">
		/* TsfReivew.reviewDeleteConfirm */
		SELECT IFNULL(R.REVIEW_SQ,0) AS REVIEW_SQ, COUNT(*) AS COUNT
		FROM TB_REVIEW R
		WHERE 1=1
		  AND R.ORD_NO = #{ordNo}
		  AND R.ORD_DTL_NO = #{ordDtlNo}
		  AND R.DEL_YN ='Y'
		  AND R.GOODS_CD = #{goodsCd}
		  AND R.CUST_NO = #{custNo}
	</select>
	
	<update id="reviewCustomerConfirm" parameterType="Review">
		/* TsfReivew.reviewCustomerConfirm */	
		UPDATE TB_REVIEW
		SET RPL_CFM_YN = 'Y'
		   ,RPL_CFM_DT = NOW()
		WHERE CUST_NO = #{custNo}
	</update>
	
	<insert id="saveReviewAttach" parameterType="ReviewAttach">
		/* TsfReivew.saveReviewAttach */	
		INSERT INTO TB_REVIEW_ATTACH
		(
		  REVIEW_SQ
		, FILE_GB
		, ORG_FILE_NM
		, SYS_FILE_NM
		, KMC_KEY
		, KUF_KEY
		, DEL_YN
		, REG_NO
		, REG_DT
		, UPD_NO
		, UPD_DT
		)
		VALUES(
		  #{reviewSq}
		, #{fileGb}
		, #{orgFileNm}
		, #{sysFileNm}
		, #{kmcKey}
		, #{kufKey}
		, #{delYn}
		, #{regNo}
		, CURRENT_TIMESTAMP
		, #{updNo}
		, CURRENT_TIMESTAMP
		)
	</insert>
	
	<!-- 2021.08.11 포인트 지급 이번달 1일부터 31일 사이의 포인트지급 카운트 -->
	<select id="getReviewPointGiveMonthCount" parameterType="Review" resultType="int">
		/* TsfReivew.getReviewPointGiveMonthCount */	
		SELECT COUNT(*) AS COUNT
		FROM   TB_REVIEW RV
		WHERE  1=1 
		AND    RV.PNT_GIVE_STAT = 'G043_30'
		AND    RV.CUST_NO = #{custNo}
		AND    RV.REG_DT BETWEEN LAST_DAY(NOW() - INTERVAL -1 MONTH) AND LAST_DAY(NOW())	
	</select>
		
	<!-- 2021.08.11 포인트 지급은 한개의 상품만 가능 -->
	<select id="getReviewPointGiveCount" parameterType="Review" resultType="int">
		/* TsfReivew.getReviewPointGiveCount */	
		SELECT COUNT(*) AS COUNT
		FROM   TB_REVIEW RV
		WHERE  1=1 
		AND    RV.PNT_GIVE_STAT = 'G043_30'
		AND    RV.CUST_NO = #{custNo}
		AND    EXISTS (
		    SELECT 1
		    FROM   TB_ORDER_DETAIL OD
		    WHERE  1=1
		    AND    OD.ORD_NO = #{ordNo}
		    AND    OD.ORD_DTL_NO = #{ordDtlNo}
		    AND    OD.GOODS_CD = RV.GOODS_CD
		)
		AND    RV.REVIEW_SQ    != #{reviewSq}
	</select>
	
	<insert id="createReviewPoint" parameterType="Point" useGeneratedKeys="true" keyProperty="custPntSq">
		/* TsfReivew.createReviewPoint */
		INSERT INTO TB_CUST_POINT (
		       CUST_PNT_SQ
		     , CUST_NO
		     , GV_PNT_AMT
		     , US_PNT_AMT
		     , RM_PNT_AMT
		     , EXP_BE_DT
		     , EXP_CMP_DT
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		) VALUES (
		       #{custPntSq}
		     , #{custNo}
		     , #{gvPntAmt}
		     , 0
		     , #{rmPntAmt}
		     , DATE_FORMAT(CONCAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 YEAR), ' 23:59:59'), '%Y-%m-%d %H:%i:%S')
		     , #{expCmpDt}
		     , #{custNo}
		     , NOW()
		     , #{custNo}
		     , NOW()	
		     )
	</insert>
	
	<insert id="createReviewPointHst" parameterType="Point">
		/* TsfReivew.createReviewPoint */
		INSERT INTO TB_CUST_POINT_HST (
		       CUST_NO
		     , OCCUR_GB
		     , OCCUR_DTL_DESC
		     , PNT_AMT
		     , CUST_PNT_SQ
		     , ORD_NO
		     , ORD_DTL_NO
		     , REVIEW_SQ
		     , SWITCH_DUE_DT
		     , PNT_UPLOAD_STAT
		     , PNT_UPLOAD_DT
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		) VALUES (
		       #{custNo}
		     , #{occurGb}
		     , #{occurDtlDesc}
		     , #{pntAmt}
		     , #{custPntSq}
		     , #{ordNo}
		     , #{ordDtlNo}
		     , #{reviewSq}
		     <if test='pntUploadStat == "G070_30"'>
		     , NOW()                                -- SWITCH_DUE_DT
		     </if>
		     <if test='pntUploadStat != "G070_30"'>
		     , DATE_FORMAT(#{switchDueDt}, '%Y%m%d%H%i%S') -- SWITCH_DUE_DT
		     </if>
		     , #{pntUploadStat}
		     <if test='pntUploadStat == "G070_30"'>
		     , NOW()                                -- PNT_UPLOAD_DT
		     </if>
		     <if test='pntUploadStat != "G070_30"'>
		     , NULL                                 -- PNT_UPLOAD_DT
		     </if>
		     , #{custNo}
		     , NOW()                                -- REG_DT
		     , #{custNo}
		     , NOW()                                -- UPD_DT
		)
	</insert>
	
</mapper>