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
98 lines
3.1 KiB
Python
98 lines
3.1 KiB
Python
#!/usr/bin/env python3
|
|
"""Restore GPS from merged-assets.json account-level data.
|
|
|
|
Priority: canteen_connect_guid match > connect_id match > already restored via serial."""
|
|
|
|
import json
|
|
import sqlite3
|
|
import sys
|
|
|
|
DB_PATH = "/home/oplabs/projects/canteen-asset-tracker/assets.db"
|
|
MERGED_PATH = "/home/oplabs/projects/ms-field-service-extraction/web/static/data/merged-assets.json"
|
|
|
|
def build_lookups(merged_path):
|
|
"""Build GPS lookups from merged-assets.json account data."""
|
|
with open(merged_path) as f:
|
|
data = json.load(f)
|
|
|
|
by_connect_guid = {} # msfs_canteen_connect_guid → (lat, lon)
|
|
by_connect_id = {} # msfs_connect_id → (lat, lon)
|
|
|
|
for a in data['assets']:
|
|
acct = a.get('account') or {}
|
|
lat = acct.get('latitude')
|
|
lon = acct.get('longitude')
|
|
if not (lat and lon and float(lat) != 0 and float(lon) != 0):
|
|
continue
|
|
|
|
lat, lon = float(lat), float(lon)
|
|
|
|
guid = a.get('msfs_canteen_connect_guid')
|
|
if guid:
|
|
by_connect_guid[guid.upper()] = (lat, lon)
|
|
|
|
cid = a.get('msfs_connect_id')
|
|
if cid:
|
|
by_connect_id[cid] = (lat, lon)
|
|
|
|
print(f"Lookups: {len(by_connect_guid)} connect_guid, {len(by_connect_id)} connect_id")
|
|
return by_connect_guid, by_connect_id
|
|
|
|
def main():
|
|
by_connect_guid, by_connect_id = build_lookups(MERGED_PATH)
|
|
|
|
cur = sqlite3.connect(DB_PATH)
|
|
|
|
total = cur.execute("SELECT COUNT(*) FROM assets").fetchone()[0]
|
|
before = cur.execute(
|
|
"SELECT COUNT(*) FROM assets WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude != 0 AND longitude != 0"
|
|
).fetchone()[0]
|
|
print(f"Before: {before} / {total} with GPS")
|
|
|
|
via_guid = 0
|
|
via_cid = 0
|
|
skipped = 0
|
|
|
|
for r in cur.execute(
|
|
"SELECT rowid, canteen_connect_guid, connect_id, latitude, longitude FROM assets"
|
|
).fetchall():
|
|
rowid, guid, cid, lat, lon = r
|
|
|
|
# Skip if already has GPS
|
|
if lat and lon and lat != 0 and lon != 0:
|
|
skipped += 1
|
|
continue
|
|
|
|
# Try connect_guid first (most precise)
|
|
if guid:
|
|
gps = by_connect_guid.get(guid.upper())
|
|
if gps:
|
|
cur.execute("UPDATE assets SET latitude = ?, longitude = ? WHERE rowid = ?", (gps[0], gps[1], rowid))
|
|
via_guid += 1
|
|
continue
|
|
|
|
# Fallback to connect_id
|
|
if cid:
|
|
gps = by_connect_id.get(cid)
|
|
if gps:
|
|
cur.execute("UPDATE assets SET latitude = ?, longitude = ? WHERE rowid = ?", (gps[0], gps[1], rowid))
|
|
via_cid += 1
|
|
|
|
cur.commit()
|
|
|
|
after = cur.execute(
|
|
"SELECT COUNT(*) FROM assets WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude != 0 AND longitude != 0"
|
|
).fetchone()[0]
|
|
|
|
print(f"\nResults:")
|
|
print(f" Via connect_guid: {via_guid}")
|
|
print(f" Via connect_id: {via_cid}")
|
|
print(f" Already had: {skipped}")
|
|
print(f" After: {after} / {total} with GPS")
|
|
print(f" Still missing: {total - after}")
|
|
|
|
cur.close()
|
|
|
|
if __name__ == "__main__":
|
|
main()
|