#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ sipiq_report_v1.1.py ==================== Verze: 1.1 Datum: 2026-06-17 Autor: Claude Code (pro MUDr. Vladimíra Buzalku) Změny proti v1.0 ---------------- - Contact Information SBALENA do kompaktního bloku (koordinátor jméno+e-mail, má koordinátora?, kdo vyjednává CDA, kdo vyplnil dotazník) místo ~44 řádků; PI je už v bloku IDENTITA. - Q54 (doporučení dalších center) sloučeno do JEDNOHO řádku (zřetězené neprázdné hodnoty) místo rozbitých „PI Name: - No Name - -" řádků. Stará v1.0 v TRASH. Popis ----- Přehledný Excel report SIPIQ: OTÁZKY ve sloupci A (pořadí + sekce), CENTRA ve sloupcích (B+). Zdroj MongoDB feasibility (sipiq_questions + sipiq_responses + answers_supplement). Yes=zelená, No=červená; doplněné odpovědi oranžová+kurzíva+komentář; freeze panes B3. Výstup: u:\\Dropbox\\!!!Days\\Downloads Z230\\SIPIQ_prehled_center_.xlsx Závislosti: pymongo, openpyxl (.venv). Mongo 192.168.1.76:27017, bez auth. """ import os from datetime import datetime from pymongo import MongoClient from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.comments import Comment from openpyxl.utils import get_column_letter MONGO_URI = "mongodb://192.168.1.76:27017" DB = "feasibility" OUT_DIR = r"u:\Dropbox\!!!Days\Downloads Z230" SKIP_SECTIONS = {"Confidentiality Statement"} COUNTRY_ABBR = {"Czech Republic": "CZ", "Slovakia": "SK"} # kompaktní Contact Information: (label, [answer keys], spojovník) COMPACT_CONTACT = [ ("Má centrum koordinátora?", ["Q7"], ""), ("Koordinátor (jméno)", ["Q8_2", "Q8_1"], " "), ("Koordinátor (e-mail)", ["Q8_8"], ""), ("CDA vyjednává", ["Q10"], ""), ("Dotazník vyplnil", ["Q12"], ""), ] C_SECTION = PatternFill("solid", fgColor="1F4E78") C_STEM = PatternFill("solid", fgColor="DDEBF7") C_IDENT = PatternFill("solid", fgColor="F2F2F2") C_HEAD = PatternFill("solid", fgColor="305496") C_YES = PatternFill("solid", fgColor="C6EFCE") C_NO = PatternFill("solid", fgColor="FFC7CE") C_SUPP = PatternFill("solid", fgColor="FFE699") THIN = Side(style="thin", color="BFBFBF") BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN) WRAP_TOP = Alignment(wrap_text=True, vertical="top") WRAP_CTR = Alignment(wrap_text=True, vertical="center", horizontal="center") def fmt_date(s): if not s: return "" try: return datetime.strptime(str(s)[:10], "%Y-%m-%d").strftime("%d%b%Y").upper() except Exception: return str(s)[:10] def main(): client = MongoClient(MONGO_URI, serverSelectionTimeoutMS=8000) db = client[DB] client.admin.command("ping") questions = list(db.sipiq_questions.find().sort("order", 1)) resp = list(db.sipiq_responses.find()) resp.sort(key=lambda r: (COUNTRY_ABBR.get(r.get("site_country"), "ZZ"), (r.get("pi_last_name") or "").lower())) n = len(resp) print(f"Otázek: {len(questions)} | center: {n}") wb = Workbook() ws = wb.active ws.title = "SIPIQ přehled" state = {"row": 1} # --- hlavička center --- h = ws.cell(1, 1, "OTÁZKA \\ CENTRUM") h.font = Font(bold=True, color="FFFFFF"); h.fill = C_HEAD; h.alignment = WRAP_CTR ws.cell(2, 1, "") ws.merge_cells("A1:A2") for j, r in enumerate(resp): cc = COUNTRY_ABBR.get(r.get("site_country"), "?") for rr, val in ((1, r.get("pi_last_name") or "?"), (2, f"{r.get('site_name') or ''} [{cc}]")): c = ws.cell(rr, 2 + j, val) c.font = Font(bold=True, color="FFFFFF", size=9); c.fill = C_HEAD c.alignment = WRAP_CTR; c.border = BORDER state["row"] = 3 def section_header(title): row = state["row"] sc = ws.cell(row, 1, f"■ {title.upper()}") sc.font = Font(bold=True, color="FFFFFF"); sc.fill = C_SECTION sc.alignment = WRAP_TOP; sc.border = BORDER for j in range(n): c = ws.cell(row, 2 + j); c.fill = C_SECTION; c.border = BORDER state["row"] += 1 def combo_row(label, keys, sep, fill=None): row = state["row"] a = ws.cell(row, 1, label) a.alignment = WRAP_TOP; a.border = BORDER if fill: a.fill = fill; a.font = Font(bold=True) for j, r in enumerate(resp): ans = r.get("answers") or {} supp = r.get("answers_supplement") or {} c = ws.cell(row, 2 + j); c.alignment = WRAP_TOP; c.border = BORDER if fill: c.fill = fill vals, supp_hit, supp_src = [], False, None for k in keys: if k in supp: vals.append((supp[k] or {}).get("value")); supp_hit = True supp_src = (supp[k] or {}).get("answer_source") or "doplněno" elif ans.get(k): vals.append(ans.get(k)) val = sep.join(x for x in vals if x) c.value = val or None if supp_hit: c.fill = C_SUPP; c.font = Font(italic=True) c.comment = Comment(f"Doplněno mimo SIPIQ: {supp_src}", "sipiq_report") elif val == "Yes": c.fill = C_YES elif val == "No": c.fill = C_NO state["row"] += 1 # --- IDENTITA --- section_header("Identita centra") for label, getter in [ ("Země", lambda r: COUNTRY_ABBR.get(r.get("site_country"), r.get("site_country") or "")), ("Město", lambda r: r.get("site_city") or ""), ("PI", lambda r: f"{r.get('pi_first_name') or ''} {r.get('pi_last_name') or ''}".strip()), ("E-mail PI", lambda r: r.get("pi_email") or ""), ("sdl_site_id", lambda r: r.get("sdl_site_id") or ""), ("Datum vyplnění", lambda r: fmt_date((r.get("meta") or {}).get("recorded_date"))), ]: row = state["row"] a = ws.cell(row, 1, label); a.font = Font(bold=True); a.fill = C_IDENT a.alignment = WRAP_TOP; a.border = BORDER for j, r in enumerate(resp): c = ws.cell(row, 2 + j, getter(r)); c.fill = C_IDENT c.alignment = WRAP_TOP; c.border = BORDER state["row"] += 1 # --- OTÁZKY po sekcích --- cur_section = None contact_done = False for q in questions: section = q.get("section") or "Other" if section in SKIP_SECTIONS: continue if section == "Contact Information": if contact_done: continue section_header("Kontakty (koordinátor / CDA / vyplnil)") for label, keys, sep in COMPACT_CONTACT: combo_row(label, keys, sep) contact_done = True cur_section = section continue if section != cur_section: section_header(section) cur_section = section base = q["_id"] # Q54 = doporučení dalších center -> jeden zřetězený řádek if base == "Q54": keys = [it["key"] for it in (q.get("items") or [])] or [base] combo_row("Q54 — Doporučená další centra/investigátoři", keys, "; ") continue items = q.get("items") or [] if not items: combo_row(f'{base} — {q.get("text") or ""}', [base], "") else: row = state["row"] sc = ws.cell(row, 1, f'{base} — {q.get("text") or ""}') sc.font = Font(bold=True); sc.fill = C_STEM sc.alignment = WRAP_TOP; sc.border = BORDER for j in range(n): c = ws.cell(row, 2 + j); c.fill = C_STEM; c.border = BORDER state["row"] += 1 for it in items: lbl = it.get("label") or "(odpověď)" combo_row(f" – {lbl}", [it["key"]], "") # --- formátování --- ws.freeze_panes = "B3" ws.column_dimensions["A"].width = 58 for j in range(n): ws.column_dimensions[get_column_letter(2 + j)].width = 20 ws.row_dimensions[1].height = 28 ws.row_dimensions[2].height = 40 os.makedirs(OUT_DIR, exist_ok=True) ts = datetime.now().strftime("%Y%m%d_%H%M") out = os.path.join(OUT_DIR, f"SIPIQ_prehled_center_{ts}.xlsx") wb.save(out) print(f"Uloženo: {out}") client.close() if __name__ == "__main__": main()