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


    <update id="addNum">
        update WMS_PICK_TASK_D
        set check_qty = check_qty + #{incrementQty}
        <if test="user1 != null and user1 != ''">
            , checker_name = #{user1}
        </if>
        <if test="user2 != null and user2 != ''">
            , checker_name_2 = #{user2}
        </if>
        where PICK_TASK_D_ID= #{pickTaskDId}
    </update>

    <update id="updateNum">
        update WMS_PICK_TASK_D
        set check_qty = #{newQty}
        <if test="user1 != null and user1 != ''">
            , checker_name = #{user1}
        </if>
        <if test="user2 != null and user2 != ''">
            , checker_name_2 = #{user2}
        </if>
        where PICK_TASK_D_ID= #{pickTaskDId}
    </update>


    <resultMap id="PickTaskDVO" type="com.prolog.cs.biz.outbound.model.vo.check.InnerCheckTaskDVO">
        <result column="pick_task_d_id" property="pickTaskDId"/>
        <result column="batch_num" property="batchNum"/>
        <result column="qty" property="qty"/>
        <result column="pick_qty" property="pickQty"/>
        <result column="expired_date" property="expiredDate"/>
        <result column="production_date" property="productionDate"/>
        <result column="item_id" property="itemId"/>
        <result column="item_code" property="itemCode"/>
        <result column="item_name" property="itemName"/>
        <result column="MANUFACTURER_LOT_NUM" property="lotNo"/>
        <result column="wave_storage_id" property="waveStorageId"/>
        <result column="billd_id" property="billDId"/>
        <result column="bill_id" property="billId"/>
        <result column="bill_code" property="billCode"/>
        <result column="SHIPPER_ID" property="shipperId"/>
        <result column="CONSIGNOR_ID" property="consignorId"/>
        <result column="type" property="billType"/>
        
        <result column="src_line_id" property="srcLineId"/>
        <result column="item_health_is_double" property="itemHealthIsDouble"/>
        <result column="ITEM_CATEGORY_CODE" property="itemCategoryCode"/>

        <result column="CHECK_QTY" property="checkQty"/>
        <result column="CHECKER2_QTY" property="check2Qty"/>
        <result column="red_qty" property="redQty"/>
        <result column="red_reason" property="redReason"/>
        <result column="check_time" property="checkTime"/>
        <result column="CHECKER1" property="checker1"/>
        <result column="CHECKER2" property="checker2"/>
        <result column="picker_name" property="pickerName"/>
        <result column="PICK_TIME" property="pickTime"/>
        <result column="uom" property="uom"/>
        <result column="VOLUME" property="volume"/>
        <result column="weight" property="weight"/>
        <result column="ITEM_SPECS" property="spec"/>
        <result column="warehouse_id" property="warehouseId"/>
        <result column="innercheck_d_id" property="innerCheckDId"/>
        <result column="CASE_CONVERSION_RATE" property="caseConversionRate"/>
        <result column="locator_code" property="locatorCode"/>
        <result column="IS_CASE_OR_PIECE" property="isCaseOrPiece"/>
        <result column="barcodeDId" property="barcodeDId"/>
        <result column="LCL_NO" property="lclNo"/>
        <result column="lclQty" property="lclQty"/>
        <result column="src_id" property="srcId"/>
    </resultMap>

    <resultMap id="PickTaskDV2VO" type="com.prolog.cs.biz.outbound.model.vo.check.InnerCheckTaskDV2VO">
        <result column="pick_task_d_id" property="pickTaskDId"/>
        <result column="batch_num" property="batchNum"/>
        <result column="qty" property="qty"/>
        <result column="pick_qty" property="pickQty"/>
        <result column="expired_date" property="expiredDate"/>
        <result column="production_date" property="productionDate"/>
        <result column="item_id" property="itemId"/>
        <result column="item_code" property="itemCode"/>
        <result column="item_name" property="itemName"/>
        <result column="MANUFACTURER_LOT_NUM" property="lotNo"/>
        <result column="wave_storage_id" property="waveStorageId"/>
        <result column="billd_id" property="billDId"/>
        <result column="bill_id" property="billId"/>
        <result column="bill_code" property="billCode"/>
        <result column="SHIPPER_ID" property="shipperId"/>
        <result column="CONSIGNOR_ID" property="consignorId"/>
        <result column="type" property="billType"/>

        <result column="src_line_id" property="srcLineId"/>
        <result column="item_health_is_double" property="itemHealthIsDouble"/>
        <result column="ITEM_CATEGORY_CODE" property="itemCategoryCode"/>

        <result column="CHECK_QTY" property="checkQty"/>
        <result column="CHECKER2_QTY" property="check2Qty"/>
        <result column="red_qty" property="redQty"/>
        <result column="red_reason" property="redReason"/>
        <result column="check_time" property="checkTime"/>
        <result column="CHECKER1" property="checker1"/>
        <result column="CHECKER2" property="checker2"/>
        <result column="picker_name" property="pickerName"/>
        <result column="PICK_TIME" property="pickTime"/>
        <result column="uom" property="uom"/>
        <result column="VOLUME" property="volume"/>
        <result column="weight" property="weight"/>
        <result column="ITEM_SPECS" property="spec"/>
        <result column="warehouse_id" property="warehouseId"/>
        <result column="innercheck_d_id" property="innerCheckDId"/>
        <result column="CASE_CONVERSION_RATE" property="caseConversionRate"/>
        <result column="locator_code" property="locatorCode"/>
        <result column="IS_CASE_OR_PIECE" property="isCaseOrPiece"/>
        <result column="barcodeDId" property="barcodeDId"/>
        <result column="LCL_NO" property="lclNo"/>
        <result column="lclQty" property="lclQty"/>
        <result column="src_id" property="srcId"/>
        <result column="flow_status" property="flowStatus"/>
    </resultMap>

    <select id="getPickTaskd" resultMap="PickTaskDVO">
        SELECT
        taskd.pick_task_d_id ,
        taskd.wave_storage_id,
        wws.batch_num ,
        COALESCE(taskd.qty,0) qty,
        COALESCE(taskd.pick_qty,0) pick_qty,
        wws.production_date,
        wws.expired_date,
        wws.MANUFACTURER_LOT_NUM,
        billd.item_id,
        billd.bill_id,
        billd.id as billd_id,
        bill.src_id,
        bill.code bill_code,
        bill.warehouse_id,
        bill.SHIPPER_ID,bill.CONSIGNOR_ID,bill.type,
        billd.item_code,
        billd.item_name,
        billd.src_line_id,
        billd.item_health_is_double,
        billd.ITEM_CATEGORY_CODE,
        billd.CASE_CONVERSION_RATE ,
        billd.IS_CASE_OR_PIECE ,
        billd.VOLUME ,
        BILLD.WEIGHT ,
        billd.ITEM_SPECS ,
        taskd.CHECK_QTY ,
        taskd.CHECKER2_QTY ,
        taskd.CHECK_TIME check_time,
        taskd.CHECKER_NAME_2 CHECKER1 ,
        taskd.CHECKER_NAME_2 CHECKER2 ,
        (select sum(coalesce(QTY,0)) from WMS_SALE_RED_RECORD r where r.BILL_ID=bill.id and r.BILL_D_ID=wws.BILL_D_ID
        and r.type=4 and r.PICK_TASK_D_ID=taskd.PICK_TASK_D_ID) red_qty ,
        (select rr.REASON from WMS_SALE_RED_RECORD rr where rr.id=(select max(id) from WMS_SALE_RED_RECORD r
        where r.BILL_ID=bill.id and r.BILL_D_ID=wws.BILL_D_ID
        and r.type=4 and r.PICK_TASK_D_ID=taskd.PICK_TASK_D_ID)) red_reason,
        taskd.picker_name,
        wws.uom,
        wws.locator_code,
        <if test="showLcl != null and 'true'.toString() == showLcl.toString()">
            b.id barcodeDId,
            b.qty lclQty,
            h.LCL_NO,
        </if>
        taskd.PICK_TIME,
        taskd.PICK_TASK_TYPE
        FROM WMS_PICK_TASK_D taskd
        inner join WMS_WAVE_STORAGE wws on wws.id = taskd.WAVE_STORAGE_ID
        inner JOIN wms_wave_bill_d billd ON wws.BILL_D_ID =billd.id
        inner join WMS_WAVE_BILL bill on bill.id = billd.bill_id
        <if test="showLcl != null and 'true'.toString() == showLcl.toString()">
            left join WMS_BARCODE_D b on taskd.PICK_TASK_D_ID = b.WMS_PICK_TASK_D_ID
            left join WMS_BARCODE_H h on h.id = b.BARCODE_ID
        </if>
        where taskd.PICK_TASK_ID = #{pickTaskId}
        order by billd.item_code
    </select>

    <select id="getPickTaskdV2" resultMap="PickTaskDV2VO">
        SELECT
            taskd.pick_task_d_id ,
            taskd.wave_storage_id,
            wws.batch_num ,
            COALESCE(taskd.qty,0) qty,
            COALESCE(taskd.pick_qty,0) pick_qty,
            wws.production_date,
            wws.expired_date,
            wws.MANUFACTURER_LOT_NUM,
            wws.item_id,
            wws.bill_id,
            wws.BILL_D_ID as billd_id,
<!--            bill.src_id,-->
<!--            bill.code bill_code,-->
            wws.warehouse_id,
<!--            bill.SHIPPER_ID,bill.CONSIGNOR_ID,bill.type,-->
<!--            billd.item_code,-->
<!--            billd.item_name,-->
<!--            billd.src_line_id,-->
<!--            billd.item_health_is_double,-->
<!--            billd.ITEM_CATEGORY_CODE,-->
<!--            billd.CASE_CONVERSION_RATE ,-->
<!--            billd.IS_CASE_OR_PIECE ,-->
            wws.VOLUME ,
            wws.WEIGHT ,
<!--            billd.ITEM_SPECS ,-->
            taskd.CHECK_QTY ,
            taskd.CHECKER2_QTY ,
            taskd.CHECK_TIME check_time,
            taskd.CHECKER_NAME_2 CHECKER1 ,
            taskd.CHECKER_NAME_2 CHECKER2 ,

            taskd.picker_name,
            wws.uom,
            wws.locator_code,
            <if test="showLcl != null and 'true'.toString() == showLcl.toString()">
                b.id barcodeDId,
                b.qty lclQty,
                h.LCL_NO,
            </if>
            taskd.PICK_TIME,
            taskd.PICK_TASK_TYPE
        FROM WMS_PICK_TASK_D taskd
        inner join WMS_WAVE_STORAGE wws on wws.id = taskd.WAVE_STORAGE_ID
        <if test="showLcl != null and 'true'.toString() == showLcl.toString()">
            left join WMS_BARCODE_D b on taskd.PICK_TASK_D_ID = b.WMS_PICK_TASK_D_ID
            left join WMS_BARCODE_H h on h.id = b.BARCODE_ID
        </if>
        where taskd.PICK_TASK_ID = #{pickTaskId}
        order by taskd.FLOW_STATUS asc, taskd.pick_task_d_id
    </select>



    <select id="getPickTaskdAndWareHouseV2" resultMap="PickTaskDV2VO">
        SELECT
        taskd.pick_task_d_id ,
        taskd.wave_storage_id,
        wws.batch_num ,
        COALESCE(taskd.qty,0) qty,
        COALESCE(taskd.pick_qty,0) pick_qty,
        wws.production_date,
        wws.expired_date,
        wws.MANUFACTURER_LOT_NUM,
        wws.item_id,
        wws.bill_id,
        wws.BILL_D_ID as billd_id,
        <!--            bill.src_id,-->
        <!--            bill.code bill_code,-->
        wws.warehouse_id,
        <!--            bill.SHIPPER_ID,bill.CONSIGNOR_ID,bill.type,-->
        <!--            billd.item_code,-->
        <!--            billd.item_name,-->
        <!--            billd.src_line_id,-->
        <!--            billd.item_health_is_double,-->
        <!--            billd.ITEM_CATEGORY_CODE,-->
        <!--            billd.CASE_CONVERSION_RATE ,-->
        <!--            billd.IS_CASE_OR_PIECE ,-->
        wws.VOLUME ,
        wws.WEIGHT ,
        <!--            billd.ITEM_SPECS ,-->
        taskd.CHECK_QTY ,
        taskd.CHECKER2_QTY ,
        taskd.CHECK_TIME check_time,
        taskd.CHECKER_NAME_2 CHECKER1 ,
        taskd.CHECKER_NAME_2 CHECKER2 ,

        taskd.picker_name,
        wws.uom,
        wws.locator_code,
        <if test="showLcl != null and 'true'.toString() == showLcl.toString()">
            b.id barcodeDId,
            b.qty lclQty,
            h.LCL_NO,
        </if>
        taskd.PICK_TIME,
        taskd.flow_status,
        taskd.PICK_TASK_TYPE
        FROM WMS_PICK_TASK_D taskd
        inner join WMS_WAVE_STORAGE wws on wws.id = taskd.WAVE_STORAGE_ID
        <if test="showLcl != null and 'true'.toString() == showLcl.toString()">
            left join WMS_BARCODE_D b on taskd.PICK_TASK_D_ID = b.WMS_PICK_TASK_D_ID
            left join WMS_BARCODE_H h on h.id = b.BARCODE_ID
        </if>
        where taskd.PICK_TASK_ID = #{pickTaskId}
        <if test="warehouseId != null and warehouseId != ''">
            and taskd.WAREHOUSE_ID = #{warehouseId}
        </if>
        order by taskd.pick_task_d_id
    </select>

</mapper>