You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
virt/web/app/Services/Cloud/CloudAdminOverviewService.php

442 lines
18 KiB
PHTML

<?php
namespace App\Services\Cloud;
use App\Models\CloudHost;
use App\Models\CloudInstance;
use App\Models\CloudInvoice;
use App\Models\CloudJob;
use App\Models\Ticket;
use App\Models\User;
use App\Services\Config;
use DateTimeImmutable;
use DateTimeZone;
/**
* Database-only operational snapshot for the administrator landing page.
*
* The overview deliberately avoids the engine and hypervisors. A configured
* host is therefore described as enabled, never as online, and instance power
* state is not inferred from lifecycle records.
*/
final class CloudAdminOverviewService
{
private const DAY = 86400;
private const TREND_DAYS = 7;
public static function snapshot(): array
{
$window = self::timeWindow();
$now = $window['now'];
$hosts = self::hosts();
$instances = self::instances($now, $window['week_end']);
$jobs = self::jobs($now, $window['today'], $window['tomorrow']);
$revenue = self::revenue($window);
$receivables = self::receivables($now);
$users = self::users($window['today'], $window['tomorrow']);
$tickets = self::tickets();
$topPlans = self::topPlans($window['month'], $window['tomorrow']);
return [
'available' => 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);
}
}