<?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.RcvHeadersMapper">
	<resultMap id="rcvMap"
		type="com.prolog.cs.biz.inbound.dto.rcv.RcvHDTO">
		<result column="rcv_header_id" property="rcvHeaderId"/>
		<result column="auto_bill_num" property="autoBillNum"/>
		<result column="custom_bill_num" property="customBillNum"/>
		<result column="executor_user" property="executorUser"/>
		
		<result column="rcv_date" property="rcvDate"/>
		<result column="rcv_type" property="rcvType"/>
		<result column="status" property="status"/>
		<result column="case_qty" property="caseQty"/>
		<result column="piece_qty" property="pieceQty"/>
		<result column="access_level" property="accessLevel"/>
		<result column="comments" property="comments"/>
		<result column="mtl_owner_id" property="mtlOwnerId"/>
		<result column="warehouse_id" property="warehouseId"/>
		<result column="asn_header_id" property="asnHeaderId"/>
		<result column="asn_auto_bill_num" property="asnAutoBillNum"/>
		
		<result column="print_count" property="printCount"/>
		<result column="print_date" property="printDate"/>
		<result column="audit_date" property="auditDate"/>
		<result column="confirm_date" property="confirmDate"/>
		<result column="audited_name" property="auditedName"/>
		<result column="printed_name" property="printedName"/>
		<result column="confirmed_name" property="confirmedName"/>
		<result column="vendor_id" property="vendorId"/>
		<result column="src_bill_type" property="srcBillType"/>
		<result column="src_header_id" property="srcHeaderId"/>
		<!-- profile字段 -->
		<result column="ship_type" property="shipType"/>
		<result column="arrival_mode" property="arrivalMode"/>
		<result column="vendor_name" property="vendorName"/>
		<result column="warehouse_name" property="warehouseName"/>
		<result column="buyer_mane" property="buyerMane"/>
		<result column="customer_name" property="customerName"/>
		<result column="mtl_owner_name" property="mtlOwnerName"/>

		<!-- <result column="asn_auto_bill_num" property="asnAutoBillNum"/>
		<result column="buyer_id" property="buyerId"/>
		<result column="src_orgn_id" property="srcOrgnId"/>
		<result column="src_warehouse_id" property="srcWarehouseId"/>
		<result column="src_mtl_owner_id" property="srcMtlOwnerId"/>
		<result column="enterprise_id" property="enterpriseId"/>
		<result column="src_auto_bill_num" property="srcAutoBillNum"/>
		<result column="audited_by" property="auditedBy"/>
		<result column="confirmed_by" property="confirmedBy"/>
		<result column="printed_by" property="printedBy"/>
		<result column="status_as" property="statusAs"/>
		<result column="orgn_id" property="orgnId"/>
		<result column="customer_id" property="customerId"/>
		<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="clomuns">
		t1.task_recheck_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.rcv_header_id,
		t1.rcv_line_id,
		t1.item_id,
		t1.qty,
		floor(coalesce(t1.qty,0)) as caseQty,
		mod(coalesce(t1.piece_qty,0),coalesce(t1.conversion_rate,1)) as odd_piece_qty,
		t1.uom,
		t1.conversion_rate,
		t1.piece_qty,
		t1.batch_id,
		t1.batch_num,
		t1.vendor_batch_num,
		t1.active_flag,
		t1.remain_qty,
		t1.comments,
		t1.result,
		t1.reason,
		t1.manufacturer_batch_num,
		t1.executor,
		t1.container_code,
		t1.goods_case_code,
		t1.status,
		t1.times,
		t1.ENTERPRISE_ID,
		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="rcvMap">
		SELECT
		DISTINCT
			t1.rcv_header_id,
			t1.auto_bill_num,
			t1.custom_bill_num,
			t1.executor_user,
			t1.rcv_date,
			t1.rcv_type,
			t1.status,
			t1.case_qty,
			t1.piece_qty,
			t1.access_level,
			t1.comments,
			t1.mtl_owner_id,
			t1.warehouse_id,
			t1.asn_header_id,
			t1.asn_auto_bill_num,
			t1.vendor_id,
			t1.src_header_id,
			t1.src_bill_type,
			t1.print_count,
			t1.print_date,
			t1.audit_date,
			t1.confirm_date,
			t1.audited_name,
			t1.printed_name,
			t1.confirmed_name,
			t2.ship_type,
			t2.arrival_mode,
			t2.vendor_name,
			t2.warehouse_name,
			t2.buyer_mane,
			t2.customer_name,
			t2.mtl_owner_name,
			t1.ENTERPRISE_ID,
			t1.GMT_CREATE as gmtCreate,
			t1.CREATOR,
			t1.CREATOR_NAME as creatorName,
			t1.GMT_MODIFIED as gmtModified,
			t1.MODIFIER,
			t1.MODIFIER_NAME as modifierName 
		FROM
			wms_in_rcv_headers t1,
			wms_in_rcv_headers_profiles t2 
		WHERE
			t1.rcv_header_id = t2.RCV_HEADER_ID 
		<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="status != null  and status !=''">
			and t1.status = #{status}
		</if>	
		<if test="status != null  and status !=''">
			and t1.status = #{status}
		</if>
		<if test="autoBillNum != null  and autoBillNum !=''">
			and t1.auto_bill_num = #{autoBillNum}
		</if>
		<if test="rcvType != null  and rcvType !=''">
			and t1.rcv_type = #{rcvType}
		</if>
		<if test="customBillNum != null  and customBillNum !=''">
			and t1.custom_bill_num like CONCAT(CONCAT('%',#{customBillNum}),'%')
		</if>
		order by t1.gmt_modified
		desc
	</select>
	
	<select id="getRcvSequence" resultType="Long">
	 <!-- 只能用代码实现 -->
	 select SEQ_JK_JKPZDY.nextval SN from dual
	</select>
	
	<select id="findByPkId" resultMap="rcvMap">
			SELECT
			t1.rcv_header_id,
			t1.asn_auto_bill_num,
			t1.auto_bill_num,
			t1.custom_bill_num,
			t1.executor_user,
			t1.rcv_date,
			t1.rcv_type,
			t1.status,
			t1.case_qty,
			t1.piece_qty,
			t1.access_level,
			t1.comments,
			t1.mtl_owner_id,
			t1.warehouse_id,
			t1.asn_header_id,
			t1.vendor_id,
			t1.src_header_id,
			t1.src_bill_type,
			t1.print_count,
			t1.print_date,
			t1.audit_date,
			t1.confirm_date,
			t1.audited_name,
			t1.printed_name,
			t1.confirmed_name,
			t2.mtl_owner_num as mtlOwnerNum,
			t2.ship_type,
			t2.arrival_mode,
			t2.vendor_name,
			t2.warehouse_name,
			t2.buyer_mane,
			t2.customer_name,
			t2.mtl_owner_name,
			coalesce(t1.need_user,t2.BUYER_MANE) as needUser,
			coalesce(t1.need_dept,t2.ORGN_NAME) as needDept,
			t2.ORGN_NAME as orgnName,
			t1.AGENT  as agent,
			t1.ASN_REMARK as asnRemark,
			coalesce(t2.vendor_num, t1.vendor_id)as vendorNum,
			t2.orgn_num as orgnNum,
			t1.shipper_zjm as shipperZjm,
			t1.ASN_AUTO_BILL_NUM as asnBillNum,
			t1.ASN_AUTO_BILL_NUM as asnAutoBillNum,
			t2.transportation_party as transportationParty,
			t2.carriername as carriername,
			t2.ship_from_date as shipFromDate,
			t2.ship_from_add as shipFromAdd,
			t2.on_passage_days as onPassageDays,
			t2.ship_arrive_tem_perature as shipArriveTemPerature,
			t2.ship_arrive_time as shipArriveTime,
			t2.ship_tem_perature as shipTemPerature,
			t2.ship_date as shipDate,
			t2.device_no as deviceNo,
			t2.tempcontrol_mode as tempcontrolMode,
			t2.tempcontrol_status as tempcontrolStatus,
			t1.EXECUTOR_USER as rcvUser,
			t1.auto_bill_num as rcvNo,
			t1.COMMENTS as rcvRemark
		FROM
			wms_in_rcv_headers t1,
			wms_in_rcv_headers_profiles t2 
		WHERE
			t1.rcv_header_id = t2.RCV_HEADER_ID 
		    and t1.asn_header_id  = #{pkId}
		
	</select>
	<select id="getRemainQty" resultType="java.math.BigDecimal">
	 SELECT 
	(SELECT SUM(coalesce(PIECE_QTY, 0)) FROM WMS_IN_RCV_LINES WHERE RCV_HEADER_ID =  #{rcvHeaderId})
	<!-- 减去拒收任务的数量 -->
	-(SELECT coalesce(SUM(coalesce(PIECE_QTY, 0)),0) FROM WMS_IN_TASK_REJECTED_DETAIL WHERE RCV_HEADER_ID =  #{rcvHeaderId} )
	-(SELECT coalesce(SUM(coalesce(PIECE_QTY, 0)),0) FROM WMS_IN_RCV_INSTOCK_LINES WHERE RCV_HEADER_ID =  #{rcvHeaderId} )
	 as remainQty FROM dual 
	</select>
	
	<select id="getRcvIdsByBillId" resultType="string">
	 SELECT
	    <!-- group_concat(score separator ',') RCV_HEADER_IDS -->
	    group_concat(RCV_HEADER_ID) RCV_HEADER_IDS
		<!-- listagg ( RCV_HEADER_ID, ',' ) within GROUP ( ORDER BY RCV_HEADER_ID ) RCV_HEADER_IDS  -->
		FROM
			WMS_IN_RCV_HEADERS 
		WHERE
			ASN_HEADER_ID = #{billId}
		group by ASN_HEADER_ID
	</select>
	
	<select id="getRemainQtyByBillId" resultType="java.math.BigDecimal">
	 SELECT 
	(SELECT SUM(coalesce(ITEM_QTY, 0)) + SUM(coalesce(free_QTY, 0))  FROM OMS_ASN_BILL_D WHERE BILLID =  #{billId})
	-(SELECT coalesce(SUM(coalesce(PIECE_QTY, 0)),0) FROM WMS_IN_RCV_INSTOCK_LINES WHERE ASN_HEADER_ID =  #{billId} )
	 as remainQty FROM dual
	</select>
	
	<update id="updateBillStatus">
	 UPDATE OMS_ASN_BILL_H set STATUS = #{status} WHERE BILLID = #{billId} AND NOT EXISTS(SELECT asn_header_id FROM WMS_IN_RCV_HEADERS WHERE asn_header_id = #{billId})
	</update>
	
	<select id="getReportHeader" resultMap="rcvMap">
		SELECT
			t1.rcv_header_id,
			t1.auto_bill_num,
			t1.custom_bill_num,
			t1.executor_user,
			t1.rcv_date,
			t1.rcv_type,
			t1.status,
			t1.case_qty,
			t1.piece_qty,
			t1.access_level,
			t1.comments,
			t1.mtl_owner_id,
			t1.warehouse_id,
			t1.asn_header_id,
			t1.vendor_id,
			t1.src_header_id,
			t1.src_bill_type,
			t1.print_count,
			t1.print_date,
			t1.audit_date,
			t1.confirm_date,
			t1.audited_name,
			t1.printed_name,
			t1.confirmed_name,
			t2.ship_type,
			t2.arrival_mode,
			t2.vendor_name,
			t2.warehouse_name,
			t2.buyer_mane,
			t2.customer_name,
			t2.mtl_owner_name,
			
			coalesce(t1.need_user,t2.BUYER_MANE) as needUser,
			coalesce(t1.need_dept,t2.ORGN_NAME) as needDept,
			t2.ORGN_NAME as orgnName,
			t1.AGENT  as agent,
			t1.ASN_REMARK as asnRemark,
			
			t2.vendor_num as vendorNum,
			t2.orgn_num as orgnNum,
			t1.shipper_zjm as shipperZjm,
			
			t1.ASN_AUTO_BILL_NUM as asnBillNum,
			t1.ASN_AUTO_BILL_NUM as asnAutoBillNum
			
		FROM
			wms_in_rcv_headers t1,
			wms_in_rcv_headers_profiles t2 
		WHERE
			t1.rcv_header_id = t2.RCV_HEADER_ID 
		<if test="enterpriseId != null  and enterpriseId !=''">
			and t1.enterprise_id = #{enterpriseId}
		</if>
		<!-- 6.11新加 -->
		<if test="rcvUser != null  and rcvUser !=''">
			and UPPER(t1.EXECUTOR_USER) like CONCAT(CONCAT('%',UPPER(#{rcvUser})),'%')
		</if>
		<if test="rcvNo != null  and rcvNo !=''">
			and UPPER(t1.auto_bill_num) like CONCAT(CONCAT('%',UPPER(#{rcvNo})),'%') 
		</if>
		<if test="billNo != null  and billNo !=''">
			and UPPER(t1.ASN_AUTO_BILL_NUM) like CONCAT(CONCAT('%',UPPER(#{billNo})),'%') 
		</if>
		<if test="shipperZjm != null  and shipperZjm !=''">
			and UPPER(t1.SHIPPER_ZJM) like CONCAT(CONCAT('%',UPPER(#{shipperZjm})),'%') 
		</if>
		<if test="startTime != null">
			and t1.rcv_date &gt;= #{startTime}
		</if>
		<if test="endTime != null">
			and t1.rcv_date &lt;= #{endTime}
		</if>
		<!-- 6.11新加 -->
		
		<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="status != null  and status !=''">
			and t1.status = #{status}
		</if>
		<if test="autoBillNum != null  and autoBillNum !=''">
			and UPPER(t1.auto_bill_num) like CONCAT(CONCAT('%',UPPER(#{autoBillNum})),'%')  
		</if>
		<if test="rcvType != null  and rcvType !=''">
			and t1.rcv_type = #{rcvType}
		</if>
		<if test="customBillNum != null  and customBillNum !=''">
			and UPPER(t1.custom_bill_num) like CONCAT(CONCAT('%',UPPER(#{customBillNum})),'%')
		</if>
		<if test="asnAutoBillNum != null  and asnAutoBillNum !=''">
			and UPPER(t1.asn_auto_bill_num) like CONCAT(UPPER(#{asnAutoBillNum}),'%')
		</if>
		<!-- 需要加入rcvType类型,过滤掉其他的不需要看的数据任务 -->
		<!--and t1.rcv_type not in ('5','12','13','14','15') -->
		order by t1.rcv_date desc,t1.ASN_AUTO_BILL_NUM desc
	</select>

	<select id="findByPkIdNew" resultMap="rcvMap">
		SELECT
			t1.rcv_header_id,
			t1.auto_bill_num,
			t1.custom_bill_num,
			t1.executor_user,
			t1.rcv_date,
			t1.rcv_type,
			t1.status,
			t1.case_qty,
			t1.piece_qty,
			t1.access_level,
			t1.comments,
			t1.mtl_owner_id,
			t1.warehouse_id,
			t1.asn_header_id,
			t1.vendor_id,
			t1.src_header_id,
			t1.src_bill_type,
			t1.print_count,
			t1.print_date,
			t1.audit_date,
			t1.confirm_date,
			t1.audited_name,
			t1.printed_name,
			t1.confirmed_name,
			t2.mtl_owner_num as mtlOwnerNum,
			t2.ship_type,
			t2.arrival_mode,
		    coalesce(t2.vendor_name,t2.customer_name) vendor_name,
			t2.warehouse_name,
			t2.buyer_mane,
			t2.customer_name,
			t2.mtl_owner_name,
			coalesce(t1.need_user,t2.BUYER_MANE) as needUser,
			coalesce(t1.need_dept,t2.ORGN_NAME) as needDept,
			t2.ORGN_NAME as orgnName,
			t1.AGENT  as agent,
			t1.ASN_REMARK as asnRemark,
			coalesce(t2.vendor_num, t1.vendor_id)as vendorNum,
			t2.orgn_num as orgnNum,
			t1.shipper_zjm as shipperZjm,
			t1.ASN_AUTO_BILL_NUM as asnBillNum,
			t1.ASN_AUTO_BILL_NUM as asnAutoBillNum,
			t2.transportation_party as transportationParty,
			t2.carriername as carriername,
			t2.ship_from_date as shipFromDate,
			t2.ship_from_add as shipFromAdd,
			t2.on_passage_days as onPassageDays,
			t2.ship_arrive_tem_perature as shipArriveTemPerature,
			t2.ship_arrive_time as shipArriveTime,
			t2.ship_tem_perature as shipTemPerature,
			t2.ship_date as shipDate,
			t2.device_no as deviceNo,
			t2.tempcontrol_mode as tempcontrolMode,
			t2.tempcontrol_status as tempcontrolStatus,
			t1.EXECUTOR_USER as rcvUser,
			t1.auto_bill_num as rcvNo,
			t1.COMMENTS as rcvRemark,
			coalesce(t3.phone_mobile,t3.phone) as vendorPhone
		FROM
			wms_in_rcv_headers t1
			join wms_in_rcv_headers_profiles t2 on t1.rcv_header_id = t2.RCV_HEADER_ID
			left join upcloud_base_chain_qingniu.base_supplier_contacts_all t3 on t1.vendor_id = t3.supplier_id
		WHERE
		  1=1
		<if test="rcvHeaderIdList != null and rcvHeaderIdList.size() >0 ">
			and t1.rcv_header_id in
			<foreach collection="rcvHeaderIdList" item="item" open="("
					 separator="," close=")">
				#{item}
			</foreach>
		</if>
		and t1.rcv_header_id IN (
		SELECT DISTINCT t2.RCV_HEADER_ID FROM WMS_IN_RCV_LINES t2
		WHERE 1 = 1
		<if test="rcvHeaderIdList != null and rcvHeaderIdList.size() >0 ">
			and t2.rcv_header_id in
			<foreach collection="rcvHeaderIdList" item="item" open="("
					 separator="," close=")">
				#{item}
			</foreach>
		</if>
		and t2.piece_qty &lt;&gt; 0
		and t2.stock_status in('0','1')
		GROUP BY t2.RCV_HEADER_ID
		)
	</select>

	<update id="updateUploadFlag" >
		update wms_supervision_code set upload_flag = #{uploadFlag} ,upload_times = ifnull(upload_times,0)+1 , last_upload_time = NOW(),message = #{message}
		where ref_bill_id  = #{rcvHeaderId} and ref_bill_line_id =  #{rcvLineId}
		and upload_flag in ('0','2')
	</update>

	<update id="updateUploadFlagV2" >
		update wms_supervision_code set upload_flag = #{uploadFlag} ,upload_times = ifnull(upload_times,0)+1 , last_upload_time = NOW(),message = #{message}
		where  ref_bill_line_id =  #{rcvLineId}  and upload_flag in ('0','2')
	</update>

	<update id="updateUploadFlagV3" >
		update wms_supervision_code set upload_flag = #{uploadFlag} ,upload_times = ifnull(upload_times,0)+1 , last_upload_time = NOW(),message = #{message}
		where  src_code =  #{billno} and ent_id is not null and upload_flag in ('0','2')
	</update>

	<update id="updateSupervisionCodeType" >
		update wms_supervision_code set upload_type = #{uploadType} ,upload_shipp_id =  #{uploadShippId}
		where  ref_bill_line_id in
		<foreach collection="refbilllineids" close=")" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
	</update>

	<select id="findSupplierId" resultType="java.lang.String">
		select SHIPPERID from oms_asn_bill_h where BILLID =( select ASN_HEADER_ID  from wms_in_rcv_headers
		 where RCV_HEADER_ID =#{rcvHeaderId})
		and SHIPPER_TYPE =2
	</select>

	<!--<select id="findSupervisionCodes" resultType="com.prolog.cs.biz.inbound.model.SupervisionCode">-->
	<!--select  id,enterprise_id as enterpriseId,ref_bill_id as refBillId,ref_bill_line_id as refBillLineId,upload_shipp_id as uploadShippId,-->
	<!--upload_type as uploadType,supervision_code as supervisionCode,message,upload_flag as uploadFlag-->
	<!--from  wms_supervision_code where upload_flag  in ('0','2') and upload_times &lt;= 3 and upload_shipp_id is not null and upload_shipp_id !=''-->
	<!--and ref_bill_line_id is  not null and ref_bill_line_id !=''-->
	<!--</select>-->

	<select id="findSupervisionCodes" resultType="com.prolog.cs.biz.inbound.model.SupervisionCode">
		select  id,enterprise_id as enterpriseId,ref_bill_id as refBillId,ref_bill_line_id as refBillLineId,upload_shipp_id as uploadShippId,
		upload_type as uploadType,supervision_code as supervisionCode,message,upload_flag as uploadFlag,src_code as srcCode,ent_id as entId
		from  wms_supervision_code where upload_flag  in ('0','2') and ifnull(upload_times,0) &lt;= 3 and upload_shipp_id is not null and upload_shipp_id !=''
		and upload_type is  not null  and supervision_code is not null
		and ent_id is not null and  src_code in
		<foreach collection="srcCodes" close=")" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
	</select>

	<select id="findUnDownLoadSupervisionCode" resultType="com.prolog.cs.biz.inbound.model.SupervisionCode">
		select  id,enterprise_id as enterpriseId,warehouse_code as warehouseCode,supervision_code as supervisionCode,
		item_id as itemId,item_code as itemCode,item_name as itemName,
		pack_size as packSize,code_type as codeType,pack_level as packLevel,ref_bill_line_id as refBillLineId
		from  wms_supervision_code where down_flag  in (0,2) and save_type = 1 and  pack_level is not null
		and (down_times &lt;=3 or down_times is null or down_times ='') and supervision_code is not null
		ORDER BY GMT_CREATE asc  limit 20
	</select>

	<select id="findUnToHisSupervisionCode" resultType="com.prolog.cs.biz.inbound.model.SupervisionCodeSmall">
		select pack_level as  packLevel,parent_code as parentCode,id,supervision_code as supervisionCode from
		wms_supervision_code_small where DATEDIFF(CURDATE(),GMT_CREATE) >15 limit 1000
	</select>

	<select id="findBatchId" resultType="java.lang.String">
		select BATCH_ID as batchId from upcloud_base_inventory_qingniu.wms_inv_batch_numbers
		where MANUFACTURER_LOT_NUM = #{manufacturerLotNum}
		and ITEM_ID =#{itemId} and BATCH_PRODUCTION_DATE= #{productionDate}
		and BATCH_EXPIRED_DATE = #{expiredDate} limit 1
	</select>

	<select id="findFinishDetalQty" resultType="java.math.BigDecimal">
		SELECT ifnull(sum(piece_qty),0) as qty FROM wms_in_rcv_instock_lines t where   asn_line_id =  #{lineid}
	</select>

	<select id="findInSupervisionCode" resultType="com.prolog.cs.biz.inbound.model.SupervisionCode">
		select  id,enterprise_id as enterpriseId,ref_bill_id as refBillId,ref_bill_line_id as refBillLineId,upload_shipp_id as uploadShippId,
		upload_type as uploadType,supervision_code as supervisionCode,message,upload_flag as uploadFlag,src_code as srcCode,ent_id as entId
		from  wms_supervision_code where upload_flag  in ('0','2')  and upload_shipp_id is not null and upload_shipp_id !=''
		and upload_type is  not null and ent_id is not null  and src_code =  ( SELECT BILLNO FROM oms_asn_bill_h where billid = #{asnHeaderId} )
	</select>

	<update id="updateDownFlag" >
		update wms_supervision_code set down_flag = #{downFlag} ,down_times = ifnull(down_times,0)+1 ,down_message = #{message}
		where down_flag  in (0,2) and save_type = 1 and  pack_level is not null and   supervision_code in
		<foreach collection="supervisionCodes" close=")" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
	</update>

	<select id="findUnSaveCusEntId" resultType="com.prolog.cs.basic.chain.model.customer.BaseCustomerMst">
		select save_times as saveTimes,customer_id as customerId ,customer_name as customerName ,enterprise_id as  enterpriseId
		from  upcloud_base_chain_qingniu.base_customer_mst t
		where eleccode is null or eleccode ='' ORDER BY save_times asc   limit 100
	</select>

	<update id="saveCusEntId" >
		update  upcloud_base_chain_qingniu.base_customer_mst  set save_times = ifnull(save_times,0)+1 ,eleccode = #{entId}
		where customer_id = #{customerId}
	</update>

	<select id="findUnSaveEntIdSupCode" resultType="java.lang.String">
		select  distinct upload_shipp_id
		from  wms_supervision_code where upload_flag  in ('0','2') and upload_shipp_id is not null
		and upload_type is  not null  and supervision_code is not null
		and ent_id is null and  src_code in
		<foreach collection="srcCodes" close=")" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
	</select>

	<select id="findSupplierMstByIds" resultType="com.prolog.cs.basic.chain.model.supplier.BaseSupplierMst">
		select  eleccode,supplier_id as supplierId
		from  upcloud_base_chain_qingniu.base_supplier_mst where eleccode is not null
		and supplier_id in
		<foreach collection="supperlierIds" close=")" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
	</select>

	<update id="saveEntIdSupCode" >
		update  wms_supervision_code  set ent_id = #{eleccode}
		where upload_flag  in ('0','2')
		and supervision_code is not null
		and ent_id is null and upload_shipp_id = #{shippId}  and upload_type = #{uploadType}
		and  src_code in
		<foreach collection="srcCodes" close=")" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
	</update>

	<select id="findCustomerMstByIds" resultType="com.prolog.cs.basic.chain.model.customer.BaseCustomerMst">
		select  eleccode,customer_id as customerId
		from  upcloud_base_chain_qingniu.base_customer_mst where eleccode is not null
		and customer_id in
		<foreach collection="customerIds" close=")" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
	</select>

	<select id="checkendIdNum" resultType="java.lang.Integer">
		select count(1)  from wms_supervision_code where SRC_CODE = #{billno} and ent_id is null and upload_flag in ('0','2')
	</select>

	<!--<select id="findUnUploadSrcCode" resultType="java.lang.String">-->
		<!--select  SRC_CODE from  wms_supervision_code where  SRC_CODE is not null and  upload_flag  in ('0','2') and ifnull(upload_times,0) &lt;= 3 and upload_shipp_id is not null and upload_shipp_id !=''-->
		<!--and upload_type is  not null  and supervision_code is not null GROUP BY SRC_CODE-->
		<!--HAVING count(SRC_CODE)=count(ent_id) limit 10-->
	<!--</select>-->

	<select id="findUnUploadSrcCode" resultType="java.lang.String">
		select  DISTINCT t.SRC_CODE  from  wms_supervision_code t where  SRC_CODE is not null and  upload_flag  in ('0','2')
		and ifnull(upload_times,0) &lt;= 3 and upload_shipp_id is not null and upload_shipp_id !=''
		and upload_type is  not null  and supervision_code is not null
		and not EXISTS (
		select * from wms_supervision_code  s where s.SRC_CODE = t.SRC_CODE and s.ENT_ID is null
		) limit 5
	</select>



	<select id="findUnSaveEntIdSrcCodeForCustomer" resultType="java.lang.String">
		select  src_code
		from  wms_supervision_code where upload_flag  in ('0','2')  and supervision_code is not null
		and ent_id is null and upload_type = 1  and exists (select  1 from upcloud_base_chain_qingniu.base_customer_mst
		where eleccode is not null and customer_id = upload_shipp_id ) group by src_code limit 10
	</select>

	<select id="findUnSaveEntIdSrcCodeForSupplier" resultType="java.lang.String">
		select  src_code
		from  wms_supervision_code where upload_flag  in ('0','2')  and supervision_code is not null
		and ent_id is null and upload_type = 0  and exists (select  1 from upcloud_base_chain_qingniu.base_supplier_mst
		where eleccode is not null and supplier_id = upload_shipp_id ) group by src_code limit 10
	</select>
</mapper>
