package com.northglass.repository;

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

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.northglass.constants.StateConstants.RawPackageTxtState;
import com.northglass.entity.GalssTaskDetails;
import com.northglass.entity.Rank;
import com.northglass.entity.RawFile;

public interface RawFileDao extends JpaRepository<RawFile, Long>{

	@Query("select t from RawFile t where t.fileName = '052-123' and t.fileUrl = '1' and t.state = '已下发' and t.operator = '1' and t.createTime = '2021-09-22 13:22:18' and t.receiveTime = '2021-09-22 13:22:21' ")
    public List<RawFile> findMachineDispose();
	
	//找到对应产线的状态为已领取的对象
    @Query("select t from RawFile t where t.state = '已领取' and t.operator=?1")
    public RawFile findAcceptedByGroup(Long operator);
    
    @Query("select t from RawFile t where  t.id=?1")
    public RawFile findAcceptedByGroup2(Long id);
    
    @Query("select t from RawFile t where t.state = '已下发' and (t.operator=?1 or t.operator=0)")
    public List<RawFile> findAcceptedByGroups(Long operator);
    
  //找到对应产线的状态为已领取的对象
    @Query("select t from RawFile t where t.state = '已领取' and t.operator=?1")
    public RawFile findIdGroup(Long operator);
    
    @Query("select t from RawFile t where t.state = '正在切割' and t.operator=?1")
    public RawFile findIdGroup1(Long operator);
    
  //找到对应产线的状态为已领取的对象
    @Query("select t from RawFile t where (t.state = '已下发' or t.state = '中断') and (t.operator=?1 or t.operator=0)")
    public List<RawFile> findTasks(Long operator);
    

    @Query("select t from RawFile t where t.state = '中断' and t.operator=?1")
    public List<RawFile> findInterruptTasks(Long operator);
    
    @Query("select t from RawFile t where t.state = '已领取' and t.fileName=?1")
    public RawFile findfileName(String fileName);
    
    @Query("select t from RawFile t where  t.fileName=?1 ")
    public List<RawFile> findfileNamelist(String fileName);
    
    @Query(nativeQuery=true,value="select * from gmms_raw_file where  state='已完成' and file_name like '%P%' order by receive_time desc limit 0,50")
    public List<RawFile> findfileNameall();
    
    @Query(nativeQuery=true,value="select * from gmms_raw_file where  state='已完成' and file_name like '%P%' and receive_time>=?1 and receive_time<=?2 order by receive_time ")
   	public List<RawFile> findalltime(Date create_time,Date end_time);
    
    @Query(nativeQuery=true,value="select * from gmms_raw_file where  state='已完成' and file_name like '%P%' and operator!=0  group by operator")
    public List<RawFile> findfileNamealls();
    
    @Query(nativeQuery=true,value="select sum(galssmeasure) from gmms_raw_file where  state='已完成' and file_name like '%P%' and operator=?1  group by operator")
    public String findfileNameall1(Long groups);
    
    @Query(nativeQuery=true,value="select sum(patternmeasure) from gmms_raw_file where  state='已完成' and file_name like '%P%' and operator=?1  group by operator")
    public String findfileNameall2(Long groups);
    
    @Query(nativeQuery=true,value="select sum(galssmeasure) from gmms_raw_file where  state='已完成' and file_name like '%P%' and operator=?3 and receive_time>=?1 and receive_time<=?2  group by operator")
    public String findfileNameall3(Date create_time,Date end_time,Long groups);
    
    @Query(nativeQuery=true,value="select sum(patternmeasure) from gmms_raw_file where  state='已完成' and file_name like '%P%' and operator=?3 and receive_time>=?1 and receive_time<=?2  group by operator")
    public String findfileNameall4(Date create_time,Date end_time,Long groups);
    
    /*@Query("select t from RawFile t where  t.createTime>=?1 and t.fileName like '%P%'")
    public List<RawFile> findfileNameall(Date time);*/
    
  //流程卡报表
    @Query("select t from RawFile t where  t.fileName=?1 ")
    public RawFile GetGongChengByGCH(String fileName);
    
  //获取除未领取工程的其他所有工程//v_completedtasks
    @Query(nativeQuery = true,value="SELECT * FROM v_completedtasks")
    public List<RawFile> GeOverStatetasks();
}