File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090028/Shop/app/Models/Payment.php

Back
<?php /** * Payment Model * Maps to `payments` table — one record per order / require_once __DIR__ . '/../Core/Model.php'; class Payment extends Model { protected string $table = 'payments'; // ── Retrieval ────────────────────────────────────────────────── public function findByOrderId(int $orderId): ?array { $sql = "SELECT p., pa.account_name, pa.account_no as account_number, pa.bank_name, pa.promptpay_id, pa.type as account_method FROM `payments` p LEFT JOIN `payment_accounts` pa ON pa.id = p.payment_account_id WHERE p.order_id = :order_id LIMIT 1"; return $this->fetchOne($sql, [':order_id' => $orderId]); } public function findByOrderNo(string $orderNo): ?array { $sql = "SELECT p., pa.account_name, pa.account_no as account_number, pa.bank_name, pa.promptpay_id, pa.type as account_method FROM `payments` p LEFT JOIN `payment_accounts` pa ON pa.id = p.payment_account_id WHERE p.order_no = :order_no LIMIT 1"; return $this->fetchOne($sql, [':order_no' => $orderNo]); } public function findByOrderNoForCustomer(string $orderNo, int $customerId): ?array { $sql = "SELECT p., pa.account_name, pa.account_no as account_number, pa.bank_name, pa.promptpay_id, pa.type as account_method FROM `payments` p LEFT JOIN `payment_accounts` pa ON pa.id = p.payment_account_id WHERE p.order_no = :order_no AND p.customer_id = :customer_id LIMIT 1"; return $this->fetchOne($sql, [':order_no' => $orderNo, ':customer_id' => $customerId]); } // ── Status Management ────────────────────────────────────────── public function updateStatus(int $paymentId, string $newStatus, array $extra = []): bool { $data = array_merge(['payment_status' => $newStatus, 'updated_at' => date('Y-m-d H:i:s')], $extra); return $this->update($paymentId, $data); } // ── Admin Listing ────────────────────────────────────────────── public function getAdminPayments(array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildAdminFilters($filters); $sql = "SELECT p., CONCAT(u.first_name, ' ', u.last_name) as customer_name, u.email as customer_email, o.order_status, (SELECT COUNT() FROM `payment_attempts` pa WHERE pa.payment_id = p.id) as attempt_count, (SELECT COUNT() FROM `payment_attempts` pa WHERE pa.payment_id = p.id AND pa.status = 'pending') as pending_attempts FROM `payments` p JOIN `users` u ON u.id = p.customer_id JOIN `orders` o ON o.id = p.order_id WHERE {$whereSql} ORDER BY p.created_at DESC LIMIT :limit OFFSET :offset"; $stmt = $this->db->prepare($sql); foreach ($params as $k => $v) { $stmt->bindValue($k, $v); } $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function countAdminPayments(array $filters = []): int { [$whereSql, $params] = $this->buildAdminFilters($filters); $sql = "SELECT COUNT() FROM `payments` p JOIN `users` u ON u.id = p.customer_id JOIN `orders` o ON o.id = p.order_id WHERE {$whereSql}"; $stmt = $this->db->prepare($sql); foreach ($params as $k => $v) { $stmt->bindValue($k, $v); } $stmt->execute(); return (int)$stmt->fetchColumn(); } private function buildAdminFilters(array $filters): array { $conditions = ['1=1']; $params = []; if (!empty($filters['q'])) { $conditions[] = "(p.order_no LIKE :q OR u.username LIKE :q OR u.first_name LIKE :q OR u.email LIKE :q)"; $params[':q'] = '%' . trim($filters['q']) . '%'; } if (!empty($filters['status']) && $filters['status'] !== 'all') { $conditions[] = "p.payment_status = :status"; $params[':status'] = $filters['status']; } if (!empty($filters['method']) && $filters['method'] !== 'all') { $conditions[] = "p.payment_method = :method"; $params[':method'] = $filters['method']; } if (!empty($filters['date_from'])) { $conditions[] = "p.created_at >= :date_from"; $params[':date_from'] = $filters['date_from'] . ' 00:00:00'; } if (!empty($filters['date_to'])) { $conditions[] = "p.created_at <= :date_to"; $params[':date_to'] = $filters['date_to'] . ' 23:59:59'; } return [implode(' AND ', $conditions), $params]; } // ── Stats ────────────────────────────────────────────────────── public function getPaymentStats(): array { $sql = "SELECT COUNT() as total, SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END) as pending, SUM(CASE WHEN payment_status = 'awaiting_verification' THEN 1 ELSE 0 END) as awaiting, SUM(CASE WHEN payment_status = 'confirmed' THEN 1 ELSE 0 END) as confirmed, SUM(CASE WHEN payment_status = 'failed' THEN 1 ELSE 0 END) as failed, SUM(CASE WHEN payment_status = 'expired' THEN 1 ELSE 0 END) as expired, SUM(CASE WHEN payment_status IN ('refund_pending','refunded') THEN 1 ELSE 0 END) as refunds, SUM(CASE WHEN payment_status = 'confirmed' THEN amount ELSE 0 END) as total_confirmed_amount, SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as today_total, SUM(CASE WHEN DATE(created_at) = CURDATE() AND payment_status = 'confirmed' THEN amount ELSE 0 END) as today_amount FROM `payments`"; return $this->fetchOne($sql) ?: []; } // ── Expiry ────────────────────────────────────────────────────── public function getExpiredPayments(): array { $sql = "SELECT FROM `payments` WHERE payment_status IN ('pending','awaiting_verification') AND expires_at < NOW()"; return $this->fetchAll($sql); } }

| ver. 1.4 | Github | . | PHP 7.4.33 | Generation time: 0.24 | proxy | phpinfo | Settings