package com.uas.eis.serviceImpl; import com.uas.eis.dao.BaseDao; import com.uas.eis.dao.SqlRowList; import com.uas.eis.entity.EquipConfig; import com.uas.eis.service.EDCBakService; import com.uas.eis.utils.Constant; import com.uas.eis.utils.DateUtil; import com.uas.eis.utils.SmbUtil; import jcifs.smb.SmbFile; import jcifs.smb.SmbFileInputStream; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.scheduling.annotation.Async; import org.springframework.stereotype.Service; import java.io.BufferedInputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.*; import java.util.concurrent.CountDownLatch; /** * @Author: zhouy * @Date: 2020/5/27 14:19 */ @Service public class EDCBakServiceImpl implements EDCBakService { private final Logger logger = LoggerFactory.getLogger(this.getClass()); @Autowired private BaseDao baseDao; @Override @Async("taskExecutor") public void EDCBak(List configs, CountDownLatch countDownLatch){ //查询所有的设备 EquipConfig baseConfig = configs.get(0); /** * 主机相关配置 * */ String IP = baseConfig.getEc_ip().trim(); String counterRootPath = baseConfig.getEc_counterpath().endsWith("/") ? baseConfig.getEc_counterpath() : baseConfig.getEc_counterpath()+"/"; String dataRootPath = baseConfig.getEc_datapath().endsWith("/") ? baseConfig.getEc_datapath() : baseConfig.getEc_datapath()+"/"; String counterbakRootPath = baseConfig.getEc_counterbak().endsWith("/") ? baseConfig.getEc_counterbak() : baseConfig.getEc_counterbak()+"/"; String uploadRootPath = baseConfig.getEc_databak(); logger.info(" matchIP："+IP+" start;"); //检测是否连接成功 SmbFile smbFile = SmbUtil.getRootFile(IP,counterRootPath); try { smbFile.connect(); } catch (Exception e) { baseDao.execute("insert into EDCsyscfaillog(el_id,el_ip,el_devcode,el_teststep,el_synctime,el_reason) values(EDCsyscfaillog_seq.nextval,'"+IP+"','','',sysdate,'"+e.getMessage()+",连接超时')"); logger.info(IP+" 连接失败;"+e.getMessage()); return; }finally { countDownLatch.countDown(); } String counterpath = ""; try { for(EquipConfig config : configs){ String testStep = config.getEd_teststep(); String devcode = config.getEd_devcode(); List> lists=baseDao.getJdbcTemplate().queryForList("SELECT * FROM (SELECT * FROM DATACENTER$MESTEMP WHERE EQPCODE_='"+devcode+"' AND TAB_ ='"+testStep+"' and turn_=0 AND nvl(fail,0)=0 ORDER BY CHIPCODE_,OBJECT_RRN ASC ) WHERE ROWNUM <= 50"); logger.info(" matchIP："+IP+","+testStep+" start;"); logger.info(" matchIP："+IP+","+testStep+" 待归档数："+lists.size()); if(lists.size()>0){ try { /**相关路径匹配*/ if(smbFile !=null) { SmbFile dataFile = SmbUtil.getCurrentFile(IP,dataRootPath); List files = SmbUtil.getChildFiles(dataFile, testStep, "xls"); List files1 = SmbUtil.getChildFiles(dataFile, testStep, "jdf"); List files2 = SmbUtil.getChildFiles(dataFile, testStep, "njdf"); for(Map map : lists) { //Counter文件匹配 counterpath = String.valueOf(map.get("COUNTERPATH_")); final String filename = counterpath.substring(counterpath.lastIndexOf("\\")+1); smbFile = SmbUtil.getCurrentFile(IP,counterbakRootPath+filename); map.put("COUNTERPATH_", smbFile.getPath()); Optional optional = files.stream().filter(file -> mappingSuccess(filename,file.getDate())).findFirst(); //遍历到了对应的Data文件存在 if (optional.isPresent()){ map.put("DATAPATH_", optional.get().getPath()); } optional = files1.stream().filter(file -> mappingSuccess(filename,file.getDate())).findFirst(); //遍历到了对应的Data文件存在 if (optional.isPresent()){ map.put("JDFPATH_", optional.get().getPath()); } optional = files2.stream().filter(file -> mappingSuccess(filename,file.getDate())).findFirst(); if (optional.isPresent()){ map.put("JDFPATH_", optional.get().getPath()); } } } } catch (Exception e) { baseDao.execute("insert into EDCsyscfaillog(el_id,el_ip,el_devcode,el_teststep,el_synctime,el_reason) values(EDCsyscfaillog_seq.nextval,'"+IP+"','"+devcode+"','"+testStep+"',sysdate,'"+e.getMessage()+"')"); e.printStackTrace(); if(e.getMessage().equals("Plain text passwords are disabled")){ continue; } } } else{ logger.info(" matchIP："+IP+","+testStep+"未匹配有效文件"); continue; } List Sqls = new ArrayList(); //匹配到JDF文件或者Data List> datalist=new ArrayList<>(); for(Map map : lists){ if(map.get("DATAPATH_")!=null||map.get("JDFPATH_")!=null){ datalist.add(map); }else { String chipcode=map.get("CHIPCODE_").toString(); String ID=map.get("OBJECT_RRN").toString(); baseDao.execute("insert into EDCsyscfaillog(el_id,el_ip,el_devcode,el_teststep,el_synctime,el_chipcode,el_reason) values(EDCsyscfaillog_seq.nextval,'"+IP+"','"+devcode+"','"+testStep+"',sysdate,'"+chipcode+"','未匹配到对应JDF文件，无法归档')"); baseDao.execute("update datacenter$mestemp set fail=-1 where OBJECT_RRN="+ID); } } logger.info(" matchIP："+IP+","+testStep+" 匹配数："+datalist.size()); baseDao.execute("insert into SYS_SCHEDULETASKLOG(date_,remark_,scheduleid_) values" + "(sysdate,'执行开始:IP"+IP+"设备"+devcode+"匹配到数量"+datalist.size()+"',7042)"); //未设置备份文件夹时记录日志 if(uploadRootPath==null) { baseDao.execute("insert into EDCsyscfaillog(el_id,el_ip,el_devcode,el_teststep,el_synctime,el_reason) values(EDCsyscfaillog_seq.nextval,'"+IP+"','"+devcode+"','"+testStep+"',sysdate,'BackUp文件夹未设置，请检查终端设置')"); continue; } /** * 文件归档开始 * */ String dataPath_ = ""; String counterPath_ = ""; String jdfPath_=""; String currentUploadPath = getUploadPath(uploadRootPath+"/"); String centerUploadPath = getUploadPath(SmbUtil.centerPath+devcode+"/"); SmbFile counterFile = null; SmbFile dataFile = null; SmbFile jdfFile = null; String currentKind = null; SmbFile counterBaKFile = null; SmbFile dataBakFile = null; SmbFile jdfBakFile = null; SmbFile counterCenterFile = null; SmbFile dataCenterFile = null; SmbFile jdfCenterFile = null; List IDS = new ArrayList<>(); for(Map map : datalist){ logger.info(map.get("CHIPCODE_")+"开始"); counterPath_ = String.valueOf(map.get("COUNTERPATH_")); dataPath_ = String.valueOf(map.get("DATAPATH_")); jdfPath_ = String.valueOf(map.get("JDFPATH_")); try { currentKind = map.get("PART_NAME")+"-"+map.get("MAKE_LOT"); String path1=""; //数据中心Counter文件 String path2=""; //数据中心Data文件 String path3=""; //本地Counter备份文件 String path4=""; //本地Data备份文件 String path5=""; //数据中心JDF文件 String path6=""; //本地JDF文件 //判断本机文件夹是否存在，不存在创建 if(!SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/").exists()){ SmbUtil.getCurrentFile(IP,currentUploadPath+(currentKind+"/SJ/")).mkdirs(); SmbUtil.getCurrentFile(IP,currentUploadPath+(currentKind+"/FP/")).mkdirs(); } //判断文件服务器文件夹是否存在，不存在创建 if(!SmbUtil.getCurrentFile(SmbUtil.centerIP,centerUploadPath+currentKind+"/").exists()){ SmbUtil.getCurrentFile(SmbUtil.centerIP,centerUploadPath+(currentKind+"/SJ/")).mkdirs(); SmbUtil.getCurrentFile(SmbUtil.centerIP,centerUploadPath+(currentKind+"/FP/")).mkdirs(); } //处理Counter文件 counterFile = SmbUtil.getCurrentFile(counterPath_); counterCenterFile = SmbUtil.getCurrentFile(SmbUtil.centerIP,centerUploadPath+currentKind+"/FP/"+map.get("CHIPCODE_")+".xls"); /** * counter文件 文件服务器归档 * */ counterFile.copyTo(counterCenterFile); counterBaKFile = SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/FP/"+map.get("CHIPCODE_")+".xls"); int Count=1; while (counterBaKFile.exists()){ String filename= counterBaKFile.getName(); String name = filename.substring(0,filename.indexOf(".")); String newFilename = name+"("+Count+")"; counterBaKFile=SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/FP/"+newFilename+".xls"); } path1=counterCenterFile.getPath(); path3=counterBaKFile.getPath(); /** * 本机counter文件移到本机归档目录当前目录删除 * */ counterFile.copyTo(counterBaKFile); counterFile.delete(); //存在Data文件 if(map.get("DATAPATH_")!=null){ dataFile = SmbUtil.getCurrentFile(dataPath_); dataCenterFile = SmbUtil.getCurrentFile(SmbUtil.centerIP,centerUploadPath+currentKind+"/SJ/"+map.get("CHIPCODE_")+".xls"); /** * data文件 文件服务器归档 * */ dataFile.copyTo(dataCenterFile); dataBakFile = SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/SJ/"+map.get("CHIPCODE_")+".xls"); while (dataBakFile.exists()){ String filename= dataBakFile.getName(); String name = filename.substring(0,filename.indexOf(".")); String newFilename = name+"("+Count+")"; dataBakFile=SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/SJ/"+newFilename+".xls"); } path2=dataCenterFile.getPath(); path4=dataBakFile.getPath(); /** * 本机data文件移到本机归档目录当前目录删除 * */ dataFile.copyTo(dataBakFile); dataFile.delete(); }else{ dataCenterFile = SmbUtil.getCurrentFile(SmbUtil.centerIP,centerUploadPath+currentKind+"/SJ/"+map.get("CHIPCODE_")+".xls"); dataBakFile = SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/SJ/"+map.get("CHIPCODE_")+".xls"); path2=dataCenterFile.getPath(); path4=dataBakFile.getPath(); } //如果存在JDF文件 if(map.get("JDFPATH_")!=null){ String filesuffix=".jdf"; if(map.get("JDFPATH_").toString().contains("njdf")){ filesuffix=".njdf"; } jdfFile = SmbUtil.getCurrentFile(jdfPath_); jdfCenterFile = SmbUtil.getCurrentFile(SmbUtil.centerIP,centerUploadPath+currentKind+"/SJ/"+map.get("CHIPCODE_")+filesuffix); jdfFile.copyTo(jdfCenterFile); jdfBakFile = SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/SJ/"+map.get("CHIPCODE_")+filesuffix); while (jdfBakFile.exists()){ String filename= jdfBakFile.getName(); String name = filename.substring(0,filename.indexOf(".")); String suffix = filename.substring(filename.lastIndexOf(".")); String newFilename = name+"("+Count+")"; jdfBakFile=SmbUtil.getCurrentFile(IP,currentUploadPath+currentKind+"/SJ/"+newFilename+filesuffix); } path5=jdfCenterFile.getPath(); path6=jdfBakFile.getPath(); jdfFile.copyTo(jdfBakFile); jdfFile.delete(); } //数据中心归档 Sqls.add("MERGE INTO DATACENTER$CHIP A USING (SELECT CHIPCODE_, OBJECT_RRN, PART_NAME, TEST_LOT, MAKE_LOT,CREATEDATE_ FROM DATACENTER$MESTEMP WHERE OBJECT_RRN = "+map.get("OBJECT_RRN")+") B ON (A.CHIPCODE_= B.CHIPCODE_) " + " WHEN MATCHED THEN UPDATE SET A.OBJECT_RRN = B.OBJECT_RRN ,A.PART_NAME = B.PART_NAME, A.TEST_LOT = B.TEST_LOT , A.MAKE_LOT = B.MAKE_LOT " + " WHEN NOT MATCHED THEN INSERT (COUNTERPATH_, DATAPATH_ ,LOCALCOUNTERPATH_ , LOCALDATAPATH_,JDFPATH_,LOCALJDFPATH_,ID_, CHIPCODE_, OBJECT_RRN , PART_NAME ,TEST_LOT, MAKE_LOT,TESTDATE_,DATE_) VALUES ("+ " '"+path1+"','"+path2+"','"+path3+"','"+path4+"','"+path5+"','"+path6+"',DATACENTERCHIP_SEQ.NEXTVAL, B.CHIPCODE_,B.OBJECT_RRN,B.PART_NAME,B.TEST_LOT,B.MAKE_LOT,B.CREATEDATE_,SYSDATE)" ); IDS.add(map.get("OBJECT_RRN")); logger.info(map.get("CHIPCODE_")+"结束"); } catch (Exception e) { baseDao.execute("insert into EDCsyscfaillog(el_id,el_ip,el_devcode,el_teststep,el_synctime,el_reason) values(EDCsyscfaillog_seq.nextval,'"+IP+"','"+devcode+"','"+testStep+"',sysdate,'"+e.getMessage()+"')"); e.printStackTrace(); } } if(IDS.size() > 0){ Sqls.add("UPDATE DATACENTER$MESTEMP SET TURN_=-1 WHERE OBJECT_RRN IN ("+ StringUtils.join(IDS,",") +")"); baseDao.execute(Sqls); } //同步做COUNTER文件解析 SqlRowList rs1=baseDao.queryForRowSet("select chipcode_ from DATACENTER$CHIP left join CHIP_EDC_RECORD on DATACENTER$CHIP.CHIPCODE_=CHIP_EDC_RECORD.CER_CHIPCODE where CER_CHIPCODE is null"); while(rs1.next()){ AnalysisCounterData(rs1.getString("chipcode_")); } } } catch (Exception e){ e.printStackTrace(); logger.info(IP+" 操作失败;"); }finally { countDownLatch.countDown(); } logger.info(" matchIP："+IP+" end;"); } private boolean mappingSuccess(String fileName , Long time) { boolean match=false; //时间在同一分钟改的可以匹配 if(fileName.substring(2).startsWith(DateUtil.format(new Date(time), Constant.YMDHM))){ match=true; }else { long time1=10*1000; //如果时间不在同一分钟则直接加10秒再匹配 if(fileName.substring(2).startsWith(DateUtil.format(new Date(time+time1), Constant.YMDHM))) { match=true; }else if(fileName.substring(2).startsWith(DateUtil.format(new Date(time-time1), Constant.YMDHM))) { match=true; } } return match; } private String getUploadPath(String uploadRootPath) { String[] dateStr = new SimpleDateFormat("yyyy-MM-dd").format(new Date()).toString().split("-"); return String.format("%s/%s/%s/%s/", uploadRootPath, dateStr[0], dateStr[0]+dateStr[1], dateStr[0]+dateStr[1]+dateStr[2] ); } //解析Counter文件 public Object AnalysisCounterData(String chip_code){ SqlRowList rs=baseDao.queryForRowSet("select counterpath_ from datacenter$chip where chipcode_='"+chip_code+"' order by id_ desc"); Object cer_id=null; if(rs.next()){ try{ String FilePath=rs.getString("counterpath_"); //打开文件流 SmbFile smbFile = SmbUtil.getCurrentFile(FilePath); InputStream is = new BufferedInputStream(new SmbFileInputStream(smbFile)); Workbook book = new HSSFWorkbook(is); cer_id=baseDao.getSeqId("CHIP_EDC_RECORD_SEQ"); Sheet st=book.getSheet("Measure"); //测试日期 Object test_Date= st.getRow(2).getCell(1).getStringCellValue(); //测试站位 String test_Station= st.getRow(3).getCell(1).getStringCellValue(); //测试文件名 String test_FileName=st.getRow(4).getCell(1).getStringCellValue(); //设备名称 String test_DeviceName = st.getRow(5).getCell(1).getStringCellValue(); StringBuffer sql=new StringBuffer(); //插入主表记录 sql.append("insert into CHIP_EDC_RECORD(cer_id,cer_testdate,cer_chipcode,cer_indate,cer_station,cer_spec,cer_device,cer_testfilename,cer_lot,"); sql.append("cer_operator,cer_comment) values('"+cer_id+"',to_date('"+test_Date+"','yyyy mm dd hh24 mi ss'),'"+chip_code+"',sysdate,'"+test_Station+"','"+test_FileName.split("-")[0]+"',"); sql.append("'"+test_DeviceName+"','"+test_FileName+"','"+chip_code.split("#")[0]+"','','');"); //循环详细的测试内容，从11行最后一行 for (int i=11;i<=st.getLastRowNum();i++){ double cerd_itemdetno= st.getRow(i).getCell(0).getNumericCellValue(); String cerd_itemname = st.getRow(i).getCell(1).getStringCellValue(); double cred_testnum = st.getRow(i).getCell(2).getNumericCellValue(); double cerd_failnum= st.getRow(i).getCell(3).getNumericCellValue(); Object cerd_precent = "''"; if(st.getRow(i).getCell(4)!=null) { switch (st.getRow(i).getCell(4).getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: cerd_precent = st.getRow(i).getCell(4).getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: cerd_precent = st.getRow(i).getCell(4).getStringCellValue(); break; } } sql.append("insert into CHIP_EDC_RECORDDet(cerd_id,cerd_cerid,cerd_itemdetno,cerd_itemname,cerd_precent,cred_testnum,cerd_failnum)"); sql.append("values(CHIP_EDC_RECORDDet_SEQ.nextval,'"+cer_id+"','"+cerd_itemdetno+"','"+cerd_itemname+"',"+cerd_precent+","+cred_testnum+","+cerd_failnum+");"); } baseDao.execute("begin "+sql.toString()+" end;"); }catch (Exception e){ e.printStackTrace(); } } return cer_id; } }