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/CloudAdminBillingService.php

1306 lines
58 KiB
PHP

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

<?php
namespace App\Services\Cloud;
use App\Models\CloudInstance;
use App\Models\CloudInvoice;
use App\Models\CloudInvoicePayment;
use App\Models\CloudJob;
use App\Models\CloudOrder;
use App\Models\Shop;
use App\Models\User;
use App\Services\AdminDataMasker;
use App\Services\AdminPermission;
/**
* Read-only, administrator-scoped billing queries.
*
* Financial state, fulfilment state and provider attempts deliberately remain
* separate. The service returns explicit allowlists rather than serializing
* models, so encrypted payloads and provider references cannot leak when a
* model gains a new column.
*/
final class CloudAdminBillingService
{
private const DEFAULT_PER_PAGE = 30;
private const MAX_PER_PAGE = 100;
private const DETAIL_PAYMENT_LIMIT = 100;
private const PURPOSES = ['provision', 'renewal', 'recharge'];
private const FINANCIAL_STATUSES = [
'open', 'overdue', 'paid', 'refunded', 'expired', 'void',
];
private const ORDER_STATUSES = [
'awaiting_payment', 'queued', 'pending', 'running', 'uncertain',
'succeeded', 'failed', 'expired', 'refunded', 'cancelled',
];
private const PAYMENT_STATUSES = [
'creating', 'pending', 'failed', 'paid_invoice', 'credited_balance',
];
private const PROVIDERS = [
'balance', 'yipay_alipay', 'yipay_wxpay', 'paytaro', 'usdt',
];
private const FULFILLMENT_STATUSES = [
'awaiting_payment', 'queued', 'pending', 'running', 'uncertain',
'succeeded', 'failed', 'expired', 'refunded', 'cancelled',
];
/** The SSR page and its JSON refreshes share the same read-only queries. */
public static function page(array $input): array
{
$view = $input['view'] ?? 'invoices';
if (!is_string($view) || !in_array($view, ['invoices', 'orders', 'payments'], true)) {
throw new CloudError('账单视图无效', 400);
}
$payload = match ($view) {
'orders' => self::orders($input),
'payments' => self::payments($input),
default => self::invoices($input),
};
return ['view' => $view] + $payload;
}
public static function invoices(array $input): array
{
$filters = self::invoiceFilters($input);
$query = CloudInvoice::query();
self::applyInvoiceFilters($query, $filters);
$summary = self::invoiceSummary(clone $query);
$total = $summary['total_records'];
$pagination = self::pagination($filters['page'], $filters['per_page'], $total);
$records = (clone $query)->orderByDesc('cloud_invoices.created_at')
->orderByDesc('cloud_invoices.id')
->offset(($pagination['page'] - 1) * $pagination['per_page'])
->limit($pagination['per_page'])
->get([
'cloud_invoices.id', 'cloud_invoices.purpose',
'cloud_invoices.instance_id', 'cloud_invoices.order_id',
'cloud_invoices.user_id', 'cloud_invoices.plan_id',
'cloud_invoices.renewal_id', 'cloud_invoices.renews_expires_at',
'cloud_invoices.due_at', 'cloud_invoices.amount',
'cloud_invoices.validity_days',
'cloud_invoices.traffic_quota_bytes', 'cloud_invoices.status',
'cloud_invoices.paid_via', 'cloud_invoices.paid_at',
'cloud_invoices.created_at', 'cloud_invoices.updated_at',
]);
return [
'items' => self::invoiceRows($records->all()),
'pagination' => $pagination,
'summary' => $summary,
'filters' => $filters,
];
}
public static function invoice(string $id): array
{
$invoiceId = self::identifier($id, '账单编号');
$invoice = CloudInvoice::where('id', $invoiceId)->first();
if ($invoice === null) {
throw new CloudError('账单不存在', 404);
}
$context = self::invoiceContext([$invoice]);
$row = self::invoiceRow($invoice, $context);
$order = $invoice->order_id === null ? null
: self::recordById($context['orders'], $invoice->order_id);
if ($order !== null
&& !self::sameIdentifier($order->user_id, $invoice->user_id)) {
$order = null;
}
$job = $order === null ? null
: self::recordById($context['jobs'], $order->id);
if ($job !== null
&& !self::sameIdentifier($job->user_id, $invoice->user_id)) {
$job = null;
}
$instanceId = self::linkedInstanceId($invoice, $order);
$instance = $instanceId === null ? null
: self::recordById($context['instances'], $instanceId);
if ($instance !== null
&& !self::sameIdentifier($instance->user_id, $invoice->user_id)) {
$instance = null;
}
$paymentQuery = CloudInvoicePayment::where('invoice_id', $invoice->id)
->where('user_id', $invoice->user_id);
$retained = (clone $paymentQuery)->where('status', 'credited_balance')
->selectRaw('COUNT(*) AS retained_count, COALESCE(SUM(amount), 0) AS retained_amount')->first();
$payments = (clone $paymentQuery)->orderByDesc('id')
->limit(self::DETAIL_PAYMENT_LIMIT + 1)
->get(['id', 'invoice_id', 'user_id', 'provider', 'amount', 'status', 'created_at', 'updated_at'])
->all();
$paymentsTruncated = count($payments) > self::DETAIL_PAYMENT_LIMIT;
if ($paymentsTruncated) {
$payments = array_slice($payments, 0, self::DETAIL_PAYMENT_LIMIT);
}
$row['validity_days'] = max(0, (int) $invoice->validity_days);
$row['traffic_quota_bytes'] = max(0, (int) $invoice->traffic_quota_bytes);
$row['renews_expires_at'] = $invoice->renews_expires_at === null
? null : (int) $invoice->renews_expires_at;
$row['payment_deadline_at'] = CloudInvoiceService::paymentDeadline($invoice);
$row['specification'] = self::specification($order, $instance);
return [
'invoice' => $row,
'order' => $order === null ? null
: self::orderRow($order, self::orderContext([$order])),
'job' => $job === null ? null : self::jobRow($job),
'instance' => $instance === null ? null : self::instanceRow($instance),
'payment_attempts' => array_map(
static fn ($payment): array => self::paymentRow(
$payment,
['users' => $context['users'],
'invoices' => self::keyed([$invoice]),
'instances' => $context['instances']]
),
$payments
),
'payment_attempts_truncated' => $paymentsTruncated,
'funds' => self::funds($invoice, $order, $retained, $paymentsTruncated,
$row['financial_status']),
'timeline' => self::timeline($invoice, $order, $job, $payments,
$row['financial_status']),
'links' => self::links($invoice, $instance, $context['users']),
];
}
/** Explain recorded outcomes, not an inferred wallet balance or cash ledger. */
private static function funds($invoice, $order, $retained, bool $truncated,
string $financialStatus): array
{
$status = (string) $invoice->status;
$paid = $status === 'paid' || self::recordedTime($invoice->paid_at) !== null;
$refundedAt = $status === 'refunded' && (string) $invoice->purpose === 'provision'
&& $order !== null ? self::recordedTime($order->refunded_at) : null;
$refundAmount = $refundedAt === null ? null : self::money($order->amount);
$titles = ['paid' => '账单已结清', 'refunded' => '账单标记已退款',
'open' => '账单尚未结清', 'overdue' => '账单尚未结清',
'expired' => '账单已失效', 'void' => '账单已作废'];
$title = $titles[$financialStatus] ?? '资金状态待核对';
$description = '请结合账单状态与支付记录核对;支付尝试金额不等同于实际到账金额。';
if ($status === 'paid') {
$description = (string) $invoice->paid_via === 'balance'
? '该账单已使用账户余额结清;余额支付不是一次新的外部充值。'
: '该账单已结清。在线支付记录单独记录网关支付结果,不应与账单金额重复计算收入。';
} elseif ($refundedAt !== null) {
$title = '开通款项已退回账户余额';
$description = '关联开通订单记录已将款项退回该用户的账户余额,不代表支付网关原路退款。';
} elseif ($status === 'refunded') {
$description = '账单记录为已退款,但现有记录不足以确认退款去向或具体时间,请人工核对。';
} elseif (in_array($financialStatus, ['expired', 'void'], true)) {
$description = '失效或作废表示账单不可继续支付;这个状态本身不表示发生过扣款或退款。';
}
$retainedCount = max(0, (int) ($retained?->retained_count ?? 0));
$notes = ['此处汇总该账单的现有业务记录,不是完整账户余额变动流水,也不表示用户当前余额。'];
if ($retainedCount > 0) {
$notes[] = '存在已到账但未由该次付款结清目标账单、因而留在账户余额的款项;这不是账单退款,现有记录未保存具体留存原因。';
}
if ($refundAmount !== null && $refundAmount !== self::money($invoice->amount)) {
$notes[] = '关联订单记录的退款金额与账单面额不同,请核对历史记录。';
}
if ($truncated) {
$notes[] = '下方仅展示最近 100 条支付记录;留存余额金额按该账单全部关联支付记录汇总。';
}
return [
'title' => $title, 'description' => $description,
'settled_amount' => $paid ? self::money($invoice->amount) : null,
'refund_amount' => $refundAmount,
'refund_destination' => $refundedAt === null ? null : 'balance',
'refunded_at' => $refundedAt,
'balance_retained_amount' => self::money($retained?->retained_amount ?? '0'),
'balance_retained_count' => $retainedCount,
'payment_records_truncated' => $truncated, 'notes' => $notes,
];
}
/** Only dedicated timestamps imply events; updated_at means last update. */
private static function timeline($invoice, $order, $job, array $payments,
string $financialStatus): array
{
$events = [[
'kind' => 'invoice_created', 'title' => '账单创建',
'description' => '创建账单 #' . $invoice->id . ',不表示已付款。',
'at' => self::recordedTime($invoice->created_at),
]];
if (self::recordedTime($invoice->paid_at) !== null || (string) $invoice->status === 'paid') {
$events[] = ['kind' => 'invoice_paid', 'title' => '账单结清',
'description' => (string) $invoice->paid_via === 'balance'
? '使用账户余额结清账单。' : '记录已结清账单,原支付渠道见账单概览。',
'at' => self::recordedTime($invoice->paid_at)];
}
if ((string) $invoice->status === 'refunded') {
$at = (string) $invoice->purpose === 'provision' && $order !== null
? self::recordedTime($order->refunded_at) : null;
$events[] = ['kind' => 'invoice_refunded',
'title' => $at === null ? '账单当前标记已退款' : '开通款项退回余额',
'description' => $at === null ? '没有可确认退款时间及去向的独立记录。'
: '关联开通订单记录款项退回账户余额;不是网关原路退款。',
'at' => $at];
} elseif (in_array($financialStatus, ['expired', 'void'], true)) {
$events[] = ['kind' => 'invoice_closed',
'title' => $financialStatus === 'void' ? '账单当前已作废' : '账单当前已失效',
'description' => '此状态不是退款记录;未保存独立的状态变更时间。', 'at' => null];
}
if ($job !== null) {
$labels = ['queued' => '排队中', 'running' => '执行中', 'uncertain' => '待核对',
'succeeded' => '已完成', 'failed' => '失败'];
$events[] = ['kind' => 'job_updated', 'title' => '开通任务最近更新',
'description' => '任务 #' . $job->id . ',当前状态:'
. ($labels[(string) $job->status] ?? '其他状态') . '。此时间不是完整的任务事件历史。',
'at' => self::recordedTime($job->updated_at)];
}
$outcomes = ['creating' => '支付订单创建中', 'pending' => '等待支付结果',
'failed' => '支付记录失败', 'paid_invoice' => '该次付款已用于结清账单',
'credited_balance' => '该次款项留在账户余额,不属于账单退款'];
foreach ($payments as $payment) {
$events[] = ['kind' => 'payment_updated', 'title' => '在线支付记录最近更新',
'description' => '支付记录 #' . $payment->id . ':'
. ($outcomes[(string) $payment->status] ?? '其他状态')
. '。此时间来自记录更新时间,不代表网关实际到账时间。',
'at' => self::recordedTime($payment->updated_at)];
}
usort($events, static function (array $left, array $right): int {
if ($left['at'] === null || $right['at'] === null) {
return $left['at'] === $right['at'] ? 0 : ($left['at'] === null ? 1 : -1);
}
return $left['at'] <=> $right['at'];
});
return $events;
}
private static function links($invoice, $instance, array $users): array
{
$user = self::recordById($users, $invoice->user_id);
$uuid = $instance === null ? '' : (string) $instance->uuid;
$visibleInstance = $instance !== null && (string) $instance->status !== 'deleted'
&& self::sameIdentifier($instance->user_id, $invoice->user_id)
&& preg_match('/^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$/iD', $uuid) === 1;
return [
'user' => $user !== null && AdminPermission::isSuperAdmin()
? '/admin/user/' . $invoice->user_id . '/edit' : null,
'instance' => $visibleInstance ? '/admin/cloud/instances?q=' . rawurlencode($uuid) : null,
'payments' => '/admin/billing?view=payments&invoice_id=' . $invoice->id,
];
}
private static function recordedTime($value): ?int
{
return $value !== null && (int) $value > 0 ? (int) $value : null;
}
public static function orders(array $input): array
{
$filters = self::orderFilters($input);
$query = CloudOrder::query();
self::applyCommonFilters($query, 'cloud_orders', $filters);
if ($filters['status'] !== '') {
self::applyOrderFulfillmentStatus($query, $filters['status']);
}
if ($filters['financial_status'] !== '') {
self::whereInvoiceFinancialStatus($query, $filters['financial_status'],
'cloud_orders.id', 'order_id', 'cloud_orders.user_id');
}
self::applyOrderSearch($query, $filters['q']);
$summary = self::orderSummary(clone $query);
$total = $summary['total_records'];
$pagination = self::pagination($filters['page'], $filters['per_page'], $total);
$records = (clone $query)->orderByDesc('cloud_orders.created_at')
->orderByDesc('cloud_orders.id')
->offset(($pagination['page'] - 1) * $pagination['per_page'])
->limit($pagination['per_page'])
->get([
'cloud_orders.id', 'cloud_orders.user_id', 'cloud_orders.plan_id',
'cloud_orders.host_id', 'cloud_orders.amount',
'cloud_orders.status', 'cloud_orders.spec_json',
'cloud_orders.instance_id', 'cloud_orders.refunded_at',
'cloud_orders.created_at', 'cloud_orders.updated_at',
]);
$context = self::orderContext($records->all());
return [
'items' => array_map(
static fn ($order): array => self::orderRow($order, $context),
$records->all()
),
'pagination' => $pagination,
'summary' => $summary,
'filters' => $filters,
];
}
public static function payments(array $input): array
{
$filters = self::paymentFilters($input);
$query = CloudInvoicePayment::query();
self::applyCommonFilters($query, 'cloud_invoice_payments', $filters);
if ($filters['status'] !== '') {
$query->where('cloud_invoice_payments.status', $filters['status']);
}
if ($filters['provider'] !== '') {
$query->where('cloud_invoice_payments.provider', $filters['provider']);
}
if ($filters['invoice_id'] !== null) {
$query->where('cloud_invoice_payments.invoice_id', $filters['invoice_id']);
}
self::applyPaymentSearch($query, $filters['q']);
$summary = self::paymentSummary(clone $query);
$total = $summary['total_records'];
$pagination = self::pagination($filters['page'], $filters['per_page'], $total);
$records = (clone $query)
->orderByDesc('cloud_invoice_payments.created_at')
->orderByDesc('cloud_invoice_payments.id')
->offset(($pagination['page'] - 1) * $pagination['per_page'])
->limit($pagination['per_page'])
->get([
'cloud_invoice_payments.id', 'cloud_invoice_payments.invoice_id',
'cloud_invoice_payments.user_id',
'cloud_invoice_payments.provider',
'cloud_invoice_payments.amount', 'cloud_invoice_payments.status',
'cloud_invoice_payments.created_at',
'cloud_invoice_payments.updated_at',
]);
$context = self::paymentContext($records->all());
return [
'items' => array_map(
static fn ($payment): array => self::paymentRow($payment, $context),
$records->all()
),
'pagination' => $pagination,
'summary' => $summary,
'filters' => $filters,
];
}
private static function invoiceFilters(array $input): array
{
$filters = self::commonFilters($input);
$filters['purpose'] = self::enum($input, 'purpose', self::PURPOSES);
$filters['financial_status'] = self::enum(
$input, 'financial_status', self::FINANCIAL_STATUSES
);
$filters['fulfillment_status'] = self::enum(
$input, 'fulfillment_status', self::FULFILLMENT_STATUSES
);
$filters['payment_method'] = self::enum(
$input, 'payment_method', self::PROVIDERS
);
return $filters;
}
private static function orderFilters(array $input): array
{
$filters = self::commonFilters($input);
$filters['status'] = self::enum($input, 'status', self::ORDER_STATUSES);
$filters['financial_status'] = self::enum(
$input, 'financial_status', self::FINANCIAL_STATUSES
);
return $filters;
}
private static function paymentFilters(array $input): array
{
$filters = self::commonFilters($input);
$filters['status'] = self::enum($input, 'status', self::PAYMENT_STATUSES);
$filters['provider'] = self::enum($input, 'provider', self::PROVIDERS);
$filters['invoice_id'] = self::optionalIdentifier($input, 'invoice_id');
return $filters;
}
private static function commonFilters(array $input): array
{
$page = self::positiveInteger($input, 'page', 1);
$perPage = self::positiveInteger($input, 'per_page', self::DEFAULT_PER_PAGE);
$perPage = min(self::MAX_PER_PAGE, $perPage);
$q = self::text($input, 'q', 128);
$userId = self::optionalIdentifier($input, 'user_id');
$from = self::optionalTimestamp($input, 'created_from');
$to = self::optionalTimestamp($input, 'created_to');
if ($from !== null && $to !== null && $from > $to) {
throw new CloudError('开始时间不能晚于结束时间');
}
return ['page' => $page, 'per_page' => $perPage, 'q' => $q,
'user_id' => $userId, 'created_from' => $from, 'created_to' => $to];
}
private static function applyInvoiceFilters($query, array $filters): void
{
self::applyCommonFilters($query, 'cloud_invoices', $filters);
if ($filters['purpose'] !== '') {
$query->where('cloud_invoices.purpose', $filters['purpose']);
}
if ($filters['financial_status'] !== '') {
self::applyInvoiceFinancialStatus($query, 'cloud_invoices',
$filters['financial_status']);
}
if ($filters['payment_method'] !== '') {
// The invoice list reports the settled method (paid_via). Payment
// attempts, including failed providers, live in the Payments view.
$query->where('cloud_invoices.paid_via',
$filters['payment_method']);
}
if ($filters['fulfillment_status'] !== '') {
$status = $filters['fulfillment_status'];
$query->where(function ($fulfillment) use ($status) {
$fulfillment->where(function ($provision) use ($status) {
$provision->where('cloud_invoices.purpose', 'provision')
->whereExists(function ($orders) use ($status) {
$orders->selectRaw('1')->from('cloud_orders')
->leftJoin('cloud_jobs', function ($join) {
$join->on('cloud_jobs.order_id', '=',
'cloud_orders.id')
->on('cloud_jobs.user_id', '=',
'cloud_orders.user_id');
})
->whereColumn('cloud_orders.id',
'cloud_invoices.order_id')
->whereColumn('cloud_orders.user_id',
'cloud_invoices.user_id')
->where(function ($value) use ($status) {
$value->where('cloud_jobs.status', $status)
->orWhere(function ($withoutJob) use ($status) {
$withoutJob->whereNull('cloud_jobs.id')
->where('cloud_orders.status', $status);
});
});
});
});
self::orWhereRenewalFulfillment($fulfillment, $status);
});
}
self::applyInvoiceSearch($query, $filters['q']);
}
private static function applyCommonFilters($query, string $table,
array $filters): void
{
if ($filters['user_id'] !== null) {
$query->where($table . '.user_id', $filters['user_id']);
}
if ($filters['created_from'] !== null) {
$query->where($table . '.created_at', '>=', $filters['created_from']);
}
if ($filters['created_to'] !== null) {
$query->where($table . '.created_at', '<=', $filters['created_to']);
}
}
private static function applyInvoiceSearch($query, string $q): void
{
if ($q === '') {
return;
}
$numeric = self::normalizeIdentifier($q);
$canSearchAccount = !AdminDataMasker::shouldMask();
$query->where(function ($builder) use ($q, $numeric, $canSearchAccount) {
if ($numeric !== null) {
$builder->where('cloud_invoices.id', $numeric)
->orWhere('cloud_invoices.user_id', $numeric)
->orWhere('cloud_invoices.plan_id', $numeric)
->orWhere('cloud_invoices.order_id', $numeric)
->orWhere('cloud_invoices.instance_id', $numeric);
} else {
$builder->whereRaw('1 = 0');
}
if ($canSearchAccount) {
$builder->orWhereExists(function ($users) use ($q) {
$users->selectRaw('1')->from('user')
->whereColumn('user.id', 'cloud_invoices.user_id')
->where(function ($account) use ($q) {
$account->where('user.user_name', 'like', '%' . $q . '%')
->orWhere('user.email', 'like', '%' . $q . '%');
});
});
}
$builder->orWhereExists(function ($plans) use ($q) {
$plans->selectRaw('1')->from('shop')
->whereColumn('shop.id', 'cloud_invoices.plan_id')
->where('shop.name', 'like', '%' . $q . '%');
});
});
}
private static function applyOrderSearch($query, string $q): void
{
if ($q === '') {
return;
}
$numeric = self::normalizeIdentifier($q);
$canSearchAccount = !AdminDataMasker::shouldMask();
$query->where(function ($builder) use ($q, $numeric, $canSearchAccount) {
if ($numeric !== null) {
$builder->where('cloud_orders.id', $numeric)
->orWhere('cloud_orders.user_id', $numeric)
->orWhere('cloud_orders.plan_id', $numeric)
->orWhere('cloud_orders.instance_id', $numeric);
} else {
$builder->whereRaw('1 = 0');
}
if ($canSearchAccount) {
$builder->orWhereExists(function ($users) use ($q) {
$users->selectRaw('1')->from('user')
->whereColumn('user.id', 'cloud_orders.user_id')
->where(function ($account) use ($q) {
$account->where('user.user_name', 'like', '%' . $q . '%')
->orWhere('user.email', 'like', '%' . $q . '%');
});
});
}
$builder->orWhereExists(function ($plans) use ($q) {
$plans->selectRaw('1')->from('shop')
->whereColumn('shop.id', 'cloud_orders.plan_id')
->where('shop.name', 'like', '%' . $q . '%');
});
});
}
private static function applyPaymentSearch($query, string $q): void
{
if ($q === '') {
return;
}
$numeric = self::normalizeIdentifier($q);
$canSearchAccount = !AdminDataMasker::shouldMask();
$query->where(function ($builder) use ($q, $numeric, $canSearchAccount) {
if ($numeric !== null) {
$builder->where('cloud_invoice_payments.id', $numeric)
->orWhere('cloud_invoice_payments.invoice_id', $numeric)
->orWhere('cloud_invoice_payments.user_id', $numeric);
} else {
$builder->whereRaw('1 = 0');
}
if ($canSearchAccount) {
$builder->orWhereExists(function ($users) use ($q) {
$users->selectRaw('1')->from('user')
->whereColumn('user.id', 'cloud_invoice_payments.user_id')
->where(function ($account) use ($q) {
$account->where('user.user_name', 'like', '%' . $q . '%')
->orWhere('user.email', 'like', '%' . $q . '%');
});
});
}
});
}
/** Match the same job-first fulfilment state returned by orderRow(). */
private static function applyOrderFulfillmentStatus($query,
string $status): void
{
$query->where(function ($state) use ($status) {
$state->whereExists(function ($job) use ($status) {
$job->selectRaw('1')->from('cloud_jobs')
->whereColumn('cloud_jobs.order_id', 'cloud_orders.id')
->whereColumn('cloud_jobs.user_id', 'cloud_orders.user_id')
->where('cloud_jobs.status', $status);
})->orWhere(function ($withoutJob) use ($status) {
$withoutJob->where('cloud_orders.status', $status)
->whereNotExists(function ($job) {
$job->selectRaw('1')->from('cloud_jobs')
->whereColumn('cloud_jobs.order_id',
'cloud_orders.id')
->whereColumn('cloud_jobs.user_id',
'cloud_orders.user_id');
});
});
});
}
private static function whereInvoiceFinancialStatus($query, string $status,
string $outerColumn, string $invoiceForeignKey,
string $outerUserColumn): void
{
$query->whereExists(function ($invoice) use ($status, $outerColumn,
$invoiceForeignKey, $outerUserColumn) {
$invoice->selectRaw('1')->from('cloud_invoices')
->whereColumn('cloud_invoices.' . $invoiceForeignKey, $outerColumn)
->whereColumn('cloud_invoices.user_id', $outerUserColumn);
self::applyFinancialStatus($invoice, 'cloud_invoices', $status);
});
}
private static function applyFinancialStatus($query, string $table,
string $status): void
{
$now = time();
if ($status === 'overdue') {
$query->where(function ($value) use ($table, $now) {
$value->where($table . '.status', 'overdue')
->orWhere(function ($open) use ($table, $now) {
$open->where($table . '.status', 'open')
->where($table . '.due_at', '<=', $now);
});
});
return;
}
if ($status === 'open') {
$query->where($table . '.status', 'open')
->where($table . '.due_at', '>', $now);
return;
}
$query->where($table . '.status', $status);
}
/** Apply the same derived state used by invoiceRow(). */
private static function applyInvoiceFinancialStatus($query, string $table,
string $status): void
{
if ($status === 'void') {
$query->where(function ($state) use ($table) {
$state->where($table . '.status', 'void')
->orWhere(function ($orphan) use ($table) {
self::whereOrphanOpenRenewal($orphan, $table);
});
});
return;
}
self::applyFinancialStatus($query, $table, $status);
if (in_array($status, ['open', 'overdue'], true)) {
self::whereValidRenewalTarget($query, $table);
}
}
/** Exclude orphan/deleted renewal targets from actionable states. */
private static function whereValidRenewalTarget($query, string $table): void
{
$query->where(function ($target) use ($table) {
$target->where($table . '.purpose', '<>', 'renewal')
->orWhereExists(function ($instance) use ($table) {
self::liveInstanceQuery($instance, $table);
});
});
}
private static function whereOrphanOpenRenewal($query, string $table): void
{
$query->where($table . '.purpose', 'renewal')
->whereIn($table . '.status', ['open', 'overdue'])
->whereNotExists(function ($instance) use ($table) {
self::liveInstanceQuery($instance, $table);
});
}
private static function liveInstanceQuery($query, string $invoiceTable): void
{
$query->selectRaw('1')->from('cloud_instances')
->whereColumn('cloud_instances.id', $invoiceTable . '.instance_id')
->whereColumn('cloud_instances.user_id', $invoiceTable . '.user_id')
->where('cloud_instances.status', '<>', 'deleted');
}
private static function orWhereRenewalFulfillment($query, string $status): void
{
if (!in_array($status,
['awaiting_payment', 'succeeded', 'uncertain', 'refunded', 'cancelled'],
true)) {
return;
}
$query->orWhere(function ($renewal) use ($status) {
$renewal->where('cloud_invoices.purpose', 'renewal');
if ($status === 'awaiting_payment') {
$renewal->whereIn('cloud_invoices.status', ['open', 'overdue'])
->whereNull('cloud_invoices.renewal_id');
self::whereValidRenewalTarget($renewal, 'cloud_invoices');
} elseif ($status === 'succeeded') {
$renewal->whereNotNull('cloud_invoices.renewal_id');
} elseif ($status === 'uncertain') {
$renewal->where('cloud_invoices.status', 'paid')
->whereNull('cloud_invoices.renewal_id');
} elseif ($status === 'refunded') {
$renewal->where('cloud_invoices.status', 'refunded')
->whereNull('cloud_invoices.renewal_id');
} else {
$renewal->whereNull('cloud_invoices.renewal_id');
$renewal->where(function ($cancelled) {
$cancelled->whereIn('cloud_invoices.status',
['expired', 'void'])
->orWhere(function ($orphan) {
self::whereOrphanOpenRenewal(
$orphan, 'cloud_invoices'
);
});
});
}
});
}
private static function invoiceRows(array $records): array
{
$context = self::invoiceContext($records);
return array_map(
static fn ($invoice): array => self::invoiceRow($invoice, $context),
$records
);
}
private static function invoiceContext(array $records): array
{
$userIds = self::ids($records, 'user_id');
$orderIds = self::ids($records, 'order_id');
$planIds = self::ids($records, 'plan_id');
$invoiceInstanceIds = self::ids($records, 'instance_id');
$users = self::keyed(User::whereIn('id', $userIds)
->get(['id', 'user_name', 'email'])->all());
$orders = self::keyed(CloudOrder::whereIn('id', $orderIds)
->get(['id', 'user_id', 'plan_id', 'host_id', 'amount', 'status',
'spec_json', 'instance_id', 'refunded_at', 'created_at',
'updated_at'])->all());
$jobs = self::keyed(CloudJob::whereIn('order_id', $orderIds)
->get(['id', 'order_id', 'instance_id', 'user_id', 'kind', 'status',
'attempts', 'error_code', 'result_json', 'created_at',
'updated_at'])->all(),
'order_id');
$orderInstanceIds = self::ids(array_values($orders), 'instance_id');
$instanceIds = array_values(array_unique(array_merge(
$invoiceInstanceIds, $orderInstanceIds
)));
$instances = self::keyed(CloudInstance::whereIn('id', $instanceIds)
->get(['id', 'uuid', 'user_id', 'plan_id', 'name', 'status',
'service_status', 'network_status', 'expires_at', 'spec_json'])
->all());
$planIds = array_values(array_unique(array_merge($planIds,
self::ids(array_values($orders), 'plan_id'),
self::ids(array_values($instances), 'plan_id'))));
$plans = self::keyed(Shop::whereIn('id', $planIds)
->get(['id', 'name'])->all());
return compact('users', 'orders', 'jobs', 'instances', 'plans');
}
private static function invoiceRow($invoice, array $context): array
{
$order = $invoice->order_id === null ? null
: self::recordById($context['orders'], $invoice->order_id);
if ($order !== null
&& !self::sameIdentifier($order->user_id, $invoice->user_id)) {
$order = null;
}
$job = $order === null ? null
: self::recordById($context['jobs'], $order->id);
if ($job !== null
&& !self::sameIdentifier($job->user_id, $invoice->user_id)) {
$job = null;
}
$instanceId = self::linkedInstanceId($invoice, $order);
$instance = $instanceId === null ? null
: self::recordById($context['instances'], $instanceId);
if ($instance !== null
&& !self::sameIdentifier($instance->user_id, $invoice->user_id)) {
$instance = null;
}
$user = self::recordById($context['users'], $invoice->user_id);
$spec = self::snapshot($order?->spec_json ?? $instance?->spec_json ?? null);
$plan = $invoice->plan_id === null ? null
: self::recordById($context['plans'], $invoice->plan_id);
$planName = trim((string) ($spec['plan_name'] ?? $plan?->name ?? ''));
$subject = trim((string) ($spec['name'] ?? $instance?->name ?? ''));
$financialStatus = self::financialStatus($invoice, $instance, true);
return [
'id' => (string) $invoice->id,
'user_id' => (string) $invoice->user_id,
'user_name' => AdminDataMasker::account($user?->user_name ?? ''),
'user_email' => AdminDataMasker::email($user?->email ?? ''),
'purpose' => (string) ($invoice->purpose ?: 'renewal'),
'plan_id' => $invoice->plan_id === null
? null : (string) $invoice->plan_id,
'plan_name' => $planName,
'subject_name' => $subject,
'amount' => self::money($invoice->amount),
'financial_status' => $financialStatus,
'fulfillment_status' => self::fulfillmentStatus(
$invoice, $order, $job, $financialStatus
),
'payment_method' => trim((string) $invoice->paid_via) === ''
? null : (string) $invoice->paid_via,
'due_at' => max(0, (int) $invoice->due_at),
'paid_at' => $invoice->paid_at === null ? null : (int) $invoice->paid_at,
'created_at' => max(0, (int) $invoice->created_at),
'updated_at' => max(0, (int) $invoice->updated_at),
'order_id' => $invoice->order_id === null
? null : (string) $invoice->order_id,
'job_id' => $job === null ? null : (string) $job->id,
'instance_id' => $instanceId === null ? null : (string) $instanceId,
'renewal_id' => $invoice->renewal_id === null
? null : (string) $invoice->renewal_id,
];
}
private static function orderContext(array $records): array
{
$userIds = self::ids($records, 'user_id');
$orderIds = self::ids($records, 'id');
$planIds = self::ids($records, 'plan_id');
return [
'users' => self::keyed(User::whereIn('id', $userIds)
->get(['id', 'user_name', 'email'])->all()),
'plans' => self::keyed(Shop::whereIn('id', $planIds)
->get(['id', 'name'])->all()),
'invoices' => self::keyed(CloudInvoice::whereIn('order_id', $orderIds)
->get(['id', 'purpose', 'order_id', 'instance_id', 'user_id',
'due_at', 'amount', 'status', 'paid_via', 'paid_at',
'created_at', 'updated_at'])->all(), 'order_id'),
'jobs' => self::keyed(CloudJob::whereIn('order_id', $orderIds)
->get(['id', 'order_id', 'instance_id', 'user_id', 'kind',
'status', 'attempts', 'error_code', 'result_json',
'created_at', 'updated_at'])->all(), 'order_id'),
];
}
private static function orderRow($order, array $context): array
{
$user = self::recordById($context['users'], $order->user_id);
$plan = self::recordById($context['plans'], $order->plan_id);
$invoice = self::recordById($context['invoices'], $order->id);
if ($invoice !== null
&& !self::sameIdentifier($invoice->user_id, $order->user_id)) {
$invoice = null;
}
$job = self::recordById($context['jobs'], $order->id);
if ($job !== null
&& !self::sameIdentifier($job->user_id, $order->user_id)) {
$job = null;
}
$spec = self::snapshot($order->spec_json);
return [
'id' => (string) $order->id,
'user_id' => (string) $order->user_id,
'user_name' => AdminDataMasker::account($user?->user_name ?? ''),
'user_email' => AdminDataMasker::email($user?->email ?? ''),
'plan_id' => (string) $order->plan_id,
'plan_name' => trim((string) ($spec['plan_name'] ?? $plan?->name ?? '')),
'host_id' => (string) $order->host_id,
'amount' => self::money($order->amount),
'financial_status' => $invoice === null ? null
: self::financialStatus($invoice, null),
'fulfillment_status' => (string) ($job?->status ?? $order->status),
'invoice_id' => $invoice === null ? null : (string) $invoice->id,
'job_id' => $job === null ? null : (string) $job->id,
'job_status' => $job?->status,
'instance_id' => $order->instance_id === null
? ($job?->instance_id === null ? null : (string) $job->instance_id)
: (string) $order->instance_id,
'attempts' => max(0, (int) ($job?->attempts ?? 0)),
'error' => $job === null ? null : CloudOperationErrorCatalog::adminSummary(
(string) $job->kind, (string) $job->status, $job->error_code,
true, null
),
'refunded_at' => $order->refunded_at === null
? null : (int) $order->refunded_at,
'created_at' => max(0, (int) $order->created_at),
'updated_at' => max(0, (int) $order->updated_at),
];
}
private static function paymentContext(array $records): array
{
$userIds = self::ids($records, 'user_id');
$invoiceIds = self::ids($records, 'invoice_id');
$invoices = self::keyed(CloudInvoice::whereIn('id', $invoiceIds)
->get(['id', 'purpose', 'instance_id', 'user_id', 'due_at',
'status'])->all());
$instanceIds = self::ids(array_values($invoices), 'instance_id');
return [
'users' => self::keyed(User::whereIn('id', $userIds)
->get(['id', 'user_name', 'email'])->all()),
'invoices' => $invoices,
'instances' => self::keyed(CloudInstance::whereIn('id', $instanceIds)
->get(['id', 'user_id', 'status'])->all()),
];
}
private static function paymentRow($payment, array $context): array
{
$user = self::recordById($context['users'], $payment->user_id);
$invoice = self::recordById($context['invoices'], $payment->invoice_id);
if ($invoice !== null
&& !self::sameIdentifier($invoice->user_id, $payment->user_id)) {
$invoice = null;
}
$instance = $invoice === null || $invoice->instance_id === null ? null
: self::recordById($context['instances'], $invoice->instance_id);
if ($instance !== null
&& !self::sameIdentifier($instance->user_id, $payment->user_id)) {
$instance = null;
}
return [
'id' => (string) $payment->id,
'invoice_id' => (string) $payment->invoice_id,
'user_id' => (string) $payment->user_id,
'user_name' => AdminDataMasker::account($user?->user_name ?? ''),
'user_email' => AdminDataMasker::email($user?->email ?? ''),
'provider' => (string) $payment->provider,
'amount' => self::money($payment->amount),
'payment_status' => (string) $payment->status,
'invoice_financial_status' => $invoice === null ? null
: self::financialStatus($invoice, $instance, true),
'purpose' => $invoice === null ? null
: (string) ($invoice->purpose ?: 'renewal'),
'created_at' => max(0, (int) $payment->created_at),
'updated_at' => max(0, (int) $payment->updated_at),
];
}
private static function jobRow($job): array
{
$result = json_decode((string) $job->result_json, true);
$result = is_array($result) ? CloudOperationErrorCatalog::result($result, true,
(string) $job->kind, (string) $job->status, (string) $job->kind === 'create') : [];
$phase = trim((string) ($result['phase'] ?? ''));
// Billing rows only need the persisted job kind. Keep encrypted job
// payloads out of this broad read path; power-action detail belongs to
// the operations and instance views that explicitly select it.
$labels = CloudOperationCatalog::labels((string) $job->kind);
return [
'id' => (string) $job->id,
'kind' => (string) $job->kind,
'kind_label' => $labels['kind_label'],
'action_label' => $labels['action_label'],
'status' => (string) $job->status,
'attempts' => max(0, (int) $job->attempts),
'phase' => $phase === '' ? null
: CloudOperationErrorCatalog::adminSummary(null, null, $phase),
'error' => CloudOperationErrorCatalog::adminSummary(
(string) $job->kind, (string) $job->status, $job->error_code,
(string) $job->kind === 'create', null
),
'created_at' => max(0, (int) $job->created_at),
'updated_at' => max(0, (int) $job->updated_at),
];
}
private static function instanceRow($instance): array
{
return [
'id' => (string) $instance->id,
'name' => (string) $instance->name,
'status' => (string) $instance->status,
'service_status' => (string) $instance->service_status,
'network_status' => (string) $instance->network_status,
'expires_at' => $instance->expires_at === null
? null : (int) $instance->expires_at,
];
}
private static function specification($order, $instance): array
{
$spec = self::snapshot($order?->spec_json ?? $instance?->spec_json ?? null);
return [
'cpu_cores' => self::positiveOrNull($spec['cpu_cores'] ?? $spec['vcpu'] ?? null),
'memory_mib' => self::positiveOrNull($spec['memory_mib'] ?? null),
'disk_gib' => self::positiveOrNull($spec['disk_gib'] ?? null),
'bandwidth_mbps' => self::positiveOrNull($spec['bandwidth_mbps'] ?? null),
];
}
private static function financialStatus($invoice, $instance,
bool $renewalInstanceLoaded = false): string
{
$status = (string) $invoice->status;
if ((string) ($invoice->purpose ?: 'renewal') === 'renewal'
&& $renewalInstanceLoaded
&& ($instance === null || (string) $instance->status === 'deleted')
&& in_array($status, ['open', 'overdue'], true)) {
return 'void';
}
if ($status === 'open' && (int) $invoice->due_at <= time()) {
return 'overdue';
}
return $status;
}
private static function fulfillmentStatus($invoice, $order, $job,
string $financialStatus): ?string
{
$purpose = (string) ($invoice->purpose ?: 'renewal');
if ($purpose === 'provision') {
return $job?->status ?? $order?->status;
}
if ($purpose !== 'renewal') {
return null;
}
if ($invoice->renewal_id !== null) {
return 'succeeded';
}
return match ($financialStatus) {
'open', 'overdue' => 'awaiting_payment',
'refunded' => 'refunded',
'expired', 'void' => 'cancelled',
'paid' => 'uncertain',
default => null,
};
}
private static function linkedInstanceId($invoice, $order): ?string
{
$value = $order?->instance_id ?? $invoice->instance_id;
return self::normalizeIdentifier($value);
}
private static function invoiceSummary($query): array
{
$liveRenewalTarget = "(purpose <> 'renewal' OR EXISTS ("
. 'SELECT 1 FROM cloud_instances AS billing_instance '
. 'WHERE billing_instance.id = cloud_invoices.instance_id '
. 'AND billing_instance.user_id = cloud_invoices.user_id '
. "AND billing_instance.status <> 'deleted'))";
$row = $query->selectRaw(
"COUNT(*) AS total_records, COALESCE(SUM(amount), 0) AS total_amount, "
. "SUM(CASE WHEN status IN ('open','overdue') AND {$liveRenewalTarget} "
. "THEN 1 ELSE 0 END) AS outstanding_count, "
. "COALESCE(SUM(CASE WHEN status IN ('open','overdue') "
. "AND {$liveRenewalTarget} THEN amount ELSE 0 END), 0) "
. "AS outstanding_amount, "
. "SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, "
. "COALESCE(SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 0) AS paid_amount, "
. "SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_count, "
. "COALESCE(SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END), 0) AS refunded_amount"
)->first();
return [
'total_records' => (int) ($row?->total_records ?? 0),
'total_amount' => self::money($row?->total_amount ?? '0'),
'outstanding_count' => (int) ($row?->outstanding_count ?? 0),
'outstanding_amount' => self::money($row?->outstanding_amount ?? '0'),
'paid_count' => (int) ($row?->paid_count ?? 0),
'paid_amount' => self::money($row?->paid_amount ?? '0'),
'refunded_count' => (int) ($row?->refunded_count ?? 0),
'refunded_amount' => self::money($row?->refunded_amount ?? '0'),
];
}
private static function orderSummary($query): array
{
$state = 'COALESCE(cloud_jobs.status, cloud_orders.status)';
$select = [
'COUNT(*) AS total_records',
'COALESCE(SUM(cloud_orders.amount), 0) AS total_amount',
];
foreach (self::ORDER_STATUSES as $status) {
$select[] = "SUM(CASE WHEN {$state} = '{$status}' "
. "THEN 1 ELSE 0 END) AS count_{$status}";
}
$row = $query->leftJoin('cloud_jobs', function ($join) {
$join->on('cloud_jobs.order_id', '=', 'cloud_orders.id')
->on('cloud_jobs.user_id', '=', 'cloud_orders.user_id');
})->selectRaw(implode(', ', $select))->first();
$counts = [];
foreach (self::ORDER_STATUSES as $status) {
$count = (int) ($row?->{'count_' . $status} ?? 0);
if ($count > 0) {
$counts[$status] = $count;
}
}
return [
'total_records' => (int) ($row?->total_records ?? 0),
'total_amount' => self::money($row?->total_amount ?? '0'),
'status_counts' => $counts,
];
}
private static function paymentSummary($query): array
{
$select = [
'COUNT(*) AS total_records',
'COALESCE(SUM(cloud_invoice_payments.amount), 0) AS total_amount',
"COALESCE(SUM(CASE WHEN cloud_invoice_payments.status = 'paid_invoice' "
. 'THEN cloud_invoice_payments.amount ELSE 0 END), 0) '
. 'AS paid_invoice_amount',
"COALESCE(SUM(CASE WHEN cloud_invoice_payments.status = 'credited_balance' "
. 'THEN cloud_invoice_payments.amount ELSE 0 END), 0) '
. 'AS credited_balance_amount',
];
foreach (self::PAYMENT_STATUSES as $status) {
$select[] = "SUM(CASE WHEN cloud_invoice_payments.status = '{$status}' "
. "THEN 1 ELSE 0 END) AS count_{$status}";
}
$row = $query->selectRaw(implode(', ', $select))->first();
$counts = [];
foreach (self::PAYMENT_STATUSES as $status) {
$count = (int) ($row?->{'count_' . $status} ?? 0);
if ($count > 0) {
$counts[$status] = $count;
}
}
return [
'total_records' => (int) ($row?->total_records ?? 0),
'total_amount' => self::money($row?->total_amount ?? '0'),
'status_counts' => $counts,
'paid_invoice_amount' => self::money(
$row?->paid_invoice_amount ?? '0'
),
'credited_balance_amount' => self::money(
$row?->credited_balance_amount ?? '0'
),
];
}
private static function pagination(int $page, int $perPage, int $total): array
{
$totalPages = max(1, (int) ceil($total / $perPage));
$page = min($page, $totalPages);
return ['page' => $page, 'per_page' => $perPage, 'total' => $total,
'total_pages' => $totalPages, 'has_previous' => $page > 1,
'has_next' => $page < $totalPages];
}
private static function positiveInteger(array $input, string $name,
int $default): int
{
if (!array_key_exists($name, $input) || $input[$name] === '') {
return $default;
}
$value = $input[$name];
if (is_array($value) || preg_match('/^[1-9][0-9]{0,8}$/D',
(string) $value) !== 1) {
throw new CloudError($name . ' 参数无效');
}
return (int) $value;
}
private static function optionalIdentifier(array $input,
string $name): ?string
{
if (!array_key_exists($name, $input) || $input[$name] === '') {
return null;
}
if (is_array($input[$name])) {
throw new CloudError($name . ' 参数无效');
}
return self::identifier((string) $input[$name], $name . ' 参数');
}
private static function identifier(string $value, string $label): string
{
$normalized = self::normalizeIdentifier($value);
if ($normalized === null) {
throw new CloudError($label . '无效');
}
return $normalized;
}
/** Keep BIGINT UNSIGNED identifiers as decimal strings (safe for JSON/JS). */
private static function normalizeIdentifier($value): ?string
{
if (is_array($value) || is_object($value) || $value === null) {
return null;
}
$value = (string) $value;
if (preg_match('/^[1-9][0-9]{0,19}$/D', $value) !== 1) {
return null;
}
$maximum = '18446744073709551615';
if (strlen($value) === strlen($maximum)
&& strcmp($value, $maximum) > 0) {
return null;
}
return $value;
}
private static function optionalTimestamp(array $input, string $name): ?int
{
if (!array_key_exists($name, $input) || $input[$name] === '') {
return null;
}
$value = $input[$name];
if (is_array($value) || preg_match('/^[1-9][0-9]{0,10}$/D',
(string) $value) !== 1) {
throw new CloudError($name . ' 时间参数无效');
}
return (int) $value;
}
private static function enum(array $input, string $name,
array $allowed): string
{
if (!array_key_exists($name, $input) || $input[$name] === '') {
return '';
}
if (is_array($input[$name])
|| !in_array((string) $input[$name], $allowed, true)) {
throw new CloudError($name . ' 筛选值无效');
}
return (string) $input[$name];
}
private static function text(array $input, string $name, int $maximum): string
{
if (!array_key_exists($name, $input) || $input[$name] === '') {
return '';
}
if (is_array($input[$name])) {
throw new CloudError($name . ' 参数无效');
}
$value = trim((string) $input[$name]);
if (mb_strlen($value) > $maximum) {
throw new CloudError($name . ' 参数过长');
}
return $value;
}
private static function snapshot($json): array
{
if (!is_string($json) || $json === '') {
return [];
}
$value = json_decode($json, true);
return is_array($value) && !array_is_list($value) ? $value : [];
}
private static function ids(array $records, string $column): array
{
$ids = [];
foreach ($records as $record) {
$value = self::normalizeIdentifier($record->$column ?? null);
if ($value !== null) {
$ids[$value] = $value;
}
}
return array_values($ids);
}
private static function keyed(array $records, string $column = 'id'): array
{
$values = [];
foreach ($records as $record) {
$key = self::normalizeIdentifier($record->$column ?? null);
if ($key !== null) {
$values[$key] = $record;
}
}
return $values;
}
private static function recordById(array $records, $id)
{
$key = self::normalizeIdentifier($id);
return $key === null ? null : ($records[$key] ?? null);
}
private static function sameIdentifier($left, $right): bool
{
$left = self::normalizeIdentifier($left);
return $left !== null && $left === self::normalizeIdentifier($right);
}
private static function positiveOrNull($value): ?int
{
$value = filter_var($value, FILTER_VALIDATE_INT);
return $value === false || $value <= 0 ? null : (int) $value;
}
private static function money($value): string
{
$value = (string) $value;
if (preg_match('/^(0|[1-9][0-9]*)(?:\.([0-9]+))?$/D', $value,
$match) !== 1) {
return '0.00';
}
$whole = ltrim($match[1], '0');
if ($whole === '') {
$whole = '0';
}
return $whole . '.' . str_pad(substr($match[2] ?? '', 0, 2), 2, '0');
}
}