93 lines
3.5 KiB
Python
Executable File
93 lines
3.5 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""Read-only audit for Odoo status consistency on ClientFlow opportunities.
|
|
|
|
Reports opportunities that are in advanced operational stages but lack a linked
|
|
Odoo sale order or a recently synced physical_status operation link.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
from datetime import datetime, timezone
|
|
from pathlib import Path
|
|
import json
|
|
|
|
from sqlalchemy import text
|
|
|
|
from app.db import engine
|
|
|
|
ADVANCED_STAGES = (
|
|
"ODOO_ORDER_CREATED", "IN_PRODUCTION", "ORDER_PREPARATION", "READY_TO_SHIP",
|
|
"INVOICED", "SHIPMENT_CREATED", "SHIPPED", "TRACKING_SENT", "DELIVERED", "WON",
|
|
)
|
|
|
|
|
|
def main() -> int:
|
|
with engine.begin() as conn:
|
|
rows = conn.execute(text("""
|
|
SELECT
|
|
o.id::text AS opportunity_id,
|
|
o.title,
|
|
o.customer_name,
|
|
o.customer_email,
|
|
o.status,
|
|
o.stage,
|
|
sale.external_name AS odoo_sale,
|
|
sale.status AS sale_status,
|
|
phys.status AS physical_status,
|
|
phys.last_synced_at AS physical_last_synced_at,
|
|
inv.external_name AS invoice_number
|
|
FROM opportunities o
|
|
LEFT JOIN operation_links sale
|
|
ON sale.opportunity_id = o.id
|
|
AND sale.system = 'odoo'
|
|
AND sale.external_type = 'sale_order'
|
|
LEFT JOIN operation_links phys
|
|
ON phys.opportunity_id = o.id
|
|
AND phys.system = 'odoo'
|
|
AND phys.external_type = 'physical_status'
|
|
LEFT JOIN operation_links inv
|
|
ON inv.opportunity_id = o.id
|
|
AND inv.system = 'jasmin'
|
|
AND inv.external_type = 'invoice'
|
|
WHERE COALESCE(o.status, '') IN ('open','won','closed','done','completed')
|
|
AND (
|
|
COALESCE(o.stage, '') = ANY(CAST(:advanced AS text[]))
|
|
OR inv.id IS NOT NULL
|
|
)
|
|
ORDER BY o.updated_at DESC
|
|
LIMIT 200
|
|
"""), {"advanced": list(ADVANCED_STAGES)}).mappings().all()
|
|
|
|
missing_sale = [dict(r) for r in rows if not r.get("odoo_sale")]
|
|
missing_physical = [dict(r) for r in rows if r.get("odoo_sale") and not r.get("physical_status")]
|
|
|
|
report = {
|
|
"generated_at": datetime.now(timezone.utc).isoformat(),
|
|
"checked": len(rows),
|
|
"missing_sale_order": missing_sale[:50],
|
|
"missing_physical_status": missing_physical[:50],
|
|
}
|
|
out_dir = Path("audit_reports")
|
|
out_dir.mkdir(exist_ok=True)
|
|
out_path = out_dir / "odoo_opportunity_sync_audit.json"
|
|
out_path.write_text(json.dumps(report, ensure_ascii=False, indent=2, default=str))
|
|
|
|
print("ClientFlow Odoo opportunity sync audit")
|
|
print("=" * 80)
|
|
print("checked:", len(rows))
|
|
print("missing_sale_order:", len(missing_sale))
|
|
print("missing_physical_status:", len(missing_physical))
|
|
print("JSON:", out_path)
|
|
if missing_sale:
|
|
print("\nExamples missing sale order:")
|
|
for r in missing_sale[:10]:
|
|
print(f"- {r['opportunity_id']} | {r.get('title')} | stage={r.get('stage')} | invoice={r.get('invoice_number')}")
|
|
if missing_physical:
|
|
print("\nExamples missing physical status:")
|
|
for r in missing_physical[:10]:
|
|
print(f"- {r['opportunity_id']} | {r.get('title')} | sale={r.get('odoo_sale')} | stage={r.get('stage')}")
|
|
return 1 if missing_sale or missing_physical else 0
|
|
|
|
|
|
if __name__ == "__main__":
|
|
raise SystemExit(main())
|