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

	<!-- 외부몰 목록 -->
	<select id="getExtmallList" parameterType="Extmall" resultType="Extmall">
		/* TsaOcm.getExtmallList */
		SELECT VENDOR_ID         /*벤더ID*/
		     , EXTMALL_ID        /*외부몰ID*/
		     , EXTMALL_USER_ID   /*외부몰사용자ID*/
		     , EXTMALL_NM        /*외부몰명*/
		     , EXTMALL_TEL		 /*고객센터연락처*/
		     , SUPPLY_COMP_CD    /*업체코드*/
		     , SELL_STORE_CD     /*판매매장코드*/
		     , STOCK_SELL_RATE   /*재고판매비율*/
		     , PRICE_ACCEPT_RATE /*가격차이허용율*/
		     , SELL_FEE_RATE 	 /*수수료율(관리용)*/
		     , DWDP_YN           /*직접회수처리여부*/
		     , CNCLSMS_SEND_YN   /*취소문자발송여부*/
		     , USE_YN            /*사용여부*/
		FROM   TB_EXTMALL A
		WHERE  1 = 1
		<if test="extmallId != null and extmallId != ''">
		AND    EXTMALL_ID = #{extmallId}
		</if>
		<if test="vendorId != null and vendorId != ''">
		AND    VENDOR_ID = #{vendorId}
		</if>
		<if test="searchTxt != null and searchTxt != ''">
		AND    EXTMALL_NM LIKE CONCAT('%', #{searchTxt}, '%')
		</if>
		ORDER  BY VENDOR_ID, EXTMALL_ID, EXTMALL_USER_ID
	</select>

	<!-- 외부몰 등록/수정 -->
	<insert id="saveExtmall" parameterType="Extmall">
		/* TsaOcm.saveExtmall */
		INSERT INTO TB_EXTMALL (
		       VENDOR_ID
		     , EXTMALL_ID
		     , EXTMALL_USER_ID
		     , EXTMALL_NM
		     , SUPPLY_COMP_CD
		     , SELL_STORE_CD
		     , STOCK_SELL_RATE
		     , PRICE_ACCEPT_RATE
		     , SELL_FEE_RATE
		     , DWDP_YN
		     , CNCLSMS_SEND_YN
		     , EXTMALL_TEL
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{vendorId}
		     , #{extmallId}
		     , #{extmallUserId}
		     , #{extmallNm}
		     , #{supplyCompCd}
		     , #{sellStoreCd}
		     , IFNULL(#{stockSellRate},0)
		     , IFNULL(#{priceAcceptRate},0)
		     , IFNULL(#{sellFeeRate},0)
		     , #{dwdpYn}
		     , #{cnclsmsSendYn}
		     , #{extmallTel}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       EXTMALL_USER_ID = #{extmallUserId}
		     , EXTMALL_NM = #{extmallNm}
		     , SUPPLY_COMP_CD = #{supplyCompCd}
		     , SELL_STORE_CD = #{sellStoreCd}
		     , STOCK_SELL_RATE = IFNULL(#{stockSellRate},0)
		     , PRICE_ACCEPT_RATE = IFNULL(#{priceAcceptRate},0)
		     , SELL_FEE_RATE = IFNULL(#{sellFeeRate},0)
		     , DWDP_YN = #{dwdpYn}
		     , CNCLSMS_SEND_YN = #{cnclsmsSendYn}
		     , EXTMALL_TEL = #{extmallTel}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>

	<!-- 외부몰고시정보 목록 -->
	<select id="getExtmallNotiinfoList" parameterType="ExtmallNoti" resultType="ExtmallNoti">
		/* TsaOcm.getExtmallNotiinfoList */
		SELECT A.VENDOR_ID                                 /*벤더ID*/
		     , A.EXTMALL_NI_CLSF_CD                        /*외부몰고시정보분류코드*/
		     , A.EXTMALL_NI_CLSF_NM                        /*외부몰고시정보분류명*/
		     , A.EXTMALL_NI_ITEM_CD                        /*외부몰고시정보항목코드*/
		     , A.EXTMALL_NI_ITEM_NM                        /*외부몰고시정보항목명*/
		     , CASE WHEN A.NI_CLSF_CD IS NULL THEN
		                NULL
		            ELSE
		                CONCAT('[',A.NI_CLSF_CD,'] ',B.CD_NM)
		       END                  AS SELFMALL_NI_CLSF_NM /*자사몰정보고시분류명*/
		     , CASE WHEN A.NI_ITEM_CD IS NULL THEN
		                NULL
		            ELSE
		                CONCAT('[',A.NI_ITEM_CD,'] ',C.CD_NM)
		       END                  AS SELFMALL_NI_ITEM_NM /*자사몰정보고시항목명*/
		FROM   TB_EXTMALL_NOTI_INFO A
		LEFT OUTER JOIN TB_COMMON_CODE B ON A.NI_CLSF_CD = B.CD
		                                AND B.CD_GB = 'G004'
		LEFT OUTER JOIN TB_COMMON_CODE C ON A.NI_ITEM_CD = C.CD
		                                AND C.CD_GB = 'G005'
		WHERE  A.VENDOR_ID = #{vendorId}
		ORDER  BY A.EXTMALL_NI_CLSF_CD, A.EXTMALL_NI_ITEM_CD
	</select>

	<!-- 외부몰원산지 목록 -->
	<select id="getExtmallOriginList" parameterType="ExtmallOrigin" resultType="ExtmallOrigin">
		/* TsaOcm.getExtmallOriginList */
		SELECT A.VENDOR_ID                                /*벤더ID*/
		     , A.EXTMALL_ORIGIN_CD                        /*외부몰원산지코드*/
		     , A.EXTMALL_ORIGIN_NM                        /*외부몰원산지명*/
		     , CASE WHEN A.ORIGIN_CD IS NULL THEN
		                NULL
		            ELSE
		                CONCAT('[',A.ORIGIN_CD,'] ',B.CD_NM)
		       END                  AS SELFMALL_ORIGIN_NM /*자사몰원산지명*/
		FROM   TB_EXTMALL_ORIGIN A
		LEFT OUTER JOIN TB_COMMON_CODE B ON A.ORIGIN_CD = B.CD
		                                AND B.CD_GB = 'G076'
		WHERE  A.VENDOR_ID = #{vendorId}
		ORDER  BY A.EXTMALL_ORIGIN_NM
	</select>

	<!-- 외부몰가격연계 목록 -->
	<select id="getExtmallPriceSyncList" parameterType="ExtmallPriceSync" resultType="ExtmallPriceSync">
		/* TsaOcm.getExtmallPriceSyncList */
		SELECT #{vendorId}           AS VENDOR_ID /*벤더ID*/
		     , A.BRAND_CD                         /*브랜드코드*/
		     , A.BRAND_KNM                        /*브랜드국문명*/
		     , A.BRAND_ENM                        /*브랜드영문명*/
		     , IFNULL(B.SYNC_YN,'N') AS SYNC_YN   /*연계여부*/
		FROM   TB_BRAND A
		LEFT OUTER JOIN TB_EXTMALL_PRICE_SYNC B ON A.BRAND_CD = B.BRAND_CD
		                                       AND B.VENDOR_ID = #{vendorId}
		WHERE  A.SELF_YN = 'Y' /*자사브랜드만*/
		ORDER  BY 1, 2
	</select>

	<!-- 외부몰가격연계 등록/수정 -->
	<insert id="saveExtmallPriceSync" parameterType="ExtmallPriceSync">
		/* TsaOcm.saveExtmallPriceSync */
		INSERT INTO TB_EXTMALL_PRICE_SYNC (
		       VENDOR_ID
		     , BRAND_CD
		     , SYNC_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{vendorId}
		     , #{brandCd}
		     , #{syncYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       SYNC_YN = #{syncYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>

	<!-- 제휴몰정산가격업로드- 목록 건수 cnt -->
	<select id="getExtmallUsacListCount" parameterType="ExtmallUsac" resultType="int">
		/* TsaOcm.getExtmallUsacListCount */
		SELECT 
			count(ODIH.ORD_DTL_ITEM_HST_SQ)
		FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
			   INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
			   INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
			   INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
			   INNER JOIN TB_GOODS G ON OD.GOODS_CD = G.GOODS_CD
			   INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
			   LEFT OUTER JOIN TB_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
			   LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
		WHERE O.MALL_GB = 'G011_20'
		
		<include refid="getExtmallUsacCondition_sql"/>

	</select>

	<!-- 제휴몰정산가격업로드- 목록 -->
	<select id="getExtmallUsacList" parameterType="ExtmallUsac" resultType="ExtmallUsac">
		/* TsaOcm.getExtmallUsacList */
		SELECT 
			ODIH.ORD_DTL_ITEM_HST_SQ
			, OD.ORD_NO
			, OD.ORD_DTL_NO
			, OD.GOODS_CD
			, G.GOODS_NM
			, ODIH.OPT_CD
			, ODIH.OPT_CD1
			, ODIH.OPT_CD2
			, ODIH.ITEM_QTY
			, ODIH.ITEM_PRICE
			, E.EXTMALL_ID                                                        /*제휴몰ID*/
		    , E.EXTMALL_NM                                                        /*제휴몰명*/
		    , SC.SUPPLY_COMP_CD                                                   /*공급업체코드*/
		    , SC.SUPPLY_COMP_NM                                                   /*공급업체명*/
		    , SC.DISTRIBUTION_GB                                                  /*유통구분*/
		    , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB) AS DISTRIBUTION_GB_NM     /*유통구분명*/
		    , B.BRAND_CD                                                          /*브랜드코드*/
		    , B.BRAND_ENM                                                         /*브랜드명*/
			, IFNULL(EUP.USAC_PRICE, ODIH.ITEM_PRICE) AS USAC_PRICE
			, EUP.REG_DT
			, EUP.UPD_DT
			, FN_GET_USER_NM(EUP.REG_NO) as REG_NM
			, FN_GET_USER_NM(EUP.UPD_NO) as UPD_NM
		FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
			   INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
			   INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
			   INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
			   INNER JOIN TB_GOODS G ON OD.GOODS_CD = G.GOODS_CD
			   INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
			   LEFT OUTER JOIN TB_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
			   LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
		WHERE O.MALL_GB = 'G011_20'

		<include refid="getExtmallUsacCondition_sql"/>

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

	<!-- 제휴몰정산가격업로드- 목록 엑셀 -->
	<select id="getExtmallUsacExcelList" parameterType="ExtmallUsac" resultType="paramMap">
		/* TsaOcm.getExtmallUsacExcelList */
		SELECT 
			ODIH.ORD_DTL_ITEM_HST_SQ
			, OD.ORD_NO 
			, OD.ORD_DTL_NO
			, OD.GOODS_CD
			, G.GOODS_NM
			, ODIH.OPT_CD
			, ODIH.OPT_CD1
			, ODIH.OPT_CD2
			, ODIH.ITEM_QTY
			, ODIH.ITEM_PRICE
			, E.EXTMALL_ID                                                        /*제휴몰ID*/
		    , E.EXTMALL_NM                                                        /*제휴몰명*/
		    , SC.SUPPLY_COMP_CD                                                   /*공급업체코드*/
		    , SC.SUPPLY_COMP_NM                                                   /*공급업체명*/
		    , SC.DISTRIBUTION_GB                                                  /*유통구분*/
		    , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB) AS DISTRIBUTION_GB_NM     /*유통구분명*/
		    , B.BRAND_CD                                                          /*브랜드코드*/
		    , B.BRAND_ENM                                                         /*브랜드명*/
			, IFNULL(EUP.USAC_PRICE, ODIH.ITEM_PRICE) AS USAC_PRICE
			, EUP.REG_DT
			, EUP.UPD_DT
			, FN_GET_USER_NM(EUP.REG_NO) as REG_NM
			, FN_GET_USER_NM(EUP.UPD_NO) as UPD_NM
		FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
			   INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
			   INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
			   INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
			   INNER JOIN TB_GOODS G ON OD.GOODS_CD = G.GOODS_CD
			   INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
			   LEFT OUTER JOIN TB_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
			   LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
		WHERE O.MALL_GB = 'G011_20'
		
		<include refid="getExtmallUsacCondition_sql"/>

		ORDER  BY ODIH.ORD_DTL_ITEM_HST_SQ DESC

	</select>

	<!-- 상품 목록 조건 정보 -->
	<sql id="getExtmallUsacCondition_sql">
       		AND    ODIH.REG_DT <![CDATA[>=]]> STR_TO_DATE(#{salesStdt},'%Y-%m-%d')
	   		AND    ODIH.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{salesEddt},'%Y-%m-%d'), INTERVAL 1 DAY)

	        <if test='distributionGb != null and distributionGb != ""'>
	        AND    SC.DISTRIBUTION_GB = #{distributionGb}
	        </if>
	        <if test='extmallId != null and extmallId != ""'>
	        AND    OD.EXTMALL_ID = #{extmallId}
	        </if>
	        <if test="multiSupplyComp != null and multiSupplyComp != ''">
	        AND    SC.SUPPLY_COMP_CD IN
	            <foreach collection="multiSupplyComp" item="item" index="index"  open="(" close=")" separator=",">
	            #{item}
	            </foreach>
	        </if>
	        <if test="multiBrand != null and multiBrand != ''">
	        AND    B.BRAND_CD IN
	            <foreach collection="multiBrand" item="item" index="index"  open="(" close=")" separator=",">
	            #{item}
	            </foreach>
	        </if>
	        
	        <if test='usacYn != null and usacYn == "Y"'>
	        AND EUP.USAC_PRICE IS NOT NULL
	        </if>
	        <if test='usacYn != null and usacYn == "N"'>
	        AND EUP.USAC_PRICE IS NULL
	        </if>
	        <if test="conditionList != null and conditionList.length>0">
	        AND OD.ORD_NO IN
	            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
	       		UPPER(#{item})
	            </foreach>
	        </if>
	</sql>
	
	<!-- 외부몰 등록/수정 -->
	<insert id="createExtmallUsacExcelupload" parameterType="ExtmallUsac">
		/* TsaOcm.createExtmallUsacExcelupload */
		INSERT INTO TB_EXTMALL_USAC_PRICE (
		     ORD_DTL_ITEM_HST_SQ
			, USAC_PRICE
			, REG_NO
			, REG_DT
			, UPD_NO
			, UPD_DT
		)
		VALUES (
		       #{ordDtlItemHstSq}
		     , #{usacPrice}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       USAC_PRICE = #{usacPrice}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
</mapper>