103 lines
3.2 KiB
Python
Executable File
103 lines
3.2 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""Compute daily stats from the Traccar H2 DB for the FT360 dashboard export."""
|
|
import os, subprocess, json, math
|
|
from datetime import datetime
|
|
from pathlib import Path
|
|
|
|
APP2='152.53.39.202'
|
|
SSH_KEY='/root/.ssh/itpp-infra'
|
|
H2='/tmp/h2.jar'
|
|
LOCAL_DIR='/tmp/ft360_daily_stats'
|
|
DB=os.path.join(LOCAL_DIR,'database.mv.db')
|
|
|
|
os.makedirs(LOCAL_DIR,exist_ok=True)
|
|
subprocess.run(['scp','-o','StrictHostKeyChecking=no','-o','ConnectTimeout=15','-i',SSH_KEY,
|
|
f'root@{APP2}:/root/docker/traccar/traccar-data/database.mv.db',DB],check=True,timeout=40)
|
|
|
|
def query(sql):
|
|
jdbc=f'jdbc:h2:{os.path.join(LOCAL_DIR,"database")};IFEXISTS=TRUE'
|
|
p=subprocess.run(['java','-cp',H2,'org.h2.tools.Shell','-url',jdbc,'-user','sa','-password','','-sql',sql],
|
|
text=True,capture_output=True,timeout=40)
|
|
out=p.stdout if p.returncode==0 else ''
|
|
rows=[]
|
|
for line in out.splitlines():
|
|
s=line.strip()
|
|
if not s or s.startswith('(') or s.startswith('---'): continue
|
|
cells=[c.strip() for c in s.split('|')]
|
|
if cells and cells[0].upper() in {'ID','DEVICEID','LATITUDE','NAME','COUNT(*)'}: continue
|
|
if all(not c for c in cells): continue
|
|
rows.append(cells)
|
|
return rows
|
|
|
|
DEVICE_ID=1
|
|
|
|
# Today's positions ordered by devicetime
|
|
today_rows = query(f"""
|
|
SELECT devicetime, latitude, longitude, speed, attributes
|
|
FROM tc_positions
|
|
WHERE deviceid={DEVICE_ID}
|
|
AND devicetime >= CURRENT_DATE
|
|
ORDER BY devicetime ASC
|
|
""")
|
|
|
|
# Extract totalDistance deltas
|
|
first_td = None
|
|
last_td = None
|
|
max_speed = 0.0
|
|
trip_segments = 0
|
|
positions_with_speed = 0
|
|
prev_time = None
|
|
|
|
for row in today_rows:
|
|
if len(row) < 4: continue
|
|
try:
|
|
dt = datetime.strptime(row[0][:19], '%Y-%m-%d %H:%M:%S')
|
|
speed = float(row[3]) if row[3] else 0.0
|
|
km_speed = speed * 1.852 # knots to km/h
|
|
mph_speed = km_speed * 0.621371
|
|
attrs = row[4] if len(row) > 4 else ''
|
|
except (ValueError, IndexError):
|
|
continue
|
|
|
|
if mph_speed > 0.5:
|
|
positions_with_speed += 1
|
|
if mph_speed > max_speed:
|
|
max_speed = mph_speed
|
|
if prev_time and mph_speed > 1.0:
|
|
gap = (dt - prev_time).total_seconds()
|
|
if gap > 0 and gap < 3600:
|
|
trip_segments += 1
|
|
|
|
# Extract totalDistance from attributes JSON
|
|
td = None
|
|
if attrs:
|
|
try:
|
|
import re
|
|
m = re.search(r'"totalDistance":([0-9.]+)', attrs)
|
|
if m:
|
|
td = float(m.group(1))
|
|
except: pass
|
|
|
|
if td is not None:
|
|
if first_td is None: first_td = td
|
|
last_td = td
|
|
|
|
prev_time = dt
|
|
|
|
total_miles = 0.0
|
|
if first_td is not None and last_td is not None:
|
|
total_miles = (last_td - first_td) * 0.000621371 # meters to miles
|
|
total_miles = round(total_miles, 2)
|
|
|
|
result = {
|
|
"date": datetime.now().strftime('%Y-%m-%d'),
|
|
"device_id": DEVICE_ID,
|
|
"total_positions_today": len(today_rows),
|
|
"miles_traveled": total_miles,
|
|
"max_speed_mph": round(max_speed, 1),
|
|
"moving_segments": trip_segments,
|
|
"positions_with_movement": positions_with_speed,
|
|
"notes": "Miles from Traccar totalDistance. Steps and active time not available from OsmAnd protocol.",
|
|
}
|
|
print(json.dumps(result, indent=2))
|