"""MOD-REC · paso 41: SEGUNDA IMPLEMENTACIÓN del hecho por órgano (pre-registro 1 §6 + enmienda 1), escrita desde el texto sin importar py/pob.py ni py/40_hecho.py: su propia lectura de la PLACSP (SQL propio), su propia primera adjudicación (de unified + .versiones), sus propios periodos (de salidas/10_cobertura.json) y su propio recuento en Python. Compara con priv/hecho_organo.csv: N, k y n por órgano × tribunal × acto. Imagen etl:v2, sin red. Salida: /w/salidas/41_hecho_impl2.json. """ import csv, json, glob, re, collections as C import duckdb U = '/d/unified_20261101' TRIB = {'AN': 'tarcja', 'CT': 'tccsp', 'AR': 'tacpa', 'CN': 'tacpc', 'CL': 'tarccyl'} PRIMERO = {'tarcja': 2011, 'tccsp': 2024, 'tacpa': 2023, 'tacpc': 2023, 'tarccyl': 2024} cob = json.load(open('/w/salidas/10_cobertura.json'))['cobertura'] def completa(tr, a, acto, g): if a < PRIMERO[tr] or (tr == 'tacpc' and acto == 'adj_excl'): # índice solo de pliegos return False v = cob.get('|'.join([tr, str(a), acto, g])) return v is None or v['completa_95'] per = {} for cc, tr in TRIB.items(): for acto in ('pliegos', 'adj_excl'): for t in (2022, 2023, 2024, 2025): if completa(tr, t, acto, 'E') and completa(tr, t + 1, acto, 'E'): per[(cc, acto, t)] = completa(tr, t, acto, 'D') and completa(tr, t + 1, acto, 'D') con = duckdb.connect() con.execute("SET memory_limit='4GB'; SET threads=3; SET temp_directory='/w/tmp/duck'; SET preserve_insertion_order=false") act = sorted(glob.glob(f'{U}/source=PLACSP/*/*.parquet')); ver = sorted(glob.glob(f'{U}.versiones/source=PLACSP/*/*.parquet')) lst = lambda fs: '[' + ', '.join("'" + f + "'" for f in fs) + ']' con.execute(f"""CREATE TABLE f AS SELECT licitacion_id, id_canonical, ccaa, organo_nif, organo_nombre, dir3, tipo_contrato, valor_estimado_eur_expediente, imp_lic_eur_expediente, imp_lic_eur, fecha_pub, rol_am, tramitacion FROM read_parquet({lst(act)}, union_by_name=true, hive_partitioning=true) WHERE source = 'PLACSP' AND vigente AND es_contrato AND NOT coalesce(es_menor, false) AND licitacion_id IS NOT NULL AND procedimiento IN ('Abierto', 'Restringido', 'Negociado_publicidad', 'Dialogo_competitivo', 'Asociacion_innovacion') AND tabla_origen IN ('resultados_licitaciones', 'resultados_agregacion', 'licitaciones_sin_resultado') AND unidad IN ('adjudicacion', 'desierto', 'licitacion:sin_resultado') AND coalesce(rol_am, '') NOT IN ('basado', 'basado_sda') AND coalesce(tramitacion, '') <> 'emergencia'""") # atributos: por columna, el primer valor no nulo en orden de id_canonical con.execute("""CREATE TABLE l AS SELECT licitacion_id, first(ccaa ORDER BY id_canonical) FILTER (WHERE ccaa IS NOT NULL) ccaa, first(organo_nif ORDER BY id_canonical) FILTER (WHERE organo_nif IS NOT NULL) organo_nif, first(organo_nombre ORDER BY id_canonical) FILTER (WHERE organo_nombre IS NOT NULL) organo_nombre, first(dir3 ORDER BY id_canonical) FILTER (WHERE dir3 IS NOT NULL) dir3, first(tipo_contrato ORDER BY id_canonical) FILTER (WHERE tipo_contrato IS NOT NULL) tipo_contrato, first(valor_estimado_eur_expediente ORDER BY id_canonical) FILTER (WHERE valor_estimado_eur_expediente IS NOT NULL) valor_estimado_eur_expediente, first(imp_lic_eur_expediente ORDER BY id_canonical) FILTER (WHERE imp_lic_eur_expediente IS NOT NULL) imp_lic_eur_expediente, first(imp_lic_eur ORDER BY id_canonical) FILTER (WHERE imp_lic_eur IS NOT NULL) imp_lic_eur, first(fecha_pub ORDER BY id_canonical) FILTER (WHERE fecha_pub IS NOT NULL) fecha_pub FROM f GROUP BY licitacion_id""") con.execute(f"""CREATE TABLE fa AS SELECT licitacion_id, min(fecha_adj) fa FROM read_parquet({lst(act + ver)}, union_by_name=true, hive_partitioning=true) WHERE source = 'PLACSP' AND unidad = 'adjudicacion' AND fecha_adj IS NOT NULL AND es_contrato AND NOT coalesce(es_menor, false) AND procedimiento IN ('Abierto', 'Restringido', 'Negociado_publicidad', 'Dialogo_competitivo', 'Asociacion_innovacion') AND licitacion_id IN (SELECT licitacion_id FROM l) GROUP BY 1""") con.execute(f"""CREATE TABLE fd AS SELECT licitacion_id, min(fecha_primera_version) fd FROM read_parquet('{U}.documentos/documentos.parquet') WHERE tipo_documento = 'resolucion_adjudicacion' AND licitacion_id IN (SELECT licitacion_id FROM l) GROUP BY 1""") ident = f'{U}.identidad' filas = con.execute(f"""SELECT l.licitacion_id, l.ccaa, coalesce(c.organo_clave, o.organo_clave) oc, t.tipo, l.tipo_contrato, coalesce(l.valor_estimado_eur_expediente, l.imp_lic_eur_expediente, l.imp_lic_eur) ve, year(l.fecha_pub) ap, year(CASE WHEN fd.fd IS NOT NULL AND fd.fd < fa.fa THEN fd.fd ELSE fa.fa END) aa FROM l LEFT JOIN read_parquet('{ident}/organos_filas.parquet') o ON o.organo_nif IS NOT DISTINCT FROM l.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM l.organo_nombre AND o.dir3 IS NOT DISTINCT FROM l.dir3 LEFT JOIN read_parquet('{ident}/organos_filas_ccaa.parquet') c ON c.organo_nif IS NOT DISTINCT FROM l.organo_nif AND c.organo_nombre IS NOT DISTINCT FROM l.organo_nombre AND c.dir3 IS NOT DISTINCT FROM l.dir3 AND c.ccaa = l.ccaa LEFT JOIN read_parquet('{ident}/organos_tipo.parquet') t ON t.organo_clave = coalesce(c.organo_clave, o.organo_clave) LEFT JOIN fa USING (licitacion_id) LEFT JOIN fd USING (licitacion_id) WHERE l.ccaa IN ('AN', 'CT', 'AR', 'CN', 'CL')""").fetchall() OBRAS = {'Obras', 'Concesion_obras', 'Concesion_servicios', 'Gestion_servicios_publicos'} res = C.defaultdict(lambda: C.Counter()) # licitacion_id → {(acto, 'E'|'ED'): n} for l in open('/w/priv/casado.jsonl'): x = json.loads(l) if x['estado'] != 'casada': continue acto = 'adj_excl' if x['acto'] in ('adjudicacion', 'exclusion') else 'pliegos' if x['grupo'] == 'E': res[x['licitacion_id']][(acto, x['tribunal'], 'E')] += 1 if x['grupo'] in ('E', 'D'): res[x['licitacion_id']][(acto, x['tribunal'], 'ED')] += 1 cuenta = C.defaultdict(lambda: {'N': 0, 'k': 0, 'n': 0, 'anios': set(), 'd_ok': True}) for lic, cc, oc, tipo, tc, ve, ap, aa in filas: if not oc or tipo == 'age': continue if tc in ('Servicios', 'Suministros'): rec = ve is not None and ve >= 100000 elif tc in OBRAS: rec = ve is not None and ve >= 3000000 else: rec = False if not rec: continue tr = TRIB[cc] for acto, anio in (('pliegos', ap), ('adj_excl', aa)): if anio is None or (cc, acto, anio) not in per: continue c = cuenta[(oc, tr, acto)] c['N'] += 1; c['anios'].add(anio); c['d_ok'] = c['d_ok'] and per[(cc, acto, anio)] c['k'] += int(res[lic][(acto, tr, 'E')] > 0); c['n'] += res[lic][(acto, tr, 'ED')] PF = re.compile(r'^(\d{8}[A-Z]|[XYZ]\d{7}[A-Z])$') PERSONA_ID = {r[0] for r in con.execute(f"SELECT organo_clave FROM read_parquet('{ident}/organos_tipo.parquet') WHERE nif_persona_fisica").fetchall()} uno = {} for r in csv.DictReader(open('/w/priv/hecho_organo.csv')): uno[(r['organo_clave'], r['tribunal'], r['acto'])] = (int(r['N']), int(r['k']), r['n_rec'] or None, int(r['desde']), int(r['hasta'])) todo_d = {} for (cc, acto, t), dok in per.items(): todo_d[(TRIB[cc], acto)] = todo_d.get((TRIB[cc], acto), True) and dok dos = {k: (v['N'], v['k'], str(v['n']) if todo_d[(k[1], k[2])] else None, min(v['anios']), max(v['anios'])) for k, v in cuenta.items() if not PF.match(str(k[0]).upper()) and k[0] not in PERSONA_ID} dif = [{'clave': list(k), 'impl1': uno.get(k), 'impl2': dos.get(k)} for k in sorted(set(uno) | set(dos)) if uno.get(k) != dos.get(k)] out = {'filas_impl1': len(uno), 'filas_impl2': len(dos), 'diferencias': len(dif), 'muestra_diferencias': dif[:20]} json.dump(out, open('/w/salidas/41_hecho_impl2.json', 'w'), indent=1, default=str) print(json.dumps({k: out[k] for k in ('filas_impl1', 'filas_impl2', 'diferencias')}), dif[:5])