package com.whyc.service;
|
|
import com.whyc.dto.FileDirPath;
|
import com.whyc.dto.Response;
|
import com.whyc.dto.paramter.AnalysisDto;
|
import com.whyc.pojo.*;
|
import com.whyc.util.ActionUtil;
|
import com.whyc.util.AnalysisUtil;
|
import lombok.Data;
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.web.bind.annotation.RequestParam;
|
|
import javax.imageio.ImageIO;
|
import javax.servlet.http.HttpServletResponse;
|
import java.awt.image.BufferedImage;
|
import java.awt.image.RenderedImage;
|
import java.io.*;
|
import java.net.URLEncoder;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
@Service
|
public class AnaysiService {
|
@Autowired(required = false)
|
private SubTablePageInfoService subService;
|
|
@Autowired(required = false)
|
private BatttestdataInfService tinfService;
|
|
|
|
|
//预估数据
|
public String createAnaysisXls(int battGroupId, int testRecordCount) {
|
UserInf uinf= ActionUtil.getUser();
|
String uid=String.valueOf(uinf.getUId());
|
//1.查出需要预估的数据
|
List<AnaysisData> list=subService.getAnaysisData(battGroupId,testRecordCount);
|
String excelName="测试数据"+"_"+battGroupId+"_"+testRecordCount+".xlsx";
|
//2.生成解析的xls
|
String root=exportDataToXls(uid,excelName,list);
|
String voltagePredictExePath=root+ File.separator+"10小时率电压预估\\voltage_predict_process\\main.exe";
|
String excelOutPutPath=root+ File.separator+"analysis"+File.separator+uid+ File.separator+"outPath"+File.separator+battGroupId+File.separator+testRecordCount;
|
String excelSourcePath=root+ File.separator+"analysis"+File.separator+uid+File.separator+excelName;
|
//4。将预估的数据存入数据库
|
if(list!=null){
|
int recordNum=list.size()+1;
|
//验证分析表是否存在
|
int flag=subService.judgeTable_anaysis(battGroupId,testRecordCount);
|
int insertFlag=0;
|
//检测表是存在后testRecordCount数据是否存在
|
if(flag!=0){
|
int sum=subService.getAnaysisIdByTestRecordCount(battGroupId,testRecordCount);
|
if(sum==0) {
|
insertFlag=1;
|
}
|
}else{
|
//表不存在则创建表
|
subService.creatAnasys(battGroupId);
|
insertFlag=1;
|
}
|
if(insertFlag!=0){
|
//补前面的数据
|
subService.insertBeforeData(battGroupId,testRecordCount,list);
|
//3.生成预估的文件xls
|
AnalysisUtil.analysisXlx(excelSourcePath,voltagePredictExePath,excelOutPutPath);
|
|
storeAnaysisToSql(battGroupId,testRecordCount,recordNum,excelOutPutPath,insertFlag);
|
}
|
}
|
return voltagePredictExePath;
|
}
|
//将预估的数据存入数据库
|
private void storeAnaysisToSql(int battGroupId,int testRecordCount,int recordNum,String excelSourcePath,int insertFlag) {
|
//获取文件夹下所有的数据
|
List<String> fileList=getFileNameWithOutDirectory(excelSourcePath);
|
List<AnaylsisId> groupVolList=new ArrayList<>();
|
if(insertFlag!=0){
|
if(fileList!=null){
|
for (String fileName:fileList) {
|
|
if(fileName.contains("总电压")){
|
//解析组端电压单个文件
|
groupVolList=anaysisFileByGroupVol(testRecordCount,recordNum,excelSourcePath+File.separator+fileName);
|
}else{
|
String name=fileName.substring(0,fileName.indexOf("#"));
|
int monNum=Integer.valueOf(name);
|
//解析单个文件
|
List<AnaylsisId> idList=anaysisFileByNum(monNum,testRecordCount,recordNum,excelSourcePath+File.separator+fileName);
|
//将解析出的数据存入数据库表
|
subService.storeAnaysisToSqlWithNum(battGroupId,testRecordCount,idList);
|
}
|
}
|
//将解析的组端电压修改到数据库
|
subService.updateAnaysisToSqlWithNum(battGroupId,testRecordCount,groupVolList);
|
}
|
}
|
}
|
//解析单体的单个文件
|
private static List<AnaylsisId> anaysisFileByNum(int monNum,int testRecordCount, int recordNum, String fileRoot) {
|
List<AnaylsisId> idList=new ArrayList<>();
|
Workbook workbook = null;
|
File file=new File(fileRoot);
|
try {
|
workbook = WorkbookFactory.create(file);
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
|
/*Row rowTemp = sheet.getRow(0);
|
short lastCellNum = rowTemp.getLastCellNum();*/
|
DataFormatter dataFormatter = new DataFormatter(Locale.US);
|
for (int i = 1; i < lastRowNum; i++) {
|
Row row = sheet.getRow(i);
|
|
AnaylsisId aIdDta=new AnaylsisId();
|
aIdDta.setTestRecordCount(testRecordCount);
|
aIdDta.setMonNum(monNum);
|
Cell cell0 = row.getCell(0);
|
cell0.setCellType(Cell.CELL_TYPE_NUMERIC);
|
aIdDta.setRecordNum( (recordNum+(int)cell0.getNumericCellValue()));
|
|
Cell cell1 = row.getCell(1);
|
String formattedValue = dataFormatter.formatCellValue(cell1);
|
aIdDta.setRecordTime(formattedValue);
|
|
Cell cell2 = row.getCell(2);
|
cell2 .setCellType(Cell.CELL_TYPE_NUMERIC);
|
aIdDta.setMonVol(cell2.getNumericCellValue());
|
idList.add(aIdDta);
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
} catch (InvalidFormatException e) {
|
e.printStackTrace();
|
}
|
return idList;
|
}
|
|
//解析组端电压单个文件
|
private static List<AnaylsisId> anaysisFileByGroupVol(int testRecordCount, int recordNum, String fileRoot) {
|
List<AnaylsisId> idList=new ArrayList<>();
|
Workbook workbook = null;
|
File file=new File(fileRoot);
|
try {
|
workbook = WorkbookFactory.create(file);
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
|
/*Row rowTemp = sheet.getRow(0);
|
short lastCellNum = rowTemp.getLastCellNum();*/
|
DataFormatter dataFormatter = new DataFormatter(Locale.US);
|
for (int i = 1; i < lastRowNum; i++) {
|
Row row = sheet.getRow(i);
|
|
AnaylsisId aIdDta=new AnaylsisId();
|
aIdDta.setTestRecordCount(testRecordCount);
|
Cell cell0 = row.getCell(0);
|
cell0.setCellType(Cell.CELL_TYPE_NUMERIC);
|
aIdDta.setRecordNum( (recordNum+(int)cell0.getNumericCellValue()));
|
|
Cell cell1 = row.getCell(1);
|
String formattedValue = dataFormatter.formatCellValue(cell1);
|
aIdDta.setRecordTime(formattedValue);
|
|
Cell cell2 = row.getCell(2);
|
cell2 .setCellType(Cell.CELL_TYPE_NUMERIC);
|
aIdDta.setGroupVol(cell2.getNumericCellValue());
|
idList.add(aIdDta);
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
} catch (InvalidFormatException e) {
|
e.printStackTrace();
|
}
|
return idList;
|
}
|
|
//读取文件夹下的所有文件（不读取文件夹内的文件）
|
public static List getFileNameWithOutDirectory(String filePath) {
|
File folder = new File(filePath); // 文件夹路径
|
List nameList=new ArrayList();
|
File[] listOfFiles = folder.listFiles();
|
if (listOfFiles != null) {
|
for (File file : listOfFiles) {
|
if (file.isFile()) {
|
nameList.add(file.getName());
|
}
|
}
|
}
|
return nameList;
|
}
|
|
//生成解析的xls
|
private String exportDataToXls(String uid, String excelName,List<AnaysisData> list) {
|
String fileDirName = FileDirPath.getFileDirName();
|
String rootFace="";
|
String[] titleNames=new String[]{"时间(HMS)","总电压(V)","总电流(A)","容量(AH)"};
|
//创建单个sheet
|
HSSFWorkbook wb = new HSSFWorkbook();
|
//字体格式-加粗
|
HSSFCellStyle cellStyle = wb.createCellStyle();
|
cellStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());//添加前景色,内容看的清楚
|
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
|
HSSFFont font = wb.createFont();
|
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
|
//font.setColor(HSSFFont.COLOR_RED);
|
cellStyle.setFont(font);
|
|
HSSFSheet sheet = wb.createSheet("电压数据表格1");
|
int rownum = 0;
|
int monSum =24;//单体总数
|
HSSFRow row=sheet.createRow(rownum);
|
if(titleNames.length>0){
|
HSSFCell cell=null;
|
for (int i=0;i<titleNames.length;i++) {
|
cell=row.createCell(i);
|
cell.setCellValue(titleNames[i]);
|
}
|
for (int j=0;j<monSum;j++) {
|
cell=row.createCell(j+4);
|
cell.setCellValue(j+1+"#");
|
}
|
}
|
rootFace=fileDirName+File.separator+"analysis";
|
File destfile = new File(rootFace);
|
if(!destfile.exists()) {
|
destfile.mkdir();
|
}
|
rootFace=fileDirName+File.separator+"analysis"+File.separator+uid;
|
File destfile1 = new File(rootFace);
|
if(!destfile1.exists()) {
|
destfile1.mkdir();
|
}
|
//将选中的文件存入指定目录下打包下载
|
if(list!=null&&list.size()>0){
|
for (int i=0;i<list.size();i++) {
|
AnaysisData data = list.get(i);
|
rownum = rownum + 1;
|
row = sheet.createRow(rownum);
|
row.createCell(0).setCellValue(data.getRecordTime());
|
row.createCell(1).setCellValue(data.getGroupVol());
|
row.createCell(2).setCellValue(data.getGroupCurr());
|
row.createCell(3).setCellValue(data.getTestCap());
|
if (data.getMonVols() != null) {
|
List<Float> monVols = data.getMonVols();
|
for (int j = 0; j < monVols.size(); j++) {
|
row.createCell(4 + j).setCellValue(monVols.get(j));
|
}
|
}
|
}
|
}
|
FileOutputStream fileOut =null;
|
excelName=rootFace+ File.separator+excelName;
|
try {
|
fileOut = new FileOutputStream(excelName);
|
// 写入excel文件
|
wb.write(fileOut);
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
if(fileOut != null){
|
try {
|
fileOut.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
return fileDirName;
|
}
|
|
//查询预估数据
|
public Response getAnaysisXls(int battGroupId, int testRecordCount) {
|
List<AnaylsisId> list=subService.getAnaysisXls(battGroupId,testRecordCount);
|
return new Response().setII(1,list!=null,list,"查询预估数据");
|
}
|
//解析xls文件到放电数据中
|
public Response<Object> anaysisXlsToTdata(int battGroupId,InputStream inputStream) throws IOException, InvalidFormatException, ParseException {
|
Workbook workbook = null;
|
workbook = WorkbookFactory.create(inputStream);
|
inputStream.close();
|
Sheet sheet =null;
|
int numberOfSheets=workbook.getNumberOfSheets();
|
int flag=0;
|
//取第一个sheet表
|
for (int i = 0; i < numberOfSheets; i++) {
|
sheet = workbook.getSheetAt(i);
|
String sheetName=sheet.getSheetName();
|
if (sheetName.contains("电压数据表格")) {
|
flag=1;
|
break;
|
}
|
}
|
if(flag==0){
|
return new Response<>().set(1,false,"不存在包含电压数据表格的数据");
|
}
|
int lastRowNum = sheet.getLastRowNum();
|
Row rowTemp = sheet.getRow(0);
|
int lastCellNum = rowTemp.getLastCellNum();
|
DataFormatter dataFormatter = new DataFormatter(Locale.US);
|
List<XlsToTdata> list=new ArrayList<>();
|
int testTimelong=0;
|
int timeInterval=0;
|
for (int i=1;i<lastRowNum;i++){
|
XlsToTdata tData=new XlsToTdata();
|
Row row = sheet.getRow(i);
|
Cell cell0 = row.getCell(0);
|
String formattedValue = dataFormatter.formatCellValue(cell0);
|
tData.setRecordTime(ActionUtil.parseDurationDirectly(formattedValue));
|
|
int testTimeLong=ActionUtil.convertToSeconds(formattedValue);
|
tData.setTestTimelong(testTimeLong);
|
if(i==2){
|
timeInterval=testTimeLong-timeInterval;
|
}
|
|
Cell cell1 = row.getCell(1);
|
cell1.setCellType(Cell.CELL_TYPE_NUMERIC);
|
tData.setGroupVol((float) cell1.getNumericCellValue());
|
|
Cell cell2 = row.getCell(2);
|
cell2.setCellType(Cell.CELL_TYPE_NUMERIC);
|
tData.setGroupCurr((float) cell2.getNumericCellValue());
|
|
Cell cell3 = row.getCell(3);
|
cell3.setCellType(Cell.CELL_TYPE_NUMERIC);
|
tData.setTestCap((float) cell3.getNumericCellValue());
|
|
List<Float> monVols=new ArrayList<>();
|
for (int j=4;j<lastCellNum;j++){
|
Cell cell = row.getCell(j);
|
if(cell!=null){
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String monVol= cell.getStringCellValue();
|
monVols.add(Float.valueOf(monVol));
|
}
|
|
}
|
testTimelong= tData.getTestTimelong();
|
tData.setMonVols(monVols);
|
list.add(tData);
|
}
|
|
//1.获取当前最大的放电信息testRecordCount
|
BatttestdataInf tinf=tinfService.getMaxRecord(battGroupId);
|
int testRecordCount=0;
|
if(tinf!=null) {
|
testRecordCount = tinf.getTestRecordCount();
|
}
|
|
|
//3.将tinf补齐
|
tinf=new BatttestdataInf();
|
tinf.setBattGroupId(battGroupId);
|
tinf.setDataNew((byte) 1);
|
tinf.setDataAvailable((byte) 1);
|
tinf.setTestType(3);
|
tinf.setRecordTimeInterval(timeInterval);
|
tinf.setTestRecordCount(testRecordCount+1);
|
tinf.setTestRecordCountEx(testRecordCount+2);
|
tinf.setRecordNum(list.size()+1);
|
tinf.setTestStarttime(new Date());
|
tinf.setRecordTime(new Date());
|
tinf.setTestStarttype(3);
|
tinf.setTestTimelong(testTimelong);
|
tinf.setTestStoptype(1);
|
tinfService.insertTinf(tinf);
|
|
//2.将数据插入电池组数据
|
subService.insertAnaysisXlsToTdata(battGroupId,testRecordCount+1,list);
|
return new Response<>().set(1,true,"解析xls文件到放电数据中");
|
}
|
//查询当前Ai解析数据的程度
|
public Response getAnalysisSocket(AnalysisDto dto) {
|
//1.检测是否存在数据
|
int sum=subService.getAnaysisIdByTestRecordCount(dto.getBattGroupId(),dto.getTestRecordCount());
|
if(sum==0){
|
return new Response().setII(1,true,-1,"查询当前Ai解析数据的程度");
|
}
|
//检测书否存在补充的数据
|
int supple=subService.getAnaysisSupplement(dto.getBattGroupId(),dto.getTestRecordCount());
|
if(supple==0){
|
return new Response().setII(1,true,0,"查询当前Ai解析数据的程度");
|
}
|
//检测书否存在解析的数据
|
int anaNum=subService.getAnaysisNum(dto.getBattGroupId(),dto.getTestRecordCount());
|
return new Response().setII(1,true,anaNum,"查询当前Ai解析数据的程度");
|
}
|
|
//预估一个2小时后面的数据
|
public Response get2To10Data(int battGroupId, int testRecordCount1, int testRecordCount2) {
|
//查询上一次最后一次的数据
|
//1.查出需要预估的数据
|
List<PredictDataId> list2=subService.getPredictDataId(battGroupId,testRecordCount2);
|
int maxRecordNum=list2.get(list2.size()-1).getRecordNum();
|
//2.取出最后一笔数据
|
List<PredictDataId> listLast=subService.getPredictDataIdLast(battGroupId,testRecordCount2,maxRecordNum);
|
//3.取出预估对象的maxRecordNum之后的数据
|
List<PredictDataId> list1=subService.getPredictDataIdAgain(battGroupId,testRecordCount1,maxRecordNum);
|
//4.取出预估对象的maxRecordNum的数据
|
List<PredictDataId> listAgain=subService.getPredictDataIdLast(battGroupId,testRecordCount1,maxRecordNum);
|
Map<Integer,PredictDataId> mapDifference=new HashMap<>();
|
for (int i=0;i<listLast.size();i++){
|
PredictDataId dataLast=listLast.get(i);
|
PredictDataId dataAgain=listAgain.get(i);
|
PredictDataId data=new PredictDataId();
|
data.setTestTimelong(dataLast.getTestTimelong()-dataAgain.getTestTimelong());
|
data.setMonNum(dataLast.getMonNum());
|
data.setTestCurr(dataLast.getTestCurr()-dataAgain.getTestCurr());
|
data.setTestCap(dataLast.getTestCap()-dataAgain.getTestCap());
|
data.setMonVol(dataLast.getMonVol()- dataAgain.getMonVol());
|
data.setMonTmp(dataLast.getMonTmp()- dataAgain.getMonTmp());
|
mapDifference.put(dataLast.getMonNum(),data);
|
}
|
//将数据插入inf表和id表
|
//3.将tinf补齐
|
BatttestdataInf tinf=new BatttestdataInf();
|
tinf.setBattGroupId(battGroupId);
|
tinf.setDataNew((byte) 1);
|
tinf.setDataAvailable((byte) 1);
|
tinf.setTestType(3);
|
tinf.setRecordTimeInterval(0);
|
tinf.setTestRecordCount(testRecordCount2+1);
|
tinf.setTestRecordCountEx(testRecordCount2+2);
|
tinf.setRecordNum(0);
|
tinf.setTestStarttime(new Date());
|
tinf.setRecordTime(new Date());
|
tinf.setTestStarttype(3);
|
tinf.setTestTimelong(0);
|
tinf.setTestStoptype(1);
|
tinfService.insertTinf(tinf);
|
|
//2.将数据插入电池组数据
|
subService.addPredictDataId(battGroupId,testRecordCount2,list2,list1,mapDifference);
|
return new Response().set(1,true);
|
}
|
}
|
