import json import re import shutil import sys import unicodedata import zipfile from datetime import datetime, date from pathlib import Path from xml.sax.saxutils import escape as xml_escape DEFAULT_TEMPLATE = "/scripts/EXPEDIENTE_FINANCIERO_FINAL.xlsx" SHEET_PORTADA = "xl/worksheets/sheet1.xml" # 01_Portada SHEET_EC = "xl/worksheets/sheet3.xml" # 03_EstadosCuenta SHEET_RAW = "xl/worksheets/sheet8.xml" # 08_EC_Raw TABLE_EC = "xl/tables/table1.xml" # Tabla_EC MESES_ES = ['ene', 'feb', 'mar', 'abr', 'may', 'jun', 'jul', 'ago', 'sep', 'oct', 'nov', 'dic'] # Comisiones por SPEI / transferencia. NO se descartan: solo se MARCAN (bandera # en la columna N de 08_EC_Raw) para la comparativa de # de movimientos. # OJO: 'CGO' y 'CARGO' NO son marcas de comision, son la palabra que muchos bancos # usan para el movimiento mismo. Con ellas dentro, HSBC marcaba 107 de 208 # movimientos como 'comision por SPEI' (son transferencias reales: 'CGO SPEI FACT # 2440' de $174,000, 'CGO SPEI FACT DTEFACT1466' de $1,832,928) y SANTANDER 12 mas # ('CARGO TRANSFERENCIA ENLACE' de $150,000). Una comision real dice COMISION/COM/ # CUOTA de forma explicita. La bandera es informativa (columna N), no mueve dinero. COMISION_RE = re.compile( r'(COMIS\w*|\bCOM\b\.?|CUOTA)[\s\S]{0,50}(SPEI|TRANSFER\w*|\bTRANSF\b|\bTEF\b)' r'|(SPEI|TRANSFER\w*|\bTRANSF\b|\bTEF\b)[\s\S]{0,50}(COMIS\w*|\bCOM\b\.?|CUOTA)', re.IGNORECASE) # Filas de saldos en 03_EstadosCuenta (V3.8.3: se insertaron 4 renglones en la # zona de conteos, por eso bajaron respecto a V3.8.2 que las tenia en 19 y 28). ROW_SALDO_INI = 23 ROW_SALDO_FIN = 32 ROW_SALDO_PROM = 33 # SALDO PROMEDIO (J33 ya es =AVERAGE(D33:I33) en la plantilla) # Seccion "VERIFICACION CONTRA TOTALES DEL BANCO" (V3.8.5, fila base 196) ROW_VERIF = 196 # B: titulo; B+1: banco(s); B+3 Depositos; B+4 Retiros; # B+5 saldo ini; B+6 saldo fin. Reportado en col D. # Columna de la bandera de comision en 08_EC_Raw (fuera de Tabla_EC, que es A:M) COL_COMISION = 'N' # Capacidad del desglose por persona: el array dinamico B33 (y su gemelo de # egresos) se derrama hacia abajo hasta toparse con la fila TOTAL. En la # plantilla V3.8.1 hay 74 filas libres (33..106). Si hay mas ordenantes que eso # Excel marca #DESBORDAMIENTO!, asi que se limita el numero de nombres DISTINTOS: # se conservan los mayores por monto y el resto se agrupa en "OTROS". DESGLOSE_CAPACIDAD = 74 EPOCH = date(1899, 12, 30) # ---------------------------------------------------------------- utilidades def parse_fecha(val): if not val: return None s = str(val).strip()[:10] try: return datetime.strptime(s, '%Y-%m-%d').date() except Exception: return None def parse_num(val): if val is None or val == '': return 0.0 if isinstance(val, (int, float)): return float(val) s = str(val).replace('$', '').replace(',', '').strip() neg = s.startswith('(') and s.endswith(')') s = s.strip('()') try: n = float(s) except (TypeError, ValueError): return 0.0 return -n if neg else n def serial(d): """Fecha -> numero de serie Excel.""" return (d - EPOCH).days def num_str(x): """Numero -> string sin notacion cientifica, max 2 decimales utiles.""" r = round(float(x), 2) if r == int(r): return str(int(r)) return ('%.2f' % r) def col_idx(letra): n = 0 for ch in letra: n = n * 26 + (ord(ch) - 64) return n def quitar_acentos(s): return ''.join(c for c in unicodedata.normalize('NFD', s) if unicodedata.category(c) != 'Mn') # ------------------------------------------------- edicion XML de worksheets def desharear_formulas(xml): """Convierte TODAS las formulas compartidas (t="shared") en explicitas. Excel guarda grupos de formulas donde solo la celda "maestra" tiene el texto y las demas son referencias (si="N"). Si la cirugia sobreescribe una celda del grupo, el rango compartido queda inconsistente y algunas versiones de Excel "reparan" el archivo borrando formulas en cascada. Expandirlas todas elimina el riesgo por completo. """ if ' (celda_origen, formula_cruda) # el cuerpo de nunca contiene '<' crudo (va escapado), por eso [^<]* masters = {} for m in re.finditer( r']*>]*ref=")[^>]*?si="(\d+)"[^>]*>([^<]*)', xml): masters[m.group(2)] = (m.group(1), xml_unescape(m.group(3))) # 2) reescribir cada celda con f compartida def reescribir(cm): celda = cm.group(0) ref = cm.group(1) fm = re.search(r']*?si="(\d+)"[^>]*?(?:/>|>([^<]*))', celda) if not fm: return celda si = fm.group(1) cuerpo = fm.group(2) if cuerpo: # maestra: quitar atributos shared nueva_f = '%s' % cuerpo else: # miembro: traducir desde la maestra if si not in masters: return celda # huerfana: no inventar nada origen, formula = masters[si] traducida = Translator('=' + formula, origin=origen).translate_formula(ref) nueva_f = '%s' % xml_escape(traducida[1:]) return celda[:fm.start()] + nueva_f + celda[fm.end():] return re.sub(r']*?(?:/>|>.*?)', reescribir, xml, flags=re.S) def build_cell(ref, s=None, num=None, formula=None, text=None): sa = ' s="%s"' % s if s else '' if text is not None: return ('%s' % (ref, sa, xml_escape(str(text)))) if formula is not None: return '%s' % (ref, sa, xml_escape(formula)) if num is not None: return '%s' % (ref, sa, num if isinstance(num, str) else num_str(num)) return '' % (ref, sa) def get_style(xml, ref): m = re.search(r']*?s="(\d+)"' % ref, xml) return m.group(1) if m else None def set_row_cells(xml, row, updates): """updates: dict {'D': cell_xml_completo, ...}. La fila DEBE existir.""" pat = re.compile(r']*\br="%d"[^>]*?)(/>|>(.*?))' % row, re.S) m = pat.search(xml) if not m: raise ValueError('Fila %d no existe en la hoja' % row) attrs = m.group(1) body = m.group(3) or '' # celdas existentes -> dict por letra de columna cells = {} for cm in re.finditer(r']*?(?:/>|>.*?)' % row, body, re.S): cells[cm.group(1)] = cm.group(0) cells.update(updates) nuevo_body = ''.join(cells[k] for k in sorted(cells.keys(), key=col_idx)) nuevo = '%s' % (attrs, nuevo_body) return xml[:m.start()] + nuevo + xml[m.end():] def replace_sheet_data_rows(xml, filas_xml, keep_row1=True): """Reemplaza todo el contenido de dejando la fila 1 intacta.""" m = re.search(r'(.*)', xml, re.S) if not m: raise ValueError('sheetData no encontrado') row1 = '' if keep_row1: r1 = re.search(r']*\br="1"[^>]*(?:/>|>.*?)', m.group(1), re.S) row1 = r1.group(0) if r1 else '' nuevo = '%s%s' % (row1, ''.join(filas_xml)) return xml[:m.start()] + nuevo + xml[m.end():] # --------------------------------------- extraccion de nombre del ordenante BANCOS_TOKENS = [ 'CITIBANAMEX', 'BANAMEX', 'BBVA', 'BANCOMER', 'SANTANDER', 'BANORTE', 'SCOTIABANK', 'BANREGIO', 'AFIRME', 'INBURSA', 'MULTIVA', 'BANBAJIO', 'BAJIO', 'AZTECA', 'BANCOPPEL', 'MIFEL', 'MONEX', 'ACTINVER', 'INTERCAM', 'COMPARTAMOS', 'BANSI', 'HSBC', 'ALBO', 'KLAR', 'NVIO', 'MERCADO PAGO', 'MERCADOPAGO', 'STP', ] STOPWORDS = { 'SPEI', 'RECIBIDO', 'RECIBIDOS', 'ENVIADO', 'ENVIADOS', 'TRANSFERENCIA', 'TRANSFERENCIAS', 'TRANSF', 'TEF', 'INTERBANCARIA', 'INTERBANCARIO', 'DEPOSITO', 'DEPOSITOS', 'ABONO', 'ABONOS', 'PAGO', 'PAGOS', 'CUENTA', 'TERCERO', 'TERCEROS', 'REF', 'REFERENCIA', 'CLAVE', 'RASTREO', 'CVE', 'FOLIO', 'CONCEPTO', 'ORDENANTE', 'BENEFICIARIO', 'BANCO', 'BCO', 'CTA', 'CLABE', 'RFC', 'HORA', 'HR', 'BNET', 'BMOV', 'CEP', 'NULL', 'EFECTIVO', 'PRACTIC', 'PRACTICAJA', 'CAJERO', 'SUCURSAL', 'VENTANILLA', 'TRASPASO', 'MISMO', 'TITULAR', 'NOMINA', 'DISPERSION', 'WEB', 'BANCA', 'MOVIL', 'APP', 'LINEA', 'POR', 'PARA', 'CON', 'SIN', 'DESDE', # ruido del disclaimer SPEI / Santander ("...DATO NO VERIFICADO POR ESTA # INSTITUCION...") y palabras de relleno que no son nombre 'DATO', 'DATOS', 'VERIFICADO', 'VERIFICADA', 'INSTITUCION', 'ESTA', 'ESTE', 'AUT', 'AUTORIZACION', 'PUNTO', 'VENTA', 'TERMINAL', 'AFIL', 'COBRO', 'CARGO', 'CARGOS', 'IVA', 'COMISION', 'COMISIONES', 'MEMBRESIA', # disclaimer GAT de BBVA al pie ("...RENDIMIENTO QUE SE OBTENDRIA DESPUES DE # DESCONTAR LA INFLACION ESTIMADA... en adelante") -> nunca es nombre 'OBTENDRIA', 'OBTENDRA', 'INFLACION', 'RENDIMIENTO', 'ESTIMADO', 'ESTIMADA', 'ADELANTE', 'NOMINAL', 'DESCONTAR', 'IMPUESTOS', 'ANTES', 'DESPUES', 'GAT', 'FACT', 'PRAC', 'FOLIO', 'CIUDAD', 'TARJETA', 'CREDITO', 'DEBITO', 'AUTOMATICO', 'RECIBO', 'PREST', 'PRESTAMO', 'DISPOSICION', } PERMITIDAS_CORTAS = {'Y', 'DE', 'LA', 'EL', 'SA', 'CV', 'RL', 'SC', 'AC', 'S'} # --- Movimientos SIN contraparte: se resumen por su NATURALEZA, no por nombre --- # El usuario pide que todo lo que aluda a efectivo diga 'EFECTIVO' (antes caia en # 'SIN IDENTIFICAR' porque EFECTIVO/DEPOSITO/CAJERO estan en STOPWORDS: en # SANTANDER eran 63 movimientos, entre ellos 'DEPOSITO EN EFECTIVO' x36). EFECTIVO_RE = re.compile( r'RETIRO\s+SIN\s+TARJETA|SIN\s+TARJETA|EN\s+EFECTIVO|DE\s+EFECTIVO|' r'\bEFECTIVO\b|DISPOSICION\s+(?:DE\s+)?EFECTIVO|RETIRO\s+CAJERO|' r'\bCAJERO\b|\bATM\b|\bPRACTICAJA\b|PRACTIC\b|\bVENTANILLA\b|' r'RETIRO\s+EN\s+SUCURSAL|DEP[O0]SITO\s+SUCURSAL', re.I) # Otros movimientos que tampoco tienen contraparte (son del propio banco/SAT). # El orden importa: se evalua de arriba hacia abajo. NATURALEZA = [ ('EFECTIVO', EFECTIVO_RE), ('ISR', re.compile(r'\bI\s*S\s*R\b|IMPUESTO\s+SOBRE\s+LA\s+RENTA|RETENCION\s+ISR', re.I)), ('IVA', re.compile(r'\bI\s*V\s*A\b|\bVV\s+AA\b', re.I)), ('COMISIONES', re.compile(r'COMISION|MEMBRESIA|ANUALIDAD|MANEJO\s+DE\s+CUENTA', re.I)), ('INTERESES', re.compile(r'\bINTERES(?:ES)?\b|RENDIMIENTO(?:S)?\s+PAGADO', re.I)), ('TRASPASO PROPIO', re.compile(r'MISMO\s+TITULAR|TRASPASO\s+ENTRE\s+CUENTAS|CUENTA\s+PROPIA', re.I)), ] # OJO: aqui NO van 'PAGO DE SERVICIOS' ni 'DOMICILIACION'. Esos SI tienen # contraparte (CFE, TELMEX, la aseguradora...) y el usuario quiere saber DE QUIEN # fue el movimiento: colapsarlos en una etiqueta generica escondia 128 y 63 # movimientos de SANTANDER bajo un mismo nombre. def _naturaleza(raw): """Etiqueta corta para movimientos sin contraparte. '' si no aplica.""" for etiqueta, rx in NATURALEZA: if rx.search(raw): return etiqueta return '' def _ultimo_recurso(raw): """Etiqueta para movimientos donde el banco NO da contraparte. Se aplica solo DESPUES de que fallaron todas las reglas de nombre, para que un nombre real siempre gane. Casos medidos en BBVA (60 de 686 caian en 'SIN IDENTIFICAR').""" # Pago de impuestos: el SAT es la contraparte, pero 'S.A.T.' se pierde al # quitar la puntuacion (queda 'S A T', tres tokens de 1 letra). if re.search(r'\bS\.A\.T\.?|\bSAT\b|SERVICIO\s+DE\s+ADMINISTRACION\s+TRIBUTARIA', raw): return 'SAT' # Pago a la tarjeta de credito del propio cliente: no hay tercero. (22 casos) if re.search(r'PAGO\s+TARJETA\s+DE\s+CREDITO|PAGO\s+A\s+TARJETA', raw): return 'TARJETA DE CREDITO' # Cobro automatico de un prestamo: el numero de recibo identifica el credito, # asi que se conserva (se repite mes con mes y agrupa solo). m = re.search(r'RECIBO\s+PREST\.?\s*(\d{6,})', raw) if m: return 'PRESTAMO ' + m.group(1) m = re.search(r'\bPREST\w*\s+(\d{6,})', raw) if m: return 'PRESTAMO ' + m.group(1) # BBVA 'PAGO CUENTA DE TERCERO BNET pago': el ordenante no escribio # nombre. La referencia BNET es del movimiento, no del tercero, asi que usarla # crearia un nombre distinto por operacion; se agrupan en una sola etiqueta. if re.search(r'CUENTA\s+DE\s+TERCERO', raw): return 'TERCERO SIN NOMBRE' # Si al menos se sabe el banco contraparte, decirlo es mejor que no decir nada. for b in BANCOS_TOKENS: if b in raw: return b return '' def _resumen_crudo(raw): """Ultimo recurso: resume el concepto en algo LEGIBLE en vez de devolver 'SIN IDENTIFICAR'. Quita bancos, digitos y puntuacion, tira las stopwords y se queda con las palabras con sustancia. Solo devuelve '' si de verdad no quedo nada (concepto vacio o puros numeros).""" s = raw for b in BANCOS_TOKENS: s = s.replace(b, ' ') s = re.sub(r'[^A-ZÑ&\s]', ' ', s) toks, vistos = [], set() for t in s.split(): if len(t) < 3 or t in STOPWORDS or t in vistos: continue vistos.add(t) toks.append(t) return ' '.join(toks[:6])[:60].strip() def _con_sustancia(r): return any(len(t) >= 3 and t not in PERMITIDAS_CORTAS for t in r) def _primer_nombre(text): """Toma la PRIMERA corrida de palabras-nombre del texto (se detiene en el primer token invalido: stopword, conector suelto o token con digito). Sirve para 'DEL CLIENTE CLAVE...' o 'BNET 123 ...'.""" s = quitar_acentos(str(text or '').upper()) for b in BANCOS_TOKENS: s = s.replace(b, ' ') s = re.sub(r'[^A-ZÑ&\s]', ' ', s) # quita digitos y puntuacion run = [] for tok in s.split(): valido = (len(tok) >= 3 and tok not in STOPWORDS) or tok in PERMITIDAS_CORTAS if valido: run.append(tok) elif run: break while run and run[0] in PERMITIDAS_CORTAS: run.pop(0) while run and run[-1] in PERMITIDAS_CORTAS: run.pop() if run and _con_sustancia(run): return ' '.join(run[:6])[:60].strip() return '' def extraer_nombre(concepto, tipo=''): """Nombre de la persona/empresa contraparte del movimiento. Orden de prioridad: 1) Patron explicito de contraparte SPEI (Santander/Banamex): 'DEL CLIENTE X' (deposito), 'AL CLIENTE X' / 'A FAVOR DE X' (retiro). 2) BBVA: texto libre del ordenante tras 'BNET '. 3) Heuristica: ultima corrida de palabras-nombre del concepto. """ raw = quitar_acentos(str(concepto or '').upper()) # 0) Movimientos SIN contraparte (efectivo, comisiones, IVA/ISR, intereses, # servicios): se resumen por su NATURALEZA. Va PRIMERO porque un # 'RETIRO SIN TARJETA' o un 'DEPOSITO EN EFECTIVO' no tienen de quien # sacar un nombre y antes terminaban en 'SIN IDENTIFICAR'. nat = _naturaleza(raw) if nat: return nat # 1) contraparte explicita por NOMBRE (Santander/Banorte SPEI: 'DEL CLIENTE X', # 'AL CLIENTE X', 'A FAVOR DE X'). m = re.search(r'\b(?:DEL CLIENTE|AL CLIENTE|A FAVOR DE)\s+(.*)$', raw) if m: cand = _primer_nombre(m.group(1)) if cand: return cand # 2) Banorte orden de pago SPEI: 'BENEF: (DATO NO VERIF...)'. m = re.search(r'\bBENEF:?\s*(.+?)(?:\(DATO|CVE\s+RAST|\bRFC\b|$)', raw) if m: cand = _primer_nombre(m.group(1)) if cand: return cand # 3) Banorte traspaso a/desde tercero: el banco identifica al tercero por su # NUMERO DE CUENTA ('A LA CUENTA: ' / 'DE LA CUENTA: '); se # agrupa por esa cuenta (NO por el folio generico 'TERCEROS 0000000001'). m = re.search(r'\b(?:A LA CUENTA|DE LA CUENTA)\s*:?\s*(\d{6,})', raw) if m: return 'CUENTA ' + m.group(1) # 4) BBVA 'CUENTA DE TERCERO ': nombre si lo trae (2+ palabras), # si solo es un concepto ('pago de mercancia') se agrupa por el num. de cuenta. m_acc = re.search(r'\bTERCERO\s+(\d{6,})', raw) if m_acc: m2 = re.search(r'\bTERCERO\s+\d{6,}\s+(.*?)(?:\s+REF\b|$)', raw) cand = _primer_nombre(m2.group(1)) if m2 else '' if cand and len(cand.split()) >= 2: return cand return 'CUENTA ' + m_acc.group(1) # 5) BBVA 'BNET ' (lo que escribio quien ordena/recibe) m = re.search(r'\bBNET\s+\d+\s+(.*)$', raw) if m: cand = _primer_nombre(m.group(1)) if cand: return cand # 6) SPEI generico: el banco imprime primero las REFERENCIAS (clave de rastreo, # CLABE, folio: bloques largos de digitos) y AL FINAL el nombre de la # contraparte. Se toma el texto que sigue al ULTIMO bloque de 10+ digitos. # Ej: 'SPEI RECIBIDOMULTIVA BANC 0000121PAGO HONORARIOS LOMAS # 00132180000009445645 26021595130297 ARUAL MEDICINA DE REANIMACION' # -> 'ARUAL MEDICINA DE REANIMACION'. if 'SPEI' in raw or 'TRANSFERENCIA' in raw: cola = re.split(r'\d{10,}', raw)[-1] cand = _primer_nombre(cola) if cand and len(cand.split()) >= 2: return cand # 3) heuristica general: ultima corrida con 2+ palabras (si no, la mas larga) s = raw for b in BANCOS_TOKENS: s = s.replace(b, ' ') s = re.sub(r'[^A-Z0-9Ñ&\s]', ' ', s) tokens = [t for t in s.split() if not any(ch.isdigit() for ch in t)] runs, run = [], [] for tok in tokens: valido = (len(tok) >= 3 and tok not in STOPWORDS) or tok in PERMITIDAS_CORTAS if valido: run.append(tok) else: if run: runs.append(run) run = [] if run: runs.append(run) runs = [r for r in runs if _con_sustancia(r)] if not runs: # Antes se devolvia 'SIN IDENTIFICAR' aunque el concepto TUVIERA texto util # (el Sheet lo mostraba bien y la maqueta no). Ahora solo se rinde si de # verdad no quedo nada legible. return _resumen_crudo(raw) or _ultimo_recurso(raw) or 'SIN IDENTIFICAR' candidata = None for r in reversed(runs): if len(r) >= 2: candidata = r break if candidata is None: candidata = max(runs, key=len) candidata = candidata[-6:] return (' '.join(candidata)[:60].strip() or _resumen_crudo(raw) or _ultimo_recurso(raw) or 'SIN IDENTIFICAR') # ---------------------------------------------------------------- principal def fill(template_path, output_path, data): cliente = data.get('cliente', 'CLIENTE_SIN_NOMBRE') cliente_raw = data.get('cliente_raw', cliente) rfc = str(data.get('rfc', '') or '').strip() transacciones = data.get('transacciones', []) saldos_in = data.get('saldos', []) or [] if not isinstance(transacciones, list): raise ValueError("'transacciones' debe ser una lista") src = Path(template_path) if not src.exists(): raise FileNotFoundError('Template no existe: %s' % template_path) shutil.copy(str(src), output_path) # ===== 1) Normalizar transacciones (SIN FILTRAR: se conservan todas) ===== # Cada movimiento del estado de cuenta se conserva tal cual. Solo se MARCA # si es una comision por SPEI/transferencia (bandera 'es_comision') para la # comparativa de # de movimientos; la marca NO elimina nada. txs, comisiones = [], 0 for t in transacciones: f = parse_fecha(t.get('Fecha')) if not f: continue dep = parse_num(t.get('Deposito')) ret = parse_num(t.get('Retiro')) movimiento = max(abs(dep), abs(ret)) concepto = str(t.get('Concepto', '') or '').strip() es_com = bool(COMISION_RE.search(concepto)) if es_com: comisiones += 1 txs.append({ 'fecha': f, 'concepto': concepto, 'tipo': str(t.get('Tipo', '') or '').strip(), 'banco': str(t.get('Banco', '') or 'N/A').strip(), 'categoria': str(t.get('Categoria', '') or '').strip(), 'deposito': round(abs(dep), 2), 'retiro': round(abs(ret), 2), 'monto': round(parse_num(t.get('Monto')) or (dep - ret) or movimiento, 2), 'moneda': str(t.get('Moneda', '') or 'MXN').strip().upper() or 'MXN', 'monto_original': round(parse_num(t.get('MontoOriginal')) or movimiento, 2), 'es_comision': es_com, }) txs.sort(key=lambda x: x['fecha']) # nombre del ordenante/beneficiario limpio, una sola vez por transaccion for t in txs: t['nombre'] = extraer_nombre(t['concepto']) # ===== Mapas de nombres con TOPE de capacidad (evita #DESBORDAMIENTO!) ===== # Se construye un mapa nombre->nombre_a_mostrar para depositos y otro para # retiros: si hay mas nombres distintos que la capacidad del desglose, se # conservan los mayores por monto y el resto cae en una bolsa "OTROS". def _mapa_nombres(clave_monto, etiqueta_otros): tot = {} for t in txs: if t[clave_monto] > 0: tot[t['nombre']] = tot.get(t['nombre'], 0.0) + t[clave_monto] # --- Fusion de variantes del MISMO nombre ------------------------------- # El PDF corta la razon social en distinto punto segun cuanto texto la # precede, asi que el mismo cliente aparece como 'EXPORTACIONES TEXTIL' y # 'EXPORTACIONES TEXTILES MEXICANAS'. Se fusionan solo cuando la clave # canonica de uno es PREFIJO de la del otro y mide >= 12 caracteres: es # conservador a proposito (fusionar de mas atribuiria dinero a la # contraparte equivocada, que es peor que dejar dos filas separadas). canon = {n: re.sub(r'[^A-Z0-9]', '', quitar_acentos(n).upper()) for n in tot} rep = {} for n in sorted(tot, key=lambda x: (-len(canon[x]), x)): # del mas largo al mas corto cn = canon[n] destino = n if len(cn) >= 12: for m in rep: if canon[m].startswith(cn) or cn.startswith(canon[m]): destino = rep[m] break rep[n] = destino fusionado = {} for n, dest in rep.items(): fusionado[dest] = fusionado.get(dest, 0.0) + tot[n] if len(fusionado) <= DESGLOSE_CAPACIDAD: return {n: rep[n] for n in tot} principales = set(sorted(fusionado, key=lambda n: -fusionado[n])[:DESGLOSE_CAPACIDAD - 1]) return {n: (rep[n] if rep[n] in principales else etiqueta_otros) for n in tot} mapa_dep = _mapa_nombres('deposito', 'OTROS DEPOSITANTES') mapa_ret = _mapa_nombres('retiro', 'OTROS BENEFICIARIOS') # ===== 2) Saldos por (banco, mes): el ULTIMO registro gana ===== saldos = {} for srow in saldos_in: mes = str(srow.get('mes', '') or '').strip() if not re.match(r'^\d{4}-\d{2}$', mes): continue banco = str(srow.get('banco', '') or 'N/A').strip() si = srow.get('saldo_inicial') sf = srow.get('saldo_final') sp = srow.get('saldo_promedio') dr = srow.get('dep_reportado') rr = srow.get('ret_reportado') si = None if si in (None, '') else parse_num(si) sf = None if sf in (None, '') else parse_num(sf) sp = None if sp in (None, '') else parse_num(sp) dr = None if dr in (None, '') else parse_num(dr) rr = None if rr in (None, '') else parse_num(rr) fuente = str(srow.get('fuente', '') or '').strip() saldos[(banco, mes)] = (si, sf, sp, dr, rr, fuente) # ===== Saldos por MES CALENDARIO via SALDO CORRIDO ===== # El banco reporta el saldo por CORTE (p.ej. BBVA corta el dia 22, NO por mes # calendario). Pegar el saldo del corte a un mes calendario descuadra: el saldo # es del periodo 23->22 pero los ingresos/egresos se suman por mes calendario. # Por eso derivamos el saldo de cada mes calendario del SALDO CORRIDO: ancla = # saldo inicial del banco del estado MAS ANTIGUO, y se acumula (deposito-retiro) # en orden de fecha. Asi DESCUADRE = 0 por construccion, y el primer saldo # inicial y el ultimo saldo final amarran con lo que reporta el banco. # acc = [si, sf, has_si, has_sf, sp, has_sp] _periodos = sorted(saldos.keys(), key=lambda k: k[1]) bank_si = next((saldos[k][0] for k in _periodos if saldos[k][0] is not None), None) bank_sf = next((saldos[k][1] for k in reversed(_periodos) if saldos[k][1] is not None), None) sp_por_mes = {} for (banco, mes), tup in saldos.items(): if len(tup) > 2 and tup[2] is not None: sp_por_mes[mes] = sp_por_mes.get(mes, 0.0) + tup[2] # El saldo corrido asume UNA cuenta. Con varios bancos/cuentas mezclados no # aplica (el saldo no es un solo hilo) -> se cae al saldo del banco por mes. _un_solo_banco = len({k[0] for k in saldos}) <= 1 saldo_mes = {} if bank_si is not None and txs and _un_solo_banco: running = bank_si for t in txs: # txs ya esta ordenado por fecha mk = t['fecha'].strftime('%Y-%m') if mk not in saldo_mes: saldo_mes[mk] = [round(running, 2), round(running, 2), True, True, 0.0, False] running = round(running + t['deposito'] - t['retiro'], 2) saldo_mes[mk][1] = running for mk in saldo_mes: # saldo promedio del banco (informativo) if mk in sp_por_mes: saldo_mes[mk][4] = sp_por_mes[mk] saldo_mes[mk][5] = True else: # Fallback (sin ancla de saldo): saldos del banco mapeados por mes tal cual. for (banco, mes), tup in saldos.items(): si, sf, sp = tup[0], tup[1], tup[2] acc = saldo_mes.setdefault(mes, [0.0, 0.0, False, False, 0.0, False]) if si is not None: acc[0] += si acc[2] = True if sf is not None: acc[1] += sf acc[3] = True if sp is not None: acc[4] += sp acc[5] = True # ===== 3) Ventana de ultimos 6 meses ===== meses_data = {t['fecha'].strftime('%Y-%m') for t in txs} | set(saldo_mes.keys()) if meses_data: ultimo = max(meses_data) uy, um = int(ultimo[:4]), int(ultimo[5:7]) ventana = [] y, mth = uy, um for _ in range(6): ventana.append('%04d-%02d' % (y, mth)) mth -= 1 if mth == 0: mth, y = 12, y - 1 ventana.reverse() # [mas antiguo .. mas reciente] else: ventana = [] # ===== 4) Desglose de ingresos/egresos por persona ===== # En V3.8.1 lo calcula la PLANTILLA sola con formulas dinamicas que agrupan # por el nombre que escribimos en la columna Concepto de 08_EC_Raw. Aqui solo # contamos cuantos nombres distintos quedaran (tras el tope) para el reporte. ordenantes = set(mapa_dep.values()) beneficiarios = set(mapa_ret.values()) # ===== 5) Abrir el xlsx como zip y editar XML ===== zin = zipfile.ZipFile(output_path, 'r') contenido = {i.filename: zin.read(i.filename) for i in zin.infolist()} infos = {i.filename: i for i in zin.infolist()} zin.close() # ---- 5a) 08_EC_Raw: TODAS las transacciones (sin filtrar) ---- raw = contenido[SHEET_RAW].decode('utf-8') # estilos de referencia tomados de la propia plantilla s_fecha = get_style(raw, 'A2') or get_style(raw, 'A3') s_texto = get_style(raw, 'B2') s_dep = get_style(raw, 'F2') s_mon = get_style(raw, 'G2') filas_xml = [] for i, t in enumerate(txs, start=2): # Concepto que se escribe en la columna B = nombre limpio del # ordenante (depositos) o beneficiario (retiros), ya con tope de # capacidad aplicado. Asi los DOS desgloses de la plantilla (ingresos # por FILTER de Deposito>0, egresos por FILTER de Retiro>0) agrupan por # persona sin desbordarse. if t['deposito'] > 0: concepto_out = mapa_dep.get(t['nombre'], t['nombre']) else: concepto_out = mapa_ret.get(t['nombre'], t['nombre']) celdas = [ build_cell('A%d' % i, s=s_fecha, num=str(serial(t['fecha']))), build_cell('B%d' % i, s=s_texto, text=concepto_out[:250]), build_cell('C%d' % i, text=t['tipo']), build_cell('D%d' % i, text=t['banco']), build_cell('E%d' % i, text=t['categoria']), build_cell('F%d' % i, s=s_dep, num=t['deposito']), build_cell('G%d' % i, s=s_mon, num=t['retiro']), build_cell('H%d' % i, s=s_mon, num=t['monto']), build_cell('I%d' % i, text=MESES_ES[t['fecha'].month - 1]), build_cell('J%d' % i, num=str(t['fecha'].year)), build_cell('K%d' % i, text='%02d %d' % (t['fecha'].month, t['fecha'].year)), build_cell('L%d' % i, text=t['moneda']), build_cell('M%d' % i, num=t['monto_original']), # N = bandera de comision (1 si es comision SPEI/transferencia) para # la comparativa de # de movimientos. Fuera de Tabla_EC (A:M). build_cell('N%d' % i, num='1') if t['es_comision'] else '', ] filas_xml.append('%s' % (i, ''.join(celdas))) raw = replace_sheet_data_rows(raw, filas_xml, keep_row1=True) last_row = max(2, 1 + len(txs)) raw = re.sub(r'', '' % last_row, raw) contenido[SHEET_RAW] = raw.encode('utf-8') # ---- 5b) Tabla_EC: redimensionar al numero real de filas ---- tbl = contenido[TABLE_EC].decode('utf-8') tbl = re.sub(r'(]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl) tbl = re.sub(r'(]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl) contenido[TABLE_EC] = tbl.encode('utf-8') # ---- 5c) 03_EstadosCuenta ---- ec = contenido[SHEET_EC].decode('utf-8') # expandir formulas compartidas ANTES de sobreescribir celdas (evita # que Excel "repare" el archivo por rangos compartidos inconsistentes) ec = desharear_formulas(ec) # limites de escaneo de las formulas sobre 08_EC_Raw. La plantilla usa # rangos del tipo '08_EC_Raw'!$F$2:$F$1350 (y $1259 en el FILTER, $560 en # versiones viejas). Solo se amplian ESOS limites si hay mas transacciones; # NO se tocan referencias a celdas como $J$104 (fila TOTAL del desglose). # Patron seguro: ":$$" -> fin de un rango de una columna. def _ampliar_bound(m): col, b = m.group(1), int(m.group(2)) nuevo = max(b, last_row) return ':$%s$%d' % (col, nuevo) ec = re.sub(r':\$([A-Z]{1,3})\$(\d{3,6})\b', _ampliar_bound, ec) if ventana: # D16 = primer mes de la ventana (E16:I16 son EDATE y se actualizan solos) y0, m0 = int(ventana[0][:4]), int(ventana[0][5:7]) d16 = date(y0, m0, 1) ec = set_row_cells(ec, 16, { 'D': build_cell('D16', s=get_style(ec, 'D16'), num=str(serial(d16))), }) # SALDO INICIAL (fila ROW_SALDO_INI) / SALDO FINAL (ROW_SALDO_FIN): # valores REALES del banco. Se escriben TODAS las columnas D..I: con dato # real si existe, o vacias si ese mes no tiene estado de cuenta (asi no # queda el numero viejo de la plantilla ni la cascada). J = suma fila. ri, rf = ROW_SALDO_INI, ROW_SALDO_FIN upd19, upd28 = {}, {} for off, mes in enumerate(ventana): colL = chr(ord('D') + off) acc = saldo_mes.get(mes) s19 = get_style(ec, '%s%d' % (colL, ri)) or get_style(ec, '%s%d' % (colL, rf)) s28 = get_style(ec, '%s%d' % (colL, rf)) if acc and acc[2]: upd19[colL] = build_cell('%s%d' % (colL, ri), s=s19, num=round(acc[0], 2)) else: upd19[colL] = build_cell('%s%d' % (colL, ri), s=s19) # vacia if acc and acc[3]: upd28[colL] = build_cell('%s%d' % (colL, rf), s=s28, num=round(acc[1], 2)) else: upd28[colL] = build_cell('%s%d' % (colL, rf), s=s28) # vacia upd19['J'] = build_cell('J%d' % ri, s=get_style(ec, 'J%d' % ri) or get_style(ec, 'J%d' % rf), formula='SUM(D%d:I%d)' % (ri, ri)) upd28['J'] = build_cell('J%d' % rf, s=get_style(ec, 'J%d' % rf), formula='SUM(D%d:I%d)' % (rf, rf)) ec = set_row_cells(ec, ri, upd19) ec = set_row_cells(ec, rf, upd28) # SALDO PROMEDIO (fila ROW_SALDO_PROM): saldo promedio REAL de cada mes # (suma de cuentas del mes). J33 se deja como esta en la plantilla # (=AVERAGE(D33:I33)). Meses sin dato quedan vacios. rp = ROW_SALDO_PROM upd33 = {} for off, mes in enumerate(ventana): colL = chr(ord('D') + off) acc = saldo_mes.get(mes) sp_sty = get_style(ec, '%s%d' % (colL, rp)) or get_style(ec, '%s%d' % (colL, rf)) if acc and acc[5]: upd33[colL] = build_cell('%s%d' % (colL, rp), s=sp_sty, num=round(acc[4], 2)) else: upd33[colL] = build_cell('%s%d' % (colL, rp), s=sp_sty) # vacia ec = set_row_cells(ec, rp, upd33) # (Desglose por persona: lo genera la plantilla con su formula dinamica B33; # no se escribe nada en las filas 33+.) # ---- VERIFICACION CONTRA TOTALES DEL BANCO (fila ROW_VERIF, V3.8.5) ---- # Escribe los totales REALES que reporto el banco (depositos/retiros/saldos, # deterministas, de totales_banco.py). La plantilla calcula 'Extraido' con # SUM(Tabla_EC[...]) y la 'Diferencia'. Si difiere de 0 -> extraccion incompleta. rv = ROW_VERIF dep_rep_tot = sum(t[3] for t in saldos.values() if len(t) > 3 and t[3] is not None) ret_rep_tot = sum(t[4] for t in saldos.values() if len(t) > 4 and t[4] is not None) hay_rep = any((len(t) > 3 and t[3] is not None) or (len(t) > 4 and t[4] is not None) for t in saldos.values()) bancos = sorted({b for (b, m) in saldos.keys() if b and b not in ('N/A', '')}) # Saldo inicial/final que REPORTA el banco (independiente del saldo corrido): # el del estado mas antiguo y el del mas reciente. Sirve de referencia y para # cotejar contra los totales del banco. si_per = bank_si sf_per = bank_sf # Fuente de extraccion por mes (determinista=exacto / gemini=respaldo). Se # anexa a la linea de banco para que de un vistazo se vea si algun mes cayo a # Gemini (y conviene reprocesar tras desplegar el extractor). from collections import Counter as _Counter _fu = _Counter(t[5] for t in saldos.values() if len(t) > 5 and t[5]) _fu_txt = (' | Fuente: ' + ', '.join('%s (%d)' % (k, v) for k, v in sorted(_fu.items()))) if _fu else '' # Comisiones/Intereses marcados Tipo='COMISION' (Banorte: van APARTE de EGRESOS; # la plantilla ya los excluye de EGRESOS y los resta en DESCUADRE). Se anota su # total y conteo en la linea de banco como referencia (en una celda que existe). comi_tot = round(sum(t['retiro'] for t in txs if t['tipo'] == 'COMISION'), 2) comi_n = sum(1 for t in txs if t['tipo'] == 'COMISION') _comi_txt = (' | Comisiones/Intereses (aparte de egresos): $%s (%d mov)' % ('{:,.2f}'.format(comi_tot), comi_n)) if comi_tot > 0 else '' # Devoluciones (Banorte, Tipo='DEVOLUCION', monto NEGATIVO): no cuentan como # ingreso ni egreso (netean). Se anota su total (positivo) y conteo. dev_tot = round(-sum((t['retiro'] or 0) + (t['deposito'] or 0) for t in txs if t['tipo'] == 'DEVOLUCION'), 2) dev_n = sum(1 for t in txs if t['tipo'] == 'DEVOLUCION') if dev_n: _comi_txt += (' | Devoluciones (no cuentan): $%s (%d mov)' % ('{:,.2f}'.format(dev_tot), dev_n)) # Meses FUERA de la ventana de 6 columnas. Los movimientos si estan en # 08_EC_Raw, pero 03_EstadosCuenta solo muestra 6 meses: con bancos que cortan # a mitad de mes (BBVA corta el dia 22) SEIS estados producen SIETE meses # calendario, y el mas viejo queda fuera. Sin este aviso se lee como # "faltan movimientos" cuando en realidad es la ventana. _fuera = {} for t in txs: mk = t['fecha'].strftime('%Y-%m') if ventana and mk not in ventana: acc = _fuera.setdefault(mk, [0, 0.0, 0.0]) acc[0] += 1 acc[1] += t['deposito'] acc[2] += t['retiro'] meses_fuera = {k: {'movimientos': v[0], 'depositos': round(v[1], 2), 'retiros': round(v[2], 2)} for k, v in sorted(_fuera.items())} if meses_fuera: _nf = sum(v['movimientos'] for v in meses_fuera.values()) _df = sum(v['depositos'] for v in meses_fuera.values()) _rf = sum(v['retiros'] for v in meses_fuera.values()) _comi_txt += (' | FUERA DE LA VENTANA DE 6 MESES (%s): %d mov, ' 'depositos $%s, retiros $%s (si estan en 08_EC_Raw)' % (', '.join(meses_fuera), _nf, '{:,.2f}'.format(_df), '{:,.2f}'.format(_rf))) if bancos: ec = set_row_cells(ec, rv + 1, { 'C': build_cell('C%d' % (rv + 1), s=get_style(ec, 'C%d' % (rv + 1)), text=', '.join(bancos) + _fu_txt + _comi_txt)}) if hay_rep: ec = set_row_cells(ec, rv + 3, { 'D': build_cell('D%d' % (rv + 3), s=get_style(ec, 'D%d' % (rv + 3)), num=round(dep_rep_tot, 2))}) ec = set_row_cells(ec, rv + 4, { 'D': build_cell('D%d' % (rv + 4), s=get_style(ec, 'D%d' % (rv + 4)), num=round(ret_rep_tot, 2))}) if si_per is not None: ec = set_row_cells(ec, rv + 5, { 'D': build_cell('D%d' % (rv + 5), s=get_style(ec, 'D%d' % (rv + 5)), num=round(si_per, 2))}) if sf_per is not None: ec = set_row_cells(ec, rv + 6, { 'D': build_cell('D%d' % (rv + 6), s=get_style(ec, 'D%d' % (rv + 6)), num=round(sf_per, 2))}) contenido[SHEET_EC] = ec.encode('utf-8') # ---- 5d) 01_Portada (V3.8.1: C9 nombre, C11 RFC, C17 fecha de analisis) ---- por = contenido[SHEET_PORTADA].decode('utf-8') por = desharear_formulas(por) if cliente_raw: por = set_row_cells(por, 9, { 'C': build_cell('C9', s=get_style(por, 'C9'), text=str(cliente_raw).upper())}) if rfc: por = set_row_cells(por, 11, { 'C': build_cell('C11', s=get_style(por, 'C11'), text=rfc.upper())}) por = set_row_cells(por, 17, { 'C': build_cell('C17', s=get_style(por, 'C17'), num=str(serial(date.today())))}) contenido[SHEET_PORTADA] = por.encode('utf-8') # ---- 5e) recálculo completo al abrir + eliminar calcChain obsoleto ---- wbx = contenido['xl/workbook.xml'].decode('utf-8') if '', '') contenido['xl/workbook.xml'] = wbx.encode('utf-8') if 'xl/calcChain.xml' in contenido: del contenido['xl/calcChain.xml'] rels = contenido['xl/_rels/workbook.xml.rels'].decode('utf-8') rels = re.sub(r']*calcChain[^>]*/>', '', rels) contenido['xl/_rels/workbook.xml.rels'] = rels.encode('utf-8') ct = contenido['[Content_Types].xml'].decode('utf-8') ct = re.sub(r']*calcChain[^>]*/>', '', ct) contenido['[Content_Types].xml'] = ct.encode('utf-8') # ---- 5f) reempaquetar ---- with zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as zout: for nombre, datos in contenido.items(): zi = infos.get(nombre) if zi is not None: zi2 = zipfile.ZipInfo(nombre, date_time=zi.date_time) zi2.compress_type = zipfile.ZIP_DEFLATED zi2.external_attr = zi.external_attr zout.writestr(zi2, datos) else: zout.writestr(nombre, datos) return { 'output': output_path, 'rows_written': len(txs), 'comisiones_marcadas': comisiones, 'ventana_meses': ventana, 'meses_fuera_ventana': meses_fuera, # en 08_EC_Raw pero no en 03_EstadosCuenta 'meses_con_saldo': sorted(saldo_mes.keys()), 'ordenantes_detectados': len(ordenantes), 'beneficiarios_detectados': len(beneficiarios), 'cliente': cliente, 'portada_filled': { 'nombre': cliente_raw or None, 'rfc': rfc or None, 'fecha_analisis': date.today().strftime('%Y-%m-%d'), }, } def _hojas_por_nombre(contenido): """{nombre de hoja -> 'xl/worksheets/sheetN.xml'} resuelto por los rels. NO se puede asumir que la hoja N-esima del libro sea sheetN.xml: el orden de las pestanas y el numero de archivo son independientes (en la plantilla de microempresa '00_VisitaFisica' es sheet7.xml y '07_EC_Raw' es sheet8.xml). """ wbx = contenido['xl/workbook.xml'].decode('utf-8') relsx = contenido['xl/_rels/workbook.xml.rels'].decode('utf-8') rels = {} for m in re.finditer(r']*>', relsx): tag = m.group(0) rid = re.search(r'Id="([^"]+)"', tag) tgt = re.search(r'Target="([^"]+)"', tag) if rid and tgt: rels[rid.group(1)] = tgt.group(1).lstrip('/') hojas = {} for m in re.finditer(r']*>', wbx): tag = m.group(0) nom = re.search(r'name="([^"]+)"', tag) rid = re.search(r'r:id="([^"]+)"', tag) if nom and rid and rid.group(1) in rels: destino = rels[rid.group(1)] if not destino.startswith('xl/'): destino = 'xl/' + destino hojas[unescape_xml(nom.group(1))] = destino return hojas def unescape_xml(s): from xml.sax.saxutils import unescape return unescape(s, {'"': '"', ''': "'"}) # Columnas de 07_EC_Raw en la plantilla de microempresa. Ojo: Categoria(E), # Deposito(F) y Retiro(G) son COLUMNAS CALCULADAS de Tabla_EC (derivan de Tipo y # Monto), y Mes(I)/Anio(J)/MesAnio(K)/Saldo(M) tambien son formulas. Excel solo # las autorrellena al editar de forma interactiva, asi que al escribir el XML hay # que emitir la formula en CADA fila o esas columnas quedan vacias y toda la # maqueta lee ceros. MICRO_RAW_HOJA = '07_EC_Raw' MICRO_EC_HOJA = '04_EstadosCuenta' MICRO_PORTADA_HOJA = '01_Portada' MICRO_F = { # formulas por fila de 07_EC_Raw 'E': 'IF(Tabla_EC[[#This Row],[Tipo]]="Depósito","Ingreso","Egreso")', 'F': 'IF(Tabla_EC[[#This Row],[Categoria]]="Ingreso",Tabla_EC[[#This Row],[Monto]],0)', 'G': 'IF(Tabla_EC[[#This Row],[Categoria]]="Egreso",Tabla_EC[[#This Row],[Monto]],0)', 'I': 'IF(Tabla_EC[[#This Row],[Fecha]]="","",TEXT(Tabla_EC[[#This Row],[Fecha]], "mmm"))', 'J': 'IF(Tabla_EC[[#This Row],[Fecha]]="","",YEAR(Tabla_EC[[#This Row],[Fecha]]))', 'K': ('IF(Tabla_EC[[#This Row],[Fecha]]="","",TEXT(Tabla_EC[[#This Row],[Fecha]], "mm")' ' & " " & YEAR(Tabla_EC[[#This Row],[Fecha]]))'), } # Filas de 04_EstadosCuenta que se escriben (el resto son formulas SUMIFS/COUNTIFS # que leen 07_EC_Raw y se recalculan solas). MICRO_ROW_MES = 7 # C7 = primer mes de la ventana (D7..H7 son EDATE) MICRO_ROW_SALDO_INI = 8 # C8 = saldo inicial (D8..H8 = saldo final anterior) MICRO_ROW_SALDO_PROM = 12 # C12:H12 = saldo promedio del mes (vacias en la plantilla) MICRO_COLS = ['C', 'D', 'E', 'F', 'G', 'H'] def fill_microempresa(template_path, output_path, data): """Llena EXPEDIENTE_FINANCIERO_V1.5 MICROEMPRESA.xlsx. A diferencia de la maqueta V3 anterior, esta plantilla SI tiene hoja de datos crudos ('07_EC_Raw', con Tabla_EC A1:M) y su hoja 04_EstadosCuenta calcula todo con SUMIFS/COUNTIFS sobre ella. Asi que el llenado es el mismo concepto que la maqueta estandar: se vuelcan TODOS los movimientos y la maqueta se calcula sola. Solo se escriben ademas el primer mes de la ventana, el saldo inicial, el saldo promedio por mes y los datos del solicitante. """ cliente = data.get('cliente', 'CLIENTE_SIN_NOMBRE') cliente_raw = data.get('cliente_raw', cliente) rfc = str(data.get('rfc', '') or '').strip() transacciones = data.get('transacciones', []) or [] saldos_in = data.get('saldos', []) or [] if not isinstance(transacciones, list): raise ValueError("'transacciones' debe ser una lista") src = Path(template_path) if not src.exists(): raise FileNotFoundError('Template no existe: %s' % template_path) shutil.copy(str(src), output_path) # ===== 1) Normalizar (sin filtrar, igual que la maqueta estandar) ===== txs = [] for t in transacciones: f = parse_fecha(t.get('Fecha')) if not f: continue dep = abs(parse_num(t.get('Deposito'))) ret = abs(parse_num(t.get('Retiro'))) concepto = str(t.get('Concepto', '') or '').strip() tipo = str(t.get('Tipo', '') or '').strip() txs.append({ 'fecha': f, 'nombre': extraer_nombre(concepto), # La columna calculada Categoria compara Tipo="Depósito". Se normaliza # para que el acento y la forma coincidan siempre; los demas tipos # (COMISION, DEVOLUCION, SIN_IMPORTE) caen en "Egreso", que es correcto. 'tipo': 'Depósito' if (dep > 0 and ret == 0) else (tipo or 'Retiro'), 'banco': str(t.get('Banco', '') or 'N/A').strip(), 'monto': round(dep if dep > 0 else ret, 2), 'dep': round(dep, 2), 'ret': round(ret, 2), 'moneda': (str(t.get('Moneda', '') or 'MXN').strip().upper() or 'MXN'), }) txs.sort(key=lambda x: x['fecha']) # tope de nombres distintos, igual que en la maqueta estandar def _mapa(clave, etiqueta): tot = {} for t in txs: if t[clave] > 0: tot[t['nombre']] = tot.get(t['nombre'], 0.0) + t[clave] canon = {n: re.sub(r'[^A-Z0-9]', '', quitar_acentos(n).upper()) for n in tot} rep = {} for n in sorted(tot, key=lambda x: (-len(canon[x]), x)): destino = n if len(canon[n]) >= 12: for m in rep: if canon[m].startswith(canon[n]) or canon[n].startswith(canon[m]): destino = rep[m] break rep[n] = destino fus = {} for n, dst in rep.items(): fus[dst] = fus.get(dst, 0.0) + tot[n] if len(fus) <= DESGLOSE_CAPACIDAD: return {n: rep[n] for n in tot} top = set(sorted(fus, key=lambda n: -fus[n])[:DESGLOSE_CAPACIDAD - 1]) return {n: (rep[n] if rep[n] in top else etiqueta) for n in tot} mapa_dep = _mapa('dep', 'OTROS DEPOSITANTES') mapa_ret = _mapa('ret', 'OTROS BENEFICIARIOS') # ===== 2) Ventana de 6 meses y saldo inicial ancla ===== meses = sorted({t['fecha'].strftime('%Y-%m') for t in txs}) ventana = meses[-6:] fuera = meses[:-6] sal_por_mes = {} prom_por_mes = {} for s in saldos_in: m = str(s.get('mes', '')) if not re.match(r'^\d{4}-\d{2}$', m): continue if s.get('saldo_inicial') not in (None, ''): sal_por_mes[m] = parse_num(s.get('saldo_inicial')) if s.get('saldo_promedio') not in (None, ''): prom_por_mes[m] = parse_num(s.get('saldo_promedio')) ancla = None for m in ventana: if m in sal_por_mes: ancla = sal_por_mes[m] break # saldo promedio por mes: el del banco si lo reporta, si no el punto medio # del saldo corrido de ese mes. por_mes = {m: [0.0, 0.0] for m in ventana} for t in txs: m = t['fecha'].strftime('%Y-%m') if m in por_mes: por_mes[m][0] += t['dep'] por_mes[m][1] += t['ret'] corrido = ancla if ancla is not None else 0.0 prom_calc = {} for m in ventana: ini = corrido fin = round(ini + por_mes[m][0] - por_mes[m][1], 2) prom_calc[m] = round((ini + fin) / 2, 2) corrido = fin # ===== 3) Abrir el xlsx y resolver hojas POR NOMBRE ===== zin = zipfile.ZipFile(output_path, 'r') contenido = {i.filename: zin.read(i.filename) for i in zin.infolist()} infos = {i.filename: i for i in zin.infolist()} zin.close() hojas = _hojas_por_nombre(contenido) for req in (MICRO_RAW_HOJA, MICRO_EC_HOJA, MICRO_PORTADA_HOJA): if req not in hojas: raise ValueError('La plantilla no tiene la hoja %r (hojas: %s)' % (req, sorted(hojas))) # ===== 4) 07_EC_Raw: TODOS los movimientos ===== raw = contenido[hojas[MICRO_RAW_HOJA]].decode('utf-8') est = {c: get_style(raw, '%s2' % c) for c in 'ABCDEFGHIJKLM'} filas_xml = [] for i, t in enumerate(txs, start=2): nombre = (mapa_dep if t['dep'] > 0 else mapa_ret).get(t['nombre'], t['nombre']) celdas = [ build_cell('A%d' % i, s=est['A'], num=str(serial(t['fecha']))), build_cell('B%d' % i, s=est['B'], text=nombre[:250]), build_cell('C%d' % i, s=est['C'], text=t['tipo']), build_cell('D%d' % i, s=est['D'], text=t['banco']), build_cell('E%d' % i, s=est['E'], formula=MICRO_F['E']), build_cell('F%d' % i, s=est['F'], formula=MICRO_F['F']), build_cell('G%d' % i, s=est['G'], formula=MICRO_F['G']), build_cell('H%d' % i, s=est['H'], num=t['monto']), build_cell('I%d' % i, s=est['I'], formula=MICRO_F['I']), build_cell('J%d' % i, s=est['J'], formula=MICRO_F['J']), build_cell('K%d' % i, s=est['K'], formula=MICRO_F['K']), build_cell('L%d' % i, s=est['L'], text=t['moneda']), # Saldo corrido: la primera fila arranca del saldo inicial del banco. build_cell('M%d' % i, s=est['M'], formula=('%s+Tabla_EC[[#This Row],[Deposito]]' '-Tabla_EC[[#This Row],[Retiro]]' % (num_str(ancla) if (i == 2 and ancla is not None) else ('M%d' % (i - 1)) if i > 2 else '0'))), ] filas_xml.append('%s' % (i, ''.join(celdas))) raw = replace_sheet_data_rows(raw, filas_xml, keep_row1=True) last_row = max(2, 1 + len(txs)) raw = re.sub(r'', '' % last_row, raw) contenido[hojas[MICRO_RAW_HOJA]] = raw.encode('utf-8') # Tabla_EC al numero real de filas tbl = contenido[TABLE_EC].decode('utf-8') tbl = re.sub(r'(
]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl) tbl = re.sub(r'(]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl) contenido[TABLE_EC] = tbl.encode('utf-8') # ===== 5) 04_EstadosCuenta ===== ec = desharear_formulas(contenido[hojas[MICRO_EC_HOJA]].decode('utf-8')) # DEFECTO DE LA PLANTILLA: los SUMIFS de Depositos/Retiros/Efectivo cierran el # mes con '"<=" & EDATE(C$7,1)', o sea INCLUYEN el dia 1 del mes siguiente, asi # que todo movimiento fechado el dia 1 se suma en DOS meses. Los COUNTIFS de # al lado (# Depositos / # Retiros) ya usan '"<"', que es lo correcto. # Medido con 6 estados de BANAMEX: +$517,344.11 en depositos y +$192,332.71 en # retiros contra lo que reporta el banco. Se corrige aqui para que la maqueta # cuadre; conviene arreglarlo tambien en la plantilla de origen. n_fix = ec.count('"<=" & EDATE(') ec = ec.replace('"<=" & EDATE(', '"<" & EDATE(') # los SUMIFS/COUNTIFS escanean 07_EC_Raw hasta $2167: se amplia si hay mas filas def _ampliar(m): return ':$%s$%d' % (m.group(1), max(int(m.group(2)), last_row)) ec = re.sub(r':\$([A-Z]{1,3})\$(\d{3,6})\b', _ampliar, ec) if ventana: y0, m0 = int(ventana[0][:4]), int(ventana[0][5:7]) ec = set_row_cells(ec, MICRO_ROW_MES, { 'C': build_cell('C%d' % MICRO_ROW_MES, s=get_style(ec, 'C%d' % MICRO_ROW_MES), num=str(serial(date(y0, m0, 1))))}) if ancla is not None: ec = set_row_cells(ec, MICRO_ROW_SALDO_INI, { 'C': build_cell('C%d' % MICRO_ROW_SALDO_INI, s=get_style(ec, 'C%d' % MICRO_ROW_SALDO_INI), num=round(ancla, 2))}) for i, m in enumerate(ventana): col = MICRO_COLS[i] ec = set_row_cells(ec, MICRO_ROW_SALDO_PROM, { col: build_cell('%s%d' % (col, MICRO_ROW_SALDO_PROM), s=get_style(ec, '%s%d' % (col, MICRO_ROW_SALDO_PROM)), num=prom_por_mes.get(m, prom_calc[m]))}) for i in range(len(ventana), 6): # meses sin estado -> vacio col = MICRO_COLS[i] ec = set_row_cells(ec, MICRO_ROW_SALDO_PROM, { col: build_cell('%s%d' % (col, MICRO_ROW_SALDO_PROM), s=get_style(ec, '%s%d' % (col, MICRO_ROW_SALDO_PROM)))}) contenido[hojas[MICRO_EC_HOJA]] = ec.encode('utf-8') # ===== 6) 01_Portada: C9 nombre, C10 RFC, C14 fecha de analisis ===== por = desharear_formulas(contenido[hojas[MICRO_PORTADA_HOJA]].decode('utf-8')) por = set_row_cells(por, 9, {'C': build_cell('C9', s=get_style(por, 'C9'), text=str(cliente_raw).upper())}) # El RFC se escribe SIEMPRE (aunque venga vacio) para no dejar el de ejemplo. por = set_row_cells(por, 10, {'C': build_cell('C10', s=get_style(por, 'C10'), text=rfc.upper() if rfc else '')}) por = set_row_cells(por, 14, {'C': build_cell('C14', s=get_style(por, 'C14'), num=str(serial(date.today())))}) contenido[hojas[MICRO_PORTADA_HOJA]] = por.encode('utf-8') # ===== 7) recalculo al abrir ===== contenido.pop('xl/calcChain.xml', None) wbx = contenido['xl/workbook.xml'].decode('utf-8') if ']*/>', '', wbx) else: wbx = wbx.replace('', '') contenido['xl/workbook.xml'] = wbx.encode('utf-8') ct = contenido['[Content_Types].xml'].decode('utf-8') ct = re.sub(r']*calcChain[^>]*/>', '', ct) contenido['[Content_Types].xml'] = ct.encode('utf-8') zout = zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) for nombre, blob in contenido.items(): if nombre == 'xl/calcChain.xml': continue zi = infos.get(nombre) zout.writestr(zi if zi is not None else nombre, blob) zout.close() return { 'output': output_path, 'plantilla': 'microempresa', 'rows_written': len(txs), 'ventana_meses': ventana, 'meses_fuera_ventana': fuera, 'saldo_inicial_ancla': ancla, 'ordenantes_detectados': len(set(mapa_dep.values())), 'beneficiarios_detectados': len(set(mapa_ret.values())), 'sumifs_corregidos': n_fix, # '<=' -> '<' en el cierre de mes (ver nota) 'cliente': cliente, } def main(): if len(sys.argv) < 2: print(json.dumps({"error": "Uso: fill_template.py [template_path] [--microempresa]"})) sys.exit(1) argv = [a for a in sys.argv[1:] if a != '--microempresa'] micro = '--microempresa' in sys.argv output_path = argv[0] template_path = argv[1] if len(argv) > 1 else DEFAULT_TEMPLATE try: data = json.load(sys.stdin) except Exception as e: print(json.dumps({"error": "JSON invalido por stdin: %s" % e})) sys.exit(1) try: result = (fill_microempresa if micro else fill)(template_path, output_path, data) print(json.dumps(result, ensure_ascii=False)) except Exception as e: import traceback print(json.dumps({"error": str(e), "trace": traceback.format_exc()})) sys.exit(1) if __name__ == '__main__': main()