113 lines
3.8 KiB
Python
113 lines
3.8 KiB
Python
"""Excel-Export der Auswertung (mehrere Tabellenblätter)."""
|
||
import io
|
||
import json
|
||
|
||
from openpyxl import Workbook
|
||
from openpyxl.styles import Alignment, Font, PatternFill
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
from . import analytics, db
|
||
|
||
HEAD_FILL = PatternFill("solid", fgColor="0B6E4F")
|
||
HEAD_FONT = Font(color="FFFFFF", bold=True)
|
||
|
||
|
||
def _style_header(ws, ncols):
|
||
for c in range(1, ncols + 1):
|
||
cell = ws.cell(row=1, column=c)
|
||
cell.fill = HEAD_FILL
|
||
cell.font = HEAD_FONT
|
||
cell.alignment = Alignment(vertical="center")
|
||
ws.freeze_panes = "A2"
|
||
|
||
|
||
def _autosize(ws, max_width=60):
|
||
for col in ws.columns:
|
||
length = max((len(str(c.value)) for c in col if c.value is not None), default=8)
|
||
ws.column_dimensions[get_column_letter(col[0].column)].width = min(length + 2, max_width)
|
||
|
||
|
||
def build_workbook(period_days=None) -> bytes:
|
||
m = analytics.compute(period_days)
|
||
h = m["humanize"]
|
||
wb = Workbook()
|
||
|
||
# --- KPIs ---
|
||
ws = wb.active
|
||
ws.title = "KPIs"
|
||
ws.append(["Kennzahl", "Wert"])
|
||
rows = [
|
||
("Tickets gesamt", m["total"]),
|
||
("offen", m["open"]),
|
||
("geschlossen", m["closed"]),
|
||
("eskaliert", m["escalated"]),
|
||
("Ø Reaktionszeit", h(m["response"]["avg"])),
|
||
("Median Reaktionszeit", h(m["response"]["median"])),
|
||
("Ø Lösungszeit", h(m["resolution"]["avg"])),
|
||
("Median Lösungszeit", h(m["resolution"]["median"])),
|
||
("Ø Alter offener Tickets (Tage)",
|
||
round(m["open_age_avg_days"], 1) if m["open_age_avg_days"] else "–"),
|
||
]
|
||
for r in rows:
|
||
ws.append(list(r))
|
||
_style_header(ws, 2)
|
||
_autosize(ws)
|
||
|
||
# --- Verteilungen ---
|
||
ws = wb.create_sheet("Verteilungen")
|
||
ws.append(["Kategorie", "Wert", "Anzahl"])
|
||
for label, data in (("Status", m["by_state"]), ("Priorität", m["by_priority"]),
|
||
("Gruppe", m["by_group"]), ("Bearbeiter", m["by_owner"])):
|
||
for key, cnt in data:
|
||
ws.append([label, key, cnt])
|
||
_style_header(ws, 3)
|
||
_autosize(ws)
|
||
|
||
# --- Monatswerte ---
|
||
ws = wb.create_sheet("Monatswerte")
|
||
ws.append(["Monat", "erstellt", "geschlossen"])
|
||
for row in m["monthly"]:
|
||
ws.append([row["month"], row["created"], row["closed"]])
|
||
_style_header(ws, 3)
|
||
_autosize(ws)
|
||
|
||
# --- Tickets ---
|
||
ws = wb.create_sheet("Tickets")
|
||
cols = ["Nummer", "Titel", "Status", "Priorität", "Gruppe", "Bearbeiter",
|
||
"Artikel", "erstellt", "1. Reaktion", "geschlossen", "zuletzt geändert"]
|
||
ws.append(cols)
|
||
with db.get_conn() as conn:
|
||
for r in conn.execute("SELECT raw_json FROM tickets ORDER BY zammad_updated_at DESC"):
|
||
t = json.loads(r["raw_json"])
|
||
ws.append([
|
||
t.get("number"), t.get("title"), t.get("state"), t.get("priority"),
|
||
t.get("group"), t.get("owner"), t.get("article_count"),
|
||
_d(t.get("created_at")), _d(t.get("first_response_at")),
|
||
_d(t.get("last_close_at")), _d(t.get("updated_at")),
|
||
])
|
||
_style_header(ws, len(cols))
|
||
_autosize(ws)
|
||
|
||
# --- Änderungen ---
|
||
ws = wb.create_sheet("Änderungen")
|
||
ws.append(["Zeitpunkt", "Ticket", "Titel", "Feld", "alt", "neu", "Detail"])
|
||
with db.get_conn() as conn:
|
||
for c in conn.execute(
|
||
"SELECT detected_at, ticket_number, ticket_title, label, old_value, "
|
||
"new_value, detail FROM changes ORDER BY detected_at DESC LIMIT 5000"
|
||
):
|
||
ws.append([_d(c["detected_at"]), c["ticket_number"], c["ticket_title"],
|
||
c["label"], c["old_value"], c["new_value"], c["detail"]])
|
||
_style_header(ws, 7)
|
||
_autosize(ws)
|
||
|
||
buf = io.BytesIO()
|
||
wb.save(buf)
|
||
return buf.getvalue()
|
||
|
||
|
||
def _d(value):
|
||
if not value:
|
||
return ""
|
||
return str(value).replace("T", " ")[:16]
|