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