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

	<!-- 품목 목록 -->
	<select id="getItemkindList" parameterType="Itemkind" resultType="Itemkind">
		/* TsaGoods.getItemkindList */
		SELECT ITEMKIND_CD
		     , ITEMKIND_NM
		     /* , ITEMKIND_ENM*/
		     , NI_CLSF_CD
		     , USE_YN
		FROM TB_ITEMKIND
		WHERE 1 = 1
		<if test="itemkindCd != null and itemkindCd != ''">
		AND ITEMKIND_CD = #{itemkindCd} 
		</if>
		<if test="useYn != null and useYn != ''">
		AND USE_YN = #{useYn} 
		</if>
		ORDER BY ITEMKIND_CD
	</select>

	<!-- 품목 등록/저장 -->
	<insert id="saveItemkindInfo" parameterType="Itemkind">
		/* TsaGoods.saveItemkindInfo */
		INSERT INTO TB_ITEMKIND (
		       ITEMKIND_CD
		     , ITEMKIND_NM
		     , NI_CLSF_CD
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{itemkindCd}
		     , #{itemkindNm}
		     , #{niClsfCd}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       ITEMKIND_NM = #{itemkindNm}
		     , NI_CLSF_CD = #{niClsfCd}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 품목 삭제 -->
	<delete id="deleteItemkindInfo" parameterType="Itemkind">
		/* TsaGoods.deleteItemkindInfo */
		DELETE FROM TB_ITEMKIND
		 WHERE ITEMKIND_CD = #{itemkindCd}
	</delete>
	
	<!-- 색상 조회 -->
	<select id="getColorList" parameterType="Color" resultType="Color" >
		/* TsaGoods.getColorList */
		SELECT COLOR_CD
		     , COLOR_ENM
		     , COLOR_KNM
		     , COLOR_FILE
		     , COLOR_GRP_NM
		     , USE_YN
		     , FN_GET_USER_NM(REG_NO) AS REG_NM
		     , DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		     , FN_GET_USER_NM(UPD_NO) AS UPD_NM
		     , DATE_FORMAT(UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		     , COLOR_GRP_CD
		FROM TB_COLOR
		WHERE 1 = 1
		<if test='colorCd != null and colorCd != ""'>
		AND COLOR_CD = #{colorCd}
		</if>
		<if test='useYn != null and useYn != ""'>
		AND USE_YN = #{useYn}
		</if>
		ORDER BY COLOR_CD
	</select>
	
	<!-- 색상 저장 -->
	<update id="saveColor" parameterType="Color">
		/* TsaGoods.saveColor */
		INSERT INTO TB_COLOR (
		       COLOR_CD
		     , COLOR_ENM
		     , COLOR_KNM
		     , COLOR_FILE
		     , COLOR_GRP_NM
		     , COLOR_GRP_CD
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{colorCd}
		     , #{colorEnm}
		     , #{colorKnm}
		     , #{colorFile}
		     , #{colorGrpNm}
		     , #{colorGrpCd}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       COLOR_ENM = #{colorEnm}
		     , COLOR_KNM = #{colorKnm}
		     , COLOR_FILE = UPPER(#{colorFile})
		     , COLOR_GRP_NM = UPPER(#{colorGrpNm})
		     , COLOR_GRP_CD = UPPER(#{colorGrpCd})
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</update>
	
	<!-- 정보고시분류 목록 -->
	<select id="getNotiInfoList" parameterType="NotiInfo" resultType="NotiInfo">
		/* TsaGoods.getNotiInfoList */
		SELECT DISTINCT B.SUPPLY_COMP_CD
		     , B.NI_CLSF_CD
		     , C.CD_NM AS NI_CLSF_NM
		     , C.DISP_ORD
		FROM TB_NOTI_INFO B
		INNER JOIN TB_COMMON_CODE C ON B.NI_CLSF_CD = C.CD 
		WHERE B.SUPPLY_COMP_CD = CASE #{supplyCompCd} 
		                         WHEN 'S0001' THEN 'S0001'
		                         WHEN 'S0002' THEN 'S0002'
		                         ELSE 'E' END 
		AND C.CD_GB = 'G004'   /*고시정보*/
		ORDER BY C.DISP_ORD
	</select>
	
	<!-- 정보고시 상세 목록 -->
	<select id="getNotiInfoItemList" parameterType="NotiInfo" resultType="NotiInfo">
		/* TsaGoods.getNotiInfoItemList */
		SELECT B.SUPPLY_COMP_CD
		     , B.NI_CLSF_CD
		     , B.NI_ITEM_CD
		     , FN_GET_CODE_NM('G005', B.NI_ITEM_CD) AS NI_ITEM_NM
		     , B.NI_CONTENT
		     , B.DISP_ORD
		     , B.REQ_YN
		     , B.DISP_YN
		FROM TB_NOTI_INFO B
		WHERE B.SUPPLY_COMP_CD = CASE #{supplyCompCd} 
		                         WHEN 'S0001' THEN 'S0001'
		                         WHEN 'S0002' THEN 'S0002'
		                         ELSE 'E' END 
		<if test="niClsfCd != null and niClsfCd != ''">
		AND B.NI_CLSF_CD = #{niClsfCd}
		</if>
		ORDER BY B.NI_CLSF_CD, B.DISP_ORD
	</select>

	<!-- 정보고시 항목 등록 -->
	<insert id="saveNotiInfo" parameterType="NotiInfo">
		/* TsaGoods.saveNotiInfo */
		INSERT INTO TB_NOTI_INFO (
		       SUPPLY_COMP_CD
		     , NI_CLSF_CD
		     , NI_ITEM_CD
		     , NI_CONTENT
		     , DISP_ORD
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{supplyCompCd}
		     , #{niClsfCd}
		     , #{niItemCd}
		     , #{niContent}
		     , #{dispOrd}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       NI_CONTENT = #{niContent}
		     , DISP_ORD = #{dispOrd}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 상품 목록 건수 -->
	<select id="getGoodsListCount" parameterType="GoodsSearch" resultType="int">
		/* TsaGoods.getGoodsListCount */
		        SELECT COUNT(*) AS TOTCNT
		        FROM TB_GOODS G
		        INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		        INNER JOIN TB_SUPPLY_COMPANY S ON G.SUPPLY_COMP_CD = S.SUPPLY_COMP_CD
		        INNER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
		        <if test="searchGb != null and searchGb =='EXTEND'">
		        INNER JOIN TB_GOODS_EXTEND GE ON G.GOODS_CD =  GE.EXTEND_GOODS_CD
		                                      AND GE.GOODS_CD IN
		            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
		                                          UPPER(#{item})
		            </foreach>
		        </if>
		        <if test="searchGb != null and searchGb =='MASTER'">
		        INNER JOIN TB_GOODS_EXTEND GE ON G.GOODS_CD =  GE.GOODS_CD
		                                      AND GE.EXTEND_GOODS_CD IN
		            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
		                                          UPPER(#{item})
		            </foreach>
		        </if>
		        <if test="searchGb != null and searchGb =='EXCEL'">
		        INNER JOIN (
		                     SELECT SEARCH_CD
		                          , TMP_DISP_ORD
		                     FROM (
		                           SELECT SEARCH_CD
		                                , MIN(DISP_ORD) AS TMP_DISP_ORD
		                           FROM TB_SEARCH_DATA
		                           WHERE REG_NO = #{regNo}
		                           GROUP BY SEARCH_CD) T
		                   ) SD
		                   ON ( (G.GOODS_CD LIKE CONCAT(SD.SEARCH_CD,'%')
		                        OR G.SUPPLY_GOODS_CD LIKE CONCAT(SD.SEARCH_CD,'%')) 
		                        <if test="mdId != null and mdId != ''">
		                        AND G.BRAND_CD IN (
		                                           SELECT DISTINCT BRAND_CD
		                                           FROM TB_BRAND_MD
		                                           WHERE MD_ID = #{mdId}
		                                           )
		                       </if>
		                   )
		        </if>
		        LEFT OUTER JOIN TB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
		        WHERE 1=1
		        <if test="searchGb == null or searchGb =='BASIC' or searchGb =='EXTEND' or searchGb =='MASTER'" >
		        <include refid="getGoodsListContion_sql"/>
		        </if>
	</select>

	<!-- 상품 목록 -->
	<select id="getGoodsList" parameterType="GoodsSearch" resultType="Goods">
		/* TsaGoods.getGoodsList */
		SELECT Z.*
		    /*, (CASE WHEN Z.GOODS_TYPE = 'N' THEN (SELECT NVL(SUM(CURR_STOCK_QTY - BASE_STOCK_QTY),0) FROM VW_STOCK WHERE GOODS_CD = Z.GOODS_CD) 
		            ELSE (SELECT NVL(SUM(CURR_STOCK_QTY - BASE_STOCK_QTY),0) FROM VW_STOCK_EXTEND WHERE GOODS_CD = Z.GOODS_CD) 
		            END) AS STOCK_QTY_SUM */
		    , 0 AS STOCK_QTY_SUM
		    , (SELECT ITEMKIND_NM FROM TB_ITEMKIND WHERE ITEMKIND_CD = Z.ITEMKIND_CD ) AS ITEMKIND_NM
		    , FN_GET_USER_NM(REG_NO) AS REG_NM
		    , FN_GET_USER_NM(UPD_NO) AS UPD_NM
		FROM (
		    SELECT A.*, @rownum := @rownum + 1 AS RNUM FROM (
		        SELECT
		                G.GOODS_CD
		              , G.GOODS_TYPE
		              , B.BRAND_ENM
		              , B.BRAND_GRP_NM
		              , G.BRAND_CD
		              , G.ITEMKIND_CD
		              , G.SUPPLY_COMP_CD
		              , G.SUPPLY_GOODS_CD
		              , G.GOODS_NUM
		              , G.GOODS_NM
		              , G.GOODS_STAT
		              , G.LIST_PRICE
		              , G.CURR_PRICE
		              , G.DC_RATE
		              , G.SELF_GOODS_YN
		              , G.SELF_MALL_YN
		              , G.FORMAL_GB
		              , G.STYLE_YEAR
		              , G.SEASON_CD
		              , G.ERP_STOCK_LINK_YN
		              , G.ERP_PRICE_LINK_YN
		              , G.CHANGEABLE_YN
		              , G.RETURNABLE_YN
		              , DATE_FORMAT(G.FRST_CFRM_DT, '%Y%m%d%H%i%S') AS FRST_CFRM_DT
		              , G.SELL_FEE_RATE
		              , DATE_FORMAT(G.PRICE_UPD_DT,'%Y%m%d%H%i%S') AS PRICE_UPD_DT
		              , CASE WHEN IMG_PATH1 IS NULL THEN
		                     'N'
		                ELSE
		                     'Y'
		                END AS GOODS_IMAGE_YN
		              , GI.IMG_PATH1
		              , GI.IMG_PATH6
		              , GI.IMG_TYPE
		              , G.REG_NO
		              , DATE_FORMAT(G.REG_DT,'%Y%m%d%H%i%S') AS REG_DT
		              , G.UPD_NO
		              , DATE_FORMAT(G.UPD_DT,'%Y%m%d%H%i%S') AS UPD_DT
		              , GS.SELL_DAY15_QTY
		              <choose>
		                  <when test="searchGb != null and searchGb =='EXCEL'">
		              , SD.TMP_DISP_ORD
		                  </when>
		                  <otherwise>
		              , 0 AS TMP_DISP_ORD
		                  </otherwise>
		              </choose>
		        FROM TB_GOODS G
		        JOIN ( SELECT @rownum := 0) R
		        INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		        INNER JOIN TB_SUPPLY_COMPANY S ON G.SUPPLY_COMP_CD = S.SUPPLY_COMP_CD
		        INNER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
		        <if test="searchGb != null and searchGb =='EXTEND'">
		        INNER JOIN TB_GOODS_EXTEND GE ON G.GOODS_CD =  GE.EXTEND_GOODS_CD
		                                      AND GE.GOODS_CD IN
		            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
		                                          UPPER(#{item})
		            </foreach>
		        </if>
		        <if test="searchGb != null and searchGb =='MASTER'">
		        INNER JOIN TB_GOODS_EXTEND GE ON G.GOODS_CD =  GE.GOODS_CD
		                                      AND GE.EXTEND_GOODS_CD IN
		            <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
		                                          UPPER(#{item})
		            </foreach>
		        </if>
		        <if test="searchGb != null and searchGb =='EXCEL'">
		        INNER JOIN (
		                     SELECT SEARCH_CD
		                          , TMP_DISP_ORD
		                     FROM (
		                           SELECT SEARCH_CD
		                                , MIN(DISP_ORD) AS TMP_DISP_ORD
		                           FROM TB_SEARCH_DATA
		                           WHERE REG_NO = #{regNo}
		                           GROUP BY SEARCH_CD) T
		                   ) SD
		                   ON ( (G.GOODS_CD LIKE CONCAT(SD.SEARCH_CD,'%')
		                        OR G.SUPPLY_GOODS_CD LIKE CONCAT(SD.SEARCH_CD,'%')) 
		                        <if test="mdId != null and mdId != ''">
		                        AND G.BRAND_CD IN (
		                                           SELECT DISTINCT BRAND_CD
		                                           FROM TB_BRAND_MD
		                                           WHERE MD_ID = #{mdId}
		                                           )
		                       </if>
		                   )
		        </if>
		        LEFT OUTER JOIN TB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
		        WHERE 1=1
		        <if test="searchGb == null or searchGb =='BASIC' or searchGb =='EXTEND' or searchGb =='MASTER'" >
		        <include refid="getGoodsListContion_sql"/>
		        ORDER BY G.REG_DT DESC, G.GOODS_CD
		        </if>
		        <if test="searchGb != null and searchGb =='EXCEL'">
		        ORDER BY SD.TMP_DISP_ORD
		        </if>
		<include refid="getListPagingContion_sql"/>
	</select>
	
	<!-- 상품 목록 조건 정보 -->
	<sql id="getGoodsListContion_sql">
		        <if test='conditionList != null and conditionList.length>0'>
		            <choose>
		              <when test='search != null and search == "searchGoodsCd"'>
		        AND (
		              <foreach collection="conditionList" item="item" index="index" separator="or">
		               UPPER(G.GOODS_CD) LIKE CONCAT('%',UPPER(#{item}),'%') 
		              </foreach>
		             )
		              </when>
		              <when test='search != null and search == "searchGoodsNum"'>
		        AND (
		              <foreach collection="conditionList" item="item" index="index" separator="or">
		               UPPER(G.GOODS_NUM) LIKE CONCAT('%',UPPER(#{item}),'%')
		              </foreach>
		             )
		              </when>
		              <when test='search != null and search == "searchSupplyGoodsCd"'>
		        AND (
		              <foreach collection="conditionList" item="item" index="index" separator="or">
		               UPPER(G.SUPPLY_GOODS_CD) LIKE CONCAT('%',UPPER(#{item}),'%')
		              </foreach>
		             )
		              </when>
		              <when test='search != null and search == "searchExtendGoodsCd"'>
		        AND 1 = 1
		              </when>
		              <when test='search != null and search == "searchMasterGoodsCd"'>
		        AND 1 = 1
		              </when>
		              <otherwise>
		        AND UPPER(G.REG_ID) LIKE CONCAT('%',UPPER(#{item}),'%')
		              </otherwise>
		            </choose>
		        </if>
		        <if test="goodsCd != null and goodsCd != ''">
		        AND UPPER(G.GOODS_CD) LIKE CONCAT('%',UPPER(#{goodsCd}),'%')
		        </if>
		         <if test="goodsNm != null and goodsNm != ''">
		        AND UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{goodsNm}),'%')
		        </if>
		         <if test="goodsType != null and goodsType != ''">
		        AND G.GOODS_TYPE = #{goodsType}
		        </if>
		        <if test="supplyCompCd != null and supplyCompCd != ''">
		        AND G.SUPPLY_COMP_CD = #{supplyCompCd}
		        </if>
		        <if test="brandCd != null and brandCd != ''">
		        AND G.BRAND_CD = #{brandCd}
		        </if>
		        <if test="multiBrand != null and multiBrand != ''">
		        AND G.BRAND_CD IN
		            <foreach collection="multiBrand" item="item" index="index"  open="(" close=")" separator=",">
		        #{item}
		            </foreach>
		        </if>
		        <if test="goodsType != null and goodsType != ''">
		        AND G.GOODS_TYPE = #{goodsType}
		        </if>
		        <if test="selfMallYn != null and selfMallYn != ''">
		        AND G.SELF_MALL_YN = #{selfMallYn}
		        </if>
		        <if test="itemkindCd != null and itemkindCd != ''">
		        AND G.ITEMKIND_CD = #{itemkindCd}
		        </if>
		        <if test="multiItemkindCd != null and multiItemkindCd != ''">
		        AND G.ITEMKIND_CD IN
		            <foreach collection="multiItemkindCd" item="item" index="index"  open="(" close=")" separator=",">
		        #{item}
		            </foreach>
		        </if>
		        <if test="goodsStat != null and goodsStat != ''">
		        AND G.GOODS_STAT = #{goodsStat}
		        </if>
		        <if test="multiGoodsStat != null and multiGoodsStat != ''">
		        AND G.GOODS_STAT IN
		            <foreach collection="multiGoodsStat" item="item" index="index"  open="(" close=")" separator=",">
		        #{item}
		            </foreach>
		        </if>
		        <if test="styleYear != null and styleYear != ''">
		        AND G.STYLE_YEAR = (SELECT CD_NM FROM TB_COMMON_CODE WHERE CD = #{styleYear} AND CD_GB='G023')
		        </if>
		        <if test="multiStyleYear != null and multiStyleYear != ''">
		        AND G.STYLE_YEAR IN (SELECT CD_NM 
		                             FROM TB_COMMON_CODE 
		                             WHERE CD_GB='G023' 
		                             AND CD IN 
		                             <foreach collection="multiStyleYear" item="item" index="index"  open="(" close=")" separator=",">
		                             #{item}
		                             </foreach>
		                             )
		        </if>
		        <if test="seasonCd != null and seasonCd != ''">
		        AND G.SEASON_CD = #{seasonCd}
		        </if>
		        <if test="multiSeasonCd != null and multiSeasonCd != ''">
		        AND G.SEASON_CD IN
		            <foreach collection="multiSeasonCd" item="item" index="index"  open="(" close=")" separator=",">
		        #{item}
		            </foreach>
		        </if>
		        <if test="sexGb != null and sexGb != ''">
		        AND G.SEX_GB = #{sexGb}
		        </if>
		        <if test="changeableYn != null and changeableYn != ''">
		        AND G.CHANGEABLE_YN = #{changeableYn}
		        </if>
		        <if test="returnableYn != null and returnableYn != ''">
		        AND G.RETURNABLE_YN = #{returnableYn}
		        </if>
		        <if test="returnFeeFreeYn != null and returnFeeFreeYn != ''">
		        AND G.RETURN_FEE_FREE_YN = #{returnFeeFreeYn}
		        </if>
		        <if test="changeFeeFreeYn != null and changeFeeFreeYn != ''">
		        AND G.CHANGE_FEE_FREE_YN = #{changeFeeFreeYn}
		        </if>
		        <if test="formalGb != null and formalGb != ''">
		        AND G.FORMAL_GB = #{formalGb}
		        </if>
		        <if test='soldOutYn != null and soldOutYn == "Y"'>
		        AND EXISTS (
		                    SELECT GOODS_CD 
		                    FROM VW_STOCK
		                    WHERE SOLDOUT_YN = 'Y'
		                    AND GOODS_CD = G.GOODS_CD
		                   )
		        </if>
		        <if test='soldOutYn != null and soldOutYn == "N"'>
		        AND NOT EXISTS (
		                    SELECT GOODS_CD 
		                    FROM VW_STOCK
		                    WHERE SOLDOUT_YN = 'Y'
		                    AND GOODS_CD = G.GOODS_CD
		                   )
		        </if>
		        <if test="erpStockLinkYn != null and erpStockLinkYn != ''">
		        AND G.ERP_STOCK_LINK_YN = #{erpStockLinkYn}
		        </if>
		        <if test="erpPriceLinkYn != null and erpPriceLinkYn != ''">
		        AND G.ERP_PRICE_LINK_YN = #{erpPriceLinkYn}
		        </if>
		        <if test="mdId != null and mdId != ''">
		        AND G.BRAND_CD IN (
		            SELECT DISTINCT BRAND_CD
		            FROM   TB_BRAND_MD
		            WHERE  MD_ID = #{mdId}
		        )
		        </if>
		        <if test="currPriceSt != null and currPriceSt != ''">
		        AND G.CURR_PRICE >= #{currPriceSt}
		        </if>
		        <if test="currPriceEd != null and currPriceEd != ''">
		        <![CDATA[
		        AND G.CURR_PRICE <= #{currPriceEd}
		         ]]>
		        </if>
		        <if test="dcRateSt != null and dcRateSt != ''">
		        AND G.DC_RATE >= #{dcRateSt}
		        </if>
		        <if test="dcRateEd != null and dcRateEd != ''">
		        <![CDATA[
		        AND G.DC_RATE <= #{dcRateEd}
		        ]]>
		        </if>
		        <if test='dateGbn != null and dateGbn == "P"'>
		            <if test="stDate != null and stDate != ''">
		        AND G.PRICE_UPD_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		            </if>
		            <if test="edDate != null and edDate != ''">
		            <![CDATA[
		        AND G.PRICE_UPD_DT < DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		            ]]>
		            </if>
		        </if>
		        <if test='dateGbn != null and dateGbn == "R"'>
		            <if test="stDate != null and stDate != ''">
		        AND G.REG_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		            </if>
		            <if test="edDate != null and edDate != ''">
		            <![CDATA[
		        AND G.REG_DT < DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		            ]]>
		            </if>
		        </if>
		        <if test='dateGbn != null and dateGbn == "S"'>
		            <if test="stDate != null and stDate != ''">
		        AND G.APPR_UPD_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		            </if>
		            <if test="edDate != null and edDate != ''">
		            <![CDATA[
		        AND G.APPR_UPD_DT < DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		            ]]>
		            </if>
		        </if>
		        <if test="stockQtySt != null and stockQtySt != ''">
		        AND ST.CURR_STOCK_QTY >= #{stockQtySt}
		        </if>
		        <if test="stockQtyEd != null and stockQtyEd != ''">
		        <![CDATA[
		        AND ST.CURR_STOCK_QTY <= #{stockQtyEd}
		        ]]>
		        </if>
		        <if test="ageGrpCd != null and ageGrpCd != ''">
		        AND G.AGE_GRP_CD = #{ageGrpCd}
		        </if>
	</sql>
	
	<!-- 목록 페이징 정보 -->
	<sql id="getListPagingContion_sql">
		<choose>
		<when test="pageable != null">
		    ) A
		)Z WHERE RNUM BETWEEN  #{pageable.startRow} AND #{pageable.endRow}
		</when>
		<otherwise>
		    ) A
		)Z
		</otherwise>
		</choose>
	</sql>
	
	<!-- 상품 목록 기본정보 엑셀  -->
	<select id="getGoodsInfoExcelList" parameterType="GoodsSearch" resultType="paramMap">
		/* TsaGoods.getGoodsInfoExcelList */
		SELECT G.GOODS_CD
		     , B.BRAND_ENM
		     , B.BRAND_GRP_NM
		     , G.BRAND_CD
		     , G.ITEMKIND_CD
		     , (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY WHERE SUPPLY_COMP_CD = G.SUPPLY_COMP_CD) AS SUPPLY_COMP_CD
		     , G.SUPPLY_GOODS_CD
		     , G.GOODS_NUM
		     , G.GOODS_NM
		     , G.GOODS_TYPE
		     , FN_GET_CODE_NM('G008', G.GOODS_STAT) AS GOODS_STAT
		     , G.LIST_PRICE
		     , G.CURR_PRICE
		     , G.DC_RATE
		     , G.SELF_GOODS_YN
		     , FN_GET_CODE_NM('G009', G.FORMAL_GB) AS FORMAL_GB
		     , G.STYLE_YEAR
		     , FN_GET_CODE_NM('G006', G.SEASON_CD) AS SEASON_CD
		     , G.ERP_STOCK_LINK_YN
		     , G.CHANGEABLE_YN
		     , G.RETURNABLE_YN
		     , G.SELL_FEE_RATE
		     , DATE_FORMAT(G.PRICE_UPD_DT, '%Y-%m-%d %H:%i:%S') AS PRICE_UPD_DT
		     , DATE_FORMAT(G.FRST_CFRM_DT, '%Y-%m-%d %H:%i:%S') AS FRST_CFRM_DT
		     , CASE WHEN IMG_PATH1 IS NULL THEN
		           'N'
		       ELSE
		           'Y'
		       END AS GOODS_IMAGE_YN
		     /*, (CASE WHEN G.GOODS_TYPE = 'N' THEN (SELECT NVL(SUM(CURR_STOCK_QTY - BASE_STOCK_QTY),0) FROM VW_STOCK WHERE GOODS_CD = G.GOODS_CD) 
		             ELSE (SELECT NVL(SUM(CURR_STOCK_QTY - BASE_STOCK_QTY),0) FROM VW_STOCK_EXTEND WHERE GOODS_CD = G.GOODS_CD) 
		             END) AS STOCK_QTY_SUM*/
		     , (SELECT ITEMKIND_NM FROM TB_ITEMKIND WHERE ITEMKIND_CD = G.ITEMKIND_CD) AS ITEMKIND_NM
		     , GI.IMG_PATH1
		     , GI.IMG_PATH6
		     , GI.IMG_TYPE
		     , G.REG_NO
		     , DATE_FORMAT(G.REG_DT, '%Y-%m-%d %H:%i:%S') AS REG_DT
		     , G.UPD_NO
		     , DATE_FORMAT(G.UPD_DT, '%Y-%m-%d %H:%i:%S') AS UPD_DT
		FROM TB_GOODS G
		INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		INNER JOIN TB_SUPPLY_COMPANY S ON G.SUPPLY_COMP_CD = S.SUPPLY_COMP_CD
		INNER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
		<if test="searchGb != null and searchGb =='EXTEND'">
		INNER JOIN TB_GOODS_EXTEND GE ON G.GOODS_CD =  GE.EXTEND_GOODS_CD
		                              AND GE.GOODS_CD IN
		    <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
		                              UPPER(#{item})
		    </foreach>
		</if>
		<if test="searchGb != null and searchGb =='MASTER'">
		INNER JOIN TB_GOODS_EXTEND GE ON G.GOODS_CD =  GE.GOODS_CD
		                              AND GE.EXTEND_GOODS_CD IN
		    <foreach collection="conditionList" item="item" index="index"  open="(" close=")" separator=",">
		                          UPPER(#{item})
		    </foreach>
		</if>
		<if test="searchGb != null and searchGb =='EXCEL'">
		INNER JOIN (
		             SELECT SEARCH_CD
		                  , TMP_DISP_ORD
		             FROM (
		                   SELECT SEARCH_CD
		                        , MIN(DISP_ORD) AS TMP_DISP_ORD
		                   FROM TB_SEARCH_DATA
		                   WHERE REG_NO = #{regNo}
		                   GROUP BY SEARCH_CD) T
		          ) SD
		           ON ( (G.GOODS_CD LIKE CONCAT(SD.SEARCH_CD,'%')
		                 OR G.SUPPLY_GOODS_CD LIKE CONCAT(SD.SEARCH_CD,'%')) 
		                <if test="mdId != null and mdId != ''">
		                AND G.BRAND_CD IN (
		                                   SELECT DISTINCT BRAND_CD
		                                   FROM TB_BRAND_MD
		                                   WHERE MD_ID = #{mdId}
		                                  )
		               </if>
		              )
		</if>
		LEFT OUTER JOIN TB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
		WHERE 1=1
		<if test="searchGb == null or searchGb =='BASIC' or searchGb =='EXTEND' or searchGb =='MASTER'" >
		<include refid="getGoodsListContion_sql"/>
		ORDER BY G.REG_DT DESC
		</if>
		<if test="searchGb == null or searchGb =='EXCEL'">
		ORDER BY SD.TMP_DISP_ORD
		</if>
	</select>
	
	<!-- 상품 정보 -->
	<select id="getGoods" parameterType="Goods" resultType="Goods">
		/* TsaGoods.getGoods */
		SELECT G.GOODS_CD
		     , G.GOODS_NM
		     , G.GOODS_TNM
		     , G.GOODS_SNM
		     , G.GOODS_SNM1
		     , G.BRAND_CD
		     , B.BRAND_GRP_NM
		     , G.ITEMKIND_CD
		     , G.STYLE_YEAR
		     , G.SEASON_CD
		     , G.SEX_GB
		     , G.GOODS_NUM
		     , G.COLOR_CD
		     , G.GOODS_TYPE
		     , G.LIST_PRICE
		     , G.CURR_PRICE
		     , G.CURR_BPRICE
		     , DATE_FORMAT(G.PRICE_UPD_DT, '%Y%m%d%H%i%S') AS PRICE_UPD_DT
		     , G.DC_RATE
		     , G.GOODS_STAT
		     , G.SELF_GOODS_YN
		     , G.SELF_MALL_YN
		     , G.GOODS_GB
		     , G.DISTRIBUTION_GB
		     /*, (SELECT COLOR_GRP_FILE 
		        FROM TB_COLOR TC 
		        WHERE TC.COLOR_CD = G.COLOR_CD) AS COLOR_GRP_FILE */
		     , G.SUPPLY_COMP_CD
		     , G.SUPPLY_GOODS_CD
		     , G.AGE_GRP_CD
		     , G.DELV_FEE
		     , G.MIN_ORD_AMT
		     , G.PNT_PRATE
		     , G.PNT_MRATE
		     , G.SELL_FEE_RATE
		     , G.FORMAL_GB
		     , G.RETURNABLE_YN
		     , G.PRE_PPNT_USABLE_YN
		     , G.PRE_MPNT_USABLE_YN
		     , G.MIN_ORD_QTY
		     , G.MAX_ORD_QTY
		     , G.DAY_MAX_ORD_QTY
		     , G.ERP_STOCK_LINK_YN
		     , G.ERP_PRICE_LINK_YN
		     , G.MAKE_NM
		     , G.MAKE_YMD
		     , G.TAX_GB
		     , G.GOODS_TYPE
		     , FN_GET_CODE_NM('G056', G.GOODS_TYPE) AS GOODS_TYPE_NM
		     , DATE_FORMAT(G.FRST_CFRM_DT, '%Y%m%d%H%i%S') AS FRST_CFRM_DT
		     , (SELECT I.NI_CLSF_CD FROM TB_ITEMKIND I WHERE I.ITEMKIND_CD = G.ITEMKIND_CD) AS NI_CLSF_CD
		     , GI.IMG_TYPE
		     , GI.IMG_PATH1
		     , GI.IMG_PATH6
		     , CASE WHEN IMG_PATH1 IS NULL THEN
		           'N'
		       ELSE
		           'Y'
		       END AS GOODS_IMAGE_YN
		     , (SELECT B.CD_NM 
		       FROM TB_ITEMKIND A
		          , TB_COMMON_CODE B 
		       WHERE A.ITEMKIND_CD = G.ITEMKIND_CD 
		       AND A.NI_CLSF_CD = B.CD 
		       AND B.CD_GB = 'G004') AS  NI_CLSF_NM
		     , FN_GET_USER_NM(G.UPD_NO) AS UPD_NM  
		FROM TB_GOODS G
		INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		LEFT OUTER JOIN TB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
		WHERE 1 = 1 
		<choose>
		<when test="goodsCd != null and goodsCd != ''">
		AND G.GOODS_CD = #{goodsCd}
		</when>
		<otherwise>
		AND G.SUPPLY_COMP_CD = #{supplyCompCd}
		AND G.SUPPLY_GOODS_CD = #{supplyGoodsCd}
		</otherwise>
		</choose>
	</select>
	
	<!-- 상품 상세 정보 조회 -->
	<select id="getGoodsDescList" parameterType="GoodsDesc" resultType="GoodsDesc">
		/* TsaGoods.getGoodsDescList */
		SELECT GOODS_CD
		     , DESC_GB
		     , SEQ
		     , GOODS_DESC
		FROM TB_GOODS_DESC
		WHERE GOODS_CD = #{goodsCd}
		AND DESC_GB = #{descGb}
		ORDER BY SEQ
	</select>
	
	<!-- 상품 고시정보 조회 -->
	<select id="getGoodsNotiInfoList" parameterType="GoodsNotiInfo" resultType="GoodsNotiInfo">
		/* TsaGoods.getGoodsNotiInfoList */
		SELECT C.GOODS_CD
		     , C.NI_CLSF_CD
		     , FN_GET_CODE_NM('G005', C.NI_ITEM_CD) AS NI_ITEM_NM
		     , C.NI_ITEM_CD
		     , C.NI_CONTENT
		     , C.DISP_ORD
		     , A.DISP_YN
		     , A.REQ_YN
		FROM TB_GOODS_NOTI_INFO C
		INNER JOIN TB_NOTI_INFO A ON C.NI_CLSF_CD = A.NI_CLSF_CD
		                          AND C.NI_ITEM_CD = A.NI_ITEM_CD
		                          AND A.SUPPLY_COMP_CD = CASE #{supplyCompCd} 
		                                                 WHEN 'S0001' THEN 'S0001'
		                                                 WHEN 'S0002' THEN 'S0002'
		                                                 ELSE 'E' END
		WHERE C.GOODS_CD = #{goodsCd}
		AND C.NI_CLSF_CD = #{niClsfCd}
		ORDER BY C.DISP_ORD
	</select>
	
	<!-- 상품 기본정보 이력 생성 -->
	<insert id="createGoodsHst" parameterType="Goods">
		/* TsaGoods.createGoodsHst */
		INSERT INTO TB_GOODS_HST 
		(       GOODS_CD
		      , BRAND_CD
		      , ITEMKIND_CD
		      , GOODS_NM
		      , GOODS_TNM
		      , GOODS_SNM
		      , GOODS_SNM1
		      , COLOR_CD
		      , STYLE_YEAR
		      , SEASON_CD
		      , SEX_GB
		      , GOODS_NUM
		      , GOODS_TYPE
		      , LIST_PRICE
		      , CURR_PRICE
		      , CURR_BPRICE
		      , PRICE_UPD_DT
		      , DC_RATE
		      , GOODS_STAT
		      , SELF_MALL_YN
		      , GOODS_GB
		      , DISTRIBUTION_GB
		      , SELF_GOODS_YN
		      , SUPPLY_COMP_CD
		      , SUPPLY_GOODS_CD
		      , AGE_GRP_CD
		      , DELV_FEE
		      , MIN_ORD_AMT
		      , PNT_PRATE
		      , PNT_MRATE
		      , SELL_FEE_RATE
		      , FORMAL_GB
		      , CHANGEABLE_YN
		      , RETURNABLE_YN
		      , CHANGE_FEE_FREE_YN
		      , RETURN_FEE_FREE_YN
		      , PRE_PPNT_USABLE_YN
		      , PRE_MPNT_USABLE_YN
		      , MIN_ORD_QTY
		      , MAX_ORD_QTY
		      , DAY_MAX_ORD_QTY
		      , FRST_CFRM_DT
		      , MAKE_NM
		      , MAKE_YMD
		      , TAX_GB
		      , ERP_PRICE_LINK_YN
		      , ERP_STOCK_LINK_YN
		      , REG_NO
		      , REG_DT
		      , UPD_NO
		      , UPD_DT
		)
		SELECT GOODS_CD            
		     , BRAND_CD            
		     , ITEMKIND_CD         
		     , GOODS_NM            
		     , GOODS_TNM           
		     , GOODS_SNM           
		     , GOODS_SNM1          
		     , COLOR_CD            
		     , STYLE_YEAR          
		     , SEASON_CD           
		     , SEX_GB              
		     , GOODS_NUM           
		     , GOODS_TYPE          
		     , LIST_PRICE          
		     , CURR_PRICE          
		     , CURR_BPRICE         
		     , PRICE_UPD_DT        
		     , DC_RATE             
		     , GOODS_STAT          
		     , SELF_MALL_YN             
		     , GOODS_GB            
		     , DISTRIBUTION_GB     
		     , SELF_GOODS_YN       
		     , SUPPLY_COMP_CD      
		     , SUPPLY_GOODS_CD     
		     , AGE_GRP_CD          
		     , DELV_FEE            
		     , MIN_ORD_AMT         
		     , PNT_PRATE           
		     , PNT_MRATE           
		     , SELL_FEE_RATE       
		     , FORMAL_GB           
		     , CHANGEABLE_YN       
		     , RETURNABLE_YN       
		     , CHANGE_FEE_FREE_YN  
		     , RETURN_FEE_FREE_YN  
		     , PRE_PPNT_USABLE_YN  
		     , PRE_MPNT_USABLE_YN  
		     , MIN_ORD_QTY         
		     , MAX_ORD_QTY         
		     , DAY_MAX_ORD_QTY     
		     , FRST_CFRM_DT        
		     , MAKE_NM             
		     , MAKE_YMD            
		     , TAX_GB              
		     , ERP_PRICE_LINK_YN   
		     , ERP_STOCK_LINK_YN   
		     , #{regNo}              
		     , NOW()              
		     , UPD_NO              
		     , UPD_DT              
		FROM TB_GOODS
		WHERE GOODS_CD = #{goodsCd}
	</insert>
	
	<!-- 상품  수정 항목 일괄변경 -->
	<update id="updateGoodsState" parameterType="Goods">
		/* TsaGoods.updateGoodsState */
		UPDATE TB_GOODS SET
		      UPD_NO = #{updNo}
		    , UPD_DT = NOW()
		<if test="goodsStat != null and goodsStat != ''" >
		    , GOODS_STAT = #{goodsStat}
		    , FRST_CFRM_DT = IF(#{chGoodsStatYn} = 'Y', COALESCE(FRST_CFRM_DT, NOW()) , FRST_CFRM_DT)
		</if>
		<if test="erpStockLinkYn != null and erpStockLinkYn != ''" >
		    , ERP_STOCK_LINK_YN = #{erpStockLinkYn}
		</if>
		<if test="erpPriceLinkYn != null and erpPriceLinkYn != ''" >
		    , ERP_PRICE_LINK_YN = #{erpPriceLinkYn}
		</if>
		<if test="formalGb != null and formalGb != ''" >
		    , FORMAL_GB = #{formalGb}
		    , PNT_PRATE = IFNULL(#{pntPrate}, PNT_PRATE)
		    , PNT_MRATE = IFNULL(#{pntMrate}, PNT_MRATE)
		</if>
		<if test='procJob == "pntPrate" and pntPrate != null' >
		    , PNT_PRATE = #{pntPrate}
		</if>
		<if test='procJob == "pntMrate"  and pntMrate != null' >
		    , PNT_MRATE = #{pntMrate}
		</if>
		<if test="returnableYn != null and returnableYn != ''" >
		    , RETURNABLE_YN = #{returnableYn}
		</if>
		<if test="changeableYn != null and changeableYn != ''" >
		    , CHANGEABLE_YN = #{changeableYn}
		</if>
		<if test="prePpntUsableYn != null and prePpntUsableYn != ''" >
		    , PRE_PPNT_USABLE_YN = #{prePpntUsableYn}
		</if>
		<if test="preMpntUsableYn != null and preMpntUsableYn != ''" >
		    , PRE_MPNT_USABLE_YN = #{preMpntUsableYn}
		</if>
		<if test='procJob == "minOrdAmt"  and minOrdAmt != null and minOrdAmt != ""' >
		    , MIN_ORD_AMT = #{minOrdAmt}
		</if>
		<if test='blankFlag != null and blankFlag == "Y"'>
		    , GOODS_TNM = ''
		</if>
		<if test="goodsTnm != null and goodsTnm != ''" >
		    , GOODS_TNM = #{goodsTnm}
		</if>
		<if test="minOrdQty != null and minOrdQty > 0" >
		    , MIN_ORD_QTY = #{minOrdQty}
		</if>
		<if test="maxOrdQty != null and maxOrdQty > 0" >
		    , MAX_ORD_QTY = #{maxOrdQty}
		</if>
		<if test="dayMaxOrdQty != null and dayMaxOrdQty > 0" >
		    , DAY_MAX_ORD_QTY = #{dayMaxOrdQty}
		</if>
		WHERE GOODS_CD = #{goodsCd}
	</update>
	
	<!-- 상품 이미지 필수  항목 입력 여부  조회 -->
	<select id="getGoodsImgsYn" parameterType="Goods" resultType="Goods">
		/* TsaGoods.getGoodsImgsYn */
		SELECT A.GOODS_CD
		     , A.SUPPLY_COMP_CD
		     , (SELECT I.NI_CLSF_CD FROM TB_ITEMKIND I WHERE I.ITEMKIND_CD = A.ITEMKIND_CD) AS NI_CLSF_CD
		     , CASE WHEN B.IMG_PATH1 IS NULL THEN
		            'N'
		       ELSE
		            'Y'
		       END AS GOODS_IMAGE_YN
		FROM TB_GOODS A
		LEFT OUTER JOIN TB_GOODS_IMG B ON A.GOODS_CD = B.GOODS_CD
		WHERE 1 = 1
		<choose>
		<when test="goodsCd != null and goodsCd != ''">
		AND A.GOODS_CD  = #{goodsCd}
		</when>
		<otherwise>
		AND A.GOODS_CD IN
		    <foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
		#{item}
		    </foreach>
		</otherwise>
		</choose>
	</select>
	
	<!-- 상품 정보 이력 -->
	<select id="getGoodsHstList" parameterType="Goods" resultType="GoodsHst">
		/*TsaGoods.getGoodsHstList*/
		SELECT CURR_PRICE
		     , CURR_BPRICE
		     , GOODS_NM
		     , GOODS_TNM
		     , GOODS_SNM
		     , FORMAL_GB
		     , GOODS_STAT
		     , ERP_STOCK_LINK_YN
		     , CHANGEABLE_YN
		     , PRE_PPNT_USABLE_YN
		     , PRE_MPNT_USABLE_YN
		     , GOODS_SNM1
		     , ITEMKIND_CD
		     , SELF_MALL_YN
		     , DATE_FORMAT(FRST_CFRM_DT, '%Y%m%d%H%i%S') AS FRST_CFRM_DT
		     , UPD_NO
		     , FN_GET_USER_NM(UPD_NO) AS UPD_NM
		     , DATE_FORMAT(UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		     , REG_NO
		     , FN_GET_USER_NM(REG_NO) AS REG_NM
		     , DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		FROM TB_GOODS_HST
		WHERE GOODS_CD = #{goodsCd}
		ORDER BY REG_DT DESC
	</select>
	
	<!-- 상품 품목 변경 -->
	<update id="updateGoodItemKindCd" parameterType="Goods">
		/* TsaGoods.updateGoodItemKindCd */
		UPDATE TB_GOODS
		SET ITEMKIND_CD = #{itemkindCd}
		  , UPD_NO = #{updNo}
		  , UPD_DT = NOW()
		WHERE GOODS_CD = #{goodsCd}
	</update >
	
	<!-- 상품 자동 검색어 조회-->
	<select id="getGoodsSnm" parameterType="String" resultType="String">
		/* TsaGoods.getGoodsSnm */
		SELECT UPPER(CONCAT(
		       G.GOODS_CD,';',
		       REPLACE(G.GOODS_NM,' ',''),';',
		       B.BRAND_ENM,';',
		       B.BRAND_KNM,';',
		       B.BRAND_GRP_NM,';',
		       G.STYLE_YEAR,';',
		       FN_GET_CODE_NM('G006',G.SEASON_CD),';',
		       FN_GET_CODE_NM('G007',G.SEX_GB),';',
		       G.COLOR_CD,';',
		       C.COLOR_ENM,';',
		       C.COLOR_KNM,';',
		       REPLACE(I.ITEMKIND_NM,'>',';')
		       )) AS GOODS_SNM
		FROM TB_GOODS G
		INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		LEFT OUTER JOIN TB_ITEMKIND I ON G.ITEMKIND_CD = I.ITEMKIND_CD
		LEFT OUTER JOIN TB_COLOR C ON G.COLOR_CD = C.COLOR_CD
		WHERE G.GOODS_CD = #{goodsCd}
	</select>
	
	<!-- 상품검색어 수정 -->
	<update id="updateGoodsSnm" parameterType="Goods">
		/* TsaGoods.updateGoodsSnm */
		UPDATE TB_GOODS A
		SET UPD_ID = #{updId}
		  , UPD_DT = SYSDATE
		  , GOODS_SNM = #{goodsSnm} 
		WHERE GOODS_CD = #{goodsCd}
	</update>
	
	<!-- 상품 품목변경 고시정보 조회  -->
	<select id="getNewNotiInfo" parameterType="Goods" resultType="GoodsNotiInfo">
		/* TsaGoods.getNewNotiInfo */
		SELECT D.NI_CLSF_CD
		     , NVL(C.NI_ITEM_CD, D.NI_ITEM_CD) AS NI_ITEM_CD
		     , NVL(C.NI_CONTENT, D.NI_CONTENT) AS NI_CONTENT
		     , D.DISP_ORD
		FROM (
		       SELECT NI_ITEM_CD
		            , NI_CONTENT 
		       FROM TB_GOODS_NOTI_INFO
		       WHERE GOODS_CD = #{goodsCd}
		   ) C
		   , (
		       SELECT A.NI_CLSF_CD
		          , A.NI_ITEM_CD
		          , A.NI_CONTENT
		          , A.DISP_ORD
		       FROM TB_NOTI_INFO A
		          , TB_ITEMKIND B
		       WHERE A.NI_CLSF_CD = B.NI_CLSF_CD
		       AND A.SUPPLY_COMP_CD = #{supplyCompCd}
		       AND B.ITEMKIND_CD = #{itemkindCd}
		   ) D
		WHERE D.NI_ITEM_CD = C.NI_ITEM_CD (+)
		ORDER BY D.DISP_ORD
	</select>
	
	<!-- 상품 품목변경 고시정보 삭제  -->
	<delete id="deleteGoodsNotiInfo" parameterType="Goods">
		/* TsaGoods.deleteGoodsNotiInfo */
		DELETE FROM TB_GOODS_NOTI_INFO
		WHERE GOODS_CD = #{goodsCd}
	</delete>
	
	<!-- 상품 고시 정보  저장 -->
	<update id="saveGoodsNotiInfo" parameterType="GoodsNotiInfo">
		/* TsaGoods.saveGoodsNotiInfo */
		INSERT INTO TB_GOODS_NOTI_INFO(
		       GOODS_CD
		     , NI_CLSF_CD
		     , NI_ITEM_CD
		     , NI_CONTENT
		     , DISP_ORD
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{goodsCd}
		     , #{niClsfCd}
		     , #{niItemCd}
		     , #{niContent}
		     , #{dispOrd}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       NI_CONTENT = #{niContent}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</update>
	
	<!-- 코디 상품 목록 조회  -->
	<select id="getCodiGoodsList" parameterType="CodiGoods" resultType="CodiGoods">
		/* TsaGoods.getCodiGoodsList */
		SELECT A.GOODS_CD
		     , A.CODI_GOODS_CD
		     , A.DISP_ORD
		     , A.DEL_YN
		     , G.GOODS_NM
		     , G.CURR_PRICE
		     , G.GOODS_STAT
		     , G.FORMAL_GB
		     , GI.IMG_TYPE
		     , GI.IMG_PATH1
		     , GI.IMG_PATH6
		     , FN_GET_USER_NM(A.UPD_NO) AS UPD_NM
		     , A.UPD_NO
		     , DATE_FORMAT(A.UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		FROM TB_CODI_GOODS A
		INNER JOIN TB_GOODS G ON A.CODI_GOODS_CD = G.GOODS_CD
		LEFT OUTER JOIN TB_GOODS_IMG GI ON A.CODI_GOODS_CD = GI.GOODS_CD
		WHERE DEL_YN = 'N'
		AND A.GOODS_CD = #{goodsCd}
		ORDER BY A.DISP_ORD
	</select>
	
	<!-- 상품 사이즈 조회 -->
	<select id="getGoodsSizeList" parameterType="Goods" resultType="Option">
		/* TsaGoods.getGoodsSizeList */
		WITH RECURSIVE TMP_GOODS AS (
		    SELECT A.GOODS_CD 
		         , A.GOODS_TYPE 
		         , IFNULL(B.COMPS_GOODS_CD, A.GOODS_CD) AS COMPS_GOODS_CD
		         , IFNULL(B.DISP_ORD,1) AS DISP_ORD
		         , A.SELF_GOODS_YN
		    FROM TB_GOODS A 
		    LEFT OUTER JOIN TB_GOODS_COMPOSE B ON A.GOODS_CD = B.GOODS_CD
		    WHERE A.GOODS_CD = #{goodsCd}
		    )
		SELECT #{goodsCd} AS GOODS_CD
		     , A.GOODS_CD AS COMPS_GOODS_CD
		     , A.OPT_CD
		     , CONCAT(A.OPT_NM1,  NVL2(A.OPT_NM2, CONCAT('/', A.OPT_NM2), '')) AS OPT_NM 
		     , A.OPT_NM1
		     , A.OPT_NM2
		     , A.BASE_STOCK_QTY
		     , A.CURR_STOCK_QTY
		     , IFNULL(B.STOCK_QTY,0) AS SALE_STOCK_QTY
		     , GREATEST(GREATEST(A.CURR_STOCK_QTY - A.BASE_STOCK_QTY, 0) - IFNULL(B.STOCK_QTY, 0), 0) AS ABLE_STOCK_QTY
		     , A.SOLDOUT_YN
		     , A.DISP_ORD
		     , A.DISP_YN
		     , COUNT(*) OVER(PARTITION BY A.GOODS_CD ) AS RCOUNT
		     , ROW_NUMBER() OVER(PARTITION BY A.GOODS_CD ORDER BY A.DISP_ORD) AS RNUM
		FROM  TMP_GOODS C 
		INNER JOIN TB_OPTION A ON C.COMPS_GOODS_CD = A.GOODS_CD 
		LEFT OUTER JOIN (
		                 SELECT Z.GOODS_CD
		                      , Z.OPT_CD
		                      , SUM((CASE Z.SELL_GB WHEN '10' THEN 1
		                                            WHEN '20' THEN 1
		                                            ELSE -1 END) * Z.SELL_QTY) AS STOCK_QTY
		                 FROM TB_SELL_QTY Z
		                 INNER JOIN TMP_GOODS Y ON Z.GOODS_CD = Y.COMPS_GOODS_CD
		                 GROUP BY Z.GOODS_CD, Z.OPT_CD
		                ) B
		           ON (  A.GOODS_CD = B.GOODS_CD
		             AND A.OPT_CD = B.OPT_CD)
		WHERE 1 = 1
		AND C.GOODS_CD = #{goodsCd}
		ORDER BY C.DISP_ORD, A.DISP_ORD
	</select>
	
	<!-- 구성상품 목록 -->
	<select id="getGoodsDetailComposeList" parameterType="Goods" resultType="GoodsCompose">
		/* TsaGoods.getGoodsDetailComposeList */
		SELECT A.GOODS_CD
		     , A.GOODS_TYPE
		     , FN_GET_CODE_NM('G056', A.GOODS_TYPE) AS GOODS_TYPE_NM
		     , A.COMPS_GOODS_CD
		     , B.GOODS_NM AS COMPS_GOODS_NM
		     , A.DISP_ORD
		     , A.QTY
		     , A.COMPS_CURR_PRICE
		     , A.COMPS_GOODS_OPT_NM
		     , A.BASE_YN
		     , A.USE_YN
		     , B.CURR_PRICE
		     , B.GOODS_STAT
		     , B.SELF_GOODS_YN
		     , B.SUPPLY_COMP_CD
		     , I.IMG_PATH1
		     , I.IMG_PATH6
		     , I.IMG_TYPE
		     , A.REG_NO
		     , FN_GET_USER_NM(A.REG_NO) AS UPD_NM
		     , DATE_FORMAT(A.REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		     , A.UPD_NO
		     , FN_GET_USER_NM(A.UPD_NO) AS UPD_NM
		     , DATE_FORMAT(A.UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		FROM TB_GOODS_COMPOSE A
		INNER JOIN  TB_GOODS B ON A.COMPS_GOODS_CD = B.GOODS_CD
		LEFT OUTER JOIN TB_GOODS_IMG I ON A.COMPS_GOODS_CD = I.GOODS_CD
		WHERE A.GOODS_CD = #{goodsCd}
		ORDER BY A.DISP_ORD 
	</select>
	
	<!-- 상품 기본 정보 수정 -->
	<update id="updateGoods" parameterType="Goods">
		/* TsaGoods.updateGoods */
		UPDATE TB_GOODS
		SET GOODS_TNM = #{goodsTnm}
		  , GOODS_NM = #{goodsNm}
		  , SEASON_CD = #{seasonCd}
		  , SEX_GB = #{sexGb}
		  , CURR_PRICE = #{currPrice}
		  <if test="currBprice != null and currBprice != ''">
		  , CURR_BPRICE = #{currBprice}
		  , PRICE_UPD_DT = NOW()
		  </if>
		  , DC_RATE = #{dcRate}
		  , GOODS_STAT = #{goodsStat}
		  , GOODS_GB = #{goodsGb}
		  , MIN_ORD_AMT = #{minOrdAmt}
		  , PNT_PRATE = #{pntPrate}
		  , PNT_MRATE = #{pntMrate}
		  , FORMAL_GB = #{formalGb}
		  , MIN_ORD_QTY = #{minOrdQty}
		  , MAX_ORD_QTY = #{maxOrdQty}
		  , DAY_MAX_ORD_QTY = #{dayMaxOrdQty}
		  , ERP_STOCK_LINK_YN = #{erpStockLinkYn}
		  , GOODS_SNM1 = #{goodsSnm1}
		  , SELF_MALL_YN = #{selfMallYn}
		  , GOODS_SNM = #{goodsSnm}
		  , FRST_CFRM_DT = (CASE #{chGoodsStatYn} WHEN 'Y' THEN NVL2(FRST_CFRM_DT, NOW(), FRST_CFRM_DT)
		                                          ELSE FRST_CFRM_DT END)
		  , PRE_PPNT_USABLE_YN = #{prePpntUsableYn}
		  , PRE_MPNT_USABLE_YN = #{preMpntUsableYn}
		  , UPD_NO = #{updNo}
		  , UPD_DT = NOW()
		WHERE GOODS_CD = #{goodsCd}
	</update>
	
	<!-- 상품 상세정보 삭제 -->
	<delete id="deleteGoodsDesc" parameterType="GoodsDesc">
		/* TsaGoods.deleteGoodsDesc */
		DELETE TB_GOODS_DESC
		WHERE GOODS_CD = #{goodsCd}
		AND DESC_GB = #{descGb}
	</delete>
	
	<!-- 상품 상세정보 등록 -->
	<insert id="createGoodsDesc" parameterType="GoodsDesc">
		/* TsaGoods.createGoodsDesc */
		INSERT INTO TB_GOODS_DESC (
		    GOODS_CD
		  , DESC_GB
		  , SEQ
		  , GOODS_DESC
		  , REG_NO
		  , REG_DT
		)
		VALUES(
		    #{goodsCd}
		  , #{descGb}
		  , #{seq}
		  , REPLACE(#{goodsDesc},'char(13)', CONCAT(char(13),char(10))
		  , #{regNo}
		  , NOW()
		)
	</insert>
	
	<!-- 구상 상품등록/저장 -->
	<insert id="saveGoodsCompose" parameterType="GoodsCompose">
		/* TsaGoods.saveGoodsCompose */
		INSERT INTO TB_GOODS_COMPOSE (
		           GOODS_CD
		         , COMPS_GOODS_CD
		         , GOODS_TYPE
		         , DISP_ORD
		         , QTY
		         , COMPS_CURR_PRICE
		         , COMPS_GOODS_OPT_NM
		         , BASE_YN
		         , USE_YN
		         , REG_NO
		         , REG_DT
		         , UPD_NO
		         , UPD_DT
		    )
		    VALUES (
		           #{goodsCd}
		         , #{compsGoodsCd}
		         , #{goodsType}
		         , #{dispOrd}
		         , #{qty}
		         , (CASE WHEN #{goodsType} = 'S' THEN #{compsCurrPrice}
		                ELSE 0 END)
		         , #{compsGoodsOptNm}
		         , #{baseYn}
		         , #{useYn}
		         , #{regNo}
		         , NOW()
		         , #{updNo}
		         , NOW()
		    )
		ON DUPLICATE KEY UPDATE 
		           DISP_ORD = #{dispOrd}
		         , COMPS_GOODS_OPT_NM = #{compsGoodsOptNm}
		         , USE_YN = #{useYn}
		         , UPD_NO = #{updNo}
		         , UPD_DT = NOW()
	</insert>
	
	<!-- 상품 옵션/재고 정보 이력생성 -->
	<insert id="createStockHst" parameterType="Option">
	/* TsaGoods.createStockHst */
		INSERT INTO TB_OPTION_HST 
		(     OPTION_HST_SQ
		    , GOODS_CD
		    , OPT_CD
		    , OPT_NM1
		    , OPT_NM2
		    , BASE_STOCK_QTY
		    , CURR_STOCK_QTY
		    , ADD_PRICE
		    , SOLDOUT_YN
		    , DISP_ORD
		    , DISP_YN
		    , REG_NO
		    , REG_DT
		    , UPD_NO
		    , UPD_DT
		)
		SELECT NULL
		     , GOODS_CD
		     , OPT_CD
		     , OPT_NM1
		     , OPT_NM2
		     , BASE_STOCK_QTY
		     , CURR_STOCK_QTY
		     , ADD_PRICE
		     , SOLDOUT_YN
		     , DISP_ORD
		     , DISP_YN
		     , #{regNo}
		     , NOW()
		     , UPD_NO
		     , UPD_DT
		FROM TB_OPTION
		WHERE GOODS_CD = #{goodsCd}
		<if test='optCd != null and optCd != ""'>
		AND OPT_CD = #{optCd}
		</if>
	</insert>
	
	<!-- 상품 옵션/재고 정보 저장 -->
	<update id="saveStock" parameterType="Option">
		/* TsaGoods.saveStock */
		INSERT INTO TB_OPTION(
		       GOODS_CD
		     , OPT_CD
		     , OPT_NM1
		     , OPT_NM2
		     , BASE_STOCK_QTY
		     , CURR_STOCK_QTY
		     , ADD_PRICE
		     , SOLDOUT_YN
		     , DISP_ORD
		     , DISP_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{goodsCd}
		     , #{optCd}
		     , #{optNm1}
		     , #{optNm2}
		     , #{baseStockQty}
		     , #{currStockQty}
		     , #{addPrice}
		     , #{soldoutYn}
		     , #{dispOrd}
		     , #{dispYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE 
		        BASE_STOCK_QTY = #{baseStockQty}
		      , OPT_NM1 = #{optNm1}
		      , OPT_NM2 = #{optNm2}
		      , ADD_PRICE = #{addPrice}
		      , SOLDOUT_YN = #{soldoutYn}
		      , DISP_ORD = #{dispOrd}
		      , DISP_YN = #{dispYn}
		      , UPD_NO = #{updNo}
		      , UPD_DT = NOW()
		      <if test='selfGoodsYn != null and selfGoodsYn == "N"'>
		      , CURR_ON_STOCK_QTY = #{currOnStockQty}
		      </if>
	</update>
	
	<!-- 코디상품 저장 -->
	<insert id="saveCodiGoods" parameterType="CodiGoods">
		/* TsaGoods.saveCodiGoods */
		INSERT INTO TB_CODI_GOODS (
		    GOODS_CD
		  , CODI_GOODS_CD
		  , DISP_ORD
		  , DEL_YN
		  , REG_NO
		  , REG_DT
		  , UPD_NO
		  , UPD_DT
		) VALUES(
		    #{goodsCd}
		  , #{codiGoodsCd}
		  , #{dispOrd}
		  , #{delYn}
		  , #{regNo}
		  , NOW()
		  , #{updNo}
		  , NOW()
		)
		ON DUPLICATE KEY UPDATE 
		      DISP_ORD = #{dispOrd}
		    , DEL_YN = #{delYn} 
		    , UPD_NO = #{updNo}
		    , UPD_DT = NOW()
	</insert>
	
	<!-- 코디상품 삭제 -->
	<delete id="deleteCodiGoods" parameterType="CodiGoods">
		/* TsaGoods.deleteCodiGoods */
		UPDATE TB_CODI_GOODS SET
		      DEL_YN = #{delYn} 
		    , UPD_NO = #{updNo}
		    , UPD_DT = NOW()
		WHERE GOODS_CD = #{goodsCd}
		AND CODI_GOODS_CD = #{codiGoodsCd}
	</delete>
	
</mapper>