################################################################################ #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_REG_CNT 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 ); SET GROUP_CONCAT_MAX_LEN = 102400; /* TsbGoodsEp.truncateEpGoodsImage */ TRUNCATE TABLE TB_EP_GOODS_IMG; /* TsbGoodsEp.createEpGoodsImage */ 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 -- , MIN(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' --GROUP BY G.GOODS_CD ; SELECT GOODS_CD, COUNT(*) AS CNT FROM TB_EP_GOODS_IMG GROUP BY GOODS_CD HAVING COUNT(*) > 1 ; /* TsbGoodsEp.truncateEpGoodsAddImage */ TRUNCATE TABLE TB_EP_GOODS_ADD_IMG; /* TsbGoodsEp.createEpGoodsAddImage */ 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 ; /* TsbGoodsEp.truncateEpCardEvent */ TRUNCATE TABLE TB_EP_CARD_EVENT; /* TsbGoodsEp.createEpCardEvent */ 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 ; /* TsbGoodsEp.truncateEpCoupon */ TRUNCATE TABLE TB_EP_COUPON; /* TsbGoodsEp.createEpCoupon */ 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((SELECT COUNT(*) FROM TB_COUPON_AF_CHANNEL WHERE CPN_ID = C.CPN_ID ),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((SELECT COUNT(*) FROM TB_COUPON_AF_CHANNEL WHERE CPN_ID = C.CPN_ID AND AF_LINK_CD IN (SELECT AF_LINK_CD FROM TB_AF_LINK WHERE AF_CHANNEL = 'G053_02' /*네이버*/ AND USE_YN = 'Y' ) ),0) > 0 AND C.DC_WAY = 'G240_10' THEN C.DC_PVAL ELSE NULL END AS AFLINK_CPN10 /*정액제휴쿠폰*/ , CASE WHEN IFNULL((SELECT COUNT(*) FROM TB_COUPON_AF_CHANNEL WHERE CPN_ID = C.CPN_ID AND AF_LINK_CD IN (SELECT AF_LINK_CD FROM TB_AF_LINK WHERE AF_CHANNEL = 'G053_02' /*네이버*/ AND USE_YN = 'Y' ) ),0) > 0 AND C.DC_WAY = 'G240_11' THEN C.DC_PVAL ELSE NULL END AS AFLINK_CPN11 /*정율제휴쿠폰*/ , CASE WHEN IFNULL((SELECT COUNT(*) FROM TB_COUPON_AF_CHANNEL WHERE CPN_ID = C.CPN_ID ),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((SELECT COUNT(*) FROM TB_COUPON_AF_CHANNEL WHERE CPN_ID = C.CPN_ID ),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((SELECT COUNT(*) FROM TB_COUPON_AF_CHANNEL WHERE CPN_ID = C.CPN_ID AND AF_LINK_CD IN (SELECT AF_LINK_CD FROM TB_AF_LINK WHERE AF_CHANNEL = 'G053_02' /*네이버*/ AND USE_YN = 'Y' ) ),0) > 0 AND C.DC_WAY = 'G240_10' THEN C.DC_PVAL ELSE 0 END) DESC, (CASE WHEN IFNULL((SELECT COUNT(*) FROM TB_COUPON_AF_CHANNEL WHERE CPN_ID = C.CPN_ID AND AF_LINK_CD IN (SELECT AF_LINK_CD FROM TB_AF_LINK WHERE AF_CHANNEL = 'G053_02' /*네이버*/ AND USE_YN = 'Y' ) ),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 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 ) ; /* TsbGoodsEp.truncateEpInterestFreeEvent */ TRUNCATE TABLE TB_EP_INTEREST_FREE_EVENT; /* TsbGoodsEp.createEpInterestFreeEvent */ 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 ; /* 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_REG_CNT , 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_REG_CNT , SHIPPING , AGE_GROUP , GENDER , UPD_GB , UPDATE_TIME FROM TB_EP_NAVER WHERE GB = 'LAST' ; /* TsbGoodsEp.deleteEpNaver */ DELETE FROM TB_EP_NAVER WHERE GB = 'CURR'; /* TsbGoodsEp.createEpNaver */ 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_REG_CNT , 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_REG_CNT /*상품평수*/ , 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생성시사용*/ ; -- 최종 네이버 EP 생성 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_REG_CNT , 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_REG_CNT , 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_REG_CNT , 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_REG_CNT , 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_REG_CNT != X.REVIEW_REG_CNT OR X.SHIPPING != X.SHIPPING OR X.AGE_GROUP != X.AGE_GROUP OR X.GENDER != X.GENDER OR ) 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_REG_CNT , 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 ; /* 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_REG_CNT , SHIPPING , AGE_GROUP , GENDER , UPD_GB , UPDATE_TIME FROM TB_EP_NAVER WHERE GB = 'LAST' ;