style24.doc/산출물/4.구축/데이터처리/EP_생성로직.sql

-- 네이버 전체 EP
SELECT G.GOODS_CD                                             AS ID
     , CONCAT('[',CASE WHEN B.DISP_NM_LANG = 'EN' THEN B.BRAND_ENM ELSE B.BRAND_KNM END,'] ',
              G.GOODS_NM,
              IFNULL(G.GOODS_NUM,''))                         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 /*정상가*/
     , G.GOODS_STAT
     , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',
              G.GOODS_CD)                                     AS LINK
     , NULL                                                   AS MOBILE_LINK  /*PC URL과 다를 경우. 동일함으로 NULL로 처리*/
     , CONCAT('https://image.istyle24.com/Upload/ProductImage/',
              (SELECT SYS_IMG_NM
               FROM   TB_GOODS_IMG
               WHERE  GOODS_CD = G.GOODS_CD
               AND    COLOR_CD = IF(G.SELF_GOODS_YN = 'N','XX',G.MAIN_COLOR_CD)
               AND    DEFAULT_IMG_YN = 'Y'
               LIMIT 1))                                      AS IMAGE_LINK
     , (SELECT GROUP_CONCAT(CONCAT('https://image.istyle24.com/Upload/ProductImage/',SYS_IMG_NM) SEPARATOR '|')
        FROM   (SELECT DISTINCT SYS_IMG_NM
                FROM   TB_GOODS_IMG
                WHERE  GOODS_CD = G.GOODS_CD
                AND    DEFAULT_IMG_YN = 'N'
                LIMIT 4
               ) Z
       )                                                      AS ADD_IMAGE_LINK /*이미지4개까지(|로 구분)*/
     , SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1)                   AS CATEGORY_NAME1
     , 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
     , 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
     , 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
--      , NULL                                                   AS NAVER_PRODUCT_ID
     , FN_GET_CODE_NM('G073',G.GOODS_GB)                      AS CONDITION1
     , G.FOREIGN_BUY_YN                                       AS IMPORT_FLAG     /*해외구매대행여부*/
     , G.PARALLEL_IMPORT_YN                                   AS PARALLEL_IMPORT /*병행수입여부*/
     , G.ORDER_MADE_YN                                        AS ORDER_MADE      /*주문제작상품여부*/
     , CASE WHEN IFNULL((SELECT COUNT(*)
                         FROM   TB_GOODS_RES_SELL
                         WHERE  GOODS_CD = G.GOODS_CD
                         AND    DELV_RES_DT >= NOW()
                         AND    USE_YN = 'Y'
                        ),0) = 0 THEN NULL ELSE '예약판매' END  AS PRODUCT_FLAG /*예약판매*/
     , G.ADULT_YN                                             AS ADULT           /*성인여부*/
--      , NULL                                                   AS GOODS_TYPE      /*상품유형. DP/HS/DF/MA(백화점/홈쇼핑/면세점/마트). 해당없음으로 NULL로 처리*/
--      , NULL                                                   AS BARCODE         /*바코드. 데이터 없어 NULL로 처리*/
     , G.GOODS_NUM                                            AS MANUFACTURE_DEFINE_NUMBER /*제조번호*/
--      , CASE WHEN G.SELF_GOODS_YN = 'Y' THEN
--                 G.GOODS_NUM
--             ELSE
--                 G.SUPPLY_GOODS_CD
--        END                                                    AS MODEL_NUMBER
     , CASE WHEN B.DISP_NM_LANG = 'EN' THEN
                B.BRAND_ENM
            ELSE
                B.BRAND_KNM
       END                                                    AS BRAND           /*브랜드명*/
     , (SELECT NI_CONTENT
        FROM   TB_GOODS_NOTI_INFO
        WHERE  GOODS_CD = G.GOODS_CD
        AND    NI_ITEM_CD = 'G005_005'
        LIMIT 1)                                              AS MAKER            /*제조사*/
     , IFNULL(FN_GET_CODE_NM('G076',G.ORIGIN_CD),G.ORIGIN_CD) AS ORIGIN          /*원산지*/
     , (SELECT GROUP_CONCAT(DISTINCT
                            CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^',
                                   (IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE)
                                    -
                                    CASE WHEN CPC.DC_WAY = 'G240_10' /*정액할인*/ THEN CPC.DC_VAL
                                         ELSE /*정율할인*/ CAST(IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * CPC.DC_VAL / 100 AS SIGNED INTEGER)
                                    END))
                            SEPARATOR '|')
        FROM   TB_CARD_PROMOTION CP
             , TB_CARD_PROMOTION_CONDITION CPC
             , TB_CARD_PROMOTION_TARGET CPT
        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 < IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY
       )                                                      AS CARD_EVENT      /*카드행사. 카드즉시할인 등록된 기준으로 적용. 카드명^카드적용가 형식*/
     , G.GOODS_TNM                                            AS EVENT_WORDS     /*상품셀링문구*/
     , (SELECT CONCAT(IFNULL(NORMAL_CPN,''),'^',IFNULL(AFLINK_CPN,''))
        FROM   (
                SELECT 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
                                        ),0) > 0 AND C.DC_WAY = 'G240_11' THEN
                                C.DC_PVAL
                            ELSE
                                NULL
                       END                   AS AFLINK_CPN /*제휴쿠폰*/
                     , 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 40000 * C.DC_PVAL / 100
                                END
                            ELSE
                                0
                       END                   AS NCPN_DC_AMT /*일반쿠폰할인금액*/
                     , CASE WHEN IFNULL((SELECT COUNT(*)
                                         FROM   TB_COUPON_AF_CHANNEL
                                         WHERE  CPN_ID = C.CPN_ID
                                        ),0) > 0 AND C.DC_WAY = 'G240_11' THEN
                                40000 * C.DC_PVAL / 100
                            ELSE
                                0
                       END                   AS ACPN_DC_AMT /*제휴쿠폰할인금액*/
                FROM   TB_COUPON C
                     , TB_COUPON_REFVAL CR
                WHERE  C.CPN_ID = CR.CPN_ID
                AND    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.REF_VAL = G.GOODS_CD
                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'
                                  )
                ORDER  BY NCPN_DC_AMT DESC, ACPN_DC_AMT DESC
                LIMIT 1
               ) Z
       )                                                      AS COUPON
--      , NULL AS PARTNER_COUPON_DOWNLOAD /*COUPON 값이 있을 경우 Y로 표기*/
     , (SELECT GROUP_CONCAT(DISTINCT
                            CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^',
                                   CPC.MIN_NO_ITRT,'~',CPC.MAX_NO_ITRT)
                            SEPARATOR '|')
        FROM   TB_CARD_PROMOTION CP
             , TB_CARD_PROMOTION_CONDITION CPC
             , TB_CARD_PROMOTION_TARGET CPT
        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 < IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY
       )                                                      AS 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             /*포인트. 스타일포인트^포인트금액 형식으로 표기*/
--      , NULL                                                   AS INSTALLATION_COSTS /*별도설치비유무*/
     , CONCAT(IFNULL(G.GOODS_SNM,''),
              CASE WHEN LENGTH(IFNULL(G.GOODS_SNM1,'')) = 0 THEN
                       ''
                   ELSE
                       '|'
              END,
              IFNULL(G.GOODS_SNM1,'')
             )                                                AS SEARCH_TAG
--      , NULL                                                   AS GROUP_ID   /*없음*/
--      , NULL                                                   AS VENDOR_ID  /*몰별 상품아이디이나 별도 저장하고 있지 않아 사용안함*/
--      , NULL                                                   AS COORDI_ID  /*스타일링 추천에 세팅된 상품코드 노출. 상품코드|상품코드... 형식*/
     , CASE WHEN G.MIN_ORD_QTY > 0 THEN
                G.MIN_ORD_QTY
            ELSE
                ''
       END                                                    AS MINIMUM_PURCHASE_QUANTITY
     , IFNULL(GS.REVIEW_REG_CNT,0)                            AS REVIEW_REG_CNT
     , IFNULL((SELECT CASE WHEN DELV_FEE_CRITE = 'G078_20' /*무료*/ THEN
                               0
                           ELSE /*조건부무료 또는 유료*/
                               CASE WHEN MIN_ORD_AMT <= IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY THEN
                                        0
                                    ELSE
                                        DELV_FEE
                               END
                      END
               FROM   TB_DELV_FEE_POLICY
               WHERE  SUPPLY_COMP_CD = G.SUPPLY_COMP_CD
               AND    DELV_FEE_CD = G.DELV_FEE_CD
               AND    USE_YN = 'Y'
              ),0)                                            AS SHIPPING
--      , NULL                                                   AS DELIVERY_GRADE  /*현재없음*/
--      , NULL                                                   AS DELIVERY_DETAIL /*현재없음*/
--      , NULL                                                   AS ATTR            /*속성-별도없음*/
--      , NULL                                                   AS OPTION_DETAIL   /*옵션별목록-별도제공안함*/
--      , NULL                                                   AS SELLER_ID       /*해당없음. 오픈마켓/몰인몰일때만 사용*/
     , 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
     , CASE WHEN IFNULL((SELECT COUNT(*)
                         FROM   TB_GOODS_STOCK
                         WHERE  GOODS_CD = G.GOODS_CD
                         AND    STOCK_QTY = 0
                        ),0) = 0 THEN 'D'
            ELSE 'I/U'
       END                                                    AS CLASS /*전체색인:I, 부분색인:전체색인 데이터랑 비교해서 없으면 I, 있는데 변경되었으면 U, 품절: D)*/
     , 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
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
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.SELF_GOODS_YN = 'Y'
-- AND    G.GOODS_CD IN (
-- '10770353',
-- 'VNS11QJM49',
-- 'AOW13QDM76',
-- '10770353',
-- 'STYS00000002',
-- 'L191TJ571P',
-- 'MMF1BAKP40',
-- 'K193SH040P',
-- 'B191DS100M',
-- 'CNF0GABL01',
-- 'STYS00000009',
-- 'STYS00000028',
-- '10781128',
-- '10778030',
-- 'ANW11APT65',
-- '10781128'
-- )
;

SELECT GROUP_CONCAT(CONCAT('https://image.istyle24.com/Upload/ProductImage/',SYS_IMG_NM) SEPARATOR '|')
FROM   (SELECT DISTINCT SYS_IMG_NM
        FROM   TB_GOODS_IMG
        WHERE  GOODS_CD = 'A82F-DP511A'
        AND    DEFAULT_IMG_YN = 'N'
        LIMIT 4
       ) Z
;

SELECT NI_CONTENT
FROM   TB_GOODS_NOTI_INFO
WHERE  GOODS_CD = 'A82F-DP511A'
AND    NI_ITEM_CD = 'G005_005'
;

SELECT DISTINCT SUBSTRING(ITEMKIND_CD,1,2)
FROM   TB_ITEMKIND
WHERE  ITEMKIND_NM LIKE '주니어공용%'
;

-- 여성, 남성, 유니, 스포츠, 골프, 언더웨어, 베이비(07), 키즈여아(08), 키즈남아(09), 키즈공용(10), 주니어여아(11), 주니어남아(12), 주니어공용(13),

-- 카드행사- 카드즉시할인 등록된 기준으로 적용함
-- 전송 PRICE기준이 카드할인의 허들을 초과할경우, 카드할인의 기준으로 전송함
-- 카드명^카드적용가 로 전송함
-- 카드 구분값은 33번 (interest_free_event) 참조

SELECT GROUP_CONCAT(DISTINCT
                    CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^',
                           (40000 - CASE WHEN CPC.DC_WAY = 'G240_10' /*정액할인*/ THEN CPC.DC_VAL
                                         ELSE /*정율할인*/ CAST(40000 * CPC.DC_VAL / 100 AS SIGNED INTEGER)
                                    END))
                    SEPARATOR '|') AS AAAAAAA
FROM   TB_CARD_PROMOTION CP
     , TB_CARD_PROMOTION_CONDITION CPC
     , TB_CARD_PROMOTION_TARGET CPT
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 <= 40000
;

SELECT *
FROM   TB_CARD_PROMOTION
;

SELECT GROUP_CONCAT(DISTINCT
                    CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^',
                           CPC.MIN_NO_ITRT,'~',CPC.MAX_NO_ITRT)
                    SEPARATOR '|') AS AAAAAAA
FROM   TB_CARD_PROMOTION CP
     , TB_CARD_PROMOTION_CONDITION CPC
     , TB_CARD_PROMOTION_TARGET CPT
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 < 40000
;

SELECT CASE WHEN DELV_FEE_CRITE = 'G078_20' /*무료*/ THEN
                0
            ELSE /*조건부무료 또는 유료*/
                CASE WHEN MIN_ORD_AMT <= 20000 THEN 0 ELSE DELV_FEE END
       END AS DELV_FEE
FROM   TB_DELV_FEE_POLICY
WHERE  DELV_FEE_CD = '4_01'
AND    USE_YN = 'Y'
;

-- G078_10	조건부 무료
-- G078_20	무료
-- G078_30	유료

SELECT *
FROM   TB_ITEMKIND
WHERE  ITEMKIND_CD = '03180201Z'
;

SELECT CATE_NO
FROM   TB_CATE_GOODS
WHERE  BRAND_GROUP_NO = 0 /*BYITEM카테고리*/
AND    GOODS_CD = 'A82F-DP511A'
LIMIT 1
;

SELECT *
FROM   TB_CATE_4SRCH
WHERE  LEAF_CATE_NO IN (1109,1216)
;

SELECT *
FROM   TB_GOODS_IMG
;

SELECT *
FROM   TB_COMMON_CODE
WHERE  CD_GB = 'G005'
;