217 lines
7.7 KiB
Python
217 lines
7.7 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
"""
|
||
sipiq_report_v1.0.py
|
||
====================
|
||
Verze: 1.0
|
||
Datum: 2026-06-17
|
||
Autor: Claude Code (pro MUDr. Vladimíra Buzalku)
|
||
|
||
Popis
|
||
-----
|
||
Přehledný Excel report SIPIQ odpovědí: OTÁZKY ve sloupci A (v pořadí SIPIQ, seskupené
|
||
dle sekcí), CENTRA ve sloupcích (B+). Buňka = odpověď daného centra na danou otázku.
|
||
Tj. vyplněné SIPIQ všech center vedle sebe (= transponovaná rekonstrukce dotazníku).
|
||
|
||
Zdroj: MongoDB feasibility (sipiq_questions = slovník + pořadí + sekce + items;
|
||
sipiq_responses = odpovědi + answers_supplement z doplnujici_dotazy).
|
||
|
||
Layout:
|
||
- hlavička 2 řádky: příjmení PI + název centra (a země)
|
||
- blok IDENTITA (země/město/PI/e-mail/sdl_site_id/datum vyplnění)
|
||
- sekce SIPIQ jako barevné mezititulky, otázky a položky matic v řádcích
|
||
- Yes = zelená, No = červená; doplněné odpovědi (answers_supplement) = oranžová + kurzíva + komentář
|
||
- freeze panes (sloupec A + hlavička), ohraničení, vhodné šířky
|
||
|
||
Výstup: u:\\Dropbox\\!!!Days\\Downloads Z230\\SIPIQ_prehled_center_<ts>.xlsx
|
||
Závislosti: pymongo, openpyxl (.venv). Mongo 192.168.1.76:27017, bez auth.
|
||
"""
|
||
import os
|
||
import re
|
||
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"} # dlouhé právní souhlasy, ne data
|
||
COUNTRY_ABBR = {"Czech Republic": "CZ", "Slovakia": "SK"}
|
||
|
||
# barvy
|
||
C_SECTION = PatternFill("solid", fgColor="1F4E78") # tmavě modrá
|
||
C_STEM = PatternFill("solid", fgColor="DDEBF7") # světle modrá
|
||
C_IDENT = PatternFill("solid", fgColor="F2F2F2") # světle šedá
|
||
C_HEAD = PatternFill("solid", fgColor="305496") # hlavička center
|
||
C_YES = PatternFill("solid", fgColor="C6EFCE") # zelená
|
||
C_NO = PatternFill("solid", fgColor="FFC7CE") # červená
|
||
C_SUPP = PatternFill("solid", fgColor="FFE699") # oranžová (doplněno)
|
||
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())
|
||
# seřadit centra: CZ -> SK, pak příjmení
|
||
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"
|
||
|
||
# --- hlavička center (2 řádky) ---
|
||
ws.cell(1, 1, "OTÁZKA \\ CENTRUM").font = Font(bold=True, color="FFFFFF")
|
||
ws.cell(2, 1, "").font = Font(bold=True, color="FFFFFF")
|
||
ws.merge_cells("A1:A2")
|
||
for j, r in enumerate(resp):
|
||
col = 2 + j
|
||
c1 = ws.cell(1, col, r.get("pi_last_name") or "?")
|
||
cc = COUNTRY_ABBR.get(r.get("site_country"), "?")
|
||
c2 = ws.cell(2, col, f"{r.get('site_name') or ''} [{cc}]")
|
||
for c in (c1, c2):
|
||
c.font = Font(bold=True, color="FFFFFF", size=9)
|
||
c.fill = C_HEAD
|
||
c.alignment = WRAP_CTR
|
||
c.border = BORDER
|
||
ws.cell(1, 1).fill = C_HEAD
|
||
ws.cell(1, 1).alignment = WRAP_CTR
|
||
|
||
row = 3
|
||
|
||
def ident_row(label, getter):
|
||
nonlocal 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
|
||
row += 1
|
||
|
||
# --- IDENTITA ---
|
||
sec = ws.cell(row, 1, "■ IDENTITA CENTRA")
|
||
sec.font = Font(bold=True, color="FFFFFF")
|
||
sec.fill = C_SECTION
|
||
for j in range(n):
|
||
ws.cell(row, 2 + j).fill = C_SECTION
|
||
row += 1
|
||
ident_row("Země", lambda r: COUNTRY_ABBR.get(r.get("site_country"), r.get("site_country") or ""))
|
||
ident_row("Město", lambda r: r.get("site_city") or "")
|
||
ident_row("PI", lambda r: f"{r.get('pi_first_name') or ''} {r.get('pi_last_name') or ''}".strip())
|
||
ident_row("E-mail PI", lambda r: r.get("pi_email") or "")
|
||
ident_row("sdl_site_id", lambda r: r.get("sdl_site_id") or "")
|
||
ident_row("Datum vyplnění", lambda r: fmt_date((r.get("meta") or {}).get("recorded_date")))
|
||
|
||
# --- OTÁZKY po sekcích ---
|
||
cur_section = None
|
||
for q in questions:
|
||
section = q.get("section") or "Other"
|
||
if section in SKIP_SECTIONS:
|
||
continue
|
||
if section != cur_section:
|
||
cur_section = section
|
||
sc = ws.cell(row, 1, f"■ {section.upper()}")
|
||
sc.font = Font(bold=True, color="FFFFFF")
|
||
sc.fill = C_SECTION
|
||
sc.alignment = WRAP_TOP
|
||
sc.border = BORDER
|
||
for j in range(n):
|
||
cc = ws.cell(row, 2 + j)
|
||
cc.fill = C_SECTION
|
||
cc.border = BORDER
|
||
row += 1
|
||
|
||
base = q["_id"]
|
||
items = q.get("items") or []
|
||
if not items:
|
||
_emit_answer_row(ws, row, f'{base} — {q.get("text") or ""}', base, resp, stem=False)
|
||
row += 1
|
||
else:
|
||
# stem řádek (text otázky) — bez hodnot
|
||
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):
|
||
cc = ws.cell(row, 2 + j)
|
||
cc.fill = C_STEM
|
||
cc.border = BORDER
|
||
row += 1
|
||
for it in items:
|
||
lbl = it.get("label") or "(odpověď)"
|
||
_emit_answer_row(ws, row, f" – {lbl}", it["key"], resp, stem=False)
|
||
row += 1
|
||
|
||
# --- 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()
|
||
|
||
|
||
def _emit_answer_row(ws, row, label, key, resp, stem):
|
||
a = ws.cell(row, 1, label)
|
||
a.alignment = WRAP_TOP
|
||
a.border = BORDER
|
||
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 key in supp:
|
||
val = (supp[key] or {}).get("value")
|
||
c.value = val
|
||
c.fill = C_SUPP
|
||
c.font = Font(italic=True)
|
||
src = (supp[key] or {}).get("answer_source") or "doplněno"
|
||
c.comment = Comment(f"Doplněno mimo SIPIQ: {src}", "sipiq_report")
|
||
else:
|
||
val = ans.get(key)
|
||
c.value = val
|
||
if val == "Yes":
|
||
c.fill = C_YES
|
||
elif val == "No":
|
||
c.fill = C_NO
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|