#!/usr/bin/env python3 """Import legacy payments (24 months) as Payment Entry + references to Sales Invoices.""" import pymysql import psycopg2 import uuid from datetime import datetime, timezone LEGACY = {"host": "legacy-db", "user": "facturation", "password": "VD67owoj", "database": "gestionclient", "connect_timeout": 30, "read_timeout": 600} PG = {"host": "db", "port": 5432, "user": "postgres", "password": "123", "dbname": "_eb65bdc0c4b1b2d6"} ADMIN = "Administrator" COMPANY = "TARGO" # Legacy type → ERPNext mode_of_payment MODE_MAP = { "ppa": "Bank Draft", "paiement direct": "Bank Draft", "carte credit": "Credit Card", "cheque": "Cheque", "comptant": "Cash", "reversement": "Bank Draft", "credit": "Credit Note", "credit targo": "Credit Note", "credit facture": "Credit Note", } def uid(p=""): return p + uuid.uuid4().hex[:10] def ts_to_dt(t): if not t or t <= 0: return None try: return datetime.fromtimestamp(int(t), tz=timezone.utc).strftime("%Y-%m-%d %H:%M:%S") except: return None def ts_to_date(t): if not t or t <= 0: return None try: return datetime.fromtimestamp(int(t), tz=timezone.utc).strftime("%Y-%m-%d") except: return None def log(msg): print(msg, flush=True) def main(): log("=== Import Payments (24 months) ===") mc = pymysql.connect(**LEGACY) cur = mc.cursor(pymysql.cursors.DictCursor) cutoff = int(datetime.now(timezone.utc).timestamp()) - (24 * 30 * 86400) cur.execute("SELECT * FROM payment WHERE date_orig >= %s AND type != 'credit' AND type != 'credit targo' AND type != 'credit facture' ORDER BY id", (cutoff,)) payments = cur.fetchall() log(" {} payments (excl credits)".format(len(payments))) # Payment items (links to invoices) pay_ids = [p["id"] for p in payments] items_by_pay = {} chunk = 10000 for s in range(0, len(pay_ids), chunk): batch = pay_ids[s:s+chunk] cur.execute("SELECT * FROM payment_item WHERE payment_id IN ({})".format(",".join(["%s"]*len(batch))), batch) for r in cur.fetchall(): items_by_pay.setdefault(r["payment_id"], []).append(r) mc.close() log(" {} payment-invoice links loaded".format(sum(len(v) for v in items_by_pay.values()))) pg = psycopg2.connect(**PG) pgc = pg.cursor() # Mappings pgc.execute('SELECT legacy_account_id, name FROM "tabCustomer" WHERE legacy_account_id > 0') cust_map = {r[0]: r[1] for r in pgc.fetchall()} pgc.execute('SELECT legacy_invoice_id, name FROM "tabSales Invoice" WHERE legacy_invoice_id > 0') inv_map = {r[0]: r[1] for r in pgc.fetchall()} pgc.execute("SELECT name FROM \"tabAccount\" WHERE account_type = 'Receivable' AND company = %s AND is_group = 0 LIMIT 1", (COMPANY,)) receivable = pgc.fetchone()[0] pgc.execute("SELECT name FROM \"tabAccount\" WHERE account_type = 'Bank' AND company = %s AND is_group = 0 LIMIT 1", (COMPANY,)) bank_row = pgc.fetchone() bank_acct = bank_row[0] if bank_row else "Banque - T" # Check existing pgc.execute("SELECT name FROM \"tabPayment Entry\" WHERE name LIKE 'PE-%'") existing = set(r[0] for r in pgc.fetchall()) # Add custom field for legacy payment id try: pgc.execute(""" INSERT INTO "tabCustom Field" (name, creation, modified, modified_by, owner, docstatus, idx, dt, label, fieldname, fieldtype, insert_after) VALUES (%s, %s, %s, %s, %s, 0, 0, 'Payment Entry', 'Legacy Payment ID', 'legacy_payment_id', 'Int', 'naming_series') """, (uid("CF-"), ts_to_dt(int(datetime.now(timezone.utc).timestamp())), ts_to_dt(int(datetime.now(timezone.utc).timestamp())), ADMIN, ADMIN)) pg.commit() log(" Created legacy_payment_id custom field") except: pg.rollback() log(" legacy_payment_id field already exists") # Check if column exists pgc.execute("""SELECT column_name FROM information_schema.columns WHERE table_name = 'tabPayment Entry' AND column_name = 'legacy_payment_id'""") has_legacy_col = pgc.fetchone() is not None pay_ok = pay_skip = pay_err = ref_ok = 0 now_ts = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S.%f") for i, p in enumerate(payments): pe_name = "PE-{}".format(uid()) if pe_name in existing: pay_skip += 1 continue cust_name = cust_map.get(p["account_id"]) if not cust_name: pay_err += 1 continue posting_date = ts_to_date(p["date_orig"]) or "2025-01-01" amount = round(abs(float(p["amount"] or 0)), 2) if amount <= 0: pay_skip += 1 continue mode = MODE_MAP.get(p.get("type", ""), "Bank Draft") ref = p.get("reference") or "" memo = p.get("memo") or "" try: cols = """name, creation, modified, modified_by, owner, docstatus, idx, naming_series, payment_type, posting_date, company, mode_of_payment, party_type, party, paid_from, paid_to, paid_amount, received_amount, base_paid_amount, base_received_amount, target_exchange_rate, source_exchange_rate, reference_no, reference_date, remarks, status""" vals = [pe_name, now_ts, now_ts, ADMIN, ADMIN, 0, 0, 'ACC-PAY-.YYYY.-', 'Receive', posting_date, COMPANY, mode, 'Customer', cust_name, bank_acct, receivable, amount, amount, amount, amount, 1, 1, ref[:140] if ref else None, posting_date, memo[:140] if memo else None, 'Draft'] if has_legacy_col: cols += ", legacy_payment_id" vals.append(p["id"]) placeholders = ",".join(["%s"] * len(vals)) pgc.execute('INSERT INTO "tabPayment Entry" ({}) VALUES ({})'.format(cols, placeholders), vals) # Payment Entry References (link to invoices) for j, pi in enumerate(items_by_pay.get(p["id"], [])): inv_name = inv_map.get(pi.get("invoice_id")) if not inv_name: continue alloc = round(abs(float(pi.get("amount") or 0)), 2) pgc.execute(""" INSERT INTO "tabPayment Entry Reference" ( name, creation, modified, modified_by, owner, docstatus, idx, reference_doctype, reference_name, allocated_amount, total_amount, outstanding_amount, exchange_rate, parent, parentfield, parenttype ) VALUES (%s, %s, %s, %s, %s, 0, %s, 'Sales Invoice', %s, %s, %s, %s, 1, %s, 'references', 'Payment Entry') """, (uid("PER-"), now_ts, now_ts, ADMIN, ADMIN, j+1, inv_name, alloc, alloc, alloc, pe_name)) ref_ok += 1 pay_ok += 1 except Exception as e: pay_err += 1 pg.rollback() if pay_err <= 10: log(" ERR pay#{} -> {}".format(p["id"], str(e)[:100])) continue if pay_ok % 2000 == 0: pg.commit() log(" [{}/{}] pay={} refs={} skip={} err={}".format( i+1, len(payments), pay_ok, ref_ok, pay_skip, pay_err)) pg.commit() # Fix creation dates log("") log("--- Fixing Payment Entry dates ---") mc = pymysql.connect(**LEGACY) cur = mc.cursor(pymysql.cursors.DictCursor) cur.execute("SELECT id, date_orig FROM payment WHERE date_orig >= %s ORDER BY id", (cutoff,)) pay_dates = {r["id"]: r["date_orig"] for r in cur.fetchall()} mc.close() if has_legacy_col: pgc.execute('SELECT name, legacy_payment_id FROM "tabPayment Entry" WHERE legacy_payment_id > 0') for name, pid in pgc.fetchall(): dt = ts_to_dt(pay_dates.get(pid)) if dt: pgc.execute('UPDATE "tabPayment Entry" SET creation = %s, modified = %s WHERE name = %s', (dt, dt, name)) pg.commit() log(" Dates fixed") pg.close() log("") log("=" * 60) log("Payments: {} created, {} skipped, {} errors".format(pay_ok, pay_skip, pay_err)) log("Invoice refs: {}".format(ref_ok)) log("=" * 60) if __name__ == "__main__": main()