################################################################################ #EP다음 ################################################################################ DROP TABLE IF EXISTS TB_EP_DAUM RESTRICT; CREATE TABLE TB_EP_DAUM ( GB VARCHAR(4) NOT NULL COMMENT '구분(PREV:이전, CURR:현재)', MAPID VARCHAR(20) NOT NULL COMMENT '상품코드(상품)', LPRICE INT UNSIGNED COMMENT '정상가', PRICE INT UNSIGNED COMMENT 'PC가격', MPRIC INT UNSIGNED COMMENT '모바일가격', PNAME VARCHAR(100) COMMENT '상품명([상품구분명]+[브랜드명]+상품명+상품코드 형식)', PGURL VARCHAR(200) COMMENT 'PC상품상세URL', IGURL VARCHAR(200) COMMENT '이미지링크', UPIMG VARCHAR(1) DEFAULT 'N' COMMENT '전체EP생성 이후 이미지변경되었을때전송(Y/N)', CATE1 VARCHAR(50) COMMENT '카테고리명1', CAID1 VARCHAR(10) COMMENT '카테고리ID1', CATE2 VARCHAR(50) COMMENT '카테고리명2', CAID2 VARCHAR(10) COMMENT '카테고리ID2', CATE3 VARCHAR(50) COMMENT '카테고리명3', CAID3 VARCHAR(10) COMMENT '카테고리ID3', CATE4 VARCHAR(50) COMMENT '카테고리명4', CAID4 VARCHAR(10) COMMENT '카테고리ID4', MODEL VARCHAR(20) COMMENT '모델(품번)', BRAND VARCHAR(50) COMMENT '브랜드명', MAKER VARCHAR(50) COMMENT '제조사', COUPON VARCHAR(100) COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)', MCOUPON VARCHAR(100) COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)', PCARD VARCHAR(100) COMMENT '무이자행사(카드명^개월수-개월수|카드명^개월수-개월수... 형식)', POINT VARCHAR(10) COMMENT '포인트율', DELIV INT UNSIGNED COMMENT '배송료', DELIVTERM INT UNSIGNED COMMENT '배송기간', RATING VARCHAR(20) COMMENT '상품평점수(4.5/5 형식)', REVCT INT UNSIGNED COMMENT '상품평수', EVENT VARCHAR(100) COMMENT '상품타이틀명(세일링문구)', CARDDN VARCHAR(100) COMMENT '카드행사(카드즉시할인 등록된 기준으로 적용. 카드명^카드적용가|카드명^카드적용가... 형식)', CARDP INT UNSIGNED COMMENT '카드적용가(최대1개)', ADULT VARCHAR(1) COMMENT '성인상품여부', PUBDATE VARCHAR(8) COMMENT '상품등록일', GOODS_STAT VARCHAR(20) COMMENT '상품상태(공통코드G008)', SELF_MALL_YN CHAR(1) COMMENT '자사몰노출여부', SELL_STDT DATETIME COMMENT '판매시작일시', SELL_EDDT DATETIME COMMENT '판매종료일시', UPD_GB VARCHAR(1) COMMENT '업데이트구분(I:신규상품, U:기존상품중업데이트된상품 또는 품절되었다가다시서비스되는상품, D:품절상품)', UTIME VARCHAR(20) COMMENT '업데이트시간(YYYYMMDDHHMMSS 형식)' ) COMMENT 'EP다음. EP다음 생성에 의한 발생'; CREATE UNIQUE INDEX IX_EP_DAUM_01 ON TB_EP_DAUM ( GB, MAPID ); -- EP다음 정보 삭제 DELETE FROM TB_EP_DAUM WHERE GB = 'PREV'; -- EP다음 이전 데이터로 백업 INSERT INTO TB_EP_DAUM ( GB , MAPID , LPRICE , PRICE , MPRIC , PNAME , PGURL , IGURL , 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 ) SELECT 'PREV' AS GB , MAPID , LPRICE , PRICE , MPRIC , PNAME , PGURL , IGURL , 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 FROM TB_EP_DAUM WHERE GB = 'LAST' ; -- EP다음 현재 데이터 생성 (2분 6초) INSERT INTO TB_EP_DAUM ( GB , MAPID , LPRICE , PRICE , MPRIC , PNAME , PGURL , IGURL , 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 ) SELECT 'CURR' 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 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 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_IMG GI ON G.GOODS_CD = GI.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 G.UPD_DT >= DATE_ADD(NOW(), INTERVAL -7 HOUR) -- 요약EP ; -- 최종 EP다음 데이터 생성 (45초) 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 , 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 , DATE_FORMAT(NOW(),'%Y%m%d%H%i%S') AS UTIME FROM ( /*신규 목록*/ SELECT MAPID , LPRICE , PRICE , MPRIC , PNAME , PGURL , IGURL , 'N' AS 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 , 'I' AS UPD_GB /*변경구분*/ FROM TB_EP_DAUM X WHERE GB = 'CURR' /*현재데이터*/ -- 요약EP -- AND NOT EXISTS (SELECT 1 -- FROM TB_EP_DAUM -- WHERE GB = 'PREV' /*이전백업데이터*/ -- AND ID = X.ID -- UNION ALL -- /*변경 목록*/ -- SELECT X.MAPID -- , X.LPRICE -- , X.PRICE -- , X.MPRIC -- , X.PNAME -- , X.PGURL -- , X.IGURL -- , CASE WHEN X.IGURL != Y.IGURL THEN 'Y' ELSE 'N' END AS UPIMG -- , X.CATE1 -- , X.CAID1 -- , X.CATE2 -- , X.CAID2 -- , X.CATE3 -- , X.CAID3 -- , X.CATE4 -- , X.CAID4 -- , X.MODEL -- , X.BRAND -- , X.MAKER -- , X.COUPON -- , X.MCOUPON -- , X.PCARD -- , X.POINT -- , X.DELIV -- , X.DELIVTERM -- , X.RATING -- , X.REVCT -- , X.EVENT -- , X.CARDDN -- , X.CARDP -- , X.ADULT -- , X.PUBDATE -- , X.GOODS_STAT -- , X.SELF_MALL_YN -- , X.SELL_STDT -- , X.SELL_EDDT -- , 'U' AS UPD_GB /*변경구분*/ -- FROM TB_EP_DAUM X -- , TB_EP_DAUM Y -- WHERE X.ID = Y.ID -- AND X.GB = 'CURR' /*현재데이터*/ -- AND Y.GB = 'PREV' /*이전백업데이터*/ -- AND ( -- X.MAPID != Y.MAPID OR -- X.LPRICE != Y.LPRICE OR -- X.PRICE != Y.PRICE OR -- X.MPRIC != Y.MPRIC OR -- X.PNAME != Y.PNAME OR -- X.PGURL != Y.PGURL OR -- X.IGURL != Y.IGURL OR -- X.CATE1 != Y.CATE1 OR -- X.CAID1 != Y.CAID1 OR -- X.CATE2 != Y.CATE2 OR -- X.CAID2 != Y.CAID2 OR -- X.CATE3 != Y.CATE3 OR -- X.CAID3 != Y.CAID3 OR -- X.CATE4 != Y.CATE4 OR -- X.CAID4 != Y.CAID4 OR -- X.MODEL != Y.MODEL OR -- X.BRAND != Y.BRAND OR -- X.MAKER != Y.MAKER OR -- X.COUPON != Y.COUPON OR -- X.MCOUPON != Y.MCOUPON OR -- X.PCARD != Y.PCARD OR -- X.POINT != Y.POINT OR -- X.DELIV != Y.DELIV OR -- X.DELIVTERM != Y.DELIVTERM OR -- X.RATING != Y.RATING OR -- X.REVCT != Y.REVCT OR -- X.EVENT != Y.EVENT OR -- X.CARDDN != Y.CARDDN OR -- X.CARDP != Y.CARDP OR -- X.ADULT != Y.ADULT OR -- X.PUBDATE != Y.PUBDATE -- ) -- UNION ALL -- /*삭제된 목록*/ -- SELECT X.MAPID -- , X.LPRICE -- , X.PRICE -- , X.MPRIC -- , X.PNAME -- , X.PGURL -- , X.IGURL -- , CASE WHEN X.IGURL != Y.IGURL THEN 'Y' ELSE 'N' END AS UPIMG -- , X.CATE1 -- , X.CAID1 -- , X.CATE2 -- , X.CAID2 -- , X.CATE3 -- , X.CAID3 -- , X.CATE4 -- , X.CAID4 -- , X.MODEL -- , X.BRAND -- , X.MAKER -- , X.COUPON -- , X.MCOUPON -- , X.PCARD -- , X.POINT -- , X.DELIV -- , X.DELIVTERM -- , X.RATING -- , X.REVCT -- , X.EVENT -- , X.CARDDN -- , X.CARDP -- , X.ADULT -- , X.PUBDATE -- , X.GOODS_STAT -- , X.SELF_MALL_YN -- , X.SELL_STDT -- , X.SELL_EDDT -- , 'D' AS UPD_GB /*변경구분*/ -- FROM TB_EP_DAUM X -- , TB_EP_DAUM Y -- WHERE X.ID = Y.ID -- AND X.GB = 'CURR' /*현재데이터*/ -- AND Y.GB = 'PREV' /*이전백업데이터*/ -- AND ( -- (Y.GOODS_STAT != 'G008_70' AND X.GOODS_STAT = 'G008_70') -- OR -- (Y.SELF_MALL_YN = 'Y' AND X.SELF_MALL_YN = 'N') -- OR -- (Y.SELL_EDDT != X.SELL_EDDT AND Y.SELL_EDDT < NOW()) -- ) ) X ; -- 다음 EP 목록 SELECT IFNULL(MAPID,'') AS MAPID , LPRICE , PRICE , MPRIC , IFNULL(PNAME,'') AS PNAME , IFNULL(PGURL,'') AS PGURL , IFNULL(IGURL,'') AS IGURL , IFNULL(UPIMG,'') AS UPIMG , IFNULL(CATE1,'') AS CATE1 , IFNULL(CAID1,'') AS CAID1 , IFNULL(CATE2,'') AS CATE2 , IFNULL(CAID2,'') AS CAID2 , IFNULL(CATE3,'') AS CATE3 , IFNULL(CAID3,'') AS CAID3 , IFNULL(CATE4,'') AS CATE4 , IFNULL(CAID4,'') AS CAID4 , IFNULL(MODEL,'') AS MODEL , IFNULL(BRAND,'') AS BRAND , IFNULL(MAKER,'') AS MAKER , IFNULL(COUPON,'') AS COUPON , IFNULL(MCOUPON,'') AS MCOUPON , IFNULL(PCARD,'') AS PCARD , IFNULL(POINT,'') AS POINT , IFNULL(DELIV,'') AS DELIV , IFNULL(DELIVTERM,'') AS DELIVTERM , IFNULL(RATING,'') AS RATING , IFNULL(REVCT,'') AS REVCT , IFNULL(EVENT,'') AS EVENT , IFNULL(CARDDN,'') AS CARDDN , IFNULL(CARDP,'') AS CARDP , IFNULL(ADULT,'') AS ADULT , IFNULL(PUBDATE,'') AS PUBDATE FROM TB_EP_DAUM WHERE GB = 'LAST' ; -- 다음 상품평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' /*컨펌된넘*/ ;