119 lines
2.2 KiB
Python
119 lines
2.2 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
|
|
from datetime import datetime, timedelta
|
|
import sys
|
|
import sqlite3
|
|
|
|
|
|
db_file = "/www/wwwroot/www.manuelw.de/ebike/ebike.db"
|
|
|
|
|
|
try:
|
|
loading = sys.argv[1]
|
|
watt = sys.argv[2]
|
|
except:
|
|
print("Fehlende Command Line Inputs")
|
|
exit(0)
|
|
|
|
|
|
##
|
|
### auf neue Files überwachen
|
|
def main():
|
|
try:
|
|
db = sqlite3.connect(db_file)
|
|
cursor = db.cursor()
|
|
print("Connected to SQLite")
|
|
|
|
updateLast(db, cursor)
|
|
insertNew(db, cursor)
|
|
|
|
except sqlite3.Error as error:
|
|
print("Failed to insert data into sqlite table", error)
|
|
finally:
|
|
if db:
|
|
db.close()
|
|
print("The SQLite connection is closed")
|
|
|
|
|
|
def updateLast(db, cursor):
|
|
global watt
|
|
|
|
## hole letztes Ladungs-Datum
|
|
sql = '''
|
|
SELECT id, l_date FROM bike_loading ORDER BY l_date DESC LIMIT 1;
|
|
'''
|
|
cursor.execute(sql)
|
|
row = cursor.fetchone()
|
|
|
|
if not row:
|
|
print("DB leer, lege neu an.")
|
|
|
|
else:
|
|
ins_id = row[0]
|
|
|
|
## hole alle Fahrten nach letzter Ladung
|
|
sql = '''
|
|
SELECT d_total_distance, d_total_ascent, d_avg_speed FROM bike_drives WHERE d_date >= ?;
|
|
'''
|
|
cursor.execute(sql, (row[1],))
|
|
rows = cursor.fetchall()
|
|
|
|
total_range = 0
|
|
total_ascent = 0
|
|
avg_speed = 0
|
|
fahrten = 0
|
|
|
|
print("Anzahl:", len(rows))
|
|
if len(rows) < 1:
|
|
print("Keine neuen Daten vorhanden, beende jetzt.")
|
|
if db:
|
|
db.close()
|
|
print("The SQLite connection is closed")
|
|
exit(0)
|
|
|
|
for row in rows:
|
|
fahrten += 1
|
|
total_range += row[0]
|
|
total_ascent += row[1]
|
|
avg_speed += row[2]
|
|
|
|
avg_speed = round(avg_speed / fahrten, 1)
|
|
|
|
data = (total_range, total_ascent, avg_speed, watt, fahrten, ins_id)
|
|
sql = '''
|
|
UPDATE bike_loading SET
|
|
l_total_range = ?,
|
|
l_total_ascent = ?,
|
|
l_avg_speed = ?,
|
|
l_loaded_wh = ?,
|
|
l_fahrten = ?
|
|
WHERE id = ?
|
|
'''
|
|
cursor.execute(sql, data)
|
|
db.commit()
|
|
print("Data updated successfully into bike_loading table")
|
|
|
|
|
|
def insertNew(db, cursor):
|
|
global loading
|
|
|
|
date = datetime.today().strftime('%Y-%m-%d %H:%M:%S')
|
|
data = (date, loading)
|
|
sql = '''
|
|
INSERT INTO bike_loading
|
|
(l_date, l_loading)
|
|
VALUES
|
|
(?, ?)
|
|
'''
|
|
|
|
cursor.execute(sql, data)
|
|
db.commit()
|
|
print("Data inserted successfully into bike_drives table")
|
|
cursor.close()
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|
|
|
|
|