File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090039/Shop39/app/Models/Product.php

Back
<?php class Product extends Model { protected string $table = 'products'; public function getFeaturedProducts(int $limit = 8): array { $sql = "SELECT p., c.name AS category_name, sp.shop_name, sp.slug AS shop_slug, (SELECT image_path FROM product_images WHERE product_id = p.id ORDER BY is_primary DESC, id ASC LIMIT 1) AS primary_image, COALESCE((SELECT AVG(rating) FROM reviews WHERE product_id = p.id), 5.0) AS avg_rating, COALESCE((SELECT COUNT(id) FROM reviews WHERE product_id = p.id), 0) AS review_count FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN seller_profiles sp ON p.seller_id = sp.id WHERE p.status = 'active' ORDER BY p.id DESC LIMIT :limit"; $stmt = $this->db->prepare($sql); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function getNewArrivals(int $limit = 8): array { $sql = "SELECT p., c.name AS category_name, sp.shop_name, sp.slug AS shop_slug, (SELECT image_path FROM product_images WHERE product_id = p.id ORDER BY is_primary DESC, id ASC LIMIT 1) AS primary_image, COALESCE((SELECT AVG(rating) FROM reviews WHERE product_id = p.id), 5.0) AS avg_rating, COALESCE((SELECT COUNT(id) FROM reviews WHERE product_id = p.id), 0) AS review_count FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN seller_profiles sp ON p.seller_id = sp.id WHERE p.status = 'active' ORDER BY p.created_at DESC, p.id DESC LIMIT :limit"; $stmt = $this->db->prepare($sql); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function getBestSellers(int $limit = 8): array { $sql = "SELECT p., c.name AS category_name, sp.shop_name, sp.slug AS shop_slug, (SELECT image_path FROM product_images WHERE product_id = p.id ORDER BY is_primary DESC, id ASC LIMIT 1) AS primary_image, COALESCE((SELECT AVG(rating) FROM reviews WHERE product_id = p.id), 5.0) AS avg_rating, COALESCE((SELECT COUNT(id) FROM reviews WHERE product_id = p.id), 0) AS review_count FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN seller_profiles sp ON p.seller_id = sp.id WHERE p.status = 'active' ORDER BY p.id ASC LIMIT :limit"; $stmt = $this->db->prepare($sql); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function getProductWithDetails(string $slug): ?array { $sql = "SELECT p., c.name AS category_name, sp.shop_name, sp.slug AS shop_slug, sp.logo AS shop_logo FROM products p JOIN categories c ON p.category_id = c.id JOIN seller_profiles sp ON p.seller_id = sp.id WHERE p.slug = :slug AND p.status = 'active' LIMIT 1"; $stmt = $this->db->prepare($sql); $stmt->execute(['slug' => $slug]); $product = $stmt->fetch(); if (!$product) return null; $imgStmt = $this->db->prepare("SELECT * FROM product_images WHERE product_id = :pid ORDER BY is_primary DESC, id ASC"); $imgStmt->execute(['pid' => $product['id']]); $product['images'] = $imgStmt->fetchAll(); [$options, $variants] = $this->_loadOptionsAndVariants((int)$product['id']); $product['options'] = $options; $product['variants'] = $variants; return $product; } public function getFullProductData(int $productId): ?array { $stmt = $this->db->prepare("SELECT * FROM products WHERE id = :id"); $stmt->execute(['id' => $productId]); $product = $stmt->fetch(); if (!$product) return null; $imgStmt = $this->db->prepare("SELECT * FROM product_images WHERE product_id = :pid ORDER BY is_primary DESC, id ASC"); $imgStmt->execute(['pid' => $productId]); $product['images'] = $imgStmt->fetchAll(); [$options, $variants] = $this->_loadOptionsAndVariants($productId); $product['options'] = $options; $product['variants'] = $variants; return $product; } /** * Unified helper — loads options + normalised variants for a product. * * Every variant element is guaranteed to have these keys: * id, product_id, sku, price, sale_price, stock, stock_quantity, * image, variant_type, variant_value, combination_text, option_value_ids * * Works with BOTH: * - Legacy schema (variant_type/variant_value/stock_quantity only) * - New schema (product_options + variant_option_values + separate sku/price/sale_price/stock/image) / private function _loadOptionsAndVariants(int $productId): array { // ── 1. Discover actual product_variants columns (cached per request) ── static $variantColCache = null; if ($variantColCache === null) { $c = $this->db->query("SHOW COLUMNS FROM product_variants"); $variantColCache = $c ? $c->fetchAll(PDO::FETCH_COLUMN) : []; } $vc = $variantColCache; $hasSku = in_array('sku', $vc); $hasPrice = in_array('price', $vc); $hasSalePrice = in_array('sale_price', $vc); $hasImage = in_array('image', $vc); $hasStock = in_array('stock', $vc); $hasStockQty = in_array('stock_quantity', $vc); $hasVarType = in_array('variant_type', $vc); $hasVarValue = in_array('variant_value', $vc); // ── 2. Check variant_option_values table exists (cached) ── static $vovTableExists = null; if ($vovTableExists === null) { $t = $this->db->query("SHOW TABLES LIKE 'variant_option_values'"); $vovTableExists = $t && $t->rowCount() > 0; } // ── 3. Load product_options (new multi-option schema) ── $optStmt = $this->db->prepare( "SELECT FROM product_options WHERE product_id = :pid ORDER BY sort_order ASC, id ASC" ); $optStmt->execute(['pid' => $productId]); $options = $optStmt->fetchAll(); foreach ($options as &$opt) { $valStmt = $this->db->prepare( "SELECT * FROM product_option_values WHERE option_id = :oid ORDER BY sort_order ASC, id ASC" ); $valStmt->execute(['oid' => $opt['id']]); $opt['values'] = $valStmt->fetchAll(); } unset($opt); // ── 4. Load raw product_variants ── $varStmt = $this->db->prepare( "SELECT * FROM product_variants WHERE product_id = :pid ORDER BY id ASC" ); $varStmt->execute(['pid' => $productId]); $rawVariants = $varStmt->fetchAll(); // ── 5. Load variant_option_values links into a map: variant_id => [option_value_id, ...] ── $vovMap = []; if ($vovTableExists && !empty($rawVariants)) { $vovStmt = $this->db->prepare( "SELECT variant_id, option_value_id FROM variant_option_values WHERE variant_id IN (SELECT id FROM product_variants WHERE product_id = :pid)" ); $vovStmt->execute(['pid' => $productId]); foreach ($vovStmt->fetchAll() as $vov) { $vovMap[(int)$vov['variant_id']][] = (int)$vov['option_value_id']; } } // ── 6. Normalise every variant into a consistent array ── $variants = []; foreach ($rawVariants as $raw) { $vid = (int)$raw['id']; // Stock: prefer new 'stock' col, fall back to legacy 'stock_quantity' $stock = null; if ($hasStock && $raw['stock'] !== null) { $stock = (int)$raw['stock']; } elseif ($hasStockQty) { $stock = (int)$raw['stock_quantity']; } $variants[] = [ 'id' => $vid, 'product_id' => (int)$raw['product_id'], 'sku' => $hasSku ? ($raw['sku'] ?? null) : null, 'price' => ($hasPrice && $raw['price'] !== null) ? (float)$raw['price'] : null, 'sale_price' => ($hasSalePrice && $raw['sale_price'] !== null) ? (float)$raw['sale_price'] : null, 'stock' => $stock, 'stock_quantity' => $hasStockQty ? (int)$raw['stock_quantity'] : $stock, 'image' => $hasImage ? ($raw['image'] ?? null) : null, 'variant_type' => $hasVarType ? ($raw['variant_type'] ?? '') : '', 'variant_value' => $hasVarValue ? ($raw['variant_value'] ?? '') : '', 'price_modifier' => (float)($raw['price_modifier'] ?? 0), 'combination_text' => '', 'option_value_ids' => $vovMap[$vid] ?? [], ]; } // ── 7. Build combination_text for new-schema variants (have VOV links) ── if (!empty($options)) { foreach ($variants as &$v) { if (!empty($v['option_value_ids'])) { $parts = []; foreach ($options as $opt) { foreach ($opt['values'] as $ov) { if (in_array((int)$ov['id'], $v['option_value_ids'])) { $parts[] = $ov['value']; break; } } } $v['combination_text'] = implode(' / ', $parts); } } unset($v); } // ── 8. Legacy fallback: no product_options rows for this product ── // Synthesise options from variant_type/variant_value so the Detail // page renders selectable buttons and JS can still match by variant id. if (empty($options) && !empty($variants)) { $grouped = []; foreach ($variants as &$v) { $type = !empty($v['variant_type']) ? ucfirst($v['variant_type']) : 'ตัวเลือก'; $val = !empty($v['variant_value']) ? $v['variant_value'] : (!empty($v['sku']) ? $v['sku'] : 'ตัวเลือก ' . $v['id']); // For legacy variants: "option_value_id" === variant id (so JS lookup works) $v['option_value_ids'] = [$v['id']]; $v['combination_text'] = $val; if (!isset($grouped[$type])) { $grouped[$type] = [ 'id' => count($grouped) + 1, 'option_name' => $type, 'values' => [], ]; } $grouped[$type]['values'][] = [ 'id' => $v['id'], // variant id used as pseudo option_value_id 'value' => $val, ]; } unset($v); $options = array_values($grouped); } return [$options, $variants]; } }

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