package com.huimv.admin.repo.secondary;

import com.huimv.admin.domain.secondary.MachineControlEntity;
import com.huimv.admin.vo.MachineControlVO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @Author yinhao
 * @Date 2021/4/1 18:12
 * @Description 料控管理Dao
 */
public interface MachineControlRepository extends JpaRepository<MachineControlEntity, Integer>, JpaSpecificationExecutor<MachineControlEntity> {

//    MachineControlEntity findByJqid(String jqId);

    @Query(nativeQuery = true,value = "SELECT new com.huimv.admin.vo.MachineControlVO(lk.id,ZSMC,lk.jqid,no1_dlstart_time1,no1_dlstart_time2,no1_dlstart_time3,no1_dlstart_time4,no2_dlstart_time1,no2_dlstart_time2,no2_dlstart_time3,no2_dlstart_time4,no3_dlstart_time1,no3_dlstart_time2,no3_dlstart_time3,no3_dlstart_time4,no4_dlstart_time1,no4_dlstart_time2,no4_dlstart_time3,no4_dlstart_time4) FROM da_zsda zs JOIN da_jqda jq ON zs.`ID`=jq.`ZSID` JOIN da_lkpz lk ON jq.`JQID`=lk.`jqid` where zs.ZSMC like %?3% limit ?1,?2")
    List<MachineControlVO> getMachineControlInfo(Integer start,Integer pageSize,String keyword);

//    @Query(nativeQuery = true,value = "SELECT zs.`ZSMC`,lk.* FROM da_zsda  zs JOIN da_jqda  jq ON zs.`ID`=jq.`ZSID` JOIN da_lkpz lk ON jq.`JQID`=lk.`jqid` where zs.ZSMC like %?3% limit ?1,?2")
//    List<Map> findMachineControlInfo(Integer start,Integer pageSize,String keyword);

    @Query(nativeQuery = true,value = "SELECT count(*) FROM da_zsda  zs JOIN da_jqda  jq ON zs.`ID`=jq.`ZSID` JOIN da_lkpz lk ON jq.`JQID`=lk.`jqid` where zs.ZSMC like %?1%")
    Long countTotalSize(String keyword);

    /**
     * 新增时的猪舍名称下拉框
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT zs.ZSMC FROM da_zsda zs JOIN da_jqda jq ON zs.ID = jq.ZSID WHERE jq.JQID NOT IN (SELECT jqid FROM da_lkpz lk)")
    List<String> selectZsList();
}
