344 lines
14 KiB
TypeScript
344 lines
14 KiB
TypeScript
import { createServiceClient } from "@/lib/supabase";
|
||
import { AdminNav } from "@/components/admin/AdminNav";
|
||
import { GanttKalender } from "./GanttKalender";
|
||
import type { Metadata } from "next";
|
||
|
||
export const metadata: Metadata = { title: "Statistik & Planung" };
|
||
export const dynamic = "force-dynamic";
|
||
|
||
function fmt(n: number, nachkomma = 0) {
|
||
return n.toLocaleString("de-DE", {
|
||
minimumFractionDigits: nachkomma,
|
||
maximumFractionDigits: nachkomma,
|
||
});
|
||
}
|
||
|
||
// Maximale Balkenbreite in % relativ zum Top-Wert
|
||
function balkenBreite(wert: number, max: number): string {
|
||
if (max === 0) return "0%";
|
||
return `${Math.max(4, Math.round((wert / max) * 100))}%`;
|
||
}
|
||
|
||
export default async function StatistikPage() {
|
||
const db = createServiceClient();
|
||
|
||
const heute = new Date();
|
||
heute.setHours(0, 0, 0, 0);
|
||
const heuteISO = heute.toISOString().slice(0, 10);
|
||
|
||
const vor12Monaten = new Date(heute);
|
||
vor12Monaten.setMonth(vor12Monaten.getMonth() - 12);
|
||
const vor12ISO = vor12Monaten.toISOString().slice(0, 10);
|
||
|
||
const in3Monaten = new Date(heute);
|
||
in3Monaten.setMonth(in3Monaten.getMonth() + 3);
|
||
in3Monaten.setDate(in3Monaten.getDate() + 1);
|
||
const in3ISO = in3Monaten.toISOString().slice(0, 10);
|
||
|
||
// ── DB-Abfragen ──────────────────────────────────────────────────────────
|
||
const { data: anfragen } = await db
|
||
.from("anfragen")
|
||
.select("id, status, created_at, firma");
|
||
|
||
const { data: allePositionen } = await db
|
||
.from("anfragen_positionen")
|
||
.select(
|
||
"id, anfrage_id, maschine_name, maschine_kategorie, mietbeginn, mietende, gesamt_tage, tagessatz"
|
||
)
|
||
.gte("mietbeginn", vor12ISO)
|
||
.order("mietbeginn");
|
||
|
||
const { data: planRoh } = await db
|
||
.from("anfragen_positionen")
|
||
.select(
|
||
"id, anfrage_id, maschine_name, maschine_kategorie, mietbeginn, mietende, gesamt_tage, tagessatz"
|
||
)
|
||
.lte("mietbeginn", in3ISO)
|
||
.gte("mietende", heuteISO)
|
||
.order("mietbeginn");
|
||
|
||
const anfrageMap = new Map(
|
||
(anfragen ?? []).map((a) => [a.id, a])
|
||
);
|
||
|
||
// Status-Sets (nur abgeschlossene Mietvorgänge zählen, die zuvor bestätigt waren)
|
||
// Lade Audit Log um zu verifizieren: bestätigt → abgeschlossenen Workflow
|
||
const { data: auditLogs } = await db
|
||
.from("anfrage_status_audit")
|
||
.select("anfrage_id, status_zu");
|
||
|
||
// Map: anfrage_id → hat bestätigt Status durchlaufen?
|
||
const hatBestaetigt = new Map<string, boolean>();
|
||
(auditLogs ?? []).forEach((log: any) => {
|
||
if (log.status_zu === "bestaetigt") {
|
||
hatBestaetigt.set(log.anfrage_id, true);
|
||
}
|
||
});
|
||
|
||
const bestaetigtIds = new Set(
|
||
(anfragen ?? [])
|
||
.filter((a) => a.status === "abgeschlossen" && hatBestaetigt.has(a.id))
|
||
.map((a) => a.id)
|
||
);
|
||
|
||
// ── KPIs ────────────────────────────────────────────────────────────────
|
||
const kpiPos = (allePositionen ?? []).filter((p) => bestaetigtIds.has(p.anfrage_id));
|
||
const umsatzGesamt = kpiPos.reduce(
|
||
(s, p) => s + (p.tagessatz != null ? p.tagessatz * p.gesamt_tage : 0),
|
||
0
|
||
);
|
||
const mietTageGesamt = kpiPos.reduce((s, p) => s + (p.gesamt_tage ?? 0), 0);
|
||
const offenAnzahl = (anfragen ?? []).filter((a) => a.status === "offen").length;
|
||
|
||
// ── Auslastung pro Maschine ──────────────────────────────────────────────
|
||
const maschinenMap: Record<
|
||
string,
|
||
{ name: string; kategorie: string; tage: number; umsatz: number; einsaetze: number }
|
||
> = {};
|
||
|
||
for (const p of allePositionen ?? []) {
|
||
if (!bestaetigtIds.has(p.anfrage_id)) continue;
|
||
const key = p.maschine_name;
|
||
if (!maschinenMap[key]) {
|
||
maschinenMap[key] = {
|
||
name: p.maschine_name,
|
||
kategorie: p.maschine_kategorie ?? "",
|
||
tage: 0,
|
||
umsatz: 0,
|
||
einsaetze: 0,
|
||
};
|
||
}
|
||
maschinenMap[key].tage += p.gesamt_tage ?? 0;
|
||
maschinenMap[key].umsatz +=
|
||
p.tagessatz != null ? p.tagessatz * p.gesamt_tage : 0;
|
||
maschinenMap[key].einsaetze += 1;
|
||
}
|
||
|
||
const maschinenStats = Object.values(maschinenMap).sort((a, b) => b.tage - a.tage);
|
||
const maxTage = maschinenStats[0]?.tage ?? 1;
|
||
|
||
// ── Monatliche Entwicklung (letzte 6 Monate) ────────────────────────────
|
||
const monatsStats: {
|
||
monat: string;
|
||
label: string;
|
||
einsaetze: number;
|
||
tage: number;
|
||
umsatz: number;
|
||
}[] = [];
|
||
|
||
for (let i = 5; i >= 0; i--) {
|
||
const d = new Date(heute);
|
||
d.setMonth(d.getMonth() - i);
|
||
const monat = `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}`;
|
||
const label = d.toLocaleDateString("de-DE", { month: "short", year: "2-digit" });
|
||
|
||
const mPos = (allePositionen ?? []).filter(
|
||
(p) => bestaetigtIds.has(p.anfrage_id) && p.mietbeginn?.startsWith(monat)
|
||
);
|
||
|
||
monatsStats.push({
|
||
monat,
|
||
label,
|
||
einsaetze: mPos.length,
|
||
tage: mPos.reduce((s, p) => s + (p.gesamt_tage ?? 0), 0),
|
||
umsatz: mPos.reduce(
|
||
(s, p) => s + (p.tagessatz != null ? p.tagessatz * p.gesamt_tage : 0),
|
||
0
|
||
),
|
||
});
|
||
}
|
||
const maxUmsatz = Math.max(...monatsStats.map((m) => m.umsatz), 1);
|
||
|
||
// ── Planungs-Daten anreichern ─────────────────────────────────────────
|
||
const planDaten = (planRoh ?? []).map((p) => {
|
||
const a = anfrageMap.get(p.anfrage_id);
|
||
return {
|
||
...p,
|
||
anfrage_status: a?.status ?? "offen",
|
||
firma: a?.firma ?? "–",
|
||
};
|
||
});
|
||
|
||
return (
|
||
<div className="min-h-screen bg-[#f5f5f4]">
|
||
<AdminNav />
|
||
|
||
<div className="max-w-7xl mx-auto px-4 py-8 space-y-8">
|
||
<div>
|
||
<h1 className="text-2xl font-bold text-[#1c1917] tracking-tight">
|
||
Statistik & Planung
|
||
</h1>
|
||
<p className="text-sm text-slate-500 mt-0.5">
|
||
Letzte 12 Monate · Bestätigte Vermietungen
|
||
</p>
|
||
</div>
|
||
|
||
{/* ── KPI-Cards ─────────────────────────────────────────────────── */}
|
||
<div className="grid grid-cols-2 lg:grid-cols-4 gap-4">
|
||
{[
|
||
{
|
||
label: "Anfragen gesamt",
|
||
wert: fmt(anfragen?.length ?? 0),
|
||
sub: `${offenAnzahl} offen`,
|
||
farbe: "border-l-slate-400",
|
||
},
|
||
{
|
||
label: "Bestätigte Mieten",
|
||
wert: fmt(bestaetigtIds.size),
|
||
sub: "letzte 12 Monate",
|
||
farbe: "border-l-green-500",
|
||
},
|
||
{
|
||
label: "Vermietete Tage",
|
||
wert: fmt(mietTageGesamt),
|
||
sub: `Ø ${fmt(mietTageGesamt / Math.max(bestaetigtIds.size, 1), 1)} Tage/Miete`,
|
||
farbe: "border-l-[#f7d334]",
|
||
},
|
||
{
|
||
label: "Netto-Umsatz",
|
||
wert: `${fmt(umsatzGesamt)} €`,
|
||
sub: `Ø ${fmt(umsatzGesamt / Math.max(6, 1))} €/Monat`,
|
||
farbe: "border-l-blue-500",
|
||
},
|
||
].map((k) => (
|
||
<div
|
||
key={k.label}
|
||
className={`bg-white border border-slate-200 border-l-4 ${k.farbe} p-4`}
|
||
>
|
||
<p className="text-xs text-slate-500 uppercase tracking-wide font-medium mb-1">
|
||
{k.label}
|
||
</p>
|
||
<p className="text-2xl font-bold text-[#1c1917] font-mono">{k.wert}</p>
|
||
<p className="text-xs text-slate-400 mt-1">{k.sub}</p>
|
||
</div>
|
||
))}
|
||
</div>
|
||
|
||
{/* ── Monatsüberblick (Balkendiagramm) ─────────────────────────── */}
|
||
<div className="bg-white border border-slate-200 p-5">
|
||
<h2 className="font-semibold text-slate-900 mb-5">
|
||
Umsatz letzte 6 Monate (netto)
|
||
</h2>
|
||
<div className="flex items-end gap-3 h-32">
|
||
{monatsStats.map((m) => (
|
||
<div key={m.monat} className="flex-1 flex flex-col items-center gap-1">
|
||
<span className="text-[10px] font-mono text-slate-500">
|
||
{m.umsatz > 0 ? `${fmt(m.umsatz)} €` : "–"}
|
||
</span>
|
||
<div className="w-full relative flex items-end" style={{ height: 80 }}>
|
||
<div
|
||
className="w-full bg-[#f7d334] rounded-sm transition-all"
|
||
style={{
|
||
height: `${Math.max(m.umsatz > 0 ? 8 : 0, Math.round((m.umsatz / maxUmsatz) * 80))}px`,
|
||
}}
|
||
/>
|
||
</div>
|
||
<span className="text-[10px] text-slate-400 font-medium">{m.label}</span>
|
||
<span className="text-[10px] text-slate-300">{m.einsaetze} Eins.</span>
|
||
</div>
|
||
))}
|
||
</div>
|
||
</div>
|
||
|
||
{/* ── Gantt-Kalender ───────────────────────────────────────────── */}
|
||
<div className="bg-white border border-slate-200 p-5">
|
||
<div className="flex items-baseline justify-between gap-4 mb-4">
|
||
<h2 className="font-semibold text-slate-900">
|
||
Vermietungsplanung – aktuelle & nächste 3 Monate
|
||
</h2>
|
||
<span className="text-xs text-slate-400 whitespace-nowrap">
|
||
{planDaten.filter((p) => p.anfrage_status !== "abgelehnt").length} Positionen
|
||
</span>
|
||
</div>
|
||
<GanttKalender planDaten={planDaten} heuteISO={heuteISO} />
|
||
</div>
|
||
|
||
{/* ── Auslastungstabelle pro Maschine ─────────────────────────── */}
|
||
<div className="bg-white border border-slate-200 p-5">
|
||
<h2 className="font-semibold text-slate-900 mb-4">
|
||
Auslastung pro Maschine
|
||
<span className="ml-2 text-sm font-normal text-slate-400">letzte 12 Monate</span>
|
||
</h2>
|
||
|
||
{maschinenStats.length === 0 ? (
|
||
<p className="text-sm text-slate-400 py-4 text-center">
|
||
Noch keine bestätigten Vermietungen vorhanden.
|
||
</p>
|
||
) : (
|
||
<div className="overflow-x-auto">
|
||
<table className="w-full text-sm">
|
||
<thead>
|
||
<tr className="border-b border-slate-200 text-xs text-slate-500 uppercase tracking-wide">
|
||
<th className="text-left py-2 pr-4 font-medium">Maschine</th>
|
||
<th className="text-left py-2 pr-4 font-medium hidden sm:table-cell">Kategorie</th>
|
||
<th className="text-right py-2 pr-4 font-medium">Einsätze</th>
|
||
<th className="text-right py-2 pr-4 font-medium">Miettage</th>
|
||
<th className="py-2 pr-4 font-medium hidden md:table-cell" style={{ minWidth: 120 }}>
|
||
Auslastung
|
||
</th>
|
||
<th className="text-right py-2 font-medium">Umsatz netto</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
{maschinenStats.map((m, i) => (
|
||
<tr
|
||
key={m.name}
|
||
className={`border-b border-slate-100 last:border-0 ${
|
||
i % 2 === 0 ? "bg-white" : "bg-slate-50/50"
|
||
}`}
|
||
>
|
||
<td className="py-2.5 pr-4 font-medium text-slate-800">{m.name}</td>
|
||
<td className="py-2.5 pr-4 text-slate-500 hidden sm:table-cell text-xs">
|
||
{m.kategorie}
|
||
</td>
|
||
<td className="py-2.5 pr-4 text-right font-mono text-slate-700">
|
||
{m.einsaetze}×
|
||
</td>
|
||
<td className="py-2.5 pr-4 text-right font-mono font-semibold text-[#1c1917]">
|
||
{fmt(m.tage)}
|
||
</td>
|
||
<td className="py-2.5 pr-4 hidden md:table-cell">
|
||
<div className="flex items-center gap-2">
|
||
<div className="flex-1 h-1.5 bg-slate-100 rounded-full overflow-hidden">
|
||
<div
|
||
className="h-full bg-[#f7d334] rounded-full"
|
||
style={{ width: balkenBreite(m.tage, maxTage) }}
|
||
/>
|
||
</div>
|
||
<span className="text-xs text-slate-400 w-8 text-right">
|
||
{Math.round((m.tage / maxTage) * 100)}%
|
||
</span>
|
||
</div>
|
||
</td>
|
||
<td className="py-2.5 text-right font-mono text-slate-700">
|
||
{m.umsatz > 0 ? `${fmt(m.umsatz)} €` : "–"}
|
||
</td>
|
||
</tr>
|
||
))}
|
||
</tbody>
|
||
<tfoot>
|
||
<tr className="border-t-2 border-[#f7d334]">
|
||
<td className="py-3 pr-4 font-bold text-slate-900" colSpan={2}>
|
||
Gesamt
|
||
</td>
|
||
<td className="py-3 pr-4 text-right font-mono font-bold text-slate-900">
|
||
{fmt(maschinenStats.reduce((s, m) => s + m.einsaetze, 0))}×
|
||
</td>
|
||
<td className="py-3 pr-4 text-right font-mono font-bold text-[#1c1917]">
|
||
{fmt(mietTageGesamt)}
|
||
</td>
|
||
<td className="hidden md:table-cell" />
|
||
<td className="py-3 text-right font-mono font-bold text-[#1c1917]">
|
||
{fmt(umsatzGesamt)} €
|
||
</td>
|
||
</tr>
|
||
</tfoot>
|
||
</table>
|
||
</div>
|
||
)}
|
||
</div>
|
||
</div>
|
||
</div>
|
||
);
|
||
}
|