style24.doc/산출물/4.구축/데이터처리/네이버EP생성_20210614_2.sql

################################################################################
#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 <![CDATA[<]]> 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'
;