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

	<!-- WMS회수목록 건수 -->
	<select id="getWmsWithdrawListCount" parameterType="WmsWithdraw" resultType="int">
		/* TsaWmsWithdraw.getWmsWithdrawListCount */
		SELECT COUNT(*) AS TOTCNT
		  FROM (		
				SELECT 
					  A.RecallNo			-- 반품 번호 
					, B.RecallItemNo		-- 반품 항목 번호 
				  FROM ISTYLE24_WMSIF.DBO.TB_IF_RECALLDELIVERY A , ISTYLE24_WMSIF.DBO.TB_IF_RECALLDELIVERYITEM B
				 WHERE A.RECALLNO = B.RECALLNO 
					<if test='customername != null and customername != ""'> -- 주문자
					AND   A.CustomerName = #{customername}
					</if>
					<if test='orderno != null and orderno != ""'>  -- 주문번호 
					AND   A.OrderNo  = #{orderno}
					</if>
					<if test='statuscd != null and statuscd != ""'>  -- 입고상태
					AND   B.StatusCd  = #{statuscd}
					</if>
	
					<if test='recalltypecd != null and recalltypecd != ""'>  -- 반품타입
					AND   A.RecallTypeCd = #{recalltypecd}
					</if>
					<if test='isdeleted != null and isdeleted != ""'>  -- 삭제여부 1삭제 
					AND    A.IsDeleted = #{isdeleted}
					</if>				
					<if test='stDate != null and stDate != ""'> -- 회수요청일 
					AND   A.DateCreated <![CDATA[>= ]]>  CONVERT(datetime,#{stDate}) 
					</if>
					<if test='edDate != null and edDate != ""'>
					AND   A.DateCreated <![CDATA[<]]> CONVERT(datetime,#{edDate})+1 
					</if>
			) A

	</select>
		
	<!-- WMS회수목록 -->
	<select id="getWmsWithdrawList" parameterType="WmsWithdraw" resultType="WmsWithdraw">
		/* TsaWmsWithdraw.getWmsWithdrawList */
		SELECT Z.*
		, CASE WHEN Z.RecallTypeCd ='10' THEN '반품' ELSE '교환' END AS RecallTypeCdNm
		FROM (
		    SELECT A.*,  ROW_NUMBER() OVER (ORDER BY RecallNo DESC, RecallItemNo ) as RNUM 
		    FROM (
		    
				SELECT 
					  A.RecallNo			-- 반품 번호 
					, B.RecallItemNo		-- 반품 항목 번호 X
					, A.OrderExceptionNo	-- 주문변경번호  
					, A.RecallTypeCd		-- 반품타입
					, A.Receiver			-- 받는자
					, A.ReceiverTelNum		-- 받는자 전화번호
					, A.ReceiverCellNum		-- 받는자 핸드폰 번호
					, A.DeliveryPostalCode	-- 우편 번호
					, A.DeliveryAddr1		-- 기본 주소
					, A.DeliveryAddr2		-- 상세 주소
					, A.OrderNo				-- 주문 번호
					, A.OrderGUID			-- 주문 고유 번호
					, A.CustomerName		-- 주문자
					, A.LoginId				-- 아이디
					, A.CSMemo				-- CS 메모
					, CONVERT(char(10), A.DateCSMemoUpdated, 23) AS DateCSMemoUpdated	-- CS 메모 변경일
					, A.WMSMemo				-- WMS 메모
					, CONVERT(char(10), A.DateWMSMemoUpdated, 23) AS DateWMSMemoUpdated	-- WMS 메모 변경일
					, CONVERT(char(10), A.DateCreated, 23) AS DateCreated			    -- 생성일 -- 회수요청일
					, A.IsDeleted			-- 삭제 여부  
					, A.DateDeleted			-- 삭제일  x
					, A.DeliveryAddrNew		-- 도로명 주소
					, B.ProductNo			-- 상품 번호
					, B.ProductCode			-- 상품 코드
					, B.ProductName			-- 상품명
					, B.SKUCode				-- 옵션 코드
					, B.SKUValue			-- 옵션 내용
					, B.RecallQty			-- 수량
					, B.StatusCd			-- 상태
					, CONVERT(char(10), B.DateCompleted, 23) AS DateCompleted		-- 완료일
					, B.LogisticsName		-- 택배사 명
					, B.RecallInvoiceNo		-- 회수송장번호
					, B.InvoiceNo			-- 원운송장번호
					, B.OrderDtlNo			-- 주문상세번호
				  FROM ISTYLE24_WMSIF.DBO.TB_IF_RECALLDELIVERY A , ISTYLE24_WMSIF.DBO.TB_IF_RECALLDELIVERYITEM B
				 WHERE A.RECALLNO = B.RECALLNO 
					<if test='customername != null and customername != ""'> -- 주문자
					AND   A.CustomerName = #{customername}
					</if>
					<if test='orderno != null and orderno != ""'>  -- 주문번호
					AND   A.OrderNo  = #{orderno}
					</if>
					<if test='statuscd != null and statuscd != ""'>  -- 입고상태
					AND   B.StatusCd  = #{statuscd}
					</if>
	
					<if test='recalltypecd != null and recalltypecd != ""'>  -- 반품타입
					AND   A.RecallTypeCd = #{recalltypecd}
					</if>
					<if test='isdeleted != null and isdeleted != ""'>  -- 삭제여부 1삭제 
					AND    A.IsDeleted = #{isdeleted}
					</if>				
					<if test='stDate != null and stDate != ""'> -- 회수요청일 
					AND   A.DateCreated <![CDATA[>= ]]>  CONVERT(datetime,#{stDate}) 
					</if>
					<if test='edDate != null and edDate != ""'>
					AND   A.DateCreated <![CDATA[<]]> CONVERT(datetime,#{edDate})+1 
					</if>
		    ) A
		)Z WHERE RNUM BETWEEN  #{pageable.startRow} AND #{pageable.endRow}
	</select>

	<!-- 출고처지정 지연재고 등록     -->
	<insert id="insertWmsTbIfDelifixQty" parameterType="Delivery" >
		/*TsaWmsWithdraw.insertWmsTbIfDelifixQty*/
		INSERT INTO iSTYLE24_WmsIf.dbo.TB_IF_DELIFIX_QTY
		(
			  OrderNo
			, OrderDtlNo
			, SKUCode
			, ProductNo
			, Qty
			, DateCreated
		)
		VALUES
		(
			  #{ordNo}
			, #{ordDtlNo}
			, #{optCd}
			, #{productNo}
			, #{ordQty}
			, GETDATE()
		)
	</insert>	
	
	<!-- 출고처지정 지연재고 삭제     -->
	<delete id="deleteWmsTbIfDelifixQty" parameterType="Delivery" >
		/*TsaWmsWithdraw.deleteWmsTbIfDelifixQty*/
		DELETE FROM iSTYLE24_WmsIf.dbo.TB_IF_DELIFIX_QTY 
		WHERE OrderNo    = #{ordNo}
		  AND OrderDtlNo = #{ordDtlNo}
	</delete>		
	
	<!-- 재회수지시      -->
	<insert id="updateReRecallOrder" parameterType="Withdraw" >
		/*TsaWmsWithdraw.updateReRecallOrder*/
		UPDATE ISTYLE24_WMSIF.DBO.TB_IF_RECALLDELIVERY SET 
		  IsDeleted   = 1
		, DateDeleted = GETDATE() 
		WHERE OrderExceptionNo  =  #{ordChgSq}
		  AND OrderNo           =  #{ordNo}
          and IfStat in ('0','1')
          and IsDeleted = 0
          		  
	</insert>	
	
	<!-- 회수예외 수정 -H   -->
	<update id="updateWmsIfWithdrawExcList" parameterType="WithdrawExc" >
		/*TsbWmsDelivery.updateWmsIfWithdrawExcList*/
		UPDATE iSTYLE24_WmsIf.DBO.TB_IF_RecallException SET 
			   IfStat = #{ifstat}
			 , IfDate = GETDATE()
			 <if test='ifstat == "2"'>
			 , CSenCloseFee = #{csAddFee}
			 , CSMemo       = #{csMemo}
			 </if>
		WHERE RecallExceptionNo = #{recallExceptionNo}
		
	</update>
		
	<!-- 회수예외 수정 -D   -->
	<update id="updateWmsIfWithdrawExcItemList" parameterType="WithdrawExc" >
		/*TsbWmsDelivery.updateWmsIfWithdrawExcItemList*/
		UPDATE iSTYLE24_WmsIf.DBO.TB_IF_RecallExceptionItem SET 
			   IfStat = #{ifstat}
			 , IfDate = GETDATE()
		WHERE RecallExceptionNo = #{recallExceptionNo}
	</update>
	
	<!-- 회수예외 수정 -M   -->
	<update id="updateWmsIfWithdrawExcMemoList" parameterType="WithdrawExc" >
		/*TsbWmsDelivery.updateWmsIfWithdrawExcMemoList*/
		UPDATE iSTYLE24_WmsIf.DBO.TB_IF_RecallExceptionMemo SET 
			   IfStat = #{ifstat}
			 , IfDate = GETDATE()
		WHERE RecallExceptionNo = #{recallExceptionNo}
	</update>
		
	
</mapper>