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

    <resultMap type="FmsTaskasset" id="FmsTaskassetResult">
        <result property="taskassetId"    column="taskassetId"    />
        <result property="assetgoodsId"    column="assetgoodsId"    />
        <result property="taskrcdId"    column="taskrcdId"    />
        <result property="taskassetUsedcount"    column="taskassetUsedcount"    />
    </resultMap>
    <resultMap id="FmsTaskAssetUsageResVo" type="FmsTaskAssetUsageResVo">
        <result property="goodsType" column="goodsType"/>
        <result property="goodsName" column="goodsName"/>
        <result property="taskNo" column="taskNo"/>
        <result property="planNo" column="planNo"/>
        <result property="cropName" column="cropName"/>
        <result property="cropType" column="cropType"/>
        <result property="taskId" column="taskId"/>
        <result property="taskassetUsedcount" column="taskassetUsedcount"/>
        <result property="goodsUnit" column="goodsUnit"/>
        <result property="goodsSpec" column="goodsSpec"/>
        <result property="taskrcdAuditstatus" column="taskrcdAuditstatus"/>
        <result property="taskrcdCompletedate" column="taskrcdCompletedate"/>
        <result property="taskManagername" column="taskManagername"/>
    </resultMap>

    <sql id="selectFmsTaskassetVo">
        select taskassetId, assetgoodsId, taskrcdId, taskassetUsedcount from FmsTaskasset
    </sql>

    <select id="selectFmsTaskassetList" parameterType="FmsTaskasset" resultMap="FmsTaskassetResult">
        <include refid="selectFmsTaskassetVo"/>
        <where>
            <if test="assetgoodsId != null  and assetgoodsId != ''"> and assetgoodsId = #{assetgoodsId}</if>
            <if test="taskrcdId != null  and taskrcdId != ''"> and taskrcdId = #{taskrcdId}</if>
            <if test="taskassetUsedcount != null "> and taskassetUsedcount = #{taskassetUsedcount}</if>
        </where>
    </select>

    <select id="selectFmsTaskassetByTaskassetId" parameterType="String" resultMap="FmsTaskassetResult">
        <include refid="selectFmsTaskassetVo"/>
        where taskassetId = #{taskassetId}
    </select>

    <insert id="insertFmsTaskasset" parameterType="FmsTaskasset">
        insert into FmsTaskasset
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="taskassetId != null">taskassetId,</if>
            <if test="assetgoodsId != null">assetgoodsId,</if>
            <if test="taskrcdId != null">taskrcdId,</if>
            <if test="taskassetUsedcount != null">taskassetUsedcount,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="taskassetId != null">#{taskassetId},</if>
            <if test="assetgoodsId != null">#{assetgoodsId},</if>
            <if test="taskrcdId != null">#{taskrcdId},</if>
            <if test="taskassetUsedcount != null">#{taskassetUsedcount},</if>
        </trim>
    </insert>

    <insert id="insertFmsTaskassetBatch" parameterType="FmsTaskasset">
        insert into FmsTaskasset
        <trim prefix="(" suffix=")" suffixOverrides=",">
            taskassetId,
            assetgoodsId,
            taskrcdId,
            taskassetUsedcount,
            taskassetInvestmentAmount,
        </trim>
        values
        <foreach item="data" collection="list" separator=",">
            (
            #{data.taskassetId},
            #{data.assetgoodsId},
            #{data.taskrcdId},
            #{data.taskassetUsedcount},
            #{data.taskassetInvestmentAmount}
            )
        </foreach>
    </insert>

    <update id="updateFmsTaskasset" parameterType="FmsTaskasset">
        update FmsTaskasset
        <trim prefix="SET" suffixOverrides=",">
            <if test="assetgoodsId != null">assetgoodsId = #{assetgoodsId},</if>
            <if test="taskrcdId != null">taskrcdId = #{taskrcdId},</if>
            <if test="taskassetUsedcount != null">taskassetUsedcount = #{taskassetUsedcount},</if>
        </trim>
        where taskassetId = #{taskassetId}
    </update>

    <update id="updateFmsTaskassetBatch" parameterType="FmsTaskasset">
        <foreach collection="list" item="taskasset" separator=";">
            update FmsTaskasset
            <trim prefix="SET" suffixOverrides=",">
                <if test="taskasset.assetgoodsId != null">assetgoodsId = #{taskasset.assetgoodsId},</if>
                <if test="taskasset.taskrcdId != null">taskrcdId = #{taskasset.taskrcdId},</if>
                <if test="taskasset.taskassetUsedcount != null">taskassetUsedcount = #{taskasset.taskassetUsedcount},</if>
            </trim>
            where taskassetId = #{taskasset.taskassetId}
        </foreach>
    </update>

    <delete id="deleteFmsTaskassetByTaskassetId" parameterType="String">
        delete from FmsTaskasset where taskassetId = #{taskassetId}
    </delete>

    <delete id="deleteFmsTaskassetByTaskassetIds" parameterType="String">
        delete from FmsTaskasset where taskassetId in
        <foreach item="taskassetId" collection="array" open="(" separator="," close=")">
            #{taskassetId}
        </foreach>
    </delete>

    <select id="selectFmsTaskAssetUsageResVoList" parameterType="FmsTaskAssetUsageReqVo" resultMap="FmsTaskAssetUsageResVo">
        SELECT
        a.taskassetId,
        ag.assetgoodsType AS goodsType,
        g.goodsName,
        t.taskNo,
        p.planNo,
        c.cropName,
        c.cropType,
        t.taskId,
        a.taskassetUsedcount,
        g.goodsUnit,
        g.goodsSpec,
        r.taskrcdCompletedate,
        t.taskManagername
        FROM
        FmsTaskasset a
        LEFT JOIN FmsTaskrcd r ON r.taskrcdId = a.taskrcdId
        LEFT JOIN WmsAssetgoods ag ON ag.assetgoodsId = a.assetgoodsId
        LEFT JOIN WmsGoods g ON g.goodsId = ag.goodsId
        LEFT JOIN FmsTask t ON t.taskId = r.taskId
        LEFT JOIN FmsPlan p ON p.planId = t.planId
        LEFT JOIN FmsCrop c ON c.cropId = p.cropId
        <where>
            <if test="goodsType != null and goodsType != '' ">and ag.assetgoodsType = #{goodsType}</if>
            <if test="goodsName != null and goodsName != '' ">and g.goodsName = #{goodsName}</if>
            <if test="planNo != null and planNo != '' ">and p.planNo = #{planNo}</if>
            <if test="taskNo != null and taskNo != '' ">and t.taskNo = #{taskNo}</if>
            <if test="cropName != null and cropName != '' ">and c.cropName = #{cropName}</if>
            <if test="taskManagername != null and taskManagername != '' ">and t.taskManagername = #{taskManagername}</if>
            <if test="taskrcdCompletedateStart != null and taskrcdCompletedateStart != '' and taskrcdCompletedateEnd != null and taskrcdCompletedateEnd != '' ">and r.taskrcdCompletedate between #{taskrcdCompletedateStart} and #{taskrcdCompletedateEnd}</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="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and r.taskrcdAuditstatus = '2'
        </where>
    </select>

    <select id="selectFmsTaskAssetByRcdId" parameterType="String" resultMap="FmsTaskAssetUsageResVo">
        SELECT
        ta.*,
        g.*,
        ag.assetgoodsType as goodsType,
        ag.assetgoodsManufactor as manufactor,
        ag.supplierId as supplierId
        FROM
        FmsTaskasset ta
        LEFT JOIN WmsAssetgoods ag on ag.assetgoodsId = ta.assetgoodsId
        LEFT JOIN WmsGoods g ON g.goodsId = ag.goodsId
        where ta.taskrcdId = #{taskrcdId}
    </select>

    <select id="selectFmsTaskAssetStat" parameterType="FmsTaskListReqVo" resultMap="FmsTaskAssetUsageResVo">
        SELECT
        sum( a.taskassetUsedcount ) taskassetUsedcount,
        ag.assetgoodsType AS goodsType,
        g.goodsName,
        g.goodsUnit,
        g.goodsSpec,
        g.goodsId
        FROM
        FmsTaskasset a
        LEFT JOIN FmsTaskrcd r ON r.taskrcdId = a.taskrcdId
        LEFT JOIN WmsAssetgoods ag ON ag.assetgoodsId = a.assetgoodsId
        LEFT JOIN WmsGoods g ON g.goodsId = ag.goodsId
        LEFT JOIN FmsTask t ON t.taskId = r.taskId
        <where>
            <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="startDate != null  and startDate != ''"> and t.taskCompletedate <![CDATA[ >= ]]> #{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>
            and r.taskrcdAuditstatus = '2'
        </where>
        GROUP BY g.goodsId,ag.assetgoodsType
    </select>

</mapper>