| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236 |
- /* TsaSettle.getGiftcardSettleList */
- SELECT SUM(USE_GFCD_AMT - CNCL_GFCD_AMT)
- FROM (
- SELECT CG.GFCD_NO /*상품권번호*/
- , DATE_FORMAT(CG.REG_DT,'%Y-%m-%d') AS REG_DT /*등록일*/
- , DATE_FORMAT(CG.USE_EXP_DATE,'%Y-%m-%d') AS USE_EXP_DATE /*사용만료일*/
- , CASE WHEN NOW() < CG.USE_EXP_DATE THEN 'Y' ELSE 'N' END AS AVAIL_YN /*유효여부*/
- , CG.CUST_NO /*고객번호*/
- , C.CUST_ID /*고객ID*/
- , CG.CHG_GFCD_AMT /*전환금액(=최초등록금액)*/
- , IFNULL(CGH1.USE_GFCD_AMT,0) AS USE_GFCD_AMT /*정산월사용금액*/
- , IFNULL(CGH1.CNCL_GFCD_AMT,0) AS CNCL_GFCD_AMT /*정산월취소금액*/
- FROM TB_CUST_GIFTCARD CG
- INNER JOIN TB_CUSTOMER C ON CG.CUST_NO = C.CUST_NO
- LEFT OUTER JOIN (
- /*정산월기준 사용금액과 취소금액*/
- SELECT CUST_NO
- , CUST_GFCD_SQ
- , -1 * SUM(CASE WHEN OCCUR_GB IN ('G074_12','G074_19') THEN GFCD_AMT ELSE 0 END) AS USE_GFCD_AMT /*사용금액*/
- , SUM(CASE WHEN OCCUR_GB IN ('G074_13','G074_18') THEN GFCD_AMT ELSE 0 END) AS CNCL_GFCD_AMT /*취소금액*/
- FROM TB_CUST_GIFTCARD_HST
- WHERE REG_DT >= STR_TO_DATE(CONCAT( '2021-09' /**P*/,'-01'),'%Y-%m-%d')
- AND REG_DT < DATE_ADD(STR_TO_DATE(CONCAT( '2021-09' /**P*/,'-01'),'%Y-%m-%d'), INTERVAL 1 MONTH)
- GROUP BY CUST_NO, CUST_GFCD_SQ
- ) CGH1 ON CG.CUST_NO = CGH1.CUST_NO
- AND CG.CUST_GFCD_SQ = CGH1.CUST_GFCD_SQ
- WHERE 1 = 1
- ORDER BY CG.GFCD_NO
- ) Z
- ;
- -- 236,500
- /* TsaStatistics.getDailyTradingList */
- SELECT SUM(GFCD_USE_AMT)
- FROM (
- SELECT ODIH.ORD_NO /*주문번호*/
- , ODIH.ORD_DTL_NO /*주문상세번호*/
- , ODIH.SELL_GB /*판매구분*/
- , ODIH.MALL_GB /*몰구분*/
- , ODIH.MALL_GB_NM /*몰구분명*/
- , ODIH.EXTMALL_ORDER_ID /*제휴몰주문번호*/
- , ODIH.EXTMALL_ID /*제휴몰ID*/
- , ODIH.EXTMALL_NM /*제휴몰명*/
- , ODIH.AF_LINK_CD /*제휴링크코드*/
- , ODIH.AF_LINK_NM /*제휴링크명*/
- , ODIH.AF_CHANNEL /*제휴채널코드*/
- , ODIH.AF_CHANNEL_NM /*제휴채널명*/
- , ODIH.DISTRIBUTION_GB /*유통구분*/
- , ODIH.DISTRIBUTION_GB_NM /*유통구분명*/
- , ODIH.SUPPLY_COMP_CD /*공급업체코드*/
- , ODIH.SUPPLY_COMP_NM /*공급업체명*/
- , ODIH.SUPPLY_VENDOR_CD /*공급벤더코드*/
- , ODIH.SUPPLY_VENDOR_NM /*공급벤더명*/
- , ODIH.ORD_DT /*주문일시*/
- , ODIH.OCCUR_DT /*발생일시*/
- , ODIH.BRAND_CD /*브랜드코드*/
- , ODIH.BRAND_ENM /*브랜드명*/
- , ODIH.GOODS_CD /*상품코드*/
- , ODIH.GOODS_NM /*상품명*/
- , ODIH.ITEM_CD /*단품코드*/
- , ODIH.OPT_CD1 /*옵션코드1*/
- , ODIH.OPT_CD2 /*옵션코드2*/
- , ODIH.ITEMKIND_CD /*품목코드*/
- , ODIH.ITEMKIND_NM /*품목명*/
- , SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',1) AS ITEMKIND_NM1 /*품목명1*/
- , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',1),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',2),'>',-1) THEN
- NULL
- ELSE
- SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',2),'>',-1)
- END AS ITEMKIND_NM2 /*품목명2*/
- , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',2),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',3),'>',-1) THEN
- NULL
- ELSE
- SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',3),'>',-1)
- END AS ITEMKIND_NM3 /*품목명3*/
- , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',3),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',4),'>',-1) THEN
- ''
- ELSE
- SUBSTRING_INDEX(SUBSTRING_INDEX(ODIH.ITEMKIND_NM,'>',4),'>',-1)
- END AS ITEMKIND_NM4 /*품목명4*/
- , ODIH.MALL_PRICE /*몰판매가*/
- , ODIH.SELL_PRICE /*판매가*/
- , ODIH.SELL_QTY /*판매수량*/
- , ODIH.SELL_AMT /*판매금액(ORD_AMT에는 CPN1_DC_AMT가 빠진 금액이 들어가 있음)*/
- , ODIH.CPN1_DC_AMT /*즉시사용쿠폰금액*/
- , ODIH.GOODS_CPN_DC_AMT /*상품쿠폰사용금액*/
- , ODIH.CART_CPN_DC_AMT /*주문서쿠폰사용금액*/
- , ODIH.PNT_DC_AMT /*포인트사용금액*/
- , ODIH.TMTB_DC_AMT /*다다익선할인금액*/
- , ODIH.SELF_TMTB_DC_AMT /*자사다다익선분담액*/
- , ODIH.SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
- , ODIH.GFCD_USE_AMT /*상품권사용금액*/
- , ODIH.SELF_CPN_DC_AMT /*자사쿠폰분담액*/
- , ODIH.SUPPLY_COMP_CPN_DC_AMT /*입점쿠폰분담액*/
- , CASE WHEN ODIH.SELL_QTY = 0 THEN 0
- ELSE
- (ODIH.SELL_AMT
- - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
- - ODIH.TMTB_DC_AMT
- ) / ODIH.SELL_QTY
- END AS REAL_SELL_PRICE /*실판매가*/
- , ODIH.SELL_AMT
- - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
- - ODIH.TMTB_DC_AMT AS REAL_SELL_AMT /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
- , ODIH.SELL_FEE_RATE /*판매수수료율*/
- , ROUND((ODIH.SELL_AMT
- - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
- - ODIH.TMTB_DC_AMT
- ) * (ODIH.SELL_FEE_RATE / 100),0) AS SELL_FEE_AMT /*수수료*/
- , ODIH.MD_NM /*담당MD*/
- FROM (
- SELECT ODIH.ORD_DTL_ITEM_HST_SQ /*주문상세단품이력일련번호*/
- , ODIH.ORD_NO /*주문번호*/
- , ODIH.ORD_DTL_NO /*주문상세번호*/
- , CASE WHEN ODIH.ORD_DTL_STAT = 'G720_10' THEN '판매'
- WHEN ODIH.ORD_DTL_STAT = 'G720_30' THEN '취소'
- WHEN ODIH.ORD_DTL_STAT = 'G720_40' THEN '품절취소'
- WHEN ODIH.ORD_DTL_STAT = 'G720_50' THEN '반품'
- WHEN ODIH.ORD_DTL_STAT = 'G720_60' THEN '교환'
- END AS SELL_GB /*판매구분*/
- , O.MALL_GB /*몰구분*/
- , FN_GET_CODE_NM('G011',O.MALL_GB) AS MALL_GB_NM /*몰구분명*/
- , OD.EXTMALL_ORDER_ID /*제휴몰주문번호*/
- , OD.EXTMALL_ID /*제휴몰ID*/
- , E.EXTMALL_NM /*제휴몰명*/
- , OD.AF_LINK_CD /*제휴링크코드*/
- , AL.AF_LINK_NM /*제휴링크명*/
- , AL.AF_CHANNEL /*제휴채널코드*/
- , FN_GET_CODE_NM('G053',AL.AF_CHANNEL) AS AF_CHANNEL_NM /*제휴채널명*/
- , SC.DISTRIBUTION_GB /*유통구분*/
- , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB) AS DISTRIBUTION_GB_NM /*유통구분명*/
- , OD.SUPPLY_COMP_CD /*공급업체코드*/
- , SC.SUPPLY_COMP_NM /*공급업체명*/
- , SC.SUPPLY_VENDOR_CD /*공급벤더코드*/
- , SV.SUPPLY_VENDOR_NM /*공급벤더명*/
- , DATE_FORMAT(O.PAY_DT,'%Y-%m-%d %H:%i:%S') AS ORD_DT /*주문일시*/
- , DATE_FORMAT(ODIH.REG_DT,'%Y-%m-%d %H:%i:%S') AS OCCUR_DT /*발생일시*/
- , G.BRAND_CD /*브랜드코드*/
- , B.BRAND_ENM /*브랜드명*/
- , OD.GOODS_CD /*상품코드*/
- , G.GOODS_NM /*상품명*/
- , ODIH.ITEM_CD /*단품코드*/
- , ODIH.OPT_CD1 /*옵션코드1*/
- , ODIH.OPT_CD2 /*옵션코드2*/
- , G.ITEMKIND_CD /*품목코드*/
- , I.ITEMKIND_NM /*품목명*/
- , ODIH.ITEM_PRICE AS MALL_PRICE /*몰판매가*/
- , CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
- IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
- ELSE
- ODIH.ITEM_PRICE
- END AS SELL_PRICE /*판매가(제휴몰정산가적용)*/
- , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_10' THEN 1 ELSE -1 END)
- *
- CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_10'/*판매-결제완료*/ THEN
- ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY
- ELSE /*환입-반품완료,환입-교환완료*/
- ODIH.CNCL_RTN_QTY
- END AS SIGNED INT) AS SELL_QTY /*판매수량*/
- , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_10' THEN 1 ELSE -1 END)
- *
- CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_10'/*판매-결제완료*/ 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 SELL_AMT /*판매금액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) * ODIH.CPN1_DC_AMT AS CPN1_DC_AMT /*즉시사용쿠폰금액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) * ODIH.GOODS_CPN_DC_AMT AS GOODS_CPN_DC_AMT /*상품쿠폰사용금액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) * ODIH.CART_CPN_DC_AMT AS CART_CPN_DC_AMT /*주문서쿠폰사용금액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) * ODIH.PNT_DC_AMT AS PNT_DC_AMT /*포인트사용금액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) * (ODIH.TMTB1_DC_AMT + ODIH.TMTB2_DC_AMT) AS TMTB_DC_AMT /*다다익선할인금액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) *
- (((100 - IFNULL(TB1.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB1_DC_AMT
- + ((100 - IFNULL(TB2.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB2_DC_AMT) AS SELF_TMTB_DC_AMT /*자사다다익선분담액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) *
- ((IFNULL(TB1.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB1_DC_AMT
- + (IFNULL(TB2.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB2_DC_AMT) AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) * ODIH.GFCD_USE_AMT AS GFCD_USE_AMT /*상품권사용금액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) *
- (((100 - IFNULL(CB1.BURDEN_RATE,0)) / 100) * ODIH.CPN1_DC_AMT
- + ((100 - IFNULL(CB2.BURDEN_RATE,0)) / 100) * ODIH.GOODS_CPN_DC_AMT
- + ((100 - IFNULL(CB3.BURDEN_RATE,0)) / 100) * ODIH.CART_CPN_DC_AMT) AS SELF_CPN_DC_AMT /*자사쿠폰분담액*/
- , IF(ODIH.ORD_DTL_STAT = 'G720_10',1,-1) *
- ((IFNULL(CB1.BURDEN_RATE,0) / 100) * ODIH.CPN1_DC_AMT
- + (IFNULL(CB2.BURDEN_RATE,0) / 100) * ODIH.GOODS_CPN_DC_AMT
- + (IFNULL(CB3.BURDEN_RATE,0) / 100) * ODIH.CART_CPN_DC_AMT) AS SUPPLY_COMP_CPN_DC_AMT /*입점쿠폰분담액*/
- , CASE WHEN O.MALL_GB = 'G011_10' /*자사몰*/ THEN
- OD.SELL_FEE_RATE
- ELSE /*제휴몰*/
- IFNULL(E.SELL_FEE_RATE,0)
- END AS SELL_FEE_RATE /*판매수수료율*/
- , (SELECT U.USER_NM
- FROM TB_BRAND_MD BM
- , TB_USER U
- WHERE BM.MD_NO = U.USER_NO
- AND BM.BRAND_CD = G.BRAND_CD
- AND BM.FORMAL_GB = G.FORMAL_GB
- LIMIT 1) AS MD_NM /*담당MD*/
- 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_SUPPLY_VENDOR SV ON SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
- INNER JOIN TB_GOODS G ON OD.GOODS_CD = G.GOODS_CD
- 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_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
- LEFT OUTER JOIN TB_AF_LINK AL ON OD.AF_LINK_CD = AL.AF_LINK_CD
- LEFT OUTER JOIN TB_COUPON_BURDEN CB1 ON OD.CPN1_CPN_SQ = CB1.CPN_ID
- AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD
- LEFT OUTER JOIN TB_COUPON_BURDEN CB2 ON OD.GOODS_CPN_SQ = CB2.CPN_ID
- AND OD.SUPPLY_COMP_CD = CB2.SUPPLY_COMP_CD
- LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB3.CPN_ID
- AND OD.SUPPLY_COMP_CD = CB3.SUPPLY_COMP_CD
- LEFT OUTER JOIN TB_TMTB_BURDEN TB1 ON OD.TMTB1_SQ = TB1.TMTB_SQ
- AND OD.SUPPLY_COMP_CD = TB1.TMTB_SUPPLY_CD
- LEFT OUTER JOIN TB_TMTB_BURDEN TB2 ON OD.TMTB2_SQ = TB2.TMTB_SQ
- AND OD.SUPPLY_COMP_CD = TB2.TMTB_SUPPLY_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' /**P*/,'%Y-%m-%d')
- AND ODIH.REG_DT < DATE_ADD(STR_TO_DATE( '2021-09-30' /**P*/,'%Y-%m-%d'), INTERVAL 1 DAY)
- AND ODIH.ORD_DTL_STAT IN ('G720_10','G720_30','G720_40','G720_50','G720_60') /*판매-결제완료,환입-취소완료,환입-품절취소,환입-반품완료,환입-교환완료*/
- ) ODIH
- ORDER BY ODIH.ORD_DTL_ITEM_HST_SQ
- ) Z
- ;
|
