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

    <resultMap id="FmsTaskResult" type="FmsTask">
    <result column="taskId" property="taskId"/>
    <result column="taskNo" property="taskNo"/>
    <result column="planId" property="planId"/>
    <result column="blockId" property="blockId"/>
    <result column="landId" property="landId"/>
    <result column="cropId" property="cropId"/>
    <result column="taskCreatetype" property="taskCreatetype"/>
    <result column="taskType" property="taskType"/>
    <result column="taskContent" property="taskContent"/>
    <result column="taskPlanstartdate" property="taskPlanstartdate"/>
    <result column="taskPlanenddate" property="taskPlanenddate"/>
    <result column="taskAudittype" property="taskAudittype"/>
    <result column="taskStatus" property="taskStatus"/>
    <result column="taskManager" property="taskManager"/>
    <result column="taskManagername" property="taskManagername"/>
    <result column="taskCreator" property="taskCreator"/>
    <result column="taskCreateddate" property="taskCreateddate"/>
    <result column="taskCompletedate" property="taskCompletedate"/>
    <result column="taskModifieddate" property="taskModifieddate"/>
    <result column="taskCompletereason" property="taskCompletereason"/>
</resultMap>

    <sql id="selectFmsTaskVo">
        select taskId, taskNo, planId, blockId, landId, cropId, taskCreatetype, taskType, taskContent, taskPlanstartdate, taskPlanenddate, taskAudittype, taskStatus, taskManager, taskManagername, taskCreator, taskCreateddate, taskCompletedate, taskModifieddate, taskCompletereason, taskAuditor from FmsTask
    </sql>

    <select id="selectFmsTaskList" parameterType="FmsTask" resultMap="FmsTaskResult">
        <include refid="selectFmsTaskVo"/>
        <where>  
            <if test="taskNo != null  and taskNo != ''"> and taskNo = #{taskNo}</if>
            <if test="planId != null  and planId != ''"> and planId = #{planId}</if>
            <if test="blockId != null  and blockId != ''"> and blockId = #{blockId}</if>
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="cropId != null  and cropId != ''"> and cropId = #{cropId}</if>
            <if test="taskCreatetype != null  and taskCreatetype != ''"> and taskCreatetype = #{taskCreatetype}</if>
            <if test="taskType != null  and taskType != ''"> and taskType = #{taskType}</if>
            <if test="taskContent != null  and taskContent != ''"> and taskContent = #{taskContent}</if>
            <if test="taskPlanstartdate != null  and taskPlanstartdate != ''"> and taskPlanstartdate = #{taskPlanstartdate}</if>
            <if test="taskPlanenddate != null  and taskPlanenddate != ''"> and taskPlanenddate = #{taskPlanenddate}</if>
            <if test="taskAudittype != null  and taskAudittype != ''"> and taskAudittype = #{taskAudittype}</if>
            <if test="taskManager != null  and taskManager != ''"> and taskManager = #{taskManager}</if>
            <if test="taskManagername != null  and taskManagername != ''"> and taskManagername like concat('%', #{taskManagername}, '%')</if>
            <if test="taskCreator != null  and taskCreator != ''"> and taskCreator = #{taskCreator}</if>
            <if test="taskCreateddate != null  and taskCreateddate != ''"> and taskCreateddate = #{taskCreateddate}</if>
            <if test="taskCompletedate != null  and taskCompletedate != ''"> and taskCompletedate = #{taskCompletedate}</if>
            <if test="taskModifieddate != null  and taskModifieddate != ''"> and taskModifieddate = #{taskModifieddate}</if>
        </where>
    </select>

    <select id="selectTaskList" parameterType="FmsTaskListReqVo" resultType="FmsTaskResVo">
        select t.*, c.cropName, c.cropType, l.landName, b.blockName, p.planNo, p.planArea,s.taskrcdHour
        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 FmsTaskrcd s ON t.taskId = s.taskId and s.taskrcdAuditstatus = 2
        <where>
            <if test="taskNo != null  and taskNo != ''"> and t.taskNo = #{taskNo}</if>
            <if test="planId != null  and planId != ''"> and t.planId = #{planId}</if>
            <if test="blockId != null  and blockId != ''"> and t.blockId = #{blockId}</if>
            <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
            <if test="cropId != null  and cropId != ''"> and t.cropId = #{cropId}</if>
            <if test="taskAuditstatus != null  and taskAuditstatus != ''"> and t.taskStatus = #{taskAuditstatus}</if>
            <if test="taskAudittype != null  and taskAudittype != ''"> and t.taskAudittype = #{taskAudittype}</if>
            <if test="cropName != null  and cropName != ''"> and c.cropName = #{cropName}</if>
            <if test="taskCreatetype != null  and taskCreatetype != ''"> and t.taskCreatetype = #{taskCreatetype}</if>
            <if test="taskType != null  and taskType != ''"> and t.taskType = #{taskType}</if>
            <if test="taskType == null || taskType == ''">and t.taskType >= 0</if>
            <if test="taskManager != null  and taskManager != ''"> and t.taskManager = #{taskManager}</if>
            <if test="taskManagername != null  and taskManagername != ''"> and t.taskManagername like concat('%', #{taskManagername}, '%')</if>
            <if test="startDate != null  and startDate != ''">
                and t.taskPlanenddate >=  #{startDate}
            </if>
            <if test="endDate != null  and endDate != ''">
                and t.taskPlanstartdate <![CDATA[ <= ]]>  #{endDate}
            </if>
            <if test="taskAuditor != null and taskAuditor !=''">
                and t.taskAuditor = #{taskAuditor}
            </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>
    </select>

    <select id="selectTaskListAll" parameterType="FmsTaskListReqVo" resultType="FmsTaskResVo">
        select t.*, c.cropName, c.cropType, l.landName, b.blockName, p.planNo, p.planArea,s.taskrcdHour
        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 FmsTaskrcd s ON t.taskId = s.taskId and s.taskrcdAuditstatus = 2
        <where>
            <if test="taskNo != null  and taskNo != ''"> and t.taskNo = #{taskNo}</if>
            <if test="planId != null  and planId != ''"> and t.planId = #{planId}</if>
            <if test="blockId != null  and blockId != ''"> and t.blockId = #{blockId}</if>
            <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
            <if test="cropId != null  and cropId != ''"> and t.cropId = #{cropId}</if>
            <if test="taskAuditstatus != null  and taskAuditstatus != ''"> and t.taskStatus = #{taskAuditstatus}</if>
            <if test="taskAudittype != null  and taskAudittype != ''"> and t.taskAudittype = #{taskAudittype}</if>
            <if test="cropName != null  and cropName != ''"> and c.cropName = #{cropName}</if>
            <if test="taskCreatetype != null  and taskCreatetype != ''"> and t.taskCreatetype = #{taskCreatetype}</if>
            <if test="taskType != null  and taskType != ''">
                and t.taskType in
                <foreach collection="taskType.split(',')" item="items" open="(" separator="," close=")">
                    <if test="items != null  and items != ''">
                        #{items}
                    </if>
                </foreach>
            </if>
            <if test="taskManager != null  and taskManager != ''"> and t.taskManager = #{taskManager}</if>
            <if test="taskManagername != null  and taskManagername != ''"> and t.taskManagername like concat('%', #{taskManagername}, '%')</if>
            <if test="startDate != null  and startDate != ''">
                and t.taskPlanenddate >=  #{startDate}
            </if>
            <if test="endDate != null  and endDate != ''">
                and t.taskPlanstartdate <![CDATA[ <= ]]>  #{endDate}
            </if>
            <if test="taskAuditor != null and taskAuditor !=''">
                and t.taskAuditor = #{taskAuditor}
            </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>
    </select>

    <select id="selectTaskListScreenData" parameterType="FmsTaskListReqVo" resultType="FmsTaskResVo">
        select t.*, c.cropName, c.cropType, l.landName, b.blockName
        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
        <where>
            <if test="blockId != null  and blockId != ''"> and t.blockId = #{blockId}</if>
            <if test="landId != null  and landId != ''"> and t.landId = #{landId}</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>
        order by taskCreateddate desc limit 10
    </select>

    <select id="selectAllTaskList" parameterType="FmsTaskListReqVo" resultType="FmsTaskMessageResVo">
        select t.*, c.cropName, c.cropType, l.landName, b.blockName, p.planNo, p.planArea,tp.taskpatrolWay,tp.taskpatrolAddress
        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 FmsTaskpatrol tp on tp.taskId = t.taskId
        <where>
            <if test="taskNo != null  and taskNo != ''"> and t.taskNo = #{taskNo}</if>
            <if test="planId != null  and planId != ''"> and t.planId = #{planId}</if>
            <if test="blockId != null  and blockId != ''"> and t.blockId = #{blockId}</if>
            <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
            <if test="cropId != null  and cropId != ''"> and t.cropId = #{cropId}</if>
            <if test="taskAuditstatus != null  and taskAuditstatus != ''"> and t.taskStatus = #{taskAuditstatus}</if>
            <if test="cropName != null  and cropName != ''"> and c.cropName = #{cropName}</if>
            <if test="taskCreatetype != null  and taskCreatetype != ''"> and t.taskCreatetype = #{taskCreatetype}</if>
            <if test="taskType != null  and taskType != ''"> and t.taskType = #{taskType}</if>
            <if test="taskManager != null  and taskManager != ''"> and t.taskManager = #{taskManager}</if>
            <if test="taskManagername != null  and taskManagername != ''"> and t.taskManagername like concat('%', #{taskManagername}, '%')</if>
            <if test="startDate != null  and startDate != ''">
                and t.taskPlanenddate >=  #{startDate}
            </if>
            <if test="endDate != null  and endDate != ''">
                and t.taskPlanstartdate <![CDATA[ <= ]]>  #{endDate}
            </if>
            <if test="taskAuditor != null and taskAuditor !=''">
                and t.taskAuditor = #{taskAuditor}
            </if>
            <if test="dataFilter == true and  blockIds != null and blockIds.size()>0 and landIds !=null and landIds.size()>0">
                AND (

                t.blockId in
                <foreach collection="blockIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
                or
                t.landId in
                <foreach collection="landIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
                )
            </if>
        </where>
    </select>
    
    <select id="selectFmsTaskByTaskId" parameterType="String" resultMap="FmsTaskResult">
        <include refid="selectFmsTaskVo"/>
        where taskId = #{taskId}
    </select>

    <select id="selectCountFmsTaskByPlanId" parameterType="String" resultType="int">
        select count(1) from FmsTask where planId = #{planId}
    </select>

    <select id="selectCountFmsTask" parameterType="FmsTaskListReqVo" resultType="int">
        select count(1) from FmsTask
        <where>
            <if test="taskAuditstatus != null  and taskAuditstatus != ''">
                and
                <foreach collection="taskAuditstatus.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        taskStatus = #{items}
                    </if>
                </foreach>
            </if>
            <if test="startDate != null  and startDate != ''"> and taskCompletedate <![CDATA[ >= ]]> #{startDate}</if>
            <if test="endDate != null  and endDate != ''"> and taskCompletedate <![CDATA[ <= ]]> #{endDate}</if>
            <if test="taskManager != null  and taskManager != ''"> and taskManager = #{taskManager}</if>
        </where>
    </select>

    <select id="selectCountFmsTaskByType" parameterType="FmsTaskListReqVo" resultType="FmsTaskTypeCountResVo">
        SELECT count(taskType) count, taskType FROM FmsTask
        <where>
            <if test="taskAuditstatus != null  and taskAuditstatus != ''"> and taskStatus = #{taskAuditstatus}</if>
            <if test="taskAudittype != null  and taskAudittype != ''">
                and
                <foreach collection="taskAudittype.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        taskAudittype = #{items}
                    </if>
                </foreach>
            </if>
            <if test="startDate != null  and startDate != ''"> and taskCreateddate <![CDATA[ >= ]]> #{startDate}</if>
            <if test="endDate != null  and endDate != ''"> and taskCreateddate <![CDATA[ <= ]]> #{endDate}</if>
            <if test="dataFilter == true and blockIds != null and blockIds.size() > 0">
                AND blockId in
                <foreach collection="blockIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and taskType >=0
        </where>
        GROUP BY taskType
        order by count desc;
    </select>

    <select id="selectSumTaskrcdHour" parameterType="FmsTaskListReqVo" resultType="FmsTaskSumTaskrcdHourResVo">
        select sum(tb1.taskrcdHour) taskrcdHour,t.taskType
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '1' or tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        where t.taskType >= 0 and tb1.taskrcdAuditstatus = '2'
        <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
        <if test="blockId != null  and blockId != ''"> and t.blockId = #{blockId}</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>
        group by t.taskType
    </select>

    <select id="getInfoByTaskId" parameterType="String" resultType="FmsTaskResVo">
        select t.*, c.cropName, c.cropType, l.landName, b.blockName, p.planNo, p.planArea, u.userName taskCreatorName
        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 SysUser u ON t.taskCreator = u.userId
        where t.taskId = #{taskId}
    </select>

    <select id="getInfoByPlanId" parameterType="String" resultType="FmsTaskResVo">
        select t.*, tb1.taskrcdId
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where taskrcdAuditstatus = '1' or taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        where t.taskType >= 0 and t.taskStatus = '3' and t.planId = #{planId}
    </select>

    <select id="selectFmsTaskMaxWaterKey"  resultType="String">
        select max(taskNo) as taskNo from FmsTask LIMIT  1
    </select>

    <insert id="insertFmsTask" parameterType="FmsTask">
        insert into FmsTask
        <trim suffixOverrides="," suffix=")" prefix="(">
            <if test="taskId != null">taskId,</if>
            <if test="taskNo != null">taskNo,</if>
            <if test="planId != null">planId,</if>
            <if test="blockId != null">blockId,</if>
            <if test="landId != null">landId,</if>
            <if test="cropId != null">cropId,</if>
            <if test="taskCreatetype != null">taskCreatetype,</if>
            <if test="taskType != null">taskType,</if>
            <if test="taskContent != null">taskContent,</if>
            <if test="taskPlanstartdate != null">taskPlanstartdate,</if>
            <if test="taskPlanenddate != null">taskPlanenddate,</if>
            <if test="taskAudittype != null">taskAudittype,</if>
            <if test="taskStatus != null">taskStatus,</if>
            <if test="taskManager != null">taskManager,</if>
            <if test="taskManagername != null">taskManagername,</if>
            <if test="taskCreator != null">taskCreator,</if>
            <if test="taskCreateddate != null">taskCreateddate,</if>
            <if test="taskCompletedate != null">taskCompletedate,</if>
            <if test="taskModifieddate != null">taskModifieddate,</if>
            <if test="taskCompletereason != null">taskCompletereason,</if>
            <if test="taskAuditor != null">taskAuditor,</if>
        </trim>
        <trim suffixOverrides="," suffix=")" prefix="values (">
            <if test="taskId != null">#{taskId},</if>
            <if test="taskNo != null">#{taskNo},</if>
            <if test="planId != null">#{planId},</if>
            <if test="blockId != null">#{blockId},</if>
            <if test="landId != null">#{landId},</if>
            <if test="cropId != null">#{cropId},</if>
            <if test="taskCreatetype != null">#{taskCreatetype},</if>
            <if test="taskType != null">#{taskType},</if>
            <if test="taskContent != null">#{taskContent},</if>
            <if test="taskPlanstartdate != null">#{taskPlanstartdate},</if>
            <if test="taskPlanenddate != null">#{taskPlanenddate},</if>
            <if test="taskAudittype != null">#{taskAudittype},</if>
            <if test="taskStatus != null">#{taskStatus},</if>
            <if test="taskManager != null">#{taskManager},</if>
            <if test="taskManagername != null">#{taskManagername},</if>
            <if test="taskCreator != null">#{taskCreator},</if>
            <if test="taskCreateddate != null">#{taskCreateddate},</if>
            <if test="taskCompletedate != null">#{taskCompletedate},</if>
            <if test="taskModifieddate != null">#{taskModifieddate},</if>
            <if test="taskCompletereason != null">#{taskCompletereason},</if>
            <if test="taskAuditor != null">#{taskAuditor},</if>
        </trim>
    </insert>

    <update id="updateFmsTask" parameterType="FmsTask">
        update FmsTask
        <trim prefix="SET" suffixOverrides=",">
            <if test="taskNo != null">taskNo = #{taskNo},</if>
            <if test="planId != null">planId = #{planId},</if>
            <if test="blockId != null">blockId = #{blockId},</if>
            <if test="landId != null">landId = #{landId},</if>
            <if test="cropId != null">cropId = #{cropId},</if>
            <if test="taskCreatetype != null">taskCreatetype = #{taskCreatetype},</if>
            <if test="taskType != null">taskType = #{taskType},</if>
            <if test="taskStatus != null">taskStatus = #{taskStatus},</if>
            <if test="taskContent != null">taskContent = #{taskContent},</if>
            <if test="taskPlanstartdate != null">taskPlanstartdate = #{taskPlanstartdate},</if>
            <if test="taskPlanenddate != null">taskPlanenddate = #{taskPlanenddate},</if>
            <if test="taskAudittype != null">taskAudittype = #{taskAudittype},</if>
            <if test="taskManager != null">taskManager = #{taskManager},</if>
            <if test="taskManagername != null">taskManagername = #{taskManagername},</if>
            <if test="taskCreator != null">taskCreator = #{taskCreator},</if>
            <if test="taskCreateddate != null">taskCreateddate = #{taskCreateddate},</if>
            <if test="taskCompletedate != null">taskCompletedate = #{taskCompletedate},</if>
            <if test="taskModifieddate != null">taskModifieddate = #{taskModifieddate},</if>
            <if test="taskCompletereason != null">taskCompletereason = #{taskCompletereason}</if>
            <if test="taskAuditor != null">taskAuditor = #{taskAuditor}</if>
        </trim>
        where taskId = #{taskId}
    </update>

    <delete id="deleteFmsTaskByTaskId" parameterType="String">
        delete from FmsTask where taskId = #{taskId}
    </delete>

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

    <select id="selectTaskCountOfTaskStatus" parameterType="String" resultType="int">
        SELECT count(1) taskCount
        FROM FmsTask t
        WHERE t.taskStatus <![CDATA[ < ]]> 3
        <if test="landId != null and landId != ''">
            and t.landId  = #{landId}
        </if>
    </select>

    <select id="selectTaskListOfTaskStatus" parameterType="String" resultType="FmsTaskMessageResVo">
        select t.*, c.cropName, c.cropType, l.landName, b.blockName, p.planNo, p.planArea,tp.taskpatrolWay,tp.taskpatrolAddress
        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 FmsTaskpatrol tp on tp.taskId = t.taskId
        WHERE t.taskStatus <![CDATA[ < ]]> 3
        <if test="landId != null and landId != ''">
            and t.landId  = #{landId}
        </if>
    </select>
    
    <!-- 溯源统计页面使用：成本占比（农资，农机，人工费用） -->
	<select id="selectSumTaskrcdMoney" parameterType="SelectOfTssStatReqVo" resultType="map">
        select IFNULL(sum(tb1.taskrcdCost),0) rgSum
        ,IFNULL(sum(tb1.taskrcdAssetInvestment),0) nzSum
        ,IFNULL(sum(tb1.taskrcdMachineInvestment),0) njSum
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        where left(t.taskCompletedate,4)=#{startTime}
        <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
    </select>
    
	<!-- 溯源统计页面使用：年度分析-每月（人工） -->
	<select id="selectPayMonthByRg" parameterType="SelectOfTssStatReqVo" resultType="map">
		SELECT left(tb1.taskrcdSubmitdate,7) month
		,IFNULL(sum(tb1.taskrcdCost),0) money
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        where left(tb1.taskrcdSubmitdate,4)=#{startTime}
        <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
        GROUP BY left(tb1.taskrcdSubmitdate,7)
        order by tb1.taskrcdSubmitdate
    </select>
    
    <!-- 溯源统计页面使用：成本占比-明细（人工） -->
	<select id="selectPayByRg" parameterType="SelectOfTssStatReqVo" resultType="SpmPayOfTssStatResVo">
        select tb1.taskrcdCost money,
        tb1.taskrcdSubmitdate time,
        '用工费用' typeName
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        <where>
        	and left(t.taskCompletedate,4)=#{startTime}
        	and tb1.taskrcdCost>0
            <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
        </where>
        order by tb1.taskrcdSubmitdate desc
    </select>
    
    <!-- 溯源统计页面使用：年度分析-每月（农资） -->
	<select id="selectPayMonthByNz" parameterType="SelectOfTssStatReqVo" resultType="map">
		SELECT left(tb1.taskrcdSubmitdate,7) month
		,IFNULL(sum(tb1.taskrcdAssetInvestment),0) money
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        where left(tb1.taskrcdSubmitdate,4)=#{startTime}
        <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
        GROUP BY left(tb1.taskrcdSubmitdate,7)
        order by tb1.taskrcdSubmitdate
    </select>
    
    <!-- 溯源统计页面使用：成本占比-明细（农资） -->
	<select id="selectPayByNz" parameterType="SelectOfTssStatReqVo" resultType="SpmPayOfTssStatResVo">
        select tb1.taskrcdAssetInvestment money,
        tb1.taskrcdSubmitdate time,
        '农资费用' typeName
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        where left(t.taskCompletedate,4)=#{startTime} and tb1.taskrcdAssetInvestment>0
        <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
        order by tb1.taskrcdSubmitdate desc
    </select>
    
    <!-- 溯源统计页面使用：年度分析-每月（农机） -->
	<select id="selectPayMonthByNj" parameterType="SelectOfTssStatReqVo" resultType="map">
		SELECT left(tb1.taskrcdSubmitdate,7) month
		,IFNULL(sum(tb1.taskrcdMachineInvestment),0) money
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        where left(tb1.taskrcdSubmitdate,4)=#{startTime}
        <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
        GROUP BY left(tb1.taskrcdSubmitdate,7)
        order by tb1.taskrcdSubmitdate
    </select>
    
    <!-- 溯源统计页面使用：成本占比-明细（农机） -->
	<select id="selectPayByNj" parameterType="SelectOfTssStatReqVo" resultType="SpmPayOfTssStatResVo">
        select tb1.taskrcdMachineInvestment money,
        tb1.taskrcdSubmitdate time,
        '农机费用' typeName
        from FmsTask t
        left join
        (select tr.* from FmsTaskrcd tr where tr.taskrcdAuditstatus = '2') tb1
        on tb1.taskId = t.taskId
        <where>
        	and left(t.taskCompletedate,4)=#{startTime}
        	and tb1.taskrcdMachineInvestment>0
            <if test="landId != null  and landId != ''"> and t.landId = #{landId}</if>
        </where>
        order by tb1.taskrcdSubmitdate desc
    </select>
</mapper>