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
PHTML

<?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');
}
}