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.
442 lines
18 KiB
PHP
442 lines
18 KiB
PHP
<?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);
|
|
}
|
|
}
|