110 lines
2.6 KiB
Python
110 lines
2.6 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
|
|
import pymysql
|
|
import pandas as pd
|
|
from datetime import datetime, timedelta
|
|
from openpyxl import load_workbook
|
|
from openpyxl.styles import Font, PatternFill, Alignment
|
|
from pathlib import Path
|
|
import os
|
|
|
|
# ==============================
|
|
# ⚙️ CONFIG
|
|
# ==============================
|
|
DB_CONFIG = {
|
|
"host": "192.168.1.76",
|
|
"port": 3307,
|
|
"user": "root",
|
|
"password": "Vlado9674+",
|
|
"database": "medevio",
|
|
"charset": "utf8mb4",
|
|
}
|
|
|
|
# Output location
|
|
timestamp = datetime.now().strftime("%Y-%m-%d %H-%M-%S")
|
|
OUTPUT_DIR = Path(r"U:\Dropbox\!!!Days\Downloads Z230")
|
|
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
|
|
|
|
OUTPUT_FILE = OUTPUT_DIR / f"{timestamp} Medevio report.xlsx"
|
|
|
|
# ==============================
|
|
# 🧹 >>> NEW: Remove old reports
|
|
# ==============================
|
|
for file in OUTPUT_DIR.glob("* Medevio report.xlsx"):
|
|
try:
|
|
file.unlink()
|
|
print("Removed old report:", file)
|
|
except Exception as e:
|
|
print("Could not remove:", file, "Error:", e)
|
|
|
|
# ==============================
|
|
# 📥 FETCH DATA
|
|
# ==============================
|
|
conn = pymysql.connect(**DB_CONFIG)
|
|
|
|
two_months_ago = (datetime.now() - timedelta(days=1000)).strftime("%Y-%m-%d %H:%M:%S")
|
|
|
|
sql = """
|
|
SELECT
|
|
id,
|
|
pacient_prijmeni,
|
|
pacient_jmeno,
|
|
pacient_rodnecislo,
|
|
displayTitle,
|
|
createdAt,
|
|
updatedAt,
|
|
doneAt,
|
|
removedAt,
|
|
attachmentsProcessed,
|
|
messagesProcessed,
|
|
communicationprocessed,
|
|
questionnaireprocessed,
|
|
lastSync
|
|
FROM pozadavky
|
|
WHERE createdAt >= %s
|
|
ORDER BY updatedAt DESC
|
|
"""
|
|
|
|
df = pd.read_sql(sql, conn, params=(two_months_ago,))
|
|
conn.close()
|
|
|
|
# ==============================
|
|
# 💾 SAVE TO EXCEL
|
|
# ==============================
|
|
df.to_excel(OUTPUT_FILE, index=False)
|
|
|
|
# ==============================
|
|
# 🎨 FORMAT EXCEL
|
|
# ==============================
|
|
wb = load_workbook(OUTPUT_FILE)
|
|
ws = wb.active
|
|
|
|
# Yellow header
|
|
header_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
|
|
header_font = Font(bold=True)
|
|
|
|
for cell in ws[1]:
|
|
cell.fill = header_fill
|
|
cell.font = header_font
|
|
cell.alignment = Alignment(horizontal="center")
|
|
|
|
# >>> NEW: AutoFilter
|
|
ws.auto_filter.ref = ws.dimensions
|
|
|
|
# Auto column width
|
|
for column in ws.columns:
|
|
max_length = 0
|
|
column_letter = column[0].column_letter
|
|
for cell in column:
|
|
try:
|
|
if cell.value is not None:
|
|
max_length = max(max_length, len(str(cell.value)))
|
|
except:
|
|
pass
|
|
ws.column_dimensions[column_letter].width = max_length + 2
|
|
|
|
wb.save(OUTPUT_FILE)
|
|
|
|
print("Report saved:", OUTPUT_FILE)
|