style24.doc/산출물/6.전개/오픈시_DB마이그레이션후_작업할_DB처리_Script.sql

-- 1.1. WMS재고이력 데이터 삭제 (2개월전 데이터)
DELETE FROM TB_WMS_STOCK_HST
WHERE  JOBDATE < DATE_ADD(DATE_FORMAT(NOW(), '%Y%m%d'), INTERVAL -2 MONTH)
;

-- 1.2. WMS재고이력 생성
INSERT INTO TB_WMS_STOCK_HST (
       JOBDATE
     , PRODUCT_NO      
     , SKU_CODE        
     , LOC_QTY         
     , PICKING_QTY     
     , EOUT_PICKING_QTY
)
SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%S') AS JOBDATE
     , PRODUCT_NO      
     , SKU_CODE        
     , LOC_QTY         
     , PICKING_QTY     
     , EOUT_PICKING_QTY
FROM   TB_WMS_STOCK
;

-- 2.1. WMS 재고 truncate
TRUNCATE TABLE TB_WMS_STOCK;

-- 2.2. WMS 재고 수신 (MSSQL 접속. 배치로 처리해야 함)
SELECT PRODUCT_NO 
     , SKU_CODE
     , LOC_QTY
     , PICKING_QTY
     , EOUT_PICKING_QTY
FROM   (
        SELECT PRODUCT_NO
             , SKU_CODE
             , SUM(LOC_QTY)          AS LOC_QTY
             , SUM(PICKING_QTY)      AS PICKING_QTY 
             , SUM(EOUT_PICKING_QTY) AS EOUT_PICKING_QTY
        FROM   iSTYLE24_WMS.DBO.VW_STOCK_BY_LOCCODE
        GROUP  BY PRODUCT_NO, SKU_CODE
       ) Z
WHERE  1 = 1
AND    LOC_QTY - PICKING_QTY - EOUT_PICKING_QTY > 0
;

-- 3.1 매장재고 삭제
TRUNCATE TABLE TB_SHOP_STOCK;

-- 3.2. 출고처별 판매비율을 적용한 매장재고 생성
INSERT INTO TB_SHOP_STOCK (
		      SUPPLY_COMP_CD
		    , CD_SHOP          
		    , CD_STYLE         
		    , CD_COLOR         
		    , CD_SIZE          
		    , QT_STOCK         
		    , OPT_CD           
		    , OPT_CD1          
		    , STORE_STOCK_QTY  
		    , UPD_DT           
		)
		SELECT A.SUPPLY_COMP_CD
		     , A.CD_SHOP
		     , A.CD_STYLE
		     , A.CD_COLOR
		     , A.CD_SIZE
		     , A.QT_STOCK
		     , E.OPT_CD
		     , E.OPT_CD1
		     , ROUND(GREATEST(A.QT_STOCK, 0) * (IFNULL(D.STOCK_APPL_RATE, 0)/100)) AS STORE_STOCK_QTY
		     , NOW()
		FROM (SELECT S.SUPPLY_COMP_CD
		          , S.CD_SHOP
		          , S.CD_STYLE
		          , S.CD_COLOR
		          , S.CD_SIZE
		          , S.QT_STOCK
		          , M.COLOR_CD AS OPT_CD1
		     FROM TB_SHOP_GOODS_STOCK S
		     INNER JOIN TB_WMS_COLOR_MAPPING M ON S.SUPPLY_COMP_CD = M.SUPPLY_COMP_CD
		                                       AND S.CD_COLOR = M.WMS_COLOR_CD
		                                       AND M.USE_YN = 'Y') A
		INNER JOIN TB_OPTION E USE INDEX(PRIMARY) ON A.CD_STYLE = E.GOODS_CD
		                       AND A.OPT_CD1 = E.OPT_CD1
		                       AND A.CD_SIZE = E.OPT_CD2
		INNER JOIN TB_GOODS B ON A.CD_STYLE = B.GOODS_CD
		                      AND B.SELF_GOODS_YN = 'Y'
		                      AND B.ERP_STOCK_LINK_YN = 'Y'
		INNER JOIN TB_STOCK_SYNC_BASE C ON B.BRAND_CD = C.BRAND_CD
		                                AND C.STOCK_SYNC_YN = 'Y'
		                                AND A.CD_SHOP = C.DELV_LOC_CD
		INNER JOIN TB_DELIVERY_LOC D ON A.SUPPLY_COMP_CD = D.SUPPLY_COMP_CD
		                             AND A.CD_SHOP = D.DELV_LOC_CD
		                             AND D.USE_YN = 'Y'
;

-- 3.3. 매장재고이력 삭제 (2개월전 데이터)
DELETE FROM TB_SHOP_STOCK_HST
WHERE JOBDATE <![CDATA[<=]]> #{jobdate} 
;

INSERT INTO TB_SHOP_STOCK_HST(
       JOBDATE
     , SUPPLY_COMP_CD
     , CD_SHOP
     , CD_STYLE
     , CD_COLOR
     , CD_SIZE
     , QT_STOCK
     , OPT_CD
     , OPT_CD1
     , STORE_STOCK_QTY
)
SELECT #{jobdate}
      SUPPLY_COMP_CD
     , CD_SHOP
     , CD_STYLE
     , CD_COLOR
     , CD_SIZE
     , QT_STOCK
     , OPT_CD
     , OPT_CD1
     , STORE_STOCK_QTY
FROM TB_SHOP_STOCK
;

DELETE FROM TB_OPTION_SYNC_TMP;

INSERT INTO TB_OPTION_SYNC_TMP
		(
		       GOODS_CD
		     , OPT_CD
		     , OPT_CD1
		     , OPT_CD2
		     , SKU_MODEL_NO
		     , PRODUCT_NO
		     , PRODUCT_CODE
		     , BASE_STOCK_QTY
		     , CURR_STOCK_QTY
		     , STORE_STOCK_QTY
		     , STORE_MAX_QTY
		     , ADD_PRICE
		     , SOLDOUT_YN
		     , DISP_ORD
		     , DISP_YN
		     , ERP_STOCK_LINK_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		SELECT GOODS_CD
		     , OPT_CD
		     , OPT_CD1
		     , OPT_CD2
		     , SKU_MODEL_NO
		     , PRODUCT_NO
		     , PRODUCT_CODE
		     , 0 AS BASE_STOCK_QTY
		     , CURR_STOCK_QTY
		     , 0 AS STORE_STOCK_QTY
		     , 0 AS STORE_MAX_QTY
		     , 0 AS ADD_PRICE
		     , 'N' AS SOLDOUT_YN
		     , DISP_ORD
		     , 'Y' AS DISP_YN
		     , ERP_STOCK_LINK_YN
		     , 99999 AS REG_NO
		     , NOW() AS REG_DT
		     , 99999 AS UPD_NO
		     , NOW() AS UPD_DT
		FROM (
		      SELECT B.GOODS_CD
		           , A.SKU_CODE AS OPT_CD
		           , E.OPT_CD1
		           , E.OPT_CD2
		           , E.SKU_MODEL_NO
		           , E.PRODUCT_NO
		           , E.PRODUCT_CODE
		           , ROUND((GREATEST(A.LOC_QTY, 0) -  GREATEST(A.PICKING_QTY, 0)  - GREATEST(A.EOUT_PICKING_QTY, 0)) * (IFNULL(D.STOCK_APPL_RATE, 0)/100)) AS CURR_STOCK_QTY
		           , E.STORE_STOCK_QTY
		           , E.DISP_ORD
		           , B.ERP_STOCK_LINK_YN
		      FROM TB_WMS_STOCK A
		      INNER JOIN TB_OPTION E ON A.PRODUCT_NO = E.PRODUCT_NO
		                             AND A.SKU_CODE = E.OPT_CD
		      INNER JOIN TB_GOODS B ON E.GOODS_CD = B.GOODS_CD
		                            AND B.SELF_GOODS_YN = 'Y'
		                            AND B.ERP_STOCK_LINK_YN = 'Y'
		      INNER JOIN TB_STOCK_SYNC_BASE C ON B.BRAND_CD = C.BRAND_CD
		                                      AND C.STOCK_SYNC_YN = 'Y'
		                                      AND C.DELV_LOC_CD = 'ST0001'    -- WMS 매장코드
		      INNER JOIN TB_DELIVERY_LOC D ON B.SUPPLY_COMP_CD = D.SUPPLY_COMP_CD
		                                   AND C.DELV_LOC_CD = D.DELV_LOC_CD
		                                   AND D.USE_YN = 'Y'
		                                   AND D.DELV_LOC_CD = 'ST0001'    -- WMS 매장코드
		     ) Z
		     ;
		     
INSERT INTO TB_OPTION_SYNC_TMP
		(
		       GOODS_CD
		     , OPT_CD
		     , OPT_CD1
		     , OPT_CD2
		     , SKU_MODEL_NO
		     , PRODUCT_NO
		     , PRODUCT_CODE
		     , BASE_STOCK_QTY
		     , CURR_STOCK_QTY
		     , STORE_STOCK_QTY
		     , STORE_MAX_QTY
		     , ADD_PRICE
		     , SOLDOUT_YN
		     , DISP_ORD
		     , DISP_YN
		     , ERP_STOCK_LINK_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		SELECT GOODS_CD
		     , OPT_CD
		     , OPT_CD1
		     , OPT_CD2
		     , SKU_MODEL_NO
		     , PRODUCT_NO
		     , PRODUCT_CODE
		     , 0 AS BASE_STOCK_QTY
		     , 0 AS CURR_STOCK_QTY
		     , STORE_STOCK_QTY
		     , 0 AS STORE_MAX_QTY
		     , 0 AS ADD_PRICE
		     , 'N' AS SOLDOUT_YN
		     , DISP_ORD
		     , 'Y' AS DISP_YN
		     , ERP_STOCK_LINK_YN
		     , 99999 AS REG_NO
		     , NOW() AS REG_DT
		     , 99999 AS UPD_NO
		     , NOW() AS UPD_DT
		FROM (
		      SELECT E.GOODS_CD
		           , E.OPT_CD
		           , E.OPT_CD1
		           , E.OPT_CD2
		           , E.SKU_MODEL_NO
		           , E.PRODUCT_NO
		           , E.PRODUCT_CODE
		           , E.CURR_STOCK_QTY
		           , SUM(A.STORE_STOCK_QTY) AS STORE_STOCK_QTY
		           , B.ERP_STOCK_LINK_YN
		           , E.DISP_ORD
		      FROM TB_SHOP_STOCK A
		      INNER JOIN TB_OPTION E ON A.CD_STYLE = E.GOODS_CD
		                             AND A.OPT_CD = E.OPT_CD
		      INNER JOIN TB_GOODS B ON E.GOODS_CD = B.GOODS_CD
		                            AND B.SELF_GOODS_YN = 'Y'
		                            AND B.ERP_STOCK_LINK_YN = 'Y'
		      GROUP BY GOODS_CD , OPT_CD , OPT_CD1 , OPT_CD2 , SKU_MODEL_NO , PRODUCT_NO , PRODUCT_CODE , CURR_STOCK_QTY , DISP_ORD , ERP_STOCK_LINK_YN
		  ) Z
		ON DUPLICATE KEY UPDATE
		     STORE_STOCK_QTY = Z.STORE_STOCK_QTY
		     ;
		     
-- 입점상품 재고 동기화
-- 입점상품 SELL_QTY 목록
/* TsbGoods.getGoodsSelfNoStockList */
		SELECT A.GOODS_CD
		     , A.OPT_CD
		     , SUM(
		           (CASE A.SELL_GB WHEN '10' THEN 1
		                          WHEN '20' THEN 1
		                          ELSE -1 END) * A.SELL_QTY
		           ) AS SALE_STOCK_QTY
		FROM TB_SELL_QTY A
		   , TB_GOODS B
		WHERE A.GOODS_CD = B.GOODS_CD
		AND B.SELF_GOODS_YN = 'N'  -- 입점상품
		GROUP BY A.GOODS_CD, A.OPT_CD
		;

-- LOOP 돌면서
-- 이력생성
goodsDao.createStockHst(option);

-- 재고 변경
goodsDao.updateStock(option);

-- TB_OPTION = > TB_OPTION_SYNC 적용
TRUNCATE TABLE TB_OPTION_SYNC;

-- TB_OPTION_SYNC.CURR_STOCK_QTY, STORE_STOCK_QTY, STORE_MAX_QTY 초기화
INSERT INTO TB_OPTION_SYNC
		(
		       GOODS_CD
		     , OPT_CD
		     , OPT_CD1
		     , OPT_CD2
		     , SKU_MODEL_NO
		     , PRODUCT_NO
		     , PRODUCT_CODE
		     , BASE_STOCK_QTY
		     , CURR_STOCK_QTY
		     , STORE_STOCK_QTY
		     , STORE_MAX_QTY
		     , ADD_PRICE
		     , SOLDOUT_YN
		     , DISP_ORD
		     , DISP_YN
		     , ERP_STOCK_LINK_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		SELECT B.GOODS_CD
		     , B.OPT_CD
		     , B.OPT_CD1
		     , B.OPT_CD2
		     , B.SKU_MODEL_NO
		     , B.PRODUCT_NO
		     , B.PRODUCT_CODE
		     , B.BASE_STOCK_QTY
		     , 0 AS CURR_STOCK_QTY
		     , 0 AS STORE_STOCK_QTY
		     , 0 AS STORE_MAX_QTY
		     , B.ADD_PRICE
		     , B.SOLDOUT_YN
		     , B.DISP_ORD
		     , B.DISP_YN
		     , A.ERP_STOCK_LINK_YN
		     , B.REG_NO
		     , B.REG_DT
		     , B.UPD_NO
		     , B.UPD_DT
		FROM   TB_GOODS A, TB_OPTION B
		WHERE  A.ERP_STOCK_LINK_YN = 'Y'
		AND    A.SELF_GOODS_YN ='Y'
		AND    A.GOODS_CD = B.GOODS_CD
		;

-- TB_OPTION_SYNC_TMP => TB_OPTION_SYNC 변경적용
/* TsbGoods.saveGoodsOptionSyncSync */
		UPDATE TB_OPTION_SYNC A, (SELECT GOODS_CD
		                               , OPT_CD
		                               , CURR_STOCK_QTY
		                               , STORE_STOCK_QTY
		                               , ERP_STOCK_LINK_YN
		                          FROM TB_OPTION_SYNC_TMP
		                          WHERE ERP_STOCK_LINK_YN = 'Y'
		                          ) B
		SET A.CURR_STOCK_QTY = B.CURR_STOCK_QTY
		  , A.STORE_STOCK_QTY = B.STORE_STOCK_QTY
		  , A.UPD_NO = 99999
		  , A.UPD_DT = NOW()
		WHERE A.GOODS_CD = B.GOODS_CD
		AND A.OPT_CD = B.OPT_CD
		<![CDATA[
		AND (A.CURR_STOCK_QTY <> B.CURR_STOCK_QTY
		    OR
		    A.STORE_STOCK_QTY <> B.STORE_STOCK_QTY
		    )
		]]>
		;

-- WMS, 매장별 최대 주문수량 적용  == > 작업합시다.
-- TB_SHOP_STOCK과 TB_OPTION_SYNC.CURR_STOCK_QTY 비교하여 STORE_MAX_QTY에 적용
/* TsbGoods.updateGoodsOptionSyncSync */
		UPDATE TB_OPTION_SYNC A, (SELECT GOODS_CD
		                               , OPT_CD
		                               , STORE_MAX_QTY
		                          FROM (     
		                                SELECT GOODS_CD
		                                     , OPT_CD
		                                     , GREATEST(CURR_STOCK_QTY, QT_STOCK) AS STORE_MAX_QTY
		                                FROM (
		                                         SELECT OS.GOODS_CD
		                                              , OS.OPT_CD
		                                              , GREATEST(OS.CURR_STOCK_QTY, 0)  AS CURR_STOCK_QTY
		                                              , GREATEST(IFNULL(SS.QT_STOCK, 0), 0) AS QT_STOCK
		                                         FROM TB_OPTION_SYNC OS
		                                         LEFT OUTER JOIN TB_SHOP_STOCK SS ON OS.GOODS_CD = SS.CD_STYLE
		                                                                       AND OS.OPT_CD = SS.OPT_CD
		                                         WHERE OS.ERP_STOCK_LINK_YN = 'Y'
		                                      ) Z
		                               ) Y WHERE STORE_MAX_QTY > 0
		                           ) B
		SET A.STORE_MAX_QTY = B.STORE_MAX_QTY
		  , A.UPD_NO = 99999
		  , A.UPD_DT = NOW()
		WHERE A.GOODS_CD = B.GOODS_CD
		AND A.OPT_CD = B.OPT_CD
		<![CDATA[
		AND A.STORE_MAX_QTY <> B.STORE_MAX_QTY
		]]>
		;
		  
-- TB_OPTION_SYNC => TB_OPTION 적용
UPDATE TB_OPTION A, (SELECT GOODS_CD
		                          , OPT_CD
		                          , CURR_STOCK_QTY
		                          , STORE_STOCK_QTY
		                          , STORE_MAX_QTY
		                          , ERP_STOCK_LINK_YN
		                      FROM TB_OPTION_SYNC
		                      WHERE ERP_STOCK_LINK_YN = 'Y'
		                      ) B
		SET A.CURR_STOCK_QTY = B.CURR_STOCK_QTY
		  , A.STORE_STOCK_QTY = B.STORE_STOCK_QTY
		  , A.STORE_MAX_QTY = B.STORE_MAX_QTY
		  , A.UPD_NO = 99999
		  , A.UPD_DT = NOW()
		WHERE A.GOODS_CD = B.GOODS_CD
		AND A.OPT_CD = B.OPT_CD
		<![CDATA[
		AND (A.CURR_STOCK_QTY <> B.CURR_STOCK_QTY
		    OR
		    A.STORE_STOCK_QTY <> B.STORE_STOCK_QTY
		    OR
		    A.STORE_MAX_QTY <> B.STORE_MAX_QTY
		    )
		]]>
		;

		// 자사상품중 주문상세상태가 입금대기, 결제완료, 교환대기 건은 TB_SELL_QTY 생성
-- TB_SELL_QTY_TEMP 삭제
TRUNCATE TABLE TB_SELL_QTY_TMP;

-- TB_SELL_QTY_TEMP 생성
INSERT INTO TB_SELL_QTY_TMP (
		      GOODS_CD
		    , OPT_CD
		    , SELL_GB
		    , ORD_DTL_NO
		    , ORD_CHG_SQ
		    , AGENT_ORDER_ID
		    , EXTMALL_ORDER_ID
		    , SELL_QTY
		    , REG_NO
		    , REG_DT
		)
		SELECT GOODS_CD
		    , OPT_CD
		    , SELL_GB
		    , ORD_DTL_NO
		    , ORD_CHG_SQ
		    , AGENT_ORDER_ID
		    , EXTMALL_ORDER_ID
		    , SELL_QTY
		    , REG_NO
		    , REG_DT
		FROM TB_SELL_QTY
		;
	
-- 초기화
DELETE FROM TB_SELL_QTY;

-- TB_SELL_QTY_TEMP => TB_SELL_QTY 생성
INSERT INTO TB_SELL_QTY (
		      GOODS_CD
		    , OPT_CD
		    , SELL_GB
		    , ORD_DTL_NO
		    , ORD_CHG_SQ
		    , AGENT_ORDER_ID
		    , EXTMALL_ORDER_ID
		    , SELL_QTY
		    , REG_NO
		    , REG_DT
		)
		SELECT  GOODS_CD
		    , OPT_CD
		    , SELL_GB
		    , ORD_DTL_NO
		    , ORD_CHG_SQ
		    , AGENT_ORDER_ID
		    , EXTMALL_ORDER_ID
		    , SELL_QTY
		    , REG_NO
		    , REG_DT
		FROM TB_SELL_QTY_TMP
		WHERE ORD_DTL_NO IN (
		                     SELECT OD.ORD_DTL_NO
		                     FROM TB_ORDER_DETAIL OD
		                        , TB_ORDER_DETAIL_ITEM ODI
		                        , TB_GOODS G
		                     WHERE OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		                     AND OD.ORD_DTL_STAT IN ('G013_10','G013_20','G013_25','G013_30','G013_35','G013_40') -- 입금대기, 결제완료, 교환대기, 상품준비중,출고처지정,배송준비중
		                     AND OD.GOODS_CD = G.GOODS_CD
		                     AND G.SELF_GOODS_YN ='Y'
		                    )
		                    ;

-- TB_ORDER_DETAIL => TB_SELL_QTY 생성
INSERT INTO TB_SELL_QTY (
		      GOODS_CD
		    , OPT_CD
		    , SELL_GB
		    , ORD_DTL_NO
		    , AGENT_ORDER_ID
		    , EXTMALL_ORDER_ID
		    , SELL_QTY
		    , REG_NO
		    , REG_DT
		)
		SELECT ITEM_CD
		     , OPT_CD
		     , SELL_GB
		     , ORD_DTL_NO
		     , AGENT_ORDER_ID
		     , EXTMALL_ORDER_ID
		     , SELL_QTY
		     , 0
		     , NOW()
		FROM (
		      SELECT ODI.ITEM_CD
		           , ODI.OPT_CD
		           , '10' AS SELL_GB -- 주문건
		           , OD.ORD_DTL_NO
		           , OD.AGENT_ORDER_ID
		           , OD.EXTMALL_ORDER_ID
		           , SUM((OD.ORD_QTY - OD.CNCL_RTN_QTY) * ODI.ITEM_QTY) AS SELL_QTY
		      FROM TB_ORDER_DETAIL OD
		         , TB_ORDER_DETAIL_ITEM ODI
		      WHERE OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		      AND OD.ORD_DTL_STAT IN ('G013_10','G013_20','G013_25','G013_30','G013_35','G013_40') -- 입금대기, 결제완료, 교환대기, 상품준비중,출고처지정,배송준비중
		      AND EXISTS (SELECT 1 FROM TB_OPTION X WHERE X.GOODS_CD = ODI.ITEM_CD AND X.OPT_CD = ODI.OPT_CD)
		      GROUP BY ODI.ITEM_CD, ODI.OPT_CD, OD.ORD_DTL_NO, OD.AGENT_ORDER_ID, OD.EXTMALL_ORDER_ID
		     ) A
		WHERE NOT EXISTS (SELECT 1 
		                  FROM TB_SELL_QTY Y 
		                  WHERE Y.GOODS_CD = A.ITEM_CD 
		                  AND Y.OPT_CD = A.OPT_CD
		                  AND Y.SELL_GB = A.SELL_GB
		                  AND Y.ORD_DTL_NO = A.ORD_DTL_NO
		                  )
;