| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168 |
- TRUNCATE TABLE TB_EP_DAUM;
- INSERT INTO TB_EP_DAUM (
- GB
- , MAPID
- , LPRICE
- , PRICE
- , MPRIC
- , PNAME
- , PGURL
- , IGURL
- , UPIMG
- , CATE1
- , CAID1
- , CATE2
- , CAID2
- , CATE3
- , CAID3
- , CATE4
- , CAID4
- , MODEL
- , BRAND
- , MAKER
- , COUPON
- , MCOUPON
- , PCARD
- , POINT
- , DELIV
- , DELIVTERM
- , RATING
- , REVCT
- , EVENT
- , CARDDN
- , CARDP
- , ADULT
- , PUBDATE
- , GOODS_STAT
- , SELF_MALL_YN
- , SELL_STDT
- , SELL_EDDT
- , UPD_GB
- , UTIME
- )
- SELECT 'LAST' AS GB /*구분(PREV:이전, CURR:현재)*/
- , G.GOODS_CD AS MAPID /*상품ID*/
- , G.LIST_PRICE AS LPRICE /*정상가*/
- , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS PRICE /*즉시할인이적용된가격*/
- , IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS MPRIC /*즉시할인이적용된가격*/
- , SUBSTRING(CONCAT(CASE WHEN CC1.CD IN ('G073_12','G073_13') THEN CONCAT('[',CC1.CD_NM,']')
- WHEN G.FOREIGN_BUY_YN = 'Y' THEN '[해외]'
- ELSE ''
- END,
- '[',CASE WHEN B.DISP_NM_LANG = 'EN' THEN B.BRAND_ENM ELSE B.BRAND_KNM END,'] ',
- G.GOODS_NM,
- IFNULL(G.GOODS_NUM,'')),1,100) AS PNAME /*상품명*/
- , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',G.GOODS_CD) AS PGURL /*PC상세URL*/
- , GI.IMAGE_LINK AS IGURL /*이미지URL*/
- , SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1) AS CATE1 /*카테고리명1*/
- , SUBSTRING(G.ITEMKIND_CD,1,2) AS CAID1 /*카테고리ID1*/
- , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1) THEN
- NULL
- ELSE
- SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1)
- END AS CATE2 /*카테고리명2*/
- , SUBSTRING(G.ITEMKIND_CD,3,2) AS CAID2 /*카테고리ID2*/
- , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1) THEN
- NULL
- ELSE
- SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1)
- END AS CATE3 /*카테고리명3*/
- , SUBSTRING(G.ITEMKIND_CD,5,2) AS CAID3 /*카테고리ID3*/
- , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',4),'>',-1) THEN
- NULL
- ELSE
- SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',4),'>',-1)
- END AS CATE4 /*카테고리명4*/
- , SUBSTRING(G.ITEMKIND_CD,7,2) AS CAID4 /*카테고리ID4*/
- , G.GOODS_NUM AS MODEL /*모델(품번)*/
- , IF(B.DISP_NM_LANG = 'EN',B.BRAND_ENM,B.BRAND_KNM) AS BRAND /*브랜드명*/
- , SUBSTRING(GNI.NI_CONTENT,1,50) AS MAKER /*제조사*/
- , TEC.COUPON /*쿠폰*/
- , TEC.COUPON AS MCOUPON /*모바일쿠폰*/
- , IFE.INTEREST_FREE_EVENT AS PCARD /*무이자행사*/
- , CONCAT('',G.PNT_PRATE) AS POINT /*포인트율*/
- , IFNULL(CASE WHEN DFP.DELV_FEE_CRITE = 'G078_20' /*무료*/ THEN
- 0
- ELSE /*조건부무료 또는 유료*/
- CASE WHEN DFP.MIN_ORD_AMT <= IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY THEN
- 0
- ELSE
- DFP.DELV_FEE
- END
- END,0) AS DELIV /*배송료*/
- , CASE WHEN G.SELF_GOODS_YN = 'Y' THEN 1 ELSE 3 END AS DELIVTERM /*배송기간(자사상품:1일,입점상품:3일)*/
- , CASE WHEN GS.SCORE IS NULL OR GS.SCORE = 0.0 THEN ''
- ELSE CONCAT(IFNULL(GS.SCORE,0),'/',5)
- END AS RATING /*상품평평점*/
- , IFNULL(GS.REVIEW_REG_CNT,0) AS REVCT /*상품평수*/
- , G.GOODS_TNM AS EVENT /*상품셀링문구*/
- , SUBSTRING_INDEX(SUBSTRING_INDEX(CE.CARD_EVENT,'|',1),'^',1) AS CARDDN /*카드행사*/
- , SUBSTRING(SUBSTRING_INDEX(CE.CARD_EVENT,'|',1),INSTR(SUBSTRING_INDEX(CE.CARD_EVENT,'|',1),'^') + 1) AS CARDP /*카드할인가*/
- , G.ADULT_YN AS ADULT /*성인여부*/
- , DATE_FORMAT(G.REG_DT,'%Y%m%d') AS PUBDATE /*등록일*/
- , G.GOODS_STAT
- , G.SELF_MALL_YN
- , G.SELL_STDT
- , G.SELL_EDDT
- , 'I' AS UPD_GB /*변경구분*/
- , DATE_FORMAT(NOW(),'%Y%m%d%H%i%S') AS UTIME
- FROM TB_GOODS G
- INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
- INNER JOIN TB_ITEMKIND I ON G.ITEMKIND_CD = I.ITEMKIND_CD
- INNER JOIN TB_DELV_FEE_POLICY DFP ON G.DELV_FEE_CD = DFP.DELV_FEE_CD
- AND G.SUPPLY_COMP_CD = DFP.SUPPLY_COMP_CD
- INNER JOIN TB_EP_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
- LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD
- LEFT OUTER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
- LEFT OUTER JOIN TB_EP_GOODS_ADD_IMG GAI ON G.GOODS_CD = GAI.GOODS_CD
- LEFT OUTER JOIN (
- SELECT ITEMKIND_CD
- , MIN(EP_CATE_CD) AS EP_CATE_CD
- FROM TB_EP_CATE
- WHERE EP_GB = '10' /*네이버*/
- AND USE_YN = 'Y'
- GROUP BY ITEMKIND_CD
- ) EC ON G.ITEMKIND_CD = EC.ITEMKIND_CD
- LEFT OUTER JOIN TB_GOODS_RES_SELL GRS ON G.GOODS_CD = GRS.GOODS_CD
- AND GRS.DELV_RES_DT >= NOW()
- AND GRS.USE_YN = 'Y'
- LEFT OUTER JOIN TB_EP_CARD_EVENT CE ON G.GOODS_CD = CE.GOODS_CD
- LEFT OUTER JOIN TB_EP_COUPON TEC ON G.GOODS_CD = TEC.GOODS_CD
- LEFT OUTER JOIN TB_EP_INTEREST_FREE_EVENT IFE ON G.GOODS_CD = IFE.GOODS_CD
- LEFT OUTER JOIN TB_GOODS_NOTI_INFO GNI ON G.GOODS_CD = GNI.GOODS_CD
- AND GNI.NI_ITEM_CD = 'G005_005' /*제조사*/
- LEFT OUTER JOIN TB_COMMON_CODE CC1 ON G.GOODS_GB = CC1.CD
- AND CC1.CD_GB = 'G073'
- LEFT OUTER JOIN TB_COMMON_CODE CC2 ON G.ORIGIN_CD = CC2.CD
- AND CC2.CD_GB = 'G076'
- WHERE 1 = 1
- AND G.GOODS_STAT = 'G008_90' /*승인완료상품*/
- AND G.SELF_MALL_YN = 'Y' /*몰노출상품*/
- AND G.SELL_STDT <= NOW() /*유효한 판매기간*/
- AND G.SELL_EDDT >= NOW() /*유효한 판매기간*/
- AND G.ADULT_YN = 'N' /*성인상품아닌넘만*/
- AND NOT EXISTS (SELECT 1
- FROM TB_GOODS_EP_SKIP
- WHERE GOODS_CD = G.GOODS_CD
- AND NOW() BETWEEN APPLY_STDT AND APPLY_EDDT
- )
- ;
- -- 다음 상품평EP 목록
- SELECT R.GOODS_CD AS MAPID /*상품ID*/
- , R.REVIEW_SQ AS REVIEWID /*상품평ID*/
- , CASE WHEN R.DISP_YN = 'N' OR R.DEL_YN = 'Y' THEN 'D'
- ELSE 'S'
- END AS STATUS /*상품평상태(S:정상, D:삭제)*/
- , R.REVIEW_TITLE AS TITLE /*상품평제목*/
- , SUBSTRING(R.REVIEW_CONTENT,1,250) AS CONTENT /*상품평내용(250자이내)*/
- , C.CUST_NM AS WRITER
- , DATE_FORMAT(R.REG_DT,'%Y%m%d%H%i%S') AS CDATE /*상품평작성일시*/
- , R.SCORE AS RATING /*상품평점수*/
- FROM TB_REVIEW R
- , TB_CUSTOMER C
- WHERE R.REG_NO = C.CUST_NO
- AND R.CONFIRM_YN = 'Y' /*컨펌된넘*/
- ;
|
