102 lines
2.5 KiB
Python
102 lines
2.5 KiB
Python
import os
|
|
import fdb
|
|
import csv,time,pandas as pd
|
|
import openpyxl
|
|
|
|
|
|
PathToSaveCSV=r"z:\NextCloudOrdinace\Reporty"
|
|
timestr = time.strftime("%Y-%m-%d %H-%M-%S ")
|
|
CSVname="Pacienti.xlsx"
|
|
# PathToSaveCSV=r"//tower/tempspeed"
|
|
|
|
con = fdb.connect(
|
|
host='localhost', database=r'm:\MEDICUS\data\medicus.FDB',
|
|
user='sysdba', password='masterkey',charset='WIN1250')
|
|
|
|
# Create a Cursor object that operates in the context of Connection con:
|
|
cur = con.cursor()
|
|
|
|
# import openpyxl module
|
|
import openpyxl
|
|
import xlwings as xw
|
|
wb = openpyxl.Workbook()
|
|
sheet = wb.active
|
|
# wb.save("sample.xlsx")
|
|
|
|
|
|
|
|
#Načtení receptů
|
|
cur.execute("""select
|
|
kar.rodcis,
|
|
TRIM(kar.prijmeni) ||' '|| substring(kar.jmeno from 1 for 1) ||'.' as jmeno,
|
|
recept.datum,
|
|
TRIM(recept.lek) ||' '|| trim(recept.dop) as lek,
|
|
recept.expori AS Poc,
|
|
CASE
|
|
WHEN recept.opakovani is null THEN 1
|
|
ELSE recept.opakovani
|
|
END AS OP,
|
|
recept.uhrada,
|
|
recept.dsig,
|
|
recept.NOTIFIKACE_KONTAKT as notifikace,
|
|
recept_epodani.erp,
|
|
recept_epodani.vystavitel_jmeno,
|
|
recept.atc,
|
|
recept.CENAPOJ,
|
|
recept.cenapac
|
|
from recept LEFT Join RECEPT_EPODANI on recept.id_epodani=recept_epodani.id
|
|
LEFT join kar on recept.idpac=kar.idpac
|
|
order by datum desc,erp desc"""
|
|
)
|
|
nacteno=cur.fetchall()
|
|
print(len(nacteno))
|
|
|
|
wb.create_sheet('Recepty',0)
|
|
sheet=wb['Recepty']
|
|
|
|
sheet.title="Recepty"
|
|
sheet.append(["Rodné číslo","Jméno","Datum vystavení","Název leku","Poč.","Op.","Úhr.","Da signa","Notifikace","eRECEPT","Vystavil","ATC","Cena pojišťovna","Cena pacient"])
|
|
|
|
for row in nacteno:
|
|
try:
|
|
sheet.append(row)
|
|
except:
|
|
continue
|
|
|
|
wb.save(os.path.join(PathToSaveCSV ,timestr+CSVname))
|
|
|
|
|
|
# autofilter
|
|
for ws in wb.worksheets:
|
|
# Get the maximum number of rows and columns
|
|
max_row = ws.max_row
|
|
max_column = ws.max_column
|
|
ws.auto_filter.ref = f"A1:{openpyxl.utils.get_column_letter(max_column)}{max_row}"
|
|
# ws.auto_filter.ref = ws.dimensions
|
|
|
|
|
|
|
|
|
|
|
|
wb.save(os.path.join(PathToSaveCSV ,timestr+CSVname))
|
|
|
|
|
|
# Tento modul je pouze na autofit jednotlivych sloupcu na vsech listech workbooku
|
|
file = os.path.join(PathToSaveCSV ,timestr+CSVname)
|
|
with xw.App(visible=False) as app:
|
|
wb = xw.Book(file)
|
|
for sheet in range(len(wb.sheets)):
|
|
ws = wb.sheets[sheet]
|
|
ws.autofit()
|
|
|
|
# centrování receptů
|
|
sheet = wb.sheets['Recepty']
|
|
for sloupec in ["C:C", "E:E", "F:F", "G:G", "I:I", "M:M", "N:N"]:
|
|
sheet.range(sloupec).api.HorizontalAlignment = 3 # 3 = Center
|
|
|
|
|
|
wb.save()
|
|
wb.close()
|
|
|
|
|