package com.whyc.service;
|
|
import cn.hutool.json.JSONUtil;
|
import com.whyc.dto.InterfaceParam;
|
import com.whyc.dto.StandardFeatureCurve;
|
import com.whyc.dto.paramter.AlarmPar;
|
import com.whyc.dto.paramter.DevAlarmPar;
|
import com.whyc.mapper.CallBack;
|
import com.whyc.mapper.CommonMapper;
|
import com.whyc.mapper.StandardFeatureCurveMapper;
|
import com.whyc.pojo.BattalarmDataHistory;
|
import com.whyc.pojo.Battinf;
|
import com.whyc.pojo.PwrdevAlarmHistory;
|
import com.whyc.pojo.UserLog;
|
import com.whyc.util.ActionUtil;
|
import net.sf.json.JSONArray;
|
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.transaction.annotation.Transactional;
|
|
import javax.annotation.Resource;
|
import javax.swing.*;
|
import java.io.*;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class StandardFeatureCurveService {
|
|
@Resource
|
private StandardFeatureCurveMapper mapper;
|
|
@Resource
|
private CommonMapper commonMapper;
|
|
@Autowired
|
private MybatisSqlExecuteService sqlExecuteService;
|
|
@Autowired
|
private SubTablePageInfoService subService;
|
|
|
@Transactional
|
public void excelImport(InputStream inputStream) throws IOException, InvalidFormatException {
|
List<StandardFeatureCurve> curveList = new LinkedList<>();
|
//获取book
|
Workbook book = WorkbookFactory.create(inputStream);
|
int numberOfSheets = book.getNumberOfSheets();
|
for (int i = 0; i < numberOfSheets; i++) {
|
Sheet sheet = book.getSheetAt(i);
|
String sheetName = sheet.getSheetName();
|
int lastRowNum = sheet.getLastRowNum();
|
Row row1 = sheet.getRow(0);
|
//根据row1的列数来生成对象
|
short lastCellNum = row1.getLastCellNum();
|
for (int j = 1; j < lastRowNum; j++) {
|
//获取当前行
|
Row row = sheet.getRow(j);
|
Cell cellTime = row.getCell(0);
|
Date dateCellValue = cellTime.getDateCellValue();
|
String time = new SimpleDateFormat("HH:mm:ss").format(dateCellValue);
|
for (int k = 1; k < lastCellNum; k++) {
|
Cell cell = row.getCell(k);
|
if(cell == null){
|
break;
|
}
|
double value = cell.getNumericCellValue();
|
if(value == 0.0d){
|
break;
|
}
|
|
String titleNameUnion = sheetName+"_"+row1.getCell(k).getStringCellValue();
|
|
StandardFeatureCurve curve = new StandardFeatureCurve();
|
curve.setTitleNameUnion(titleNameUnion);
|
curve.setTime(time);
|
curve.setValue(value);
|
|
curveList.add(curve);
|
}
|
}
|
}
|
//存入数据库
|
Map<String, List<StandardFeatureCurve>> curveListMap = curveList.stream().collect(Collectors.groupingBy(StandardFeatureCurve::getTitleNameUnion));
|
curveListMap.forEach((titleNameUnion,curveList2)->{
|
//查询表是否存在,不存在需要先创建表
|
String tableName = commonMapper.existTable("web_site", "tb_standard_feature_curve_"+titleNameUnion);
|
if(tableName==null){
|
mapper.createTable(titleNameUnion);
|
}else{ //存在表,先清空数据
|
commonMapper.truncate("web_site", "tb_standard_feature_curve_"+titleNameUnion);
|
}
|
mapper.insertBatch(titleNameUnion,curveList2);
|
});
|
|
}
|
|
public List<StandardFeatureCurve> getList(String va, String c) {
|
if(commonMapper.existTable("web_site","tb_standard_feature_curve_"+va+"_"+c) == null){
|
return null;
|
}
|
// return mapper.getList(va,c);
|
return subService.getList(va,c);
|
}
|
//测试mybatisDb
|
/*public List testTableDb(String va, String c) {
|
String sql="select * from `web_site`.`tb_standard_feature_curve_"+va+"_"+c+"`";
|
List<StandardFeatureCurve> list = sqlExecuteService.executeQuery(sql);
|
return list;
|
}*/
|
public List testTableDb() {
|
Date date1 = null;
|
Date date2 = null;
|
try {
|
date1 = ActionUtil.sdf.parse("2023-03-27 10:10:01" );
|
date2 = ActionUtil.sdf.parse("2023-03-31 14:36:55");
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
/*2，3 "2020-03-22 15:11:23" "2020-03-23 15:11:23"
|
PwrdevAlarmHistory data=new PwrdevAlarmHistory();
|
data.setRecordYear("tb_pwrdev_alarm_history_2020");
|
data.setAlmStartTime(date1);
|
data.setAlmStartTime1(date2);
|
data.setAlmSource(0);
|
List typeList=new ArrayList();
|
typeList.add(110119);
|
List levelList=new ArrayList();
|
levelList.add(1);
|
levelList.add(2);
|
levelList.add(3);
|
levelList.add(4);
|
data.setAlmTypes(typeList);
|
data.setUsrId(1002);
|
data.setLimitStart(0);
|
data.setLimitEnd(10);
|
//List list=subService.getList2(data);
|
List list=subService.getListForTX(data);*/
|
/*4 "2022-05-10 09:20:09" "2022-05-21 16:21:53"
|
UserLog userLog=new UserLog();
|
userLog.setRecordYear("tb_user_log_2022");
|
userLog.setOperationTime(date1);
|
userLog.setOperationTime2(date2);
|
userLog.setEventType(1);
|
userLog.setOperationType(31);
|
int count=subService.getCount(userLog);
|
List list=new ArrayList();
|
list.add(count);*/
|
/*5
|
String tableName="tb_pwrdev_historydata_110000099_2021_06";
|
List list=subService.getData(tableName);*/
|
/*6 2023-04-11 16:08:20 2023-04-11 20:08:20
|
List list=subService.getListByParam(110000001l,"2023_04",date1,date2);*/
|
/*8
|
List list=subService.getList("122v150ah","0.15c");*/
|
/*9 "2022-09-27 08:26:37" "2022-09-27 09:04:40"
|
InterfaceParam param=new InterfaceParam();
|
List battGroupList=new ArrayList();
|
battGroupList.add(1000034);
|
battGroupList.add(1000030);
|
param.setBattgroupIds(battGroupList);
|
param.setRecordYear("tb_battalarm_data_history_2022_09");
|
param.setStartTime(date1);
|
param.setEndTime(date2);
|
param.setLimitStart(1);
|
param.setLimitEnd(10);
|
List list=subService.getListForInterface(param);*/
|
/*10 2020-03-22 15:11:23 2020-03-23 15:11:23
|
PwrdevAlarmHistory data=new PwrdevAlarmHistory();
|
data.setRecordYear("tb_pwrdev_alarm_history_2020");
|
data.setAlmStartTime(date1);
|
data.setAlmStartTime1(date2);
|
data.setAlmSource(0);
|
List typeList=new ArrayList();
|
typeList.add(110119);
|
List levelList=new ArrayList();
|
levelList.add(1);
|
levelList.add(2);
|
levelList.add(3);
|
levelList.add(4);
|
data.setAlmTypes(typeList);
|
data.setUsrId(1002);
|
data.setLimitStart(0);
|
data.setLimitEnd(10);
|
int count=subService.getCount(data);
|
List list=new ArrayList();
|
list.add(count);*/
|
/*13
|
DevAlarmPar param=new DevAlarmPar();
|
param.setRecordYear("tb_devalarm_data_history_2023_03");
|
param.setAlmStartTime(date1);
|
param.setAlmStartTime1(date2);
|
param.setStationName("");
|
param.setStationName1("");
|
param.setUId(1002);
|
int count=subService.getCount(param);
|
List list=new ArrayList();
|
list.add(count);*/
|
/*15
|
List list=subService.getList(1002,"tb_pwrdev_alarm_param_115000001");*/
|
/*16
|
DevAlarmPar param=new DevAlarmPar();
|
param.setRecordYear("tb_devalarm_data_history_2023_03");
|
param.setAlmStartTime(date1);
|
param.setAlmStartTime1(date2);
|
param.setStationName("");
|
param.setStationName1("");
|
param.setLimitStart(0);
|
param.setLimitEnd(10);
|
param.setUId(1002);
|
List list=subService.getList2(param);*/
|
/*21 "2022-05-10 09:20:09" 2022-05-21 16:21:53
|
UserLog userLog=new UserLog();
|
userLog.setRecordYear("tb_user_log_2022");
|
userLog.setOperationTime(date1);
|
userLog.setOperationTime2(date2);
|
userLog.setEventType(1);
|
userLog.setOperationType(31);
|
userLog.setLimitStart(0);
|
userLog.setLimitEnd(10);
|
List list= subService.getList2(userLog);*/
|
/*23
|
InterfaceParam param=new InterfaceParam();
|
param.setRecordYear("tb_devalarm_data_history_2023_03");
|
param.setStartTime(date1);
|
param.setEndTime(date2);
|
List devList=new ArrayList();
|
devList.add(618500017);
|
devList.add(481500011);
|
param.setDeviceIds(devList);
|
param.setLimitStart(0);
|
param.setLimitEnd(10);
|
List list=subService.getListForInterface_dev(param);*/
|
/*26
|
List List=new ArrayList();
|
for (int i=0;i<4;i++){
|
BattalarmDataHistory bh=new BattalarmDataHistory();
|
bh.setBattGroupId(1000021+i);
|
bh.setMonNum(1+i);
|
bh.setRecordId(-6069479889549612607l);
|
bh.setAlmId(119005+i);
|
bh.setAlmSignalId(1+i);
|
bh.setAlmLevel(1+i);
|
bh.setAlmStartTime(date1);
|
bh.setAlmEndTime(date2);
|
bh.setAlmValue(2.53f+i);
|
bh.setAlmIsConfirmed(0+i);
|
bh.setAlmConfirmedTime(date1);
|
bh.setAlmClearedType(1+i);
|
bh.setUsrId(1002+i);
|
bh.setAlmSeverity(1+i);
|
List.add(bh);
|
}
|
subService.insertBatch4BattAlarmDataHistoryByYear("2022_09",List);*/
|
/*28
|
subService.create_standard_curve("tb_battmon_testcap_2_1000_15");*/
|
/*29
|
PwrdevAlarmHistory data=new PwrdevAlarmHistory();
|
data.setRecordYear("tb_pwrdev_alarm_history_2020");
|
data.setAlmStartTime(date1);
|
data.setAlmStartTime1(date2);
|
data.setAlmSource(0);
|
List typeList=new ArrayList();
|
typeList.add(110119);
|
List levelList=new ArrayList();
|
levelList.add(1);
|
levelList.add(2);
|
levelList.add(3);
|
levelList.add(4);
|
data.setAlmTypes(typeList);
|
data.setUsrId(1002);
|
data.setLimitStart(0);
|
data.setLimitEnd(10);
|
int count=subService.getCountForTX(data);
|
List list=new ArrayList();
|
list.add(count);*/
|
/*32
|
List list=subService.searchAll("tb_pwrdev_alarm_param_110000001");*/
|
/*34
|
InterfaceParam param=new InterfaceParam();
|
param.setRecordYear("tb_devalarm_data_history_2023_03");
|
param.setStartTime(date1);
|
param.setEndTime(date2);
|
List devList=new ArrayList();
|
devList.add(618500017);
|
devList.add(481500011);
|
param.setDeviceIds(devList);
|
param.setLimitStart(0);
|
param.setLimitEnd(10);
|
int count=subService.getCountForInterface(param);
|
List list=new ArrayList();
|
list.add(count);*/
|
AlarmPar param=new AlarmPar();
|
param.setRecordYear("tb_battalarm_data_history_2023_03");
|
param.setAlmIdOne(119001);
|
param.setAlmIdTwo(119002);
|
param.setAlmIdThree(119003);
|
param.setAlmIdFour(119004);
|
param.setAlmIdFive(119005);
|
param.setAlmIdSix(119006);
|
param.setAlmIdSeven(1190011);
|
param.setAlmIdEight(1190012);
|
param.setAlmSignalIdOne(0);
|
param.setAlmSignalIdTwo(1);
|
param.setAlmLevelOne(1);
|
param.setAlmLevelTwo(2);
|
param.setAlmLevelThree(3);
|
param.setAlmLevelFour(4);
|
param.setStationname("");
|
param.setStationname1("");
|
param.setAlmStartTime(date1);
|
param.setAlmStartTime1(date2);
|
param.setBattGroupId(0);
|
param.setUId(1002);
|
param.setLimitStart(0);
|
param.setLimitEnd(100);
|
List list= subService.getList2(param);
|
return list;
|
}
|
}
|
