package com.web.pda.lyt.lytPda.dao;
|
|
import com.system.role.entity.SysRole;
|
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
|
import org.springframework.data.jpa.repository.Query;
|
import org.springframework.data.repository.CrudRepository;
|
|
import java.util.List;
|
import java.util.Map;
|
|
public interface DefectInputDao extends CrudRepository<SysRole, Long>, JpaSpecificationExecutor<SysRole> {
|
|
// // 出现频率最高排序不良类型
|
// @Query(value = "SELECT *FROM (select T.DEFECT_CODE, T.DEFECT_NAME,
|
// COUNT(1) DS from MES_QA_ITEMS_DETECT_DETAIL11 t GROUP BY T.DEFECT_CODE,
|
// T.DEFECT_NAME) WHERE 1 = 1 and rownum<=20 ORDER BY DS DESC", nativeQuery
|
// = true)
|
// public List<Map<String, Object>> getDefectList();
|
|
// 出现频率最高排序不良类型
|
@Query(value = "SELECT * FROM (select T.DEFECT_CODE, T.DEFECT_NAME, COUNT(1) DS from MES_QA_ITEMS_DETECT_DETAIL11 t LEFT JOIN MES_QA_ITEMS_DETECT_06 t2 ON t.RELEASE_NO = t2.RELEASE_NO where t2.proc_line = ?1 GROUP BY T.DEFECT_CODE, T.DEFECT_NAME) WHERE 1 = 1 and rownum<=20 ORDER BY DS DESC", nativeQuery = true)
|
public List<Map<String, Object>> getDefectList(String procLine);
|
|
// 其他不良类型选择(低频率)
|
@Query(value = "select T.DEFECT_NAME,T.DEFECT_CODE from MES_DEFECT_CODE t", nativeQuery = true)
|
public List<Map<String, Object>> getDefectOtherList();
|
|
// 其他不良类型选择(低频率)-查询方法
|
@Query(value = "select T.DEFECT_NAME,T.DEFECT_CODE from MES_DEFECT_CODE t where t.defect_name like ?1", nativeQuery = true)
|
public List<Map<String, Object>> searchDefectOther(String keyword);
|
|
// 设备编码
|
@Query(value = "select T.EQ_CODE from MES_EQ_BASEINFO_LINE t", nativeQuery = true)
|
public List<Map<String, Object>> getDeviceCodeList();
|
|
// 选择工单
|
@Query(value = "select a.task_no 工单号, a.board_item 产品编码, a.board_name 产品名称,b.item_model 型号,a.plan_qty 计划数 from mes_dep_task_info a left join mes_items b on a.board_item = b.item_no where A.CREATE_DATE > SYSDATE - 30 AND A.PRODUCE_STATE <> '完工' AND (a.task_no LIKE ?1 OR a.board_name like ?1)", nativeQuery = true)
|
public List<Map<String, Object>> getTaskNoList(String keyword);
|
|
// 显示投入的不良明细
|
@Query(value = "SELECT B.DEFECT_CODE 不良代码,B.DEFECT_NAME 不良名称,SUM(B.FNG_QTY) AS 不良数量 FROM MES_QA_ITEMS_DETECT_DETAIL11 B LEFT JOIN MES_QA_ITEMS_DETECT_06 D ON B.RELEASE_NO = D.RELEASE_NO left join mes_defect_code c on c.company = b.company and c.factory = b.factory and c.defect_code = b.defect_code and c.s_type = 'D' WHERE D.AUFNR = ?1 AND D.PROC_LINE = ?2 AND D.fprod_date = to_char(sysdate, 'YYYY-MM-DD')GROUP BY B.DEFECT_CODE, B.DEFECT_NAME ORDER BY B.DEFECT_CODE", nativeQuery = true)
|
public List<Map<String, Object>> getRecordList(String taskNo, String procLine);
|
|
// 选择工序线
|
//@Query(value = "select a.line_no 工序线体编号,a.line_name 工序线体名称 from MES_LINE_INFO a where a.line_no like ?1 or a.line_name like ?1 order by a.line_no ", nativeQuery = true)
|
//public List<Map<String, Object>> getProcLineList(String keyword);
|
|
// 选择工序线体-2021-8-27 修改
|
@Query(value = "SELECT 工序线体编号, 工序线体名称 FROM (select a.line_no 工序线体编号, a.line_name 工序线体名称 from MES_LINE_INFO a WHERE A.LINE_CODE in(SELECT b.CH002 FROM SYS_USER A LEFT JOIN SYS_USER_CHILD B ON A.FCODE = B.CH001 where a.fcode = ?1)) order by 工序线体编号", nativeQuery = true)
|
public List<Map<String, Object>> getProcLineList(String keyword);
|
|
// 通过工号获取对应工序线体
|
@Query(value = "SELECT 工序线体编号, 工序线体名称 FROM (select a.line_no 工序线体编号, a.line_name 工序线体名称 from MES_LINE_INFO a WHERE A.LINE_CODE = (SELECT b.CH002 FROM SYS_USER A LEFT JOIN SYS_USER_CHILD B ON A.FCODE = B.CH001 where a.fcode = ?1)) order by 工序线体编号", nativeQuery = true)
|
public List<Map<String, Object>> findProcLineByUser(String userNo);
|
|
// //删除记录
|
// @Modifying
|
// @Query(value = "delete from MES_QA_ITEMS_DETECT_DETAIL11 a where a.id =(SELECT max(b.id) FROM MES_QA_ITEMS_DETECT_DETAIL11 B LEFT JOIN MES_QA_ITEMS_DETECT_06 D ON B.RELEASE_NO = D.RELEASE_NO left join mes_defect_code c on c.company = b.company and c.factory = b.factory and c.defect_code = b.defect_code and c.s_type = 'D' WHERE D.AUFNR = ?1 AND D.PROC_LINE = ?2 AND D.fprod_date = to_char(sysdate, 'YYYY-MM-DD') and b.defect_code = ?3) ", nativeQuery = true)
|
// public List<Map<String, Object>> delDefectData(String taskNo,String procLine,String defCode);
|
|
}
|
