<?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.batch.biz.dao.TsbWmsGoodsDao">
	
	<!-- wms 실측 사이즈 목록   -->
	<select id="getWmsMeasurementList"  resultType="GoodsIfMeasurement">
		/* TsbWmsGoods.getWmsMeasurementList */
		SELECT SKUCODE
		     , PRODUCTNO
		     , PRODUCTCODE
		     , TYPECD
		     , WASHINGMETHOD
		     , VALUE1
		     , VALUE2
		     , VALUE3
		     , VALUE4
		     , VALUE5
		     , MEMO
		     , CONVERT(CHAR(19),DATEINSERTED,120) AS DATEINSERTED  /* yyyy-mm-dd hh:mi:ss */
		     , CONVERT(CHAR(19),DATELASTMODIFIED,120) AS DATELASTMODIFIED 
		     , ISUSE
		     , USERLASTMODIFIED
		FROM iSTYLE24_WmsIf.dbo.TB_IF_MEASUREMENT
		WHERE 1 = 1 
		<choose>
		    <when test='jobdate != null and jobdate != ""'>
		AND CONVERT(CHAR(8),DATELASTMODIFIED,112) = #{jobdate}
		    </when>
		    <otherwise>
		AND CONVERT(CHAR(8),DATELASTMODIFIED,112) = CONVERT(CHAR(8),DATEADD(day,-1, GETDATE()),112)
		    </otherwise>
		</choose>
	</select>
	
	<!-- wms 입고 목록   -->
	<select id="getWmsIncomelotList"  parameterType="String" resultType="GoodsIfIncomelot">
		/* TsbWmsGoods.getWmsIncomelotList */
		SELECT LOTNO
		     , PURCHASENO
		     , PROVIDERNO
		     , PROVIDERNAME
		     , BRANDNO
		     , BRANDNAME
		     , CONVERT(CHAR(19),DATEINCOME,120) AS DATEINCOME  /* yyyy-mm-dd hh:mi:ss */
		FROM iSTYLE24_WmsIf.dbo.TB_IF_INCOMELOT
		WHERE 1 = 1 
		<choose>
		    <when test='jobdate != null and jobdate != ""'>
		AND CONVERT(CHAR(8),DATEINCOME,112) = #{jobdate}
		    </when>
		    <otherwise>
		AND CONVERT(CHAR(8),DATEINCOME,112) = CONVERT(CHAR(8),DATEADD(day,-1, GETDATE()),112)
		    </otherwise>
		</choose>
	</select>
	
	<!-- wms 입고 상품목록   -->
	<select id="getWmsIncomelotitemList" parameterType="String"  resultType="GoodsIfIncomelotitem">
		/* TsbWmsGoods.getWmsIncomelotitemList */
		SELECT A.LOTNO
		     , A.ITEMNO
		     , A.WMSITEMNO
		     , CONVERT(CHAR(19),A.DATEINCOME,120) AS DATEINCOME  /* yyyy-mm-dd hh:mi:ss */
		     , A.PRODUCTNO
		     , A.PRODUCTCODE
		     , A.PRODUCTNAME
		     , A.SKUCODE
		     , A.NORMALQTY
		     , A.BROKENQTY
		     , A.TOTALQTY
		     , B.SKUMODELNO
		FROM iSTYLE24_WmsIf.dbo.TB_IF_INCOMELOTITEM A
		LEFT OUTER JOIN iSTYLE24_WmsIf.dbo.TB_IF_PRODUCTSKU B ON A.PRODUCTNO  = B.PRODUCTNO 
		                                                      AND A.SKUCODE = B.SKUCODE
		WHERE 1 = 1 
		<choose>
		    <when test='jobdate != null and jobdate != ""'>
		AND CONVERT(CHAR(8),A.DATEINCOME,112) = #{jobdate}
		    </when>
		    <otherwise>
		AND CONVERT(CHAR(8),A.DATEINCOME,112) = CONVERT(CHAR(8),DATEADD(day,-1, GETDATE()),112)
		    </otherwise>
		</choose>
	</select>

	<!--WMS 업체정보 송신-->
	<insert id="saveWmsProvider" parameterType="IfProvider" >
		/* TsbWmsGoods.saveWmsProvider */
		MERGE iSTYLE24_WmsIf.dbo.TB_IF_PROVIDER 
		      USING (SELECT 'AA' AS DUAL) AS B
		         ON (PROVIDERNO = #{providerno})
		      WHEN MATCHED THEN
		           UPDATE SET
		                   PROVIDERNAME = #{providername}
		                 , CATEGORYTYPECD = #{categorytypecd}
		                 , DISTRIBUTIONCD = #{distributioncd}
		                 , CHARGENAME = #{chargename}
		                 , CHARGECELLNUM = #{chargecellnum}
		                 , STATUSCD = #{statuscd}
		                 , STOCKMGMTTYPECD = #{stockmgmttypecd}
		                 , SUPPLY_COMP_CD = #{supplyCompCd}
		                 , DATElASTMODIFIED = GETDATE()
		      WHEN NOT MATCHED THEN
		           INSERT (
		                   PROVIDERNO
		                 , VENDORNO  
		                 , PROVIDERNAME
		                 , CATEGORYTYPECD
		                 , DISTRIBUTIONCD
		                 , CHARGENAME
		                 , CHARGECELLNUM
		                 , STATUSCD
		                 , STOCKMGMTTYPECD
		                 , SUPPLY_COMP_CD
		                 , DATECREATED
		                 , DATElASTMODIFIED
		           )
		           VALUES (
		                  #{providerno}
		                , #{vendorno}
		                , #{providername}
		                , #{categorytypecd}
		                , #{distributioncd}
		                , #{chargename}
		                , #{chargecellnum}
		                , #{statuscd}
		                , #{stockmgmttypecd}
		                , #{supplyCompCd}
		                , GETDATE()
		                , GETDATE()
		           );
	</insert>
	
	<!--WMS 브랜드정보 송신-->
	<insert id="saveWmsBrand" parameterType="IfBrand" >
		/* TsbWmsGoods.saveWmsBrand */
		MERGE iSTYLE24_WmsIf.dbo.TB_IF_BRAND
		      USING (SELECT 'AA' AS DUAL) AS B
		         ON (BRANDNO = #{brandno})
		      WHEN MATCHED THEN
		           UPDATE SET
		                   BRANDNAME = #{brandname}
		                 , STATUSCD = #{statuscd}
		                 , BRAND_CD = #{brandCd}
		      WHEN NOT MATCHED THEN
		           INSERT (
		                   BRANDNO
		                 , BRANDNAME
		                 , STATUSCD
		                 , BRAND_CD
		                 , DATECREATED
		           )
		           VALUES (
		                  #{brandno}
		                , #{brandname}
		                , #{statuscd}
		                , #{brandCd}
		                , #{datecreated}
		           );
	</insert>
	
	<!--WMS 브랜드업체관계정보 송신-->
	<insert id="saveWmsBrandProviderXref" parameterType="IfBrand" >
		/* TsbWmsGoods.saveWmsBrandProviderXref */
		MERGE INTO iSTYLE24_WmsIf.dbo.TB_IF_BRANDPROVIDERXREF
		      USING (SELECT 'AA' AS DUAL) AS B
		         ON (BRANDNO = #{brandno} AND PROVIDERNO = #{providerno})
		      WHEN MATCHED THEN
		           UPDATE SET
		                   PROVIDERNO = #{providerno}
		                 , BRAND_CD = #{brandCd}
		                 , SUPPLY_COMP_CD = #{supplyCompCd}
		                 , DATEUPDATEED = GETDATE()
		      WHEN NOT MATCHED THEN
		           INSERT (
		                   BRANDNO
		                 , PROVIDERNO
		                 , BRAND_CD
		                 , SUPPLY_COMP_CD
		                 , DATECREATED
		                 , DATEUPDATEED
		           )
		           VALUES (
		                  #{brandno}
		                , #{providerno}
		                , #{brandCd}
		                , #{supplyCompCd}
		                , GETDATE()
		                , GETDATE()
		           );
	</insert>
	
	<!--WMS 상품정보 송신-->
	<update id="saveWmsProduct" parameterType="IfProduct" >
		/* TsbWmsGoods.saveWmsProduct */
		UPDATE iSTYLE24_WmsIf.dbo.TB_IF_PRODUCT
		SET PRODUCTNAME = #{productname}
		  , BRANDNO = #{brandno}
		  , BRANDNAME = #{brandname}
		  , PROVIDERNO = #{providerno}
		  , STATUSCD = #{statuscd}
		  , VENDORPRODUCTCODE = #{vendorproductcode}
		  , GOODS_CD = #{goodsCd}
		  , BRAND_CD = #{brandCd}
		  , SUPPLY_COMP_CD = #{supplyCompCd}
		  , SYS_IMG_NM = #{sysImgNm}
		  , DATElASTMODIFIED = GETDATE()
		WHERE PRODUCTNO = #{productno}
	</update>
	
	<!-- WMS 재고 테이블 조회   -->
	<select id="getWmsIfProductSukList"  resultType="IfProductSku">
		/* TsbWmsGoods.getWmsIfProductSukList */
		SELECT C.SKUCODE
		     , C.SKUMODELNO
		     , C.PRODUCTNO
		     , C.PRODUCTCODE
		     , C.OPTION1
		     , C.OPTION2
		     , C.OPTION3
		     , C.SELLINGSTOCKAMOUNT
		     , C.SELLINGSTOCKTYPECD
		     , C.LIMITSTOCKAMOUNT
		     , C.VENDORSKUMODELNO
		     , C.VENDORSKUCODE
		     , C.ISVIRTUALSTOCK
		     , C.DATEVIRTUALSTOCK
		     , C.SKUCODE88
		FROM iSTYLE24_WMSIF.DBO.TB_IF_PROVIDER P
		INNER JOIN iSTYLE24_WMSIF.DBO.TB_IF_BRANDPROVIDERXREF A ON P.PROVIDERNO = A.PROVIDERNO
		INNER JOIN iSTYLE24_WmsIf.dbo.TB_IF_PRODUCT B ON A.BRANDNO  = B.BRANDNO 
		INNER JOIN iSTYLE24_WmsIf.dbo.TB_IF_PRODUCTSKU C ON B.PRODUCTNO  = C.PRODUCTNO 
		WHERE P.DISTRIBUTIONCD = '자사'
		-- AND B.PRODUCTNO  IN (16561091 ,16561090)   -- 테스트용
	</select>
	
		<!-- WMS view 재고 테이블 조회   -->
	<select id="getWmsStockList"  resultType="WmsStock">
		/* TsbWmsGoods.getWmsStockList */
		SELECT PRODUCT_NO 
		     , SKU_CODE
		     , LOC_QTY
		     , PICKING_QTY
		     , EOUT_PICKING_QTY
		FROM (
		     SELECT PRODUCT_NO
		          , SKU_CODE
		          , SUM(LOC_QTY) AS LOC_QTY
		          , SUM(PICKING_QTY) AS PICKING_QTY 
		          , SUM(EOUT_PICKING_QTY) AS EOUT_PICKING_QTY
		     FROM iSTYLE24_WMS.DBO.VW_STOCK_BY_LOCCODE
		     GROUP BY PRODUCT_NO, SKU_CODE
		) Z
		WHERE 1 = 1
		AND LOC_QTY - PICKING_QTY - EOUT_PICKING_QTY > 0
	</select>
	
	<!--WMS 발주정보-->
	<select id="getWmsIfPurchaseorder" parameterType="IfPurchaseorder" resultType="IfPurchaseorder"  >
		/* TsbWmsGoods.getWmsIfPurchaseorder */
		SELECT MAX(PURCHASENO) AS PURCHASENO
		FROM iSTYLE24_WmsIf.dbo.TB_IF_PURCHASEORDER
		WHERE PROVIDERNO  = #{providerno}
	</select>
	
	<!--WMS 발주정보-->
	<insert id="createWmsIfPurchaseorder" parameterType="IfPurchaseorder" >
		/* TsbWmsGoods.createWmsIfPurchaseorder */
		INSERT INTO iSTYLE24_WmsIf.dbo.TB_IF_PURCHASEORDER (
		     PURCHASENO      
		   , PURCHASETITLE   
		   , PROVIDERNO      
		   , PROVIDERNAME    
		   , DISTRIBUTIONCD  
		   , TOTALPAYMENT    
		   , ISPAYMENT       
		   , TOTALPRODUCTQTY 
		   , TOTALSKUQTY     
		   , STATUSCD        
		   , DATEORDERED     
		   , DATEFIRSTINCOME 
		   , DATECOMPLETED   
		   )
		   VALUES (
		      (SELECT MAX(PURCHASENO) + 1 FROM iSTYLE24_WmsIf.dbo.TB_IF_PURCHASEORDER Z)
		    , #{purchasetitle}   
		    , #{providerno}      
		    , #{providername}    
		    , #{distributioncd}  
		    , #{totalpayment}    
		    , #{ispayment}       
		    , #{totalproductqty} 
		    , #{totalskuqty}     
		    , #{statuscd}        
		    , GETDATE() 
		    , GETDATE()
		    , GETDATE()
		   )
	</insert>
	
	<!--WMS 발주정보 변경-->
	<update id="updateWmsIfPurchaseorder" parameterType="IfPurchaseorder" >
		/* TsbWmsGoods.updateWmsIfPurchaseorder */
		UPDATE iSTYLE24_WmsIf.dbo.TB_IF_PURCHASEORDER 
		SET DISTRIBUTIONCD =  #{distributioncd}
		  , STATUSCD = #{statuscd}
		WHERE PURCHASENO = #{purchaseno}  
	</update>	
	
	<!--WMS 발주정보 - 업체/브랜드 송신-->
	<insert id="saveWmsIfPurchaseorderbrandXref" parameterType="IfPurchaseorderbrandXref" >
		/* TsbWmsGoods.saveWmsIfPurchaseorderbrandXref */
		MERGE INTO iSTYLE24_WmsIf.dbo.TB_IF_PURCHASEORDERBRAND_XREF
		      USING (SELECT 'AA' AS DUAL) AS B
		         ON (PURCHASENO = #{purchaseno} AND BRANDNO = #{brandno})
		      WHEN MATCHED THEN
		           UPDATE SET
		                   BRANDSTATUSCD = #{brandstatuscd}
		      WHEN NOT MATCHED THEN
		           INSERT (
		                   BRANDNO
		                 , BRANDNAME
		                 , MGMTBRANDNAME
		                 , BRANDSTATUSCD
		                 , PURCHASENO
		           )
		           VALUES (
		                  #{brandno}
		                , #{brandname}
		                , #{brandname}
		                , #{brandstatuscd}
		                , #{purchaseno}
		           );
	</insert>
	
	
	<!-- wms 입고 상품목록 PRODUCTSKU  -->
	<select id="getWmsProductskuList" parameterType="String"  resultType="GoodsIfProductsku">
		/* TsbWmsGoods.getWmsProductskuList */
		SELECT 
			B.SKUCODE
			, B.SKUMODELNO
			, B.PRODUCTNO
			, B.PRODUCTCODE
			, B.OPTION1
			, B.OPTION2
			, B.OPTION3
			, B.SELLINGSTOCKAMOUNT
			, B.SELLINGSTOCKTYPECD
			, B.LIMITSTOCKAMOUNT
			, B.VENDORSKUMODELNO
			, B.VENDORSKUCODE
			, B.ISVIRTUALSTOCK
			, B.DATEVIRTUALSTOCK
			, B.SKUCODE88
		FROM iSTYLE24_WmsIf.dbo.TB_IF_INCOMELOTITEM A
		LEFT OUTER JOIN iSTYLE24_WmsIf.dbo.TB_IF_PRODUCTSKU B ON A.PRODUCTNO  = B.PRODUCTNO 
		                                                      AND A.SKUCODE = B.SKUCODE
		WHERE 1 = 1 
		<choose>
		    <when test='jobdate != null and jobdate != ""'>
		AND CONVERT(CHAR(8),A.DATEINCOME,112) = #{jobdate}
		    </when>
		    <otherwise>
		AND CONVERT(CHAR(8),A.DATEINCOME,112) = CONVERT(CHAR(8),DATEADD(day,-1, GETDATE()),112)
		    </otherwise>
		</choose>
	</select>
	
</mapper>
