From 397cbc65ff2cdbb85e2e48b7ab54d303e51ca3bf Mon Sep 17 00:00:00 2001
From: cuikaidong <ckd2942379034@163.com>
Date: 星期一, 15 九月 2025 16:06:30 +0800
Subject: [PATCH] 定时生成csv文件地址修改
---
lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/quartz/job/ParameterCsvJob.java | 471 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 471 insertions(+), 0 deletions(-)
diff --git a/lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/quartz/job/ParameterCsvJob.java b/lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/quartz/job/ParameterCsvJob.java
new file mode 100644
index 0000000..c015597
--- /dev/null
+++ b/lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/quartz/job/ParameterCsvJob.java
@@ -0,0 +1,471 @@
+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 = "/parameters/";
+ private static final Map<String, String> FIELD_MAPPING = new HashMap<>();
+
+ static {
+ // 鍒濆鍖栧瓧娈垫槧灏勶細key鏄暟鎹簱瀛楁鍚嶏紝value鏄疌SV/瀹炰綋绫诲瓧娈靛悕
+ 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 // 甯鐨勬牸寮忥紙濡�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("鏁版嵁搴撴槸鍚﹀寘鍚獵ollectTime锛歿}锛屾槸鍚﹀寘鍚獷quipmentID锛歿}", 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鈫択_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鈫択_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("鏃犳硶杞崲鏃堕棿鏍煎紡涓簓yyy-MM-dd HH:mm:ss锛屽師濮嬪�硷細{}", trimmedTime);
+ return trimmedTime;
+ }
+
+}
--
Gitblit v1.9.3