reporty/Vakcíny/Reporter Očkování report.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from pathlib import Path
import time
import firebirdsql as fb
import pandas as pd
import re
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
# from Functions import get_medicus_connection

# ================== Výstupní cesta ==================
BASE_DIR = Path(r"z:\Dropbox\Ordinace\Reporty")  # uprav dle potřeby
timestamp = time.strftime("%Y-%m-%d %H-%M-%S")
xlsx_name = f"{timestamp} Očkování report.xlsx"
xlsx_path = BASE_DIR / xlsx_name

# ================== Smazání starých souborů ==================
for old_file in BASE_DIR.glob("*očkování report.xlsx"):
    try:
        if old_file != xlsx_path:  # skip the file we’re about to create
            old_file.unlink()
            print(f"Smazán starý soubor: {old_file.name}")
    except Exception as e:
        print(f"⚠️ Nelze smazat {old_file.name}: {e}")

# ================== Definice skupin vakcín ==================
SHEETS = {
    "COVID-19": ["commirnaty", "spikevax", "nuvaxovid"],
    "Chřipka": ["vaxigrip", "influvac", "fluarix", "afluria"],
    "Klíšťová encefalitida": ["fsme", "encepur"],
    "Tetanus": ["tetavax", "boostrix", "adacel"],
    "HepA": ["avaxim", "havrix","vaqta"],
    "HepB": ["engerix"],
    "HepA+B": ["twinrix"],
}

# ================== Připojení k DB ==================
# con = get_medicus_connection()
con = fb.connect(
    host="192.168.1.10",
    port=3050,
    database=r"m:\Medicus\data\MEDICUS.FDB",  # raw string for backslashes
    user="SYSDBA",
    password="masterkey",
    charset="WIN1250",  # adjust if needed
)
# ================== SQL dotaz ==================
sql = """
SELECT
    kar.rodcis    AS "Rodné číslo",
    kar.prijmeni  AS "Příjmení",
    kar.jmeno     AS "Jméno",
    ockzaz.datum  AS "Datum očkování",
    ockzaz.kodmz  AS "Kód MZ",
    ockzaz.poznamka AS "Šarže",
    ockzaz.latka  AS "Látka",
    ockzaz.nazev  AS "Název",
    ockzaz.expire AS "Expirace",
    (
      SELECT LIST(l.kod, ', ')
      FROM lecd l
      WHERE l.rodcis = kar.rodcis
        AND l.datose = CAST(ockzaz.datum AS DATE)
    ) AS "LECD kódy (ten den)",
    (
      SELECT LIST(d.kod, ', ')
      FROM dokladd d
      WHERE d.rodcis = kar.rodcis
        AND d.datose = CAST(ockzaz.datum AS DATE)
    ) AS "Výkony (ten den)"
FROM registr
JOIN kar     ON registr.idpac = kar.idpac
JOIN ockzaz  ON registr.idpac = ockzaz.idpac
WHERE
    registr.datum_zruseni IS NULL
    AND kar.vyrazen <> 'A'
    AND kar.rodcis IS NOT NULL
    AND idicp <> 0
    AND EXTRACT(YEAR FROM ockzaz.datum) = 2025
ORDER BY ockzaz.datum DESC
"""

# ================== Načtení do DataFrame ==================
df = pd.read_sql(sql, con)
con.close()

# ================== Datové typy ==================
for col in ["Kód MZ", "Šarže", "Rodné číslo", "Látka", "Název", "Příjmení", "Jméno", "LECD kódy", "Výkony"]:
    if col in df.columns:
        df[col] = df[col].astype("string")

for dcol in ["Datum očkování", "Expirace"]:
    if dcol in df.columns:
        df[dcol] = pd.to_datetime(df[dcol], errors="coerce")

# ================== Uložení do Excelu – více listů ==================
with pd.ExcelWriter(xlsx_path, engine="openpyxl") as writer:
    for sheet_name, vakciny in SHEETS.items():
        pattern = "|".join(re.escape(v) for v in vakciny if v)
        mask = df["Látka"].astype(str).str.contains(pattern, case=False, na=False)
        df_filtered = df[mask]
        if not df_filtered.empty:
            df_filtered.to_excel(writer, index=False, sheet_name=sheet_name)
    # navíc celkový přehled všech očkování
    df.to_excel(writer, index=False, sheet_name="Vše")

# ================== Formátování ==================
wb = load_workbook(xlsx_path)

def autosize_columns(ws):
    for col_idx in range(1, ws.max_column + 1):
        col_letter = get_column_letter(col_idx)
        max_len = 0
        for cell in ws[col_letter]:
            val = "" if cell.value is None else str(cell.value)
            if len(val) > max_len:
                max_len = len(val)
        ws.column_dimensions[col_letter].width = min(max(12, max_len + 2), 60)

def safe_table_name(sheet_name):
    """Return an Excel-safe, unique table name."""
    name = re.sub(r"[^0-9A-Za-z_]", "_", sheet_name)
    return f"tbl_{name[:25]}"

def style_table(ws):
    max_row = ws.max_row
    max_col = ws.max_column
    if max_col == 0:
        return

    header_fill = PatternFill("solid", fgColor="D9E1F2")
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.alignment = Alignment(vertical="center")

    ws.freeze_panes = "A2"

    if max_row < 2:
        autosize_columns(ws)
        return

    ref = f"A1:{get_column_letter(max_col)}{max_row}"
    tbl = Table(displayName=safe_table_name(ws.title), ref=ref)
    tbl.tableStyleInfo = TableStyleInfo(
        name="TableStyleMedium9", showRowStripes=True, showColumnStripes=False
    )
    ws.add_table(tbl)
    autosize_columns(ws)

def format_dates(ws, columns_names):
    header = [c.value for c in ws[1]]
    date_cols = [header.index(name) + 1 for name in columns_names if name in header]
    for col_idx in date_cols:
        for row in ws.iter_rows(min_row=2, min_col=col_idx, max_col=col_idx, max_row=ws.max_row):
            row[0].number_format = "DD.MM.YYYY"

for ws in wb.worksheets:
    style_table(ws)
    format_dates(ws, ["Datum očkování", "Expirace"])

wb.save(xlsx_path)

print(f"✅ Hotovo. Uloženo do: {xlsx_path.resolve()}")