gigafibre-fsm/scripts/migration/explore_expro_payments.py

"""Explore legacy payments for Expro Transit (account 3673) vs ERPNext."""
import frappe
import pymysql
import os
from datetime import datetime

os.chdir("/home/frappe/frappe-bench/sites")
frappe.init(site="erp.gigafibre.ca", sites_path=".")
frappe.connect()
print("Connected:", frappe.local.site)

conn = pymysql.connect(
    host="legacy-db",
    user="facturation",
    password="*******",
    database="gestionclient",
    cursorclass=pymysql.cursors.DictCursor
)

ACCOUNT_ID = 3673
CUSTOMER = "CUST-cbf03814b9"

with conn.cursor() as cur:
    # Get all payments for this account
    cur.execute("""
        SELECT p.id, p.date_orig, p.amount, p.applied_amt, p.type, p.memo, p.reference, p.excedent, p.correction
        FROM payment p
        WHERE p.account_id = %s
        ORDER BY p.date_orig DESC
    """, (ACCOUNT_ID,))
    payments = cur.fetchall()

    print("\n=== ALL LEGACY PAYMENTS for account {} ===".format(ACCOUNT_ID))
    print("Total: {} payments".format(len(payments)))
    total_paid = 0
    for r in payments:
        dt = datetime.fromtimestamp(r["date_orig"]).strftime("%Y-%m-%d") if r["date_orig"] else "NULL"
        total_paid += float(r["amount"] or 0)
        print("  PE-{:<8} date={} amount={:>10.2f} applied={:>10.2f} type={:<12} ref={}".format(
            r["id"], dt, float(r["amount"] or 0), float(r["applied_amt"] or 0),
            r["type"] or "", r["reference"] or ""))
    print("  TOTAL PAID: {:,.2f}".format(total_paid))

    # Get all payment_items
    cur.execute("""
        SELECT pi.payment_id, pi.invoice_id, pi.amount, p.date_orig
        FROM payment_item pi
        JOIN payment p ON p.id = pi.payment_id
        WHERE p.account_id = %s
        ORDER BY p.date_orig DESC
    """, (ACCOUNT_ID,))
    items = cur.fetchall()

    print("\n=== PAYMENT-INVOICE ALLOCATIONS ===")
    print("Total allocations: {}".format(len(items)))
    for r in items[:30]:
        dt = datetime.fromtimestamp(r["date_orig"]).strftime("%Y-%m-%d") if r["date_orig"] else "NULL"
        print("  payment PE-{} -> SINV-{} amount={:.2f} date={}".format(
            r["payment_id"], r["invoice_id"], float(r["amount"] or 0), dt))
    if len(items) > 30:
        print("  ... ({} more)".format(len(items) - 30))

    # Get all invoices for this account
    cur.execute("""
        SELECT id, total_amt, billed_amt, billing_status, date_orig
        FROM invoice
        WHERE account_id = %s
        ORDER BY date_orig DESC
    """, (ACCOUNT_ID,))
    invoices = cur.fetchall()

    print("\n=== LEGACY INVOICES ===")
    print("Total: {} invoices".format(len(invoices)))
    total_invoiced = 0
    total_outstanding = 0
    for inv in invoices:
        total_amt = float(inv["total_amt"] or 0)
        billed_amt = float(inv["billed_amt"] or 0)
        total_invoiced += total_amt
        montant_du = max(total_amt - billed_amt, 0)
        total_outstanding += montant_du
    print("  Total invoiced:    {:,.2f}".format(total_invoiced))
    print("  Total paid:        {:,.2f}".format(total_paid))
    print("  Total outstanding: {:,.2f}".format(total_outstanding))

# Now check ERPNext state
print("\n=== ERPNEXT STATE ===")
erp_inv = frappe.db.sql("""
    SELECT COUNT(*) as cnt, COALESCE(SUM(grand_total), 0) as total,
           COALESCE(SUM(outstanding_amount), 0) as outstanding
    FROM "tabSales Invoice"
    WHERE customer = %s AND docstatus = 1 AND grand_total > 0
""", (CUSTOMER,), as_dict=True)[0]

erp_pe = frappe.db.sql("""
    SELECT COUNT(*) as cnt, COALESCE(SUM(paid_amount), 0) as total
    FROM "tabPayment Entry"
    WHERE party = %s AND docstatus = 1
""", (CUSTOMER,), as_dict=True)[0]

print("  Invoices: {} for {:,.2f}  (outstanding: {:,.2f})".format(
    erp_inv["cnt"], float(erp_inv["total"]), float(erp_inv["outstanding"])))
print("  Payments: {} for {:,.2f}".format(erp_pe["cnt"], float(erp_pe["total"])))

# Which PE ids exist in ERPNext?
erp_pes = frappe.db.sql("""
    SELECT name FROM "tabPayment Entry" WHERE party = %s AND docstatus = 1 ORDER BY name
""", (CUSTOMER,), as_dict=True)
erp_pe_ids = set()
for pe in erp_pes:
    try:
        erp_pe_ids.add(int(pe["name"].split("-")[1]))
    except:
        pass

legacy_pe_ids = set(r["id"] for r in payments)
missing = legacy_pe_ids - erp_pe_ids
existing = legacy_pe_ids & erp_pe_ids

print("\n  Legacy payment IDs: {}".format(len(legacy_pe_ids)))
print("  In ERPNext:         {}".format(len(existing)))
print("  MISSING:            {}".format(len(missing)))

# Summary of missing payments
missing_total = 0
for r in payments:
    if r["id"] in missing:
        missing_total += float(r["amount"] or 0)
print("  Missing total:      {:,.2f}".format(missing_total))

conn.close()