"""LOB1 · 70 · muestra del cotejo (DEFINICIONES §7.2): 10 CAT-R por nombre, 10 MAD-R por NIF, 10 CAM-R por NIF (semilla 22) + 10 eventos de agenda.""" import sys; sys.path.insert(0, '/w/py'); from comun import * con = abrir(ro=True); x = con.execute x("SELECT setseed(0.22)") out = [] for R, via in (('CAT-R', 'nombre'), ('MAD-R', 'nif'), ('CAM-R', 'nif')): rows = x(f"""SELECT c.registro, c.via, c.nif, r.gid, r.nom, r.nom2, r.nif_decl, r.fecha, r.estado FROM casado c JOIN reg r USING (registro, gid) WHERE c.registro = '{R}' AND c.via = '{via}' ORDER BY md5(c.gid || c.nif || '22') LIMIT 10""").fetchall() for r in rows: nombres = x(f"""SELECT string_agg(adjudicatario_nombre, ' | ' ORDER BY filas DESC) FROM (SELECT adjudicatario_nombre, sum(filas) filas FROM '{I}adjudicatarios_filas.parquet' WHERE empresa_clave = '{r[2]}' GROUP BY 1 ORDER BY 2 DESC LIMIT 3)""").fetchone()[0] out.append(r + (nombres,)) import csv with open('/w/interno/70_cotejo_casados.tsv', 'w', newline='') as fh: w = csv.writer(fh, delimiter='\t'); w.writerow(['registro', 'via', 'nif', 'gid', 'nombre_registro', 'nombre2_registro', 'nif_declarado', 'fecha_inscripcion', 'estado', 'nombres_en_contratacion']) w.writerows(out) ev = x("""SELECT p.nif, p.dep, p.t0, (SELECT string_agg(DISTINCT o.organo_clave, ' | ') FROM dep_org o WHERE o.dep = p.dep) organos, (SELECT count(*) FROM cag_emp a WHERE a.nif = p.nif AND a.dep = p.dep) reuniones, (SELECT any_value(grup) FROM cag_emp a WHERE a.nif = p.nif AND a.dep = p.dep AND a.d = p.t0) grupo_agenda FROM (SELECT nif, dep, min(d) t0 FROM cag_emp WHERE nif IS NOT NULL AND dep IN (SELECT dep FROM dep_org) GROUP BY 1,2) p WHERE t0 BETWEEN DATE '2018-01-01' AND DATE '2024-12-31' ORDER BY md5(p.nif || p.dep || '22') LIMIT 10""").fetchall() with open('/w/interno/70_cotejo_eventos.tsv', 'w', newline='') as fh: w = csv.writer(fh, delimiter='\t'); w.writerow(['nif', 'departamento', 't0', 'organos_mapeados', 'reuniones', 'grupo_en_agenda']); w.writerows(ev) log(len(out), len(ev))