SELECT ORD_NO
, CASE WHEN PG_GB = 'KCP' THEN FN_GET_CODE_NM('G014',PAY_MEANS)
WHEN PG_GB = 'NAVER' THEN
CASE WHEN PAY_MEANS = 'G014_10' THEN '네이버페이(계좌)'
WHEN PAY_MEANS = 'G014_30' THEN '네이버페이(신용)'
ELSE '네이버페이'
END
WHEN PG_GB = 'NAVER_ORDER' THEN '네이버페이주문형'
WHEN PG_GB = 'KAKAO' THEN
CASE WHEN PAY_MEANS = 'G014_10' THEN '카카오페이(계좌)'
WHEN PAY_MEANS = 'G014_30' THEN '카카오페이(신용)'
ELSE '카카오페이'
END
WHEN PG_GB = 'PAYCO' THEN
CASE WHEN PAY_MEANS = 'G014_10' THEN '페이코(계좌)'
WHEN PAY_MEANS = 'G014_30' THEN '페이코(신용)'
ELSE '페이코'
END
ELSE ''
END AS PAY_MEANS_NM /*결제수단명*/
, CASE WHEN PG_GB = 'KCP' THEN
CASE WHEN PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO)
WHEN PAY_MEANS = 'G014_20' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO,' / ',VA_NM,' / ',IFNULL(DATE_FORMAT(PAY_DT,'%Y-%m-%d %H:%i:%S'),''))
WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
ELSE ''
END
WHEN PG_GB IN ('NAVER','KAKAO','PAYCO') THEN
CASE WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
ELSE ''
END
ELSE ''
END AS PAY_MEANS_DESC /*결제수단설명*/
, PG_CPN_AMT /*PG쿠폰금액*/
, NPAY_PNT_AMT /*포인트금액(마일리지)*/
, CASE WHEN NPAY_PNT_AMT > 0 AND PAY_MEANS IN ('G014_80','G014_81','G014_82','G014_83') THEN FN_GET_CODE_NM('G014',PAY_MEANS)
ELSE ''
END AS NPAY_PNT_DESC /*포인트(마일리지)설명*/
, CASE WHEN CASH_AUTH_NO IS NOT NULL AND CASH_TRADE_NO IS NOT NULL THEN 'Y'
ELSE 'N'
END AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
FROM TB_PAYMENT
WHERE ORD_NO = 5791 /**P*/
AND PAY_GB = 'O' /*주문시결제*/
AND PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
;
SELECT *
FROM TB_PAYMENT
WHERE ORD_NO = 5791 /**P*/
;
WITH ORIGINAL_ORD AS (
SELECT ODH.ORD_NO /*주문번호*/
, ODH.ORD_DTL_NO /*주문상세번호*/
-- , ODH.ORD_AMT /*주문금액*/
, ODH.REAL_ORD_AMT /*실주문금액*/
-- , (ODH.LIST_PRICE + ODH.OPT_ADD_PRICE) * (ODH.ORD_QTY - ODH.CNCL_RTN_QTY) AS ORD_LIST_AMT /*주문정상금액*/
, (ODH.CURR_PRICE + ODH.OPT_ADD_PRICE) * (ODH.ORD_QTY - ODH.CNCL_RTN_QTY) AS ORD_CURR_AMT /*주문상품금액*/
, ODH.CPN1_CPN_SQ /*즉시할인쿠폰번호*/
, ODH.CPN1_DC_AMT /*즉시할인쿠폰금액*/
, ODH.TMTB1_SQ /*다다익선수량할인번호*/
, ODH.TMTB1_DC_AMT /*다다익선수량할인금액*/
, ODH.TMTB2_SQ /*다다익선금액할인번호*/
, ODH.TMTB2_DC_AMT /*다다익선금액할인금액*/
, ODH.GOODS_CPN_SQ /*상품쿠폰번호*/
, ODH.GOODS_CPN_DC_AMT /*상품쿠폰할인금액*/
, ODH.CART_CPN_SQ /*장바구니쿠폰번호*/
, ODH.CART_CPN_DC_AMT /*장바구니쿠폰할인금액*/
, ODH.PRE_PNT_DC_AMT /*선포인트할인금액*/
, ODH.GFCD_USE_AMT /*상품권사용금액*/
, ODH.PNT_DC_AMT /*포인트할인금액*/
, ODH.SAVE_PNT_AMT /*적립포인트금액*/
FROM TB_ORDER_DETAIL_HST ODH
WHERE ODH.ORD_NO = 6005
AND ODH.ORD_EXCH_GB = 'O' /*원주문*/
AND ODH.ORD_DTL_STAT = 'G013_00' /*주문접수*/
)
, ORIGINAL_DELV_FEE AS (
SELECT DF.DELV_FEE_CD /*배송비정책코드*/
, DF.SUPPLY_COMP_CD /*공급업체코드*/
, FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD) AS SUPPLY_COMP_NM /*공급업체명*/
, DF.DELV_FEE /*배송비*/
, DF.DELV_CPN_SQ /*배송비쿠폰번호*/
, DF.DELV_CPN_DC_AMT /*배송비쿠폰할인금액*/
, DF.DELV_GFCD_USE_AMT /*배송비상품권사용금액*/
, DF.REAL_DELV_AMT /*실배송비금액*/
FROM TB_DELIVERY_FEE DF
WHERE DF.ORD_NO = 6005
AND DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
)
, ORIGINAL_PAYMENT AS (
SELECT ORD_NO
, CASE WHEN PG_GB = 'KCP' THEN FN_GET_CODE_NM('G014', PAY_MEANS)
WHEN PG_GB = 'NAVER' THEN '네이버페이'
WHEN PG_GB = 'NAVER_ORDER' THEN '네이버페이주문형'
WHEN PG_GB = 'KAKAO' THEN '카카오페이'
ELSE ''
END AS PAY_MEANS_NM /*결제수단명*/
, CASE WHEN PG_GB = 'KCP' THEN
CASE WHEN PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO)
WHEN PAY_MEANS = 'G014_20' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO,' / ',VA_NM,' / ',DATE_FORMAT(VA_DEADLINE,'%Y-%m-%d %H:%i:%S'))
WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
ELSE ''
END
WHEN PG_GB IN ('NAVER','KAKAO') THEN
CASE WHEN PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',VA_BANK),' / ',VA_NO)
WHEN PAY_MEANS = 'G014_30' THEN CONCAT(CARD_NM,' / ',CASE WHEN CAST(CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(CARD_MIPS AS UNSIGNED),'개월') END)
ELSE ''
END
END AS PAY_MEANS_DESC /*결제수단설명*/
, PG_CPN_AMT /*PG쿠폰금액*/
, NPAY_PNT_AMT /*네이버페이포인트금액(마일리지)*/
, CASE WHEN CASH_AUTH_NO IS NOT NULL AND CASH_TRADE_NO IS NOT NULL THEN 'Y'
ELSE 'N'
END AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
FROM TB_PAYMENT
WHERE ORD_NO = 6005
AND PAY_GB = 'O' /*주문시결제*/
AND PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
)
SELECT OO.ORD_NO /*주문번호*/
, SUM(OO.ORD_CURR_AMT) AS ORD_CURR_AMT /*주문상품금액*/
, IFNULL((SELECT SUM(REAL_DELV_AMT)
FROM ORIGINAL_DELV_FEE
),0) AS REAL_DELV_AMT /*실배송비금액*/
, (SELECT CONCAT((SELECT CONCAT('자사 : ',FORMAT(SUM(DF.DELV_FEE),0),'원')
FROM ORIGINAL_DELV_FEE DF
WHERE DF.DELV_FEE_CD = 'WMS' /*자사*/
),
(SELECT CONCAT(' / 업체 : ',GROUP_CONCAT(DELV_FEE_DESC SEPARATOR ', '))
FROM (
SELECT CONCAT(DF.SUPPLY_COMP_CD,'/',FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD),' ',FORMAT(SUM(DF.DELV_FEE),0),'원') AS DELV_FEE_DESC
FROM ORIGINAL_DELV_FEE DF
WHERE DF.DELV_FEE_CD != 'WMS' /*입점업체*/
GROUP BY DF.SUPPLY_COMP_CD
) Z
)) AS DELV_FEE_DESC
FROM DUAL
) AS DELV_FEE_DESC /*배송비설명*/
, (SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,' ',FORMAT(DF.DELV_CPN_DC_AMT,0),'원') SEPARATOR ', ') AS DELV_FEE_CPN_DESC
FROM ORIGINAL_DELV_FEE DF
INNER JOIN TB_CUST_COUPON CC ON DF.DELV_CPN_SQ = CC.CUST_CPN_SQ
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE DF.DELV_CPN_SQ > 0
) AS DELV_FEE_CPN_DESC /*배송비쿠폰설명*/
, SUM(OO.CPN1_DC_AMT +
OO.TMTB1_DC_AMT + OO.TMTB2_DC_AMT +
OO.GOODS_CPN_DC_AMT + OO.CART_CPN_DC_AMT +
OO.PRE_PNT_DC_AMT) AS TOT_DC_AMT /*총할인금액*/
, SUM(OO.CPN1_DC_AMT) AS CPN1_DC_AMT /*즉시할인쿠폰금액*/
, (SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',CPN_ID,' / ',CPN_NM) SEPARATOR '!@!') AS CPN_DESC
FROM TB_COUPON
WHERE CPN_ID IN (SELECT DISTINCT CPN1_CPN_SQ FROM ORIGINAL_ORD)
) AS CPN1_CPN_DC_DESC /*즉시할인쿠폰설명*/
, SUM(OO.TMTB1_DC_AMT + OO.TMTB2_DC_AMT) AS TMTB_DC_AMT /*다다익선인금액*/
, SUM(OO.TMTB1_DC_AMT) AS TMTB1_DC_AMT /*다다익선수량할인금액*/
, (SELECT GROUP_CONCAT(DISTINCT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) SEPARATOR '!@!') AS CPN_DESC
FROM TB_TMTB
WHERE TMTB_SQ IN (SELECT DISTINCT TMTB1_SQ FROM ORIGINAL_ORD)
) AS TMTB1_DESC /*다다익선수량할인설명*/
, SUM(OO.TMTB2_DC_AMT) AS TMTB2_DC_AMT /*다다익선금액할인금액*/
, (SELECT GROUP_CONCAT(DISTINCT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) SEPARATOR '!@!') AS CPN_DESC
FROM TB_TMTB
WHERE TMTB_SQ IN (SELECT DISTINCT TMTB2_SQ FROM ORIGINAL_ORD)
) AS TMTB2_DESC /*다다익선금액할인설명*/
, SUM(OO.GOODS_CPN_DC_AMT) AS GOODS_CPN_DC_AMT /*상품쿠폰할인금액*/
, (SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM) SEPARATOR '!@!') AS CPN_DESC
FROM TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE CC.CUST_CPN_SQ IN (SELECT DISTINCT GOODS_CPN_SQ FROM ORIGINAL_ORD)
) AS GOODS_CPN_DC_DESC /*상품쿠폰할인설명*/
, SUM(OO.CART_CPN_DC_AMT) AS CART_CPN_DC_AMT /*장바구니쿠폰할인금액*/
, (SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,
' / 구매금액 ',
CASE WHEN C.BUY_LIMIT_AMT = 0 THEN '제한없음'
ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT,0),'원 이상')
END,
' / ',
FORMAT(C.DC_PVAL,0),
CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
ELSE '%'
END,
' 할인 / 최대할인 ',
CASE WHEN C.MAX_DC_AMT = 0 THEN '제한없음'
ELSE CONCAT(FORMAT(C.MAX_DC_AMT,0),'원')
END
) AS CPN_DESC
FROM TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE CC.CUST_CPN_SQ = (SELECT DISTINCT CART_CPN_SQ FROM ORIGINAL_ORD)
) AS CART_CPN_DC_DESC /*장바구니쿠폰할인설명*/
, SUM(OO.PRE_PNT_DC_AMT) AS PRE_PNT_DC_AMT /*선포인트할인금액*/
, SUM(OO.ORD_CURR_AMT)
+ IFNULL((SELECT SUM(REAL_DELV_AMT)
FROM ORIGINAL_DELV_FEE
),0)
- SUM(OO.CPN1_DC_AMT +
OO.TMTB1_DC_AMT + OO.TMTB2_DC_AMT +
OO.GOODS_CPN_DC_AMT + OO.CART_CPN_DC_AMT +
OO.PRE_PNT_DC_AMT) AS TOT_PAY_AMT /*총결제금액*/
, SUM(OO.REAL_ORD_AMT)
+ IFNULL((SELECT SUM(REAL_DELV_AMT)
FROM ORIGINAL_DELV_FEE
),0) AS REAL_ORD_AMT /*실결제금액*/
, MAX(P.PAY_MEANS_NM) AS PAY_MEANS_NM /*결제수단명*/
, MAX(P.PAY_MEANS_DESC) AS PAY_MEANS_DESC /*결제수단설명*/
, SUM(OO.GFCD_USE_AMT) AS GFCD_USE_AMT /*상품권사용금액*/
, (SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!') AS GFCD_USE_DESC
FROM (
SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
FROM TB_CUST_GIFTCARD_HST CGH
INNER JOIN TB_CUST_GIFTCARD CG ON CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
WHERE CGH.ORD_NO = OO.ORD_NO
AND CGH.OCCUR_GB = 'G074_12' /*상품권사용*/
GROUP BY CG.GFCD_NO
) Z
) AS GFCD_USE_DESC /*상품권사용설명*/
, SUM(OO.PNT_DC_AMT) AS PNT_DC_AMT /*포인트할인금액*/
, MAX(P.PG_CPN_AMT) AS PG_CPN_AMT /*PG쿠폰금액(KCP쿠폰금액)*/
, MAX(P.NPAY_PNT_AMT) AS NPAY_PNT_AMT /*네이버페이포인트금액(마일리지)*/
, SUM(OO.SAVE_PNT_AMT) AS SAVE_PNT_AMT /*적립포인트금액*/
, MAX(P.CASH_RECEIP_REQ_YN) AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
FROM ORIGINAL_ORD OO
INNER JOIN TB_ORDER O ON OO.ORD_NO = O.ORD_NO
INNER JOIN ORIGINAL_PAYMENT P ON OO.ORD_NO = P.ORD_NO
GROUP BY OO.ORD_NO
;
-- 상품쿠폰내역 목록 가져오기
SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM) SEPARATOR '!@!') AS CPN_DESC
FROM TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE CC.CUST_CPN_SQ IN (3465,3575,3298,3456,10,3581)
;
-- 장바구니쿠폰내역 가져오기
SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,
' / 구매금액 ',
CASE WHEN C.BUY_LIMIT_AMT = 0 THEN '제한없음'
ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT,0),'원 이상')
END,
' / ',
FORMAT(C.DC_PVAL,0),
CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
ELSE '%'
END,
' 할인 / 최대할인 ',
CASE WHEN C.MAX_DC_AMT = 0 THEN '제한없음'
ELSE CONCAT(FORMAT(C.MAX_DC_AMT,0),'원')
END
)
FROM TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE CC.CUST_CPN_SQ IN (3473)
;
-- 상품권사용내역 목록
SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!') AS GFCD_USE_DESC
FROM (
SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
FROM TB_CUST_GIFTCARD_HST CGH
, TB_CUST_GIFTCARD CG
WHERE CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
AND CGH.ORD_NO = 6005
AND CGH.OCCUR_GB = 'G074_12' /*상품권사용*/
GROUP BY CG.GFCD_NO
) Z
;
SELECT DF.DELV_FEE_CD /*배송정책코드*/
, DF.DELV_FEE /*배송비*/
, DF.DELV_CPN_SQ /*배송비쿠폰번호*/
, DF.DELV_CPN_DC_AMT /*배송비쿠폰할인금액*/
, DF.DELV_GFCD_USE_AMT /*배송비상품권사용금액*/
, DF.SUPPLY_COMP_CD /*공급업체코드*/
FROM TB_DELIVERY_FEE DF
WHERE DF.ORD_NO = 6005 /**P*/
AND DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
-- GROUP BY DF.DELV_FEE_CD
;
WITH ORIGINAL_DELV_FEE AS (
SELECT DF.DELV_FEE_CD /*배송비정책코드*/
, DF.SUPPLY_COMP_CD /*공급업체코드*/
, FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD) AS SUPPLY_COMP_NM /*공급업체명*/
, DF.DELV_FEE /*배송비*/
, DF.DELV_CPN_SQ /*배송비쿠폰번호*/
, DF.DELV_CPN_DC_AMT /*배송비쿠폰할인금액*/
, DF.DELV_GFCD_USE_AMT /*배송비상품권사용금액*/
FROM TB_DELIVERY_FEE DF
WHERE DF.ORD_NO = 6005
AND DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
)
SELECT CONCAT((SELECT CONCAT('자사 : ',FORMAT(SUM(DF.DELV_FEE),0),'원') AS DELV_FEE_DESC
FROM ORIGINAL_DELV_FEE DF
WHERE DF.DELV_FEE_CD = 'WMS' /*자사*/
),
(SELECT CONCAT(' / 업체 : ',GROUP_CONCAT(DELV_FEE_DESC SEPARATOR ', ')) AS DELV_FEE_DESC
FROM (
SELECT CONCAT(DF.SUPPLY_COMP_CD,'/',FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD),' ',FORMAT(SUM(DF.DELV_FEE),0),'원') AS DELV_FEE_DESC
FROM ORIGINAL_DELV_FEE DF
WHERE DF.DELV_FEE_CD != 'WMS' /*입점업체*/
GROUP BY DF.SUPPLY_COMP_CD
) Z
)) AS DELV_FEE_DESC
FROM DUAL
;
SELECT CONCAT('업체 : ',GROUP_CONCAT(CONCAT(DF.SUPPLY_COMP_CD,'/',FN_GET_SUPPLY_COMP_NM(DF.SUPPLY_COMP_CD),' ',FORMAT(DF.DELV_FEE,0),'원') SEPARATOR ', ')) AS DELV_FEE_DESC
-- , DF.DELV_CPN_SQ /*배송비쿠폰번호*/
-- , DF.DELV_CPN_DC_AMT /*배송비쿠폰할인금액*/
-- , DF.DELV_GFCD_USE_AMT /*배송비상품권사용금액*/
FROM ORIGINAL_DELV_FEE DF
WHERE DF.DELV_FEE_CD != 'WMS' /*입점업체*/
;
-- 배송비쿠폰내역 가져오기
SELECT GROUP_CONCAT(DISTINCT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM) SEPARATOR ', ') AS CPN_DESC
FROM TB_CUST_COUPON CC
INNER JOIN TB_COUPON C ON CC.CPN_ID = C.CPN_ID
WHERE CC.CUST_CPN_SQ IN (13)
;
SELECT *
FROM TB_CUST_GIFTCARD_HST CGH
WHERE CGH.ORD_NO = 6005
AND CGH.OCCUR_GB = 'G074_12' /*상품권사용*/
;
SELECT *
FROM TB_COMMON_CODE
WHERE CD_GB = 'G720'
;
-- G720_10 판매-결제완료
-- G720_20 판매-배송중
-- G720_30 환입-취소완료
-- G720_40 환입-품절취소
-- G720_50 환입_반품완료
-- G720_60 환입-교환완료
-- G014_10 실시간계좌이체(KCP)
-- G014_20 무통장입금(KCP)
-- G014_30 신용카드(KCP)
-- G014_40 포인트
-- G014_50 쿠폰
-- G014_60 휴대전화(KCP)
-- G014_70 상품권
-- G014_80 네이버포인트
-- G014_81 카카오포인트
-- G014_82 PAYCO포인트
-- G014_83 카카오머니
-- G014_90 외부몰입금
SELECT *
FROM TB_PAYMENT
WHERE 1 = 1
AND PG_GB = 'KCP'
AND PAY_MEANS = 'G014_20'
AND PAY_GB = 'O'
AND PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
;
SELECT *
FROM TB_ORDER
WHERE ORD_NO = 3357
;
-- 5791 2021-09-03 20:10:11
/* order.getOrderDetailList */
SELECT A.ORD_NO
, A.ORD_EXCH_GB
, A.EXCH_GB_NM
, A.ORD_DTL_NO
, A.GOODS_CD
, A.GOODS_NM
, A.GOODS_TYPE
, A.GOODS_TYPE_NM
, A.SYS_IMG_NM
, A.BRAND_ENM
, GROUP_CONCAT(A.ITEM_NM ORDER BY A.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
, GROUP_CONCAT(A.ITEM_CD ORDER BY A.ORD_DTL_ITEM_SQ) AS ITEM_CD
, GROUP_CONCAT(A.OPT_CD ORDER BY A.ORD_DTL_ITEM_SQ) AS OPT_CD
, GROUP_CONCAT(A.OPT_CD1 ORDER BY A.ORD_DTL_ITEM_SQ) AS OPT_CD1
, GROUP_CONCAT(A.OPT_CD2 ORDER BY A.ORD_DTL_ITEM_SQ) AS OPT_CD2
, GROUP_CONCAT(A.ITEM_QTY ORDER BY A.ORD_DTL_ITEM_SQ) AS ITEM_QTYR
, GROUP_CONCAT(A.COLOR_NM ORDER BY A.ORD_DTL_ITEM_SQ) AS COLOR_NM
, A.LIST_PRICE
, A.CURR_PRICE
, A.ORD_QTY
, A.CNCL_RTN_QTY
, A.ORD_REQ_CHG_QTY
, A.CANCEL_REQUEST_QTY
, A.EXCHANGE_REQUEST_QTY
, A.RETURN_REQUEST_QTY
, SUM(A.OPT_ADD_PRICE) AS OPT_ADD_PRICE
, (SELECT CONCAT('쿠폰번호 : ',CPN_ID,' / ',CPN_NM)
FROM TB_COUPON
WHERE CPN_ID = A.CPN1_CPN_SQ) AS CPN1_CPN_DC_DESC
, A.CPN1_DC_AMT
, (SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM)
FROM TB_CUST_COUPON CC
, TB_COUPON C
WHERE CC.CPN_ID = C.CPN_ID
AND CC.CUST_NO = A.CUST_NO
AND CC.CUST_CPN_SQ = A.GOODS_CPN_SQ) AS GOODS_CPN_DC_DESC
, A.GOODS_CPN_DC_AMT
, (SELECT CONCAT('쿠폰번호 : ',C.CPN_ID,' / ',C.CPN_NM,
' / 구매금액 ',
CASE WHEN C.BUY_LIMIT_AMT = 0 THEN '제한없음'
ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT,0),'원 이상')
END,
' / ',
FORMAT(C.DC_PVAL,0),
CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
ELSE '%'
END,
' 할인 / 최대할인 ',
CASE WHEN C.MAX_DC_AMT = 0 THEN '제한없음'
ELSE CONCAT(FORMAT(C.MAX_DC_AMT,0),'원')
END
)
FROM TB_CUST_COUPON CC
, TB_COUPON C
WHERE CC.CPN_ID = C.CPN_ID
AND CC.CUST_NO = A.CUST_NO
AND CC.CUST_CPN_SQ = A.CART_CPN_SQ) AS CART_CPN_DC_DESC
, A.CART_CPN_DC_AMT
, (SELECT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) FROM TB_TMTB WHERE TMTB_SQ = A.TMTB1_SQ) AS TMTB1_DESC
, A.TMTB1_DC_AMT
, (SELECT CONCAT('프로모션번호 : ',TMTB_SQ,' / ',TMTB_NM) FROM TB_TMTB WHERE TMTB_SQ = A.TMTB2_SQ) AS TMTB2_DESC
, A.TMTB2_DC_AMT
, A.PNT_DC_AMT
, A.PRE_PNT_DC_AMT
, A.SAVE_PNT_AMT
, A.GFCD_USE_AMT
, (SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!') AS GFCD_USE_DESC
FROM (
SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
FROM TB_CUST_GIFTCARD_HST CGH
, TB_CUST_GIFTCARD CG
WHERE CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
AND CGH.ORD_NO = A.ORD_NO
GROUP BY CG.GFCD_NO
) Z
) AS GFCD_USE_DESC
, A.ORD_AMT
, A.REAL_ORD_AMT
, A.CNCL_RTN_AMT
, A.ORD_DTL_STAT
, A.ORD_DTL_STAT_NM
, A.SOLDOUT_YN
, A.SOLDOUT_REG_NO
, A.SOLDOUT_MEMO
, A.INVOICE_NO
, A.DELV_LOC_CD
, A.DELV_ADDR_SQ
, A.RETURNABLE_YN
, A.CHANGEABLE_YN
, A.DELV_FEE_CD
, A.DELV_FEE_CD_GRP
, A.BRAND_GROUP_NM
, A.SHIP_COMP_CD
, A.SHIP_COMP_NM
, A.DELV_LOC_NM
, A.PG_GB
, A.SUPPLY_COMP_NM
, CASE WHEN IFNULL((SELECT COUNT(*)
FROM TB_GIFTCARD_RECEIPT
WHERE ORD_NO = A.ORD_NO
AND TRADE_NO IS NOT NULL
AND AUTH_NO IS NOT NULL
AND AMT > 0
),0) = 0 THEN 'N' ELSE 'Y' END AS GCCR_REQ_YN /*상품권현금영수증신청여부*/
FROM (
SELECT O.CUST_NO
, OD.ORD_NO
, OD.ORD_EXCH_GB
, CASE WHEN OD.ORD_EXCH_GB = 'O' THEN '주문' ELSE '교환' END AS EXCH_GB_NM
, OD.ORD_DTL_NO
, ODI.ORD_DTL_ITEM_SQ
, G1.GOODS_CD
, FN_GET_GOODS_NM(G1.GOODS_NM, G1.GOODS_GB, G1.FOREIGN_BUY_YN, G1.PARALLEL_IMPORT_YN, G1.ORDER_MADE_YN) AS GOODS_NM
, G1.GOODS_TYPE
, FN_GET_CODE_NM('G056', G1.GOODS_TYPE) AS GOODS_TYPE_NM
, CASE WHEN G1.GOODS_TYPE != 'G056_S' AND G1.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G1.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G1.GOODS_CD AND COLOR_CD = IFNULL(G1.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y')
END AS SYS_IMG_NM
, G1.BRAND_CD
, (SELECT Z.BRAND_ENM
FROM TB_BRAND Z
WHERE Z.BRAND_CD = G1.BRAND_CD) AS BRAND_ENM
, G2.GOODS_CD AS ITEM_CD
, G2.GOODS_NM AS ITEM_NM
, IFNULL((SELECT COLOR_KNM
FROM TB_COLOR
WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,''), G1.MAIN_COLOR_CD)
AND USE_YN = 'Y'), ODI.OPT_CD1) AS COLOR_NM
, ODI.OPT_CD
, ODI.OPT_CD1
, ODI.OPT_CD2
, ODI.ITEM_QTY
, ODI.ITEM_PRICE
, OD.LIST_PRICE
, OD.CURR_PRICE
, OD.ORD_QTY
, OD.CNCL_RTN_QTY
, CASE WHEN OCD.ORD_REQ_CHG_QTY IS NULL THEN 0
ELSE OCD.ORD_REQ_CHG_QTY
END AS ORD_REQ_CHG_QTY
, CASE WHEN OCD.CANCEL_REQUEST_QTY IS NULL THEN 0
ELSE OCD.CANCEL_REQUEST_QTY
END AS CANCEL_REQUEST_QTY
, CASE WHEN OCD.EXCHANGE_REQUEST_QTY IS NULL THEN 0
ELSE OCD.EXCHANGE_REQUEST_QTY
END AS EXCHANGE_REQUEST_QTY
, CASE WHEN OCD.RETURN_REQUEST_QTY IS NULL THEN 0
ELSE OCD.RETURN_REQUEST_QTY
END AS RETURN_REQUEST_QTY
, ODI.OPT_ADD_PRICE
, OD.CPN1_CPN_SQ
, OD.CPN1_DC_AMT
, OD.GOODS_CPN_SQ
, OD.GOODS_CPN_DC_AMT
, OD.CART_CPN_SQ
, OD.CART_CPN_DC_AMT
, OD.TMTB1_SQ
, OD.TMTB1_DC_AMT
, OD.TMTB2_SQ
, OD.TMTB2_DC_AMT
, OD.PNT_DC_AMT
, OD.PRE_PNT_DC_AMT
, OD.SAVE_PNT_AMT
, OD.GFCD_USE_AMT
, OD.ORD_AMT
, OD.REAL_ORD_AMT
, OD.CNCL_RTN_AMT
, OD.ORD_DTL_STAT
, FN_GET_CODE_NM('G013', OD.ORD_DTL_STAT) AS ORD_DTL_STAT_NM
, OD.SOLDOUT_YN
, OD.SOLDOUT_REG_NO
, OD.SOLDOUT_MEMO
, OD.INVOICE_NO
, OD.DELV_LOC_CD
, OD.DELV_ADDR_SQ
, OD.RETURNABLE_YN
, OD.CHANGEABLE_YN
, OD.DELV_FEE_CD
, CASE WHEN G1.SELF_GOODS_YN = 'Y' THEN 'WMS'
ELSE OD.DELV_FEE_CD
END AS DELV_FEE_CD_GRP
, CASE WHEN BG.DISP_NM_LANG = 'EN' THEN BG.BRAND_GROUP_ENM
ELSE BG.BRAND_GROUP_KNM
END AS BRAND_GROUP_NM
, OD.SHIP_COMP_CD
, (SELECT SHIP_COMP_NM
FROM TB_SHIP_COMPANY SC
WHERE SC.SHIP_COMP_CD = OD.SHIP_COMP_CD) AS SHIP_COMP_NM
, (SELECT DL.DELV_LOC_NM
FROM TB_DELIVERY_LOC DL
WHERE DL.DELV_LOC_CD = OD.DELV_LOC_CD
AND DL.USE_YN = 'Y'
LIMIT 1) AS DELV_LOC_NM
, P.PG_GB
, (SELECT Z.SUPPLY_COMP_NM
FROM TB_SUPPLY_COMPANY Z
WHERE Z.SUPPLY_COMP_CD = OD.SUPPLY_COMP_CD) AS SUPPLY_COMP_NM
FROM TB_ORDER O
INNER JOIN TB_ORDER_DETAIL OD
ON O.ORD_NO = OD.ORD_NO
INNER JOIN TB_GOODS G1
ON OD.GOODS_CD = G1.GOODS_CD
INNER JOIN TB_BRAND B
ON B.BRAND_CD = G1.BRAND_CD
INNER JOIN TB_BRAND_GROUP BG
ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
AND BG.USE_YN = 'Y'
INNER JOIN TB_ORDER_DETAIL_ITEM ODI
ON OD.ORD_NO = ODI.ORD_NO
AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
INNER JOIN TB_GOODS G2
ON ODI.ITEM_CD = G2.GOODS_CD
INNER JOIN TB_PAYMENT P
ON O.ORD_NO = P.ORD_NO
AND P.PAY_GB = 'O'
AND P.PAY_STAT IN ('G016_10', 'G016_30')
INNER JOIN TB_DELIVERY_ADDR DA
ON OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ
LEFT OUTER JOIN (SELECT OD.ORD_DTL_NO
, SUM(CASE OC.WD_BF_SEND_YN
WHEN 'Y' THEN 0
ELSE OCD.CHG_QTY
END) AS ORD_REQ_CHG_QTY
, SUM(CASE OCD.CHG_STAT
WHEN 'G685_10' THEN OCD.CHG_QTY
WHEN 'G685_11' THEN OCD.CHG_QTY
ELSE 0
END) AS CANCEL_REQUEST_QTY
, SUM(CASE WHEN OCD.CHG_STAT = 'G685_20' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_21' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_30' AND OC.CHG_GB = 'G680_40' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_31' AND OC.CHG_GB = 'G680_40' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_32' AND OC.CHG_GB = 'G680_40' THEN OCD.CHG_QTY
ELSE 0
END) AS EXCHANGE_REQUEST_QTY
, SUM(CASE WHEN OCD.CHG_STAT = 'G685_50' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_51' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_33' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_30' AND OC.CHG_GB = 'G680_30' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_31' AND OC.CHG_GB = 'G680_30' THEN OCD.CHG_QTY
WHEN OCD.CHG_STAT = 'G685_32' AND OC.CHG_GB = 'G680_30' THEN OCD.CHG_QTY
ELSE 0
END) AS RETURN_REQUEST_QTY
FROM TB_ORDER_CHANGE_DETAIL OCD
INNER JOIN TB_ORDER_CHANGE OC
ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
INNER JOIN TB_ORDER_DETAIL OD
ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
WHERE 1 = 1
AND OD.ORD_NO = 6005 /**P*/
AND OCD.CHG_STAT IN ('G685_10', 'G685_11', 'G685_20', 'G685_21', 'G685_30', 'G685_31', 'G685_32', 'G685_33', 'G685_50', 'G685_51')
AND OCD.DEL_YN = 'N'
GROUP BY OCD.ORD_DTL_NO
) OCD
ON OD.ORD_DTL_NO = OCD.ORD_DTL_NO
WHERE 1 = 1
AND OD.ORD_NO = 6005 /**P*/
AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_25', 'G013_97')
) A
GROUP BY A.ORD_NO
, A.ORD_EXCH_GB
, A.EXCH_GB_NM
, A.ORD_DTL_NO
, A.GOODS_CD
, A.GOODS_NM
, A.GOODS_TYPE
, A.GOODS_TYPE_NM
, A.SYS_IMG_NM
, A.BRAND_ENM
, A.LIST_PRICE
, A.CURR_PRICE
, A.ORD_QTY
, A.CNCL_RTN_QTY
, A.ORD_REQ_CHG_QTY
, A.CANCEL_REQUEST_QTY
, A.EXCHANGE_REQUEST_QTY
, A.RETURN_REQUEST_QTY
, A.CPN1_DC_AMT
, A.GOODS_CPN_DC_AMT
, A.CART_CPN_DC_AMT
, A.TMTB1_DC_AMT
, A.TMTB2_DC_AMT
, A.PNT_DC_AMT
, A.PRE_PNT_DC_AMT
, A.SAVE_PNT_AMT
, A.GFCD_USE_AMT
, A.ORD_AMT
, A.REAL_ORD_AMT
, A.CNCL_RTN_AMT
, A.ORD_DTL_STAT
, A.ORD_DTL_STAT_NM
, A.SOLDOUT_YN
, A.SOLDOUT_REG_NO
, A.SOLDOUT_MEMO
, A.INVOICE_NO
, A.DELV_LOC_CD
, A.DELV_ADDR_SQ
, A.RETURNABLE_YN
, A.CHANGEABLE_YN
, A.DELV_FEE_CD
, A.DELV_FEE_CD_GRP
, A.BRAND_GROUP_NM
, A.SHIP_COMP_CD
, A.SHIP_COMP_NM
, A.DELV_LOC_NM
, A.PG_GB
, A.SUPPLY_COMP_NM
ORDER BY A.ORD_NO
, A.ORD_DTL_NO
;
SELECT OD.*
FROM TB_ORDER O
-- WHERE ORD_NO = 353 2223
INNER JOIN TB_ORDER_DETAIL OD
ON O.ORD_NO = OD.ORD_NO
INNER JOIN TB_GOODS G1
ON OD.GOODS_CD = G1.GOODS_CD
INNER JOIN TB_BRAND B
ON B.BRAND_CD = G1.BRAND_CD
INNER JOIN TB_BRAND_GROUP BG
ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
AND BG.USE_YN = 'Y'
INNER JOIN TB_ORDER_DETAIL_ITEM ODI
ON OD.ORD_NO = ODI.ORD_NO
AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
INNER JOIN TB_GOODS G2
ON ODI.ITEM_CD = G2.GOODS_CD
INNER JOIN TB_PAYMENT P
ON O.ORD_NO = P.ORD_NO
AND P.PAY_GB = 'O'
AND P.PAY_STAT IN ('G016_10', 'G016_30')
INNER JOIN TB_DELIVERY_ADDR DA
ON OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ
WHERE 1 = 1
AND OD.GOODS_CPN_SQ IS NOT NULL
AND OD.GOODS_CPN_SQ > 0
AND OD.CART_CPN_SQ IS NOT NULL
AND OD.CART_CPN_SQ > 0
-- AND TMTB1_SQ > 0
AND OD.ORD_DTL_STAT = 'G013_20'
-- AND OD.GOODS_CPN_SQ IN (SELECT CPN_ID FROM TB_COUPON WHERE CPN_TYPE = 'G230_11')
-- AND OD.CART_CPN_SQ IN (SELECT CPN_ID FROM TB_COUPON WHERE CPN_TYPE = 'G230_20')
;
SELECT *
FROM TB_COMMON_CODE
WHERE CD_GB = 'G230'
;
SELECT *
FROM TB_COUPON
WHERE CPN_TYPE IN ('G230_11','G230_20')
;
SELECT *
FROM TB_CUST_COUPON
;
SELECT *
FROM TB_ORDER
;
-- G230_10 즉시할인쿠폰
-- G230_11 상품쿠폰
-- G230_12 브랜드쿠폰
-- G230_13 카테고리쿠폰
-- G230_14 공급처쿠폰
-- G230_20 주문서쿠폰
-- G230_30 배송비쿠폰
/* order.getOrderGiftcardHstList */
SELECT GROUP_CONCAT(GFCD_USE_DESC SEPARATOR '!@!')
FROM (
SELECT CONCAT(FORMAT(SUM(CGH.GFCD_AMT),0),'원 / ',CG.GFCD_NO) AS GFCD_USE_DESC
FROM TB_ORDER_DETAIL OD
INNER JOIN TB_CUST_GIFTCARD_HST CGH
ON OD.ORD_DTL_NO = CGH.ORD_DTL_NO
AND OD.ORD_NO = CGH.ORD_NO
INNER JOIN TB_CUST_GIFTCARD CG
ON CGH.CUST_GFCD_SQ = CG.CUST_GFCD_SQ
WHERE OD.ORD_NO = 1433 /**P*/
GROUP BY CG.GFCD_NO
) Z
;
SELECT ORD_NO, ORD_DTL_NO, COUNT(DISTINCT CUST_GFCD_SQ) AS CNT
FROM TB_CUST_GIFTCARD_HST
GROUP BY ORD_NO, ORD_DTL_NO
HAVING COUNT(DISTINCT CUST_GFCD_SQ) > 1
;
SELECT *
FROM TB_COMMON_CODE
WHERE CD_GB = 'G016'
;
/* order.getDeliveryFeeList */
SELECT Z.*
FROM (
SELECT DF.DELV_FEE_GB
, FN_GET_CODE_NM('G018', DF.DELV_FEE_GB) AS DELV_FEE_GB_NM
, DF.DELV_FEE
, DF.REAL_DELV_AMT
, DF.DELV_USAC_YN
, DATE_FORMAT(DF.DELV_USAC_DT, '%Y%m%d%H%i%S') AS DELV_USAC_DT
, DF.SUPPLY_COMP_CD
, DF.DELV_FEE_SQ
, DF.DELV_CPN_SQ
, DF.DELV_CPN_DC_AMT
, DF.DELV_GFCD_USE_AMT
, DF.DELV_FEE_CD
, CASE WHEN DF.DELV_FEE_CD IN ('WMS')
THEN '자사'
ELSE (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY X WHERE X.SUPPLY_COMP_CD = DF.SUPPLY_COMP_CD)
END SUPPLY_COMP_NM
FROM TB_DELIVERY_FEE DF
WHERE 1 = 1
AND DF.ORD_NO = 6005 /**P*/
AND DF.DELV_FEE_GB = 'G018_10' /*원주문배송비*/
UNION ALL
SELECT DF.DELV_FEE_GB
, FN_GET_CODE_NM('G018', DF.DELV_FEE_GB) AS DELV_FEE_GB_NM
, DF.DELV_FEE
, DF.REAL_DELV_AMT
, DF.DELV_USAC_YN
, DATE_FORMAT(DF.DELV_USAC_DT, '%Y%m%d%H%i%S') AS DELV_USAC_DT
, DF.SUPPLY_COMP_CD
, DF.DELV_FEE_SQ
, DF.DELV_CPN_SQ
, DF.DELV_CPN_DC_AMT
, DF.DELV_GFCD_USE_AMT
, DF.DELV_FEE_CD
, CASE WHEN DF.DELV_FEE_CD IN ('WMS')
THEN '자사'
ELSE (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY X WHERE X.SUPPLY_COMP_CD = DF.SUPPLY_COMP_CD)
END SUPPLY_COMP_NM
FROM TB_DELIVERY_FEE DF
WHERE 1 = 1
AND DF.ORD_NO = 6005 /**P*/
AND EXISTS (SELECT 1
FROM TB_ORDER_CHANGE_DETAIL OCD
WHERE DF.ORD_CHG_SQ = OCD.ORD_CHG_SQ
AND OCD.CHG_STAT IN ('G685_40', 'G685_60'))
) Z
GROUP BY Z.DELV_FEE_GB
, Z.DELV_FEE_GB_NM
, Z.DELV_FEE
, Z.REAL_DELV_AMT
, Z.DELV_USAC_YN
, Z.DELV_USAC_DT
, Z.SUPPLY_COMP_CD
, Z.SUPPLY_COMP_NM
, Z.DELV_FEE_SQ
, Z.DELV_CPN_SQ
, Z.DELV_CPN_DC_AMT
, Z.DELV_GFCD_USE_AMT
, Z.DELV_FEE_CD
;
/* order.getOrderPaymentBasicInfoList */
SELECT P.ESCROW_YN
, CASE WHEN O.MALL_GB IN ('G011_20')
THEN ''
ELSE P.PG_GB
END PG_GB
, CASE WHEN O.MALL_GB IN ('G011_20')
THEN ''
ELSE P.PAY_MEANS
END PAY_MEANS
, CASE WHEN O.MALL_GB IN ('G011_20')
THEN ''
ELSE FN_GET_CODE_NM('G014', P.PAY_MEANS)
END PAY_MEANS_NM
, CASE WHEN P.PAY_MEANS IN ('G014_20')
THEN P.VA_BANK
ELSE ''
END VA_BANK
, CASE WHEN P.PAY_MEANS = 'G014_20' THEN FN_GET_CODE_NM('G940', P.VA_BANK)
WHEN P.PAY_MEANS = 'G014_10' THEN ''
ELSE P.CARD_NM
END CARD_NM
, P.PG_TRADE_NO
, P.PG_TID
, P.VA_NO
, DATE_FORMAT(P.PAY_DT, '%Y%m%d%H%i%S') AS PAY_DT
, P.PAY_STAT
, FN_GET_CODE_NM('G016', P.PAY_STAT) AS PAY_STAT_NM
, P.PAY_AMT
, P.PG_CPN_AMT
, P.NPAY_PNT_AMT
, DATE_FORMAT(P.VA_DEADLINE, '%Y-%m-%d') AS VA_DEADLINE
, CARD_MIPS
, CARD_TYPE
, CASH_AUTH_NO
, CASH_TRADE_NO
FROM TB_PAYMENT P
INNER JOIN TB_ORDER O
ON P.ORD_NO = O.ORD_NO
WHERE 1 = 1
AND P.ORD_NO = 169 /**P*/
AND P.PAY_STAT IN ('G016_30', 'G016_10') /*결제완료,무통장입금관련*/
ORDER BY P.REG_DT
;
-- G016_00 결제대기
-- G016_10 무통장입금전
-- G016_30 결제완료
-- G016_97 결제성공후DB실패
-- G016_98 결제전취소
-- G016_99 결제취소
SELECT *
FROM TB_PAYMENT
WHERE ORD_NO = 169
;
SELECT ORD_NO, COUNT(*) AS CNT
FROM TB_PAYMENT
GROUP BY ORD_NO
HAVING COUNT(*) > 1
;
SELECT CASE WHEN P.PG_GB = 'KCP' THEN FN_GET_CODE_NM('G014', P.PAY_MEANS)
WHEN P.PG_GB = 'NAVER' THEN '네이버페이'
WHEN P.PG_GB = 'NAVER_ORDER' THEN '네이버페이주문형'
WHEN P.PG_GB = 'KAKAO' THEN '카카오페이'
ELSE ''
END AS PAY_MEANS_NM /*결제수단명*/
, CASE WHEN P.PG_GB = 'KCP' THEN
CASE WHEN P.PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',P.VA_BANK),' / ',P.VA_NO)
WHEN P.PAY_MEANS = 'G014_20' THEN CONCAT(FN_GET_CODE_NM('G940',P.VA_BANK),' / ',P.VA_NO,' / ',P.VA_NM,' / ',DATE_FORMAT(P.VA_DEADLINE,'%Y-%m-%d %H:%i:%S'))
WHEN P.PAY_MEANS = 'G014_30' THEN CONCAT(P.CARD_NM,' / ',CASE WHEN CAST(P.CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(P.CARD_MIPS AS UNSIGNED),'개월') END)
ELSE ''
END
WHEN P.PG_GB IN ('NAVER','KAKAO') THEN
CASE WHEN P.PAY_MEANS = 'G014_10' THEN CONCAT(FN_GET_CODE_NM('G940',P.VA_BANK),' / ',P.VA_NO)
WHEN P.PAY_MEANS = 'G014_30' THEN CONCAT(P.CARD_NM,' / ',CASE WHEN CAST(P.CARD_MIPS AS UNSIGNED) = 0 THEN '일시불' ELSE CONCAT(CAST(P.CARD_MIPS AS UNSIGNED),'개월') END)
ELSE ''
END
END AS PAY_MEANS_DESC /*결제수단설명*/
, PG_CPN_AMT /*PG쿠폰금액*/
, NPAY_PNT_AMT /*네이버페이포인트금액(마일리지)*/
, CASE WHEN CASH_AUTH_NO IS NOT NULL AND CASH_TRADE_NO IS NOT NULL THEN 'Y'
ELSE 'N'
END AS CASH_RECEIP_REQ_YN /*현금영수증신청여부*/
FROM TB_PAYMENT P
WHERE P.ORD_NO = 6005
AND P.PAY_GB = 'O' /*주문시결제*/
AND P.PAY_STAT IN ('G016_30','G016_10') /*결제완료,무통장입금관련*/
;
style24-core Order.java
private Integer ordNo;
private int ordCurrAmt; /*주문상품금액*/
private int realDelvAmt; /*실배송비금액*/
private String delvFeeDesc; /*배송비설명*/
private String delvFeeCpnDesc; /*배송비쿠폰설명*/
private int totDcAmt; /*총할인금액*/
private int cpn1DcAmt; /*즉시할인쿠폰금액*/
private String cpn1CpnDcDesc; /*즉시할인쿠폰설명*/
private int tmtbDcAmt; /*다다익선인금액*/
private int tmtb1DcAmt; /*다다익선수량할인금액*/
private String tmtb1Desc; /*다다익선수량할인설명*/
private int tmtb2DcAmt; /*다다익선금액할인금액*/
private String tmtb2Desc; /*다다익선금액할인설명*/
private int goodsCpnDcAmt; /*상품쿠폰할인금액*/
private String goodsCpnDcDesc; /*상품쿠폰할인설명*/
private int cartCpnDcAmt; /*장바구니쿠폰할인금액*/
private String cartCpnDcDesc; /*장바구니쿠폰할인설명*/
private int prePntDcAmt; /*선포인트할인금액*/
private int totPayAmt; /*총결제금액*/
private int realOrdAmt; /*실결제금액*/
private String payMeansNm; /*결제수단명*/
private String payMeansDesc; /*결제수단설명*/
private int gfcdUseAmt; /*상품권사용금액*/
private String gfcdUseDesc; /*상품권사용설명*/
private int pntDcAmt; /*포인트할인금액*/
private int pgCpnAmt; /*PG쿠폰금액(KCP쿠폰금액)*/
private int npayPntAmt; /*네이버페이포인트금액(마일리지)*/
private int savePntAmt; /*적립포인트금액*/
private String cashReceipReqYn; /*현금영수증신청여부*/
주문결제내역
| 구분 |
합계 |
내역 |
구분 |
합계 |
내역 |
| 총결제금액(A+B+C) |
|
|
주문금액(A) |
|
|
|
|
|
배송비 합산(B) |
|
|
| 상품권 |
|
|
할인금액 총합(C) |
|
|
| 포인트 |
|
|
즉시할인쿠폰 |
|
|
| KCP쿠폰 |
|
|
다다익선할인 |
|
|
| 마일리지 |
|
|
상품쿠폰 |
|
|
| 적립포인트 |
|
|
장바구니쿠폰 |
|
|
| 현금영수증신청여부 |
|
|
선포인트할인 |
|
|