package com.huimv.environment.repo;

import com.huimv.environment.entity.SjHkSd;
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 SjHkSdRepo extends JpaRepository<SjHkSd, Void>, JpaSpecificationExecutor<SjHkSd> {
    @Query(nativeQuery = true , value = "SELECT ID,LQID,CGQID,DQSD,CJSJ,SCSJ FROM sj_hk_sd WHERE id IN( SELECT MAX(id) FROM sj_hk_sd WHERE lqid IN(?1) AND DATE_FORMAT(CJSJ,'%Y-%m-%d')=?2  GROUP BY lqid)")
    List<SjHkSd> getLastSd(List lqidList, String date);

    @Query(nativeQuery = true , value = "SELECT id,CGQID,lqid,dqsd,CJSJ,SCSJ FROM sj_hk_sd WHERE lqid=?1 AND DATE_FORMAT(CJSJ,'%Y-%m-%d')=DATE_FORMAT(?2,'%Y-%m-%d') ORDER BY cjsj DESC LIMIT 0,1")
    SjHkSd getLastSd(Integer lqid, String todayDate);

    @Query(nativeQuery = true , value = "SELECT id,CGQID,lqid,dqsd,SCSJ,CJSJ FROM sj_hk_sd WHERE lqid=?1 AND DATE_FORMAT(CJSJ,'%Y-%m-%d %H:%i:%s')>=DATE_FORMAT(?2,'%Y-%m-%d %H:%i:%s') ORDER BY cjsj ASC ")
    List<SjHkSd> getHumidityOnToday(Integer lqid, String beforeDateTime);

    @Query(nativeQuery = true , value = "SELECT id,CGQID,lqid,dqsd,SCSJ,CJSJ FROM sj_hk_sd WHERE lqid=?1 ORDER BY cjsj DESC limit 10")
    List<SjHkSd> getHumidity(Integer lqid, String beforeDateTime);

    List<SjHkSd> findAllByLqidAndScsjBetween(String LQID, Date startTime, Date endTime);
}