<?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.TaskInfoMapper">
    
    <resultMap type="TaskInfo" id="TaskInfoResult">
        <result property="id"    column="id"    />
        <result property="taskId"    column="task_id"    />
        <result property="startPosition"    column="start_position"    />
        <result property="endPosition"    column="end_position"    />
        <result property="type"    column="type"    />
        <result property="state"    column="state"    />
        <result property="quantity"    column="quantity"    />
        <result property="completeQuantity"    column="complete_quantity"    />
        <result property="barCode"    column="bar_code"    />
        <result property="boxCode"    column="box_code"    />
        <result property="billInDetailId"    column="bill_in_detail_id"    />
        <result property="isTop"    column="istop"    />
        <result property="billOutDetailId"    column="bill_out_detail_id"    />
        <result property="cardNo"    column="card_no"    />
        <result property="taskStartTime"    column="task_start_time"    />
        <result property="taskEndTime"    column="task_end_time"    />
        <result property="inventoryCheckId"    column="inventory_check_id"    />
    </resultMap>

    <resultMap type="TaskInfoWcs" id="taskInfoWcs">
        <result property="taskNo"  jdbcType="VARCHAR"  column="task_id"    />
        <result property="fromStation"  jdbcType="VARCHAR"  column="start_position"    />
        <result property="toStation" jdbcType="VARCHAR"  column="end_position"    />
        <result property="type"  jdbcType="VARCHAR" column="type"    />
        <result property="state" jdbcType="VARCHAR" column="state"/>
        <result property="createTime" jdbcType="VARCHAR" column="create_time"/>
        <result property="level" jdbcType="VARCHAR" column="istop"/>
        <result property="barcode" jdbcType="VARCHAR" column="bar_code"/>
        <result property="number" jdbcType="INTEGER" column="quantity"/>
    </resultMap>

    <resultMap type="TaskInfoDto" id="TaskInfoDtoResult" extends="TaskInfoResult">
        <result property="billId"  jdbcType="INTEGER"  column="bill_id"    />
        <result property="itemCode"  jdbcType="VARCHAR"  column="item_code"    />
        <result property="priority" jdbcType="VARCHAR"  column="priority"    />
        <result property="sequence" jdbcType="INTEGER" column="sequence"/>
        <result property="billNo"  jdbcType="VARCHAR" column="bill_no"    />
        <result property="operatorName" jdbcType="VARCHAR" column="operator_name"/>
        <result property="inventoryItemId" jdbcType="INTEGER" column="inventory_item_id"/>
        <result property="unit" jdbcType="VARCHAR" column="unit"/>
        <result property="batch" jdbcType="VARCHAR" column="batch"/>
        <result property="pd" jdbcType="VARCHAR" column="pd"/>
        <result property="exp" jdbcType="VARCHAR" column="exp"/>
        <result property="inTime" jdbcType="VARCHAR" column="in_time"/>
        <result property="height" jdbcType="DOUBLE" column="height"/>
        <result property="billMasterType" jdbcType="INTEGER" column="bill_master_type"/>
    </resultMap>

    <resultMap type="com.deer.wms.base.system.model.task.TotalSevenDays" id="Dto">
        <result property="times"  jdbcType="VARCHAR"  column="times"    />
        <result property="totalItemCodeQuantity"  jdbcType="INTEGER"  column="total_item_code_quantity"    />
        <result property="totalBoxQuantity" jdbcType="INTEGER"  column="total_box_quantity"    />
        <result property="totalQuantity"  jdbcType="INTEGER" column="total_quantity"    />
    </resultMap>

    <resultMap id="taskDto" type="com.deer.wms.base.system.model.threeDimensional.Task">
        <result property="id"  jdbcType="INTEGER"  column="id"    />
        <result property="taskId"  jdbcType="VARCHAR"  column="task_id"    />
        <result property="startPosition"  jdbcType="VARCHAR"  column="start_position"    />
        <result property="endPosition"  jdbcType="VARCHAR"  column="end_position"    />
        <result property="type"  jdbcType="INTEGER"  column="type"    />
        <result property="state"  jdbcType="INTEGER"  column="state"    />
        <result property="quantity"  jdbcType="INTEGER"  column="quantity"    />
        <result property="completeQuantity"  jdbcType="INTEGER"  column="complete_quantity"    />
        <result property="barCode"  jdbcType="VARCHAR"  column="bar_code"    />
        <result property="boxCode" jdbcType="VARCHAR"   column="box_code"    />
        <result property="operatorName" jdbcType="VARCHAR" column="operator_name"/>
        <result property="taskStartTime"   jdbcType="VARCHAR"  column="task_start_time"    />
        <result property="taskEndTime" jdbcType="VARCHAR"    column="task_end_time"    />
    </resultMap>
	
	<sql id="selectTaskInfoVo">
        SELECT id,task_id,start_position,end_position,type,state,quantity,complete_quantity,
        	bar_code,box_code,bill_in_detail_id,istop,bill_out_detail_id,card_no,task_start_time,
        	task_end_time,inventory_check_id
        FROM
        	task_info
    </sql>

    <select id="getTaskInfoByTaskId" parameterType="String" resultMap="TaskInfoResult">

        <include refid="selectTaskInfoVo"/>
        where task_id = #{taskId}

    </select>

    <select id="selectTaskInfoByBillOutMasterId" parameterType="Integer" resultMap="TaskInfoResult">

        select ti.*
        from task_info ti join bill_out_detail bod
        on ti.bill_out_detail_id = bod.bill_out_detail_id
        where bod.bill_id = #{billId}
    </select>

    <select id="selectTaskInfoByBillInMasterId" parameterType="Integer" resultMap="TaskInfoResult">
        select ti.*
        from task_info ti join bill_in_detail bid
        on ti.bill_in_detail_id = bid.bill_in_detail_id
        where bid.bill_id = #{billId}
    </select>
    <!--查询任务表状态为0的任务提供给WCS-->
    <select id="selectTaskInfoForWcsByState" resultMap="taskInfoWcs">
        select
        task_id,
        start_position,
        end_position,
        type,
        state,
        istop,
        now() as create_time,
        bar_code,
        quantity
        from task_info
        where state = 0
        order by istop,id
    </select>

    <select id="selectTaskInfoList" parameterType="TaskInfo" resultMap="TaskInfoResult">
        <include refid="selectTaskInfoVo"/>
        <where>  
            <if test="id != null "> and id = #{id}</if>
             <if test="taskId != null  and taskId != '' "> and task_id = #{taskId}</if>
             <if test="startPosition != null  and startPosition != '' "> and start_position = #{startPosition}</if>
             <if test="endPosition != null  and endPosition != '' "> and end_position = #{endPosition}</if>
             <if test="type != null "> and type = #{type}</if>
             <if test="state != null "> and state = #{state}</if>
             <if test="quantity != null "> and quantity = #{quantity}</if>
             <if test="completeQuantity != null "> and complete_quantity = #{completeQuantity}</if>
             <if test="barCode != null  and barCode != '' "> and bar_code = #{barCode}</if>
             <if test="boxCode != null  and boxCode != '' "> and box_code = #{boxCode}</if>
             <if test="billInDetailId != null  and billInDetailId != '' "> and bill_in_detail_id = #{billInDetailId}</if>
             <if test="isTop != null  and isTop != '' "> and istop = #{isTop}</if>
             <if test="billOutDetailId != null  and billOutDetailId != '' "> and bill_out_detail_id = #{billOutDetailId}</if>
         </where>
    </select>

    <select id="selectTaskInfoById" parameterType="Integer" resultMap="TaskInfoResult">
        <include refid="selectTaskInfoVo"/>
        where id = #{id}
    </select>
        
    <insert id="insertTaskInfo" parameterType="TaskInfo" useGeneratedKeys="true" keyProperty="id">
        insert into task_info
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="taskId != null  and taskId != ''  ">task_id,</if>
			<if test="startPosition != null  and startPosition != ''  ">start_position,</if>
			<if test="endPosition != null  and endPosition != ''  ">end_position,</if>
			<if test="type != null  ">type,</if>
			<if test="state != null  ">state,</if>
			<if test="quantity != null  ">quantity,</if>
			<if test="completeQuantity != null  ">complete_quantity,</if>
			<if test="barCode != null  and barCode != ''  ">bar_code,</if>
			<if test="boxCode != null  and boxCode != ''  ">box_code,</if>
			<if test="billInDetailId != null  and billInDetailId != ''  ">bill_in_detail_id,</if>
			<if test="isTop != null  and isTop != ''  ">istop,</if>
			<if test="billOutDetailId != null  and billOutDetailId != ''  ">bill_out_detail_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="taskId != null  and taskId != ''  ">#{taskId},</if>
			<if test="startPosition != null  and startPosition != ''  ">#{startPosition},</if>
			<if test="endPosition != null  and endPosition != ''  ">#{endPosition},</if>
			<if test="type != null  ">#{type},</if>
			<if test="state != null  ">#{state},</if>
			<if test="quantity != null  ">#{quantity},</if>
			<if test="completeQuantity != null  ">#{completeQuantity},</if>
			<if test="barCode != null  and barCode != ''  ">#{barCode},</if>
			<if test="boxCode != null  and boxCode != ''  ">#{boxCode},</if>
			<if test="billInDetailId != null  and billInDetailId != ''  ">#{billInDetailId},</if>
			<if test="isTop != null  and isTop != ''  ">#{isTop},</if>
			<if test="billOutDetailId != null  and billOutDetailId != ''  ">#{billOutDetailId},</if>
         </trim>
    </insert>
	 
    <update id="updateTaskInfo" parameterType="TaskInfo">
        update task_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="taskId != null  and taskId != ''  ">task_id = #{taskId},</if>
            <if test="startPosition != null  and startPosition != ''  ">start_position = #{startPosition},</if>
            <if test="endPosition != null  and endPosition != ''  ">end_position = #{endPosition},</if>
            <if test="type != null  ">type = #{type},</if>
            <if test="state != null  ">state = #{state},</if>
            <if test="quantity != null  ">quantity = #{quantity},</if>
            <if test="completeQuantity != null  ">complete_quantity = #{completeQuantity},</if>
            <if test="barCode != null  and barCode != ''  ">bar_code = #{barCode},</if>
            <if test="boxCode != null  and boxCode != ''  ">box_code = #{boxCode},</if>
            <if test="billInDetailId != null  and billInDetailId != ''  ">bill_in_detail_id = #{billInDetailId},</if>
            <if test="isTop != null  and isTop != ''  ">istop = #{isTop},</if>
            <if test="billOutDetailId != null  and billOutDetailId != ''  ">istop = #{billOutDetailId},</if>
        </trim>
        where id = #{id}
    </update>

	<delete id="deleteTaskInfoById" parameterType="Integer">
        delete from task_info where id = #{id}
    </delete>
	
    <delete id="deleteTaskInfoByIds" parameterType="String">
        delete from task_info where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="findList" parameterType="com.deer.wms.base.system.model.task.TaskInfoCriteria" resultMap="TaskInfoDtoResult">
        select
        ti.*,
        bom.bill_no,
        bom.type as bill_master_type,
        bod.item_code,
        ii.inventory_item_id,
        ii.unit,
        bod.priority,
        op.operator_name
        from task_info ti
        left join bill_out_detail bod on bod.bill_out_detail_id = ti.bill_out_detail_id
        left join bill_out_master bom on bom.bill_id = bod.bill_id
        left join operator op on op.operator_card = ti.card_no
        left join item_info ii on ii.item_code = bod.item_code
        where 1=1
        <if test="boxCode != null and boxCode != ''">
            and ti.box_code = #{boxCode}
        </if>
        <if test="type != null">
            and ti.type = #{type}
        </if>
        <if test="state != null">
            and ti.state = #{state}
        </if>
        <if test="startTime != null and startTime != ''">
            and date(ti.task_start_time) &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and date(ti.task_start_time) &lt;= #{endTime}
        </if>
        <if test="billOutDetailId != null and billOutDetailId != ''">
            and ti.bill_out_detail_id = #{billOutDetailId}
        </if>
        <if test="operatorName != null and operatorName != ''">
            and op.operator_name = #{operatorName}
        </if>
        <if test="operatorName != null and operatorName != ''">
            and op.operator_name = #{operatorName}
        </if>
        order by
        (case
        when ti.state = 0 then 1
        when ti.state = 1 then 2
        when ti.state = 5 then 3
        when ti.state = 3 then 4
        end) asc,id desc
    </select>

    <select id="findByItemCodeAndBatchAndExp" parameterType="com.deer.wms.base.system.model.task.TaskInfoCriteria" resultMap="TaskInfoResult">
        select
        max(ti.task_start_time) as task_start_time
        from task_info ti
        left join bill_out_detail bod on bod.bill_out_detail_id = ti.bill_out_detail_id
        left join bill_out_master bom on bom.bill_id = bod.bill_id
        left join box_item bi on bi.box_code = ti.box_code
        where 1=1
        and bom.type in (1,4)
        <if test="types != null">
            and ti.type in
            <foreach item="type" collection="types" open="(" separator="," close=")">
                #{type}
            </foreach>
        </if>
        <if test="itemCode != null and itemCode != ''">
            and bi.item_code = #{itemCode}
        </if>
        <if test="batch != null and batch != ''">
            and bi.batch = #{batch}
        </if>
        <if test="exp != null and exp != ''">
            and bi.exp = #{exp}
        </if>
        and bi.sub_inventory_id in (1,4,7)
        group by ti.task_start_time
    </select>

    <select id="findByBoxCodeLastTask" parameterType="java.lang.String"  resultType="java.lang.String">
        select task_end_time
        from task_info
        where box_code = #{boxCode}
        and type = 1
        order by id desc limit 0,1
    </select>

    <select id="findByType" parameterType="com.deer.wms.base.system.model.task.TaskInfoCriteria" resultMap="TaskInfoDtoResult">
        <if test="type != null and type == 3">
            select
            ti.box_code,
            ti.quantity,
            ti.task_end_time,
            ii.item_code,
            bid.segment as bill_no,
            (select bir.batch from bill_in_record bir where bir.bill_in_detail_id = ti.bill_in_detail_id and bir.box_code = ti.box_code order by bir.bill_in_record_id limit 1) as batch
            from task_info ti
            left join bill_in_detail bid on bid.bill_in_detail_id = ti.bill_in_detail_id
            left join item_info ii on ii.inventory_item_id = bid.item_id
            where 1=1
            and ti.type = #{type} and ti.bill_in_detail_id is not null
            order by ti.id desc,ti.state asc
        </if>
        <if test="type != null and type == 12">
            select
            ti.box_code,
            ti.state,
            ti.quantity,
            pt.pick_quantity as complete_quantity,
            ti.task_end_time,
            batch,
            bod.item_code,
            bom.bill_no,
            ii.unit,
            (ii.thickness*pt.pick_quantity) as height
            from task_info ti
            left join pick_task pt on pt.bill_out_detail_id = ti.bill_out_detail_id and pt.box_code = ti.box_code
            left join bill_out_detail bod on bod.bill_out_detail_id = ti.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 ti.type = #{type}
            order by ti.id desc,ti.state asc
        </if>
    </select>

    <select id="totalSevenDays"  resultMap="Dto">
        select
        date(tii.task_end_time) as times,
         (
            select count(*) from (
                select count(*),ti.task_end_time
                from task_info ti
                where ti.type = 3
                group by ti.bill_in_detail_id,date(date(ti.task_end_time))
            ) as a where date(a.task_end_time) = date(tii.task_end_time)
         ) as total_item_code_quantity,
        count(*) as total_box_quantity,
        sum(tii.quantity) as total_quantity
        from task_info tii
        where date(tii.task_end_time)
        in
        (select abc.task_end_time from
        (select date(task_end_time) as task_end_time from task_info where date(task_end_time) &lt; date(NOW()) and type = 3 and bill_in_detail_id is not null GROUP BY date(task_end_time) order by date(task_end_time) desc limit 7)
        as abc
        )
        and tii.type = 3 and bill_in_detail_id is not null
        group by date(tii.task_end_time)

    </select>

    <select id="findByTypeAndState" resultMap="TaskInfoResult">
        select * from task_info
        where type not in (1,2,3) and state !=3
    </select>

    <select id="findByStateAndType" parameterType="com.deer.wms.base.system.model.task.TaskInfoCriteria" resultMap="taskDto">
        <if test="whetherNullBox != null and whetherNullBox == 1">
            select count(0) as quantity from (
        </if>
        select
        ti.id,
        ti.task_id,
        ti.start_position,
        ti.end_position,
        ti.type,
        ti.state,
        ti.quantity,
        ti.complete_quantity,
        ti.box_code,
        ti.bar_code,
        op.operator_name,
        ti.task_start_time,
        ti.task_end_time
        from task_info ti
        left join operator op on op.operator_card = ti.card_no
        where 1=1
        and ti.state != 3
        order by ti.id desc
        <if test="whetherNullBox != null and whetherNullBox == 1">
            ) as total
        </if>
    </select>


</mapper>