Kevin
03416b0afe
F1: partner_paid_at wird beim Rechnungskauf (paymentStatusPaidAction mit paid=false) nicht mehr gesetzt, sondern erst bei der Rechnungszahlung; der Backfill nimmt das Aktivierungsdatum nur noch ohne jede Registrierungsbestellung (offene Rechnung bleibt leer). F2: Snapshots speichern je Linie new_partner_ids, die Neupartner-Liste zeigt genau diese; aeltere Snapshots ohne IDs rechnen mit der damaligen Regel (Registrierungsmonat, scopeNewPartnerInByActiveDate), damit Kachel und Liste uebereinstimmen. Keine Payload-Versionsaenderung, kein Neuaufbau noetig. F3: Liefertag-/Termin-Aenderung waehrend einer Pause zieht paused_until mit; eine Ausfuehrung loescht auch den paused_by_*-Grund. F4: expiredCount der Firstlines-Seite (Controller, View, Sprachen) zum bereits committeten Test nachgezogen. F5: Backfills schreiben ohne updated_at (toBase()->update, timestamps=false). Kuendigungsbestaetigung abo_cancel_confirm wird im Selfservice serverseitig verlangt. AboStatusExplainer laedt die Historie je Abo und Aktion nur einmal. Stellungnahme und Deploy-Folgen im Audit-Dokument, Regressionstests je Befund. Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
1460 lines
58 KiB
PHP
1460 lines
58 KiB
PHP
<?php
|
||
|
||
namespace App\Services\Backoffice;
|
||
|
||
use App\Models\BackofficeStatisticsSnapshot;
|
||
use App\Models\ShoppingOrderItem;
|
||
use App\Models\UserAbo;
|
||
use App\Models\UserBusiness;
|
||
use App\Models\UserBusinessStructure;
|
||
use App\Models\UserLevel;
|
||
use App\Models\UserSalesVolume;
|
||
use App\Providers\AppServiceProvider;
|
||
use App\Services\BusinessPlan\BusinessUserItemOptimized;
|
||
use App\User;
|
||
use Carbon\Carbon;
|
||
use Illuminate\Database\Eloquent\Builder;
|
||
use Illuminate\Support\Collection;
|
||
|
||
/**
|
||
* Monatsuebersicht der Backoffice-Statistik.
|
||
*
|
||
* ## Warum abgeschlossene Monate eingefroren sind (Kundenentscheidung 2026-09-10)
|
||
*
|
||
* Am ersten Tag des Folgemonats laeuft die Monatsabrechnung: Provisionen werden
|
||
* berechnet, Gutschriften ausgestellt, der Monat ist damit kaufmaennisch
|
||
* abgeschlossen. Aus demselben Grund fliessen auch keine Stornos mehr rueckwirkend
|
||
* in einen abgeschlossenen Monat ({@see SalesPointsVolume::isStornoMonthOpen()}).
|
||
*
|
||
* Die Statistik muss dieser Logik folgen, denn die Team-Struktur ist beweglich:
|
||
* Teampartner kommen dazu, wechseln den Sponsor oder fallen weg. Wuerde ein
|
||
* vergangener Monat weiter aus heutigen Daten gerechnet, saehe eine Beraterin dort
|
||
* spaeter andere Zahlen als in der Abrechnung, die sie fuer diesen Monat erhalten
|
||
* hat — und die Ursache waere von aussen nicht erkennbar.
|
||
*
|
||
* Eingefroren wird deshalb der **gesamte** Monatsstand, nicht nur die Summen:
|
||
* Punkte, Umsaetze, Abo-Bestaende, die Mitglieder je Linie (`user_ids`) und die
|
||
* Abos je Linie (`abo_ids`). Aus diesen beiden ID-Listen baut
|
||
* {@see BackofficeDrilldownService} die Detaillisten, sodass jede verlinkte Liste
|
||
* zu der Zahl passt, aus der sie aufgerufen wurde.
|
||
*
|
||
* Bewusst NICHT eingefroren werden Stammdaten wie Name oder Karrierestufe: sie
|
||
* werden zur Anzeigezeit gelesen. Eine Umbenennung soll rueckwirkend sichtbar sein,
|
||
* eine Strukturaenderung nicht.
|
||
*/
|
||
class BackofficeDashboardService
|
||
{
|
||
/**
|
||
* Payload-Schema der gespeicherten Monats-Snapshots.
|
||
*
|
||
* 2 — Linienzeilen tragen zusaetzlich `abo_ids`, damit die Detaillisten eines
|
||
* abgeschlossenen Monats aus demselben eingefrorenen Stand kommen wie die
|
||
* Zahl darueber.
|
||
* 3 — Payload traegt `tree` (Baum-Definition) und `storno_floor_points`;
|
||
* `total_points` sind seitdem die Provisionspunkte (TP + Shop, Knotenregel
|
||
* des Provisionsbaums) statt der Zeilensumme inkl. Nur-KP-Buchungen.
|
||
*/
|
||
public const PAYLOAD_VERSION = 3;
|
||
|
||
/**
|
||
* Baum-Definitionen der Statistik (Kundenentscheid 2026-09-14, K11/F4):
|
||
*
|
||
* - `commission` (Standard): der Provisionsbaum — dieselben Mitglieder, mit denen
|
||
* der TreeCalcBot die Team-Struktur und die Provision rechnet. Damit zeigen
|
||
* Team-Statistik und Team-Struktur dieselben Punkte.
|
||
* - `downline`: die komplette Downline (jeder nicht geloeschte Teampartner,
|
||
* auch ohne Mitgliedsebene/Abrechnungskonto, ohne Kappen des Unterbaums) —
|
||
* die fruehere Standardsicht, per Umschalter weiterhin verfuegbar, damit
|
||
* z. B. noch nicht freigeschaltete Partner nachverfolgbar bleiben.
|
||
*/
|
||
public const TREE_COMMISSION = 'commission';
|
||
|
||
public const TREE_DOWNLINE = 'downline';
|
||
|
||
/**
|
||
* Linien-Obergrenze des Provisionsbaums: {@see BusinessUserItemOptimized::readParentsBusinessUsers()}
|
||
* bricht bei Tiefe > 20 ab; der Knoten auf Tiefe 20 laedt seine Kinder also noch —
|
||
* das ist Linie 21.
|
||
*/
|
||
public const COMMISSION_TREE_MAX_LINES = 21;
|
||
|
||
private const MAX_DEPTH_SAFETY_LIMIT = 100;
|
||
|
||
private const LEADER_LEVEL_NAME = 'Vertriebsleiter';
|
||
|
||
/**
|
||
* Prozess-Memo der Baum-Traversierung: ein Dashboard-Request braucht die
|
||
* Downline mehrfach (KPIs, Payline, Top 3, Prognose) — ohne Memo liefe die
|
||
* BFS pro Aufruf neu. Der Service ist als Singleton registriert
|
||
* ({@see AppServiceProvider}); pro Root-User wird einmal
|
||
* traversiert, Batch-Läufe über viele User bleiben durch den Key getrennt.
|
||
*
|
||
* @var array<int, array<int, Collection<int, User>>>
|
||
*/
|
||
private array $lineBucketsMemo = [];
|
||
|
||
/**
|
||
* Memo des Provisionsbaums je Root und Monat (das Monatsende entscheidet ueber
|
||
* `active_date`), abgeleitet aus {@see $lineBucketsMemo}.
|
||
*
|
||
* @var array<string, array<int, Collection<int, User>>>
|
||
*/
|
||
private array $commissionLineBucketsMemo = [];
|
||
|
||
/**
|
||
* @return string[]
|
||
*/
|
||
public static function trees(): array
|
||
{
|
||
return [self::TREE_COMMISSION, self::TREE_DOWNLINE];
|
||
}
|
||
|
||
/**
|
||
* @return array<string, string>
|
||
*/
|
||
public static function treeLabels(): array
|
||
{
|
||
return [
|
||
self::TREE_COMMISSION => 'Provisionsbaum (wie Team-Struktur)',
|
||
self::TREE_DOWNLINE => 'Gesamte Downline (inkl. nicht provisionsberechtigte Berater)',
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Unbekannte oder leere Werte (alte Links, manipulierte Parameter) fallen auf
|
||
* den Standard zurueck.
|
||
*/
|
||
public static function normalizeTree(?string $tree): string
|
||
{
|
||
return in_array($tree, self::trees(), true) ? $tree : self::TREE_COMMISSION;
|
||
}
|
||
|
||
/**
|
||
* @return array<string, string>
|
||
*/
|
||
public function metricLabels(): array
|
||
{
|
||
return [
|
||
'consultants' => 'Berater',
|
||
'new_partners' => 'Neupartner',
|
||
'team_partner_abos' => 'Teamabos',
|
||
'team_customer_abos' => 'Teamkundenabos',
|
||
'own_points' => 'Eigenpunkte',
|
||
'external_points' => 'Externe Kundenpunkte',
|
||
'customer_abo_points' => 'Kundenabo-Punkte',
|
||
'customer_single_order_points' => 'Einzelbestellungs-Punkte',
|
||
'customer_other_points' => 'Sonstige Kundenpunkte',
|
||
'total_points' => 'Gesamtpunkte',
|
||
'shop_1000' => '1000 Punkte Shop',
|
||
];
|
||
}
|
||
|
||
/**
|
||
* @return array{month: int, year: int, metric_labels: array<string, string>, lines: array<int, array<string, mixed>>, totals: array<string, mixed>}
|
||
*/
|
||
public function overview(User $user, int $month, int $year, string $tree = self::TREE_COMMISSION): array
|
||
{
|
||
$tree = self::normalizeTree($tree);
|
||
|
||
if ($this->isClosedMonth($month, $year)) {
|
||
$snapshot = BackofficeStatisticsSnapshot::query()
|
||
->where('user_id', $user->id)
|
||
->where('month', $month)
|
||
->where('year', $year)
|
||
->where('tree', $tree)
|
||
->first();
|
||
|
||
if ($snapshot) {
|
||
// Abgeschlossener Monat: der gespeicherte Stand wird unveraendert
|
||
// ausgeliefert — siehe Klassenkommentar. Frueher wurden hier die
|
||
// Abo-Bestaende live nachgerechnet; das hat den eingefrorenen Monat
|
||
// mit heutigen Zahlen vermischt.
|
||
return $this->withMeta(
|
||
$snapshot->payload,
|
||
'snapshot',
|
||
$tree,
|
||
$snapshot->calculated_at?->format('d.m.Y H:i')
|
||
);
|
||
}
|
||
}
|
||
|
||
return $this->withMeta($this->buildOverview($user, $month, $year, $tree), 'live', $tree);
|
||
}
|
||
|
||
/**
|
||
* Der Monatsstand immer frisch aus den heutigen Daten — auch fuer abgeschlossene
|
||
* Monate (Mess-/Vergleichslaeufe, Snapshot-Aufbau). Die Seite selbst geht ueber
|
||
* {@see overview()}, damit eingefrorene Monate eingefroren bleiben.
|
||
*
|
||
* @return array<string, mixed>
|
||
*/
|
||
public function liveOverview(User $user, int $month, int $year, string $tree = self::TREE_COMMISSION): array
|
||
{
|
||
return $this->withMeta($this->buildOverview($user, $month, $year, self::normalizeTree($tree)), 'live', $tree);
|
||
}
|
||
|
||
/**
|
||
* Baum-Memo verwerfen — nötig, wenn sich die Team-Struktur innerhalb eines
|
||
* langlebigen Prozesses ändert (Batch-Kommandos, Tests).
|
||
*/
|
||
public function flushLineBucketsMemo(?int $rootUserId = null): void
|
||
{
|
||
if ($rootUserId === null) {
|
||
$this->lineBucketsMemo = [];
|
||
$this->commissionLineBucketsMemo = [];
|
||
|
||
return;
|
||
}
|
||
|
||
unset($this->lineBucketsMemo[$rootUserId]);
|
||
|
||
foreach (array_keys($this->commissionLineBucketsMemo) as $key) {
|
||
if (str_starts_with($key, $rootUserId.':')) {
|
||
unset($this->commissionLineBucketsMemo[$key]);
|
||
}
|
||
}
|
||
}
|
||
|
||
public function storeSnapshot(User $user, int $month, int $year, string $tree = self::TREE_COMMISSION): BackofficeStatisticsSnapshot
|
||
{
|
||
$tree = self::normalizeTree($tree);
|
||
|
||
// Frisch traversieren: der Snapshot-Cron läuft lange und über viele User —
|
||
// ein Memo aus früheren Aufrufen desselben Roots wäre potenziell veraltet.
|
||
$this->flushLineBucketsMemo((int) $user->id);
|
||
|
||
$payload = $this->buildOverview($user, $month, $year, $tree);
|
||
|
||
return BackofficeStatisticsSnapshot::query()->updateOrCreate(
|
||
[
|
||
'user_id' => $user->id,
|
||
'year' => $year,
|
||
'month' => $month,
|
||
'tree' => $tree,
|
||
],
|
||
[
|
||
'payload' => $payload,
|
||
'calculated_at' => now(),
|
||
]
|
||
);
|
||
}
|
||
|
||
/**
|
||
* @return array{month: int, year: int, metric_labels: array<string, string>, lines: array<int, array<string, mixed>>, totals: array<string, mixed>}
|
||
*/
|
||
private function buildOverview(User $user, int $month, int $year, string $tree): array
|
||
{
|
||
$lineBuckets = $this->lineBucketsFor((int) $user->id, $tree, $month, $year);
|
||
$recruiter = $this->recruiterRanking($user, $lineBuckets, $month, $year);
|
||
$lines = [];
|
||
$totals = $this->emptyLine(0, []);
|
||
|
||
foreach ($lineBuckets as $line => $users) {
|
||
$row = $this->buildLineRow($line, $users, $month, $year);
|
||
$lines[$line] = $row;
|
||
$totals = $this->addToTotals($totals, $row);
|
||
}
|
||
|
||
// 1000-Punkte-Shop: der VIP selbst zählt mit (Kundenwunsch), obwohl er in
|
||
// keiner Linie steckt — nur diese Kennzahl bekommt ihn zusätzlich.
|
||
$totals['shop_1000'] += $this->shop1000Count([(int) $user->id], $month, $year);
|
||
|
||
$totals['label'] = 'Summe';
|
||
|
||
return [
|
||
'month' => $month,
|
||
'year' => $year,
|
||
// Schema-Version des Payloads: ab 2 enthalten die Linienzeilen `abo_ids`,
|
||
// aus denen die Detaillisten eines abgeschlossenen Monats gebaut werden.
|
||
// Aeltere Snapshots bleiben lesbar, ihre Detaillisten fallen auf die
|
||
// Monatsend-Rueckrechnung zurueck ({@see BackofficeDrilldownService}).
|
||
'payload_version' => self::PAYLOAD_VERSION,
|
||
'tree' => $tree,
|
||
'tree_source' => ($tree === self::TREE_COMMISSION && $this->storedStructureFor($month, $year) !== null) ? 'stored_structure' : 'live',
|
||
'metric_labels' => $this->metricLabels(),
|
||
'lines' => $lines,
|
||
'totals' => $totals,
|
||
'non_payline_points' => $this->nonPaylinePointsSum($totals['user_ids'], $month, $year),
|
||
'storno_floor_points' => $totals['storno_floor_points'],
|
||
'new_customer_abos' => $this->newCustomerAboRanking($user, $lineBuckets, $month, $year),
|
||
'leader_abos' => $this->leaderAboOverview($lineBuckets),
|
||
'top_customer_abos' => $this->topCustomerAboRanking($user, $lineBuckets, $month, $year),
|
||
'recruiter_ranking' => $recruiter['rows'],
|
||
// Eigener Payload-Schlüssel statt geänderter Listenform: ältere
|
||
// Snapshots kennen nur recruiter_ranking und bleiben lesbar.
|
||
'recruiter_totals' => $recruiter['totals'],
|
||
];
|
||
}
|
||
|
||
/**
|
||
* @param array<string, mixed> $overview
|
||
* @return array<string, mixed>
|
||
*/
|
||
private function withMeta(array $overview, string $source, string $tree, ?string $calculatedAt = null): array
|
||
{
|
||
$overview['_meta'] = [
|
||
'source' => $source,
|
||
'source_label' => $source === 'snapshot' ? 'Snapshot' : 'Live',
|
||
'calculated_at' => $calculatedAt,
|
||
'tree' => $tree,
|
||
'tree_label' => self::treeLabels()[$tree] ?? $tree,
|
||
];
|
||
|
||
return $overview;
|
||
}
|
||
|
||
private function isClosedMonth(int $month, int $year): bool
|
||
{
|
||
return Carbon::create($year, $month, 1)->endOfMonth()->lt(now()->startOfMonth());
|
||
}
|
||
|
||
/**
|
||
* @return array<int, Collection<int, User>>
|
||
*/
|
||
public function lineBuckets(int $rootUserId): array
|
||
{
|
||
if (isset($this->lineBucketsMemo[$rootUserId])) {
|
||
return $this->lineBucketsMemo[$rootUserId];
|
||
}
|
||
|
||
$lineBuckets = [];
|
||
$currentSponsorIds = [$rootUserId];
|
||
$visitedUserIds = [$rootUserId];
|
||
|
||
for ($line = 1; $line <= self::MAX_DEPTH_SAFETY_LIMIT && $currentSponsorIds !== []; $line++) {
|
||
$users = User::query()
|
||
->with(['account', 'user_level'])
|
||
->whereIn('m_sponsor', $currentSponsorIds)
|
||
->whereNotIn('id', $visitedUserIds)
|
||
->whereColumn('id', '!=', 'm_sponsor')
|
||
->whereNull('deleted_at')
|
||
->get();
|
||
|
||
if ($users->isEmpty()) {
|
||
break;
|
||
}
|
||
|
||
$lineBuckets[$line] = $users;
|
||
$currentSponsorIds = $users->pluck('id')->all();
|
||
$visitedUserIds = array_merge($visitedUserIds, $currentSponsorIds);
|
||
}
|
||
|
||
return $this->lineBucketsMemo[$rootUserId] = $lineBuckets;
|
||
}
|
||
|
||
/**
|
||
* Die Linien des Provisionsbaums: {@see lineBuckets()} gefiltert und gekappt
|
||
* nach denselben Regeln, mit denen der TreeCalcBot seine Struktur laedt
|
||
* ({@see BusinessUserItemOptimized::readParentsBusinessUsers()}). Ein Knoten,
|
||
* der die Bedingungen nicht erfuellt, faellt samt seinem GESAMTEN Unterbaum
|
||
* heraus — genau wie in der Provisionsberechnung, wo unter einem
|
||
* ausgefilterten Sponsor nicht weitergelesen wird.
|
||
*
|
||
* Die BFS-Reihenfolge von lineBuckets() garantiert, dass der Sponsor immer
|
||
* schon entschieden ist, wenn sein Teampartner drankommt.
|
||
*
|
||
* @return array<int, Collection<int, User>>
|
||
*/
|
||
public function commissionLineBuckets(int $rootUserId, int $month, int $year): array
|
||
{
|
||
$memoKey = $rootUserId.':'.$year.'-'.$month;
|
||
|
||
if (isset($this->commissionLineBucketsMemo[$memoKey])) {
|
||
return $this->commissionLineBucketsMemo[$memoKey];
|
||
}
|
||
|
||
// Abgeschlossener Monat mit gespeichertem Provisionslauf: die damals
|
||
// verprovisionierte Struktur ist massgeblich, nicht der heutige Baum. Sonst
|
||
// laufen Statistik und Struktur wieder auseinander, sobald sich seit dem
|
||
// Monatsabschluss ein Sponsor geaendert hat oder ein Berater reaktiviert wurde.
|
||
$storedStructure = $this->storedStructureFor($month, $year);
|
||
|
||
if ($storedStructure !== null) {
|
||
return $this->commissionLineBucketsMemo[$memoKey] = $this->storedCommissionLineBuckets($rootUserId, $storedStructure);
|
||
}
|
||
|
||
$endOfMonth = Carbon::create($year, $month, 1)->endOfMonth();
|
||
$buckets = [];
|
||
$pruned = [];
|
||
|
||
foreach ($this->lineBuckets($rootUserId) as $line => $users) {
|
||
if ($line > self::COMMISSION_TREE_MAX_LINES) {
|
||
break;
|
||
}
|
||
|
||
$kept = $users->filter(function (User $member) use (&$pruned, $endOfMonth): bool {
|
||
if (isset($pruned[(int) $member->m_sponsor]) || ! self::countsInCommissionTree($member, $endOfMonth)) {
|
||
$pruned[(int) $member->id] = true;
|
||
|
||
return false;
|
||
}
|
||
|
||
return true;
|
||
})->values();
|
||
|
||
if ($kept->isEmpty()) {
|
||
// Ohne Mitglieder auf dieser Linie kann es auch darunter keine geben.
|
||
break;
|
||
}
|
||
|
||
$buckets[$line] = $kept;
|
||
}
|
||
|
||
return $this->commissionLineBucketsMemo[$memoKey] = $buckets;
|
||
}
|
||
|
||
/**
|
||
* Der abgeschlossene Provisionslauf eines Monats — nur fuer abgeschlossene
|
||
* Monate: im laufenden Monat rechnet die Statistik live, auch wenn der
|
||
* Nachtlauf schon eine Struktur gespeichert hat (Buchungen des Tages sollen
|
||
* sofort sichtbar sein; die Struktur-Seite holt sie am Folgetag nach).
|
||
*/
|
||
public function storedStructureFor(int $month, int $year): ?UserBusinessStructure
|
||
{
|
||
if (! $this->isClosedMonth($month, $year)) {
|
||
return null;
|
||
}
|
||
|
||
return UserBusinessStructure::query()
|
||
->where('year', $year)
|
||
->where('month', $month)
|
||
->where('completed', true)
|
||
->first();
|
||
}
|
||
|
||
/**
|
||
* Linien aus der gespeicherten Struktur: `user_businesses` traegt je Mitglied
|
||
* des Provisionsbaums den Sponsor zum Zeitpunkt des Laufs; wer damals nicht im
|
||
* Baum stand, hat keine Zeile. Ein BFS ueber diese Zeilen ergibt exakt die
|
||
* Struktur, mit der der Monat verprovisioniert wurde.
|
||
*
|
||
* @return array<int, Collection<int, User>>
|
||
*/
|
||
private function storedCommissionLineBuckets(int $rootUserId, UserBusinessStructure $structure): array
|
||
{
|
||
$childrenBySponsor = [];
|
||
|
||
UserBusiness::query()
|
||
->where('month', $structure->month)
|
||
->where('year', $structure->year)
|
||
->where('b_structure_id', $structure->id)
|
||
->orderBy('user_id')
|
||
->get(['user_id', 'sponsor'])
|
||
->each(function (UserBusiness $row) use (&$childrenBySponsor): void {
|
||
$sponsorId = (int) ($row->sponsor->user_id ?? 0);
|
||
|
||
if ($sponsorId > 0 && $sponsorId !== (int) $row->user_id) {
|
||
$childrenBySponsor[$sponsorId][] = (int) $row->user_id;
|
||
}
|
||
});
|
||
|
||
$idsByLine = [];
|
||
$visited = [$rootUserId => true];
|
||
$currentIds = [$rootUserId];
|
||
|
||
for ($line = 1; $line <= self::COMMISSION_TREE_MAX_LINES && $currentIds !== []; $line++) {
|
||
$nextIds = [];
|
||
|
||
foreach ($currentIds as $parentId) {
|
||
foreach ($childrenBySponsor[$parentId] ?? [] as $childId) {
|
||
if (isset($visited[$childId])) {
|
||
continue;
|
||
}
|
||
|
||
$visited[$childId] = true;
|
||
$nextIds[] = $childId;
|
||
}
|
||
}
|
||
|
||
if ($nextIds === []) {
|
||
break;
|
||
}
|
||
|
||
$idsByLine[$line] = $nextIds;
|
||
$currentIds = $nextIds;
|
||
}
|
||
|
||
if ($idsByLine === []) {
|
||
return [];
|
||
}
|
||
|
||
$users = User::query()
|
||
->with(['account', 'user_level'])
|
||
->whereIn('id', array_merge(...array_values($idsByLine)))
|
||
->whereNull('deleted_at')
|
||
->get()
|
||
->keyBy('id');
|
||
|
||
$buckets = [];
|
||
|
||
foreach ($idsByLine as $line => $ids) {
|
||
$buckets[$line] = collect($ids)
|
||
->map(fn (int $id) => $users->get($id))
|
||
->filter()
|
||
->values();
|
||
}
|
||
|
||
return $buckets;
|
||
}
|
||
|
||
/**
|
||
* Dieselben Bedingungen, mit denen der TreeCalcBot seine Struktur laedt
|
||
* ({@see BusinessUserItemOptimized::readParentsBusinessUsers()}): kein
|
||
* System-/Adminkonto, Mitgliedsebene und Abrechnungskonto gesetzt (das
|
||
* Ablaufdatum spielt dort KEINE Rolle), bis zum Monatsende aktiv geworden.
|
||
* Geloeschte User sind schon in lineBuckets() ausgeschlossen.
|
||
*/
|
||
public static function countsInCommissionTree(User $member, Carbon $endOfMonth): bool
|
||
{
|
||
if ((int) $member->id === 1 || $member->admin === null || (int) $member->admin >= 4) {
|
||
return false;
|
||
}
|
||
|
||
if ($member->m_level === null || $member->payment_account === null || $member->active_date === null) {
|
||
return false;
|
||
}
|
||
|
||
return Carbon::parse($member->active_date)->lte($endOfMonth);
|
||
}
|
||
|
||
/**
|
||
* @return array<int, Collection<int, User>>
|
||
*/
|
||
public function lineBucketsFor(int $rootUserId, string $tree, int $month, int $year): array
|
||
{
|
||
return self::normalizeTree($tree) === self::TREE_DOWNLINE
|
||
? $this->lineBuckets($rootUserId)
|
||
: $this->commissionLineBuckets($rootUserId, $month, $year);
|
||
}
|
||
|
||
/**
|
||
* Mitglieder einer Linie in der gewaehlten Baum-Definition; Linie 0 = das ganze Team.
|
||
*
|
||
* @return int[]
|
||
*/
|
||
public function lineUserIds(int $rootUserId, int $line, int $month, int $year, string $tree = self::TREE_COMMISSION): array
|
||
{
|
||
if ($line < 0) {
|
||
return [];
|
||
}
|
||
|
||
$buckets = $this->lineBucketsFor($rootUserId, $tree, $month, $year);
|
||
|
||
if ($line === 0) {
|
||
return collect($buckets)
|
||
->flatMap(fn (Collection $users) => $users->pluck('id'))
|
||
->map(fn ($id) => (int) $id)
|
||
->values()
|
||
->all();
|
||
}
|
||
|
||
return ($buckets[$line] ?? collect())->pluck('id')->map(fn ($id) => (int) $id)->all();
|
||
}
|
||
|
||
/**
|
||
* @return int[]
|
||
*/
|
||
public function teamUserIds(int $rootUserId): array
|
||
{
|
||
return collect($this->lineBuckets($rootUserId))
|
||
->flatMap(fn (Collection $users) => $users->pluck('id'))
|
||
->map(fn ($id) => (int) $id)
|
||
->values()
|
||
->all();
|
||
}
|
||
|
||
/**
|
||
* IDs der gesamten Downline, aber nur aktive Berater
|
||
* (gleiche Definition wie die "Berater"-Spalte der Backoffice-Statistik).
|
||
*
|
||
* @return int[]
|
||
*/
|
||
public function activeTeamUserIds(int $rootUserId): array
|
||
{
|
||
return collect($this->lineBuckets($rootUserId))
|
||
->flatMap(fn (Collection $users) => $users)
|
||
->filter(fn (User $user) => $this->isActiveConsultant($user))
|
||
->map(fn (User $user) => (int) $user->id)
|
||
->values()
|
||
->all();
|
||
}
|
||
|
||
/**
|
||
* Ein aktiver Berater hat eine Mitgliedsebene und ein gesetztes Abrechnungskonto.
|
||
* Maßgeblich für die "Berater"-Zählung in den Team-Übersichten.
|
||
*/
|
||
public function isActiveConsultant(User $user): bool
|
||
{
|
||
return $user->m_level !== null && $user->payment_account !== null;
|
||
}
|
||
|
||
/**
|
||
* @param Collection<int, User> $users
|
||
* @return array<string, mixed>
|
||
*/
|
||
private function buildLineRow(int $line, Collection $users, int $month, int $year): array
|
||
{
|
||
$userIds = $users->pluck('id')->map(fn ($id) => (int) $id)->all();
|
||
|
||
if ($userIds === []) {
|
||
return $this->emptyLine($line, $userIds);
|
||
}
|
||
|
||
$salesSummary = $this->salesSummary($userIds, $month, $year);
|
||
|
||
// Nicht nur zaehlen, sondern die Abo-IDs mitschreiben: der Snapshot ist der
|
||
// eingefrorene Monatsstand, und die Detailliste hinter der Zahl muss genau
|
||
// diese Abos zeigen — auch wenn eines davon spaeter gekuendigt oder geloescht
|
||
// wird. Ohne die IDs liesse sich die Liste nur aus dem heutigen Bestand
|
||
// rekonstruieren und wuerde von ihrer eigenen Kachel abweichen.
|
||
$partnerAboIds = $this->aboIds($userIds, 'user_id', 'me');
|
||
$customerAboIds = $this->aboIds($userIds, 'member_id', 'ot');
|
||
$partnerAboIdsNew = $this->aboIds($userIds, 'user_id', 'me', $month, $year);
|
||
$customerAboIdsNew = $this->aboIds($userIds, 'member_id', 'ot', $month, $year);
|
||
// Neupartner ebenfalls als IDs festhalten: die Regel (Zahlungsmonat seit
|
||
// 16.09.2026) kann sich aendern, die Liste hinter der eingefrorenen Kachel
|
||
// muss trotzdem genau die damals gezaehlten Partner zeigen (Audit 18.09., F2).
|
||
$newPartnerIds = $this->newPartnerIds($users, $month, $year);
|
||
|
||
return [
|
||
'line' => $line,
|
||
'label' => 'Linie '.$line,
|
||
'user_ids' => $userIds,
|
||
'consultants' => $this->activeConsultants($users),
|
||
'new_partners' => count($newPartnerIds),
|
||
'new_partner_ids' => $newPartnerIds,
|
||
'team_partner_abos' => count($partnerAboIds),
|
||
'team_partner_abos_new' => count($partnerAboIdsNew),
|
||
'team_customer_abos' => count($customerAboIds),
|
||
'team_customer_abos_new' => count($customerAboIdsNew),
|
||
'abo_ids' => [
|
||
'team_partner_abos' => $partnerAboIds,
|
||
'team_partner_abos_new' => $partnerAboIdsNew,
|
||
'team_customer_abos' => $customerAboIds,
|
||
'team_customer_abos_new' => $customerAboIdsNew,
|
||
],
|
||
'own_points' => $salesSummary['own_points'],
|
||
'external_points' => $salesSummary['external_points'],
|
||
'customer_abo_points' => $salesSummary['customer_abo_points'],
|
||
'customer_single_order_points' => $salesSummary['customer_single_order_points'],
|
||
'customer_other_points' => $salesSummary['customer_other_points'],
|
||
'total_points' => $salesSummary['total_points'],
|
||
'storno_floor_points' => $salesSummary['storno_floor_points'],
|
||
'turnover_net' => $salesSummary['turnover_net'],
|
||
'shop_1000' => $this->shop1000Count($userIds, $month, $year),
|
||
];
|
||
}
|
||
|
||
/**
|
||
* @return array<string, mixed>
|
||
*/
|
||
private function emptyLine(int $line, array $userIds): array
|
||
{
|
||
return [
|
||
'line' => $line,
|
||
'label' => $line > 0 ? 'Linie '.$line : 'Summe',
|
||
'user_ids' => $userIds,
|
||
'consultants' => 0,
|
||
'new_partners' => 0,
|
||
'team_partner_abos' => 0,
|
||
'team_partner_abos_new' => 0,
|
||
'team_customer_abos' => 0,
|
||
'team_customer_abos_new' => 0,
|
||
'own_points' => 0.0,
|
||
'external_points' => 0.0,
|
||
'customer_abo_points' => 0.0,
|
||
'customer_single_order_points' => 0.0,
|
||
'customer_other_points' => 0.0,
|
||
'total_points' => 0.0,
|
||
'storno_floor_points' => 0.0,
|
||
'turnover_net' => 0.0,
|
||
'shop_1000' => 0,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* @param array<string, mixed> $totals
|
||
* @param array<string, mixed> $row
|
||
* @return array<string, mixed>
|
||
*/
|
||
private function addToTotals(array $totals, array $row): array
|
||
{
|
||
foreach (array_keys($this->metricLabels()) as $metric) {
|
||
$totals[$metric] += $row[$metric];
|
||
}
|
||
|
||
$totals['turnover_net'] += $row['turnover_net'];
|
||
$totals['storno_floor_points'] += $row['storno_floor_points'] ?? 0.0;
|
||
$totals['team_partner_abos_new'] += $row['team_partner_abos_new'];
|
||
$totals['team_customer_abos_new'] += $row['team_customer_abos_new'];
|
||
$totals['user_ids'] = array_merge($totals['user_ids'], $row['user_ids']);
|
||
|
||
return $totals;
|
||
}
|
||
|
||
/**
|
||
* @param Collection<int, User> $users
|
||
*/
|
||
private function activeConsultants(Collection $users): int
|
||
{
|
||
return $users
|
||
->filter(fn (User $user) => $this->isActiveConsultant($user))
|
||
->count();
|
||
}
|
||
|
||
/**
|
||
* @param Collection<int, User> $users
|
||
*/
|
||
private function newPartners(Collection $users, int $month, int $year): int
|
||
{
|
||
return count($this->newPartnerIds($users, $month, $year));
|
||
}
|
||
|
||
/**
|
||
* @param Collection<int, User> $users
|
||
* @return int[]
|
||
*/
|
||
private function newPartnerIds(Collection $users, int $month, int $year): array
|
||
{
|
||
return $users
|
||
->filter(fn (User $user) => $user->isNewPartnerIn($month, $year))
|
||
->pluck('id')
|
||
->map(fn ($id) => (int) $id)
|
||
->values()
|
||
->all();
|
||
}
|
||
|
||
private function activeAboQuery(): Builder
|
||
{
|
||
return UserAbo::query()
|
||
->where('active', true)
|
||
->whereNotIn('status', [4, 5, 6]);
|
||
}
|
||
|
||
/**
|
||
* IDs der aktiven Abos einer Mitgliedermenge. Mit Monat/Jahr nur die in diesem
|
||
* Monat gestarteten ("+N neu").
|
||
*
|
||
* @param int[] $userIds
|
||
* @return int[]
|
||
*/
|
||
private function aboIds(array $userIds, string $userColumn, string $isFor, ?int $month = null, ?int $year = null): array
|
||
{
|
||
if ($userIds === []) {
|
||
return [];
|
||
}
|
||
|
||
$query = $this->activeAboQuery()
|
||
->whereIn($userColumn, $userIds)
|
||
->where('is_for', $isFor);
|
||
|
||
if ($month !== null && $year !== null) {
|
||
$query->whereBetween('start_date', [
|
||
Carbon::create($year, $month, 1)->startOfMonth(),
|
||
Carbon::create($year, $month, 1)->endOfMonth(),
|
||
]);
|
||
}
|
||
|
||
return $query->pluck('id')->map(fn ($id) => (int) $id)->all();
|
||
}
|
||
|
||
/**
|
||
* @param int[] $userIds
|
||
*/
|
||
private function newAboCount(array $userIds, string $userColumn, string $isFor, int $month, int $year): int
|
||
{
|
||
return count($this->aboIds($userIds, $userColumn, $isFor, $month, $year));
|
||
}
|
||
|
||
/**
|
||
* @param int[] $userIds
|
||
* Die Kennzahl `total_points` („Umsatz (Punkte)") sind die Provisionspunkte mit
|
||
* der Knotenregel des Provisionsbaums ({@see SalesVolumePoints::floorPerUser()}) —
|
||
* dieselbe Zahl wie die Linien-Punkte der Team-Struktur. Die Aufteilung
|
||
* (Eigen-/Kundenpunkte) bleibt die reine Zeilensumme; `storno_floor_points`
|
||
* weist aus, was die Knotenregel nicht abgezogen hat.
|
||
* @return array{own_points: float, external_points: float, customer_abo_points: float, customer_single_order_points: float, customer_other_points: float, total_points: float, storno_floor_points: float, turnover_net: float}
|
||
*/
|
||
private function salesSummary(array $userIds, int $month, int $year): array
|
||
{
|
||
$summary = UserSalesVolume::query()
|
||
->leftJoin('shopping_orders', 'shopping_orders.id', '=', 'user_sales_volumes.shopping_order_id')
|
||
->whereIn('user_id', $userIds)
|
||
->where('month', $month)
|
||
->where('year', $year)
|
||
->selectRaw(SalesVolumePoints::ownPoints().' as own_points')
|
||
->selectRaw(SalesVolumePoints::externalPoints().' as external_points')
|
||
->selectRaw(SalesVolumePoints::customerAboPoints().' as customer_abo_points')
|
||
->selectRaw(SalesVolumePoints::customerSingleOrderPoints().' as customer_single_order_points')
|
||
->selectRaw(SalesVolumePoints::customerOtherPoints().' as customer_other_points')
|
||
->selectRaw(SalesVolumePoints::turnoverNet().' as turnover_net')
|
||
->first();
|
||
|
||
$commission = SalesVolumePoints::floorPerUser(SalesVolumePoints::commissionPointsByUser($userIds, $month, $year));
|
||
|
||
return [
|
||
'own_points' => (float) ($summary->own_points ?? 0),
|
||
'external_points' => (float) ($summary->external_points ?? 0),
|
||
'customer_abo_points' => (float) ($summary->customer_abo_points ?? 0),
|
||
'customer_single_order_points' => (float) ($summary->customer_single_order_points ?? 0),
|
||
'customer_other_points' => (float) ($summary->customer_other_points ?? 0),
|
||
'total_points' => $commission['points'],
|
||
'storno_floor_points' => $commission['storno_floor'],
|
||
'turnover_net' => (float) ($summary->turnover_net ?? 0),
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Summe der Punkte des Teams, die nicht in die Payline fließen (nur-KP-Einträge
|
||
* wie Neupartner-Einschreibungspunkte). Seit die Kennzahl „Umsatz (Punkte)" die
|
||
* Provisionspunkte zeigt, sind sie dort NICHT mehr enthalten — die Zahl erklaert
|
||
* die Differenz zu den Eigenpunkten der Aufteilung.
|
||
*
|
||
* @param int[] $userIds
|
||
*/
|
||
private function nonPaylinePointsSum(array $userIds, int $month, int $year): float
|
||
{
|
||
if ($userIds === []) {
|
||
return 0.0;
|
||
}
|
||
|
||
return (float) UserSalesVolume::query()
|
||
->whereIn('user_id', $userIds)
|
||
->where('month', $month)
|
||
->where('year', $year)
|
||
->selectRaw(SalesVolumePoints::nonPaylinePoints().' as points')
|
||
->value('points');
|
||
}
|
||
|
||
/**
|
||
* Anzahl der Neupartner (aktiver Account, im Monat aktiv geworden).
|
||
* Konsistent mit der Drilldown-Liste {@see BackofficeDrilldownService::newPartnerRows()}.
|
||
*
|
||
* @param int[] $userIds
|
||
*/
|
||
public function newPartnersCount(array $userIds, int $month, int $year): int
|
||
{
|
||
if ($userIds === []) {
|
||
return 0;
|
||
}
|
||
|
||
return User::query()
|
||
->whereIn('id', $userIds)
|
||
->newPartnerIn($month, $year)
|
||
->count();
|
||
}
|
||
|
||
/**
|
||
* Ranking: Wer im Team hat im Monat wie viele neue Kundenabos abgeschlossen?
|
||
* Sortiert nach Anzahl absteigend (nicht nach Linie), Linie wird je Berater mitgeliefert,
|
||
* dazu der aktuelle Gesamtbestand an Kundenabos des Beraters.
|
||
* Der Statistik-Nutzer selbst zählt mit (Kundenwunsch, Linie 0 / "ich").
|
||
* Zählweise identisch zu team_customer_abos_new {@see newAboCount()}.
|
||
*
|
||
* @param array<int, Collection<int, User>> $lineBuckets
|
||
* @return array<int, array{user_id: int, name: string, career_level: string, line: int, is_self: bool, abo_count: int, total_abo_count: int}>
|
||
*/
|
||
private function newCustomerAboRanking(User $rootUser, array $lineBuckets, int $month, int $year): array
|
||
{
|
||
$usersByLine = $this->usersWithLine($lineBuckets, $rootUser);
|
||
|
||
if ($usersByLine === []) {
|
||
return [];
|
||
}
|
||
|
||
$startDate = Carbon::create($year, $month, 1)->startOfMonth();
|
||
$endDate = Carbon::create($year, $month, 1)->endOfMonth();
|
||
|
||
$counts = $this->activeAboQuery()
|
||
->whereIn('member_id', array_keys($usersByLine))
|
||
->where('is_for', 'ot')
|
||
->whereBetween('start_date', [$startDate, $endDate])
|
||
->selectRaw('member_id, COUNT(*) as abo_count')
|
||
->groupBy('member_id')
|
||
->pluck('abo_count', 'member_id');
|
||
|
||
$totalCounts = $this->customerAboCounts($counts->keys()->all());
|
||
|
||
return collect($usersByLine)
|
||
->filter(fn (array $entry, int $userId) => $counts->has($userId))
|
||
->map(fn (array $entry, int $userId) => [
|
||
'user_id' => $userId,
|
||
'name' => $this->userName($entry['user']),
|
||
'career_level' => $this->careerLevel($entry['user']),
|
||
'line' => $entry['line'],
|
||
'is_self' => $entry['line'] === 0,
|
||
'abo_count' => (int) $counts->get($userId),
|
||
'total_abo_count' => (int) $totalCounts->get($userId, 0),
|
||
])
|
||
->sortByDesc('abo_count')
|
||
->values()
|
||
->all();
|
||
}
|
||
|
||
/**
|
||
* Abos der Führungskräfte (ab Stufe Vertriebsleiter) im Team: Gesamtzahlen plus
|
||
* Liste aller Führungskräfte inklusive Linie, eigener Abos, Kundenabos sowie
|
||
* Team-Abos und Team-Kundenabos ihres Teilbaums und der Gesamtsumme aller
|
||
* vier Kennzahlen (Kundenwunsch B7).
|
||
*
|
||
* Zwei Team-Sichten je Führungskraft (Kundenentscheid 2026-07-24, revidiert F9):
|
||
* - team_abo_count / team_customer_abo_count ("direkt"): Teilbaum wird an der
|
||
* nächsten Führungskraft abgeschnitten — überlappungsfrei, die Kopf-Summen
|
||
* (total_*) bleiben dadurch echte Gesamtwerte ohne Doppelzählung.
|
||
* - team_abo_count_total / team_customer_abo_count_total ("gesamt"): komplette
|
||
* Downline der Führungskraft; verschachtelte Führungskräfte zählen hier
|
||
* mehrfach, deshalb werden diese Spalten nicht aufsummiert.
|
||
* total_abo_count_full = eigene + Kundenabos + Team gesamt (Sortierung).
|
||
*
|
||
* @param array<int, Collection<int, User>> $lineBuckets
|
||
* @return array{level_name: string, total: int, total_own: int, total_customer: int, total_team: int, total_team_customer: int, rows: array<int, array{user_id: int, name: string, career_level: string, line: int, own_abo_count: int, customer_abo_count: int, team_abo_count: int, team_customer_abo_count: int, team_abo_count_total: int, team_customer_abo_count_total: int, total_abo_count: int, total_abo_count_full: int}>}
|
||
*/
|
||
private function leaderAboOverview(array $lineBuckets): array
|
||
{
|
||
$leaderMinPos = UserLevel::query()
|
||
->where('name', self::LEADER_LEVEL_NAME)
|
||
->value('pos');
|
||
|
||
$usersWithLine = $this->usersWithLine($lineBuckets);
|
||
$isLeader = fn (User $user): bool => $leaderMinPos !== null && ($user->user_level?->pos ?? 0) >= $leaderMinPos;
|
||
|
||
$leadersByLine = $leaderMinPos === null ? [] : collect($usersWithLine)
|
||
->filter(fn (array $entry) => $isLeader($entry['user']))
|
||
->all();
|
||
|
||
if ($leadersByLine === []) {
|
||
return ['level_name' => self::LEADER_LEVEL_NAME, 'total' => 0, 'total_own' => 0, 'total_customer' => 0, 'total_team' => 0, 'total_team_customer' => 0, 'rows' => []];
|
||
}
|
||
|
||
$leaderIds = array_keys($leadersByLine);
|
||
$teamIdsByLeader = $this->teamIdsPerLeader($usersWithLine, $leaderIds, $isLeader);
|
||
$fullTeamIdsByLeader = $this->teamIdsPerLeader($usersWithLine, $leaderIds, fn (User $user): bool => false);
|
||
$allCountIds = array_values(array_unique(array_merge($leaderIds, ...array_values($fullTeamIdsByLeader))));
|
||
|
||
$ownCounts = $this->activeAboQuery()
|
||
->whereIn('user_id', $allCountIds)
|
||
->where('is_for', 'me')
|
||
->selectRaw('user_id, COUNT(*) as abo_count')
|
||
->groupBy('user_id')
|
||
->pluck('abo_count', 'user_id');
|
||
|
||
$customerCounts = $this->customerAboCounts($allCountIds);
|
||
|
||
$rows = collect($leadersByLine)
|
||
->map(function (array $entry, int $userId) use ($ownCounts, $customerCounts, $teamIdsByLeader, $fullTeamIdsByLeader) {
|
||
$teamIds = $teamIdsByLeader[$userId] ?? [];
|
||
$fullTeamIds = $fullTeamIdsByLeader[$userId] ?? [];
|
||
$own = (int) $ownCounts->get($userId, 0);
|
||
$customer = (int) $customerCounts->get($userId, 0);
|
||
$team = (int) collect($teamIds)->sum(fn (int $teamUserId) => (int) $ownCounts->get($teamUserId, 0));
|
||
$teamCustomer = (int) collect($teamIds)->sum(fn (int $teamUserId) => (int) $customerCounts->get($teamUserId, 0));
|
||
$teamTotal = (int) collect($fullTeamIds)->sum(fn (int $teamUserId) => (int) $ownCounts->get($teamUserId, 0));
|
||
$teamCustomerTotal = (int) collect($fullTeamIds)->sum(fn (int $teamUserId) => (int) $customerCounts->get($teamUserId, 0));
|
||
|
||
return [
|
||
'user_id' => $userId,
|
||
'name' => $this->userName($entry['user']),
|
||
'career_level' => $this->careerLevel($entry['user']),
|
||
'line' => $entry['line'],
|
||
'own_abo_count' => $own,
|
||
'customer_abo_count' => $customer,
|
||
'team_abo_count' => $team,
|
||
'team_customer_abo_count' => $teamCustomer,
|
||
'team_abo_count_total' => $teamTotal,
|
||
'team_customer_abo_count_total' => $teamCustomerTotal,
|
||
'total_abo_count' => $own + $customer + $team + $teamCustomer,
|
||
'total_abo_count_full' => $own + $customer + $teamTotal + $teamCustomerTotal,
|
||
];
|
||
})
|
||
->sortByDesc('total_abo_count_full')
|
||
->values();
|
||
|
||
$totalOwn = (int) $rows->sum('own_abo_count');
|
||
$totalCustomer = (int) $rows->sum('customer_abo_count');
|
||
$totalTeam = (int) $rows->sum('team_abo_count');
|
||
$totalTeamCustomer = (int) $rows->sum('team_customer_abo_count');
|
||
|
||
return [
|
||
'level_name' => self::LEADER_LEVEL_NAME,
|
||
'total' => $totalOwn + $totalCustomer + $totalTeam + $totalTeamCustomer,
|
||
'total_own' => $totalOwn,
|
||
'total_customer' => $totalCustomer,
|
||
'total_team' => $totalTeam,
|
||
'total_team_customer' => $totalTeamCustomer,
|
||
'rows' => $rows->all(),
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Teilbaum-User-IDs je Führungskraft aus der gemeinsamen Sponsor-Map
|
||
* (ein Durchlauf über die bereits geladene Downline, keine Abfragen pro
|
||
* Führungskraft). Der Abstieg stoppt an jedem Kind, für das $isLeader true
|
||
* liefert (dieses und sein Team werden übersprungen); mit einem immer-false
|
||
* Callable ergibt sich die komplette Downline.
|
||
*
|
||
* @param array<int, array{user: User, line: int}> $usersWithLine
|
||
* @param int[] $leaderIds
|
||
* @return array<int, int[]>
|
||
*/
|
||
private function teamIdsPerLeader(array $usersWithLine, array $leaderIds, callable $isLeader): array
|
||
{
|
||
$childrenBySponsor = [];
|
||
|
||
foreach ($usersWithLine as $entry) {
|
||
$childrenBySponsor[(int) $entry['user']->m_sponsor][] = $entry['user'];
|
||
}
|
||
|
||
$teamIdsByLeader = [];
|
||
|
||
foreach ($leaderIds as $leaderId) {
|
||
$teamIds = [];
|
||
$queue = $childrenBySponsor[$leaderId] ?? [];
|
||
|
||
while ($queue !== []) {
|
||
$child = array_shift($queue);
|
||
|
||
if ($isLeader($child)) {
|
||
continue;
|
||
}
|
||
|
||
$teamIds[] = (int) $child->id;
|
||
|
||
foreach ($childrenBySponsor[(int) $child->id] ?? [] as $grandChild) {
|
||
$queue[] = $grandChild;
|
||
}
|
||
}
|
||
|
||
$teamIdsByLeader[$leaderId] = $teamIds;
|
||
}
|
||
|
||
return $teamIdsByLeader;
|
||
}
|
||
|
||
/**
|
||
* Top-10-Ranglisten der Kundenabos im eigenen Team (Kundenwunsch B5/B6,
|
||
* "unabhängig vom Incentive" = aktueller Gesamtbestand aktiver Kundenabos):
|
||
*
|
||
* - by_count: nach Anzahl aktiver Kundenabos je Berater
|
||
* - by_points: nach Umsatz mit Ist (im gewählten Monat abgerechnete
|
||
* Kundenabo-Punkte aus user_sales_volumes, Hauptwert und Sortierung —
|
||
* Kundenentscheid 2026-07-24) und Soll (Abo-Punkte-Bestand, Produktpunkte
|
||
* × Menge der laufenden Kundenabos) als Zweitwert
|
||
*
|
||
* Der Statistik-Nutzer selbst zählt in beiden Listen mit (Linie 0 / "ich").
|
||
*
|
||
* @param array<int, Collection<int, User>> $lineBuckets
|
||
* @return array{by_count: array<int, array<string, mixed>>, by_points: array<int, array<string, mixed>>}
|
||
*/
|
||
private function topCustomerAboRanking(User $rootUser, array $lineBuckets, int $month, int $year): array
|
||
{
|
||
$usersWithLine = $this->usersWithLine($lineBuckets, $rootUser);
|
||
|
||
if ($usersWithLine === []) {
|
||
return ['by_count' => [], 'by_points' => []];
|
||
}
|
||
|
||
$memberIds = array_keys($usersWithLine);
|
||
$aboCounts = $this->customerAboCounts($memberIds);
|
||
$targetPoints = $this->customerAboTargetPoints($memberIds);
|
||
$actualPoints = $this->customerAboActualPoints($memberIds, $month, $year);
|
||
$addonPoints = $this->customerAboAddonPoints($memberIds, $month, $year);
|
||
|
||
$rows = collect($usersWithLine)
|
||
->map(function (array $entry, int $userId) use ($aboCounts, $targetPoints, $actualPoints, $addonPoints) {
|
||
$actual = (float) $actualPoints->get($userId, 0.0);
|
||
// Einmal-Zusätze aus der Position ableiten, den wiederkehrenden
|
||
// Anteil als Rest: so ergeben die beiden Spalten IMMER die
|
||
// angezeigte Gesamtsumme, auch wenn Positions- und
|
||
// Zeilensummen in Altdaten einmal auseinanderfallen.
|
||
$addon = min(max((float) $addonPoints->get($userId, 0.0), 0.0), $actual);
|
||
|
||
return [
|
||
'user_id' => $userId,
|
||
'name' => $this->userName($entry['user']),
|
||
'career_level' => $this->careerLevel($entry['user']),
|
||
'line' => $entry['line'],
|
||
'is_self' => $entry['line'] === 0,
|
||
'abo_count' => (int) $aboCounts->get($userId, 0),
|
||
'points_target' => (float) $targetPoints->get($userId, 0.0),
|
||
'points_actual' => $actual,
|
||
'points_addon' => round($addon, 2),
|
||
'points_recurring' => round($actual - $addon, 2),
|
||
];
|
||
});
|
||
|
||
return [
|
||
'by_count' => $rows
|
||
->filter(fn (array $row) => $row['abo_count'] > 0)
|
||
->sortByDesc('abo_count')
|
||
->take(10)
|
||
->values()
|
||
->all(),
|
||
'by_points' => $rows
|
||
->filter(fn (array $row) => $row['points_target'] > 0 || $row['points_actual'] > 0)
|
||
->sortByDesc('points_actual')
|
||
->take(10)
|
||
->values()
|
||
->all(),
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Soll-Punkte je Berater: Abo-Punkte-Bestand der aktiven Kundenabos
|
||
* (Produktpunkte × Menge, nur Hauptartikel comp = 0 — gleiche Logik wie
|
||
* {@see UserAbo::getTotalPoints()}).
|
||
*
|
||
* @param int[] $memberIds
|
||
* @return Collection<int, float>
|
||
*/
|
||
private function customerAboTargetPoints(array $memberIds): Collection
|
||
{
|
||
return UserAbo::query()
|
||
->join('user_abo_items', 'user_abo_items.user_abo_id', '=', 'user_abos.id')
|
||
->join('products', 'products.id', '=', 'user_abo_items.product_id')
|
||
->whereNull('products.deleted_at')
|
||
->where('user_abos.active', true)
|
||
->whereNotIn('user_abos.status', [4, 5, 6])
|
||
->whereIn('user_abos.member_id', $memberIds)
|
||
->where('user_abos.is_for', 'ot')
|
||
->where('user_abo_items.comp', 0)
|
||
->selectRaw('user_abos.member_id, COALESCE(SUM(products.points * user_abo_items.qty), 0) as points')
|
||
->groupBy('user_abos.member_id')
|
||
->pluck('points', 'member_id')
|
||
->map(fn ($points) => (float) $points);
|
||
}
|
||
|
||
/**
|
||
* Ist-Punkte je Berater: im Monat tatsächlich abgerechnete Kundenabo-Punkte.
|
||
*
|
||
* @param int[] $memberIds
|
||
* @return Collection<int, float>
|
||
*/
|
||
private function customerAboActualPoints(array $memberIds, int $month, int $year): Collection
|
||
{
|
||
return UserSalesVolume::query()
|
||
->leftJoin('shopping_orders', 'shopping_orders.id', '=', 'user_sales_volumes.shopping_order_id')
|
||
->whereIn('user_id', $memberIds)
|
||
->where('month', $month)
|
||
->where('year', $year)
|
||
->select('user_sales_volumes.user_id')
|
||
->selectRaw(SalesVolumePoints::customerAboPoints().' as points')
|
||
->groupBy('user_sales_volumes.user_id')
|
||
->pluck('points', 'user_id')
|
||
->map(fn ($points) => (float) $points);
|
||
}
|
||
|
||
/**
|
||
* Von den abgerechneten Kundenabo-Punkten der Anteil, der aus EINMALIG zum
|
||
* Abo dazubestellten Positionen stammt (Kundenwunsch 2026-09-07:
|
||
* "1231 abgerechnet – davon 1000 reine Abo-Punkte – 231 einmalige").
|
||
*
|
||
* Grundlage ist `shopping_order_items.is_abo_addon`. Die Positionspunkte sind
|
||
* Stückpunkte zum Bestellzeitpunkt, die Zeilensumme also `points * qty` —
|
||
* gegen die Produktivdaten geprüft: die Positionssummen je Bestellung stimmen
|
||
* mit `user_sales_volumes.points` überein.
|
||
*
|
||
* Bewusst zweistufig statt als ein JOIN: eine Bestellung darf pro Berater nur
|
||
* EINMAL zählen, ein zusätzlicher Join auf die Positionen würde die Zeilen
|
||
* vervielfachen, sobald zu einer Bestellung mehrere Umsatzzeilen existieren.
|
||
*
|
||
* Einschränkung: `is_abo_addon` gibt es erst seit 06/2026. Für ältere
|
||
* Bestellungen ist der Wert false — die Aufteilung zeigt dort alles als
|
||
* wiederkehrend.
|
||
*
|
||
* @param int[] $memberIds
|
||
* @return Collection<int, float>
|
||
*/
|
||
private function customerAboAddonPoints(array $memberIds, int $month, int $year): Collection
|
||
{
|
||
if ($memberIds === []) {
|
||
return collect();
|
||
}
|
||
|
||
// Je Berater und Bestellung sowohl die gebuchte Summe (netto, Stornos also
|
||
// gegengerechnet) als auch die urspruenglich positive Summe: aus deren
|
||
// Verhaeltnis ergibt sich, wie viel von der Bestellung im Monat stehen
|
||
// geblieben ist.
|
||
$pairs = UserSalesVolume::query()
|
||
->join('shopping_orders', 'shopping_orders.id', '=', 'user_sales_volumes.shopping_order_id')
|
||
->whereIn('user_sales_volumes.user_id', $memberIds)
|
||
->where('user_sales_volumes.month', $month)
|
||
->where('user_sales_volumes.year', $year)
|
||
->where('shopping_orders.is_abo', 1)
|
||
->whereRaw(SalesVolumePoints::shopPredicate())
|
||
->groupBy('user_sales_volumes.user_id', 'shopping_orders.id')
|
||
->selectRaw('user_sales_volumes.user_id, shopping_orders.id as shopping_order_id')
|
||
->selectRaw('SUM(user_sales_volumes.points) as net_points')
|
||
->selectRaw('SUM(CASE WHEN user_sales_volumes.points > 0 THEN user_sales_volumes.points ELSE 0 END) as gross_points')
|
||
->get();
|
||
|
||
if ($pairs->isEmpty()) {
|
||
return collect();
|
||
}
|
||
|
||
$addonByOrder = ShoppingOrderItem::query()
|
||
->whereIn('shopping_order_id', $pairs->pluck('shopping_order_id')->unique()->all())
|
||
->addonItems()
|
||
->groupBy('shopping_order_id')
|
||
->selectRaw('shopping_order_id, COALESCE(SUM(points * qty), 0) as points')
|
||
->pluck('points', 'shopping_order_id');
|
||
|
||
$byUser = [];
|
||
foreach ($pairs as $pair) {
|
||
$addon = (float) $addonByOrder->get((int) $pair->shopping_order_id, 0.0);
|
||
if ($addon <= 0) {
|
||
continue;
|
||
}
|
||
|
||
// Anteil der Bestellung, der im Monat tatsaechlich stehen geblieben ist.
|
||
// Vollstorno ergibt 0, eine unstornierte Bestellung 1. Ein Teilstorno wird
|
||
// anteilig verrechnet: welche Position storniert wurde, geht aus der
|
||
// Stornobuchung nicht hervor — sie hat keine Positionsaufteilung.
|
||
$gross = (float) $pair->gross_points;
|
||
$factor = $gross > 0 ? max(0.0, min(1.0, (float) $pair->net_points / $gross)) : 0.0;
|
||
|
||
$points = $addon * $factor;
|
||
if ($points > 0) {
|
||
$byUser[(int) $pair->user_id] = ($byUser[(int) $pair->user_id] ?? 0.0) + $points;
|
||
}
|
||
}
|
||
|
||
return collect($byUser);
|
||
}
|
||
|
||
/**
|
||
* "Recruiter des Monats": Wer im Team (inklusive des Statistik-Nutzers
|
||
* selbst, Linie 0) hat im gewählten Monat wie viele Neupartner gesponsert?
|
||
*
|
||
* Neupartner-Definition identisch zur Neupartner-KPI {@see newPartnersCount()}:
|
||
* m_level gesetzt, aktiver Account (payment_account in der Zukunft),
|
||
* active_date im Monat — Zuordnung über users.m_sponsor.
|
||
*
|
||
* "Neues Volumen" je Sponsor = Summe der Monats-Gesamtpunkte (eigene +
|
||
* Kundenpunkte, {@see SalesVolumePoints::totalPoints()} — das Starterpaket
|
||
* mit Status 1 zählt damit mit) aller seiner Neupartner, plus rekursiv die
|
||
* Gesamtpunkte der Neupartner, die diese Neupartner im selben Monat selbst
|
||
* geworben haben (Kettenlogik). Die Rekursion läuft rein im Speicher über
|
||
* die bereits geladene Neupartner-Menge und ist zyklen-sicher.
|
||
*
|
||
* Sortiert nach Anzahl absteigend (Volumen als Tiebreaker); nur Sponsoren
|
||
* mit mindestens einem Neupartner.
|
||
*
|
||
* `totals` (Kundenwunsch 2026-09-07) ist bewusst NICHT die Spaltensumme:
|
||
* `volume_points` enthält je Sponsor das Kettenvolumen, ein Neupartner steckt
|
||
* also gleichzeitig in der Zeile seines Sponsors und in seiner eigenen. Die
|
||
* Summe wird deshalb einmal über die deduplizierte Neupartner-Menge gerechnet
|
||
* und ist entsprechend kleiner als die Spalte darüber.
|
||
*
|
||
* @param array<int, Collection<int, User>> $lineBuckets
|
||
* @return array{rows: array<int, array{user_id: int, name: string, career_level: string, line: int, is_self: bool, new_partner_count: int, volume_points: float, partners: array<int, array{user_id: int, name: string, career_level: string, total_points: float, new_partner_count: int, children: array<int, array{user_id: int, name: string, career_level: string, total_points: float}>}>}>, totals: array{new_partner_count: int, volume_points: float}}
|
||
*/
|
||
private function recruiterRanking(User $rootUser, array $lineBuckets, int $month, int $year): array
|
||
{
|
||
$emptyTotals = ['new_partner_count' => 0, 'volume_points' => 0.0];
|
||
$usersByLine = $this->usersWithLine($lineBuckets, $rootUser);
|
||
|
||
if ($usersByLine === []) {
|
||
return ['rows' => [], 'totals' => $emptyTotals];
|
||
}
|
||
|
||
$newPartnersBySponsor = [];
|
||
|
||
foreach ($usersByLine as $entry) {
|
||
if ($entry['line'] === 0) {
|
||
continue;
|
||
}
|
||
|
||
$user = $entry['user'];
|
||
|
||
if (! $user->isNewPartnerIn($month, $year)) {
|
||
continue;
|
||
}
|
||
|
||
$newPartnersBySponsor[(int) $user->m_sponsor][] = $user;
|
||
}
|
||
|
||
if ($newPartnersBySponsor === []) {
|
||
return ['rows' => [], 'totals' => $emptyTotals];
|
||
}
|
||
|
||
$newPartnerIds = collect($newPartnersBySponsor)
|
||
->flatMap(fn (array $partners) => collect($partners)->map(fn (User $partner) => (int) $partner->id))
|
||
->unique()
|
||
->values()
|
||
->all();
|
||
|
||
$pointsByUser = $this->totalPointsByUser($newPartnerIds, $month, $year);
|
||
|
||
$totals = [
|
||
'new_partner_count' => count($newPartnerIds),
|
||
'volume_points' => round((float) collect($newPartnerIds)->sum(fn (int $id) => (float) $pointsByUser->get($id, 0.0)), 2),
|
||
];
|
||
|
||
$chainVolume = function (int $sponsorId, array $visited) use (&$chainVolume, $newPartnersBySponsor, $pointsByUser): float {
|
||
$volume = 0.0;
|
||
|
||
foreach ($newPartnersBySponsor[$sponsorId] ?? [] as $partner) {
|
||
$partnerId = (int) $partner->id;
|
||
|
||
if (isset($visited[$partnerId])) {
|
||
continue;
|
||
}
|
||
|
||
$visited[$partnerId] = true;
|
||
$volume += (float) $pointsByUser->get($partnerId, 0.0) + $chainVolume($partnerId, $visited);
|
||
}
|
||
|
||
return $volume;
|
||
};
|
||
|
||
$rows = [];
|
||
|
||
foreach ($usersByLine as $sponsorId => $entry) {
|
||
$directPartners = $newPartnersBySponsor[$sponsorId] ?? [];
|
||
|
||
if ($directPartners === []) {
|
||
continue;
|
||
}
|
||
|
||
$partners = [];
|
||
|
||
foreach ($directPartners as $partner) {
|
||
$partnerId = (int) $partner->id;
|
||
$children = [];
|
||
|
||
foreach ($newPartnersBySponsor[$partnerId] ?? [] as $child) {
|
||
if ((int) $child->id === (int) $sponsorId) {
|
||
continue;
|
||
}
|
||
|
||
$children[] = [
|
||
'user_id' => (int) $child->id,
|
||
'name' => $this->userName($child),
|
||
'career_level' => $this->careerLevel($child),
|
||
'total_points' => (float) $pointsByUser->get((int) $child->id, 0.0),
|
||
];
|
||
}
|
||
|
||
$partners[] = [
|
||
'user_id' => $partnerId,
|
||
'name' => $this->userName($partner),
|
||
'career_level' => $this->careerLevel($partner),
|
||
'total_points' => (float) $pointsByUser->get($partnerId, 0.0),
|
||
'new_partner_count' => count($children),
|
||
'children' => $children,
|
||
];
|
||
}
|
||
|
||
$rows[] = [
|
||
'user_id' => (int) $sponsorId,
|
||
'name' => $this->userName($entry['user']),
|
||
'career_level' => $this->careerLevel($entry['user']),
|
||
'line' => $entry['line'],
|
||
'is_self' => $entry['line'] === 0,
|
||
'new_partner_count' => count($directPartners),
|
||
'volume_points' => $chainVolume((int) $sponsorId, [(int) $sponsorId => true]),
|
||
'partners' => $partners,
|
||
];
|
||
}
|
||
|
||
return [
|
||
'rows' => collect($rows)
|
||
->sortBy([['new_partner_count', 'desc'], ['volume_points', 'desc']])
|
||
->values()
|
||
->all(),
|
||
'totals' => $totals,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Monats-Gesamtpunkte (eigene + Kundenpunkte, inkl. Starterpaket Status 1)
|
||
* je User — eine gebatchte Query für alle übergebenen IDs.
|
||
*
|
||
* @param int[] $userIds
|
||
* @return Collection<int, float>
|
||
*/
|
||
private function totalPointsByUser(array $userIds, int $month, int $year): Collection
|
||
{
|
||
return SalesVolumePoints::totalPointsByUser($userIds, $month, $year);
|
||
}
|
||
|
||
/**
|
||
* Aktueller Bestand aktiver Kundenabos je Berater (member_id).
|
||
*
|
||
* @param int[] $memberIds
|
||
* @return Collection<int, int>
|
||
*/
|
||
private function customerAboCounts(array $memberIds): Collection
|
||
{
|
||
if ($memberIds === []) {
|
||
return collect();
|
||
}
|
||
|
||
return $this->activeAboQuery()
|
||
->whereIn('member_id', $memberIds)
|
||
->where('is_for', 'ot')
|
||
->selectRaw('member_id, COUNT(*) as abo_count')
|
||
->groupBy('member_id')
|
||
->pluck('abo_count', 'member_id');
|
||
}
|
||
|
||
/**
|
||
* Downline-User mit ihrer Linie; optional inklusive des Statistik-Nutzers
|
||
* selbst als Linie 0 (er steckt in keinem lineBucket, {@see lineBuckets()}).
|
||
*
|
||
* @param array<int, Collection<int, User>> $lineBuckets
|
||
* @return array<int, array{user: User, line: int}>
|
||
*/
|
||
private function usersWithLine(array $lineBuckets, ?User $rootUser = null): array
|
||
{
|
||
$usersByLine = [];
|
||
|
||
if ($rootUser !== null) {
|
||
$usersByLine[(int) $rootUser->id] = ['user' => $rootUser, 'line' => 0];
|
||
}
|
||
|
||
foreach ($lineBuckets as $line => $users) {
|
||
foreach ($users as $user) {
|
||
$usersByLine[(int) $user->id] = ['user' => $user, 'line' => $line];
|
||
}
|
||
}
|
||
|
||
return $usersByLine;
|
||
}
|
||
|
||
private function userName(User $user): string
|
||
{
|
||
$name = trim(($user->account?->first_name ?? '').' '.($user->account?->last_name ?? ''));
|
||
|
||
return $name !== '' ? $name : ($user->email ?: '#'.$user->id);
|
||
}
|
||
|
||
/**
|
||
* Karriere-Level in der Sprache des Betrachters — dieselbe Auflösung wie in
|
||
* den übrigen Team-Auswertungen ({@see TeamAboOverviewService}, Firstlines,
|
||
* Jahresgebühr-Liste), sonst steht in einer Karte der deutsche Name.
|
||
*/
|
||
private function careerLevel(User $user): string
|
||
{
|
||
return $user->user_level?->getLang('name') ?: ($user->m_level ? 'Level '.$user->m_level : '-');
|
||
}
|
||
|
||
/**
|
||
* Anzahl der Berater mit >= 1000 Punkten Kundenumsatz (KP + Shop) im Monat.
|
||
*
|
||
* @param int[] $userIds
|
||
*/
|
||
public function shop1000Count(array $userIds, int $month, int $year): int
|
||
{
|
||
return UserSalesVolume::query()
|
||
->whereIn('user_id', $userIds)
|
||
->where('month', $month)
|
||
->where('year', $year)
|
||
->select('user_id')
|
||
->selectRaw(SalesVolumePoints::totalPoints().' as total_points')
|
||
->groupBy('user_id')
|
||
->havingRaw(SalesVolumePoints::totalPoints().' >= 1000')
|
||
->get()
|
||
->count();
|
||
}
|
||
}
|