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

	<!-- 카테고리 목록 -->
	<select id="getCategoryList" parameterType="Category" resultType="Category">
		/* TsaDisplay.getCategoryList */
		<if test='selLvl != null and selLvl != ""'>
		SELECT SITE_CD                 /*사이트코드*/
		     , CATE_GB                 /*카테고리구분*/
		    <if test='selLvl == 1'>
		     , CATE1_NO
		     , NULL     AS CATE2_NO
		     , NULL     AS CATE3_NO
		     , NULL     AS CATE4_NO
		     , NULL     AS CATE5_NO
		     , CATE1_NO AS CATE_NO     /*카테고리번호*/
		     , CATE1_NM AS CATE_NM     /*카테고리명*/
		    </if>
		    <if test='selLvl == 2'>
		     , CATE2_NO
		     , NULL     AS CATE3_NO
		     , NULL     AS CATE4_NO
		     , NULL     AS CATE5_NO
		     , CATE2_NO AS CATE_NO     /*카테고리번호*/
		     , CATE2_NM AS CATE_NM     /*카테고리명*/
		    </if>
		    <if test='selLvl == 3'>
		     , CATE2_NO
		     , CATE3_NO
		     , NULL     AS CATE4_NO
		     , NULL     AS CATE5_NO
		     , CATE3_NO AS CATE_NO     /*카테고리번호*/
		     , CATE3_NM AS CATE_NM     /*카테고리명*/
		    </if>
		    <if test='selLvl == 4'>
		     , CATE2_NO
		     , CATE3_NO
		     , CATE4_NO
		     , NULL     AS CATE5_NO
		     , CATE4_NO AS CATE_NO     /*카테고리번호*/
		     , CATE4_NM AS CATE_NM     /*카테고리명*/
		    </if>
		    <if test='selLvl == 5'>
		     , CATE2_NO
		     , CATE3_NO
		     , CATE4_NO
		     , CATE5_NO
		     , CATE5_NO AS CATE_NO     /*카테고리번호*/
		     , CATE5_NM AS CATE_NM     /*카테고리명*/
		    </if>
		     , CATE_TYPE               /*카테고리유형*/
		     , LEAF_YN                 /*단말여부*/
		     , DISP_ORD                /*노출순서*/
		     , FORMAL_GB               /*정상이월구분*/
		     , CONTENTS_LOC            /*컨텐츠위치*/
		     , DISP_YN                 /*전시여부*/
		     , USE_YN                  /*사용여부*/
		    <if test='selLvl == 1'>
		FROM   TB_CATE1
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		    </if>
		    <if test='selLvl == 2'>
		FROM   TB_CATE2
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		AND    CATE1_NO = #{cate1No}
		    </if>
		    <if test='selLvl == 3'>
		FROM   TB_CATE3
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		AND    CATE1_NO = #{cate1No}
		AND    CATE2_NO = #{cate2No}
		    </if>
		    <if test='selLvl == 4'>
		FROM   TB_CATE4
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		AND    CATE1_NO = #{cate1No}
		AND    CATE2_NO = #{cate2No}
		AND    CATE3_NO = #{cate3No}
		    </if>
		    <if test='selLvl == 5'>
		FROM   TB_CATE5
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		AND    CATE1_NO = #{cate1No}
		AND    CATE2_NO = #{cate2No}
		AND    CATE3_NO = #{cate3No}
		AND    CATE4_NO = #{cate4No}
		    </if>
		ORDER  BY DISP_ORD
		</if>
	</select>
	
	<!-- 카테고리1 저장 -->
	<insert id="saveCategory1" parameterType="Category">
		/* TsaDisplay.saveCategory1 */
		INSERT INTO TB_CATE1 (
		       SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE1_NM
		     , CATE_TYPE
		     , LEAF_YN
		     , DISP_ORD
		     , FORMAL_GB
		     , CONTENTS_LOC
		     , DISP_YN
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{siteCd}
		     , #{cateGb}
		     , #{cate1No}
		     , #{cate1Nm}
		     , #{cateType}
		     , 'Y'
		     , #{dispOrd}
		     , #{formalGb}
		     , #{contentsLoc}
		     , #{dispYn}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       CATE1_NM = #{cate1Nm}
		     , CATE_TYPE = #{cateType}
		     , DISP_ORD = #{dispOrd}
		     , FORMAL_GB = #{formalGb}
		     , CONTENTS_LOC = #{contentsLoc}
		     , DISP_YN = #{dispYn}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 카테고리2 저장 -->
	<insert id="saveCategory2" parameterType="Category">
		/* TsaDisplay.saveCategory2 */
		INSERT INTO TB_CATE2 (
		       SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE2_NM
		     , CATE_TYPE
		     , LEAF_YN
		     , DISP_ORD
		     , FORMAL_GB
		     , CONTENTS_LOC
		     , DISP_YN
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{siteCd}
		     , #{cateGb}
		     , #{cate1No}
		     , #{cate2No}
		     , #{cate2Nm}
		     , #{cateType}
		     , 'Y'
		     , #{dispOrd}
		     , #{formalGb}
		     , #{contentsLoc}
		     , #{dispYn}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       CATE1_NO = #{cate1No}
		     , CATE2_NM = #{cate2Nm}
		     , CATE_TYPE = #{cateType}
		     , DISP_ORD = #{dispOrd}
		     , FORMAL_GB = #{formalGb}
		     , CONTENTS_LOC = #{contentsLoc}
		     , DISP_YN = #{dispYn}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 카테고리3 저장 -->
	<insert id="saveCategory3" parameterType="Category">
		/* TsaDisplay.saveCategory3 */
		INSERT INTO TB_CATE3 (
		       SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE3_NO
		     , CATE3_NM
		     , CATE_TYPE
		     , LEAF_YN
		     , DISP_ORD
		     , FORMAL_GB
		     , CONTENTS_LOC
		     , DISP_YN
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{siteCd}
		     , #{cateGb}
		     , #{cate1No}
		     , #{cate2No}
		     , #{cate3No}
		     , #{cate3Nm}
		     , #{cateType}
		     , 'Y'
		     , #{dispOrd}
		     , #{formalGb}
		     , #{contentsLoc}
		     , #{dispYn}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       CATE2_NO = #{cate2No}
		     , CATE3_NM = #{cate3Nm}
		     , CATE_TYPE = #{cateType}
		     , DISP_ORD = #{dispOrd}
		     , FORMAL_GB = #{formalGb}
		     , CONTENTS_LOC = #{contentsLoc}
		     , DISP_YN = #{dispYn}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 카테고리4 저장 -->
	<insert id="saveCategory4" parameterType="Category">
		/* TsaDisplay.saveCategory4 */
		INSERT INTO TB_CATE4 (
		       SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE3_NO
		     , CATE4_NO
		     , CATE4_NM
		     , CATE_TYPE
		     , LEAF_YN
		     , DISP_ORD
		     , FORMAL_GB
		     , CONTENTS_LOC
		     , DISP_YN
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{siteCd}
		     , #{cateGb}
		     , #{cate1No}
		     , #{cate2No}
		     , #{cate3No}
		     , #{cate4No}
		     , #{cate4Nm}
		     , #{cateType}
		     , 'Y'
		     , #{dispOrd}
		     , #{formalGb}
		     , #{contentsLoc}
		     , #{dispYn}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       CATE3_NO = #{cate3No}
		     , CATE4_NM = #{cate4Nm}
		     , CATE_TYPE = #{cateType}
		     , DISP_ORD = #{dispOrd}
		     , FORMAL_GB = #{formalGb}
		     , CONTENTS_LOC = #{contentsLoc}
		     , DISP_YN = #{dispYn}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 카테고리5 저장 -->
	<insert id="saveCategory5" parameterType="Category">
		/* TsaDisplay.saveCategory5 */
		INSERT INTO TB_CATE5 (
		       SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE3_NO
		     , CATE4_NO
		     , CATE5_NO
		     , CATE5_NM
		     , CATE_TYPE
		     , LEAF_YN
		     , DISP_ORD
		     , FORMAL_GB
		     , CONTENTS_LOC
		     , DISP_YN
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{siteCd}
		     , #{cateGb}
		     , #{cate1No}
		     , #{cate2No}
		     , #{cate3No}
		     , #{cate4No}
		     , #{cate5No}
		     , #{cate5Nm}
		     , #{cateType}
		     , 'Y'
		     , #{dispOrd}
		     , #{formalGb}
		     , #{contentsLoc}
		     , #{dispYn}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       CATE4_NO = #{cate4No}
		     , CATE5_NM = #{cate5Nm}
		     , CATE_TYPE = #{cateType}
		     , DISP_ORD = #{dispOrd}
		     , FORMAL_GB = #{formalGb}
		     , CONTENTS_LOC = #{contentsLoc}
		     , DISP_YN = #{dispYn}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 상위카테고리 단말여부 "N" 처리-->
	<update id="updateUpperCategoryLeafNo" parameterType="Category">
		/* TsaDisplay.updateUpperCategoryLeafNo */
		<if test='selLvl == 2'>
		UPDATE TB_CATE1
		</if>
		<if test='selLvl == 3'>
		UPDATE TB_CATE2
		</if>
		<if test='selLvl == 4'>
		UPDATE TB_CATE3
		</if>
		<if test='selLvl == 5'>
		UPDATE TB_CATE4
		</if>
		SET    LEAF_YN = 'N'
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		<if test='selLvl gte 2'>
		AND    CATE1_NO = #{cate1No}
		</if>
		<if test='selLvl gte 3'>
		AND    CATE2_NO = #{cate2No}
		</if>
		<if test='selLvl gte 4'>
		AND    CATE3_NO = #{cate3No}
		</if>
		<if test='selLvl gte 5'>
		AND    CATE4_NO = #{cate4No}
		</if>
		AND    LEAF_YN = 'Y'
	</update>
	
	<!-- 카테고리4SRCH truncate -->
	<delete id="truncateCategory4Srch">
		/* TsaDisplay.truncateCategory4Srch TRUNCATE TABLE TB_CATE_4SRCH */
		DELETE FROM TB_CATE_4SRCH
	</delete>
	
	<!-- 카테고리4SRCH 생성 -->
	<insert id="createCategory4Srch">
		/* TsaDisplay.createCategory4Srch */
		INSERT INTO TB_CATE_4SRCH (
		       SITE_CD
		     , CATE_GB
		     , LEAF_CATE_NO
		     , CATE_TYPE
		     , CATE1_NO
		     , CATE1_NM
		     , CATE2_NO
		     , CATE2_NM
		     , CATE3_NO
		     , CATE3_NM
		     , CATE4_NO
		     , CATE4_NM
		     , CATE5_NO
		     , CATE5_NM
		     , FULL_CATE_NO
		     , FULL_CATE_NM
		     , DISP_ORD
		     , FORMAL_GB
		     , CONTENTS_LOC
		)
		SELECT SITE_CD
		     , CATE_GB
		     , LEAF_CATE_NO
		     , CATE_TYPE
		     , CATE1_NO
		     , CATE1_NM
		     , CATE2_NO
		     , CATE2_NM
		     , CATE3_NO
		     , CATE3_NM
		     , CATE4_NO
		     , CATE4_NM
		     , CATE5_NO
		     , CATE5_NM
		     , FULL_CATE_NO
		     , FULL_CATE_NM
		     , DISP_ORD
		     , FORMAL_GB
		     , CONTENTS_LOC
		FROM   VW_CATE_4SRCH
	</insert>
	
	<!-- 카테고리구분 목록 -->
	<select id="getCategoryGbList" parameterType="String" resultType="Category">
		/* TsaDisplay.getCategoryGbList */
		SELECT CATE_GB                                        /*카테고리구분*/
		     , LEAF_CATE_NO                    AS CATE_NO     /*카테고리번호*/
		     , CASE WHEN LEAF_CATE_NO = CATE5_NO THEN CATE5_NM
		            WHEN LEAF_CATE_NO = CATE4_NO THEN CATE4_NM
		            WHEN LEAF_CATE_NO = CATE3_NO THEN CATE3_NM
		            WHEN LEAF_CATE_NO = CATE2_NO THEN CATE2_NM
		            WHEN LEAF_CATE_NO = CATE1_NO THEN CATE1_NM
		       END                             AS CATE_NM     /*카테고리명*/
		     , FULL_CATE_NO                                   /*FULL카테고리번호*/
		     , FULL_CATE_NM                                   /*FULL카테고리명*/
		     , CASE WHEN LEAF_CATE_NO = CATE5_NO THEN 5
		            WHEN LEAF_CATE_NO = CATE4_NO THEN 4
		            WHEN LEAF_CATE_NO = CATE3_NO THEN 3
		            WHEN LEAF_CATE_NO = CATE2_NO THEN 2
		            WHEN LEAF_CATE_NO = CATE1_NO THEN 1
		       END                             AS CLS_LVL     /*분류레벨*/
		     , REPLACE(FULL_CATE_NO,' > ','/') AS TREE_PATH   /*AG-GRID트리패스(ag-Grid미사용시 필요 없음)*/
		     , (SELECT LEAF_YN FROM TB_CATE2 C2 WHERE C2.CATE2_NO = C4.CATE2_NO AND C2.CATE2_NO = C4.LEAF_CATE_NO) AS LEAF_YN /*카테고리2 LEAF_YN 여부*/
		FROM   TB_CATE_4SRCH C4
		WHERE  CATE_GB = #{cateGb}
		AND    CATE_TYPE = 'G031_10' /*상품분류카테고리*/
		ORDER  BY DISP_ORD
	</select>
	
	<!-- 품목카테고리매핑 생성  -->
	<insert id="createItemkindCategoryMapping" parameterType="ItemkindCategory">
		/* TsaDisplay.createItemkindCategoryMapping */
		INSERT INTO TB_ITEMKIND_CATE (
		       ITEMKIND_CD
		     , CATE_NO
		     , REG_NO
		     , REG_DT
		)
		VALUES (
		       #{itemkindCd}
		     , #{cateNo}
		     , #{regNo}
		     , NOW()
		)
	</insert>
	
	<!-- 품목카테고리매핑에 의한 카테고리상품 생성  -->
	<insert id="createCategoryGoodsByItemkindCategoryMapping" parameterType="ItemkindCategory">
		/* TsaDisplay.createCategoryGoodsByItemkindCategoryMapping */
		INSERT INTO TB_CATE_GOODS (
		       BRAND_GROUP_NO
		     , CATE_NO
		     , GOODS_CD
		     , DISP_ORD
		     , REG_NO
		     , REG_DT
		)
		SELECT 0 AS BRAND_GROUP_NO
		     , #{cateNo} AS CATE_NO
		     , GOODS_CD
		     , 100       AS DISP_ORD
		     , #{regNo}  AS REG_NO
		     , NOW()     AS REG_DT
		FROM   TB_GOODS A
		WHERE  ITEMKIND_CD = #{itemkindCd}
		AND    GOODS_STAT NOT IN ('G008_95','G008_99') /*시즌아웃,DROP(삭제)가 아닌넘*/
		AND    NOT EXISTS (SELECT 1
		                   FROM   TB_CATE_GOODS
		                   WHERE  CATE_NO = #{cateNo}
		                   AND    BRAND_GROUP_NO = 0
		                   AND    GOODS_CD = A.GOODS_CD
		                  )
	</insert>

	<!-- 품목카테고리매핑 삭제  -->
	<delete id="deleteItemkindCategoryMapping" parameterType="ItemkindCategory">
		/* TsaDisplay.deleteItemkindCategoryMapping */
		DELETE FROM TB_ITEMKIND_CATE
		WHERE  ITEMKIND_CD = #{itemkindCd}
		AND    CATE_NO = #{cateNo}
	</delete>
	
	<!-- 품목카테고리매핑에 의한 카테고리상품 삭제  -->
	<delete id="deleteCategoryGoodsByItemkindCategoryMapping" parameterType="ItemkindCategory">
		/* TsaDisplay.deleteCategoryGoodsByItemkindCategoryMapping */
		DELETE FROM TB_CATE_GOODS
		WHERE  CATE_NO = #{cateNo}
		AND    BRAND_GROUP_NO = 0
		AND    GOODS_CD IN (SELECT GOODS_CD
		                    FROM   TB_GOODS
		                    WHERE  ITEMKIND_CD = #{itemkindCd}
		                   )
	</delete>
	
	<!-- 품목카테고리매핑 목록 -->
	<select id="getItemkindCategoryMappingList" parameterType="String" resultType="ItemkindCategory">
		/* TsaDisplay.getItemkindCategoryMappingList */
		SELECT IC.ITEMKIND_CD             /*품목코드*/
		     , C4.CATE_GB                 /*카테고리구분*/
		     , C4.LEAF_CATE_NO AS CATE_NO /*카테고리번호*/
		     , C4.FULL_CATE_NO            /*FULL카테고리번호*/
		     , C4.FULL_CATE_NM            /*FULL카테고리명*/
		FROM   TB_ITEMKIND_CATE IC
		     , TB_CATE_4SRCH C4
		WHERE  IC.CATE_NO = C4.LEAF_CATE_NO
		AND    IC.ITEMKIND_CD = #{itemkindCd}
		ORDER  BY C4.CATE_GB, C4.LEAF_CATE_NO
	</select>

	<!-- 상품별카테고리전시관리 목록 건수 조회 -->
	<select id="getGoodsCategoryListCount" parameterType="GoodsCategory" resultType="int">
		/* TsaDisplay.getGoodsCategoryListCount */
		SELECT  COUNT(*) AS TOTCNT
		FROM	TB_GOODS G
		INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		<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="mdNo != null and mdNo != ''">
				AND G.BRAND_CD IN (
				SELECT DISTINCT BRAND_CD
				FROM TB_BRAND_MD
				WHERE MD_NO = CAST(#{mdNo} AS UNSIGNED)
				)
			</if>
			)
		</if>
		WHERE 1=1
		<if test="searchGb == null or searchGb =='BASIC' or searchGb =='EXTEND' or searchGb =='MASTER'" >
			<include refid="getGoodsCategoryListCondition_sql"/>
		</if>
		<if test='stockQtySum != null and stockQtySum != ""'>
		AND IFNULL((SELECT STOCK_QTY FROM TB_GOODS_STOCK WHERE GOODS_CD = G.GOODS_CD),0) <![CDATA[>]]> #{stockQtySum}
		</if>
	</select>

	<!-- 상품별카테고리전시관리 목록 조회 -->
	<select id="getGoodsCategoryList" parameterType="GoodsCategory" resultType="GoodsCategory">
		/* TsaDisplay.getGoodsCategoryList */
		SELECT  Q.*
		, (SELECT ITEMKIND_NM FROM TB_ITEMKIND WHERE ITEMKIND_CD = Q.ITEMKIND_CD ) AS ITEMKIND_NM
		, (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = Q.GOODS_CD AND COLOR_CD = Q.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ) AS SYS_IMG_NM
		, CASE IFNULL((SELECT COUNT(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = Q.GOODS_CD AND COLOR_CD = Q.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ),0)
		       WHEN 0 THEN 'N'
		  ELSE 'Y' END AS GOODS_IMAGE_YN
		, (SELECT CD_NM FROM TB_COMMON_CODE WHERE 1=1 AND CD = Q.GOODS_TYPE) AS GOODS_TYPE
		FROM (
		SELECT	Z.*
		FROM	(
			SELECT	A.*
		          , IFNULL((SELECT STOCK_QTY FROM TB_GOODS_STOCK WHERE GOODS_CD = A.GOODS_CD),0) AS STOCK_QTY_SUM
			FROM	(
				SELECT	'카테고리확인' AS FULL_CATE_NM
						, G.GOODS_CD
						, G.GOODS_NM
						, IFNULL(G.LIST_PRICE,0) AS LIST_PRICE
						, IFNULL(G.CURR_PRICE,0) AS CURR_PRICE
						, ROUND((G.LIST_PRICE - G.CURR_PRICE) / (G.LIST_PRICE*1.0) *100 , 0) AS DC_RATE
						, FN_GET_CODE_NM('G056',G.GOODS_TYPE) AS GOODS_TYPE
						, B.BRAND_ENM
						, B.BRAND_GRP_NM
						, G.PNT_PRATE
						, G.PRE_PPNT_USABLE_YN
						, G.PNT_MRATE
						, G.PRE_MPNT_USABLE_YN
						, G.ERP_STOCK_LINK_YN
						, G.ERP_PRICE_LINK_YN
						, FN_GET_CODE_NM('G009',IFNULL(G.FORMAL_GB,'10')) AS FORMAL_GB
						, G.SELF_GOODS_YN
						, G.REG_DT
						, G.SUPPLY_GOODS_CD
						, G.SUPPLY_COMP_CD
						, G.BRAND_CD
						, G.ITEMKIND_CD
						, G.MAIN_COLOR_CD
						, G.GOODS_STAT
						, G.STYLE_YEAR
						, FN_GET_CODE_NM('G006',G.SEASON_CD) AS SEASON_CD
						<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
				INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
				<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="mdNo != null and mdNo != ''">
						AND G.BRAND_CD IN (
						SELECT DISTINCT BRAND_CD
						FROM TB_BRAND_MD
						WHERE MD_NO = CAST(#{mdNo} AS UNSIGNED)
						)
					</if>
					)
				</if>
				WHERE 1=1
				<if test="searchGb == null or searchGb =='BASIC' or searchGb =='EXTEND' or searchGb =='MASTER'" >
					<include refid="getGoodsCategoryListCondition_sql"/>
					ORDER BY G.REG_DT DESC, G.GOODS_CD
				</if>
				<if test="searchGb != null and searchGb =='EXCEL'">
					ORDER BY SD.TMP_DISP_ORD, G.GOODS_CD
				</if>
			<include refid="getListPagingCondition_sql"/>
	</select>

	<sql id="getListPagingCondition_sql">
		<choose>
			<when test="pageable != null">
		            ) A
		        )Z
				<if test='stockQtySum != null and stockQtySum != ""'>
				WHERE 1=1
				AND Z.STOCK_QTY_SUM <![CDATA[>]]> #{stockQtySum}
				</if>
		        LIMIT #{pageable.limitStartRow} ,  #{pageable.pageSize}
		    ) Q
			</when>
			<otherwise>
		            ) A
		        )Z
				<if test='stockQtySum != null and stockQtySum != ""'>
					WHERE 1=1
					AND Z.STOCK_QTY_SUM <![CDATA[>]]> #{stockQtySum}
				</if>
		    )Q
			</otherwise>
		</choose>
	</sql>

	<sql id="getGoodsCategoryListCondition_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">
						G.GOODS_CD LIKE CONCAT(UPPER(#{item}),'%')
					</foreach>
					)
				</when>
				<when test='search != null and search == "searchGoodsNm"'>
					AND (
					<foreach collection="conditionList" item="item" index="index" separator="or">
						UPPER(G.GOODS_NM) 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 1 = 1
				</otherwise>
			</choose>
		</if>
		<if test='goodsCd != null and goodsCd != ""'>
			AND G.GOODS_CD LIKE CONCAT(UPPER(#{goodsCd}),'%')
		</if>
		<if test="arrGoodsCd != null and arrGoodsCd.length>0">
			AND G.GOODS_CD IN
			<foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
				UPPER(#{item})
			</foreach>
		</if>
		<if test='supplyGoodsCd != null and supplyGoodsCd != ""'>
			AND G.SUPPLY_GOODS_CD = #{supplyGoodsCd}
		</if>
		<if test='goodsNm != null and goodsNm != ""'>
			AND UPPER(G.GOODS_NM) LIKE CONCAT(UPPER(#{goodsNm}),'%')
		</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="supplyCompCd != null and supplyCompCd != ''">
			AND G.SUPPLY_COMP_CD = #{supplyCompCd}
		</if>
		<if test="multiSupplyCompCd != null and multiSupplyCompCd != ''">
			AND G.SUPPLY_COMP_CD IN
			<foreach collection="multiSupplyCompCd" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		<if test="mdId != null and mdId != ''">
			AND EXISTS (
			SELECT BRAND_CD
			FROM TB_BRAND_MD
			WHERE MD_NO = #{mdId}
			AND BRAND_CD = G.BRAND_CD
			)
		</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="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="formalGb != null and formalGb != ''">
			AND G.FORMAL_GB = #{formalGb}
		</if>
		<if test="selfMallYn != null and selfMallYn != ''">
			AND G.SELF_MALL_YN = #{selfMallYn}
		</if>
		<!--<if test="currStockQty != null and currStockQty != ''">
			AND EXISTS (
			SELECT 1 FROM(
			SELECT NVL(SUM(CURR_STOCK_QTY), 0) - NVL(SUM(BASE_STOCK_QTY), 0) AS CURR_STOCK_QTY
			FROM VW_STOCK WHERE GOODS_CD = A.GOODS_CD
			)
			WHERE CURR_STOCK_QTY >= #{currStockQty}
			)
		</if>--> <!-- 자사/입점일때 따로 테이블 찾아야함..일단 패스 -->
		<if test="styleYear != null and styleYear != ''">
			AND G.STYLE_YEAR = #{styleYear}
		</if>
		<if test="multiStyleYear != null and multiStyleYear != ''">
			AND G.STYLE_YEAR 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="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='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="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="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="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 test="dispYn != null and dispYn != ''">
			<choose>
				<when test='dispYn == "Y"'>
					AND EXISTS
					( SELECT 1
					FROM TB_CATE_GOODS
					WHERE GOODS_CD = G.GOODS_CD
					)
				</when>
				<when test='dispYn == "N"'>
					AND NOT EXISTS
					( SELECT 1
					FROM TB_CATE_GOODS
					WHERE GOODS_CD = G.GOODS_CD
					)
				</when>
			</choose>
		</if>
	</sql>

	<!-- 상품별카테고리전시 저장 -->
	<insert id="saveGoodsCategory" parameterType="GoodsCategory">
		/* TsaDisplay.saveGoodsCategory */
		INSERT INTO TB_CATE_GOODS(
		             BRAND_GROUP_NO
		            ,CATE_NO
		            ,GOODS_CD
		            ,DISP_ORD
		            ,REG_NO
		            ,REG_DT
		)
		VALUES (
		        IFNULL(NULLIF(#{brandGroupNo},''),0)
		        ,#{cateCd}
		        ,#{goodsCd}
		        ,(SELECT	IFNULL((MAX(DISP_ORD) + 1), 1) AS DISP_ORD
		            FROM	TB_CATE_GOODS ALIAS_FOR_SUBQUERY
		            WHERE   CATE_NO = #{cateCd}
		            <if test="brandGroupNo != null and brandGroupNo !=''">
		            AND     BRAND_GROUP_NO = #{brandGroupNo}
		            </if>
		        )
		        ,#{regNo}
		        ,NOW()
		    )
		ON DUPLICATE KEY UPDATE
		    REG_NO = #{regNo}
		   ,REG_DT = NOW()
	</insert>

	<!-- 상품별카테고리 리스트 조회 -->
	<select id="getGoodsCategoryViewList" parameterType="String" resultType="GoodsCategory">
		/* TsaDisplay.getGoodsCategoryViewList */
		SELECT CONCAT(FN_GET_CODE_NM('G032', B.CATE_GB ), ' > ',B.FULL_CATE_NM ) AS FULL_CATE_NM
			 , A.CATE_NO AS CATE_CD
			 , B.CATE_TYPE
			 , A.GOODS_CD
			 , B.CATE1_NO AS CATE_CD1
			 , B.CATE2_NO AS CATE_CD2
			 , B.CATE3_NO AS CATE_CD3
			 , B.CATE4_NO AS CATE_CD4
		FROM TB_CATE_GOODS A
		   , TB_CATE_4SRCH B
		WHERE A.CATE_NO = B.LEAF_CATE_NO
		  AND A.GOODS_CD = #{goodsCd}
		ORDER BY B.CATE_TYPE, A.CATE_NO, B.DISP_ORD
	</select>

	<!-- 상품별 전시카테고리 삭제-->
	<delete id="deleteGoodsCategory" parameterType="GoodsCategory">
		/* TsaDisplay.deleteGoodsCategory */
		DELETE
		FROM TB_CATE_GOODS
		WHERE 1=1
		<if test="brandGroupNo != null and brandGroupNo > 0">
		AND BRAND_GROUP_NO = #{brandGroupNo}
		</if>
		AND CATE_NO = #{cateNo}
		<if test="goodsCd != null and goodsCd != ''">
		AND	GOODS_CD = #{goodsCd}
		</if>
	</delete>

	<select id="getCategoryGoodsListCount" parameterType="CategoryGoods" resultType="int">
		/* TsaDisplay.getCategoryGoodsListCount */
		SELECT COUNT(*)
		FROM ( SELECT A.GOODS_CD
					, A.CATE_NO
					, A.DISP_ORD
					, A.REG_NO
					, A.REG_DT
				FROM TB_CATE_GOODS A
				WHERE 1=1
				  AND A.CATE_NO IN (
				      			SELECT LEAF_CATE_NO
								  FROM TB_CATE_4SRCH
								 WHERE 1=1
								<if test='selLvl == "3"'>
									AND CATE1_NO  = #{cate1No}
								</if>
								<if test='selLvl == "4"'>
									AND CATE2_NO  = #{cate2No}
								</if>
								<if test='selLvl == "5"'>
									AND CATE3_NO  = #{cate3No}
								</if>
								<if test='selLvl == "6"'>
									AND CATE4_NO  = #{cate4No}
								</if>
							<if test="contentsLoc != null and contentsLoc !=''">
								AND CONTENTS_LOC = #{contentsLoc}
							</if>
				)
			<if test="brandGroupNo != null and brandGroupNo !=''">
				AND A.BRAND_GROUP_NO = #{brandGroupNo}
			</if>
		) A
		<if test="searchGb == null or searchGb =='BASIC'">
			, TB_GOODS G
		</if>
		<if test="searchGb != null and searchGb =='EXCEL'">
			, (SELECT G.*
			, TMP_DISP_ORD
			FROM TB_GOODS G
			, (SELECT SEARCH_CD, MIN(DISP_ORD) AS TMP_DISP_ORD
			FROM TB_SEARCH_DATA
			WHERE REG_NO = #{regNo}
			GROUP BY SEARCH_CD
			) D
			WHERE G.GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
			OR G.SUPPLY_GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
			) G
		</if>
		WHERE A.GOODS_CD = G.GOODS_CD
		<if test="goodsCd != null and goodsCd !='' and searchGb !='EXCEL'">
			AND G.GOODS_CD = #{goodsCd}
		</if>
	</select>

	<select id="getCategoryGoodsList" parameterType="CategoryGoods" resultType="CategoryGoods">
		/* TsaDisplay.getCategoryGoodsList */
		SELECT Q.*
		FROM (
			SELECT Z.*
			FROM ( SELECT IFNULL((SELECT STOCK_QTY FROM TB_GOODS_STOCK WHERE GOODS_CD = A.GOODS_CD ),0) AS CURR_STOCK_QTY
					, A.*
					FROM ( SELECT G.GOODS_CD
								, G.GOODS_TNM
								, G.GOODS_NM
								, (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' ORDER BY REG_DT DESC LIMIT 1) AS IMG_PATH1
								, A.CATE_NO
								, A.DISP_ORD
								, FN_GET_CODE_NM('G056',G.GOODS_TYPE) AS GOODS_TYPE
								, G.TAG_PRICE
								, G.LIST_PRICE
								, G.CURR_PRICE
								, G.DC_RATE
								, G.GOODS_STAT
								, G.BRAND_CD
								, (SELECT CASE WHEN DISP_NM_LANG = 'EN' THEN BRAND_ENM ELSE BRAND_KNM END AS BRAND_NM FROM TB_BRAND WHERE 1=1 AND G.BRAND_CD = BRAND_CD) AS BRAND_GRP_NM
								, A.REG_NO
								, FN_GET_USER_NM(A.REG_NO) AS REG_NM
								, A.REG_DT
								, G.SUPPLY_GOODS_CD
								, G.STYLE_YEAR
								, FN_GET_CODE_NM('G009',IFNULL(G.FORMAL_GB,'10'))  AS FORMAL_GB
							FROM ( SELECT A.GOODS_CD
										, A.CATE_NO
										, A.DISP_ORD
										, A.REG_NO
										, A.REG_DT
									FROM TB_CATE_GOODS A
									WHERE 1=1
									AND A.CATE_NO IN ( SELECT LEAF_CATE_NO
														FROM TB_CATE_4SRCH
														WHERE 1=1
													<if test='selLvl == "3"'>
														AND CATE1_NO  = #{cate1No}
													</if>
													<if test='selLvl == "4"'>
														AND CATE2_NO  = #{cate2No}
													</if>
													<if test='selLvl == "5"'>
														AND CATE3_NO  = #{cate3No}
													</if>
													<if test='selLvl == "6"'>
														AND CATE4_NO  = #{cate4No}
													</if>
												<if test="contentsLoc != null and contentsLoc !=''">
													AND CONTENTS_LOC = #{contentsLoc}
												</if>
									)
							<if test="brandGroupNo != null and brandGroupNo !=''">
								AND A.BRAND_GROUP_NO = #{brandGroupNo}
							</if>
						) A
					<if test="searchGb == null or searchGb =='BASIC'">
						, TB_GOODS G
					</if>
					<if test="searchGb != null and searchGb =='EXCEL'">
						, (SELECT G.*
						, TMP_DISP_ORD
						FROM TB_GOODS G
						, (SELECT SEARCH_CD, MIN(DISP_ORD) AS TMP_DISP_ORD
						FROM TB_SEARCH_DATA
						WHERE REG_NO = #{regNo}
						GROUP BY SEARCH_CD
						) D
						WHERE G.GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
						OR G.SUPPLY_GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
						) G
					</if>
					WHERE A.GOODS_CD = G.GOODS_CD
					<if test="goodsCd != null and goodsCd !='' and searchGb !='EXCEL'">
						AND G.GOODS_CD = #{goodsCd}
					</if>
					<if test='dispUseGb != null and dispUseGb != "" and dispUseGb == "Y"'>
						ORDER BY A.DISP_ORD ASC, G.UPD_DT DESC, G.CURR_PRICE DESC, G.GOODS_CD ASC
					</if>
			<include refid="getListPagingCondition_sql"/>
	</select>

	<select id="getCategoryGoodsListCount_old" parameterType="CategoryGoods" resultType="int">
		/* TsaDisplay.getCategoryGoodsListCount_old */
		SELECT COUNT(*)
		FROM ( SELECT A.GOODS_CD
					, A.CATE_NO
					, A.DISP_ORD
					, A.REG_NO
					, A.REG_DT
			   FROM TB_CATE_GOODS A
			   WHERE 1=1
				 AND EXISTS ( SELECT 1
							  FROM TB_CATE_4SRCH
							  WHERE 1=1
							  <if test='selLvl == "2"'>
								AND CATE1_NO  = A.CATE_NO
							  </if>
							  <if test='selLvl != "2"'>
								AND CATE1_NO  = #{cate1No}
							    <if test='selLvl == "3"'>
									AND CATE2_NO  = A.CATE_NO
								</if>
							    <if test='selLvl != "3"'>
									AND CATE2_NO  = #{cate2No}
							    	<if test='selLvl == "4"'>
										AND CATE3_NO  = A.CATE_NO
									</if>
							    	<if test='selLvl == "5"'>
										AND CATE3_NO  = #{cate3No}
							    		AND CATE4_NO  = A.CATE_NO
									</if>
								</if>
							  </if>
				   )
		) A
		<if test="searchGb == null or searchGb =='BASIC'">
			, TB_GOODS G
		</if>
		<if test="searchGb != null and searchGb =='EXCEL'">
			, (SELECT G.*
			, TMP_DISP_ORD
			FROM TB_GOODS G
			, (SELECT SEARCH_CD, MIN(DISP_ORD) AS TMP_DISP_ORD
			FROM TB_SEARCH_DATA
			WHERE REG_NO = #{regNo}
			GROUP BY SEARCH_CD
			) D
			WHERE G.GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
			OR G.SUPPLY_GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
			) G
		</if>
		WHERE A.GOODS_CD = G.GOODS_CD
	</select>

	<select id="getCategoryGoodsList_old" parameterType="CategoryGoods" resultType="CategoryGoods">
		/* TsaDisplay.getCategoryGoodsList_old */
		SELECT Z.*
		FROM ( SELECT @rownum := @rownum + 1 AS RNUM
					, A.*
			   FROM ( SELECT G.GOODS_CD
						   , G.GOODS_TNM
						   , G.GOODS_NM
						   , A.CATE_NO
						   , A.DISP_ORD
						   , G.GOODS_TYPE
						   , G.TAG_PRICE
						   , G.LIST_PRICE
						   , G.CURR_PRICE
						   , G.DC_RATE
						   , G.GOODS_STAT
						   , G.BRAND_CD
						   , A.REG_NO
						   , A.REG_DT
						   , G.SUPPLY_GOODS_CD
						   , G.STYLE_YEAR
						   , IFNULL(G.FORMAL_GB,'10') AS FORMAL_GB
					   FROM ( SELECT A.GOODS_CD
								   , A.CATE_NO
								   , A.DISP_ORD
								   , A.REG_NO
								   , A.REG_DT
							   FROM TB_CATE_GOODS A
							   WHERE 1=1
								<if test='selLvl == "3"'>
									AND CATE_NO  = #{cate1No}
								</if>
								<if test='selLvl == "4"'>
									AND CATE_NO  = #{cate2No}
								</if>
								<if test='selLvl == "5"'>
									AND CATE_NO  = #{cate3No}
								</if>
								<if test='selLvl == "6"'>
									AND CATE_NO  = #{cate4No}
								</if>
							   AND EXISTS (   SELECT 1
											   FROM TB_CATE_4SRCH
											   WHERE 1=1
												<if test='selLvl == "3"'>
													AND CATE1_NO  = A.CATE_NO
												</if>
												<if test='selLvl != "3"'>
													AND CATE1_NO  = #{cate1No}
													<if test='selLvl == "4"'>
														AND CATE2_NO  = A.CATE_NO
													</if>
													<if test='selLvl != "4"'>
														AND CATE2_NO  = #{cate2No}
														<if test='selLvl == "5"'>
															AND CATE3_NO  = A.CATE_NO
														</if>
														<if test='selLvl == "6"'>
															AND CATE3_NO  = #{cate3No}
															AND CATE4_NO  = A.CATE_NO
														</if>
													</if>
												</if>
											)
							   ) A
						<if test="searchGb == null or searchGb =='BASIC'">
							, TB_GOODS G
						</if>
						<if test="searchGb != null and searchGb =='EXCEL'">
							, (SELECT G.*
							, TMP_DISP_ORD
							FROM TB_GOODS G
							, (SELECT SEARCH_CD, MIN(DISP_ORD) AS TMP_DISP_ORD
							FROM TB_SEARCH_DATA
							WHERE REG_NO = #{regNo}
							GROUP BY SEARCH_CD
							) D
							WHERE G.GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
							OR G.SUPPLY_GOODS_CD LIKE CONCAT(D.SEARCH_CD,'%')
							) G
						</if>
					   JOIN ( SELECT @rownum := 0) R
					   WHERE A.GOODS_CD = G.GOODS_CD
					   ORDER BY A.DISP_ORD ASC, G.UPD_DT DESC, G.CURR_PRICE DESC, G.GOODS_CD ASC
			<include refid="getListPagingCondition_sql"/>
	</select>

	<!-- 전시 상품 순서변경 전시초기화 -->
	<insert id="updateCategoryGoodsDispOrdInit" parameterType="CategoryGoods">
		/* TsaDisplay.updateCategoryGoodsDispOrdInit */
		INSERT INTO TB_CATE_GOODS
		(CATE_NO , GOODS_CD, DISP_ORD, REG_NO, REG_DT)
		(
		    SELECT *
		    FROM 	(
		        SELECT	CATE_NO
		               ,GOODS_CD
		               ,@ROWNUM:=@ROWNUM+1 AS DISP_ORD
		               ,REG_NO
		               ,REG_DT
		        FROM TB_CATE_GOODS
		       WHERE 1=1
		       AND CATE_NO = #{cateNo}
		       <if test="brandGroupNo != null and brandGroupNo > 0">
		       AND BRAND_GROUP_NO = #{brandGroupNo}
		       </if>
		         AND	(@ROWNUM:=0)=0
		       ORDER BY REG_DT
		    ) B
		)
		ON DUPLICATE KEY UPDATE
		DISP_ORD = B.DISP_ORD
	</insert>

	<!-- 전시 상품 순서변경 최상단/하단 이동 -->
	<update id="updateCategoryGoodsDispOrdUpdown" parameterType="CategoryGoods">
		/* TsaDisplay.updateCategoryGoodsDispOrdUpdown */
		UPDATE TB_CATE_GOODS A
		<if test="changeGb != null and changeGb =='up'">
			SET A.DISP_ORD = DISP_ORD + 1
		</if>
		<if test="changeGb != null and changeGb =='down'">
			SET A.DISP_ORD = DISP_ORD - 1
		</if>
		<if test="changeGb != null and changeGb =='min'">
			SET A.DISP_ORD = (SELECT DISP_ORD FROM (SELECT MIN(DISP_ORD) AS DISP_ORD FROM TB_CATE_GOODS B WHERE CATE_NO = #{cateNo}) TMP)
		</if>
		<if test="changeGb != null and changeGb =='max'">
			SET A.DISP_ORD = (SELECT DISP_ORD FROM (SELECT MAX(DISP_ORD) AS DISP_ORD FROM TB_CATE_GOODS B WHERE CATE_NO = #{cateNo}) TMP)
		</if>
		WHERE A.CATE_NO = #{cateNo}
		<choose>
			<when test="changeGb != null and changeGb =='up' or changeGb =='down'">
				AND A.GOODS_CD != #{goodsCd}
			</when>
			<otherwise>
				AND A.GOODS_CD = #{goodsCd}
			</otherwise>
		</choose>
		<if test="changeGb != null and changeGb =='up'">
			<![CDATA[
		    AND A.DISP_ORD <= #{dispOrd}
			]]>
		</if>
		<if test="changeGb != null and changeGb =='down'">
			<![CDATA[
		    AND A.DISP_ORD >= #{dispOrd}
		    ]]>
		</if>
	</update>

	<!-- 전시 상품 순서변경 저장 -->
	<update id="updateCategoryGoodsDispOrd" parameterType="CategoryGoods">
		/* TsaDisplay.updateCategoryGoodsDispOrd */
		UPDATE	TB_CATE_GOODS
			SET DISP_ORD = #{dispOrd}
		WHERE	CATE_NO = #{cateNo}
		AND 	GOODS_CD = #{goodsCd}
	</update>

	<!-- 메인전시관리 카테고리 리스트 -->
	<select id="getMainCategoryList" parameterType="Category" resultType="Category">
		/* TsaDisplay.getMainCategoryList */
		SELECT	CATE_NO
			 ,CATE_NM
			 ,DISP_ORD
			 ,LEAF_YN
			 ,CLOC_PREFIX
		FROM(
		<choose>
			<when test="clsLvl==1">
				SELECT	CATE1_NO	AS CATE_NO
						,CATE1_NM	AS CATE_NM
						,MIN(DISP_ORD) AS DISP_ORD
						,MIN(CASE WHEN LEAF_CATE_NO = CATE1_NO THEN 'Y' ELSE 'N' END) AS LEAF_YN
						,CLOC_PREFIX
				FROM	VW_MAIN_CATEGORY
				GROUP BY CATE1_NO, CATE1_NM, CLOC_PREFIX
			</when>
			<when test="clsLvl==2">
				SELECT	CATE2_NO	AS CATE_NO
						,CATE2_NM	AS CATE_NM
						,MIN(DISP_ORD) AS DISP_ORD
						,MIN(CASE WHEN LEAF_CATE_NO = CATE2_NO THEN 'Y' ELSE 'N' END) AS LEAF_YN
						,CLOC_PREFIX
				FROM	VW_MAIN_CATEGORY
				WHERE	CATE1_NO = #{cateNo}
				GROUP BY CATE2_NO, CATE2_NM, CLOC_PREFIX
			</when>
		</choose>
		) A
		ORDER BY DISP_ORD, CATE_NO
	</select>

	<!-- 메인전시관리 레이아웃 -->
	<select id="getMainLayoutList" parameterType="MainDisplay" resultType="MainDisplay">
		/* TsaDisplay.getMainLayoutList */
		SELECT	CATE_NO
				,CONTENTS_LOC
		     	,BRAND_GROUP_NO
				,DISP_ORD
				,CONTENTS_YN
				,COL_CNT
				,COL_NO
		        ,IFNULL(CONTENTS_TITLE,'') as CONTENTS_TITLE
				,ORG_FILE_NM
				,SYS_FILE_NM
		FROM	TB_MAIN_LAYOUT
		WHERE	CATE_NO = #{cateNo}
		<if test='cateNo == "1711" or cateNo == "1721"'>
			<choose>
				<when test='brandGroupNo != null and brandGroupNo != ""'>
					AND BRAND_GROUP_NO = #{brandGroupNo}
				</when>
		  		<otherwise>
					AND BRAND_GROUP_NO = '0'
				</otherwise>
			</choose>
		</if>
		ORDER BY DISP_ORD, COL_NO
	</select>

	<!-- 메인전시관리 삭제 -->
	<delete id="deleteMainLayout" parameterType="MainDisplay">
		/* TsaDisplay.deleteMainLayout */
		DELETE FROM TB_MAIN_LAYOUT
		WHERE CATE_NO = #{cateNo}
		<if test='brandGroupNo!=null and brandGroupNo!=""'>
			AND 	BRAND_GROUP_NO = #{brandGroupNo}
		</if>
		<if test='contentsLoc!=null and contentsLoc!=""'>
			AND SUBSTR(CONTENTS_LOC, 0, 4) = SUBSTR(#{contentsLoc}, 0, 4)
		</if>
	</delete>

	<!-- 메인전시관리 저장 -->
	<insert id="saveMainLayout" parameterType="MainDisplay">
		/* TsaDisplay.saveMainLayout */
		INSERT INTO TB_MAIN_LAYOUT
		(
			CATE_NO
			,CONTENTS_LOC
			,BRAND_GROUP_NO
			,DISP_ORD
			,CONTENTS_YN
			,COL_CNT
			,COL_NO
			,CONTENTS_TITLE
			,ORG_FILE_NM
			,SYS_FILE_NM
			,REG_NO
			,REG_DT
			,UPD_NO
			,UPD_DT
		)
		VALUES
		(
		 	#{cateNo}
		 	,#{contentsLoc}
		 	,IFNULL(NULLIF(#{brandGroupNo},''),0)
		 	,#{dispOrd}
			,#{contentsYn}
			,#{colCnt}
			,#{colNo}
			,#{contentsTitle}
			,#{orgFileNm}
			,#{sysFileNm}
			,#{regNo}
			,NOW()
			,#{updNo}
			,NOW()
		)
	</insert>

	<!-- 컨텐츠 설명 조회 -->
	<select id="getContentsDesc" parameterType="String" resultType="String">
		/* TsaDisplay.getContentsDesc */
		SELECT	IFNULL(CD_DESC, '') AS CONTENTS_DESC
		FROM	TB_COMMON_CODE
		WHERE	CD = #{contentsLoc}
	</select>

	<!-- 컨텐츠 미리보기 리스트 조회 -->
	<select id="getContentsPreviewList" parameterType="Contents" resultType="Contents">
		/* TsaDisplay.getContentsPreviewList */
		SELECT A.PRE_CONTENTS_SQ
		     , A.CATE_NO
		     , A.CONTENTS_LOC
		     , A.CONTENTS_TYPE
		     , A.DISP_STDT
		     , A.DISP_EDDT
		     , A.DISP_ORD
		     , A.IMG_PATH1
		     , A.IMG_PATH2
		     , A.IMG_PATH3
		     , A.IMG_PATH4
		     , A.IMG_PATH5
		     , A.IMG_PATH6
		     , A.IMG_PATH7
		     , A.IMG_PATH8
		     , A.STR_VAR1
		     , A.STR_VAR2
		     , A.STR_VAR3
		     , A.STR_VAR4
		     , A.STR_VAR5
		     , A.STR_VAR6
		     , A.STR_VAR7
		     , A.STR_VAR8
		     , A.STR_TITLE1
		     , A.STR_TITLE2
		     , A.STR_TITLE3
		     , A.STR_TITLE4
		     , A.SUB_TEXT1
		     , A.SUB_TEXT2
		     , A.SUB_TEXT3
		     , A.SUB_TEXT4
		     , A.HTML
		     , A.BRAND_GROUP_NO
		     , A.BRAND_GROUP_IMG
		     , A.MD_TITLE
		     , A.USE_YN
		     , A.REG_NO
		     , A.REG_DT
		     , A.UPD_NO
		     , A.UPD_DT
		FROM TB_CONTENTS_PREVIEW A
		WHERE A.CATE_NO = #{cateNo}
		  AND A.CONTENTS_LOC = #{contentsLoc}
		<if test='cateNo == "1711" or cateNo == "1721"'>
		    <if test='brandGroupNo != null and brandGroupNo != ""'>
		  AND A.BRAND_GROUP_NO = #{brandGroupNo}
		    </if>
		</if>
		<if test='contentsLoc != null and contentsLoc != ""'>
		  <if test='contentsLoc != "SCM002" and contentsLoc != "SCM003" and contentsLoc != "SOM003"'>
		  AND A.USE_YN = 'Y'
		  </if>
		</if>
		ORDER BY A.DISP_ORD
	</select>

	<!-- 메인전시 컨텐츠 미리보기 삭제 -->
	<delete id="deleteContentsPreview" parameterType="Contents">
		/* TsaDisplay.deleteContentsPreview */
		DELETE FROM TB_CONTENTS_PREVIEW
		WHERE 	CATE_NO = #{cateNo}
		AND 	CONTENTS_LOC = #{contentsLoc}
		<if test="cateNo != null and cateNo !='' and cateNo == '1711'">
			<if test="brandGroupNo != null and brandGroupNo !=''">
				AND BRAND_GROUP_NO = #{brandGroupNo}
			</if>
		</if>
	</delete>

	<!-- 메인전시 컨텐츠 미리보기 저장 -->
	<insert id="saveContentsPreview" parameterType="Contents" keyProperty="preContentsSq">
		/* TsaDisplay.saveContentsPreview */
		INSERT INTO TB_CONTENTS_PREVIEW
		(
			PRE_CONTENTS_SQ
			, CATE_NO
			, CONTENTS_LOC
			, CONTENTS_TYPE
			, DISP_STDT
			, DISP_EDDT
			, DISP_ORD
			, IMG_PATH1
			, IMG_PATH2
			, IMG_PATH3
			, IMG_PATH4
			, IMG_PATH5
			, IMG_PATH6
			, IMG_PATH7
			, IMG_PATH8
			, STR_VAR1
			, STR_VAR2
			, STR_VAR3
			, STR_VAR4
			, STR_VAR5
			, STR_VAR6
			, STR_VAR7
			, STR_VAR8
			, STR_TITLE1
			, STR_TITLE2
			, STR_TITLE3
			, STR_TITLE4
			, SUB_TEXT1
			, SUB_TEXT2
			, SUB_TEXT3
			, SUB_TEXT4
			, HTML
			, BRAND_GROUP_NO
			, BRAND_GROUP_IMG
			, MD_TITLE
			, USE_YN
			, REG_NO
			, REG_DT
			, UPD_NO
			, UPD_DT
		)
		VALUES
		(
			#{preContentsSq}
			,#{cateNo}
			,#{contentsLoc}
			,#{contentsType}
			,DATE_FORMAT(#{dispStdt}, '%Y-%m-%d %H:%i:%S')
			,DATE_FORMAT(#{dispEddt}, '%Y-%m-%d %H:%i:%S')
			<choose>
				<when test="dispOrd != null and dispOrd != ''">
			,#{dispOrd}
				</when>
				<otherwise>
			,(SELECT DISP_ORD FROM (SELECT IFNULL((MAX(DISP_ORD) + 1), 1) AS DISP_ORD FROM	TB_CONTENTS_PREVIEW	WHERE CATE_NO = #{cateNo} AND CONTENTS_LOC = #{contentsLoc}
			<if test="contentsLoc != null and contentsLoc != '' and contentsLoc != 'SMM007'">
				<if test="brandGroupNo != null and brandGroupNo !=''">
					AND BRAND_GROUP_NO = #{brandGroupNo}
				</if>
			</if>
			) TMP)
				</otherwise>
			</choose>
			,#{imgPath1}
			,#{imgPath2}
			,#{imgPath3}
			,#{imgPath4}
			,#{imgPath5}
			,#{imgPath6}
			,#{imgPath7}
			,#{imgPath8}
			,#{strVar1}
			,#{strVar2}
			,#{strVar3}
			,#{strVar4}
			,#{strVar5}
			,#{strVar6}
			,#{strVar7}
			,#{strVar8}
			,#{strTitle1}
			,#{strTitle2}
			,#{strTitle3}
			,#{strTitle4}
			,#{subText1}
			,#{subText2}
			,#{subText3}
			,#{subText4}
			,#{html}
			,CASE WHEN #{brandGroupNo} = '' THEN NULL ELSE #{brandGroupNo} END
			,#{brandGroupImg}
			,#{mdTitle}
			,#{useYn}
			,#{regNo}
			,NOW()
			,#{updNo}
			,NOW()
		)
	</insert>

	<!-- 메인전시 컨텐츠 삭제 -->
	<delete id="deleteContents" parameterType="Contents">
		/* TsaDisplay.deleteContents */
		DELETE FROM TB_CONTENTS
		WHERE 	CATE_NO = #{cateNo}
		  AND 	CONTENTS_LOC = #{contentsLoc}
		<if test="brandGroupNo != null and brandGroupNo !=''">
			AND BRAND_GROUP_NO = #{brandGroupNo}
		</if>
	</delete>

	<!-- 메인전시 컨텐츠 저장 -->
	<insert id="saveContents" parameterType="Contents">
		/* TsaDisplay.saveContents */
		<selectKey keyProperty="contentsSq" resultType="int" order="AFTER">
			SELECT LAST_INSERT_ID()
		</selectKey>
		INSERT INTO TB_CONTENTS
		(
			CATE_NO
			, CONTENTS_LOC
			, CONTENTS_TYPE
			, DISP_STDT
			, DISP_EDDT
			, DISP_ORD
			, IMG_PATH1
			, IMG_PATH2
			, IMG_PATH3
			, IMG_PATH4
			, IMG_PATH5
			, IMG_PATH6
			, IMG_PATH7
			, IMG_PATH8
			, STR_VAR1
			, STR_VAR2
			, STR_VAR3
			, STR_VAR4
			, STR_VAR5
			, STR_VAR6
			, STR_VAR7
			, STR_VAR8
			, STR_TITLE1
			, STR_TITLE2
			, STR_TITLE3
			, STR_TITLE4
			, SUB_TEXT1
			, SUB_TEXT2
			, SUB_TEXT3
			, SUB_TEXT4
			, HTML
			, BRAND_GROUP_NO
			, BRAND_GROUP_IMG
			, MD_TITLE
			, USE_YN
			, REG_NO
			, REG_DT
			, UPD_NO
			, UPD_DT
		)
		SELECT
		     CATE_NO
			 , CONTENTS_LOC
			 , CONTENTS_TYPE
			 , DISP_STDT
			 , DISP_EDDT
			 , DISP_ORD
			 , IMG_PATH1
			 , IMG_PATH2
			 , IMG_PATH3
			 , IMG_PATH4
			 , IMG_PATH5
			 , IMG_PATH6
			 , IMG_PATH7
			 , IMG_PATH8
			 , STR_VAR1
			 , STR_VAR2
			 , STR_VAR3
			 , STR_VAR4
			 , STR_VAR5
			 , STR_VAR6
			 , STR_VAR7
			 , STR_VAR8
			 , STR_TITLE1
			 , STR_TITLE2
			 , STR_TITLE3
			 , STR_TITLE4
		 	 , SUB_TEXT1
			 , SUB_TEXT2
			 , SUB_TEXT3
			 , SUB_TEXT4
		     , HTML
			 , BRAND_GROUP_NO
			 , BRAND_GROUP_IMG
			 , MD_TITLE
			 , USE_YN
			 , #{regNo}
			 , NOW()
			 , #{updNo}
			 , NOW()
		FROM	TB_CONTENTS_PREVIEW
		WHERE	CONTENTS_LOC = #{contentsLoc}
		AND 	CATE_NO = #{cateNo}
		<if test="brandGroupNo != null and brandGroupNo !=''">
			AND BRAND_GROUP_NO = #{brandGroupNo}
		</if>
	</insert>

	<!-- CATEGORY4SRCH 조회 -->
	<select id="getCategory4srch" parameterType="Category" resultType="Category">
		/* TsaDisplay.getCategory4srch */
		SELECT	SITE_CD
				,CATE_GB
				,LEAF_CATE_NO
				,CATE_TYPE
				,CATE1_NO
				,CATE1_NM
				,CATE2_NO
				,CATE2_NM
				,CATE3_NO
				,CATE3_NM
				,CATE4_NO
				,CATE4_NM
				,CATE5_NO
				,CATE5_NM
				,FULL_CATE_NO
				,FULL_CATE_NM
				,DISP_ORD
				,FORMAL_GB
				,CONTENTS_LOC
		FROM	TB_CATE_4SRCH
		WHERE	1=1
		<if test='cate1No != null and cate1No != ""'>
			AND CATE1_NO = #{cate1No}
		</if>
		<if test='cateGb != null and cateGb != ""'>
			AND CATE_GB = #{cateGb}
		</if>
		<if test='contentsLoc != null and contentsLoc != ""'>
			AND CONTENTS_LOC = #{contentsLoc}
		</if>
	</select>

	<delete id="deleteContentsGoodsTemp" parameterType="Contents">
		/* TsaDisplay.deleteContentsGoodsTemp */
		DELETE FROM TB_CONTENTS_GOODS
		WHERE 1=1
		<if test="cateNo != null and cateNo != ''">
		AND CATE_NO = #{cateNo}
		</if>
		AND CONTENTS_LOC = #{contentsLoc}
		<if test="brandGroupNo != null and brandGroupNo != ''">
		AND BRAND_GROUP_NO = #{brandGroupNo}
		</if>
		AND CONTENTS_SQ = #{contentsSq}
	</delete>

	<insert id="saveContentsGoodsTemp" parameterType="Contents">
		/* TsaDisplay.saveContentsGoodsTemp */
		INSERT INTO TB_CONTENTS_GOODS
		(
		    BRAND_GROUP_NO
		    ,CATE_NO
		    ,LEAF_CATE_NO
		    ,CONTENTS_LOC
		    ,PRE_CONTENTS_SQ
		    ,CONTENTS_SQ
		    ,GOODS_CD
		    ,GOODS_NM
		    ,IMG_PATH1
		    ,DISP_ORD
		    ,REG_NO
		    ,REG_DT
		)
		VALUES
		(
		     IFNULL(NULLIF(#{brandGroupNo},''),0)
		    ,#{cateNo}
		    ,IFNULL((SELECT LEAF_CATE_NO FROM (SELECT	LEAF_CATE_NO
		                                FROM	TB_CATE_4SRCH
		                                WHERE	1=1
		                                <if test="cateNo != null and cateNo != ''">
		                                AND     CATE1_NO = #{cateNo}
		                                </if>
		                                AND		CONTENTS_LOC = #{contentsLoc}) TMP),0)
		    ,#{contentsLoc}
		    ,#{preContentsSq}
		    ,#{contentsSq}
		    ,#{goodsCd}
		    ,IFNULL(#{goodsNm},'')
		    ,IFNULL(#{imgPath1},'')
		    <choose>
				<when test="dispOrd != null and dispOrd != ''">
			,#{dispOrd}
				</when>
				<otherwise>
		    ,(SELECT DISP_ORD FROM (SELECT IFNULL((MAX(DISP_ORD) + 1), 1) AS DISP_ORD
		                            FROM	TB_CONTENTS_GOODS
		                            WHERE 1=1
		                            <if test="cateNo != null and cateNo != ''">
		                            AND CATE_NO = #{cateNo}
		                            </if>
		                            AND CONTENTS_LOC = #{contentsLoc}
		                            AND CONTENTS_SQ = #{contentsSq}
		                            <if test="brandGroupNo != null and brandGroupNo != ''">
		                            AND BRAND_GROUP_NO = #{brandGroupNo}
		                            </if>
		                        ) TMP)
				</otherwise>
			</choose>
		    ,#{regNo}
		    ,NOW()
		)
	</insert>

	<!-- 메인전시 컨텐츠 상품 삭제 -->
	<delete id="deleteContentsGoods" parameterType="Contents">
		/* TsaDisplay.deleteContentsGoods */
		DELETE	FROM TB_CATE_GOODS
		WHERE	CATE_NO = (SELECT LEAF_CATE_NO FROM (SELECT LEAF_CATE_NO
		                                               FROM TB_CATE_4SRCH
		                                              WHERE 1=1
		                                              <if test="cateNo != null and cateNo != ''">
		                                              AND CATE1_NO = #{cateNo}
		                                              </if>
		                                              AND CONTENTS_LOC = #{contentsLoc}) TMP)
	</delete>

	<!-- 메인전시 컨텐츠 상품 저장 -->
	<insert id="saveContentsGoods" parameterType="Contents">
		/* TsaDisplay.saveContentsGoods */
		INSERT INTO TB_CATE_GOODS
		( BRAND_GROUP_NO
		, CATE_NO
		, GOODS_CD
		, DISP_ORD
		, REG_NO
		, REG_DT)
		    (
		        SELECT *
		        FROM (SELECT BRAND_GROUP_NO
		                   , LEAF_CATE_NO
		                   , GOODS_CD
		                   , DISP_ORD
		                   , #{regNo} AS REG_NO
		                   , NOW()
		              FROM TB_CONTENTS_GOODS
		              WHERE 1=1
		              <if test="cateNo != null and cateNo != ''">
		              AND CATE_NO = #{cateNo}
		              </if>
		              AND CONTENTS_LOC = #{contentsLoc}
		            ) B
		    ) ON DUPLICATE KEY
		UPDATE
		    GOODS_CD = B.GOODS_CD
		    , DISP_ORD = B.DISP_ORD
		    , REG_NO = B.REG_NO
		    , REG_DT = NOW()
	</insert>

	<!-- 메인전시 컨텐츠 탭영역 미리보기 리스트 -->
	<select id="getContentsTabPreviewList" parameterType="Contents" resultType="Contents">
		/* TsaDisplay.getContentsTabPreviewList */
		SELECT 	STR_TITLE3
		      , DISP_STDT
		      , DISP_EDDT
		      , MAX(GROUP1_TITLE) AS GROUP1_TITLE
		      , MAX(GROUP1_BRAND_CD) AS GROUP1_BRAND_CD
		      , MAX(GROUP1_BRAND_NM) AS GROUP1_BRAND_NM
		      , MAX(GROUP1_BRAND_IMG) AS GROUP1_BRAND_IMG
		      , MAX(GROUP2_TITLE) AS GROUP2_TITLE
		      , MAX(GROUP2_BRAND_CD) AS GROUP2_BRAND_CD
		      , MAX(GROUP2_BRAND_NM) AS GROUP2_BRAND_NM
		      , MAX(GROUP2_BRAND_IMG) AS GROUP2_BRAND_IMG
		      , MAX(GROUP3_TITLE) AS GROUP3_TITLE
		      , MAX(GROUP3_BRAND_CD) AS GROUP3_BRAND_CD
		      , MAX(GROUP3_BRAND_NM) AS GROUP3_BRAND_NM
		      , MAX(GROUP3_BRAND_IMG) AS GROUP3_BRAND_IMG
		FROM (
		        SELECT  STR_TITLE3
		              , DISP_STDT
		              , DISP_EDDT
		              , CASE WHEN STR_TITLE2 = 1 THEN TITLE END AS GROUP1_TITLE
		              , CASE WHEN STR_TITLE2 = 1 THEN GROUP_CONCAT(CASE WHEN BRAND_GROUP_NO IS NULL THEN '' ELSE BRAND_GROUP_NO END ORDER BY DISP_ORD SEPARATOR ',') END GROUP1_BRAND_CD
		              , CASE WHEN STR_TITLE2 = 1 THEN GROUP_CONCAT(CASE WHEN BRAND_GROUP_NM IS NULL THEN '' ELSE BRAND_GROUP_NM END ORDER BY DISP_ORD SEPARATOR ',') END GROUP1_BRAND_NM
		              , CASE WHEN STR_TITLE2 = 1 THEN GROUP_CONCAT(CASE WHEN LOGO_FILE_NM IS NULL THEN '' ELSE LOGO_FILE_NM END ORDER BY DISP_ORD SEPARATOR ',') END GROUP1_BRAND_IMG
		              , CASE WHEN STR_TITLE2 = 2 THEN TITLE END AS GROUP2_TITLE
		              , CASE WHEN STR_TITLE2 = 2 THEN GROUP_CONCAT(CASE WHEN BRAND_GROUP_NO IS NULL THEN '' ELSE BRAND_GROUP_NO END ORDER BY DISP_ORD SEPARATOR ',') END GROUP2_BRAND_CD
		              , CASE WHEN STR_TITLE2 = 2 THEN GROUP_CONCAT(CASE WHEN BRAND_GROUP_NM IS NULL THEN '' ELSE BRAND_GROUP_NM END ORDER BY DISP_ORD SEPARATOR ',') END GROUP2_BRAND_NM
		              , CASE WHEN STR_TITLE2 = 2 THEN GROUP_CONCAT(CASE WHEN LOGO_FILE_NM IS NULL THEN '' ELSE LOGO_FILE_NM END ORDER BY DISP_ORD SEPARATOR ',') END GROUP2_BRAND_IMG
		              , CASE WHEN STR_TITLE2 = 3 THEN TITLE END AS GROUP3_TITLE
		              , CASE WHEN STR_TITLE2 = 3 THEN GROUP_CONCAT(CASE WHEN BRAND_GROUP_NO IS NULL THEN '' ELSE BRAND_GROUP_NO END ORDER BY DISP_ORD SEPARATOR ',') END GROUP3_BRAND_CD
		              , CASE WHEN STR_TITLE2 = 3 THEN GROUP_CONCAT(CASE WHEN BRAND_GROUP_NM IS NULL THEN '' ELSE BRAND_GROUP_NM END ORDER BY DISP_ORD SEPARATOR ',') END GROUP3_BRAND_NM
		              , CASE WHEN STR_TITLE2 = 3 THEN GROUP_CONCAT(CASE WHEN LOGO_FILE_NM IS NULL THEN '' ELSE LOGO_FILE_NM END ORDER BY DISP_ORD SEPARATOR ',') END GROUP3_BRAND_IMG
		        FROM (
		            SELECT
		                  B.STR_TITLE3
		                  , B.STR_TITLE2
		                  , B.DISP_STDT
		                  , B.DISP_EDDT
		                  , B.STR_TITLE1     AS TITLE
		                  , A.BRAND_GROUP_NO
		                  , CASE WHEN A.DISP_NM_LANG = 'EN' THEN A.BRAND_GROUP_ENM
		                  ELSE A.BRAND_GROUP_KNM
		                  END              AS BRAND_GROUP_NM
		                  , A.LOGO_FILE_NM
		                  , B.DISP_ORD
		            FROM   TB_BRAND_GROUP A
		                 , TB_CONTENTS_PREVIEW B
		            WHERE  A.BRAND_GROUP_NO = B.STR_VAR1
		            AND    A.USE_YN = 'Y'
		            AND	   B.CATE_NO = #{cateNo}
		            AND    B.CONTENTS_LOC = #{contentsLoc}
		            AND    B.DISP_STDT <![CDATA[<=]]> NOW()
		            AND    B.DISP_EDDT <![CDATA[>=]]> NOW()
		            ORDER  BY B.DISP_ORD, B.CONTENTS_SQ
		        ) T
		    GROUP BY  T.STR_TITLE3, T.DISP_STDT, T.DISP_EDDT , T.STR_TITLE2, T.TITLE
		) Z
		GROUP BY  Z.STR_TITLE3 , Z.DISP_STDT , Z.DISP_EDDT
	</select>

	<!-- 메인전시 GnbTab 미리보기 삭제 -->
	<delete id="deleteGnbTabPreview" parameterType="GnbTab">
		/* TsaDisplay.deleteGnbTabPreview */
		DELETE FROM  TB_GNB_TAB_PREVIEW
		WHERE 	GTAB_GB = #{gtabGb}
		<if test='gtabGb != null and gtabGb !="" and gtabGb=="B"'>
			<if test='brandGroupNo != null and brandGroupNo !=""'>
				AND	BRAND_GROUP_NO = #{brandGroupNo}
			</if>
		</if>
	</delete>

	<!-- 메인전시 GnbTab 미리보기 저장 -->
	<insert id="saveGnbTabPreview" parameterType="GnbTab" keyProperty="preGtabSq">
		/* TsaDisplay.saveGnbTabPreview */
		INSERT INTO TB_GNB_TAB_PREVIEW
		(
		     PRE_GTAB_SQ
		    , GTAB_GB
		    , GTAB_NM
		    , CONTENTS_TYPE
		    , CATE1_NO
		    , BRAND_GROUP_NO
		    , LINK_URL
		    , DISP_STDT
		    , DISP_EDDT
		    , DISP_ORD
		    , USE_YN
		    , REG_NO
		    , REG_DT
		    , UPD_NO
		    , UPD_DT
		)
		VALUES
		(
		    #{preGtabSq}
		    ,#{gtabGb}
		    ,#{gtabNm}
		    ,#{contentsType}
		    ,#{cate1No}
		    ,#{brandGroupNo}
		    ,#{linkUrl}
		    ,DATE_FORMAT(#{dispStdt}, '%Y-%m-%d %H:%i:%S')
		    ,DATE_FORMAT(#{dispEddt}, '%Y-%m-%d %H:%i:%S')
		    ,(SELECT DISP_ORD FROM (SELECT IFNULL((MAX(DISP_ORD) + 1), 1) AS DISP_ORD
		        FROM	TB_GNB_TAB_PREVIEW
		       WHERE GTAB_GB = #{gtabGb}
		         AND USE_YN = 'Y'
		        <if test="brandGroupNo != null and brandGroupNo !=''">
		         AND BRAND_GROUP_NO = #{brandGroupNo}
		        </if>
		        ) TMP)
		    ,#{useYn}
		    ,#{regNo}
		    ,NOW()
		    ,#{updNo}
		    ,NOW()
		)
	</insert>

	<!-- 메인전시 컨텐츠 탭영역 카테고리 미리보기 리스트 -->
	<select id="getGnbtabPreviewList" parameterType="GnbTab" resultType="GnbTab">
		/* TsaDisplay.getGnbtabPreviewList */
		SELECT  PRE_GTAB_SQ
				, GTAB_GB
				, GTAB_NM
				, CONTENTS_TYPE
				, CATE1_NO
				, BRAND_GROUP_NO
				, LINK_URL
				, DISP_STDT
				, DISP_EDDT
				, DISP_ORD
		FROM 	TB_GNB_TAB_PREVIEW
		WHERE	GTAB_GB = #{gtabGb}
		  <if test='gtabGb != null and gtabGb !="" and gtabGb=="B"'>
			  <if test='brandGroupNo != null and brandGroupNo !=""'>
				  AND	BRAND_GROUP_NO = #{brandGroupNo}
			  </if>
		  </if>
		ORDER BY DISP_ORD
		;
	</select>

	<!-- 메인전시 컨텐츠 카테고리 탭영역 삭제 -->
	<delete id="deleteGnbTabContents" parameterType="GnbTab">
		/* TsaDisplay.deleteGnbTabContents */
		DELETE FROM TB_GNB_TAB
		WHERE 	GTAB_GB = #{gtabGb}
		<if test='gtabGb != null and gtabGb !="" and gtabGb=="B"'>
			<if test='brandGroupNo != null and brandGroupNo !=""'>
				AND	BRAND_GROUP_NO = #{brandGroupNo}
			</if>
		</if>
	</delete>

	<!-- 메인전시 컨텐츠 카테고리 탭영역 저장 -->
	<insert id="saveGnbTabContents" parameterType="GnbTab">
		/* TsaDisplay.saveGnbTabContents */
		<selectKey keyProperty="gtabSq" resultType="int" order="AFTER">
			SELECT LAST_INSERT_ID()
		</selectKey>
		INSERT INTO TB_GNB_TAB
		(
			PRE_GTAB_SQ
		 	, GTAB_GB
			, GTAB_NM
			, CONTENTS_TYPE
			, CATE1_NO
			, BRAND_GROUP_NO
			, LINK_URL
			, DISP_STDT
			, DISP_EDDT
			, DISP_ORD
			, USE_YN
			, REG_NO
			, REG_DT
			, UPD_NO
			, UPD_DT
		)
		SELECT
			PRE_GTAB_SQ
			, GTAB_GB
			, GTAB_NM
			, CONTENTS_TYPE
			, CATE1_NO
			, BRAND_GROUP_NO
			, LINK_URL
			, DISP_STDT
			, DISP_EDDT
			, DISP_ORD
			, USE_YN
			, #{regNo}
			, NOW()
			, #{updNo}
			, NOW()
		FROM	TB_GNB_TAB_PREVIEW
		WHERE	GTAB_GB = #{gtabGb}
		<if test='gtabGb != null and gtabGb !="" and gtabGb=="B"'>
			<if test='brandGroupNo != null and brandGroupNo !=""'>
				AND	BRAND_GROUP_NO = #{brandGroupNo}
			</if>
		</if>
	</insert>

	<select id="getContentsForGoodsList" parameterType="Contents" resultType="Goods">
		/* TsaDisplay.getContentsForGoodsList */
		SELECT CG.GOODS_CD
		     , G.GOODS_NM
		     , GI.SYS_IMG_NM
		     , CG.DISP_ORD AS CONTENTS_DISP_ORD
		FROM TB_CONTENTS_GOODS CG
		JOIN TB_GOODS G ON CG.GOODS_CD = G.GOODS_CD
		LEFT OUTER JOIN TB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD AND G.MAIN_COLOR_CD = COLOR_CD AND GI.DEFAULT_IMG_YN = 'Y'
		WHERE 1=1
		<if test="contentsLoc != 'SMM012'">
		AND CG.CATE_NO = #{cateNo}
		</if>
		AND CG.CONTENTS_LOC = #{contentsLoc}
		<if test="brandGroupNo != null and brandGroupNo !=''">
		AND  CG.BRAND_GROUP_NO = #{brandGroupNo}
		</if>
		AND CG.CONTENTS_SQ = #{dispOrd}
		ORDER BY CG.BRAND_GROUP_NO , CG.CATE_NO ,CG.LEAF_CATE_NO ,CG.CONTENTS_LOC ,CG.PRE_CONTENTS_SQ , CG.CONTENTS_SQ , CG.DISP_ORD
	</select>

	<!-- 검색키워드  등록 -->
	<insert id="createSearchKeyword" parameterType="SearchKeyword" keyProperty="keywordSq">
		/* TsaDisplay.createSearchKeyword */
		INSERT INTO TB_SEARCH_KEYWORD
		(
		  KEYWORD_SQ
		  ,KEYWORD
		  ,LINK_URL
		  ,DISP_STDT
		  ,DISP_EDDT
		  ,REG_NO
		  ,REG_DT
		  ,UPD_NO
		  ,UPD_DT
		) VALUES (
		  #{keywordSq}
		  , #{keyword}
		  , #{linkUrl}
		  , DATE_FORMAT(CONCAT(#{dispStdt},'000000') , '%Y-%m-%d %H:%i:%S')
		  , DATE_FORMAT(CONCAT(#{dispEddt},'235959') , '%Y-%m-%d %H:%i:%S')
		  , #{regNo}
		  , NOW()
		  , #{updNo}
		  , NOW()
		)
		ON DUPLICATE KEY UPDATE
		    KEYWORD = #{keyword}
		  , LINK_URL = #{linkUrl}
		  , DISP_STDT = DATE_FORMAT(CONCAT(#{dispStdt},'000000') , '%Y-%m-%d %H:%i:%S')
		  , DISP_EDDT = DATE_FORMAT(CONCAT(#{dispEddt},'235959') , '%Y-%m-%d %H:%i:%S')
		  , UPD_NO = #{updNo}
		  , UPD_DT = NOW()
	</insert>

	<select id="getSearchKeywordList" parameterType="SearchKeyword" resultType="SearchKeyword">
		/*TsaDisplay.getSearchKeywordList*/
		SELECT KEYWORD_SQ
		     , KEYWORD
		     , LINK_URL
		     , DISP_STDT
		     , DISP_EDDT
		     , REG_NO
		     , FN_GET_USER_NM(REG_NO) AS REG_NM
		     , REG_DT
		     , UPD_NO
		     , FN_GET_USER_NM(UPD_NO) AS UPD_NM
		     , UPD_DT
		FROM TB_SEARCH_KEYWORD
		WHERE 1=1
		<if test='edDate != null and edDate != ""'>
		<![CDATA[
		   AND DISP_STDT <= DATE_FORMAT(CONCAT(#{edDate},'235959') , '%Y-%m-%d %H:%i:%S')
		]]>
		</if>
		<if test='stDate != null and stDate != ""'>
		<![CDATA[
		   AND DISP_EDDT >= DATE_FORMAT(CONCAT(#{stDate},'000000') , '%Y-%m-%d %H:%i:%S')
		]]>
		</if>
	</select>
	
	<!-- 스플래시이미지 목록 -->
	<select id="getSplashImageList" resultType="SplashImage">
		/* TsaDisplay.getSplashImageList */
		SELECT SPLASH_IMG_SQ
		     , SPLASH_IMG
		     , DATE_FORMAT(DISP_STDT,'%Y%m%d')    AS DISP_STDT
		     , DATE_FORMAT(DISP_EDDT,'%Y%m%d')    AS DISP_EDDT
		     , DISP_YN
		     , FN_GET_USER_NM(UPD_NO)             AS UPD_NM
		     , DATE_FORMAT(UPD_DT,'%Y%m%d%H%i%S') AS UPD_DT
		FROM   TB_SPLASH_IMG SI
		ORDER  BY SPLASH_IMG_SQ DESC
	</select>

	<!-- 스플래시이미지 등록/수정 -->
	<insert id="createSplashImage" parameterType="SplashImage">
		/* TsaDisplay.createSplashImage */
		INSERT INTO TB_SPLASH_IMG (
		       SPLASH_IMG_SQ
		     , SPLASH_IMG
		     , DISP_STDT
		     , DISP_EDDT
		     , DISP_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       NULL
		     , #{splashImg}
		     , STR_TO_DATE(#{dispStdt},'%Y-%m-%d')
		     , STR_TO_DATE(#{dispEddt},'%Y-%m-%d')
		     , #{dispYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 스플래시이미지 등록/수정 -->
	<update id="updateSplashImage" parameterType="SplashImage">
		/* TsaDisplay.updateSplashImage */
		UPDATE TB_SPLASH_IMG
		SET    SPLASH_IMG = #{splashImg}
		     , DISP_STDT = STR_TO_DATE(#{dispStdt},'%Y-%m-%d')
		     , DISP_EDDT = STR_TO_DATE(#{dispEddt},'%Y-%m-%d')
		     , DISP_YN = #{dispYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  SPLASH_IMG_SQ = #{splashImgSq}
	</update>
	
</mapper>