", "", "", "" ]; protected array $statusLabels = [ 'new' => 'Neu', 'assigned' => 'Zugewiesen', 'scheduled' => 'Geplant', 'in_progress' => 'In Bearbeitung', 'correction_requested' => 'Korrektur angefordert', 'intervention_required' => 'Eingriff erforderlich', 'civil_engineering_required' => 'Tiefbau benötigt', 'civil_engineering_completed' => 'Tiefbau abgeschlossen', 'problem_solved' => 'Problem gelöst', 'documented' => 'Dokumentiert', 'completed' => 'Abgeschlossen', 'charged' => 'Verrechnet', 'cancelled' => 'Abgebrochen', 'archived' => 'Archiviert', ]; protected array $statusColors = [ 'new' => '#3b82f6', 'assigned' => '#06b6d4', 'scheduled' => '#8b5cf6', 'in_progress' => '#f59e0b', 'correction_requested' => '#ef4444', 'intervention_required' => '#dc2626', 'civil_engineering_required' => '#ea580c', 'civil_engineering_completed' => '#65a30d', 'problem_solved' => '#22c55e', 'documented' => '#14b8a6', 'completed' => '#10b981', 'charged' => '#8b5cf6', 'cancelled' => '#6b7280', 'archived' => '#9ca3af', ]; protected function indexAction() { $this->layout()->set('additionalHead', $this->additionalHead); Helper::renderVue($this, 'WorkorderDashboard', $this->headerTitle, []); } protected function getFilterOptionsAction() { if ($this->user->isAdmin()) { $tenants = WorkorderTenantConfigModel::getAll([], null, 0, ['key' => 'name', 'order' => 'ASC']); $companies = WorkorderCompanyModel::getAll([], null, 0, ['key' => 'name', 'order' => 'ASC']); } else { $tenants = WorkorderTenantConfigModel::getAll(['addressId' => $this->user->address_id], null, 0, ['key' => 'name', 'order' => 'ASC']); $db = FronkDB::singleton(); $userAddressId = intval($this->user->address_id); $sql = "SELECT * FROM thetool.WorkorderCompany WHERE visibleForAddressId IS NULL OR JSON_CONTAINS(visibleForAddressId, '$userAddressId') ORDER BY name ASC"; $result = $db->query($sql); $companies = []; while ($row = $result->fetch_assoc()) { $company = new WorkorderCompanyModel(); $company->id = (int)$row['id']; $company->name = $row['name']; $companies[] = $company; } } self::returnJson([ 'tenants' => array_map(fn($t) => ['value' => $t->id, 'text' => $t->name], $tenants), 'companies' => array_map(fn($c) => ['value' => $c->id, 'text' => $c->name], $companies), 'statuses' => array_map(fn($key, $label) => ['value' => $key, 'text' => $label], array_keys($this->statusLabels), $this->statusLabels), 'campaigns' => [] ]); } protected function getCampaignsForTenantAction() { $tenantId = $this->postData['tenantId'] ?? null; if (!$tenantId || !($config = WorkorderTenantConfigModel::get($tenantId))) { self::returnJson([]); return; } if (!$this->user->isAdmin() && $config->addressId != $this->user->address_id) { self::returnJson([]); return; } $networks = NetworkModel::search(['owner_id' => $config->addressId]); if (empty($networks)) { self::returnJson([]); return; } $campaigns = PreordercampaignModel::search(['network_id' => array_map(fn($n) => $n->id, $networks)]); $options = array_map(fn($c) => ['value' => $c->id, 'text' => $c->name], $campaigns); usort($options, fn($a, $b) => strcmp($a['text'], $b['text'])); self::returnJson($options); } protected function getDashboardDataAction() { $tenantId = $this->postData['tenantId'] ?? null; $dateFrom = $this->postData['dateFrom'] ?? null; $dateTo = $this->postData['dateTo'] ?? null; $companyIds = $this->postData['companyIds'] ?? []; $statuses = $this->postData['statuses'] ?? []; $campaignIds = $this->postData['campaignIds'] ?? []; if (!$tenantId) self::sendError('Mandant muss ausgewählt werden.'); $config = WorkorderTenantConfigModel::get($tenantId); if (!$config) self::sendError('Mandant nicht gefunden.'); if (!$this->user->isAdmin() && $config->addressId != $this->user->address_id) self::sendError('Keine Berechtigung für diesen Mandanten.'); $networks = NetworkModel::search(['owner_id' => $config->addressId]); $tenantCampaignIds = array_map(fn($c) => $c->id, PreordercampaignModel::search(['network_id' => array_map(fn($n) => $n->id, $networks)])); if (empty($tenantCampaignIds)) { self::returnJson($this->getEmptyDashboardData()); return; } if (!empty($campaignIds)) $tenantCampaignIds = array_intersect($tenantCampaignIds, $campaignIds); $db = FronkDB::singleton(); $whereConditions = ["p.preordercampaign_id IN (" . implode(',', $tenantCampaignIds) . ")"]; if ($dateFrom || $dateTo) { $dateFromInt = $dateFrom ? intval($dateFrom) : 0; $dateToInt = $dateTo ? intval($dateTo) : PHP_INT_MAX; $whereConditions[] = "( (w.`create` >= $dateFromInt AND w.`create` <= $dateToInt) OR EXISTS ( SELECT 1 FROM thetool.WorkorderJournal wj WHERE wj.workorderId = w.id AND wj.`create` >= $dateFromInt AND wj.`create` <= $dateToInt ) )"; } if (!empty($companyIds)) $whereConditions[] = "w.companyId IN (" . implode(',', array_map('intval', $companyIds)) . ")"; if (!empty($statuses)) $whereConditions[] = "w.status IN (" . implode(',', array_map(fn($s) => "'" . $db->escape($s) . "'", $statuses)) . ")"; $whereClause = implode(' AND ', $whereConditions); self::returnJson([ 'kpis' => $this->getKPIs($db, $whereClause, $tenantCampaignIds, $dateFrom, $dateTo), 'statusDistribution' => $this->getStatusDistribution($db, $whereClause), 'companyPerformance' => $this->getCompanyPerformance($db, $whereClause), 'timeTrends' => $this->getTimeTrends($db, $tenantCampaignIds, $dateFrom, $dateTo, $companyIds), 'companyStatusCampaign' => $this->getCompanyStatusCampaign($db, $whereClause), 'interventionRates' => $this->getInterventionRates($db, $whereClause), 'statusTransitions' => $this->getStatusTransitions($db, $tenantCampaignIds, $dateFrom, $dateTo), ]); } private function getKPIs($db, $whereClause, $tenantCampaignIds, $dateFrom, $dateTo): array { $total = $db->query("SELECT COUNT(*) as c FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id WHERE $whereClause")->fetch_assoc()['c'] ?? 0; $offen = $db->query("SELECT COUNT(*) as c FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id WHERE $whereClause AND w.status NOT IN ('completed', 'charged', 'archived')")->fetch_assoc()['c'] ?? 0; $terminisiert = $db->query("SELECT COUNT(*) as c FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id WHERE $whereClause AND w.status NOT IN ('completed', 'charged', 'archived') AND w.appointmentDate IS NOT NULL")->fetch_assoc()['c'] ?? 0; $issues = $db->query("SELECT COUNT(*) as c FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id WHERE $whereClause AND w.status IN ('intervention_required', 'correction_requested')")->fetch_assoc()['c'] ?? 0; return [ 'total' => (int)$total, 'offen' => (int)$offen, 'terminisiert' => (int)$terminisiert, 'issues' => (int)$issues, 'interventionRate' => $total > 0 ? round(($issues / $total) * 100, 1) : 0, 'avgCompletionDays' => $this->calculateAvgCompletionTime($db, $tenantCampaignIds), ]; } private function calculateAvgCompletionTime($db, $tenantCampaignIds): ?float { $sql = "SELECT w.id, MIN(CASE WHEN wj.statusChange LIKE '%Zugewiesen%' OR wj.statusChange LIKE '%-> Zugewiesen' THEN wj.`create` END) as assigned_time, MIN(CASE WHEN wj.statusChange LIKE '%-> Abgeschlossen%' THEN wj.`create` END) as completed_time FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id JOIN thetool.WorkorderJournal wj ON w.id = wj.workorderId WHERE p.preordercampaign_id IN (" . implode(',', $tenantCampaignIds) . ") AND w.status IN ('completed', 'charged') GROUP BY w.id HAVING assigned_time IS NOT NULL AND completed_time IS NOT NULL"; $result = $db->query($sql); $totalDays = $count = 0; while ($row = $result->fetch_assoc()) { if ($row['completed_time'] > $row['assigned_time']) { $totalDays += ($row['completed_time'] - $row['assigned_time']) / 86400; $count++; } } return $count > 0 ? round($totalDays / $count, 1) : null; } private function getStatusDistribution($db, $whereClause): array { $result = $db->query("SELECT w.status, COUNT(*) as count FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id WHERE $whereClause GROUP BY w.status ORDER BY count DESC"); $distribution = []; while ($row = $result->fetch_assoc()) { $distribution[] = [ 'status' => $row['status'], 'label' => $this->statusLabels[$row['status']] ?? $row['status'], 'count' => (int)$row['count'], 'color' => $this->statusColors[$row['status']] ?? '#6b7280', ]; } return $distribution; } private function getCompanyPerformance($db, $whereClause): array { $sql = "SELECT wc.name as company, wc.id as companyId, SUM(CASE WHEN w.status IN ('completed', 'charged') THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN w.status IN ('new', 'assigned', 'scheduled', 'in_progress', 'documented') THEN 1 ELSE 0 END) as pending, SUM(CASE WHEN w.status IN ('intervention_required', 'correction_requested') THEN 1 ELSE 0 END) as issues, COUNT(*) as total FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id LEFT JOIN thetool.WorkorderCompany wc ON w.companyId = wc.id WHERE $whereClause AND w.companyId IS NOT NULL GROUP BY wc.id, wc.name ORDER BY total DESC"; $result = $db->query($sql); $performance = []; while ($row = $result->fetch_assoc()) { $performance[] = [ 'company' => $row['company'] ?? 'Nicht zugewiesen', 'companyId' => (int)$row['companyId'], 'completed' => (int)$row['completed'], 'pending' => (int)$row['pending'], 'issues' => (int)$row['issues'], 'total' => (int)$row['total'], ]; } return $performance; } private function getTimeTrends($db, $tenantCampaignIds, $dateFrom, $dateTo, $companyIds): array { $where = ["p.preordercampaign_id IN (" . implode(',', $tenantCampaignIds) . ")"]; if ($dateFrom || $dateTo) { $dateFromInt = $dateFrom ? intval($dateFrom) : 0; $dateToInt = $dateTo ? intval($dateTo) : PHP_INT_MAX; $where[] = "( (w.`create` >= $dateFromInt AND w.`create` <= $dateToInt) OR EXISTS ( SELECT 1 FROM thetool.WorkorderJournal wj WHERE wj.workorderId = w.id AND wj.`create` >= $dateFromInt AND wj.`create` <= $dateToInt ) )"; } if (!empty($companyIds)) $where[] = "w.companyId IN (" . implode(',', array_map('intval', $companyIds)) . ")"; $sql = "SELECT DATE(FROM_UNIXTIME(w.`create`)) as date, COUNT(*) as created, SUM(CASE WHEN w.status IN ('completed', 'charged') THEN 1 ELSE 0 END) as completed FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id WHERE " . implode(' AND ', $where) . " GROUP BY DATE(FROM_UNIXTIME(w.`create`)) ORDER BY date ASC"; $result = $db->query($sql); $trends = []; while ($row = $result->fetch_assoc()) { $trends[] = ['date' => $row['date'], 'created' => (int)$row['created'], 'completed' => (int)$row['completed']]; } return $trends; } private function getCompanyStatusCampaign($db, $whereClause): array { $sql = "SELECT wc.name as company, w.status, pc.name as campaign, COUNT(*) as count FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id JOIN thetool.Preordercampaign pc ON p.preordercampaign_id = pc.id LEFT JOIN thetool.WorkorderCompany wc ON w.companyId = wc.id WHERE $whereClause AND w.companyId IS NOT NULL GROUP BY wc.name, w.status, pc.name ORDER BY wc.name, count DESC"; $result = $db->query($sql); $data = []; while ($row = $result->fetch_assoc()) { $key = ($row['company'] ?? 'Nicht zugewiesen') . '|' . $row['status']; if (!isset($data[$key])) { $data[$key] = [ 'company' => $row['company'] ?? 'Nicht zugewiesen', 'status' => $row['status'], 'statusLabel' => $this->statusLabels[$row['status']] ?? $row['status'], 'count' => 0, 'campaigns' => [], ]; } $data[$key]['count'] += (int)$row['count']; $data[$key]['campaigns'][] = ['name' => $row['campaign'], 'count' => (int)$row['count']]; } $result = array_values($data); usort($result, fn($a, $b) => $b['count'] - $a['count']); return $result; } private function getInterventionRates($db, $whereClause): array { $sql = "SELECT wc.name as company, COUNT(*) as total, SUM(CASE WHEN w.status = 'intervention_required' THEN 1 ELSE 0 END) as interventions, SUM(CASE WHEN w.status = 'correction_requested' THEN 1 ELSE 0 END) as corrections FROM thetool.Workorder w JOIN thetool.Preorder p ON w.preorderId = p.id LEFT JOIN thetool.WorkorderCompany wc ON w.companyId = wc.id WHERE $whereClause AND w.companyId IS NOT NULL GROUP BY wc.name HAVING COUNT(*) >= 5 ORDER BY (SUM(CASE WHEN w.status IN ('intervention_required', 'correction_requested') THEN 1 ELSE 0 END) / COUNT(*)) DESC"; $result = $db->query($sql); $rates = []; while ($row = $result->fetch_assoc()) { $total = (int)$row['total']; $issueCount = (int)$row['interventions'] + (int)$row['corrections']; $rates[] = [ 'company' => $row['company'] ?? 'Nicht zugewiesen', 'total' => $total, 'interventions' => (int)$row['interventions'], 'corrections' => (int)$row['corrections'], 'rate' => $total > 0 ? round(($issueCount / $total) * 100, 1) : 0, ]; } return $rates; } private function getStatusTransitions($db, $tenantCampaignIds, $dateFrom, $dateTo): array { $where = ["p.preordercampaign_id IN (" . implode(',', $tenantCampaignIds) . ")", "wj.statusChange IS NOT NULL"]; if ($dateFrom) $where[] = "wj.`create` >= " . intval($dateFrom); if ($dateTo) $where[] = "wj.`create` <= " . intval($dateTo); $sql = "SELECT wj.statusChange, COUNT(*) as count FROM thetool.WorkorderJournal wj JOIN thetool.Workorder w ON wj.workorderId = w.id JOIN thetool.Preorder p ON w.preorderId = p.id WHERE " . implode(' AND ', $where) . " GROUP BY wj.statusChange ORDER BY count DESC LIMIT 15"; $result = $db->query($sql); $transitions = []; while ($row = $result->fetch_assoc()) { $transitions[] = ['transition' => $row['statusChange'], 'count' => (int)$row['count']]; } return $transitions; } private function getEmptyDashboardData(): array { return [ 'kpis' => ['total' => 0, 'offen' => 0, 'terminisiert' => 0, 'issues' => 0, 'interventionRate' => 0, 'avgCompletionDays' => null], 'statusDistribution' => [], 'companyPerformance' => [], 'timeTrends' => [], 'companyStatusCampaign' => [], 'interventionRates' => [], 'statusTransitions' => [], ]; } }