huimv.ql.farm.v1/huimv-ql-farm/huimv-ql-environment/src/main/java/com/huimv/environment/repo/DeviceUpDataEntityRepo.java

package com.huimv.environment.repo;

import com.huimv.environment.entity.DeviceUpDataEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface DeviceUpDataEntityRepo extends JpaRepository<DeviceUpDataEntity, Integer>, JpaSpecificationExecutor<DeviceUpDataEntity> {


    @Query(nativeQuery = true,value = "select * from device_up_data where device_id =?1 ORDER by id DESC limit 1")
    DeviceUpDataEntity findByDeviceId(String deviceId);


//    List<DeviceUpDataEntity> findAllByTimestampBetweenAndDeviceId(Date startTime, Date endTime, String deviceId);

    @Query(nativeQuery = true,value = "SELECT * FROM device_up_data WHERE DATE_FORMAT(timestamp ,'%Y-%m-%d')=DATE_FORMAT(?1,'%Y-%m-%d') and device_id = ?2")
    List<DeviceUpDataEntity> findAllByDeviceIdOrderByIdDesc(String today, String deviceId);

    @Query(nativeQuery = true,value = "SELECT * FROM (SELECT * FROM device_up_data WHERE data_temp_convert < ?1 OR data_temp_convert > ?2 )  a\n" +
            "WHERE DATE_FORMAT(TIMESTAMP,'%Y-%m-%d')=DATE_FORMAT(?3,'%Y-%m-%d')  ")
    List<DeviceUpDataEntity> findAllByTimestampBetween(Double minTemp,Double maxTemp, String today );

    @Query(nativeQuery = true,value = "SELECT  any_value(`timestamp`),device_id FROM `device_up_data` WHERE DATE_FORMAT(TIMESTAMP,'%Y-%m-%d')=DATE_FORMAT(?1,'%Y-%m-%d') GROUP BY device_id")
    List findAllByIdAfter(String today);



}