<?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.yunfeiyun.agmp.fms.mapper.FmsTaskpickMapper">

    <resultMap type="FmsTaskpick" id="FmsTaskpickResult">
        <result property="taskpickId"    column="taskpickId"    />
        <result property="taskId"    column="taskId"    />
        <result property="pickArea"    column="pickArea"    />
        <result property="pickWeight"    column="pickWeight"    />
        <result property="pickDate"    column="pickDate"    />
        <result property="pickRemark"    column="pickRemark"    />
    </resultMap>

    <resultMap id="FmsTaskPickListResVo" type="FmsTaskPickListResVo">

        <result property="taskNo" column="taskNo"/>
        <result property="taskId" column="taskId"/>
        <result property="cropName" column="cropName"/>
        <result property="cropType" column="cropType"/>
        <result property="cropPreview" column="cropPreview"/>
        <result property="planNo" column="planNo"/>
        <result property="landName" column="landName"/>
        <result property="blockName" column="blockName"/>
        <result property="planArea" column="planArea"/>
        <result property="pickWeight" column="pickWeight"/>
        <result property="taskPlanstartdate" column="taskPlanstartdate"/>
        <result property="taskPlanenddate" column="taskPlanenddate"/>
        <result property="taskrcdHour" column="taskrcdHour"/>
        <result property="taskCreateddate" column="taskCreateddate"/>
        <result property="taskManagername" column="taskManagername"/>
        <result property="taskAudittype" column="taskAudittype"/>
        <result property="taskStatus" column="taskStatus"/>
        <result property="taskContent" column="taskContent"/>
        <result property="pickNo" column="pickNo"/>
        <result property="pickDate" column="pickDate"/>
    </resultMap>

    <sql id="selectFmsTaskpickVo">
        select taskpickId, taskId, pickArea, pickWeight, pickDate, pickRemark, pickNo from FmsTaskpick
    </sql>

    <select id="selectFmsTaskpickList" parameterType="FmsTaskPickListReqVo" resultMap="FmsTaskPickListResVo">
        SELECT c.cropName, c.cropType, t.taskNo, t.taskId,r.resUrl as cropPreview, p.planNo, l.landName, b.blockName, p.planArea, i.pickWeight, i.pickNo, t.taskPlanstartdate, t.taskPlanenddate, s.taskrcdHour, t.taskCreateddate, t.taskManagername, t.taskAudittype, t.taskStatus, t.taskContent, t.taskAuditor
        FROM
        FmsTask t
        LEFT JOIN FmsCrop c ON t.cropId = c.cropId
        LEFT JOIN FmsLand l ON t.landId = l.landId
        LEFT JOIN FmsBlock b ON t.blockId = b.blockId
        LEFT JOIN FmsPlan p ON t.planId = p.planId
        LEFT JOIN FmsTaskpick i ON t.taskId = i.taskId
        LEFT JOIN FmsTaskrcd s ON t.taskId = s.taskId and s.taskrcdAuditstatus = 2
        LEFT JOIN
        (select * from SysRes where resMediatype = '1' and resBiztype = 'CROP')
        r on r.resBusId = c.cropId
        <where>
            <if test="cropId != null and cropId != ''">and t.cropId = #{cropId}</if>
            <if test="landId != null and landId != ''">and t.landId = #{landId}</if>
            <if test="blockId != null and blockId != ''">and t.blockId = #{blockId}</if>
            <if test="planId != null and planId != ''">and t.planId = #{planId}</if>
            <if test="taskId != null and taskId != ''">and t.taskId = #{taskId}</if>
            <if test="taskNo != null and taskNo != ''">and t.taskNo like concat('%', #{taskNo}, '%')</if>
            <if test="planNo != null and planNo != ''">and p.planNo like concat('%', #{planNo}, '%')</if>
            <if test="pickNo != null and pickNo != ''">and i.pickNo like concat('%', #{pickNo}, '%')</if>
            <if test="taskManagername != null and taskManagername !=''">and t.taskManagername like concat('%', #{taskManagername}, '%')</if>
            <if test="taskStatus != null and taskStatus != ''">and t.taskStatus = #{taskStatus}</if>
            <if test="taskAudittype != null and taskAudittype != ''">and t.taskAudittype = #{taskAudittype}</if>
            <if test="taskPlanstartdate != null and taskPlanstartdate != '' and taskPlanenddate != null and taskPlanenddate != ''">
                and t.taskPlanstartdate between #{taskPlanstartdate} and #{taskPlanenddate}
            </if>
            <if test="dataFilter == true and blockIds != null and blockIds.size() > 0">
                AND t.blockId in
                <foreach collection="blockIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and t.taskType = -1
        </where>
    </select>

    <select id="selectFmsTaskPickStockList" parameterType="String" resultType="com.yunfeiyun.agmp.fms.domain.resvo.FmsTaskPickStockListResVo">
        SELECT
        t.cropId,c.cropName,c.cropType,sum(tp.pickWeight) as pickWeight
        FROM
        FmsTaskpick tp
        LEFT JOIN FmsTask t on t.taskId = tp.taskId
        LEFT JOIN FmsTaskrcd r on r.taskId = t.taskId
        LEFT JOIN FmsCrop c on c.cropId = t.cropId
        <where>
            t.cropId is not null
            <if test="cropName != null and cropName != ''">and c.cropName like CONCAT("%",#{cropName},"%")</if>
            <if test="dataFilter == true and blockIds != null and blockIds.size() > 0">
                AND t.blockId in
                <foreach collection="blockIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY t.cropId,c.cropName,c.cropType
    </select>

    <select id="selectFmsTaskPickStockListStat" parameterType="FmsTaskPickListReqVo" resultType="FmsTaskPickStockListResVo">
        SELECT
        t.cropId,c.cropName,c.cropType,sum(tp.pickWeight) as pickWeight
        FROM
        FmsTaskpick tp
        LEFT JOIN FmsTask t on t.taskId = tp.taskId
        LEFT JOIN FmsTaskrcd r on r.taskId = t.taskId
        LEFT JOIN FmsCrop c on c.cropId = t.cropId
        <where>
            t.cropId is not null
            <if test="taskStatus != null and taskStatus != ''">and t.taskStatus = #{taskStatus}</if>
            <if test="landId != null and landId != ''">and t.landId = #{landId}</if>
            <if test="blockId != null and blockId != ''">and t.blockId = #{blockId}</if>
            <if test="dataFilter == true and blockIds !=null and blockIds.size()>0">
                and t.blockId in
                <foreach collection="blockIds" item="blockId" index="index" open="(" close=")" separator=",">
                    #{blockId}
                </foreach>
            </if>
        </where>
        GROUP BY t.cropId,c.cropName,c.cropType
    </select>

    <select id="selectSumPickWeightListGroupByCropName" parameterType="String" resultType="FmsTaskPickStockListResVo">
        select sum(tp.pickWeight) pickWeight, cropName
        from FmsTaskpick tp
        left join FmsTask t on t.taskId = tp.taskId
        left join FmsCrop c on t.cropId = c.cropId
        where t.taskStatus = '3'
        <if test="landId != null  and landId != ''">
            and t.landId =  #{landId}
        </if>
        <if test="startDate != null  and startDate != ''">
            and t.taskCompletedate >=  #{startDate}
        </if>
        <if test="endDate != null  and endDate != ''">
            and t.taskCompletedate <![CDATA[ <= ]]>  #{endDate}
        </if>
        <if test="dataFilter == true and landIds != null and landIds.size() > 0">
            AND t.landId in
            <foreach collection="landIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        group by c.cropName
    </select>

    <select id="selectFmsTaskPickStockDetailList" parameterType="FmsTaskPickStockDetailReqVo" resultType="com.yunfeiyun.agmp.fms.domain.resvo.FmsTaskPickStockDetailResVo">
        SELECT
        tp.pickNo,
        p.planNo,
        l.landName,
        b.blockName,
        p.planArea,
        t.taskCompletedate,
        tp.pickWeight
        FROM
        FmsTaskpick tp
        LEFT JOIN FmsTask t on t.taskId = tp.taskId
        LEFT JOIN FmsPlan p on p.planId = t.planId
        LEFT JOIN FmsLand l on l.landId = t.landId
        LEFT JOIN FmsBlock b on b.blockId = t.blockId
        LEFT JOIN FmsTaskrcd r on r.taskId = t.taskId
        <where>
            t.cropId = #{cropId}
            <if test="landId !=null and landId != ''">and t.landId = #{landId}</if>
            <if test="blockId !=null and blockId != ''">and t.blockId = #{blockId}</if>
            <if test="planNo !=null and planNo != ''">and p.planNo = #{planNo}</if>
            <if test="pickNo !=null and pickNo != ''">and tp.pickNo = #{pickNo}</if>
            <if test="taskCompletedatestart !=null and taskCompletedatestart != '' and taskCompletedateend !=null and taskCompletedateend != ''">and t.taskCompletedate between #{taskCompletedatestart} and #{taskCompletedateend}</if>
        </where>
    </select>

    <select id="selectFmsTaskpickByTaskpickId" parameterType="String" resultMap="FmsTaskpickResult">
        <include refid="selectFmsTaskpickVo"/>
        where taskpickId = #{taskpickId}
    </select>

    <select id="selectFmsTaskpickByTaskId" parameterType="String" resultMap="FmsTaskpickResult">
        <include refid="selectFmsTaskpickVo"/>
        where taskId = #{taskId}
    </select>

    <select id="selectFmsTaskpickByPlanId" parameterType="String" resultMap="FmsTaskPickListResVo">
        select tp.*, t.taskManagername, t.taskCompletedate
        from FmsTaskpick tp
        left join FmsTask t on tp.taskId = t.taskId
        where t.taskStatus = '3' and t.planId = #{planId}
    </select>

    <select id="selectFmsTaskpickMaxWaterKey"  resultType="String">
        select max(pickNo) as pickNo from FmsTaskpick LIMIT  1
    </select>

    <insert id="insertFmsTaskpick" parameterType="FmsTaskpick">
        insert into FmsTaskpick
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="taskpickId != null">taskpickId,</if>
            <if test="taskId != null">taskId,</if>
            <if test="pickArea != null">pickArea,</if>
            <if test="pickWeight != null">pickWeight,</if>
            <if test="pickDate != null">pickDate,</if>
            <if test="pickRemark != null">pickRemark,</if>
            <if test="pickNo != null">pickNo,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="taskpickId != null">#{taskpickId},</if>
            <if test="taskId != null">#{taskId},</if>
            <if test="pickArea != null">#{pickArea},</if>
            <if test="pickWeight != null">#{pickWeight},</if>
            <if test="pickDate != null">#{pickDate},</if>
            <if test="pickRemark != null">#{pickRemark},</if>
            <if test="pickNo != null">#{pickNo},</if>
        </trim>
    </insert>

    <update id="updateFmsTaskpick" parameterType="FmsTaskpick">
        update FmsTaskpick
        <trim prefix="SET" suffixOverrides=",">
            <if test="taskId != null">taskId = #{taskId},</if>
            <if test="pickArea != null">pickArea = #{pickArea},</if>
            <if test="pickWeight != null">pickWeight = #{pickWeight},</if>
            <if test="pickDate != null">pickDate = #{pickDate},</if>
            <if test="pickRemark != null">pickRemark = #{pickRemark},</if>
            <if test="pickNo != null">pickNo = #{pickNo},</if>
            <if test="pickChangestatus != null">pickChangestatus = #{pickChangestatus},</if>
        </trim>
        where taskpickId = #{taskpickId}
    </update>

    <delete id="deleteFmsTaskpickByTaskpickId" parameterType="String">
        delete from FmsTaskpick where taskpickId = #{taskpickId}
    </delete>

    <delete id="deleteFmsTaskpickByTaskpickIds" parameterType="String">
        delete from FmsTaskpick where taskpickId in
        <foreach item="taskpickId" collection="array" open="(" separator="," close=")">
            #{taskpickId}
        </foreach>
    </delete>
</mapper>