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