<?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.PickTaskHMapper">

    <resultMap id="storageMap" type="com.prolog.cs.biz.outbound.model.dto.WaveStorageDTO">
        <result column="id" property="id"/>
        <result column="wave_bill_d_id" property="waveBillDId"/>
        <result column="warehouse_id" property="warehouseId"/>
        <result column="batch_id" property="batchId"/>
        <result column="batch_num" property="batchNum"/>
        <result column="qty" property="qty"/>
        <result column="uom" property="uom"/>
        <result column="locator_id" property="warehouseLocatorId"/>
        <result column="locator_type" property="warehouseLocatorType"/>
        <result column="expired_date" property="expiredDate"/>
        <result column="production_date" property="productionDate"/>
        <result column="shelf_life_days" property="shelfLifeDays"/>
        <result column="volume" property="volume"/>
        <result column="weight" property="weight"/>
        <result column="warehouse_area_code" property="warehouseAreaCode"/>
        <result column="pick_model" property="pickModel"/>
        <result column="floor_num" property="floorNum"/>
        <result column="locator_lane_code" property="locatorLaneCode"/>
        <result column="out_index" property="outIndex"/>
        <result column="pick_piece_group" property="pickPieceGroup"/>
        <result column="segment1" property="segment1"/>
        <result column="segment2" property="segment2"/>
        <result column="segment3" property="segment3"/>
        <result column="segment4" property="segment4"/>
        <result column="segment5" property="segment5"/>
        <result column="creator" property="creator"/>
        <result column="creator_name" property="creatorName"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="modifier" property="modifier"/>
        <result column="modifier_name" property="modifierName"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="enterprise_id" property="enterpriseId"/>
        <result column="status" property="status"/>
        <result column="case_conversion_rate" property="caseConversionRate"/>
        <result column="wave_id" property="waveId"/>
        <result column="bill_id" property="billId"/>
        <result column="manufacturer_lot_num" property="manufacturerLotNum"/>
        <result column="bldg_id" property="bldgId"/>
        <result column="bldg_name" property="bldgName"/>
        <result column="BILL_GROUP_ID" property="mergedBillId"/>
        <result column="locator_code" property="locatorCode"/>
        <result column="area_category" property="areaCategory"/>
        <result column="item_id" property="itemId"/>
        <result column="item_code" property="itemCode"/>
        <result column="production_date_show" property="productionDateShow"/>
        <result column="expired_date_show" property="expiredDateShow"/>
        <result column="consignor_id" property="consignorId"/>
        <result column="container_code" property="containerCode"/>
        <result column="container_code_qty" property="containerCodeQty"/>
    </resultMap>

    <resultMap id="storageMapNew" type="com.prolog.cs.biz.outbound.model.dto.WaveStorageDTO">
        <result column="id" property="id"/>
        <result column="wave_bill_d_id" property="waveBillDId"/>
        <result column="warehouse_id" property="warehouseId"/>
        <result column="batch_id" property="batchId"/>
        <result column="batch_num" property="batchNum"/>
        <result column="qty" property="qty"/>
        <result column="uom" property="uom"/>
        <result column="locator_id" property="warehouseLocatorId"/>
        <result column="locator_type" property="warehouseLocatorType"/>
        <result column="expired_date" property="expiredDate"/>
        <result column="production_date" property="productionDate"/>
        <result column="shelf_life_days" property="shelfLifeDays"/>
        <result column="volume" property="volume"/>
        <result column="weight" property="weight"/>
        <result column="warehouse_area_code" property="warehouseAreaCode"/>
        <result column="pick_model" property="pickModel"/>
        <result column="floor_num" property="floorNum"/>
        <result column="locator_lane_code" property="locatorLaneCode"/>
        <result column="out_index" property="outIndex"/>
        <result column="pick_piece_group" property="pickPieceGroup"/>
        <result column="segment1" property="segment1"/>
        <result column="segment2" property="segment2"/>
        <result column="segment3" property="segment3"/>
        <result column="segment4" property="segment4"/>
        <result column="segment5" property="segment5"/>
        <result column="creator" property="creator"/>
        <result column="creator_name" property="creatorName"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="modifier" property="modifier"/>
        <result column="modifier_name" property="modifierName"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="enterprise_id" property="enterpriseId"/>
        <result column="status" property="status"/>
        <result column="case_conversion_rate" property="caseConversionRate"/>
        <result column="wave_id" property="waveId"/>
        <result column="bill_id" property="billId"/>
        <result column="bill_d_id" property="billDId"/>
        <result column="manufacturer_lot_num" property="manufacturerLotNum"/>
        <result column="bldg_id" property="bldgId"/>
        <result column="bldg_name" property="bldgName"/>
        <result column="merged_bill_id" property="mergedBillId"/>
        <result column="locator_code" property="locatorCode"/>
        <result column="area_category" property="areaCategory"/>
        <result column="item_id" property="itemId"/>
        <result column="production_date_show" property="productionDateShow"/>
        <result column="expired_date_show" property="expiredDateShow"/>
        <result column="consignor_id" property="consignorId"/>
        <result column="priority" property="priority"/>
        <result column="is_merged" property="isMerged"/>
    </resultMap>

    <resultMap id="pickTaskHMap" type="com.prolog.cs.biz.outbound.model.PickTaskH">
        <result column="pick_task_id" property="pickTaskId"/>
        <result column="pick_task_type" property="pickTaskType"/>
        <result column="status" property="status"/>
        <result column="container_code" property="containerCode"/>
        <result column="wave_id" property="waveId"/>
        <result column="warehouse_id" property="warehouseId"/>
        <result column="pick_order_id" property="pickOrderId"/>
        <result column="creator" property="creator"/>
        <result column="creator_name" property="creatorName"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="modifier" property="modifier"/>
        <result column="modifier_name" property="modifierName"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="enterprise_id" property="enterpriseId"/>
        <result column="station_id" property="stationId"/>
        <result column="station_code" property="stationCode"/>
        <result column="station_name" property="stationName"/>
        <result column="pick_piece_group" property="pickPieceGroup"/>
        <result column="bldg_id" property="bldgId"/>
        <result column="bldg_name" property="bldgName"/>
        <result column="warehouse_area_code" property="warehouseAreaCode"/>
        <result column="bill_id" property="billId"/>
        <result column="type" property="type"/>
        <result column="slideway_id" property="slidewayId"/>
    </resultMap>

    <resultMap id="barcodeMap" type="com.prolog.cs.biz.outbound.model.entity.BarcodeHDO">
        <result column="id" property="id"/>
        <result column="type" property="type"/>
        <result column="barcode" property="barcode"/>
        <result column="lcl_no" property="lclNo"/>
        <result column="status" property="status"/>
        <result column="warehouse_id" property="warehouseId"/>
        <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"/>
        <result column="container_code" property="containerCode"/>
        <result column="station_code" property="stationCode"/>
        <result column="print_count" property="printCount"/>
        <result column="print_name" property="printName"/>
        <result column="print_date" property="printDate"/>
        <result column="check_status" property="checkStatus"/>
        <result column="checker_id" property="checkerId"/>
        <result column="checker_no" property="checkerNo"/>
        <result column="checker_name" property="checkerName"/>
        <result column="checker_id_2" property="checkerId2"/>
        <result column="checker_no_2" property="checkerNo2"/>
        <result column="checker_name_2" property="checkerName2"/>
        <result column="check_time" property="checkTime"/>
        <result column="check_time_2" property="checkTime2"/>
        <result column="supervise_code" property="superviseCode"/>
        <result column="bldg_name" property="bldgName"/>
        <result column="bldg_id" property="bldgId"/>
        <result column="is_merged" property="isMerged"/>
        <result column="merged_code" property="mergedCode"/>
        <result column="bill_group_id" property="billGroupId"/>
    </resultMap>

    <select id="getBillDList" resultType="com.prolog.cs.biz.outbound.model.entity.WaveBillDDO">
        select distinct a.BILL_GROUP_ID srcCode,b.id,b.bill_id billId,b.SRC_LINE_ID srcLineId,a.src_id srcId,b.item_id itemId,b.item_name itemName,
        case when b.case_conversion_rate=0 then 1 else IFNULL(b.case_conversion_rate,1) end conversionRate
        from wms_wave_bill a,wms_wave_bill_d b,
        wms_wave_storage c,wms_pick_task_d d
        where b.id = c.bill_d_id and a.id = b.bill_id and d.wave_storage_id = c.id
        and a.warehouse_id = #{warehouseId} and a.enterprise_id = #{enterpriseId}
        and d.wave_storage_id = #{waveStorageId}
    </select>

    <select id="getBillDListV2" resultType="com.prolog.cs.biz.outbound.model.entity.WaveBillDDO">
        select distinct a.BILL_GROUP_ID srcCode,b.id,b.bill_id billId,b.SRC_LINE_ID srcLineId,a.src_id srcId,b.item_id itemId,b.item_name itemName,
        case when b.case_conversion_rate=0 then 1 else IFNULL(b.case_conversion_rate,1) end conversionRate
        from wms_wave_bill a,wms_wave_bill_d b,
        wms_wave_storage c,wms_pick_task_d d
        where b.id = c.bill_d_id and a.id = b.bill_id and d.wave_storage_id = c.id
        and a.warehouse_id = #{warehouseId} and a.enterprise_id = #{enterpriseId}
        and d.wave_storage_id in
        <foreach collection="waveStorageIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>

    </select>

    <select id="getWaveStorageList" resultMap="storageMapNew">
        select distinct a.*,b.CASE_CONVERSION_RATE,c.wave_id,c.is_merged,b.bill_id,b.manufacturer_lot_num,b.merged_bill_id,c.priority from wms_wave_storage a
        left join wms_wave_bill_d b on a.BILL_D_ID = b.id
        left join wms_wave_bill c on b.bill_id = c.id
        left join wms_wave e on e.id = a.wave_id
        where a.status='0' and (a.PICK_MODEL is null or a.PICK_MODEL &lt;&gt; 'Device')
        <choose>
            <when test="waveType != null and waveType != ''">
                and e.type != '1'
            </when>
            <otherwise>
                and (e.type is null or e.type='1')
            </otherwise>
        </choose>
        <if test="enterpriseId != null and enterpriseId != ''">
            and a.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="type != null">
            and a.type = #{type}
        </if>
        <if test="warehouseLocatorType != null and warehouseLocatorType != ''">
            and a.LOCATOR_TYPE = #{warehouseLocatorType}
        </if>
        <if test="locatorTypes != null and locatorTypes.size()>0">
            and a.LOCATOR_TYPE in
            <foreach collection="locatorTypes" item="locatorType" open="(" separator="," close=")">
                #{locatorType}
            </foreach>
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and a.WAREHOUSE_ID = #{warehouseId}
        </if>
        <if test="waveId != null and waveId != ''">
            and c.wave_id = #{waveId}
        </if>
        <if test="consignorId != null and consignorId != ''">
            and c.consignor_id = #{consignorId}
        </if>
        <!-- 老逻辑只要整散合一就是功能库房，所以逻辑改为如果检查是否是功能库房，则将整散合一的商品也找出来 - by Huanglh 2022-03-02 -->
        <if test="isFunctionRoom != null and isFunctionRoom != ''">
            <choose>
                <when test='isFunctionRoom == "1"'>
                    and (a.IS_FUNCTION_ROOM = #{isFunctionRoom} OR a.LOCATOR_TYPE = 2)
                </when>
                <otherwise>
                    and (a.IS_FUNCTION_ROOM = #{isFunctionRoom} or a.IS_FUNCTION_ROOM is null)
                </otherwise>
            </choose>
        </if>
        order by b.merged_bill_id,a.item_id,a.LOCATOR_CODE,a.warehouse_area_code asc,a.volume desc
    </select>

    <select id="getAgvStorageList" resultMap="storageMap">
        select distinct a.*,b.CASE_CONVERSION_RATE,c.wave_id,b.bill_id,b.manufacturer_lot_num,c.BILL_GROUP_ID,c.CONSIGNOR_ID
        from wms_wave_storage a
        left join wms_wave_bill_d b on a.BILL_D_ID = b.id
        left join wms_wave_bill c on b.bill_id = c.id
        left join wms_wave e on e.id = a.wave_id
        where a.status='0'
        <choose>
            <when test="waveType != null and waveType != ''">
                and e.type != '1'
            </when>
            <otherwise>
                and (e.type is null or e.type='1')
            </otherwise>
        </choose>
        <if test="enterpriseId != null and enterpriseId != ''">
            and a.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="type != null">
            and a.type = #{type}
        </if>
        <if test="warehouseLocatorType != null and warehouseLocatorType != ''">
            and a.LOCATOR_TYPE = #{warehouseLocatorType}
        </if>
        <if test="locatorTypes != null and locatorTypes.size()>0">
            and a.LOCATOR_TYPE in
            <foreach collection="locatorTypes" item="locatorType" open="(" separator="," close=")">
                #{locatorType}
            </foreach>
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and a.WAREHOUSE_ID = #{warehouseId}
        </if>
        <if test="waveId != null and waveId != ''">
            and c.wave_id = #{waveId}
        </if>
        <if test="consignorId != null and consignorId != ''">
            and c.consignor_id = #{consignorId}
        </if>
        order by c.BILL_GROUP_ID,a.item_id,a.LOCATOR_CODE,a.warehouse_area_code asc,a.volume desc
    </select>

    <select id="getWaveStorageDeviceList" resultMap="storageMap">
        select distinct a.*,b.CASE_CONVERSION_RATE,c.wave_id,b.bill_id,b.manufacturer_lot_num,b.item_code,c.BILL_GROUP_ID from wms_wave_storage a
        left join wms_wave_bill_d b on a.BILL_D_ID = b.id
        left join wms_wave_bill c on b.bill_id = c.id
        left join wms_wave e on e.id = a.wave_id
        where a.status='0' and a.PICK_MODEL = 'Device'
        <choose>
            <when test="waveType != null and waveType != ''">
                and e.type != '1'
            </when>
            <otherwise>
                and (e.type is null or e.type='1')
            </otherwise>
        </choose>
        <if test="enterpriseId != null and enterpriseId != ''">
            and a.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="type != null">
            and a.type = #{type}
        </if>
        <if test="locatorTypes != null and locatorTypes.size()>0">
            and a.LOCATOR_TYPE in
            <foreach collection="locatorTypes" item="locatorType" open="(" separator="," close=")">
                #{locatorType}
            </foreach>
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and a.WAREHOUSE_ID = #{warehouseId}
        </if>
        <if test="waveId != null and waveId != ''">
            and c.wave_id = #{waveId}
        </if>
        order by c.BILL_GROUP_ID,a.item_id,a.LOCATOR_CODE,a.warehouse_area_code asc,a.volume desc
    </select>

    <!--    查询待索取拣选任务列表（整单索取,有补货任务则无法拣货）-->
    <select id="getPickTaskHListByBill2" resultMap="pickTaskHMap">
        select h.*
        from WMS_PICK_TASK_H h
        where h.STATUS =0 and h.TYPE=0 and h.BILL_GROUP_ID =
        (select a.BILL_GROUP_ID from (select h.BILL_GROUP_ID
        from WMS_PICK_TASK_H h LEFT JOIN WMS_WAVE_BILL m on h.BILL_GROUP_ID =m.BILL_GROUP_ID
        where h.STATUS =0 and h.ENTERPRISE_ID=#{enterpriseId}
          and not exists (select 1 from WMS_PICK_TASK_D d1 where d1.PICK_TASK_ID = h.PICK_TASK_ID
          and d1.picker_no is not null and d1.PICKER_NO &lt;&gt; #{operatorCode})
            <if test="warehouseId != null and warehouseId != ''">
                and h.WAREHOUSE_ID=#{warehouseId}
            </if>
            <if test="bldgId != null and bldgId != ''">
                and h.bldg_id = #{bldgId}
            </if>
            <if test="areaCodes != null and areaCodes.size()>0">
                and h.WAREHOUSE_AREA_CODE in
                <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                    #{areaCode}
                </foreach>
            </if>
        order by IF(ISNULL(m.PRIORITY),1,0),m.PRIORITY desc,h.type desc,h.gmt_create asc,m.src_id asc,h.WAREHOUSE_AREA_CODE asc) a limit 1)
        <if test="areaCodes != null and areaCodes.size()>0">
            and h.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
    </select>

    <!--    查询待索取拣选任务列表（整单索取）-->
    <select id="getPickTaskHListByBill" resultMap="pickTaskHMap">
        select DISTINCT h.*
        from WMS_PICK_TASK_H h
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = h.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL m on s.BILL_ID =m.id
        where m.BILL_GROUP_ID =
        (select a.BILL_GROUP_ID from (select DISTINCT m.BILL_GROUP_ID,m.WAVE_ID,m.PRIORITY,h.type,h.gmt_create,m.src_id,h.WAREHOUSE_AREA_CODE
        from WMS_PICK_TASK_H h
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = h.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL m on s.BILL_ID =m.id
        where h.STATUS='0'
        /*非功能库房*/
        <if test="type=='0'.toString()">
            and h.PICK_TASK_TYPE &lt;&gt; '2'
        </if>
        /*功能库房*/
        <if test="type=='1'.toString()">
            and h.PICK_TASK_TYPE ='2'
        </if>
        and h.ENTERPRISE_ID=#{enterpriseId}
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and h.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        order by h.type desc,IF(ISNULL(m.PRIORITY),1,0),m.PRIORITY desc,h.gmt_create asc,m.src_id asc,h.WAREHOUSE_AREA_CODE asc) a limit 1)
        <if test="areaCodes != null and areaCodes.size()>0">
            and h.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <if test="type=='1'.toString()">
            and h.PICK_TASK_TYPE ='2'
        </if>
    </select>

    <!--    查询拣选任务列表，根据优先级排序（零货拣货）-->
    <select id="getPickTaskHList" resultMap="pickTaskHMap">
        select DISTINCT
            wpth.PICK_TASK_ID,
            wpth.BLDG_NAME,
            wpth.PICK_TASK_TYPE,
            wpth.BILL_GROUP_ID,
            wpth.WAREHOUSE_ID,
            wpth.type,
            wpth.WAVE_ID,
            wpth.WAREHOUSE_AREA_CODE,
            wwb.PRIORITY,
            wwb.src_id,
            wpth.gmt_create
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_WAVE_BILL wwb on wpth.bill_group_id =wwb.bill_group_id
        where WPTH.ENTERPRISE_ID = #{enterpriseId}
            and WPTH.WAREHOUSE_ID= #{warehouseId}
            and WPTH.status = '0'
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="containerCode != null and containerCode != ''">
            and wpth.container_code=#{containerCode} and WPTH.status = '1'
        </if>
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and wpth.pick_task_type in
            <foreach collection="pickTaskTypes" item="pickTaskType" open="(" separator="," close=")">
                #{pickTaskType}
            </foreach>
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by wpth.type desc,wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by wpth.type asc,wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        limit #{count}
    </select>

    <select id="getPickTaskWholeList" resultMap="pickTaskHMap">
        select * from (
            SELECT min(wpth.status) AS status, wpth.PICK_TASK_ID, wpth.BLDG_NAME, wpth.PICK_TASK_TYPE
        , wpth.BILL_GROUP_ID, wpth.WAREHOUSE_ID, wpth.type, wpth.WAVE_ID, wpth.WAREHOUSE_AREA_CODE
        , wpth.PRIORITY, wpth.gmt_create
        FROM WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d ON d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN wms_task_out_back wto ON wto.msg_id = d.ob_msg_id
        LEFT JOIN wms_task_out ou ON (ou.seqno = d.pick_task_d_id OR ou.seqno = d.src_pick_task_d_id)
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        AND (ou.msg_id IS NULL OR (ou.msg_id IS NOT NULL AND wto.msg_id IS NOT NULL AND wto.outtype = 1))
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="containerCode != null and containerCode != ''">
            and d.container_code=#{containerCode} and WPTH.status = '1'
        </if>
        <if test="pickTaskIds != null">
            and wpth.pick_task_id in
            <foreach collection="pickTaskIds" item="pickTaskId" open="(" separator="," close=")">
                #{pickTaskId}
            </foreach>
        </if>
        <if test="containerNos != null">
            and d.container_code in
            <foreach collection="containerNos" item="containerNo" open="(" separator="," close=")">
                #{containerNo}
            </foreach>
        </if>
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and wpth.pick_task_type in
            <foreach collection="pickTaskTypes" item="pickTaskType" open="(" separator="," close=")">
                #{pickTaskType}
            </foreach>
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <if test="count != null">
            and WPTH.status = '0'
        </if>
        <choose>
            <when test="workType !=null and workType == 'WHOLE_PICKING'.toString()">
                and wpth.type='0'
            </when>
            <when test="workType !=null and workType == 'SPECIAL_PICKING'.toString()">
                and wpth.type='0'
            </when>
        </choose>
        GROUP BY wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wpth.PRIORITY,wpth.gmt_create
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by wpth.type desc,IF(ISNULL(wpth.PRIORITY),1,0),wpth.PRIORITY desc,wpth.gmt_create asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by wpth.type asc,IF(ISNULL(wpth.PRIORITY),1,0),wpth.PRIORITY desc,wpth.gmt_create asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        ) a
        where 1=1
        <if test="workType == null">
            and (status is null or status='2')
        </if>
        <if test="count != null and pickTaskIds == null and containerNos == null">
            limit #{count}
        </if>
    </select>

    <!--优化SQL备份-->
    <select id="getPickTaskWholeListBack" resultMap="pickTaskHMap">
        select * from (select min(wpth.status) status,wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wpth.gmt_create
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL_D m on m.ID = s.BILL_D_ID
        LEFT JOIN WMS_WAVE_BILL wwb on s.BILL_ID =wwb.id
        LEFT JOIN WMS_MOVE_TASK v on v.BILL_D_ID= m.id
        LEFT JOIN wms_task_out_back wto ON wto.msg_id = d.ob_msg_id
        LEFT JOIN wms_task_out ou ON (ou.seqno = d.pick_task_d_id OR ou.seqno = d.src_pick_task_d_id)
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        AND (ou.msg_id IS NULL OR (ou.msg_id IS NOT NULL AND wto.msg_id IS NOT NULL AND wto.outtype = 1))
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="containerCode != null and containerCode != ''">
            and wpth.container_code=#{containerCode} and WPTH.status = '1'
        </if>
        <if test="pickTaskIds != null">
            and wpth.pick_task_id in
            <foreach collection="pickTaskIds" item="pickTaskId" open="(" separator="," close=")">
                #{pickTaskId}
            </foreach>
        </if>
        <if test="containerNos != null">
            and wto.container_no in
            <foreach collection="containerNos" item="containerNo" open="(" separator="," close=")">
                #{containerNo}
            </foreach>
        </if>
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and wpth.pick_task_type in
            <foreach collection="pickTaskTypes" item="pickTaskType" open="(" separator="," close=")">
                #{pickTaskType}
            </foreach>
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <if test="count != null">
            and WPTH.status = '0'
        </if>
        <choose>
            <when test="workType !=null and workType == 'WHOLE_PICKING'.toString()">
                and wpth.type='0'
            </when>
            <when test="workType !=null and workType == 'SPECIAL_PICKING'.toString()">
                and wpth.type='0'
            </when>
        </choose>
        GROUP BY wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wpth.gmt_create
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by wpth.type desc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by wpth.type asc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        ) a
        where 1=1
        <if test="workType == null">
            and (status is null or status='2')
        </if>
        <if test="count != null and pickTaskIds == null and containerNos == null">
            limit #{count}
        </if>
    </select>

    <select id="listPickTaskWholeRF" resultMap="pickTaskHMap">
        select * from (
        select
            min(wpth.status) status,
            wpth.PICK_TASK_ID,
            wpth.BLDG_NAME,
            wpth.PICK_TASK_TYPE,
            wpth.BILL_GROUP_ID,
            wpth.WAREHOUSE_ID,
            wpth.type,
            wpth.WAVE_ID,
            wpth.WAREHOUSE_AREA_CODE,
            wwb.PRIORITY,
            wwb.src_id,
            wpth.gmt_create
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL_D m on m.ID = s.BILL_D_ID
        LEFT JOIN WMS_WAVE_BILL wwb on s.BILL_ID =wwb.id
        LEFT JOIN WMS_MOVE_TASK v on v.BILL_D_ID= m.id
        LEFT JOIN wms_task_out_back wto ON wto.msg_id = d.ob_msg_id
        LEFT JOIN wms_task_out ou ON (ou.seqno = d.pick_task_d_id OR ou.seqno = d.src_pick_task_d_id)
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        AND (ou.msg_id IS NULL OR (ou.msg_id IS NOT NULL AND wto.msg_id IS NOT NULL AND wto.outtype = 1))
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="containerCode != null and containerCode != ''">
            and wpth.container_code=#{containerCode} and WPTH.status = '1'
        </if>
        <if test="pickTaskIds != null">
            and wpth.pick_task_id in
            <foreach collection="pickTaskIds" item="pickTaskId" open="(" separator="," close=")">
                #{pickTaskId}
            </foreach>
        </if>
        <if test="containerNos != null">
            and wto.container_no in
            <foreach collection="containerNos" item="containerNo" open="(" separator="," close=")">
                #{containerNo}
            </foreach>
        </if>
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and wpth.pick_task_type in
            <foreach collection="pickTaskTypes" item="pickTaskType" open="(" separator="," close=")">
                #{pickTaskType}
            </foreach>
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <if test="count != null">
            and WPTH.status = '0'
        </if>
        GROUP BY wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wpth.gmt_create
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by wpth.type desc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by wpth.type asc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        ) a
        where 1=1
        <if test="workType == null">
            and (status is null or status='2')
        </if>
        <if test="count != null and pickTaskIds == null and containerNos == null">
            limit #{count}
        </if>
    </select>

    <select id="getSpecialPickTaskList" resultMap="pickTaskHMap">
        select * from (
        select min(v.status) status,wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wto.gmt_create,wto.container_no containerNo
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL_D m on m.ID = s.BILL_D_ID
        LEFT JOIN WMS_WAVE_BILL wwb on s.BILL_ID =wwb.id
        LEFT JOIN WMS_MOVE_TASK v on v.BILL_D_ID= m.id
        left join wms_task_out wto on wto.seqno = d.pick_task_d_id and wto.enterprise_id = d.enterprise_id and wto.warehouse_id=d.warehouse_id and wto.wmflg = 'Y'
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="containerCode != null and containerCode != ''">
            and wpth.container_code=#{containerCode} and WPTH.status = '1'
        </if>
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and wpth.pick_task_type in
            <foreach collection="pickTaskTypes" item="pickTaskType" open="(" separator="," close=")">
                #{pickTaskType}
            </foreach>
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <if test="count != null">
            and WPTH.status = '0'
        </if>
        <choose>
            <when test="workType !=null and workType == 'WHOLE_PICKING'.toString()">
                and wpth.type='0'
            </when>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH'.toString()">
                and wpth.type='1'
            </when>
        </choose>
        GROUP BY wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wto.gmt_create,wto.container_no
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by wpth.type desc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by wpth.type asc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        ) a
        where 1=1
        <if test="workType == null">
            and (status is null or status='2')
        </if>
        ORDER BY a.gmt_create asc
        <if test="count != null">
            limit #{count}
        </if>
    </select>

    <!--    查询待索取拣选任务列表（整单补货）-->
    <select id="getPickTaskHListByMove" resultMap="pickTaskHMap">
        select g.* from (select wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,
                              wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wpth.gmt_create
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL wwb on s.BILL_ID =wwb.id
        LEFT JOIN WMS_MOVE_TASK_REF r on r.BILL_ID= wwb.id
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        and wpth.pick_task_type = '0'
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        and wpth.BILL_GROUP_ID =
            (select tt.BILL_GROUP_ID from (select h.BILL_GROUP_ID
            from WMS_PICK_TASK_H h LEFT JOIN WMS_WAVE_BILL m on h.BILL_GROUP_ID =m.BILL_GROUP_ID
            where h.STATUS =0 and h.type=1 and h.ENTERPRISE_ID=#{enterpriseId}
            and not exists (select 1 from WMS_PICK_TASK_D d1 where d1.PICK_TASK_ID = h.PICK_TASK_ID
                                                               and d1.picker_no is not null and d1.PICKER_NO &lt;&gt; #{operatorCode})
            and not exists (select 1 from WMS_PICK_TASK_H h1 where h1.BILL_GROUP_ID = h.BILL_GROUP_ID and h1.type=1 and h1.status = 1)
            <if test="warehouseId != null and warehouseId != ''">
                and h.WAREHOUSE_ID=#{warehouseId}
            </if>
            <if test="bldgId != null and bldgId != ''">
                and h.bldg_id = #{bldgId}
            </if>
            <if test="areaCodes != null and areaCodes.size()>0">
                and h.WAREHOUSE_AREA_CODE in
                <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                    #{areaCode}
                </foreach>
            </if>
            order by IF(ISNULL(m.PRIORITY),1,0),m.PRIORITY desc,h.type desc,h.gmt_create asc,m.src_id asc,h.WAREHOUSE_AREA_CODE asc) tt limit 1)
        and WPTH.status = '0'
        and wpth.type='1'
        GROUP BY wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wpth.gmt_create
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.type desc,wpth.gmt_create asc,wpth.BILL_GROUP_ID asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.type asc,wpth.gmt_create asc,wpth.BILL_GROUP_ID asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        ) g
        where 1=1
        <if test="count != null">
            limit #{count}
        </if>
    </select>

    <select id="getUnConfirmPickCount" resultType="java.lang.Integer">
        SELECT count(*) from WMS_PICK_ORDER where WAREHOUSE_ID=#{warehouseId} and OPERATOR_CODE = #{operatorCode} and PICK_TASK_TYPE in (0,3)
        and STATUS in ('0','1')
    </select>

    <select id="getUnConfirmCount" resultType="java.lang.Integer">
        SELECT count(*) from WMS_PICK_TASK_D WHERE
        PICK_TASK_ID = (SELECT PICK_TASK_ID from WMS_PICK_TASK_D WHERE PICK_TASK_D_ID = #{pickTaskDId} )
        and pick_time is null and qty &gt; coalesce(SALE_RED_QTY, 0) and enterprise_id =#{enterpriseId} and PICK_TASK_D_ID not in
        <foreach collection="pickTaskDIds" item="pickTaskDId" open="(" separator="," close=")">
            #{pickTaskDId}
        </foreach>
    </select>

    <select id="getUnConfirmCountH" resultType="java.lang.Integer">
        SELECT count(*) from WMS_PICK_TASK_H h WHERE h.pick_order_id = #{pickOrderId} and h.status='1' and h.PICK_TASK_ID != #{pickTaskId}
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID=#{warehouseId}
        </if>
    </select>


    <resultMap id="billContainer" type="com.prolog.cs.biz.outbound.model.vo.check.InnerCHeckAllInfoVO">
        <result column="BILL_ID" property="billId"/>
        <result column="STATION_CODE" property="stationCode"/>
        <result column="STATION_NAME" property="taskStatus"/>
        <result column="CONTAINER_CODE" property="containerCode"/>

    </resultMap>




    <select id="getTotalItem" resultType="java.lang.Integer">
        select
        COUNT(distinct wws.item_id )
        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_storage wws on wws.id=taskd.wave_storage_id
        <if test="containerCode != null and containerCode != ''">
            and taskh.container_code = #{containerCode}
        </if>
    </select>

    <select id="getTotalLot" resultType="java.lang.Integer">
        select
        count(distinct wws.batch_id )
        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_storage wws on wws.id=taskd.wave_storage_id
        <if test="containerCode != null and containerCode != ''">
            and taskh.container_code = #{containerCode}
        </if>
    </select>

    <select id="getCheckedItem" resultType="java.lang.Integer">
        select
        count (distinct wws.item_id)
        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_storage wws ON wws.id=taskd.wave_storage_id
        where Taskd.check_qty = taskd.qty
        <if test="containerCode != null and containerCode != ''">
            and taskh.container_code = #{containerCode}
        </if>
    </select>
    <select id="findCheckingTask" resultType="com.prolog.cs.biz.outbound.model.PickTaskH">
        select * from wms_pick_task_h taskh
        where taskh.status in ('0','1','2')
        <if test="containerCode != null and containerCode != ''">
            and taskh.container_code = #{containerCode}
        </if>
    </select>


    <select id="getStatus" resultType="java.lang.String">
        SELECT status
        FROM WMS_PICK_TASK_H taskh
        WHERE 1=1
        <if test="containerCode != null and containerCode != ''">
            and taskh.container_code = #{containerCode}
        </if>
        <if test="billId != null and billId != ''">
            and bill_id= #{billId}
        </if>
    </select>
    <resultMap id="InnerCheckBillVO" type="com.prolog.cs.biz.outbound.model.vo.check.InnerCheckBillVO">
        <result column="bill_group_id" property="billGroupId"/>
        <result column="container_code" property="containerCode"/>
        <result column="WAREHOUSE_ID" property="warehouseId"/>
        <result column="consignor_name" property="consignorName"/>
        <result column="consignor_id" property="consignorId"/>
        <result column="client_name" property="clientName"/>
        <result column="bill_code" property="billCode"/>
        <result column="bill_type" property="billType"/>
        <result column="pick_task_h_id" property="pickTaskHId"/>
        <result column="stage_code" property="workStageCode"/>
        <result column="shipper_name" property="shipperName"/>
        <result column="shipper_id" property="shipperId"/>
        <result column="station_code" property="stationCode"/>
    </resultMap>

    <select id="getContainerInfo" resultMap="InnerCheckBillVO">
        SELECT
        taskh.bill_group_id,
        taskh.STATION_CODE,
        taskh.container_code,
        taskh.WAREHOUSE_ID,
        omsbill.consignorname consignor_name ,
        omsbill.CONSIGNORID consignor_id,
        taskh.PICK_TASK_ID pick_task_h_id,
        bill.code bill_code,
        bill.TYPE bill_type,
        workstage.stage_code,
        omsbill.CUST_PERSON client_name,
        omsbill.shippername shipper_name,
        omsbill.shipperid shipper_id
        FROM
        WMS_PICK_TASK_H taskh
        LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = taskh.BILL_GROUP_ID
        LEFT JOIN OMS_DO_BILL_H omsbill ON omsbill.BILLID = bill.SRC_ID
        LEFT JOIN WMS_WORK_STAGE_RUNTIME workstage  ON WORKSTAGE.BILL_GROUP_ID = taskh.BILL_GROUP_ID
        WHERE taskh.status in ('2','3')
        <if test="containerCode != null and containerCode != ''">
            and taskh.container_code = #{containerCode}
        </if>
        <if test="stationCode != null and stationCode != ''">
            and (taskh.station_code is null or taskh.station_code = '-1' or taskh.station_code = #{stationCode})
        </if>
    </select>

    <select id="getLocatorIdList" resultType="java.lang.String">
        SELECT DISTINCT s.locator_id locatorId from wms_pick_task_h h,wms_pick_task_d d,wms_wave_storage s where
        h.pick_task_id=d.pick_task_id and d.wave_storage_id=s.id
        and h.enterprise_id= #{enterpriseId} and s.locator_id is not null
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID = #{warehouseId}
        </if>
        <if test="pickTaskId != null and pickTaskId != ''">
            and h.pick_task_id = #{pickTaskId}
        </if>
    </select>

    <select id="getStationByBillId" resultType="com.prolog.cs.basic.warehouse.model.BaseWmsWhWorkstation">
        select DISTINCT station_id,station_name,station_code from WMS_PICK_TASK_H
        where BILL_GROUP_ID = (select BILL_GROUP_ID from WMS_PICK_TASK_H where pick_task_id = #{pickTaskId})
        and station_id is not null and WAREHOUSE_AREA_CODE = #{areaCode}
    </select>

    <select id="getStationByBill" resultType="com.prolog.cs.basic.warehouse.model.BaseWmsWhWorkstation">
        select DISTINCT station_id,station_name,station_code from WMS_PICK_TASK_H
        where BILL_GROUP_ID = #{billId}
        and station_id is not null and WAREHOUSE_AREA_CODE = #{areaCode}
    </select>


    <select id="getStationOfWorks" resultType="com.prolog.cs.basic.warehouse.model.BaseWmsWhWorkstation">
        select station_id,station_name,station_code from WMS_PICK_TASK_H where enterprise_id =#{enterpriseId} and status in('1','2','3') and station_id is not null and pick_task_type in(1,2)
        <if test="warehouseId != null and warehouseId != ''">
            and WAREHOUSE_ID = #{warehouseId}
        </if>
        <if test="stationIds != null and stationIds.size()>0">
            and station_id in
            <foreach collection="stationIds" item="stationId" open="(" separator="," close=")">
                #{stationId}
            </foreach>
        </if>
        group by station_id,station_name,station_code
        order by count(*) asc
    </select>

    <select id="getTaskHList" resultMap="pickTaskHMap">
        select a.*,(a.qty-a.pickQty) unPickQty from (select
        h.pick_task_id,h.status,h.container_code,h.warehouse_id,h.pick_order_id,h.station_id,h.station_code,h.station_name,h.bill_id,
        sum(d.qty) qty,sum(d.pick_qty) pickQty,sum(d.check_qty) checkQty, h.serial_number
        from WMS_PICK_TASK_H h,wms_pick_task_d d
        where h.pick_task_id = d.pick_task_id and h.pick_order_id= #{pickOrderId}
        group by
        h.pick_task_id,h.status,h.container_code,h.warehouse_id,h.pick_order_id,h.station_id,h.station_code,h.station_name,h.bill_id)
        a
    </select>

    <select id="getTaskDList" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        select DISTINCT d.PICK_TASK_D_ID pickTaskDId,d.PICK_TASK_ID pickTaskId,d.qty,d.PICK_QTY pickQty,d.CHECK_QTY
        checkQty,m.ITEM_ID itemId,
        m.ITEM_NAME itemName,m.ITEM_CODE itemCode,s.MANUFACTURER_LOT_NUM manufacturerLotNum,s.WAREHOUSE_AREA_CODE warehouseAreaCode,s.LOCATOR_CODE locatorCode,
        n.WAREHOUSE_ID warehouseId,n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,n.type billType,n.BILL_GROUP_ID srcCode,
        s.uom,s.EXPIRED_DATE expiredDate,s.PRODUCTION_DATE productionDate,s.EXPIRED_DATE_SHOW expiredDateShow,s.PRODUCTION_DATE_SHOW productionDateShow,s.batch_num batchNum,
        case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate
        from wms_pick_task_d d
        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_WAVE_BILL n on n.ID = m.BILL_ID
        where d.pick_task_id = #{pickTaskId}
    </select>

    <select id="getPickTaskByIds" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        select DISTINCT
            d.PICK_TASK_D_ID pickTaskDId,
            d.PICK_TASK_ID pickTaskId,
            h.pick_order_id pickOrderId,
            d.qty,
            d.PICK_QTY pickQty,
            d.CHECK_QTY
            checkQty,
            m.ITEM_ID itemId,
            m.ITEM_NAME itemName,
            m.ITEM_CODE itemCode,
            s.MANUFACTURER_LOT_NUM manufacturerLotNum,
            s.WAREHOUSE_AREA_CODE warehouseAreaCode,
            s.LOCATOR_CODE locatorCode,
            n.WAREHOUSE_ID warehouseId,
            n.SHIPPER_ID shipperId,
            n.CONSIGNOR_ID consignorId,
            n.type billType,
            n.BILL_GROUP_ID srcCode,
            s.uom,
            s.EXPIRED_DATE expiredDate,
            s.PRODUCTION_DATE productionDate,
            s.EXPIRED_DATE_SHOW expiredDateShow,
            s.PRODUCTION_DATE_SHOW productionDateShow,
            s.batch_num batchNum,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate
        from wms_pick_task_d d
        left join wms_pick_task_h h on h.pick_task_id = d.pick_task_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_WAVE_BILL n on n.ID = m.BILL_ID
        where d.PICK_TASK_D_ID IN
        <foreach collection="pickTaskDIds" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="getPickTaskList" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        select DISTINCT d.PICK_TASK_D_ID pickTaskDId,d.PICK_TASK_ID pickTaskId,d.qty,d.PICK_QTY pickQty,d.CHECK_QTY
        checkQty,(d.qty-d.PICK_QTY) unCheckQty,m.ITEM_ID itemId,
        m.ITEM_NAME itemName,m.ITEM_CODE itemCode,s.MANUFACTURER_LOT_NUM manufacturerLotNum,s.WAREHOUSE_AREA_CODE warehouseAreaCode,s.LOCATOR_CODE
        locatorCode,h.container_code containerCode,h.status,
        n.WAREHOUSE_ID warehouseId,n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,n.type billType,n.BILL_GROUP_ID srcCode,
        s.uom,s.EXPIRED_DATE expiredDate,s.PRODUCTION_DATE productionDate,s.EXPIRED_DATE_SHOW expiredDateShow,s.PRODUCTION_DATE_SHOW productionDateShow,s.batch_num batchNum,
        case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,d.SALE_RED_QTY saleRedQty
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_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_WAVE_BILL n on n.ID = m.BILL_ID
        where h.pick_order_id = #{pickOrderId} and d.pick_time is null
        <if test="itemCode != null and itemCode != ''">
            and m.ITEM_CODE like concat(concat('%',#{itemCode}),'%')
        </if>
        <if test="containerCode != null and containerCode != ''">
            and h.container_code like concat(concat('%',#{containerCode}),'%')
        </if>
        order by d.PICK_TASK_ID,m.ITEM_ID
    </select>

    <resultMap id="PickTaskDVO" type="com.prolog.cs.biz.outbound.model.vo.check.InnerCheckTaskDVO">
        <result column="pick_task_d_id" property="pickTaskDId"/>
        <result column="batchNum" property="batchNum"/>
        <result column="qty" property="qty"/>
        <result column="check_qty" property="checkQty"/>
        <result column="pick_qty" property="pickQty"/>
        <result column="billDId" property="billDId"/>
        <result column="expired_date" property="expiredDate"/>
        <result column="production_date" property="productionDate"/>
    </resultMap>
    <select id="getPickTaskds" resultMap="PickTaskDVO">
        SELECT
        taskd.pick_task_d_id ,
        taskd.check_qty ,
        wws.batch_num as batchNum,
        taskd.qty as qty,
        taskd.pick_qty,
        wws.production_date,
        wws.bill_d_id as billDId,
        billd.CASE_CONVERSION_RATE ,
        BILLD.IS_CASE_OR_PIECE ,
        wws.expired_date
        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_STORAGE wws on wws.id = taskd.WAVE_STORAGE_ID
        left join WMS_WAVE_BILL_D billd on billd.id = wws.BILL_D_ID
        WHERE  wws.bill_d_id IS NOT null
        <if test="containerCode != null and containerCode != ''">
            and taskh.container_code = #{containerCode}
        </if>
        <if test="billId != null and billId != ''">
            and wws.bill_id = #{billId}
        </if>
        <if test="itemCode != null and itemCode != ''">
            and billd.item_code = #{itemCode}
        </if>
        ORDER BY  billd.item_code desc
    </select>

    <select id="getPickTaskAllList" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT a.* FROM(select DISTINCT
            d.PICK_TASK_D_ID pickTaskDId,
            d.PICK_TASK_ID pickTaskId,
            coalesce(d.qty,0) qty,
            d.PICK_QTY pickQty,
            d.WAREHOUSE_ID warehouseId,
            d.CHECK_QTY checkQty,
            d.PICKER_NAME pickerName,
            s.manufacturer_lot_num manufacturerLotNum,
            coalesce(m.CASE_CONVERSION_RATE, 1) conversionRate,
            m.ITEM_ID itemId,
            m.ITEM_NAME itemName,
            m.ITEM_CODE itemCode,
            s.batch_num batchNum,
            m.item_specs itemSpecs,
            s.EXPIRED_DATE expiredDate,
            s.WAREHOUSE_AREA_CODE warehouseAreaCode,
            s.LOCATOR_CODE locatorCode,
            s.uom,
            h.container_code containerCode,
            IF(d.flow_status > 2,2,1) status,
            s.PRODUCTION_DATE productionDate,
            e.PICK_ORDER_ID pickOrderId,
            e.operator_code operatorCode,
            e.operator_name operatorName,
            n.SHIPPER_ID shipperId,
            n.CONSIGNOR_ID consignorId,
            n.type billType,
            n.BILL_GROUP_ID srcCode,
            case when d.PRINT_COUNT=0 then 0 else 1 end printStatus,
            e.GMT_CREATE,
            s.EXPIRED_DATE_SHOW expiredDateShow,
            s.PRODUCTION_DATE_SHOW productionDateShow,
            h.type,
            obh.CONSIGNORNAME consignorName,
            d.SALE_RED_QTY saleRedQty,
            s.area_category areaCategory,
            'N/A' container_no,
            m.MANUFACTURER
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        left join wms_pick_order e on e.pick_order_id = h.pick_order_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_WAVE_BILL n on m.bill_id = n.id
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        left join wms_task_out_back ob on ob.billno = obh.billno
        where d.ENTERPRISE_ID=#{enterpriseId} and h.PICK_TASK_TYPE = '0'
        and (s.PICK_MODEL is null or s.PICK_MODEL &lt;&gt; 'Device') AND d.print_count > 0
        <choose>
            <when test="type=='0'.toString()">
                and IF(d.flow_status > 2,2,1) ='1' AND (d.print_count = 0 OR d.print_count IS NULL)
            </when>
            <when test="type=='1'.toString()">
                and IF(d.flow_status > 2,2,1) ='1' AND d.print_count > 0
            </when>
            <otherwise>
                and IF(d.flow_status > 2,2,1) > '1' AND d.print_count > 0
            </otherwise>
        </choose>
        <if test="warehouseId != null and warehouseId != ''">
            and d.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="pickOrderId != null and pickOrderId != ''">
            and h.pick_order_id like concat(concat('%',#{pickOrderId}),'%')
        </if>
        <if test="itemCode != null and itemCode != ''">
            and m.ITEM_CODE like concat(concat('%',#{itemCode}),'%')
        </if>
        <if test="operatorCode != null and operatorCode != ''">
            and e.operator_code like concat(concat('%',#{operatorCode}),'%')
        </if>
        <if test="srcCode != null and srcCode != ''">
            and n.BILL_GROUP_ID like concat(concat('%',#{srcCode}),'%')
        </if>
        <if test="barcode != null and barcode != ''">
            and f.barcode like concat(concat('%',#{barcode}),'%')
        </if>
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="containerNo != null and containerNo != ''">
            and d.container_code = #{containerNo}
        </if>
        order by e.GMT_CREATE desc,d.PICK_TASK_ID asc)a

        union all
        SELECT b.* FROM(
        select DISTINCT
            d.PICK_TASK_D_ID pickTaskDId,
            d.PICK_TASK_ID pickTaskId,
            d.qty,
            d.PICK_QTY pickQty,
            d.WAREHOUSE_ID warehouseId,
            d.CHECK_QTY checkQty,
            d.PICKER_NAME pickerName,
            s.manufacturer_lot_num manufacturerLotNum,
            m.CASE_CONVERSION_RATE conversionRate,
            m.ITEM_ID itemId,
            m.ITEM_NAME itemName,
            m.ITEM_CODE itemCode,
            m.BATCH_NUM batchNum,
            m.item_specs itemSpecs,
            s.EXPIRED_DATE expiredDate,
            s.WAREHOUSE_AREA_CODE warehouseAreaCode,
            s.LOCATOR_CODE locatorCode,
            s.uom,
            h.container_code containerCode,
            IF(d.flow_status > 2,2,1) status,
            s.PRODUCTION_DATE productionDate,
            e.PICK_ORDER_ID pickOrderId,
            e.operator_code operatorCode,
            e.operator_name operatorName,
            n.SHIPPER_ID shipperId,
            n.CONSIGNOR_ID consignorId,
            n.type billType,
            n.BILL_GROUP_ID srcCode,
            case when d.PRINT_COUNT=0 then 0 else 1 end printStatus,
            e.GMT_CREATE,
            s.EXPIRED_DATE_SHOW expiredDateShow,
            s.PRODUCTION_DATE_SHOW productionDateShow,
            h.type,
            obh.CONSIGNORNAME consignorName,
            d.SALE_RED_QTY saleRedQty,
            s.area_category areaCategory,
            d.container_code container_no,
            m.MANUFACTURER
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        left join wms_pick_order e on e.pick_order_id = h.pick_order_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_WAVE_BILL n on m.bill_id = n.id
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        left join wms_task_out wto on wto.billno = obh.billno and wto.wmflg = 'Y'
        left join wms_task_out_back ob on ob.billno = obh.billno
        where d.ENTERPRISE_ID=#{enterpriseId}
          and h.PICK_TASK_TYPE = '0'
          and s.PICK_MODEL ='Device'
        <if test="containerNo != null and containerNo != ''">
            and d.container_code = #{containerNo}
        </if>
        <choose>
            <when test="type=='0'.toString()">
                and IF(d.flow_status > 2,2,1) ='1' AND (d.print_count = 0 OR d.print_count IS NULL)
            </when>
            <when test="type=='1'.toString()">
                and IF(d.flow_status > 2,2,1) ='1' AND d.print_count > 0
            </when>
            <otherwise>
                and IF(d.flow_status > 2,2,1) > '1' AND d.print_count > 0
            </otherwise>
        </choose>
        <if test="warehouseId != null and warehouseId != ''">
            and d.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="pickOrderId != null and pickOrderId != ''">
            and h.pick_order_id like concat(concat('%',#{pickOrderId}),'%')
        </if>
        <if test="itemCode != null and itemCode != ''">
            and m.ITEM_CODE like concat(concat('%',#{itemCode}),'%')
        </if>
        <if test="operatorCode != null and operatorCode != ''">
            and e.operator_code like concat(concat('%',#{operatorCode}),'%')
        </if>
        <if test="srcCode != null and srcCode != ''">
            and n.BILL_GROUP_ID like concat(concat('%',#{srcCode}),'%')
        </if>
        <if test="barcode != null and barcode != ''">
            and f.barcode like concat(concat('%',#{barcode}),'%')
        </if>
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        order by e.GMT_CREATE desc,d.PICK_TASK_ID asc)b
    </select>

    <select id="getWaitPickTaskList" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT DISTINCT
            d.PICK_TASK_D_ID AS pickTaskDId,
            d.PICK_TASK_ID AS pickTaskId,
            d.qty,
            d.PICK_QTY AS pickQty,
            d.CHECK_QTY AS checkQty,
            d.PICKER_NAME AS pickerName,
            obh.CONSIGNORNAME AS consignorName,
            m.ITEM_ID AS itemId,
            m.ITEM_NAME AS itemName,
            m.ITEM_CODE AS itemCode,
            s.WAREHOUSE_AREA_CODE AS warehouseAreaCode,
            s.MANUFACTURER_LOT_NUM AS manufacturerLotNum,
            s.LOCATOR_CODE AS locatorCode,
            h.container_code AS containerCode,
            h.STATUS,
            h.type,
            h.BILL_GROUP_ID AS srcCode,
            (case when h.pick_task_type = 2 then (SELECT po.print_status FROM wms_pick_order po WHERE po.pick_order_id = h.pick_order_id)
            ELSE IF(ifnull( d.PRINT_COUNT, '0' ) > 0, 1, 0) END) AS printStatus,
            n.SHIPPER_ID AS shipperId,
            n.CONSIGNOR_ID AS consignorId,
            n.type AS billType,
            s.uom,
            s.EXPIRED_DATE AS expiredDate,
            s.PRODUCTION_DATE AS productionDate,
            s.EXPIRED_DATE_SHOW AS expiredDateShow,
            s.PRODUCTION_DATE_SHOW AS productionDateShow,
            s.batch_num AS batchNum,
            CASE WHEN m.CASE_CONVERSION_RATE = 0 THEN 1 ELSE ifnull( m.CASE_CONVERSION_RATE, 1 ) END AS conversionRate,
            s.area_category AS areaCategory,
            s.PICK_MODEL,
            m.MANUFACTURER,
            d.container_code containerNo
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_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_WAVE_BILL n on m.bill_id = n.id
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        LEFT JOIN wms_task_out o ON d.pick_task_d_id = o.seqno OR d.src_pick_task_d_id = o.seqno
        LEFT JOIN wms_task_out_back ob ON d.pick_task_d_id = ob.seqno OR d.src_pick_task_d_id = ob.seqno
        where 1=1 and d.ENTERPRISE_ID=#{enterpriseId}
        AND ((o.msg_id is not null AND ob.msg_id is not null) OR o.msg_id is null)
        <if test="tab == '1'.toString()">
            AND h.status ='0'
        </if>
        <if test="tab == '2'.toString()">
            AND (h.status ='0' or (h.status ='1' and d.PRINT_COUNT = 0))
        </if>
        <choose>
            <when test="pickTaskType=='3'.toString()">
                and h.PICK_TASK_TYPE in ('0','3')
            </when>
            <otherwise>
                and h.PICK_TASK_TYPE = #{pickTaskType}
            </otherwise>
        </choose>
        <if test="warehouseId != null and warehouseId != ''">
            and d.WAREHOUSE_ID= #{warehouseId}
        </if>
        <if test="itemCode != null and itemCode != ''">
            and m.ITEM_CODE like concat(concat('%',#{itemCode}),'%')
        </if>
        <if test="warehouseAreaCode != null and warehouseAreaCode != ''">
            and s.WAREHOUSE_AREA_CODE = #{warehouseAreaCode}
        </if>
        <if test="srcCode != null and srcCode != ''">
            and n.BILL_GROUP_ID like concat(concat('%',#{srcCode}),'%')
        </if>
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="containerCode != null and containerCode != ''">
            and d.container_code = #{containerCode}
        </if>
        ORDER BY pickTaskDId ASC
    </select>

    <select id="getMixPickTaskHIds" resultType="java.lang.String">
        select DISTINCT h.PICK_TASK_ID
        from wms_pick_task_h h
        left join wms_pick_task_d d on d.pick_task_id = h.pick_task_id
        left join wms_wave_storage s on d.WAVE_STORAGE_ID = s.id
        where h.status ='1' and d.ENTERPRISE_ID=#{enterpriseId} and h.WAREHOUSE_ID = #{warehouseId}
        <if test="pickTaskType != null and pickTaskType !='' ">
            and h.PICK_TASK_TYPE= #{pickTaskType}
        </if>
        and h.PICK_ORDER_ID = #{pickOrderId}
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
    </select>

    <select id="getMixPickTaskAllList" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        select DISTINCT
            d.PICK_TASK_D_ID pickTaskDId,
            d.PICK_TASK_ID pickTaskId,
            d.qty,
            d.PICK_QTY pickQty,
            d.WAREHOUSE_ID warehouseId,
            d.CHECK_QTY checkQty,
            d.PICKER_NAME pickerName,
            d.PICK_TASK_TYPE pickTaskType,
            m.ITEM_ID itemId,
            m.ITEM_NAME itemName,
            m.ITEM_CODE itemCode,
            s.MANUFACTURER_LOT_NUM manufacturerLotNum,
            s.WAREHOUSE_AREA_CODE warehouseAreaCode,
            s.LOCATOR_CODE locatorCode,
            h.container_code containerCode,
            h.status,h.type,
            e.PICK_ORDER_ID pickOrderId,
            e.operator_code operatorCode,
            e.operator_name operatorName,
            n.BILL_GROUP_ID srcCode,
            n.SHIPPER_ID shipperId,
            n.CONSIGNOR_ID consignorId,
            n.type billType,
            e.GMT_CREATE,
            s.uom,
            s.EXPIRED_DATE expiredDate,
            s.PRODUCTION_DATE productionDate,
            s.EXPIRED_DATE_SHOW expiredDateShow,
            s.PRODUCTION_DATE_SHOW productionDateShow,
            s.batch_num batchNum,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,d.SALE_RED_QTY saleRedQty,
            m.MANUFACTURER
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        left join wms_pick_order e on e.pick_order_id = h.pick_order_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_WAVE_BILL n on m.bill_id = n.id
        where h.status ='1' and d.ENTERPRISE_ID=#{enterpriseId}
        <choose>
            <when test="pickTaskType=='3'.toString()">
                and h.PICK_TASK_TYPE in ('0','3')
            </when>
            <otherwise>
                and h.PICK_TASK_TYPE = #{pickTaskType}
            </otherwise>
        </choose>
        <if test="warehouseId != null and warehouseId != ''">
            and d.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="pickOrderId != null and pickOrderId != ''">
            and h.pick_order_id like concat(concat('%',#{pickOrderId}),'%')
        </if>
        <if test="itemCode != null and itemCode != ''">
            and m.ITEM_CODE = #{itemCode}
        </if>
        <if test="operatorCode != null and operatorCode != ''">
            and e.operator_code = #{operatorCode}
        </if>
        <if test="srcCode != null and srcCode != ''">
            and n.BILL_GROUP_ID like concat(concat('%',#{srcCode}),'%')
        </if>
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        order by e.GMT_CREATE desc,d.PICK_TASK_ID asc
    </select>

    <select id="getPickTaskHOut" resultType="com.prolog.cs.biz.outbound.model.dto.WaveBillDODTO">
        select DISTINCT f.barcode,f.id barcodeId,f.bill_group_id srcCode,f.type,f.check_status checkStatus,f.checker_no checkerNo
        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 n.id = m.bill_id
        where n.WAREHOUSE_ID=#{warehouseId} and f.check_status='0'
        <if test="billCode != null and billCode != ''">
            and n.src_id = #{billCode}
        </if>
        <if test="barcode != null and barcode != ''">
            AND EXISTS(SELECT 1 FROM wms_barcode_d d WHERE d.barcode_id = f.id and d.id like concat(concat('%',#{barcode}),'%'))
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
    </select>

    <select id="getOuterCheckH" resultType="com.prolog.cs.biz.outbound.model.dto.WaveBillDODTO">
        SELECT
        max(f.barcode) barcode,max(f.id) barcodeId,max(taskh.CONTAINER_CODE) containerCode,max(f.LCL_NO) lclNo,
        max(f.type) type,max(taskd.CHECKER_NAME) checkerName,max(taskd.CHECK_TIME) checkTime,
        max(f.status) printStatus,max(f.STATION_CODE) stationCode,
        taskh.BILL_GROUP_ID srcCode,'' shipperCode,
        obh.LINE_NAME shipLineId,obh.carrier_name carrierName,obh.take_mode receiveWay,obh.RECEIVE_ADDRESS receiveAddress,
        obh.SHIPPERNAME shipperName,
        case when obh.ARRIVAL_MODE is null then '配送'
        WHEN obh.ARRIVAL_MODE = 1 THEN '配送'
        WHEN obh.ARRIVAL_MODE = 2 THEN '客户自提'
        WHEN obh.ARRIVAL_MODE = 3 THEN '取货直接交货'
        end as arrivalMode,obh.SALESPERSON_NAME salespersonName,
        obh.SALE_BRANCHNAME saleBranchname,obh.CREATEBY,obh.PAYMENTDAYTYPECODE paymentType,obh.RECEIVE_ADDRESS customerAddress,
        obh.CONSIGNORNAME consignorName,
        case when obh.BILLTYPE is null then '正常销售'
        WHEN obh.BILLTYPE = 1 THEN '销售出库'
        WHEN obh.BILLTYPE = 2 THEN '采购退货'
        WHEN obh.BILLTYPE = 3 THEN '调拨出库'
        WHEN obh.BILLTYPE = 4 THEN '移库出库'
        WHEN obh.BILLTYPE = 5 THEN '调账出库'
        WHEN obh.BILLTYPE = 6 THEN '越库出库'
        WHEN obh.BILLTYPE = 7 THEN '直调出库'
        end as billType,obh.remark,
        taskh.BILL_GROUP_ID erpSrcCode,
        taskh.is_merged isMerged
        FROM WMS_BARCODE_H f
        left join wms_pick_task_h taskh on f.bill_group_id = taskh.bill_group_id
        left join wms_pick_task_d taskd on taskd.pick_task_id = taskh.pick_task_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = taskd.SRC_ID
        where f.check_status ='1' and taskd.SRC_ID is not null
        and taskh.type=0
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="operatorCode != null and operatorCode != ''">
            and f.CHECKER_NO=#{operatorCode}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        <if test="srcCode != null and srcCode != ''">
            and (taskh.BILL_GROUP_ID like concat(concat('%',#{srcCode}),'%') or n.src_code like concat(concat('%',#{srcCode}),'%'))
        </if>
        <if test="barcode != null and barcode != ''">
            and f.barcode like concat(concat('%',#{barcode}),'%')
        </if>
        GROUP BY taskh.BILL_GROUP_ID,
        obh.LINE_NAME,obh.carrier_name,obh.take_mode,obh.RECEIVE_ADDRESS,

        obh.SHIPPERNAME,obh.ARRIVAL_MODE,obh.SALESPERSON_NAME,
        obh.SALE_BRANCHNAME,obh.CREATEBY,obh.PAYMENTDAYTYPECODE,obh.RECEIVE_ADDRESS,
        obh.CONSIGNORNAME,obh.BILLTYPE,obh.remark
    </select>

  <!--   外复核已索取SQL优化备份
        <select id="getOuterCheckH" resultType="com.prolog.cs.biz.outbound.model.dto.WaveBillDODTO">
        select
        max(f.barcode) barcode,max(f.id) barcodeId,max(taskh.CONTAINER_CODE) containerCode,max(f.LCL_NO) lclNo,
        max(f.type) type,max(taskd.CHECKER_NAME) checkerName,max(taskd.CHECK_TIME) checkTime,
               max(f.status) printStatus,max(f.STATION_CODE) stationCode,
        n.src_id srcCode,n.shipper_code shipperCode,
        obh.LINE_NAME shipLineId,n.carrier_name carrierName,n.receive_way receiveWay,n.RECEIVE_ADDRESS receiveAddress,

        obh.SHIPPERNAME shipperName,case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,obh.SALESPERSON_NAME salespersonName,
        obh.SALE_BRANCHNAME saleBranchname,obh.CREATEBY,obh.PAYMENTDAYTYPECODE paymentType,obh.RECEIVE_ADDRESS customerAddress,
        obh.CONSIGNORNAME consignorName,case obh.BILLTYPE when null then '正常销售' when 1 then '销售出库' when 2 then '采购退货' when 3 then '调拨出库' when 4 then '移库出库' when 5 then '调账出库' when 6 then '越库出库' when 7 then '直调出库' end billType,obh.remark,
        n.src_code erpSrcCode,max(n.is_merged) isMerged,f.checker_no checkerNo
        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 n.id = m.bill_id
        left join wms_pick_task_h taskh on n.bill_group_id = taskh.bill_group_id
        left join wms_pick_task_d taskd on taskd.Pick_task_id = taskh.pick_task_id
        left join wms_wave_storage s on m.id = s.BILL_D_ID
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        where f.check_status ='1'
        and s.type=0
        <if test="warehouseId != null and warehouseId != ''">
            and n.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="operatorCode != null and operatorCode != ''">
            and f.CHECKER_NO=#{operatorCode}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        <if test="srcCode != null and srcCode != ''">
            and (n.BILL_GROUP_ID like concat(concat('%',#{srcCode}),'%') or n.src_code like concat(concat('%',#{srcCode}),'%'))
        </if>
        <if test="barcode != null and barcode != ''">
            and f.barcode like concat(concat('%',#{barcode}),'%')
        </if>
        GROUP BY n.BILL_GROUP_ID,n.shipper_code,
        obh.LINE_NAME,n.carrier_name,n.receive_way,n.RECEIVE_ADDRESS,

        obh.SHIPPERNAME,obh.ARRIVAL_MODE,obh.SALESPERSON_NAME,
        obh.SALE_BRANCHNAME,obh.CREATEBY,obh.PAYMENTDAYTYPECODE,obh.RECEIVE_ADDRESS,
        obh.CONSIGNORNAME,obh.BILLTYPE,obh.remark,f.checker_no,n.src_id,
        n.src_code
    </select>-->

    <select id="getUnOuterCheckH" resultType="com.prolog.cs.biz.outbound.model.dto.WaveBillDODTO">
        select * from (select
        max(f.barcode) barcode,max(taskh.CONTAINER_CODE) containerCode,max(f.LCL_NO) lclNo,min(v.status) status,
        max(f.type) type,max(taskd.CHECKER_NAME) checkerName,max(taskd.CHECK_TIME) checkTime,                              
                              max(f.status) printStatus,max(f.STATION_CODE) stationCode,
        n.src_id srcCode,n.shipper_code shipperCode,
        obh.LINE_NAME shipLineId,n.carrier_name carrierName,n.receive_way receiveWay,n.RECEIVE_ADDRESS receiveAddress,
        sum(n.total_pcs) totalPcs,
        obh.SHIPPERNAME shipperName,case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,obh.SALESPERSON_NAME salespersonName,
        obh.SALE_BRANCHNAME saleBranchname,obh.CREATEBY,obh.PAYMENTDAYTYPECODE paymentType,obh.RECEIVE_ADDRESS customerAddress,
        obh.CONSIGNORNAME consignorName,case obh.BILLTYPE when null then '正常销售' when 1 then '销售出库' when 2 then '采购退货' when 3 then '调拨出库' when 4 then '移库出库' when 5 then '调账出库' when 6 then '越库出库' when 7 then '直调出库' end billType,obh.remark,
        n.src_code erpSrcCode,max(n.is_merged) isMerged

        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
        left join wms_pick_task_h taskh on n.bill_group_id = taskh.bill_group_id
        left join wms_pick_task_d taskd on taskd.Pick_task_id = taskh.pick_task_id
        left join WMS_MOVE_TASK v on v.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        where f.check_status ='0' and f.WAREHOUSE_ID=#{warehouseId} and n.ENTERPRISE_ID=#{enterpriseId}
        <if test="barcode != null and barcode != ''">
            and f.barcode like concat(concat('%',#{barcode}),'%')
        </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')) and WAREHOUSE_ID=#{warehouseId}
        UNION
        SELECT BILL_GROUP_ID FROM WMS_PICK_TASK_H wpth WHERE PICK_TASK_TYPE in (1,2,3) AND STATUS &lt;&gt; '10' and WAREHOUSE_ID=#{warehouseId}
        ) b
        )
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        <choose>
            <when test="type != null and type ==1">
                and n.receive_way = '自提'
            </when>
            <otherwise>
                and (n.receive_way is null or n.receive_way != '自提')
            </otherwise>
        </choose>
        GROUP BY n.BILL_GROUP_ID,n.shipper_code,
        obh.LINE_NAME,n.carrier_name,n.receive_way,n.RECEIVE_ADDRESS,


        obh.SHIPPERNAME,obh.ARRIVAL_MODE,obh.SALESPERSON_NAME,
        obh.SALE_BRANCHNAME,obh.CREATEBY,obh.PAYMENTDAYTYPECODE,obh.RECEIVE_ADDRESS,
        obh.CONSIGNORNAME,obh.BILLTYPE,obh.remark
        ) a
        where (a.status is null or a.status='2')
        and a.srcCode not in (select DISTINCT f.bill_group_id from WMS_BARCODE_H f where f.check_status is null
        and f.WAREHOUSE_ID=#{warehouseId} and f.ENTERPRISE_ID=#{enterpriseId}
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        )
        <if test="srcCode != null and srcCode != ''">
            and a.srcCode like concat(concat('%',#{srcCode}),'%')
        </if>
    </select>

    <select id="getUnOuterCheckH2" resultType="com.prolog.cs.biz.outbound.model.dto.WaveBillDODTO">
        SELECT
        max(taskh.CONTAINER_CODE) containerCode,
        max(f.LCL_NO) lclNo,
        max(f.type) type,
        max(taskd.CHECKER_NAME) checkerName,
        max(taskd.CHECK_TIME) checkTime,
        max(f.status) printStatus,
        max(f.STATION_CODE) stationCode,
        taskd.src_id srcCode,'' shipperCode,
        obh.LINE_NAME shipLineId,
        obh.carrier_name carrierName,
        obh.take_mode receiveWay,
        obh.RECEIVE_ADDRESS receiveAddress,
        obh.SHIPPERNAME shipperName,
        case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,
        obh.SALESPERSON_NAME salespersonName,
        obh.SALE_BRANCHNAME saleBranchname,
        obh.CREATEBY,
        obh.PAYMENTDAYTYPECODE paymentType,
        obh.RECEIVE_ADDRESS customerAddress,
        obh.CONSIGNORNAME consignorName,
        case obh.BILLTYPE when null then '正常销售' when 1 then '销售出库' when 2 then '采购退货' when 3 then '调拨出库' when 4 then '移库出库' when 5 then '调账出库' when 6 then '越库出库' when 7 then '直调出库' end billType,
        obh.remark,
        taskd.src_code erpSrcCode,
        taskh.is_merged isMerged
        FROM WMS_BARCODE_H f
        left join wms_pick_task_h taskh on f.bill_group_id = taskh.bill_group_id
        left join wms_pick_task_d taskd on taskd.pick_task_id = taskh.pick_task_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = taskd.SRC_ID
        where f.check_status ='0' and f.WAREHOUSE_ID=#{warehouseId} and taskh.ENTERPRISE_ID=#{enterpriseId} and taskd.SRC_ID is not null
        <if test="barcode != null and barcode != ''">
            AND EXISTS(SELECT 1 FROM wms_barcode_d d WHERE d.barcode_id = f.id and d.id like concat(concat('%',#{barcode}),'%'))
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        <if test="srcCode != null and srcCode != ''">
            and taskd.src_code like concat(concat('%',#{srcCode}),'%')
        </if>

        <if test="billtypeGroup != null and billtypeGroup != ''">
            and obh.billtype = #{billtypeGroup}
        </if>
        <if test="partlyRed != null and partlyRed != ''">
            <choose>
                <when test="partlyRed == 0">
                    and ((SELECT SUM(dd.sale_red_qty) from wms_pick_task_d dd WHERE dd.Pick_task_id = taskh.pick_task_id AND coalesce(dd.sale_red_qty, 0) > 0)=0
                    OR (SELECT SUM(dd.sale_red_qty) from wms_pick_task_d dd WHERE dd.Pick_task_id = taskh.pick_task_id AND coalesce(dd.sale_red_qty, 0) > 0) IS null)
                </when>
                <otherwise>
                    AND (SELECT SUM(dd.sale_red_qty) from wms_pick_task_d dd WHERE dd.Pick_task_id = taskh.pick_task_id AND coalesce(dd.sale_red_qty, 0) > 0)>0
                </otherwise>
            </choose>
        </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')) and WAREHOUSE_ID=#{warehouseId}
        UNION
        SELECT BILL_GROUP_ID FROM WMS_PICK_TASK_H wpth WHERE PICK_TASK_TYPE in (1,2,3) AND STATUS &lt;&gt; '10' and WAREHOUSE_ID=#{warehouseId}
        ) b
        )
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        <choose>
            <when test="type != null and type ==1">
                and obh.take_mode = '自提'
            </when>
            <otherwise>
                and (obh.take_mode is null or obh.take_mode != '自提')
            </otherwise>
        </choose>
        GROUP BY taskh.BILL_GROUP_ID,obh.LINE_NAME,
        obh.SHIPPERNAME,obh.ARRIVAL_MODE,obh.SALESPERSON_NAME,
        obh.SALE_BRANCHNAME,obh.CREATEBY,obh.PAYMENTDAYTYPECODE,obh.RECEIVE_ADDRESS,
        obh.CONSIGNORNAME,obh.BILLTYPE,obh.remark,taskd.src_code,taskd.src_id
    </select>

   <!-- 外复核为复核索取任务查询优化备份
   <select id="getUnOuterCheckH2" resultType="com.prolog.cs.biz.outbound.model.dto.WaveBillDODTO">
        select * from
            (select
                max(bd.id) barcode,
                max(taskh.CONTAINER_CODE) containerCode,
                max(f.LCL_NO) lclNo,
                min(v.status) status,
                max(f.type) type,
                max(taskd.CHECKER_NAME) checkerName,
                max(taskd.CHECK_TIME) checkTime,
                max(f.status) printStatus,
                max(f.STATION_CODE) stationCode,
                n.src_id srcCode,n.shipper_code shipperCode,
                obh.LINE_NAME shipLineId,
                n.carrier_name carrierName,
                n.receive_way receiveWay,
                n.RECEIVE_ADDRESS receiveAddress,
                sum(n.total_pcs) totalPcs,
                obh.SHIPPERNAME shipperName,
                case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,
                obh.SALESPERSON_NAME salespersonName,
                obh.SALE_BRANCHNAME saleBranchname,
                obh.CREATEBY,
                obh.PAYMENTDAYTYPECODE paymentType,
                obh.RECEIVE_ADDRESS customerAddress,
                obh.CONSIGNORNAME consignorName,
                case obh.BILLTYPE when null then '正常销售' when 1 then '销售出库' when 2 then '采购退货' when 3 then '调拨出库' when 4 then '移库出库' when 5 then '调账出库' when 6 then '越库出库' when 7 then '直调出库' end billType,
                obh.remark,
                n.src_code erpSrcCode,
                max(n.is_merged) isMerged
            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
            left join wms_pick_task_h taskh on n.bill_group_id = taskh.bill_group_id
            left join wms_pick_task_d taskd on taskd.Pick_task_id = taskh.pick_task_id
            left join WMS_MOVE_TASK v on v.bill_id = n.id
            LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
            LEFT JOIN wms_barcode_d bd ON f.id = bd.barcode_id
            where f.check_status ='0' and f.WAREHOUSE_ID=#{warehouseId} and n.ENTERPRISE_ID=#{enterpriseId}
            <if test="barcode != null and barcode != ''">
                AND EXISTS(SELECT 1 FROM wms_barcode_d d WHERE d.barcode_id = f.id and d.id like concat(concat('%',#{barcode}),'%'))
            </if>
            <if test="billtypeGroup != null and billtypeGroup != ''">
                and obh.billtype = #{billtypeGroup}
            </if>
            <if test="partlyRed != null and partlyRed != ''">
                <choose>
                    <when test="partlyRed == 0">
                        and ((SELECT SUM(dd.sale_red_qty) from wms_pick_task_d dd WHERE dd.Pick_task_id = taskh.pick_task_id AND coalesce(dd.sale_red_qty, 0) > 0)=0
                        OR (SELECT SUM(dd.sale_red_qty) from wms_pick_task_d dd WHERE dd.Pick_task_id = taskh.pick_task_id AND coalesce(dd.sale_red_qty, 0) > 0) IS null)
                    </when>
                    <otherwise>
                        AND (SELECT SUM(dd.sale_red_qty) from wms_pick_task_d dd WHERE dd.Pick_task_id = taskh.pick_task_id AND coalesce(dd.sale_red_qty, 0) > 0)>0
                    </otherwise>
                </choose>
            </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')) and WAREHOUSE_ID=#{warehouseId}
            UNION
            SELECT BILL_GROUP_ID FROM WMS_PICK_TASK_H wpth WHERE PICK_TASK_TYPE in (1,2,3) AND STATUS &lt;&gt; '10' and WAREHOUSE_ID=#{warehouseId}
            ) b
            )
            <if test="bldgId != null and bldgId != ''">
                and f.bldg_id = #{bldgId}
            </if>
            <choose>
                <when test="type != null and type ==1">
                    and n.receive_way = '自提'
                </when>
                <otherwise>
                    and (n.receive_way is null or n.receive_way != '自提')
                </otherwise>
            </choose>
            GROUP BY n.BILL_GROUP_ID,n.shipper_code,
            obh.LINE_NAME,n.carrier_name,n.receive_way,n.RECEIVE_ADDRESS,
            obh.SHIPPERNAME,obh.ARRIVAL_MODE,obh.SALESPERSON_NAME,
            obh.SALE_BRANCHNAME,obh.CREATEBY,obh.PAYMENTDAYTYPECODE,obh.RECEIVE_ADDRESS,
            obh.CONSIGNORNAME,obh.BILLTYPE,obh.remark,n.src_code,n.src_id
        ) a
        where (a.status is null or a.status='2')
        and a.srcCode not in (select DISTINCT f.bill_group_id from WMS_BARCODE_H f where f.check_status is null
        and f.WAREHOUSE_ID=#{warehouseId} and f.ENTERPRISE_ID=#{enterpriseId}
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        )
        <if test="srcCode != null and srcCode != ''">
            and a.srcCode like concat(concat('%',#{srcCode}),'%')
        </if>
    </select>-->

    <select id="getOuterCheckHOrder" resultType="com.prolog.cs.biz.outbound.model.dto.WaveBillDODTO">
        select
        n.BILL_GROUP_ID srcCode,n.SHIPPER_ID shipperId,n.shipper_name shipperName,obh.SHIPPERCODE shipperCode,
        n.carrier_name carrierName,n.receive_way receiveWay,n.RECEIVE_ADDRESS receiveAddress,
        obh.CONSIGNORCODE consignorCode,obh.CONSIGNORNAME consignorName,
        case obh.BILLTYPE when null then '正常销售' when 1 then '销售出库' when 2 then '采购退货' when 3 then '调拨出库' when 4 then '移库出库' when 5 then '调账出库' when 6 then '越库出库' when 7 then '直调出库' end billType,
        obh.BRANCHCODE warehouseCode,obh.BRANCHNAME warehouseName,obh.LINE_NAME lineName,obh.LINE_CODE lineCode,
        obh.CREATEBY,obh.PAYMENTDAYTYPECODE paymentType,
        obh.remark,n.BILL_GROUP_ID erpSrcCode
        ,case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,obh.SALESPERSON_NAME salespersonName,
        obh.SALE_BRANCHNAME saleBranchname,max(n.is_merged) isMerged
        from WMS_WAVE_BILL n
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        left join WMS_BARCODE_H f on f.bill_group_id = n.BILL_GROUP_ID
        where n.SRC_ID is not null and n.BILL_GROUP_ID 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') and STATUS &lt;&gt; '10') or (type = 1 and PICK_TASK_TYPE = 0 AND STATUS &lt;&gt; '5'))
                and WAREHOUSE_ID=#{warehouseId} and ENTERPRISE_ID=#{enterpriseId}
                UNION
                SELECT BILL_GROUP_ID FROM WMS_PICK_TASK_H wpth WHERE PICK_TASK_TYPE in (1,2,3) AND STATUS &lt;&gt; '10' and WAREHOUSE_ID=#{warehouseId} and ENTERPRISE_ID=#{enterpriseId}
            ) w
        )
        GROUP BY n.BILL_GROUP_ID,n.SHIPPER_ID,obh.SHIPPERCODE,n.shipper_name,
        n.carrier_name,n.receive_way,n.RECEIVE_ADDRESS,obh.CONSIGNORCODE,obh.CONSIGNORNAME,
        obh.BILLTYPE,obh.BRANCHCODE,obh.BRANCHNAME,obh.LINE_NAME,obh.LINE_CODE,
        obh.CREATEBY,obh.PAYMENTDAYTYPECODE,obh.remark
        ,obh.ARRIVAL_MODE,obh.SALESPERSON_NAME,obh.SALE_BRANCHNAME
        <if test="srcCode != null and srcCode != ''">
            having n.BILL_GROUP_ID like concat(concat('%',#{srcCode}),'%')
        </if>
    </select>

    <!--外复核-根据单据号查询条码明细-->
    <select id="getBarcodeHList" resultMap="barcodeMap">
        select TYPE,BARCODE,max(LCL_NO) LCL_NO,STATUS,GMT_CREATE,CREATOR_NAME,CONTAINER_CODE,STATION_CODE,PRINT_COUNT,PRINT_NAME,PRINT_DATE,CHECK_STATUS,
        CHECKER_NO,CHECKER_NAME,CHECK_TIME,BLDG_NAME,IS_MERGED,BILL_GROUP_ID
        from WMS_BARCODE_H where BILL_GROUP_ID = #{billGroupId}
        <if test="warehouseId != null and warehouseId != ''">
            and WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and bldg_id = #{bldgId}
        </if>
        group by TYPE,BARCODE,STATUS,GMT_CREATE,CREATOR_NAME,CONTAINER_CODE,STATION_CODE,PRINT_COUNT,PRINT_NAME,PRINT_DATE,CHECK_STATUS,
        CHECKER_NO,CHECKER_NAME,CHECK_TIME,BLDG_NAME,IS_MERGED,BILL_GROUP_ID
    </select>


    <select id="getOuterCheckD" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckDDTO">
        SELECT
            a.*,
            floor(a.qty / a.conversionRate) AS uomQty,
            mod(a.qty, a.conversionRate) AS oddPieceQty,
            floor(a.pickQty / a.conversionRate) AS pickUomQty,
            mod(a.pickQty, a.conversionRate) AS pickOddPieceQty
        from (
            SELECT DISTINCT
                g.id AS barcodeDId,
                d.PICK_TASK_D_ID AS pickTaskDId,
                g.id AS barcode,
                g.qty AS qty,
                d.PICK_QTY AS pickQty,
                d.PICKER_NAME AS pickerName,
                f.CHECK_TIME AS outerAskDate,
                m.ITEM_NAME AS itemName,
                m.ITEM_ID AS itemId,
                m.ITEM_CODE AS itemCode,
                m.ITEM_SPECS AS itemSpecs,
                m.MANUFACTURER AS manufacturer,
                m.BATCH_NUM AS batchNum,
                s.uom,
                f.LCL_NO AS lclNo,
                s.MANUFACTURER_LOT_NUM AS manufacturerLotNum,
                s.LOCATOR_CODE AS locatorCode,
                s.PRODUCTION_DATE AS productionDate,
                s.EXPIRED_DATE AS expiredDate,
                d.src_code AS srcCode,
                '' billType,
                f.WAREHOUSE_ID AS warehouseId,
                omsd.CONSIGNORID as consignorId,
                omsd.CONSIGNORCODE as consignorCode,
                omsd.CONSIGNORNAME as consignorName,
                CASE WHEN m.case_conversion_rate = 0 THEN 1 ELSE ifnull(m.case_conversion_rate, 1) END AS conversionRate
            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 oms_do_bill_d omsd on omsd.lineid = d.src_line_id
            WHERE d.PICK_QTY > 0
                AND g.qty > 0
                AND d.qty - IFNULL(d.sale_red_qty, 0) > 0
                AND f.ENTERPRISE_ID = #{enterpriseId}
                AND f.BILL_GROUP_ID = #{srcCode}
                AND s.type = 0
                AND f.type = 1
                AND g.CHECK_STATUS &lt;&gt; '5'
                <if test="warehouseId != null and warehouseId != ''">
                    and f.WAREHOUSE_ID=#{warehouseId}
                </if>
                <if test="bldgId != null and bldgId != ''">
                    and f.bldg_id = #{bldgId}
                </if>
        union all
        SELECT DISTINCT
            g.id AS barcodeDId,
            d.PICK_TASK_D_ID AS pickTaskDId,
            g.id AS barcode,
            d.qty - IFNULL(d.sale_red_qty, 0) AS qty,
            d.PICK_QTY AS pickQty,
            d.PICKER_NAME AS pickerName,
            f.CHECK_TIME AS outerAskDate,
            m.ITEM_NAME AS itemName,
            m.ITEM_ID AS itemId,
            m.ITEM_CODE AS itemCode,
            m.ITEM_SPECS AS itemSpecs,
            m.MANUFACTURER AS manufacturer,
            m.BATCH_NUM AS batchNum,
            s.uom,
            f.LCL_NO AS lclNo,
            s.MANUFACTURER_LOT_NUM AS manufacturerLotNum,
            s.LOCATOR_CODE AS locatorCode,
            s.PRODUCTION_DATE AS productionDate,
            s.EXPIRED_DATE AS expiredDate,
            d.src_code AS srcCode,
            '' billType,
            f.WAREHOUSE_ID AS warehouseId,
            omsd.CONSIGNORID as consignorId,
            omsd.CONSIGNORCODE as consignorCode,
            omsd.CONSIGNORNAME as consignorName,
            CASE WHEN m.case_conversion_rate = 0 THEN 1 ELSE ifnull(m.case_conversion_rate, 1) END AS conversionRate
        FROM WMS_BARCODE_D g
        LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
        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 omsd on omsd.lineid = d.src_line_id
        WHERE 1 = 1
            AND d.PICK_QTY > 0
            AND d.qty - IFNULL(d.sale_red_qty, 0) > 0
            AND f.ENTERPRISE_ID = #{enterpriseId}
            AND f.BILL_GROUP_ID = #{srcCode}
            AND s.type = 0
            AND f.type = 0
            AND g.CHECK_STATUS &lt;&gt; '5'
            <if test="warehouseId != null and warehouseId != ''">
                and f.WAREHOUSE_ID=#{warehouseId}
            </if>
            <if test="bldgId != null and bldgId != ''">
                and f.bldg_id = #{bldgId}
            </if>
        ) a order by a.lclNo,a.itemId

    </select>
    <!--外复核-根据单据号查询商品明细-->
   <!-- 外复核明细SQL修改备份
      <select id="getOuterCheckD" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckDDTO">
        SELECT a.*, floor(a.qty / a.conversionRate) AS uomQty
        , mod(a.qty, a.conversionRate) AS oddPieceQty
        , floor(a.pickQty / a.conversionRate) AS pickUomQty
        , mod(a.pickQty, a.conversionRate) AS pickOddPieceQty
        from (
        SELECT barcodeDId,pickTaskDId,barcode,sum(qty) qty,sum(pickQty) pickQty,pickerName,
        outerAskDate,
        itemName,itemId,itemCode,itemSpecs,
        manufacturer, batchNum,
        uom, lclNo,
        manufacturerLotNum, warehouseId, locatorCode,
        productionDate, expiredDate,
        shipperId, consignorId, srcCode,billType,
        consignorCode,consignorName,
        conversionRate from (
        select DISTINCT g.id barcodeDId,d.PICK_TASK_D_ID pickTaskDId,g.id barcode,g.qty qty,d.PICK_QTY pickQty,d.PICKER_NAME pickerName,
        f.CHECK_TIME outerAskDate,
        m.ITEM_NAME itemName,m.ITEM_ID itemId,m.ITEM_CODE itemCode,m.ITEM_SPECS itemSpecs,m.MANUFACTURER
        manufacturer,m.BATCH_NUM batchNum,
        s.uom,f.LCL_NO lclNo,s.MANUFACTURER_LOT_NUM
        manufacturerLotNum,f.WAREHOUSE_ID warehouseId,s.LOCATOR_CODE locatorCode,
        s.PRODUCTION_DATE productionDate,s.EXPIRED_DATE expiredDate,
        n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,n.src_code srcCode,n.type billType,
        obh.CONSIGNORCODE consignorCode,obh.CONSIGNORNAME consignorName,
        case when m.case_conversion_rate=0 then 1 else ifnull(m.case_conversion_rate,1) end conversionRate
        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_pick_task_h h ON d.pick_task_id = h.pick_task_id
        LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
        LEFT JOIN WMS_WAVE_BILL n ON n.id = m.bill_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        WHERE  1=1
        AND d.PICK_QTY>0
        AND g.qty>0
        AND (d.qty-IFNULL(d.sale_red_qty,0))>0
        AND f.ENTERPRISE_ID = #{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 1
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        UNION ALL
        SELECT DISTINCT
        g.id AS barcodeDId,
        d.PICK_TASK_D_ID pickTaskDId,
        g.id AS barcode,
        (d.qty - IFNULL(d.sale_red_qty,0)) AS qty,
        d.PICK_QTY AS pickQty,
        d.PICKER_NAME AS pickerName,
        f.CHECK_TIME AS outerAskDate,
        m.ITEM_NAME AS itemName,
        m.ITEM_ID AS itemId,
        m.ITEM_CODE AS itemCode,
        m.ITEM_SPECS AS itemSpecs,
        m.MANUFACTURER AS manufacturer,
        m.BATCH_NUM AS batchNum,
        s.uom,
        f.LCL_NO AS lclNo,
        s.MANUFACTURER_LOT_NUM AS manufacturerLotNum,
        f.WAREHOUSE_ID AS warehouseId,
        s.LOCATOR_CODE AS locatorCode,
        s.PRODUCTION_DATE AS productionDate,
        s.EXPIRED_DATE AS expiredDate,
        n.SHIPPER_ID AS shipperId,
        n.CONSIGNOR_ID AS consignorId,
        n.src_code AS srcCode,
        n.type AS billType,
        obh.CONSIGNORCODE AS consignorCode,
        obh.CONSIGNORNAME AS consignorName,
        CASE

        WHEN m.case_conversion_rate = 0 THEN
        1 ELSE ifnull( m.case_conversion_rate, 1 )
        END AS conversionRate
        FROM
        WMS_BARCODE_D g
        LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
        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 wms_pick_task_h h ON d.pick_task_id = h.pick_task_id
        LEFT JOIN WMS_WAVE_BILL n ON n.id = m.bill_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        WHERE
        1 = 1
        AND d.PICK_QTY > 0
        AND (d.qty-IFNULL(d.sale_red_qty,0))>0
        AND f.ENTERPRISE_ID = #{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 0
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        ) aa
        GROUP BY
        barcodeDId,pickTaskDId,barcode,pickerName,
        outerAskDate,
        itemName,itemId,itemCode,itemSpecs,
        manufacturer, batchNum,
        uom, lclNo,
        manufacturerLotNum, warehouseId, locatorCode,
        productionDate, expiredDate,
        shipperId, consignorId, srcCode,billType,
        consignorCode,consignorName,
        conversionRate
        UNION all
        select DISTINCT g.id barcodeDId,g.WMS_PICK_TASK_D_ID pickTaskDId,g.id AS barcode,g.qty qty,null pickQty,null pickerName,
        f.CHECK_TIME outerAskDate,
        m.ITEM_NAME itemName,m.ITEM_ID itemId,m.ITEM_CODE itemCode,m.ITEM_SPECS itemSpecs,m.MANUFACTURER
        manufacturer,null batchNum,
        s.uom,f.LCL_NO lclNo,null
        manufacturerLotNum,f.WAREHOUSE_ID warehouseId,null locatorCode,
        null productionDate,null expiredDate,
        n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,n.BILL_GROUP_ID srcCode,n.type billType,
        obh.CONSIGNORCODE consignorCode,obh.CONSIGNORNAME consignorName,
        case when m.case_conversion_rate=0 then 1 else ifnull(m.case_conversion_rate,1) end conversionRate
        FROM WMS_BARCODE_D g
        LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
        LEFT JOIN WMS_WAVE_BILL_D m ON m.merged_bill_id = f.BILL_GROUP_ID
        LEFT JOIN WMS_WAVE_BILL n ON n.id = m.bill_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        left join wms_wave_storage s on m.ID = s.BILL_D_ID
        WHERE m.ITEM_ID = g.ITEM_ID
        AND g.qty>0
        AND f.ENTERPRISE_ID = #{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 0
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        ) a order by a.lclNo,a.itemId
    </select>-->

    <select id="getOuterCheckPickTaskD" resultType="com.prolog.cs.biz.outbound.model.PickTaskD">
        select DISTINCT d.pick_task_d_id pickTaskDId,d.pick_task_id pickTaskId,d.pick_qty pickQty from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id=h.pick_task_id
        left join WMS_WAVE_BILL n on h.BILL_GROUP_ID = n.BILL_GROUP_ID
        where bill.ENTERPRISE_ID=#{enterpriseId} and d.checker_no = #{operatorCode} and n.BILL_GROUP_ID in
        <foreach collection="srcCodes" item="srcCode" open="(" separator="," close=")">
            #{srcCode}
        </foreach>
    </select>

    <update id="updateBarcodeHStatus">
        update WMS_BARCODE_H set CHECK_STATUS ='0' where id in(
            select DISTINCT BARCODE_ID from WMS_BARCODE_D g
            left join wms_pick_task_d d on g.WMS_PICK_TASK_D_ID = d.PICK_TASK_D_ID
            left join wms_pick_task_h h on d.pick_task_id=h.pick_task_id
            where h.pick_order_id = #{pickOrderId} and g.warehouse_id = #{warehouseId}
        )
    </update>

    <select id="getDataByTaskDId" resultType="com.prolog.cs.biz.outbound.model.dto.InvStockOutDTO">
        select DISTINCT m.id lineBillId,m.BILL_ID billId,s.LOCATOR_ID locatorId,s.BATCH_ID batchId,d.WAREHOUSE_ID
        from wms_pick_task_d d
        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
        where 1=1
        <if test="warehouseId != null and warehouseId != ''">
            and d.WAREHOUSE_ID = #{warehouseId}
        </if>
        <if test="pickTaskDId != null and pickTaskDId != ''">
            and d.pick_task_d_id = #{pickTaskDId}
        </if>
    </select>

    <select id="getPickMoveCount" resultType="com.prolog.cs.biz.outbound.model.dto.PickMoveCountDTO">
        SELECT b.bldgId,b.bldgName,ifnull(a.activeMoveCount,0) activeMoveCount,ifnull(b.passiveMoveCount,0) passiveMoveCount from
        (
        SELECT count(*) activeMoveCount,w.bldgId,w.bldgName from (SELECT DISTINCT v.MOVE_TASK_ID,v.BLDG_ID bldgId,v.BLDG_NAME bldgName from WMS_MOVE_TASK v
        left join WMS_WAVE_BILL_D m on v.BILL_D_ID = m.id
        left join wms_wave_storage s on m.ID = s.BILL_D_ID
        left join wms_pick_task_d d on d.WAVE_STORAGE_ID = s.id
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        where v.status = '0' and v.MOVE_TYPE in('1','2','3')
        and h.PICK_TASK_TYPE= #{pickTaskType}
        and h.WAREHOUSE_ID = #{warehouseId}
        ) w GROUP BY w.bldgId,w.bldgName
        ) a
        join
        (
        SELECT count(*) passiveMoveCount,n.bldgId,n.bldgName from (SELECT DISTINCT v.MOVE_TASK_ID,v.BLDG_ID bldgId,v.BLDG_NAME bldgName from WMS_MOVE_TASK v
        left join WMS_WAVE_BILL_D m on v.BILL_D_ID = m.id
        left join wms_wave_storage s on m.ID = s.BILL_D_ID
        left join wms_pick_task_d d on d.WAVE_STORAGE_ID = s.id
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        where v.status = '0' and v.MOVE_TYPE ='4'
        and h.PICK_TASK_TYPE= #{pickTaskType}
        and h.WAREHOUSE_ID = #{warehouseId}
        ) n GROUP BY n.bldgId,n.bldgName
        ) b
        on a.bldgId = b.bldgId
    </select>

    <select id="getPickTaskCount" resultType="java.lang.Integer">
        SELECT count(DISTINCT h.PICK_TASK_ID) from WMS_PICK_TASK_H h
        left join wms_pick_task_d d on d.pick_task_id = h.pick_task_id
        left join wms_wave_storage s on d.WAVE_STORAGE_ID = s.id
        where h.status = '1' and h.warehouse_id = #{warehouseId}
        and h.PICK_TASK_TYPE= #{pickTaskType}
        UNION all
        SELECT count(DISTINCT v.MOVE_TASK_ID) from WMS_MOVE_TASK v
        left join WMS_WAVE_BILL_D m on v.BILL_D_ID = m.id
        left join wms_wave_storage s on m.ID = s.BILL_D_ID
        left join wms_pick_task_d d on d.WAVE_STORAGE_ID = s.id
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        WHERE h.status = '1'
        and h.WAREHOUSE_ID = #{warehouseId}
        and v.STATUS in('1','2','3')
        and h.PICK_TASK_TYPE= #{pickTaskType}
    </select>

    <select id="getPickingOrderCount" resultType="java.lang.Integer">
        SELECT
        count(DISTINCT h.BILL_GROUP_ID)
        FROM
        WMS_PICK_TASK_H h
        INNER JOIN WMS_PICK_TASK_D d ON h.PICK_TASK_ID = d.PICK_TASK_ID
        where h.STATUS &lt; 2 and d.PICKER_NO=#{operatorCode}
    </select>

    <select id="getMoveTaskId" resultType="java.lang.String">
        select DISTINCT v.MOVE_TASK_ID from WMS_MOVE_TASK v
            inner join wms_wave_storage s on v.MOVE_TASK_ID = s.MOVE_TASK_ID
            inner join wms_pick_task_d d on d.WAVE_STORAGE_ID = s.id
            where d.WAREHOUSE_ID = #{warehouseId}
            and d.PICK_TASK_D_ID = #{pickTaskDId}
    </select>

    <!--根据合并单号查询：客户、货主-->
    <select id="getLclDetail" resultType="com.prolog.cs.biz.outbound.model.dto.OutboundHandoverDTO">
        SELECT DISTINCT obh.CONSIGNORNAME consignorName,obh.SHIPPERNAME shipperName,n.BILL_GROUP_ID billGroupId
        FROM WMS_WAVE_BILL n
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        WHERE n.BILL_GROUP_ID = #{billGroupId}
        <if test="enterpriseId != null and enterpriseId != ''">
            and n.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and n.WAREHOUSE_ID= #{warehouseId}
        </if>
    </select>

    <!--根据合并单号查询：客户、货主-->
    <select id="getLclDetails" resultType="com.prolog.cs.biz.outbound.model.dto.OutboundHandoverDTO">
        SELECT DISTINCT obh.CONSIGNORNAME consignorName,obh.SHIPPERNAME shipperName,n.BILL_GROUP_ID billGroupId
        FROM WMS_WAVE_BILL n
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        WHERE n.BILL_GROUP_ID concat(concat('%',#{billGroupId}),'%')
        <if test="enterpriseId != null and enterpriseId != ''">
            and n.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and n.WAREHOUSE_ID= #{warehouseId}
        </if>
    </select>

    <select id="getItemCode" resultType="map">
        select DISTINCT m.ITEM_CODE,m.ITEM_ID from wms_pick_task_d d,wms_wave_storage s,WMS_WAVE_BILL_D m
        where d.WAVE_STORAGE_ID = s.id and m.ID = s.BILL_D_ID
        and d.PICK_TASK_D_ID = #{pickTaskDId}
        <if test="warehouseId != null and warehouseId != ''">
            and d.WAREHOUSE_ID= #{warehouseId}
        </if>
    </select>

    <!--根据 合并单号 查询条码列表(件数、箱数、条码)-->
    <select id="getBarcodeDList" resultType="com.prolog.cs.biz.outbound.model.dto.HandoverDetailDTO">
        SELECT DISTINCT * from (
            SELECT
            case when barcodeD.IS_PIECE=0 then 0 when barcodeD.IS_PIECE != 0 then 1 end as lclQty,
            case when barcodeD.IS_PIECE=0 then sum(floor(barcodeD.QTY/m.case_conversion_rate)) when barcodeD.IS_PIECE != 0 then 0 end as uomQty,
            barcodeH.BARCODE barcode,bill.BILL_GROUP_ID srcCode,barcodeD.IS_PIECE type
            FROM WMS_BARCODE_H barcodeH
            LEFT JOIN WMS_BARCODE_D barcodeD ON barcodeH.id = barcodeD.BARCODE_ID
            LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = barcodeH.BILL_GROUP_ID
            LEFT JOIN wms_pick_task_d d ON d.PICK_TASK_D_ID = barcodeD.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
            WHERE barcodeH.BILL_GROUP_ID = #{billGroupId}
            <if test="enterpriseId != null and enterpriseId != ''">
                and barcodeH.ENTERPRISE_ID = #{enterpriseId}
            </if>
            <if test="warehouseId != null and warehouseId != ''">
                and barcodeH.WAREHOUSE_ID= #{warehouseId}
            </if>
            group by barcodeH.BARCODE,bill.BILL_GROUP_ID,barcodeD.IS_PIECE,m.case_conversion_rate
        )
    </select>

    <!--外复核草单明细-->
    <select id="getOuterCheckItem" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckItemDTO">
        SELECT sum(a.qty) AS qty
        , floor(sum(a.qty) / a.conversionRate) AS uomQty
        , mod(sum(a.qty), a.conversionRate) AS oddPieceQty
        , a.itemLot,a.itemId,a.itemName, a.itemSpecs, a.manufacturer, a.warehouseAreaCode, a.uom
        , DATE_FORMAT(a.expired_date, '%Y-%m-%d') AS expiredDate, a.remark
        , a.manufacturerLotNum, a.itemCode, a.lclNo,a.conversionRate,a.barcode_d_id,a.type,a.pickerName
        FROM (
        SELECT DISTINCT if(d.check_qty=0,d.pick_qty,d.check_qty) as qty,CONCAT(m.item_id,'-',s.MANUFACTURER_LOT_NUM) AS itemLot,m.item_id as itemId, m.item_name AS itemName, m.ITEM_SPECS AS itemSpecs, s.MANUFACTURER_LOT_NUM AS manufacturerLotNum, m.MANUFACTURER AS manufacturer
        , s.WAREHOUSE_AREA_CODE AS warehouseAreaCode, s.uom, s.expired_date, m.item_code AS itemCode
        , f.lcl_no AS lclNo,d.barcode_d_id,f.type,ifnull(picker_name,picker_no) pickerName
        , CASE
        WHEN m.CASE_CONVERSION_RATE = 0 THEN 1
        ELSE ifnull(m.CASE_CONVERSION_RATE, 1)
        END AS conversionRate, obh.remark
        FROM wms_wave_storage s
        LEFT JOIN WMS_WAVE_BILL_D m ON m.ID = s.BILL_D_ID
        LEFT JOIN wms_pick_task_d d ON s.id = d.wave_storage_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = m.merged_bill_id
        LEFT JOIN WMS_BARCODE_H f ON f.bill_group_id = obh.billid
        where s.ENTERPRISE_ID=#{enterpriseId} and obh.billid = #{srcCode} and s.TYPE=0
        <if test="warehouseId != null and warehouseId != ''">
            and s.WAREHOUSE_ID=#{warehouseId}
        </if>
        ) a
        GROUP BY a.itemLot,a.itemId,a.itemName, a.itemSpecs, a.manufacturer, a.warehouseAreaCode, a.uom, a.conversionRate, a.remark, a.expired_date, a.manufacturerLotNum, a.itemCode, a.lclNo,a.barcode_d_id,a.type,a.pickerName
        order by a.warehouseAreaCode,a.itemCode
    </select>

    <!--外复核草单头-->
    <select id="getOuterCheckHead" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckHeadDTO">
        SELECT DISTINCT obh.CONSIGNORNAME consignorName,obh.SHIPPERNAME shipperName,n.BILL_GROUP_ID billGroupId,obh.CARRIER_NAME carrierName,
        case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,obh.BILLTYPE billType,obh.LINE_NAME lineName,obh.cust_person custPerson,obh.cust_person_tel custPersonTel,
        obh.SALESPERSON_NAME salespersonName,obh.SALE_BRANCHNAME saleBranchname,obh.CREATEBY,obh.PAYMENTDAYTYPECODE paymentType,obh.RECEIVE_ADDRESS customerAddress
        FROM WMS_WAVE_BILL n
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        WHERE n.BILL_GROUP_ID = #{srcCode}
        <if test="enterpriseId != null and enterpriseId != ''">
            and n.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and n.WAREHOUSE_ID= #{warehouseId}
        </if>
    </select>

    <!--外复核单明细-->
    <select id="getOuterCheckItemH" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckItemDTO">
        SELECT a.*, floor(a.qty / a.conversionRate) AS uomQty
        , mod(a.qty, a.conversionRate) AS oddPieceQty
        from (
        SELECT sum(qty) qty,pickMan,
        itemName,itemSpecs,
        manufacturer,
        uom, lclNo,
        manufacturerLotNum,
        conversionRate,barcodeDId,barcode,warehouseArea,remark from (
        select DISTINCT g.id barcodeDId,f.barcode,g.qty qty,d.PICKER_NAME pickMan,
        m.ITEM_NAME itemName,m.ITEM_SPECS itemSpecs,m.MANUFACTURER
        manufacturer,
        s.uom,f.LCL_NO lclNo,s.MANUFACTURER_LOT_NUM
        manufacturerLotNum,
        case when m.case_conversion_rate=0 then 1 else ifnull(m.case_conversion_rate,1) end conversionRate,s.WAREHOUSE_AREA_CODE warehouseArea,obh.remark
        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_pick_task_h h ON d.pick_task_id = h.pick_task_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
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        WHERE  1=1
        AND d.PICK_QTY>0
        AND g.qty>0
        AND f.ENTERPRISE_ID=#{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 1
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        ) k
        GROUP BY
        barcodeDId,barcode,pickMan,
        itemName,itemSpecs,
        manufacturer,
        uom, lclNo,
        manufacturerLotNum,
        conversionRate,warehouseArea,remark
        UNION all
        select DISTINCT g.qty qty,null pickMan,
        m.ITEM_NAME itemName,m.ITEM_SPECS itemSpecs,m.MANUFACTURER
        manufacturer,
        s.uom,f.LCL_NO lclNo,null
        manufacturerLotNum,
        case when m.case_conversion_rate=0 then 1 else ifnull(m.case_conversion_rate,1) end conversionRate
        ,g.id barcodeDId,f.barcode,s.WAREHOUSE_AREA_CODE warehouseArea,obh.remark
        FROM WMS_BARCODE_D g
        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 WMS_WAVE_BILL_D m ON m.BILL_ID = n.id
        left join wms_wave_storage s on m.ID = s.BILL_D_ID
        WHERE m.ITEM_ID = g.ITEM_ID
        AND g.qty>0
        AND f.ENTERPRISE_ID=#{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 0
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        ) a order by a.lclNo,a.itemName
    </select>

    <!--外复核单头-->
    <select id="getOuterCheckHeadH" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckHeadDTO">
        SELECT obh.CONSIGNORNAME consignorName,obh.SHIPPERNAME shipperName,n.shipper_code shipperCode,n.BILL_GROUP_ID billGroupId,obh.CARRIER_NAME carrierName,f.CHECKER_NAME checkerName,
        case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,obh.BILLTYPE billType,
        obh.IS_EMERGENCY isEmergency,obh.SALESPERSON_NAME salespersonName,obh.SALE_BRANCHNAME saleBranchname,obh.CREATEBY,obh.LINE_NAME lineName,
        max(stage.STAGE_CODE) stageCodeMax,min(stage.STAGE_CODE) stageCodeMin,obh.PAYMENTDAYTYPECODE paymentType,obh.RECEIVE_ADDRESS customerAddress,
        obh.branchname,obh.branchcode
        FROM WMS_WAVE_BILL n
        LEFT JOIN WMS_BARCODE_H f on f.bill_group_id = n.BILL_GROUP_ID
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        LEFT JOIN WMS_WORK_STAGE_RUNTIME stage ON stage.BILL_GROUP_ID=n.BILL_GROUP_ID
        WHERE n.BILL_GROUP_ID = #{srcCode}
        <if test="enterpriseId != null and enterpriseId != ''">
            and n.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and n.WAREHOUSE_ID= #{warehouseId}
        </if>
        group by obh.CONSIGNORNAME,obh.SHIPPERNAME,n.BILL_GROUP_ID,obh.CARRIER_NAME,obh.BILLTYPE,obh.LINE_NAME,n.shipper_code,obh.branchname,obh.branchcode,
        obh.ARRIVAL_MODE,obh.IS_EMERGENCY,f.CHECKER_NAME,obh.SALE_BRANCHNAME,obh.CREATEBY,obh.SALESPERSON_NAME,obh.PAYMENTDAYTYPECODE,obh.RECEIVE_ADDRESS
    </select>

    <!--外复核出库清单，特殊订单，自提出库单头-->
    <select id="getDeliveryOuterHeadH" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckHeadDTO">
        SELECT obh.CONSIGNORNAME consignorName,obh.SHIPPERNAME shipperName,n.BILL_GROUP_ID billGroupId,obh.CARRIER_NAME carrierName,f.CHECKER_NAME checkerName,
        case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end arrivalMode,obh.BILLTYPE billType,
        obh.IS_EMERGENCY isEmergency,obh.SALESPERSON_NAME salespersonName,obh.SALE_BRANCHNAME saleBranchname,obh.CREATEBY,obh.LINE_NAME lineName,
        max(stage.STAGE_CODE) stageCodeMax,min(stage.STAGE_CODE) stageCodeMin,obh.PAYMENTDAYTYPECODE paymentType,obh.RECEIVE_ADDRESS customerAddress,obh.cust_person custPerson,obh.cust_person_tel custPersonTel,obh.remark,
        obh.branchname,obh.operationlicenseno,f.gmt_create createdate
        FROM WMS_WAVE_BILL n
        LEFT JOIN WMS_BARCODE_H f on f.bill_group_id = n.BILL_GROUP_ID
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        LEFT JOIN WMS_WORK_STAGE_RUNTIME stage ON stage.BILL_GROUP_ID=n.BILL_GROUP_ID
        WHERE n.BILL_GROUP_ID = #{srcCode}
        <if test="enterpriseId != null and enterpriseId != ''">
            and n.ENTERPRISE_ID = #{enterpriseId}
        </if>
        <if test="warehouseId != null and warehouseId != ''">
            and n.WAREHOUSE_ID= #{warehouseId}
        </if>
        group by obh.CONSIGNORNAME,obh.SHIPPERNAME,n.BILL_GROUP_ID,obh.CARRIER_NAME,obh.BILLTYPE,obh.LINE_NAME,
        obh.ARRIVAL_MODE,obh.IS_EMERGENCY,f.CHECKER_NAME,obh.SALE_BRANCHNAME,obh.CREATEBY,obh.SALESPERSON_NAME,obh.PAYMENTDAYTYPECODE,obh.RECEIVE_ADDRESS,obh.cust_person,obh.cust_person_tel,obh.remark,f.gmt_create,
        obh.branchname,obh.operationlicenseno
    </select>

    <!--外复核单明细-->
    <select id="getDeliveryOuterItemH" resultType="com.prolog.cs.biz.outbound.model.dto.OuterCheckItemDTO">
        SELECT a.*, floor(a.qty / a.conversionRate) AS uomQty
        , mod(a.qty, a.conversionRate) AS oddPieceQty
        , TRUNCATE(a.price * a.qty,2) AS payamount
        from (
        SELECT barcodeDId,barcode,sum(qty) qty,sum(pickQty) pickQty,pickerName,
        outerAskDate,
        itemName,itemId,itemCode,itemSpecs,
        manufacturer, batchNum,
        uom, lclNo,
        manufacturerLotNum, warehouseId, locatorCode,
        pdateFrom, expiredDate,
        shipperId, consignorId, srcCode,billType,
        consignorCode,consignorName,price,
        conversionRate from (
        select DISTINCT g.id barcodeDId,f.barcode,g.qty qty,d.PICK_QTY pickQty,d.PICKER_NAME pickerName,
        f.CHECK_TIME outerAskDate,
        m.ITEM_NAME itemName,m.ITEM_ID itemId,m.ITEM_CODE itemCode,m.ITEM_SPECS itemSpecs,m.MANUFACTURER
        manufacturer,m.BATCH_NUM batchNum,
        s.uom,f.LCL_NO lclNo,s.MANUFACTURER_LOT_NUM
        manufacturerLotNum,f.WAREHOUSE_ID warehouseId,s.LOCATOR_CODE locatorCode,
        s.PRODUCTION_DATE pdateFrom,s.EXPIRED_DATE expiredDate,
        n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,n.src_code srcCode,n.type billType,
        obh.CONSIGNORCODE consignorCode,obh.CONSIGNORNAME consignorName,obd.price,
        case when m.case_conversion_rate=0 then 1 else ifnull(m.case_conversion_rate,1) end conversionRate
        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_pick_task_h h ON d.pick_task_id = h.pick_task_id
        LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
        LEFT JOIN WMS_WAVE_BILL n ON n.id = m.bill_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        LEFT JOIN OMS_DO_BILL_D obd ON m.src_line_id = obd.lineid
        WHERE  1=1
        AND d.PICK_QTY>0
        AND g.qty>0
        AND f.ENTERPRISE_ID = #{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 1
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        UNION ALL
        SELECT DISTINCT
        g.id AS barcodeDId,
        f.barcode,
        d.qty AS qty,
        d.PICK_QTY AS pickQty,
        d.PICKER_NAME AS pickerName,
        f.CHECK_TIME AS outerAskDate,
        m.ITEM_NAME AS itemName,
        m.ITEM_ID AS itemId,
        m.ITEM_CODE AS itemCode,
        m.ITEM_SPECS AS itemSpecs,
        m.MANUFACTURER AS manufacturer,
        m.BATCH_NUM AS batchNum,
        s.uom,
        f.LCL_NO AS lclNo,
        s.MANUFACTURER_LOT_NUM AS manufacturerLotNum,
        f.WAREHOUSE_ID AS warehouseId,
        s.LOCATOR_CODE AS locatorCode,
        s.PRODUCTION_DATE AS pdateFrom,
        s.EXPIRED_DATE AS expiredDate,
        n.SHIPPER_ID AS shipperId,
        n.CONSIGNOR_ID AS consignorId,
        n.src_code AS srcCode,
        n.type AS billType,
        obh.CONSIGNORCODE AS consignorCode,
        obh.CONSIGNORNAME AS consignorName,
        obd.price,
        CASE

        WHEN m.case_conversion_rate = 0 THEN
        1 ELSE ifnull( m.case_conversion_rate, 1 )
        END AS conversionRate
        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 wms_pick_task_h h ON d.pick_task_id = h.pick_task_id
        LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
        LEFT JOIN WMS_WAVE_BILL n ON n.id = m.bill_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        LEFT JOIN OMS_DO_BILL_D obd ON m.src_line_id = obd.lineid
        WHERE
        1 = 1
        AND d.PICK_QTY > 0
        AND f.ENTERPRISE_ID = #{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 0
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        ) aa
        GROUP BY
        barcodeDId,barcode,pickerName,
        outerAskDate,
        itemName,itemId,itemCode,itemSpecs,
        manufacturer, batchNum,
        uom, lclNo,
        manufacturerLotNum, warehouseId, locatorCode,
        pdateFrom, expiredDate,
        shipperId, consignorId, srcCode,billType,
        consignorCode,consignorName,
        conversionRate,price
        UNION all
        select DISTINCT g.id barcodeDId,f.barcode,g.qty qty,null pickQty,null pickerName,
        f.CHECK_TIME outerAskDate,
        m.ITEM_NAME itemName,m.ITEM_ID itemId,m.ITEM_CODE itemCode,m.ITEM_SPECS itemSpecs,m.MANUFACTURER
        manufacturer,null batchNum,
        s.uom,f.LCL_NO lclNo,null
        manufacturerLotNum,f.WAREHOUSE_ID warehouseId,null locatorCode,
        null pdateFrom,null expiredDate,
        n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,n.BILL_GROUP_ID srcCode,n.type billType,
        obh.CONSIGNORCODE consignorCode,obh.CONSIGNORNAME consignorName,obd.price,
        case when m.case_conversion_rate=0 then 1 else ifnull(m.case_conversion_rate,1) end conversionRate
        FROM WMS_BARCODE_D g
        LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
        LEFT JOIN WMS_WAVE_BILL_D m ON m.merged_bill_id = f.BILL_GROUP_ID
        LEFT JOIN WMS_WAVE_BILL n ON n.id = m.bill_id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        LEFT JOIN OMS_DO_BILL_D obd ON m.src_line_id = obd.lineid
        left join wms_wave_storage s on m.ID = s.BILL_D_ID
        WHERE m.ITEM_ID = g.ITEM_ID
        AND g.qty>0
        AND f.ENTERPRISE_ID = #{enterpriseId}
        AND n.BILL_GROUP_ID = #{srcCode}
        AND s.type = 0
        AND f.type = 0
        <if test="warehouseId != null and warehouseId != ''">
            and f.WAREHOUSE_ID=#{warehouseId}
        </if>
        ) a order by a.lclNo,a.itemId
    </select>

    <!--整件拣货标签-->
    <select id="getWholeLabel" resultType="map">
        SELECT
            d.pick_task_d_id BQ,
            bd.id,<!--条码(二维码)-->
            bd.container_code,
            n.BILL_GROUP_ID DJBHQ,
            obh.LINE_NAME PSFX,
            obh.shipperid,
            obh.shipperCode,
            obh.shipperName,
            obh.SHIPPERNAME DWMCH,
            m.item_code	itemcode,
            m.item_id itemId,
            m.ITEM_NAME SPMCH,
            m.ITEM_SPECS SHPGG,
            s.MANUFACTURER_LOT_NUM PH,
            m.MANUFACTURER SHENGCCJ,
            bd.QTY Z_QTY,
            e.operator_name FHR1,
            n.contact_name lxr,
            n.contact_tel lxfs,
            obh.RECEIVE_ADDRESS DW_DZ,
            obh.SALE_BRANCHNAME BM,
            obh.SALESPERSON_NAME YWY,
            obh.remark REMARK,
            s.PRODUCTION_DATE_SHOW P_DATE_S,
            s.EXPIRED_DATE_SHOW E_DATE_S,
            s.uom DW,
            s.LOCATOR_CODE,
            e.PRINT_STATUS PRINTSTATUS,
            obh.billno billid,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end JLGG,
            IFNULL(bd.print_cnt,0) print_cnt
        FROM  wms_barcode_d bd
        LEFT JOIN wms_pick_task_d d ON bd.wms_pick_task_d_id = d.pick_task_d_id
        LEFT JOIN wms_pick_task_h h ON d.pick_task_id = h.pick_task_id
        LEFT JOIN wms_pick_order e ON e.pick_order_id = h.pick_order_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_WAVE_BILL n ON m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        WHERE h.ENTERPRISE_ID=#{enterpriseId}
        AND h.pick_task_type  = 0 AND h.type = 0
        AND d.pick_task_d_id in
        <foreach collection="pickTaskDIds" item="pickTaskDId" open="(" separator="," close=")">
            #{pickTaskDId}
        </foreach>
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID= #{warehouseId}
        </if>
        order by n.BILL_GROUP_ID,s.ITEM_PLACE_TYPE,s.LOCATOR_CODE,s.MANUFACTURER_LOT_NUM,m.ITEM_NAME,length(d.whole_num),d.whole_num
    </select>

    <!--整件拣货标签-->
    <select id="wholeLabelRepairPrint" resultType="map">
        SELECT
            bd.id,<!--条码(二维码)-->
            bd.container_code,
            n.BILL_GROUP_ID DJBHQ,
            obh.LINE_NAME PSFX,
            obh.shipperid,
            obh.shipperCode,
            obh.shipperName,
            obh.SHIPPERNAME DWMCH,
            m.item_code	itemcode,
            m.item_id itemId,
            m.ITEM_NAME SPMCH,
            m.ITEM_SPECS SHPGG,
            s.MANUFACTURER_LOT_NUM PH,
            m.MANUFACTURER SHENGCCJ,
            bd.QTY Z_QTY,
            e.operator_name FHR1,
            n.contact_name lxr,
            n.contact_tel lxfs,
            obh.RECEIVE_ADDRESS DW_DZ,
            obh.SALE_BRANCHNAME BM,
            obh.SALESPERSON_NAME YWY,
            obh.remark REMARK,
            s.PRODUCTION_DATE_SHOW P_DATE_S,
            s.EXPIRED_DATE_SHOW E_DATE_S,
            s.uom DW,
            s.LOCATOR_CODE,
            e.PRINT_STATUS PRINTSTATUS,
            obh.billno billid,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end JLGG,
            IFNULL(bd.print_cnt,0) print_cnt
        FROM  wms_barcode_d bd
        LEFT JOIN wms_pick_task_d d ON bd.wms_pick_task_d_id = d.pick_task_d_id
        LEFT JOIN wms_pick_task_h h ON d.pick_task_id = h.pick_task_id
        LEFT JOIN wms_pick_order e ON e.pick_order_id = h.pick_order_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_WAVE_BILL n ON m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
        WHERE h.ENTERPRISE_ID=#{enterpriseId}
        AND bd.id in
        <foreach collection="barcodeDId" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID= #{warehouseId}
        </if>
        order by n.BILL_GROUP_ID,s.ITEM_PLACE_TYPE,s.LOCATOR_CODE,s.MANUFACTURER_LOT_NUM,m.ITEM_NAME,length(d.whole_num),d.whole_num
    </select>

    <!--整件补货标签-->
    <select id="getWholeMoveLabel" resultType="map">
        select
            DISTINCT
            bd.id,
            bd.container_code,
            IFNULL(bd.print_cnt,0) print_cnt,
            m.ITEM_NAME SPMCH,
            m.ITEM_SPECS SHPGG,
            s.MANUFACTURER_LOT_NUM PH,
            m.MANUFACTURER SHENGCCJ,
            d.whole_num NR,
            s.PRODUCTION_DATE_SHOW P_DATE_S,
            s.EXPIRED_DATE_SHOW E_DATE_S,
            s.WAREHOUSE_AREA_CODE area,s.uom||'/件' DW,
            e.OPERATOR_NAME PY_JHY,
            v.SRC_WAREHOUSE_LOCATOR_NUM XSHW,
            d.pick_task_d_id BQ,
            '['||n.TOTAL_PCS||']' DD_JS,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end JLGG,
            obh.BILLTYPE CKLX,
            n.BILL_GROUP_ID DJBHQ,
            obh.LINE_NAME PSFX,
            obh.TOT_PCS ZJS,
            obh.CONSIGNORNAME YZMCH,
            obh.SHIPPERNAME DWMCH,
            obh.CUST_PERSON LXR,
            obh.RECEIVE_ADDRESS DW_DZ,
            case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end THFS,
            '正常' KHBZ,
            v.DEST_WAREHOUSE_LOCATOR_NUM KB,
            m.ITEM_CODE SPBH,
            m.item_id itemId,
            s.FLOOR_NUM HWLC,
            e.OPERATOR_NAME CZR,
            v.MOVE_TYPE MOVETYPE,
            h.pick_order_id pickOrderId,
            d.print_count PRINTCOUNT,
            e.PRINT_STATUS PRINTSTATUS,
            v.SRC_ITEM_PLACE_TYPE,
            v.gmt_create,
            v.dest_warehouse_area_num destAreaCode,
            obh.SALESPERSON_NAME YWY,
            obh.CREATEBY KPY,
            obh.PAYMENTDAYTYPECODE ZQ,
            obh.CUST_PERSON_TEL LXFS,
            obh.SALE_BRANCHNAME BM,
            bd.QTY Z_QTY
        FROM  wms_barcode_d bd
        LEFT JOIN wms_pick_task_d d ON bd.wms_pick_task_d_id = d.pick_task_d_id
        LEFT JOIN wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        LEFT JOIN wms_pick_order e on e.pick_order_id = h.pick_order_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_MOVE_TASK v on v.BILL_D_ID = m.id
        LEFT JOIN WMS_WAVE_BILL n on m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where h.ENTERPRISE_ID=#{enterpriseId}
        AND h.type = 1
        and d.pick_task_d_id in
        <foreach collection="pickTaskDIds" item="pickTaskDId" open="(" separator="," close=")">
            #{pickTaskDId}
        </foreach>
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID= #{warehouseId}
        </if>
        order by n.BILL_GROUP_ID,v.SRC_ITEM_PLACE_TYPE,v.SRC_WAREHOUSE_LOCATOR_NUM,m.ITEM_NAME,d.whole_num
    </select>

    <!-- 根据barcodedid查询补货标签 -->
    <select id="replenishLabelRepairPrint" resultType="map">
        select
            DISTINCT
            bd.id,
            bd.container_code,
            IFNULL(bd.print_cnt,0) print_cnt,
            m.ITEM_NAME SPMCH,
            m.ITEM_SPECS SHPGG,
            s.MANUFACTURER_LOT_NUM PH,
            m.MANUFACTURER SHENGCCJ,
            d.whole_num NR,
            s.PRODUCTION_DATE_SHOW P_DATE_S,
            s.EXPIRED_DATE_SHOW E_DATE_S,
            s.WAREHOUSE_AREA_CODE area,s.uom||'/件' DW,
            e.OPERATOR_NAME PY_JHY,
            v.SRC_WAREHOUSE_LOCATOR_NUM XSHW,
            d.pick_task_d_id BQ,
            '['||n.TOTAL_PCS||']' DD_JS,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end JLGG,
            obh.BILLTYPE CKLX,
            n.BILL_GROUP_ID DJBHQ,
            obh.LINE_NAME PSFX,
            obh.TOT_PCS ZJS,
            obh.CONSIGNORNAME YZMCH,
            obh.SHIPPERNAME DWMCH,
            obh.CUST_PERSON LXR,
            obh.RECEIVE_ADDRESS DW_DZ,
            case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end THFS,
            '正常' KHBZ,
            v.DEST_WAREHOUSE_LOCATOR_NUM KB,
            m.ITEM_CODE SPBH,
            m.item_id itemId,
            s.FLOOR_NUM HWLC,
            e.OPERATOR_NAME CZR,
            v.MOVE_TYPE MOVETYPE,
            h.pick_order_id pickOrderId,
            d.print_count PRINTCOUNT,
            e.PRINT_STATUS PRINTSTATUS,
            v.SRC_ITEM_PLACE_TYPE,
            v.gmt_create,
            obh.SALESPERSON_NAME YWY,
            obh.CREATEBY KPY,
            obh.PAYMENTDAYTYPECODE ZQ,
            obh.CUST_PERSON_TEL LXFS,
            obh.SALE_BRANCHNAME BM,
            d.QTY Z_QTY
        FROM  wms_barcode_d bd
        LEFT JOIN wms_pick_task_d d ON bd.wms_pick_task_d_id = d.pick_task_d_id
        LEFT JOIN wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        LEFT JOIN wms_pick_order e on e.pick_order_id = h.pick_order_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_MOVE_TASK v on v.BILL_D_ID = m.id
        LEFT JOIN WMS_WAVE_BILL n on m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where h.ENTERPRISE_ID=#{enterpriseId}
        AND h.type = 1
        AND bd.id in
        <foreach collection="barcodeDId" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID= #{warehouseId}
        </if>
        order by n.BILL_GROUP_ID,v.SRC_ITEM_PLACE_TYPE,v.SRC_WAREHOUSE_LOCATOR_NUM,m.ITEM_NAME,d.whole_num
    </select>

    <!--查询暂存区范围-->
    <select id="getStage" resultType="java.lang.String">
        select min(stage_code),max(stage_code) from WMS_WORK_STAGE_RUNTIME where ENTERPRISE_ID = #{enterpriseId} and BILL_GROUP_ID = #{billGroupId}
        group by BILL_GROUP_ID
    </select>

    <!--查询整件任务件数-->
    <select id="getWholePcs" resultType="java.lang.Integer">
        select sum(d.qty)/case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end pcs
        from wms_pick_task_h h
        left join wms_pick_task_d d on d.pick_task_id = h.pick_task_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
        where h.ENTERPRISE_ID = #{enterpriseId} and h.pick_task_id = #{pickTaskId}
        group by h.pick_task_id,m.CASE_CONVERSION_RATE
    </select>

    <!--    ===================================         PDA使用sql：     ====================================================-->

    <!--    根据订单编码查询所有复核任务-->
    <select id="outerCheckUnCheckApp" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        select f.id barcodeId,d.id barcode,f.type,n.src_code orderNo,f.warehouse_id warehouseId,d.check_status checkStatus,n.shipper_name shipperName,f.lcl_no lclNo,
        obh.CONSIGNORNAME consignorName,obh.SALESPERSON_NAME salespersonName,obh.SALE_BRANCHNAME saleBranchName,obh.PAYMENTDAYTYPECODE paymentType,obh.remark remark,
        n.type billType,n.SHIPPER_ID shipperId,n.SHIP_LINE_ID shipLineId,n.CONSIGNOR_ID consignorId,d.id barcodeDId
        from WMS_BARCODE_D d
        LEFT JOIN WMS_BARCODE_H f on f.id = d.barcode_id
        inner join WMS_WAVE_BILL n on f.bill_group_id = n.BILL_GROUP_ID
        inner JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where f.bill_group_id = #{orderNo} and n.ENTERPRISE_ID=#{enterpriseId} and f.warehouse_id = #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        order by d.check_status,d.id
    </select>


  <!--  <select id="getUnOuterCheckHApp" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        select * from (select min(v.status) status,f.barcode,f.bill_group_id orderNo,n.shipper_name shipperName,
        case when f.type='1' then 0 when f.type='0' then max(f.lcl_no) end as lcl,
        case when f.type='1' then sum(g.qty) when f.type='0' then 0 end as pcs
        from WMS_BARCODE_H f
        left join WMS_WAVE_BILL n on f.bill_group_id = n.BILL_GROUP_ID
        left join WMS_BARCODE_D g on f.id=g.BARCODE_ID
        left join WMS_MOVE_TASK v on v.bill_id = n.id
        where f.check_status='0'and n.ENTERPRISE_ID=#{enterpriseId} and f.WAREHOUSE_ID=#{warehouseId}
        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')) and WAREHOUSE_ID=#{warehouseId}
                UNION
                SELECT BILL_GROUP_ID FROM WMS_PICK_TASK_H wpth WHERE PICK_TASK_TYPE in (1,2,3) AND STATUS &lt;&gt; '10' and WAREHOUSE_ID=#{warehouseId}
            ) c
        )
        and (n.receive_way is null or n.receive_way != '自提')
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        GROUP BY f.barcode,f.bill_group_id,n.shipper_name,f.type
        ) a where a.status is null or a.status='2'
        and a.orderNo not in (select DISTINCT f.bill_group_id from WMS_BARCODE_H f where f.check_status is null and f.WAREHOUSE_ID=#{warehouseId})
        <if test="barcode != null and barcode != ''">
            and a.barcode like concat(concat('%',#{barcode}),'%')
        </if>
    </select>-->

    <select id="getUnOuterCheckHApp" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        select
        f.barcode,f.bill_group_id orderNo,n.shipper_name shipperName,
        case when f.type='1' then 0 when f.type='0' then max(f.lcl_no) end as lcl,
        case when f.type='1' then sum(g.qty) when f.type='0' then 0 end as pcs
        from WMS_BARCODE_H f
        left join WMS_WAVE_BILL n on f.bill_group_id = n.BILL_GROUP_ID
        left join WMS_BARCODE_D g on f.id=g.BARCODE_ID
        where f.check_status='0'and n.ENTERPRISE_ID=#{enterpriseId} and f.WAREHOUSE_ID=#{warehouseId}
        and (n.receive_way is null or n.receive_way != '自提')
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        <if test="barcode != null and barcode != ''">
            and f.barcode like concat(concat('%',#{barcode}),'%')
        </if>
        GROUP BY f.barcode,f.bill_group_id,n.shipper_name,f.type
    </select>

    <select id="askByBarcodeApp" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        select f.barcode,f.bill_group_id orderNo,n.shipper_name shipperName,f.status printStatus,f.type,max(f.check_status) checkStatus,f.checker_no checkerNo,
        n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,n.type billType,obh.CONSIGNORNAME consignorName,n.BILL_GROUP_ID srcCode,
        case when f.type='1' then 0 when f.type='0' then max(f.lcl_no) end as lcl,
        case when f.type='1' then sum(g.qty) when f.type='0' then 0 end as pcs
        from WMS_BARCODE_H f
        left join WMS_WAVE_BILL n on f.bill_group_id = n.BILL_GROUP_ID
        left join WMS_BARCODE_D g on f.id=g.BARCODE_ID
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where f.barcode = #{barcode} and f.WAREHOUSE_ID=#{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        GROUP BY f.barcode,f.bill_group_id,n.shipper_name,f.status,f.type,
        f.checker_no,n.SHIPPER_ID,n.CONSIGNOR_ID,n.type,obh.CONSIGNORNAME,n.BILL_GROUP_ID
    </select>

    <select id="getItemByBarcode" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        select DISTINCT m.item_id itemId,m.item_code itemCode,m.item_name itemName,m.manufacturer,m.item_specs itemSpecs,m.CASE_CONVERSION_RATE conversionRate,
        s.uom,s.manufacturer_lot_num manufacturerLotNum,obh.CONSIGNORNAME consignorName,g.ID barcodeDId,g.BARCODE_ID barcodeId,n.BILL_GROUP_ID srcCode
        from wms_pick_task_d d
        left join WMS_BARCODE_D g on g.WMS_PICK_TASK_D_ID = d.PICK_TASK_D_ID
        left join WMS_BARCODE_H f on f.id=g.BARCODE_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_WAVE_BILL n on m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where f.WAREHOUSE_ID=#{warehouseId} and s.type=0
        <if test="barcodeId != null and barcodeId != ''">
            and f.id = #{barcodeId}
        </if>
        <if test="barcode != null and barcode != ''">
            and f.barcode = #{barcode}
        </if>
    </select>

    <select id="getTotCount" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        SELECT type,max(lcl) lcl,sum(pcs) pcs from(
        select a.type
        ,case when a.type='0' then max(a.lcl_no) end as lcl
        ,case when a.type='1' then sum(a.qty)/a.CASE_CONVERSION_RATE end as pcs
        FROM
        (select DISTINCT g.qty,f.type,f.lcl_no,m.CASE_CONVERSION_RATE,f.id,n.BILL_GROUP_ID,m.BILL_ID,m.ITEM_CODE
        from WMS_BARCODE_H f
        LEFT JOIN WMS_BARCODE_D g on f.id=g.BARCODE_ID
        LEFT JOIN WMS_WAVE_BILL n ON n.BILL_GROUP_ID = f.BILL_GROUP_ID
        LEFT JOIN WMS_WAVE_BILL_D m ON m.BILL_ID = n.id
        where m.ITEM_ID = g.ITEM_ID
        <if test="barcode != null and barcode != ''">
            and f.BILL_GROUP_ID = #{billGroupId}
        </if>
        <if test="barcode != null and barcode != ''">
            and f.BILL_GROUP_ID =
            (
            SELECT DISTINCT BILL_GROUP_ID from  WMS_BARCODE_H where BARCODE =#{barcode}
            )
        </if>
        and f.warehouse_id = #{warehouseId}
        <if test="checkStatus != null and checkStatus != ''">
            and f.check_status = #{checkStatus}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and f.bldg_id = #{bldgId}
        </if>
        ) a
        GROUP BY a.type,a.CASE_CONVERSION_RATE) b
        GROUP BY type
    </select>

    <!--    零货/功能库房-拣货明细列表-->
    <select id="getPickTaskDList" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT
            a.*,floor(a.qty/a.conversionRate) uomQty,
            mod(a.qty,a.conversionRate) oddPieceQty
        FROM (
            select sum(d.QTY-coalesce(d.SALE_RED_QTY,0)) qty,
                m.ITEM_ID itemId,
                m.ITEM_NAME itemName,
                m.ITEM_CODE itemCode,
                m.manufacturer,
                m.item_specs itemSpecs,
                case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
                h.WAREHOUSE_ID warehouseId,
                s.manufacturer_lot_num manufacturerLotNum,
                s.uom,
                h.container_code containerCode,
                h.STATION_CODE stationCode,
                ifnull(s.PRODUCTION_DATE_SHOW,date_format(s.PRODUCTION_DATE,'%Y%m%d')) productionDateShow,
                ifnull(s.EXPIRED_DATE_SHOW,date_format(s.EXPIRED_DATE,'%Y%m%d')) expiredDateShow,
                s.WAREHOUSE_AREA_CODE warehouseAreaCode,
                s.LOCATOR_CODE locatorCode,
                n.shipper_name shipperName,
                n.shipper_code shipperCode,
                h.pick_order_id pickOrderId,
                n.SHIPPER_ID shipperId,
                n.CONSIGNOR_ID consignorId,
                obh.CONSIGNORNAME consignorName,
                obh.billid billId,
                obh.billno billNo,
                n.BILL_GROUP_ID srcCode,
                n.type billType,
                obh.REMARK
            FROM wms_pick_task_d d
            LEFT JOIN wms_pick_task_h h on h.pick_task_id = d.pick_task_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_wave_bill n on m.bill_id = n.id
            LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
            LEFT JOIN wms_task_out_back wto ON wto.msg_id = d.ob_msg_id
            LEFT JOIN wms_task_out ou ON (ou.seqno = d.pick_task_d_id OR ou.seqno = d.src_pick_task_d_id)
            WHERE h.pick_order_id = #{pickOrderId}
        AND (ou.msg_id IS NULL OR (ou.msg_id IS NOT NULL AND wto.msg_id IS NOT NULL AND wto.outtype = 1))
            <!-- and h.status='1' and d.pick_time is null-->
            <if test="bldgId != null and bldgId != ''">
                AND h.bldg_id = #{bldgId}
            </if>
            <if test="warehouseId != null and warehouseId != ''">
                AND h.WAREHOUSE_ID = #{warehouseId}
            </if>
            <if test="pickTaskType != null and pickTaskType !='' ">
                AND h.PICK_TASK_TYPE = #{pickTaskType}
            </if>
            GROUP BY m.ITEM_ID,m.ITEM_NAME,m.ITEM_CODE,m.manufacturer,m.item_specs,
            m.CASE_CONVERSION_RATE,s.manufacturer_lot_num,s.uom,s.PRODUCTION_DATE_SHOW,s.EXPIRED_DATE_SHOW,
            s.WAREHOUSE_AREA_CODE,s.LOCATOR_CODE,n.shipper_name,h.pick_order_id,n.SHIPPER_ID,
            n.CONSIGNOR_ID,obh.CONSIGNORNAME,n.BILL_GROUP_ID,n.type,h.WAREHOUSE_ID,s.PRODUCTION_DATE,s.EXPIRED_DATE,h.container_code,h.STATION_CODE,obh.REMARK
        ) a
        ORDER BY a.locatorCode,a.itemId
    </select>

    <!--    零货/功能库房/整单拣货-拣货明细列表-->
    <select id="getPickTaskDList2" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT
            a.*,floor(a.qty/a.conversionRate) uomQty,
            mod(a.qty,a.conversionRate) oddPieceQty,a.qty as pieceQty
            from (select group_concat(PICK_TASK_D_ID) pickTaskDIds,sum(d.QTY-coalesce(d.SALE_RED_QTY, 0)) qty,
            m.ITEM_ID itemId,
            m.ITEM_NAME itemName,
            m.ITEM_CODE itemCode,
            m.manufacturer,
            m.item_specs itemSpecs,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
            h.WAREHOUSE_ID warehouseId,
            s.manufacturer_lot_num manufacturerLotNum,
            s.uom,
            h.STATION_CODE stationCode,
            h.type,
            ifnull(s.PRODUCTION_DATE_SHOW,date_format(s.PRODUCTION_DATE,'%Y%m%d')) productionDateShow,
            ifnull(s.EXPIRED_DATE_SHOW,date_format(s.EXPIRED_DATE,'%Y%m%d')) expiredDateShow,
            s.WAREHOUSE_AREA_CODE warehouseAreaCode,
            s.LOCATOR_CODE locatorCode,
            h.pick_order_id pickOrderId,
            max(obh.CREATEBY) createby,
            max(n.shipper_name) shipperName,
            max(n.shipper_id) shipperId,
            max(obh.ref_billid) refBillId,
            n.CONSIGNOR_ID consignorId,
            max(obh.CONSIGNORNAME) consignorName,
            max(n.BILL_GROUP_ID) srcCode,
            max(obh.remark) remark,
            max(obh.SALESPERSON_NAME) salespersonName,
            max(obh.PAYMENTDAYTYPECODE) paymentType,
            max(obh.BILLTYPE) billType
        from wms_pick_task_d d
        left join wms_pick_task_h h on h.pick_task_id = d.pick_task_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_wave_bill n on m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        LEFT JOIN wms_task_out_back wto ON wto.msg_id = d.ob_msg_id
        LEFT JOIN wms_task_out ou ON (ou.seqno = d.pick_task_d_id OR ou.seqno = d.src_pick_task_d_id)
        where h.WAREHOUSE_ID = #{warehouseId} and h.pick_order_id = #{pickOrderId}
        AND (ou.msg_id IS NULL OR (ou.msg_id IS NOT NULL AND wto.msg_id IS NOT NULL AND wto.outtype = 1))
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="pickTaskType == null or pickTaskType =='' ">
            and d.pick_time is null
        </if>
        <if test="pickTaskType != null and pickTaskType != '' and pickTaskType =='3'.toString() ">
            and d.FLOW_STATUS &lt; 3
        </if>
        group by m.ITEM_ID,m.ITEM_NAME,m.ITEM_CODE,m.manufacturer,m.item_specs,
        m.CASE_CONVERSION_RATE,s.manufacturer_lot_num,s.uom,s.PRODUCTION_DATE_SHOW,s.EXPIRED_DATE_SHOW,
        s.WAREHOUSE_AREA_CODE,s.LOCATOR_CODE,h.pick_order_id,
        n.CONSIGNOR_ID,h.WAREHOUSE_ID,s.PRODUCTION_DATE,s.EXPIRED_DATE,h.STATION_CODE,h.type) a
        order by a.locatorCode,a.itemId
    </select>

    <!--    整件-拣货任务列表-->
    <select id="getTaskHListByPickOrder" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT
            a.*,floor(a.qty/a.conversionRate) uomQty,
            mod(a.qty,a.conversionRate) oddPieceQty,
            a.qty as pieceQty
        from (
            select group_concat(d.PICK_TASK_D_ID) pickTaskDIds,
            sum(d.qty-coalesce(d.SALE_RED_QTY,0)) qty,
                h.PICK_TASK_ID pickTaskId,
                m.ITEM_ID itemId,
                m.ITEM_NAME itemName,
                m.ITEM_CODE itemCode,
                m.manufacturer,
                m.item_specs itemSpecs,
                d.PICK_TASK_D_ID pickTaskDId,
                d.container_code containerCode,
                case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
                h.WAREHOUSE_ID warehouseId,
                s.manufacturer_lot_num manufacturerLotNum,
                s.uom,
                ifnull(s.PRODUCTION_DATE_SHOW,date_format(s.PRODUCTION_DATE,'%Y%m%d')) productionDateShow,
                ifnull(s.EXPIRED_DATE_SHOW,date_format(s.EXPIRED_DATE,'%Y%m%d')) expiredDateShow,
                s.WAREHOUSE_AREA_CODE warehouseAreaCode,
                s.area_category areaCategory,
                s.LOCATOR_CODE locatorCode,
                s.batch_num batchNum,
                n.shipper_name shipperName,
                h.pick_order_id pickOrderId,
                n.SHIPPER_ID shipperId,
                n.CONSIGNOR_ID consignorId,
                obh.CONSIGNORNAME consignorName,
                n.BILL_GROUP_ID srcCode,
                obh.BILLTYPE billType,
                obh.REMARK,
                obh.PAYMENTDAYTYPECODE paymentType,
                n.RECEIVE_ADDRESS receiveAddress,
                obh.SALESPERSON_NAME salespersonName,
                obh.CREATEBY,
                h.type
            from wms_pick_task_d d
            left join wms_pick_task_h h on h.pick_task_id = d.pick_task_id
            LEFT JOIN wms_pick_order e ON h.pick_order_id = e.pick_order_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_wave_bill n on m.bill_id = n.id
            LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
            where h.WAREHOUSE_ID = #{warehouseId} AND e.operator_code = #{operatorCode}
            <if test="pickOrderId != null and pickOrderId != ''">
                and h.pick_order_id = #{pickOrderId}
            </if>
              and h.status='1' and d.pick_time is null and h.PICK_TASK_TYPE = '0'
            <if test="bldgId != null and bldgId != ''">
                and h.bldg_id = #{bldgId}
            </if>
            group by
                m.ITEM_ID,
                m.ITEM_NAME,
                m.ITEM_CODE,
                m.manufacturer,
                m.item_specs,
                m.CASE_CONVERSION_RATE,
                s.manufacturer_lot_num,
                s.uom,
                s.WAREHOUSE_AREA_CODE,
                s.area_category,
                s.LOCATOR_CODE,
                s.batch_num,
                n.shipper_name,
                h.pick_order_id,
                h.PICK_TASK_ID,
                d.PICK_TASK_D_ID,
                d.container_code,
                n.SHIPPER_ID,
                n.CONSIGNOR_ID,
                obh.CONSIGNORNAME,
                n.BILL_GROUP_ID,
                obh.BILLTYPE,
                h.WAREHOUSE_ID,
                obh.REMARK,
                obh.PAYMENTDAYTYPECODE,
                n.RECEIVE_ADDRESS,
                obh.SALESPERSON_NAME,
                obh.CREATEBY,
                s.PRODUCTION_DATE,
                s.EXPIRED_DATE,
                s.PRODUCTION_DATE_SHOW,
                s.EXPIRED_DATE_SHOW,
                h.type
        ) a
    </select>

    <select id="getContainerList" resultType="com.prolog.cs.biz.outbound.model.dto.ContainerListDTO">
        SELECT a.*,floor(a.qty/a.conversionRate) uomQty,mod(a.qty,a.conversionRate) oddPieceQty from (
        select sum(d.qty) qty,
        case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
        h.pick_task_id pickTaskId,h.container_code containerCode
        ,m.ITEM_ID itemId,s.manufacturer_lot_num manufacturerLotNum,s.LOCATOR_CODE locatorCode,h.WAREHOUSE_ID warehouseId,max(n.shipper_name) shipperName,max(n.shipper_id) shipperId
        from wms_pick_task_d d
        left join wms_pick_task_h h on h.pick_task_id = d.pick_task_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_wave_bill n on m.bill_id = n.id
        where h.WAREHOUSE_ID = #{warehouseId} and h.pick_order_id = #{pickOrderId}
        <if test="itemId != null and itemId != ''">
            and m.ITEM_ID = #{itemId}
        </if>
        <if test="locatorCode != null and locatorCode != ''">
            and s.LOCATOR_CODE = #{locatorCode}
        </if>
        <if test="manufacturerLotNum != null and manufacturerLotNum != ''">
            and s.manufacturer_lot_num = #{manufacturerLotNum}
        </if>
        group by m.ITEM_ID,s.manufacturer_lot_num,s.LOCATOR_CODE,h.container_code,m.CASE_CONVERSION_RATE,h.pick_task_id,h.WAREHOUSE_ID) a
    </select>

    <select id="getPickTaskDId" resultType="com.prolog.cs.biz.outbound.model.dto.InvStockOutDTO">
        select DISTINCT d.pick_task_d_id pickTaskDId,d.WAREHOUSE_ID warehouseId,h.pick_task_id pickTaskId,d.qty,d.WAVE_STORAGE_ID waveStorageId
        from wms_pick_task_d d
        left join wms_pick_task_h h on h.pick_task_id = d.pick_task_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
        where h.pick_task_id = #{pickTaskId} and h.WAREHOUSE_ID = #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="itemId != null and itemId != ''">
            and m.ITEM_ID = #{itemId}
        </if>
        <if test="locatorCode != null and locatorCode != ''">
            and s.LOCATOR_CODE = #{locatorCode}
        </if>
        <if test="manufacturerLotNum != null and manufacturerLotNum != ''">
            and s.manufacturer_lot_num = #{manufacturerLotNum}
        </if>
    </select>

    <select id="getUnAskPickCount" resultType="java.lang.Long">
        select count(DISTINCT wpth.pick_task_id)
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        where WPTH.WAREHOUSE_ID= #{warehouseId} and wpth.status = '0'
        <choose>
            <when test="pickTaskType== null">
                and wpth.PICK_TASK_TYPE in ('0','3')
            </when>
            <otherwise>
                and wpth.PICK_TASK_TYPE = #{pickTaskType}
            </otherwise>
        </choose>
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="type != null">
            and wpth.type = #{type}
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
    </select>

    <select id="getRedCount" resultType="java.lang.Integer">
        SELECT count(*) count from OMS_DO_BILL_D a,
        (SELECT SRC_ID,SRC_LINE_ID,sum(QTY) qty from WMS_SALE_RED_RECORD a where ENTERPRISE_ID=#{enterpriseId}
        and SRC_ID=#{srcId} and SRC_LINE_ID=#{srcLineId}
        GROUP BY SRC_ID,SRC_LINE_ID) b where a.LINEID = b.SRC_LINE_ID
        and a.BILLID=#{srcId} and a.P_ENTERPRISEID=#{enterpriseId} and a.ITEM_QTY != ifnull(b.qty,0)
        UNION ALL
        SELECT count(*) count from OMS_DO_BILL_D a
        LEFT JOIN
        (SELECT SRC_ID,SRC_LINE_ID,sum(QTY) qty from WMS_SALE_RED_RECORD a where ENTERPRISE_ID=#{enterpriseId}
        and SRC_ID=#{srcId}
        GROUP BY SRC_ID,SRC_LINE_ID) b on a.LINEID = b.SRC_LINE_ID
        where a.BILLID=#{srcId} and a.P_ENTERPRISEID=#{enterpriseId} and a.ITEM_QTY != ifnull(b.qty,0) and a.LINEID !=#{srcLineId}
    </select>

    <update id="updateFlowStatus">
        update WMS_PICK_TASK_D set FLOW_STATUS =#{flowStatus} where PICK_TASK_D_ID in (select PICK_TASK_D_ID from (
            select d.PICK_TASK_D_ID from
            wms_pick_task_d d,wms_pick_task_h h where d.pick_task_id=h.pick_task_id
            and h.BILL_GROUP_ID = #{billGroupId} and h.warehouse_id = #{warehouseId}
        ) a )
    </update>

    <update id="updateFlowStatusApp">
        update WMS_PICK_TASK_D set FLOW_STATUS =#{flowStatus} where (PICK_TASK_D_ID in(
            select DISTINCT d.WMS_PICK_TASK_D_ID from
            WMS_BARCODE_D d,WMS_BARCODE_H h where d.BARCODE_ID=h.id
            and d.id = #{barcode} and h.warehouse_id = #{warehouseId}
        ) OR barcode_d_id = #{barcode})
    </update>

    <!--代管/待检出库单 明细查询-->
    <select id="getSpecialBillDList" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT a.*,floor(a.qty/a.conversionRate) uomQty,mod(a.qty,a.conversionRate) oddPieceQty from (select sum(s.QTY) qty,max(s.id) id,
        m.ITEM_ID itemId,m.ITEM_NAME itemName,m.ITEM_CODE itemCode,m.manufacturer,m.item_specs itemSpecs,
        case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
        s.manufacturer_lot_num manufacturerLotNum,s.uom,
        ifnull(s.PRODUCTION_DATE_SHOW,date_format(s.PRODUCTION_DATE,'%Y%m%d')) productionDateShow,ifnull(s.EXPIRED_DATE_SHOW,date_format(s.EXPIRED_DATE,'%Y%m%d')) expiredDateShow,
        s.WAREHOUSE_AREA_CODE warehouseAreaCode,s.LOCATOR_CODE locatorCode,n.shipper_name shipperName,
        n.SHIPPER_ID shipperId,n.CONSIGNOR_ID consignorId,obh.CONSIGNORNAME consignorName,n.BILL_GROUP_ID srcCode,n.type billType,obh.REMARK
        from wms_wave_storage s
        left join WMS_WAVE_BILL_D m on m.ID = s.BILL_D_ID
        left join wms_wave_bill n on m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where n.WAREHOUSE_ID = #{warehouseId} and n.src_code= #{srcCode}
        group by m.ITEM_ID,m.ITEM_NAME,m.ITEM_CODE,m.manufacturer,m.item_specs,
        m.CASE_CONVERSION_RATE,s.manufacturer_lot_num,s.uom,s.PRODUCTION_DATE_SHOW,s.EXPIRED_DATE_SHOW,
        s.WAREHOUSE_AREA_CODE,s.LOCATOR_CODE,n.shipper_name,n.SHIPPER_ID,
        n.CONSIGNOR_ID,obh.CONSIGNORNAME,n.BILL_GROUP_ID,n.type,s.PRODUCTION_DATE,s.EXPIRED_DATE,obh.REMARK) a
        order by a.locatorCode,a.itemId
    </select>

    <select id="getNotFinishCount" resultType="java.lang.Integer">
        SELECT count(*) from WMS_PICK_TASK_H h
        left join WMS_PICK_ORDER e on e.pick_order_id = h.pick_order_id
        left join wms_pick_task_d d on d.pick_task_id = h.pick_task_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_MOVE_TASK v on v.BILL_D_ID = m.id
        where v.STATUS &lt;&gt; '2'
        and h.PICK_TASK_ID = (
            SELECT PICK_TASK_ID from (
            SELECT h.PICK_TASK_ID from WMS_PICK_TASK_H h
            left join WMS_PICK_ORDER e on e.pick_order_id = h.pick_order_id
            left join wms_pick_task_d d on d.pick_task_id = h.pick_task_id
            where h.WAREHOUSE_ID = #{warehouseId} and e.OPERATOR_CODE = #{operatorCode} and h.type='1' and date(e.GMT_CREATE)=curdate()
            ORDER BY e.GMT_CREATE desc
            ) a limit 1
        )
    </select>

    <select id="getAgvPickTaskH" resultType="map">
        select DISTINCT date_format(NOW(),'%Y-%m-%d %H:%i:%S') RQ,h.BILL_GROUP_ID DJBH,obh.SHIPPERID DWID,obh.SALESPERSON_NAME RY_KPY, obh.ARRIVAL_MODE THFS,
        obh.ENQUIRYDEPARTMENT BMID,ifnull(n.PRIORITY,0) CKYXJ,obh.BILL_DATE KPSJ,n.WAVE_ID BCH,obh.remark KHBZ,'0' DDLX,obh.CONSIGNORID YZID
        from wms_pick_task_h h
        left join WMS_WAVE_BILL n on h.BILL_GROUP_ID = n.BILL_GROUP_ID
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        where h.WAREHOUSE_ID=#{warehouseId} and h.pick_task_id = #{pickTaskId}
    </select>

    <select id="getDevicePickTaskH" resultType="com.prolog.eis.core.dto.business.outboundtask.OutboundTaskIssueDto">
        select DISTINCT
        STR_TO_DATE('2099-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') expireDate,
        h.pick_task_id upperSystemTaskId,obh.BILLTYPE outboundTaskTypeNo,ifnull(n.PRIORITY,0) priority,h.WAREHOUSE_ID warehouseId,
        h.enterprise_id enterpriseId,n.CONSIGNOR_ID cargoOwnerId
        from wms_pick_task_h h
        left join WMS_WAVE_BILL n on h.BILL_GROUP_ID = n.BILL_GROUP_ID
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        where h.WAREHOUSE_ID=#{warehouseId} and h.pick_task_id = #{pickTaskId}
    </select>

    <select id="getAgvPickTaskHList" resultType="map">
        select DISTINCT date_format(NOW(),'%Y-%m-%d %H:%i:%S') RQ,h.BILL_GROUP_ID DJBH,obh.SHIPPERID DWID,obh.SALESPERSON_NAME RY_KPY, obh.ARRIVAL_MODE THFS,
        obh.ENQUIRYDEPARTMENT BMID,ifnull(n.PRIORITY,0) CKYXJ,obh.BILL_DATE KPSJ,n.WAVE_ID BCH,obh.remark KHBZ,'0' DDLX,obh.CONSIGNORID YZID,h.PICK_TASK_ID
        from wms_pick_task_h h
        left join WMS_WAVE_BILL n on h.BILL_GROUP_ID = n.BILL_GROUP_ID
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        where h.WAREHOUSE_ID=#{warehouseId} and h.pick_task_id in
        <foreach collection="pickTaskIds" item="pickTaskId" open="(" separator="," close=")">
            #{pickTaskId}
        </foreach>
    </select>

    <select id="getAgvPickTaskD" resultType="map">
        select DISTINCT @rownum:=@rownum+1 DJ_SORT,h.BILL_GROUP_ID DJBH,m.ITEM_ID SPID,d.qty SL, 0 JS,0 LSS,d.qty SL_JH,d.qty SL_LHWCL,s.manufacturer_lot_num PH,obh.CONSIGNORID YZID
        ,obh.remark MX_BZ,d.PICK_TASK_D_ID ROWNO_BILL
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_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_WAVE_BILL n on m.bill_id = n.id
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        join (SELECT @rownum:=0) t
        where h.WAREHOUSE_ID=#{warehouseId} and h.pick_task_id=#{pickTaskId}
    </select>

    <select id="getDevicePickTaskD" resultType="com.prolog.eis.core.dto.business.outboundtask.OutboundTaskDetailIssueDto">
        select DISTINCT
        @rownum:=@rownum+1 num,s.batch_id lotId,m.ITEM_ID itemId,d.qty planNum,d.PICK_TASK_D_ID
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_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_WAVE_BILL n on m.bill_id = n.id
        left join OMS_DO_BILL_H obh on obh.BILLID = n.src_id
        join (SELECT @rownum:=0) t
        where h.WAREHOUSE_ID=#{warehouseId} and h.pick_task_id=#{pickTaskId}
    </select>

    <select id="getMoveStrogeCount" resultType="java.lang.Integer">
        SELECT count(*) from WMS_WAVE_STORAGE s,WMS_WAVE_BILL m where s.BILL_ID=m.id and m.BILL_GROUP_ID = #{billGroupId} and s.WAREHOUSE_ID= #{warehouseId} and s.type =1 and s.pick_model='Device'
    </select>

    <select id="getUnPickTaskIds" resultType="java.lang.String">
        SELECT distinct h.pick_task_id from wms_pick_task_h h
        left join wms_pick_task_d d on d.pick_task_id = h.pick_task_id
        left join wms_wave_storage s on d.WAVE_STORAGE_ID = s.id
        where h.bill_group_id in (
            SELECT DISTINCT h.bill_group_id from wms_pick_task_h h
            left join wms_pick_task_d d on d.pick_task_id = h.pick_task_id
            left join wms_wave_storage s on d.WAVE_STORAGE_ID = s.id
            where s.item_id = #{itemId} and s.batch_num = #{batchNum} and h.STATUS = '1' and h.pick_task_type='1' and h.type ='0' and s.pick_model ='Device' and h.warehouse_id = #{warehouseId}
        ) and h.bill_group_id not in(
            SELECT DISTINCT h.bill_group_id from wms_pick_task_h h
            left join wms_pick_task_d d on d.pick_task_id = h.pick_task_id
            left join wms_wave_storage s on d.WAVE_STORAGE_ID = s.id
            left join wms_wave_bill_d m on s.BILL_D_ID = m.id
            left join WMS_MOVE_TASK v on v.BILL_D_ID = m.id
            where s.pick_model ='Device' and h.STATUS = '1' and h.pick_task_type='1' and h.type ='0' and s.batch_num &lt;&gt; #{batchNum}
            and s.item_id in (
                SELECT v.item_id from WMS_MOVE_TASK v
                left join WMS_WAVE_BILL_D m on v.BILL_D_ID = m.id
                left join wms_wave_storage s on m.ID = s.BILL_D_ID
                where v.item_id &lt;&gt; #{itemId} and v.batch_num &lt;&gt; #{batchNum} and v.status &lt;&gt; '2' and s.pick_model ='Device' and v.warehouse_id = #{warehouseId}
            )
        )
        and h.STATUS = '1' and h.pick_task_type='1' and h.type ='0' and s.pick_model='Device' and h.warehouse_id = #{warehouseId}
    </select>

    <select id="getMoveItemCount" resultType="java.lang.Integer">
        SELECT COUNT(*) from WMS_MOVE_TASK a,(
            SELECT distinct s.item_id,s.batch_num from WMS_WAVE_STORAGE s,WMS_WAVE_BILL m where
            s.BILL_ID=m.id and m.BILL_GROUP_ID = #{billGroupId} and s.type =0 and s.WAREHOUSE_ID = #{warehouseId} and s.pick_model='Device'
        ) b where a.item_id = b.item_id and a.batch_num = b.batch_num
        and a.status &lt; 2 and a.WAREHOUSE_ID = #{warehouseId}
    </select>

    <select id="getAgvMoveStorageList" resultMap="storageMap">
        select distinct a.*,b.CASE_CONVERSION_RATE,c.wave_id,b.bill_id,b.manufacturer_lot_num,c.BILL_GROUP_ID,c.CONSIGNOR_ID
        from wms_wave_storage a
        left join wms_wave_bill_d b on a.BILL_D_ID = b.id
        left join wms_wave_bill c on b.bill_id = c.id
        left join WMS_PICK_TASK_D d on d.WAVE_STORAGE_ID = a.id
        where a.WAREHOUSE_ID = #{warehouseId}
        and d.PICK_TASK_D_ID in
        <foreach collection="pickTaskDIds" item="pickTaskDId" open="(" separator="," close=")">
            #{pickTaskDId}
        </foreach>
    </select>

    <select id="getLastMoveBillId" resultType="java.lang.String">
        SELECT a.bill_group_id from (
           SELECT h.bill_group_id from WMS_PICK_TASK_H h
        left join WMS_PICK_ORDER e on e.pick_order_id = h.pick_order_id
        left join wms_pick_task_d d on d.pick_task_id = h.pick_task_id
            where h.WAREHOUSE_ID = #{warehouseId} and e.OPERATOR_CODE = #{operatorCode} and h.type='1' and date(e.GMT_CREATE)=date(now())
              and e.status >0
            ORDER BY e.GMT_CREATE desc
        ) a limit 1
    </select>


    <select id="getPickTaskHListByBill3" resultMap="pickTaskHMap">
        select h.*
        from WMS_PICK_TASK_H h
        where h.STATUS =0 and h.TYPE=0 and h.BILL_GROUP_ID = #{billGroupId}
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID=#{warehouseId}
        </if>
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and h.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
    </select>

    <select id="getItemByPickTaskDId" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        select DISTINCT m.item_id itemId,m.item_code itemCode,m.item_name itemName,m.manufacturer,m.item_specs itemSpecs,m.CASE_CONVERSION_RATE conversionRate,
                        s.uom,s.manufacturer_lot_num manufacturerLotNum,
                        CASE WHEN d.check_qty != 0 THEN d.check_qty
                        WHEN d.pick_qty !=0 THEN d.pick_qty
                        ELSE d.qty END AS pcs
        from WMS_WAVE_BILL_D m
                 inner join wms_wave_storage s on m.ID = s.BILL_D_ID
                 inner join wms_pick_task_d d on d.WAVE_STORAGE_ID = s.id
        where d.PICK_TASK_D_ID = #{pickTaskDId} and s.WAREHOUSE_ID=#{warehouseId} and s.type=0
    </select>

    <select id="getItemByBarcodeDId" resultType="com.prolog.cs.biz.outbound.model.dto.OuterTaskDDTO">
        select DISTINCT m.item_id itemId,m.item_code itemCode,m.item_name itemName,m.manufacturer,m.item_specs itemSpecs,m.CASE_CONVERSION_RATE conversionRate,
                        s.uom,s.manufacturer_lot_num manufacturerLotNum,d.pick_task_d_id pickTaskDId,
                        COALESCE(d.sale_red_qty,0) saleRedQty,
                        CASE WHEN d.check_qty != 0 THEN d.check_qty
                        WHEN d.pick_qty !=0 THEN d.pick_qty
                        ELSE d.qty END AS pcs
        from WMS_WAVE_BILL_D m
                 inner join wms_wave_storage s on m.ID = s.BILL_D_ID
                 inner join wms_pick_task_d d on d.WAVE_STORAGE_ID = s.id
        where (d.barcode_d_id = #{barcodeDId} OR d.pick_task_d_id IN (SELECT wms_pick_task_d_id FROM wms_barcode_d WHERE id = #{barcodeDId})) and s.WAREHOUSE_ID=#{warehouseId} and s.type=0
    </select>

    <!--整件补货标签-->
    <select id="getMoveLabel" resultType="map">
        select
            m.ITEM_NAME SPMCH,
            m.ITEM_SPECS SHPGG,
            GROUP_CONCAT(DISTINCT s.MANUFACTURER_LOT_NUM SEPARATOR ' ') PH,
            m.MANUFACTURER SHENGCCJ,
            v.DEST_WAREHOUSE_LOCATOR_NUM SJHW,
            d.pick_task_id,
            SUM(d.QTY) Z_QTY,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end JLGG,
            m.ITEM_CODE SPBH
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_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_MOVE_TASK v on v.BILL_D_ID = m.id
        where h.ENTERPRISE_ID=#{enterpriseId} AND h.type = '1'
        and d.pick_task_id in
        <foreach collection="pickTaskIds" item="pickTaskId" open="(" separator="," close=")">
            #{pickTaskId}
        </foreach>
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID= #{warehouseId}
        </if>
        GROUP BY D.pick_task_id, SPMCH, SHPGG, SHENGCCJ, SJHW, JLGG, SPBH
        order by v.DEST_WAREHOUSE_LOCATOR_NUM,m.ITEM_NAME
    </select>
    <!--整件拣货标签-->
    <select id="getPickLabel" resultType="map">
        select DISTINCT
            floor(d.qty/case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end) DD_ZJS,
            m.item_id itemId,
            m.ITEM_NAME SPMCH,
            m.ITEM_SPECS SHPGG,
            s.MANUFACTURER_LOT_NUM PH,
            m.MANUFACTURER SHENGCCJ,
            d.whole_num NR,
            s.PRODUCTION_DATE_SHOW P_DATE_S,
            s.EXPIRED_DATE_SHOW E_DATE_S,
            s.WAREHOUSE_AREA_CODE AREA,
            s.uom||'/件' DW,
            e.OPERATOR_NAME PY_JHY,
            s.LOCATOR_CODE XSHW,
            d.pick_task_d_id BQ,
            n.TOTAL_PCS DD_JS,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end JLGG,
            obh.BILLTYPE CKLX,
            n.BILL_GROUP_ID DJBHQ,
            obh.LINE_NAME PSFX,
            obh.TOT_PCS ZJS,
            obh.CONSIGNORNAME YZMCH,
            obh.SHIPPERNAME DWMCH,
            obh.CUST_PERSON LXR,
            obh.RECEIVE_ADDRESS DW_DZ,
            case obh.ARRIVAL_MODE  when null then '配送' when 1 then '配送' when 2 then '配送' when 3 then '配送' end THFS,'正常' KHBZ,
            h.pick_order_id PICKORDERID,
            h.remark pickRemark,
            d.print_count PRINTCOUNT,
            e.PRINT_STATUS PRINTSTATUS,
            s.ITEM_PLACE_TYPE,
            obh.SALESPERSON_NAME YWY,
            obh.CREATEBY KPY,
            obh.PAYMENTDAYTYPECODE ZQ,
            obh.CUST_PERSON_TEL LXFS,
            obh.SALE_BRANCHNAME BM,
            obh.remark REMARK,
            d.QTY Z_QTY,
            d.CHECKER_NAME FHR1,
            d.CHECKER_NAME_2 FHR2,
            s.pick_model pickModel,
            m.ITEM_code	itemcode,
            h.container_code,
            h.bill_id billno,
            n.contact_name lxr,
            n.contact_tel lxfs,
            n.receive_address lxdz
        from wms_pick_task_d d
        left join wms_pick_task_h h on d.pick_task_id = h.pick_task_id
        left join wms_pick_order e on e.pick_order_id = h.pick_order_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_WAVE_BILL n on m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where h.ENTERPRISE_ID=#{enterpriseId}
        and d.pick_task_id in
        <foreach collection="pickTaskIds" item="pickTaskId" open="(" separator="," close=")">
            #{pickTaskId}
        </foreach>
        <if test="warehouseId != null and warehouseId != ''">
            and h.WAREHOUSE_ID= #{warehouseId}
        </if>
        order by n.BILL_GROUP_ID,s.ITEM_PLACE_TYPE,s.LOCATOR_CODE,s.MANUFACTURER_LOT_NUM,m.ITEM_NAME,length(d.whole_num),d.whole_num
    </select>

    <select id="getTaskCount" resultType="com.prolog.cs.biz.outbound.model.vo.TaskCountVo">
        select
            COUNT(*) count,
            wpth.WAREHOUSE_AREA_CODE areaCode
        from WMS_PICK_TASK_H wpth
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        and WPTH.status = '0' AND WPTH.type = 0 AND pick_task_type = '1'
        AND NOT EXISTS (SELECT pick_task_d_id FROM wms_pick_task_d d WHERE d.pick_task_id = wpth.pick_task_id AND (SELECT COUNT(*) FROM wms_task_out o WHERE d.pick_task_d_id = o.seqno) > 0 )
        GROUP BY WAREHOUSE_AREA_CODE
    </select>

    <select id="getMoveCount" resultType="com.prolog.cs.biz.outbound.model.vo.TaskCountVo">
        select COUNT(*) count,  wpth.WAREHOUSE_AREA_CODE areaCode
        from WMS_PICK_TASK_H wpth
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        and WPTH.status = '0' AND WPTH.type = 1 AND pick_task_type = '1'
        GROUP BY WAREHOUSE_AREA_CODE
    </select>

    <select id="getContainerListById" resultType="com.prolog.cs.biz.outbound.model.dto.ContainerListDTO">
        SELECT
            a.*,floor(a.qty/a.conversionRate) uomQty,mod(a.qty,a.conversionRate) oddPieceQty
        from (
            select sum(d.qty) qty,
                case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
                h.pick_task_id pickTaskId,h.container_code containerCode
                ,m.ITEM_ID itemId,s.manufacturer_lot_num manufacturerLotNum,s.LOCATOR_CODE locatorCode,h.WAREHOUSE_ID warehouseId,max(n.shipper_name) shipperName,max(n.shipper_id) shipperId
            from wms_pick_task_d d
            left join wms_pick_task_h h on h.pick_task_id = d.pick_task_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_wave_bill n on m.bill_id = n.id
            where h.WAREHOUSE_ID = #{warehouseId} and h.pick_order_id = #{pickOrderId} AND h.container_code = #{containerCode}
            <if test="itemId != null and itemId!=''">
                AND s.item_id = #{itemId}
            </if>
            <if test="locateId != null and locateId!=''">
                AND s.locator_id = #{locateId}
            </if>
            <if test="manufacturerLotNum != null and manufacturerLotNum!=''">
                AND s.manufacturer_lot_num = #{manufacturerLotNum}
            </if>
            group by m.ITEM_ID,s.manufacturer_lot_num,s.LOCATOR_CODE,h.container_code,m.CASE_CONVERSION_RATE,h.pick_task_id,h.WAREHOUSE_ID
        ) a
    </select>
    <select id="countHBySlidWayId" resultType="map">
        SELECT COUNT(*) count, slideway_id slideWayId FROM wms_pick_task_h
        WHERE slideway_id IN
        <foreach collection="list" index="index" item="item" separator="," close=")" open="(">
            #{item}
        </foreach>
        and status in('1','2','3') and station_id is not null and pick_task_type in(1,2)
        GROUP BY slideway_id ORDER BY count
    </select>

    <select id="getBillSlideways" resultMap="pickTaskHMap">
        SELECT DISTINCT * FROM wms_pick_task_h WHERE bill_id IN(SELECT bill_id FROM wms_pick_task_h WHERE pick_task_id = #{pickTaskId}) and status in('1','2','3') and station_id is not null and pick_task_type in(1,2)
    </select>
    <update id="updatePickTaskHSlideWayInfo">
        UPDATE wms_pick_task_h
        SET
            station_id = #{stationId},
            station_code = #{stationCode},
            station_name = #{stationName},
            slideway_id = #{slidewayId}
        WHERE pick_task_id = #{pickTaskId}
    </update>
    <select id="getByTaskDId" resultMap="pickTaskHMap">
        SELECT * FROM wms_pick_task_h
        WHERE pick_task_id IN(SELECT pick_task_id FROM wms_pick_task_d WHERE pick_task_d_id = #{pickTaskDId})
        AND container_code = #{containerCode}
        AND station_code = #{stationCode}
    </select>


    <select id="getPickTaskDList3" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT
            a.*,
            floor(a.qty/a.conversionRate) uomQty,
            mod(a.qty,a.conversionRate) oddPieceQty
        from
        (select
            d.PICK_TASK_D_ID,
            d.PICK_TASK_ID pickTaskId,
            d.qty,
            d.PICK_QTY pickQty,
            m.ITEM_ID itemId,
            m.ITEM_NAME itemName,
            m.ITEM_CODE itemCode,
            m.manufacturer,
            m.item_specs itemSpecs,
            case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
            h.WAREHOUSE_ID warehouseId,
            s.manufacturer_lot_num manufacturerLotNum,
            s.uom,
            h.STATION_CODE stationCode,
            h.type,
            ifnull(s.PRODUCTION_DATE_SHOW,date_format(s.PRODUCTION_DATE,'%Y%m%d')) productionDateShow,
            ifnull(s.EXPIRED_DATE_SHOW,date_format(s.EXPIRED_DATE,'%Y%m%d')) expiredDateShow,
            s.WAREHOUSE_AREA_CODE warehouseAreaCode,
            s.LOCATOR_CODE locatorCode,
            s.LOCATOR_ID locatorId,
            s.bill_id,
            h.pick_order_id pickOrderId,
            max(obh.CREATEBY) createby,
            max(n.shipper_name) shipperName,
            max(n.shipper_id) shipperId,
            n.CONSIGNOR_ID consignorId,
            max(obh.CONSIGNORNAME) consignorName,
            max(n.BILL_GROUP_ID) srcCode,
            max(obh.remark) remark,
            max(obh.SALESPERSON_NAME) salespersonName,
            max(obh.PAYMENTDAYTYPECODE) paymentType,
            max(obh.BILLTYPE) billType,
            max(obh.ref_billid) refBillId,
            GROUP_CONCAT(bd.id) barcodeDId,
            wto.container_no containerCode,
            h.pick_task_type pickTaskType,
            s.BILL_D_ID billDId
        from wms_pick_task_d d
        left join wms_pick_task_h h on h.pick_task_id = d.pick_task_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_wave_bill n on m.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        LEFT JOIN wms_barcode_d bd ON d.pick_task_d_id = bd.wms_pick_task_d_id
        LEFT JOIN wms_barcode_h bh ON bd.barcode_id = bh.id
        LEFT JOIN wms_task_out_back wto ON wto.msg_id = d.ob_msg_id
        LEFT JOIN wms_task_out ou ON (ou.seqno = d.pick_task_d_id OR ou.seqno = d.src_pick_task_d_id)
        where h.WAREHOUSE_ID = #{warehouseId} AND h.type = 0
        AND (h.pick_order_id = #{containerCode} OR wto.container_no = #{containerCode} OR d.pick_task_d_id IN(SELECT wms_pick_task_d_id FROM wms_barcode_d WHERE id = #{containerCode}) OR d.pick_task_id = #{containerCode})
        AND d.FLOW_STATUS = 2
        AND (ou.msg_id IS NULL OR (ou.msg_id IS NOT NULL AND wto.msg_id IS NOT NULL AND wto.outtype = 1))
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and h.pick_task_type in
            <foreach collection="pickTaskTypes" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="bldgId != null and bldgId != ''">
            and h.bldg_id = #{bldgId}
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and h.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        group by
        m.ITEM_ID,
        m.ITEM_NAME,
        m.ITEM_CODE,
        m.manufacturer,
        m.item_specs,
        m.CASE_CONVERSION_RATE,
        s.manufacturer_lot_num,
        s.uom,
        s.PRODUCTION_DATE_SHOW,
        s.EXPIRED_DATE_SHOW,
        s.WAREHOUSE_AREA_CODE,
        s.LOCATOR_CODE,h.pick_order_id,
        n.CONSIGNOR_ID,h.WAREHOUSE_ID,s.PRODUCTION_DATE,s.EXPIRED_DATE,h.STATION_CODE,h.type) a
        order by a.locatorCode,a.itemId
    </select>

    <select id="getPickTaskDList4" resultType="com.prolog.cs.biz.outbound.model.dto.PickTaskDDTO">
        SELECT
            a.*,
            floor(a.qty/a.conversionRate) uomQty,
            mod(a.qty,a.conversionRate) oddPieceQty
        from
            (select
                d.PICK_TASK_D_ID,
                d.PICK_TASK_ID pickTaskId,
                d.qty qty,
                d.PICK_QTY pickQty,
                m.ITEM_ID itemId,
                m.ITEM_NAME itemName,
                m.ITEM_CODE itemCode,
                m.manufacturer,
                m.item_specs itemSpecs,
                case when m.CASE_CONVERSION_RATE=0 then 1 else ifnull(m.CASE_CONVERSION_RATE,1) end conversionRate,
                h.WAREHOUSE_ID warehouseId,
                s.manufacturer_lot_num manufacturerLotNum,
                s.uom,
                h.type,
                ifnull(s.PRODUCTION_DATE_SHOW,date_format(s.PRODUCTION_DATE,'%Y%m%d')) productionDateShow,
                ifnull(s.EXPIRED_DATE_SHOW,date_format(s.EXPIRED_DATE,'%Y%m%d')) expiredDateShow,
                s.WAREHOUSE_AREA_CODE warehouseAreaCode,
                s.LOCATOR_CODE locatorCode,
                s.bill_id,
                h.pick_order_id pickOrderId,
                max(obh.CREATEBY) createby,
                max(n.shipper_name) shipperName,
                n.CONSIGNOR_ID consignorId,
                max(obh.CONSIGNORNAME) consignorName,
                max(n.BILL_GROUP_ID) srcCode,
                max(obh.remark) remark,
                max(obh.SALESPERSON_NAME) salespersonName,
                max(obh.PAYMENTDAYTYPECODE) paymentType,
                max(obh.BILLTYPE) billType,
                po.operator_id,
                po.operator_code,
                po.operator_name,
                wto.container_no containerCode
            FROM wms_pick_task_d d
            LEFT JOIN wms_pick_task_h h on h.pick_task_id = d.pick_task_id
            LEFT JOIN wms_pick_order po ON h.pick_order_id = po.pick_order_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_wave_bill n on m.bill_id = n.id
            LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
            LEFT JOIN wms_barcode_d bd ON d.pick_task_d_id = bd.wms_pick_task_d_id
            LEFT JOIN wms_barcode_h bh ON bd.barcode_id = bh.id
            LEFT JOIN wms_task_out_back wto ON (wto.seqno = d.pick_task_d_id OR wto.seqno = d.src_pick_task_d_id)
            WHERE h.WAREHOUSE_ID = #{warehouseId}
            <if test="containerCode != null and containerCode != ''">
                AND (h.pick_order_id = #{containerCode} OR wto.container_no = #{containerCode})
            </if>
            AND h.status = #{flowStatus}
            AND h.pick_task_type = 0
            <if test="bldgId != null and bldgId != ''">
                AND h.bldg_id = #{bldgId}
            </if>
            <if test="areaCodes != null and areaCodes.size()>0">
                AND h.WAREHOUSE_AREA_CODE in
                <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                    #{areaCode}
                </foreach>
            </if>
            GROUP BY PICK_TASK_D_ID, pickTaskId,qty, itemId, itemName, itemCode, manufacturer,itemSpecs, conversionRate,warehouseId,manufacturerLotNum,uom, productionDateShow, expiredDateShow, warehouseAreaCode, locatorCode, bill_id, pickOrderId,shipperName, consignorName,operator_id, operator_code, operator_name, containerCode
        ) a
        order by a.locatorCode,a.itemId
    </select>

    <select id="findShipLineId" resultType="string">
        select
            obh.ship_line_id
        from wms_pick_task_h h
        left join WMS_WAVE_BILL n on h.bill_id = n.id
        LEFT JOIN OMS_DO_BILL_H obh ON obh.billid=n.SRC_ID
        where h.pick_task_id = #{pickTaskId}
        limit 1
    </select>

    <select id="listHByDetailId" resultType="com.prolog.cs.biz.outbound.model.PickTaskH">
        SELECT DISTINCT wpth.*
        FROM WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_WAVE_BILL wwb ON wpth.BILL_GROUP_ID = wwb.BILL_GROUP_ID
        LEFT JOIN WMS_MOVE_TASK_REF r ON r.BILL_ID = wwb.id
        LEFT JOIN WMS_MOVE_TASK v ON v.DETAIL_ID= r.REF_LINE_BILL_ID
        WHERE v.detail_id IN
        <foreach item="item" index="index" collection="detailIds" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

    <select id="countReplenishUnFinishTask" resultType="integer">
        SELECT count(DISTINCT v.detail_id)
        FROM WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_WAVE_BILL wwb ON wpth.BILL_GROUP_ID = wwb.BILL_GROUP_ID
        LEFT JOIN WMS_MOVE_TASK_REF r ON r.BILL_ID = wwb.id
        LEFT JOIN WMS_MOVE_TASK v ON v.DETAIL_ID= r.REF_LINE_BILL_ID
        WHERE wpth.pick_task_id = #{pickTaskId}
        AND v.`status` != 2
        AND v.detail_id NOT IN
        <foreach item="item" index="index" collection="detailIds" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

    <update id="updateToUnUp">
        UPDATE WMS_PICK_TASK_H wpth
        SET wpth.status = '0'
        WHERE wpth.pick_task_id = #{pickTaskId}
        AND wpth.`status` = '-1'
    </update>

    <update id="updateStatusEndByBarcode">
        UPDATE
            WMS_PICK_TASK_H
        set STATUS='10'
        where status='2'
        and PICK_TASK_TYPE='0'
        <if test="warehouseId != null and warehouseId != ''">
            and WAREHOUSE_ID= #{warehouseId}
        </if>
        and PICK_TASK_ID in (
            SELECT
                c.PICK_TASK_ID
            FROM WMS_BARCODE_D b,WMS_PICK_TASK_D c
            where (c.pick_task_d_id = b.wms_pick_task_d_id OR c.barcode_d_id = b.id)
            AND b.id = #{barcode}
            AND (SELECT
                    COUNT(DISTINCT pick_task_d_id)
                FROM WMS_PICK_TASK_D a
                WHERE PICK_TASK_ID = c.pick_task_id
                AND a.flow_status != 7
                AND (a.barcode_d_id != b.id OR b.wms_pick_task_d_id != a.pick_task_d_id) ) = 0
            )
    </update>

    <select id="getAreaUnAskPickCount"  resultType="com.prolog.cs.biz.outbound.model.vo.TaskCountVo">
        select wpth.warehouse_area_code as areaCode,count(DISTINCT wpth.pick_task_id) as count
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        where WPTH.WAREHOUSE_ID= #{warehouseId} and wpth.status = '0'
        and ((s.pick_model != 'Device' and (s.container_code is null or s.container_code = '' or s.container_code = 'N/A') )
        or (s.pick_model = 'Device' and s.container_code !='' and s.container_code is not null and s.container_code !='N/A'))
        <choose>
            <when test="pickTaskType== null">
                and wpth.PICK_TASK_TYPE in ('0','3')
            </when>
            <otherwise>
                and wpth.PICK_TASK_TYPE = #{pickTaskType}
            </otherwise>
        </choose>
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="type != null">
            and wpth.type = #{type}
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        GROUP BY wpth.warehouse_area_code
    </select>

    <select id="getPickTaskByContainerCode" resultMap="pickTaskHMap">
        select * from (select min(v.status) status,wpth.PICK_TASK_ID,wpth.BLDG_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wpth.gmt_create
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL_D m on m.ID = s.BILL_D_ID
        LEFT JOIN WMS_WAVE_BILL wwb on s.BILL_ID =wwb.id
        LEFT JOIN WMS_MOVE_TASK v on v.BILL_D_ID= m.id
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="billId != null and billId != ''">
            and wpth.bill_group_id = #{billId}
        </if>
        <choose>
            <when test='mjkFlag =="1"'>
                and s.pick_model = 'Device'
            </when>
            <otherwise>
                and s.pick_model != 'Device'
            </otherwise>
        </choose>
        <if test="containerCode != null and containerCode != ''">
            and s.container_code = #{containerCode} and WPTH.status = '0'
        </if>
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and wpth.pick_task_type in
            <foreach collection="pickTaskTypes" item="pickTaskType" open="(" separator="," close=")">
                #{pickTaskType}
            </foreach>
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <choose>
            <when test="workType !=null and workType == 'WHOLE_PICKING'.toString()">
                and wpth.type='0'
            </when>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH'.toString()">
                and wpth.type='1'
            </when>
        </choose>
        and s.item_id not in (
        SELECT v.item_id from WMS_MOVE_TASK v
        left join WMS_WAVE_BILL_D m on v.BILL_D_ID = m.id
        left join wms_wave_storage s on m.ID = s.BILL_D_ID
        where v.status &lt;&gt; '2' and s.pick_model !='Device'
        )
        GROUP BY wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wpth.gmt_create
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by wpth.type desc,COALESCE(wwb.PRIORITY,0) desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by wpth.type asc,COALESCE(wwb.PRIORITY,0) desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        ) t
        where 1=1
        <if test="workType == null">
            and (status is null or status='2')
        </if>
    </select>

    <select id="getPickTaskWholeListV2" resultMap="pickTaskHMap">
        select * from (select min(v.status) status,wpth.PICK_TASK_ID,wpth.BLDG_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wpth.gmt_create
        from WMS_PICK_TASK_H wpth
        LEFT JOIN WMS_PICK_TASK_D d on d.PICK_TASK_ID = wpth.PICK_TASK_ID
        LEFT JOIN WMS_WAVE_STORAGE s on s.id = d.wave_storage_id
        LEFT JOIN WMS_WAVE_BILL_D m on m.ID = s.BILL_D_ID
        LEFT JOIN WMS_WAVE_BILL wwb on s.BILL_ID =wwb.id
        LEFT JOIN WMS_MOVE_TASK v on v.BILL_D_ID= m.id
        where WPTH.ENTERPRISE_ID = #{enterpriseId} and WPTH.WAREHOUSE_ID= #{warehouseId}
        <if test="bldgId != null and bldgId != ''">
            and wpth.bldg_id = #{bldgId}
        </if>
        <if test="billId != null and billId != ''">
            and wpth.bill_group_id = #{billId}
        </if>
        <choose>
            <when test='mjkFlag =="1"'>
                and s.pick_model = 'Device'
            </when>
            <otherwise>
                and s.pick_model != 'Device'
            </otherwise>
        </choose>
        <if test="containerCode != null and containerCode != ''">
            and s.container_code=#{containerCode}
        </if>
        <if test="pickTaskTypes != null and pickTaskTypes.size()>0">
            and wpth.pick_task_type in
            <foreach collection="pickTaskTypes" item="pickTaskType" open="(" separator="," close=")">
                #{pickTaskType}
            </foreach>
        </if>
        <if test="areaCodes != null and areaCodes.size()>0">
            and wpth.WAREHOUSE_AREA_CODE in
            <foreach collection="areaCodes" item="areaCode" open="(" separator="," close=")">
                #{areaCode}
            </foreach>
        </if>
        <if test="count != null">
            and WPTH.status = '0'
        </if>
        <choose>
            <when test="workType !=null and workType == 'WHOLE_PICKING'.toString()">
                and wpth.type='0'
            </when>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH'.toString()">
                and wpth.type='1'
            </when>
        </choose>
        GROUP BY wpth.PICK_TASK_ID,wpth.BLDG_NAME ,wpth.PICK_TASK_TYPE ,wpth.BILL_GROUP_ID ,wpth.WAREHOUSE_ID ,wpth.type,wpth.WAVE_ID,wpth.WAREHOUSE_AREA_CODE,
        wwb.PRIORITY,wwb.src_id,wpth.gmt_create
        <choose>
            <when test="workType !=null and workType == 'WHOLE_REPLENISH_FIRST'.toString()">
                order by wpth.type desc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </when>
            <otherwise>
                order by wpth.type asc,IF(ISNULL(wwb.PRIORITY),1,0),wwb.PRIORITY desc,wpth.gmt_create asc,wwb.src_id asc,wpth.WAREHOUSE_AREA_CODE asc
            </otherwise>
        </choose>
        ) a
        where 1=1
        <if test="workType == null">
            and (status is null or status='2')
        </if>
        <!--<if test="count != null">
            limit #{count}
        </if>-->
    </select>

    <select id="getZtBills" resultMap="pickTaskHMap" >
        select
            wpth.*
        from
            wms_pick_task_h wpth
        join outcoming_record_h orh
        on
            wpth.bill_group_id = orh.BILLID
            and wpth.pick_task_type = '4'
            and wpth.status = 0
            and wpth.warehouse_area_code = 'ZT'
    </select>
    <select id="getByBarcode" resultType="com.prolog.cs.biz.outbound.model.PickTaskH">
        select
            wpth.*
        from
            wms_pick_task_h wpth
        join wms_pick_task_d wptd on
            wptd.pick_task_id = wpth.pick_task_id
        join wms_barcode_d wbd on
            wbd.wms_pick_task_d_id = wptd.pick_task_d_id
        join wms_barcode_h wbh on
            wbh.id = wbd.barcode_id
        where
            wbh.barcode = #{barcode}
            and wbh.bill_group_id = #{billGroupId}
            and wbh.bldg_id = #{bldgId}
            and wbh.warehouse_id = #{warehouseId}
            and wpth.warehouse_area_code != 'WLK'
    </select>


    <select id="getTaskHdrsByReplenish" resultType="com.prolog.cs.biz.outbound.model.PickTaskH">
        SELECT h.pick_task_id,mf.WAVE_ID as waveId, mf.SRC_CODE as billGroupId
        FROM WMS_MOVE_TASK mt
        LEFT JOIN wms_move_task_ref mf ON mt.detail_id = mf.REF_LINE_BILL_ID
        LEFT JOIN wms_wave_storage s ON mf.MOVE_TASK_ID = s.move_task_id
        LEFT JOIN wms_pick_task_d d ON d.wave_storage_id = s.id
        LEFT JOIN wms_pick_task_h h ON h.pick_task_id = d.pick_task_id
        WHERE mt.detail_id in
        <foreach item="item" index="index" collection="detailIds" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

    <select id="getTaskHdrsByReplenishV2" resultType="com.prolog.cs.biz.outbound.model.PickTaskH">
        SELECT h.*
        FROM WMS_MOVE_TASK mt
        LEFT JOIN wms_pick_task_d d ON d.bill_d_id  = mt.bill_d_id
        LEFT JOIN wms_pick_task_h h ON h.pick_task_id = d.pick_task_id
        WHERE mt.detail_id in
        <foreach item="item" index="index" collection="detailIds" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>
</mapper>