<?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.admin.biz.dao.TsaShoplinkerDao">

	<!-- 상품등록-단품 목록 조회 -->
	<select id="getOptionList" parameterType="ShoplinkerGoods" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getOptionList */
		SELECT
			A.GOODS_CD
			, B.OPT_CD AS PARTNER_PRODUCT_ID
			, B.OPT_CD
			, B.OPT_CD1
			, B.OPT_CD2
		   /*, IF(B.CURR_STOCK_QTY - B.BASE_STOCK_QTY <![CDATA[<]]> 1, 1 , B.CURR_STOCK_QTY - B.BASE_STOCK_QTY) AS QUANTITY -- 기준재고(안전재고)수량
		   , B.STORE_STOCK_QTY -- 매장재고수량
		   , B.CURR_STOCK_QTY -- 현재고수량
		   */
			, A.SELF_GOODS_YN
			, VS.CURR_STOCK_QTY AS QUANTITY   	-- 현재고
			, VS.BASE_STOCK_QTY 				-- 안전재고
		FROM VW_STOCK VS INNER JOIN TB_GOODS A ON VS.GOODS_CD = A.GOODS_CD
		INNER JOIN TB_OPTION B ON VS.GOODS_CD = B.GOODS_CD AND A.GOODS_CD = B.GOODS_CD AND VS.OPT_CD = B.OPT_CD
		WHERE VS.GOODS_CD = #{goodsCd}
		AND A.SELF_GOODS_YN = 'Y'
		ORDER BY cast(REPLACE(B.OPT_CD, '-', '') as unsigned)
	</select>

	<!-- 상품등록-상품 상세 -->
	<select id="getGoods" parameterType="ShoplinkerGoods" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getGoods */
		SELECT
			GOODS_CD
			, A.BRAND_CD
			, IFNULL((SELECT S.SYNC_YN FROM TB_EXTMALL_PRICE_SYNC S WHERE S.VENDOR_ID = 'G003_V001' and S.BRAND_CD = A.BRAND_CD), 'N') as SYNC_YN
			, (SELECT REPLACE(REPLACE(BRAND_ENM, ' ', '_'), '\'', '') FROM TB_BRAND S WHERE S.BRAND_CD = A.BRAND_CD AND S.USE_YN = 'Y') AS BRAND_ENM
			, '999999999' AS ATTRIBUTE_TITLE_CODE
			, (SELECT GROUP_CONCAT(OPT_CD ORDER BY CAST(REPLACE(OPT_CD, '-', '') AS UNSIGNED) SEPARATOR ',')  FROM TB_OPTION S WHERE A.GOODS_CD = S.GOODS_CD) AS ATTRIBUTE_PARTNER_PRODUCT_ID
			, GOODS_NM AS PRODUCT_NAME

			, TOBE_FORM_YN
			, (SELECT GOODS_DESC FROM TB_GOODS_DESC S WHERE A.GOODS_CD = S.GOODS_CD AND DESC_GB= '80' LIMIT 1) AS DETAIL_DESC
			, (SELECT GOODS_DESC FROM TB_GOODS_DESC S WHERE A.GOODS_CD = S.GOODS_CD AND DESC_GB= '10' LIMIT 1) AS DETAIL_DESC_NEW_10
			, (SELECT GOODS_DESC FROM TB_GOODS_DESC S WHERE A.GOODS_CD = S.GOODS_CD AND DESC_GB= '20' LIMIT 1) AS DETAIL_DESC_NEW_20
			, (SELECT GOODS_DESC FROM TB_GOODS_DESC S WHERE A.GOODS_CD = S.GOODS_CD AND DESC_GB= '30' LIMIT 1) AS DETAIL_DESC_NEW_30
			, (SELECT S.MODEL_INFO FROM TB_GOODS_MODEL_INFO S WHERE S.GOODS_CD = A.GOODS_CD) AS MODEL_INFO

			, '' AS MARKET_PRICE -- 쇼핑몰시중가(도서정가)
			, CURR_PRICE AS SALE_PRICE -- 쇼핑몰판매가
			, LIST_PRICE AS SUPPLY_PRICE -- 쇼핑몰공급가
			, '' AS MARKET_PRICE_P -- 매입처 시중가(도서정가)
			, '' AS SALE_PRICE_P -- 매입처 판매가
			, '' AS SUPPLY_PRICE_P -- 매입처 공급가
			, '001' AS SALE_STATUS -- 상품판매상태
			, MAKE_YMD AS MAKER_DT -- 발행일(제조일)
			, '001' AS TAX_YN -- 과세
			, '' AS NEW_DESC_TOP -- 신상세설명
			, (CASE SEX_GB WHEN 'G007_M' THEN '002' WHEN 'G007_F' THEN '003' ELSE '001' END) AS SEX -- 샵링커 성별
			, GOODS_CD AS MODEL -- 모델명
			, GOODS_CD AS MODEL_NO -- 모델NO
			, (SELECT BRAND_ENM FROM TB_BRAND S WHERE A.BRAND_CD = S.BRAND_CD AND USE_YN = 'Y') AS BRAND -- 브랜드명
			, IFNULL(( SELECT CERT_NUM FROM TB_GOODS_SAFE_NO S WHERE A.GOODS_CD = S.GOODS_CD ), '') AS AUTH_NO -- 인증번호
			, '20991231' AS EXPIRYDATE -- 유효일
			, (SELECT NI_CONTENT FROM TB_GOODS_NOTI_INFO S WHERE A.GOODS_CD = S.GOODS_CD AND NI_ITEM_CD = 'G005_005') AS MAKER -- 제조업체
			, IFNULL((select S.EXTMALL_ORIGIN_NM from TB_EXTMALL_ORIGIN S where S.ORIGIN_CD = A.ORIGIN_CD), '') AS ORIGIN -- 원산지명 OR 코드
			, ADULT_YN AS ADULT_INFO -- 성인상품

			, A.ITEMKIND_CD
			, C.ITEMKIND_NM AS SHOPLINKER_ITEMKIND_NM
			, C.SL_CATE1_CD AS CCATEGORY_L
			, C.SL_CATE2_CD AS CCATEGORY_M
			, C.SL_CATE3_CD AS CCATEGORY_S
			, C.SL_CATE4_CD AS CCATEGORY_D

			, MAIN_COLOR_CD
			, SELF_GOODS_YN
			, (SELECT COLOR_ENM FROM TB_COLOR WHERE COLOR_CD = MAIN_COLOR_CD) AS MAIN_COLOR_ENM
			, (SELECT GROUP_CONCAT(opt_cd1 order BY cast(REPLACE(OPT_CD, '-', '') as unsigned) SEPARATOR ',')
				FROM (
				    SELECT
				        GOODS_CD, opt_cd, opt_cd1
				        , ROW_NUMBER() OVER (PARTITION BY opt_cd1 ORDER BY opt_cd ) AS RankNo
				    FROM tb_option WHERE GOODS_CD = a.GOODS_CD
				) T WHERE RankNo = 1
			 ) as all_opt_cd1
			, CASE
				WHEN SUBSTRING(A.ITEMKIND_CD, 1, 2) = '08' || SUBSTRING(A.ITEMKIND_CD, 1, 2) = '09'
					|| SUBSTRING(A.ITEMKIND_CD, 1, 2) = '10' || SUBSTRING(A.ITEMKIND_CD, 1, 2) = '11'
					|| SUBSTRING(A.ITEMKIND_CD, 1, 2) = '12' || SUBSTRING(A.ITEMKIND_CD, 1, 2) = '13'
			  	THEN 'Y'
			  	ELSE 'N'
			  END KIDS_YN
		FROM TB_GOODS A
			LEFT OUTER JOIN TB_ITEMKIND B ON A.ITEMKIND_CD = B.ITEMKIND_CD
 			LEFT OUTER JOIN SHOPLINKER_CATE C ON B.ITEMKIND_CD = C.ITEMKIND_CD AND A.BRAND_CD = C.BRAND_CD
		WHERE A.GOODS_CD = #{goodsCd}
		AND A.SELF_GOODS_YN = 'Y'
		limit 1

	</select>

	<!-- 상품등록-상품 이미지 조회 -->
	<select id="getGoodsImageList" parameterType="ShoplinkerGoods" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getGoodsImageList */
		SELECT
			B.GOODS_CD
		     , B.COLOR_CD
		     , C.COLOR_KNM
		     , B.DISP_ORD
		     , B.SYS_IMG_NM
		     , B.DEFAULT_IMG_YN
		     , B.EXTMALL_IMG_YN
		FROM   TB_GOODS A INNER JOIN TB_GOODS_IMG B ON A.GOODS_CD = B.GOODS_CD
				LEFT OUTER JOIN TB_COLOR C ON B.COLOR_CD = C.COLOR_CD
		WHERE A.GOODS_CD = #{goodsCd}
		AND A.SELF_GOODS_YN = 'Y'
		<if test='extmallImgYn != null and extmallImgYn == "Y"'>
			AND A.MAIN_COLOR_CD = B.COLOR_CD
        	AND (B.DEFAULT_IMG_YN = 'Y' OR B.EXTMALL_IMG_YN = 'Y')
        </if>
        <if test='extmallDescImgYn != null and extmallDescImgYn == "Y"'>
        	AND A.MAIN_COLOR_CD = B.COLOR_CD
        </if>
        <if test='colorListYn != null and colorListYn == "Y"'>
			AND (B.SYS_IMG_NM LIKE '%_01.jpg' or B.SYS_IMG_NM LIKE '%_02.jpg' )
        </if>
		ORDER BY B.COLOR_CD, B.DISP_ORD
	</select>

	<!-- 상품등록-상품 사이즈 조회 -->
	<select id="getGoodsSizeList" parameterType="ShoplinkerGoods" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getGoodsSizeList */
		SELECT
			 A.OPT_CD2
			, B.TYPECD as SIZE_TYPECD
			, B.VALUE1 as SIZE_VALUE1
			, B.VALUE2 as SIZE_VALUE2
			, B.VALUE3 as SIZE_VALUE3
			, B.VALUE4 as SIZE_VALUE4
			, B.VALUE5 as SIZE_VALUE5

		FROM TB_OPTION A INNER JOIN TB_IF_MEASUREMENT B ON A.OPT_CD = B.SKUCODE /*A.PRODUCT_NO = B.PRODUCTNO*/
		WHERE A.GOODS_CD = #{goodsCd}
		GROUP BY A.OPT_CD2, B.TYPECD, B.VALUE1, B.VALUE2, B.VALUE3, B.VALUE4, B.VALUE5
		ORDER BY B.TYPECD, B.VALUE1, B.VALUE2, B.VALUE3, B.VALUE4, B.VALUE5
	</select>

	<!-- 상품등록-고시정보 -->
	<select id="getGoodsNotiList" parameterType="ShoplinkerGoods" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getGoodsNotiList */
		SELECT
			A.GOODS_CD as partner_product_id
			, A.GOODS_NM
			/*, B.NI_CLSF_CD
			, B.NI_ITEM_CD */
			, C.NI_CLSF_CD
			, C.NI_ITEM_CD
			, C.EXTMALL_NI_CLSF_CD as lclass_id
			, C.EXTMALL_NI_ITEM_CD as item_seq
			, B.NI_CONTENT as item_info
			, FN_GET_CODE_NM('G005', C.NI_ITEM_CD) AS NI_CONTENT_INFO
			, A.SELF_GOODS_YN = 'Y'
		FROM TB_GOODS A INNER JOIN TB_GOODS_NOTI_INFO B INNER JOIN TB_EXTMALL_NOTI_INFO C
		ON A.GOODS_CD = B.GOODS_CD
		AND B.NI_CLSF_CD = C.NI_CLSF_CD
		AND B.NI_ITEM_CD = C.NI_ITEM_CD
		AND A.SELF_GOODS_YN = 'Y'
		AND A.GOODS_CD = #{goodsCd}
	</select>

	<!-- 상품등록-인증정보 -->
	<select id="getCert" parameterType="ShoplinkerGoods" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getCert */
		SELECT
			B.GOODS_CD AS PARTNER_PRODUCT_ID
			, CASE B.CERT_ORGAN_NAME
				WHEN NULL THEN '.' WHEN '' THEN '.'
				ELSE B.CERT_ORGAN_NAME
			 END AS CERT_ORGAN
			, IFNULL(B.CERT_NUM, '') AS CERT_NO
			, A.SELF_GOODS_YN

			, B.CERT_TARGET_GB
			, FN_GET_CODE_NM('G083', CERT_TARGET_GB) AS CERT_TARGET_NM
			, CERT_FORM_GB
			, FN_GET_CODE_NM('G084', CERT_FORM_GB) AS CERT_FORM_NM
			, CERT_TYPE
			, FN_GET_CODE_NM('G081', CERT_TYPE) AS CERT_TYPE_NM
			, CERT_NUM
			, CERT_STATE
			, CERT_DIV
			, CERT_ORGAN_NAME
			, CERT_DT
		FROM TB_GOODS A INNER JOIN TB_GOODS_SAFE_NO B
		ON A.GOODS_CD = B.GOODS_CD
		WHERE A.GOODS_CD = #{goodsCd}
		AND A.SELF_GOODS_YN = 'Y'
	</select>

	<!-- 상품등록-상품 목록 건수 -->
	<select id="getGoodsSendListCount" parameterType="ShoplinkerSearch" resultType="int">
		/* TsaShoplinker.getGoodsSendListCount */
		SELECT COUNT(1) FROM
		(
			SELECT
				 B.GOODS_NM
				, B.BRAND_CD
				, A.*
				, fn_get_user_nm(A.REG_NO) AS REG_NM
			FROM SHOPLINKER_SYNC_HST A
				LEFT OUTER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD

			WHERE 1=1
			<include refid="getListCondition_sql"/>
		) A


	</select>

	<!-- 상품등록-상품 목록 -->
	<select id="getGoodsSendList" parameterType="ShoplinkerSearch" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getGoodsSendList */
		SELECT * FROM
		(
			SELECT
				 B.GOODS_NM
				, B.BRAND_CD
				, A.*
				, fn_get_user_nm(A.REG_NO) AS REG_NM
			FROM SHOPLINKER_SYNC_HST A
				LEFT OUTER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD

			WHERE 1=1
			<include refid="getListCondition_sql"/>
		) A

		ORDER BY REG_DT DESC
		LIMIT #{pageable.limitStartRow} ,  #{pageable.pageSize}
	</select>

	<!-- 상품등록-상품 목록 엑셀 -->
	<select id="getGoodsSendExcelList" parameterType="ShoplinkerSearch" resultType="paramMap">
		/* TsaShoplinker.getGoodsSendExcelList */
		SELECT
			 IFNULL(B.GOODS_NM, '') AS GOODS_NM
			, IFNULL(B.BRAND_CD, '') AS BRAND_CD
			, CASE WHEN 'OPTION' = A.API_TYPE THEN '단품'
				  WHEN 'PRODUCT' =  A.API_TYPE THEN '상품'
				  WHEN 'NOTI' =  A.API_TYPE THEN '품목(고시)'
				  WHEN 'CERT' =  A.API_TYPE THEN '인증'
				  WHEN 'IMAGE' =  A.API_TYPE THEN '이미지'
			  END API_TYPE
			, CASE WHEN 'true' = A.API_RESULT THEN '전송완료'
  		    	   WHEN 'false' = A.API_RESULT THEN '전송실패'
  		    	   WHEN 'error' = A.API_RESULT THEN '오류'
  		    	   ELSE IFNULL(A.API_RESULT, '')
			  END API_RESULT
			, IFNULL(A.API_MESSAGE, '') AS API_MESSAGE
			, IFNULL(A.API_PRODUCT_ID, '') AS API_PRODUCT_ID
			, IFNULL(A.GOODS_CD, '') AS GOODS_CD
			, IFNULL(A.OPT_CD, '') AS OPT_CD
			, IFNULL(A.QTY, '') AS QTY
			, IFNULL(A.ORD_DTL_NO, '') AS ORD_DTL_NO
			, IFNULL(A.REG_DT, '') AS REG_DT
			, fn_get_user_nm(A.REG_NO) AS REG_NM
		FROM SHOPLINKER_SYNC_HST A
		LEFT OUTER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD

		WHERE 1=1
		<include refid="getListCondition_sql"/>

		ORDER BY REG_DT DESC
	</select>

	<!-- 재고-단품 등록 -->
	<insert id="insertStockOption" parameterType="ShoplinkerGoods">
		/* TsaShoplinker.insertStockOption */
		INSERT INTO SHOPLINKER_STOCK (
		    GOODS_CD
			, OPT_CD
			, STOCK_QTY
			, SEND_YN
			, SEND_DT
			, REG_DT
			, UPD_DT
		)
		VALUES (
			#{goodsCd}
			, #{optCd}
			, #{quantity}
			, 'Y'
			, now()
			, now()
			, now()
		) ON DUPLICATE KEY UPDATE
		  	UPD_DT = now()
	</insert>

	<!-- 재고전송-목록 건수 -->
	<select id="getStockListCount" parameterType="ShoplinkerSearch" resultType="int">
		/* TsaShoplinker.getStockListCount */
		SELECT COUNT(1) FROM (
			SELECT
				A.*
				, B.GOODS_NM
				, B.BRAND_CD
				, C.OPT_CD1
				, C.OPT_CD2
			FROM
				SHOPLINKER_SYNC_HST A
				INNER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD
				INNER JOIN TB_OPTION C ON A.GOODS_CD = C.GOODS_CD AND A.OPT_CD = C.OPT_CD
			WHERE A.API_TYPE = 'STOCK'

			<include refid="getListCondition_sql"/>
		) A
	</select>

	<!-- 재고전송-목록 -->
	<select id="getStockList" parameterType="ShoplinkerSearch" resultType="ShoplinkerGoods">
		/* TsaShoplinker.getStockList */
		SELECT * FROM
		(
			SELECT
				A.API_TYPE
				, A.API_RESULT
				, A.API_MESSAGE
				, A.GOODS_CD
				, A.OPT_CD
				, A.QTY as QUANTITY
				, A.REG_DT
				, B.GOODS_NM
				, B.BRAND_CD
				, C.OPT_CD1
				, C.OPT_CD2
			FROM
				SHOPLINKER_SYNC_HST A
				INNER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD
				INNER JOIN TB_OPTION C ON A.GOODS_CD = C.GOODS_CD AND A.OPT_CD = C.OPT_CD
			WHERE A.API_TYPE = 'STOCK'

			<include refid="getListCondition_sql"/>
		) A
		ORDER BY A.REG_DT DESC, A.GOODS_CD, A.OPT_CD

		LIMIT #{pageable.limitStartRow} ,  #{pageable.pageSize}
	</select>

	<!-- 재고- 목록 엑셀 -->
	<select id="getStockExcelList" parameterType="ShoplinkerSearch" resultType="paramMap">
	/* TsaShoplinker.getStockList */
		SELECT
			A.API_TYPE
			, CASE WHEN 'true' = A.API_RESULT THEN '전송완료'
  		    	   WHEN 'false' = A.API_RESULT THEN '전송실패'
  		    	   WHEN 'error' = A.API_RESULT THEN '오류'
  		    	   ELSE IFNULL(A.API_RESULT, '')
			  END API_RESULT
			, A.API_MESSAGE
			, A.GOODS_CD
			, A.OPT_CD
			, A.QTY as QUANTITY
			, A.REG_DT
			, B.GOODS_NM
			, B.BRAND_CD
			, C.OPT_CD1
			, C.OPT_CD2
		FROM
			SHOPLINKER_SYNC_HST A
			INNER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD
			INNER JOIN TB_OPTION C ON A.GOODS_CD = C.GOODS_CD AND A.OPT_CD = C.OPT_CD
		WHERE A.API_TYPE = 'STOCK'
		<include refid="getListCondition_sql"/>

		ORDER BY A.REG_DT DESC, A.GOODS_CD, A.OPT_CD

	</select>

	<!-- 상품/재고 목록 조건 정보 -->
	<sql id="getListCondition_sql">

		        <if test="goodsCd != null and goodsCd != ''">
		        AND A.GOODS_CD LIKE CONCAT('%',UPPER(#{goodsCd}),'%')
		        </if>
		    	<if test="conditionList != null and conditionList.length>0">
		        AND A.GOODS_CD IN
		            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
		       		UPPER(#{item})
		            </foreach>
		        </if>

		        <if test="goodsNm != null and goodsNm != ''">
		        AND B.GOODS_NM LIKE CONCAT('%',UPPER(#{goodsNm}),'%')
		        </if>
		        <if test="brandCd != null and brandCd != ''">
		        AND BRAND_CD = #{brandCd}
		        </if>
		        <if test="multiBrand != null and multiBrand != ''">
		        AND BRAND_CD IN
		            <foreach collection="multiBrand" item="item" index="index"  open="(" close=")" separator=",">
		        #{item}
		            </foreach>
		        </if>

	            <if test="stDate != null and stDate != ''">
		        AND A.REG_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
	            </if>
	            <if test="edDate != null and edDate != ''">
	            <![CDATA[
		        AND A.REG_DT < DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
	            ]]>
	            </if>

		        <if test="apiType != null and apiType != ''">
		        AND API_TYPE = #{apiType}
		        </if>
		        <if test="apiType == null or apiType == ''">
		        AND API_TYPE in ('OPTION','PRODUCT','NOTI','CERT','IMAGE')
		        </if>

		        <if test="apiResult != null and apiResult != ''">
		        AND API_RESULT = #{apiResult}
		        </if>

	</sql>

	<!-- 샵링커주문수집-목록 건수 cnt -->
	<select id="getShoplinkerOrderListCount" parameterType="ShoplinkerOrder" resultType="int">
		/* TsaShoplinker.getShoplinkerOrderListCount */
		select count(1) from (
			SELECT
				ROW_NUMBER() OVER(PARTITION BY B.VENDOR_ID, B.EXTMALL_ID, B.AGENT_ORDER_ID, B.EXTMALL_ORDER_ID ORDER by B.UPLOAD_GB desc, B.UPLOAD_STAT) AS RANKING
				, A.*
				, B.VENDOR_ID
				, B.EXTMALL_ID
				, B.EXTMALL_NM
				, B.AGENT_ORDER_ID
				, B.EXTMALL_ORDER_ID
				, IFNULL(B.UPLOAD_STAT, 'G021_50') AS UPLOAD_STAT
				, B.UPLOAD_FAIL_CD
				, B.UPLOAD_FAIL_REASON
				, IFNULL(B.ORD_NO, '') AS ORD_NO
				, B.DELV_ADDR_SQ
			FROM SHOPLINKER_ORDER A
				LEFT OUTER JOIN TB_EXTMALL_ORDER_UPLOAD B
					ON A.SHOPLINKER_ORDER_ID = B.AGENT_ORDER_ID

			WHERE 1=1
			<include refid="getSlkOrderListCondition_sql"/>
		) A
		WHERE RANKING = 1
	</select>

	<!-- 샵링커주문수집-목록 -->
	<select id="getShoplinkerOrderList" parameterType="ShoplinkerOrder" resultType="ShoplinkerOrder">
		/* TsaShoplinker.getShoplinkerOrderList */
		SELECT * FROM
		(
			SELECT
				ROW_NUMBER() OVER(PARTITION BY B.VENDOR_ID, B.EXTMALL_ID, B.AGENT_ORDER_ID, B.EXTMALL_ORDER_ID ORDER by B.UPLOAD_GB desc, B.UPLOAD_STAT) AS RANKING
				, A.*
				, B.VENDOR_ID
				, B.EXTMALL_ID
				, B.EXTMALL_NM
				, B.AGENT_ORDER_ID
				, B.EXTMALL_ORDER_ID
				, IFNULL(B.UPLOAD_STAT, 'G021_50') AS UPLOAD_STAT
				, B.UPLOAD_FAIL_CD
				, B.UPLOAD_FAIL_REASON
				, IFNULL(B.ORD_NO, '') AS ORD_NO
				, B.DELV_ADDR_SQ
				, B.REG_DT AS EXTMALL_REG_DT
			FROM SHOPLINKER_ORDER A
				LEFT OUTER JOIN TB_EXTMALL_ORDER_UPLOAD B
					ON A.SHOPLINKER_ORDER_ID = B.AGENT_ORDER_ID
			WHERE 1=1

			<include refid="getSlkOrderListCondition_sql"/>
		) A
		WHERE RANKING = 1
		ORDER BY EXTMALL_REG_DT, REG_DT DESC
		LIMIT #{pageable.limitStartRow} ,  #{pageable.pageSize}

	</select>

	<!-- 샵링커주문수집-상세 -->
	<select id="getShoplinkerOrderInfo" parameterType="ShoplinkerOrder" resultType="ShoplinkerOrder">
		/* TsaShoplinker.getShoplinkerOrderInfo */
		SELECT
			ORDER_IF_IDX
			, SHOPLINKER_ORDER_ID
			, MALL_ORDER_ID
			, MALL_NAME
			, BAESONG_STATUS
			, ORDER_NAME
			, ORDER_TEL
			, ORDER_CEL
			, ORDER_EMAIL
			, RECEIVE
			, RECEIVE_TEL
			, RECEIVE_CEL
			, RECEIVE_ZIPCODE
			, RECEIVE_ADDR
			, BAESONG_TYPE
			, BAESONG_BI
			, DELIVERY_MSG
			, ORDER_PRODUCT_ID
			, SHOPLINKER_PRODUCT_ID
			, PARTNER_PRODUCT_ID
			, PRODUCT_NAME
			, QUANTITY
			, ORDER_PRICE
			, SALE_PRICE
			, SUPPLY_PRICE
			, SKU
			, STR_TO_DATE(ORDERDATE, '%Y%m%d%H%i%S') as ORDERDATE
			, STR_TO_DATE(ORDER_REG_DATE, '%Y%m%d%H%i%S') as ORDER_REG_DATE
			, DELIVERY_INVOICE
			, ORDER_SEQ
			, TOTAL_COUNT
			, TOTAL_PAGE
			, TOTAL_STANDARD_COUNT
			, CURRENTLY_PAGE
			, CURRENTLY_COUNT
			, SELLER_ID
			, ONLY_SKU
			, ADD_SKU
			, MALL_ID
			, DIS_PRICE_MALL
			, DIS_PRICE_SELLER
			, DIS_PRICE_COUPON
			, DIS_PRICE_POINT
			, DISTRIBUTION_DELIVERY
			, SKU_MATCH_CODE
			, SKU_BARCODE
			, ORDER_CUSTOMS_NUMBER
			, ORDER_INPUT_TYPE
			, SUPPLY_ID
			, DELIVERY
			, ORDER_USER_ID
			, CRSPIK_USE
			, SHIP_NO
			, DEAL_NO
			, EXCHANGE_ORDER_YN
			, ORDER_MAIN_KEY
			, PAN_TYPE
			, CHANNEL_TYPE
			, SHIP_RSV_DATE
			, ORDER_FLAG
			, EXTMALL_REG_YN
			, REG_DT
		FROM SHOPLINKER_ORDER
		WHERE SHOPLINKER_ORDER_ID = #{shoplinkerOrderId}
	</select>

	<!-- 제휴몰 주문등록 상세 -->
	<select id="getExtmallOrderInfo" parameterType="String" resultType="ExtmallOrder">
		/* TsaShoplinker.getExtmallOrderInfo */
		SELECT
			A.*
			, FN_GET_USER_NM(A.REG_NO) AS REG_NM
			, FN_GET_CODE_NM('G021', UPLOAD_STAT) AS UPLOAD_STAT_NM
			, FN_GET_CODE_NM('G022', UPLOAD_FAIL_CD) AS UPLOAD_FAIL_NM
			, FN_GET_CODE_NM('G003', A.VENDOR_ID) AS VENDOR_NM
		FROM TB_EXTMALL_ORDER_UPLOAD A
		WHERE AGENT_ORDER_ID = #{agentOrderId}
		AND A.UPLOAD_GB = 'S'

	</select>

	<!-- 샵링커주문수집-목록엑셀 -->
	<select id="getOrderExcelList" parameterType="ShoplinkerOrder" resultType="paramMap">
		/* TsaShoplinker.getOrderExcelList */
		SELECT * FROM
		(
			SELECT
				ROW_NUMBER() OVER(PARTITION BY B.VENDOR_ID, B.EXTMALL_ID, B.AGENT_ORDER_ID, B.EXTMALL_ORDER_ID ORDER by B.UPLOAD_GB desc, B.UPLOAD_STAT) AS RANKING
				, A.*
				, B.VENDOR_ID
				, B.EXTMALL_ID
				, B.EXTMALL_NM
				, B.AGENT_ORDER_ID
				, B.EXTMALL_ORDER_ID
				, FN_GET_CODE_NM('G021', IFNULL(B.UPLOAD_STAT, 'G021_50')) AS UPLOAD_STAT
				, FN_GET_CODE_NM('G022', B.UPLOAD_FAIL_CD) AS UPLOAD_FAIL_CD
				, B.UPLOAD_FAIL_REASON
				, IFNULL(B.ORD_NO, '') AS ORD_NO
				, B.DELV_ADDR_SQ
				, B.REG_DT AS EXTMALL_REG_DT
			FROM SHOPLINKER_ORDER A
				LEFT OUTER JOIN TB_EXTMALL_ORDER_UPLOAD B
					ON A.SHOPLINKER_ORDER_ID = B.AGENT_ORDER_ID
			WHERE 1=1

			<include refid="getSlkOrderListCondition_sql"/>
		) A
		WHERE RANKING = 1
		ORDER BY EXTMALL_REG_DT, REG_DT DESC

	</select>

	<!-- 샵링커주문수집 조건 정보 -->
	<sql id="getSlkOrderListCondition_sql">

			<if test="stDate != null and stDate != ''">
	        AND A.ORDER_REG_DATE >= DATE_FORMAT(replace(#{stDate}, '-', ''), '%Y%m%d%H%i%S')
            </if>
            <if test="edDate != null and edDate != ''">
            <![CDATA[
	        AND A.ORDER_REG_DATE < DATE_FORMAT(DATE_ADD(replace(#{edDate}, '-', ''), INTERVAL 1 DAY), '%Y%m%d%H%i%S')
            ]]>
            </if>

			<if test="extmallId != null and extmallId != ''">
	        AND A.MALL_ID = #{extmallId}
	        </if>

			<if test="uploadStat != null and uploadStat != ''">
	        AND IFNULL(B.UPLOAD_STAT, 'G021_50') = #{uploadStat}
	        </if>
	        <if test="uploadFailCd != null and uploadFailCd != ''">
	        AND B.UPLOAD_FAIL_CD = #{uploadFailCd}
	        </if>

	        <if test='conditionList != null and conditionList.length>0'>
	            <choose>
	              <when test='search != null and search == "shoplinkerOrderId"'>
	              	AND UPPER(A.SHOPLINKER_ORDER_ID) IN
			            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
			       		UPPER(#{item})
			            </foreach>
	              </when>

	              <when test='search != null and search == "mallOrderId"'>
	              	AND UPPER(A.MALL_ORDER_ID) IN
			            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
			       		UPPER(#{item})
			            </foreach>
	              </when>

	              <when test='search != null and search == "ordNo"'>
	              	AND B.ORD_NO IN
			            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
			       		#{item}
			            </foreach>
	              </when>
	            </choose>
	        </if>
	</sql>

	<!-- 송장전송-전송 목록 건수 cnt -->
	<select id="getSendInvoiceListCount" parameterType="ShoplinkerInvoice" resultType="int">
		/* TsaShoplinker.getSendInvoiceListCount */
		SELECT
			count(OD.ORD_DTL_NO)
		FROM
			TB_ORDER_DETAIL OD
			LEFT OUTER JOIN SHOPLINKER_SYNC_HST SL ON OD.ORD_DTL_NO = SL.ORD_DTL_NO AND SL.API_TYPE = 'INVOICE'
		WHERE OD.VENDOR_ID = 'G003_V001'
		AND OD.INVOICE_NO IS NOT NULL
		AND OD.DELV_STDT IS NOT NULL
		<include refid="getSlkInvoiceCondition_sql"/>

	</select>

	<!-- 송장전송-전송 목록 -->
	<select id="getSendInvoiceList" parameterType="ShoplinkerInvoice" resultType="ShoplinkerInvoice">
		/* TsaShoplinker.getSendInvoiceList */
		SELECT
			OD.ORD_NO
			, OD.ORD_DTL_NO
			, (SELECT SHIP_COMP_NM FROM TB_SHIP_COMPANY S WHERE S.SHIP_COMP_CD = OD.SHIP_COMP_CD ) AS SHIP_COMP_NM
			, OD.SHIP_COMP_CD AS DELIVERY_CODE-- 배송업체(택배사코드)
			, OD.INVOICE_NO AS DELIVERY_INVOICE-- 송장번호

			, OD.VENDOR_ID -- 외부몰벤더ID
			, OD.EXTMALL_ID -- 외부몰ID(외부몰)
			, OD.AGENT_ORDER_ID -- 에이전트주문번호
			, OD.EXTMALL_ORDER_ID -- 외부몰주문번호
			, OD.DELV_ASSIGN_STAT -- 출고지정상태(P:대기,Y:수락,N:거부)
			, OD.ORD_EXCH_GB -- 주문교환구분(O:주문,E:교환)
			, OD.ORD_DTL_STAT -- 주문상세상태(공통코드G013)
			, FN_GET_CODE_NM('G013', OD.ORD_DTL_STAT) AS ORD_DTL_STAT_NM
			, OD.DELV_STDT

			, SL.API_RESULT
			, SL.API_MESSAGE
			, SL.XML_TXT
			, SL.REG_DT
			, FN_GET_USER_NM(SL.REG_NO) AS REG_NM
		FROM
			TB_ORDER_DETAIL OD
			LEFT OUTER JOIN SHOPLINKER_SYNC_HST SL ON OD.ORD_DTL_NO = SL.ORD_DTL_NO AND SL.API_TYPE = 'INVOICE'
		WHERE OD.VENDOR_ID = 'G003_V001'
		AND (OD.ORD_DTL_STAT = 'G013_50' OR SL.ORD_DTL_NO IS NOT NULL)
		AND OD.INVOICE_NO IS NOT NULL
		AND OD.DELV_STDT IS NOT NULL

		<include refid="getSlkInvoiceCondition_sql"/>

		ORDER BY OD.DELV_STDT DESC, SL.REG_DT DESC, OD.ORD_NO, OD.ORD_DTL_NO
		LIMIT #{pageable.limitStartRow} ,  #{pageable.pageSize}
	</select>

	<!-- 송장전송-전송 목록 엑셀 -->
	<select id="getInvoiceExcelList" parameterType="ShoplinkerInvoice" resultType="paramMap">
		/* TsaShoplinker.getInvoiceExcelList */
		SELECT
			OD.ORD_NO
			, OD.ORD_DTL_NO
			, (SELECT SHIP_COMP_NM FROM TB_SHIP_COMPANY S WHERE S.SHIP_COMP_CD = OD.SHIP_COMP_CD ) AS SHIP_COMP_NM
			, OD.SHIP_COMP_CD AS DELIVERY_CODE-- 배송업체(택배사코드)
			, OD.INVOICE_NO AS DELIVERY_INVOICE-- 송장번호

			, OD.VENDOR_ID -- 외부몰벤더ID
			, OD.EXTMALL_ID -- 외부몰ID(외부몰)
			, OD.AGENT_ORDER_ID -- 에이전트주문번호
			, OD.EXTMALL_ORDER_ID -- 외부몰주문번호
			, OD.DELV_ASSIGN_STAT -- 출고지정상태(P:대기,Y:수락,N:거부)
			, OD.ORD_EXCH_GB -- 주문교환구분(O:주문,E:교환)
			, OD.ORD_DTL_STAT -- 주문상세상태(공통코드G013)
			, FN_GET_CODE_NM('G013', OD.ORD_DTL_STAT) AS ORD_DTL_STAT_NM
			, OD.DELV_STDT

			, CASE WHEN 'true' = SL.API_RESULT THEN '전송완료'
  		    	   WHEN 'false' = SL.API_RESULT THEN '전송실패'
  		    	   WHEN 'error' = SL.API_RESULT THEN '오류'
  		    	   ELSE IFNULL(SL.API_RESULT, '')
			  END API_RESULT
			, SL.API_MESSAGE
			, SL.XML_TXT
			, SL.REG_DT
		FROM
			TB_ORDER_DETAIL OD
			LEFT OUTER JOIN SHOPLINKER_SYNC_HST SL ON OD.ORD_DTL_NO = SL.ORD_DTL_NO AND SL.API_TYPE = 'INVOICE'
		WHERE OD.VENDOR_ID = 'G003_V001'
		AND (OD.ORD_DTL_STAT = 'G013_50' OR SL.ORD_DTL_NO IS NOT NULL)
		AND OD.INVOICE_NO IS NOT NULL
		AND OD.DELV_STDT IS NOT NULL

		<include refid="getSlkInvoiceCondition_sql"/>

		ORDER BY OD.ORD_NO, OD.ORD_DTL_NO

	</select>

	<!-- 상품 목록 조건 정보 -->
	<sql id="getSlkInvoiceCondition_sql">

			<if test="stDate != null and stDate != ''">
	        AND OD.DELV_STDT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
            </if>
            <if test="edDate != null and edDate != ''">
            <![CDATA[
	        AND OD.DELV_STDT < DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
            ]]>
            </if>

            <if test="extmallId != null and extmallId != ''">
	        AND OD.EXTMALL_ID = #{extmallId}
            </if>

	        <if test='apiResult != null and apiResult != "" and apiResult != "default"'>
	        AND IFNULL(SL.API_RESULT, 'wait') = #{apiResult}
	        </if>
	        <if test='apiResult != null and apiResult == "default"'>
	        AND IFNULL(SL.API_RESULT, 'wait') != 'true'
	        </if>

			<if test='conditionList != null and conditionList.length>0'>
	            <choose>
	              <when test='search != null and search == "agentOrderId"'>
	              	AND UPPER(OD.AGENT_ORDER_ID) IN
			            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
			       		UPPER(#{item})
			            </foreach>
	              </when>

	              <when test='search != null and search == "extmallOrderId"'>
	              	AND UPPER(OD.EXTMALL_ORDER_ID) IN
			            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
			       		UPPER(#{item})
			            </foreach>
	              </when>

	              <when test='search != null and search == "ordNo"'>
	              	AND OD.ORD_NO IN
			            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
			       		#{item}
			            </foreach>
	              </when>

	              <when test='search != null and search == "deliveryInvoice"'>
	              	AND OD.INVOICE_NO IN
			            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
			       		#{item}
			            </foreach>
	              </when>

	            </choose>
	        </if>
	</sql>

</mapper>


