<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.style24.front.biz.dao.TsfOrderDao">

	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM   (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	<!--// Paging -->

	<!-- 마이페이지 주문 목록 조회 -->
	<select id="getOrderListForMypage" parameterType="Order" resultType="Order">
		/* TsfOrder.getOrderListForMypage */
		SELECT GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
		     , GROUP_CONCAT(Z.COLOR_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		     , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1
		     , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD2
		     , Z.ORD_NO
		     , Z.ORD_DT
		     , Z.GIFT_LIMIT_DT
		     , Z.GIFT_LIMIT_DAY
		     , Z.GIFT_COMPLETE_DT
		     , Z.GIFT_ADDR_INP_YN
		     , Z.DELV_EDDT
		     , Z.AUTO_DECIDE_LEFT_DAY
		     , Z.ORD_DTL_NO
		     , Z.ORD_EXCH_GB
		     , Z.GOODS_CD
		     , Z.GOODS_NM
		     , Z.LIST_PRICE
		     , Z.GOODS_TYPE
		     , Z.SYS_IMG_NM
		     , Z.ORD_DTL_STAT
		     , Z.ORD_QTY
		     , Z.CNCL_RTN_QTY
		     , Z.ORD_AMT
		     , Z.REAL_ORD_AMT
		     , Z.SAVE_PNT_AMT
		     , Z.CNCL_RTN_AMT
		     , Z.PNT_DC_AMT
		     , Z.GFCD_USE_AMT
		     , Z.SHIP_COMP_CD
		     , Z.GIFT_PACK_YN
		     , Z.SHIP_COMP_NM
		     , Z.INVOICE_NO
		     , Z.SUPPLY_COMP_CD
		     , Z.DELV_FEE_CD
		     , Z.SHOT_DELV_YN
		     , Z.CHANGEABLE_YN
		     , Z.RETURNABLE_YN
		     , Z.SELF_GOODS_YN
		     , Z.BRAND_NM
		     , Z.ORD_DTL_STAT_NM
		     , Z.REVIEW_SQ
		     , Z.REVIEWABLE_YN
		     , Z.PURCHASE_CONFIRM_DAY
		     , Z.DELV_RES_DT
		     , Z.ORD_REQ_CHG_QTY
		     , Z.ORD_CAN_CHG_QTY
		     , Z.BANK_CD
		     , Z.ACCOUNT_NO
		     , Z.ACCOUNT_NM
		     , Z.DELV_ADDR_SQ
		     , Z.RECIP_NM
		     , Z.PAY_STAT
		  FROM (SELECT O.ORD_NO
		             , DATE_FORMAT(O.ORD_DT, '%Y.%m.%d') AS ORD_DT
		             , CASE OD.GIFT_PACK_YN WHEN 'Y' THEN DATE_FORMAT(O.ORD_DT + INTERVAL 3 DAY, '%Y.%m.%d')
		                END                                                             AS GIFT_LIMIT_DT
		             , CASE OD.GIFT_PACK_YN WHEN 'Y' THEN TIMESTAMPDIFF(DAY, NOW(), O.ORD_DT + INTERVAL 3 DAY)
		                END                                                             AS GIFT_LIMIT_DAY
		             , CASE OD.GIFT_PACK_YN WHEN 'Y' THEN DATE_FORMAT(DA.UPD_DT, '%Y.%m.%d')
		                END                                                             AS GIFT_COMPLETE_DT
		             , DATE_FORMAT(OD.DELV_EDDT, '%m/%d')                               AS DELV_EDDT
		             , CASE WHEN DATEDIFF(OD.DELV_EDDT + INTERVAL #{autoDecideDay} DAY, NOW()) <![CDATA[>=]]> 1 THEN DATEDIFF(OD.DELV_EDDT + INTERVAL #{autoDecideDay} DAY, NOW())
		                    ELSE 1
		                END AS AUTO_DECIDE_LEFT_DAY
		             , OD.ORD_DTL_NO
		             , OD.ORD_EXCH_GB
		             , OD.GOODS_CD
		             , OD.GIFT_ADDR_INP_YN
		             , G.GOODS_NM
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), ODI.OPT_CD1) AS COLOR_NM
		             , (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = ODI.ITEM_CD)     AS ITEM_NM
		             , ODI.ORD_DTL_ITEM_SQ
		             , ODI.OPT_CD1
		             , ODI.OPT_CD2
		             , G.LIST_PRICE
		             , G.GOODS_TYPE
		             , CASE WHEN G.GOODS_TYPE != 'G056_S' AND G.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.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 = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
		             , OD.ORD_DTL_STAT
		             , OD.ORD_QTY
		             , OD.CNCL_RTN_QTY
		             , OD.ORD_AMT
		             , OD.REAL_ORD_AMT
		             , OD.SAVE_PNT_AMT
		             , OD.CNCL_RTN_AMT
		             , OD.PNT_DC_AMT
		             , OD.GFCD_USE_AMT
		             , OD.SHIP_COMP_CD
		             , OD.GIFT_PACK_YN
		             , (SELECT SHIP_COMP_NM
		                  FROM TB_SHIP_COMPANY Z
		                 WHERE Z.SHIP_COMP_CD = OD.SHIP_COMP_CD)                        AS SHIP_COMP_NM
		             , OD.INVOICE_NO
		             , OD.SUPPLY_COMP_CD
		             , OD.DELV_FEE_CD
		             , OD.SHOT_DELV_YN
		             , OD.CHANGEABLE_YN
		             , OD.RETURNABLE_YN
		             , G.SELF_GOODS_YN
		             , CASE BG.DISP_NM_LANG WHEN 'EN' THEN BG.BRAND_GROUP_ENM
		                                    ELSE BG.BRAND_GROUP_KNM
		                END                                                             AS BRAND_NM
		             , FN_GET_CODE_NM('G013', OD.ORD_DTL_STAT)                          AS ORD_DTL_STAT_NM
		             , R.REVIEW_SQ
		             , IF(NOW() <![CDATA[<]]> O.ORD_DT + INTERVAL 90 DAY, 'Y', 'N')     AS REVIEWABLE_YN
		             , TIMESTAMPDIFF(DAY, NOW(), OD.DELV_EDDT + INTERVAL 2 WEEK)        AS PURCHASE_CONFIRM_DAY
		             , (SELECT MIN(DATE_FORMAT(DELV_RES_DT,'%Y.%m.%d')) FROM TB_GOODS_RES_SELL WHERE GOODS_CD = G.GOODS_CD AND NOW() <![CDATA[ < ]]> DELV_RES_DT AND USE_YN = 'Y') AS DELV_RES_DT
		             , IFNULL(OCD.ORD_REQ_CHG_QTY, 0) AS ORD_REQ_CHG_QTY
		             , OD.ORD_QTY - OD.CNCL_RTN_QTY - IFNULL(OCD.ORD_REQ_CHG_QTY, 0)    AS ORD_CAN_CHG_QTY
		             , CA.BANK_CD
		             , CA.ACCOUNT_NO
		             , IFNULL(CA.ACCOUNT_NM, O.ORD_NM)                                  AS ACCOUNT_NM
		             , DA.DELV_ADDR_SQ
		             , DA.RECIP_NM
		             , P.PAY_STAT
		          FROM TB_ORDER O
		         INNER JOIN TB_ORDER_DETAIL OD
		            ON O.ORD_NO = OD.ORD_NO
		           AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25', 'G013_97', 'G013_98', 'G013_99')
		         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 G
		            ON OD.GOODS_CD = G.GOODS_CD
		         INNER JOIN TB_BRAND B
		            ON B.BRAND_CD = G.BRAND_CD
		           AND B.USE_YN = 'Y'
		         INNER JOIN TB_BRAND_GROUP BG
		            ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		           AND BG.USE_YN = 'Y'
		         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')
		           AND P.PG_GB <![CDATA[<>]]> 'NAVER_ORDER'
		          LEFT OUTER JOIN TB_REVIEW R
		            ON R.ORD_NO = OD.ORD_NO
		           AND R.ORD_DTL_NO = OD.ORD_DTL_NO
		           AND R.DEL_YN = 'N'
		          LEFT OUTER JOIN TB_DELIVERY_ADDR DA
		            ON DA.DELV_ADDR_SQ = OD.DELV_ADDR_SQ
		          LEFT OUTER JOIN (SELECT OD.ORD_DTL_NO
		                                , SUM(OCD.CHG_QTY) AS ORD_REQ_CHG_QTY
		                             FROM TB_ORDER_CHANGE_DETAIL OCD
		                            INNER JOIN TB_ORDER_DETAIL OD
		                               ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		                            INNER JOIN TB_ORDER_CHANGE OC
		                               ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		                            WHERE OCD.DEL_YN = 'N'
		<if test="ordNo != null and ordNo != ''">
		                              AND OD.ORD_NO = #{ordNo}
		</if>
		<if test="ordNoList != null">
		                              AND OD.ORD_NO IN
			<foreach collection="ordNoList" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		                              AND OCD.CHG_STAT IN ('G685_20', 'G685_21','G685_30', 'G685_31', 'G685_32', 'G685_33', 'G685_34', 'G685_50', 'G685_51')
		                              AND OC.WD_BF_SEND_YN = 'N'
		                            GROUP BY OCD.ORD_DTL_NO
		                          ) OCD
		            ON OD.ORD_DTL_NO = OCD.ORD_DTL_NO
		          LEFT OUTER JOIN TB_CUST_ACCOUNT CA
		            ON O.CUST_NO = CA.CUST_NO
		<where>
			<choose>
				<when test="custNo > 0">
		   AND O.CUST_NO = #{custNo}
				</when>
				<otherwise>
		   AND O.ORD_NO = #{ordNo}
		   AND O.ORD_PHNNO = #{ordPhnno}
		   AND O.ORD_NM = #{ordNm}
				</otherwise>
			</choose>
			<if test="ordNo != null and ordNo != ''">
		   AND O.ORD_NO = #{ordNo}
			</if>
			<if test="ordDtlNo != null and ordDtlNo != ''">
		   AND OD.ORD_DTL_NO = #{ordDtlNo}
			</if>
			<if test="ordDtlNoArr != null">
		   AND OD.ORD_DTL_NO IN
				<foreach collection="ordDtlNoArr" item="item" index="index"  open="(" close=")" separator=",">
					#{item}
				</foreach>
			</if>
			<if test="ordNoList != null">
		   AND O.ORD_NO IN
				<foreach collection="ordNoList" item="item" index="index"  open="(" close=")" separator=",">
					#{item}
				</foreach>
			</if>
			<if test="ordDtlStat != null and ordDtlStat != ''">
				<choose>
					<when test="ordDtlStat == 'G013_00'">
		   AND OD.ORD_DTL_STAT = 'G013_10'
					</when>
					<when test="ordDtlStat == 'G013_30'">
		   AND OD.ORD_DTL_STAT IN ('G013_30', 'G013_35')
					</when>
					<otherwise>
		   AND OD.ORD_DTL_STAT = #{ordDtlStat}
					</otherwise>
				</choose>
			</if>
		   AND O.DISP_YN = 'Y'
		   AND OD.ORD_DTL_STAT <![CDATA[<>]]> 'G013_25'
		   AND OD.ORD_QTY - OD.CNCL_RTN_QTY > 0
		   -- AND O.REG_DT > 20210901
		</where>
				) Z
		 GROUP BY Z.ORD_NO, Z.ORD_DT, Z.GIFT_LIMIT_DT, Z.GIFT_LIMIT_DAY, Z.GIFT_COMPLETE_DT, GIFT_ADDR_INP_YN, Z.DELV_EDDT, Z.AUTO_DECIDE_LEFT_DAY
		        , Z.ORD_DTL_NO, Z.ORD_EXCH_GB, Z.GOODS_CD, Z.GOODS_NM, Z.LIST_PRICE, Z.SYS_IMG_NM, Z.ORD_DTL_STAT, Z.ORD_QTY, Z.CNCL_RTN_QTY, Z.ORD_AMT
		        , Z.REAL_ORD_AMT, Z.SAVE_PNT_AMT, Z.CNCL_RTN_AMT, Z.PNT_DC_AMT, Z.GFCD_USE_AMT, Z.SHIP_COMP_CD, Z.GIFT_PACK_YN, Z.SHIP_COMP_NM, Z.INVOICE_NO
		        , Z.SUPPLY_COMP_CD, Z.DELV_FEE_CD, Z.SHOT_DELV_YN, Z.CHANGEABLE_YN, Z.SELF_GOODS_YN, Z.BRAND_NM, Z.ORD_DTL_STAT_NM, Z.REVIEW_SQ
		        , Z.REVIEWABLE_YN, Z.PURCHASE_CONFIRM_DAY, Z.DELV_RES_DT, Z.ORD_REQ_CHG_QTY, Z.ORD_CAN_CHG_QTY, Z.BANK_CD, Z.ACCOUNT_NO, Z.ACCOUNT_NM
		        , Z.DELV_ADDR_SQ, Z.RECIP_NM, Z.PAY_STAT
		 ORDER BY Z.ORD_NO DESC, Z.DELV_FEE_CD DESC, Z.ORD_DTL_NO DESC
	</select>

	<!-- 마이페이지 주문목록 페이징 처리 주문번호 수 조회 -->
	<select id="getPagingOrdNoListCount" parameterType="Order" resultType="int">
		/* TsfOrder.getPagingOrdNoListCount */
		SELECT COUNT(*) AS CNT
		  FROM (SELECT O.ORD_NO
		          FROM TB_ORDER O
		         INNER JOIN TB_ORDER_DETAIL OD
		            ON O.ORD_NO = OD.ORD_NO
		         INNER JOIN TB_PAYMENT P
		            ON O.ORD_NO = P.ORD_NO
		           AND P.PAY_STAT IN ('G016_10', 'G016_30')
		           AND P.PAY_GB = 'O'
		           AND P.PG_GB <![CDATA[<>]]> 'NAVER_ORDER'
		         WHERE O.DISP_YN = 'Y'
		           AND OD.ORD_QTY - OD.CNCL_RTN_QTY > 0
		           AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25', 'G013_97', 'G013_98', 'G013_99')
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		           AND O.ORD_DT BETWEEN CONCAT(#{stDate}, ' 00:00:00') AND CONCAT(#{edDate}, ' 23:59:59')
		</if>
		<if test="ordDtlStat != null and ordDtlStat != ''">
			<choose>
				<when test="ordDtlStat == 'G013_00'">
		           AND OD.ORD_DTL_STAT = 'G013_10'
				</when>
				<when test="ordDtlStat == 'G013_30'">
		           AND OD.ORD_DTL_STAT IN ('G013_30', 'G013_35')
				</when>
				<otherwise>
		           AND OD.ORD_DTL_STAT = #{ordDtlStat}
				</otherwise>
			</choose>
		</if>
		<choose>
			<when test="custNo > 0">
		           AND O.CUST_NO = #{custNo}
			</when>
			<otherwise>
		           AND O.ORD_NM = #{ordNm}
		           AND O.ORD_PHNNO = #{ordPhnno}
		           AND O.ORD_NO = #{ordNo}
			</otherwise>
		</choose>
		           -- AND O.REG_DT > 20210901
		         GROUP BY O.ORD_NO
		       ) Z
	</select>

	<!-- 마이페이지 주문목록 페이징 처리 주문번호 조회 -->
	<select id="getPagingOrdNoList" parameterType="Order" resultType="Order">
		/* TsfOrder.getPagingOrdNoList */
		<include refid="selectForPagingHeader"/>
		SELECT O.ORD_NO
		     , RANK() OVER(ORDER BY O.ORD_DT DESC) AS NUMB
		  FROM TB_ORDER O
		 INNER JOIN TB_ORDER_DETAIL OD
		    ON O.ORD_NO = OD.ORD_NO
		 INNER JOIN TB_PAYMENT P
		    ON O.ORD_NO = P.ORD_NO
		   AND P.PAY_STAT IN ('G016_10', 'G016_30')
		   AND P.PAY_GB = 'O'
		   AND P.PG_GB <![CDATA[<>]]> 'NAVER_ORDER'
		 WHERE O.DISP_YN = 'Y'
		   AND OD.ORD_QTY - OD.CNCL_RTN_QTY > 0
		   AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25', 'G013_97', 'G013_98', 'G013_99')
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		   AND O.ORD_DT BETWEEN CONCAT(#{stDate}, ' 00:00:00') AND CONCAT(#{edDate}, ' 23:59:59')
		</if>
		<if test="ordDtlStat != null and ordDtlStat != ''">
			<choose>
				<when test="ordDtlStat == 'G013_00'">
		   AND OD.ORD_DTL_STAT = 'G013_10'
				</when>
				<when test="ordDtlStat == 'G013_30'">
		   AND OD.ORD_DTL_STAT IN ('G013_30', 'G013_35')
				</when>
				<otherwise>
		   AND OD.ORD_DTL_STAT = #{ordDtlStat}
				</otherwise>
			</choose>
		</if>
		<choose>
			<when test="custNo > 0">
		   AND O.CUST_NO = #{custNo}
			</when>
			<otherwise>
		   AND O.ORD_NM = #{ordNm}
		   AND O.ORD_PHNNO = #{ordPhnno}
		   AND O.ORD_NO = #{ordNo}
			</otherwise>
		</choose>
		   -- AND O.REG_DT > 20210901
		 GROUP BY O.ORD_NO
		<include refid="selectForPagingFooter"/>
	</select>
	
	<!-- 마이페이지 주문목록 사은품 조회 -->
	<select id="getOrderFreeGiftListForMypage" parameterType="Order" resultType="Order">
		/* TsfOrder.getOrderFreeGiftListForMypage */
		SELECT FG.GOODS_NM
		     , OFV.USE_POINT
		  FROM TB_ORD_FREEGIFT OFG
		 INNER JOIN TB_ORD_FREEGIFT_VAL OFV
		    ON OFG.ORD_FREEGIFT_SQ = OFV.ORD_FREEGIFT_SQ
		 INNER JOIN TB_FREE_GOODS FG
		    ON OFV.PRODUCT_NO = FG.PRODUCT_NO
		   AND OFV.PRODUCT_CODE = FG.PRODUCT_CODE
		 WHERE OFG.ORD_NO = #{ordNo}
	</select>

	<!-- 마이페이지 주문상세 금액정보 조회 -->
	<select id="getOrderAmtForMypage" parameterType="Order" resultType="Order">
		/* TsfOrder.getOrderAmtForMypage */
		SELECT SUM(OD.ORD_AMT)          AS ORD_AMT
		     , SUM(OD.CNCL_RTN_AMT)     AS CNCL_RTN_AMT
		     , SUM(OD.CPN1_DC_AMT)      AS CPN1_DC_AMT
		     , SUM(OD.TMTB1_DC_AMT)     AS TMTB1_DC_AMT
		     , SUM(OD.TMTB2_DC_AMT)     AS TMTB2_DC_AMT
		     , SUM(OD.CART_CPN_DC_AMT)  AS CART_CPN_DC_AMT
		     , SUM(OD.GOODS_CPN_DC_AMT) AS GOODS_CPN_DC_AMT
		     , SUM(OD.PRE_PNT_DC_AMT)   AS PRE_PNT_DC_AMT
		     , SUM(OD.PNT_DC_AMT)       AS PNT_DC_AMT
		     , SUM(OD.GFCD_USE_AMT)     AS GFCD_USE_AMT
		     , SUM(OD.CPN1_DC_AMT + OD.TMTB1_DC_AMT + OD.TMTB2_DC_AMT + OD.CART_CPN_DC_AMT + OD.GOODS_CPN_DC_AMT + OD.PRE_PNT_DC_AMT + OD.PNT_DC_AMT + OD.GFCD_USE_AMT) AS TOTAL_DC_AMT
		     , SUM(OD.REAL_ORD_AMT)     AS REAL_ORD_AMT
		     , SUM(OD.SAVE_PNT_AMT)     AS SAVE_PNT_AMT
		     , DF.DELV_FEE
		     , DF.DELV_CPN_DC_AMT
		     , DF.REAL_DELV_AMT
		     , P.PAY_AMT
		     , P.NPAY_PNT_AMT
		     , P.PG_CPN_AMT
		  FROM TB_ORDER_DETAIL OD
		  LEFT OUTER JOIN (
		      SELECT ORD_NO
		           , SUM(DELV_FEE)        AS DELV_FEE
		           , SUM(DELV_CPN_DC_AMT) AS DELV_CPN_DC_AMT
		           , SUM(REAL_DELV_AMT)   AS REAL_DELV_AMT
		        FROM TB_DELIVERY_FEE
		       WHERE ORD_NO = #{ordNo}
		         AND DELV_FEE_GB = 'G018_10'
		       GROUP BY ORD_NO
		  ) DF
		    ON OD.ORD_NO = DF.ORD_NO
		  LEFT OUTER JOIN (
		      SELECT ORD_NO
		           , SUM(PAY_AMT)         AS PAY_AMT
		           , SUM(NPAY_PNT_AMT)    AS NPAY_PNT_AMT
		           , SUM(PG_CPN_AMT)      AS PG_CPN_AMT
		        FROM TB_PAYMENT
		       WHERE ORD_NO = #{ordNo}
		       GROUP BY ORD_NO
		  ) P
		    ON OD.ORD_NO = P.ORD_NO
		 WHERE OD.ORD_NO = #{ordNo}
		   AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25', 'G013_97', 'G013_98', 'G013_99')
		 GROUP BY DF.DELV_FEE, DF.DELV_CPN_DC_AMT, DF.REAL_DELV_AMT, P.PAY_AMT, P.NPAY_PNT_AMT, P.PG_CPN_AMT
	</select>

	<!-- 마이페이지 주문상세 결제정보 조회 -->
	<select id="getPaymentInfoForMypage" parameterType="Order" resultType="Order">
		/* TsfOrder.getPaymentInfoForMypage */
		SELECT PAY_SQ
		     , ORD_NO
		     , PAY_DT
		     , PAY_MEANS
		     , FN_GET_CODE_NM('G014', PAY_MEANS) AS PAY_MEANS_NM
		     , PAY_AMT
		     , PG_CPN_AMT
		     , NPAY_PNT_AMT
		     , PAY_GB
		     , PAY_STAT
		     , PG_GB
		     , PG_TID
		     , PG_TRADE_NO
		     , PG_SHOP_ID
		     , CARD_TYPE
		     , CARD_KIND
		     , CARD_BANK
		     , CARD_NM
		     , IF(CARD_MIPS = 0, '일시불', CONCAT(CARD_MIPS, '개월')) AS CARD_MIPS
		     , CARD_PCABLE_YN
		     , VA_NO
		     , VA_NM
		     , FN_GET_CODE_NM('G940', VA_BANK) AS VA_BANK
		     , VA_DEADLINE
		     , DATE_FORMAT(VA_DEADLINE, '%Y.%m.%d') AS VA_DEADLINE_YMD
		     , DATE_FORMAT(VA_DEADLINE, '%H:%i:%S') AS VA_DEADLINE_HMS
		     , TELECOM
		     , ESCROW_YN
		     , CASH_AUTH_NO
		     , CASH_TRADE_NO
		     , ORD_CHG_SQ
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		  FROM TB_PAYMENT
		<where>
			<if test="ordNo != null and ordNo != ''">
		   AND ORD_NO = #{ordNo}
			</if>
			<if test="paySq != null and paySq != ''">
		   AND PAY_SQ = #{paySq}
			</if>
			<if test="ordChgSq != null and ordChgSq != ''">
		   AND ORD_CHG_SQ = #{ordChgSq}
			</if>
			<if test="pgTid != null and pgTid != ''">
		   AND PG_TID = #{pgTid}
			</if>
			<if test="payGb != null and payGb != ''">
		   AND PAY_GB = #{payGb}
			</if>
			<if test="pgGb != null and pgGb != ''">
		   AND PG_GB = #{pgGb}
			</if>
		   AND PAY_STAT IN ('G016_10', 'G016_30')
		</where>
	</select>

	<!-- 마이페이지 주문상세 배송지 정보 조회 -->
	<select id="getOrderDeliveryAddrInfo" parameterType="Order" resultType="Order">
		/* TsfOrder.getOrderDeliveryAddrInfo */
		SELECT DA.DELV_ADDR_SQ
		     , DA.RECIP_NM
		     , DA.RECIP_PHNNO
		     , DA.RECIP_TELNO
		     , DA.RECIP_ZIPCODE
		     , DA.RECIP_BASE_ADDR
		     , DA.RECIP_DTL_ADDR
		     , DA.DELV_MEMO
		     , DL.RTN_LOC_NM
		     , DL.RTN_LOC_TELNO
		     , DL.RTN_LOC_ZIPCODE
		     , DL.RTN_LOC_BASE_ADDR
		     , DL.RTN_LOC_DTL_ADDR
		     , DA.GIFT_MSG
		     , DA.REG_NO
		     , DA.REG_DT
		     , DA.UPD_NO
		     , DA.UPD_DT
		  FROM TB_DELIVERY_ADDR DA
		 INNER JOIN TB_ORDER_DETAIL OD
		    ON OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ
		   AND OD.ORD_NO = #{ordNo}
		<choose>
			<when test="ordDtlNo != null and ordDtlNo != ''">
		   AND OD.ORD_DTL_NO = #{ordDtlNo}
			</when>
			<otherwise>
		   AND OD.ORD_EXCH_GB = 'O'
			</otherwise>
		</choose>
		<if test="supplyCompCd != null and supplyCompCd != ''">
		   AND OD.SUPPLY_COMP_CD = #{supplyCompCd}
		</if>
		<if test="ordDtlNoArr != null">
		   AND OD.ORD_DTL_NO IN
			<foreach collection="ordDtlNoArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		  LEFT OUTER JOIN TB_DELIVERY_LOC DL
		    ON OD.DELV_LOC_CD = DL.DELV_LOC_CD
		   AND DL.USE_YN = 'Y'
		 LIMIT 1
	</select>

	<!-- 마이페이지 주문상세 주문내역삭제 처리 -->
	<update id="updateOrderDisplayYn" parameterType="Order">
		/* TsfOrder.updateOrderDisplayYn */
		UPDATE TB_ORDER
		   SET DISP_YN = 'N'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		 WHERE ORD_NO = #{ordNo}
	</update>

	<!-- 마이페이지 주문상세 주문내역삭제 처리 -->
	<update id="updatePaymentForOrderFinished" parameterType="Payment">
		/* TsfOrder.updatePaymentForOrderFinished */
		UPDATE TB_PAYMENT SET
		      PAY_DT = DATE_FORMAT(#{payDt} , '%Y%m%d%H%i%S')
		    , PAY_STAT = #{payStat}
		    , PG_CPN_AMT = #{pgCpnAmt}
		    , NPAY_PNT_AMT = #{npayPntAmt}
		    , PG_TID = #{pgTid}
		    , PG_TRADE_NO = #{pgTradeNo}
		    , PG_SHOP_ID = #{pgShopId}
		    , CARD_TYPE = #{cardType}
		    , CARD_NM = #{cardNm}
		    , CARD_MIPS = #{cardMips}
		    , CARD_PCABLE_YN = IFNULL(#{cardPcableYn}, 'Y')
		    , VA_NO = #{vaNo}
		    , VA_BANK = #{vaBank}
		    , VA_DEADLINE = DATE_FORMAT(#{vaDeadline} , '%Y%m%d%H%i%S')
		    , TELECOM = #{telecom}
		    , CASH_AUTH_NO = #{cashAuthNo}
		    , CASH_TRADE_NO = #{cashTradeNo}
		    , UPD_NO = #{updNo}
		    , UPD_DT = NOW()
		WHERE PAY_SQ = #{paySq}
		  AND ORD_NO = #{ordNo}
	</update>
	
	<!-- 고객 특정기간 구매 건수 -->
	<select id="getCustFirstOrderCount" parameterType="Coupon" resultType="int">
		/* TsfOrder.getCustFirstOrderCount */
		SELECT COUNT(*) 
		FROM TB_ORDER
		WHERE CUST_NO = #{custNo}
		AND ORD_DT BETWEEN CONCAT(#{buyStdt}, '000000') and CONCAT(#{buyEddt}, '235959')
	</select>
	
	<!-- 보유쿠폰체크 -->
	<select id="getCheckCustCpnSq" parameterType="Order" resultType="int">
		/* TsfOrder.getCustCpnSqCheck */
		SELECT COUNT(1)
		FROM   TB_CUST_COUPON
		WHERE  1=1
		AND    CUST_CPN_SQ = #{custCpnSq}
		AND    CUST_NO = #{custNo}
		AND    USED_DT IS NULL
	</select>
	
	<!-- 사은품 수량 및 사용여부 체크 -->
	<select id="getCheckFreegiftVal" parameterType="Order" resultType="int">
		/* TsfOrder.getCheckFreegiftVal */
		SELECT SUM(CNT) CNT
		FROM (
		    SELECT COUNT(1) AS CNT
		    FROM   TB_FREEGIFT_VAL FV
		    WHERE  1=1
		    AND    FV.FREEGIFT_VAL_SQ IN
		<foreach collection="freegiftValArr" item="item" index="index"  open="(" close=")" separator=",">
			#{item}
		</foreach> 
		    AND    FV.ITEM_QTY <![CDATA[>]]> FV.LEFT_QTY
		    UNION  ALL
		    SELECT COUNT(1) AS CNT
		    FROM   TB_FREEGIFT_VAL FV
		    WHERE  1=1
		    AND    FV.FREEGIFT_VAL_SQ IN
		<foreach collection="freegiftValArr" item="item" index="index"  open="(" close=")" separator=",">
			#{item}
		</foreach> 
		    AND    FV.DEL_YN = 'Y'
		) A
	</select>

	<!-- 상품권 현금영수증 정보 등록 -->
	<insert id="createGiftcartReceipt" parameterType="GiftCard">
		/* TsfOrder.createGiftcartReceipt : 상품권 현금영수증 정보 등록 */
		INSERT INTO TB_GIFTCARD_RECEIPT (
		      ORD_NO
		    , TRADE_NO
		    , AUTH_NO
		    , AMT
		    , REG_DT
		    , UPD_DT
		) VALUES (
		      #{ordNo}
		    , #{tradeNo}
		    , #{authNo}
		    , #{usGfcdAmt}
		    , NOW()
		    , NOW()
		)
	</insert>
	
	<!-- 보증보험 API 연동위한 정보조회 -->
	<select id="getGoodsInfoForInsurance" parameterType="Order" resultType="Order">
		/* TsfOrder.getGoodsInfoForInsurance : 보증보험 API 연동위한 정보조회 */
		SELECT G.GOODS_NM
		     , OD.REAL_ORD_AMT
		     , OD.ORD_QTY
		     , O.ORD_NM
		     , P.PAY_MEANS 
		     , P.VA_BANK
		     , P.VA_NM
		     , P.VA_NO
		     , FN_GET_CODE_NM('G940', P.VA_BANK) AS BANK_NM
		     , P.PG_TID
		     , O.ORD_PHNNO 
		     , O.ORD_EMAIL 
		     , INS.BIRTH_GEN 
		     , DA.RECIP_NM 
		     , DA.RECIP_PHNNO 
		     , DA.RECIP_ZIPCODE 
		     , DA.RECIP_BASE_ADDR 
		     , DA.RECIP_DTL_ADDR 
		FROM   TB_INSURANCE INS
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     OD.ORD_NO = INS.ORD_NO
		INNER  JOIN TB_GOODS G
		ON     OD.GOODS_CD = G.GOODS_CD
		INNER  JOIN TB_PAYMENT P
		ON     OD.ORD_NO = P.ORD_NO
		INNER  JOIN TB_ORDER O
		ON     P.ORD_NO = O.ORD_NO
		INNER  JOIN TB_DELIVERY_ADDR DA
		ON     OD.DELV_ADDR_SQ = DA.DELV_ADDR_SQ 
		WHERE  1=1
		AND    INS.IF_YN = 'N'
		AND    P.PAY_GB = 'O'
		AND    INS.ORD_NO = #{ordNo}
	</select>
	
	<!-- 비회원 주문 여부 정보 조회 -->
	<select id="getNoMemberOrderInfo" parameterType="Order" resultType="int">
		/* TsfOrder.getNoMemberOrderInfo */
		SELECT COUNT(*)
		  FROM TB_ORDER
		 WHERE ORD_NO = #{ordNo}
		   AND ORD_NM = #{ordNm}
		   AND ORD_PHNNO = #{ordPhnno}
	</select>

	<select id="getRealPaymentAmt" parameterType="Order" resultType="int">
		SELECT OD.REAL_ORD_AMT + DF.REAL_DELV_AMT AS PAY_AMT
		FROM   (SELECT OD.ORD_NO
		             , SUM(OD.REAL_ORD_AMT) AS REAL_ORD_AMT
		        FROM   TB_ORDER_DETAIL OD
		        WHERE  OD.ORD_NO = #{ordNo}
		        GROUP  BY OD.ORD_NO) OD
		INNER  JOIN (SELECT DF.ORD_NO
		                 , SUM(DF.REAL_DELV_AMT) AS REAL_DELV_AMT
		            FROM   TB_DELIVERY_FEE DF
		            WHERE  DF.ORD_NO = #{ordNo}
		            GROUP  BY DF.ORD_NO) DF
		ON     OD.ORD_NO = DF.ORD_NO
	</select>
</mapper>
