uas-eis/src/main/java/com/uas/eis/serviceImpl/EDCBakServiceImpl.java

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<EquipConfig> 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<Map<String, Object>> 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 <SmbFile> files = SmbUtil.getChildFiles(dataFile, testStep, "xls");
                            List <SmbFile> files1 = SmbUtil.getChildFiles(dataFile, testStep, "jdf");
                            List <SmbFile> files2 = SmbUtil.getChildFiles(dataFile, testStep, "njdf");
                            for(Map<String, Object> 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<SmbFile> 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<String> Sqls = new ArrayList<String>();
                //匹配到JDF文件或者Data
                List<Map<String, Object>> datalist=new ArrayList<>();
                for(Map<String,Object> 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<Object> IDS = new ArrayList<>();
                for(Map<String,Object> 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;
    }
}