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



    <select id="getEngineWorkTime" resultType="uml.tech.bigdata.bean.JobTimeBean">
        select tbname,
               CAST((last(`4163`)-first(`4163`)) as INT) as engineWorkTime
        from frequent.v_c
        where `3014` between #{dayStart}
            and #{dayEnd}
          and (params3 = 1 or params3 = 2)
          and `4163` > 0
        group by tbname
    </select>

    <select id="getRunTime" resultType="uml.tech.bigdata.bean.JobTimeBean">
        select tbname,
            last(`4592`)-first(`4592`) as runTime
        from frequent.v_c
        where `3014` between #{dayStart}
            and #{dayEnd}
          and (params3 = 1 or params3 = 2)
          and `4592` > 0
        group by tbname
    </select>


    <select id="getLastLonLat" resultType="uml.tech.bigdata.bean.LonLatBean">
        select tbname as `tbName`,
            last (`3014`) as `lastGpsTime`,
            last (`2602`) as lastLon,
            last (`2603`) as lastLat
        from frequent.v_c
        where `3014` between #{dayStart}
          and #{dayEnd}
          and `2601` = 0
          and
            (params3 = 1
           or params3 = 2)
          and vin is not null
        group by tbname
    </select>

    <select id="getRunStateByDid" resultType="java.lang.Integer">
        select count(*) as runState
        from frequent.v_c
        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="getCtByDay" resultType="integer">
        select count(*)
        from
        (
        select 1
        from frequent.${tbName}
        where
        `4617` is not null
        and (`4163` is not null or `4592` is not null)
        and
        (params3 = 1
        or params3 = 2)
        <if test="dayStart != '' and dayStart != null">
            and `3014` between #{dayStart}
            and #{dayEnd}
        </if>
        limit 40
        )
    </select>

    <select id="getFirstByDay" resultType="string">
        select `3014`
        from frequent.${tbName}
        where
        `4617` is not null
        and (`4163` is not null or `4592` is not null)
        and
        (params3 = 1
        or params3 = 2)
        <if test="dayStart != '' and dayStart != null">
            and `3014` between #{dayStart}
            and #{dayEnd}
        </if>
        limit 1
    </select>

    <select id="getLtCt" resultType="integer">
        select count(*)
        from frequent.${tbName}
        where `4617` is not null
          and (`4163` is not null or `4592` is not null)
          and (params3 = 1
            or params3 = 2)
          and `3014` &lt; #{dayStart}
    </select>

    <select id="getFirst" resultType="string">
        select `3014`
        from frequent.${tbName}
        where `4617` is not null
          and (`4163` is not null or `4592` is not null)
          and (params3 = 1
            or params3 = 2)
          and `3014` &lt; #{dayStart}
        limit 1
    </select>

    <select id="getAlarmStatus" resultType="integer">
        select count(*)
        from frequent.${tbname}
        where `3014` between #{dayStart} and #{dayEnd}
          and protocol match '.*,2810:.*'
    </select>


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

</mapper>