<?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.outbound.dao.OutcomingRecordHMapper">

    <select id="getH" resultType="com.prolog.cs.biz.outbound.model.OutcomingRecordH">
	SELECT DISTINCT
	OMSBILL.BILLID,
	OMSBILL.BILLNO,
	OMSBILL.BILLTYPE,
	OMSBILL.BRANCHID,
	OMSBILL.BRANCHCODE,
	OMSBILL.BRANCHNAME,
	OMSBILL.CONSIGNORID,
	ifnull(OMSBILL.CONSIGNORCODE,OMSBILL.CONSIGNORID) CONSIGNORCODE,
	OMSBILL.CONSIGNORNAME,
	'' CONSIGNOR_BILL_NO,
	OMSBILL.REF_BILLTYPE REFBILLTYPE,
	OMSBILL.REF_BILLID REFBILLID,
	OMSBILL.REF_BILLNO REFBILLNO,
	OMSBILL.SALESPERSON_ID salespersonId,
	OMSBILL.SALESPERSON_NAME salespersonName,
	'' PURORG_ID,
	'' PURORG_NAME,
	OMSBILL.BRANCHAREA,
	OMSBILL.BRANCH_ADDRESS branchAddress,
	'' businessNo,
	OMSBILL.SHIPPER_TYPE shipperType,
	OMSBILL.SHIPPERCODE SHIPPERCODE,
	OMSBILL.SHIPPERNAME SHIPPERNAME,
	OMSBILL.PRIORITY_LEVEL priorityLevel,
	OMSBILL.ARRIVAL_MODE arrivalMode,
	OMSBILL.SHIP_LINE_ID shilineId,
	OMSBILL.CARRIER_ID carrierId,
	OMSBILL.CARRIER_NAME carrierName,
	'10' STATUS,
	coalesce( INNERCHECK.CHECK_TIME, BARCODE.CHECK_TIME, BILL.GMT_CREATE, BILL.GMT_CREATE ) billOutTime,
	NULL CONFIRMTIME, INNERCHECK.CHECKER_NAME OPERATOR,
	NULL CONFIRMER,
	XXX2.PCS totPcs,
	BILL.TOTAL_QTY totQty,
	COALESCE( X1.TOT_BOX_NUM, 0 ) totBoxNum,
	BILL.TOTAL_WEIGHT totWeight,
	BILL.TOTAL_VOLUME totVolume,
	BARCODE.CHECKER_NAME EXTERNALREVIEWER,
	BARCODE.CHECK_TIME CHECK_DATE,
	BILL.GMT_CREATE WMSSCHEDTIME,
	OMSBILL.SALE_BRANCHID,
	OMSBILL.SALE_BRANCHCODE,
	OMSBILL.SALE_BRANCHNAME,
	OMSBILL.CREATEBY,
	OMSBILL.CREATEDATE,
	OMSBILL.LASTMODIFYBY,
	OMSBILL.LASTMODIFYDATE,
	OMSBILL.P_ENTERPRISEID enterpriseid,
	OMSBILL.P_ENTERPRISENAME enterprisename,
	XXX1.STAGE_CODE_MIN,
	XXX1.STAGE_CODE_MAX,
	redFlag.red_flag attrStr8,
	'N' attrStr1
FROM
	OMS_DO_BILL_H OMSBILL
	LEFT JOIN WMS_WAVE_BILL BILL ON BILL.SRC_ID = OMSBILL.BILLID
	LEFT JOIN (
	SELECT
		*
	FROM
		(
		SELECT @rownum:=@rownum+1
			row_flag,
			taskd.CHECK_TIME,
			taskd.CHECKER_NAME,
			taskd.CHECKER_NAME_2
		FROM
			wms_pick_task_h taskh
			LEFT JOIN wms_pick_task_d taskd ON taskd.Pick_task_id = taskh.pick_task_id
			LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = taskh.BILL_GROUP_ID
			join (SELECT @rownum:=0) t
		WHERE
			bill.src_id = #{omsBillId}
		) a
	WHERE
		row_flag = 1
	) INNERCHECK ON 1 = 1
	LEFT JOIN (
	SELECT
		*
	FROM
		(
		SELECT @rownum:=@rownum+1
			ROWFLAG,
			BARCODE.CHECKER_NAME,
			BARCODE.CHECK_TIME
		FROM
			WMS_BARCODE_H barcode
			LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = barcode.BILL_GROUP_ID
			join (SELECT @rownum:=0) t
		WHERE
			bill.src_id = #{omsBillId}
			AND barcode.CHECK_TIME IS NOT NULL
		) b
	WHERE
		ROWFLAG = 1
	) BARCODE ON 1 = 1
	LEFT JOIN (
	SELECT
		count( * ) TOT_BOX_NUM
	FROM
		WMS_BARCODE_H barcodeH
		LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = barcodeH.BILL_GROUP_ID
	WHERE
		bill.src_id = #{omsBillId}
		AND barcodeH.TYPE = '0'
		AND NOT EXISTS ( SELECT 1 FROM WMS_BARCODE_D g WHERE barcodeH.id = g.BARCODE_ID AND g.IS_PIECE = 0 )
	) X1 ON 1 = 1
	LEFT JOIN (
	SELECT
		billid,
		min(stage_code) stage_code_min,
		max(stage_code) stage_code_max
	FROM
		(
		SELECT
			OMSBILL.billid,
			COALESCE( x.stage_code, '机动' ) stage_code
		FROM
			OMS_DO_BILL_H OMSBILL
			LEFT JOIN WMS_WORK_STAGE_RUNTIME x ON OMSBILL.BILLID = x.bill_group_id
		WHERE
			OMSBILL.billid = #{omsBillId}
		) X
		GROUP BY x.billid
	) XXX1 ON 1 = 1
	LEFT JOIN (
	SELECT
		sum( pcs ) pcs
	FROM
		(
		SELECT
			floor( sum( qty ) / case_conversion_rate ) pcs,
			SRC_ID
		FROM
			(
			SELECT DISTINCT
				g.id,
				g.qty,
				m.case_conversion_rate,
				n.SRC_ID
			FROM
				WMS_BARCODE_D g
				LEFT JOIN wms_pick_task_d d ON d.PICK_TASK_D_ID = g.WMS_PICK_TASK_D_ID
				LEFT JOIN wms_wave_storage s ON d.WAVE_STORAGE_ID = s.ID
				LEFT JOIN WMS_WAVE_BILL_D m ON m.ID = s.BILL_D_ID
				LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
				LEFT JOIN WMS_WAVE_BILL n ON n.BILL_GROUP_ID = f.BILL_GROUP_ID
			WHERE
				n.SRC_ID =#{omsBillId}
				AND g.qty > 0
				AND g.IS_PIECE = 0
			) c
		GROUP BY
			SRC_ID,
			case_conversion_rate
		) z
	) XXX2 ON 1 = 1
	LEFT JOIN (
		SELECT
			CASE WHEN
			sum(  b.item_qty - COALESCE ( x3.RUSHREDNUMB, 0 ) ) &lt;= 0 THEN
				'Y' ELSE 'N'
			END red_flag
		FROM
			oms_do_bill_d b
			LEFT JOIN (SELECT SRC_LINE_ID, sum( t.qty ) RUSHREDNUMB FROM wms_sale_red_record t WHERE t.src_id = #{omsBillId} GROUP BY SRC_LINE_ID) X3 ON 1 = 1
			AND b.LINEID = x3.SRC_LINE_ID
		WHERE
			b.billid = #{omsBillId}
	) redFlag ON 1 = 1
WHERE
	1 = 1
	AND OMSBILL.BILLID = #{omsBillId}
    </select>


	<select id="getD" resultType="com.prolog.cs.biz.outbound.model.OutcomingRecordD">
		SELECT @rownum:=@rownum+1 refSeqno,k.* from (
		WITH R AS (
		SELECT BILLNO
-- 		,max(MERGENUMBER) MERGENUMBER
		,max(REVIEWSTAGENO) REVIEWSTAGENO
-- 		,STAGAREADESCRIP
		,RT_REASON,RETURNGOODSTATUS,WMSSCHEDTIME,DISPATCHER
-- 		,EXTERREVIEWER,EXTERREVIEWER2
		from (
		SELECT DISTINCT
		OMSBILL.BILLNO,
-- 		barcodeH.LCL_NO MERGENUMBER,
		PICKTASKH.STATION_CODE REVIEWSTAGENO,
-- 		WWSR.STAGE_CODE STAGAREADESCRIP,
		'-' RT_REASON,
		'-' RETURNGOODSTATUS,
		BILL.GMT_CREATE WMSSCHEDTIME,
		BILL.creator_name DISPATCHER
-- 		BARCODEH.checker_name EXTERREVIEWER,
-- 		BARCODEH.checker_name_2 EXTERREVIEWER2
		FROM
		OMS_DO_BILL_H OMSBILL
		LEFT JOIN WMS_WAVE_BILL BILL ON BILL.SRC_ID = OMSBILL.BILLID
		LEFT JOIN WMS_PICK_TASK_H PICKTASKH ON PICKTASKH.bill_group_id = BILL.bill_group_id
-- 		LEFT JOIN WMS_BARCODE_H BARCODEH ON BARCODEH.BILL_GROUP_ID = BILL.BILL_GROUP_ID
-- 		LEFT JOIN WMS_WORK_STAGE_RUNTIME WWSR ON WWSR.bill_group_id = BILL.BILL_GROUP_ID
		WHERE
		1 = 1
		AND OMSBILL.BILLID = #{omsBillId} and PICKTASKH.type='0'
		) l GROUP BY BILLNO
-- 		,STAGAREADESCRIP
		,RT_REASON,RETURNGOODSTATUS,WMSSCHEDTIME,DISPATCHER
-- 		,EXTERREVIEWER,EXTERREVIEWER2
		),
		A AS ( SELECT * FROM ( SELECT T.*, 1 ROW_FLG FROM R T WHERE 1 = 1 ) X WHERE 1 = 1 AND ROW_FLG = 1 ),
		B AS (
		SELECT
		SRC_LINE_ID,
		x.reason RUSHREDREASON
		FROM
		(
		SELECT
		T.SRC_LINE_ID,
		t.reason,
		ROW_NUMBER ( ) OVER ( PARTITION BY coalesce( t.SRC_LINE_ID, '-1' ) ORDER BY T.GMT_CREATE DESC ) AS ROW_FLG
		FROM
		wms_sale_red_record T
		WHERE
		1 = 1
		AND SRC_ID = #{omsBillId}
		) X
		WHERE
		1 = 1
		AND ROW_FLG = 1
		),
		C AS (
		SELECT
		count( * ) TOT_BOX_NUM_X
		FROM
		WMS_BARCODE_H barcodeH
		LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = barcodeH.BILL_GROUP_ID
		WHERE
		bill.src_id = #{omsBillId}
		AND barcodeH.TYPE = '0'
		)

		SELECT
		OMSBILLD.LINEID lineId,
		OMSBILL.BILLID BILLID,
		OMSBILL.BILLNO BILLNO,
		OMSBILLD.SEQNO SEQNO,
		OMSBILL.REF_BILLTYPE,
		OMSBILL.REF_BILLID,
		OMSBILL.REF_BILLNO,
		OMSBILL.CONSIGNORID,
		coalesce(OMSBILL.CONSIGNORCODE,OMSBILL.CONSIGNORID) CONSIGNORCODE,
		OMSBILL.CONSIGNORNAME,
		OMSBILLD.ITEMID,
		OMSBILLD.ITEMCODE,
		OMSBILLD.ITEMNAME,
		OMSBILLD.PARERNT_ITEMID parerntItemid,
		OMSBILLD.TYPE_ID categoryId,
		OMSBILLD.PACK_SIZE packSize,
		OMSBILLD.PACK_UNITS packUnits,
		OMSBILLD.MACH_TYPE machType,
		s.manufacturer_lot_num lotNo,
		s.batch_num batchNo,
		date_format(s.PRODUCTION_DATE,'%Y-%m-%d %H:%i:%S') pdateFrom,
		date_format(s.EXPIRED_DATE,'%Y-%m-%d %H:%i:%S') validUntil,
		OMSBILLD.MANUFACTURER,
		coalesce( floor((sum(ifnull(d.pick_qty,d.qty))  - coalesce(x3.RUSHREDNUMB, 0) ) / BILLD.CASE_CONVERSION_RATE) , OMSBILLD.ITEM_PCS) affirmedPcs,
		coalesce( MOD(( sum(ifnull(d.pick_qty,d.qty))  - coalesce(x3.RUSHREDNUMB, 0) ) ,BILLD.CASE_CONVERSION_RATE),OMSBILLD.ITEM_ODD) affirmedOdd,
		coalesce(OMSBILLD.FREE_QTY,0) affirmedFreeqty,
		sum(ifnull(d.pick_qty,d.qty)) affirmedQty,
		COALESCE( BILLD.TOTAL_WEIGHT, 0 ) affirmedWeight,
		COALESCE( BILLD.TOTAL_VOLUME, 0 ) affirmedVolume,
		coalesce(OMSBILLD.PRICE,0) PRICE,
		OMSBILLD.TNI_PRICE tniPrice,
		coalesce(OMSBILLD.PRICE,0) * BILLD.PIECE_QTY AMT,
		OMSBILLD.TNI_PRICE * BILLD.PIECE_QTY TNI_AMT,
		OMSBILLD.TNI_PRICE PIECE_COST_TE,
		coalesce(OMSBILLD.PRICE,0) PIECE_COST_TI,
		CASE WHEN ( coalesce( OMSBILLD.price / OMSBILLD.tni_price, 0 ) ) = 0 THEN
		13 ELSE floor( ( ( coalesce( OMSBILLD.price / OMSBILLD.tni_price, 0 ) ) - 1 ) * 100 )
		END TAX_RATE,
		X2.TOT_BOX_NUM_X MERGENUMBER,
		d.picker_name SORTER,
		d.picker_no PICKER_NO,
		d.checker_name REVIEWER,
		d.checker_name_2 TWOREVIEWER,
		d.warehouse_area_code attrStr1,
		A.REVIEWSTAGENO,
		coalesce(x3.RUSHREDNUMB,0) RUSHREDNUMB,
		x1.RUSHREDREASON RUSHREDREASON,
-- 		A.STAGAREADESCRIP,
		A.RT_REASON rtReason,
		A.RETURNGOODSTATUS,
		A.WMSSCHEDTIME,
		A.DISPATCHER,
-- 		A.EXTERREVIEWER,
-- 		A.EXTERREVIEWER2,
		OMSBILLD.REMARK,
		OMSBILLD.CREATEBY,
		OMSBILLD.CREATEDATE,
		OMSBILLD.LASTMODIFYBY,
		OMSBILLD.LASTMODIFYDATE,
		OMSBILL.P_ENTERPRISEID enterpriseid,
		OMSBILL.P_ENTERPRISENAME enterprisename,
		X2.TOT_BOX_NUM_X SUMBOXNUM
		FROM
		OMS_DO_BILL_D OMSBILLD
		LEFT JOIN OMS_DO_BILL_H OMSBILL ON OMSBILL.BILLID = OMSBILLD.BILLID
		LEFT JOIN WMS_WAVE_BILL BILL ON BILL.SRC_ID = OMSBILL.BILLID
		LEFT JOIN WMS_WAVE_BILL_D BILLD ON BILL.ID = BILLD.BILL_ID
		AND OMSBILLD.ITEMID = BILLD.ITEM_ID
		AND BILLD.Src_Line_Id = OMSBILLD.LINEID
		LEFT JOIN WMS_WAVE_STORAGE s on BILLD.ID = s.BILL_D_ID
		LEFT JOIN wms_pick_task_d d on d.wave_storage_id = s.id
		LEFT JOIN A ON 1 = 1
		LEFT JOIN B X1 ON 1 = 1
		AND OMSBILLD.LINEID = x1.SRC_LINE_ID
		LEFT JOIN C X2 ON 1 = 1
		LEFT JOIN (SELECT SRC_LINE_ID, sum( t.qty ) RUSHREDNUMB FROM wms_sale_red_record t WHERE t.src_id = #{omsBillId} GROUP BY SRC_LINE_ID) X3 ON 1 = 1
		AND OMSBILLD.LINEID = x3.SRC_LINE_ID
		WHERE
		OMSBILLD.BILLID = #{omsBillId}
		group by OMSBILLD.LINEID,d.item_id,d.batch_id
		) k
		join (SELECT @rownum:=0) t
	</select>

	<select id="findNoBackOmsBill" resultType="java.lang.String">
		SELECT billid FROM oms_do_bill_h
		WHERE status &lt;&gt; '5' and billid not in (SELECT billid from oms_packing_data)
	</select>

	<select id="finishPickTaskAll" resultType="java.lang.String">
		select * from
		(select
		n.src_id srcCode
		from WMS_BARCODE_H f
		left join WMS_WAVE_BILL_D m on f.bill_group_id = m.merged_bill_id
		left join WMS_WAVE_BILL n on m.bill_id = n.id
		where 1=1
		<if test="point =='2'">
			f.check_status ='5'
		</if>
		and f.bill_group_id not in (
		SELECT DISTINCT BILL_GROUP_ID FROM (
		SELECT BILL_GROUP_ID FROM WMS_PICK_TASK_H wpth WHERE ((type = 0 and PICK_TASK_TYPE = 0 AND STATUS in ('0','1')) or (type = 1 and PICK_TASK_TYPE = 0 AND STATUS &lt;&gt; '5'))
		UNION
		SELECT BILL_GROUP_ID FROM WMS_PICK_TASK_H wpth WHERE PICK_TASK_TYPE in (1,2,3) AND STATUS &lt;&gt; '10'
		) b
		)
		GROUP BY n.src_id
		) a
		where 1=1
		and a.srcCode not in (select DISTINCT f.bill_group_id from WMS_BARCODE_H f where f.check_status is null
		)
		<if test="srcCode != null and srcCode != ''">
			and a.srcCode = #{srcCode}
		</if>
	</select>

	<select id="getOmsPackingData" resultType="com.prolog.cs.biz.outbound.model.OmsPackingData">
	SELECT a.id,a.billid,a.billno,a.branchid,a.consignorid,a.refBillid,a.refBillno,
	a.shipperid,a.shippercode,a.shippername,a.boxmode,a.mergenumber,a.unionbillcode ,
	a.p_enterpriseid enterpriseid,a.p_enterprisename enterprisename,a.tms_flag,a.confirmdate,a.boxcode,
	sum(a.totweight*a.totqty) totweight,sum(a.totvolume*a.totqty) totvolume,
	SUM(a.totqty) totqty,if(a.type='1',1,0) wholenum,if(a.type='0',1,0) totboxnum,
	a.isgreenroad,if(a.type='0',1,SUM(a.totpcs)) totpcs,a.stagecode,now() CREATEDATE,now() LASTMODIFYDATE
	FROM
	(SELECT DISTINCT
		g.id,
		obd.seqno,
		obd.seqno refSeqno,
		obh.billid,
		obh.billno,
		s.item_id itemid,
		s.warehouse_id branchid,
		obh.consignorid,
		obh.ref_billid refBillid,
		obh.ref_billno refBillno,
		obh.shipperid,
		obh.shippercode,
		obh.shippername,
		f.type,
	CASE
		WHEN f.type = '0' THEN
		'2'
		WHEN f.type = '1' THEN
		'1'
		END AS boxmode,
		f.lcl_no mergenumber,
		n.bill_group_id unionbillcode,
		f.check_time_2 createdate,
		f.check_time_2 lastmodifydate,
		f.checker_name createby,
		f.checker_name lastmodifyby,
		obh.p_enterpriseid,
		obh.p_enterprisename,
		'0' tms_flag,
		f.check_time_2 confirmdate,
		g.id boxcode,
		ifnull(m.total_weight,0)/m.piece_qty totweight,
		ifnull(m.total_VOLUME,0)/m.piece_qty totvolume,
		CASE
		WHEN f.type = '0' THEN (CASE WHEN (d.check_qty =0 OR d.check_qty is null) THEN d.pick_qty ELSE d.check_qty END)
		WHEN f.type = '1' THEN d.pick_qty END totqty,
		0 isgreenroad,
		CASE
			WHEN f.type = '0' THEN
			1
			ELSE (d.pick_qty/m.conversion_rate)
		END AS totpcs,
		x.stage_code stagecode
	FROM
		WMS_BARCODE_D g
		LEFT JOIN wms_pick_task_d d ON d.BARCODE_D_ID = g.id
		LEFT JOIN wms_wave_storage s ON d.WAVE_STORAGE_ID = s.ID
		LEFT JOIN WMS_WAVE_BILL_D m ON m.ID = s.BILL_D_ID
		LEFT JOIN OMS_DO_BILL_D obd ON m.Src_Line_Id = obd.LINEID
		LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
		LEFT JOIN WMS_WAVE_BILL n ON n.BILL_GROUP_ID = f.BILL_GROUP_ID
		LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
		LEFT JOIN (
			SELECT min(STAGE_CODE) STAGE_CODE,BILL_GROUP_ID from WMS_WORK_STAGE_RUNTIME where BILL_GROUP_ID = #{billGroupId} GROUP BY BILL_GROUP_ID
		) x on x.BILL_GROUP_ID = obh.billno
		WHERE  1=1
		AND f.type= '0'
		AND n.BILL_GROUP_ID = #{billGroupId}
		UNION ALL
		SELECT DISTINCT
		g.id,
		obd.seqno,
		obd.seqno refSeqno,
		obh.billid,
		obh.billno,
		s.item_id itemid,
		s.warehouse_id branchid,
		obh.consignorid,
		obh.ref_billid refBillid,
		obh.ref_billno refBillno,
		obh.shipperid,
		obh.shippercode,
		obh.shippername,
		f.type,
	CASE
		WHEN f.type = '0' THEN
		'2'
		WHEN f.type = '1' THEN
		'1'
		END AS boxmode,
		f.lcl_no mergenumber,
		n.bill_group_id unionbillcode,
		f.check_time_2 createdate,
		f.check_time_2 lastmodifydate,
		f.checker_name createby,
		f.checker_name lastmodifyby,
		obh.p_enterpriseid,
		obh.p_enterprisename,
		'0' tms_flag,
		f.check_time_2 confirmdate,
		g.id boxcode,
		ifnull(m.total_weight,0)/m.piece_qty totweight,
		ifnull(m.total_VOLUME,0)/m.piece_qty totvolume,
		CASE
		WHEN f.type = '0' THEN (CASE WHEN (d.check_qty =0 OR d.check_qty is null) THEN d.pick_qty ELSE d.check_qty END)
		WHEN f.type = '1' THEN d.pick_qty END totqty,
		0 isgreenroad,
		CASE
			WHEN f.type = '0' THEN
			1
			ELSE (d.pick_qty/m.conversion_rate)
		END AS totpcs,
		x.stage_code stagecode
	FROM
		WMS_BARCODE_D g
		LEFT JOIN wms_pick_task_d d ON d.pick_task_d_id = g.wms_pick_task_d_id
		LEFT JOIN wms_wave_storage s ON d.WAVE_STORAGE_ID = s.ID
		LEFT JOIN WMS_WAVE_BILL_D m ON m.ID = s.BILL_D_ID
		LEFT JOIN OMS_DO_BILL_D obd ON m.Src_Line_Id = obd.LINEID
		LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
		LEFT JOIN WMS_WAVE_BILL n ON n.BILL_GROUP_ID = f.BILL_GROUP_ID
		LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
		LEFT JOIN (
			SELECT min(STAGE_CODE) STAGE_CODE,BILL_GROUP_ID from WMS_WORK_STAGE_RUNTIME where BILL_GROUP_ID = #{billGroupId} GROUP BY BILL_GROUP_ID
		) x on x.BILL_GROUP_ID = obh.billno
		WHERE  1=1
		AND f.type= '1'
		AND n.BILL_GROUP_ID = #{billGroupId}) a
		GROUP BY a.id,a.billid,a.billno,a.branchid,a.consignorid,a.refBillid,a.refBillno,a.shipperid,
		a.shippercode,a.shippername,a.boxmode,a.mergenumber,a.unionbillcode ,a.p_enterpriseid,
		a.p_enterprisename,a.tms_flag,a.confirmdate,a.boxcode,a.isgreenroad,a.stagecode
	</select>

	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap" type="com.prolog.cs.biz.outbound.model.dto.PrintAgDateDto">
		<id column="H_BILLID" property="billid"/>
		<result column="H_BILLNO" property="billno"/>
		<result column="BILLTYPE" property="billtype"/>
		<result column="BRANCHID" property="branchid"/>
		<result column="BRANCHCODE" property="branchcode"/>
		<result column="BRANCHNAME" property="branchname"/>
		<result column="H_CONSIGNORID" property="consignorid"/>
		<result column="H_CONSIGNORCODE" property="consignorcode"/>
		<result column="H_CONSIGNORNAME" property="consignorname"/>
		<result column="CONSIGNOR_BILL_NO" property="consignorBillNo"/>
		<result column="REFBILLTYPE" property="refbilltype"/>
		<result column="REFBILLID" property="refbillid"/>
		<result column="REFBILLNO" property="refbillno"/>
		<result column="SALESPERSON_ID" property="salespersonId"/>
		<result column="SALESPERSON_NAME" property="salespersonName"/>
		<result column="PURORG_ID" property="purorgId"/>
		<result column="PURORG_NAME" property="purorgName"/>
		<result column="BRANCHAREA" property="brancharea"/>
		<result column="BRANCH_ADDRESS" property="branchAddress"/>
		<result column="BUSINESS_NO" property="businessNo"/>
		<result column="SHIPPER_TYPE" property="shipperType"/>
		<result column="SHIPPERID" property="shipperId"/>
		<result column="SHIPPERCODE" property="shippercode"/>
		<result column="SHIPPERNAME" property="shippername"/>
		<result column="PRIORITY_LEVEL" property="priorityLevel"/>
		<result column="ARRIVAL_MODE" property="arrivalMode"/>
		<result column="SHIP_LINE_ID" property="shilineId"/>
		<result column="CARRIER_ID" property="carrierId"/>
		<result column="CARRIER_NAME" property="carrierName"/>
		<result column="STATUS" property="status"/>
		<result column="BILL_OUT_TIME" property="billOutTime"/>
		<result column="CONFIRMTIME" property="confirmtime"/>
		<result column="OPERATOR" property="operator"/>
		<result column="CONFIRMER" property="confirmer"/>
		<result column="TOT_PCS" property="totPcs"/>
		<result column="TOT_QTY" property="totQty"/>
		<result column="TOT_BOX_NUM" property="totBoxNum"/>
		<result column="TOT_WEIGHT" property="totWeight"/>
		<result column="TOT_VOLUME" property="totVolume"/>
		<result column="EXTERNALREVIEWER" property="externalreviewer"/>
		<result column="CHECKDATE" property="checkdate"/>
		<result column="WMSSCHEDTIME" property="wmsschedtime"/>
		<result column="H_REMARK" property="remark"/>
		<result column="H_CREATEBY" property="createby"/>
		<result column="H_CREATEDATE" property="createdate"/>
		<result column="H_LASTMODIFYBY" property="lastmodifyby"/>
		<result column="H_LASTMODIFYDATE" property="lastmodifydate"/>
		<result column="H_P_ENTERPRISEID" property="enterpriseid"/>
		<result column="H_P_ENTERPRISENAME" property="enterprisename"/>
		<result column="STAGE_CODE_MIN" property="stageCodeMin"/>
		<result column="STAGE_CODE_MAX" property="stageCodeMax"/>
		<result column="CUST_PERSON" property="custPerson"/>
		<result column="CUST_PERSON_TEL" property="custPersonTel"/>
		<result column="RECEIVE_ADDRESS" property="receiveAddress"/>
		<result column="BILL_DATE" property="billDate"/>
		<result column="PRINT_COUNT" property="printCount"/>
		<result column="CAOZY" property="czy"/>
		<result column="LINE_NAME" property="lineName"/>
		<result column="CONTRACTLIST" property="contractList"/>
		<result column="GRSH_FLAG" property="grshFlag"/>
		<result column="IS_ACCOMPANY_INVOICE" property="isAccompanyInvoice"/>
		<result column="REJECT_HANDLE_TYPE" property="rejectHandleType"/>
		<result column="barCodeId" property="barCodeId"/>
		<result column="outboundPrintCount" property="outboundPrintCount"/>
		<result column="reportPrintCount" property="reportPrintCount"/>
		<collection property="outcomingRecordDList" ofType="com.prolog.cs.biz.outbound.model.OutcomingRecordD">
			<id 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="PRICE" property="price"/>
			<result column="TNI_PRICE" property="tniPrice"/>
			<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="REVIEWSTAGENO" property="reviewstageno"/>
			<result column="SUMBOXNUM" property="sumboxnum"/>
			<result column="MERGENUMBER" property="mergenumber"/>
			<result column="REMARK" property="remark"/>
			<result column="STAGAREADESCRIP" property="stagareadescrip"/>
			<result column="DISPATCHER" property="dispatcher"/>
			<result column="SORTER" property="sorter"/>
			<result column="REVIEWER" property="reviewer"/>
			<result column="TWOREVIEWER" property="tworeviewer"/>
			<result column="EXTERREVIEWER" property="exterreviewer"/>
			<result column="EXTERREVIEWER2" property="exterreviewer2"/>
			<result column="RUSHREDNUMB" property="rushrednumb"/>
			<result column="RUSHREDREASON" property="rushredreason"/>
			<result column="RT_REASON" property="rtReason"/>
			<result column="RETURNGOODSTATUS" property="returngoodstatus"/>
			<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="enterpriseid"/>
			<result column="P_ENTERPRISENAME" property="enterprisename"/>
		</collection>
	</resultMap>

	<select id="getAccompanyingListData" resultMap="BaseResultMap">
		SELECT
			h.BILLID H_BILLID,
			h.BILLNO H_BILLNO,
			h.BILLTYPE,
			h.BRANCHID,
			h.BRANCHCODE,
			h.BRANCHNAME,
			h.CONSIGNORID H_CONSIGNORID,
			h.CONSIGNORCODE H_CONSIGNORCODE,
			h.CONSIGNORNAME H_CONSIGNORNAME,
			h.CONSIGNOR_BILL_NO,
			h.REFBILLTYPE,
			h.REFBILLID,
			h.REFBILLNO,
			h.SALESPERSON_ID,
			h.SALESPERSON_NAME,
			h.PURORG_ID,
			h.PURORG_NAME,
			h.BRANCHAREA,
			h.BRANCH_ADDRESS,
			h.BUSINESS_NO,
			h.SHIPPER_TYPE,
			h.SHIPPERCODE,
			h.SHIPPERNAME,
			h.PRIORITY_LEVEL,
			h.ARRIVAL_MODE,
			h.SHIP_LINE_ID,
			h.CARRIER_ID,
			h.CARRIER_NAME,
			h.STATUS,
			h.BILL_OUT_TIME,
			h.CONFIRMTIME,
			h.OPERATOR,
			h.CONFIRMER,
			h.TOT_PCS,
			h.TOT_QTY,
			h.TOT_BOX_NUM,
			h.TOT_WEIGHT,
			h.TOT_VOLUME,
			h.EXTERNALREVIEWER,
			h.CHECKDATE,
			h.WMSSCHEDTIME,
			h.REMARK H_REMARK,
			h.CREATEBY H_CREATEBY,
			h.CREATEDATE H_CREATEDATE,
			h.LASTMODIFYBY H_LASTMODIFYBY,
			h.LASTMODIFYDATE H_LASTMODIFYDATE,
			h.P_ENTERPRISEID H_P_ENTERPRISEID,
			h.P_ENTERPRISENAME H_P_ENTERPRISENAME,
			h.STAGE_CODE_MIN,
			h.STAGE_CODE_MAX,
			mh.SHIPPERID,
			mh.CUST_PERSON,
			mh.CUST_PERSON_TEL,
			mh.RECEIVE_ADDRESS,
			mh.ATTAIN_DATE,
			mh.CAOZY,
			mh.BILLTYPE,
			mh.LINE_NAME,
			mh.BILL_DATE,
			md.APPROVAL_NUMBER,
			d.LINE_ID,
			d.BILLID,
			d.BILLNO,
			d.SEQNO,
			d.REF_BILLTYPE,
			d.REF_BILLID,
			d.REF_BILLNO,
			d.REF_SEQNO,
			d.CONSIGNORID,
			d.CONSIGNORCODE,
			d.CONSIGNORNAME,
			d.ITEMID,
			d.ITEMCODE,
			d.ITEMNAME,
			d.PARERNT_ITEMID,
			d.CATEGORY_ID,
			d.PACK_SIZE,
			d.PACK_UNITS,
			d.MACH_TYPE,
			d.LOT_NO,
			d.BATCH_NO,
			d.PDATE_FROM,
			d.VALID_UNTIL,
			d.MANUFACTURER,
			d.PRICE,
			d.TNI_PRICE,
			d.AFFIRMED_PCS,
			d.AFFIRMED_ODD,
			d.AFFIRMED_FREEQTY,
			d.AFFIRMED_QTY,
			d.AFFIRMED_WEIGHT,
			d.AFFIRMED_VOLUME,
			d.REVIEWSTAGENO,
			d.SUMBOXNUM,
			d.MERGENUMBER,
			d.REMARK,
			d.STAGAREADESCRIP,
			d.DISPATCHER,
			d.SORTER,
			d.REVIEWER,
			d.TWOREVIEWER,
			d.EXTERREVIEWER,
			d.EXTERREVIEWER2,
			d.RUSHREDNUMB,
			d.RUSHREDREASON,
			d.RT_REASON,
			d.RETURNGOODSTATUS,
			d.CREATEBY,
			d.CREATEDATE,
			d.LASTMODIFYBY,
			d.LASTMODIFYDATE,
			d.P_ENTERPRISEID,
			d.P_ENTERPRISENAME,
			h.outbound_print_count outboundPrintCount
		FROM OUTCOMING_RECORD_H h
		LEFT JOIN OUTCOMING_RECORD_D d ON h.BILLID=d.BILLID
		LEFT JOIN OMS_DO_BILL_H mh ON h.BILLID=mh.BILLID
		LEFT JOIN OMS_DO_BILL_D md ON h.BILLID=md.BILLID
		WHERE h.BILLNO IN(
		<foreach collection="omsBillId" separator="," item="item" index="index">
			#{item}
		</foreach>)
		ORDER BY d.ITEMNAME ASC,d.ITEMID ASC, d.BATCH_NO ASC
	</select>

	<select id="queryPrintList" resultType="com.prolog.cs.biz.outbound.model.dto.RePrintWholeDto">
		SELECT
			h.BILLNO billNo,
			h.createdate createtime,
			SUM(md.item_pcs) pcs,
			SUM(md.item_qty) qty,
			IFNULL(h.outbound_print_count, 0) printcount4
		FROM OUTCOMING_RECORD_H h
		LEFT JOIN OMS_DO_BILL_H mh ON h.BILLID=mh.BILLID
		LEFT JOIN OMS_DO_BILL_D md ON h.BILLID=md.BILLID
		WHERE 1=1
		<if test="params.billNo != null and params.billNo != ''">
			and d.BILLNO = #{params.billNo}
		</if>
		<if test="params.startTimeStr != null and params.startTimeStr != ''">
			and h.createdate &gt;= DATE_FORMAT(#{params.startTimeStr},'%Y-%m-%d')
		</if>
		<if test="params.endTimeStr != null and params.endTimeStr != ''">
			and h.createdate &lt;= DATE_FORMAT(#{params.endTimeStr},'%Y-%m-%d')
		</if>
		group by billNo, createtime, printcount4
		order by h.createdate desc
	</select>

	<update id="updatePrintCount">
		UPDATE outcoming_record_h
		SET `outbound_print_count` = IFNULL(`outbound_print_count`, 0) + 1
		WHERE billid = #{omsBillId}
	</update>
</mapper>