File manager

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

Back
<?php /** * Review Model * Maps to `reviews` table * Handles review eligibility verification, data retrieval, rating calculation, and moderation / require_once __DIR__ . '/../Core/Model.php'; require_once __DIR__ . '/ReviewImage.php'; class Review extends Model { protected string $table = 'reviews'; private ReviewImage $reviewImageModel; public function __construct() { parent::__construct(); $this->reviewImageModel = new ReviewImage(); } /* * Check if a user is eligible to review an order item * Rule: Order must belong to user, order_status = 'completed', and item not yet reviewed. / public function checkEligibility(int $userId, int $orderItemId): array { $sql = "SELECT oi., o.order_no, o.customer_id, o.store_id, o.order_status, o.created_at as order_date, p.name as product_name, p.slug as product_slug, p.main_image as product_main_image, s.store_name, s.store_slug FROM `order_items` oi JOIN `orders` o ON oi.order_id = o.id JOIN `products` p ON oi.product_id = p.id JOIN `stores` s ON o.store_id = s.id WHERE oi.id = :order_item_id AND o.deleted_at IS NULL LIMIT 1"; $item = $this->fetchOne($sql, [':order_item_id' => $orderItemId]); if (!$item) { return [ 'eligible' => false, 'message' => 'ไม่พบรายการสินค้าที่ระบุ', 'item' => null ]; } if ((int)$item['customer_id'] !== $userId) { return [ 'eligible' => false, 'message' => 'คุณไม่มีสิทธิ์รีวิวรายการคำสั่งซื้อของผู้อื่น', 'item' => null ]; } if ($item['order_status'] !== 'completed') { return [ 'eligible' => false, 'message' => 'สามารถรีวิวได้เฉพาะสินค้าในคำสั่งซื้อที่จัดส่งสำเร็จแล้วเท่านั้น', 'item' => $item ]; } // Check if already reviewed $existing = $this->fetchOne("SELECT * FROM `reviews` WHERE `order_item_id` = :order_item_id LIMIT 1", [ ':order_item_id' => $orderItemId ]); if ($existing) { return [ 'eligible' => false, 'already_reviewed' => true, 'message' => 'คุณได้ทำการรีวิวสินค้านี้ไปแล้ว', 'item' => $item, 'existing_review' => $existing ]; } return [ 'eligible' => true, 'message' => 'สามารถรีวิวสินค้านี้ได้', 'item' => $item ]; } /** * Find review by ID with images and item info / public function findDetailById(int $id): ?array { $sql = "SELECT r., u.first_name, u.last_name, u.username, u.profile_image as user_avatar, p.name as product_name, p.slug as product_slug, p.main_image as product_main_image, s.store_name, s.store_slug, oi.variation_summary, oi.unit_price, oi.quantity, o.order_no FROM `reviews` r JOIN `users` u ON r.user_id = u.id JOIN `products` p ON r.product_id = p.id JOIN `stores` s ON r.store_id = s.id JOIN `order_items` oi ON r.order_item_id = oi.id JOIN `orders` o ON r.order_id = o.id WHERE r.id = :id LIMIT 1"; $review = $this->fetchOne($sql, [':id' => $id]); if (!$review) { return null; } $review['images'] = $this->reviewImageModel->getImagesByReview((int)$review['id']); return $review; } /** * Create review with transactional safety and recalculate ratings / public function createReview(array $data, array $imagePaths = []): int { $this->db->beginTransaction(); try { $reviewId = $this->insert([ 'order_id' => $data['order_id'], 'order_item_id' => $data['order_item_id'], 'product_id' => $data['product_id'], 'store_id' => $data['store_id'], 'user_id' => $data['user_id'], 'rating' => $data['rating'], 'review_text' => $data['review_text'] ?? null, 'status' => 'active', 'is_anonymous' => !empty($data['is_anonymous']) ? 1 : 0, 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s') ]); foreach ($imagePaths as $idx => $path) { $this->reviewImageModel->addImage($reviewId, $path, $idx); } // Commit review creation $this->db->commit(); // Recalculate rating caches $this->recalculateProductRating((int)$data['product_id']); $this->recalculateStoreRating((int)$data['store_id']); return $reviewId; } catch (Exception $e) { $this->db->rollBack(); throw $e; } } /* * Update existing review / public function updateReview(int $reviewId, int $userId, int $rating, ?string $text, bool $isAnonymous, array $newImagePaths = [], array $deleteImageIds = []): bool { $review = $this->findById($reviewId); if (!$review \|\| (int)$review['user_id'] !== $userId) { return false; } $this->db->beginTransaction(); try { $this->update($reviewId, [ 'rating' => $rating, 'review_text' => $text, 'is_anonymous' => $isAnonymous ? 1 : 0, 'updated_at' => date('Y-m-d H:i:s') ]); // Delete requested images foreach ($deleteImageIds as $imgId) { $this->reviewImageModel->deleteImageById((int)$imgId, $reviewId); } // Add new images $existingImages = $this->reviewImageModel->getImagesByReview($reviewId); $startOrder = count($existingImages); foreach ($newImagePaths as $idx => $path) { $this->reviewImageModel->addImage($reviewId, $path, $startOrder + $idx); } $this->db->commit(); // Recalculate ratings $this->recalculateProductRating((int)$review['product_id']); $this->recalculateStoreRating((int)$review['store_id']); return true; } catch (Exception $e) { $this->db->rollBack(); throw $e; } } /* * Delete review (soft delete by marking status = 'removed' or hard delete) / public function deleteReview(int $reviewId, int $userId): bool { $review = $this->findById($reviewId); if (!$review \|\| (int)$review['user_id'] !== $userId) { return false; } $this->update($reviewId, [ 'status' => 'removed', 'updated_at' => date('Y-m-d H:i:s') ]); $this->recalculateProductRating((int)$review['product_id']); $this->recalculateStoreRating((int)$review['store_id']); return true; } /* * Update review status (Moderation: active, hidden, removed) / public function updateReviewStatus(int $reviewId, string $status, ?string $adminNotes = null): bool { $review = $this->findById($reviewId); if (!$review) { return false; } $data = [ 'status' => $status, 'updated_at' => date('Y-m-d H:i:s') ]; if ($adminNotes !== null) { $data['admin_notes'] = $adminNotes; } $this->update($reviewId, $data); // Recalculate rating because hidden/removed reviews don't count toward active rating $this->recalculateProductRating((int)$review['product_id']); $this->recalculateStoreRating((int)$review['store_id']); return true; } /* * Get active reviews for a product with pagination and star rating filter / public function getProductReviews(int $productId, int $limit = 10, int $offset = 0, ?int $rating = null): array { $where = "r.product_id = :product_id AND r.status = 'active'"; $params = [':product_id' => $productId]; if ($rating !== null && $rating >= 1 && $rating <= 5) { $where .= " AND r.rating = :rating"; $params[':rating'] = $rating; } $sql = "SELECT r., u.first_name, u.last_name, u.username, u.profile_image as user_avatar, oi.variation_summary FROM `reviews` r JOIN `users` u ON r.user_id = u.id JOIN `order_items` oi ON r.order_item_id = oi.id WHERE {$where} ORDER BY r.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(); $reviews = $stmt->fetchAll(); // Attach images foreach ($reviews as &$rev) { $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']); $rev['display_name'] = $this->formatReviewerName($rev); } return $reviews; } public function countProductReviews(int $productId, ?int $rating = null): int { $where = "product_id = :product_id AND status = 'active'"; $params = [':product_id' => $productId]; if ($rating !== null && $rating >= 1 && $rating <= 5) { $where .= " AND rating = :rating"; $params[':rating'] = $rating; } $sql = "SELECT COUNT() FROM `reviews` WHERE {$where}"; $stmt = $this->db->prepare($sql); $stmt->execute($params); return (int)$stmt->fetchColumn(); } /* * Get Product Rating Breakdown (Average, total count, count per star 1-5) / public function getProductRatingBreakdown(int $productId): array { $sql = "SELECT COUNT() as total_reviews, COALESCE(AVG(rating), 0) as avg_rating, SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as star_5, SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as star_4, SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as star_3, SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as star_2, SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as star_1, SUM(CASE WHEN (SELECT COUNT() FROM `review_images` ri WHERE ri.review_id = r.id) > 0 THEN 1 ELSE 0 END) as with_images_count FROM `reviews` r WHERE r.product_id = :product_id AND r.status = 'active'"; $res = $this->fetchOne($sql, [':product_id' => $productId]); if (!$res \|\| (int)$res['total_reviews'] === 0) { return [ 'total_reviews' => 0, 'avg_rating' => 0.00, 'star_5' => 0, 'star_4' => 0, 'star_3' => 0, 'star_2' => 0, 'star_1' => 0, 'star_percentages' => [5 => 0, 4 => 0, 3 => 0, 2 => 0, 1 => 0], 'with_images_count' => 0 ]; } $total = (int)$res['total_reviews']; $percentages = []; for ($s = 1; $s <= 5; $s++) { $percentages[$s] = $total > 0 ? round(((int)$res['star_' . $s] / $total) 100) : 0; } return [ 'total_reviews' => $total, 'avg_rating' => round((float)$res['avg_rating'], 1), 'star_5' => (int)$res['star_5'], 'star_4' => (int)$res['star_4'], 'star_3' => (int)$res['star_3'], 'star_2' => (int)$res['star_2'], 'star_1' => (int)$res['star_1'], 'star_percentages' => $percentages, 'with_images_count' => (int)$res['with_images_count'] ]; } /** * Get Store Reviews for Seller Center / public function getStoreReviews(int $storeId, array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildStoreConditions($storeId, $filters); $sql = "SELECT r., u.first_name, u.last_name, u.username, u.profile_image as user_avatar, p.name as product_name, p.slug as product_slug, p.main_image as product_main_image, oi.variation_summary, o.order_no FROM `reviews` r JOIN `users` u ON r.user_id = u.id JOIN `products` p ON r.product_id = p.id JOIN `order_items` oi ON r.order_item_id = oi.id JOIN `orders` o ON r.order_id = o.id WHERE {$whereSql} ORDER BY r.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(); $reviews = $stmt->fetchAll(); foreach ($reviews as &$rev) { $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']); $rev['display_name'] = $this->formatReviewerName($rev); } return $reviews; } public function countStoreReviews(int $storeId, array $filters = []): int { [$whereSql, $params] = $this->buildStoreConditions($storeId, $filters); $sql = "SELECT COUNT() FROM `reviews` r JOIN `products` p ON r.product_id = p.id JOIN `users` u ON r.user_id = u.id WHERE {$whereSql}"; $stmt = $this->db->prepare($sql); $stmt->execute($params); return (int)$stmt->fetchColumn(); } private function buildStoreConditions(int $storeId, array $filters): array { $conditions = ["r.store_id = :store_id", "r.status != 'removed'"]; $params = [':store_id' => $storeId]; if (!empty($filters['rating']) && (int)$filters['rating'] > 0) { $conditions[] = "r.rating = :rating"; $params[':rating'] = (int)$filters['rating']; } if (!empty($filters['product_id'])) { $conditions[] = "r.product_id = :product_id"; $params[':product_id'] = (int)$filters['product_id']; } if (!empty($filters['q'])) { $conditions[] = "(p.name LIKE :kw OR r.review_text LIKE :kw)"; $params[':kw'] = '%' . trim($filters['q']) . '%'; } return [implode(' AND ', $conditions), $params]; } public function getStoreRatingSummary(int $storeId): array { $sql = "SELECT COUNT() as total_reviews, COALESCE(AVG(rating), 0) as avg_rating, SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as star_5, SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as star_4, SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as star_3, SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as star_2, SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as star_1 FROM `reviews` WHERE store_id = :store_id AND status = 'active'"; $res = $this->fetchOne($sql, [':store_id' => $storeId]); return [ 'total_reviews' => (int)($res['total_reviews'] ?? 0), 'avg_rating' => round((float)($res['avg_rating'] ?? 0), 1), 'star_5' => (int)($res['star_5'] ?? 0), 'star_4' => (int)($res['star_4'] ?? 0), 'star_3' => (int)($res['star_3'] ?? 0), 'star_2' => (int)($res['star_2'] ?? 0), 'star_1' => (int)($res['star_1'] ?? 0) ]; } /** * Get Customer's Submitted Reviews / public function getUserReviews(int $userId): array { $sql = "SELECT r., p.name as product_name, p.slug as product_slug, p.main_image as product_main_image, s.store_name, s.store_slug, oi.variation_summary, o.order_no FROM `reviews` r JOIN `products` p ON r.product_id = p.id JOIN `stores` s ON r.store_id = s.id JOIN `order_items` oi ON r.order_item_id = oi.id JOIN `orders` o ON r.order_id = o.id WHERE r.user_id = :user_id AND r.status != 'removed' ORDER BY r.created_at DESC"; $reviews = $this->fetchAll($sql, [':user_id' => $userId]); foreach ($reviews as &$rev) { $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']); } return $reviews; } /** * Get Customer's Completed Order Items Pending Review / public function getPendingReviewItemsForUser(int $userId): array { $sql = "SELECT oi.id as order_item_id, oi.product_id, oi.product_name, oi.variation_summary, oi.product_image, oi.unit_price, oi.quantity, o.id as order_id, o.order_no, o.created_at as order_date, p.slug as product_slug, s.store_name, s.store_slug FROM `order_items` oi JOIN `orders` o ON oi.order_id = o.id JOIN `products` p ON oi.product_id = p.id JOIN `stores` s ON o.store_id = s.id LEFT JOIN `reviews` r ON r.order_item_id = oi.id WHERE o.customer_id = :user_id AND o.order_status = 'completed' AND o.deleted_at IS NULL AND r.id IS NULL ORDER BY o.created_at DESC"; return $this->fetchAll($sql, [':user_id' => $userId]); } /* * Admin Review Listing with Moderation Filters / public function getAdminReviews(array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildAdminConditions($filters); $sql = "SELECT r., u.first_name, u.last_name, u.username, u.email as user_email, p.name as product_name, p.slug as product_slug, p.main_image as product_main_image, s.store_name, s.store_slug, o.order_no FROM `reviews` r JOIN `users` u ON r.user_id = u.id JOIN `products` p ON r.product_id = p.id JOIN `stores` s ON r.store_id = s.id JOIN `orders` o ON r.order_id = o.id WHERE {$whereSql} ORDER BY r.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(); $reviews = $stmt->fetchAll(); foreach ($reviews as &$rev) { $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']); $rev['display_name'] = $this->formatReviewerName($rev, false); } return $reviews; } public function countAdminReviews(array $filters = []): int { [$whereSql, $params] = $this->buildAdminConditions($filters); $sql = "SELECT COUNT() FROM `reviews` r JOIN `users` u ON r.user_id = u.id JOIN `products` p ON r.product_id = p.id JOIN `stores` s ON r.store_id = s.id WHERE {$whereSql}"; $stmt = $this->db->prepare($sql); $stmt->execute($params); return (int)$stmt->fetchColumn(); } public function getAdminReviewStats(): array { $sql = "SELECT COUNT() as total_reviews, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_count, SUM(CASE WHEN status = 'hidden' THEN 1 ELSE 0 END) as hidden_count, SUM(CASE WHEN status = 'removed' THEN 1 ELSE 0 END) as removed_count, COALESCE(AVG(CASE WHEN status = 'active' THEN rating ELSE NULL END), 0) as platform_avg_rating FROM `reviews`"; $res = $this->fetchOne($sql); return [ 'total_reviews' => (int)($res['total_reviews'] ?? 0), 'active_count' => (int)($res['active_count'] ?? 0), 'hidden_count' => (int)($res['hidden_count'] ?? 0), 'removed_count' => (int)($res['removed_count'] ?? 0), 'platform_avg_rating' => round((float)($res['platform_avg_rating'] ?? 0), 2) ]; } private function buildAdminConditions(array $filters): array { $conditions = ['1=1']; $params = []; if (!empty($filters['status']) && $filters['status'] !== 'all') { $conditions[] = 'r.status = :status'; $params[':status'] = $filters['status']; } if (!empty($filters['rating']) && (int)$filters['rating'] > 0) { $conditions[] = 'r.rating = :rating'; $params[':rating'] = (int)$filters['rating']; } if (!empty($filters['q'])) { $conditions[] = '(p.name LIKE :kw OR s.store_name LIKE :kw OR u.username LIKE :kw OR u.first_name LIKE :kw OR r.review_text LIKE :kw OR o.order_no LIKE :kw)'; $params[':kw'] = '%' . trim($filters['q']) . '%'; } if (!empty($filters['date_from'])) { $conditions[] = 'r.created_at >= :date_from'; $params[':date_from'] = $filters['date_from'] . ' 00:00:00'; } if (!empty($filters['date_to'])) { $conditions[] = 'r.created_at <= :date_to'; $params[':date_to'] = $filters['date_to'] . ' 23:59:59'; } return [implode(' AND ', $conditions), $params]; } /** * Recalculate cached rating values in `products` table / public function recalculateProductRating(int $productId): array { $sql = "SELECT COUNT() as cnt, COALESCE(AVG(rating), 0) as avg_score FROM `reviews` WHERE product_id = :product_id AND status = 'active'"; $stat = $this->fetchOne($sql, [':product_id' => $productId]); $cnt = (int)($stat['cnt'] ?? 0); $avg = round((float)($stat['avg_score'] ?? 0), 2); $updateSql = "UPDATE `products` SET `rating_avg` = :avg, `rating_count` = :cnt WHERE `id` = :id"; $this->query($updateSql, [ ':avg' => $avg, ':cnt' => $cnt, ':id' => $productId ]); return ['rating_avg' => $avg, 'rating_count' => $cnt]; } /** * Recalculate cached rating values in `stores` table / public function recalculateStoreRating(int $storeId): array { $sql = "SELECT COUNT() as cnt, COALESCE(AVG(rating), 0) as avg_score FROM `reviews` WHERE store_id = :store_id AND status = 'active'"; $stat = $this->fetchOne($sql, [':store_id' => $storeId]); $cnt = (int)($stat['cnt'] ?? 0); $avg = round((float)($stat['avg_score'] ?? 0), 2); $updateSql = "UPDATE `stores` SET `rating_avg` = :avg, `rating_count` = :cnt WHERE `id` = :id"; $this->query($updateSql, [ ':avg' => $avg, ':cnt' => $cnt, ':id' => $storeId ]); return ['rating_avg' => $avg, 'rating_count' => $cnt]; } /** * Anonymize reviewer name for public privacy protection / public function formatReviewerName(array $review, bool $anonymize = true): string { $isAnon = !empty($review['is_anonymous']); $rawName = trim(($review['first_name'] ?? '') . ' ' . mb_substr($review['last_name'] ?? '', 0, 1) . '.'); if (empty($rawName) \|\| $rawName === '.') { $rawName = $review['username'] ?? 'User'; } if ($anonymize && $isAnon) { $len = mb_strlen($rawName); if ($len <= 2) { return mb_substr($rawName, 0, 1) . '*'; } return mb_substr($rawName, 0, 1) . str_repeat('', min(4, $len - 2)) . mb_substr($rawName, -1); } return $rawName; } }

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