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

    <sql id="Base_Column_List">
        id, barcode_id, wms_pick_task_d_id, warehouse_id, enterprise_id, gmt_create, creator, creator_name, gmt_modified, modifier, modifier_name, qty, item_code, item_id, container_code, is_piece, print_cnt, check_status, src_line_id, src_id, src_code, bill_d_id, bill_id
    </sql>

    <insert id="insertOrUpdate">
        MERGE INTO WMS_BARCODE_D t1 USING dual on (
        t1.ITEM_CODE = #{itemCode}
        AND t1.BARCODE_ID = #{barcodeId}
        AND T1.container_code = #{containerCode}
        AND T1.WMS_PICK_TASK_D_ID = #{pickTaskDId}
        )
        WHEN MATCHED THEN
        UPDATE
        SET
        t1.qty = #{qty}
        WHEN NOT MATCHED THEN
        insert
        (ITEM_CODE,item_id, id, BARCODE_ID,container_code, qty,WMS_PICK_TASK_D_ID,is_piece)
        values
        (#{itemCode}, #{itemId}, #{barcodeDId}, #{barcodeId},  #{containerCode}, #{qty},#{pickTaskDId},#{isPiece})
    </insert>

    <insert id="insertOrAdd">
        MERGE INTO WMS_BARCODE_D t1 USING dual on (
        t1.ITEM_CODE = #{itemCode}
        AND t1.BARCODE_ID = #{barcodeId}
        AND T1.container_code = #{containerCode}
        AND T1.WMS_PICK_TASK_D_ID = #{pickTaskDId}
        )
        WHEN MATCHED THEN
        UPDATE
        SET
        t1.qty = t1.qty + #{qty}
        WHEN NOT MATCHED THEN
        insert
        (ITEM_CODE,item_id, id, BARCODE_ID,container_code,qty,WMS_PICK_TASK_D_ID,is_piece)
        values
         (#{itemCode}, #{itemId}, #{barcodeDId}, #{barcodeId}, #{containerCode}, #{qty},#{pickTaskDId},#{isPiece})
    </insert>


    <update id="updateBarcodeD">
        merge into WMS_BARCODE_D barcodeD using (
        select
        WMS_BARCODE_H.id,
        WMS_BARCODE_H.bill_group_id,
        WMS_BARCODE_H.lcl_no
        from
        WMS_BARCODE_H
        ) barcodeH on (
        barcodeH.id = barcodeD.BARCODE_ID
        AND barcodeH.bill_group_id= #{billGroupId}
        AND barcodeD.item_code= #{itemCode}
        and barcodeH.lcl_no = #{lclNo}
        )
        when matched then
        update
        set
        barcodeD.qty = #{qty}
    </update>
    <update id="adJustBarcodeD">
       update  WMS_BARCODE_D t1 set qty = #{qty}
       where    t1.item_code= #{itemCode}  AND t1.BARCODE_ID = #{barcodeId}
    </update>
    <update id="updatePrintCountByBarcode">
        update  WMS_BARCODE_D t1 SET print_cnt = IFNULL(print_cnt, 0) + 1
        WHERE id IN
        <foreach collection="ids" index="index" item="item" separator="," close=")" open="(">
            #{item}
        </foreach>
    </update>

    <delete id="deleteByBarcodeLong">
        DELETE FROM WMS_BARCODE_D barcodeD
        WHERE BARCODE_ID = #{barcodeHId}
    </delete>
    <select id="getLclDetail" resultType="com.prolog.cs.biz.outbound.model.print.LclDetailTableVO">
        SELECT
          barcodeD.ITEM_CODE,
          barcodeD.QTY pieceQty,
          barcodeD.item_id,
          billd.item_name,
          billd.ITEM_SPECS specification,
          billd.MANUFACTURER manufacture,
          billd.CASE_CONVERSION_RATE conversionRate,
          barcodeH.lcl_no,
          wws.MANUFACTURER_LOT_NUM manufacturerLotNum ,
          odbd.REMARK ,
          wws.uom
        FROM
          WMS_BARCODE_D barcodeD
        LEFT JOIN WMS_BARCODE_H barcodeH ON barcodeH.id = barcodeD.BARCODE_ID
        LEFT JOIN WMS_PICK_TASK_D wptd ON wptd.PICK_TASK_D_ID = barcodeD.WMS_PICK_TASK_D_ID
        LEFT JOIN WMS_WAVE_STORAGE wws ON wws.id = wptd.WAVE_STORAGE_ID
        LEFT JOIN WMS_WAVE_BILL_D billd ON  billd.id = wws.BILL_D_ID
        LEFT JOIN OMS_DO_BILL_D odbd ON odbd.LINEID = billd.SRC_LINE_ID
        WHERE
          barcodeH.BILL_GROUP_ID = #{billGroupId}
          AND barcodeh.TYPE = '0'
          AND barcodeH.LCL_NO =  #{lclNo}
    </select>

    <resultMap id="LclPrintParameterVO" type="com.prolog.cs.biz.outbound.model.vo.check.LclPrintParameterVO">
        <result column="id" property="lclNo"/>
        <result column="telephone" property="telephone"/>
        <result column="weight" property="weight"/>
        <result column="address" property="address"/>
        <result column="company" property="company"/>
        <result column="contacts" property="contacts"/>
        <result column="checker1" property="checker1"/>
        <result column="checker2" property="checker2"/>
        <result column="workstation" property="workstation"/>
        <result column="workstageCode" property="workstageCode"/>
        <result column="barcode" property="barcode"/>
        <result column="routeNo" property="routeNo"/>
        <result column="bill_type" property="billType"/>

    </resultMap>

    <select id="getBarcodeByPickTaskDId" resultType="map">
        SELECT h.barcode, d.wms_pick_task_d_id from wms_barcode_d d left join wms_barcode_h h on d.barcode_id = h.id
        WHERE d.wms_pick_task_d_id IN
        <foreach collection="list" index="index" item="item" separator="," close=")" open="(">
            #{item}
        </foreach>
    </select>

    <select id="listBarcodeDBySeqNo" resultType="com.prolog.cs.biz.outbound.model.entity.BarCodeDDO">
        SELECT
            bd.*, ph.type taskType
        FROM WMS_BARCODE_D bd
        LEFT JOIN wms_pick_task_d pd ON bd.wms_pick_task_d_id = pd.pick_task_d_id
        LEFT JOIN wms_task_out_back ob ON pd.ob_msg_id = ob.msg_id
        LEFT JOIN wms_pick_task_h ph ON pd.pick_task_id = ph.pick_task_id
        WHERE ob.seqno IN
        <foreach collection="list" open="(" close=")" separator="," item="item" index="index">
            #{item}
        </foreach>
        AND ob.container_no = #{containerCode}
    </select>

    <select id="getListByBarCodeIds" resultType="com.prolog.cs.biz.outbound.model.entity.BarCodeDDO">
        select <include refid="Base_Column_List"/>
        from wms_barcode_d
        where barcode_id in
        <foreach collection="barcodeIds" open="(" close=")" separator="," item="item" index="index">
            #{item}
        </foreach>
    </select>
    <select id="getBarcodeDetail"
            resultType="com.prolog.cs.biz.outbound.model.dto.check.OuterCheckBarcodeDetailDTO">
        select
            DISTINCT
            wbh.barcode ,
            wbd.item_code as itemCode,
            bmim.ITEM_NAME itemName,
            bmim.SPECIFICATION,
            odbd.manufacturer ,
            wws.manufacturer_lot_num manufacturerLotNum,
            DATE_FORMAT(wws.production_date, '%Y-%m-%d') batchProductionDateShow,
            DATE_FORMAT(wws.expired_date, '%Y-%m-%d') batchExpiredDateShow,
            wptd.pick_qty qty,
            wws.uom,
            round(wptd.pick_qty / wwbd.case_conversion_rate, 2) as uomQty,
            (case
                wisf.STATUS when 1 then '合格'
                when 2 then '不合格'
                when '3' then '待检'
                when '4' then '退货'
                else ''
            end ) as stockStatus
        from
            wms_barcode_d wbd
        join wms_barcode_h wbh on
            wbh.id = wbd.barcode_id
        join wms_pick_task_d wptd on
            wptd.pick_task_d_id = wbd.wms_pick_task_d_id
        join oms_do_bill_d odbd on
            wptd.src_line_id = odbd.lineid
        join wms_wave_storage wws on
            wptd.wave_storage_id = wws.id
        join wms_wave_bill_d wwbd on
            wwbd.id = wptd.bill_d_id
        join upcloud_base_item_qingniu.base_mtl_item_mst bmim on
            bmim.ITEM_ID = wptd.item_id
        join upcloud_base_inventory_qingniu.wms_inv_stock_flow wisf on
            (wisf.LINE_BILL_ID  = wwbd.id
            and wisf.OP_TYPE  = 'Z')
        where
            wbh.barcode = #{barCode}
            and wbh.warehouse_id = #{warehouseId}
    </select>

</mapper>