""" create_test_installation_lpb4.py — Create a full installation order for test customer LPB4 (C-LPB4). Creates: 1. Service Subscriptions (Internet FTTH150I + TV Standard + Phone) 2. Service Equipment placeholders (ONT, Routeur, Décodeur, Téléphone IP) 3. Project "Installation - LPB4" with ordered tasks (each with n8n webhooks) 4. Dispatch Job linked to the project Each task has: - on_open_webhook: n8n trigger when task status → assigned - on_close_webhook: n8n trigger when task status → completed The n8n workflows can then automate: - OLT port activation (task: "Activer port OLT") - WiFi provisioning (task: "Configurer WiFi") - VoIP provisioning (task: "Configurer VoIP") - Notification to customer (task: "Confirmer service") Run inside erpnext-backend-1: /home/frappe/frappe-bench/env/bin/python /home/frappe/frappe-bench/create_test_installation_lpb4.py Or via REST API from targo-hub — see /api/checkout endpoint design. """ import frappe import os import hashlib from datetime import datetime, timedelta os.chdir("/home/frappe/frappe-bench/sites") frappe.init(site="erp.gigafibre.ca", sites_path=".") frappe.connect() frappe.local.flags.ignore_permissions = True print("Connected:", frappe.local.site) # ═══════════════════════════════════════════════════════════════ # CONFIG — Adjust these for your test # ═══════════════════════════════════════════════════════════════ CUSTOMER_NAME = "C-LPB4" # ERPNext Customer name N8N_BASE = "https://n8n.targo.ca/webhook" # n8n webhook base URL # OLT port info (from fibre table — this is the address-specific port) OLT_IP = "172.17.16.2" # Saint-Anicet OLT OLT_FRAME = 0 OLT_SLOT = 3 OLT_PORT = 2 ONT_ID = 12 # Next available ONT ID on that port VLAN_INTERNET = 100 VLAN_MANAGE = 200 VLAN_TELEPHONE = 300 VLAN_TV = 400 # Preferred installation dates PREFERRED_DATE_1 = (datetime.now() + timedelta(days=3)).strftime("%Y-%m-%d") PREFERRED_DATE_2 = (datetime.now() + timedelta(days=5)).strftime("%Y-%m-%d") PREFERRED_DATE_3 = (datetime.now() + timedelta(days=7)).strftime("%Y-%m-%d") # ═══════════════════════════════════════════════════════════════ # VERIFY CUSTOMER EXISTS # ═══════════════════════════════════════════════════════════════ print("\n" + "=" * 60) print("VERIFY CUSTOMER: {}".format(CUSTOMER_NAME)) print("=" * 60) cust = frappe.db.sql(""" SELECT name, customer_name FROM "tabCustomer" WHERE name = %s """, (CUSTOMER_NAME,), as_dict=True) if not cust: print("ERROR: Customer {} not found!".format(CUSTOMER_NAME)) exit(1) cust = cust[0] print("Found: {} ({})".format(cust["name"], cust["customer_name"])) # Find their Service Location locs = frappe.db.sql(""" SELECT name, address_line, city, postal_code, olt_port FROM "tabService Location" WHERE customer = %s ORDER BY name LIMIT 1 """, (CUSTOMER_NAME,), as_dict=True) if not locs: print("ERROR: No Service Location found for {}".format(CUSTOMER_NAME)) exit(1) loc = locs[0] print("Location: {} — {} {}".format(loc["name"], loc["address_line"], loc["city"])) # ═══════════════════════════════════════════════════════════════ # UPDATE SERVICE LOCATION — OLT port for this installation # ═══════════════════════════════════════════════════════════════ print("\n" + "=" * 60) print("UPDATE SERVICE LOCATION WITH OLT PORT") print("=" * 60) olt_port_str = "{}/{}/{} ONT:{}".format(OLT_FRAME, OLT_SLOT, OLT_PORT, ONT_ID) frappe.db.sql(""" UPDATE "tabService Location" SET olt_port = %(olt_port)s, olt_ip = %(olt_ip)s, ont_id = %(ont_id)s, vlan_internet = %(vlan_inet)s, vlan_manage = %(vlan_mgmt)s, vlan_telephone = %(vlan_tel)s, vlan_tv = %(vlan_tv)s, connection_type = 'Fibre FTTH', status = 'Pending Install' WHERE name = %(name)s """, { "name": loc["name"], "olt_port": olt_port_str, "olt_ip": OLT_IP, "ont_id": ONT_ID, "vlan_inet": VLAN_INTERNET, "vlan_mgmt": VLAN_MANAGE, "vlan_tel": VLAN_TELEPHONE, "vlan_tv": VLAN_TV, }) frappe.db.commit() print("OLT port set: {} on {}".format(olt_port_str, OLT_IP)) # ═══════════════════════════════════════════════════════════════ # CREATE SERVICE SUBSCRIPTIONS # ═══════════════════════════════════════════════════════════════ print("\n" + "=" * 60) print("CREATE SERVICE SUBSCRIPTIONS") print("=" * 60) now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f") today = datetime.now().strftime("%Y-%m-%d") subscriptions = [ { "name": "SUB-TEST-INET-LPB4", "service_category": "Internet", "plan_name": "Fibre 150/150 Mbps", "product_sku": "FTTH150I", "speed_down": 150, "speed_up": 150, "monthly_price": 99.95, }, { "name": "SUB-TEST-TV-LPB4", "service_category": "IPTV", "plan_name": "TV Standard", "product_sku": "TVBSTANDARD", "speed_down": 0, "speed_up": 0, "monthly_price": 30.00, }, { "name": "SUB-TEST-TEL-LPB4", "service_category": "VoIP", "plan_name": "Téléphonie résidentielle", "product_sku": "TELEPMENS", "speed_down": 0, "speed_up": 0, "monthly_price": 28.95, }, ] for sub in subscriptions: exists = frappe.db.exists("Service Subscription", sub["name"]) if exists: print(" {} — already exists, skipping".format(sub["name"])) continue frappe.db.sql(""" INSERT INTO "tabService Subscription" ( name, creation, modified, modified_by, owner, docstatus, idx, customer, customer_name, service_location, status, service_category, plan_name, product_sku, speed_down, speed_up, monthly_price, billing_cycle, start_date ) VALUES ( %(name)s, %(now)s, %(now)s, 'Administrator', 'Administrator', 0, 0, %(customer)s, %(customer_name)s, %(location)s, 'En attente', %(category)s, %(plan_name)s, %(sku)s, %(speed_down)s, %(speed_up)s, %(price)s, 'Mensuel', %(today)s ) """, { "name": sub["name"], "now": now_str, "customer": CUSTOMER_NAME, "customer_name": cust["customer_name"], "location": loc["name"], "category": sub["service_category"], "plan_name": sub["plan_name"], "sku": sub["product_sku"], "speed_down": sub["speed_down"], "speed_up": sub["speed_up"], "price": sub["monthly_price"], "today": today, }) print(" Created: {} — {} @ ${}/mo".format(sub["name"], sub["plan_name"], sub["monthly_price"])) frappe.db.commit() # ═══════════════════════════════════════════════════════════════ # CREATE SERVICE EQUIPMENT PLACEHOLDERS # ═══════════════════════════════════════════════════════════════ print("\n" + "=" * 60) print("CREATE EQUIPMENT PLACEHOLDERS") print("=" * 60) equipment = [ {"name": "EQ-TEST-ONT-LPB4", "type": "ONT", "brand": "TP-Link", "model": "XX230v", "notes": "Serial TBD — tech scans on site"}, {"name": "EQ-TEST-DECO-LPB4", "type": "Routeur", "brand": "TP-Link", "model": "Deco XE75", "notes": "Serial TBD — tech scans on site"}, {"name": "EQ-TEST-STB-LPB4", "type": "Décodeur TV", "brand": "Formuler", "model": "Z11 Pro", "notes": "Serial TBD — tech scans on site"}, {"name": "EQ-TEST-ATA-LPB4", "type": "Téléphone IP", "brand": "Grandstream", "model": "HT812", "notes": "Serial TBD — tech scans on site"}, ] for eq in equipment: exists = frappe.db.exists("Service Equipment", eq["name"]) if exists: print(" {} — already exists, skipping".format(eq["name"])) continue frappe.db.sql(""" INSERT INTO "tabService Equipment" ( name, creation, modified, modified_by, owner, docstatus, idx, equipment_type, brand, model, serial_number, customer, service_location, status, ownership, notes ) VALUES ( %(name)s, %(now)s, %(now)s, 'Administrator', 'Administrator', 0, 0, %(type)s, %(brand)s, %(model)s, %(serial)s, %(customer)s, %(location)s, 'En inventaire', 'Gigafibre', %(notes)s ) """, { "name": eq["name"], "now": now_str, "type": eq["type"], "brand": eq["brand"], "model": eq["model"], "serial": "TBD-" + eq["name"], # Placeholder serial — replaced on scan "customer": CUSTOMER_NAME, "location": loc["name"], "notes": eq["notes"], }) print(" Created: {} — {} {}".format(eq["name"], eq["brand"], eq["model"])) frappe.db.commit() # ═══════════════════════════════════════════════════════════════ # CREATE PROJECT WITH ORDERED TASKS # ═══════════════════════════════════════════════════════════════ print("\n" + "=" * 60) print("CREATE PROJECT + TASKS") print("=" * 60) project_name = "INST-LPB4" project_subject = "Installation - {} ({})".format(cust["customer_name"], CUSTOMER_NAME) if not frappe.db.exists("Project", project_name): frappe.get_doc({ "doctype": "Project", "name": project_name, "project_name": project_subject, "status": "Open", "expected_start_date": today, "expected_end_date": PREFERRED_DATE_1, }).insert(ignore_permissions=True) print("Created Project: {}".format(project_name)) else: print("Project {} already exists".format(project_name)) frappe.db.commit() # ═══════════════════════════════════════════════════════════════ # CREATE DISPATCH JOBS (one per installation task, with n8n webhooks) # ═══════════════════════════════════════════════════════════════ print("\n" + "=" * 60) print("CREATE DISPATCH JOBS WITH N8N WEBHOOKS") print("=" * 60) # Each task = a Dispatch Job with step_order and webhooks # The n8n webhook receives the event and triggers the appropriate automation tasks = [ { "name": "DJ-LPB4-01", "subject": "Préparer équipement (ONT + Deco + STB + ATA)", "job_type": "Installation", "step_order": 1, "on_open": "{}/installation/prepare-equipment".format(N8N_BASE), "on_close": "{}/installation/equipment-ready".format(N8N_BASE), "tags": ["Préparation", "Support"], "notes": "Sortir du stock: XX230v + Deco XE75 + Formuler Z11 Pro + HT812.\n" "Pré-configurer WiFi SSID/password si possible.", }, { "name": "DJ-LPB4-02", "subject": "Activer port OLT {}/{}/{} ONT:{}".format(OLT_FRAME, OLT_SLOT, OLT_PORT, ONT_ID), "job_type": "Installation", "step_order": 2, "on_open": "{}/installation/activate-olt-port".format(N8N_BASE), "on_close": "{}/installation/olt-port-active".format(N8N_BASE), "tags": ["OLT", "Support"], "notes": "OLT: {} — Port: {}/{}/{}\n" "ONT ID: {}\n" "VLANs: inet={}, mgmt={}, tel={}, tv={}\n\n" "PRE-AUTH OPTION: If ONT GPON serial is known (scanned in warehouse),\n" "register it on the OLT now so it auto-activates when tech plugs it in.\n" "SSH to OLT → ont add / ont confirm / service-port create".format( OLT_IP, OLT_FRAME, OLT_SLOT, OLT_PORT, ONT_ID, VLAN_INTERNET, VLAN_MANAGE, VLAN_TELEPHONE, VLAN_TV), }, { "name": "DJ-LPB4-03", "subject": "Configurer WiFi (Deco XE75)", "job_type": "Installation", "step_order": 3, "on_open": "{}/installation/configure-wifi".format(N8N_BASE), "on_close": "{}/installation/wifi-ready".format(N8N_BASE), "tags": ["WiFi", "Support"], "notes": "Generate WiFi SSID: Gigafibre-{}\n" "Generate random password (12 chars)\n" "Store on Service Equipment → wifi_ssid + wifi_password\n" "ACS will push config when Deco bootstraps.".format( cust["customer_name"].split()[-1] if cust["customer_name"] else "Client"), }, { "name": "DJ-LPB4-04", "subject": "Configurer VoIP (SIP sur HT812)", "job_type": "Installation", "step_order": 4, "on_open": "{}/installation/configure-voip".format(N8N_BASE), "on_close": "{}/installation/voip-ready".format(N8N_BASE), "tags": ["VoIP", "Support"], "notes": "Allocate SIP account from Routr/Fonoster.\n" "Store SIP credentials on Service Equipment → sip_username + sip_password\n" "ACS will push VoIP config when ONT bootstraps.", }, { "name": "DJ-LPB4-05", "subject": "Installation sur site — {}".format(loc["address_line"] or ""), "job_type": "Installation", "step_order": 5, "on_open": "{}/installation/dispatch-tech".format(N8N_BASE), "on_close": "{}/installation/onsite-complete".format(N8N_BASE), "tags": ["Fibre", "Installation", "Terrain"], "notes": "Adresse: {} {}\n" "Dates préférées: {}, {}, {}\n\n" "Tech workflow:\n" "1. Scanner ONT barcode → updates serial on EQ-TEST-ONT-LPB4\n" " → Triggers OLT pre-auth if not already done (n8n webhook)\n" "2. Scanner Deco barcode → updates serial on EQ-TEST-DECO-LPB4\n" "3. Scanner STB barcode → updates serial on EQ-TEST-STB-LPB4\n" "4. Scanner ATA barcode → updates serial on EQ-TEST-ATA-LPB4\n" "5. Brancher fibre → ONT powers up → TR-069 bootstrap\n" "6. Vérifier signal ONT\n" "7. Confirmer WiFi + VoIP fonctionnels".format( loc["address_line"], loc["city"], PREFERRED_DATE_1, PREFERRED_DATE_2, PREFERRED_DATE_3), }, { "name": "DJ-LPB4-06", "subject": "Vérifier signal ONT + services", "job_type": "Installation", "step_order": 6, "on_open": "{}/installation/verify-signal".format(N8N_BASE), "on_close": "{}/installation/signal-verified".format(N8N_BASE), "tags": ["Fibre", "Validation"], "notes": "Check ONT optical power (target: -8 to -25 dBm)\n" "Check internet connectivity\n" "Check TV streams\n" "Check phone dial tone", }, { "name": "DJ-LPB4-07", "subject": "Confirmer service + signature client", "job_type": "Installation", "step_order": 7, "on_open": "{}/installation/confirm-service".format(N8N_BASE), "on_close": "{}/installation/service-live".format(N8N_BASE), "tags": ["Validation", "Client"], "notes": "Get customer signature confirming service works.\n" "On completion:\n" " → n8n activates subscriptions (status: En attente → Actif)\n" " → n8n updates Service Location (status: Pending Install → Active)\n" " → n8n triggers Stripe charge (first month + one-time fees)\n" " → n8n sends SMS/email to customer: 'Service activé!'\n" " → n8n closes Project", }, ] for task in tasks: exists = frappe.db.exists("Dispatch Job", task["name"]) if exists: print(" {} — already exists, skipping".format(task["name"])) continue doc = frappe.get_doc({ "doctype": "Dispatch Job", "name": task["name"], "subject": task["subject"], "customer": CUSTOMER_NAME, "service_location": loc["name"], "job_type": task["job_type"], "step_order": task["step_order"], "project": project_name, "status": "open", "scheduled_date": PREFERRED_DATE_1 if task["step_order"] >= 5 else today, "on_open_webhook": task["on_open"], "on_close_webhook": task["on_close"], "completion_notes": task["notes"], }) doc.insert(ignore_permissions=True) # Add tags for tag in task.get("tags", []): try: frappe.db.sql(""" INSERT INTO "tabDispatch Tags" (name, creation, modified, modified_by, owner, docstatus, idx, parent, parentfield, parenttype, tag) VALUES (%(n)s, %(now)s, %(now)s, 'Administrator', 'Administrator', 0, 0, %(parent)s, 'tags', 'Dispatch Job', %(tag)s) """, {"n": "{}-{}".format(task["name"], tag[:8]), "now": now_str, "parent": task["name"], "tag": tag}) except Exception: pass # Tag may already exist or table structure different print(" Created: {} — Step {} — {}".format(task["name"], task["step_order"], task["subject"])) frappe.db.commit() # ═══════════════════════════════════════════════════════════════ # SUMMARY # ═══════════════════════════════════════════════════════════════ print("\n" + "=" * 60) print("TEST INSTALLATION ORDER CREATED FOR {}".format(CUSTOMER_NAME)) print("=" * 60) print("") print("Customer: {} ({})".format(cust["customer_name"], CUSTOMER_NAME)) print("Location: {} — {} {}".format(loc["name"], loc["address_line"], loc["city"])) print("OLT: {} — {}/{}/{} ONT:{}".format(OLT_IP, OLT_FRAME, OLT_SLOT, OLT_PORT, ONT_ID)) print("Project: {}".format(project_name)) print("") print("Subscriptions:") for sub in subscriptions: print(" {} — {} @ ${}/mo".format(sub["name"], sub["plan_name"], sub["monthly_price"])) print("") print("Equipment (serials TBD — tech scans on site):") for eq in equipment: print(" {} — {} {}".format(eq["name"], eq["brand"], eq["model"])) print("") print("Dispatch Jobs (7 steps with n8n webhooks):") for task in tasks: print(" Step {}: {} → {}".format(task["step_order"], task["name"], task["subject"][:50])) print(" open→ {}".format(task["on_open"])) print(" close→ {}".format(task["on_close"])) print("") print("Preferred dates: {}, {}, {}".format(PREFERRED_DATE_1, PREFERRED_DATE_2, PREFERRED_DATE_3)) print("") print("NEXT STEPS:") print(" 1. Assign support tasks (steps 1-4) to support team") print(" 2. Assign on-site tasks (steps 5-7) to field tech via dispatch") print(" 3. Tech scans barcodes → triggers OLT activation via n8n") print(" 4. Service confirmed → n8n charges Stripe + activates subscriptions")