<?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.prolog.cs.biz.inbound.dao.rcv.RcvInstockLinesMapper">
	<resultMap id="mainMap"
		type="com.prolog.cs.biz.inbound.dto.rcv.RcvInstockLinesDTO">
		<result column="in_bound_line_id" property="inBoundLineId" />
		<result column="rcv_header_id" property="rcvHeaderId" />
		<result column="rcv_line_id" property="rcvLineId" />
		<result column="line_num" property="lineNum" />
		<result column="wh_locator_id" property="whLocatorId" />
		<result column="item_id" property="itemId" />
		<result column="piece_qty" property="pieceQty" />
		<result column="uom" property="uom" />
		<result column="uom_type" property="uomType" />
		<result column="conversion_rate" property="conversionRate" />
		<result column="odd_case_qty" property="oddCaseQty" />
		<result column="odd_piece_qty" property="oddPieceQty" />
		<result column="batch_id" property="batchId" />
		<result column="batch_num" property="batchNum" />
		<result column="batch_production_date"
			property="batchProductionDate" />
		<result column="batch_expired_date" property="batchExpiredDate" />
		<result column="vendor_batch_num" property="vendorBatchNum" />
		<result column="manufacturer_lot_num"
			property="manufacturerLotNum" />
		<result column="container_code" property="containerCode" />
		<result column="goods_case_code" property="goodsCaseCode" />
		<result column="comments" property="comments" />
		<result column="algorithm_type" property="algorithmType" />
		<result column="executor_user" property="executorUser" />
		<result column="stock_status" property="stockStatus" />
		<result column="task_id" property="taskId" />
		<result column="mtl_owner_id" property="mtlOwnerId" />
		<result column="warehouse_id" property="warehouseId" />
		<result column="src_bill_type" property="srcBillType" />
		<result column="src_header_id" property="srcHeaderId" />
		<result column="src_line_id" property="srcLineId" />
		<result column="asn_header_id" property="asnHeaderId" />
		<result column="asn_line_id" property="asnLineId" />
		<result column="CATEGORY_ID" property="categoryId" />
		<result column="wh_locator_code" property="whLocatorCode" />
		<result column="wh_locator_name" property="whLocatorName" />
		<result column="wh_area_category_code"
			property="whAreaCategoryCode" />
		<result column="wh_area_category_name"
			property="whAreaCategoryName" />
		<result column="wh_area_code" property="whAreaCode" />
		<result column="bldg_code" property="bldgCode" />

		<result column="wh_area_category_id"
			property="whAreaCategoryId" />
		<result column="inventory_status" property="inventoryStatus" />
		<result column="instock_label" property="instockLabel" />
		<result column="bldg_id" property="bldgId" />
		<result column="real_wh_locator_code"
			property="realWhLocatorCode" />
		<result column="real_wh_locator_id" property="realWhLocatorId" />
		<result column="real_wh_locator_name"
			property="realWhLocatorName" />

		<result column="ITEM_VOLUME" property="itemVolume" />
		<result column="ITEM_WEIGHT" property="itemWeight" />
		<result column="ITEM_HEALTH_TWOVOTEFLAG"
			property="itemHealthTwovoflag" />
		<result column="WAREHOUSE_NAME" property="warehouseName" />
		<result column="WAREHOUSE_AREA_NAME"
			property="warehouseAreaName" />
		<result column="ITEM_NUM" property="itemNum" />
		<result column="ITEM_NAME" property="itemName" />
		<result column="CATEGORY_NAME" property="categoryName" />
		<result column="ITEM_BARCODE" property="itemBarcode" />
		<!-- <result column="enterprise_id" property="enterpriseId"/> <result column="gmt_create" 
			property="gmtCreate"/> <result column="creator" property="creator"/> <result 
			column="creator_name" property="creatorName"/> <result column="gmt_modified" 
			property="gmtModified"/> <result column="modifier" property="modifier"/> 
			<result column="modifier_name" property="modifierName"/> -->
	</resultMap>
	<sql id="mainClomuns">
		t1.in_bound_line_id,
		t1.rcv_header_id,
		t1.rcv_line_id,
		t1.line_num,
		t1.wh_locator_id,

		t1.wh_locator_code,
		t1.wh_locator_name,
		t1.wh_area_category_code,
		t1.wh_area_category_name,
		t1.wh_area_code,
		t1.bldg_code,

		t1.wh_area_category_id,
		t1.inventory_status,
		t1.instock_label,
		t1.bldg_id,
		t1.real_wh_locator_code,
		t1.real_wh_locator_id,
		t1.real_wh_locator_name,

		t1.item_id,
		t1.piece_qty,
		t1.uom,
		t1.uom_type,
		t1.conversion_rate,
		t1.odd_case_qty,
		t1.odd_piece_qty,
		t1.batch_id,
		t1.batch_num,
		t1.batch_production_date,
		t1.batch_expired_date,
		t1.vendor_batch_num,
		t1.manufacturer_lot_num,
		t1.container_code,
		t1.goods_case_code,
		t1.comments,
		t1.algorithm_type,
		t1.executor_user,
		t1.stock_status,
		t1.task_id,
		t1.mtl_owner_id,
		t1.warehouse_id,
		t1.src_bill_type,
		t1.src_header_id,
		t1.src_line_id,
		t1.asn_header_id,
		t1.asn_line_id,
		t1.ENTERPRISE_ID as enterpriseId,
		t1.GMT_CREATE as gmtCreate,
		t1.CREATOR,
		t1.CREATOR_NAME as creatorName,
		t1.GMT_MODIFIED as gmtModified,
		t1.MODIFIER,
		t1.MODIFIER_NAME as
		modifierName,
	</sql>
	<select id="findList" resultMap="mainMap">
		select
		distinct
		<include refid="mainClomuns"></include>
		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		t2.ITEM_IS_PIECE_OR_CASE as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.ITEM_HEALTH_TWOVOTEFLAG,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_ID,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.specification as specification,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t3.ITEM_HEALTH_PRODUCE_LICENSE as itemHealthProduceLicense,
		t3.ITEM_HEALTH_STORE_CATEGORY as itemHealthStoreCategory,
		t3.ITEM_IS_RECHECK as itemIsRecheck,
		t3.IS_INSTRUMENT as isInstrument,
		t3.ITEM_IS_IMPORTED as itemIsImported
		FROM
		wms_in_rcv_instock_lines t1
		LEFT JOIN
		WMS_IN_RCV_LINES t2
		on t1.rcv_line_id = t2.RCV_LINE_ID
		LEFT
		JOIN
		WMS_IN_RCV_LINES_PROFILES t3
		on t1.rcv_line_id = t3.RCV_LINE_ID
		where
		1=1
		<if test="enterpriseId != null  and enterpriseId !=''">
			and t1.enterprise_id = #{enterpriseId}
		</if>
		<if test="warehouseId != null  and warehouseId !=''">
			and t1.warehouse_id = #{warehouseId}
		</if>
		<if test="mtlOwnerId != null  and mtlOwnerId !=''">
			and t1.mtl_owner_id = #{mtlOwnerId}
		</if>
		<if test="rcvHeaderId != null  and rcvHeaderId !=''">
			and t1.rcv_header_id = #{rcvHeaderId}
		</if>
		<if test="rcvLineId != null  and rcvLineId !=''">
			and t1.rcv_line_id = #{rcvLineId}
		</if>

		<if test="itemId != null  and itemId !=''">
			and t1.item_id = #{itemId}
		</if>
		<if test="stockStatus != null  and stockStatus !=''">
			and t1.stock_status = #{stockStatus}
		</if>
		<if test="includeIds != null and includeIds.size() >0 ">
			and t1.in_bound_line_id in
			<foreach collection="includeIds" item="item" open="("
				separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="excludeIds != null and excludeIds.size() >0 ">
			and t1.in_bound_line_id not in
			<foreach collection="excludeIds" item="item" open="("
				separator="," close=")">
				#{item}
			</foreach>
		</if>
		order by t1.gmt_modified
		desc
	</select>

	<select id="findByPkId" resultMap="mainMap"
		parameterType="String">
		select
		<include refid="mainClomuns"></include>
		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		t2.ITEM_IS_PIECE_OR_CASE as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.ITEM_HEALTH_TWOVOTEFLAG,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t3.ITEM_NUM,
		t3.CATEGORY_ID,
		t3.ITEM_NAME,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.specification as specification,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t3.ITEM_HEALTH_PRODUCE_LICENSE as itemHealthProduceLicense,
		t3.ITEM_HEALTH_STORE_CATEGORY as itemHealthStoreCategory,
		t3.ITEM_IS_RECHECK as itemIsRecheck,
		t3.IS_INSTRUMENT as isInstrument,
		t3.ITEM_IS_IMPORTED as itemIsImported
		FROM
		wms_in_rcv_instock_lines t1
		LEFT JOIN
		WMS_IN_RCV_LINES t2
		on t1.rcv_line_id = t2.RCV_LINE_ID
		LEFT
		JOIN
		WMS_IN_RCV_LINES_PROFILES t3
		on t1.rcv_line_id = t3.RCV_LINE_ID
		where
		t1.in_bound_line_id = #{pkId}
	</select>
	<select id="getReport" resultMap="mainMap">
		select
		distinct
		<include refid="mainClomuns"></include>
		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		t2.ITEM_IS_PIECE_OR_CASE as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.ITEM_HEALTH_TWOVOTEFLAG,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_ID,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.specification as specification,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t3.ITEM_HEALTH_PRODUCE_LICENSE as itemHealthProduceLicense,
		t3.ITEM_HEALTH_STORE_CATEGORY as itemHealthStoreCategory,
		t3.ITEM_IS_RECHECK as itemIsRecheck,
		t3.IS_INSTRUMENT as isInstrument,
		t3.ITEM_IS_IMPORTED as itemIsImported,

		t2.show_production_date as showProductionDate,
		t2.show_expired_date as showExpiredDate,
		t2.item_zjm as itemZjm,
		t2.approval_number as
		approvalNumber,
		t2.production_place as productionPlace,
		t3.ITEM_HEALTH_REGISTER_NBR as itemHealthRegisterNbr,
		t3.manufacturer_name as manufacturerName

		FROM
		wms_in_rcv_instock_lines t1
		LEFT JOIN
		WMS_IN_RCV_LINES t2
		on
		t1.rcv_line_id = t2.RCV_LINE_ID
		LEFT JOIN
		WMS_IN_RCV_LINES_PROFILES t3
		on t1.rcv_line_id = t3.RCV_LINE_ID
		where
		1=1
		<if test="rcvHeaderId != null  and rcvHeaderId !=''">
			and t1.rcv_header_id = #{rcvHeaderId}
		</if>
		<if test="itemId != null  and itemId !=''">
			and t1.item_id = #{itemId}
		</if>
		<if test="itemCode != null  and itemCode !=''">
			and UPPER(t3.item_num) like
			concat(CONCAT('%',UPPER(#{itemCode})),'%')
		</if>
		<if test="itemName != null  and itemName !=''">
			and UPPER(t3.item_name) like
			concat(concat('%',UPPER(#{itemName})),'%')
		</if>
		<if test="itemZjm != null  and itemZjm !=''">
			and UPPER(t2.ITEM_ZJM) like CONCAT(CONCAT('%',UPPER(#{itemZjm})),'%')
		</if>
		<if test="manufacturerLotNum != null  and manufacturerLotNum !=''">
			and UPPER(t1.manufacturer_lot_num) like
			CONCAT(concat('%',UPPER(#{manufacturerLotNum})),,'%')
		</if>
		order by t1.gmt_modified
		desc
	</select>
	<select id="getMaxLineNum" parameterType="String"
		resultType="java.lang.Long">
		SELECT coalesce(MAX(coalesce(LINE_NUM, 1)), 1) FROM
		WMS_IN_RCV_INSTOCK_LINES WHERE RCV_HEADER_ID = #{rcvHeaderId}
	</select>


	<select id="findEisNum" resultMap="mainMap">
		SELECT
		s1.*
		FROM
		wms_in_rcv_instock_lines s1
		LEFT JOIN wms_in_task_instock_detail s2 ON s1.TASK_ID = s2.TASK_INBOUND_ID
		WHERE
		s2.`STATUS` = '1'
		AND s2.warehouse_id = #{wareHouseId}
		AND s2.mtl_owner_id = #{oweId}
		AND s2.TASK_INBOUND_ID IN 
		<foreach collection="msgList" item="msgId" open="("
			separator="," close=")">
			#{msgId}
		</foreach>
	</select>

	<select id="findListNew" resultMap="mainMap">
		select
		t4.asn_auto_bill_num as asnNo,
		t4.auto_bill_num as rcvNo,
		t5.auto_bill_num as rqcNo,
		t4.rcv_type as rcvType,
		coalesce(t6.vendor_name,t6.customer_name) as shipperName,
		t2.item_zjm,
		t3.item_name,
		t3.item_num,
		t0.uom as uom,
		t0.conversion_rate,
		coalesce(t7.stock_status,t2.stock_status) as stock_status,
		t0.manufacturer_lot_num,
		t2.show_production_date as showProductionDate,
		t2.show_expired_date as showExpiredDate,
		coalesce(t1.piece_qty,t0.piece_qty) as piece_qty,
		coalesce(t1.container_code,t0.container_code)  as container_code,
		t0.wh_area_category_code as wh_area_category_code,
		coalesce(t1.wh_area_code,t0.wh_area_code) as wh_area_code,
		coalesce( t1.REAL_WH_LOCATOR_CODE, t1.wh_locator_code) AS wh_locator_code,
		t0.wh_locator_code as whLocatorCodeRecommend,
		t1.creator_name as creatorName,
		t1.gmt_create as gmtCreateTime,
		t0.gmt_create as gmtCreate,
        t0.task_inbound_id as task_id,
		t1.in_bound_line_id,
		t6.mtl_owner_name as mtlOwnerName,
		coalesce(t6.vendor_name,t6.customer_name) as vendorName,
		t3.specification as specification,
		t3.manufacturer_name as manufacturerName,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t2.APPROVAL_NUMBER as approvalNumber,
		t0.warehouse_id
		<!--<include refid="mainClomuns"></include>
		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		t2.ITEM_IS_PIECE_OR_CASE as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.ITEM_HEALTH_TWOVOTEFLAG,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_ID,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t3.ITEM_HEALTH_PRODUCE_LICENSE as itemHealthProduceLicense,
		t3.ITEM_HEALTH_STORE_CATEGORY as itemHealthStoreCategory,
		t3.ITEM_IS_RECHECK as itemIsRecheck,
		t3.IS_INSTRUMENT as isInstrument,
		t3.ITEM_IS_IMPORTED as itemIsImported-->
		FROM
		wms_in_rcv_instock_lines t1
		LEFT JOIN
		wms_in_task_instock_detail t0
		on t0.task_inbound_id = t1.task_id
		JOIN
		WMS_IN_RCV_LINES t2
		on t0.rcv_line_id = t2.RCV_LINE_ID
		JOIN
		WMS_IN_RCV_LINES_PROFILES t3
		on t0.rcv_line_id = t3.RCV_LINE_ID
		JOIN
		wms_in_rcv_headers t4
		on t4.rcv_header_id = t0.rcv_header_id
		LEFT JOIN
		WMS_IN_RQC_HEADERS t5
		ON t5.rcv_header_id = t4.rcv_header_id
		JOIN
		wms_in_rcv_headers_profiles t6
		ON t4.rcv_header_id =t6.rcv_header_id
		LEFT JOIN
		wms_in_rqc_lines t7
		on t7.rqc_line_id = t0.ref_line_id
		where
		1=1
		<if test="enterpriseId != null  and enterpriseId !=''">
			and t0.enterprise_id = #{enterpriseId}
		</if>
		<if test="warehouseId != null  and warehouseId !=''">
			and t0.warehouse_id = #{warehouseId}
		</if>
		<if test="mtlOwnerId != null  and mtlOwnerId !=''">
			and t0.mtl_owner_id = #{mtlOwnerId}
		</if>
		<if test="rcvType != null  and rcvType !=''">
			and t4.rcv_type = #{rcvType}
		</if>
		<if test="asnNo != null  and asnNo !=''">
			and UPPER(t4.asn_auto_bill_num) like
			CONCAT(CONCAT('%',UPPER(#{asnNo})),'%')
		</if>
		<if test="rcvNo != null  and rcvNo !=''">
			and UPPER(t4.auto_bill_num) like
			CONCAT(CONCAT('%',UPPER(#{rcvNo})),'%')
		</if>
		<if test="rqcNo != null  and rqcNo !=''">
			and UPPER(t5.auto_bill_num) like
			CONCAT(CONCAT('%',UPPER(#{rqcNo})),'%')
		</if>
		<if test="whAreaCategoryCode != null  and whAreaCategoryCode !=''">
			and t0.wh_area_category_code = #{whAreaCategoryCode}
		</if>
		<if test="whAreaCode != null  and whAreaCode !=''">
			and coalesce(t1.wh_area_code,t0.wh_area_code)= #{whAreaCode}
		</if>
		<if test="containerCode != null  and containerCode !=''">
			and coalesce(t1.container_code,t0.container_code)= #{containerCode}
		</if>
		<if test="shipperCondition != null  and shipperCondition !=''">
			and(
			UPPER(t4.SHIPPER_ZJM) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%')
			or
			UPPER(t6.VENDOR_NUM) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%')
			or
			UPPER(t6.VENDOR_NAME) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%')
			or
			UPPER(t6.customer_name) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%')
			or
			UPPER(t6.customer_num) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%')
			)
		</if>
		<if test="itemCondition != null  and itemCondition !=''">
			and(
			UPPER(t2.ITEM_ZJM) like
			CONCAT(CONCAT('%',UPPER(#{itemCondition})),'%')
			or
			UPPER(t3.ITEM_NUM) like
			CONCAT(CONCAT('%',UPPER(#{itemCondition})),'%')
			or
			UPPER(t3.ITEM_NAME) like
			CONCAT(CONCAT('%',UPPER(#{itemCondition})),'%')
			)
		</if>
		<if test="status != null and status != '' and status=='0'.toString() ">
			and t1.in_bound_line_id is null
		</if>
		<if test="status != null and status != '' and status=='1'.toString() ">
			and t1.in_bound_line_id is not null
		</if>
		<if test="startTime != null">
			and t0.gmt_create &gt;= #{startTime}
		</if>
		<if test="endTime != null">
			and t0.gmt_create &lt;= #{endTime}
		</if>
		<if test="taskIdList != null and taskIdList.size() >0 ">
			and t0.task_inbound_id in
			<foreach collection="taskIdList" item="item" open="("
					 separator="," close=")">
				#{item}
			</foreach>
		</if>

		<if test="rcvHeaderId != null  and rcvHeaderId !=''">
			and t1.rcv_header_id = #{rcvHeaderId}
		</if>
		<if test="rcvLineId != null  and rcvLineId !=''">
			and t1.rcv_line_id = #{rcvLineId}
		</if>
		<if test="itemId != null  and itemId !=''">
			and t1.item_id = #{itemId}
		</if>
		<if test="stockStatus != null  and stockStatus !=''">
			and t1.stock_status = #{stockStatus}
		</if>
		<if test="includeIds != null and includeIds.size() >0 ">
			and t1.in_bound_line_id in
			<foreach collection="includeIds" item="item" open="("
					 separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="excludeIds != null and excludeIds.size() >0 ">
			and t1.in_bound_line_id not in
			<foreach collection="excludeIds" item="item" open="("
					 separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="printStatus != null  and printStatus !=''">
			<choose>
				<when test="printStatus != null and printStatus == 1">
					AND t1.second_status = '1'
				</when>
				<otherwise>
					AND (t1.second_status = '0' or t1.second_status is null)
				</otherwise>
			</choose>
		</if>
		order by t0.gmt_create desc
	</select>


	<select id="queryRoomInfo" resultType="java.util.Map">
		SELECT DISTINCT
		t.warehouse_room_code,t.warehouse_room_name
		FROM upcloud_base_warehouse_qingniu.base_wms_wh_room_mst t
		WHERE t.warehouse_id = #{warehouseId} AND t.is_enable = 1
	</select>

	<select id="queryAreaInfo" resultType="java.util.Map">
		SELECT DISTINCT
			t.warehouse_area_code,t.warehouse_area_name
		FROM upcloud_base_warehouse_qingniu.base_wms_wh_area t
		WHERE t.warehouse_id = #{warehouseId} AND t.is_enable = 1
	</select>

	<select id="checkContainerTask" resultType="java.lang.Integer">
		select  (ifnull((SELECT sum(qty) FROM Wms_in_rcv_lines  t
			where t.CONTAINER_CODE = #{containerCode} and t.STOCK_STATUS = 0),0) - ifnull(( select sum(QTY) from wms_in_task_instock_detail
			where status =0 and CONTAINER_CODE = #{containerCode} and   RCV_LINE_ID in (
			SELECT RCV_LINE_ID FROM Wms_in_rcv_lines  t
			where t.CONTAINER_CODE = #{containerCode} and t.STOCK_STATUS = 0
			)),0)) as qty from dual
	</select>

</mapper>
