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

package com.uas.eis.serviceImpl;

import com.uas.eis.dao.BaseDao;
import com.uas.eis.entity.DataChipTestLog;
import com.uas.eis.service.FileParseService;
import com.uas.eis.utils.FTPUtil;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;

@Service
public class FileParseServiceImpl implements FileParseService {
    private final Logger logger = LoggerFactory.getLogger(getClass());
    @Autowired
    private BaseDao baseDao;
    @Override
    @Async("taskExecutor")
    public void EDCDataDeal(DataChipTestLog dataChipTestLog, CountDownLatch countDownLatch) {
        String TableName = "DATA$" + dataChipTestLog.getLot_id().replace("-","_");
        FTPClient ftpClient = null;
        InputStream ftpInputStream = null;
        Workbook workBook = null;
        Sheet workSheet =null;
        try {
            this.logger.info(new StringBuilder().append(dataChipTestLog.getWafer_id()).append("Data文件解析开始").toString());
            String FilePath = extractFilePath(dataChipTestLog.getData_path());
            ftpClient = FTPUtil.getFTPClient();
            System.out.println("开始下载文件：" + FilePath+";文件是否存在："+FTPUtil.exists(ftpClient, FilePath));
            if (ftpClient != null && FTPUtil.exists(ftpClient, FilePath)) {
                ftpInputStream = new BufferedInputStream(FTPUtil.readFileAsStream(ftpClient, FilePath));
                workBook = WorkbookFactory.create(ftpInputStream);
                workSheet = workBook.getSheetAt(0);
                int labelIndex = 13;
                int dataStart = 23;
                Row labelRow = workSheet.getRow(labelIndex);
                List items = new ArrayList();
                for (int i = 0; i < labelRow.getPhysicalNumberOfCells(); i++) {
                    items.add(labelRow.getCell(i)!=null?labelRow.getCell(i).getStringCellValue(): "");
                }
                List itemCols = new ArrayList();
                for (int j = 0; j < items.size(); j++) {
                    itemCols.add(new StringBuilder().append("ITEM").append(j + 1).toString());
                }
                List sqls = new ArrayList();
                sqls.add(new StringBuilder().append("DELETE FROM ").append(TableName).append("@SHENAI_EDCDATA  WHERE WAFER_ID='").append(dataChipTestLog.getWafer_id()).append("'").toString());
                int allRow = workSheet.getLastRowNum() - (dataStart - 1);
                int a = allRow / 3000;
                int b = allRow % 3000;
                StringBuffer detailBuffer = new StringBuffer();
                String dynamicCols = String.join(",", itemCols);
                String ValueSql = "";
                String cellValue = "";
                Row dataRow = null;
                if (a > 0) {
                    for (int i = 0; i < a; i++) {
                        detailBuffer.setLength(0);
                        for (int k = i * 3000; k < (i + 1) * 3000; k++) {
                            detailBuffer.append(new StringBuilder().append("INSERT INTO ").append(TableName).append("@SHENAI_EDCDATA (WAFER_ID,DETNO_,DATATYPE_,FLAG_,").append(String.join(",", new CharSequence[]{dynamicCols})).append(") VALUES ").append("('")
                                    .append(dataChipTestLog.getWafer_id()).append("',").append(k).append(",").append((k==0?0:1)).append(",").toString());
                            ValueSql = "";
                            dataRow = workSheet.getRow(k + dataStart);
                            if(dataRow.getCell(0).toString().startsWith("F")){
                                detailBuffer.append("1").toString();
                            }else {
                                detailBuffer.append("0").toString();
                            }
                            for (int m = 0; m < items.size(); m++) {
                                cellValue = dataRow.getCell(m).getStringCellValue();
                                ValueSql = new StringBuilder().append(ValueSql).append(cellValue != null ? new StringBuilder().append("'").append(cellValue.trim()).append("'").toString() : "''").toString();
                                if (m < items.size() - 1) {
                                    ValueSql = new StringBuilder().append(ValueSql).append(",").toString();
                                }
                            }
                            detailBuffer.append(new StringBuilder().append(",").append(ValueSql).append(");").toString());
                        }
                        sqls.add(new StringBuilder().append(" BEGIN ").append(detailBuffer.toString()).append("  END;").toString());
                    }
                }
                if (b > 0) {
                    detailBuffer.setLength(0);
                    for (int k = 0; k < b; k++) {
                        detailBuffer.append(new StringBuilder().append("INSERT INTO ").append(TableName).append("@SHENAI_EDCDATA (WAFER_ID,DETNO_,DATATYPE_,FLAG_,").append(String.join(",", new CharSequence[]{dynamicCols})).append(") VALUES ").append("('")
                               .append(dataChipTestLog.getWafer_id()).append("',").append(a * 3000 + k).append(",").append((k==0?0:1)).append(","));
                        ValueSql = "";
                        dataRow = workSheet.getRow(a * 3000 + k + dataStart);
                        if(dataRow.getCell(0).toString().startsWith("F")){
                            detailBuffer.append("1").toString();
                        }else {
                            detailBuffer.append("0").toString();
                        }
                        for (int m = 0; m < items.size(); m++) {
                            cellValue = dataRow.getCell(m).getStringCellValue();
                            ValueSql = new StringBuilder().append(ValueSql).append(cellValue != null ? new StringBuilder().append("'").append(cellValue.trim()).append("'").toString() : "''").toString();
                            if (m < items.size() - 1) {
                                ValueSql = new StringBuilder().append(ValueSql).append(",").toString();
                            }
                        }
                        detailBuffer.append(new StringBuilder().append(",").append(ValueSql).append(");").toString());
                    }
                    sqls.add(new StringBuilder().append("BEGIN ").append(detailBuffer.toString()).append("  END;").toString());
                }
                this.baseDao.getJdbcTemplate().batchUpdate((String[]) sqls.toArray(new String[sqls.size()]));
                StringBuilder endBuilder = new StringBuilder().append("BEGIN ").append("UPDATE DATACENTER$MESFILE SET DEALSTATE_=1,DEALTIME_=SYSDATE WHERE DBID_=").
                        append(dataChipTestLog.getDbid_()).append("; ");
                endBuilder.append("INSERT INTO  chip$file (WAFER_ID,LOT_ID,PASS_QTY,FAIL_QTY,PASS_YFIELD,EQUIP_ID,STATUS,COUNTER_PATH,DATA_PATH,TEST_LOT,CREATETIME_,DEALSTATE_,DEALTIME_,DBID_) SELECT WAFER_ID,LOT_ID,PASS_QTY,FAIL_QTY,PASS_YFIELD,EQUIP_ID,STATUS,TRIM(COUNTER_PATH),trim(DATA_PATH),TEST_LOT,CREATETIME_,DEALSTATE_,DEALTIME_,DBID_ " +
                        "FROM DATACENTER$MESFILE WHERE DBID_="+dataChipTestLog.getDbid_()+" AND DATACENTER$MESFILE.WAFER_ID NOT IN (SELECT WAFER_ID FROM chip$file WHERE WAFER_ID='"+dataChipTestLog.getWafer_id()+"' ); ");
                endBuilder.append("INSERT INTO   chip$data$log (WAFER_ID,ID_) SELECT '"+dataChipTestLog.getWafer_id()+"',CHIP$DATA$LOG_SEQ.NEXTVAL FROM DUAL; END;");
                this.baseDao.execute(endBuilder.toString());
            }
        } catch (Exception e) {
            this.logger.error(new StringBuilder().append("Exception:芯片号:").append(dataChipTestLog.getWafer_id()).toString(), e);
            e.printStackTrace();
            this.baseDao.execute(new StringBuilder().append("UPDATE DATACENTER$MESFILE SET DEALSTATE_=-99 , DEALTIME_=SYSDATE WHERE DBID_=").append(dataChipTestLog.getDbid_()).toString());
        } finally {
            try {
                if (workBook != null)  workBook.close();
            } catch (Exception ex) {
                logger.error("关闭workBook失败", ex);
            }
            try {
                if (ftpInputStream != null) ftpInputStream.close();
            } catch (Exception ex) {
                logger.error("关闭FTP流失败", ex);
            }
            try {
                if (ftpClient != null) {
                    ftpClient.logout();
                    ftpClient.disconnect();
                }
            } catch (Exception ex) {
                logger.error("释放FTP连接失败", ex);
            }
            countDownLatch.countDown();
        }
    }

    @Override
    public void EDCCounterDeal(List<String> codes) {

    }


    private String extractFilePath(String ftpUrl) {
        if (ftpUrl == null || ftpUrl.isEmpty()) {
            return null;
        }

        // 查找 ftp:// 的位置
        int protocolIndex = ftpUrl.indexOf("ftp://");
        if (protocolIndex == -1) {
            // 不是FTP URL，直接返回原路径
            return ftpUrl;
        }
        int pathStartIndex = ftpUrl.indexOf("/", protocolIndex + 6);
        if (pathStartIndex == -1) {
            // 没有路径部分，返回根目录
            return "/";
        }
        return "/home/mes"+ftpUrl.substring(pathStartIndex).trim();
    }
}