From 428851fa44382ddd420d88dc79ee944162e5f93a Mon Sep 17 00:00:00 2001
From: xingjinshuang <xingjs@qq.com>
Date: 星期四, 26 十二月 2024 17:18:52 +0800
Subject: [PATCH] @xingjs@20241226@添加查询管线工具类,修改解决查询站点接口token存在无法获取的情况，解决后可正常获取token，并查询出需要的站点；解析shp文件获取站点范围;新增把降水CSV文件内容解析保存到数据库中;计算CSV文件中的降水时间和降水总量

---
 src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java    |   39 +++++++++++++++++++++++++++++++++++++++
 src/main/java/com/se/simu/controller/SimuController.java |   11 ++++++-----
 2 files changed, 45 insertions(+), 5 deletions(-)

diff --git a/src/main/java/com/se/simu/controller/SimuController.java b/src/main/java/com/se/simu/controller/SimuController.java
index 3e9aee1..50ff53a 100644
--- a/src/main/java/com/se/simu/controller/SimuController.java
+++ b/src/main/java/com/se/simu/controller/SimuController.java
@@ -265,19 +265,20 @@
             // 1 璇诲彇CSV 鏂囦欢
             CsvToSQLiteUtils.readCsvSaveLocal(stationRainFile, tableName);
             // 鑾峰彇浠跨湡鏃堕棿 duration
-
+            Integer duration = CsvToSQLiteUtils.getDuration(tableName);
+            log.info("浠跨湡鏃堕棿 duration = {}", duration);
+            vo.setDuration(duration);
 
             // 鑾峰彇闄嶉洦鎬婚噺 total
-
+            Double total = CsvToSQLiteUtils.getTotal(tableName);
+            log.info("闄嶉洦鎬婚噺 total = {}", total);
+            vo.setTotal(total);
 
         } catch (Exception e) {
             log.error("瑙ｆ瀽绔欑偣闆ㄩ噺CSV鏂囦欢澶辫触");
         }
         // TODO: 2024/12/24 鏍规嵁杩欎簺鏂囦欢鐨勫湴鍧�锛岃幏鍙栨枃浠跺唴瀹癸紝鍒涘缓浠跨湡瑙嗗浘
         try {
-            if (null == vo.getTotal() || vo.getTotal() < 1 || vo.getTotal() > 1000) {
-                return fail("闄嶉洦閲忎笉鑳戒负绌猴紝涓斿彇鍊煎湪1~1000涔嬮棿");
-            }
             if (null == vo.getDuration() || vo.getDuration() < 1 || vo.getDuration() > 10080) {
                 return fail("浠跨湡鏃堕暱涓嶈兘涓虹┖锛屼笖鍙栧�煎湪1~10080涔嬮棿");
             }
diff --git a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
index 1d35646..7dcf5fa 100644
--- a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
+++ b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
@@ -230,4 +230,43 @@
         }
     }
 
+    public static Double getTotal(String tableName) {
+        // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+            if (conn != null) {
+                // 2. 鎵цSQL鏌ヨ
+                String queryDataSql = "SELECT sum(rainfall_difference) AS total" +
+                        "FROM " +
+                        "    (SELECT ABS( " +
+                        "        (SELECT rainfall" +
+                        "        FROM " + tableName +
+                        "        WHERE station_name = s.station_name" +
+                        "        ORDER BY  datetime ASC LIMIT 1) - " +
+                        "            (SELECT rainfall" +
+                        "            FROM " + tableName +
+                        "            WHERE station_name = s.station_name" +
+                        "            ORDER BY  datetime DESC LIMIT 1) ) AS rainfall_difference" +
+                        "            FROM " + tableName + "s" +
+                        "            GROUP BY  station_name)";
+                // 3. 澶勭悊鏌ヨ缁撴灉
+                try (PreparedStatement pstmt = conn.prepareStatement(queryDataSql)) {
+                    ResultSet rs = pstmt.executeQuery();
+                    while (rs.next()) {
+                        // 鑾峰彇鎬诲拰
+                        return rs.getDouble("total");
+                    }
+                } catch (SQLException e) {
+                    System.err.println("鏌ヨ鏁版嵁鏃跺嚭閿�: " + e.getMessage());
+                }
+            }
+        } catch (SQLException e) {
+            System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+        }
+
+        return null;
+    }
+
+    public static Integer getDuration(String tableName) {
+        return 1440;
+    }
 }

--
Gitblit v1.9.3