File manager

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

Back
<?php /** * Store Model * Maps to `stores` table / require_once __DIR__ . '/../Core/Model.php'; class Store extends Model { protected string $table = 'stores'; public function getActiveStores(int $limit = 8): array { $sql = "SELECT s., u.username as owner_username, (SELECT COUNT() FROM `products` p WHERE p.store_id = s.id AND p.status = 'active' AND p.deleted_at IS NULL) as total_products FROM `stores` s JOIN `users` u ON s.user_id = u.id WHERE s.status = 'active' AND s.deleted_at IS NULL AND u.status = 'active' ORDER BY s.verified_at DESC, s.created_at DESC LIMIT :limit"; $stmt = $this->db->prepare($sql); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function findBySlug(string $slug): ?array { $sql = "SELECT s., u.username as owner_username, u.first_name, u.last_name, (SELECT COUNT() FROM `products` p WHERE p.store_id = s.id AND p.status = 'active' AND p.deleted_at IS NULL) as total_products FROM `stores` s JOIN `users` u ON s.user_id = u.id WHERE s.store_slug = :slug AND s.status = 'active' AND s.deleted_at IS NULL LIMIT 1"; return $this->fetchOne($sql, [':slug' => $slug]); } public function findByUserId(int $userId): ?array { $sql = "SELECT FROM `stores` WHERE `user_id` = :user_id AND `deleted_at` IS NULL LIMIT 1"; return $this->fetchOne($sql, [':user_id' => $userId]); } public function slugExists(string $slug, ?int $excludeStoreId = null): bool { $sql = "SELECT id FROM `stores` WHERE store_slug = :slug AND deleted_at IS NULL"; $params = [':slug' => $slug]; if ($excludeStoreId) { $sql .= " AND id != :exclude_id"; $params[':exclude_id'] = $excludeStoreId; } $sql .= " LIMIT 1"; return (bool)$this->fetchOne($sql, $params); } public function getStoreStats(int $storeId): array { $sql = "SELECT COUNT() as total_products, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_products, SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_products, SUM(CASE WHEN stock <= 0 THEN 1 ELSE 0 END) as out_of_stock_products, SUM(CASE WHEN stock > 0 AND stock <= 5 THEN 1 ELSE 0 END) as low_stock_products FROM `products` WHERE store_id = :store_id AND deleted_at IS NULL"; $productStats = $this->fetchOne($sql, [':store_id' => $storeId]); $sqlOrders = "SELECT COUNT() as total_orders, SUM(CASE WHEN order_status = 'pending_payment' THEN 1 ELSE 0 END) as pending_orders, SUM(CASE WHEN order_status = 'preparing' THEN 1 ELSE 0 END) as preparing_orders, SUM(CASE WHEN order_status = 'shipping' THEN 1 ELSE 0 END) as shipping_orders, SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) as total_sales FROM `orders` WHERE store_id = :store_id AND deleted_at IS NULL"; $orderStats = $this->fetchOne($sqlOrders, [':store_id' => $storeId]); return array_merge($productStats ?: [], $orderStats ?: []); } // ========================================================= // Admin Management Methods // ========================================================= public function getAdminStores(array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildAdminStoreConditions($filters); $sql = "SELECT s., u.username as owner_username, u.email as owner_email, u.first_name, u.last_name, (SELECT COUNT() FROM `products` p WHERE p.store_id = s.id AND p.deleted_at IS NULL) as total_products, (SELECT COUNT() FROM `orders` o WHERE o.store_id = s.id AND o.deleted_at IS NULL) as total_orders FROM `stores` s JOIN `users` u ON s.user_id = u.id WHERE {$whereSql} ORDER BY s.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 countAdminStores(array $filters = []): int { [$whereSql, $params] = $this->buildAdminStoreConditions($filters); $sql = "SELECT COUNT() FROM `stores` s JOIN `users` u ON s.user_id = u.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 buildAdminStoreConditions(array $filters): array { $conditions = ['s.deleted_at IS NULL']; $params = []; if (!empty($filters['q'])) { $conditions[] = '(s.store_name LIKE :q OR s.store_slug LIKE :q OR u.username LIKE :q OR u.email LIKE :q)'; $params[':q'] = '%' . trim($filters['q']) . '%'; } if (!empty($filters['status']) && $filters['status'] !== 'all') { $conditions[] = 's.status = :status'; $params[':status'] = $filters['status']; } if (!empty($filters['store_type']) && $filters['store_type'] !== 'all') { if ($filters['store_type'] === 'official') { $conditions[] = 's.verified_at IS NOT NULL'; } elseif ($filters['store_type'] === 'standard') { $conditions[] = 's.verified_at IS NULL'; } } return [implode(' AND ', $conditions), $params]; } public function findAdminStore(int $storeId): ?array { $sql = "SELECT s., u.username as owner_username, u.email as owner_email, u.first_name, u.last_name, u.phone as owner_phone FROM `stores` s JOIN `users` u ON s.user_id = u.id WHERE s.id = :id AND s.deleted_at IS NULL LIMIT 1"; return $this->fetchOne($sql, [':id' => $storeId]); } public function suspendStore(int $storeId): bool { $stmt = $this->query( "UPDATE `stores` SET `status` = 'suspended', `updated_at` = NOW() WHERE `id` = :id AND `deleted_at` IS NULL", [':id' => $storeId] ); return $stmt->rowCount() > 0; } public function reactivateStore(int $storeId): bool { $stmt = $this->query( "UPDATE `stores` SET `status` = 'active', `updated_at` = NOW() WHERE `id` = :id", [':id' => $storeId] ); return $stmt->rowCount() > 0; } public function setOfficialStatus(int $storeId, bool $official): bool { $sql = $official ? "UPDATE `stores` SET `verified_at` = NOW(), `updated_at` = NOW() WHERE `id` = :id" : "UPDATE `stores` SET `verified_at` = NULL, `updated_at` = NOW() WHERE `id` = :id"; $stmt = $this->query($sql, [':id' => $storeId]); return $stmt->rowCount() > 0; } public function getPlatformStoreStats(): array { $sql = "SELECT COUNT() as total_stores, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_stores, SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) as suspended_stores, SUM(CASE WHEN verified_at IS NOT NULL THEN 1 ELSE 0 END) as official_stores, SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as new_today FROM `stores` WHERE deleted_at IS NULL"; return $this->fetchOne($sql) ?: []; } }

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