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

	<!--상품평 총건수  -->
	<select id="getReviewTotalCount" parameterType="Review" resultType="int">
		/* TsaReview.getReviewTotalCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_REVIEW R
		     , TB_CUSTOMER C
		     , TB_GOODS G
		WHERE  R.CUST_NO = C.CUST_NO
		AND    R.GOODS_CD = G.GOODS_CD
		AND    R.DEL_YN = 'N'
		<if test="goodsCds != null and goodsCds != ''">
		AND    (
		        <foreach collection="goodsCds" item="item" index="index" separator="or">
		        R.GOODS_CD LIKE CONCAT(UPPER(#{item}),'%') 
		        </foreach>
		       )
		</if>
		<if test="startDt != null and startDt != ''">
		AND    R.REG_DT <![CDATA[>=]]> STR_TO_DATE(#{startDt},'%Y-%m-%d')
		</if>
		<if test="endDt != null and endDt != ''">
		AND    R.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{endDt},'%Y-%m-%d'),INTERVAL 1 DAY)
		</if>
		<if test="reviewGb != null and reviewGb != ''">
		AND    R.REVIEW_GB = #{reviewGb}
		</if>
		<if test="pntGiveStat != null and pntGiveStat != ''">
		AND    R.PNT_GIVE_STAT = #{pntGiveStat}
		</if>
		<if test="dispYn != null and dispYn != ''">
		AND    R.DISP_YN = #{dispYn}
		</if>
		<if test="searchTxt != null and searchTxt != ''">
		    <if test="searchGb == 'memNm'">
		AND    UPPER(C.CUST_NM) LIKE CONCAT(UPPER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'email'">
		AND    UPPER(C.EMAIL) LIKE CONCAT(UPPER(#{searchTxt}),'%')
		    </if>
		</if>
	</select>
	
	<!--상품평 목록  -->
	<select id="getReviewList" parameterType="Review" resultType="Review">
		/* TsaReview.getReviewList */
		SELECT R.REVIEW_SQ                                                /*상품평일련번호*/
		     , R.GOODS_CD                                                 /*상품코드*/
		     , G.GOODS_NM                                                 /*상품명*/
		     , (SELECT SYS_IMG_NM
		        FROM   TB_GOODS_IMG
		        WHERE  GOODS_CD = G.GOODS_CD
		        AND    COLOR_CD = G.MAIN_COLOR_CD
		        AND    DEFAULT_IMG_YN = 'Y' /*디폴트이미지*/
		        LIMIT 1
		       )                                        AS GOODS_IMG      /*상품이미지*/
		     , CASE WHEN R.REVIEW_GB = 'P' THEN
		                (SELECT CASE WHEN MOVIE_CNT > 0 AND IMG_CNT = 0 THEN 'M' /*영상*/
		                             WHEN MOVIE_CNT = 0 AND IMG_CNT > 0 THEN 'I' /*포토*/
		                             ELSE 'P' /*포토+영상*/
		                        END
		                 FROM   (
		                         SELECT SUM(CASE WHEN FILE_GB = 'M' THEN 1 ELSE 0 END) AS MOVIE_CNT
		                              , SUM(CASE WHEN FILE_GB = 'I' THEN 1 ELSE 0 END) AS IMG_CNT
		                         FROM   TB_REVIEW_ATTACH
		                         WHERE  REVIEW_SQ = R.REVIEW_SQ
		                         AND    DEL_YN = 'N'
		                        ) Z
		                )
		            ELSE
		                R.REVIEW_GB
		       END                                      AS REVIEW_GB      /*상품평구분*/
		     , R.CUST_NO                                                  /*고객번호*/
		     , C.CUST_NM                                                  /*고객명*/
		     , 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.BEST_YN                                                  /*베스트여부*/
		     , R.GIVE_DUE_BPNT                                            /*베스트지급에정포인트*/
		     , R.BPNT_GIVE_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.ADM_RPL                                                  /*관리자댓글*/
		     , FN_GET_USER_NM(R.ADM_RPL_REG_NO)         AS ADM_RPL_REG_NM /*관리자댓글작성자명*/
		     , DATE_FORMAT(R.ADM_RPL_DT,'%Y%m%d%H%i%S') AS ADM_RPL_DT     /*관리자댓글작성일시*/
		FROM   TB_REVIEW R
		     , TB_CUSTOMER C
		     , TB_GOODS G
		WHERE  R.CUST_NO = C.CUST_NO
		AND    R.GOODS_CD = G.GOODS_CD
		AND    R.DEL_YN = 'N'
		<if test="goodsCds != null and goodsCds != ''">
		AND    (
		        <foreach collection="goodsCds" item="item" index="index" separator="or">
		        R.GOODS_CD LIKE CONCAT(UPPER(#{item}),'%') 
		        </foreach>
		       )
		</if>
		<if test="startDt != null and startDt != ''">
		AND    R.REG_DT <![CDATA[>=]]> STR_TO_DATE(#{startDt},'%Y-%m-%d')
		</if>
		<if test="endDt != null and endDt != ''">
		AND    R.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{endDt},'%Y-%m-%d'),INTERVAL 1 DAY)
		</if>
		<if test="reviewGb != null and reviewGb != ''">
		AND    R.REVIEW_GB = #{reviewGb}
		</if>
		<if test="pntGiveStat != null and pntGiveStat != ''">
		AND    R.PNT_GIVE_STAT = #{pntGiveStat}
		</if>
		<if test="dispYn != null and dispYn != ''">
		AND    R.DISP_YN = #{dispYn}
		</if>
		<if test="searchTxt != null and searchTxt != ''">
		    <if test="searchGb == 'memNm'">
		AND    UPPER(C.CUST_NM) LIKE CONCAT(UPPER(#{searchTxt}),'%')
		    </if>
		    <if test="searchGb == 'email'">
		AND    UPPER(C.EMAIL) LIKE CONCAT(UPPER(#{searchTxt}),'%')
		    </if>
		</if>
		ORDER  BY R.REG_DT DESC
		LIMIT #{pageable.limitStartRow}, #{pageable.pageSize}
	</select>
	
	<!--상품평 정보 -->
	<select id="getReview" parameterType="Integer" resultType="Review">
		/* TsaReview.getReview */
		SELECT R.REVIEW_SQ                                                     /*상품평일련번호*/
		     , R.GOODS_CD                                                      /*상품코드*/
		     , G.GOODS_NM                                                      /*상품명*/
		     , (SELECT SYS_IMG_NM
		        FROM   TB_GOODS_IMG
		        WHERE  GOODS_CD = G.GOODS_CD
		        AND    COLOR_CD = G.MAIN_COLOR_CD
		        AND    DEFAULT_IMG_YN = 'Y' /*디폴트이미지*/
		        LIMIT 1
		       )                                             AS GOODS_IMG      /*상품이미지*/
		     , R.REVIEW_GB                                                     /*상품평구분*/
		     , R.CUST_NO                                                       /*고객번호*/
		     , C.CUST_NM                                                       /*고객명*/
		     , R.ORD_NO                                                        /*주문번호*/
		     , R.ORD_DTL_NO                                                    /*주문상세번호*/
		     , R.REVIEW_TITLE                                                  /*상품평제목*/
		     , R.REVIEW_CONTENT                                                /*상품평내용*/
		     , R.SCORE                                                         /*구매평점*/
		     , R.HEIGHT                                                        /*키*/
		     , R.WEIGHT                                                        /*몸무게*/
		     , FN_GET_CODE_NM('G040',R.SCORE_SIZE)           AS SCORE_SIZE     /*사이즈점수*/
		     , FN_GET_CODE_NM('G039',R.SCORE_COLOR)          AS SCORE_COLOR    /*컬러점수*/
		     , FN_GET_CODE_NM('G042',R.SCORE_FIT)            AS SCORE_FIT      /*핏점수*/
		     , FN_GET_CODE_NM('G079',R.SCORE_THICK)          AS SCORE_THICK    /*두께감점수*/
		     , FN_GET_CODE_NM('G080',R.SCORE_WEIGHT)         AS SCORE_WEIGHT   /*무게감점수*/
		     , FN_GET_CODE_NM('G041',R.SCORE_BALL)           AS SCORE_BALL     /*볼넓이점수*/
		     , R.GIVE_DUE_PNT                                                  /*지급예정포인트*/
		     , FN_GET_CODE_NM('G043',R.PNT_GIVE_STAT)        AS PNT_GIVE_STAT  /*포인트지급상태*/
		     , R.BEST_YN                                                       /*베스트여부*/
		     , R.GIVE_DUE_BPNT                                                 /*베스트지급에정포인트*/
		     , R.BPNT_GIVE_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.ADM_RPL                                                       /*관리자댓글*/
		     , FN_GET_USER_NM(R.ADM_RPL_REG_NO)              AS ADM_RPL_REG_NM /*관리자댓글작성자명*/
		     , DATE_FORMAT(R.ADM_RPL_DT,'%Y-%m-%d %H:%i:%S') AS ADM_RPL_DT     /*관리자댓글작성일시*/
		     , R.RPL_CFM_YN                                                    /*댓글확인여부*/
		     , DATE_FORMAT(R.RPL_CFM_DT,'%Y-%m-%d %H:%i:%S') AS RPL_CFM_DT     /*댓글확인일시*/
		     , (
		        SELECT GROUP_CONCAT(CONCAT(CASE WHEN G.SELF_GOODS_YN = 'Y' THEN (SELECT COLOR_KNM FROM TB_COLOR WHERE COLOR_CD = ODI.OPT_CD1) ELSE ODI.OPT_CD1 END,'/',ODI.OPT_CD2)) AS OPT_INFO
		        FROM   TB_ORDER_DETAIL OD
		             , TB_ORDER_DETAIL_ITEM ODI
		             , TB_GOODS G
		        WHERE  OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		        AND    OD.GOODS_CD = G.GOODS_CD
		        AND    OD.ORD_DTL_NO = R.ORD_DTL_NO
		        AND    OD.GOODS_CD = R.GOODS_CD)             AS OPT_INFO
		FROM   TB_REVIEW R
		     , TB_CUSTOMER C
		     , TB_GOODS G
		WHERE  R.CUST_NO = C.CUST_NO
		AND    R.GOODS_CD = G.GOODS_CD
		AND    R.REVIEW_SQ = #{reviewSq}
	</select>
	
	<!--상품평 첨부파일 목록 -->
	<select id="getReviewAttachList" parameterType="Integer" resultType="ReviewAttach">
		/* TsaReview.getReviewAttachList */
		SELECT RV_ATC_SQ   /*상품평첨부파일일련번호*/
		     , REVIEW_SQ   /*상품평일련번호*/
		     , FILE_GB     /*파일구분(M:동영상, I:이미지)*/
		     , ORG_FILE_NM /*원본파일명*/
		     , SYS_FILE_NM /*시스템파일명*/
		     , KUF_KEY     /*KOLLUS업로드파일키*/
		     , KMC_KEY     /*KOLLUS미디어컨텐츠키*/
		FROM   TB_REVIEW_ATTACH
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    DEL_YN = 'N' /*삭제안된넘*/
		ORDER  BY FILE_GB DESC, RV_ATC_SQ
	</select>
	
	<!-- 상품평 관리자댓글 저장 -->
	<update id="updateReviewUserReply" parameterType="Review">
		/* TsaReview.updateReviewUserReply */
		UPDATE TB_REVIEW
		SET    ADM_RPL = #{admRpl}
		     , ADM_RPL_REG_NO = #{admRplRegNo}
		     , ADM_RPL_DT = NOW()
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    RPL_CFM_YN = 'N' /*고객이 관리자댓글 확인을 안한넘*/
	</update>
	
	<!-- 동영상상품평포인트 생성-->
	<insert id="createVideoReviewPoint" parameterType="Review" keyProperty="custPntSq">
		/* TsaReview.createVideoReviewPoint */
		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
		)
		SELECT NULL                             AS CUST_PNT_SQ
		     , CUST_NO
		     , #{gvPntAmt}                      AS GV_PNT_AMT
		     , 0                                AS US_PNT_AMT
		     , #{rmPntAmt}                      AS RM_PNT_AMT
		     , DATE_ADD(NOW(), INTERVAL 1 YEAR) AS EXP_BE_DT
		     , NULL                             AS EXP_CMP_DT
		     , #{regNo}                         AS REG_NO
		     , NOW()                            AS REG_DT
		     , #{updNo}                         AS UPD_NO
		     , NOW()                            AS UPD_DT
		FROM   TB_REVIEW
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    CONFIRM_YN = 'N' /*동영상노출처리안한넘*/
		AND    PNT_GIVE_STAT = 'G043_10' /*포인트지급예정 상태*/
	</insert>
	
	<!-- 동영상상품평포인트이력 생성-->
	<insert id="createVideoReviewPointHistory" parameterType="Review">
		/* TsaReview.createVideoReviewPointHistory */
		INSERT INTO TB_CUST_POINT_HST (
		       PNT_HST_SQ
		     , 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
		)
		SELECT NULL            AS PNT_HST_SQ
		     , CUST_NO
		     , #{occurGb}      AS OCCUR_GB
		     , #{occurDtlDesc} AS OCCUR_DTL_DESC
		     , #{pntAmt}       AS PNT_AMT
		     , #{custPntSq}    AS CUST_PNT_SQ
		     , ORD_NO
		     , ORD_DTL_NO
		     , REVIEW_SQ
		     , NOW()            AS SWITCH_DUE_DT
		     , #{pntUploadStat} AS PNT_UPLOAD_STAT
		     , NOW()            AS PNT_UPLOAD_DT
		     , #{regNo}         AS REG_NO
		     , NOW()            AS REG_DT
		     , #{updNo}         AS UPD_NO
		     , NOW()            AS UPD_DT
		FROM   TB_REVIEW
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    CONFIRM_YN = 'N' /*동영상노출처리안한넘*/
		AND    PNT_GIVE_STAT = 'G043_10' /*포인트지급예정 상태*/
	</insert>
	
	<!-- 상품평 동영상 노출 처리 -->
	<update id="updateVideoReviewDisplay" parameterType="Review">
		/* TsaReview.updateVideoReviewDisplay */
		UPDATE TB_REVIEW
		SET    CONFIRM_YN = 'Y'
		     , GIVE_DUE_PNT = #{giveDuePnt}
		     , PNT_GIVE_STAT = 'G043_30' /*포인트지급반영*/
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    CONFIRM_YN = 'N' /*동영상노출처리안한넘*/
		AND    PNT_GIVE_STAT = 'G043_10' /*포인트지급예정 상태*/
	</update>
	
	<!-- 베스트상품평포인트 생성-->
	<insert id="createBestReviewPoint" parameterType="Review" keyProperty="custPntSq">
		/* TsaReview.createBestReviewPoint */
		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
		)
		SELECT NULL                             AS CUST_PNT_SQ
		     , CUST_NO
		     , #{gvPntAmt}                      AS GV_PNT_AMT
		     , 0                                AS US_PNT_AMT
		     , #{rmPntAmt}                      AS RM_PNT_AMT
		     , DATE_ADD(NOW(), INTERVAL 1 YEAR) AS EXP_BE_DT
		     , NULL                             AS EXP_CMP_DT
		     , #{regNo}                         AS REG_NO
		     , NOW()                            AS REG_DT
		     , #{updNo}                         AS UPD_NO
		     , NOW()                            AS UPD_DT
		FROM   TB_REVIEW
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    BEST_YN = 'N' /*베스트상품이아닌넘. 베스트여부 값이 변경될 수도 있으므로 체크로직 추가*/
	</insert>

	<!-- 베스트상품평포인트이력 생성-->
	<insert id="createBestReviewPointHistory" parameterType="Review">
		/* TsaReview.createBestReviewPointHistory */
		INSERT INTO TB_CUST_POINT_HST (
		       PNT_HST_SQ
		     , 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
		)
		SELECT NULL            AS PNT_HST_SQ
		     , CUST_NO
		     , #{occurGb}      AS OCCUR_GB
		     , #{occurDtlDesc} AS OCCUR_DTL_DESC
		     , #{pntAmt}       AS PNT_AMT
		     , #{custPntSq}    AS CUST_PNT_SQ
		     , ORD_NO
		     , ORD_DTL_NO
		     , REVIEW_SQ
		     , NOW()            AS SWITCH_DUE_DT
		     , #{pntUploadStat} AS PNT_UPLOAD_STAT
		     , NOW()            AS PNT_UPLOAD_DT
		     , #{regNo}         AS REG_NO
		     , NOW()            AS REG_DT
		     , #{updNo}         AS UPD_NO
		     , NOW()            AS UPD_DT
		FROM   TB_REVIEW
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    BEST_YN = 'N' /*베스트상품이아닌넘. 베스트여부 값이 변경될 수도 있으므로 체크로직 추가*/
	</insert>
	
	<!-- 베스트상품평 선정 처리 -->
	<update id="updateBestReviewSelection" parameterType="Review">
		/* TsaReview.updateBestReviewSelection */
		UPDATE TB_REVIEW
		SET    BEST_YN = 'Y'
		     , GIVE_DUE_BPNT = #{giveDueBpnt}
		     , BPNT_GIVE_YN = 'Y'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  REVIEW_SQ = #{reviewSq}
		AND    BEST_YN = 'N' /*베스트상품이아닌넘. 베스트여부 값이 변경될 수도 있으므로 체크로직 추가*/
	</update>
	
</mapper>