File manager

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

Back
<?php /** * Order Model * Maps to `orders` and `order_items` tables / require_once __DIR__ . '/../Core/Model.php'; class Order extends Model { protected string $table = 'orders'; public function getCustomerOrders(int $customerId, ?string $status = null): array { $where = "o.customer_id = :customer_id AND o.deleted_at IS NULL"; $params = [':customer_id' => $customerId]; if ($status && $status !== 'all') { $where .= " AND o.order_status = :status"; $params[':status'] = $status; } $sql = "SELECT o., s.store_name, s.store_slug, s.logo as store_logo, (SELECT COUNT() FROM `order_items` oi WHERE oi.order_id = o.id) as total_items, (SELECT oi.product_name FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_name, (SELECT oi.product_image FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_image FROM `orders` o JOIN `stores` s ON o.store_id = s.id WHERE {$where} ORDER BY o.created_at DESC"; return $this->fetchAll($sql, $params); } public function findOrderByNo(string $orderNo, int $customerId): ?array { $sql = "SELECT o., s.store_name, s.store_slug, s.logo as store_logo, s.phone as store_phone FROM `orders` o JOIN `stores` s ON o.store_id = s.id WHERE o.order_no = :order_no AND o.customer_id = :customer_id AND o.deleted_at IS NULL LIMIT 1"; return $this->fetchOne($sql, [':order_no' => $orderNo, ':customer_id' => $customerId]); } public function getOrderItems(int $orderId): array { $sql = "SELECT oi., p.slug as product_slug FROM `order_items` oi LEFT JOIN `products` p ON oi.product_id = p.id WHERE oi.order_id = :order_id"; return $this->fetchAll($sql, [':order_id' => $orderId]); } public function generateOrderNo(): string { return 'CG' . date('ymd') . strtoupper(bin2hex(random_bytes(3))); } /* * Create Multi-Vendor Orders transactionally / public function createVendorOrder(array $orderData, array $items): int { $this->db->beginTransaction(); try { $orderId = $this->insert($orderData); $sqlItem = "INSERT INTO `order_items` (`order_id`, `product_id`, `sku_id`, `product_name`, `variation_summary`, `product_image`, `unit_price`, `quantity`, `subtotal`, `created_at`) VALUES (:order_id, :product_id, :sku_id, :product_name, :variation_summary, :product_image, :unit_price, :quantity, :subtotal, NOW())"; $stmtItem = $this->db->prepare($sqlItem); foreach ($items as $item) { $stmtItem->execute([ ':order_id' => $orderId, ':product_id' => $item['product_id'], ':sku_id' => $item['sku_id'] ?? null, ':product_name' => $item['product_name'], ':variation_summary' => $item['variation_summary'] ?? null, ':product_image' => $item['main_image'] ?? null, ':unit_price' => $item['price'], ':quantity' => $item['quantity'], ':subtotal' => (float)$item['price'] (int)$item['quantity'] ]); // Deduct Product Stock $sqlStock = "UPDATE `products` SET `stock` = `stock` - :qty WHERE `id` = :id"; $stmtStock = $this->db->prepare($sqlStock); $stmtStock->execute([':qty' => $item['quantity'], ':id' => $item['product_id']]); } $this->db->commit(); return $orderId; } catch (Exception $e) { $this->db->rollBack(); throw $e; } } /** * Seller Orders Listing / public function getSellerOrders(int $storeId, array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildSellerOrderConditions($storeId, $filters); $sql = "SELECT o., (SELECT COUNT() FROM `order_items` oi WHERE oi.order_id = o.id) as total_items, (SELECT oi.product_name FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_name, (SELECT oi.product_image FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_image FROM `orders` o WHERE {$whereSql} ORDER BY o.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 countSellerOrders(int $storeId, array $filters = []): int { [$whereSql, $params] = $this->buildSellerOrderConditions($storeId, $filters); $sql = "SELECT COUNT() FROM `orders` o WHERE {$whereSql}"; $stmt = $this->db->prepare($sql); foreach ($params as $k => $v) { $stmt->bindValue($k, $v); } $stmt->execute(); return (int)$stmt->fetchColumn(); } private function buildSellerOrderConditions(int $storeId, array $filters): array { $conditions = ["o.store_id = :store_id", "o.deleted_at IS NULL"]; $params = [':store_id' => $storeId]; if (!empty($filters['q'])) { $conditions[] = "(o.order_no LIKE :kw OR o.customer_name LIKE :kw OR o.customer_phone LIKE :kw)"; $params[':kw'] = '%' . trim($filters['q']) . '%'; } if (!empty($filters['status']) && $filters['status'] !== 'all') { $conditions[] = "o.order_status = :status"; $params[':status'] = $filters['status']; } if (!empty($filters['date_from'])) { $conditions[] = "o.created_at >= :date_from"; $params[':date_from'] = $filters['date_from'] . ' 00:00:00'; } if (!empty($filters['date_to'])) { $conditions[] = "o.created_at <= :date_to"; $params[':date_to'] = $filters['date_to'] . ' 23:59:59'; } return [implode(" AND ", $conditions), $params]; } public function findSellerOrderByNo(string $orderNo, int $storeId): ?array { $sql = "SELECT o.* FROM `orders` o WHERE o.order_no = :order_no AND o.store_id = :store_id AND o.deleted_at IS NULL LIMIT 1"; return $this->fetchOne($sql, [':order_no' => $orderNo, ':store_id' => $storeId]); } public function updateSellerOrderStatus(int $orderId, int $storeId, string $newStatus, ?string $trackingNo = null, ?string $shippingMethod = null): bool { $updateData = [ 'order_status' => $newStatus, 'updated_at' => date('Y-m-d H:i:s') ]; if ($trackingNo !== null) { $updateData['tracking_no'] = $trackingNo; } if ($shippingMethod !== null) { $updateData['shipping_method'] = $shippingMethod; } $sql = "UPDATE `orders` SET " . implode(', ', array_map(fn($k) => "`$k` = :$k", array_keys($updateData))) . " WHERE `id` = :order_id AND `store_id` = :store_id"; $params = array_merge($updateData, [ 'order_id' => $orderId, 'store_id' => $storeId ]); $stmt = $this->query($sql, $params); return $stmt->rowCount() > 0; } public function getSellerFinanceSummary(int $storeId): array { $sql = "SELECT COUNT() as total_orders, SUM(CASE WHEN order_status = 'completed' THEN subtotal ELSE 0 END) as gross_sales, SUM(CASE WHEN order_status = 'completed' THEN shipping_fee ELSE 0 END) as total_shipping_revenue, SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) as total_collected, SUM(CASE WHEN order_status = 'completed' THEN commission_amount ELSE 0 END) as total_commission, SUM(CASE WHEN order_status = 'completed' THEN net_revenue ELSE 0 END) as net_revenue, SUM(CASE WHEN order_status IN ('payment_confirmed', 'preparing', 'shipping') THEN net_revenue ELSE 0 END) as pending_settlement FROM `orders` WHERE store_id = :store_id AND deleted_at IS NULL"; $summary = $this->fetchOne($sql, [':store_id' => $storeId]); return $summary ?: [ 'total_orders' => 0, 'gross_sales' => 0.00, 'total_shipping_revenue' => 0.00, 'total_collected' => 0.00, 'total_commission' => 0.00, 'net_revenue' => 0.00, 'pending_settlement' => 0.00 ]; } // ========================================================= // Admin Order Monitoring Methods // ========================================================= public function getAdminOrders(array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildAdminOrderConditions($filters); $sql = "SELECT o., s.store_name, s.store_slug, (SELECT COUNT() FROM `order_items` oi WHERE oi.order_id = o.id) as total_items FROM `orders` o JOIN `stores` s ON o.store_id = s.id WHERE {$whereSql} ORDER BY o.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 countAdminOrders(array $filters = []): int { [$whereSql, $params] = $this->buildAdminOrderConditions($filters); $sql = "SELECT COUNT() FROM `orders` o JOIN `stores` s ON o.store_id = s.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 buildAdminOrderConditions(array $filters): array { $conditions = ['o.deleted_at IS NULL']; $params = []; if (!empty($filters['q'])) { $conditions[] = '(o.order_no LIKE :q OR o.customer_name LIKE :q OR o.customer_phone LIKE :q)'; $params[':q'] = '%' . trim($filters['q']) . '%'; } if (!empty($filters['status']) && $filters['status'] !== 'all') { $conditions[] = 'o.order_status = :status'; $params[':status'] = $filters['status']; } if (!empty($filters['payment_status']) && $filters['payment_status'] !== 'all') { $conditions[] = 'o.payment_status = :pay_status'; $params[':pay_status'] = $filters['payment_status']; } if (!empty($filters['store_id'])) { $conditions[] = 'o.store_id = :store_id'; $params[':store_id'] = (int)$filters['store_id']; } if (!empty($filters['date_from'])) { $conditions[] = 'o.created_at >= :date_from'; $params[':date_from'] = $filters['date_from'] . ' 00:00:00'; } if (!empty($filters['date_to'])) { $conditions[] = 'o.created_at <= :date_to'; $params[':date_to'] = $filters['date_to'] . ' 23:59:59'; } return [implode(' AND ', $conditions), $params]; } public function findAdminOrderByNo(string $orderNo): ?array { $sql = "SELECT o., s.store_name, s.store_slug, s.phone as store_phone FROM `orders` o JOIN `stores` s ON o.store_id = s.id WHERE o.order_no = :order_no AND o.deleted_at IS NULL LIMIT 1"; return $this->fetchOne($sql, [':order_no' => $orderNo]); } public function getPlatformOrderStats(): array { $sql = "SELECT COUNT() as total_orders, SUM(CASE WHEN order_status = 'pending_payment' THEN 1 ELSE 0 END) as pending_payment, SUM(CASE WHEN order_status = 'preparing' THEN 1 ELSE 0 END) as preparing, SUM(CASE WHEN order_status = 'shipping' THEN 1 ELSE 0 END) as shipping, SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as today_orders, SUM(CASE WHEN DATE(created_at) = CURDATE() AND order_status = 'completed' THEN total_amount ELSE 0 END) as today_gmv, SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) as total_gmv, SUM(CASE WHEN order_status = 'completed' THEN commission_amount ELSE 0 END) as total_commission FROM `orders` WHERE deleted_at IS NULL"; return $this->fetchOne($sql) ?: []; } }

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