#!/usr/bin/env python3 """ Generiert ein PDF mit Laborwerten aus der Datenbank. Usage: python3 generate_laborwerte_pdf.py [--date DATE] [--output FILE] [--latest] [--category CAT] Options: --date DATE Specific date to query (YYYY-MM-DD or full timestamp) --output FILE Output PDF path (default: ~/Gesundheit/laborwerte_YYYY-MM-DD.pdf) --latest Show only the latest measurement --category CAT Filter by category (Lipide, Entzündung, Niere, Leber, Hämatologie, Koagulation, Schilddrüse, Infektion, Urin) """ import sqlite3 import sys import os from datetime import datetime from io import BytesIO from reportlab.lib.pagesizes import A4 from reportlab.lib import colors from reportlab.lib.units import cm from reportlab.platypus import ( SimpleDocTemplate, Paragraph, Table, TableStyle, Spacer, KeepTogether, HRFlowable ) from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.enums import TA_LEFT, TA_CENTER, TA_RIGHT DB_PATH = os.path.expanduser('~/Gesundheit/health_data.db') # Category mapping for filtering CATEGORY_MAP = { 'lipide': ['Cholesterin total', 'HDL-Cholesterin', 'LDL-Cholesterin', 'non-HDL-Cholesterin', 'Triglyceride', 'Cholesterin gesamt', 'HDL-Cholesterin', 'LDL-Cholesterin', 'non-HDL-Cholesterin'], 'entzündung': ['CRP'], 'nieren': ['Kreatinin', 'eGFR', 'Harnstoff', 'Phosphat', 'Kalium', 'Natrium', 'Albumin/Kreatinin Urin', 'Protein/Kreatinin Urin', 'Ketokörper Urin', 'Urobilinogen Urin', 'spez. Gewicht Urin'], 'leber': ['ALT', 'ALAT', 'AST', 'ASAT', 'GGT', 'Alk. Phosphatase', 'Albumin', 'Bilirubin gesamt', 'Proteine gesamt'], 'hämatologie': ['Hämoglobin', 'Hämatokrit', 'Leukozyten', 'Thrombozyten', 'MCV', 'MCH', 'MCHC', 'MPV'], 'koagulation': ['Gerinnung', 'Prothrombinzeit', 'INR', 'Thrombozyten'], 'schilddrüse': ['TSH', 'fT3', 'fT4', 'T3', 'T4', 'Thyroglobulin', 'TPO-Antikörper', 'Tg-Antikörper'], 'infektion': ['HIV Ag/Ak Combo', 'HBs-Antigen', 'Anti-HCV', 'Anti-HBc-total', 'Anti-HBs', 'Anti-HBs quant', 'EBNA-1 IgG', 'VCA-IgG', 'VCA-IgM', 'EA-IgG'], 'urin': ['Kreatinin', 'Albumin/Kreatinin Urin', 'Protein/Kreatinin Urin', 'Ketokörper Urin', 'Urobilinogen Urin', 'spez. Gewicht Urin', 'Leukozyten Urin', 'Nitrit Urin'], } # Parameter categories for display grouping PARAM_CATEGORY = {} def get_categories(): """Get unique dates from database.""" conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("SELECT DISTINCT ermittlung_datum FROM laborwerte ORDER BY ermittlung_datum DESC") dates = [r[0] for r in cursor.fetchall()] conn.close() return dates def fetch_values(date_filter=None, category_filter=None): """Fetch laborwerte from database.""" conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() if date_filter: cursor.execute(""" SELECT parameter_name, wert, einheiten, reference_min, reference_max, bemerking FROM laborwerte WHERE ermittlung_datum = ? ORDER BY parameter_name """, (date_filter,)) else: # Get latest cursor.execute("SELECT MAX(ermittlung_datum) FROM laborwerte") max_date = cursor.fetchone()[0] cursor.execute(""" SELECT parameter_name, wert, einheiten, reference_min, reference_max, bemerking FROM laborwerte WHERE ermittlung_datum = ? ORDER BY parameter_name """, (max_date,)) rows = cursor.fetchall() conn.close() # Apply category filter if category_filter and category_filter.lower() in CATEGORY_MAP: allowed = CATEGORY_MAP[category_filter.lower()] rows = [r for r in rows if r[0] in allowed] return rows def format_value(value, unit, ref_min, ref_max): """Format a single value with status indicator.""" status = "" # Check if value is numeric for range comparison try: num_val = float(value) if ref_min and ref_max: r_min = float(ref_min) r_max = float(ref_max) if num_val < r_min: status = " ⬇" elif num_val > r_max: status = " ⬆" except (ValueError, TypeError): pass ref_str = "" if ref_min and ref_max: ref_str = f"{ref_min}–{ref_max}" return f"{value} {unit}".strip(), ref_str, status def generate_pdf(dates=None, output_path=None, category_filter=None): """Generate PDF report.""" if output_path is None: output_path = os.path.expanduser('~/Gesundheit/laborwerte_report.pdf') doc = SimpleDocTemplate( output_path, pagesize=A4, rightMargin=2*cm, leftMargin=2*cm, topMargin=2*cm, bottomMargin=2*cm, ) styles = getSampleStyleSheet() elements = [] # Title title_style = ParagraphStyle('Title', parent=styles['Heading1'], fontSize=18, spaceAfter=12) elements.append(Paragraph("Laborwerte-Report", title_style)) # Date info info_style = ParagraphStyle('Info', parent=styles['Normal'], fontSize=10, spaceAfter=8) if dates: date_str = ", ".join(str(d)[:10] for d in dates[:10]) elements.append(Paragraph(f"Zeitraum: {date_str}", info_style)) else: elements.append(Paragraph(f"Erstellt: {datetime.now().strftime('%d.%m.%Y %H:%M')}", info_style)) elements.append(Spacer(1, 12)) # Process each date if dates: date_list = dates else: date_list = get_categories() for date in date_list: rows = fetch_values(str(date), category_filter) if not rows: continue # Date header header_style = ParagraphStyle('DateHeader', parent=styles['Heading2'], fontSize=13, spaceBefore=12, spaceAfter=6) date_display = str(date)[:10] elements.append(Paragraph(f"Laborwerte vom {date_display}", header_style)) # Build table header = [ Paragraph("Parameter", ParagraphStyle('h', fontSize=9, fontName='Helvetica-Bold')), Paragraph("Wert", ParagraphStyle('h', fontSize=9, fontName='Helvetica-Bold')), Paragraph("Einheit", ParagraphStyle('h', fontSize=9, fontName='Helvetica-Bold')), Paragraph("Referenz", ParagraphStyle('h', fontSize=9, fontName='Helvetica-Bold')), ] table_data = [header] for param, wert, einheit, ref_min, ref_max, bemerking in rows: formatted_val, ref_str, status = format_value(wert, einheit, ref_min, ref_max) row = [ Paragraph(param, ParagraphStyle('p', fontSize=9)), Paragraph(f"{formatted_val}{status}", ParagraphStyle('v', fontSize=9, alignment=TA_RIGHT)), Paragraph(einheit or "", ParagraphStyle('u', fontSize=9, alignment=TA_CENTER)), Paragraph(ref_str or "–", ParagraphStyle('r', fontSize=9, alignment=TA_CENTER)), ] table_data.append(row) t = Table(table_data, colWidths=[5*cm, 3*cm, 2*cm, 3*cm]) style = TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.grey), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, -1), 9), ('BOTTOMPADDING', (0, 0), (-1, 0), 6), ('TOPPADDING', (0, 0), (-1, 0), 6), ('BACKGROUND', (0, 1), (-1, -1), colors.whitesmoke), ('GRID', (0, 0), (-1, -1), 0.5, colors.grey), ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'), ('LEFTPADDING', (0, 0), (-1, -1), 4), ('RIGHTPADDING', (0, 0), (-1, -1), 4), ]) t.setStyle(style) elements.append(t) elements.append(Spacer(1, 12)) doc.build(elements) print(f"PDF generated: {output_path}") return output_path def main(): import argparse parser = argparse.ArgumentParser(description='Generate PDF from health database') parser.add_argument('--date', help='Specific date (YYYY-MM-DD)') parser.add_argument('--output', '-o', help='Output PDF path') parser.add_argument('--latest', '-l', action='store_true', help='Show latest only') parser.add_argument('--category', '-c', help='Filter by category') parser.add_argument('--list-dates', action='store_true', help='List available dates') args = parser.parse_args() if args.list_dates: dates = get_categories() print("Available dates:") for d in dates: print(f" {d}") return if args.date: date_str = args.date dates = [date_str] elif args.latest: dates = None # Use latest else: dates = None # Use latest generate_pdf( dates=dates, output_path=args.output, category_filter=args.category, ) if __name__ == '__main__': main()