shawn
616769e805
- 221 GPS entries restored via serial_number match from pre-MSFS backup - 5,730 GPS entries restored via MSFS account address data - Coverage improved from 1.6% (124) to 80.4% (6,075) of 7,560 assets - Remaining 1,485 have no GPS in MSFS account records
79 lines
2.4 KiB
Python
79 lines
2.4 KiB
Python
#!/usr/bin/env python3
|
|
"""Restore GPS data from pre-MSFS backup by matching serial numbers."""
|
|
|
|
import sqlite3
|
|
import sys
|
|
|
|
DB_PATH = "/home/oplabs/projects/canteen-asset-tracker/assets.db"
|
|
OLD_DB_PATH = "/home/oplabs/projects/canteen-asset-tracker/assets.db.20260528_214316.pre-msfs-import"
|
|
|
|
def main():
|
|
# Open old DB (read-only)
|
|
old = sqlite3.connect(f"file:{OLD_DB_PATH}?mode=ro", uri=True)
|
|
old.row_factory = sqlite3.Row
|
|
|
|
# Build mapping: serial_number -> (lat, lon) from old DB
|
|
old_gps = {}
|
|
for r in old.execute(
|
|
"SELECT serial_number, latitude, longitude FROM assets "
|
|
"WHERE serial_number IS NOT NULL AND serial_number != '' "
|
|
"AND latitude IS NOT NULL AND latitude != 0 AND longitude IS NOT NULL"
|
|
).fetchall():
|
|
old_gps[r["serial_number"]] = (r["latitude"], r["longitude"])
|
|
|
|
old.close()
|
|
print(f"Old DB: {len(old_gps)} GPS entries indexed by serial_number")
|
|
|
|
# Open current DB and update
|
|
cur = sqlite3.connect(DB_PATH)
|
|
updated = 0
|
|
skipped_existing = 0
|
|
skipped_no_sn = 0
|
|
skipped_no_match = 0
|
|
|
|
for r in cur.execute(
|
|
"SELECT rowid, machine_id, serial_number, latitude, longitude FROM assets"
|
|
).fetchall():
|
|
rowid, machine_id, serial_number, lat, lon = r
|
|
|
|
# Skip if already has GPS
|
|
if lat and lon and lat != 0 and lon != 0:
|
|
skipped_existing += 1
|
|
continue
|
|
|
|
if not serial_number:
|
|
skipped_no_sn += 1
|
|
continue
|
|
|
|
if serial_number in old_gps:
|
|
new_lat, new_lon = old_gps[serial_number]
|
|
cur.execute(
|
|
"UPDATE assets SET latitude = ?, longitude = ? WHERE rowid = ?",
|
|
(new_lat, new_lon, rowid)
|
|
)
|
|
updated += 1
|
|
else:
|
|
skipped_no_match += 1
|
|
|
|
cur.commit()
|
|
|
|
# Verify
|
|
gps_count = cur.execute(
|
|
"SELECT COUNT(*) FROM assets "
|
|
"WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude != 0 AND longitude != 0"
|
|
).fetchone()[0]
|
|
|
|
total = cur.execute("SELECT COUNT(*) FROM assets").fetchone()[0]
|
|
|
|
print(f"\nResults:")
|
|
print(f" GPS restored: {updated}")
|
|
print(f" Already had GPS: {skipped_existing}")
|
|
print(f" No serial number: {skipped_no_sn}")
|
|
print(f" No match in old DB: {skipped_no_match}")
|
|
print(f" Total with GPS now: {gps_count} / {total}")
|
|
|
|
cur.close()
|
|
|
|
if __name__ == "__main__":
|
|
main()
|