style24.doc/산출물/4.구축/데이터처리/매출반영.sql

-- TRUNCATE TABLE TB_HANSAE_SALES;

-- 한세MK 전일자 매출반영 데이터
INSERT INTO TB_HANSAE_SALES (
       ERP_GB
     , DT_SALE
     , TP_SALE
     , CD_STYLE
     , CD_COLOR
     , CD_SIZE
     , BRAND_CD
     , QT_SALE
     , AM_ACSALE
     , DS_REMARK
     , REG_DT
)
SELECT 'hsmk'      AS ERP_GB
     , DT_SALE
     , TP_SALE
     , CD_STYLE
     , CD_COLOR
     , CD_SIZE
     , BRAND_CD
     , QT_SALE
     , AM_ACSALE
     , ''          AS DS_REMARK
     , NOW()       AS REG_DT
FROM   (
        SELECT DATE_FORMAT(O.PAY_DT,'%Y%m%d')          AS DT_SALE  /*판매일자*/
             , CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN '1'
                    ELSE '2'
               END                                     AS TP_SALE  /*판매구분(1:판매, 2:환불)*/
             , SUBSTRING(ODIH.SKU_MODEL_NO,1,10)       AS CD_STYLE /*ERP스타일코드*/
             , SUBSTRING(ODIH.SKU_MODEL_NO,11,2)       AS CD_COLOR /*ERP색상코드*/
             , TRIM(SUBSTRING(ODIH.SKU_MODEL_NO,13,3)) AS CD_SIZE  /*ERP사이즈코드*/
             , G.BRAND_CD                                          /*브랜드코드*/
             , SUM(CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY /*판매-배송중*/
                             ELSE ODIH.CNCL_RTN_QTY /*환입-반품완료,환입-교환완료*/
                        END AS SIGNED INT))            AS QT_SALE  /*판매수량*/
             , SUM(CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' /*판매-배송중*/ THEN
                                 ((ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY) * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                                                 IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                                             ELSE
                                                                                 ODIH.ITEM_PRICE
                                                                        END))
                             ELSE /*환입-반품완료,환입-교환완료*/
                                 (ODIH.CNCL_RTN_QTY * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                                IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                            ELSE
                                                                ODIH.ITEM_PRICE
                                                       END))
                        END AS SIGNED INT))            AS AM_ACSALE /*실판매금액*/
        FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
        INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
        INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
        INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
        INNER JOIN TB_GOODS G ON ODIH.ITEM_CD = G.GOODS_CD
        LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
        WHERE  1 = 1
        AND    ODIH.REG_DT >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) /*전일자기준*/
        AND    ODIH.REG_DT < CURRENT_DATE() /*전일자기준*/
        AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중, 환입-반품완료, 환입-교환완료*/
        AND    OD.SUPPLY_COMP_CD = 5 /*한세MK*/
        AND    SC.DISTRIBUTION_GB IN ('G065_10','G065_11','G065_12') /*자사*/
        GROUP  BY DATE_FORMAT(O.PAY_DT,'%Y%m%d')
                , CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN '1'
                       ELSE '2'
                  END
                , SUBSTRING(ODIH.SKU_MODEL_NO,1,10)
                , SUBSTRING(ODIH.SKU_MODEL_NO,11,2)
                , TRIM(SUBSTRING(ODIH.SKU_MODEL_NO,13,3))
                , G.BRAND_CD
       ) ODIH
WHERE  QT_SALE <> 0
AND    NOT EXISTS (SELECT 1
                   FROM   TB_HANSAE_SALES
                   WHERE  ERP_GB = 'hsmk'
                   AND    DT_SALE = ODIH.DT_SALE
                   AND    TP_SALE = ODIH.TP_SALE
                   AND    CD_STYLE = ODIH.CD_STYLE
                   AND    CD_COLOR = ODIH.CD_COLOR
                   AND    CD_SIZE = ODIH.CD_SIZE
                  )
;

-- 한세드림 전일자 매출반영 데이터
INSERT INTO TB_HANSAE_SALES (
       ERP_GB
     , DT_SALE
     , TP_SALE
     , CD_STYLE
     , CD_COLOR
     , CD_SIZE
     , BRAND_CD
     , QT_SALE
     , AM_ACSALE
     , DS_REMARK
     , REG_DT
)
SELECT 'hsdr'     AS ERP_GB
     , DT_SALE
     , TP_SALE
     , CD_STYLE
     , CD_COLOR
     , CD_SIZE
     , BRAND_CD
     , QT_SALE
     , AM_ACSALE
     , ''         AS DS_REMARK
     , NOW()      AS REG_DT
FROM   (
        SELECT DATE_FORMAT(O.PAY_DT,'%Y%m%d')          AS DT_SALE  /*판매일자*/
             , CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN '1'
                    ELSE '2'
               END                                     AS TP_SALE  /*판매구분(1:판매, 2:환불)*/
             , SUBSTRING(ODIH.SKU_MODEL_NO,1,10)       AS CD_STYLE /*ERP스타일코드*/
             , SUBSTRING(ODIH.SKU_MODEL_NO,11,2)       AS CD_COLOR /*ERP색상코드*/
             , TRIM(SUBSTRING(ODIH.SKU_MODEL_NO,13,3)) AS CD_SIZE  /*ERP사이즈코드*/
             , G.BRAND_CD                                          /*브랜드코드*/
             , SUM(CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY /*판매-배송중*/
                             ELSE ODIH.CNCL_RTN_QTY /*환입-반품완료,환입-교환완료*/
                        END AS SIGNED INT))            AS QT_SALE  /*판매수량*/
             , SUM(CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' /*판매-배송중*/ THEN
                                 ((ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY) * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                                                 IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                                             ELSE
                                                                                 ODIH.ITEM_PRICE
                                                                        END))
                             ELSE /*환입-반품완료,환입-교환완료*/
                                 (ODIH.CNCL_RTN_QTY * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
                                                                IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
                                                            ELSE
                                                                ODIH.ITEM_PRICE
                                                       END))
                        END AS SIGNED INT))            AS AM_ACSALE /*실판매금액*/
        FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
        INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
        INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
        INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
        INNER JOIN TB_GOODS G ON ODIH.ITEM_CD = G.GOODS_CD
        LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
        WHERE  1 = 1
        AND    ODIH.REG_DT >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) /*전일자기준*/
        AND    ODIH.REG_DT < CURRENT_DATE() /*전일자기준*/
        AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중, 환입-반품완료, 환입-교환완료*/
        AND    OD.SUPPLY_COMP_CD = 3 /*한세드림*/
        AND    SC.DISTRIBUTION_GB IN ('G065_10','G065_11','G065_12') /*자사*/
        GROUP  BY DATE_FORMAT(O.PAY_DT,'%Y%m%d')
                , CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN '1'
                       ELSE '2'
                  END
                , SUBSTRING(ODIH.SKU_MODEL_NO,1,10)
                , SUBSTRING(ODIH.SKU_MODEL_NO,11,2)
                , TRIM(SUBSTRING(ODIH.SKU_MODEL_NO,13,3))
                , G.BRAND_CD
       ) ODIH
WHERE  QT_SALE <> 0
AND    NOT EXISTS (SELECT 1
                   FROM   TB_HANSAE_SALES
                   WHERE  ERP_GB = 'hsdr'
                   AND    DT_SALE = ODIH.DT_SALE
                   AND    TP_SALE = ODIH.TP_SALE
                   AND    CD_STYLE = ODIH.CD_STYLE
                   AND    CD_COLOR = ODIH.CD_COLOR
                   AND    CD_SIZE = ODIH.CD_SIZE
                  )
;

-- 업체별 매출반영 건수와 성공건수 조회
SELECT ERP_GB
     , COUNT(*)                                                 AS UPLOAD_CNT
     , SUM(CASE WHEN LENGTH(CD_SALEBILL) > 0 THEN 1 ELSE 0 END) AS SUCC_CNT
FROM   TB_HANSAE_SALES
WHERE  DT_SALE = '20210901'
GROUP  BY ERP_GB
;

-- 위의 UPLOAD_CNT와 SUCC_CNT의 값이 다를 경우 재전송을 위한 데이터 조회
SELECT ERP_GB      /*ERP구분(hsmk:한세MK, hsdr:한세드림*/
     , DT_SALE     /*판매일자(YYYYMMDD 형식)*/
     , TP_SALE     /*판매구분(1:판매, 2:환불)*/
     , CD_STYLE    /*스타일코드*/
     , CD_COLOR    /*색상코드*/
     , CD_SIZE     /*사이즈코드*/
     , BRAND_CD    /*온라인브랜드코드*/
     , QT_SALE     /*판매수량*/
     , AM_ACSALE   /*실판매금액(수량*단가*/
     , DS_REMARK   /*비고*/
     , NO_IF       /*I/F번호*/
     , CD_SALEBILL /*판매전표번호. 성공시 값 있음*/
     , DS_ERROR    /*실패오류*/
FROM   TB_HANSAE_SALES
WHERE  DT_SALE = '20210901'
AND    DS_ERROR IS NOT NULL /*오류발생건*/
;