style24.doc/산출물/4.구축/데이터추출/상품평포인튼지급로직_분석및처리.sql

SELECT *
FROM   TB_COMMON_CODE
WHERE  CD_GB = 'G069'
;

SELECT *
FROM   TB_REVIEW
WHERE  CUST_NO = 100011
;

SELECT DATE_ADD(LAST_DAY(NOW()), INTERVAL -1 MONTH)
     , DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY)
     , STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(),'%Y-%m'),'-01'),'%Y-%m-%d')
FROM   DUAL
;

SELECT COUNT(*) AS CNT
FROM   TB_REVIEW RV
WHERE  RV.CUST_NO = 100011 
AND    RV.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
AND    RV.CONFIRM_DT >= STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(),'%Y-%m'),'-01'),'%Y-%m-%d')
AND    RV.CONFIRM_DT < DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY)
;

INSERT INTO TB_COMMON_CODE VALUES ('G043', 'G043_50', '당월지급횟수(10)초과', NULL, 'Y', 5, 1, NOW(), 1, NOW());
INSERT INTO TB_COMMON_CODE VALUES ('G043', 'G043_60', '동일상품기지급', NULL, 'Y', 5, 1, NOW(), 1, NOW());
COMMIT;

SELECT CASE WHEN IFNULL(RA.VIDEO_CNT,0) = 0 AND IFNULL(RA.IMG_CNT,0) = 0 THEN 'T'
            ELSE
                CASE WHEN IFNULL(RA.VIDEO_CNT,0) = 0 AND IFNULL(RA.IMG_CNT,0) > 0 THEN 'I'
                     WHEN IFNULL(RA.VIDEO_CNT,0) > 0 AND IFNULL(RA.IMG_CNT,0) = 0 THEN 'V'
                     ELSE 'IV'
                END
       END AS REVIEW_GB
FROM   TB_REVIEW R
LEFT OUTER JOIN (
		SELECT REVIEW_SQ
		     , SUM(CASE WHEN FILE_GB = 'M' AND KUF_KEY IS NOT NULL THEN 1 ELSE 0 END)     AS VIDEO_CNT
		     , SUM(CASE WHEN FILE_GB = 'I' AND ORG_FILE_NM IS NOT NULL THEN 1 ELSE 0 END) AS IMG_CNT
		FROM   TB_REVIEW_ATTACH
		WHERE  REVIEW_SQ = 47
		GROUP  BY REVIEW_SQ
       ) RA ON R.REVIEW_SQ = RA.REVIEW_SQ
WHERE  R.REVIEW_SQ = 47
;

SELECT GOODS_CD, COUNT(*) AS CNT
FROM   TB_REVIEW
WHERE  CUST_NO = 100030
AND    PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
AND    CONFIRM_YN = 'Y' /*확인완료된 넘*/
GROUP  BY GOODS_CD
HAVING COUNT(*) > 1
;

-- 당월에 지급된 포인트 횟수
SELECT *
FROM   TB_REVIEW R
WHERE  1 = 1
AND    R.CUST_NO = 100047
AND    R.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
AND    R.REG_DT >= STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(),'%Y-%m'),'-01'),'%Y-%m-%d') /*당월1일부터*/
AND    R.REG_DT < DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) /*당월말일까지*/
ORDER  BY REVIEW_SQ DESC
;

SELECT *
FROM   TB_CUSTOMER
WHERE  CUST_NO = 100011
;

SELECT *
FROM   TB_ORDER
WHERE  ORD_NO = 6217
;

-- 테스트를 위한 비번 업데이트
UPDATE TB_CUSTOMER SET PASSWD = 'aee70ae8fb1dc5d6e9b7aec45c5f4f23e597752ddc213321eebc41979b78ebc4' WHERE CUST_NO = 100011;
COMMIT;
-- 비번 원복
UPDATE TB_CUSTOMER SET PASSWD = 'a0252f05fcea604efb19d3132877922f9f0a1ac56f7715368aadde4bd03538dc' WHERE CUST_NO = 100011;

SELECT *
FROM   TB_REVIEW
WHERE  REVIEW_SQ = 242
;

SELECT *
FROM   TB_REVIEW_ATTACH
WHERE  REVIEW_SQ = 242
;

SELECT *
FROM   TB_CUST_POINT_HST
WHERE  REVIEW_SQ = 242
;

DELETE
FROM   TB_REVIEW
WHERE  REVIEW_SQ = 242
;

DELETE
FROM   TB_REVIEW_ATTACH
WHERE  1 = 1
AND    REVIEW_SQ = 242
;

DELETE
FROM   TB_CUST_POINT_HST
WHERE  REVIEW_SQ = 242
;

-- 텍스트만 등록한 경우 포인트 즉시 지급되는가? => 즉시 지급된다.
-- => OK

-- 텍스트+포토만 등록한 경우 포인트 즉시 지급되는가? => 즉시 지급된다.
-- => OK

-- 텍스트+동영상만 등록한 경우 포인트 즉시 지급되는가? => 즉시 지급 불가. BOS에서 동영상 노출 시 포인트 지급
-- 텍스트+포토+동영상 모두 등록한 경우 포인트 즉시 지급되는가? => 즉시 지급되나 동영상은 프론트에 노출되지 않는다. BOS에서 동영상 노출 시 프론트에 노출된다.

-- 포인트가 기 지급된 상품을 프론트에서 상품평을 등록한 경우 포인트가 지급되는가? => 포인트가 지급되지 않아야 한다.. 그리고 BOS에서 포인트지급상태가 "동일상품기지급"로 표기되어야 한다.
-- 등록 예시) 해당 상품은 이미 등록된 상품평입니다. 상품평은 등록되어도 포인트는 지급이 안 되어야 합니다.
-- => OK

-- 당월에 지급 가능한 총횟수(10회)가 넘는 상품평을 등록한 경우 포인트가 지급되는가? => 포인트가 지급되지 않아야 한다. 그리고 BOS에서 포인트지급상태가 "당월지급횟수(10)초과"로 표기되어야 한다.
-- => OK

SELECT *
FROM   TB_REVIEW
WHERE  CUST_NO = 100030
AND    GOODS_CD = 'ANW11APT65'
AND    PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
AND    CONFIRM_YN = 'Y' /*확인완료된 넘*/
;

SELECT COUNT(*) AS CNT
FROM   TB_REVIEW R
WHERE  1 = 1
AND    R.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
AND    R.CONFIRM_YN = 'Y' /*확인완료된 넘*/
AND    R.CUST_NO = #{custNo}
AND    R.GOODS_CD = #{goodsCd}
;

SELECT COUNT(*) AS CNT
FROM   TB_REVIEW R
WHERE  (R.CUST_NO,R.GOODS_CD) = (SELECT CUST_NO, GOODS_CD
                                 FROM   TB_REVIEW
                                 WHERE  REVIEW_SQ = 152) /*해당 상품평을 등록한 고객과 상품코드*/
AND    R.REVIEW_SQ != 152 /*해당 상품평 외*/
AND    R.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
AND    R.CONFIRM_YN = 'Y' /*확인완료된 넘*/
;
	
SELECT *
FROM   TB_REVIEW_ATTACH
;

SELECT *
FROM   TB_CUST_POINT_HST
WHERE  1 = 1
AND    OCCUR_GB = 'G069_20' /*상품평포인트지급*/
AND    PNT_UPLOAD_STAT = 'G070_30' /*반영완료*/
AND    CUST_NO
AND    ORD_NO 
AND    ORD_DTL_NO
;

/* 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 =  210 /**P*/
AND    DEL_YN = 'N' /*삭제안된넘*/
ORDER  BY FILE_GB DESC, RV_ATC_SQ
;

/* TsfReivew.getReviewPointGiveMonthCount */	
		SELECT COUNT(*) AS CNT
		FROM   TB_REVIEW R
		WHERE  R.CUST_NO = (SELECT CUST_NO
		                    FROM   TB_REVIEW
		                    WHERE  REVIEW_SQ =  210 /**P*/) /*해당 상품평을 등록한 고객*/
		AND    R.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
		AND    R.REG_DT  >=  STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(),'%Y-%m'),'-01'),'%Y-%m-%d') /*당월1일부터*/
		AND    R.REG_DT  <  DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) /*당월말일까지*/
		;
		
	/* TsfReivew.getReviewPointGiveCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_REVIEW R
		WHERE  (R.CUST_NO,R.GOODS_CD) = (SELECT CUST_NO, GOODS_CD
		                                 FROM   TB_REVIEW
		                                 WHERE  REVIEW_SQ =  210 /**P*/) /*해당 상품평을 등록한 고객과 상품코드*/
		AND    R.REVIEW_SQ !=  210 /**P*/ /*해당 상품평 외*/
		AND    R.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
		;
		
	/* TsaReview.getReviewAttachList */
		SELECT CASE WHEN IFNULL(RA.VIDEO_CNT,0) = 0 AND IFNULL(RA.IMG_CNT,0) = 0 THEN 'T' /*텍스트상품평*/
		            ELSE
		                CASE WHEN IFNULL(RA.VIDEO_CNT,0) = 0 AND IFNULL(RA.IMG_CNT,0) > 0 THEN 'I' /*포토상품평*/
		                     WHEN IFNULL(RA.VIDEO_CNT,0) > 0 AND IFNULL(RA.IMG_CNT,0) = 0 THEN 'V' /*동영상상품평*/
		                     ELSE 'IV' /*포토+동영상상품평*/
		                END
		       END AS REVIEW_GB
		FROM   TB_REVIEW R
		LEFT OUTER JOIN (
		        SELECT REVIEW_SQ
		             , SUM(CASE WHEN FILE_GB = 'M' AND KUF_KEY IS NOT NULL THEN 1 ELSE 0 END)     AS VIDEO_CNT
		             , SUM(CASE WHEN FILE_GB = 'I' AND ORG_FILE_NM IS NOT NULL THEN 1 ELSE 0 END) AS IMG_CNT
		        FROM   TB_REVIEW_ATTACH
		        WHERE  REVIEW_SQ =  210 /**P*/
		        GROUP  BY REVIEW_SQ
		       ) RA ON R.REVIEW_SQ = RA.REVIEW_SQ
		WHERE  R.REVIEW_SQ =  210 /**P*/
		;
		
-- 상품평포인트 월지급횟수(10회) 초과한 포인트금액 조회
with BASE_REVIEW as (
	SELECT DATE_FORMAT(R.REG_DT,'%Y-%m') as REG_YM
	     , R.CUST_NO
	     , RANK() OVER(partition by DATE_FORMAT(R.REG_DT,'%Y-%m'), R.CUST_NO order by R.REVIEW_SQ) as RNUM
	     , R.REVIEW_SQ
	     , CPH.PNT_AMT
	FROM   TB_REVIEW R
	inner join TB_CUST_POINT_HST CPH on R.CUST_NO = CPH.CUST_NO and R.REVIEW_SQ = CPH.REVIEW_SQ
	WHERE  1 = 1
-- 	and    R.CUST_NO = 106600
	AND    R.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
	AND    R.REG_DT >= STR_TO_DATE('2021-09-01','%Y-%m-%d') /*9월1일부터*/
)
select R.REG_YM       /*연월*/
     , R.CUST_NO      /*고객번호*/
     , R.TOT_PNT_AMT  /*지급된총포인트*/
     , R.OVER_PNT_AMT /*초과지급된포인트*/
     , R.TOT_PNT_AMT - R.OVER_PNT_AMT as REAL_PNT_AMT /*실제지급되어야할포인트*/
from   (
		select R.REG_YM
		     , R.CUST_NO
		     , SUM(R.PNT_AMT) as TOT_PNT_AMT
		     , IFNULL((select SUM(PNT_AMT)
		               from   BASE_REVIEW
		               where  REG_YM = R.REG_YM
		               and    CUST_NO = R.CUST_NO
		               and    RNUM > 10
		              ),0) as OVER_PNT_AMT /*포인트초과분*/
		from   BASE_REVIEW R
		group  by R.REG_YM, R.CUST_NO
	   ) R 
where  R.OVER_PNT_AMT > 0
order  by R.REG_YM, R.CUST_NO
;

-- 상품평포인트 동일상품지급횟수 조회 (3건)
SELECT R.CUST_NO
     , R.GOODS_CD
     , COUNT(*) AS CNT
     , SUM(CPH.PNT_AMT) as PNT_AMT
FROM   TB_REVIEW R
inner join TB_CUST_POINT_HST CPH on R.REVIEW_SQ = CPH.REVIEW_SQ and R.CUST_NO = CPH.CUST_NO
WHERE  R.PNT_GIVE_STAT = 'G043_30' /*포인트반영완료된 넘*/
AND    R.REG_DT >= STR_TO_DATE('2021-09-01','%Y-%m-%d') /*9월1일부터*/
group  by R.CUST_NO, R.GOODS_CD
having COUNT(*) > 1
;