"""MOD-EJEC · 55 · secundario declarado de la enmienda A del PREREG_2 (md5 fb55fcd8…, §3): TODOS los contratos del universo de RPC, Madrid y PSCP contra el crudo, con las reglas del comparador (py/51, v4). Por contrato: la suma de sus modificaciones con importe en el crudo, con dos filas iguales contando una vez, en dos lecturas (todo el crudo, la que manda; y vigente, solo para ver de dónde vienen las discrepancias). Tabla 2 × 2 frente a nuestra y. Sin umbral: no decide nada. -> salidas/55_universo.json, priv/sob_universo_dis.json (700)""" import duckdb, json, re c = duckdb.connect(); x = c.execute x("SET threads=3; SET memory_limit='1800MB'; SET preserve_insertion_order=false") R = '/d/raw_preserved/20261103'; D = '/d/unified_20261103' def num(v): if v is None: return None if isinstance(v, (int, float)): return float(v) t = str(v).strip().replace('€', '').strip() if not t: return None if ',' in t and '.' in t: t = t.replace('.', '').replace(',', '.') elif ',' in t: t = t.replace(',', '.') try: return float(t) except ValueError: return None def univ(nom, src): return x(f"""SELECT c.id, c.A, c.y, c.nmod, c.s_mod, u.fila_origen FROM (SELECT * FROM '/w/priv/sob_contratos.parquet' WHERE fuente = '{nom}') c JOIN (SELECT id_canonical, fila_origen FROM read_parquet('{D}/source={src}/**/*.parquet', hive_partitioning=1, union_by_name=1)) u ON u.id_canonical = c.id""").fetchall() SAL = {}; DIS = {} def cierra(nom, filas): t = {'todo_el_crudo': {'y1_crudo1': 0, 'y1_crudo0': 0, 'y0_crudo1': 0, 'y0_crudo0': 0}, 'vigente': {'y1_crudo1': 0, 'y1_crudo0': 0, 'y0_crudo1': 0, 'y0_crudo0': 0}} dis = {'todo_el_crudo': [], 'vigente': []} tipos = {'y0_crudo1_sin_nada_nuestro': 0, 'y0_crudo1_con_algo_nuestro': 0} for id_, A, y, nmod, s_mod, st, sv in filas: for reg, s in (('todo_el_crudo', st), ('vigente', sv)): cr = s - 0.2 * A > 0.01 t[reg][f"y{int(bool(y))}_crudo{int(cr)}"] += 1 if bool(y) != cr: dis[reg].append({'y': bool(y), 'A': A, 'nuestra_suma': s_mod, 'crudo_suma': s, 'nmod': nmod}) if reg == 'todo_el_crudo' and cr and not y: tipos['y0_crudo1_sin_nada_nuestro' if not nmod else 'y0_crudo1_con_algo_nuestro'] += 1 SAL[nom] = {'contratos': len(filas), **t, 'detalle_todo_el_crudo': tipos} DIS[nom] = {k: v[:200] for k, v in dis.items()} # ---- RPC f = f'{R}/catalunya/parquet/contratacion/contratos_registro.parquet' x(f"CREATE VIEW rpc AS SELECT *, file_row_number fr FROM read_parquet('{f}', file_row_number=true)") fuera = {"Variació del termini d'execució", "Suspensió del contracte", "Successió del contractista"} mods = {} for org, exp, lot, adj, im, fe, tp, ul in x("""SELECT DISTINCT "Identificador organisme contractant", "Codi de l’expedient", "Número de lot", "Adjudicatari", "Import de la modificació", "Data aprovació modificació", "Tipus de modificació", _en_ultima_descarga FROM rpc WHERE "Situació contractual" = 'modificació'""").fetchall(): mods.setdefault((org, exp, lot, adj), []).append((im, fe, tp, ul)) def lista_rpc(fs): v = {} for im, fe, tp, ul in fs: v.setdefault((im, fe), set()).add(tp) return sum(num(im) for (im, fe), tps in v.items() if not tps <= fuera and num(im) not in (None, 0.0)) U = univ('RPC', 'CATALUNYA'); clave = dict(((r[0]), tuple(r[1:])) for r in x("""SELECT fr, "Identificador organisme contractant", "Codi de l’expedient", "Número de lot", "Adjudicatari" FROM rpc WHERE fr IN (SELECT unnest(?))""", [[u[5] for u in U]]).fetchall()) filas = [] for id_, A, y, nmod, s_mod, fo in U: fs = mods.get(clave[fo], []) fu = [r for r in fs if r[3]] filas.append((id_, A, y, nmod, s_mod, lista_rpc(fs), lista_rpc(fu if fu else fs))) cierra('RPC', filas); print('RPC', SAL['RPC'], flush=True) del mods, clave # ---- Madrid fm = f'{R}/ayto_madrid/actividad_contractual_madrid_completo.parquet' x(f"CREATE VIEW ma AS SELECT *, file_row_number fr FROM read_parquet('{fm}', file_row_number=true)") mods = {} for reg, fe, im, lo, ul in x("""SELECT DISTINCT n_registro_contrato, fecha_formalizacion_incidencia, importe_modificacion, n_lote, _en_ultima_descarga FROM ma WHERE categoria = 'modificados'""").fetchall(): mods.setdefault(reg, []).append((fe, im, lo, ul)) U = univ('MADRID', 'MADRID_AYTO'); clave = dict((r[0], (r[1], r[2])) for r in x("SELECT fr, n_registro_contrato, n_lote FROM ma WHERE fr IN (SELECT unnest(?))", [[u[5] for u in U]]).fetchall()) def lista_ma(fs, lote): v = {(r[0], r[1], r[2]) for r in fs} return sum(num(r[1]) for r in v if (r[2] is None or str(r[2]) == str(lote)) and num(r[1]) not in (None, 0.0)) filas = [] for id_, A, y, nmod, s_mod, fo in U: reg, lote = clave[fo]; fs = mods.get(reg, []) if reg is not None else [] fu = [r for r in fs if r[3]] filas.append((id_, A, y, nmod, s_mod, lista_ma(fs, lote), lista_ma(fu if fu else fs, lote))) cierra('MADRID', filas); print('MADRID', SAL['MADRID'], flush=True) del mods, clave # ---- PSCP fp = f'{R}/catalunya/parquet/contratacion/publicaciones_pscp.parquet' x(f"CREATE VIEW pp AS SELECT codi_organ, codi_expedient, numero_lot, file_row_number fr FROM read_parquet('{fp}', file_row_number=true)") mods = {} for org, exp, da, im, lo, ul in x("""SELECT DISTINCT CAST("Codi òrgan" AS VARCHAR), trim("Codi expedient"), "Data", "Import sense iva", "Numero lot", _en_ultima_descarga FROM read_parquet('/h/catalunya/parquet/contratacion/fase_ejecucion.parquet') WHERE "Tipus actuació execució" IN ('Modificació (Objectiva)', 'Modificació')""").fetchall(): mods.setdefault((org, exp), []).append((da, im, lo, ul)) U = univ('PSCP', 'CATALUNYA_PSCP'); clave = {} frs = [u[5] for u in U] for i in range(0, len(frs), 50000): for r in x("SELECT fr, CAST(codi_organ AS VARCHAR), trim(codi_expedient), numero_lot FROM pp WHERE fr IN (SELECT unnest(?))", [frs[i:i+50000]]).fetchall(): clave[r[0]] = r[1:] def lista_pp(fs, lot_): v = {(r[0], r[1], r[2]) for r in fs} cl = [r for r in v if (None if str(r[2] or '').strip() in ('', '0') else str(r[2]).strip()) == lot_] us = cl if cl else v return sum(num(r[1]) for r in us if num(r[1]) not in (None, 0.0)) filas = [] for id_, A, y, nmod, s_mod, fo in U: org, exp, lot = clave[fo]; lot_ = None if str(lot or '').strip() in ('', '0') else str(lot).strip() fs = mods.get((org, exp), []) fu = [r for r in fs if r[3]] filas.append((id_, A, y, nmod, s_mod, lista_pp(fs, lot_), lista_pp(fu if fu else fs, lot_))) cierra('PSCP', filas); print('PSCP', SAL['PSCP'], flush=True) json.dump(SAL, open('/w/salidas/55_universo.json', 'w'), ensure_ascii=False, indent=1) json.dump(DIS, open('/w/priv/sob_universo_dis.json', 'w'), ensure_ascii=False, indent=1, default=str)