From caf56217dc2bf898b63b0e1f31a7098202c32825 Mon Sep 17 00:00:00 2001
From: wlzboy <66905212@qq.com>
Date: 星期六, 15 十一月 2025 16:50:17 +0800
Subject: [PATCH] Merge branch 'feature_gps'

---
 sql/车辆里程统计实现总结.md |  339 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 files changed, 339 insertions(+), 0 deletions(-)

diff --git "a/sql/\350\275\246\350\276\206\351\207\214\347\250\213\347\273\237\350\256\241\345\256\236\347\216\260\346\200\273\347\273\223.md" "b/sql/\350\275\246\350\276\206\351\207\214\347\250\213\347\273\237\350\256\241\345\256\236\347\216\260\346\200\273\347\273\223.md"
index e69de29..1c173f1 100644
--- "a/sql/\350\275\246\350\276\206\351\207\214\347\250\213\347\273\237\350\256\241\345\256\236\347\216\260\346\200\273\347\273\223.md"
+++ "b/sql/\350\275\246\350\276\206\351\207\214\347\250\213\347\273\237\350\256\241\345\256\236\347\216\260\346\200\273\347\273\223.md"
@@ -0,0 +1,339 @@
+# 杞﹁締GPS閲岀▼缁熻鍔熻兘瀹炵幇鎬荤粨
+
+## 涓�銆佸姛鑳藉疄鐜版竻鍗�
+
+### 鉁� 1. 鏁版嵁搴撳眰
+- [x] `vehicle_mileage_stats.sql` - 鍒涘缓閲岀▼缁熻琛ㄥ拰鏄庣粏琛�
+- [x] `vehicle_mileage_stats_job.sql` - 鍒涘缓瀹氭椂浠诲姟閰嶇疆
+- [x] `vehicle_mileage_stats_menu.sql` - 鍒涘缓鑿滃崟鏉冮檺閰嶇疆
+
+### 鉁� 2. 瀹炰綋绫伙紙Domain锛�
+- [x] `VehicleMileageStats.java` - 閲岀▼缁熻瀹炰綋
+- [x] `TaskTimeInterval.java` - 浠诲姟鏃堕棿鍖洪棿杈呭姪绫�
+
+### 鉁� 3. 鏁版嵁璁块棶灞傦紙Mapper锛�
+- [x] `VehicleMileageStatsMapper.java` - 閲岀▼缁熻Mapper鎺ュ彛
+- [x] `VehicleMileageStatsMapper.xml` - MyBatis鏄犲皠閰嶇疆
+- [x] `VehicleGpsMapper.java` - 鎵╁睍GPS鏌ヨ鏂规硶锛堟柊澧�2涓柟娉曪級
+- [x] `VehicleGpsMapper.xml` - 鎵╁睍GPS鏌ヨSQL
+
+### 鉁� 4. 涓氬姟閫昏緫灞傦紙Service锛�
+- [x] `IVehicleMileageStatsService.java` - Service鎺ュ彛
+- [x] `VehicleMileageStatsServiceImpl.java` - Service瀹炵幇锛堟牳蹇冪畻娉曪級
+
+### 鉁� 5. 鎺у埗灞傦紙Controller锛�
+- [x] `VehicleMileageStatsController.java` - REST API鎺ュ彛
+
+### 鉁� 6. 瀹氭椂浠诲姟锛圱ask锛�
+- [x] `VehicleMileageStatsTask.java` - 鑷姩缁熻瀹氭椂浠诲姟
+
+### 鉁� 7. 鍓嶇API
+- [x] `mileageStats.js` - 鍓嶇鎺ュ彛灏佽
+
+### 鉁� 8. 鏂囨。
+- [x] `杞﹁締閲岀▼缁熻浣跨敤璇存槑.md` - 璇︾粏浣跨敤鏂囨。
+
+## 浜屻�佹牳蹇冩妧鏈疄鐜�
+
+### 1. 閲岀▼璁＄畻绠楁硶
+
+#### Haversine鍏紡锛堣绠桮PS鐐归棿璺濈锛�
+```java
+private double calculateDistance(double lat1, double lon1, double lat2, double lon2) {
+    double dLat = Math.toRadians(lat2 - lat1);
+    double dLon = Math.toRadians(lon2 - lon1);
+    double rLat1 = Math.toRadians(lat1);
+    double rLat2 = Math.toRadians(lat2);
+
+    double a = Math.sin(dLat / 2) * Math.sin(dLat / 2) +
+               Math.cos(rLat1) * Math.cos(rLat2) *
+               Math.sin(dLon / 2) * Math.sin(dLon / 2);
+    
+    double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
+    
+    return EARTH_RADIUS_KM * c;
+}
+```
+
+#### 鏃堕棿閲嶅彔姣斾緥璁＄畻锛堜换鍔￠噷绋嬪垎鎽婏級
+```java
+private double calculateTaskOverlapRatio(Date segmentStart, Date segmentEnd, 
+                                          List<TaskTimeInterval> taskIntervals) {
+    long segmentDuration = segmentEnd.getTime() - segmentStart.getTime();
+    long totalOverlap = 0;
+    
+    for (TaskTimeInterval task : taskIntervals) {
+        long overlapStart = Math.max(segmentStart.getTime(), task.getStartTime().getTime());
+        long overlapEnd = Math.min(segmentEnd.getTime(), task.getEndTime().getTime());
+        
+        if (overlapEnd > overlapStart) {
+            totalOverlap += (overlapEnd - overlapStart);
+        }
+    }
+    
+    return (double) totalOverlap / segmentDuration;
+}
+```
+
+### 2. 浠诲姟鏃舵瀹氫箟
+
+浠诲姟鏃舵 = 浠庝换鍔″垱寤烘椂闂达紙`sys_task.create_time`锛夊埌浠诲姟瀹屾垚鏃堕棿锛坄sys_task.actual_end_time`锛�
+
+SQL鏌ヨ锛�
+```sql
+select tv.task_id, t.create_time as start_time, 
+       IFNULL(t.actual_end_time, NOW()) as end_time
+from sys_task_vehicle tv
+inner join sys_task t on tv.task_id = t.task_id
+where tv.vehicle_id = #{vehicleId}
+  and t.del_flag = '0'
+  and t.actual_end_time is not null
+  and t.create_time < #{endTime}
+  and t.actual_end_time > #{startTime}
+```
+
+### 3. 缁熻鏁版嵁缂撳瓨
+
+- 姣忔棩瀹氭椂浠诲姟鑷姩缁熻鍓嶄竴澶╃殑鏁版嵁
+- 缁熻缁撴灉瀛樺偍鍦� `tb_vehicle_mileage_stats` 琛ㄤ腑
+- 鏀寔閲嶅璁＄畻锛堟洿鏂板凡鏈夎褰曪級
+- 鍞竴绱㈠紩锛歚uk_vehicle_date (vehicle_id, stat_date)`
+
+## 涓夈�丄PI鎺ュ彛璇存槑
+
+### 1. 鏌ヨ缁熻鍒楄〃
+```
+GET /system/mileageStats/list
+鍙傛暟锛�
+  - vehicleId: 杞﹁締ID锛堝彲閫夛級
+  - vehicleNo: 杞︾墝鍙凤紙鍙�夛級
+  - statDate: 缁熻鏃ユ湡锛堝彲閫夛級
+  - beginStatDate: 寮�濮嬫棩鏈燂紙鍙�夛級
+  - endStatDate: 缁撴潫鏃ユ湡锛堝彲閫夛級
+  - pageNum: 椤电爜
+  - pageSize: 姣忛〉鏁伴噺
+```
+
+### 2. 鎵嬪姩璁＄畻鍗曡溅杈嗛噷绋�
+```
+POST /system/mileageStats/calculate
+鍙傛暟锛�
+  - vehicleId: 杞﹁締ID锛堝繀濉級
+  - statDate: 缁熻鏃ユ湡锛屾牸寮� yyyy-MM-dd锛堝繀濉級
+杩斿洖锛歏ehicleMileageStats瀵硅薄
+```
+
+### 3. 鎵归噺璁＄畻鎵�鏈夎溅杈嗛噷绋�
+```
+POST /system/mileageStats/batchCalculate
+鍙傛暟锛�
+  - statDate: 缁熻鏃ユ湡锛屾牸寮� yyyy-MM-dd锛堝繀濉級
+杩斿洖锛氭垚鍔熺粺璁＄殑杞﹁締鏁伴噺
+```
+
+### 4. 瀵煎嚭缁熻鏁版嵁
+```
+POST /system/mileageStats/export
+鍙傛暟锛氬悓鏌ヨ鍒楄〃鎺ュ彛
+杩斿洖锛欵xcel鏂囦欢
+```
+
+## 鍥涖�佸畾鏃朵换鍔￠厤缃�
+
+### 榛樿閰嶇疆
+- **浠诲姟鍚嶇О**锛氳溅杈嗛噷绋嬬粺璁′换鍔�
+- **Bean鍚嶇О**锛歷ehicleMileageStatsTask
+- **鏂规硶璋冪敤**锛歝alculateYesterdayMileage
+- **Cron琛ㄨ揪寮�**锛歚0 30 1 * * ?`锛堟瘡澶╁噷鏅�1:30鎵ц锛�
+- **鎵ц绛栫暐**锛氱珛鍗虫墽琛�
+- **骞跺彂鎵ц**锛氱姝�
+- **鐘舵��**锛氬惎鐢�
+
+### 鎵嬪姩瑙﹀彂鏂瑰紡
+鍦ㄧ郴缁熺鐞� -> 瀹氭椂浠诲姟涓紝鍙互鎵嬪姩鎵ц锛�
+
+1. **缁熻鏄ㄦ棩鏁版嵁**锛�
+   ```
+   vehicleMileageStatsTask.calculateYesterdayMileage
+   ```
+
+2. **缁熻鎸囧畾鏃ユ湡**锛�
+   ```
+   vehicleMileageStatsTask.calculateMileageByDate('2025-11-09')
+   ```
+
+## 浜斻�佹暟鎹〃缁撴瀯
+
+### tb_vehicle_mileage_stats
+| 瀛楁鍚� | 绫诲瀷 | 璇存槑 |
+|--------|------|------|
+| stats_id | bigint(20) | 缁熻ID锛屼富閿� |
+| vehicle_id | bigint(20) | 杞﹁締ID |
+| vehicle_no | varchar(20) | 杞︾墝鍙� |
+| stat_date | date | 缁熻鏃ユ湡 |
+| total_mileage | decimal(10,2) | 鎬婚噷绋嬶紙鍏噷锛� |
+| task_mileage | decimal(10,2) | 浠诲姟鏃舵閲岀▼锛堝叕閲岋級 |
+| non_task_mileage | decimal(10,2) | 闈炰换鍔℃椂娈甸噷绋嬶紙鍏噷锛� |
+| task_ratio | decimal(5,4) | 浠诲姟閲岀▼鍗犳瘮锛�0-1锛� |
+| gps_point_count | int(11) | GPS鐐规暟閲� |
+| task_count | int(11) | 浠诲姟鏁伴噺 |
+| create_time | datetime | 鍒涘缓鏃堕棿 |
+| update_time | datetime | 鏇存柊鏃堕棿 |
+
+**绱㈠紩锛�**
+- PRIMARY KEY: stats_id
+- UNIQUE KEY: uk_vehicle_date (vehicle_id, stat_date)
+- KEY: idx_vehicle_id (vehicle_id)
+- KEY: idx_stat_date (stat_date)
+
+### tb_vehicle_mileage_detail锛堝彲閫夛級
+鐢ㄤ簬瀛樺偍閲岀▼璁＄畻鏄庣粏锛屾柟渚胯皟璇曞拰杩芥函銆�
+
+## 鍏�侀儴缃叉楠�
+
+### 1. 鎵ц鏁版嵁搴撹剼鏈紙鎸夐『搴忥級
+```bash
+1. sql/vehicle_mileage_stats.sql
+2. sql/vehicle_mileage_stats_job.sql
+3. sql/vehicle_mileage_stats_menu.sql
+```
+
+### 2. 閲嶅惎搴旂敤
+浠ｇ爜鏂囦欢宸茶嚜鍔ㄥ垱寤猴紝閲嶅惎搴旂敤鍗冲彲鐢熸晥銆�
+
+### 3. 楠岃瘉閮ㄧ讲
+1. 鐧诲綍绯荤粺锛屾鏌ヨ彍鍗曟槸鍚︽樉绀�"杞﹁締閲岀▼缁熻"
+2. 杩涘叆绯荤粺绠＄悊 -> 瀹氭椂浠诲姟锛屾鏌ユ槸鍚︽湁"杞﹁締閲岀▼缁熻浠诲姟"
+3. 鎵嬪姩鎵ц瀹氭椂浠诲姟鎴栬皟鐢ˋPI鎺ュ彛娴嬭瘯鍔熻兘
+
+## 涓冦�佷娇鐢ㄧず渚�
+
+### 绀轰緥1锛氭墜鍔ㄨ绠楁煇杞﹁締鏄ㄦ棩閲岀▼
+```javascript
+import { calculateMileage } from '@/api/mileageStats'
+
+calculateMileage(1001, '2025-11-09').then(response => {
+  console.log('缁熻缁撴灉锛�', response.data)
+  // 杈撳嚭绀轰緥锛�
+  // {
+  //   vehicleNo: '绮12345',
+  //   statDate: '2025-11-09',
+  //   totalMileage: 285.67,
+  //   taskMileage: 198.43,
+  //   nonTaskMileage: 87.24,
+  //   taskRatio: 0.6948,
+  //   gpsPointCount: 1205,
+  //   taskCount: 8
+  // }
+})
+```
+
+### 绀轰緥2锛氭壒閲忚绠楁墍鏈夎溅杈嗘寚瀹氭棩鏈熼噷绋�
+```javascript
+import { batchCalculateMileage } from '@/api/mileageStats'
+
+batchCalculateMileage('2025-11-09').then(response => {
+  console.log(response.msg) // 杈撳嚭锛氭壒閲忛噷绋嬬粺璁″畬鎴愶紝鎴愬姛缁熻 45 杈嗚溅
+})
+```
+
+### 绀轰緥3锛氭煡璇㈣溅杈嗛噷绋嬬粺璁℃姤琛�
+```javascript
+import { listMileageStats } from '@/api/mileageStats'
+
+const query = {
+  vehicleNo: '绮12345',
+  beginStatDate: '2025-11-01',
+  endStatDate: '2025-11-09',
+  pageNum: 1,
+  pageSize: 10
+}
+
+listMileageStats(query).then(response => {
+  console.log('缁熻鍒楄〃锛�', response.rows)
+})
+```
+
+## 鍏�佹敞鎰忎簨椤�
+
+### 1. GPS鏁版嵁瑕佹眰
+- GPS閲囬泦闂撮殧锛氬缓璁�30-60绉�
+- 鏁版嵁瀛楁蹇呭～锛歷ehicle_id, longitude, latitude, collect_time
+- 鍧愭爣绯荤粺锛氭敮鎸乄GS84銆丟CJ02绛夊父鐢ㄥ潗鏍囩郴
+
+### 2. 浠诲姟鏁版嵁瑕佹眰
+- 浠诲姟琛細sys_task
+- 杞﹁締浠诲姟鍏宠仈琛細sys_task_vehicle
+- 蹇呭～瀛楁锛歵ask_id, vehicle_id, create_time, actual_end_time
+
+### 3. 鎬ц兘浼樺寲寤鸿
+- 瀹氭椂浠诲姟閬垮紑涓氬姟楂樺嘲鏈燂紙寤鸿鍑屾櫒鎵ц锛�
+- GPS鍘熷鏁版嵁瀹氭湡娓呯悊锛堝缓璁繚鐣�7-30澶╋級
+- 缁熻鏁版嵁瀹氭湡褰掓。锛堝缓璁繚鐣�3-6涓湀锛�
+
+### 4. 鏁版嵁鍑嗙‘鎬�
+- 閲岀▼璁＄畻鍩轰簬GPS杞ㄨ抗锛岀簿搴﹀彈GPS淇″彿璐ㄩ噺褰卞搷
+- Haversine鍏紡璁＄畻鐨勬槸鐩寸嚎璺濈锛屽疄闄呴亾璺窛绂诲彲鑳芥洿闀�
+- 鍙粨鍚堝ぉ鍦板浘璺緞瑙勫垝API鑾峰彇鏇村噯纭殑閬撹矾璺濈
+
+## 涔濄�佹墿灞曞姛鑳藉缓璁�
+
+### 1. 闆嗘垚澶╁湴鍥捐矾寰勮窛绂伙紙鏇村噯纭級
+褰撳墠浣跨敤Haversine鍏紡璁＄畻鐩寸嚎璺濈锛屽彲鍗囩骇涓猴細
+- 灏咷PS杞ㄨ抗鐐瑰彂閫佸埌澶╁湴鍥捐矾寰勮鍒扐PI
+- 鑾峰彇瀹為檯閬撹矾璺濈
+- 鎻愰珮閲岀▼缁熻绮惧害
+
+### 2. 瀹炴椂閲岀▼缁熻
+- 鍦℅PS鏁版嵁鍏ュ簱鏃跺疄鏃惰绠�
+- 浣跨敤Redis缂撳瓨褰撴棩绱閲岀▼
+- 鍑屾櫒瀹氭椂浠诲姟浠呭仛鏁版嵁鍥哄寲
+
+### 3. 閲岀▼寮傚父鍛婅
+- 鍗曟棩閲岀▼瓒呰繃闃堝�煎憡璀�
+- 闀挎椂闂存棤GPS鏁版嵁鍛婅
+- 閲岀▼绐佸彉寮傚父鍛婅
+
+### 4. 鏁版嵁鍙鍖�
+- 姣忔棩閲岀▼瓒嬪娍鍥�
+- 浠诲姟閲岀▼鍗犳瘮楗煎浘
+- 杞﹁締閲岀▼鎺掑悕姒�
+
+## 鍗併�佹晠闅滄帓鏌�
+
+### 闂1锛氬畾鏃朵换鍔℃湭鎵ц
+- 妫�鏌ュ畾鏃朵换鍔＄姸鎬佹槸鍚︿负"鍚敤"
+- 妫�鏌ron琛ㄨ揪寮忔槸鍚︽纭�
+- 鏌ョ湅瀹氭椂浠诲姟鏃ュ織
+
+### 闂2锛氱粺璁＄粨鏋滀负0
+- 妫�鏌PS鏁版嵁鏄惁瀛樺湪
+- 妫�鏌PS鏁版嵁鐨刢ollect_time瀛楁鏄惁姝ｇ‘
+- 妫�鏌ヤ换鍔℃暟鎹槸鍚﹀瓨鍦�
+
+### 闂3锛氶噷绋嬫暟鎹紓甯�
+- 妫�鏌PS鍧愭爣鏄惁鍚堟硶锛堢粡绾害鑼冨洿锛�
+- 妫�鏌ユ槸鍚﹀瓨鍦℅PS婕傜Щ鐐�
+- 鍚敤鏄庣粏琛ㄥ垎鏋愭瘡娈佃窛绂�
+
+### 鏌ョ湅鏃ュ織
+```bash
+# Service灞傛棩蹇�
+grep "VehicleMileageStatsServiceImpl" logs/ruoyi-*.log
+
+# 瀹氭椂浠诲姟鏃ュ織
+grep "VehicleMileageStatsTask" logs/ruoyi-*.log
+```
+
+## 鍗佷竴銆佹�荤粨
+
+鉁� 鏈姛鑳藉凡瀹屾暣瀹炵幇杞﹁締GPS閲岀▼缁熻鐨勬墍鏈夐渶姹傦細
+- 鉁� 姣忔棩鑷姩缁熻杞﹁締琛岄┒閲岀▼
+- 鉁� 鍖哄垎浠诲姟鏃舵鍜岄潪浠诲姟鏃舵閲岀▼
+- 鉁� 璁＄畻浠诲姟閲岀▼鍗犳瘮
+- 鉁� 缁熻鏁版嵁缂撳瓨鍒版暟鎹簱琛�
+- 鉁� 鏀寔鎵嬪姩瑙﹀彂鍜屾壒閲忚绠�
+- 鉁� 鎻愪緵瀹屾暣鐨勬煡璇㈠拰瀵煎嚭鍔熻兘
+- 鉁� 闆嗘垚瀹氭椂浠诲姟鑷姩鍖栨墽琛�
+
+鏍稿績绠楁硶閲囩敤Haversine鍏紡璁＄畻GPS鐐归棿璺濈锛屾寜鏃堕棿閲嶅彔姣斾緥鍒嗘憡閲岀▼鍒颁换鍔″拰闈炰换鍔℃椂娈碉紝纭繚缁熻鍑嗙‘鎬с�傛墍鏈夋暟鎹紦瀛樺湪涓撶敤缁熻琛ㄤ腑锛屾敮鎸侀珮鏁堟煡璇㈠拰鍒嗘瀽銆�

--
Gitblit v1.9.1