#!/usr/bin/env python3 """ Reconcile manual ROA invoices with GoMag orders left "nefacturate". Context ------- When the Oracle pool / sync is down (e.g. after a power loss) the warehouse operator emits invoices MANUALLY in ROA. Those land in `VANZARI` with `ID_COMANDA = NULL` (vs the app's normal flow which sets `ID_COMANDA` and links to `COMENZI.COMANDA_EXTERNA` = GoMag order no). Once the app recovers it imports the same web orders into `COMENZI`, but the manual invoice is never linked, so the order stays "nefacturat" in ROA and in the dashboard. This script finds those orphan invoices (`VANZARI.ID_COMANDA IS NULL`, `sters=0`) and links each to its GoMag order, matching by **exact total + partner/name**, then populates the SQLite invoice cache (`orders.factura_*`) exactly like the app. IMPORTANT — warehouse / walk-in invoices ----------------------------------------- The operator ALSO emits genuine manual invoices directly from the warehouse, with NO online order behind them (~20+/day). Those have no matching uninvoiced GoMag order, so they get classified SKIP_NOMATCH and are LEFT UNTOUCHED. The matching is deliberately conservative: anything not an unambiguous 1:1 match is reported for manual review, never auto-linked. Run it ON the production server (it needs prod Oracle + prod import.db): # dry-run (default) — shows the plan, changes nothing C:\\gomag-vending\\venv\\Scripts\\python.exe scripts\\relink_manual_invoices.py # apply, with confirmation ... scripts\\relink_manual_invoices.py --apply # apply without confirmation (automation) ... scripts\\relink_manual_invoices.py --apply --yes # widen / narrow the lookback window (default: last 3 days) ... scripts\\relink_manual_invoices.py --days 5 From the dev container you can drive it over SSH: scp -P 22122 scripts/relink_manual_invoices.py gomag@79.119.86.134:C:/gomag-vending/scripts/ ssh -p 22122 gomag@79.119.86.134 "cd C:\\gomag-vending\\api; \ $env:TNS_ADMIN='C:\\roa\\instantclient_11_2_0_2'; \ C:\\gomag-vending\\venv\\Scripts\\python.exe ..\\scripts\\relink_manual_invoices.py" """ import argparse import os import re import sqlite3 import sys # Windows service console is cp1252; keep output robust regardless of code page. try: sys.stdout.reconfigure(encoding="utf-8", errors="replace") except Exception: pass # Make the app package importable + load .env-backed settings (Oracle creds, SQLite path). _API_ROOT = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "api") sys.path.insert(0, _API_ROOT) import oracledb # noqa: E402 from app.config import settings # noqa: E402 # Match tolerance for money comparison (lei). Totals are stored to 2 decimals. MONEY_EPS = 0.01 # ─── Oracle ────────────────────────────────────────────────────────────────── def oracle_connect(): if settings.TNS_ADMIN: os.environ.setdefault("TNS_ADMIN", settings.TNS_ADMIN) if settings.INSTANTCLIENTPATH: try: oracledb.init_oracle_client(lib_dir=settings.INSTANTCLIENTPATH) except Exception: pass # already initialized / thin mode return oracledb.connect( user=settings.ORACLE_USER, password=settings.ORACLE_PASSWORD, dsn=settings.ORACLE_DSN, ) def fetch_orphan_invoices(cur, days): """Manual invoices with no order link, created in the lookback window.""" cur.execute( """ SELECT v.ID_VANZARE, v.ID_PART, v.SERIE_ACT, v.NUMAR_ACT, v.TOTAL_FARA_TVA, v.TOTAL_TVA, v.TOTAL_CU_TVA, TO_CHAR(v.DATA_ACT, 'YYYY-MM-DD') AS data_act, TO_CHAR(v.DATAORA, 'YYYY-MM-DD HH24:MI') AS creat, v.TIP, p.DENUMIRE FROM VANZARI v LEFT JOIN NOM_PARTENERI p ON p.ID_PART = v.ID_PART WHERE v.STERS = 0 AND v.ID_COMANDA IS NULL AND v.DATAORA >= TRUNC(SYSDATE) - :days ORDER BY v.DATAORA """, days=days, ) cols = [d[0].lower() for d in cur.description] return [dict(zip(cols, r)) for r in cur.fetchall()] def comanda_active(cur, id_comanda): cur.execute("SELECT COUNT(*) FROM COMENZI WHERE ID_COMANDA = :1 AND STERS = 0", [id_comanda]) return cur.fetchone()[0] == 1 def comanda_already_invoiced(cur, id_comanda): cur.execute( "SELECT COUNT(*) FROM VANZARI WHERE ID_COMANDA = :1 AND STERS = 0", [id_comanda] ) return cur.fetchone()[0] > 0 def order_line_residual(cur, id_comanda, extra_idv=None): """COMENZI_ELEMENTE lines NOT covered by the linked invoice(s), per ROA's own line-level facturat test (`PACK_FACTURARE.cursor_comanda`): invoiced quantity is matched to the order on **ID_ARTICOL + exact PRET**, and a line is "still to invoice" when `SIGN(CANTITATE) * (CANTITATE - NVL(facturat, 0)) > 0`. `extra_idv` simulates an invoice about to be linked, so the residual can be PREDICTED before `--apply` (when VANZARI.ID_COMANDA is not set yet). A non-empty result means linking the VANZARI header is NOT enough — ROA will STILL show the order *nefacturat* (even though the app dashboard, which only checks the header link, shows it facturat). Typical cause: the manual invoice consolidated the order's discount lines (e.g. per-VAT-rate discounts merged into one 0%-TVA line), so the prices no longer match the order's COMENZI_ELEMENTE. Those need a manual line fix in ROA — the script never touches order lines. """ cur.execute( """ SELECT A.ID_COMANDA_ELEMENT, A.ID_ARTICOL, A.CANTITATE, A.PRET, NVL(D.CANTITATE, 0) AS FACTURAT FROM COMENZI_ELEMENTE A LEFT JOIN (SELECT B1.ID_ARTICOL, B1.PRET, SUM(B1.CANTITATE) AS CANTITATE FROM VANZARI A1 JOIN VANZARI_DETALII B1 ON A1.ID_VANZARE = B1.ID_VANZARE AND B1.STERS = 0 WHERE A1.STERS = 0 AND (A1.ID_COMANDA = :idc OR A1.ID_VANZARE = :idv) GROUP BY B1.ID_ARTICOL, B1.PRET) D ON A.ID_ARTICOL = D.ID_ARTICOL AND A.PRET = D.PRET WHERE A.STERS = 0 AND A.ID_COMANDA = :idc AND SIGN(A.CANTITATE) * (A.CANTITATE - NVL(D.CANTITATE, 0)) > 0 """, idc=id_comanda, idv=extra_idv, ) cols = [d[0].lower() for d in cur.description] return [dict(zip(cols, r)) for r in cur.fetchall()] # ─── SQLite ────────────────────────────────────────────────────────────────── def fetch_uninvoiced_orders(db, days): """Imported GoMag orders that have an id_comanda but no cached invoice yet.""" cur = db.execute( """ SELECT order_number, id_comanda, id_partener, order_total, customer_name, shipping_name, billing_name FROM orders WHERE status IN ('IMPORTED', 'ALREADY_IMPORTED') AND id_comanda IS NOT NULL AND (factura_numar IS NULL OR factura_numar = '') AND order_date >= date('now', ?) """, (f'-{days} day',), ) return [dict(r) for r in cur.fetchall()] # ─── Name matching (handles duplicate partner records) ──────────────────────── _NAME_NOISE = re.compile( r"\b(S\.?R\.?L\.?|S\.?C\.?|S\.?A\.?|P\.?F\.?A\.?|II|SRL|SC|SA)\b", re.IGNORECASE ) def _tokens(name): if not name: return set() name = _NAME_NOISE.sub(" ", name.upper()) name = re.sub(r"[^A-Z0-9 ]", " ", name) return {t for t in name.split() if len(t) >= 3} def name_match(a, b): """Conservative name overlap — tolerant of word order and SRL/SC noise.""" ta, tb = _tokens(a), _tokens(b) if not ta or not tb: return False shared = ta & tb return len(shared) >= 1 and len(shared) >= min(len(ta), len(tb)) * 0.5 def money_eq(a, b): return a is not None and b is not None and abs(float(a) - float(b)) <= MONEY_EPS # ─── Matching ───────────────────────────────────────────────────────────────── def classify(inv, orders, cur): """Decide what to do with one orphan invoice. Returns (action, order_or_None, note). action in: LINK unambiguous match -> will link SKIP_NOMATCH no uninvoiced GoMag order with this total -> warehouse/walk-in invoice SKIP_AMBIGUOUS several plausible orders -> needs a human SKIP_ALREADY matched comanda already has an invoice / is gone """ total = inv["total_cu_tva"] cands = [o for o in orders if money_eq(o["order_total"], total)] if not cands: return ("SKIP_NOMATCH", None, "fara comanda online cu acest total (factura depozit)") def pick(subset, why): o = subset[0] if not comanda_active(cur, o["id_comanda"]): return ("SKIP_ALREADY", None, f"comanda {o['id_comanda']} nu mai e activa in ROA") if comanda_already_invoiced(cur, o["id_comanda"]): return ("SKIP_ALREADY", None, f"comanda {o['id_comanda']} are deja factura") return ("LINK", o, why) by_partner = [o for o in cands if o["id_partener"] == inv["id_part"]] by_name = [ o for o in cands if name_match(inv["denumire"], o["customer_name"]) or name_match(inv["denumire"], o["shipping_name"]) or name_match(inv["denumire"], o["billing_name"]) ] if len(by_partner) == 1: return pick(by_partner, "potrivire partener+total") if len(by_partner) > 1: return ("SKIP_AMBIGUOUS", None, f"{len(by_partner)} comenzi acelasi partener+total: " + ", ".join(o["order_number"] for o in by_partner)) if len(by_name) == 1: return pick(by_name, "potrivire nume+total (partener dublat)") if len(by_name) > 1: return ("SKIP_AMBIGUOUS", None, f"{len(by_name)} comenzi nume+total: " + ", ".join(o["order_number"] for o in by_name)) if len(cands) == 1: return ("SKIP_AMBIGUOUS", None, f"total se potriveste cu {cands[0]['order_number']} dar partenerul si numele difera") return ("SKIP_AMBIGUOUS", None, f"{len(cands)} comenzi cu acelasi total, niciun partener/nume sigur") # ─── Apply ──────────────────────────────────────────────────────────────────── def apply_link(ora_cur, db, inv, order): """Link VANZARI -> COMANDA in Oracle and cache the invoice onto the SQLite order.""" ora_cur.execute( "UPDATE VANZARI SET ID_COMANDA = :1 " "WHERE ID_VANZARE = :2 AND ID_COMANDA IS NULL AND STERS = 0", [order["id_comanda"], inv["id_vanzare"]], ) linked = ora_cur.rowcount == 1 if linked: numar = int(inv["numar_act"]) if inv["numar_act"] is not None else None db.execute( """ UPDATE orders SET factura_serie = ?, factura_numar = ?, factura_total_fara_tva = ?, factura_total_tva = ?, factura_total_cu_tva = ?, factura_data = ?, invoice_checked_at = datetime('now'), updated_at = datetime('now') WHERE order_number = ? AND (factura_numar IS NULL OR factura_numar = '') """, (inv["serie_act"], numar, float(inv["total_fara_tva"] or 0), float(inv["total_tva"] or 0), float(inv["total_cu_tva"] or 0), inv["data_act"], order["order_number"]), ) return linked # ─── Main ───────────────────────────────────────────────────────────────────── def main(): ap = argparse.ArgumentParser(description="Relink manual ROA invoices to GoMag orders.") ap.add_argument("--apply", action="store_true", help="apply changes (default: dry-run)") ap.add_argument("--yes", action="store_true", help="skip confirmation prompt") ap.add_argument("--days", type=int, default=3, help="lookback window in days (default 3)") args = ap.parse_args() conn = oracle_connect() ora_cur = conn.cursor() db = sqlite3.connect(settings.SQLITE_DB_PATH) db.row_factory = sqlite3.Row invoices = fetch_orphan_invoices(ora_cur, args.days) orders = fetch_uninvoiced_orders(db, args.days) print(f"Fereastra: ultimele {args.days} zile") print(f"Facturi orfane (VANZARI ID_COMANDA NULL, sters=0): {len(invoices)}") print(f"Comenzi GoMag nefacturate (cu id_comanda): {len(orders)}\n") plans = [] for inv in invoices: action, order, note = classify(inv, orders, ora_cur) plans.append((inv, action, order, note)) # an order can only back one invoice if action == "LINK" and order is not None: orders = [o for o in orders if o["order_number"] != order["order_number"]] # Predict ROA's line-level residual for each LINK. Linking the VANZARI header is # not always enough: if the manual invoice represented the lines differently than # the order (e.g. consolidated discounts), ROA still shows the order nefacturat. residuals = {} for inv, action, order, _note in plans: if action == "LINK" and order is not None: res = order_line_residual(ora_cur, order["id_comanda"], inv["id_vanzare"]) if res: residuals[order["order_number"]] = res def show(action, detailed=True): rows = [(i, o, n) for (i, a, o, n) in plans if a == action] if not rows: return print(f"-- {action} ({len(rows)}) --") if not detailed: print(" (facturi de depozit, fara comanda online — lasate neatinse)\n") return for inv, order, note in rows: tag = f"-> {order['order_number']} (idcom {order['id_comanda']})" if order else "" print(f" IDV={inv['id_vanzare']} {inv['serie_act']}{inv['numar_act']} " f"tot={inv['total_cu_tva']} [{inv['denumire']}] {tag} {note}") res = residuals.get(order["order_number"]) if order else None if res: print(f" !! ATENTIE: dupa legare ROA va arata comanda tot NEFACTURATA " f"({len(res)} linii reziduale la nivel de element — factura nu le acopera " f"pe ID_ARTICOL+PRET; probabil discount comasat/0% TVA). Necesita corectie " f"manuala a liniilor in ROA:") for r in res: print(f" ART={r['id_articol']} CANT_COMANDA={r['cantitate']} " f"PRET={r['pret']} facturat={r['facturat']}") print() for a in ("LINK", "SKIP_AMBIGUOUS", "SKIP_ALREADY"): show(a) show("SKIP_NOMATCH", detailed=False) to_link = [(i, o) for (i, a, o, n) in plans if a == "LINK"] ambiguous = sum(1 for (_, a, _, _) in plans if a == "SKIP_AMBIGUOUS") with_residual = sum(1 for (_, o) in to_link if o and o["order_number"] in residuals) print(f"De legat: {len(to_link)} | De verificat manual (AMBIGUOUS): {ambiguous} | " f"Neatinse (depozit): {sum(1 for (_, a, _, _) in plans if a == 'SKIP_NOMATCH')}") if with_residual: print(f"!! Din care {with_residual} raman NEFACTURATE in ROA dupa legare " f"(reziduu de linie — vezi ATENTIE mai sus; necesita corectie manuala a liniilor).") if not args.apply: print("\n[DRY-RUN] nimic modificat. Reruleaza cu --apply ca sa aplici.") return if not to_link: print("\nNimic de legat.") return if not args.yes: resp = input(f"\nAplici {len(to_link)} legaturi? [y/N] ").strip().lower() if resp != "y": print("Anulat.") return linked = 0 for inv, order in to_link: if apply_link(ora_cur, db, inv, order): linked += 1 print(f" OK IDV={inv['id_vanzare']} -> idcom {order['id_comanda']} " f"({order['order_number']})") else: print(f" SKIP IDV={inv['id_vanzare']} — ID_COMANDA nu mai era NULL (concurenta)") conn.commit() db.commit() print(f"\nAplicat: {linked} facturi legate + cache SQLite actualizat.") # Verifica reziduul REAL dupa legare. Daca > 0, ROA arata comanda tot nefacturata # desi headerul e legat (app dashboard o vede facturata). Liniile trebuie corectate # manual in ROA — scriptul nu atinge niciodata COMENZI_ELEMENTE. still = [] for inv, order in to_link: res = order_line_residual(ora_cur, order["id_comanda"]) if res: still.append((order, res)) if still: print(f"\n!! ATENTIE — {len(still)} comenzi legate dar cu reziduu de linie in ROA " f"(raman NEFACTURATE pana corectezi liniile manual in ROA):") for order, res in still: print(f" {order['order_number']} (idcom {order['id_comanda']}): {len(res)} linii — " + ", ".join(f"ART={r['id_articol']}@{r['pret']}" for r in res)) conn.close() db.close() if __name__ == "__main__": main()