import pyodbc
import mysql.connector

# Connect to Contasol 0022026.accdb
db_path = r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\documentacion raul asencio\datos\contabilidad\0022026.accdb'
conn_str = f'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={db_path};'
acc_conn = pyodbc.connect(conn_str)
acc_cur = acc_conn.cursor()

# Connect to MySQL
my_conn = mysql.connector.connect(host='85.215.144.168', port=3306, user='asesoft', password='Pantera1', database='pastelerias')
my_cur = my_conn.cursor()

def clean_str(s):
    if not s:
        return ''
    return s.strip().replace('-', '').replace(' ', '').replace('\xa0', '').upper()

my_cur.execute("SELECT id, nif, nombre FROM ge_proveedores WHERE nif IS NOT NULL AND nif <> ''")
my_pros_by_nif = {clean_str(r[1]): (r[0], r[2]) for r in my_cur.fetchall()}

acc_cur.execute("SELECT CODPRO, CIFPRO, NOMPRO FROM F_PRO")
f_pro = acc_cur.fetchall()
matched_pro = 0
for cod, cif, nom in f_pro:
    c = clean_str(cif)
    if c in my_pros_by_nif:
        matched_pro += 1

print(f"0022026.accdb: F_PRO matched by NIF with ge_proveedores: {matched_pro} of {len(f_pro)}")

acc_cur.execute("SELECT CODCLI, CIFCLI, NOMCLI FROM F_CLI")
f_cli = acc_cur.fetchall()
my_cur.execute("SELECT id, nif, nombre_fiscal FROM ge_clientes WHERE nif IS NOT NULL AND nif <> ''")
my_clis_by_nif = {clean_str(r[1]): (r[0], r[2]) for r in my_cur.fetchall()}

matched_cli = 0
for cod, cif, nom in f_cli:
    c = clean_str(cif)
    if c in my_clis_by_nif:
        matched_cli += 1

print(f"0022026.accdb: F_CLI matched by NIF with ge_clientes: {matched_cli} of {len(f_cli)}")
