true, 'generated_at' => self::formatTimestamp($now, 'Y-m-d H:i:s'), 'hosts' => $hosts, 'instances' => $instances, 'jobs' => $jobs, 'revenue' => $revenue, 'receivables' => $receivables, 'users' => $users, 'tickets' => $tickets, 'top_plans' => $topPlans, 'health' => [ 'hosts_percent' => self::percentage($hosts['enabled'], $hosts['total']), 'service_percent' => self::percentage( $instances['service_normal'], $instances['managed'] ), 'network_percent' => self::percentage( $instances['network_normal'], $instances['managed'] ), ], 'attention_total' => $jobs['uncertain'] + $jobs['failed_24h'] + $instances['network_attention'] + $receivables['overdue_count'] + $tickets['open'], ]; } public static function unavailable(): array { $window = self::timeWindow(); $trend = []; foreach ($window['trend'] as $day) { $trend[] = self::trendItem($day['start'], '0.00', 0, 0); } return [ 'available' => false, 'generated_at' => self::formatTimestamp($window['now'], 'Y-m-d H:i:s'), 'hosts' => ['total' => 0, 'enabled' => 0], 'instances' => [ 'managed' => 0, 'billable' => 0, 'customer_count' => 0, 'service_normal' => 0, 'service_attention' => 0, 'network_normal' => 0, 'network_attention' => 0, 'expiring_7d' => 0, 'grace' => 0, ], 'jobs' => [ 'queued' => 0, 'running' => 0, 'active' => 0, 'uncertain' => 0, 'failed_24h' => 0, 'today_total' => 0, 'today_succeeded' => 0, 'today_failed' => 0, ], 'revenue' => [ 'today' => '0.00', 'yesterday' => '0.00', 'month' => '0.00', 'today_count' => 0, 'trend_total' => '0.00', 'trend_count' => 0, 'trend' => $trend, ], 'receivables' => [ 'count' => 0, 'amount' => '0.00', 'overdue_count' => 0, 'overdue_amount' => '0.00', 'renewal_count' => 0, ], 'users' => ['total' => 0, 'enabled' => 0, 'new_today' => 0], 'tickets' => ['open' => 0], 'top_plans' => [], 'health' => [ 'hosts_percent' => 0, 'service_percent' => 0, 'network_percent' => 0, ], 'attention_total' => 0, ]; } private static function hosts(): array { $row = CloudHost::query()->selectRaw( 'COUNT(*) AS total, ' . 'SUM(CASE WHEN enabled = 1 THEN 1 ELSE 0 END) AS enabled_count' )->first(); return [ 'total' => (int) ($row?->total ?? 0), 'enabled' => (int) ($row?->enabled_count ?? 0), ]; } private static function instances(int $now, int $weekEnd): array { $row = CloudInstance::where('status', '<>', 'deleted')->selectRaw( "COUNT(*) AS managed, " . "SUM(CASE WHEN service_status <> 'exempt' THEN 1 ELSE 0 END) AS billable, " . 'COUNT(DISTINCT user_id) AS customer_count, ' . "SUM(CASE WHEN status = 'active' AND (service_status = 'exempt' " . "OR (service_status = 'active' AND expires_at IS NOT NULL " . "AND expires_at > {$now})) THEN 1 ELSE 0 END) AS service_normal, " . "SUM(CASE WHEN status = 'active' AND network_status = 'active' " . 'THEN 1 ELSE 0 END) AS network_normal, ' . "SUM(CASE WHEN status = 'active' AND service_status <> 'exempt' " . "AND expires_at > {$now} AND expires_at <= {$weekEnd} THEN 1 ELSE 0 END) " . 'AS expiring_7d, ' . "SUM(CASE WHEN status = 'active' AND service_status <> 'exempt' " . "AND expires_at IS NOT NULL AND expires_at <= {$now} " . "AND service_status NOT IN ('purge_stopping','purge_stop_failed'," . "'purge_deleting','purge_failed') THEN 1 ELSE 0 END) AS grace_count" )->first(); $managed = (int) ($row?->managed ?? 0); $serviceNormal = (int) ($row?->service_normal ?? 0); $networkNormal = (int) ($row?->network_normal ?? 0); return [ 'managed' => $managed, 'billable' => (int) ($row?->billable ?? 0), 'customer_count' => (int) ($row?->customer_count ?? 0), 'service_normal' => $serviceNormal, 'service_attention' => max(0, $managed - $serviceNormal), 'network_normal' => $networkNormal, 'network_attention' => max(0, $managed - $networkNormal), 'expiring_7d' => (int) ($row?->expiring_7d ?? 0), 'grace' => (int) ($row?->grace_count ?? 0), ]; } private static function jobs(int $now, int $todayStart, int $tomorrow): array { $dayAgo = $now - self::DAY; $row = CloudJob::where('kind', 'create') ->whereNotNull('order_id') ->where(function ($query) use ($dayAgo) { $query->whereIn('status', ['queued', 'running', 'uncertain']) ->orWhere(function ($failed) use ($dayAgo) { $failed->where('status', 'failed') ->where('updated_at', '>=', $dayAgo); }); })->selectRaw( "SUM(CASE WHEN status = 'queued' THEN 1 ELSE 0 END) AS queued_count, " . "SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS running_count, " . "SUM(CASE WHEN status = 'uncertain' THEN 1 ELSE 0 END) AS uncertain_count, " . "SUM(CASE WHEN status = 'failed' AND updated_at >= {$dayAgo} " . 'THEN 1 ELSE 0 END) AS failed_24h' )->first(); $today = CloudJob::where('kind', 'create') ->whereNotNull('order_id') ->where(function ($query) use ($todayStart, $tomorrow) { $query->where(function ($created) use ($todayStart, $tomorrow) { $created->where('created_at', '>=', $todayStart) ->where('created_at', '<', $tomorrow); })->orWhere(function ($completed) use ($todayStart, $tomorrow) { $completed->whereIn('status', ['succeeded', 'failed']) ->where('updated_at', '>=', $todayStart) ->where('updated_at', '<', $tomorrow); }); }) ->selectRaw( "SUM(CASE WHEN created_at >= {$todayStart} AND created_at < {$tomorrow} " . "THEN 1 ELSE 0 END) AS total, " . "SUM(CASE WHEN status = 'succeeded' AND updated_at >= {$todayStart} " . "AND updated_at < {$tomorrow} THEN 1 ELSE 0 END) AS succeeded, " . "SUM(CASE WHEN status = 'failed' AND updated_at >= {$todayStart} " . "AND updated_at < {$tomorrow} THEN 1 ELSE 0 END) AS failed" )->first(); $queued = (int) ($row?->queued_count ?? 0); $running = (int) ($row?->running_count ?? 0); return [ 'queued' => $queued, 'running' => $running, 'active' => $queued + $running, 'uncertain' => (int) ($row?->uncertain_count ?? 0), 'failed_24h' => (int) ($row?->failed_24h ?? 0), 'today_total' => (int) ($today?->total ?? 0), 'today_succeeded' => (int) ($today?->succeeded ?? 0), 'today_failed' => (int) ($today?->failed ?? 0), ]; } private static function revenue(array $window): array { $monthStart = $window['month']; $yesterdayStart = $window['yesterday']; $todayStart = $window['today']; $tomorrow = $window['tomorrow']; $trendStart = $window['trend'][0]['start']; $windowStart = min($monthStart, $trendStart, $yesterdayStart); $select = [ 'COALESCE(SUM(CASE WHEN paid_at >= ? AND paid_at < ? ' . 'THEN amount ELSE 0 END), 0) AS today_amount', 'SUM(CASE WHEN paid_at >= ? AND paid_at < ? THEN 1 ELSE 0 END) ' . 'AS today_count', 'COALESCE(SUM(CASE WHEN paid_at >= ? AND paid_at < ? ' . 'THEN amount ELSE 0 END), 0) AS yesterday_amount', 'COALESCE(SUM(CASE WHEN paid_at >= ? AND paid_at < ? ' . 'THEN amount ELSE 0 END), 0) AS month_amount', ]; $bindings = [ $todayStart, $tomorrow, $todayStart, $tomorrow, $yesterdayStart, $todayStart, $monthStart, $tomorrow, ]; $days = []; foreach ($window['trend'] as $index => $day) { $start = $day['start']; $end = $day['end']; $select[] = "COALESCE(SUM(CASE WHEN paid_at >= ? AND paid_at < ? " . "THEN amount ELSE 0 END), 0) AS day_{$index}_amount"; $select[] = "SUM(CASE WHEN paid_at >= ? AND paid_at < ? " . "THEN 1 ELSE 0 END) AS day_{$index}_count"; array_push($bindings, $start, $end, $start, $end); $days[] = $start; } $row = CloudInvoice::where('status', 'paid') ->whereNotNull('paid_at') ->where('paid_at', '>=', $windowStart) ->where('paid_at', '<', $tomorrow) ->selectRaw(implode(', ', $select), $bindings) ->first(); $rawTrend = []; $maximum = 0.0; $trendTotal = 0.0; $trendCount = 0; foreach ($days as $index => $start) { $amount = self::money($row?->{'day_' . $index . '_amount'} ?? '0'); $count = (int) ($row?->{'day_' . $index . '_count'} ?? 0); $maximum = max($maximum, (float) $amount); $trendTotal += (float) $amount; $trendCount += $count; $rawTrend[] = [$start, $amount, $count]; } $trend = array_map(static function (array $item) use ($maximum): array { $percent = $maximum > 0 ? (int) round((float) $item[1] / $maximum * 100) : 0; return self::trendItem($item[0], $item[1], $item[2], $percent); }, $rawTrend); return [ 'today' => self::money($row?->today_amount ?? '0'), 'yesterday' => self::money($row?->yesterday_amount ?? '0'), 'month' => self::money($row?->month_amount ?? '0'), 'today_count' => (int) ($row?->today_count ?? 0), 'trend_total' => self::money($trendTotal), 'trend_count' => $trendCount, 'trend' => $trend, ]; } private static function receivables(int $now): array { $liveTarget = "(purpose <> 'renewal' OR EXISTS (" . 'SELECT 1 FROM cloud_instances AS overview_instance ' . 'WHERE overview_instance.id = cloud_invoices.instance_id ' . 'AND overview_instance.user_id = cloud_invoices.user_id ' . "AND overview_instance.status <> 'deleted'))"; $row = CloudInvoice::whereIn('status', ['open', 'overdue']) ->whereRaw($liveTarget) ->selectRaw( 'COUNT(*) AS invoice_count, COALESCE(SUM(amount), 0) AS amount, ' . "SUM(CASE WHEN status = 'overdue' OR due_at <= {$now} " . 'THEN 1 ELSE 0 END) AS overdue_count, ' . "COALESCE(SUM(CASE WHEN status = 'overdue' OR due_at <= {$now} " . 'THEN amount ELSE 0 END), 0) AS overdue_amount, ' . "SUM(CASE WHEN purpose = 'renewal' THEN 1 ELSE 0 END) AS renewal_count" )->first(); return [ 'count' => (int) ($row?->invoice_count ?? 0), 'amount' => self::money($row?->amount ?? '0'), 'overdue_count' => (int) ($row?->overdue_count ?? 0), 'overdue_amount' => self::money($row?->overdue_amount ?? '0'), 'renewal_count' => (int) ($row?->renewal_count ?? 0), ]; } private static function users(int $todayStart, int $tomorrow): array { $from = self::formatTimestamp($todayStart, 'Y-m-d H:i:s'); $to = self::formatTimestamp($tomorrow, 'Y-m-d H:i:s'); $row = User::where('is_admin', 0)->selectRaw( 'COUNT(*) AS total, ' . 'SUM(CASE WHEN enable = 1 THEN 1 ELSE 0 END) AS enabled_count, ' . 'SUM(CASE WHEN reg_date >= ? AND reg_date < ? THEN 1 ELSE 0 END) ' . 'AS new_today', [$from, $to] )->first(); return [ 'total' => (int) ($row?->total ?? 0), 'enabled' => (int) ($row?->enabled_count ?? 0), 'new_today' => (int) ($row?->new_today ?? 0), ]; } private static function tickets(): array { return [ 'open' => (int) Ticket::where('rootid', 0)->where('status', 1)->count(), ]; } private static function topPlans(int $monthStart, int $tomorrow): array { $rows = CloudInvoice::where('cloud_invoices.status', 'paid') ->where('cloud_invoices.purpose', 'provision') ->where('cloud_invoices.paid_at', '>=', $monthStart) ->where('cloud_invoices.paid_at', '<', $tomorrow) ->leftJoin('shop', 'shop.id', '=', 'cloud_invoices.plan_id') ->groupBy('cloud_invoices.plan_id', 'shop.name') ->orderByDesc('invoice_count') ->orderByDesc('paid_amount') ->limit(5) ->selectRaw( 'cloud_invoices.plan_id, shop.name, COUNT(*) AS invoice_count, ' . 'COALESCE(SUM(cloud_invoices.amount), 0) AS paid_amount' )->get(); return array_map(static function ($row): array { $planId = $row->plan_id === null ? '' : (string) $row->plan_id; $name = trim((string) ($row->name ?? '')); if ($name === '') { $name = $planId === '' ? '未记录套餐' : '已下架套餐 #' . $planId; } return [ 'name' => $name, 'count' => (int) ($row->invoice_count ?? 0), 'amount' => self::money($row->paid_amount ?? '0'), ]; }, $rows->all()); } private static function trendItem(int $start, string $amount, int $count, int $percent): array { $weekdays = ['周日', '周一', '周二', '周三', '周四', '周五', '周六']; return [ 'date' => self::formatTimestamp($start, 'Y-m-d'), 'label' => self::formatTimestamp($start, 'm/d'), 'weekday' => $weekdays[(int) self::formatTimestamp($start, 'w')], 'amount' => $amount, 'count' => $count, 'percent' => max(0, min(100, $percent)), ]; } private static function money($value): string { return number_format((float) $value, 2, '.', ''); } private static function percentage(int $value, int $total): float { if ($total <= 0) { return 0.0; } return round(min(100, max(0, $value / $total * 100)), 1); } /** Calendar boundaries are derived in the configured timezone, including DST days. */ private static function timeWindow(): array { $timezone = self::timezone(); $now = new DateTimeImmutable('now', $timezone); $today = $now->setTime(0, 0, 0); $trend = []; for ($offset = self::TREND_DAYS - 1; $offset >= 0; $offset--) { $start = $today->modify('-' . $offset . ' days'); $trend[] = [ 'start' => $start->getTimestamp(), 'end' => $start->modify('+1 day')->getTimestamp(), ]; } return [ 'now' => $now->getTimestamp(), 'today' => $today->getTimestamp(), 'tomorrow' => $today->modify('+1 day')->getTimestamp(), 'yesterday' => $today->modify('-1 day')->getTimestamp(), 'month' => $today->modify('first day of this month')->getTimestamp(), 'week_end' => $now->modify('+7 days')->getTimestamp(), 'trend' => $trend, ]; } private static function timezone(): DateTimeZone { try { return new DateTimeZone( (string) Config::get('timeZone', date_default_timezone_get()) ); } catch (\Throwable $exception) { return new DateTimeZone(date_default_timezone_get() ?: 'UTC'); } } private static function formatTimestamp(int $timestamp, string $format): string { return (new DateTimeImmutable('@' . $timestamp)) ->setTimezone(self::timezone()) ->format($format); } }