<?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.IncomingRecordDMapper">
	<resultMap id="map" type="com.prolog.cs.biz.inbound.model.IncomingRecordD">
		<result column="line_id" property="lineId"/>
		<result column="billid" property="billid"/>
		<result column="billno" property="billno"/>
		<result column="seqno" property="seqno"/>
		<result column="ref_billtype" property="refBilltype"/>
		<result column="ref_billid" property="refBillid"/>
		<result column="ref_billno" property="refBillno"/>
		<result column="ref_seqno" property="refSeqno"/>
		<result column="consignorid" property="consignorid"/>
		<result column="consignorcode" property="consignorcode"/>
		<result column="consignorname" property="consignorname"/>
		<result column="itemid" property="itemid"/>
		<result column="itemcode" property="itemcode"/>
		<result column="itemname" property="itemname"/>
		<result column="parernt_itemid" property="parerntItemid"/>
		<result column="category_id" property="categoryId"/>
		<result column="pack_size" property="packSize"/>
		<result column="pack_units" property="packUnits"/>
		<result column="mach_type" property="machType"/>
		<result column="lot_no" property="lotNo"/>
		<result column="batch_no" property="batchNo"/>
		<result column="pdate_from" property="pdateFrom"/>
		<result column="valid_until" property="validUntil"/>
		<result column="manufacturer" property="manufacturer"/>
		<result column="affirmed_pcs" property="affirmedPcs"/>
		<result column="affirmed_odd" property="affirmedOdd"/>
		<result column="affirmed_freeqty" property="affirmedFreeqty"/>
		<result column="affirmed_qty" property="affirmedQty"/>
		<result column="affirmed_weight" property="affirmedWeight"/>
		<result column="affirmed_volume" property="affirmedVolume"/>
		<result column="price" property="price"/>
		<result column="tni_price" property="tniPrice"/>
		<result column="amt" property="amt"/>
		<result column="tni_amt" property="tniAmt"/>
		<result column="piece_cost_te" property="pieceCostTe"/>
		<result column="piece_cost_ti" property="pieceCostTi"/>
		<result column="tax_rate" property="taxRate"/>
		<result column="actualmanufacturer" property="actualmanufacturer"/>
		<result column="spectreatmtype" property="spectreatmtype"/>
		<result column="qtoperator" property="qtoperator"/>
		<result column="qtoperator2" property="qtoperator2"/>
		<result column="rcvoperator" property="rcvoperator"/>
		<result column="rcvoperator2" property="rcvoperator2"/>
		<result column="exceptionspec" property="exceptionspec"/>
		<result column="returnproctype" property="returnproctype"/>
		<result column="brancharea" property="brancharea"/>
		<result column="remark" property="remark"/>
		<result column="attr_str1" property="attrStr1"/>
		<result column="attr_str2" property="attrStr2"/>
		<result column="attr_str3" property="attrStr3"/>
		<result column="attr_str4" property="attrStr4"/>
		<result column="attr_str5" property="attrStr5"/>
		<result column="createby" property="createby"/>
		<result column="createdate" property="createdate"/>
		<result column="lastmodifyby" property="lastmodifyby"/>
		<result column="lastmodifydate" property="lastmodifydate"/>
		<result column="p_enterpriseid" property="pEnterpriseid"/>
		<result column="p_enterprisename" property="pEnterprisename"/>
		<result column="stockstatus" property="stockstatus"/>
		<result column="returnreason" property="returnreason"/>
		<result column="returntype" property="returntype"/>
		<result column="belowgradereason" property="belowgradereason"/>
		<result column="handlestep" property="handlestep"/>
		<result column="samplingnum" property="samplingnum"/>
		<result column="rqc_report_no" property="rqcReportNo"/>
	</resultMap>
	<sql id="mainClomuns" >
		t1.line_id,
		t1.billid,
		t1.billno,
		t1.seqno,
		t1.ref_billtype,
		t1.ref_billid,
		t1.ref_billno,
		t1.ref_seqno,
		t1.consignorid,
		t1.consignorcode,
		t1.consignorname,
		t1.itemid,
		t1.itemcode,
		t1.itemname,
		t1.parernt_itemid,
		t1.category_id,
		t1.pack_size,
		t1.pack_units,
		t1.mach_type,
		t1.lot_no,
		t1.batch_no,
		t1.pdate_from,
		t1.valid_until,
		t1.manufacturer,
		t1.affirmed_pcs,
		t1.affirmed_odd,
		t1.affirmed_freeqty,
		t1.affirmed_qty,
		t1.affirmed_weight,
		t1.affirmed_volume,
		t1.price,
		t1.tni_price,
		t1.amt,
		t1.tni_amt,
		t1.piece_cost_te,
		t1.piece_cost_ti,
		t1.tax_rate,
		t1.actualmanufacturer,
		t1.spectreatmtype,
		t1.qtoperator,
		t1.qtoperator2,
		t1.rcvoperator,
		t1.rcvoperator2,
		t1.exceptionspec,
		t1.returnproctype,
		t1.brancharea,
		t1.remark,
		t1.attr_str1,
		t1.attr_str2,
		t1.attr_str3,
		t1.attr_str4,
		t1.attr_str5,
		t1.createby,
		t1.createdate,
		t1.lastmodifyby,
		t1.lastmodifydate,
		t1.p_enterpriseid,
		t1.p_enterprisename,
		t1.stockstatus,
		t1.returnreason,
		t1.returntype,
		t1.belowgradereason,
		t1.handlestep,
		t1.samplingnum,
		t1.rqc_report_no,
	</sql>
	
	<select id="getInsertData" resultMap="map">
	 SELECT XXX1.*, @rownum:=@rownum+1 as  SEQNO FROM
        (
        <!-- 上架 -->
		  SELECT
			  t1.IN_BOUND_LINE_ID AS LINE_ID,
			  t1.RCV_HEADER_ID AS BILLID,
			  t5.AUTO_BILL_NUM AS BILLNO,
			  coalesce(T3.REF_BILLTYPE,'-') AS REF_BILLTYPE,
			  t3.BILLID AS REF_BILLID,
			  t3.BILLNO AS REF_BILLNO,
		      t2.SEQNO AS REF_SEQNO,
			  t2.CONSIGNORID,
			 <!--  t2.CONSIGNORCODE, -->
			  coalesce(t2.CONSIGNORCODE,t2.CONSIGNORID) as CONSIGNORCODE,
			  t2.CONSIGNORNAME,
			  t2.ITEMID,
			  t2.ITEMCODE,
			  t2.ITEMNAME,
			  t2.PARERNT_ITEMID,
			  t2.CATEGORY_ID,
			  t2.PACK_SIZE,
			  t2.PACK_UNITS,
			  t2.MACH_TYPE,
			  t1.MANUFACTURER_LOT_NUM AS LOT_NO,
			  t1.BATCH_NUM AS BATCH_NO,
             t6.SHOW_PRODUCTION_DATE PDATE_FROM,
             t6.SHOW_EXPIRED_DATE VALID_UNTIL,
			  t2.MANUFACTURER,
			  t1.ODD_CASE_QTY AS AFFIRMED_PCS,
			  t1.ODD_PIECE_QTY AS AFFIRMED_ODD,
			  t2.FREE_QTY AS AFFIRMED_FREEQTY,
			  t1.PIECE_QTY AS AFFIRMED_QTY,
             ( t1.PIECE_QTY * COALESCE( t6.ITEM_WEIGHT, 0 ) ) AS AFFIRMED_WEIGHT,
             ( t1.PIECE_QTY * COALESCE( t6.ITEM_VOLUME, 0 ) ) AS AFFIRMED_VOLUME,
			  COALESCE( t2.PRICE, t4.PIECE_PRICE_TI ) AS PRICE,
			  COALESCE( t2.TNI_PRICE, t4.PIECE_PRICE_TE ) AS TNI_PRICE,
			  COALESCE( PRICE, 0 ) * t1.PIECE_QTY AS AMT,
			  COALESCE( TNI_PRICE, 0 ) * t1.PIECE_QTY AS TNI_AMT,
			  0 AS PIECE_COST_TE,
			  0 AS PIECE_COST_TI,
			  abs( TRUNCATE((COALESCE( PRICE, 0 ) / COALESCE( TNI_PRICE, 1 ) -1 ),2))  AS TAX_RATE,
			  t6.DELEGATION_PARTY_NAME as ACTUALMANUFACTURER,
			  '' as SPECTREATMTYPE,
			  x1.QC_OPCODE1 as QTOPERATOR,
			  x1.QC_OPCODE2 as QTOPERATOR2,
			  T6.EXECUTOR_USER as RCVOPERATOR,
			  '' as RCVOPERATOR2,
			  '' as EXCEPTIONSPEC,
			  t2.BRANCHAREACODE BRANCHAREA,
			 <!--  t1.COMMENTS AS REMARK, -->
			  x1.LINE_REMARK AS REMARK,
			  'N' as ATTR_STR1,
			  t2.ATTR_STR2,
			  t2.ATTR_STR3,
			  t2.ATTR_STR4,
			  t2.ATTR_STR5,
			  t2.CREATEBY,
			  t2.CREATEDATE,
			  t2.LASTMODIFYBY,
			  t2.LASTMODIFYDATE,
			  t2.P_ENTERPRISEID,
			  t2.P_ENTERPRISENAME,
		      case when  t1.INVENTORY_STATUS = '1' then '1'
					when  t1.INVENTORY_STATUS = '2' then '2'
					when  t1.INVENTORY_STATUS = '3' then '6'
					when  t1.INVENTORY_STATUS = '4' then '2'
					when  t1.INVENTORY_STATUS = '5' then '7'
					else t1.INVENTORY_STATUS
		      end  STOCKSTATUS,
			  t2.RTREASON as RETURNREASON,
			  t5.RETURN_TYPE as RETURNTYPE,
			  t6.RCV_REASON2 as BELOWGRADEREASON,
			  t6.RCV_REASON1 as HANDLESTEP,
			  TRUNCATE((SELECT COALESCE(SUM((COALESCE(QC_ODD_CASE_ACT_QTY,0) * COALESCE(CONVERSION_RATE, 1))
			   + COALESCE(QC_ODD_PIECE_ACT_QTY, 0)), 0)
			   FROM WMS_IN_RQC_SAMPLE WHERE ASN_LINE_ID = t1.ASN_LINE_ID ), 4)as SAMPLINGNUM,
		       t6.RQC_REPORT_NO

		FROM WMS_IN_RCV_INSTOCK_LINES t1
			  LEFT JOIN OMS_ASN_BILL_D t2 ON t1.ASN_LINE_ID = t2.LINEID
			  LEFT JOIN OMS_ASN_BILL_H t3 ON t1.ASN_HEADER_ID = t3.BILLID
			  LEFT JOIN WMS_IN_RCV_LINES_PROFILES t4 ON t1.RCV_LINE_ID = t4.RCV_LINE_ID
			  LEFT JOIN WMS_IN_RCV_HEADERS t5 ON t1.RCV_HEADER_ID = t5.RCV_HEADER_ID
			  LEFT JOIN WMS_IN_RCV_LINES t6 ON t1.RCV_LINE_ID = t6.RCV_LINE_ID
			  LEFT JOIN (
			  	select * from (
					  select
					      t.rcv_line_id,t.QC_OPCODE1 ,t.QC_OPCODE2 ,t.LINE_REMARK
					  from WMS_IN_RQC_LINES t
					  WHERE  1=1
					<if test="rcvHeaderIds != null  and rcvHeaderIds.size >0 ">
						 AND t.RCV_HEADER_ID IN 
			            <foreach collection="rcvHeaderIds" item="item" open="("
			                     separator="," close=")">
			                #{item}
			            </foreach>
					</if>
					<if test="asnHeaderId != null  and asnHeaderId !=''">
						and t.ASN_HEADER_ID = #{asnHeaderId}
					</if>
					) temp LIMIT 1
			  	
			  ) x1 on x1.RCV_LINE_ID = t1.RCV_LINE_ID
			WHERE 1=1
			     <if test="rcvHeaderIds != null  and rcvHeaderIds.size >0 ">
						 AND T1.RCV_HEADER_ID IN 
			            <foreach collection="rcvHeaderIds" item="item" open="("
			                     separator="," close=")">
			                #{item}
			            </foreach>
					</if>
					<if test="asnHeaderId != null  and asnHeaderId !=''">
						and T1.ASN_HEADER_ID = #{asnHeaderId}
					</if>

		union all
		<!-- 拒收 -->
		SELECT
			t1.TASK_REJECTED_ID AS LINE_ID,
			t1.RCV_HEADER_ID AS BILLID,
			t5.AUTO_BILL_NUM AS BILLNO,
			coalesce(T3.REF_BILLTYPE,'-') AS REF_BILLTYPE,
			t3.BILLID AS REF_BILLID,
			t3.BILLNO AS REF_BILLNO,
			t2.SEQNO AS REF_SEQNO,
			t2.CONSIGNORID,
			coalesce(t2.CONSIGNORCODE,t2.CONSIGNORID) as CONSIGNORCODE,
			t2.CONSIGNORNAME,
			t2.ITEMID,
			t2.ITEMCODE,
			t2.ITEMNAME,
			t2.PARERNT_ITEMID,
			t2.CATEGORY_ID,
			t2.PACK_SIZE,
			t2.PACK_UNITS,
			t2.MACH_TYPE,
			t1.MANUFACTURER_LOT_NUM AS LOT_NO,
			t1.BATCH_NUM AS BATCH_NO,
            t6.SHOW_PRODUCTION_DATE PDATE_FROM,
            t6.SHOW_EXPIRED_DATE VALID_UNTIL,
			t2.MANUFACTURER,
			t6.ODD_CASE_QTY AS AFFIRMED_PCS,
			t6.ODD_PIECE_QTY AS AFFIRMED_ODD,
			t2.FREE_QTY AS AFFIRMED_FREEQTY,
			t1.PIECE_QTY AS AFFIRMED_QTY,
			( t1.PIECE_QTY * COALESCE( t6.ITEM_WEIGHT, 0 ) ) AS AFFIRMED_WEIGHT,
			( t1.PIECE_QTY * COALESCE( t6.ITEM_VOLUME, 0 ) ) AS AFFIRMED_VOLUME,
			COALESCE( t2.PRICE, t4.PIECE_PRICE_TI ) AS PRICE,
			COALESCE( t2.TNI_PRICE, t4.PIECE_PRICE_TE ) AS TNI_PRICE,
			COALESCE( PRICE, 0 ) * t1.PIECE_QTY AS AMT,
			COALESCE( TNI_PRICE, 0 ) * t1.PIECE_QTY AS TNI_AMT,
			0 AS PIECE_COST_TE,
			0 AS PIECE_COST_TI,
			abs( TRUNCATE((COALESCE( PRICE, 0 ) / COALESCE( TNI_PRICE, 1 ) -1 ),2))  AS TAX_RATE,
			t6.DELEGATION_PARTY_NAME as ACTUALMANUFACTURER,
			'' as SPECTREATMTYPE,
			x1.QC_OPCODE1 as QTOPERATOR,
			x1.QC_OPCODE2 as QTOPERATOR2,
			T6.EXECUTOR_USER as RCVOPERATOR,
			'' as RCVOPERATOR2,
			'' as EXCEPTIONSPEC,
			t2.BRANCHAREACODE BRANCHAREA,
			t6.COMMENTS AS REMARK,
			t2.ATTR_STR1,
			t2.ATTR_STR2,
			t2.ATTR_STR3,
			t2.ATTR_STR4,
			t2.ATTR_STR5,
			t2.CREATEBY,
			t2.CREATEDATE,
			t2.LASTMODIFYBY,
			t2.LASTMODIFYDATE,
			t2.P_ENTERPRISEID,
			t2.P_ENTERPRISENAME,
			'5' STOCKSTATUS,
			t2.RTREASON as RETURNREASON,
			t5.RETURN_TYPE as RETURNTYPE,
			t6.RCV_REASON2 as BELOWGRADEREASON,
			t6.RCV_REASON1 as HANDLESTEP,
			TRUNCATE((SELECT COALESCE(SUM((COALESCE(QC_ODD_CASE_ACT_QTY,0) * COALESCE(CONVERSION_RATE, 1))+ COALESCE(QC_ODD_PIECE_ACT_QTY, 0)), 0)
			FROM WMS_IN_RQC_SAMPLE WHERE ASN_LINE_ID = t1.ASN_LINE_ID ), 4) as SAMPLINGNUM,
			t6.RQC_REPORT_NO

		FROM WMS_IN_TASK_REJECTED_DETAIL t1
		LEFT JOIN OMS_ASN_BILL_D t2 ON t1.ASN_LINE_ID = t2.LINEID
		LEFT JOIN OMS_ASN_BILL_H t3 ON t1.ASN_HEADER_ID = t3.BILLID
		LEFT JOIN WMS_IN_RCV_LINES_PROFILES t4 ON t1.RCV_LINE_ID = t4.RCV_LINE_ID
		LEFT JOIN WMS_IN_RCV_HEADERS t5 ON t1.RCV_HEADER_ID = t5.RCV_HEADER_ID
		LEFT JOIN WMS_IN_RCV_LINES t6 ON t1.RCV_LINE_ID = t6.RCV_LINE_ID
		LEFT JOIN (
			select * from (
				select
					t.rcv_line_id,t.QC_OPCODE1 ,t.QC_OPCODE2,t.LINE_REMARK
				from WMS_IN_RQC_LINES t
				WHERE  1=1
					<if test="rcvHeaderIds != null  and rcvHeaderIds.size >0 ">
						 AND t.RCV_HEADER_ID IN 
			            <foreach collection="rcvHeaderIds" item="item" open="("
			                     separator="," close=")">
			                #{item}
			            </foreach>
					</if>
					<if test="asnHeaderId != null  and asnHeaderId !=''">
						and t.ASN_HEADER_ID = #{asnHeaderId}
					</if>

		    ) temp2 LIMIT 1
		) x1 on x1.RCV_LINE_ID = t1.RCV_LINE_ID
		WHERE 1=1
			<if test="rcvHeaderIds != null  and rcvHeaderIds.size >0 ">
				 AND T1.RCV_HEADER_ID IN 
	            <foreach collection="rcvHeaderIds" item="item" open="("
	                     separator="," close=")">
	                #{item}
	            </foreach>
			</if>
			<if test="asnHeaderId != null  and asnHeaderId !=''">
				and T1.ASN_HEADER_ID = #{asnHeaderId}
			</if>
       ) XXX1,
       (select @rownum:=0) a
	</select>

	<select id="getSyncErpDataInfo" resultType="java.util.Map">
		select t3.rcv_header_id,t4.auto_bill_num as djbh,t4.vendor_id as dwid,t7.bill_date as rq,t7.buyer_name as ry_cgy,
			   t4.executor_user as ry_shouhy,t8.qc_operator1 as ry_zjy,t0.creator_name as ry_czy,t7.billno as djbh_sj,
			   t9.seqno as dj_sort,t7.billtype as rktype,t9.itemid as spid,t2.piece_qty as sl,
			   t2.odd_case_qty as js,t2.odd_piece_qty as lss,t9.lot_no as ph,t9.pdate_from as rq_sc,
			   t9.valid_until as yxqz,t3.rcvstockstatusreason as yspd,t9.attr_str1 as mjph,null as mjxq,
			   t9.attr_str2 as jkzczh,t0.enterprise_id as enterpriseid,t0.warehouse_id as branchid,t7.consignorid as yzid,
			   now() as dmrq,'N' as wmflg,null as xgtime,null as cgdd_sort,t2.conversion_rate,t2.rcv_line_id
		from 	wms_in_task_instock_detail t0
					join wms_in_rcv_instock_lines t1 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
					join wms_in_rcv_headers_profiles t6 on t4.rcv_header_id =t6.rcv_header_id
					join oms_asn_bill_h t7 on t7.billid =t4.asn_header_id
					left join wms_in_rqc_headers t8 on t8.rcv_header_id = t4.rcv_header_id
					join oms_asn_bill_d t9 on t9.billid =t7.billid and t9.lineid=t2.asn_line_id
		where t1.rcv_header_id=#{rcvHeaderId}
	</select>
</mapper>
