package org.jeecg.modules.quartz.job;
|
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.collections.CollectionUtils;
|
import org.apache.commons.csv.CSVFormat;
|
import org.apache.commons.csv.CSVPrinter;
|
import org.jeecg.modules.iot.entity.Equipment;
|
import org.jeecg.modules.iot.entity.EquipmentCsvData;
|
import org.jeecg.modules.iot.service.IEquipmentService;
|
import org.quartz.Job;
|
import org.quartz.JobExecutionContext;
|
import org.quartz.JobExecutionException;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.datasource.DataSourceUtils;
|
|
import javax.sql.DataSource;
|
import java.io.File;
|
import java.io.FileWriter;
|
import java.io.IOException;
|
import java.lang.reflect.Method;
|
import java.sql.*;
|
import java.time.LocalDateTime;
|
import java.time.format.DateTimeFormatter;
|
import java.time.format.DateTimeParseException;
|
import java.time.temporal.ChronoUnit;
|
import java.util.*;
|
|
/**
|
* 定时生成csv文件
|
*
|
* @Author Scott
|
*/
|
@Slf4j
|
public class ParameterCsvJob implements Job {
|
|
@Autowired
|
private IEquipmentService equipmentService;
|
@Autowired
|
private DataSource dataSource;
|
// CSV文件保存路径
|
private static final String CSV_FILE_PATH = "D:/iot/";
|
private static final Map<String, String> FIELD_MAPPING = new HashMap<>();
|
|
static {
|
// 初始化字段映射：key是数据库字段名，value是CSV/实体类字段名
|
FIELD_MAPPING.put("CollectTime", "k_ts");
|
FIELD_MAPPING.put("EquipmentID", "k_device");
|
FIELD_MAPPING.put("ProgramNo", "ProgramNumber");
|
// 可以添加其他需要映射的字段
|
}
|
|
private static final List<DateTimeFormatter> INPUT_FORMATTERS = Arrays.asList(
|
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"), // 标准格式（目标格式）
|
DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss"), // 斜杠分隔
|
DateTimeFormatter.ofPattern("yyyy-M-d H:m:s"), // 不补零格式
|
DateTimeFormatter.ofPattern("yyyy/MM/d HH:mm:ss"), // 混合格式
|
DateTimeFormatter.ISO_LOCAL_DATE_TIME // 带T的格式（如2025-09-15T10:21:11）
|
);
|
|
// 输出格式：严格指定为yyyy-MM-dd HH:mm:ss（补零+连字符）
|
private static final DateTimeFormatter OUTPUT_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
|
|
@Override
|
public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
|
// 查询所有虚设备
|
List<Equipment> equipmentByTypeList = equipmentService.findEquipmentByTypeList();
|
if (CollectionUtils.isNotEmpty(equipmentByTypeList)) {
|
// 遍历虚设备
|
equipmentByTypeList.forEach(eq -> {
|
String tableName = eq.getControlSystem() + "_" + eq.getEqptCode().replace('-', '_');
|
if (doesTheTableExist(tableName)) {
|
queryLastHourDataAndExportToCsv(tableName);
|
}
|
});
|
}
|
}
|
|
// 验证是否存在
|
Boolean doesTheTableExist(String tableName) {
|
boolean tableExists = false;
|
// 获取数据库连接
|
try (Connection connection = dataSource.getConnection()) {
|
// 查询虚设备是否有单表
|
// 获取数据库产品名称以区分数据库类型
|
String databaseProductName = connection.getMetaData().getDatabaseProductName();
|
// 表名转换
|
if (databaseProductName.contains("MySQL")) {
|
// MySQL: 查询information_schema库
|
String sql = "SELECT COUNT(*) FROM information_schema.tables " +
|
"WHERE table_schema = DATABASE() AND table_name = ?";
|
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
|
stmt.setString(1, tableName);
|
try (ResultSet rs = stmt.executeQuery()) {
|
if (rs.next()) {
|
tableExists = rs.getInt(1) > 0;
|
}
|
}
|
}
|
} else if (databaseProductName.contains("SQL Server")) {
|
// SQL Server: 查询sys.tables
|
String sql = "SELECT COUNT(*) FROM sys.tables WHERE name = ?";
|
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
|
stmt.setString(1, tableName);
|
try (ResultSet rs = stmt.executeQuery()) {
|
if (rs.next()) {
|
tableExists = rs.getInt(1) > 0;
|
}
|
}
|
}
|
} else {
|
log.info("不支持的数据库类型: " + databaseProductName);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return tableExists;
|
}
|
|
public void queryLastHourDataAndExportToCsv(String tableName) {
|
LocalDateTime now = LocalDateTime.now();
|
LocalDateTime lastHourStart = now.truncatedTo(ChronoUnit.HOURS).minusHours(1);
|
LocalDateTime lastHourEnd = lastHourStart.plusHours(1).minusSeconds(1);
|
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
|
String startTimeStr = lastHourStart.format(formatter);
|
String endTimeStr = lastHourEnd.format(formatter);
|
log.info("查询时间范围：{} ~ {}", startTimeStr, endTimeStr);
|
|
String sql = String.format("SELECT * FROM %s WHERE CollectTime BETWEEN ? AND ?", tableName);
|
List<EquipmentCsvData> dataList = new ArrayList<>();
|
|
try (Connection conn = DataSourceUtils.getConnection(dataSource);
|
PreparedStatement pstmt = conn.prepareStatement(sql)) {
|
pstmt.setString(1, startTimeStr);
|
pstmt.setString(2, endTimeStr);
|
|
try (ResultSet rs = pstmt.executeQuery()) {
|
ResultSetMetaData metaData = rs.getMetaData();
|
int columnCount = metaData.getColumnCount();
|
log.debug("数据库返回字段总数：{}，字段列表：{}", columnCount, getColumnNames(metaData));
|
|
// 验证关键字段是否存在
|
boolean hasCollectTime = false;
|
boolean hasEquipmentID = false;
|
for (int i = 1; i <= columnCount; i++) {
|
String colName = metaData.getColumnName(i);
|
if ("CollectTime".equals(colName)) hasCollectTime = true;
|
if ("EquipmentID".equals(colName)) hasEquipmentID = true;
|
}
|
log.info("数据库是否包含CollectTime：{}，是否包含EquipmentID：{}", hasCollectTime, hasEquipmentID);
|
|
int totalCount = 0;
|
while (rs.next()) {
|
totalCount++;
|
EquipmentCsvData csvData = new EquipmentCsvData();
|
|
// 手动处理关键字段（确保映射生效）
|
handleCriticalFields(csvData, rs);
|
|
// 处理其他字段
|
for (int i = 1; i <= columnCount; i++) {
|
String dbColName = metaData.getColumnName(i);
|
// 跳过已手动处理的字段
|
if ("CollectTime".equals(dbColName) || "EquipmentID".equals(dbColName)) {
|
continue;
|
}
|
Object value = rs.getObject(i);
|
setFieldValue(csvData, dbColName, value);
|
}
|
|
dataList.add(csvData);
|
log.debug("第{}条数据：k_device={}, k_ts={}",
|
totalCount, csvData.getK_device(), csvData.getK_ts());
|
}
|
log.info("查询完成，共{}条数据", totalCount);
|
}
|
} catch (SQLException e) {
|
log.error("查询失败", e);
|
return;
|
}
|
|
if (!dataList.isEmpty()) {
|
String fileName = CSV_FILE_PATH + tableName + "_" +
|
lastHourStart.format(DateTimeFormatter.ofPattern("yyyyMMddHH")) + ".csv";
|
exportToCsv(dataList, fileName);
|
} else {
|
log.info("无数据导出");
|
}
|
}
|
|
// 手动处理关键字段（避免反射失败）
|
private void handleCriticalFields(EquipmentCsvData csvData, ResultSet rs) throws SQLException {
|
// 机组状态
|
Object status = rs.getObject("Oporation");
|
if (status != null) {
|
String statusStr = status.toString().trim();
|
// 状态值转换映射
|
switch (statusStr) {
|
case "0":
|
csvData.setStatus("3");
|
break;
|
case "2":
|
csvData.setStatus("6");
|
break;
|
case "3":
|
csvData.setStatus("10");
|
break;
|
}
|
// 机组状态
|
Object isAlarm = rs.getObject("lsAlarm");
|
if (isAlarm != null && status.toString().trim().equals("True")) {
|
csvData.setStatus("16");
|
}
|
}
|
// 设备状态
|
Object oporation = rs.getObject("Oporation");
|
if (oporation != null) {
|
csvData.setThreeColorIndicatorState(oporation.toString().trim());
|
}
|
// 处理EquipmentID→k_device（保持不变）
|
Object equipmentId = rs.getObject("EquipmentID");
|
if (equipmentId != null) {
|
csvData.setK_device(equipmentId.toString().trim());
|
}
|
// 程序编号
|
Object programNumber = rs.getObject("ProgramNumber");
|
if (programNumber != null) {
|
csvData.setProgramNo(programNumber.toString().trim());
|
}
|
// 进给倍率
|
Object feedOverride = rs.getObject("FeedOverride");
|
if (feedOverride != null) {
|
csvData.setFeedrateOverride(feedOverride.toString().trim());
|
}
|
// 报警代码
|
Object alarmNo = rs.getObject("AlarmNo");
|
if (alarmNo != null) {
|
csvData.setAlarmCode(alarmNo.toString().trim());
|
}
|
// 报警内容
|
Object alarmInfo = rs.getObject("AlarmInfo");
|
if (alarmInfo != null) {
|
csvData.setAlarmMessage(alarmInfo.toString().trim());
|
}
|
// 主轴转速
|
Object actualSpindleSpeed = rs.getObject("ActualSpindleSpeed");
|
if (actualSpindleSpeed != null) {
|
csvData.setSpindleSpeed(actualSpindleSpeed.toString().trim());
|
}
|
// 进给速度
|
Object actualFeedRate = rs.getObject("ActualFeedRate");
|
if (actualSpindleSpeed != null) {
|
csvData.setFeedRate(actualFeedRate.toString().trim());
|
}
|
// 当前执行行号
|
Object executingCode = rs.getObject("ExecutingCode");
|
if (executingCode != null) {
|
csvData.setCurrentExecutionLine(executingCode.toString().trim());
|
}
|
// 机械坐标
|
Object xmachine = rs.getObject("Xmachine");
|
if (xmachine != null) {
|
csvData.setXmechanicalCoordinate(xmachine.toString().trim());
|
}
|
// 机械坐标
|
Object ymachine = rs.getObject("Ymachine");
|
if (ymachine != null) {
|
csvData.setYmechanicalCoordinate(ymachine.toString().trim());
|
}
|
// 机械坐标
|
Object zmachine = rs.getObject("Zmachine");
|
if (zmachine != null) {
|
csvData.setZmechanicalCoordinate(zmachine.toString().trim());
|
}
|
// 机械坐标
|
Object amachine = rs.getObject("Amachine");
|
if (amachine != null) {
|
csvData.setAmechanicalCoordinateA(amachine.toString().trim());
|
}
|
// 机械坐标
|
Object bmachine = rs.getObject("Bmachine");
|
if (bmachine != null) {
|
csvData.setBmechanicalCoordinateB(bmachine.toString().trim());
|
}
|
// 绝对坐标
|
Object aabsolute = rs.getObject("Aabsolute");
|
if (aabsolute != null) {
|
csvData.setAabsoluteCoordinate(aabsolute.toString().trim());
|
}
|
// 绝对坐标
|
Object babsolute = rs.getObject("Babsolute");
|
if (babsolute != null) {
|
csvData.setBabsoluteCoordinate(babsolute.toString().trim());
|
}
|
// 绝对坐标
|
Object xabsolute = rs.getObject("Xabsolute");
|
if (xabsolute != null) {
|
csvData.setXabsoluteCoordinate(xabsolute.toString().trim());
|
}
|
// 绝对坐标
|
Object yabsolute = rs.getObject("Yabsolute");
|
if (babsolute != null) {
|
csvData.setYabsoluteCoordinate(yabsolute.toString().trim());
|
}
|
// 绝对坐标
|
Object zabsolute = rs.getObject("Zabsolute");
|
if (zabsolute != null) {
|
csvData.setZabsoluteCoordinate(zabsolute.toString().trim());
|
}
|
// 相对坐标
|
Object arelative = rs.getObject("Arelative");
|
if (zabsolute != null) {
|
csvData.setZabsoluteCoordinate(arelative.toString().trim());
|
}
|
// 处理CollectTime→k_ts（使用改进的转换方法）
|
Object collectTimeObj = rs.getObject("CollectTime");
|
if (collectTimeObj != null) {
|
String formattedTime = convertTimeFormat(collectTimeObj.toString());
|
csvData.setK_ts(formattedTime);
|
}
|
}
|
|
// 处理其他字段的通用方法
|
private void setFieldValue(EquipmentCsvData csvData, String dbColName, Object value) {
|
if (value == null) return;
|
|
String fieldName = FIELD_MAPPING.getOrDefault(dbColName, dbColName);
|
fieldName = fieldName.replaceAll("[^a-zA-Z0-9]", "");
|
|
try {
|
String setterName = "set" +
|
fieldName.substring(0, 1).toUpperCase() +
|
fieldName.substring(1);
|
Method setter = EquipmentCsvData.class.getMethod(setterName, String.class);
|
setter.invoke(csvData, value.toString());
|
} catch (Exception e) {
|
log.debug("字段{}设置失败（可忽略）", fieldName);
|
}
|
}
|
|
private void exportToCsv(List<EquipmentCsvData> dataList, String filePath) {
|
File file = new File(filePath);
|
File parent = file.getParentFile();
|
if (parent != null && !parent.exists()) {
|
parent.mkdirs();
|
}
|
|
try (FileWriter writer = new FileWriter(file);
|
CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
|
.withHeader("k_device", "k_ts", "IsConnected", "Status",
|
"ThreeColorIndicatorState", "ProgramName", "ProgramNo",
|
"SubProgramNo", "SpindleOverride", "FeedrateOverride",
|
"AlarmCode", "AlarmMessage", "SpindleLoad", "SpindleSpeed",
|
"FeedRate", "CurrentExecutionLine", "ExecutingCode",
|
"ToolNo", "XmechanicalCoordinate", "YmechanicalCoordinate",
|
"ZmechanicalCoordinate", "AmechanicalCoordinateA",
|
"BmechanicalCoordinateB", "XabsoluteCoordinate",
|
"YabsoluteCoordinate", "ZabsoluteCoordinate",
|
"AabsoluteCoordinate", "BabsoluteCoordinate", "VTVersion",
|
"SerialNumber", "Temperature", "XaccelerationRMS",
|
"YaccelerationRMS", "ZaccelerationRMS", "XvelocityRMS",
|
"YvelocityRMS", "ZvelocityRMS", "XdisplacementRMS",
|
"YdisplacementRMS", "ZdisplacementRMS", "XvelocityMax",
|
"YvelocityMax", "ZvelocityMax", "XaccelerationMax",
|
"YaccelerationMax", "ZaccelerationMax", "XdisplacementMax",
|
"YdisplacementMax", "ZdisplacementMax", "AccelerationHrate",
|
"AccelerationLrate", "VelocityHrate", "VelocityLrate",
|
"DisplacementHrate", "DisplacementLrate"))) {
|
|
for (EquipmentCsvData data : dataList) {
|
csvPrinter.printRecord(
|
data.getK_device(),
|
data.getK_ts(),
|
data.getIsConnected(),
|
data.getStatus(),
|
data.getThreeColorIndicatorState(),
|
data.getProgramName(),
|
data.getProgramNo(),
|
data.getSubProgramNo(),
|
data.getSpindleOverride(),
|
data.getFeedrateOverride(),
|
data.getAlarmCode(),
|
data.getAlarmMessage(),
|
data.getSpindleLoad(),
|
data.getSpindleSpeed(),
|
data.getFeedRate(),
|
data.getCurrentExecutionLine(),
|
data.getExecutingCode(),
|
data.getToolNo(),
|
data.getXmechanicalCoordinate(),
|
data.getYmechanicalCoordinate(),
|
data.getZmechanicalCoordinate(),
|
data.getAmechanicalCoordinateA(),
|
data.getBmechanicalCoordinateB(),
|
data.getXabsoluteCoordinate(),
|
data.getYabsoluteCoordinate(),
|
data.getZabsoluteCoordinate(),
|
data.getAabsoluteCoordinate(),
|
data.getBabsoluteCoordinate(),
|
data.getVTVersion(),
|
data.getSerialNumber(),
|
data.getTemperature(),
|
data.getXaccelerationRMS(),
|
data.getYaccelerationRMS(),
|
data.getZaccelerationRMS(),
|
data.getXvelocityRMS(),
|
data.getYvelocityRMS(),
|
data.getZvelocityRMS(),
|
data.getXdisplacementRMS(),
|
data.getYdisplacementRMS(),
|
data.getZdisplacementRMS(),
|
data.getXvelocityMax(),
|
data.getYvelocityMax(),
|
data.getZvelocityMax(),
|
data.getXaccelerationMax(),
|
data.getYaccelerationMax(),
|
data.getZaccelerationMax(),
|
data.getXdisplacementMax(),
|
data.getYdisplacementMax(),
|
data.getZdisplacementMax(),
|
data.getAccelerationHrate(),
|
data.getAccelerationLrate(),
|
data.getVelocityHrate(),
|
data.getVelocityLrate(),
|
data.getDisplacementHrate(),
|
data.getDisplacementLrate()
|
);
|
}
|
log.info("CSV导出成功：{}", filePath);
|
} catch (IOException e) {
|
log.error("CSV导出失败", e);
|
}
|
}
|
|
private String getColumnNames(ResultSetMetaData metaData) throws SQLException {
|
StringBuilder sb = new StringBuilder();
|
for (int i = 1; i <= metaData.getColumnCount(); i++) {
|
sb.append(metaData.getColumnName(i)).append(",");
|
}
|
return sb.toString();
|
}
|
|
// 时间格式转换核心方法
|
private String convertTimeFormat(String originalTime) {
|
if (originalTime == null || originalTime.trim().isEmpty()) {
|
log.warn("原始时间为空");
|
return "";
|
}
|
|
// 去除原始时间中的多余空格
|
String trimmedTime = originalTime.trim().replaceAll("\\s+", " ");
|
|
// 尝试所有可能的输入格式进行解析
|
for (DateTimeFormatter formatter : INPUT_FORMATTERS) {
|
try {
|
LocalDateTime dateTime = LocalDateTime.parse(trimmedTime, formatter);
|
// 强制转换为目标格式
|
String formattedTime = dateTime.format(OUTPUT_FORMATTER);
|
log.debug("时间转换成功：{} → {}", trimmedTime, formattedTime);
|
return formattedTime;
|
} catch (DateTimeParseException e) {
|
// 尝试下一种格式
|
continue;
|
}
|
}
|
|
// 所有格式都解析失败时，记录警告并返回原始值（避免破坏数据）
|
log.warn("无法转换时间格式为yyyy-MM-dd HH:mm:ss，原始值：{}", trimmedTime);
|
return trimmedTime;
|
}
|
|
}
|
