style24.doc/산출물/4.구축/데이터처리/매출반영_9월분_추출.sql

SELECT 'hsdr'     AS ERP_GB /*한세MK:hsmk*/
	 , 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(ODIH.REG_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 >= STR_TO_DATE('2021-09-01','%Y-%m-%d')
		AND    ODIH.REG_DT < DATE_ADD(STR_TO_DATE('2021-09-30','%Y-%m-%d'), INTERVAL 1 DAY)
		AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중, 환입-반품완료, 환입-교환완료*/
		-- AND    OD.SUPPLY_COMP_CD = 5 /*한세MK*/
		AND    OD.SUPPLY_COMP_CD = 3 /*한세드림*/
		AND    SC.DISTRIBUTION_GB IN ('G065_10','G065_11','G065_12') /*자사*/
		GROUP  BY DATE_FORMAT(ODIH.REG_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 = #{erpGb}
-- 				   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
-- 				  )
;