package com.huimv.produce.produce.repo;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
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;

public interface ProdWeightRepo extends JpaRepository<ProdWeightEntity, Integer>, JpaSpecificationExecutor<ProdWeightEntity> {
    //
    @Query(nativeQuery = true,value = "SELECT id,DATE_FORMAT(add_time,'%Y-%m-%d %H:%i:%S') AS add_time,gross_weight,tare_weight,net_weight,farm_id,batch_id FROM prod_weight WHERE farm_id=?1 AND DATE_FORMAT(add_time,'%Y-%m-%d')>=DATE_FORMAT(?2,'%Y-%m-%d') AND DATE_FORMAT(add_time,'%Y-%m-%d')<=DATE_FORMAT(?3,'%Y-%m-%d') ORDER BY id DESC",
            countQuery = "SELECT COUNT(*) FROM prod_weight WHERE farm_id=?1 AND DATE_FORMAT(add_time,'%Y-%m-%d')>=DATE_FORMAT(?2,'%Y-%m-%d') AND DATE_FORMAT(add_time,'%Y-%m-%d')<=DATE_FORMAT(?3,'%Y-%m-%d')")
    Page<ProdWeightEntity> findWeightByFarmIdAndStartDateAndEndDate(Integer farmId, Date startDate, Date endDate, Pageable pageable);

}