95 lines
2.3 KiB
Python
95 lines
2.3 KiB
Python
"""
|
|
Report: předepsané léky — jeden řádek na lék.
|
|
Výstup: report_predepsane_leky_RRRR.xlsx
|
|
|
|
Spuštění:
|
|
python report_predepsane_leky.py # aktuální rok
|
|
python report_predepsane_leky.py 2025 # jiný rok
|
|
"""
|
|
|
|
import sys
|
|
import io
|
|
from datetime import date
|
|
from pathlib import Path
|
|
|
|
import pymysql
|
|
import pymysql.cursors
|
|
import openpyxl
|
|
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8", errors="replace")
|
|
|
|
DB = dict(
|
|
host="192.168.1.76",
|
|
user="root",
|
|
password="Vlado9674+",
|
|
database="medicus",
|
|
charset="utf8mb4",
|
|
cursorclass=pymysql.cursors.DictCursor,
|
|
)
|
|
|
|
ROK = int(sys.argv[1]) if len(sys.argv) > 1 else date.today().year
|
|
|
|
SQL = """
|
|
SELECT
|
|
p.datum_vystaveni,
|
|
pac.prijmeni,
|
|
pac.jmena,
|
|
pac.datum_narozeni,
|
|
rd.cp,
|
|
rd.zp_nazev,
|
|
p.nazev AS predepsany_lek,
|
|
p.atc,
|
|
p.forma,
|
|
p.sila,
|
|
p.baleni,
|
|
p.mnozstvi,
|
|
p.navod,
|
|
p.typ_leku,
|
|
rp.uhrada,
|
|
rp.prekroceni,
|
|
rd.stav,
|
|
rd.platnost_do,
|
|
rd.opakovani,
|
|
rd.akutni,
|
|
rd.modry_pruh,
|
|
rd.pozn AS poznamka_na_receptu,
|
|
v.datum_vydeje,
|
|
v.nazev AS vydany_lek,
|
|
v.mnozstvi AS vydane_mnozstvi,
|
|
vyd.pzs_nazev AS lekarna,
|
|
p.id_lp_predpis,
|
|
rd.id_dokladu
|
|
FROM predpis p
|
|
JOIN zprava z ON z.id = p.zprava_id
|
|
JOIN pacient pac ON pac.id = z.pacient_id
|
|
LEFT JOIN recept_plp rp ON rp.id_lp = p.id_lp_predpis
|
|
LEFT JOIN recept_doklad rd ON rd.id_dokladu = rp.id_dokladu
|
|
LEFT JOIN vydej v ON v.id_lp_predpis = p.id_lp_predpis
|
|
LEFT JOIN vydavajici vyd ON vyd.lekarnik_kod = v.kod_vydavajiciho
|
|
WHERE YEAR(p.datum_vystaveni) = %s
|
|
ORDER BY p.datum_vystaveni DESC, pac.prijmeni, pac.jmena, p.nazev
|
|
"""
|
|
|
|
conn = pymysql.connect(**DB)
|
|
with conn.cursor() as cur:
|
|
cur.execute(SQL, (ROK,))
|
|
rows = cur.fetchall()
|
|
conn.close()
|
|
|
|
print(f"Načteno {len(rows)} řádků za rok {ROK}")
|
|
|
|
wb = openpyxl.Workbook()
|
|
ws = wb.active
|
|
ws.title = f"Předpisy {ROK}"
|
|
|
|
if rows:
|
|
ws.append(list(rows[0].keys()))
|
|
for row in rows:
|
|
ws.append([str(v) if v is not None else "" for v in row.values()])
|
|
|
|
out = Path(__file__).parent / f"report_predepsane_leky_{ROK}.xlsx"
|
|
wb.save(out)
|
|
print(f"Uloženo: {out}")
|
|
import os
|
|
os.startfile(out)
|