DataProcess.java 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191
  1. package com.jjt;
  2. import cn.hutool.json.JSONArray;
  3. import cn.hutool.json.JSONObject;
  4. import com.ruoyi.RuoYiApplication;
  5. import com.ruoyi.biz.service.IIotService;
  6. import com.ruoyi.common.utils.DateUtils;
  7. import org.apache.poi.ss.usermodel.Cell;
  8. import org.apache.poi.ss.usermodel.Row;
  9. import org.apache.poi.ss.usermodel.Sheet;
  10. import org.apache.poi.ss.usermodel.Workbook;
  11. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  12. import org.junit.jupiter.api.Test;
  13. import org.springframework.boot.test.context.SpringBootTest;
  14. import org.springframework.jdbc.core.JdbcTemplate;
  15. import javax.annotation.Resource;
  16. import java.io.FileOutputStream;
  17. import java.io.IOException;
  18. import java.time.*;
  19. import java.util.ArrayList;
  20. import java.util.Date;
  21. import java.util.List;
  22. import java.util.concurrent.atomic.AtomicInteger;
  23. /**
  24. * DataProcess$
  25. *
  26. * @author wukai
  27. * @date 2024/5/7 11:49
  28. */
  29. @SpringBootTest(classes = RuoYiApplication.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
  30. public class DataProcess {
  31. @Resource
  32. private JdbcTemplate jdbcTemplate;
  33. @Resource
  34. private IIotService iotService;
  35. @Test
  36. void excel() {
  37. String code = "106";
  38. String table = "root.tl.suxi.knittings" + code + "_plc1";
  39. Workbook workbook = new XSSFWorkbook();
  40. for (int i = 21; i <= 26; i++) {
  41. String date = "2024-05-" + i;
  42. System.err.println(date);
  43. List<String[]> list = new ArrayList<>();
  44. for (int j = 1; j <= 12; j++) {
  45. List<String[]> pList = get(table, date, j);
  46. list.addAll(pList);
  47. }
  48. Sheet sheet = workbook.createSheet(date);
  49. toExcel(list, sheet);
  50. }
  51. try (FileOutputStream outputStream = new FileOutputStream("D:\\SYSTEM\\Desktop\\temp\\excel\\小经编机" + code + "号.xlsx")) {
  52. workbook.write(outputStream);
  53. } catch (IOException e) {
  54. e.printStackTrace();
  55. } finally {
  56. try {
  57. workbook.close();
  58. } catch (IOException e) {
  59. e.printStackTrace();
  60. }
  61. }
  62. }
  63. public List<String[]> get(String table, String date, int period) {
  64. List<String[]> list = new ArrayList<>();
  65. LocalDate localDate = LocalDate.parse(date);
  66. LocalDateTime ldt = LocalDateTime.of(localDate, LocalTime.MIN);
  67. LocalDateTime start = ldt.plusHours(2 * period).minusHours(2);
  68. LocalDateTime end = start.plusHours(2);
  69. start = start.minusSeconds(1);
  70. Long startTime = start.toInstant(ZoneOffset.of("+8")).toEpochMilli();
  71. Long endTime = end.toInstant(ZoneOffset.of("+8")).toEpochMilli();
  72. String sql = "select Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19" +
  73. " from %s where time>%s and time <=%s";
  74. sql = String.format(sql, table, startTime, endTime);
  75. iotService.query(sql);
  76. JSONObject jsonObject = iotService.query(sql);
  77. JSONObject data = jsonObject.getJSONObject("data");
  78. JSONArray values = data.getJSONArray("values");
  79. JSONArray timestamps = data.getJSONArray("timestamps");
  80. for (int i = 0; i < values.size(); i++) {
  81. String[] temp = new String[16];
  82. temp[0] = timestamps.getStr(i);
  83. JSONArray da = values.getJSONArray(i);
  84. for (int j = 1; j < temp.length; j++) {
  85. temp[j] = da.getStr(j - 1);
  86. }
  87. list.add(temp);
  88. }
  89. return list;
  90. }
  91. public void toExcel(List<String[]> list, Sheet sheet) {
  92. String[] names = {"时间", "GB1剩余时间", "GB2剩余时间", "GB3剩余时间", "GB4剩余时间", "GB5剩余时间", "GB1盘头剩余周长", "GB2盘头剩余周长", "GB3盘头剩余周长", "GB4盘头剩余周长", "GB5盘头剩余周长", "GB1盘头剩余圈数", "GB2盘头剩余圈数", "GB3盘头剩余圈数", "GB4盘头剩余圈数", "GB5盘头剩余圈数"};
  93. Row title = sheet.createRow(0);
  94. for (int i = 0; i < names.length; i++) {
  95. Cell cell = title.createCell(i);
  96. cell.setCellValue(names[i]);
  97. }
  98. AtomicInteger index = new AtomicInteger(1);
  99. list.forEach(d -> {
  100. Row row = sheet.createRow(index.get());
  101. Long time = Long.parseLong(d[0]);
  102. String date = DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, new Date(time));
  103. Cell cell = row.createCell(0);
  104. cell.setCellValue(date);
  105. for (int j = 1; j <= 10; j++) {
  106. cell = row.createCell(j);
  107. cell.setCellValue(Float.parseFloat(d[j]));
  108. }
  109. for (int j = 11; j < d.length; j++) {
  110. cell = row.createCell(j);
  111. cell.setCellValue(Integer.parseInt(d[j]));
  112. }
  113. index.getAndIncrement();
  114. });
  115. }
  116. @Test
  117. void data() {
  118. String table = "root.tl.suxi.knittings98_plc1";
  119. String[] arr = {
  120. "time>2024-05-03T00:00:00 and time <=2024-05-03T02:00:00",
  121. "time>2024-05-03T02:00:00 and time <=2024-05-03T04:00:00",
  122. "time>2024-05-03T04:00:00 and time <=2024-05-03T06:00:00",
  123. "time>2024-05-03T06:00:00 and time <=2024-05-03T08:00:00",
  124. "time>2024-05-03T08:00:00 and time <=2024-05-03T10:00:00",
  125. "time>2024-05-03T10:00:00 and time <=2024-05-03T12:00:00",
  126. "time>2024-05-03T12:00:00 and time <=2024-05-03T14:00:00",
  127. "time>2024-05-03T14:00:00 and time <=2024-05-03T16:00:00",
  128. "time>2024-05-03T16:00:00 and time <=2024-05-03T18:00:00",
  129. "time>2024-05-03T18:00:00 and time <=2024-05-03T20:00:00",
  130. "time>2024-05-03T20:00:00 and time <=2024-05-03T22:00:00",
  131. "time>2024-05-03T22:00:00 and time <=2024-05-04T00:00:00",
  132. "time>2024-05-04T00:00:00 and time <=2024-05-04T02:00:00",
  133. "time>2024-05-04T02:00:00 and time <=2024-05-04T04:00:00",
  134. "time>2024-05-04T04:00:00 and time <=2024-05-04T06:00:00",
  135. "time>2024-05-04T06:00:00 and time <=2024-05-04T08:00:00",
  136. "time>2024-05-04T08:00:00 and time <=2024-05-04T10:00:00",
  137. "time>2024-05-04T10:00:00 and time <=2024-05-04T12:00:00",
  138. "time>2024-05-04T12:00:00 and time <=2024-05-04T14:00:00",
  139. "time>2024-05-04T14:00:00 and time <=2024-05-04T16:00:00",
  140. "time>2024-05-04T16:00:00 and time <=2024-05-04T18:00:00",
  141. "time>2024-05-04T18:00:00 and time <=2024-05-04T20:00:00",
  142. "time>2024-05-04T20:00:00 and time <=2024-05-04T22:00:00",
  143. "time>2024-05-04T22:00:00 and time <=2024-05-05T00:00:00"
  144. };
  145. // for (String where : arr) {
  146. // process(table, where);
  147. // }
  148. String where = "time>=2024-05-05T12:00:00 and time <=2024-05-05T14:00:00";
  149. process(table, where);
  150. }
  151. @Test
  152. void process(String table, String where) {
  153. String sql = "select Alarm_unit_1,Alarm_unit_2,Alarm_unit_3,Alarm_unit_4,Alarm_unit_5,Alarm_unit_6,Alarm_unit_7,Alarm_unit_8,Alarm_unit_9,Alarm_unit_10,Alarm_unit_11,Alarm_unit_12,Alarm_unit_13,Alarm_unit_14,Alarm_unit_15,Alarm_unit_16,Alarm_unit_17,Alarm_unit_18,Alarm_unit_19,Alarm_unit_20,Alarm_unit_21,Alarm_unit_22,Alarm_unit_23,Alarm_unit_24,Alarm_unit_25,Alarm_unit_26,Alarm_unit_27,Capacity_data_1,Capacity_data_2,Capacity_data_3,Capacity_data_4,Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19,Capacity_data_20,Capacity_data_21,Capacity_data_22,Capacity_data_23,Capacity_data_24,Capacity_data_25,Capacity_data_26,Capacity_data_27,Capacity_data_28,Capacity_data_29,Capacity_data_30,Capacity_data_31,Capacity_data_32,Capacity_data_33,Capacity_data_34,Capacity_data_35,Capacity_data_36,Capacity_data_37,Capacity_data_38,Capacity_data_39,Capacity_data_40,Capacity_data_41,Capacity_data_42,Capacity_data_43,Capacity_data_44,Capacity_data_45,Capacity_data_46,Capacity_data_47,Capacity_data_48,Capacity_data_49,Capacity_data_50,Capacity_data_51,Capacity_data_52,Capacity_data_53,System_data_1,System_data_2,System_data_3,System_data_4,System_data_5,System_data_6,System_data_7,System_data_8,System_data_9,System_data_10,System_data_11,System_data_12,System_data_13,System_data_14,System_data_15,System_data_16,System_data_17,System_data_18,System_data_19,System_data_20,System_data_21,System_data_22,System_data_23,System_data_24,System_data_25,System_data_26,System_data_27,System_data_28,System_data_29,System_data_30,System_data_31,System_data_32,System_data_33,System_data_34,System_data_35,System_data_36,System_data_37,System_data_38,System_data_39,System_data_40,Formula_data_1,Formula_data_2,Formula_data_3,Formula_data_4,Formula_data_5,Formula_data_6,Formula_data_7,Formula_data_8,Formula_data_9,Formula_data_10,Formula_data_11,Formula_data_12,Formula_data_13,Formula_data_14,Formula_data_15,Formula_data_16,Formula_data_17,Formula_data_18,Formula_data_19,Formula_data_20,Formula_data_21,Formula_data_22,Formula_data_23,Formula_data_24,Formula_data_25,Formula_data_26,Formula_data_27,Formula_data_28,Formula_data_29,Formula_data_30,Formula_data_31,Formula_data_32,Formula_data_33,Formula_data_34,Formula_data_35" +
  154. " from " + table +
  155. " where " + where;
  156. // System.err.println(res);
  157. JSONObject jsonObject = iotService.query(sql);
  158. JSONObject data = jsonObject.getJSONObject("data");
  159. JSONArray array = data.getJSONArray("values");
  160. JSONArray columnNames = data.getJSONArray("columnNames");
  161. JSONArray timestamps = data.getJSONArray("timestamps");
  162. List list = new ArrayList<>();
  163. for (int i = 0; i < timestamps.size(); i++) {
  164. Object[] arr = new Object[columnNames.size() + 1];
  165. arr[0] = LocalDateTime.ofInstant(Instant.ofEpochMilli(timestamps.getLong(i)), ZoneId.systemDefault());
  166. JSONArray da = array.getJSONArray(i);
  167. for (int j = 0; j < da.size(); j++) {
  168. arr[j + 1] = da.get(j);
  169. }
  170. list.add(arr);
  171. }
  172. sql = "INSERT INTO TWIN_DATA(DATA_TIME,Alarm_unit_1,Alarm_unit_2,Alarm_unit_3,Alarm_unit_4,Alarm_unit_5,Alarm_unit_6,Alarm_unit_7,Alarm_unit_8,Alarm_unit_9,Alarm_unit_10,Alarm_unit_11,Alarm_unit_12,Alarm_unit_13,Alarm_unit_14,Alarm_unit_15,Alarm_unit_16,Alarm_unit_17,Alarm_unit_18,Alarm_unit_19,Alarm_unit_20,Alarm_unit_21,Alarm_unit_22,Alarm_unit_23,Alarm_unit_24,Alarm_unit_25,Alarm_unit_26,Alarm_unit_27,Capacity_data_1,Capacity_data_2,Capacity_data_3,Capacity_data_4,Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19,Capacity_data_20,Capacity_data_21,Capacity_data_22,Capacity_data_23,Capacity_data_24,Capacity_data_25,Capacity_data_26,Capacity_data_27,Capacity_data_28,Capacity_data_29,Capacity_data_30,Capacity_data_31,Capacity_data_32,Capacity_data_33,Capacity_data_34,Capacity_data_35,Capacity_data_36,Capacity_data_37,Capacity_data_38,Capacity_data_39,Capacity_data_40,Capacity_data_41,Capacity_data_42,Capacity_data_43,Capacity_data_44,Capacity_data_45,Capacity_data_46,Capacity_data_47,Capacity_data_48,Capacity_data_49,Capacity_data_50,Capacity_data_51,Capacity_data_52,Capacity_data_53,System_data_1,System_data_2,System_data_3,System_data_4,System_data_5,System_data_6,System_data_7,System_data_8,System_data_9,System_data_10,System_data_11,System_data_12,System_data_13,System_data_14,System_data_15,System_data_16,System_data_17,System_data_18,System_data_19,System_data_20,System_data_21,System_data_22,System_data_23,System_data_24,System_data_25,System_data_26,System_data_27,System_data_28,System_data_29,System_data_30,System_data_31,System_data_32,System_data_33,System_data_34,System_data_35,System_data_36,System_data_37,System_data_38,System_data_39,System_data_40,Formula_data_1,Formula_data_2,Formula_data_3,Formula_data_4,Formula_data_5,Formula_data_6,Formula_data_7,Formula_data_8,Formula_data_9,Formula_data_10,Formula_data_11,Formula_data_12,Formula_data_13,Formula_data_14,Formula_data_15,Formula_data_16,Formula_data_17,Formula_data_18,Formula_data_19,Formula_data_20,Formula_data_21,Formula_data_22,Formula_data_23,Formula_data_24,Formula_data_25,Formula_data_26,Formula_data_27,Formula_data_28,Formula_data_29,Formula_data_30,Formula_data_31,Formula_data_32,Formula_data_33,Formula_data_34,Formula_data_35)" +
  173. " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  174. jdbcTemplate.batchUpdate(sql, list);
  175. }
  176. }