package com.huimv.receiver.cloud.dao.repo;

import com.huimv.receiver.cloud.dao.entity.EnvHourEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface EnvHourRepo extends JpaRepository<EnvHourEntity, Integer>, JpaSpecificationExecutor<EnvHourEntity> {

    @Query(nativeQuery = true,value ="SELECT AVG(hxxyl_pj) AS hxxyl,AVG(ad_pj) AS ad,AVG(ph_pj) AS ph,AVG(ws_pj) AS ws FROM env_hour WHERE farm_id=?1 AND DATE_FORMAT(datetime,'%m-%d-%Y')=DATE_FORMAT(?2,'%m-%d-%Y')")
    List<Object[]> findByFarmIdAndDate_old1(Integer farmId, String todayDateText);

    //
    @Query(nativeQuery = true,value ="SELECT farm_id,datetime, hxxyl_pj AS hxxyl,ad_pj AS ad,ph_pj AS ph,ws_pj AS ws FROM env_hour WHERE farm_id=?1 AND DATE_FORMAT(datetime,'%Y-%m-%d')=DATE_FORMAT(?2,'%Y-%m-%d')")
    List<Object[]> findByFarmIdAndDate(Integer farmId, String todayDateText);

    @Transactional
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query(nativeQuery = true,value ="DELETE FROM env_day WHERE farm_id=?1 AND DATE_FORMAT(add_date,'%Y-%m-%d')=DATE_FORMAT(?2,'%Y-%m-%d')")
    void removeByFarmIdAndDate(Integer farmId, String todayDateText);
}