shawn
bfcf876d42
Features: - Dark-themed interactive Leaflet map with marker clustering - Combined GPS sources: booking (technician check-in) + photo EXIF + account locations - Collapsible filter bar with search, source toggles, and source type dropdown - Info popups on click showing machine details, work orders, serials, photos - Account location layer toggle (3,487 accounts) - Export filtered data as JSON - Mobile-responsive (40vh map on mobile, filter collapse) - FastAPI backend with in-memory caching - Systemd service for auto-start - NPMPlus reverse proxy at gps.ourpad.casa Stats: 94 unique machines (73 booking, 21 photo), 231 total GPS points, 3,487 account locations
348 lines
12 KiB
Python
348 lines
12 KiB
Python
#!/usr/bin/env python3
|
|
"""Combine all GPS sources into a single enriched JSON for the map frontend.
|
|
|
|
Sources:
|
|
1. Booking GPS (technician check-in) — bookableresourcebooking.msdyn_latitude/longitude
|
|
2. Photo EXIF GPS (OCR annotation photos) — ocr_results.json
|
|
3. exif-test-dev iPhone uploads — photos.db
|
|
4. Account address GPS — account.address1_latitude/longitude (for context)
|
|
|
|
Output: static/data/gps_points.json — one record per GPS point with source info
|
|
"""
|
|
import sqlite3
|
|
import json
|
|
import math
|
|
from collections import defaultdict
|
|
from datetime import datetime
|
|
|
|
DATA_DIR = "/home/oplabs/projects/canteen-gps-map/static/data"
|
|
BACKUP_DB = "/home/oplabs/projects/ms-field-service-extraction/data-samples/msfs_backup/fieldservicecanteen.crm.dynamics.com_9fc6c50c-b097-f011-b4cb-7ced8d1b15a2_data.db"
|
|
ASSET_DB = "/home/oplabs/projects/canteen-asset-tracker/assets.db"
|
|
EXIF_DEV_DB = "/home/oplabs/projects/exif-test-dev/photos.db"
|
|
MSFS_DATA = "/home/oplabs/projects/ms-field-service-extraction/web/static/data"
|
|
|
|
import os
|
|
os.makedirs(DATA_DIR, exist_ok=True)
|
|
|
|
db = sqlite3.connect(BACKUP_DB)
|
|
|
|
# ── Build lookup maps ──
|
|
|
|
# WO → customer asset
|
|
wo_to_asset = {}
|
|
for w in db.execute(
|
|
'SELECT "msdyn_workorderid", "msdyn_customerasset!id" FROM "msdyn_workorder" WHERE "msdyn_customerasset!id" IS NOT NULL'
|
|
).fetchall():
|
|
wo_to_asset[w[0]] = w[1]
|
|
|
|
# customer asset → info (guid, name, serial, account)
|
|
asset_info = {}
|
|
for c in db.execute(
|
|
'SELECT "msdyn_customerassetid", "hsl_canteenconnectguid", "msdyn_name", '
|
|
'"hsl_serialnumber", "hsl_compassassetnumber", "_msdyn_account_value", '
|
|
'"hsl_connectid", "hsl_equipmentid", "hsl_machinenumber" '
|
|
'FROM "msdyn_customerasset"'
|
|
).fetchall():
|
|
asset_info[c[0]] = {
|
|
"guid": (c[1] or "").upper() if c[1] else "",
|
|
"name": c[2] or "",
|
|
"serial": c[3] or "",
|
|
"compass": c[4] or "",
|
|
"account_id": c[5] or "",
|
|
"connect_id": c[6] or "",
|
|
"equipment_id": c[7] or "",
|
|
"machine_number": c[8] or "",
|
|
}
|
|
|
|
# Account info (name, address)
|
|
account_info = {}
|
|
for a in db.execute(
|
|
'SELECT "accountid", "name", "address1_line1", "address1_city", "address1_stateorprovince", '
|
|
'"address1_postalcode", "address1_country" FROM "account"'
|
|
).fetchall():
|
|
account_info[a[0]] = {
|
|
"name": a[1] or "",
|
|
"line1": a[2] or "",
|
|
"city": a[3] or "",
|
|
"state": a[4] or "",
|
|
"zip": a[5] or "",
|
|
"country": a[6] or "",
|
|
}
|
|
|
|
# Load merged assets for GUID → machine_id mapping
|
|
guid_to_mid = {}
|
|
try:
|
|
with open(f"{MSFS_DATA}/merged-assets.json") as f:
|
|
merged = json.load(f)
|
|
for a in merged.get("assets", []):
|
|
cmid = a.get("canteen_machine_id")
|
|
guid = a.get("msfs_canteen_connect_guid", "")
|
|
if cmid and guid:
|
|
guid_to_mid[guid.upper()] = cmid
|
|
except FileNotFoundError:
|
|
print("WARNING: merged-assets.json not found, GUID mapping will be limited")
|
|
|
|
# Canteen asset tracker for existing machine info
|
|
canteen_db = sqlite3.connect(ASSET_DB)
|
|
canteen_assets = {}
|
|
for r in canteen_db.execute(
|
|
"SELECT machine_id, name, company, make, model, latitude, longitude FROM assets"
|
|
).fetchall():
|
|
canteen_assets[r[0]] = {
|
|
"name": r[1] or "",
|
|
"company": r[2] or "",
|
|
"make": r[3] or "",
|
|
"model": r[4] or "",
|
|
"lat": r[5],
|
|
"lng": r[6],
|
|
}
|
|
|
|
# ── SOURCE 1: Booking GPS ──
|
|
booking_points = []
|
|
booking_query = db.execute("""
|
|
SELECT b."name", b."msdyn_latitude", b."msdyn_longitude",
|
|
b."msdyn_workorder!id", b."starttime", b."endtime",
|
|
b."hsl_serviceaccountidname", b."msdyn_actualarrivaltime",
|
|
wo."msdyn_name" as wo_name, wo."msdyn_customerasset!id"
|
|
FROM "bookableresourcebooking" b
|
|
LEFT JOIN "msdyn_workorder" wo ON b."msdyn_workorder!id" = wo."msdyn_workorderid"
|
|
WHERE b."msdyn_latitude" IS NOT NULL AND b."msdyn_longitude" IS NOT NULL
|
|
""")
|
|
|
|
for r in booking_query:
|
|
name, lat, lng, wo_id, start, end, account, arrival = r[0], float(r[1]), float(r[2]), r[3], r[4], r[5], r[6], r[7]
|
|
wo_name = r[8] or ""
|
|
asset_id = r[9] or ""
|
|
|
|
ai = asset_info.get(asset_id, {})
|
|
guid = ai.get("guid", "")
|
|
mid = guid_to_mid.get(guid, "")
|
|
|
|
# Try to extract machine ID from name if not found via GUID
|
|
if not mid and ai.get("name"):
|
|
import re
|
|
m = re.match(r"^(\d{5})\s", ai["name"])
|
|
if m:
|
|
mid = m.group(1)
|
|
|
|
acct = account_info.get(ai.get("account_id", ""), {})
|
|
cinfo = canteen_assets.get(mid, {})
|
|
|
|
booking_points.append(
|
|
{
|
|
"source": "booking",
|
|
"machine_id": mid,
|
|
"lat": lat,
|
|
"lng": lng,
|
|
"booking_name": name or "",
|
|
"work_order": wo_name,
|
|
"start_time": str(start or ""),
|
|
"end_time": str(end or ""),
|
|
"arrival_time": str(arrival or ""),
|
|
"account_name": account or acct.get("name", ""),
|
|
"asset_name": ai.get("name", ""),
|
|
"serial": ai.get("serial", ""),
|
|
"connect_id": ai.get("connect_id", ""),
|
|
"equipment_id": ai.get("equipment_id", ""),
|
|
"machine_number": ai.get("machine_number", ""),
|
|
"canteen_name": cinfo.get("name", ""),
|
|
"canteen_company": cinfo.get("company", ""),
|
|
"canteen_make": cinfo.get("make", ""),
|
|
"canteen_model": cinfo.get("model", ""),
|
|
}
|
|
)
|
|
|
|
print(f"Booking GPS points: {len(booking_points)}")
|
|
|
|
# ── SOURCE 2: Photo EXIF GPS (OCR) ──
|
|
photo_points = []
|
|
try:
|
|
with open(f"{MSFS_DATA}/ocr_results.json") as f:
|
|
ocr = json.load(f)
|
|
|
|
for k, v in ocr.items():
|
|
if not (v.get("has_gps") and v.get("machine_id") and v.get("gps_lat") and v.get("gps_lon")):
|
|
continue
|
|
mid_raw = v["machine_id"]
|
|
# Normalize machine ID
|
|
parts = mid_raw.split("-")
|
|
last_part = parts[-1]
|
|
if last_part.startswith("0") and len(last_part) > 5:
|
|
last_part = last_part.lstrip("0")
|
|
mid = last_part
|
|
|
|
cinfo = canteen_assets.get(mid, {})
|
|
|
|
photo_points.append(
|
|
{
|
|
"source": "photo",
|
|
"machine_id": mid,
|
|
"lat": v["gps_lat"],
|
|
"lng": v["gps_lon"],
|
|
"ocr_text": v.get("ocr_text", "")[:200],
|
|
"photo_file": k,
|
|
"canteen_name": cinfo.get("name", ""),
|
|
"canteen_company": cinfo.get("company", ""),
|
|
"canteen_make": cinfo.get("make", ""),
|
|
"canteen_model": cinfo.get("model", ""),
|
|
}
|
|
)
|
|
print(f"Photo EXIF GPS points: {len(photo_points)}")
|
|
except FileNotFoundError:
|
|
print("WARNING: ocr_results.json not found")
|
|
|
|
# ── SOURCE 3: exif-test-dev iPhone uploads ──
|
|
exif_points = []
|
|
try:
|
|
exif_db = sqlite3.connect(EXIF_DEV_DB)
|
|
for r in exif_db.execute(
|
|
"SELECT machine_id, gps_lat, gps_lng, filename, created_at FROM photos "
|
|
"WHERE machine_id IS NOT NULL AND gps_lat IS NOT NULL"
|
|
).fetchall():
|
|
mid, lat, lng, filename, created = r[0], float(r[1]), float(r[2]), r[3], r[4]
|
|
cinfo = canteen_assets.get(mid, {})
|
|
exif_points.append(
|
|
{
|
|
"source": "exif_dev",
|
|
"machine_id": mid,
|
|
"lat": lat,
|
|
"lng": lng,
|
|
"photo_file": filename or "",
|
|
"created_at": str(created or ""),
|
|
"canteen_name": cinfo.get("name", ""),
|
|
"canteen_company": cinfo.get("company", ""),
|
|
"canteen_make": cinfo.get("make", ""),
|
|
"canteen_model": cinfo.get("model", ""),
|
|
}
|
|
)
|
|
print(f"exif-test-dev GPS points: {len(exif_points)}")
|
|
except (FileNotFoundError, sqlite3.OperationalError):
|
|
print("WARNING: exif-test-dev photos.db not found")
|
|
|
|
# ── SOURCE 4: Account GPS (for context/reference) ──
|
|
account_points = []
|
|
for a in db.execute(
|
|
'SELECT "accountid", "name", "address1_latitude", "address1_longitude", '
|
|
'"address1_line1", "address1_city", "address1_stateorprovince", '
|
|
'"address1_postalcode", "address1_country", "telephone1" '
|
|
'FROM "account" '
|
|
'WHERE "address1_latitude" IS NOT NULL AND "address1_longitude" IS NOT NULL'
|
|
).fetchall():
|
|
account_points.append(
|
|
{
|
|
"source": "account",
|
|
"account_id": a[0],
|
|
"account_name": a[1] or "",
|
|
"lat": float(a[2]),
|
|
"lng": float(a[3]),
|
|
"address_line1": a[4] or "",
|
|
"city": a[5] or "",
|
|
"state": a[6] or "",
|
|
"zip": a[7] or "",
|
|
"country": a[8] or "",
|
|
"phone": a[9] or "",
|
|
}
|
|
)
|
|
|
|
print(f"Account GPS points: {len(account_points)}")
|
|
|
|
# ── Combine + deduplicate machine-level GPS ──
|
|
# For each machine_id, compute best GPS from booking (preferred) or photo
|
|
all_points = booking_points + photo_points + exif_points
|
|
|
|
machine_best = {} # machine_id → {lat, lng, source, count}
|
|
for p in all_points:
|
|
mid = p["machine_id"]
|
|
if not mid:
|
|
continue
|
|
if mid not in machine_best:
|
|
machine_best[mid] = {
|
|
"lat": p["lat"],
|
|
"lng": p["lng"],
|
|
"source": p["source"],
|
|
"booking_count": 0,
|
|
"photo_count": 0,
|
|
"exif_dev_count": 0,
|
|
}
|
|
# Booking takes priority
|
|
if p["source"] == "booking":
|
|
if machine_best[mid]["source"] != "booking":
|
|
# Only override if current best isn't also booking
|
|
machine_best[mid]["lat"] = p["lat"]
|
|
machine_best[mid]["lng"] = p["lng"]
|
|
machine_best[mid]["source"] = "booking"
|
|
machine_best[mid]["booking_count"] += 1
|
|
elif p["source"] == "photo" and machine_best[mid]["source"] != "booking":
|
|
machine_best[mid]["lat"] = p["lat"]
|
|
machine_best[mid]["lng"] = p["lng"]
|
|
if machine_best[mid]["source"] != "booking":
|
|
machine_best[mid]["source"] = "photo"
|
|
machine_best[mid]["photo_count"] += 1
|
|
else:
|
|
if p["source"] == "exif_dev":
|
|
machine_best[mid]["exif_dev_count"] += 1
|
|
|
|
# Enrich with canteen info
|
|
for mid in machine_best:
|
|
cinfo = canteen_assets.get(mid, {})
|
|
machine_best[mid]["canteen_name"] = cinfo.get("name", "")
|
|
machine_best[mid]["canteen_company"] = cinfo.get("company", "")
|
|
machine_best[mid]["canteen_make"] = cinfo.get("make", "")
|
|
machine_best[mid]["canteen_model"] = cinfo.get("model", "")
|
|
|
|
# ── Stats ──
|
|
booking_machines = sum(
|
|
1 for v in machine_best.values() if v["booking_count"] > 0
|
|
)
|
|
photo_machines = sum(
|
|
1 for v in machine_best.values() if v["photo_count"] > 0
|
|
)
|
|
exif_machines = sum(
|
|
1 for v in machine_best.values() if v["exif_dev_count"] > 0
|
|
)
|
|
both_machines = sum(
|
|
1
|
|
for v in machine_best.values()
|
|
if v["booking_count"] > 0 and (v["photo_count"] > 0 or v["exif_dev_count"] > 0)
|
|
)
|
|
|
|
# ── Output ──
|
|
output = {
|
|
"generated_at": datetime.now().isoformat(),
|
|
"stats": {
|
|
"total_all_points": len(all_points),
|
|
"booking_points": len(booking_points),
|
|
"photo_points": len(photo_points),
|
|
"exif_dev_points": len(exif_points),
|
|
"account_points": len(account_points),
|
|
"unique_machines": len(machine_best),
|
|
"machines_with_booking": booking_machines,
|
|
"machines_with_photo": photo_machines,
|
|
"machines_with_exif_dev": exif_machines,
|
|
"machines_with_both_sources": both_machines,
|
|
},
|
|
"all_points": all_points,
|
|
"machine_summary": {
|
|
mid: {
|
|
"lat": v["lat"],
|
|
"lng": v["lng"],
|
|
"source": v["source"],
|
|
"canteen_name": v["canteen_name"],
|
|
"canteen_company": v["canteen_company"],
|
|
"canteen_make": v["canteen_make"],
|
|
"canteen_model": v["canteen_model"],
|
|
"booking_count": v["booking_count"],
|
|
"photo_count": v["photo_count"],
|
|
"exif_dev_count": v["exif_dev_count"],
|
|
}
|
|
for mid, v in machine_best.items()
|
|
},
|
|
"account_points": account_points,
|
|
}
|
|
|
|
with open(f"{DATA_DIR}/gps_points.json", "w") as f:
|
|
json.dump(output, f, indent=2)
|
|
|
|
print(f"\nWritten to {DATA_DIR}/gps_points.json")
|
|
print(f" File size: {os.path.getsize(f'{DATA_DIR}/gps_points.json') / 1024 / 1024:.1f} MB")
|