shawn
ae3b114ebc
- Added import_seed.py — reads Machine List(8).xlsx, matches by Asset ID → machine_id
- 51 new columns on assets table (route, barcode, sales, contact info, etc.)
- seed_data table with full 62-field JSON per asset for detail enrichment
- Seed data card in frontend asset detail view
- GET /api/assets/{id} now returns result.seed and result.seed_imported_at
- GPS columns from Seed ignored per request
- 1,654 matched, 199 unmatched (Seed-only assets not in MSFS)
Refs #47
293 lines
12 KiB
Python
293 lines
12 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Import Seed (mycantaloupe.com) Excel data into the Canteen Asset Tracker.
|
|
|
|
Steps:
|
|
1. Read Machine List(8).xlsx — 62 columns of Seed export
|
|
2. Match rows by Asset ID → assets.machine_id
|
|
3. Add key columns to assets table
|
|
4. Create seed_data table with full JSON per asset
|
|
5. Update matched assets, report match stats
|
|
|
|
Usage:
|
|
python3 import_seed.py
|
|
"""
|
|
|
|
import json
|
|
import os
|
|
import sqlite3
|
|
import sys
|
|
from datetime import datetime, date
|
|
|
|
import openpyxl
|
|
|
|
# ── Paths ──────────────────────────────────────────────────────────────────
|
|
PROJECT_DIR = os.path.expanduser("~/projects/canteen-asset-tracker")
|
|
DB_PATH = os.path.join(PROJECT_DIR, "assets.db")
|
|
EXCEL_PATH = os.path.expanduser(
|
|
"/home/oplabs/.hermes/profiles/coder/cache/documents/doc_305e40fb92dd_Machine List(8).xlsx"
|
|
)
|
|
|
|
# ── Columns to extract as first-class DB columns ──────────────────────────
|
|
# (db_column, excel_header, type)
|
|
KEY_COLUMNS = [
|
|
("device", "Device", "TEXT DEFAULT ''"),
|
|
("seed_location", "Location", "TEXT DEFAULT ''"),
|
|
("seed_city", "City", "TEXT DEFAULT ''"),
|
|
("state", "State", "TEXT DEFAULT ''"),
|
|
("postal_code", "Postal Code", "TEXT DEFAULT ''"),
|
|
("route_name", "Route", "TEXT DEFAULT ''"),
|
|
("subroute_name", "Subroute", "TEXT DEFAULT ''"),
|
|
("seed_class", "Class", "TEXT DEFAULT ''"),
|
|
("customer_name", "Customer", "TEXT DEFAULT ''"),
|
|
("management_company", "Management Company", "TEXT DEFAULT ''"),
|
|
("branch", "Branch", "TEXT DEFAULT ''"),
|
|
("location_code", "Location Code", "TEXT DEFAULT ''"),
|
|
("customer_code", "Customer Code", "TEXT DEFAULT ''"),
|
|
("barcode", "Barcode", "TEXT DEFAULT ''"),
|
|
("has_cashless", "Has Cashless", "TEXT DEFAULT ''"),
|
|
("phone", "Phone", "TEXT DEFAULT ''"),
|
|
("fax", "Fax", "TEXT DEFAULT ''"),
|
|
("email", "Email", "TEXT DEFAULT ''"),
|
|
("asset_family", "Asset Family", "TEXT DEFAULT ''"),
|
|
("business_type", "Business Type", "TEXT DEFAULT ''"),
|
|
("primary_consumer_type","Primary Consumer Type","TEXT DEFAULT ''"),
|
|
("machine_branding", "Machine Branding", "TEXT DEFAULT ''"),
|
|
("valid_address", "Valid Address", "TEXT DEFAULT ''"),
|
|
("non_revenue", "Non-Revenue", "TEXT DEFAULT ''"),
|
|
("alerts", "Alerts", "TEXT DEFAULT ''"),
|
|
("coil_alerts", "Coil Alerts", "INTEGER DEFAULT 0"),
|
|
("product_alerts", "Product Alerts", "INTEGER DEFAULT 0"),
|
|
("daily_avg_sales", "Daily Average Sales", "REAL DEFAULT NULL"),
|
|
("monthly_sales", "Monthly Sales", "REAL DEFAULT NULL"),
|
|
("yearly_sales", "Yearly Sales", "REAL DEFAULT NULL"),
|
|
("today_sales", "Today Sales", "REAL DEFAULT NULL"),
|
|
("yesterday_sales", "Yesterday Sales", "REAL DEFAULT NULL"),
|
|
("weekly_sales", "Weekly Sales", "REAL DEFAULT NULL"),
|
|
("sales_restock", "Sales (Restock)", "REAL DEFAULT NULL"),
|
|
("last_restock", "Last Restock", "TEXT DEFAULT ''"),
|
|
("days_since_restock", "Days Since Restock", "INTEGER DEFAULT NULL"),
|
|
("last_contact_time", "Last Contact Time", "TEXT DEFAULT ''"),
|
|
("last_dex_report_time", "Last Dex Report Time","TEXT DEFAULT ''"),
|
|
("last_inventory", "Last Inventory", "TEXT DEFAULT ''"),
|
|
("prepick_group", "Prepick Group", "TEXT DEFAULT ''"),
|
|
("added_date", "Added Date", "TEXT DEFAULT ''"),
|
|
("purchase_date", "Purchase Date", "TEXT DEFAULT ''"),
|
|
("purchase_price", "Purchase Price", "REAL DEFAULT NULL"),
|
|
("depreciation_years", "Depreciation Years", "REAL DEFAULT NULL"),
|
|
("cash_discount", "Cash Discount", "REAL DEFAULT NULL"),
|
|
("tax_jurisdiction", "Tax Jurisdiction", "TEXT DEFAULT ''"),
|
|
("commission_plan", "Commission Plan", "TEXT DEFAULT ''"),
|
|
("acquirer_from", "Acquired From", "TEXT DEFAULT ''"),
|
|
("changer_par", "Changer Par", "TEXT DEFAULT ''"),
|
|
("machine_management_code","Machine Management Code","TEXT DEFAULT ''"),
|
|
]
|
|
|
|
# ── All 62 columns for the seed_data JSON dump ────────────────────────────
|
|
ALL_SEED_FIELDS = [
|
|
"Device", "Location", "Asset ID", "Place", "Type",
|
|
"City", "Address", "Last Contact Time", "Last Dex Report Time",
|
|
"Last Restock", "Coil Alerts", "Product Alerts", "Sales (Restock)",
|
|
"Daily Average Sales", "Today Sales", "Yesterday Sales", "Weekly Sales",
|
|
"Monthly Sales", "Yearly Sales", "Days Since Restock", "Prepick Group",
|
|
"Customer", "Management Company", "Management Account",
|
|
"Machine Management Code", "Route", "Subroute", "Changer Par",
|
|
"Acquired From", "Purchase Date", "Purchase Price", "Depreciation Years",
|
|
"Post-Depreciation Monthly Cost", "State", "Postal Code", "Deployed",
|
|
"Pulled Date", "Serial Number", "Class", "Make", "Model",
|
|
"Cash Discount", "Tax Jurisdiction", "Commission Plan", "Barcode",
|
|
"Non-Revenue", "Added Date", "Phone", "Fax", "Email", "Has Cashless",
|
|
"Branch", "Location Code", "Customer Code", "Last Inventory",
|
|
"Asset Family", "Status", "Alerts", "Valid Address",
|
|
"Business Type", "Primary Consumer Type", "Machine Branding",
|
|
]
|
|
|
|
|
|
# ── Schema migration helpers ──────────────────────────────────────────────
|
|
def get_db_columns(conn):
|
|
cursor = conn.execute("PRAGMA table_info(assets)")
|
|
return {row[1] for row in cursor.fetchall()}
|
|
|
|
|
|
def add_column_if_missing(conn, col_name, col_type):
|
|
existing = get_db_columns(conn)
|
|
if col_name not in existing:
|
|
sql = f"ALTER TABLE assets ADD COLUMN {col_name} {col_type}"
|
|
conn.execute(sql)
|
|
print(f" Added column: {col_name} {col_type}")
|
|
return True
|
|
return False
|
|
|
|
|
|
def create_seed_data_table(conn):
|
|
conn.execute("""
|
|
CREATE TABLE IF NOT EXISTS seed_data (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
|
|
raw_data TEXT NOT NULL,
|
|
imported_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE(asset_id)
|
|
)
|
|
""")
|
|
print(" seed_data table ready")
|
|
# Add index
|
|
conn.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_seed_data_asset_id
|
|
ON seed_data(asset_id)
|
|
""")
|
|
|
|
|
|
# ── Parse Excel ──────────────────────────────────────────────────────────
|
|
def read_excel():
|
|
"""Return (header_map, rows) where header_map is ExcelHeader->col_index."""
|
|
wb = openpyxl.load_workbook(EXCEL_PATH, data_only=True)
|
|
ws = wb.active
|
|
headers = {}
|
|
for col in range(1, ws.max_column + 1):
|
|
val = ws.cell(row=1, column=col).value
|
|
if val is not None:
|
|
headers[str(val).strip()] = col
|
|
|
|
rows = []
|
|
for row_idx in range(2, ws.max_row + 1):
|
|
row_data = {}
|
|
for hdr, col in headers.items():
|
|
val = ws.cell(row=row_idx, column=col).value
|
|
row_data[hdr] = val
|
|
row_data["_row"] = row_idx
|
|
rows.append(row_data)
|
|
|
|
return headers, rows
|
|
|
|
|
|
# ── Main import ──────────────────────────────────────────────────────────
|
|
def main():
|
|
print("═══ SEED DATA IMPORT ═══\n")
|
|
|
|
# 1. Connect to DB
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA foreign_keys=ON")
|
|
print(f"[DB] Connected: {DB_PATH}")
|
|
|
|
# 2. Schema migration
|
|
print("\n[MIGRATE] Adding new asset columns if missing...")
|
|
for col_name, excel_header, col_type in KEY_COLUMNS:
|
|
add_column_if_missing(conn, col_name, col_type)
|
|
|
|
print("\n[MIGRATE] Creating seed_data table...")
|
|
create_seed_data_table(conn)
|
|
conn.commit()
|
|
|
|
# 3. Read Excel
|
|
print(f"\n[READ] Reading {EXCEL_PATH}...")
|
|
headers, rows = read_excel()
|
|
print(f"[READ] Found {len(headers)} columns, {len(rows)} data rows")
|
|
|
|
# 4. Pre-build machine_id lookup
|
|
cursor = conn.execute("SELECT id, machine_id FROM assets")
|
|
machine_map = {} # machine_id -> asset_id
|
|
for row in cursor.fetchall():
|
|
machine_map[str(row["machine_id"]).strip()] = row["id"]
|
|
|
|
print(f"[MATCH] DB has {len(machine_map)} assets to match against")
|
|
|
|
# 5. Import
|
|
matched = 0
|
|
skipped_no_id = 0
|
|
unmatched = 0
|
|
key_field_updates = 0
|
|
json_inserts = 0
|
|
|
|
for row in rows:
|
|
asset_id_val = row.get("Asset ID")
|
|
if asset_id_val is None or str(asset_id_val).strip() == "":
|
|
skipped_no_id += 1
|
|
continue
|
|
|
|
asset_id_str = str(asset_id_val).strip()
|
|
db_asset_id = machine_map.get(asset_id_str)
|
|
|
|
if db_asset_id is None:
|
|
unmatched += 1
|
|
continue
|
|
|
|
# Update key columns on assets table
|
|
update_parts = []
|
|
update_vals = []
|
|
for col_name, excel_header, col_type in KEY_COLUMNS:
|
|
val = row.get(excel_header)
|
|
# Handle None → NULL for SQL
|
|
update_parts.append(f"{col_name} = ?")
|
|
if val is None:
|
|
update_vals.append(None)
|
|
else:
|
|
if "INTEGER" in col_type:
|
|
try:
|
|
update_vals.append(int(val))
|
|
except (ValueError, TypeError):
|
|
update_vals.append(0)
|
|
elif "REAL" in col_type:
|
|
try:
|
|
update_vals.append(float(val))
|
|
except (ValueError, TypeError):
|
|
update_vals.append(None)
|
|
else:
|
|
update_vals.append(str(val))
|
|
|
|
update_vals.append(db_asset_id)
|
|
conn.execute(
|
|
f"UPDATE assets SET {', '.join(update_parts)} WHERE id = ?",
|
|
update_vals,
|
|
)
|
|
key_field_updates += 1
|
|
|
|
# Insert/upsert seed_data JSON
|
|
raw_data = {}
|
|
for hdr in ALL_SEED_FIELDS:
|
|
val = row.get(hdr)
|
|
if val is not None:
|
|
raw_data[hdr] = val
|
|
|
|
# Convert non-serializable types (datetime, etc.) to strings
|
|
def _serialize(v):
|
|
if isinstance(v, (datetime, date)):
|
|
return v.isoformat()
|
|
return v
|
|
raw_json = json.dumps(raw_data, default=_serialize)
|
|
conn.execute(
|
|
"""INSERT OR REPLACE INTO seed_data (asset_id, raw_data, imported_at)
|
|
VALUES (?, ?, datetime('now'))""",
|
|
(db_asset_id, raw_json),
|
|
)
|
|
json_inserts += 1
|
|
matched += 1
|
|
|
|
conn.commit()
|
|
|
|
# 6. Stats
|
|
print(f"\n═══ RESULTS ═══")
|
|
print(f"Seed rows: {len(rows)}")
|
|
print(f"Matched (↔ assets): {matched}")
|
|
print(f"Unmatched (no asset): {unmatched}")
|
|
print(f"Rows without Asset ID: {skipped_no_id}")
|
|
print(f"Key field updates: {key_field_updates}")
|
|
print(f"JSON blobs inserted: {json_inserts}")
|
|
|
|
# Re-count assets with seed data
|
|
seed_count = conn.execute(
|
|
"SELECT COUNT(*) FROM seed_data"
|
|
).fetchone()[0]
|
|
print(f"\nseed_data table: {seed_count} rows")
|
|
|
|
# Show DB size
|
|
db_size = os.path.getsize(DB_PATH) / (1024 * 1024)
|
|
print(f"DB size: {db_size:.1f} MB")
|
|
|
|
conn.close()
|
|
print("\n═══ DONE ═══")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|