<?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="uml.tech.bigdata.tdengineMapper.can.FarmCanMapper">

    <resultMap id="BaseResultMap" type="uml.tech.bigdata.bean.FarmExtraBean">
        <id column="day" jdbcType="TIMESTAMP" property="day"/>
        <result column="job_area" jdbcType="DOUBLE" property="jobArea"/>
        <result column="mileage" jdbcType="DOUBLE" property="mileage"/>
        <result column="job_time" jdbcType="DOUBLE" property="jobTime"/>
        <result column="engine_work_time" jdbcType="DOUBLE" property="engineWorkTime"/>
        <result column="fuel_consumption" jdbcType="DOUBLE" property="totalFuelConsumption"/>
        <result column="run_time" jdbcType="DOUBLE" property="runTime"/>
        <result column="power_consumption" jdbcType="DOUBLE" property="totalPowerConsumption"/>
        <result column="realtime_ct" jdbcType="INTEGER" property="totalRealTime"/>
        <result column="history_ct" jdbcType="INTEGER" property="totalHistory"/>
        <result column="a_ct" jdbcType="INTEGER" property="totalA"/>
        <result column="b_ct" jdbcType="INTEGER" property="totalB"/>
        <result column="c_ct" jdbcType="INTEGER" property="totalC"/>
        <result column="d_ct" jdbcType="INTEGER" property="totalD"/>
        <result column="e_ct" jdbcType="INTEGER" property="totalE"/>
        <result column="f_ct" jdbcType="INTEGER" property="totalF"/>
        <result column="g_ct" jdbcType="INTEGER" property="totalG"/>
        <result column="did" jdbcType="INTEGER" property="did"/>
        <result column="vin" jdbcType="INTEGER" property="vin"/>
        <result column="lpn" jdbcType="INTEGER" property="lpn"/>
        <result column="tbname" jdbcType="VARCHAR" property="tbname"/>
        <result column="group_id" jdbcType="VARCHAR" property="groupId"/>
        <result column="group_path" jdbcType="VARCHAR" property="groupPath"/>
        <result column="vehicle_type_id" jdbcType="VARCHAR" property="vehicleTypeId"/>
        <result column="last_gps_time" jdbcType="VARCHAR" property="lastGpsTime"/>
        <result column="lon" jdbcType="DOUBLE" property="lastLon"/>
        <result column="lat" jdbcType="DOUBLE" property="lastLat"/>

    </resultMap>

    <select id="queryByDay" resultMap="BaseResultMap">
        select tbname,
               did,
               vin,
               `group_id`,
               `group_path`,
               `vehicle_type_id`,
               spread(`4181`) as job_area,
               spread(`2205`) as mileage,
               spread(`4023`) as fuel_consumption,
               spread(`4592`) as run_time,
               spread(`4617`) as power_consumption,
               spread(`4118`) as job_time,
            last (`4163`)- first (`4163`) as engine_work_time,
            count (`4101`) as a_ct,
            count (`4108`) as b_ct,
            count (`4163`) as c_ct,
            count (`4174`) as d_ct,
            count (`4175`) as e_ct,
            count (`4319`) as f_ct,
            count (`2205`) as g_ct,
            last (`3014`) as last_gps_time,
            last (`2602`) as lon,
            last (`2603`) as lat
        from vehicle_can.t
        where `3014` between #{dayStart}
          and #{dayEnd}
          and (params3 = 1
           or params3 = 2)
        group by tbname,did, vin,
            group_id,
            group_path,vehicle_type_id
    </select>

    <select id="getEngineWorkTime" resultType="uml.tech.bigdata.bean.VinCountBean">
        select tbname,
               max(`4163`) - min(`4163`) as ct
        from vehicle_can.t
        where `3014` between #{dayStart}
            and #{dayEnd}
          and (params3 = 1 or params3 = 2)
          and `4163` > 0
        group by tbname
    </select>
    <!--    <select id="getNormalCtByDid" resultType="int">-->
    <!--        select count(1) as normal_ct-->
    <!--        from stb_can-->
    <!--        where t_did = #{did}-->
    <!--          and c_3014 between #{dayStart} and #{dayEnd}-->
    <!--          and (-->
    <!--            c_4101  is not null or-->
    <!--            c_4108 is not null or-->
    <!--            c_4163 is not null or-->
    <!--            c_4174 is not null or-->
    <!--            c_4175 is not null or-->
    <!--            c_4319 is not null or-->
    <!--            c_2205 is not null-->
    <!--            )-->
    <!--    </select>-->

    <select id="getRunStateByDid" resultType="java.lang.Integer">
        select count(*) as runState
        from vehicle_can.t
        where vin = #{vin}
          and `3014` between #{dayStart} and #{dayEnd}
          and `2602` > 0
          and `2603` > 0
    </select>

    <select id="getSumEngineWorkTimeById" resultType="java.lang.Double">
        select sum(i)
        from (select diff(`4163`, 1) as i
              from #{tbname}
              where `3014` between #{dayStart} and #{dayEnd}
                and `4163` > 0)
        where i &lt; 1000
    </select>

    <select id="getAllRunState" resultType="uml.tech.bigdata.bean.VinCountBean">
        select tbname, count(*) as ct
        from vehicle_can.t
        where `3014`
            between #{dayStart} and #{dayEnd}
          and `2602` > 0
          and `2603` > 0
        group by tbname
    </select>

    <select id="getParams3Count" resultType="uml.tech.bigdata.bean.VinCountBean">
        select tbname, count(*) as ct
        from vehicle_can.t
        where `3014`
        between #{dayStart} and #{dayEnd}
        <if test="params3 != '' and params3 != null">
            and `params3` = #{params3}
        </if>
        group by tbname
    </select>

    <select id="getAllNormalCt" resultType="uml.tech.bigdata.bean.VinCountBean">
        select tbname, count(*) as ct
        from vehicle_can.t
        where `3014` between #{dayStart} and #{dayEnd}
          and (`4101` is not null
            or `4108` is not null
            or `4163` is not null
            or `4174` is not null
            or `4175` is not null
            or `4319` is not null
            or `2205` is not null
            )
        group by tbname;
    </select>


    <insert id="insertBatch" parameterType="java.util.List">
        insert into
        <foreach collection="list" index="index" item="item" separator=" ">
            nrmv_vehicle.`${item.tableName}` (c_3014, c_protocol,c_params3) using stb_protocol tags(#{item.tag})
            values (#{item.gpsTime},#{item.protocol},#{item.params3})
        </foreach>
    </insert>


    <select id="selectFarmWork" resultType="uml.tech.bigdata.bean.FarmWork">
        SELECT `vin`,
               `jobArea`,
               `absoluteJobTime` + `jobTime`                                        as `jobTime`,
               `avgDepth` * `positionsSum` + `avgDepth` * `jobTime`                 as `totalDepth`,
               `avgPercentOfPass` * `positionsSum` + `avgPercentOfPass` * `jobTime` as `totalPercentOfPass`,
               `positionsSum`                                                       as `totalPositionsSum`,
               `dryingWeight`                                                       as `totalDryingWeight`,
               `bundleWeight`                                                       as `totalBundleWeight`,
               `bundles`                                                            as `totalBundles`,
               `totalDose`                                                          as `totalDose`,
               `fertilizationTotal`                                                 as `totalFertilization`,
               `totalPlotYield`                                                     as `totalPlotYield`,
               `jobMileage`                                                         as `totalJobMileage`,
               `jobMileageType`,
               `GPSDateTime`                                                        as `lastGpsTime`
        from nrmv_job.work
        where `jobStartTime` between #{dayStart} and #{dayEnd}

    </select>


    <select id="selectTbName" resultType="string">
        select tbname
        from nrmv_protocol.log
    </select>

</mapper>