<?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.uml.tech.njMapper.FarmWorkNjTestMapper">

    <insert id="insertWorkInfo">
    	replace into farm_work_copy0615 (unitPrice, GPSDateTime, serialNumber,
            jobArea, jobStartLat, TIME,
            jobStartTime, DATE_TYPE, jobWidth,
            unit, currentBlockNumber, isData,
            jobStartLon, createTime, vehicleTypeCode,
            jobType, brand, farmDataType,
            jobEquipment, did, jobEndTime,
            insertTime, GPSLat, GPSLon,
            jobTime, id, lk, positionsSum,
            centerLat, centerLon, avgDepth,
            avgPercentOfPass, standardDepth,
            averageSprayDose, totalDose, flowCoefficient,
            avgFertilizationRate, fertilizationTotal,
            fertilizerDischargeCoefficient, averageYieldPerMu,
            totalPlotYield, province,
            city, district, sematicDescription,
            totalPoints, wkt_poly, dryingWeight ,absoluteJobTime ,bundleWeight,bundles,
            jobMileage,avgStubbleHeight,standardStubbleHeight,jobMileageType
            )
        values (#{unitPrice,jdbcType=DOUBLE}, #{GPSDateTime,jdbcType=TIMESTAMP}, #{serialNumber,jdbcType=VARCHAR},
            #{jobArea,jdbcType=DOUBLE}, #{jobStartLat,jdbcType=DOUBLE}, #{TIME,jdbcType=TIMESTAMP},
            #{jobStartTime,jdbcType=TIMESTAMP}, #{DATE_TYPE,jdbcType=VARCHAR}, #{jobWidth,jdbcType=DOUBLE},
            #{unit,jdbcType=VARCHAR}, #{currentBlockNumber,jdbcType=VARCHAR}, #{isData,jdbcType=VARCHAR},
            #{jobStartLon,jdbcType=DOUBLE}, #{createTime,jdbcType=TIMESTAMP}, #{vehicleTypeCode,jdbcType=VARCHAR},
            #{jobType,jdbcType=INTEGER}, #{brand,jdbcType=INTEGER}, #{farmDataType,jdbcType=VARCHAR},
            #{jobEquipment,jdbcType=INTEGER}, #{did,jdbcType=VARCHAR}, #{jobEndTime,jdbcType=TIMESTAMP},
            DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'), #{GPSLat,jdbcType=DOUBLE}, #{GPSLon,jdbcType=DOUBLE},
            #{jobTime,jdbcType=INTEGER}, #{uuid,jdbcType=VARCHAR}, #{lk,jdbcType=LONGVARBINARY},#{positionsSum,jdbcType=INTEGER},
            #{centerLat,jdbcType=DOUBLE}, #{centerLon,jdbcType=DOUBLE}, #{avgDepth,jdbcType=DOUBLE},
            #{avgPercentOfPass,jdbcType=DOUBLE}, #{standardDepth,jdbcType=DOUBLE},
            #{averageSprayDose,jdbcType=DOUBLE}, #{totalDose,jdbcType=DOUBLE}, #{flowCoefficient,jdbcType=VARCHAR},
            #{avgFertilizationRate,jdbcType=DOUBLE}, #{fertilizationTotal,jdbcType=DOUBLE},
            #{fertilizerDischargeCoefficient,jdbcType=VARCHAR}, #{averageYieldPerMu,jdbcType=DOUBLE},
            #{totalPlotYield,jdbcType=DOUBLE}, #{province,jdbcType=VARCHAR},
            #{city,jdbcType=VARCHAR}, #{district,jdbcType=VARCHAR}, #{sematicDescription,jdbcType=VARCHAR},
            #{totalPoints,jdbcType=INTEGER}, #{wktPoly,jdbcType=LONGVARBINARY},
			#{dryingWeight,jdbcType=DOUBLE},#{absoluteJobTime,jdbcType=INTEGER},#{bundleWeight,jdbcType=DOUBLE},#{bundles,jdbcType=INTEGER},
			#{jobMileage,jdbcType=DOUBLE},#{avgStubbleHeight,jdbcType=DOUBLE},#{standardStubbleHeight,jdbcType=DOUBLE},#{jobMileageType,jdbcType=TINYINT}
            )
    </insert>
    <insert id="insertSplitBatchWorkInfo">
        insert into farm_work_split_copy0615 (unitPrice, GPSDateTime, serialNumber, jobArea, jobStartLat, TIME, jobStartTime,
        DATE_TYPE, jobWidth, unit, currentBlockNumber,
        jobStartLon, vehicleTypeCode, jobType, brand, jobEquipment, did, jobEndTime,
        insertTime, jobTime, centerLat, centerLon, lk, positionsSum, id, avgDepth, avgPercentOfPass,
        standardDepth, averageSprayDose, totalDose, flowCoefficient, avgFertilizationRate,
        fertilizationTotal, fertilizerDischargeCoefficient, averageYieldPerMu, totalPlotYield,
        province, city, district, sematicDescription, totalPoints, wkt_poly , dryingWeight, absoluteJobTime, bundleWeight,
        bundles, jobMileage, avgStubbleHeight, standardStubbleHeight, jobMileageType, parentRowKey)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.unitPrice}, #{item.GPSDateTime}, #{item.serialNumber}, #{item.jobArea}, #{item.jobStartLat},
            #{item.TIME}, #{item.jobStartTime}, #{item.DATE_TYPE}, #{item.jobWidth}, #{item.unit},
            #{item.currentBlockNumber},
            #{item.jobStartLon}, #{item.vehicleTypeCode}, #{item.jobType},
            #{item.brand}, #{item.jobEquipment}, #{item.did}, #{item.jobEndTime},
            DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'), #{item.jobTime}, #{item.centerLat}, #{item.centerLon}, #{item.lk}, #{item.positionsSum},
            #{item.uuid}, #{item.avgDepth}, #{item.avgPercentOfPass},
            #{item.standardDepth}, #{item.averageSprayDose}, #{item.totalDose}, #{item.flowCoefficient},
            #{item.avgFertilizationRate}, #{item.fertilizationTotal},
            #{item.fertilizerDischargeCoefficient}, #{item.averageYieldPerMu}, #{item.totalPlotYield},
            #{item.province}, #{item.city}, #{item.district},
            #{item.sematicDescription},#{item.totalPoints},#{item.wktPoly}, #{item.dryingWeight}, #{item.absoluteJobTime},#{item.bundleWeight},#{item.bundles},
            #{item.jobMileage},#{item.avgStubbleHeight},#{item.standardStubbleHeight},#{item.jobMileageType}, #{item.parentRowKey})
        </foreach>
    </insert>

    <update id="updateWorkInfo" parameterType="com.uml.tech.bean.FarmWork">
        update farm_work_copy0615
        <set>
            <if test="jobArea !=null">
                jobArea = #{jobArea},
            </if>
            <if test="jobType !=null">
                jobType = #{jobType},
            </if>
            <if test="jobWidth !=null">
                jobWidth = #{jobWidth},
            </if>
            <if test="jobEquipment !=null">
                jobEquipment = #{jobEquipment},
            </if>
            <!-- 修改平均深度，平均合格率 -->
            <if test="avgDepth !=null">
                avgDepth = #{avgDepth},
            </if>
            <if test="avgPercentOfPass !=null">
                avgPercentOfPass = #{avgPercentOfPass},
            </if>
            <if test="lk !=null">
                lk = #{lk},
            </if>
            <if test="id !=null">
                id = #{id},
            </if>
            <if test="centerLat !=null">
                centerLat = #{centerLat},
            </if>
            <if test="centerLon !=null">
                centerLon = #{centerLon},
            </if>
            <if test="totalPoints !=null">
                totalPoints = #{totalPoints},
            </if>
            <if test="wkt !=null">
                wkt_poly = #{wkt},
            </if>
            <!-- 修改烘干重量 -->
            <if test="dryingWeight !=null">
                dryingWeight = #{dryingWeight},
            </if>
            <!-- 修改打捆重量 -->
            <if test="bundleWeight !=null">
                bundleWeight = #{bundleWeight},
            </if>
            <!-- 修改打捆个数 -->
            <if test="bundles !=null">
                bundles = #{bundles},
            </if>
            <!-- 修改绝对作业时间 -->
            <if test="absoluteJobTime !=null">
                absoluteJobTime = #{absoluteJobTime},
            </if>
            <!-- 修改作业里程 -->
            <if test="jobMileage !=null">
                jobMileage = #{jobMileage},
            </if>
            <!-- 平均留茬高度 -->
            <if test="avgStubbleHeight !=null">
                avgStubbleHeight = #{avgStubbleHeight},
            </if>
            <!-- 标准留茬高度 -->
            <if test="standardStubbleHeight !=null">
                standardStubbleHeight = #{standardStubbleHeight},
            </if>
        </set>
        <where>
            did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
        </where>
    </update>

    <update id="updateWorkInfoWithInsertTime">
        update farm_work_copy0615
        <set>
            <if test="jobArea !=null">
                jobArea = #{jobArea},
            </if>
            <if test="jobType !=null">
                jobType = #{jobType},
            </if>
            <if test="jobWidth !=null">
                jobWidth = #{jobWidth},
            </if>
            <if test="jobEquipment !=null">
                jobEquipment = #{jobEquipment},
            </if>
            <!-- 修改平均深度，平均合格率 -->
            <if test="avgDepth !=null">
                avgDepth = #{avgDepth},
            </if>
            <if test="avgPercentOfPass !=null">
                avgPercentOfPass = #{avgPercentOfPass},
            </if>
            <if test="lk !=null">
                lk = #{lk},
            </if>
            <if test="id !=null">
                id = #{id},
            </if>
            <if test="centerLat !=null">
                centerLat = #{centerLat},
            </if>
            <if test="centerLon !=null">
                centerLon = #{centerLon},
            </if>
            <if test="totalPoints !=null">
                totalPoints = #{totalPoints},
            </if>
            <if test="wkt !=null">
                wkt_poly = #{wkt},
            </if>
            <!-- 修改烘干重量 -->
            <if test="dryingWeight !=null">
                dryingWeight = #{dryingWeight},
            </if>
            <!-- 修改打捆重量 -->
            <if test="bundleWeight !=null">
                bundleWeight = #{bundleWeight},
            </if>
            <!-- 修改打捆个数 -->
            <if test="bundles !=null">
                bundles = #{bundles},
            </if>
            <!-- 修改绝对作业时间 -->
            <if test="absoluteJobTime !=null">
                absoluteJobTime = #{absoluteJobTime},
            </if>
            <!-- 修改作业里程 -->
            <if test="jobMileage !=null">
                jobMileage = #{jobMileage},
            </if>
            <!-- 平均留茬高度 -->
            <if test="avgStubbleHeight !=null">
                avgStubbleHeight = #{avgStubbleHeight},
            </if>
            <!-- 标准留茬高度 -->
            <if test="standardStubbleHeight !=null">
                standardStubbleHeight = #{standardStubbleHeight},
            </if>
            insertTime = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s')
        </set>
        <where>
            did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
        </where>
    </update>
    <update id="updateFarmWorkFixFlag">
        update farm_work_copy0615
        <set>
            <!-- 有效作业面积 -->
            <if test="effectiveJobArea !=null">
                effectiveJobArea = #{effectiveJobArea},
            </if>
            <!-- 修改地块标记 -->
            <if test="fixFlag !=null">
                fixFlag = #{fixFlag},
            </if>
            <!-- 子地块外键parentRowKey -->
            <if test="parentRowKey !=null">
                parentRowKey = #{parentRowKey},
            </if>
            insertTime = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s')
        </set>
        <where>
            did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
        </where>
    </update>

    <delete id="delNjWork">
    	delete from farm_work_copy0615 where did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
    </delete>
    <delete id="deleteOldSplitData">
        delete from farm_work_split_coyp0615 where parentRowKey = #{parentRowKey}
    </delete>
    <select id="selectEntity" resultType="com.uml.tech.bean.NjWorkInfo">
		select did, jobEndTime, jobStartTime, totalPoints from farm_work where did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime};
	</select>
    <select id="selectTotalStatus" resultType="com.uml.tech.bean.UpdateBean">
        SELECT did, sum(jobArea) jobArea, sum(if(absoluteJobTime is null, jobTime, absoluteJobTime)) jobTime,
        sum(avgDepth * if(positionsSum is null, jobTime, positionsSum)) totalDepth,
        sum(avgPercentOfPass * if(positionsSum is null, jobTime, positionsSum)) totalPercentOfPass, sum(positionsSum)
        totalPositionsSum,
        sum(dryingWeight) totalDryingWeight,sum(bundleWeight) totalBundleWeight, sum(bundles) totalBundles,
        sum(totalDose) totalDose,
        sum(fertilizationTotal) totalFertilization,sum(totalPlotYield) totalPlotYield,sum(jobMileage) totalJobMileage
        from farm_work_copy0615
        WHERE did = #{did}
        <if test="startDay !=null">
            and <![CDATA[ jobEndTime > ${startDay} ]]>
        </if>
        <if test="endDay !=null">
            and <![CDATA[ jobEndTime < ${endDay} ]]>
        </if>
    </select>
    <select id="selectHistoryData" resultType="com.uml.tech.bean.FarmWork">
		SELECT
		did,jobStartTime,jobEndTime
		FROM
		farm_work_copy0615
		WHERE
		did = #{did}
		AND
		<![CDATA[(( jobStartTime >= #{jobStartTime} AND jobStartTime <= #{jobEndTime})
		OR ( jobEndTime >= #{jobStartTime} AND jobEndTime <= #{jobEndTime} ))
        ]]>
    </select>
    <select id="selectTotalPoints" resultType="java.lang.Integer">
		select totalPoints from farm_work_copy0615 where did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
	</select>
    <select id="selectJobMileageType" resultType="java.lang.Integer">
		select jobMileageType from farm_work_copy0615 where did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
	</select>
    <select id="selectRepeat" resultType="java.lang.Boolean">
		SELECT count(1) > 0  from farm_work_copy0615 where did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
	</select>
    <select id="selectJobArea" resultType="java.lang.Double">
        SELECT jobArea  from farm_work_copy0615 where did = #{did} and jobEndTime = #{jobEndTime} and jobStartTime = #{jobStartTime}
    </select>
    <select id="selectExistSplitData" resultType="com.uml.tech.bean.NjWorkInfo">
        select did, DATE_FORMAT(jobEndTime,'%Y%m%d%H%i%s') as jobEndTime, id as uuid from farm_work_split_copy0615 where parentRowKey = #{parentRowKey}
    </select>

    <select id="selectAutoSplitInfo" resultType="com.uml.tech.bean.AutoSplitInfo">
		select * from farm_work_auto_split_info where id = #{id}
	</select>
    <select id="selectNotCalcCount" resultType="java.lang.Integer">
		select count(*) from farm_work_auto_split_info where parentRowKey = #{parentRowKey} and jobArea is null
	</select>
    <select id="selectAllSplitInfo" resultType="com.uml.tech.bean.AutoSplitInfo">
		select * from farm_work_auto_split_info where parentRowKey = #{parentRowKey}
	</select>
    <update id="updateAutoSplitInfo">
        update farm_work_auto_split_info set jobArea = #{jobArea}, jobStartLat = #{jobStartLat}, jobStartLon=#{jobStartLon}, status=2 where id = #{id}
    </update>
</mapper>
