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

	<!-- 공급벤더 목록 -->
	<select id="getSupplyVendorList" parameterType="SupplyCompany" resultType="SupplyCompany">
		/* TsaBusiness.getSupplyVendorList */
		SELECT SUPPLY_VENDOR_CD /*공급벤더코드*/
		     , SUPPLY_VENDOR_NM /*공급벤더명*/
		     , BIZ_GB           /*사업자구분*/
		     , BIZ_NO           /*사업자등록번호*/
		     , BIZ_KIND         /*업종*/
		     , BIZ_TYPE         /*업태*/
		     , OWNER_NM         /*대표자명*/
		     , BIZ_ZIPCODE      /*사업장우편번호*/
		     , BIZ_BASE_ADDR    /*사업장기본주소*/
		     , BIZ_DTL_ADDR     /*사업장상세주소*/
		     , MAIN_TELNO       /*대표전화번호*/
		     , MAIN_FAXNO       /*대표팩스번호*/
		     , HOMEPAGE_URL     /*홈페이지URL*/
		     , USE_YN           /*사용여부*/
		FROM   TB_SUPPLY_VENDOR
		WHERE  1 = 1
		<if test="searchTxt != null and searchTxt != ''">
		    <if test='searchGb == "NAME"'>
		AND    UPPER(SUPPLY_VENDOR_NM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		    </if>
		    <if test='searchGb == "OWNER"'>
		AND    UPPER(OWNER_NM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		    </if>
		</if>
		<if test="supplyVendorNm != null and supplyVendorNm != ''"> <!-- 공급벤더팝업에서 사용 -->
		AND    UPPER(SUPPLY_VENDOR_NM) LIKE CONCAT('%',UPPER(#{supplyVendorNm}),'%')
		</if>
	</select>
	
	<!-- 공급벤더 생성 -->
	<insert id="createSupplyVendor" parameterType="SupplyCompany">
		/* TsaBusiness.createSupplyVendor */
		INSERT INTO TB_SUPPLY_VENDOR (
		       SUPPLY_VENDOR_CD
		     , SUPPLY_VENDOR_NM
		     , BIZ_GB
		     , BIZ_NO
		     , BIZ_KIND
		     , BIZ_TYPE
		     , OWNER_NM
		     , BIZ_ZIPCODE
		     , BIZ_BASE_ADDR
		     , BIZ_DTL_ADDR
		     , MAIN_TELNO
		     , MAIN_FAXNO
		     , HOMEPAGE_URL
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       (SELECT CONCAT('SV',IFNULL(LPAD(SUBSTRING(MAX(SUPPLY_VENDOR_CD),3) + 1,4,'0'),'0000'))
		        FROM   TB_SUPPLY_VENDOR Z
		       )
		     , #{supplyVendorNm}
		     , #{bizGb}
		     , #{bizNo}
		     , #{bizKind}
		     , #{bizType}
		     , #{ownerNm}
		     , #{bizZipcode}
		     , #{bizBaseAddr}
		     , #{bizDtlAddr}
		     , #{mainTelno}
		     , #{mainFaxno}
		     , #{homepageUrl}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 공급벤더 수정 -->
	<update id="updateSupplyVendor" parameterType="SupplyCompany">
		/* TsaBusiness.updateSupplyVendor */
		UPDATE TB_SUPPLY_VENDOR
		SET    SUPPLY_VENDOR_NM = #{supplyVendorNm}
		     , BIZ_GB = #{bizGb}
		     , BIZ_NO = #{bizNo}
		     , BIZ_KIND = #{bizKind}
		     , BIZ_TYPE = #{bizType}
		     , OWNER_NM = #{ownerNm}
		     , BIZ_ZIPCODE = #{bizZipcode}
		     , BIZ_BASE_ADDR = #{bizBaseAddr}
		     , BIZ_DTL_ADDR = #{bizDtlAddr}
		     , MAIN_TELNO = #{mainTelno}
		     , MAIN_FAXNO = #{mainFaxno}
		     , HOMEPAGE_URL = #{homepageUrl}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  SUPPLY_VENDOR_CD = #{supplyVendorCd}
	</update>
	
	<!-- 공급업체 목록 -->
	<select id="getSupplyCompanyList" parameterType="SupplyCompany" resultType="SupplyCompany">
		/* TsaBusiness.getSupplyCompanyList */
		SELECT SC.SUPPLY_COMP_CD                                       /*공급업체코드*/
		     , SC.SUPPLY_COMP_NM                                       /*공급업체명*/
		     , SC.SUPPLY_VENDOR_CD                                     /*공급벤더코드*/
		     , SV.SUPPLY_VENDOR_NM                                     /*공급벤더명*/
		     , SV.BIZ_GB                                               /*사업자구분*/
		     , SV.BIZ_NO                                               /*사업자등록번호*/
		     , SV.BIZ_KIND                                             /*업종*/
		     , SV.BIZ_TYPE                                             /*업태*/
		     , SV.OWNER_NM                                             /*대표자명*/
		     , SV.BIZ_ZIPCODE                                          /*사업장우편번호*/
		     , SV.BIZ_BASE_ADDR                                        /*사업장기본주소*/
		     , SV.BIZ_DTL_ADDR                                         /*사업장상세주소*/
		     , SV.MAIN_TELNO                                           /*대표전화번호*/
		     , SV.MAIN_FAXNO                                           /*대표팩스번호*/
		     , SV.HOMEPAGE_URL                                         /*홈페이지URL*/
		     , SC.DISTRIBUTION_GB                                      /*유통구분*/
		     , SC.SHOT_DELV_YN                                         /*총알배송여부*/
		     , SC.SUPPLY_STAT                                          /*입점상태*/
		     , FN_GET_CODE_NM('G010',SC.SUPPLY_STAT) AS SUPPLY_STAT_NM /*입점상태명*/
		     , SC.SELL_FEE_RATE                                        /*판매수수료율*/
		     , SC.SETTLE_DAY                                           /*정산일*/
		     , SC.BANK_CD                                              /*은행코드*/
		     , SC.ACCOUNT_NO                                           /*계좌번호*/
		     , SC.DEPOSITOR_NM                                         /*예금주명*/
		     , SC.CS_CHARGE_NM                                         /*CS담당자명*/
		     , SC.CS_CHARGE_TELNO                                      /*CS담당자전화번호*/
		     , SC.SETTLE_CHARGE_NM                                     /*정산담당자명*/
		     , SC.SETTLE_CHARGE_TELNO                                  /*정산담당자전화번호*/
		     , SC.SETTLE_CHARGE_EMAIL                                  /*정산담당자이메일*/
		     , SC.BILL_EMAIL                                           /*계산서이메일*/
		     , SC.ECONTRACT_YN                                         /*전자계약여부*/
		     , SC.REMARKS                                              /*비고*/
		     , SC.USE_YN                                               /*사용여부*/
		     , SC.NOTE                                                 /*배송안내*/
		FROM   TB_SUPPLY_COMPANY SC
		     , TB_SUPPLY_VENDOR SV
		WHERE  SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
		<if test="supplyStat != null and supplyStat != ''">
		AND    SC.SUPPLY_STAT = #{supplyStat}
		</if>
		<if test="searchTxt != null and searchTxt != ''">
		    <if test='searchGb == "NAME"'>
		AND    (
		        UPPER(SC.SUPPLY_COMP_NM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		        OR
		        UPPER(SV.SUPPLY_VENDOR_NM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		       )
		    </if>
		    <if test='searchGb == "OWNER"'>
		AND    UPPER(SV.OWNER_NM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		    </if>
		</if>
		<if test="econtractYn != null and econtractYn != ''">
		AND    SC.ECONTRACT_YN = #{econtractYn}
		</if>
		<if test="supplyCompCd != null and supplyCompCd != ''"> <!-- 브랜드관리 화면에서 사용 -->
		AND    SC.SUPPLY_COMP_CD = #{supplyCompCd}
		</if>
		<if test="multiSupplyCompCd != null and multiSupplyCompCd.length > 0">
		AND    SC.SUPPLY_COMP_CD IN
		    <foreach collection="multiSupplyCompCd" item="item" index="index"  open="(" close=")" separator=",">
		UPPER(#{item})
		    </foreach>
		</if>
		<if test="multiSupplyCompNm != null and multiSupplyCompNm.length > 0">
		AND (
		    <foreach collection="multiSupplyCompNm" item="item" index="index" separator="or">
		    UPPER(SC.SUPPLY_COMP_NM) LIKE CONCAT('%',UPPER(#{item}),'%')
		    </foreach>
		 )
		</if>
		<if test='selfYn != null and (selfYn == "Y" or selfYn == "N")'>
		<choose>
		    <when test='selfYn  == "Y"'>
		AND    SC.DISTRIBUTION_GB IN ('G065_10','G065_11','G065_12') 
		    </when>
		    <otherwise>
		AND    SC.DISTRIBUTION_GB IN ('G065_20')
		    </otherwise>
		</choose>
		</if>
	</select>
	
	<!-- 공급업체 생성 -->
	<insert id="createSupplyCompany" parameterType="SupplyCompany">
		/* TsaBusiness.createSupplyCompany */
		INSERT INTO TB_SUPPLY_COMPANY (
		       SUPPLY_COMP_CD
		     , SUPPLY_COMP_NM
		     , SUPPLY_VENDOR_CD
		     , PROVIDER_NO
		     , DISTRIBUTION_GB
		     , SHOT_DELV_YN
		     , SUPPLY_STAT
		     , SELL_FEE_RATE
		     , SETTLE_DAY
		     , BANK_CD
		     , ACCOUNT_NO
		     , DEPOSITOR_NM
		     , CS_CHARGE_NM
		     , CS_CHARGE_TELNO
		     , SETTLE_CHARGE_NM
		     , SETTLE_CHARGE_TELNO
		     , SETTLE_CHARGE_EMAIL
		     , BILL_EMAIL
		     , ECONTRACT_YN
		     , REMARKS
		     , USE_YN
		     , NOTE
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		        (
		         SELECT IFNULL(MAX(SUPPLY_COMP_CD),0) + 1
		         FROM   TB_SUPPLY_COMPANY Z
		        )
		     , #{supplyCompNm}
		     , #{supplyVendorCd}
		     , (SELECT CASE WHEN MAX(PROVIDER_NO) IS NULL THEN
		                        40000 /* AS-IS 최대 수보다 크게 수정해야함*/
		                    ELSE
		                        MAX(PROVIDER_NO) + 1
		               END
		        FROM   TB_SUPPLY_COMPANY Z
		        WHERE  PROVIDER_NO > 40000 /* AS-IS 최대 수보다 크게 크게 수정해야함*/
		       )
		     , #{distributionGb}
		     , #{shotDelvYn}
		     , #{supplyStat}
		     , IFNULL(#{sellFeeRate},0.0)
		     , #{settleDay}
		     , #{bankCd}
		     , #{accountNo}
		     , #{depositorNm}
		     , #{csChargeNm}
		     , #{csChargeTelno}
		     , #{settleChargeNm}
		     , #{settleChargeTelno}
		     , #{settleChargeEmail}
		     , #{billEmail}
		     , #{econtractYn}
		     , #{remarks}
		     , #{useYn}
		     , #{note}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 공급업체 수정 -->
	<update id="updateSupplyCompany" parameterType="SupplyCompany">
		/* TsaBusiness.updateSupplyCompany */
		UPDATE TB_SUPPLY_COMPANY
		SET    SUPPLY_COMP_NM = #{supplyCompNm}
		     , SUPPLY_VENDOR_CD = #{supplyVendorCd}
		     , DISTRIBUTION_GB = #{distributionGb}
		     , SHOT_DELV_YN = #{shotDelvYn}
		     , SUPPLY_STAT = #{supplyStat}
		     , SELL_FEE_RATE = IFNULL(#{sellFeeRate},0.0)
		     , SETTLE_DAY = #{settleDay}
		     , BANK_CD = #{bankCd}
		     , ACCOUNT_NO = #{accountNo}
		     , DEPOSITOR_NM = #{depositorNm}
		     , CS_CHARGE_NM = #{csChargeNm}
		     , CS_CHARGE_TELNO = #{csChargeTelno}
		     , SETTLE_CHARGE_NM = #{settleChargeNm}
		     , SETTLE_CHARGE_TELNO = #{settleChargeTelno}
		     , SETTLE_CHARGE_EMAIL = #{settleChargeEmail}
		     , BILL_EMAIL = #{billEmail}
		     , ECONTRACT_YN = #{econtractYn}
		     , REMARKS = #{remarks}
		     , USE_YN = #{useYn}
		     , NOTE = #{note}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  SUPPLY_COMP_CD = #{supplyCompCd}
	</update>
	
	<!-- 배송비정책 목록 -->
	<select id="getDeliveryFeePolicyList" parameterType="DelvFeePolicy" resultType="DelvFeePolicy">
		/* TsaBusiness.getDeliveryFeePolicyList */
		SELECT DELV_FEE_CD    /*배송비정책코드*/
		     , SUPPLY_COMP_CD /*공급업체코드*/
		     , DELV_FEE_CRITE /*배송비부과기준*/
		     , MIN_ORD_AMT    /*무료배송비최소주문금액*/
		     , DELV_FEE       /*배송비*/
		     , RTN_DELV_FEE   /*반품배송비*/
		     , USE_YN         /*사용여부*/
		FROM   TB_DELV_FEE_POLICY
		WHERE  SUPPLY_COMP_CD = #{supplyCompCd}
		ORDER  BY DELV_FEE_CD
	</select>
	
	<!-- 배송비정책 생성 -->
	<insert id="createDeliveryFeePolicy" parameterType="DelvFeePolicy">
		/* TsaBusiness.createDeliveryFeePolicy */
		INSERT INTO TB_DELV_FEE_POLICY (
		       DELV_FEE_CD
		     , SUPPLY_COMP_CD
		     , DELV_FEE_CRITE
		     , MIN_ORD_AMT
		     , DELV_FEE
		     , RTN_DELV_FEE
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       (SELECT CONCAT(IFNULL(SUPPLY_COMP_CD,#{supplyCompCd}),'_',IFNULL(LPAD(SUBSTRING(MAX(DELV_FEE_CD),INSTR(MAX(DELV_FEE_CD),'_') + 1) + 1,2,'0'),'01'))
		        FROM   TB_DELV_FEE_POLICY Z
		        WHERE  SUPPLY_COMP_CD LIKE CONCAT(#{supplyCompCd},'%')
		       )
		     , #{supplyCompCd}
		     , #{delvFeeCrite}
		     , IFNULL(#{minOrdAmt},0)
		     , IFNULL(#{delvFee},0)
		     , IFNULL(#{rtnDelvFee},0)
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 배송비정책 수정 -->
	<update id="updateDeliveryFeePolicy" parameterType="DelvFeePolicy">
		/* TsaBusiness.updateDeliveryFeePolicy */
		UPDATE TB_DELV_FEE_POLICY
		SET    DELV_FEE_CRITE = #{delvFeeCrite}
		     , MIN_ORD_AMT = IFNULL(#{minOrdAmt},0)
		     , DELV_FEE = IFNULL(#{delvFee},0)
		     , RTN_DELV_FEE = IFNULL(#{rtnDelvFee},0)
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  1 = 1
		<choose>
		    <when test="distributionGb == 'G065_20'"> <!-- 입점-위탁 -->
		AND    DELV_FEE_CD = #{delvFeeCd}
		    </when>
		    <otherwise>
		AND    SUPPLY_COMP_CD IN (SELECT SUPPLY_COMP_CD
		                          FROM   TB_SUPPLY_COMPANY
		                          WHERE  DISTRIBUTION_GB != 'G065_20' /*자사*/
		                         )
		    </otherwise>
		</choose>
	</update>
	
	<!-- 출고처 목록 -->
	<select id="getDeliveryLocList" parameterType="DeliveryLoc" resultType="DeliveryLoc">
		/* TsaBusiness.getDeliveryLocList */
		SELECT DELV_LOC_CD        /*출고처코드*/
		     , DELV_LOC_NM        /*출고처명*/
		     , SUPPLY_COMP_CD     /*공급업체코드*/
		     , DELV_LOC_CLSF      /*출고처분류*/
		     , DELV_ASSIGN_ORD    /*출고지정순서*/
		     , DELV_ASSIGN_GRADE  /*출고지정등급*/
		     , STOCK_APPL_RATE    /*재고적용율*/
		     , DELV_FEE_RATE      /*출고수수료율*/
		     , DELV_LOC_ZIPCODE   /*출고처우편번호*/
		     , DELV_LOC_BASE_ADDR /*출고처기본주소*/
		     , DELV_LOC_DTL_ADDR  /*출고처상세주소*/
		     , RTN_LOC_ZIPCODE    /*반품처우편번호*/
		     , RTN_LOC_BASE_ADDR  /*반품처기본주소*/
		     , RTN_LOC_DTL_ADDR   /*반품처상세주소*/
		     , RTN_LOC_NM         /*반품처명*/
		     , INVOICE_PRINT_TYPE /*송장출력형태*/
		     , USE_YN             /*사용여부*/
		FROM   TB_DELIVERY_LOC
		WHERE  1 = 1
		<if test="supplyCompCd != null and supplyCompCd != ''">
		AND    SUPPLY_COMP_CD = #{supplyCompCd}
		</if>
		<if test="multiSupplyComp != null and multiSupplyComp != ''">
		AND    SUPPLY_COMP_CD IN
		    <foreach collection="multiSupplyComp" item="item" index="index"  open="(" close=")" separator=",">
		    #{item}
		    </foreach>
		</if>
		<if test="delvLocNm != null and delvLocNm !=''">
		AND    LOWER(DELV_LOC_NM) LIKE CONCAT('%',LOWER(#{delvLocNm}),'%')
		</if>
		<if test="delvLocClsf != null and delvLocClsf != ''">
		AND    DELV_LOC_CLSF = #{delvLocClsf}
		</if>
		<if test="useYn != null and useYn != ''">
		AND    USE_YN = #{useYn}
		</if>
		ORDER  BY DELV_LOC_CD
	</select>

	<!-- 출고처 저장 -->
	<insert id="saveDeliveryLoc" parameterType="DeliveryLoc">
		/*  TsaBusiness.saveDeliveryInfo */
		INSERT INTO TB_DELIVERY_LOC (
		       DELV_LOC_CD
		     , DELV_LOC_NM
		     , SUPPLY_COMP_CD
		     , DELV_LOC_CLSF
		     , DELV_ASSIGN_ORD
		     , DELV_ASSIGN_GRADE
		     , STOCK_APPL_RATE
		     , DELV_FEE_RATE
		     , DELV_LOC_ZIPCODE
		     , DELV_LOC_BASE_ADDR
		     , DELV_LOC_DTL_ADDR
		     , RTN_LOC_ZIPCODE
		     , RTN_LOC_BASE_ADDR
		     , RTN_LOC_DTL_ADDR
		     , RTN_LOC_NM
		     , INVOICE_PRINT_TYPE
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		        (CASE WHEN #{mode} = 'N' AND #{delvLocClsf} = 'G024_30' THEN /*입점업체*/
		                  (SELECT CONCAT('DL',IFNULL(LPAD(SUBSTRING(MAX(DELV_LOC_CD),3) + 1,4,'0'),'0000'))
		                   FROM   TB_DELIVERY_LOC Z
		                   WHERE  DELV_LOC_CD LIKE 'DL%')
		              ELSE
		                  #{delvLocCd}
		         END
		        )
		     , #{delvLocNm}
		     , #{supplyCompCd}
		     , #{delvLocClsf}
		     , CASE WHEN #{delvLocClsf} IN ('G024_10','G024_21') THEN /*물류창고,직송매장*/
		                IFNULL(#{delvAssignOrd},100)
		            ELSE
		                #{delvAssignOrd}
		       END
		     , #{delvAssignGrade}
		     , CASE WHEN #{delvLocClsf} IN ('G024_10','G024_21') AND #{stockApplRate} = 0.0 THEN /*물류창고,직송매장*/
		                IFNULL(#{stockApplRate},100.0)
		            ELSE
		                #{stockApplRate}
		       END
		     , CASE WHEN #{delvLocClsf} = 'G024_21' THEN /*직송매장*/
		                IFNULL(#{delvFeeRate},0.0)
		            ELSE
		                #{delvFeeRate}
		       END
		     , #{delvLocZipcode}
		     , #{delvLocBaseAddr}
		     , #{delvLocDtlAddr}
		     , #{rtnLocZipcode}
		     , #{rtnLocBaseAddr}
		     , #{rtnLocDtlAddr}
		     , #{rtnLocNm}
		     , #{invoicePrintType}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       DELV_LOC_NM = #{delvLocNm}
		     , DELV_LOC_CLSF = #{delvLocClsf}
		     , DELV_ASSIGN_ORD = CASE WHEN #{delvLocClsf} IN ('G024_10','G024_21') THEN /*물류창고,직송매장*/
		                                  IFNULL(#{delvAssignOrd},100)
		                              ELSE
		                                  #{delvAssignOrd}
		                         END
		     , DELV_ASSIGN_GRADE = #{delvAssignGrade}
		     , STOCK_APPL_RATE = CASE WHEN #{delvLocClsf} IN ('G024_10','G024_21') AND #{stockApplRate} = 0.0 THEN /*물류창고,직송매장*/
		                                  100
		                              ELSE
		                                  #{stockApplRate}
		                         END
		     , DELV_FEE_RATE = CASE WHEN #{delvLocClsf} = 'G024_21' THEN /*직송매장*/
		                                IFNULL(#{delvFeeRate},0.0)
		                            ELSE
		                                #{delvFeeRate}
		                       END
		     , DELV_LOC_ZIPCODE = #{delvLocZipcode}
		     , DELV_LOC_BASE_ADDR = #{delvLocBaseAddr}
		     , DELV_LOC_DTL_ADDR = #{delvLocDtlAddr}
		     , RTN_LOC_ZIPCODE = #{rtnLocZipcode}
		     , RTN_LOC_BASE_ADDR = #{rtnLocBaseAddr}
		     , RTN_LOC_DTL_ADDR = #{rtnLocDtlAddr}
		     , RTN_LOC_NM = #{rtnLocNm}
		     , INVOICE_PRINT_TYPE = #{invoicePrintType}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 브랜드 목록 -->
	<select id="getBrandList" parameterType="Brand" resultType="Brand">
		/* TsaBusiness.getBrandList */
		SELECT CASE WHEN A.SELF_YN = 'Y' THEN 'S'
		            ELSE 'E'
		       END               AS BRAND_GB /*브랜드구분*/
		     , A.BRAND_CD                    /*브랜드코드*/
		     , A.BRAND_ENM                   /*브랜드영문명*/
		     , A.BRAND_KNM                   /*브랜드한글명*/
		     , A.DISTRIBUTION_GB             /*유통구분*/
		     , A.ERP_BRAND_CD                /*ERP브랜드코드*/
		     , A.SELF_YN                     /*자사여부*/
		     , A.USE_YN                      /*사용여부*/
		     , A.PNT_PRATE10                 /*포인트적립율(PC)*/
		     , A.PNT_MRATE10                 /*포인트적립율(모바일)*/
		     , A.PNT_PRATE20                 /*포인트적립율(PC)*/
		     , A.PNT_MRATE20                 /*포인트적립율(모바일)*/
		     , A.DISP_ORD                    /*표시순서*/
		     , (CASE WHEN D.DISP_NM_LANG = 'EN' THEN D.BRAND_GROUP_ENM ELSE D.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		     , A.BRAND_GROUP_NO
		FROM   TB_BRAND A
		INNER JOIN TB_BRAND_GROUP D ON A.BRAND_GROUP_NO = D.BRAND_GROUP_NO
		                            AND D.USE_YN = 'Y'
		<if test='supplyCompCd != null and supplyCompCd != ""'>
		INNER JOIN TB_BRAND_SUPPLY C ON A.BRAND_CD = C.BRAND_CD
		INNER JOIN TB_SUPPLY_COMPANY B ON C.SUPPLY_COMP_CD = B.SUPPLY_COMP_CD
		</if>
		WHERE  1 = 1
		<if test='supplyCompCd != null and supplyCompCd != ""'>
		AND    C.SUPPLY_COMP_CD = #{supplyCompCd}
		</if>
		<if test='distributionGb != null and distributionGb != ""'>
		AND    A.DISTRIBUTION_GB = #{distributionGb}
		</if>
		<if test='erpBrandCd != null and erpBrandCd != ""'>
		AND    A.ERP_BRAND_CD =  #{erpBrandCd}
		</if>
		<if test='brandCd != null and brandCd != ""'>
		AND    A.BRAND_CD = #{brandCd}
		</if>
		<if test="searchTxt != null and searchTxt != ''">
		AND    (
		        UPPER(A.BRAND_ENM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		        OR
		        UPPER(A.BRAND_KNM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		        OR
		        UPPER(A.BRAND_CD) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		        OR
		        UPPER(D.BRAND_GROUP_KNM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		        OR
		        UPPER(D.BRAND_GROUP_ENM) LIKE CONCAT('%',UPPER(#{searchTxt}),'%')
		       )
		</if>
		<if test="multiBrandCd != null and multiBrandCd.length > 0">
		AND    A.BRAND_CD IN
		    <foreach collection="multiBrandCd" item="item" index="index"  open="(" close=")" separator=",">
		UPPER(#{item})
		    </foreach>
		</if>
		<if test="multiBrandNm != null and multiBrandNm.length > 0">
		AND (
		    <foreach collection="multiBrandNm" item="item" index="index" separator="or">
		    (
		    UPPER(A.BRAND_ENM) LIKE CONCAT('%',UPPER(#{item}),'%')
		    OR
		    UPPER(A.BRAND_KNM) LIKE CONCAT('%',UPPER(#{item}),'%')
		    )
		  </foreach>
		 )
		</if>
		<if test='useYn != null and useYn != ""'>
		AND    A.USE_YN = #{useYn}
		</if>
		<if test='selfYn != null and selfYn != ""'>
		AND    A.SELF_YN = #{selfYn}
		</if>
		ORDER  BY A.SELF_YN DESC,  A.DISP_ORD
	</select>
	
	<!-- 담당MD 목록 -->
	<select id="getMdList" parameterType="String" resultType="BrandMd">
		/* TsaBusiness.getMdList */
		SELECT USER_NO AS MD_NO
		     , USER_NM AS MD_NM
		     , USER_ID
		FROM   TB_USER
		WHERE   1 = 1
		/* AND ROLE_CD = 'G001_A101'  */  /*MD권한*/
		<if test="mdNm != null and mdNm != ''">
		AND    LOWER(USER_NM) LIKE CONCAT('%',LOWER(#{mdNm}),'%')
		</if>
		AND    USE_YN  = 'Y'
		ORDER  BY USER_NM
	</select>
	
	<!-- 브랜드담당MD 목록 -->
	<select id="getBrandMdList" parameterType="String" resultType="BrandMd">
		/* TsaBusiness.getBrandMdList */
		SELECT A.FORMAL_GB           /*정상이월구분*/
		     , A.MD_NO               /*담당MD번호*/
		     , B.USER_NM    AS MD_NM /*담당MD명*/
		FROM   TB_BRAND_MD A
		     , TB_USER B
		WHERE  A.MD_NO = B.USER_NO
		AND    A.BRAND_CD = #{brandCd}
	</select>
	
	<!-- 브랜드담당MD 삭제 -->
	<delete id="deleteBrandMd" parameterType="String">
		/* TsaBusiness.deleteBrandMd */
		DELETE
		FROM   TB_BRAND_MD
		WHERE  BRAND_CD = #{brandCd}
	</delete>

	<!-- 브랜드담당MD 등록 -->
	<insert id="createBrandMd" parameterType="BrandMd">
		/* TsaBusiness.createBrandMd */
		INSERT INTO TB_BRAND_MD (
		       BRAND_CD
		     , FORMAL_GB
		     , MD_NO
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		SELECT #{brandCd}  AS BRAND_CD
		     , CD          AS FORMAL_GB
		     , #{mdNo}     AS MD_NO
		     , #{regNo}    AS REG_NO
		     , NOW()       AS REG_DT
		     , #{updNo}    AS UPD_NO
		     , NOW()       AS UPD_DT
		FROM   TB_COMMON_CODE
		WHERE  CD_GB = 'G009' /*정상이월구분*/
		AND    CD IN ('G009_10','G009_20') /*정상,이월*/
		AND    USE_YN = 'Y'
	</insert>
	
	<!-- 브랜드 노출사이트 목록 -->
	<select id="getBrandSiteList" parameterType="String" resultType="SiteBrand">
		/* TsaBusiness.getBrandSiteList */
		SELECT BRAND_CD
		     , SITE_CD
		     , DISP_ORD
		     , USE_YN
		     , CATE1_NO
		FROM   TB_SITE_BRAND
		WHERE  BRAND_CD = #{brandCd}
		AND    USE_YN ='Y'
	</select>
	
	<!-- 브랜드 노출사이트 등록/수정 -->
	<insert id="saveBrandSite" parameterType="SiteBrand">
		/* TsaBusiness.saveBrandSite */
		INSERT INTO TB_SITE_BRAND (
		       SITE_CD
		     , BRAND_CD
		     , DISP_ORD
		     , USE_YN
		     , CATE1_NO
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{siteCd}
		     , #{brandCd}
		     , #{dispOrd}
		     , #{useYn}
		     , #{cate1No}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 브랜드 공급업체 목록 -->
	<select id="getBrandSupplyList" parameterType="String" resultType="Brand">
		/* TsaBusiness.getBrandSupplyList */
		SELECT A.BRAND_CD
		     , A.SUPPLY_COMP_CD
		     , B.SUPPLY_COMP_NM
		     , A.REG_NO
		     , FN_GET_USER_NM(A.REG_NO) AS REG_NM
		     , DATE_FORMAT(A.REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		FROM   TB_BRAND_SUPPLY A
		INNER JOIN TB_SUPPLY_COMPANY B ON A.SUPPLY_COMP_CD = B.SUPPLY_COMP_CD
		WHERE  BRAND_CD = #{brandCd}
	</select>
	
	<!-- 브랜드 등록 -->
	<insert id="createBrand" parameterType="Brand" >
		/* TsaBusiness.createBrand */
		<selectKey keyProperty="brandCd" resultType="String" order="BEFORE">
			 (SELECT CONCAT(#{brandGb},IFNULL(LPAD(CAST(SUBSTRING(MAX(BRAND_CD),2) AS UNSIGNED) + 1,4,'0'),'0000'))
		        FROM   TB_BRAND Z
		        WHERE  BRAND_CD LIKE CONCAT(#{brandGb},'%')
		       )
		</selectKey>
		
		INSERT INTO TB_BRAND (
		       BRAND_CD
		     , BRAND_ENM
		     , BRAND_KNM
		     , DISTRIBUTION_GB
		     , SELF_YN
		     , ERP_BRAND_CD
		     , PNT_PRATE10
		     , PNT_MRATE10
		     , PNT_PRATE20
		     , PNT_MRATE20
		     , DISP_ORD
		     , USE_YN
		     , BRAND_NO
		     , BRAND_GROUP_NO
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{brandCd}
		     , #{brandEnm}
		     , #{brandKnm}
		     , #{distributionGb}
		     , CASE WHEN #{distributionGb} IN ('G065_10','G065_11','G065_12') THEN 'Y'
		            ELSE 'N'
		       END
		     , #{erpBrandCd}
		     , IFNULL(#{pntPrate10},0)
		     , IFNULL(#{pntMrate10},0)
		     , IFNULL(#{pntPrate20},0)
		     , IFNULL(#{pntMrate20},0)
		     , #{dispOrd}
		     , #{useYn}
		     , (SELECT CASE WHEN MAX(BRAND_NO) IS NULL THEN 30000   /* AS-IS 최대 수보다 크게 설정해야 함 */
		               ELSE (MAX(BRAND_NO) + 1) END
		        FROM   TB_BRAND Z
		        WHERE  1 = 1
		        AND BRAND_NO > 29999                  /* AS-IS 최대 수보다 크게 크게 설정해야 함*/
		       ) 
		     , #{brandGroupNo}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 브랜드 수정 -->
	<insert id="updateBrand" parameterType="Brand">
		/* TsaBusiness.updateBrand */
		UPDATE TB_BRAND
		SET    BRAND_ENM = #{brandEnm}
		     , BRAND_KNM = #{brandKnm}
		     , DISTRIBUTION_GB = #{distributionGb}
		     , SELF_YN = CASE WHEN #{distributionGb} IN ('G065_10','G065_11','G065_12') THEN 'Y'
		                      ELSE 'N'
		                 END
		     , ERP_BRAND_CD = #{erpBrandCd}
		     , PNT_PRATE10 = IFNULL(#{pntPrate10},0)
		     , PNT_MRATE10 = IFNULL(#{pntMrate10},0)
		     , PNT_PRATE20 = IFNULL(#{pntPrate20},0)
		     , PNT_MRATE20 = IFNULL(#{pntMrate20},0)
		     , DISP_ORD = #{dispOrd}
		     , USE_YN = #{useYn}
		     , BRAND_GROUP_NO = #{brandGroupNo}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  BRAND_CD = #{brandCd}
	</insert>
	
	<!-- 브랜드별 업체 정보 저장 -->
	<insert id="saveBrandSupply" parameterType="Brand">
		/* TsaBusiness.saveBrandSupply */
		INSERT INTO TB_BRAND_SUPPLY ( 
		    BRAND_CD
		  , SUPPLY_COMP_CD
		  , REG_NO
		  , REG_DT
		)
		VALUES
		(   #{brandCd}
		  , #{supplyCompCd}
		  , #{regNo}
		  , NOW()
		)
		ON DUPLICATE KEY UPDATE
		     SUPPLY_COMP_CD = #{supplyCompCd} -- 무의미한 데이터
	</insert>
	
	<!-- 재고연계관리 - 출고처 목록 -->
	<select id="getStockSyncDeliveryLocList" parameterType="DeliveryLoc" resultType="DeliveryLoc">
		/* TsaBusiness.getStockSyncDeliveryLocList */
		SELECT SUPPLY_COMP_CD
		     , DELV_LOC_CD
		     , DELV_LOC_NM
		     , DELV_LOC_CLSF
		     , FN_GET_CODE_NM('G024',DELV_LOC_CLSF) AS DELV_LOC_CLSF_NM
		FROM   TB_DELIVERY_LOC
		WHERE  SUPPLY_COMP_CD = #{supplyCompCd}
		AND    DELV_LOC_CLSF IN ('G024_10','G024_21') /*물류창고,직송매장*/
		<if test="delvLocClsf != null and delvLocClsf != ''">
		AND    DELV_LOC_CLSF = #{delvLocClsf}
		</if>
		<if test="searchTxt != null and searchTxt != ''">
		    <if test="searchGb == 'CODE'">
		AND    DELV_LOC_CD = #{delvLocCd}
		    </if>
		    <if test="searchGb == 'NAME'">
		AND    DELV_LOC_NM like CONCAT('%',#{delvLocNm},'%')
		    </if>
		</if>
		AND    USE_YN = 'Y'
		ORDER  BY DELV_LOC_NM
	</select>

	<!-- 재고연계관리 - 재고연계기준 목록 -->
	<select id="getStockSyncBaseList" parameterType="StockSyncBase" resultType="StockSyncBase">
		/* TsaBusiness.getStockSyncBaseList */
		SELECT X.DELV_LOC_CD
		     , X.BRAND_CD
		     , X.ERP_BRAND_CD
		     , X.BRAND_KNM
		     , X.BRAND_ENM
		     , IFNULL(Y.STOCK_SYNC_YN,'N') AS PREV_STOCK_SYNC_YN /*이전재고연계여부*/
		     , IFNULL(Y.STOCK_SYNC_YN,'N') AS STOCK_SYNC_YN      /*재고연계여부*/
		     , IFNULL(Y.DELV_SYNC_YN,'N')  AS PREV_DELV_SYNC_YN  /*이전출고연계여부*/
		     , IFNULL(Y.DELV_SYNC_YN,'N')  AS DELV_SYNC_YN       /*출고연계여부*/
		FROM   (
		        SELECT A.DELV_LOC_CD
		             , B.BRAND_CD
		             , B.ERP_BRAND_CD
		             , B.BRAND_KNM
		             , B.BRAND_ENM
		        FROM   TB_DELIVERY_LOC A
		             , TB_BRAND B
		             , TB_BRAND_SUPPLY C 
		        WHERE  A.SUPPLY_COMP_CD  = C.SUPPLY_COMP_CD
		        AND    B.BRAND_CD = C.BRAND_CD 
		        AND    A.SUPPLY_COMP_CD = #{supplyCompCd}
		        AND    A.DELV_LOC_CD = #{delvLocCd}
		        AND    A.USE_YN = 'Y'
		       ) X
		LEFT OUTER JOIN TB_STOCK_SYNC_BASE Y ON X.DELV_LOC_CD = Y.DELV_LOC_CD
		                                    AND X.BRAND_CD = Y.BRAND_CD
		ORDER BY X.DELV_LOC_CD, X.BRAND_CD
	</select>

	<!-- 재고연계관리 - 재고연계기준 저장 -->
	<insert id="saveStockSyncBase" parameterType="StockSyncBase">
		/* TsaBusiness.saveStockSyncBase */
		INSERT INTO TB_STOCK_SYNC_BASE (
		       DELV_LOC_CD
		     , BRAND_CD
		     , FORMAL_GB
		     , STOCK_SYNC_YN
		     , DELV_SYNC_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{delvLocCd}
		     , #{brandCd}
		     , IFNULL(#{formalGb},'G009_10')
		     , #{stockSyncYn}
		     , #{delvSyncYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       STOCK_SYNC_YN = #{stockSyncYn}
		     , DELV_SYNC_YN = #{delvSyncYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>

	<!-- 판매매장 목록  -->
	<select id="getSellStoreList" parameterType="SellStore" resultType="SellStore">
		/* TsaBusiness.getSellStoreList */
		SELECT SELL_STORE_CD
		     , SELL_STORE_NM
		     , USE_YN
		FROM   TB_SELL_STORE
		WHERE  1 = 1
		<if test="sellStoreNm != null and sellStoreNm != ''">
		AND    LOWER(SELL_STORE_NM) LIKE CONCAT('%',LOWER(#{sellStoreNm}),'%')
		</if>
		<if test="useYn != null and useYn != ''">
		AND    USE_YN = #{useYn}
		</if>
		ORDER  BY SELL_STORE_NM
	</select>

	<!-- 판매매장 등록/수정 -->
	<insert id="saveSellStore" parameterType="SellStore">
		/* TsaBusiness.saveSellStore */
		INSERT INTO TB_SELL_STORE (
		       SUPPLY_COMP_CD
		     , SELL_STORE_CD
		     , SELL_STORE_NM
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{supplyCompCd}
		     , #{sellStoreCd}
		     , #{sellStoreNm}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       SELL_STORE_CD = #{sellStoreCd}
		     , SELL_STORE_NM = #{sellStoreNm}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 제휴링크 목록 -->
	<select id="getAflinkList" parameterType="Aflink" resultType="Aflink">
		/*  TsaBusiness.getAflinkList */
		SELECT AF_LINK_CD
		     , AF_LINK_NM
		     , AF_CHANNEL
		     , FEE_RATE
		     , DISP_ORD
		     , USE_YN
		FROM   TB_AF_LINK
		WHERE  1 = 1
		<if test="afChannel != null and afChannel != ''">
		AND    AF_CHANNEL = #{afChannel}
		</if>
	</select>

	<!-- 제휴링크 저장 -->
	<insert id="saveAflink" parameterType="Aflink">
		/* TsaBusiness.saveAflink */
		INSERT INTO TB_AF_LINK (
		       AF_LINK_CD
		     , AF_LINK_NM
		     , AF_CHANNEL
		     , FEE_RATE
		     , DISP_ORD
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		        (CASE WHEN #{mode} = 'N' THEN
		                  (SELECT CONCAT('AL',IFNULL(LPAD(SUBSTRING(MAX(AF_LINK_CD),3) + 1,3,'0'),'000'))
		                   FROM   TB_AF_LINK Z)
		              ELSE
		                  #{afLinkCd}
		         END
		        )
		     , #{afLinkNm}
		     , #{afChannel}
		     , #{feeRate}
		     , #{dispOrd}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       AF_LINK_NM = #{afLinkNm}
		     , AF_CHANNEL = #{afChannel}
		     , FEE_RATE = #{feeRate}
		     , DISP_ORD = #{dispOrd}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>

	<!-- 제휴링크 삭제 -->
	<update id="deleteAflink" parameterType="Aflink">
		/* TsaBusiness.deleteAflink */
		UPDATE TB_AF_LINK
		SET    USE_YN = 'N'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  AF_LINK_CD = #{afLinkCd}
	</update>
	
	<!-- 배송업체관리 목록 -->
	<select id="getShipCompanyList" resultType="ShipCompany">
		/* TsaBusiness.getShipCompanyList */
		SELECT SHIP_COMP_CD
		     , SHIP_COMP_NM
		     , TRACKING_URL
		     , USE_YN
		FROM   TB_SHIP_COMPANY
		ORDER  BY SHIP_COMP_CD
	</select>

	<!-- 배송업체관리 저장 -->
	<insert id="saveShipCompany" parameterType="ShipCompany">
		/* TsaBusiness.saveShipCompany */
		INSERT INTO TB_SHIP_COMPANY (
		       SHIP_COMP_CD
		     , SHIP_COMP_NM
		     , TRACKING_URL
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{shipCompCd}
		     , #{shipCompNm}
		     , #{trackingUrl}
		     , #{useYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       SHIP_COMP_NM = #{shipCompNm}
		     , TRACKING_URL = #{trackingUrl}
		     , USE_YN = #{useYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 배송업체관리 삭제 -->
	<update id="deleteShipCompany" parameterType="ShipCompany">
		/* TsaBusiness.updateShipCompany */
		UPDATE TB_SHIP_COMPANY
		SET    USE_YN = 'N'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  SHIP_COMP_CD=  #{shipCompCd}
	</update>
	
	<!-- 브랜드 그룹 목록 -->
	<select id="getBrandGroupList" parameterType="String" resultType="BrandGroup">
		/* TsaBusiness.getBrandGroupList */
		SELECT A.BRAND_GROUP_NO
		     , A.BRAND_GROUP_ENM
		     , A.BRAND_GROUP_KNM
		     , A.DISP_NM_LANG
		     , A.LOGO_FILE_NM
		     , A.BW_LOGO_FILE_NM
		     , A.RGB_CD
		     , A.DISP_ORD
		     , A.DEFAULT_CATE_YN
		     , A.BRAND_CATE_NO
		     , (CASE WHEN DISP_NM_LANG = 'EN' THEN BRAND_GROUP_ENM ELSE BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		FROM   TB_BRAND_GROUP A
		WHERE 1 = 1
		<if test="multiBrandGroupNo != null and multiBrandGroupNo.length > 0">
		AND    A.BRAND_GROUP_NO IN
		    <foreach collection="multiBrandGroupNo" item="item" index="index"  open="(" close=")" separator=",">
		UPPER(#{item})
		    </foreach>
		</if>
		<if test="multiBrandGroupNm != null and multiBrandGroupNm.length > 0">
		AND (
		    <foreach collection="multiBrandGroupNm" item="item" index="index" separator="or">
		    (
		    UPPER(A.BRAND_GROUP_ENM) LIKE CONCAT('%',UPPER(#{item}),'%')
		    OR
		    UPPER(A.BRAND_GROUP_KNM) LIKE CONCAT('%',UPPER(#{item}),'%')
		    )
		  </foreach>
		 )
		</if>
		ORDER  BY A.BRAND_GROUP_KNM
	</select>
	
	<!-- 브랜드 그룹 등록 -->
	<insert id="createBrandGroup" parameterType="BrandGroup">
		/* TsaBusiness.createBrandGroup */
		<selectKey keyProperty="brandGroupNo" resultType="String" order="BEFORE">
			SELECT CASE WHEN MAX(BRAND_GROUP_NO) IS NULL THEN 30000
			ELSE (MAX(BRAND_GROUP_NO) + 1) END
			FROM   TB_BRAND_GROUP Z
			WHERE  1 = 1
			AND BRAND_GROUP_NO > 29999
		</selectKey>
		INSERT INTO TB_BRAND_GROUP (
		       BRAND_GROUP_NO
		     , BRAND_GROUP_ENM
		     , BRAND_GROUP_KNM
		     , DISP_NM_LANG
		     , RGB_CD
		     , LOGO_FILE_NM
		     , BW_LOGO_FILE_NM
		     , DEFAULT_CATE_YN
		     , BRAND_CATE_NO
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{brandGroupNo}
		     , #{brandGroupEnm}
		     , #{brandGroupKnm}
		     , #{dispNmLang}
		     , IFNULL(NULLIF(#{rgbCd},''),'222222')
		     , #{logoFileNm}
		     , #{bwLogoFileNm}
		     , #{defaultCateYn}
		     , #{brandCateNo}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 브랜드 그룹 수정 -->
	<update id="updateBrandGroup" parameterType="BrandGroup">
		/* TsaBusiness.updateBrandGroup */
		UPDATE TB_BRAND_GROUP
		SET    BRAND_GROUP_ENM = #{brandGroupEnm}
		     , BRAND_GROUP_KNM = #{brandGroupKnm}
		     , DISP_NM_LANG = #{dispNmLang}
		     , RGB_CD = IFNULL(NULLIF(#{rgbCd},''),'222222')
		     , LOGO_FILE_NM = #{logoFileNm}
		     , BW_LOGO_FILE_NM = #{bwLogoFileNm}
		     , DEFAULT_CATE_YN = IFNULL(#{defaultCateYn},'Y')
		     <if test="brandCateNo != null and brandCateNo > 0">
		     , BRAND_CATE_NO =  #{brandCateNo}
		     </if>
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  BRAND_GROUP_NO = #{brandGroupNo}
	</update>
	
	<!-- 휴무일 목록 -->
	<select id="getHolidayList" parameterType="Bizday" resultType="Bizday">
		/* TsaBusiness.getHolidayList */
		SELECT DATE_FORMAT(BIZDAY,'%Y-%m-%d') AS BIZDAY /*영업일*/
		     , WEEK_DAY                                 /*요일*/
		     , BIZDAY_YN                                /*영업일여부*/
		     , RESTDAY_DESC                             /*휴무일설명*/
		FROM   TB_BIZDAY
		WHERE  1= 1
		AND    BIZDAY <![CDATA[>=]]> #{startDt}
		AND    BIZDAY <![CDATA[<]]> #{endDt}
		AND    BIZDAY_YN = 'N' /*영업일이 아닌 넘만*/
	</select>
	
	<!-- 영업일여부 조회 -->
	<select id="getBizdayYn" parameterType="String" resultType="String">
		/* TsaBusiness.getBizdayYn */
		SELECT BIZDAY_YN
		FROM   TB_BIZDAY
		WHERE  BIZDAY = #{bizday}
	</select>

	<!-- 영업일 생성 -->
	<insert id="createBizday" parameterType="Bizday">
		/*  TsaBusiness.saveBizday */
		INSERT INTO TB_BIZDAY (
		       BIZDAY
		     , WEEK_DAY
		     , BIZDAY_YN
		     , RESTDAY_DESC
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{bizday}
		     , CASE DAYOFWEEK(#{bizday})
		            WHEN 1 THEN '일'
		            WHEN 2 THEN '월'
		            WHEN 3 THEN '화'
		            WHEN 4 THEN '수'
		            WHEN 5 THEN '목'
		            WHEN 6 THEN '금'
		            WHEN 7 THEN '토'
		       END
		     , CASE DAYOFWEEK(#{bizday})
		            WHEN 1 THEN 'N'
		            WHEN 2 THEN 'Y'
		            WHEN 3 THEN 'Y'
		            WHEN 4 THEN 'Y'
		            WHEN 5 THEN 'Y'
		            WHEN 6 THEN 'Y'
		            WHEN 7 THEN 'N'
		       END
		     , CASE DAYOFWEEK(#{bizday})
		            WHEN 1 THEN '휴일'
		            WHEN 2 THEN NULL
		            WHEN 3 THEN NULL
		            WHEN 4 THEN NULL
		            WHEN 5 THEN NULL
		            WHEN 6 THEN NULL
		            WHEN 7 THEN '휴일'
		       END
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 영업일 수정 -->
	<insert id="updateBizday" parameterType="Bizday">
		/*  TsaBusiness.updateBizday */
		UPDATE TB_BIZDAY
		SET    BIZDAY_YN = (CASE WHEN #{bizdayYn} = 'Y' THEN 'N' ELSE 'Y' END)
		     , RESTDAY_DESC = (CASE WHEN #{bizdayYn} = 'Y' THEN '휴일' ELSE NULL END)
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  BIZDAY = #{bizday}
	</insert>
	
	<!-- 총알배송 브랜드 등록 -->
	<insert id="createShotDelvBrand" parameterType="Brand">
		/*  TsaBusiness.createShotDelvBrand */
		INSERT INTO TB_SHOT_DELV_BRAND
		(
		    BRAND_GROUP_NO
		   ,DISP_ORD
		   ,CORNER_DISP_TYPE
		   ,REG_DT
		)
		SELECT BRAND_GROUP_NO
		     , IFNULL((SELECT COUNT(*) FROM TB_SHOT_DELV_BRAND),0)+1 AS DISP_ORD
		     , 'G045_5' AS CORNER_DISP_TYPE
		     , NOW()
		FROM TB_BRAND  B
		WHERE BRAND_CD = #{brandCd}
		ON DUPLICATE KEY UPDATE
		    REG_DT = NOW()
	</insert>

	<!-- 브랜드그룹 신규생성시 메인레이아웃 등록 -->
	<insert id="createBrandGroupMainLayout" parameterType="BrandGroup">
		/*  TsaBusiness.createBrandGroupMainLayout */
		INSERT INTO  TB_MAIN_LAYOUT (
		    CATE_NO
		  , CONTENTS_LOC
		  , BRAND_GROUP_NO
		  , DISP_ORD
		  , CONTENTS_YN
		  , COL_CNT
		  , COL_NO
		  , CONTENTS_TITLE
		  , REG_NO
		  , REG_DT
		  , UPD_NO
		  , UPD_DT
		)
		SELECT '1711' AS CATE_NO
		     , CD AS CONTENTS_LOC
		     , #{brandGroupNo} AS BRAND_GROUP_NO
		     , ROW_NUMBER() OVER () AS DISP_ORD
		     , CD_DESC AS CONTENTS_YN
		     , 1 AS COL_CNT
		     , 1 AS COL_NO
		     , CASE WHEN CD = 'SBM007' THEN '신상품'
		            WHEN CD = 'SBM008' THEN '베스트'
		            WHEN CD = 'SBM009' THEN 'MD추천'
		            WHEN CD = 'SBM010' THEN '룩북' ELSE '' END CONTENTS_TITLE
		     , #{regNo} AS REG_NO
		     , NOW() AS REG_DT
		     , #{updNo}
			 , NOW() AS UPD_DT
		FROM TB_COMMON_CODE
		WHERE CD_GB = 'G028'
		  AND USE_YN = 'Y'
		  AND SUBSTRING(CD,1,3) = 'SBM'
		  AND CD NOT IN ('SBM014','SBM015','SBM016','SBM017','SBM018','SBM019')
		ORDER BY DISP_ORD
	</insert>
</mapper>