<?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.huimv.modular.eartagdata.mapper.EartagDataMapper">

    <select id="countByTime" resultType="com.huimv.modular.collectorregister.EartagDataCountVo">
         SELECT
          DATE_FORMAT(`time`,"%y-%m-%d %H") count_time,COUNT(*) upload_count, CAST(AVG(env_temp) AS DECIMAL(10,2)) envTemp
	        FROM `eartag_data`
	        ${ew.customSqlSegment}
    </select>


    <select id="countByDay" resultType="com.huimv.modular.collectorregister.EartagDataCountVo">
        SELECT
          DATE_FORMAT(`time`,"%y-%m-%d") count_time,COUNT(*) upload_count, CAST(AVG(env_temp) AS DECIMAL(10,2)) envTemp
          FROM `eartag_data`
          ${ew.customSqlSegment}
    </select>



    <select id="findByCondition" resultType="com.huimv.modular.eartagdata.entity.EartagData">
        SELECT * FROM eartag_data ed1 JOIN (SELECT MAX(id) id FROM eartag_data ed2 ${ew.customSqlSegment}) b ON ed1.id = b.id
    </select>



    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.huimv.modular.eartagdata.entity.vo.EartagDataVo" id="eartagDataIndex">
        <result property="id" column="id"/>
        <result property="eartagNo" column="eartag_no"/>
        <result property="deviceCode" column="device_code"/>
        <result property="time" column="time"/>
        <result property="earTemp" column="ear_temp"/>
        <result property="bat" column="bat"/>
        <result property="envTemp" column="env_temp"/>
        <result property="rssi" column="rssi"/>
        <result property="penName" column="pen_name"/>
        <result property="unitName" column="unit_name"/>
        <result property="stage" column="stage"/>
        <result property="registeCode" column="registe_code"/>
        <result property="exercise" column="exercise"/>
        <result property="countTimes" column="count_times"/>
        <result property="earTempStatus" column="ear_temp_status"/>
    </resultMap>



    <!--首页数据的接口完成-->
    <select id="pageIndex"
            resultMap="eartagDataIndex"
            parameterType="com.huimv.modular.eartagdata.entity.vo.EartagDataVo" >
        SELECT   DISTINCT ed1.id ,  ed1.eartag_no  ,ed1.device_code ,ed1.time, ed1.ear_temp   ,ed1.bat  ,ed1.exercise ,

        ed1.env_temp   ,ed1.rssi  ,co.pen_name  ,co.unit_name   ,co.stage   ,co.registe_code ,ear.count_times,ear.ear_temp_status

        FROM   eartag_data ed1    JOIN (

        SELECT MAX(id) id  FROM   eartag_data ed2
        WHERE

        1=1


        <if test="eartagParamVo.eartagNo != null">

             AND  ed2.eartag_no   like  CONCAT('%',#{eartagParamVo.eartagNo},'%')
        </if>


        <if test="eartagParamVo.bat != null">
            AND  ed2.bat  <![CDATA[ <= ]]>  #{eartagParamVo.bat}
        </if>


        <if test="eartagParamVo.starTime != null">
            AND ed2.time <![CDATA[ >= ]]> #{eartagParamVo.starTime}
            AND ed2.time <![CDATA[ <= ]]> #{eartagParamVo.endTime}
        </if>

        GROUP BY ed2.eartag_no   )   b ON ed1.id = b.id

        inner JOIN  `collector_register`  co  ON   ed1.device_code=co.device_code

        left  JOIN     eartag_register   ear  ON    ear.eartag_no = ed1.eartag_no

        where 1=1


        <if test="eartagParamVo.stage != null">
            AND co.stage = #{eartagParamVo.stage}
        </if>


        <if test="eartagParamVo.penNo != null">
            AND co.pigpen_id = #{eartagParamVo.penNo}
        </if>


        <if test="eartagParamVo.unitNo != null">
            AND co.unit_id = #{eartagParamVo.unitNo}
        </if>


        <if test="eartagParamVo.earTempStatus != null">
            AND   ear.ear_temp_status = #{eartagParamVo.earTempStatus}
        </if>


        <if test="eartagParamVo.deviceCode != null">
            AND co.device_code = #{eartagParamVo.deviceCode}
        </if>

        ORDER  BY ed1.`time`  DESC

    </select>






</mapper>
