"""PG1 · 20 · base: cargos del extracto con clase y bn; eventos de salida/entrada por unidad (§4); ámbito de los órganos (§5); adjudicaciones de las sociedades con algún evento (todas las administraciones). Base temporal tmp/pg1.duckdb.""" import sys, os, json, csv; sys.path.insert(0, '/w/py') from comun import * os.makedirs('/w/tmp/duck', exist_ok=True) if os.path.exists('/w/tmp/pg1.duckdb'): os.remove('/w/tmp/pg1.duckdb') con = abrir(); x = con.execute I = '/d/unified_20261018.identidad/' R = {} # 1. unidades y hashes x("CREATE TABLE u AS SELECT * EXCLUDE (nombre, cad) FROM '/w/interno/10_unidades.parquet'") x("CREATE TABLE uh AS SELECT DISTINCT uid, unnest(hashes) h, TRUE prim FROM u UNION SELECT DISTINCT uid, unnest(hashes1) h, FALSE prim FROM u") x("DELETE FROM uh WHERE h IS NULL") # 2. cargos del extracto -> clase y bn (como INC1) x("""CREATE TABLE cg0 AS SELECT CAST(fecha_borme AS DATE) d, pdf_filename, CAST(num_entrada AS VARCHAR) num_entrada, empresa_norm, provincia, tipo_acto, cargo, persona_hash h, fuente, regexp_replace(upper(strip_accents(coalesce(cargo,''))), '[^A-Z0-9]', '', 'g') code FROM read_csv('/w/interno/10_cargos.csv', header=true, all_varchar=true)""") x("""CREATE TABLE ac AS SELECT CAST(fecha_borme AS DATE) d, pdf_filename, CAST(num_entrada AS VARCHAR) num_entrada, empresa_norm, empresa, domicilio, provincia FROM read_csv('/w/interno/10_actos.csv', header=true, all_varchar=true)""") x("CREATE TABLE emn AS SELECT empresa, clave_borme(empresa) bn FROM (SELECT DISTINCT empresa FROM ac WHERE empresa IS NOT NULL)") x("""CREATE TABLE cg AS SELECT c.*, coalesce(n.bn, clave_borme(c.empresa_norm)) bn, n.bn IS NULL bn_de_norm, {cl} clase FROM cg0 c LEFT JOIN (SELECT pdf_filename, num_entrada, empresa_norm, any_value(empresa) empresa FROM ac GROUP BY ALL) a USING (pdf_filename, num_entrada, empresa_norm) LEFT JOIN emn n ON n.empresa = a.empresa""".format(cl=CLASE_SQL)) R['cargos_extracto'] = x("SELECT count(*), count(DISTINCT h), count(*) FILTER (WHERE bn_de_norm), count(*) FILTER (WHERE clase IS NOT NULL) FROM cg").fetchone() x("CREATE TABLE hsoc AS SELECT h, count(DISTINCT bn) n_soc FROM cg WHERE clase IS NOT NULL GROUP BY 1") # 3. eventos (§4) P4 = "('administrador','consejero','directivo','apoderado')" x(f"""CREATE TABLE ev AS SELECT u.uid, 'salida' ev, uh.prim, c.bn, c.d, c.clase, c.tipo_acto, c.pdf_filename, c.num_entrada, c.h FROM u JOIN uh USING (uid) JOIN cg c ON c.h = uh.h WHERE u.tipo IN ('cese', 'oci') AND c.clase IN {P4} AND c.tipo_acto IN ('nombramiento', 'reeleccion') AND c.d > u.fecha AND c.d <= u.fecha + 730 AND c.bn IS NOT NULL AND c.bn <> '' UNION ALL SELECT u.uid, 'entrada', uh.prim, c.bn, c.d, c.clase, c.tipo_acto, c.pdf_filename, c.num_entrada, c.h FROM u JOIN uh USING (uid) JOIN cg c ON c.h = uh.h WHERE u.tipo = 'nombramiento' AND c.clase IN {P4} AND c.d >= u.fecha - 730 AND c.d < u.fecha AND c.bn IS NOT NULL AND c.bn <> '' UNION ALL SELECT u.uid, 'oci_cualquiera', uh.prim, c.bn, c.d, c.clase, c.tipo_acto, c.pdf_filename, c.num_entrada, c.h FROM u JOIN uh USING (uid) JOIN cg c ON c.h = uh.h WHERE u.tipo = 'oci' AND c.clase IN {P4} AND c.d > u.fecha - 30 AND c.d <= u.fecha + 730 AND c.bn IS NOT NULL AND c.bn <> ''""") R['eventos'] = x("SELECT ev, prim, tipo_acto, count(*), count(DISTINCT uid), count(DISTINCT bn) FROM ev GROUP BY ALL ORDER BY ALL").fetchall() # 4. sociedades casadas x("""CREATE TABLE soc AS SELECT s.empresa_clave emp, s.borme_norm bn FROM '/et/em3/salidas/unified_20261018.borme/empresas.parquet' s LEFT JOIN '/et/em5/salidas/17_etiquetas_INTERNO.parquet' e USING (empresa_clave) WHERE s.via IN ('v1_exacto','v2_historico') AND s.utilizable AND coalesce(e.etiqueta,'') <> 'R'""") x("CREATE TABLE socev AS SELECT DISTINCT s.emp, s.bn FROM soc s WHERE s.bn IN (SELECT DISTINCT bn FROM ev)") R['sociedades_evento'] = x("SELECT (SELECT count(DISTINCT bn) FROM ev), count(DISTINCT bn), count(DISTINCT emp) FROM socev").fetchone() # 5. ámbito de los órganos (§5) rows = [(r[1:] + [''] * 15)[:15] for i, r in enumerate(csv.reader(open('/id/dir3_12_Unidades_AGE__Unidades_AGE_V_T.csv', encoding='utf-8'))) if i > 0] x("CREATE TABLE d3 (cod VARCHAR, nom VARCHAR, nivel VARCHAR, tipo VARCHAR, nj VARCHAR, sup VARCHAR, supnom VARCHAR, pri VARCHAR, prinom VARCHAR, sw VARCHAR, edp VARCHAR, edpnom VARCHAR, estado VARCHAR, alta VARCHAR, nif VARCHAR)") con.executemany("INSERT INTO d3 VALUES (" + ",".join("?" * 15) + ")", rows) x("CREATE TABLE d3nif AS SELECT nif, any_value(prinom) prinom FROM d3 WHERE nif <> '' GROUP BY 1 HAVING count(DISTINCT pri) = 1") pn = [r[0] for r in x("SELECT DISTINCT prinom FROM d3").fetchall()] x("CREATE TABLE prc (prinom VARCHAR, carts VARCHAR[])"); con.executemany("INSERT INTO prc VALUES (?, ?)", [(p, carteras(p)) for p in pn]) x(f"""CREATE TABLE org AS SELECT f.organo_nif, f.organo_nombre, f.dir3, any_value(f.organo_clave) organo_clave, any_value(e.administracion) adm, any_value(coalesce(p1.carts, p2.carts)) carts FROM '{I}organos_filas.parquet' f LEFT JOIN '{I}entidades.parquet' e USING (entidad_clave) LEFT JOIN d3 ON d3.cod = coalesce(f.dir3, f.dir3_propagado) LEFT JOIN prc p1 ON p1.prinom = d3.prinom LEFT JOIN d3nif n ON n.nif = coalesce(f.organo_nif, f.organo_nif_inferido) LEFT JOIN prc p2 ON p2.prinom = n.prinom GROUP BY 1, 2, 3""") x("""CREATE TABLE org2 AS SELECT *, CASE WHEN carts IS NOT NULL AND len(carts) > 0 THEN carts WHEN adm = 'Gobierno de Aragón' THEN ['CCAA:Aragón'] WHEN adm = 'Generalitat de Catalunya' THEN ['CCAA:Cataluña'] ELSE [] END ambitos, CASE WHEN carts IS NOT NULL THEN 'AGE' WHEN adm = 'Administración General del Estado' THEN 'AGE_sin_cartera' ELSE coalesce(adm, 'otra') END bloque FROM org""") R['organos_por_bloque'] = x("SELECT bloque IN ('AGE','AGE_sin_cartera','Gobierno de Aragón','Generalitat de Catalunya'), bloque, count(*) FROM org2 GROUP BY ALL ORDER BY 3 DESC LIMIT 12").fetchall() # 6. adjudicaciones de las sociedades con evento (todas las administraciones) x("CREATE TEMP TABLE vinc AS SELECT source, id_canonical, bool_and(cuenta) cuenta FROM '/d/unified_20261018.vinculos/filas.parquet' GROUP BY 1, 2") x("""CREATE TEMP TABLE af AS SELECT adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, any_value(empresa_clave) empresa_clave, bool_or(coalesce(persona_fisica, FALSE)) pf FROM read_parquet('/d/unified_20261018.identidad/adjudicatarios_filas.parquet') GROUP BY ALL""") x("CREATE TEMP TABLE us AS SELECT DISTINCT ute_clave, socio_nif FROM read_parquet('/d/unified_20261018.identidad/ute_socios.parquet') WHERE socio_nif IS NOT NULL AND NOT coalesce(socio_persona_fisica, FALSE)") x("""CREATE TEMP TABLE af2 AS SELECT a.*, coalesce(us.socio_nif, a.empresa_clave) emp, us.ute_clave IS NOT NULL via_ute FROM af a LEFT JOIN us ON us.ute_clave = a.empresa_clave WHERE a.empresa_clave IS NOT NULL AND NOT a.pf AND coalesce(us.socio_nif, a.empresa_clave) IN (SELECT emp FROM socev)""") x("""CREATE TABLE ad0 AS SELECT r.source, r.id_canonical, r.organo_nif, r.organo_nombre, r.dir3, r.cpv_division cpv2, r.anio_ref anio, coalesce(r.es_menor, FALSE) OR coalesce(r.es_menor_dudoso, FALSE) menor, TRY_CAST(r.imp_adj_eur_comparable AS DOUBLE) imp, coalesce(TRY_CAST(r.fecha_adj AS DATE), TRY_CAST(r.fecha_formalizacion AS DATE), TRY_CAST(r.fecha_pub AS DATE)) f, a.emp, a.via_ute FROM read_parquet('/d/unified_20261018/*/*/*.parquet', hive_partitioning=1, union_by_name=1) r JOIN af2 a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM r.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM r.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM r.adjudicatario_nombre LEFT JOIN vinc v ON v.source = r.source AND v.id_canonical = r.id_canonical WHERE r.unidad = 'adjudicacion' AND r.copia_de IS NULL AND r.correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND coalesce(v.cuenta, TRUE) AND NOT coalesce(r.anulado_en_origen, FALSE)""") x("""CREATE TABLE adj AS SELECT d.source, d.id_canonical, d.emp, s.bn, any_value(o.ambitos) ambitos, any_value(o.bloque) bloque, any_value(o.organo_clave) organo_clave, min(d.f) f, max(d.imp) imp, bool_or(d.menor) menor, any_value(d.cpv2) cpv2, any_value(d.anio) anio, bool_or(d.via_ute) via_ute FROM ad0 d JOIN org2 o ON o.organo_nif IS NOT DISTINCT FROM d.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM d.organo_nombre AND o.dir3 IS NOT DISTINCT FROM d.dir3 JOIN socev s ON s.emp = d.emp GROUP BY 1, 2, 3, 4""") R['adjudicaciones'] = x("SELECT bloque IN ('AGE','AGE_sin_cartera','Gobierno de Aragón','Generalitat de Catalunya') b, bloque, count(*), count(*) FILTER (WHERE f IS NULL), count(DISTINCT bn), round(sum(imp)/1e6) FROM adj GROUP BY ALL ORDER BY 3 DESC LIMIT 12").fetchall() R['adj_por_anio'] = x("SELECT year(f), count(*) FROM adj GROUP BY 1 ORDER BY 1").fetchall() json.dump(R, open('/w/salidas/20_base.json', 'w'), indent=1, default=str, ensure_ascii=False); log(json.dumps(R, default=str, ensure_ascii=False)) x("CHECKPOINT"); log('fin')