<?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.TsfCartDao">

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

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

	<!-- 금일 상품 주문 수량 조회 -->
	<select id="getGoodsTodayOrderCnt" parameterType="Cart" resultType="int">
		/* TsfCart.getGoodsTodayOrderCnt : 금일 상품 주문 수량 조회 */
		SELECT IFNULL(SUM(OD.ORD_QTY), 0) AS CNT
		FROM   TB_ORDER O
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     O.ORD_NO = OD.ORD_NO
		WHERE  1=1
		AND    OD.ORD_EXCH_GB = 'O'
		AND    DATE(ORD_DT) = CURRENT_DATE
		AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_00'
		AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_98'
		AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_99'
		AND    OD.GOODS_CD = #{goodsCd}
		AND    O.CUST_NO = #{custNo}
	</select>

	<!-- 장바구니 등록 세트 상품 확인 -->
	<select id="selectHasSetItemCartList" parameterType="Cart" resultType="Cart">
		/* TsfCart.selectHasSetItemCartList : 장바구니 등록 세트 상품 확인 */
		SELECT CART_SQ
		FROM   (SELECT COUNT(1) AS CNT
					 , CART_SQ
					 , #{goodsCd} AS GOODS_CD
				FROM   (
						${itemCdSql}
					   ) A
				WHERE  1=1
				GROUP  BY CART_SQ ) AS A
		INNER  JOIN ( SELECT GC.GOODS_CD
						   , COUNT(1) AS CNT
					  FROM   TB_GOODS_COMPOSE GC
					  WHERE  GC.GOODS_CD = #{goodsCd}
					  AND    GC.USE_YN = 'Y'
					  GROUP  BY GC.GOODS_CD ) B
		ON     A.GOODS_CD = B.GOODS_CD
		AND    A.CNT = B.CNT
		WHERE  1=1
		<if test="notCartSq != null and notCartSq > 0">
		AND    A.CART_SQ <![CDATA[ <> ]]> #{notCartSq}
		</if>
	</select>

	<!-- 장바구니 등록 일반 or 딜 상품 확인 -->
	<select id="selectHasNormalDealItemCartList" parameterType="Cart" resultType="int">
		/* TsfCart.selectHasNormalDealItemCartList : 장바구니 등록 일반 or 딜 상품 확인 */
		SELECT C.CART_SQ
		FROM   TB_CART C
		INNER  JOIN TB_CART_DETAIL CD
		ON     C.CART_SQ = CD.CART_SQ
		WHERE  1=1
		<if test="custNo != null and custNo > 0">
		AND    C.CUST_NO = #{custNo}
		</if>
		<if test="custNo == null or custNo == 0">
		AND    C.CUST_NO = 0
		AND    C.JSESSION_ID = #{jsessionId}
		</if>
		AND    C.GOODS_CD = #{goodsCd}
		AND    C.CART_GB = #{cartGb}
		AND    CD.OPT_CD = #{optCd}
		AND    C.CART_SQ <![CDATA[ <> ]]> #{notCartSq}
	</select>

	<!-- 장바구니 신규 등록 -->
	<insert id="insertCartInfo" parameterType="Cart" keyProperty="cartSq">
		/* TsfCart.insertCartInfo : 장바구니 신규 등록 */
		INSERT INTO TB_CART (
				  CART_GB
				, GOODS_CD
				, GOODS_QTY
				, DEAL_GOODS_CD
				, JSESSION_ID
				, CUST_NO
				, AF_LINK_CD
				, ITHR_CD
				, CONTENTS_LOC
				, PLAN_DTL_SQ
				, REG_NO
				, REG_DT
				, UPD_NO
				, UPD_DT)
		SELECT #{cartGb}
			 , GOODS_CD
			 , #{goodsQty}
			 , #{dealGoodsCd}
			 , #{jsessionId}
			 , #{custNo}
			 , #{afLinkCd}
			 , #{ithrCd}
			 , #{contentsLoc}
			 , #{planDtlSq}
			 , #{regNo}
			 , CURRENT_TIMESTAMP
			 , #{updNo}
			 , CURRENT_TIMESTAMP
		  FROM TB_GOODS
		 WHERE GOODS_CD = #{goodsCd}
	</insert>

	<!-- 장바구니 상세 신규 등록 -->
	<insert id="insertCartDetailInfo" parameterType="Cart" keyProperty="cartDtlSq">
		/* TsfCart.insertCartDetailInfo : 장바구니 상세 신규 등록 */
		INSERT INTO TB_CART_DETAIL (
				  CART_SQ
				, ITEM_CD
				, OPT_CD
				, OPT_CD1
				, OPT_CD2
				, SKU_MODEL_NO
				, PRODUCT_NO
				, PRODUCT_CODE
				, ITEM_QTY
				, DISP_ORD
				, REG_NO
				, REG_DT
				, UPD_NO
				, UPD_DT
		)
		SELECT #{cartSq}
			 , IFNULL(GC.COMPS_GOODS_CD, G.GOODS_CD)		 <!-- 세트 상품이 아니면 원상품코드 -->
			 , O.OPT_CD
			 , O.OPT_CD1
			 , O.OPT_CD2
			 , O.SKU_MODEL_NO
			 , O.PRODUCT_NO
			 , O.PRODUCT_CODE
			 , IFNULL(GC.QTY, 1)
			 , IFNULL(GC.DISP_ORD, 1)						<!-- TB_GOODS_COMPOSE DISP_ORD 따라 CART_DETAIL도 동일하게 진행 -->
			 , #{regNo}
			 , CURRENT_TIMESTAMP
			 , #{updNo}
			 , CURRENT_TIMESTAMP
		  FROM TB_GOODS G
		 INNER JOIN TB_OPTION O
			ON G.GOODS_CD = O.GOODS_CD
		  LEFT OUTER JOIN TB_GOODS_COMPOSE GC		<!-- 일반 상품 제외 확인 필요하여 join -->
			ON GC.COMPS_GOODS_CD = G.GOODS_CD
		   AND GC.GOODS_CD = #{goodsCd}
		   AND GC.COMPS_GOODS_CD = #{itemCd}
		   AND GC.GOODS_TYPE = 'G056_S'				<!-- 세트 상품만 구성 상품 코드로 올림. deal 상품은 원상품코드로 올려야함 -->
		 WHERE 1=1
		<choose>
			<when test="goodsType == 'G056_S'">
		   AND O.GOODS_CD = #{itemCd}				<!-- 세트상품은 itemCd으로 조회 -->
			</when>
			<otherwise>
		   AND O.GOODS_CD = #{goodsCd}
			</otherwise>
		</choose>
		   AND O.OPT_CD = #{optCd}
		 ORDER BY GC.DISP_ORD
	</insert>

	<!-- 장바구니 UPDATE -->
	<update id="updateCartInfo" parameterType="Cart">
		/* TsfCart.updateCartInfo : 장바구니 UPDATE */
		UPDATE TB_CART SET
			  GOODS_QTY = GOODS_QTY + #{goodsQty}
			, UPD_NO = #{updNo}
			, UPD_DT = CURRENT_TIMESTAMP
		WHERE CART_SQ = #{cartSq}
		AND   GOODS_CD = #{goodsCd}
		AND   CUST_NO = #{custNo}
	</update>

	<!-- 장바구니 상세 UPDATE -->
	<update id="updateCartDetail" parameterType="Cart">
		/* TsfCart.updateCartDetail : 장바구니 상세 UPDATE */
		UPDATE TB_CART_DETAIL A, (SELECT GOODS_CD, OPT_CD1, OPT_CD2, SKU_MODEL_NO, PRODUCT_NO, PRODUCT_CODE FROM TB_OPTION WHERE GOODS_CD = #{itemCd} AND OPT_CD = #{optCd}) B SET
			  A.OPT_CD = #{optCd}
			, A.OPT_CD1 = B.OPT_CD1
			, A.OPT_CD2 = B.OPT_CD2
			, A.SKU_MODEL_NO = B.SKU_MODEL_NO
			, A.PRODUCT_NO = B.PRODUCT_NO
			, A.PRODUCT_CODE = B.PRODUCT_CODE
			, A.UPD_NO = #{updNo}
			, A.UPD_DT = CURRENT_TIMESTAMP
		WHERE A.CART_DTL_SQ = #{cartDtlSq}
		  AND A.ITEM_CD = B.GOODS_CD
	</update>

	<!-- 장바구니 이력 정보 저장 -->
	<insert id="insertCartHst" parameterType="Cart">
		/* TsfCart.insertCartHst : 장바구니 이력 정보 저장 */
		INSERT INTO TB_CART_HST (  CART_SQ
								 , CART_GB
								 , GOODS_CD
								 , GOODS_QTY
								 , DEAL_GOODS_CD
								 , ORD_NO
								 , CUST_NO
								 , AF_LINK_CD
								 , ITHR_CD
								 , CONTENTS_LOC
								 , PLAN_DTL_SQ
								 , REG_NO
								 , REG_DT
		)
		SELECT C.CART_SQ
			 , C.CART_GB
			 , C.GOODS_CD
			 , C.GOODS_QTY
			 , C.DEAL_GOODS_CD
			 , IFNULL(#{ordNo}, 0) AS ORD_NO
			 , C.CUST_NO
			 , C.AF_LINK_CD
			 , C.ITHR_CD
			 , C.CONTENTS_LOC
			 , PLAN_DTL_SQ
			 , #{regNo}
			 , CURRENT_TIMESTAMP
		FROM   TB_CART C
		WHERE  C.CART_SQ = #{cartSq}
	</insert>

	<!-- 장바구니 상세 이력 정보 저장 -->
	<insert id="insertCartDetailHst" parameterType="Cart">
		/* TsfCart.insertCartDetailHst : 장바구니 상세 이력 정보 저장 */
		INSERT INTO TB_CART_DETAIL_HST ( CART_DTL_SQ
									   , CART_SQ
									   , ITEM_CD
									   , OPT_CD
									   , OPT_CD1
									   , OPT_CD2
									   , SKU_MODEL_NO
									   , PRODUCT_NO
									   , PRODUCT_CODE
									   , ITEM_QTY
									   , REG_NO
									   , REG_DT
		)
		SELECT CD.CART_DTL_SQ
			 , CD.CART_SQ
			 , CD.ITEM_CD
			 , CD.OPT_CD
			 , CD.OPT_CD1
			 , CD.OPT_CD2
			 , CD.SKU_MODEL_NO
			 , CD.PRODUCT_NO
			 , CD.PRODUCT_CODE
			 , CD.ITEM_QTY
			 , #{regNo}
			 , CURRENT_TIMESTAMP
		FROM   TB_CART_DETAIL CD
		WHERE  CD.CART_SQ = #{cartSq}
	</insert>

	<!-- 다다익선 기본 상품 조회 -->
	<select id="selectMoreBetterApplyCartList" parameterType="Order" resultType="Order">
		/* TsfCart.selectMoreBetterApplyCartList : 다다익선 기본 상품 조회 */
		SELECT A.CART_SQ
			 , MAX(A.QTY_TMTB_SQ) AS QTY_TMTB_SQ
			 , MAX(A.AMT_TMTB_SQ) AS AMT_TMTB_SQ
			 , MAX(A.QTY_TMTB_NM) AS QTY_TMTB_NM
			 , MAX(A.AMT_TMTB_NM) AS AMT_TMTB_NM
			 , MAX(A.QTY_GOODS_GB) AS QTY_GOODS_GB
			 , MAX(A.AMT_GOODS_GB) AS AMT_GOODS_GB
			 , MAX(A.QTY_TMTB_SUM_QTY) AS QTY_TMTB_SUM_QTY
			 , MAX(A.QTY_TMTB_SUM_AMT) AS QTY_TMTB_SUM_AMT
			 , MAX(A.AMT_TMTB_SUM_QTY) AS AMT_TMTB_SUM_QTY
			 , MAX(A.AMT_TMTB_SUM_AMT) AS AMT_TMTB_SUM_AMT
			 , MAX(A.AMT_TMTB_CNT) AS AMT_TMTB_CNT
			 , MAX(A.QTY_TMTB_CNT) AS QTY_TMTB_CNT
			 , A.GOODS_CD
			 , A.GOODS_QTY
			 , (A.CURR_PRICE + A.OPT_ADD_PRICE) * A.GOODS_QTY AS CURR_PRICE
		FROM   (SELECT C.CART_SQ
					 , CASE WHEN T.APPLY_GB = 'G810_10' THEN T.TMTB_SQ END QTY_TMTB_SQ
					 , CASE WHEN T.APPLY_GB = 'G810_11' THEN T.TMTB_SQ END AMT_TMTB_SQ
					 , CASE WHEN T.APPLY_GB = 'G810_10' THEN T.TMTB_NM END QTY_TMTB_NM
					 , CASE WHEN T.APPLY_GB = 'G810_11' THEN T.TMTB_NM END AMT_TMTB_NM
					 , CASE WHEN T.APPLY_GB = 'G810_10' THEN TAG.GOODS_GB END QTY_GOODS_GB
					 , CASE WHEN T.APPLY_GB = 'G810_11' THEN TAG.GOODS_GB END AMT_GOODS_GB
					 , CASE WHEN T.APPLY_GB = 'G810_10' THEN SUM_DATA.TMTB_SUM_QTY END QTY_TMTB_SUM_QTY
					 , CASE WHEN T.APPLY_GB = 'G810_10' THEN SUM_DATA.TMTB_SUM_AMT END QTY_TMTB_SUM_AMT
					 , CASE WHEN T.APPLY_GB = 'G810_11' THEN SUM_DATA.TMTB_SUM_QTY END AMT_TMTB_SUM_QTY
					 , CASE WHEN T.APPLY_GB = 'G810_11' THEN SUM_DATA.TMTB_SUM_AMT END AMT_TMTB_SUM_AMT
					 , CASE WHEN T.APPLY_GB = 'G810_10' THEN TAG2.TMTB_CNT END QTY_TMTB_CNT
					 , CASE WHEN T.APPLY_GB = 'G810_11' THEN TAG2.TMTB_CNT END AMT_TMTB_CNT
					, TAG.GOODS_CD
					, C.GOODS_QTY
					, FN_GET_APPLY_CPN1_PRICE(C.GOODS_CD, #{frontGb}) AS CURR_PRICE
					, IFNULL(OP.ADD_PRICE, 0) AS OPT_ADD_PRICE
				FROM   TB_CART C
				INNER  JOIN TB_TMTB_APPLY_GOODS TAG
				ON	   C.GOODS_CD = TAG.GOODS_CD
				AND	   TAG.DEL_YN = 'N'
				INNER  JOIN (SELECT TAG.TMTB_SQ
								  , COUNT(1) AS TMTB_CNT
							 FROM   TB_TMTB_APPLY_GOODS TAG
							 INNER  JOIN TB_CART C
							 ON	 TAG.GOODS_CD = C.GOODS_CD
							 WHERE  TAG.DEL_YN = 'N'
							<if test="cartSqArr != null and cartSqArr.length > 0">
							 AND	C.CART_SQ IN
								<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
										#{item}
								</foreach>
							</if>
							 GROUP  BY TAG.TMTB_SQ) AS TAG2
				ON	 TAG.TMTB_SQ = TAG2.TMTB_SQ
				INNER  JOIN TB_TMTB T
				ON	   T.TMTB_SQ = TAG.TMTB_SQ
				AND	   T.TMTB_STAT = 'G232_11' /*진행*/
				INNER  JOIN TB_GOODS G
				ON	   C.GOODS_CD = G.GOODS_CD
				INNER  JOIN TB_CART_DETAIL CD
				ON	 C.CART_SQ = CD.CART_SQ
				INNER  JOIN (SELECT TAG.TMTB_SQ
								  , SUM((FN_GET_APPLY_CPN1_PRICE(C.GOODS_CD, #{frontGb}) + IFNULL(OP.ADD_PRICE, 0)) * C.GOODS_QTY) AS TMTB_SUM_AMT
								  , SUM(C.GOODS_QTY) AS TMTB_SUM_QTY
							 FROM   TB_TMTB_APPLY_GOODS TAG
							 INNER  JOIN TB_CART C
							 ON	 TAG.GOODS_CD = C.GOODS_CD
							 INNER  JOIN TB_GOODS G
							 ON	 C.GOODS_CD = G.GOODS_CD
							 INNER  JOIN TB_TMTB T
							 ON	 TAG.TMTB_SQ = T.TMTB_SQ
							 LEFT   OUTER JOIN TB_TMTB_APPLY_GOODS NOT_APPLY
							 ON	 TAG.GOODS_CD = NOT_APPLY.GOODS_CD
							 AND	TAG.TMTB_SQ = NOT_APPLY.TMTB_SQ
							 AND	NOT_APPLY.GOODS_GB = 'G800_30'   /* 제외상품 */
							 LEFT   OUTER JOIN (SELECT OP.ADD_PRICE
							 						 , C.CART_SQ
							 					FROM   TB_CART C
							 					INNER  JOIN TB_CART_DETAIL CD
							 					ON	 C.CART_SQ = CD.CART_SQ
												 INNER  JOIN TB_OPTION OP
												 ON	 C.GOODS_CD = OP.GOODS_CD
												 AND	CD.OPT_CD = OP.OPT_CD
												 WHERE  1=1) OP
							 ON	 C.CART_SQ = OP.CART_SQ
							 WHERE  1=1
							 AND	NOT_APPLY.TMTB_SQ IS NULL
							 AND	T.DEL_YN = 'N'
							 AND	TAG.DEL_YN = 'N'
							 AND	G.GOODS_STAT = 'G008_90'
							 AND	G.SELF_MALL_YN = 'Y'
							 <if test="cartSqArr != null and cartSqArr.length > 0">
							 	AND	C.CART_SQ IN
							 	<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
							 		#{item}
							 	</foreach>
							 </if>
							 GROUP BY TAG.TMTB_SQ
					   ) SUM_DATA
				ON	 T.TMTB_SQ = SUM_DATA.TMTB_SQ
				LEFT   JOIN TB_OPTION OP
				ON	 CD.ITEM_CD = OP.GOODS_CD
				AND	 CD.OPT_CD = OP.OPT_CD
				LEFT   OUTER JOIN TB_TMTB_APPLY_GOODS NOT_APPLY
				ON	 T.TMTB_SQ = NOT_APPLY.TMTB_SQ
				AND	C.GOODS_CD = NOT_APPLY.GOODS_CD
				AND	NOT_APPLY.GOODS_GB = 'G800_30'   /* 제외상품 */
				AND	NOT_APPLY.DEL_YN = 'N'
				WHERE  1=1
				AND	NOT_APPLY.TMTB_SQ IS NULL
				AND	T.DEL_YN = 'N'
				AND	T.TMTB_ST_DT <![CDATA[<=]]> NOW()
				AND	T.TMTB_ED_DT >= NOW()
				AND	G.GOODS_STAT = 'G008_90'
				AND	G.SELF_MALL_YN = 'Y'
				<if test="cartSqArr != null and cartSqArr.length > 0">
				AND	C.CART_SQ IN
					<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
						#{item}
					</foreach>
				</if>
			   ) A
		WHERE  1=1
		GROUP  BY A.CART_SQ
			 , A.GOODS_CD
			 , A.GOODS_QTY
			 , A.CURR_PRICE
			 , A.OPT_ADD_PRICE
		ORDER BY A.CART_SQ
	</select>

	<!-- 다다익선 적용 상품 SUM DATA 조회 -->
	<select id="selectApplyMoreBetterCartInfoList" parameterType="Order" resultType="Order">
		/* TsfCart.selectApplyMoreBetterCartInfoList : 다다익선 적용 상품 SUM DATA 조회 */
		SELECT TAG.TMTB_SQ
			 , SUM((FN_GET_APPLY_CPN1_PRICE(C.GOODS_CD, #{frontGb}) + IFNULL(OP.ADD_PRICE, 0)) * C.GOODS_QTY) AS TMTB_SUM_AMT
			 , SUM(C.GOODS_QTY) AS TMTB_SUM_QTY
		FROM   TB_TMTB_APPLY_GOODS TAG
		INNER  JOIN TB_CART C
		ON	 TAG.GOODS_CD = C.GOODS_CD
		INNER  JOIN TB_GOODS G
		ON	 C.GOODS_CD = G.GOODS_CD
		INNER  JOIN TB_TMTB T
		ON	 TAG.TMTB_SQ = T.TMTB_SQ
		LEFT   OUTER JOIN TB_TMTB_APPLY_GOODS NOT_APPLY
		ON	 TAG.GOODS_CD = NOT_APPLY.GOODS_CD
		AND	TAG.TMTB_SQ = NOT_APPLY.TMTB_SQ
		AND	NOT_APPLY.GOODS_GB = 'G800_30'   /* 제외상품 */
		LEFT   OUTER JOIN (SELECT OP.ADD_PRICE
								, C.CART_SQ
						   FROM   TB_CART C
						   INNER  JOIN TB_CART_DETAIL CD
						   ON	 C.CART_SQ = CD.CART_SQ
						   INNER  JOIN TB_OPTION OP
						   ON	 C.GOODS_CD = OP.GOODS_CD
						   AND	CD.OPT_CD = OP.OPT_CD
						   WHERE  1=1
			   ) OP
		ON	 C.CART_SQ = OP.CART_SQ
		WHERE  1=1
		AND	NOT_APPLY.TMTB_SQ IS NULL
		AND	T.DEL_YN = 'N'
		AND	TAG.DEL_YN = 'N'
		AND	C.CART_GB = 'G026_BC'
		AND	G.GOODS_STAT = 'G008_90'
		AND	G.SELF_MALL_YN = 'Y'
		<if test="tmtbSqs != null and tmtbSqs.length > 0">
		AND	TAG.TMTB_SQ IN
			<foreach collection="tmtbSqs" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		<if test="cartSqArr != null and cartSqArr.length > 0">
		AND	C.CART_SQ IN
			<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		GROUP BY TAG.TMTB_SQ
	</select>

	<!-- 다다익선 SECTION 적용 조건 조회 -->
	<select id="selectTmtbSectionValList" parameterType="int" resultType="Order">
		/* TsfCart.selectTmtbSectionValList : 다다익선 SECTION 적용 조건 조회 */
		SELECT TC.SECTION_GB
		     , TC.SECTION_VAL
		     , TC.TMTB_SQ
		     , TV.DC_WAY
		     , TV.DC_VAL
		     , CASE WHEN TS.TMTB_SQ IS NULL THEN 'N' ELSE 'Y' END AS DEFAULT_YN
		FROM   TB_TMTB_SECTION TC
		INNER  JOIN TB_TMTB_VAL TV
		ON     TC.TMTB_SECTION_SQ = TV.TMTB_SECTION_SQ
		LEFT   OUTER JOIN (SELECT TS.TMTB_SQ
		                        , MIN(TS.SECTION_VAL) AS SECTION_VAL
		                   FROM   TB_TMTB_SECTION TS
		                   WHERE  TS.DEL_YN = 'N'
		                   GROUP  BY TS.TMTB_SQ) TS
		ON     TC.TMTB_SQ = TS.TMTB_SQ
		AND    TC.SECTION_VAL = TS.SECTION_VAL
		WHERE  1=1
		AND	   TC.DEL_YN = 'N'
		AND	   TV.DEL_YN = 'N'
		<if test="list != null and list.size() > 0">
		AND	   TC.TMTB_SQ IN
			<foreach collection="list" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		ORDER  BY TMTB_SQ, SECTION_VAL
	</select>

	<!-- 장바구니 상품 조회 -->
	<select id="getCartGoodsList" parameterType="Order" resultType="Order">
		/* TsfCart.getCartGoodsList : 장바구니 상품 조회 */
		SELECT GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.CART_DTL_SQ SEPARATOR '!@!') AS ITEM_NM
			 , GROUP_CONCAT(Z.ITEM_CD ORDER BY Z.CART_DTL_SQ) AS ITEM_CD
			 , GROUP_CONCAT(Z.OPT_CD ORDER BY Z.CART_DTL_SQ) AS OPT_CD
			 , GROUP_CONCAT(Z.COLOR_NM ORDER BY Z.CART_DTL_SQ) AS COLOR_NM
			 , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.CART_DTL_SQ) AS OPT_CD1
			 , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.CART_DTL_SQ) AS OPT_CD2
			 , GROUP_CONCAT(Z.ITEM_QTY ORDER BY Z.CART_DTL_SQ) AS ITEM_QTYR
			 , (SELECT COUNT(1) CNT FROM TB_WISHLIST WHERE CUST_NO = #{custNo} AND CUST_NO != 0 AND GOODS_CD = Z.GOODS_CD) AS WISH_CNT
			 , Z.CART_SQ
		     , Z.GOODS_STAT
			 , Z.GOODS_CD
			 , Z.GOODS_NM
			 , Z.BRAND_ENM
		     , Z.BRAND_KNM
			 , Z.SUPPLY_COMP_NM
			 , Z.GOODS_QTY
		     , Z.DELV_RES_DT
			 , Z.GOODS_TYPE
			 , Z.SUPPLY_COMP_CD
			 , Z.DELV_FEE_CD
		     , Z.MIN_ORD_AMT
		     , Z.DELV_FEE
		     , Z.CART_DELV_FEE_CD
		     , CASE WHEN Z.ORD_CAN_YN = 'Y' AND Z.CUST_TODAY_ORD = 0 AND Z.NOCUST_TODAY_ORD = 0 THEN 'Y' ELSE 'N' END AS ORD_CAN_YN
		     , Z.NEW_CUST_CAN_YN
		     , Z.ORD_CAN_QTY
		     , Z.MIN_ORD_QTY
		     , Z.MAX_ORD_QTY
		     , (Z.CURR_PRICE + SUM(Z.OPT_ADD_PRICE)) * Z.GOODS_QTY AS CURR_PRICE
		     , (Z.LIST_PRICE + SUM(Z.OPT_ADD_PRICE)) * Z.GOODS_QTY AS LIST_PRICE
		     , Z.SOLDOUT_YN
		     , CASE WHEN #{frontGb} = 'P' THEN Z.PNT_PRATE ELSE Z.PNT_MRATE END AS PNT_RATE
		     , SYS_IMG_NM
		     , Z.BRAND_GROUP_NM
		FROM   (SELECT C.CART_SQ
		             , C.GOODS_CD
		             , C.GOODS_QTY
		             , CD.ITEM_CD
		             , CD.OPT_CD
		             , CD.ITEM_QTY
		             , CD.CART_DTL_SQ
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(O.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), O.OPT_CD1) AS COLOR_NM
		             , O.OPT_CD1
		             , O.OPT_CD2
		             , G.GOODS_TYPE
		             , G.GOODS_STAT
		             , G.SUPPLY_COMP_CD
		             , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS GOODS_NM
		             , G.PNT_PRATE
		             , G.PNT_MRATE
		             , STOCK.SOLDOUT_YN
		             , STOCK.ORD_CAN_QTY
		             , G.MAX_ORD_QTY
		             , G.MIN_ORD_QTY
		             , FN_GET_APPLY_CPN1_PRICE(C.GOODS_CD, #{frontGb}) AS CURR_PRICE
		             , G.LIST_PRICE
		             , IFNULL(O.ADD_PRICE, 0) AS OPT_ADD_PRICE
		             , BR.BRAND_ENM
		             , BR.BRAND_KNM
		             , DFP.MIN_ORD_AMT
		             , DFP.DELV_FEE
		             , (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY WHERE SUPPLY_COMP_CD = G.SUPPLY_COMP_CD) AS SUPPLY_COMP_NM
		             , (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = CD.ITEM_CD) AS ITEM_NM
		             , (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
		             , CASE WHEN G.SELF_GOODS_YN = 'Y' THEN 'WMS'
		                    ELSE DFP.DELV_FEE_CD END AS DELV_FEE_CD
		             , CASE WHEN GQDS.GOODS_CD IS NULL AND G.SELF_GOODS_YN = 'Y' THEN 'SHOT'
		                    WHEN G.SELF_GOODS_YN = 'Y' THEN 'WMS'
		                    ELSE DFP.DELV_FEE_CD END AS CART_DELV_FEE_CD
		             , CASE WHEN STOCK.ORD_CAN_YN = 'Y' AND (SELECT  CASE WHEN MAX(Z.CNT1) IS NULL THEN 'Y' ELSE CASE WHEN MAX(Z.CNT2) > 0 THEN 'Y'
		                                                                                                              ELSE 'N' END END
		                                                     FROM    (SELECT COUNT(1) AS CNT1
		                                                                   , CASE WHEN GOG.CUST_GRADE = CT.CUST_GRADE THEN 1
		                                                                          ELSE 0 END AS CNT2
		                                                              FROM   TB_GOODS_ORDER_GRADE GOG
		                                                              WHERE  GOG.GOODS_CD = C.GOODS_CD
		                                                              GROUP  BY GOODS_CD, CUST_GRADE
		                                                              UNION  ALL
		                                                              SELECT NULL, NULL) Z ) = 'Y' AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT THEN 'Y'
		                    ELSE 'N' END AS ORD_CAN_YN
		             , CASE WHEN C.CUST_NO > 0 AND (SELECT IFNULL(SUM(OD.ORD_QTY), 0)
		                                            FROM   TB_ORDER_DETAIL OD
		                                            INNER  JOIN TB_ORDER O
		                                            ON     OD.ORD_NO = O.ORD_NO
		                                            WHERE  O.CUST_NO = C.CUST_NO
		                                            AND    OD.ORD_EXCH_GB = 'O'
		                                            AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_00'
		                                            AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_98'
		                                            AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_99'
		                                            AND    OD.GOODS_CD = C.GOODS_CD
		                                            AND    DATE(ORD_DT) = CURRENT_DATE) + C.GOODS_QTY > G.DAY_MAX_ORD_QTY THEN 1 ELSE 0 END CUST_TODAY_ORD
		             , CASE WHEN C.CUST_NO = 0 AND C.GOODS_QTY > G.DAY_MAX_ORD_QTY THEN 1 ELSE 0 END AS NOCUST_TODAY_ORD
		             , CASE WHEN G.NEW_CUST_ORD_YN = 'N' THEN 'Y'
		                    WHEN C.CUST_NO > 0 AND G.NEW_CUST_ORD_YN = 'Y' AND CT.JOIN_DT BETWEEN G.NEW_CUST_ORD_STDT AND G.NEW_CUST_ORD_EDDT THEN 'Y'
		                    ELSE 'N' END AS NEW_CUST_CAN_YN
		             , 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 = CD.OPT_CD1 AND DEFAULT_IMG_YN = 'Y' LIMIT 1)
		            		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' LIMIT 1) END AS SYS_IMG_NM
		             , (CASE WHEN D.DISP_NM_LANG = 'EN' THEN D.BRAND_GROUP_ENM ELSE D.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		        FROM   TB_CART C
		        INNER  JOIN TB_CART_DETAIL CD
		        ON     C.CART_SQ = CD.CART_SQ
		        INNER  JOIN TB_GOODS G
		        ON     C.GOODS_CD = G.GOODS_CD
		        AND    G.SELF_MALL_YN = 'Y'
		        AND    G.GOODS_STAT <![CDATA[ <> ]]> 'G008_99'
		        INNER  JOIN TB_BRAND BR
		        ON     G.BRAND_CD = BR.BRAND_CD
		        INNER  JOIN TB_BRAND_GROUP D
		        ON     BR.BRAND_GROUP_NO = D.BRAND_GROUP_NO
		        AND    D.USE_YN = 'Y'
		        INNER  JOIN TB_DELV_FEE_POLICY DFP
		        ON     G.DELV_FEE_CD = DFP.DELV_FEE_CD
		        AND    G.SUPPLY_COMP_CD = DFP.SUPPLY_COMP_CD
		        INNER  JOIN TB_OPTION O
		        ON     CD.ITEM_CD = O.GOODS_CD
		        AND    CD.OPT_CD = O.OPT_CD
		        LEFT   OUTER JOIN TB_CUSTOMER CT
		        ON     C.CUST_NO = CT.CUST_NO
		        INNER  JOIN (   SELECT A.CART_SQ
		                             , MAX(A.SOLDOUT_YN) AS SOLDOUT_YN
		                             , MAX(A.ORD_CAN_YN) AS ORD_CAN_YN
		                             , MIN(A.ORD_CAN_QTY) AS ORD_CAN_QTY
		                        FROM   (SELECT C.CART_SQ
		                                     , CASE WHEN VS.SOLDOUT_YN = 'N' AND VS.CURR_STOCK_QTY >= GC.QTY AND COMP_GOODS.GOODS_STAT != 'G008_70' AND G.GOODS_STAT != 'G008_70' THEN 'N'
		                                            ELSE 'Y' END AS SOLDOUT_YN
		                                     , CASE WHEN G.GOODS_STAT != 'G008_90' AND G.GOODS_STAT != 'G008_70' AND COMP_GOODS.GOODS_STAT != 'G008_70' AND COMP_GOODS.GOODS_STAT != 'G008_90' THEN 'N'
		                                            ELSE 'Y' END AS ORD_CAN_YN
		                                     , CASE WHEN G.GOODS_STAT = 'G008_70' OR VS.SOLDOUT_YN = 'Y' OR VS.CURR_STOCK_QTY = 0 THEN 0
		                                            ELSE CASE WHEN G.MAX_ORD_QTY > TRUNCATE(VS.CURR_STOCK_QTY / GC.QTY, 0) THEN TRUNCATE(VS.CURR_STOCK_QTY / GC.QTY, 0)
		                                                      ELSE G.MAX_ORD_QTY END
		                                            END AS ORD_CAN_QTY
		                                FROM   TB_CART C
		                                INNER  JOIN TB_CART_DETAIL CD
		                                ON     C.CART_SQ = CD.CART_SQ
		                                INNER  JOIN TB_GOODS G
		                                ON     C.GOODS_CD = G.GOODS_CD
		                                AND    G.SELF_MALL_YN = 'Y'
		                                INNER  JOIN TB_GOODS COMP_GOODS
		                                ON     CD.ITEM_CD = COMP_GOODS.GOODS_CD
		                                INNER  JOIN TB_GOODS_COMPOSE GC
		                                ON     C.GOODS_CD = GC.GOODS_CD
		                                AND    CD.ITEM_CD = GC.COMPS_GOODS_CD
		                                INNER  JOIN VW_STOCK VS
		                                ON     CD.ITEM_CD = VS.GOODS_CD
		                                AND    CD.OPT_CD = VS.OPT_CD
		                                WHERE  1=1
		                                AND    C.CART_GB = 'G026_BC'
		                                <if test="custNo == 0">
		                                AND    C.CUST_NO = 0
		                                AND    C.JSESSION_ID = #{jsessionId}
		                                </if>
		                                <if test="custNo != 0">
		                                AND    C.CUST_NO = #{custNo}
		                                </if>
		                                UNION ALL
		                                SELECT C.CART_SQ
		                                     , CASE WHEN VS.SOLDOUT_YN = 'N' AND VS.CURR_STOCK_QTY > 0 AND G.GOODS_STAT != 'G008_70' THEN 'N'
		                                            ELSE 'Y' END AS SOLDOUT_YN
		                                     , CASE WHEN G.GOODS_STAT != 'G008_90' AND G.GOODS_STAT != 'G008_70' THEN 'N'
		                                            ELSE 'Y' END AS ORD_CAN_YN
		                                     , CASE WHEN G.GOODS_STAT = 'G008_70' OR VS.SOLDOUT_YN = 'Y' THEN 0
		                                            ELSE CASE WHEN G.MAX_ORD_QTY > VS.CURR_STOCK_QTY THEN CURR_STOCK_QTY
		                                                      ELSE G.MAX_ORD_QTY END
		                                            END AS ORD_CAN_QTY
		                                FROM   TB_CART C
		                                INNER  JOIN TB_CART_DETAIL CD
		                                ON     C.CART_SQ = CD.CART_SQ
		                                INNER  JOIN TB_GOODS G
		                                ON     C.GOODS_CD = G.GOODS_CD
		                                AND    G.SELF_MALL_YN = 'Y'
		                                INNER  JOIN VW_STOCK VS
		                                ON     C.GOODS_CD = VS.GOODS_CD
		                                AND    CD.OPT_CD = VS.OPT_CD
		                                WHERE  1=1
		                                AND    C.CART_GB = 'G026_BC'
		                                <if test="custNo == 0">
		                                AND    C.CUST_NO = 0
		                                AND    C.JSESSION_ID = #{jsessionId}
		                                </if>
		                                <if test="custNo != 0">
		                                AND    C.CUST_NO = #{custNo}
		                                 </if>
		                               ) A
		                        GROUP  BY  A.CART_SQ
		               ) STOCK
		        ON     C.CART_SQ = STOCK.CART_SQ
		        LEFT   OUTER JOIN TB_GOODS_SHOT_DELV_SKIP GQDS
		        ON     G.GOODS_CD = GQDS.GOODS_CD
		        WHERE  1=1
		        <if test="custNo == 0">
		        AND    C.CUST_NO = 0
		        AND    C.JSESSION_ID = #{jsessionId}
		        </if>
		        <if test="custNo != 0">
		        AND	C.CUST_NO = #{custNo}
		        </if>
		        <if test="cartSqArr != null and cartSqArr.length > 0">
		        AND	C.CART_SQ IN
		            <foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		        ORDER BY G.SUPPLY_COMP_CD ) Z
		WHERE  1=1
		GROUP  BY WISH_CNT
		     , Z.CART_SQ
		     , Z.GOODS_STAT
		     , Z.GOODS_CD
		     , Z.GOODS_NM
		     , Z.BRAND_ENM
		     , Z.BRAND_KNM
		     , Z.SUPPLY_COMP_NM
		     , Z.GOODS_QTY
		     , Z.DELV_RES_DT
		     , Z.GOODS_TYPE
		     , Z.SUPPLY_COMP_CD
		     , Z.DELV_FEE_CD
		     , Z.MIN_ORD_AMT
		     , Z.DELV_FEE
		     , Z.CART_DELV_FEE_CD
		     , ORD_CAN_YN
		     , Z.ORD_CAN_QTY
		     , Z.MIN_ORD_QTY
		     , Z.MAX_ORD_QTY
		     , CURR_PRICE
		     , LIST_PRICE
		     , Z.SOLDOUT_YN
		     , PNT_RATE
		     , SYS_IMG_NM
		     , Z.BRAND_GROUP_NM
		     , Z.NEW_CUST_CAN_YN
		ORDER  BY Z.DELV_FEE_CD DESC
		     , Z.CART_SQ DESC
	</select>

	<!-- 장바구니 업체별 배송비 -->
	<select id="getCartListDelvFeeInfoList" parameterType="Order" resultType="Order">
		/* TsfCart.getCartListDelvFeeInfoList */
		SELECT DFP.MIN_ORD_AMT
			 , DFP.DELV_FEE
			 , DFP.DELV_FEE_CRITE
			 , DFP.DELV_FEE_CD
			 , COUNT(G.SUPPLY_COMP_CD) AS COMP_CNT
		FROM   TB_CART C
		INNER  JOIN TB_GOODS G
		ON	 C.GOODS_CD = G.GOODS_CD
		INNER  JOIN TB_DELV_FEE_POLICY DFP
		ON	 G.DELV_FEE_CD = DFP.DELV_FEE_CD
		AND  G.SUPPLY_COMP_CD = DFP.SUPPLY_COMP_CD
		AND	DFP.USE_YN = 'Y'
		WHERE  1=1
		AND	G.SELF_MALL_YN = 'Y'
		AND	C.CART_SQ IN
		<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
			#{item}
		</foreach>
		GROUP  BY DFP.MIN_ORD_AMT
			 , DFP.DELV_FEE_CRITE
			 , DFP.DELV_FEE_CD
			 , DFP.DELV_FEE
	</select>

	<!-- 장바구니 삭제 -->
	<delete id="deleteCart" parameterType="Cart">
		<!-- TsfCart.deleteCart : 장바구니 삭제 -->
		DELETE FROM TB_CART
		WHERE  CART_SQ IN
		<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
			#{item}
		</foreach>
	</delete>

	<!-- 장바구니 삭제 -->
	<delete id="deleteCartDetail" parameterType="Cart">
		<!-- TsfCart.deleteCartDetail : 장바구니 삭제 -->
		DELETE FROM TB_CART_DETAIL
		WHERE  CART_SQ IN
		<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
			#{item}
		</foreach>
	</delete>
	
	<!-- 장바구니담긴상품 고객번호 Update -->
	<update id="updateCartOfCustNo" parameterType="Cart">
		/* TsfCart.updateCartOfCustNo */
		UPDATE TB_CART
		SET	CUST_NO = #{custNo}
			 , UPD_NO = #{updNo}
			 , UPD_DT = NOW()
		WHERE  JSESSION_ID = #{jsessionId}
	</update>
	
	<!-- 로그인 후 처리할 장바구니 목록 조회 -->
	<select id="getCartListAfterLogin" parameterType="Cart" resultType="Cart">
		/* TsfCart.getCartListAfterLogin */
		SELECT A.CART_GB
			 , A.GOODS_CD
			 , A.ITEM_OPTS
			 , IFNULL(B.CART_SQ,A.CART_SQ) AS UPD_CART_SQ /*업데이트할 장바구니번호*/
			 , CASE WHEN B.CART_SQ IS NOT NULL THEN
						A.CART_SQ
					ELSE
						0
			   END						 AS DEL_CART_SQ /*삭제할 장바구니번호(0이면 삭제할 장바구니번호 없음)*/
		FROM   (
				/*장바구니에 담을 상품 목록*/
				SELECT C.CART_SQ
					 , C.CART_GB
					 , C.GOODS_CD
					 , GROUP_CONCAT(CONCAT(CD.ITEM_CD,':',CD.OPT_CD1,':',CD.OPT_CD2) ORDER BY CD.ITEM_CD, CD.OPT_CD1, CD.OPT_CD2 SEPARATOR ';') AS ITEM_OPTS
				FROM   TB_CART C
					 , TB_CART_DETAIL CD
				WHERE  C.CART_SQ = CD.CART_SQ
				AND	C.CUST_NO = #{custNo}
				AND	C.JSESSION_ID = #{jsessionId}
				GROUP  BY C.CART_SQ, C.CART_GB, C.GOODS_CD
			   ) A
		LEFT OUTER JOIN (
				/*장바구니에 담긴 기존 상품 목록*/
				SELECT C.CART_SQ
					 , C.CART_GB
					 , C.GOODS_CD
					 , GROUP_CONCAT(CONCAT(CD.ITEM_CD,':',CD.OPT_CD1,':',CD.OPT_CD2) ORDER BY CD.ITEM_CD, CD.OPT_CD1, CD.OPT_CD2 SEPARATOR ';') AS ITEM_OPTS
				FROM   TB_CART C
					 , TB_CART_DETAIL CD
				WHERE  C.CART_SQ = CD.CART_SQ
				AND	C.CUST_NO = #{custNo}
				AND	C.JSESSION_ID <![CDATA[<>]]> #{jsessionId}
				GROUP  BY C.CART_SQ, C.CART_GB, C.GOODS_CD
			   ) B
		ON	 A.CART_GB = B.CART_GB
		AND	A.GOODS_CD = B.GOODS_CD
		AND	A.ITEM_OPTS = B.ITEM_OPTS
	</select>

	<!-- 로그인 후 장바구니 수정 -->
	<update id="updateCartAfterLogin" parameterType="Cart">
		/* TsfCart.updateCartAfterLogin */
		UPDATE TB_CART
		SET	GOODS_QTY = GOODS_QTY + IFNULL((SELECT A.GOODS_QTY
												 FROM ( SELECT GOODS_QTY
														  FROM TB_CART
														 WHERE CART_SQ = #{delCartSq}
														   AND CUST_NO = #{custNo} ) A
												),0)
		WHERE  CART_SQ = #{updCartSq}
		AND    CUST_NO = #{custNo}
		AND    CART_GB = 'G026_BC'
	</update>

	<!-- 로그인 후 장바구니단품 삭제 -->
	<delete id="deleteCartItemAfterLogin" parameterType="Cart">
		/* TsfCart.deleteCartItemAfterLogin */
		DELETE FROM TB_CART_DETAIL
		WHERE  CART_SQ IN (SELECT CART_SQ
						   FROM   TB_CART
						   WHERE  CART_SQ = #{delCartSq}
						   AND    CUST_NO = #{custNo}
		                   AND    CART_GB = 'G026_BC'
						  )
	</delete>

	<!-- 로그인 후 장바구니 삭제 -->
	<delete id="deleteCartAfterLogin" parameterType="Cart">
		/* TsfCart.deleteCartAfterLogin */
		DELETE FROM TB_CART
		WHERE  CART_SQ = #{delCartSq}
		AND    CUST_NO = #{custNo}
		AND    CART_GB = 'G026_BC'
	</delete>
	
	<!-- 로그인 후 장바구니이력 생성 -->
	<update id="createCartHistoryAfterLogin" parameterType="Cart">
		/* TsfCart.createCartHistoryAfterLogin */
		INSERT INTO TB_CART_HST (
			   CART_SQ
			 , CART_GB
			 , GOODS_CD
			 , GOODS_QTY
			 , DEAL_GOODS_CD
			 , ORD_NO
			 , CUST_NO
			 , AF_LINK_CD
			 , ITHR_CD
			 , CONTENTS_LOC
			 , PLAN_DTL_SQ
			 , REG_NO
			 , REG_DT
		)
		SELECT CART_SQ
			 , CART_GB
			 , GOODS_CD
			 , GOODS_QTY
			 , DEAL_GOODS_CD
			 , 0
			 , CUST_NO
			 , AF_LINK_CD
			 , ITHR_CD
			 , CONTENTS_LOC
			 , PLAN_DTL_SQ
			 , #{regNo}
			 , NOW()
		FROM   TB_CART C
		WHERE  CART_SQ = #{updCartSq}
		AND    CART_GB = 'G026_BC'
	</update>

	<!-- 장바구니 상품 정보 조회 -->
	<select id="selectCartGoodsInfo" parameterType="Cart" resultType="Cart">
		/* TsfCart.selectCartGoodsInfo : 장바구니 상품 마스터 정보 조회 */
		SELECT G.GOODS_CD
			 , G.GOODS_TYPE
		     , G.GOODS_NM
			 , BR.BRAND_ENM
			 , BR.BRAND_KNM
		     , G.MIN_ORD_QTY
		     , CASE WHEN G.MAX_ORD_QTY > G.DAY_MAX_ORD_QTY THEN G.DAY_MAX_ORD_QTY ELSE G.MAX_ORD_QTY END AS MAX_ORD_QTY
			 , CA.CART_SQ
			 , CA.GOODS_QTY
			 , G.SELF_GOODS_YN
		     , G.GOODS_STAT
		     , FN_GET_APPLY_CPN1_PRICE(CA.GOODS_CD, #{frontGb}) AS CURR_PRICE
		FROM   TB_CART CA
		INNER  JOIN TB_GOODS G
		ON     CA.GOODS_CD = G.GOODS_CD
		INNER  JOIN TB_BRAND BR
		ON     G.BRAND_CD = BR.BRAND_CD
		WHERE  CA.CART_SQ = #{cartSq}
		AND    G.SELF_MALL_YN = 'Y'
		AND    G.GOODS_STAT IN ('G008_90', 'G008_70')
	</select>

	<!-- 장바구니 세트 구성 상품 정보 조회 -->
	<select id="selectCartSetGoodsInfo" parameterType="Cart" resultType="Cart">
		/* TsfCart.selectCartSetGoodsInfo : 장바구니 세트 구성 상품 정보 조회 */
		SELECT GC.COMPS_GOODS_CD AS GOODS_CD
			 , GC.DISP_ORD
			 , GC.QTY
			 , CD.CART_DTL_SQ
			 , CD.ITEM_CD
			 , CD.OPT_CD
			 , CD.OPT_CD1
			 , CD.OPT_CD2
			 , G.SELF_GOODS_YN
			 , G.GOODS_NM
		     , G.GOODS_STAT
			 , CASE WHEN SELF_GOODS_YN = 'Y' THEN (SELECT COLOR_KNM FROM TB_COLOR WHERE COLOR_CD = CD.OPT_CD1)
					ELSE CD.OPT_CD1 END AS COLOR_NM
		FROM   TB_CART CA
		INNER  JOIN TB_CART_DETAIL CD
		ON     CA.CART_SQ = CD.CART_SQ
		INNER  JOIN TB_GOODS_COMPOSE GC
		ON     CA.GOODS_CD = GC.GOODS_CD
		AND    CD.ITEM_CD = GC.COMPS_GOODS_CD
		INNER  JOIN TB_GOODS G
		ON     GC.COMPS_GOODS_CD = G.GOODS_CD
		WHERE  1=1
		AND    G.SELF_MALL_YN = 'Y'
		AND    G.GOODS_STAT IN ('G008_90', 'G008_70')
		AND    GC.USE_YN = 'Y'
		AND    GC.GOODS_CD = #{goodsCd}
		AND    CA.CART_SQ = #{cartSq}
		ORDER  BY CA.GOODS_CD, GC.COMPS_GOODS_CD, GC.DISP_ORD
	</select>

	<!-- 장바구니 상품 컬러 정보 조회 -->
	<select id="selectCartGoodsColorList" parameterType="Cart" resultType="Cart">
		/* TsfCart.selectCartGoodsColorList : 장바구니 상품 컬러 정보 조회 */
		SELECT A.*
		     , IFNULL(CASE WHEN A.CART_OPT_CD1 IS NOT NULL AND SELF_GOODS_YN = 'Y' THEN C.COLOR_KNM
		                   ELSE A.CART_OPT_CD1 END, A.CART_OPT_CD1) AS CART_COLOR_NM
		FROM   (SELECT ST.GOODS_CD
		             , MAX(CASE WHEN SELF_GOODS_YN = 'Y' THEN (SELECT COLOR_KNM FROM TB_COLOR WHERE COLOR_CD = ST.OPT_CD1)
		                        ELSE ST.OPT_CD1 END) AS COLOR_NM
		             , MAX(CASE WHEN SELF_GOODS_YN = 'Y' AND GOODS_TYPE != 'G056_S' THEN (SELECT SYS_IMG_NM FROM TB_GOODS_IMG WHERE GOODS_CD = ST.GOODS_CD AND COLOR_CD = ST.OPT_CD1 AND DEFAULT_IMG_YN = 'Y' LIMIT 1)
		                        ELSE (SELECT SYS_IMG_NM FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = G.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' LIMIT 1) END) AS SYS_IMG_NM
		             , ST.OPT_CD1
		             , G.MAIN_COLOR_CD
		             , G.SELF_GOODS_YN
		             , MAX(CD.CART_DTL_SQ) AS CART_DTL_SQ
		             , MAX(ST.DISP_ORD) AS DISP_ORD
		             , MAX(CD.OPT_CD1) AS CART_OPT_CD1
		             , MAX(CD.OPT_CD2) AS CART_OPT_CD2
		             , MAX(CD.OPT_CD) AS CART_OPT_CD
		             , MAX(ST.SOLDOUT_YN) AS SOLDOUT_YN
		             , ROW_NUMBER() OVER() AS COLOR_NO
		        FROM   TB_GOODS G
		        INNER  JOIN VW_STOCK ST
		        ON     G.GOODS_CD = ST.GOODS_CD
		        LEFT   OUTER JOIN TB_CART_DETAIL CD
		        ON     G.GOODS_CD = CD.ITEM_CD
		        AND    ST.OPT_CD = CD.OPT_CD
		        AND    CD.CART_SQ = #{cartSq}
		        WHERE  ST.DISP_YN = 'Y'
		        AND    G.SELF_MALL_YN = 'Y'
		        AND    G.GOODS_STAT IN ('G008_90', 'G008_70')
		        AND    G.GOODS_CD = #{goodsCd}
		        GROUP  BY ST.GOODS_CD
		                , ST.OPT_CD1
		                , G.SELF_GOODS_YN
		                , G.MAIN_COLOR_CD ) A
		LEFT   OUTER JOIN TB_COLOR C
		ON     A.OPT_CD1 = C.COLOR_CD
		AND    C.USE_YN = 'Y'
		ORDER  BY NULLIF(C.COLOR_KNM, A.OPT_CD1)
	</select>

	<!-- 장바구니 상품 사이즈 정보 조회 -->
	<select id="selectCartGoodsSizeList" parameterType="Cart" resultType="Cart">
		/* TsfCart.selectCartGoodsSizeList : 장바구니 상품 사이즈 정보 조회 */
		SELECT ST.GOODS_CD
		     , ST.OPT_CD
		     , ST.OPT_CD1
		     , ST.OPT_CD2
		     , ST.CURR_STOCK_QTY
		     , ST.ADD_PRICE
		     , CASE WHEN ST.SOLDOUT_YN = 'N' AND G.GOODS_STAT = 'G008_90' THEN 'N' ELSE 'Y' END AS SOLDOUT_YN
		     , G.MIN_ORD_QTY
		     , G.MAX_ORD_QTY
		     , G.DAY_MAX_ORD_QTY
		     , CD.CART_DTL_SQ
		     , CD.OPT_CD AS CART_OPT_CD
		     , CD.OPT_CD2 AS CART_OPT_CD2
		FROM   TB_GOODS G
		INNER  JOIN VW_STOCK ST
		ON     G.GOODS_CD = ST.GOODS_CD
		LEFT   OUTER JOIN TB_CART_DETAIL CD
		ON     G.GOODS_CD = CD.ITEM_CD
		AND    ST.OPT_CD = CD.OPT_CD
		AND    CD.CART_SQ = #{cartSq}
		WHERE  1=1
		AND    G.GOODS_CD = #{goodsCd}
		<if test="optCd1 != null and optCd1 != ''">
		AND    ST.OPT_CD1 = #{optCd1}
		</if>
		AND    ST.DISP_YN = 'Y'
		AND    G.SELF_MALL_YN = 'Y'
		AND    G.GOODS_STAT IN ('G008_90', 'G008_70')
		ORDER  BY ST.DISP_ORD
	</select>

	<!-- 장바구니 자사 일반 및 딜 상품 이미지 리스트 조회 -->
	<select id="selectCartSelfGoodsOptionImgList" parameterType="Cart" resultType="GoodsImg">
		/* TsfCart.selectCartSelfGoodsOptionImgList : 장바구니 자사 상품 이미지 리스트 조회 */
		SELECT GI.GOODS_CD
			 , GI.COLOR_CD
			 , GI.SYS_IMG_NM
			 , GI.DEFAULT_IMG_YN
		FROM   TB_GOODS G
		INNER  JOIN TB_GOODS_IMG GI
		ON     G.GOODS_CD = GI.GOODS_CD
		WHERE  G.SELF_MALL_YN = 'Y'
		AND    G.GOODS_STAT = 'G008_90'
		AND    GI.GOODS_CD = #{goodsCd}
		ORDER  BY GI.DISP_ORD
	</select>

	<!-- 장바구니 세트 & 입점업체 상품 이미지 리스트 조회 -->
	<select id="selectCartDelvGoodsOptionImgList" parameterType="Cart" resultType="GoodsImg">
		/* TsfCart.selectCartDelvGoodsOptionImgList : 장바구니 입점업체 상품 이미지 리스트 조회 */
		SELECT GI.GOODS_CD
			 , GI.COLOR_CD
			 , GI.DEFAULT_IMG_YN
			 , GI.SYS_IMG_NM
		FROM   TB_GOODS G
		INNER  JOIN TB_GOODS_IMG GI
		ON     G.GOODS_CD = GI.GOODS_CD
		AND    G.MAIN_COLOR_CD = GI.COLOR_CD
		WHERE  G.SELF_MALL_YN = 'Y'
		AND    G.GOODS_STAT IN ('G008_90', 'G008_70')
		AND    GI.GOODS_CD = #{goodsCd}
		ORDER  BY GI.DISP_ORD
	</select>
	
	<!-- 마이페이지 장바구니 담기 관련 정보 조회 -->
	<select id="getAddCartInfoForMypage" parameterType="Cart" resultType="Cart">
		/* TsfCart.getAddCartInfoForMypage */
		SELECT OD.GOODS_CD
		     , ODI.OPT_CD
		     , IF(OD.ORD_QTY - OD.CNCL_RTN_QTY = 0, 1, OD.ORD_QTY - OD.CNCL_RTN_QTY) AS ORD_QTY
		     , OD.GOODS_TYPE
		     , ODI.ITEM_CD
		  FROM TB_ORDER_DETAIL OD
		 INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		    ON OD.ORD_NO = ODI.ORD_NO
		   AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		 WHERE OD.ORD_NO = #{ordNo}
		   AND OD.ORD_DTL_NO = #{ordDtlNo}
	</select>

	<!-- 장바구니 상품 상세 조건 정보 조회 -->
	<select id="getCartDetailInfo" parameterType="Order" resultType="Order">
		/* TsfCart.getCartDetailInfo : 장바구니 상품 상세 조건 정보 조회*/
		SELECT A.CART_SQ
		     , A.GOODS_CD
		     , A.GOODS_STAT
		     , A.ITEM_CD
		     , A.ITEM_NM
		     , A.MIN_ORD_QTY
		     , A.OPT_CD
		     , A.OPT_CD1
		     , A.OPT_CD2
		     , A.COLOR_NM
		     , A.SOLDOUT_YN
		     , A.ORD_CAN_YN
		     , A.ORD_CAN_QTY
		FROM   (SELECT C.CART_SQ
		             , C.GOODS_CD
		             , G.GOODS_STAT
					 , CD.ITEM_CD
					 , COMP_GOODS.GOODS_NM AS ITEM_NM
		             , G.MIN_ORD_QTY
		             , CD.OPT_CD
		             , CD.OPT_CD1
		             , CD.OPT_CD2
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(CD.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), CD.OPT_CD1) AS COLOR_NM
		             , CASE WHEN VS.SOLDOUT_YN = 'N' AND COMP_GOODS.GOODS_STAT != 'G008_70' AND G.GOODS_STAT != 'G008_70' THEN 'N'
		                    ELSE 'Y' END AS SOLDOUT_YN
		             , CASE WHEN G.GOODS_STAT != 'G008_90' AND G.GOODS_STAT != 'G008_70' AND COMP_GOODS.GOODS_STAT != 'G008_70' AND COMP_GOODS.GOODS_STAT != 'G008_90' THEN 'N'
		                    ELSE 'Y' END AS ORD_CAN_YN
		             , CASE WHEN G.GOODS_STAT = 'G008_70' OR VS.SOLDOUT_YN = 'Y' OR VS.CURR_STOCK_QTY = 0 THEN 0
		                    ELSE CASE WHEN G.MAX_ORD_QTY > TRUNCATE(VS.CURR_STOCK_QTY / GC.QTY, 0) THEN TRUNCATE(VS.CURR_STOCK_QTY / GC.QTY, 0)
		                              ELSE G.MAX_ORD_QTY END
		                    END AS ORD_CAN_QTY
		        FROM   TB_CART C
		        INNER  JOIN TB_CART_DETAIL CD
		        ON     C.CART_SQ = CD.CART_SQ
		        INNER  JOIN TB_GOODS G
		        ON     C.GOODS_CD = G.GOODS_CD
		        INNER  JOIN TB_GOODS COMP_GOODS
		        ON     CD.ITEM_CD = COMP_GOODS.GOODS_CD
		        INNER  JOIN TB_GOODS_COMPOSE GC
		        ON     C.GOODS_CD = GC.GOODS_CD
		        AND    CD.ITEM_CD = GC.COMPS_GOODS_CD
		        INNER  JOIN VW_STOCK VS
		        ON     CD.ITEM_CD = VS.GOODS_CD
		        AND    CD.OPT_CD = VS.OPT_CD
		        WHERE  1=1
		        AND    C.CART_GB = 'G026_BC'
		        AND    G.SELF_MALL_YN = 'Y'
		        AND    C.CART_SQ =  #{cartSq}
		        UNION ALL
		        SELECT C.CART_SQ
		             , C.GOODS_CD
		             , G.GOODS_STAT
		             , CD.ITEM_CD
		             , G.GOODS_NM AS ITEM_NM
		             , G.MIN_ORD_QTY
		             , CD.OPT_CD
		             , CD.OPT_CD1
		             , CD.OPT_CD2
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(CD.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), CD.OPT_CD1) AS COLOR_NM
		             , CASE WHEN VS.SOLDOUT_YN = 'N' AND G.GOODS_STAT != 'G008_70' THEN 'N'
		                    ELSE 'Y' END AS SOLDOUT_YN
		             , CASE WHEN G.GOODS_STAT != 'G008_90' AND G.GOODS_STAT != 'G008_70' THEN 'N'
		                    ELSE 'Y' END AS ORD_CAN_YN
		             , CASE WHEN G.GOODS_STAT = 'G008_70' OR VS.SOLDOUT_YN = 'Y' THEN 0
		                    ELSE CASE WHEN G.MAX_ORD_QTY > VS.CURR_STOCK_QTY THEN CURR_STOCK_QTY
		                              ELSE G.MAX_ORD_QTY END
		                    END AS ORD_CAN_QTY
		        FROM   TB_CART C
		        INNER  JOIN TB_CART_DETAIL CD
		        ON     C.CART_SQ = CD.CART_SQ
		        INNER  JOIN TB_GOODS G
		        ON     C.GOODS_CD = G.GOODS_CD
		        INNER  JOIN VW_STOCK VS
		        ON     C.GOODS_CD = VS.GOODS_CD
		        AND    CD.OPT_CD = VS.OPT_CD
		        WHERE  1=1
		        AND    C.CART_GB = 'G026_BC'
		        AND    G.SELF_MALL_YN = 'Y'
		        AND    C.CART_SQ =  #{cartSq}
		) A
	</select>

	<!-- 장바구니 수량 UPDATE -->
	<update id="updateCartGoodsQty" parameterType="Cart">
		/* TsfCart.updateCartGoodsQty : 장바구니 수량 UPDATE */
		UPDATE TB_CART SET
		      GOODS_QTY = #{goodsQty}
		    , UPD_NO = #{updNo}
		    , UPD_DT = NOW()
		WHERE CART_SQ = #{cartSq}
		AND   CUST_NO = #{custNo}
	</update>

	<!-- 최근 등록된 상품 100개를 제외한 장바구니 시퀀스 목록 -->
	<select id="selectOldCartArr" parameterType="Cart" resultType="String">
		/* TsfCart.selectOldCartArr : 최근 등록된 상품 100개를 제외한 장바구니 시퀀스 목록 */
		SELECT GROUP_CONCAT(Z.CART_SQ ORDER BY Z.CART_SQ) AS CART_SQ_ARR
		FROM   (
		        SELECT ROW_NUMBER() OVER() AS CNT
		             , A.*
		        FROM   TB_CART A
		        WHERE  CART_GB = 'G026_BC'
		        <if test="custNo != null and custNo > 0">
		        AND    A.CUST_NO = #{custNo}
		        </if>
		        <if test="custNo == null or custNo == 0">
		        AND    A.CUST_NO = 0
		        AND    A.JSESSION_ID = #{jsessionId}
		        </if>
		        ORDER  BY REG_DT DESC
			   ) Z
		WHERE  1=1
		AND    Z.CNT > 100
	</select>

	<!-- 최근 등록된 상품 100개를 제외한 장바구니 제거 -->
	<delete id="deleteOldCart" parameterType="Cart">
		/* TsfCart.deleteOldCart : 최근 등록된 상품 100개를 제외한 장바구니 제거 */
		DELETE FROM TB_CART C
		WHERE  C.CART_SQ IN
		<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
		</foreach>
	</delete>

	<!-- 최근 등록된 상품 100개를 제외한 장바구니 상세 제거 -->
	<delete id="deleteOldCartDetail" parameterType="Cart">
		/* TsfCart.deleteOldCartDetail : 최근 등록된 상품 100개를 제외한 장바구니 상세 제거 */
		DELETE FROM TB_CART_DETAIL CD
		WHERE  CD.CART_SQ IN
		<foreach collection="cartSqArr" item="item" index="index"  open="(" close=")" separator=",">
			#{item}
		</foreach>
	</delete>

	<!-- 2021.05.10 주문완료 주문번호로 장바구니정보 삭제 -->
	<delete id="deleteCartOrdNo" parameterType="Cart">
		/* TsfCart.deleteCartOrdNo : 주문완료 주문번호로 장바구니정보 삭제 */
		DELETE FROM TB_CART
		WHERE  CART_SQ IN (
			SELECT CART_SQ
			FROM   TB_CART_HST
			WHERE  ORD_NO = #{ordNo}
		)
	</delete>

	<!-- 2021.05.10 주문완료 주문번호로 장바구니상세정보 삭제 -->
	<delete id="deleteCartDetailOrdNo"  parameterType="Cart">
		/* TsfCart.deleteCartDetailOrdNo : 주문완료 주문번호로 장바구니상세정보 삭제 */
		DELETE FROM TB_CART_DETAIL CD
		WHERE  CART_SQ IN (
			SELECT CART_SQ
			FROM   TB_CART_HST
			WHERE  ORD_NO = #{ordNo}
		)
	</delete>

	<!-- 장바구니 담긴 상품 전체 조회 -->
	<select id="getAllCartList" parameterType="Cart" resultType="Cart">
		/* TsfCart.getAllCartList : 장바구니 담긴 상품 전체 조회 */
		SELECT MAX(C.CART_SQ) AS CART_SQ
		     , C.GOODS_CD
		     , C.CUST_NO
		FROM   TB_CART C
		WHERE  C.CART_GB = 'G026_BC'
		<if test="custNo == 0">
		AND    C.CUST_NO = 0
		AND    C.JSESSION_ID = #{jsessionId}
		</if>
		<if test="custNo != 0">
		AND    C.CUST_NO = #{custNo}
		</if>
		GROUP  BY C.GOODS_CD
		        , C.CUST_NO
	</select>
</mapper>
