232 lines
9 KiB
PHP
232 lines
9 KiB
PHP
<?php
|
|
|
|
namespace App\Services;
|
|
|
|
use App\Models\UserBusiness;
|
|
use App\Models\UserLevel;
|
|
use App\User;
|
|
use Illuminate\Support\Collection;
|
|
|
|
class LevelReportService
|
|
{
|
|
public function getLevelPromotions(array $filters = []): Collection
|
|
{
|
|
$month = $filters['month'] ?? null;
|
|
$year = $filters['year'] ?? null;
|
|
$userId = $filters['user_id'] ?? null;
|
|
$onlyNotUpdated = $filters['only_not_updated'] ?? false;
|
|
|
|
// Lade UserLevels für Referenz
|
|
$userLevels = UserLevel::where('active', 1)->orderBy('pos')->get()->keyBy('id');
|
|
|
|
// Query UserBusiness Einträge mit Level-Aufstiegen
|
|
$query = UserBusiness::whereNotNull('next_qual_user_level')
|
|
->whereRaw("JSON_LENGTH(next_qual_user_level) > 0")
|
|
->orderBy('year', 'desc')
|
|
->orderBy('month', 'desc')
|
|
->orderBy('user_id');
|
|
|
|
// Filter anwenden
|
|
if ($month) {
|
|
$query->where('month', $month);
|
|
}
|
|
if ($year) {
|
|
$query->where('year', $year);
|
|
}
|
|
if ($userId) {
|
|
$query->where('user_id', $userId);
|
|
}
|
|
|
|
$userBusinesses = $query->get();
|
|
|
|
return $this->processLevelPromotions($userBusinesses, $userLevels, $onlyNotUpdated);
|
|
}
|
|
|
|
public function processLevelPromotions($userBusinesses, $userLevels, $onlyNotUpdated = false): Collection
|
|
{
|
|
$promotions = [];
|
|
|
|
// Lade User-Daten für alle Level-Vergleiche
|
|
$userIds = $userBusinesses->pluck('user_id')->unique();
|
|
$users = User::whereIn('id', $userIds)->get(['id', 'm_level']);
|
|
$currentUserLevels = $users->keyBy('id');
|
|
|
|
foreach ($userBusinesses as $userBusiness) {
|
|
$nextQualUserLevel = $userBusiness->next_qual_user_level;
|
|
|
|
if (is_array($nextQualUserLevel) && !empty($nextQualUserLevel)) {
|
|
// next_qual_user_level kann sowohl ein einzelnes Level-Objekt als auch ein Array von Level-Objekten sein
|
|
$levelArray = isset($nextQualUserLevel['id']) ? [$nextQualUserLevel] : $nextQualUserLevel;
|
|
|
|
foreach ($levelArray as $newLevelData) {
|
|
// Überprüfe ob es ein vollständiges Level-Objekt ist
|
|
if (is_array($newLevelData) && isset($newLevelData['id'])) {
|
|
$currentLevel = $userLevels->get($userBusiness->m_level_id);
|
|
$newLevelId = $newLevelData['id'];
|
|
|
|
// Lade aktuellen User Level
|
|
$currentUser = $currentUserLevels->get($userBusiness->user_id);
|
|
$currentUserLevelName = 'Unbekannt';
|
|
|
|
if ($currentUser && $currentUser->m_level) {
|
|
$currentUserLevel = $userLevels->get($currentUser->m_level);
|
|
$currentUserLevelName = $currentUserLevel ? $currentUserLevel->name : 'Level ID: ' . $currentUser->m_level;
|
|
}
|
|
|
|
// Filter: Nur User die noch nicht auf das neue Level umgestellt wurden
|
|
if ($onlyNotUpdated) {
|
|
if (!$currentUser || $currentUser->m_level == $newLevelId) {
|
|
continue; // Skip - User ist bereits auf das neue Level umgestellt
|
|
}
|
|
}
|
|
|
|
$promotions[] = [
|
|
'user_id' => $userBusiness->user_id,
|
|
'email' => $userBusiness->email,
|
|
'first_name' => $userBusiness->first_name,
|
|
'last_name' => $userBusiness->last_name,
|
|
'month' => $userBusiness->month,
|
|
'year' => $userBusiness->year,
|
|
'date' => sprintf('%04d-%02d', $userBusiness->year, $userBusiness->month),
|
|
'from_level_id' => $userBusiness->m_level_id,
|
|
'from_level_name' => $currentLevel ? $currentLevel->name : 'Unbekannt',
|
|
'to_level_id' => $newLevelId,
|
|
'to_level_name' => $newLevelData['name'] ?? 'Unbekannt',
|
|
'to_level_margin' => $newLevelData['margin'] ?? 0,
|
|
'to_level_margin_shop' => $newLevelData['margin_shop'] ?? 0,
|
|
'to_level_qual_kp' => $newLevelData['qual_kp'] ?? 0,
|
|
'to_level_qual_pp' => $newLevelData['qual_pp'] ?? 0,
|
|
'to_level_growth_bonus' => $newLevelData['growth_bonus'] ?? 0,
|
|
'to_level_pos' => $newLevelData['pos'] ?? 0,
|
|
'current_user_level_id' => $currentUser ? $currentUser->m_level : null,
|
|
'current_user_level_name' => $currentUserLevelName,
|
|
'level_updated' => $onlyNotUpdated ? 'Nein' : ($currentUser && $currentUser->m_level == $newLevelId ? 'Ja' : 'Nein'),
|
|
'total_pp' => $userBusiness->total_pp ?? 0,
|
|
'total_qual_pp' => $userBusiness->total_qual_pp ?? 0,
|
|
'payline_points_qual_kp' => $userBusiness->payline_points_qual_kp ?? 0,
|
|
'sales_volume_points_sum' => $userBusiness->sales_volume_points_KP_sum ?? 0,
|
|
'active_account' => $userBusiness->active_account ? 'Ja' : 'Nein',
|
|
];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Sortiere nach Datum (neueste zuerst) und dann nach User ID
|
|
usort($promotions, function ($a, $b) {
|
|
if ($a['year'] !== $b['year']) {
|
|
return $b['year'] - $a['year'];
|
|
}
|
|
if ($a['month'] !== $b['month']) {
|
|
return $b['month'] - $a['month'];
|
|
}
|
|
return $a['user_id'] - $b['user_id'];
|
|
});
|
|
|
|
return collect($promotions);
|
|
}
|
|
|
|
public function getStatistics(Collection $promotions): array
|
|
{
|
|
$stats = [
|
|
'total_count' => $promotions->count(),
|
|
'level_stats' => [],
|
|
'period_stats' => []
|
|
];
|
|
|
|
// Statistik nach Level
|
|
foreach ($promotions as $promotion) {
|
|
$levelName = $promotion['to_level_name'];
|
|
if (!isset($stats['level_stats'][$levelName])) {
|
|
$stats['level_stats'][$levelName] = 0;
|
|
}
|
|
$stats['level_stats'][$levelName]++;
|
|
}
|
|
|
|
// Statistik nach Monat/Jahr
|
|
foreach ($promotions as $promotion) {
|
|
$period = $promotion['date'];
|
|
if (!isset($stats['period_stats'][$period])) {
|
|
$stats['period_stats'][$period] = 0;
|
|
}
|
|
$stats['period_stats'][$period]++;
|
|
}
|
|
|
|
return $stats;
|
|
}
|
|
|
|
public function exportToCsv(Collection $promotions, string $filename = null): string
|
|
{
|
|
if (!$filename) {
|
|
$filename = 'level_promotions_' . date('Y-m-d_H-i-s') . '.csv';
|
|
}
|
|
|
|
$filepath = storage_path('app/reports/' . $filename);
|
|
|
|
// Erstelle Verzeichnis falls nicht vorhanden
|
|
if (!file_exists(dirname($filepath))) {
|
|
mkdir(dirname($filepath), 0755, true);
|
|
}
|
|
|
|
$file = fopen($filepath, 'w');
|
|
|
|
// UTF-8 BOM für korrekte Darstellung in Excel
|
|
fwrite($file, "\xEF\xBB\xBF");
|
|
|
|
// CSV Headers
|
|
$headers = [
|
|
'Datum',
|
|
'User ID',
|
|
'Vorname',
|
|
'Nachname',
|
|
'E-Mail',
|
|
'Von Level ID',
|
|
'Von Level Name',
|
|
'Zu Level ID',
|
|
'Zu Level Name',
|
|
'Zu Level KP Anforderung',
|
|
'Zu Level PP Anforderung',
|
|
'User PP Total',
|
|
'User PP Qualifiziert',
|
|
'Aktueller User Level ID',
|
|
'Aktueller User Level Name',
|
|
'Level bereits geupdatet',
|
|
'Aktiver Account',
|
|
'Monat',
|
|
'Jahr'
|
|
];
|
|
|
|
fputcsv($file, $headers, ';');
|
|
|
|
// Daten schreiben
|
|
foreach ($promotions as $promotion) {
|
|
$row = [
|
|
$promotion['date'],
|
|
$promotion['user_id'],
|
|
$promotion['first_name'],
|
|
$promotion['last_name'],
|
|
$promotion['email'],
|
|
$promotion['from_level_id'],
|
|
$promotion['from_level_name'],
|
|
$promotion['to_level_id'],
|
|
$promotion['to_level_name'],
|
|
$promotion['to_level_qual_kp'],
|
|
$promotion['to_level_qual_pp'],
|
|
$promotion['sales_volume_points_sum'],
|
|
$promotion['payline_points_qual_kp'],
|
|
$promotion['current_user_level_id'] ?? 'N/A',
|
|
$promotion['current_user_level_name'],
|
|
$promotion['level_updated'],
|
|
$promotion['active_account'],
|
|
$promotion['month'],
|
|
$promotion['year']
|
|
];
|
|
|
|
fputcsv($file, $row, ';');
|
|
}
|
|
|
|
fclose($file);
|
|
|
|
return $filepath;
|
|
}
|
|
}
|