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