################################################################################ #EP상품이미지 ################################################################################ DROP TABLE IF EXISTS TB_EP_GOODS_IMG RESTRICT; CREATE TABLE TB_EP_GOODS_IMG ( GOODS_CD VARCHAR(20) NOT NULL COMMENT '상품코드(상품)', IMAGE_LINK VARCHAR(200) COMMENT '이미지링크' ) COMMENT 'EP상품이미지. EP상품이미지 생성에 의한 발생'; ################################################################################ #EP상품추가이미지 ################################################################################ DROP TABLE IF EXISTS TB_EP_GOODS_ADD_IMG RESTRICT; CREATE TABLE TB_EP_GOODS_ADD_IMG ( GOODS_CD VARCHAR(20) NOT NULL COMMENT '상품코드(상품)', ADD_IMAGE_LINK VARCHAR(1000) COMMENT '추가이미지링크(|로 구분. 최대4개)' ) COMMENT 'EP상품추가이미지. EP상품추가이미지 생성에 의한 발생'; ################################################################################ #EP카드행사 ################################################################################ DROP TABLE IF EXISTS TB_EP_CARD_EVENT RESTRICT; CREATE TABLE TB_EP_CARD_EVENT ( GOODS_CD VARCHAR(20) NOT NULL COMMENT '상품코드(상품)', CARD_EVENT VARCHAR(100) COMMENT '카드행사' ) COMMENT 'EP카드행사. EP카드행사 생성에 의한 발생'; ################################################################################ #EP쿠폰 ################################################################################ DROP TABLE IF EXISTS TB_EP_COUPON RESTRICT; CREATE TABLE TB_EP_COUPON ( GOODS_CD VARCHAR(20) NOT NULL COMMENT '상품코드(상품)', COUPON VARCHAR(100) COMMENT '할인쿠폰정보' ) COMMENT 'EP쿠폰. EP쿠폰 생성에 의한 발생'; ################################################################################ #EP무이자행사 ################################################################################ DROP TABLE IF EXISTS TB_EP_INTEREST_FREE_EVENT RESTRICT; CREATE TABLE TB_EP_INTEREST_FREE_EVENT ( GOODS_CD VARCHAR(20) NOT NULL COMMENT '상품코드(상품)', INTEREST_FREE_EVENT VARCHAR(100) COMMENT '무이자행사' ) COMMENT 'EP무이자행사. EP무이자행사 생성에 의한 발생'; ################################################################################ #EP네이버 ################################################################################ DROP TABLE IF EXISTS TB_EP_NAVER RESTRICT; CREATE TABLE TB_EP_NAVER ( GB VARCHAR(4) NOT NULL COMMENT '구분(PREV:이전, CURR:현재)', ID VARCHAR(20) NOT NULL COMMENT '상품코드(상품)', TITLE VARCHAR(100) COMMENT '상품명([브랜드명]+상품명+상품코드 형식)', PRICE_PC INT UNSIGNED COMMENT 'PC가격', PRICE_MOBILE INT UNSIGNED COMMENT '모바일가격', NORMAL_PRICE INT UNSIGNED COMMENT '정상가', LINK VARCHAR(200) COMMENT 'PC상품상세URL', MOBILE_LINK VARCHAR(200) COMMENT '모바일상품상세URL', IMAGE_LINK VARCHAR(200) COMMENT '이미지링크', ADD_IMAGE_LINK VARCHAR(1000) COMMENT '추가이미지링크(|로 구분. 최대4개)', CATEGORY_NAME1 VARCHAR(50) COMMENT '카테고리명1', CATEGORY_NAME2 VARCHAR(50) COMMENT '카테고리명2', CATEGORY_NAME3 VARCHAR(50) COMMENT '카테고리명3', CATEGORY_NAME4 VARCHAR(50) COMMENT '카테고리명4', NAVER_CATEGORY VARCHAR(20) COMMENT '네이버카테고리(EP카테고리)', GOODS_GB_NM VARCHAR(10) COMMENT '상품구분명(공통코드G073)', IMPORT_FLAG VARCHAR(1) COMMENT '해외구매대행여부', PARALLEL_IMPORT VARCHAR(1) COMMENT '병행수입여부', ORDER_MADE VARCHAR(1) COMMENT '주문제작여부', PRODUCT_FLAG VARCHAR(10) COMMENT '예약판매', ADULT VARCHAR(1) COMMENT '미성년자구매불가상품여부', MANUFACTURE_DEFINE_NUMBER VARCHAR(20) COMMENT '제품코드(품번)', BRAND VARCHAR(50) COMMENT '브랜드명', MAKER VARCHAR(50) COMMENT '제조사', ORIGIN VARCHAR(50) COMMENT '원산지', CARD_EVENT VARCHAR(100) COMMENT '카드행사(카드즉시할인 등록된 기준으로 적용. 카드명^카드적용가|카드명^카드적용가... 형식)', EVENT_WORDS VARCHAR(100) COMMENT '상품타이틀명(세일링문구)', COUPON VARCHAR(100) COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)', PARTNER_COUPON_DOWNLOAD VARCHAR(1) COMMENT '쿠폰다운로드필요여부', INTEREST_FREE_EVENT VARCHAR(100) COMMENT '무이자행사(카드명^개월수-개월수|카드명^개월수-개월수... 형식)', POINT VARCHAR(50) COMMENT '포인트정보(스타일포인트^포인트액 형식)', SEARCH_TAG VARCHAR(100) COMMENT '검색태그', MINIMUM_PURCHASE_QUANTITY VARCHAR(10) COMMENT '최소구매수량', REVIEW_COUNT INT UNSIGNED COMMENT '상품평수', SHIPPING INT UNSIGNED COMMENT '배송료', AGE_GROUP VARCHAR(10) COMMENT '나이(베이비->유아, 키즈여아/키즈남아/키즈공용->아동, 주니어남아/주니어여아/주니어공용->청소년, 성인)', GENDER VARCHAR(10) COMMENT '성별(남성/여성/남여공용)', GOODS_STAT VARCHAR(20) COMMENT '상품상태(공통코드G008)', SELF_MALL_YN CHAR(1) COMMENT '자사몰노출여부', SELL_STDT DATETIME COMMENT '판매시작일시', SELL_EDDT DATETIME COMMENT '판매종료일시', ADULT_YN CHAR(1) COMMENT '성인용품여부', UPD_GB VARCHAR(1) COMMENT '업데이트구분(I:신규상품, U:기존상품중업데이트된상품 또는 품절되었다가다시서비스되는상품, D:품절상품)', UPDATE_TIME VARCHAR(20) COMMENT '업데이트시간(YYYY-MM-DD HH:MM:SS 형식)' ) COMMENT 'EP네이버. EP네이버 생성에 의한 발생'; CREATE UNIQUE INDEX IX_EP_NAVER_01 ON TB_EP_NAVER ( GB, ID ); ################################################################################ #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, ID ); -- SET GROUP_CONCAT_MAX_LEN = 102400; /* TsbGoodsEp.truncateEpGoodsImage */ TRUNCATE TABLE TB_EP_GOODS_IMG; /* TsbGoodsEp.createEpGoodsImage *(32초) */ INSERT INTO TB_EP_GOODS_IMG ( GOODS_CD , IMAGE_LINK ) SELECT G.GOODS_CD , CONCAT('https://image.istyle24.com/Upload/ProductImage/',GI.SYS_IMG_NM) AS IMAGE_LINK FROM TB_GOODS G , TB_GOODS_IMG GI WHERE G.GOODS_CD = GI.GOODS_CD AND IF(G.SELF_GOODS_YN = 'N','XX',G.MAIN_COLOR_CD) = GI.COLOR_CD AND GI.DEFAULT_IMG_YN = 'Y' ; SELECT COUNT(*) FROM TB_EP_GOODS_IMG; /* TsbGoodsEp.truncateEpGoodsAddImage */ TRUNCATE TABLE TB_EP_GOODS_ADD_IMG; /* TsbGoodsEp.createEpGoodsAddImage (24초) */ INSERT INTO TB_EP_GOODS_ADD_IMG ( GOODS_CD , ADD_IMAGE_LINK ) SELECT GOODS_CD , GROUP_CONCAT(CONCAT('https://image.istyle24.com/Upload/ProductImage/',SYS_IMG_NM) SEPARATOR '|') AS ADD_IMAGE_LINK FROM (SELECT GI.GOODS_CD , GI.SYS_IMG_NM , RANK() OVER(PARTITION BY GI.GOODS_CD ORDER BY GI.DISP_ORD, GI.SYS_IMG_NM) AS RK FROM TB_GOODS G , TB_GOODS_IMG GI WHERE G.GOODS_CD = GI.GOODS_CD AND IF(G.SELF_GOODS_YN = 'N','XX',G.MAIN_COLOR_CD) = GI.COLOR_CD AND GI.DEFAULT_IMG_YN = 'N' ) Z WHERE RK <= 4 GROUP BY GOODS_CD ; SELECT COUNT(*) FROM TB_EP_GOODS_ADD_IMG; /* TsbGoodsEp.truncateEpCardEvent */ TRUNCATE TABLE TB_EP_CARD_EVENT; /* TsbGoodsEp.createEpCardEvent (1초) */ INSERT INTO TB_EP_CARD_EVENT ( GOODS_CD , CARD_EVENT ) SELECT G.GOODS_CD , GROUP_CONCAT(DISTINCT CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^', (G.CURR_PRICE - CASE WHEN CPC.DC_WAY = 'G240_10' /*정액할인*/ THEN CPC.DC_VAL ELSE /*정율할인*/ CAST(G.CURR_PRICE * CPC.DC_VAL / 100 AS SIGNED INTEGER) END)) SEPARATOR '|') AS CARD_EVENT FROM TB_CARD_PROMOTION CP , TB_CARD_PROMOTION_CONDITION CPC , TB_CARD_PROMOTION_TARGET CPT , ( SELECT G.GOODS_CD , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS CURR_PRICE FROM TB_GOODS G , TB_GOODS_BENEFIT_PRICE GBP WHERE G.GOODS_CD = GBP.GOODS_CD ) G WHERE CP.CARD_PRMT_SQ = CPC.CARD_PRMT_SQ AND CPC.CARD_PRMT_CDT_SQ = CPT.CARD_PRMT_CDT_SQ AND CP.PRMT_GB = 'A' /*카드할인*/ AND CP.DC_GB = '1' /*즉시할인*/ AND CP.PRMT_STD <= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d') AND CP.PRMT_EDD >= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d') AND CP.DISP_YN = 'Y' AND CPC.MIN_PAY_AMT < G.CURR_PRICE GROUP BY G.GOODS_CD ; SELECT COUNT(*) FROM TB_EP_CARD_EVENT; /* TsbGoodsEp.truncateEpCoupon */ TRUNCATE TABLE TB_EP_COUPON; /* TsbGoodsEp.createEpCoupon (1초) */ INSERT INTO TB_EP_COUPON ( GOODS_CD , COUPON ) SELECT GOODS_CD , CONCAT(IFNULL(NORMAL_CPN,''),IF(AFLINK_CPN11 IS NULL,'',CONCAT('^',AFLINK_CPN11)),IF(AFLINK_CPN10 IS NULL,'',CONCAT('^',AFLINK_CPN10))) AS COUPON FROM ( SELECT CR.REF_VAL AS GOODS_CD , CASE WHEN IFNULL(CAC.CNT,0) = 0 THEN CONCAT(C.DC_PVAL,CASE WHEN C.DC_WAY = 'G240_10' THEN '원' ELSE '%' END) ELSE NULL END AS NORMAL_CPN /*일반쿠폰*/ , CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_10' THEN C.DC_PVAL ELSE NULL END AS AFLINK_CPN10 /*정액제휴쿠폰*/ , CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_11' THEN C.DC_PVAL ELSE NULL END AS AFLINK_CPN11 /*정율제휴쿠폰*/ , CASE WHEN IFNULL(CAC.CNT,0) = 0 THEN CASE WHEN C.DC_WAY = 'G240_10' THEN C.DC_PVAL ELSE IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * C.DC_PVAL / 100 END ELSE 0 END AS NCPN_DC_AMT /*일반쿠폰할인금액*/ , RANK() OVER(PARTITION BY CR.REF_VAL ORDER BY (CASE WHEN IFNULL(CAC.CNT,0) = 0 THEN CASE WHEN C.DC_WAY = 'G240_10' THEN C.DC_PVAL ELSE IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * C.DC_PVAL / 100 END ELSE 0 END) DESC, (CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_10' THEN C.DC_PVAL ELSE 0 END) DESC, (CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_11' THEN IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * C.DC_PVAL / 100 ELSE 0 END) DESC ) AS RK FROM TB_COUPON C INNER JOIN TB_COUPON_REFVAL CR ON C.CPN_ID = CR.CPN_ID INNER JOIN TB_GOODS G ON CR.REF_VAL = G.GOODS_CD LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD LEFT OUTER JOIN ( SELECT CPN_ID , COUNT(*) AS CNT FROM TB_COUPON_AF_CHANNEL WHERE AF_LINK_CD IN (SELECT AF_LINK_CD FROM TB_AF_LINK WHERE AF_CHANNEL = 'G053_02' /*네이버*/ AND USE_YN = 'Y' ) GROUP BY CPN_ID ) CAC ON C.CPN_ID = CAC.CPN_ID WHERE C.SITE_CD = 'G000_10' AND C.CPN_STAT = 'G232_11' /*진행쿠폰*/ AND C.CPN_TYPE IN ('G230_11','G230_20') /*상품쿠폰,주문서쿠폰*/ AND ( (C.PD_GB = 'D' AND NOW() < DATE_ADD(NOW(), INTERVAL C.AVAIL_DAYS DAY)) OR (C.PD_GB = 'P' AND NOW() BETWEEN C.AVAIL_STDT AND C.AVAIL_EDDT) ) AND C.APPLY_SCOPE = 'I' /*개별쿠폰*/ AND CR.CPN_TYPE IN ('G230_11','G230_20') /*상품쿠폰,주문서쿠폰*/ AND CR.CPN_TARGET = 'G260_10' /*상품쿠폰*/ AND CR.DEL_YN = 'N' AND NOT EXISTS (SELECT 1 FROM TB_COUPON_REFVAL WHERE CPN_ID = CR.CPN_ID AND CPN_TARGET = 'G260_14' /*제외상품*/ AND REF_VAL = CR.REF_VAL AND DEL_YN = 'N' ) ) Z WHERE RK = 1 AND ( NORMAL_CPN IS NOT NULL OR AFLINK_CPN10 IS NOT NULL OR AFLINK_CPN11 IS NOT NULL ) ; SELECT COUNT(*) FROM TB_EP_COUPON; /* TsbGoodsEp.truncateEpInterestFreeEvent */ TRUNCATE TABLE TB_EP_INTEREST_FREE_EVENT; /* TsbGoodsEp.createEpInterestFreeEvent (1초) */ INSERT INTO TB_EP_INTEREST_FREE_EVENT ( GOODS_CD , INTEREST_FREE_EVENT ) SELECT G.GOODS_CD , GROUP_CONCAT(DISTINCT CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^', CPC.MIN_NO_ITRT,'~',CPC.MAX_NO_ITRT) SEPARATOR '|') AS INTEREST_FREE_EVENT /*무이자이벤트정보*/ FROM TB_CARD_PROMOTION CP , TB_CARD_PROMOTION_CONDITION CPC , TB_CARD_PROMOTION_TARGET CPT , ( SELECT G.GOODS_CD , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS CURR_PRICE FROM TB_GOODS G , TB_GOODS_BENEFIT_PRICE GBP WHERE G.GOODS_CD = GBP.GOODS_CD ) G WHERE CP.CARD_PRMT_SQ = CPC.CARD_PRMT_SQ AND CPC.CARD_PRMT_CDT_SQ = CPT.CARD_PRMT_CDT_SQ AND CP.PRMT_GB = 'B' /*무이자할부*/ AND CP.PRMT_STD <= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d') AND CP.PRMT_EDD >= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d') AND CP.DISP_YN = 'Y' AND CPC.MIN_PAY_AMT < G.CURR_PRICE GROUP BY G.GOODS_CD ; SELECT COUNT(*) FROM TB_EP_INTEREST_FREE_EVENT; /* TsbGoodsEp.deleteEpNaver */ DELETE FROM TB_EP_NAVER WHERE GB = 'PREV'; /* TsbGoodsEp.createPreviousEpNaver */ INSERT INTO TB_EP_NAVER ( GB , ID , TITLE , PRICE_PC , PRICE_MOBILE , NORMAL_PRICE , LINK , MOBILE_LINK , IMAGE_LINK , ADD_IMAGE_LINK , CATEGORY_NAME1 , CATEGORY_NAME2 , CATEGORY_NAME3 , CATEGORY_NAME4 , NAVER_CATEGORY , GOODS_GB_NM , IMPORT_FLAG , PARALLEL_IMPORT , ORDER_MADE , PRODUCT_FLAG , ADULT , MANUFACTURE_DEFINE_NUMBER , BRAND , MAKER , ORIGIN , CARD_EVENT , EVENT_WORDS , COUPON , PARTNER_COUPON_DOWNLOAD , INTEREST_FREE_EVENT , POINT , SEARCH_TAG , MINIMUM_PURCHASE_QUANTITY , REVIEW_COUNT , SHIPPING , AGE_GROUP , GENDER , UPD_GB , UPDATE_TIME ) SELECT 'PREV' AS GB , ID , TITLE , PRICE_PC , PRICE_MOBILE , NORMAL_PRICE , LINK , MOBILE_LINK , IMAGE_LINK , ADD_IMAGE_LINK , CATEGORY_NAME1 , CATEGORY_NAME2 , CATEGORY_NAME3 , CATEGORY_NAME4 , NAVER_CATEGORY , GOODS_GB_NM , IMPORT_FLAG , PARALLEL_IMPORT , ORDER_MADE , PRODUCT_FLAG , ADULT , MANUFACTURE_DEFINE_NUMBER , BRAND , MAKER , ORIGIN , CARD_EVENT , EVENT_WORDS , COUPON , PARTNER_COUPON_DOWNLOAD , INTEREST_FREE_EVENT , POINT , SEARCH_TAG , MINIMUM_PURCHASE_QUANTITY , REVIEW_COUNT , SHIPPING , AGE_GROUP , GENDER , UPD_GB , UPDATE_TIME FROM TB_EP_NAVER WHERE GB = 'LAST' ; SELECT COUNT(*) FROM TB_EP_NAVER WHERE GB = 'PREV'; /* TsbGoodsEp.deleteEpNaver */ DELETE FROM TB_EP_NAVER WHERE GB = 'CURR'; /* TsbGoodsEp.createEpNaver (2분 27초) */ INSERT INTO TB_EP_NAVER ( GB , ID , TITLE , PRICE_PC , PRICE_MOBILE , NORMAL_PRICE , LINK , MOBILE_LINK , IMAGE_LINK , ADD_IMAGE_LINK , CATEGORY_NAME1 , CATEGORY_NAME2 , CATEGORY_NAME3 , CATEGORY_NAME4 , NAVER_CATEGORY , GOODS_GB_NM , IMPORT_FLAG , PARALLEL_IMPORT , ORDER_MADE , PRODUCT_FLAG , ADULT , MANUFACTURE_DEFINE_NUMBER , BRAND , MAKER , ORIGIN , CARD_EVENT , EVENT_WORDS , COUPON , PARTNER_COUPON_DOWNLOAD , INTEREST_FREE_EVENT , POINT , SEARCH_TAG , MINIMUM_PURCHASE_QUANTITY , REVIEW_COUNT , SHIPPING , AGE_GROUP , GENDER , UPD_GB , UPDATE_TIME ) SELECT 'CURR' AS GB /*구분(PREV:이전, CURR:현재)*/ , G.GOODS_CD AS ID /*상품ID*/ , SUBSTRING(CONCAT('[',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 TITLE /*상품명*/ , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS PRICE_PC /*즉시할인이적용된가격*/ , IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS PRICE_MOBILE /*즉시할인이적용된가격*/ , G.LIST_PRICE AS NORMAL_PRICE /*정상가*/ , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',G.GOODS_CD) AS LINK /*PC상세URL*/ , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',G.GOODS_CD) AS MOBILE_LINK /*모바일상세URL*/ , GI.IMAGE_LINK /*이미지URL*/ , GAI.ADD_IMAGE_LINK /*추가이미지URL(4개까지. |로 구분)*/ , SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1) AS CATEGORY_NAME1 /*카테고리명1*/ , 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 CATEGORY_NAME2 /*카테고리명2*/ , 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 CATEGORY_NAME3 /*카테고리명3*/ , 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 CATEGORY_NAME4 /*카테고리명4*/ , EC.EP_CATE_CD AS NAVER_CATEGORY /*네이버카테고리*/ , CC1.CD_NM AS GOODS_GB_NM /*상품구분명*/ , G.FOREIGN_BUY_YN AS IMPORT_FLAG /*해외구매대행여부*/ , G.PARALLEL_IMPORT_YN AS PARALLEL_IMPORT /*병행수입여부*/ , G.ORDER_MADE_YN AS ORDER_MADE /*주문제작상품여부*/ , IF(GRS.GOODS_CD IS NULL,NULL,'예약판매') AS PRODUCT_FLAG /*예약판매*/ , G.ADULT_YN AS ADULT /*성인여부*/ , G.GOODS_NUM AS MANUFACTURE_DEFINE_NUMBER /*제품코드*/ , IF(B.DISP_NM_LANG = 'EN',B.BRAND_ENM,B.BRAND_KNM) AS BRAND /*브랜드명*/ , SUBSTRING(GNI.NI_CONTENT,1,50) AS MAKER /*제조사*/ , CC2.CD_NM AS ORIGIN /*원산지*/ , CE.CARD_EVENT /*카드행사*/ , G.GOODS_TNM AS EVENT_WORDS /*상품셀링문구*/ , TEC.COUPON /*쿠폰*/ , IF(TEC.COUPON IS NULL,NULL,'Y') AS PARTNER_COUPON_DOWNLOAD /*쿠폰다운로드여부*/ , IFE.INTEREST_FREE_EVENT /*무이자행사*/ , CONCAT('스타일포인트','^', CAST(IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * G.PNT_PRATE AS SIGNED INTEGER) ) AS PNT /*포인트*/ , SUBSTRING(CONCAT(IFNULL(G.GOODS_SNM,''), CASE WHEN LENGTH(IFNULL(G.GOODS_SNM1,'')) = 0 THEN '' ELSE '|' END, IFNULL(G.GOODS_SNM1,'') ),1,100) AS SEARCH_TAG /*검색태그*/ , CASE WHEN G.MIN_ORD_QTY > 0 THEN G.MIN_ORD_QTY ELSE NULL END AS MINIMUM_PURCHASE_QUANTITY /*최소구매금액*/ , IFNULL(GS.REVIEW_REG_CNT,0) AS REVIEW_COUNT /*상품평수*/ , 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 SHIPPING /*배송료*/ , CASE WHEN SUBSTRING(I.ITEMKIND_CD,1,2) = '07' THEN '유아' WHEN SUBSTRING(I.ITEMKIND_CD,1,2) IN ('08','09','10') THEN '아동' WHEN SUBSTRING(I.ITEMKIND_CD,1,2) IN ('11','12','13') THEN '청소년' ELSE '성인' END AS AGE_GROUP /*나이*/ , CASE WHEN G.SEX_GB = 'G007_F' THEN '여성' WHEN G.SEX_GB = 'G007_M' THEN '남성' ELSE '남여공용' END AS GENDER /*성별*/ , 'I' AS UPD_GB /*변경구분*/ , DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S') AS UPDATE_TIME /*변경일시*/ 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 -2 HOUR) /*요약EP생성시사용*/ ; SELECT COUNT(*) FROM TB_EP_NAVER WHERE GB = 'CURR'; DELETE FROM TB_EP_NAVER WHERE GB = 'LAST'; -- 최종 네이버 EP 생성 (55초) INSERT INTO TB_EP_NAVER ( GB , ID , TITLE , PRICE_PC , PRICE_MOBILE , NORMAL_PRICE , LINK , MOBILE_LINK , IMAGE_LINK , ADD_IMAGE_LINK , CATEGORY_NAME1 , CATEGORY_NAME2 , CATEGORY_NAME3 , CATEGORY_NAME4 , NAVER_CATEGORY , GOODS_GB_NM , IMPORT_FLAG , PARALLEL_IMPORT , ORDER_MADE , PRODUCT_FLAG , ADULT , MANUFACTURE_DEFINE_NUMBER , BRAND , MAKER , ORIGIN , CARD_EVENT , EVENT_WORDS , COUPON , PARTNER_COUPON_DOWNLOAD , INTEREST_FREE_EVENT , POINT , SEARCH_TAG , MINIMUM_PURCHASE_QUANTITY , REVIEW_COUNT , SHIPPING , AGE_GROUP , GENDER , GOODS_STAT , SELF_MALL_YN , SELL_STDT , SELL_EDDT , UPD_GB , UPDATE_TIME ) SELECT 'LAST' AS GB , ID , TITLE , PRICE_PC , PRICE_MOBILE , NORMAL_PRICE , LINK , MOBILE_LINK , IMAGE_LINK , ADD_IMAGE_LINK , CATEGORY_NAME1 , CATEGORY_NAME2 , CATEGORY_NAME3 , CATEGORY_NAME4 , NAVER_CATEGORY , GOODS_GB_NM , IMPORT_FLAG , PARALLEL_IMPORT , ORDER_MADE , PRODUCT_FLAG , ADULT , MANUFACTURE_DEFINE_NUMBER , BRAND , MAKER , ORIGIN , CARD_EVENT , EVENT_WORDS , COUPON , PARTNER_COUPON_DOWNLOAD , INTEREST_FREE_EVENT , POINT , SEARCH_TAG , MINIMUM_PURCHASE_QUANTITY , REVIEW_COUNT , SHIPPING , AGE_GROUP , GENDER , GOODS_STAT , SELF_MALL_YN , SELL_STDT , SELL_EDDT , UPD_GB , DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S') AS UPDATE_TIME /*변경일시*/ FROM ( /*신규 목록*/ SELECT ID , TITLE , PRICE_PC , PRICE_MOBILE , NORMAL_PRICE , LINK , MOBILE_LINK , IMAGE_LINK , ADD_IMAGE_LINK , CATEGORY_NAME1 , CATEGORY_NAME2 , CATEGORY_NAME3 , CATEGORY_NAME4 , NAVER_CATEGORY , GOODS_GB_NM , IMPORT_FLAG , PARALLEL_IMPORT , ORDER_MADE , PRODUCT_FLAG , ADULT , MANUFACTURE_DEFINE_NUMBER , BRAND , MAKER , ORIGIN , CARD_EVENT , EVENT_WORDS , COUPON , PARTNER_COUPON_DOWNLOAD , INTEREST_FREE_EVENT , POINT , SEARCH_TAG , MINIMUM_PURCHASE_QUANTITY , REVIEW_COUNT , SHIPPING , AGE_GROUP , GENDER , GOODS_STAT , SELF_MALL_YN , SELL_STDT , SELL_EDDT , 'I' AS UPD_GB /*변경구분*/ FROM TB_EP_NAVER X WHERE GB = 'CURR' /*현재데이터*/ /*요약EP*/ # AND NOT EXISTS (SELECT 1 # FROM TB_EP_NAVER # WHERE GB = 'PREV' /*이전백업데이터*/ # AND ID = X.ID # ) # UNION ALL # -- 변경 목록 # SELECT X.ID # , X.TITLE # , X.PRICE_PC # , X.PRICE_MOBILE # , X.NORMAL_PRICE # , X.LINK # , X.MOBILE_LINK # , X.IMAGE_LINK # , X.ADD_IMAGE_LINK # , X.CATEGORY_NAME1 # , X.CATEGORY_NAME2 # , X.CATEGORY_NAME3 # , X.CATEGORY_NAME4 # , X.NAVER_CATEGORY # , X.GOODS_GB_NM # , X.IMPORT_FLAG # , X.PARALLEL_IMPORT # , X.ORDER_MADE # , X.PRODUCT_FLAG # , X.ADULT # , X.MANUFACTURE_DEFINE_NUMBER # , X.BRAND # , X.MAKER # , X.ORIGIN # , X.CARD_EVENT # , X.EVENT_WORDS # , X.COUPON # , X.PARTNER_COUPON_DOWNLOAD # , X.INTEREST_FREE_EVENT # , X.POINT # , X.SEARCH_TAG # , X.MINIMUM_PURCHASE_QUANTITY # , X.REVIEW_COUNT # , X.SHIPPING # , X.AGE_GROUP # , X.GENDER # , X.GOODS_STAT # , X.SELF_MALL_YN # , X.SELL_STDT # , X.SELL_EDDT # , 'U' AS UPD_GB /*변경구분*/ # FROM TB_EP_NAVER X # , TB_EP_NAVER Y # WHERE X.ID = Y.ID # AND X.GB = 'CURR' /*현재데이터*/ # AND Y.GB = 'PREV' /*이전백업데이터*/ # AND ( # X.PRICE_PC != X.PRICE_PC OR # X.PRICE_MOBILE != X.PRICE_MOBILE OR # X.NORMAL_PRICE != X.NORMAL_PRICE OR # X.LINK != X.LINK OR # X.MOBILE_LINK != X.MOBILE_LINK OR # X.IMAGE_LINK != X.IMAGE_LINK OR # X.ADD_IMAGE_LINK != X.ADD_IMAGE_LINK OR # X.CATEGORY_NAME1 != X.CATEGORY_NAME1 OR # X.CATEGORY_NAME2 != X.CATEGORY_NAME2 OR # X.CATEGORY_NAME3 != X.CATEGORY_NAME3 OR # X.CATEGORY_NAME4 != X.CATEGORY_NAME4 OR # X.NAVER_CATEGORY != X.NAVER_CATEGORY OR # X.GOODS_GB_NM != X.GOODS_GB_NM OR # X.IMPORT_FLAG != X.IMPORT_FLAG OR # X.PARALLEL_IMPORT != X.PARALLEL_IMPORT OR # X.ORDER_MADE != X.ORDER_MADE OR # X.PRODUCT_FLAG != X.PRODUCT_FLAG OR # X.ADULT != X.ADULT OR # X.MANUFACTURE_DEFINE_NUMBER != X.MANUFACTURE_DEFINE_NUMBER OR # X.BRAND != X.BRAND OR # X.MAKER != X.MAKER OR # X.ORIGIN != X.ORIGIN OR # X.CARD_EVENT != X.CARD_EVENT OR # X.EVENT_WORDS != X.EVENT_WORDS OR # X.COUPON != X.COUPON OR # X.PARTNER_COUPON_DOWNLOAD != X.PARTNER_COUPON_DOWNLOAD OR # X.INTEREST_FREE_EVENT != X.INTEREST_FREE_EVENT OR # X.POINT != X.POINT OR # X.SEARCH_TAG != X.SEARCH_TAG OR # X.MINIMUM_PURCHASE_QUANTITY != X.MINIMUM_PURCHASE_QUANTITY OR # X.REVIEW_COUNT != X.REVIEW_COUNT OR # X.SHIPPING != X.SHIPPING OR # X.AGE_GROUP != X.AGE_GROUP OR # X.GENDER != X.GENDER # ) # UNION ALL # -- 삭제된 목록 # SELECT X.ID # , X.TITLE # , X.PRICE_PC # , X.PRICE_MOBILE # , X.NORMAL_PRICE # , X.LINK # , X.MOBILE_LINK # , X.IMAGE_LINK # , X.ADD_IMAGE_LINK # , X.CATEGORY_NAME1 # , X.CATEGORY_NAME2 # , X.CATEGORY_NAME3 # , X.CATEGORY_NAME4 # , X.NAVER_CATEGORY # , X.GOODS_GB_NM # , X.IMPORT_FLAG # , X.PARALLEL_IMPORT # , X.ORDER_MADE # , X.PRODUCT_FLAG # , X.ADULT # , X.MANUFACTURE_DEFINE_NUMBER # , X.BRAND # , X.MAKER # , X.ORIGIN # , X.CARD_EVENT # , X.EVENT_WORDS # , X.COUPON # , X.PARTNER_COUPON_DOWNLOAD # , X.INTEREST_FREE_EVENT # , X.POINT # , X.SEARCH_TAG # , X.MINIMUM_PURCHASE_QUANTITY # , X.REVIEW_COUNT # , X.SHIPPING # , X.AGE_GROUP # , X.GENDER # , X.GOODS_STAT # , X.SELF_MALL_YN # , X.SELL_STDT # , X.SELL_EDDT # , 'D' AS UPD_GB /*변경구분*/ # FROM TB_EP_NAVER X # , TB_EP_NAVER 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 ; SELECT COUNT(*) FROM TB_EP_NAVER WHERE GB = 'LAST'; /* TsbGoodsEp.getNaverAllEpList */ SELECT ID , TITLE , PRICE_PC , PRICE_MOBILE , NORMAL_PRICE , LINK , MOBILE_LINK , IMAGE_LINK , ADD_IMAGE_LINK , CATEGORY_NAME1 , CATEGORY_NAME2 , CATEGORY_NAME3 , CATEGORY_NAME4 , NAVER_CATEGORY , GOODS_GB_NM , IMPORT_FLAG , PARALLEL_IMPORT , ORDER_MADE , PRODUCT_FLAG , ADULT , MANUFACTURE_DEFINE_NUMBER , BRAND , MAKER , ORIGIN , CARD_EVENT , EVENT_WORDS , COUPON , PARTNER_COUPON_DOWNLOAD , INTEREST_FREE_EVENT , POINT , SEARCH_TAG , MINIMUM_PURCHASE_QUANTITY , REVIEW_COUNT , SHIPPING , AGE_GROUP , GENDER , UPD_GB , UPDATE_TIME FROM TB_EP_NAVER WHERE GB = 'LAST' ;