<?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.deer.wms.base.system.dao.task.PickTaskMapper">

  <resultMap id="BaseResultMap" type="com.deer.wms.base.system.model.task.PickTask">
    <id column="pick_task_id" jdbcType="INTEGER" property="pickTaskId" />
    <result column="box_code" jdbcType="VARCHAR" property="boxCode" />
    <result column="pick_quantity" jdbcType="INTEGER" property="pickQuantity" />
    <result column="bill_out_detail_id" jdbcType="VARCHAR" property="billOutDetailId" />
      <result column="pick_state" jdbcType="INTEGER" property="pickState" />
      <result column="batch" jdbcType="VARCHAR" property="batch"/>
      <result column="sub_inventory_id" jdbcType="INTEGER" property="subInventoryId"/>
      <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
      <result column="out_time" jdbcType="VARCHAR" property="outTime"/>
      <result column="pick_type" jdbcType="INTEGER" property="pickType"/>
      <result column="lock_pick_quantity" jdbcType="INTEGER" property="lockPickQuantity"/>
  </resultMap>

    <resultMap id="Dto" type="com.deer.wms.base.system.model.task.PickTaskDto" extends="BaseResultMap">
        <result column="shelf_name" jdbcType="VARCHAR" property="shelfName"/>
        <result column="cell_id" jdbcType="INTEGER" property="cellId"/>
        <result column="s_column" jdbcType="INTEGER" property="sColumn"/>
        <result column="s_row" jdbcType="INTEGER" property="sRow"/>
        <result column="quantity" jdbcType="INTEGER" property="quantity"/>
        <result column="priority" jdbcType="VARCHAR" property="priority"/>
        <result column="sequence" jdbcType="INTEGER" property="sequence"/>
        <result column="bill_no" jdbcType="VARCHAR" property="billNo"/>
        <result column="bill_id" jdbcType="INTEGER" property="billId"/>
        <result column="slotting" jdbcType="VARCHAR" property="slotting"/>
        <result column="sub_inventory_code" jdbcType="VARCHAR" property="subInventoryCode"/>
        <result column="box_quantity" jdbcType="INTEGER" property="boxQuantity"/>
        <result column="item_code" jdbcType="VARCHAR" property="itemCode"/>
        <result column="batch" jdbcType="VARCHAR" property="batch"/>
        <result column="account_alias" jdbcType="VARCHAR" property="accountAlias"/>
        <result column="account_alias_id" jdbcType="INTEGER" property="accountAliasId"/>
        <result column="inventory_item_id" jdbcType="INTEGER" property="inventoryItemId"/>
        <result column="carrier_code" jdbcType="VARCHAR" property="carrierCode"/>
    </resultMap>

    <resultMap id="outDto" type="com.deer.wms.base.system.model.threeDimensional.OutTotal">
        <result column="bill_id" jdbcType="INTEGER" property="billId"/>
        <result column="bill_no" jdbcType="VARCHAR" property="billNo"/>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
        <result column="create_user_name" jdbcType="VARCHAR" property="createUserName"/>
        <result column="memo" jdbcType="VARCHAR" property="memo"/>
        <result column="already_out_quantity" jdbcType="INTEGER" property="alreadyOutQuantity"/>
        <result column="item_code" jdbcType="VARCHAR" property="itemCode"/>
        <result column="quantity" jdbcType="INTEGER" property="quantity"/>
        <result column="item_name" jdbcType="VARCHAR" property="itemName"/>
        <result column="out_time" jdbcType="VARCHAR" property="outTime"/>
        <result column="unit" jdbcType="VARCHAR" property="unit"/>
    </resultMap>

    <select id="findListTwo" parameterType="com.deer.wms.base.system.model.task.PickTaskCriteria" resultMap="Dto">
        SELECT
        bod.quantity,
        pt.*,
        bom.bill_id,
        bom.bill_no,
        bod.item_code,
        sin.sub_inventory_code,
        sin.slotting,
        aa.account_alias,
        bom.account_alias_id,
        ca.carrier_code
        FROM
        pick_task pt
        LEFT JOIN bill_out_detail bod ON bod.bill_out_detail_id = pt.bill_out_detail_id
        LEFT JOIN bill_out_master bom ON bom.bill_id = bod.bill_id
        left join account_alias aa on aa.disposition_id = bom.account_alias_id
        left join sub_inventory sin on sin.sub_inventory_id = pt.sub_inventory_id
        left join carrier ca on ca.bill_out_detail_id = pt.bill_out_detail_id
        where 1=1
        <if test="itemCode != null and itemCode != ''">
            and bod.item_code = #{itemCode}
        </if>
        <if test="batch != null and batch != ''">
            and pt.batch = #{batch}
        </if>
        <if test="billNo != null and billNo != ''">
            and bom.bill_no = #{billNo}
        </if>
        <if test="startTime != null and startTime != ''">
            and bom.create_time >= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND bom.create_time &lt; #{endTime}
        </if>
        <if test="state != null">
            AND bom.state = #{state}
        </if>
        <if test="pickType != null and pickType != -1">
            and pt.pick_type = #{pickType}
        </if>
        <if test="pickState != null">
            and pt.pick_state = #{pickState}
        </if>
        <if test="billOutDetailId != null and billOutDetailId != ''">
            and pt.bill_out_detail_id = #{billOutDetailId}
        </if>
        <if test="boxCode != null and boxCode != ''">
            and pt.box_code = #{boxCode}
        </if>
        <if test="billOutMasterType != null and billOutMasterType != ''">
            and bom.type = #{billOutMasterType}
        </if>
        order by pt.pick_task_id desc
    </select>
    <select id="selectList" parameterType="com.deer.wms.base.system.model.task.PickTaskCriteria" resultMap="outDto">
        <if test="state != null and state == 1">
            select count(0) as bill_id from (
        </if>
        SELECT
        bom.bill_id,
        bom.bill_no,
        bom.create_time,
        bod.item_code,
        ifnull(bom.create_user_name,"MES自动下发") as create_user_name,
        left(bom.memo,5) as memo,
        <if test="pickType != null and pickType == 2">
            sum(pt.pick_quantity) as already_out_quantity,
        </if>
        <if test="pickType != null and pickType == 6">
            pt.pick_quantity as already_out_quantity,
        </if>
        ii.unit,
        bod.quantity,
        ii.item_name,
        pt.out_time
        FROM
        pick_task pt
        LEFT JOIN bill_out_detail bod ON bod.bill_out_detail_id = pt.bill_out_detail_id
        LEFT JOIN bill_out_master bom ON bom.bill_id = bod.bill_id
        left join item_info ii on ii.item_code = bod.item_code
        where 1=1
        and bom.type = 1
        <if test="pickType != null and pickType == 2">
            and date(pt.out_time) = date(CURDATE())
            and bom.state = 2
            group by bod.bill_out_detail_id
        </if>
        <if test="pickType != null and pickType == 6">
            and pt.pick_state = 4
        </if>
        order by pt.pick_task_id desc
        <if test="state != null and state == 1">
            ) as total
        </if>
    </select>

    <select id="findList" parameterType="com.deer.wms.base.system.model.task.PickTaskCriteria" resultMap="Dto">
        SELECT
        si.shelf_name,
        ci.s_column,
        ci.s_row,
        ci.cell_id,
        bod.quantity,
        pt.*,
        bod.priority,
        bod.sequence,
        bom.bill_id,
        bom.bill_no,
        bite.quantity as box_quantity,
        bod.item_code,
        sin.sub_inventory_code,
        sin.slotting,
        aa.account_alias,
        bom.account_alias_id,
        ii.inventory_item_id,
        ca.carrier_code
        FROM
        	pick_task pt
        LEFT JOIN bill_out_detail bod ON bod.bill_out_detail_id = pt.bill_out_detail_id
        LEFT JOIN bill_out_master bom ON bom.bill_id = bod.bill_id
        left join account_alias aa on aa.disposition_id = bom.account_alias_id
        LEFT JOIN box_item bite ON bite.box_code = pt.box_code
        left join item_info ii on ii.item_code = bite.item_code
        INNER JOIN box_info bin ON bin.box_code = bite.box_code
        left JOIN cell_info ci ON ci.cell_id = bin.box_cell_id
        LEFT JOIN shelf_info si ON si.shelf_id = ci.shelf_id
        left join sub_inventory sin on sin.sub_inventory_id = pt.sub_inventory_id
        left join carrier ca on ca.bill_out_detail_id = pt.bill_out_detail_id
        where 1=1
        <if test="itemCode != null and itemCode != ''">
            and ii.item_code = #{itemCode}
        </if>
        <if test="billNo != null and billNo != ''">
            and bom.bill_no = #{billNo}
        </if>
        <if test="startTime != null and startTime != ''">
            and bom.create_time >= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND bom.create_time &lt; #{endTime}
        </if>
        <if test="state != null">
            AND bom.state = #{state}
        </if>
        <if test="workOrderStockState != null">
            and bite.work_order_stock_state = #{workOrderStockState}
        </if>
        <if test="pickState != null">
            and pt.pick_state = #{pickState}
        </if>
        <if test="billOutDetailId != null and billOutDetailId != ''">
            and pt.bill_out_detail_id = #{billOutDetailId}
        </if>
        <if test="boxCode != null and boxCode != ''">
            and pt.box_code = #{boxCode}
        </if>
        <if test="billOutMasterType != null and billOutMasterType != ''">
            and bom.type = #{billOutMasterType}
        </if>
        <if test="boxCodes != null">
            and pt.box_code in
            <foreach collection="boxCodes" item="boxCode" index="index" open="(" separator="," close=")">
                #{boxCode}
            </foreach>
        </if>
        <!--<if test="startTime != null and startTime != ''">
            and date(pt.out_time) &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and date(pt.out_time) &lt;= #{endTime}
        </if>-->
        order by bod.sequence,pt.pick_task_id
    </select>

    <select id="findByState" parameterType="com.deer.wms.base.system.model.task.PickTaskCriteria" resultMap="Dto">
        SELECT
        bin.box_cell_id,
        bod.quantity,
        pt.*,
        bod.priority,
        bod.sequence,
        bom.bill_id,
        bite.quantity as box_quantity,
        bite.item_code,
        bite.batch,
        sin.sub_inventory_code,
        sin.slotting
        FROM
        pick_task pt
        LEFT JOIN bill_out_detail bod ON bod.bill_out_detail_id = pt.bill_out_detail_id
        LEFT JOIN bill_out_master bom ON bom.bill_id = bod.bill_id
        LEFT JOIN box_item bite ON bite.box_code = pt.box_code
        INNER JOIN box_info bin ON bin.box_code = bite.box_code
        left join sub_inventory sin on sin.sub_inventory_id = pt.sub_inventory_id
        where 1=1
        <if test="startTime != null and startTime != ''">
            and bom.create_time >= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND bom.create_time &lt; #{endTime}
        </if>
        <if test="state != null">
            AND bom.state = #{state}
        </if>
        <if test="workOrderStockState != null">
            and bite.work_order_stock_state = #{workOrderStockState}
        </if>
        <if test="pickState != null">
            and pt.pick_state = #{pickState}
        </if>
        <if test="billOutDetailId != null and billOutDetailId != ''">
            and pt.bill_out_detail_id = #{billOutDetailId}
        </if>
        <if test="boxCode != null and boxCode != ''">
            and pt.box_code = #{boxCode}
        </if>
        <if test="billOutMasterType != null and billOutMasterType != ''">
            and bom.type = #{billOutMasterType}
        </if>
        order by bod.sequence,pt.pick_task_id
    </select>

    <select id="totalSevenQuantity"  resultType="java.lang.Integer">
        select
        sum(pick_quantity)
        from pick_task
        where 1=1 and out_time &gt;= date_sub(curdate(),INTERVAL 15 DAY) and out_time &lt; curdate() and pick_state = 4;
    </select>
</mapper>