#!/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")