153 lines
4.8 KiB
Python
153 lines
4.8 KiB
Python
"""Auswertungen: KPIs, Zeitverläufe, Verteilungen, Reaktions-/Lösungszeiten.
|
||
|
||
Rechnet auf Basis der gespeicherten Tickets (raw_json). Bei ~hunderten bis
|
||
wenigen tausend Tickets ist das pro Seitenaufruf problemlos schnell.
|
||
"""
|
||
import json
|
||
import statistics
|
||
from collections import Counter, defaultdict
|
||
from datetime import datetime, timezone
|
||
|
||
from . import db
|
||
|
||
OPEN_STATES = ("new", "open", "pending reminder", "pending close")
|
||
|
||
|
||
# --------------------------------------------------------------- Zeit-Helfer
|
||
def parse_dt(value):
|
||
if not value:
|
||
return None
|
||
try:
|
||
s = value.replace("Z", "+00:00") if value.endswith("Z") else value
|
||
dt = datetime.fromisoformat(s)
|
||
if dt.tzinfo is None:
|
||
dt = dt.replace(tzinfo=timezone.utc)
|
||
return dt
|
||
except (ValueError, AttributeError):
|
||
return None
|
||
|
||
|
||
def humanize(minutes):
|
||
"""Minuten -> lesbare Dauer (z. B. '3,2 Std.', '1,5 Tage')."""
|
||
if minutes is None:
|
||
return "–"
|
||
if minutes < 60:
|
||
return f"{round(minutes)} Min."
|
||
hours = minutes / 60
|
||
if hours < 48:
|
||
return f"{hours:.1f} Std.".replace(".", ",")
|
||
return f"{hours / 24:.1f} Tage".replace(".", ",")
|
||
|
||
|
||
def _stats(values):
|
||
"""min/median/avg/p90 aus einer Liste (in Minuten)."""
|
||
if not values:
|
||
return {"n": 0, "median": None, "avg": None, "p90": None}
|
||
s = sorted(values)
|
||
p90 = s[min(len(s) - 1, int(round(0.9 * (len(s) - 1))))]
|
||
return {
|
||
"n": len(s),
|
||
"median": statistics.median(s),
|
||
"avg": sum(s) / len(s),
|
||
"p90": p90,
|
||
}
|
||
|
||
|
||
# --------------------------------------------------------------- Hauptlogik
|
||
def load_tickets():
|
||
with db.get_conn() as conn:
|
||
rows = conn.execute("SELECT raw_json FROM tickets").fetchall()
|
||
return [json.loads(r["raw_json"]) for r in rows]
|
||
|
||
|
||
def compute(period_days=None):
|
||
now = datetime.now(timezone.utc)
|
||
tickets = load_tickets()
|
||
|
||
# Zeitraumfilter auf Erstellungsdatum
|
||
if period_days:
|
||
cutoff = now.timestamp() - period_days * 86400
|
||
tickets = [
|
||
t for t in tickets
|
||
if (d := parse_dt(t.get("created_at"))) and d.timestamp() >= cutoff
|
||
]
|
||
|
||
total = len(tickets)
|
||
by_state = Counter()
|
||
by_priority = Counter()
|
||
by_group = Counter()
|
||
by_owner = Counter()
|
||
by_month = defaultdict(lambda: {"created": 0, "closed": 0})
|
||
|
||
response_min = []
|
||
resolution_min = []
|
||
escalated = 0
|
||
open_ages_days = []
|
||
oldest_open = None
|
||
|
||
for t in tickets:
|
||
state = t.get("state") or "unbekannt"
|
||
by_state[state] += 1
|
||
by_priority[t.get("priority") or "—"] += 1
|
||
by_group[t.get("group") or "—"] += 1
|
||
owner = t.get("owner")
|
||
by_owner["(nicht zugewiesen)" if not owner or owner == "-" else owner] += 1
|
||
|
||
created = parse_dt(t.get("created_at"))
|
||
first_resp = parse_dt(t.get("first_response_at"))
|
||
closed = parse_dt(t.get("last_close_at"))
|
||
|
||
if created:
|
||
by_month[created.strftime("%Y-%m")]["created"] += 1
|
||
if closed:
|
||
by_month[closed.strftime("%Y-%m")]["closed"] += 1
|
||
|
||
if created and first_resp and first_resp >= created:
|
||
response_min.append((first_resp - created).total_seconds() / 60)
|
||
if created and closed and closed >= created:
|
||
resolution_min.append((closed - created).total_seconds() / 60)
|
||
|
||
if t.get("escalation_at"):
|
||
escalated += 1
|
||
|
||
if state in OPEN_STATES and created:
|
||
age = (now - created).total_seconds() / 86400
|
||
open_ages_days.append(age)
|
||
if oldest_open is None or age > oldest_open["age"]:
|
||
oldest_open = {
|
||
"age": age, "number": t.get("number"),
|
||
"title": t.get("title"), "id": t.get("id"),
|
||
}
|
||
|
||
open_count = sum(by_state[s] for s in by_state if s in OPEN_STATES)
|
||
closed_count = by_state.get("closed", 0)
|
||
|
||
# Monatsreihe lückenlos auffüllen und sortieren
|
||
months = sorted(by_month.keys())
|
||
monthly = [
|
||
{"month": m, "created": by_month[m]["created"], "closed": by_month[m]["closed"]}
|
||
for m in months
|
||
]
|
||
|
||
return {
|
||
"total": total,
|
||
"open": open_count,
|
||
"closed": closed_count,
|
||
"escalated": escalated,
|
||
"response": _stats(response_min),
|
||
"resolution": _stats(resolution_min),
|
||
"by_state": by_state.most_common(),
|
||
"by_priority": _ordered_priority(by_priority),
|
||
"by_group": by_group.most_common(),
|
||
"by_owner": by_owner.most_common(),
|
||
"monthly": monthly,
|
||
"open_age_avg_days": (sum(open_ages_days) / len(open_ages_days)) if open_ages_days else None,
|
||
"oldest_open": oldest_open,
|
||
"humanize": humanize,
|
||
}
|
||
|
||
|
||
def _ordered_priority(counter):
|
||
order = {"1 low": 0, "2 normal": 1, "3 high": 2}
|
||
return sorted(counter.items(), key=lambda kv: order.get(kv[0], 99))
|