style24.doc/산출물/4.구축/데이터처리/11.상품혜택생성(TB_GOODS_BENEFIT).sql

TRUNCATE TABLE TB_GOODS_BENEFIT_TMP;

-- 쿠폰할인상품 생성
INSERT INTO TB_GOODS_BENEFIT_TMP (
       GOODS_CD
     , BENEFIT_GB
)
SELECT G.GOODS_CD
     , '10'       AS BENEFIT_GB /*쿠폰할인*/
FROM   TB_GOODS G
     , TB_COUPON CP
WHERE  CP.SITE_CD = 'G000_10' /*전시사이트*/
AND    CP.CPN_STAT = 'G232_11' /*쿠폰인 진행중인 쿠폰만*/
AND    CP.DOWN_ABL_YN = 'Y' /*상품상세 다운로드 가능여부*/
AND    NOW() <= IF(CP.PD_GB = 'D',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y-%m-%d %H:%i:%S'),CP.AVAIL_EDDT)
AND    NOW() BETWEEN CP.DOWN_STDT AND CP.DOWN_EDDT
AND    CP.CPN_TYPE IN ('G230_11','G230_20','G230_30') /*상품쿠폰,주문서쿠폰,배송비쿠폰*/
AND    (SELECT COUNT(1)
        FROM   TB_COUPON_CUST_GBN
        WHERE  CPN_ID = CP.CPN_ID
        AND    USABLE_CUST_GB IN ('G100_10') /*사용가능고객구분:일반*/
       ) >= 1
AND    CP.BUY_LIMIT_AMT <= G.CURR_PRICE  /*최소주문금액*/
AND    CP.DC_PVAL > 0
AND    IF(CP.TOT_PUB_LIMIT_QTY = 0,9999999999,CP.TOT_PUB_LIMIT_QTY) > (SELECT COUNT(1)
                                                                       FROM   TB_CUST_COUPON
                                                                       WHERE  CPN_ID = CP.CPN_ID
                                                                      ) /*총발행제한수*/
AND   (
       (CP.APPLY_SCOPE = 'A' )
       OR  /* 적용대상:상품*/
       (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1)
                                  FROM   TB_COUPON_REFVAL
                                  WHERE  CPN_ID = CP.CPN_ID
                                  AND    CPN_TARGET = 'G260_10'
                                  AND    DEL_YN = 'N'
                                  AND    REF_VAL = G.GOODS_CD
                                 ) > 0
       )
       OR /* 적용대상:카테고리*/
       (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1)
                                  FROM   TB_COUPON_REFVAL
                                  WHERE  CPN_ID = CP.CPN_ID
                                  AND    CPN_TARGET = 'G260_11'
                                  AND    DEL_YN = 'N'
                                  AND    IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB
                                  AND    IFNULL(REF_BRAND_CD,G.BRAND_CD) = G.BRAND_CD
                                  AND    REF_VAL IN (SELECT DISTINCT
                                                            X.CATE_NO
                                                     FROM   (
                                                             SELECT LEAF_CATE_NO
                                                                  , CATE_LVL
                                                                  , CATE_NO
                                                             FROM   (
                                                                     SELECT LEAF_CATE_NO
                                                                          , CASE WHEN X = 1 THEN 'CATE1_NO' /*컬럼과 매핑(컬럼명)*/
                                                                                 WHEN X = 2 THEN 'CATE2_NO'
                                                                                 WHEN X = 3 THEN 'CATE3_NO'
                                                                                 WHEN X = 4 THEN 'CATE4_NO'
                                                                                 ELSE  'CATE5_NO'
                                                                            END AS CATE_LVL
                                                                          , CASE WHEN X = 1 THEN CATE1_NO /*컬럼과 매핑(컬럼 데이터)*/
                                                                                 WHEN X = 2 THEN CATE2_NO
                                                                                 WHEN X = 3 THEN CATE3_NO
                                                                                 WHEN X = 4 THEN CATE4_NO
                                                                                 ELSE  CATE5_NO
                                                                            END AS CATE_NO
                                                                     FROM   (SELECT LEAF_CATE_NO
                                                                                  , CATE1_NO
                                                                                  , CATE2_NO
                                                                                  , CATE3_NO
                                                                                  , CATE4_NO
                                                                                  , CATE5_NO
                                                                             FROM   TB_CATE_4SRCH
                                                                             WHERE  SITE_CD = 'G000_10'
                                                                             AND    CATE_TYPE = 'G031_10'
                                                                            ) A
                                                                          , (
                                                                             -- UNPIVOT 컬럼 수 만큼 선언
                                                                             SELECT 1 AS X
                                                                             UNION ALL SELECT 2 AS X
                                                                             UNION ALL SELECT 3 AS X
                                                                             UNION ALL SELECT 4 AS X
                                                                             UNION ALL SELECT 5 AS X
                                                                            ) B
                                                                     ORDER  BY LEAF_CATE_NO, CATE_LVL
                                                                    ) K
                                                             WHERE  CATE_NO IS NOT NULL
                                                            ) X
                                                          , (
                                                             SELECT A.CATE_NO
                                                                  , A.GOODS_CD
                                                             FROM   TB_CATE_GOODS A
                                                             WHERE GOODS_CD = G.GOODS_CD
                                                            ) Y
                                                     WHERE  X.LEAF_CATE_NO = Y.CATE_NO
                                                    )
                                 ) >0
       )
       OR /* 적용대상:브랜드*/
       (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1)
                                  FROM   TB_COUPON_REFVAL
                                  WHERE  CPN_ID = CP.CPN_ID
                                  AND    CPN_TARGET = 'G260_12'
                                  AND    DEL_YN = 'N'
                                  AND    REF_VAL = G.BRAND_CD
                                  AND    IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB
                                 ) >0
       )
       OR /* 적용대상:공급업체*/
       (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1)
                                  FROM   TB_COUPON_REFVAL
                                  WHERE  CPN_ID = CP.CPN_ID
                                  AND    CPN_TARGET = 'G260_13'
                                  AND    DEL_YN = 'N'
                                  AND    REF_VAL = G.SUPPLY_COMP_CD
                                 ) >0
       )
      )
AND   NOT EXISTS (SELECT 1
                  FROM   TB_COUPON_REFVAL SCPR
                  WHERE  SCPR.CPN_ID = CP.CPN_ID
                  AND    SCPR.REF_VAL = G.GOODS_CD
                  AND    SCPR.DEL_YN = 'N' /*삭제안된넘*/
                  AND    SCPR.CPN_TARGET = 'G260_14' /* 제외상품*/
                 )
AND   NOT EXISTS (SELECT 1
                  FROM   TB_COUPON_BAN_GOODS
                  WHERE  GOODS_CD = G.GOODS_CD
                  AND    DEL_YN = 'N'
                 )
AND NOT EXISTS (
		SELECT 1
		FROM TB_RANDOM_COUPON RC
		WHERE 1=1 
		AND RC.CPN_ID = CP.CPN_ID
	)
AND NOT EXISTS (
		SELECT 1
		FROM TB_FIRST_COME_COUPON FCC
		WHERE 1=1 
		AND FCC.CPN_ID = CP.CPN_ID
	)				 
GROUP  BY G.GOODS_CD
HAVING COUNT(*) > 0
;

-- 상품혜택임시 - 무료배송상품 생성
INSERT INTO TB_GOODS_BENEFIT_TMP (
       GOODS_CD
     , BENEFIT_GB
)
SELECT GOODS_CD
     , '20'     AS BENEFIT_GB /*무료배송*/
FROM   (
        SELECT G.GOODS_CD
             , DFP.MIN_ORD_AMT
             , FN_GET_BENEFIT_PRICE('P',G.GOODS_CD,G.CURR_PRICE,'G100_10') AS CURR_PRICE
        FROM   TB_GOODS G
             , TB_GOODS_STOCK GS
             , TB_DELV_FEE_POLICY DFP
        WHERE  G.GOODS_CD = GS.GOODS_CD
        AND    G.DELV_FEE_CD = DFP.DELV_FEE_CD
        AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
        AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
        AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
        AND    GS.STOCK_QTY > 0 /*재고있는상품*/
        AND    DFP.USE_YN = 'Y' /*사용하는배송비정책*/
       ) Z
WHERE  MIN_ORD_AMT <= CURR_PRICE
;

-- 상품혜택임시 - 사은품상품 생성
INSERT INTO TB_GOODS_BENEFIT_TMP (
       GOODS_CD
     , BENEFIT_GB
)
SELECT GOODS_CD
     , '30'       AS BENEFIT_GB
FROM   (
        SELECT G.GOODS_CD
        FROM   TB_FREEGIFT F
        INNER  JOIN TB_FREEGIFT_SECTION FS
        ON     F.FREEGIFT_SQ = FS.FREEGIFT_SQ
        INNER  JOIN TB_FREEGIFT_GOODS FGA
        ON     F.FREEGIFT_SQ = FGA.FREEGIFT_SQ
        AND    FGA.GOODS_GB = 'G800_20'
        AND    FGA.TARGET_GB = 'G260_10'
		INNER JOIN TB_FREEGIFT_VAL FGV	   
		ON     F.FREEGIFT_SQ = FGV.FREEGIFT_SQ
		AND    FGV.DEL_YN = 'N'
		AND    FGV.LEFT_QTY - FGV.ITEM_QTY > 0
        INNER  JOIN TB_GOODS G
        ON     FGA.TARGET_VAL = G.GOODS_CD
        LEFT   OUTER JOIN TB_FREEGIFT_GOODS NOT_APPLY
        ON     F.FREEGIFT_SQ = NOT_APPLY.FREEGIFT_SQ
        AND    G.GOODS_CD = NOT_APPLY.TARGET_VAL
        AND    NOT_APPLY.GOODS_GB = 'G800_30'
        AND    NOT_APPLY.TARGET_GB = 'G260_10'
        WHERE  NOT_APPLY.TARGET_VAL IS NULL
        AND    F.DEL_YN = 'N'
        AND    FS.DEL_YN = 'N'
        AND    FGA.DEL_YN = 'N'
        AND    NOW() BETWEEN F.FREEGIFT_STDT AND F.FREEGIFT_EDDT
        AND    F.SELF_YN = 'Y'
        AND    F.FREEGIFT_STAT = 'G232_11'
        GROUP  BY G.GOODS_CD
        HAVING COUNT(*) > 0

        UNION ALL

        SELECT G.GOODS_CD
        FROM   TB_FREEGIFT F
        INNER  JOIN TB_FREEGIFT_SECTION FS
        ON     F.FREEGIFT_SQ = FS.FREEGIFT_SQ
        INNER  JOIN TB_FREEGIFT_GOODS_APPLY BRAND_FGA
        ON     F.FREEGIFT_SQ = BRAND_FGA.FREEGIFT_SQ
        AND    BRAND_FGA.TARGET_GB = 'G260_12' /*브랜드*/
        INNER  JOIN TB_FREEGIFT_GOODS_APPLY COMPANY_FGA
        ON     F.FREEGIFT_SQ = COMPANY_FGA.FREEGIFT_SQ
        AND    COMPANY_FGA.TARGET_GB = 'G260_13' /*업체*/
		INNER JOIN TB_FREEGIFT_VAL FGV	   
		ON     F.FREEGIFT_SQ = FGV.FREEGIFT_SQ
		AND    FGV.DEL_YN = 'N'
		AND    FGV.LEFT_QTY - FGV.ITEM_QTY > 0
        INNER  JOIN TB_GOODS G
        ON     BRAND_FGA.TARGET_VAL = G.BRAND_CD
        AND    COMPANY_FGA.TARGET_VAL = G.SUPPLY_COMP_CD
        LEFT   OUTER JOIN TB_FREEGIFT_GOODS NOT_APPLY
        ON     F.FREEGIFT_SQ = NOT_APPLY.FREEGIFT_SQ
        AND    G.GOODS_CD = NOT_APPLY.TARGET_VAL
        AND    NOT_APPLY.GOODS_GB = 'G800_30'
        WHERE  NOT_APPLY.TARGET_VAL IS NULL
        AND    F.DEL_YN = 'N'
        AND    FS.DEL_YN = 'N'
        AND    NOW() BETWEEN F.FREEGIFT_STDT AND F.FREEGIFT_EDDT
        AND    F.SELF_YN = 'Y'
        AND    F.FREEGIFT_STAT = 'G232_11'
        GROUP  BY G.GOODS_CD
        HAVING COUNT(*) > 0
       ) Z
GROUP  BY GOODS_CD
;

-- 상품혜택임시 - 신상상품 생성
INSERT INTO TB_GOODS_BENEFIT_TMP (
       GOODS_CD
     , BENEFIT_GB
)
SELECT G.GOODS_CD
     , '40'       AS BENEFIT_GB /*신상*/
FROM   TB_GOODS G
     , TB_GOODS_STOCK GS
WHERE  G.GOODS_CD = GS.GOODS_CD
AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
AND    GS.STOCK_QTY > 0 /*재고있는상품*/
AND    G.FORMAL_GB = 'G009_10' /*정상상품(=신상)*/
;

-- 상품혜택임시 - 총알배송 생성
INSERT INTO TB_GOODS_BENEFIT_TMP (
       GOODS_CD
     , BENEFIT_GB
)
SELECT G.GOODS_CD
     , '50'       AS BENEFIT_GB /*총알배송*/
FROM   TB_GOODS G
     , TB_GOODS_STOCK GS
WHERE  G.GOODS_CD = GS.GOODS_CD
AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
AND    G.SELF_GOODS_YN = 'Y' /*자사상품만*/
AND    GS.STOCK_QTY > 0 /*재고있는상품*/
AND    NOT EXISTS (SELECT 1
                   FROM   TB_GOODS_SHOT_DELV_SKIP
                   WHERE  GOODS_CD = G.GOODS_CD
                  )
;

-- 상품혜택 삭제
TRUNCATE TABLE TB_GOODS_BENEFIT;

-- 상품혜택 생성
INSERT INTO TB_GOODS_BENEFIT (
       GOODS_CD
     , BENEFIT_GB
     , REG_NO
     , REG_DT
)
SELECT DISTINCT
       GOODS_CD
     , BENEFIT_GB
     , 0          AS REG_NO
     , NOW()      AS REG_DT
FROM   (
        SELECT GOODS_CD
             , BENEFIT_GB
        FROM   TB_GOODS_BENEFIT_TMP
        UNION ALL
        SELECT GC.GOODS_CD
             , G.BENEFIT_GB
        FROM   TB_GOODS_BENEFIT_TMP G
             , TB_GOODS_COMPOSE GC
        WHERE  G.GOODS_CD = GC.COMPS_GOODS_CD
       ) Z
;