package com.whyc.service;
|
|
import cn.afterturn.easypoi.entity.ImageEntity;
|
import cn.afterturn.easypoi.excel.ExcelExportUtil;
|
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
|
import com.google.common.collect.Maps;
|
import com.whyc.dto.BattGroupStationInfoWithFileParam;
|
import com.whyc.dto.Response;
|
import com.whyc.pojo.BattgroupData;
|
import com.whyc.pojo.FileParam;
|
import com.whyc.pojo.StationInfo;
|
import com.whyc.pojo.TestParam;
|
import com.whyc.util.MathUtil;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.core.io.ClassPathResource;
|
import org.springframework.stereotype.Service;
|
import sun.misc.BASE64Decoder;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.net.URLEncoder;
|
import java.text.NumberFormat;
|
import java.text.ParseException;
|
import java.text.ParsePosition;
|
import java.text.SimpleDateFormat;
|
import java.time.LocalDateTime;
|
import java.time.format.DateTimeFormatter;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class ExcelExportService {
|
|
@Autowired
|
private FileParamService fileParamService;
|
|
@Autowired
|
private BattGroupDataService battGroupDataService;
|
|
@Autowired
|
private StationInfoService stationInfoService;
|
|
@Autowired
|
private TestParamService testParamService;
|
|
public void resTestReport(BattGroupStationInfoWithFileParam info, HttpServletResponse response) throws IOException {
|
HashMap<String, Object> map = Maps.newHashMap();
|
//总体评价:内阻/电压/连接条/?容量
|
int resGoodCount = 0;
|
int volGoodCount = 0;
|
int chainResGoodCount = 0;
|
//总体评价:有效的电阻/电压/连接条 数
|
int battVolTestCount = 0;
|
int battResTestCount = 0;
|
int battChainResTestCount = 0;
|
FileParam param = info.getFileParam();
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
param.setTestTimeStr(format.format(param.getTestTime()));
|
//查询全局的测试系数和参数
|
TestParam param2 = testParamService.getFactorsAndThreshold();
|
//判断测试数据告警范围
|
Float stdRes = param.getBattRes();
|
Float volLowCoeK1 = null;
|
Float volHighCoeK2 = null;
|
//电压不同,电压阈值不同
|
if(param.getBattVol() == 2){
|
volLowCoeK1 = param2.getVol2LowCoeK1();
|
volHighCoeK2 = param2.getVol2HighCoeK2();
|
}
|
else if(param.getBattVol() == 1.2){
|
volLowCoeK1 = param2.getVol1d2LowCoeK1();
|
volHighCoeK2 = param2.getVol1d2HighCoeK2();
|
}
|
else if(param.getBattVol() == 6){
|
volLowCoeK1 = param2.getVol6LowCoeK1();
|
volHighCoeK2 = param2.getVol6HighCoeK2();
|
}
|
else if(param.getBattVol() == 12){
|
volLowCoeK1 = param2.getVol12LowCoeK1();
|
volHighCoeK2 = param2.getVol12HighCoeK2();
|
}else{ //电压非常规值,则默认设置为2v的电压阈值
|
volLowCoeK1 = param2.getVol2LowCoeK1();
|
volHighCoeK2 = param2.getVol2HighCoeK2();
|
}
|
Float resGoodCoeK3 = param2.getResGoodCoeK3();
|
Float resBadCoeK4 = param2.getResBadCoeK4();
|
Float chainRes = param.getVersion().toUpperCase().contains("V2.0")?param.getChainRes():param.getConnRes();
|
Float finalVolLowCoeK = volLowCoeK1;
|
Float finalVolHighCoeK = volHighCoeK2;
|
List<BattgroupData> dataList = info.getDataList();
|
for(BattgroupData data : dataList){
|
//计算电压,内阻,连接条数值是否有效,被测试过
|
float bv = Float.parseFloat(data.getBv());
|
float br = Float.parseFloat(data.getBr());
|
float cr = Float.parseFloat(data.getCr());
|
if (bv!=0.0){
|
battVolTestCount++;
|
}
|
if(br!=0.0){
|
battResTestCount++;
|
}
|
if (cr!=0.0){
|
battChainResTestCount++;
|
}
|
|
data.setMonNumStr("#"+data.getMonNum());
|
if(br==0.0){
|
data.setResEvaluation("—");
|
}
|
/*else if(!data.isHighResFlag1() && !data.isHighResFlag2()){
|
resGoodCount +=1;
|
data.setResEvaluation("优");
|
}else if(data.isHighResFlag1()){
|
data.setResEvaluation("良");
|
}else{
|
data.setResEvaluation("差");
|
}*/
|
else if(br >= stdRes*resGoodCoeK3/100){
|
if(br >= stdRes*resBadCoeK4/100){
|
// data.setHighResFlag2(true);
|
data.setResEvaluation("差");
|
}else{
|
// data.setHighResFlag1(true);
|
data.setResEvaluation("良");
|
}
|
}else{
|
resGoodCount +=1;
|
data.setResEvaluation("优");
|
}
|
/*if(bv!=0.0 && !data.isLowVolFlag() && !data.isHighVolFlag()){
|
volGoodCount +=1;
|
}*/
|
if(bv!=0.0 && bv > finalVolLowCoeK && bv < finalVolHighCoeK){
|
volGoodCount +=1;
|
}
|
/*if(cr!=0.0 && !data.isHighChainResFlag1() && !data.isHighChainResFlag2()){
|
chainResGoodCount +=1;
|
}*/
|
if(cr!=0.0 && cr < chainRes*param2.getChainResCoeK5()/100){
|
chainResGoodCount +=1;
|
}
|
};
|
info.setResGoodCount(resGoodCount);
|
info.setVolGoodCount(volGoodCount);
|
info.setChainResGoodCount(chainResGoodCount);
|
|
info.setBattVolTestCount(battVolTestCount);
|
info.setBattResTestCount(battResTestCount);
|
info.setBattChainResTestCount(battChainResTestCount);
|
|
StationInfo stationInfo = info.getStationInfo();
|
if(stationInfo!=null) {
|
if (stationInfo.getStationName2().equals("-")) {
|
stationInfo.setStationName(stationInfo.getStationName1());
|
} else {
|
stationInfo.setStationName(stationInfo.getStationName1() + "-" + stationInfo.getStationName2());
|
}
|
}
|
map.put("dto", info);
|
//图片
|
BASE64Decoder decoder = new BASE64Decoder();
|
if(!info.getVolPic().equals("")) {
|
ImageEntity volImage = new ImageEntity();
|
volImage.setData(decoder.decodeBuffer(info.getVolPic().substring(info.getVolPic().indexOf(",") + 1)));
|
volImage.setColspan(6);
|
volImage.setRowspan(17);
|
|
map.put("volImage",volImage);
|
}
|
if(!info.getResPic().equals("")) {
|
ImageEntity resImage = new ImageEntity();
|
resImage.setData(decoder.decodeBuffer(info.getResPic().substring(info.getResPic().indexOf(",") + 1)));
|
resImage.setColspan(6);
|
resImage.setRowspan(16);
|
|
map.put("resImage", resImage);
|
}
|
|
//map.put("volImage","volImage");
|
//map.put("resImage","resImage");
|
|
//获取导出模板地址
|
ClassPathResource classPathResource = new ClassPathResource("excel_templates/res_test_report_single_template.xls");
|
String path = classPathResource.getPath();
|
TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
|
Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
|
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
|
String fileName = "内阻计测试"+time+".xls";
|
try {
|
//response.setContentType("application/octet-stream;charset=utf-8");
|
response.setContentType("application/vnd.ms-excel");
|
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
|
response.flushBuffer();
|
wb.write(response.getOutputStream());
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
public void resTestCompareReport(String fileId, String fileId2, BattGroupStationInfoWithFileParam dto, HttpServletResponse response) throws IOException {
|
Map<String,Object> map = new HashMap<>();
|
Response comparedList = battGroupDataService.getComparedList(fileId, fileId2);
|
List<BattgroupData> dataList = (List<BattgroupData>) comparedList.getData();
|
List<BattgroupData> dataList2 = (List<BattgroupData>) comparedList.getData2();
|
Map<String,List<String>> data3 = (Map<String, List<String>>) comparedList.getData3();
|
//变化率
|
List<String> resChangeRateList = data3.get("resChangeRate");
|
List<String> volChangeRateList = data3.get("volChangeRate");
|
map.put("resChangeRateList",resChangeRateList.subList(0,resChangeRateList.size()-1));
|
map.put("volChangeRateList",volChangeRateList.subList(0,volChangeRateList.size()-1));
|
|
map.put("volBalanceRateChangeRate",volChangeRateList.get(volChangeRateList.size()-1));
|
map.put("resBalanceRateChangeRate",resChangeRateList.get(resChangeRateList.size()-1));
|
//均一性:res/vol
|
List<String> resBalanceRateList = data3.get("resBalanceRate");
|
List<String> volBalanceRateList = data3.get("volBalanceRate");
|
//均一性:chainRes/?容量/bs(电导)
|
NumberFormat percentInstance = NumberFormat.getPercentInstance();
|
percentInstance.setMinimumFractionDigits(2);
|
|
List<Float> chainResList = dataList2.stream().map(BattgroupData::getCr).map(Float::parseFloat).collect(Collectors.toList());
|
List<Float> bsList = dataList2.stream().map(BattgroupData::getBs).map(Float::parseFloat).collect(Collectors.toList());
|
String chainResBalanceRate = percentInstance.format(MathUtil.getStandardDeviation(chainResList));
|
String bsBalanceRate = percentInstance.format(MathUtil.getStandardDeviation(bsList));
|
//获取FileParam和StationInfo
|
StationInfo stationInfo = stationInfoService.getByFileId(fileId2);
|
FileParam param = fileParamService.getByFileId(fileId2);
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
param.setTestTimeStr(format.format(param.getTestTime()));
|
|
FileParam paramBase = fileParamService.getByFileId(fileId);
|
String testTimeStrBase = format.format(paramBase.getTestTime());
|
//图片
|
BASE64Decoder decoder = new BASE64Decoder();
|
if(!dto.getVolPic().equals("")) {
|
ImageEntity volImage = new ImageEntity();
|
volImage.setData(decoder.decodeBuffer(dto.getVolPic().substring(dto.getVolPic().indexOf(",") + 1)));
|
volImage.setColspan(6);
|
volImage.setRowspan(17);
|
|
map.put("volImage",volImage);
|
}
|
|
if(!dto.getResPic().equals("")) {
|
ImageEntity resImage = new ImageEntity();
|
resImage.setData(decoder.decodeBuffer(dto.getResPic().substring(dto.getResPic().indexOf(",") + 1)));
|
resImage.setColspan(6);
|
resImage.setRowspan(16);
|
|
map.put("resImage",resImage);
|
}
|
|
if(stationInfo.getStationName2().equals("-")){
|
stationInfo.setStationName(stationInfo.getStationName1());
|
}else{
|
stationInfo.setStationName(stationInfo.getStationName1()+"-"+stationInfo.getStationName2());
|
}
|
|
map.put("fileParam",param);
|
map.put("stationInfo",stationInfo);
|
//总体评价:内阻/电压/连接条/?容量
|
int resGoodCount = 0;
|
int volGoodCount = 0;
|
int chainResGoodCount = 0;
|
|
//总体评价:有效的电阻/电压/连接条 数
|
int battVolTestCount = 0;
|
int battResTestCount = 0;
|
int battChainResTestCount = 0;
|
|
for(BattgroupData data : dataList2){
|
//计算电压,内阻,连接条数值是否有效,被测试过
|
float bv = Float.parseFloat(data.getBv());
|
float br = Float.parseFloat(data.getBr());
|
float cr = Float.parseFloat(data.getCr());
|
if (bv!=0.0){
|
battVolTestCount++;
|
}
|
if(br!=0.0){
|
battResTestCount++;
|
}
|
if (cr!=0.0){
|
battChainResTestCount++;
|
}
|
|
data.setMonNumStr("#"+data.getMonNum());
|
if(br==0.0){
|
data.setResEvaluation("—");
|
}
|
else if(!data.isHighResFlag1() && !data.isHighResFlag2() ){
|
resGoodCount +=1;
|
data.setResEvaluation("优");
|
}else if(data.isHighResFlag1()){
|
data.setResEvaluation("良");
|
}else{
|
data.setResEvaluation("差");
|
}
|
if(bv!=0.0 && !data.isLowVolFlag() && !data.isHighVolFlag()){
|
volGoodCount +=1;
|
}
|
if(cr!=0.0 && !data.isHighChainResFlag1() && !data.isHighChainResFlag2()){
|
chainResGoodCount +=1;
|
}
|
};
|
map.put("resGoodCount",resGoodCount);
|
map.put("volGoodCount",volGoodCount);
|
map.put("chainResGoodCount",chainResGoodCount);
|
|
map.put("battVolTestCount",battVolTestCount);
|
map.put("battResTestCount",battResTestCount);
|
map.put("battChainResTestCount",battChainResTestCount);
|
|
map.put("dataList",dataList);
|
map.put("dataList2",dataList2);
|
|
map.put("stdDevBv",volBalanceRateList.get(1));
|
map.put("stdDevBr",resBalanceRateList.get(1));
|
map.put("stdDevCr",chainResBalanceRate);
|
map.put("stdDevBs",bsBalanceRate);
|
map.put("testTimeStrBase",testTimeStrBase);
|
|
map.put("stdDevBvBase",volBalanceRateList.get(0));
|
map.put("stdDevBrBase",resBalanceRateList.get(0));
|
|
|
//获取导出模板地址
|
ClassPathResource classPathResource = new ClassPathResource("excel_templates/res_test_report_compare_template.xls");
|
String path = classPathResource.getPath();
|
TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
|
templateExportParams1.setColForEach(true);
|
Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
|
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
|
String fileName = "内阻计测试"+time+".xls";
|
try {
|
//response.setContentType("application/octet-stream;charset=utf-8");
|
response.setContentType("application/vnd.ms-excel");
|
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
|
response.flushBuffer();
|
wb.write(response.getOutputStream());
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
