<?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.front.biz.dao.TsfCommonDao">

	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM   (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	
	<!-- 웹제휴채널이력 생성 -->
	<insert id="createInflowHistory" parameterType="InflowHst">
		/* TsfCommon.createInflowHistory */
		INSERT INTO TB_INFLOW_HST (
		       INFLOW_DT
		     , SITE_CD
		     , FRONT_GB
		     , APP_YN
		     , OS_TYPE
		     , AF_LINK_CD
		     , PAGE_URL
		     , INFLOW_REFER
		     , IP_ADDR
		     , JSESSION_ID
		)
		SELECT NOW()
		     , #{siteCd}
		     , #{frontGb}
		     , #{appYn}
		     , #{osType}
		     , AF_LINK_CD
		     , #{pageUrl}
		     , #{inflowRefer}
		     , #{ipAddr}
		     , #{jsessionId}
		FROM   TB_AF_LINK
		WHERE  AF_LINK_CD = #{afLinkCd}
		AND    USE_YN = 'Y'
	</insert>
	
	<!-- 웹제휴채널명 조회 -->
	<select id="getAfLinkName" parameterType="String" resultType="String">
		/* TsfCommon.getAfLinkName */
		SELECT AF_LINK_NM
		FROM   TB_AF_LINK
		WHERE  AF_LINK_CD = #{afLinkCd}
	</select>
	
	<!-- 유효한 웹제휴채널 여부 조회 -->
	<select id="getAvailableAfLinkYn" parameterType="String" resultType="String">
		/* TsfCommon.getAvailableAfLinkYn */
		SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
		FROM   TB_AF_LINK
		WHERE  AF_LINK_CD = #{afLinkCd}
		AND    USE_YN = 'Y'
	</select>
	
	<!-- 주문배송지정보수정 -->
	<update id="updateDeliverAddr" parameterType="Order">
		/* TsfCommon.updateDeliverAddr */
		UPDATE TB_DELIVERY_ADDR
		SET    RECIP_NM             = #{recipNm}
		     , RECIP_PHNNO          = #{recipPhnno}
		     , RECIP_ZIPCODE        = #{recipZipcode}
		     , RECIP_BASE_ADDR      = #{recipBaseAddr}
		     , RECIP_DTL_ADDR       = #{recipDtlAddr}
		     , DELV_MEMO            = #{delvMemo}
		     , UPD_NO               = #{custNo}
		     , UPD_DT               = NOW()
		WHERE  1=1
		AND    DELV_ADDR_SQ         = #{delvAddrSq}
	</update>
	
	<!-- 주문배송지메모정보수정 -->
	<update id="updateDeliverAddrDelvMemo" parameterType="Order">
		/* TsfCommon.updateDeliverAddrDelvMemo */
		UPDATE TB_DELIVERY_ADDR
		SET    DELV_MEMO            = #{delvMemo}
		     , UPD_NO               = #{custNo}
		     , UPD_DT               = NOW()
		WHERE  1=1
		AND    DELV_ADDR_SQ         = #{delvAddrSq}
	</update>
	
	<!-- 검색엔진 연동 여부 -->
	<select id="getSearchEngineSyncYn" resultType="String">
		/* TsfCommon.getSearchEngineSyncYn */
		SELECT UPPER(IFNULL(USE_YN,'N')) AS SYNC_YN
		FROM   TB_COMMON_CODE
		WHERE  CD_GB = 'G085'
		AND    CD = 'SEARCHSYNCYN'
	</select>

	<select id="getSupplyCompanyCount" resultType="int">
		/* TsfCommon.getSupplyCompanyCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_SUPPLY_COMPANY SC
		     , TB_SUPPLY_VENDOR SV
		WHERE  SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
		AND    SC.USE_YN = 'Y'
	</select>

	<select id="getSupplyCompanyList" parameterType="SupplyCompany" resultType="SupplyCompany">
		/* TsfCommon.getSupplyCompanyList */
		SELECT SUPPLY_COMP_CD
		     , SUPPLY_COMP_NM
		     , OWNER_NM
		     , NUMB
		FROM (
		      SELECT SC.SUPPLY_COMP_CD       /*공급업체코드*/
		           , SC.SUPPLY_COMP_NM       /*공급업체명*/
		           , SV.OWNER_NM             /*대표자명*/
		           , ROW_NUMBER() OVER(ORDER BY SV.REG_DT DESC)   AS NUMB
		      FROM   TB_SUPPLY_COMPANY SC
		           , TB_SUPPLY_VENDOR SV
		      WHERE  SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
		      AND    SC.USE_YN = 'Y'
		) ORIGINAL
		WHERE NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</select>

</mapper>