Alisa
979 lines
36 KiB
PHP
979 lines
36 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Repositories;
|
|
|
|
use App\Services\PriceTierService;
|
|
use App\Services\SmartSearchService;
|
|
|
|
final class ProductRepository extends BaseRepository
|
|
{
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function popularPreview(int $limit = 10): array
|
|
{
|
|
$limit = max(1, min($limit, 30));
|
|
$sql = $this->previewSelectSql(
|
|
'ORDER BY p.sales_count DESC, RAND()',
|
|
$limit
|
|
);
|
|
|
|
$statement = $this->pdo()->query($sql);
|
|
|
|
return $this->preparePreviewProducts($statement->fetchAll());
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function promoPreview(int $limit = 10): array
|
|
{
|
|
return array_slice($this->promoProducts(max(1, min($limit, 10))), 0, max(1, min($limit, 10)));
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function promoProducts(int $limit = 50): array
|
|
{
|
|
$limit = max(1, min($limit, 50));
|
|
$products = $this->manualPromoPreview($limit);
|
|
$usedIds = array_flip(array_map(static fn (array $product): int => (int) ($product['id'] ?? 0), $products));
|
|
|
|
$fallbackProducts = $this->catalogPreview(5000);
|
|
$fallbackProducts = array_values(array_filter(array_map(
|
|
fn (array $product): ?array => $this->withRetailPreviewVariant($product, 6.0),
|
|
$fallbackProducts
|
|
)));
|
|
|
|
usort($fallbackProducts, static function (array $left, array $right): int {
|
|
return (int) sprintf('%u', crc32('promo|' . ($left['id'] ?? 0)))
|
|
<=> (int) sprintf('%u', crc32('promo|' . ($right['id'] ?? 0)));
|
|
});
|
|
|
|
foreach ($fallbackProducts as $fallbackProduct) {
|
|
$fallbackId = (int) ($fallbackProduct['id'] ?? 0);
|
|
if (isset($usedIds[$fallbackId])) {
|
|
continue;
|
|
}
|
|
|
|
$products[] = $fallbackProduct;
|
|
if (count($products) >= $limit) {
|
|
break;
|
|
}
|
|
}
|
|
|
|
return array_map(
|
|
fn (array $product): array => $this->applyPromoPricing($product),
|
|
array_slice($products, 0, $limit)
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{products: array<int, array<string, mixed>>, total: int}
|
|
*/
|
|
public function promoListing(int $limit = 48, int $offset = 0): array
|
|
{
|
|
$products = $this->promoProducts(50);
|
|
$total = count($products);
|
|
|
|
return [
|
|
'products' => array_slice($products, max(0, $offset), max(1, min($limit, 48))),
|
|
'total' => $total,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function newPreview(int $limit = 10): array
|
|
{
|
|
$limit = max(1, min($limit, 50));
|
|
$sql = $this->previewSelectSql(
|
|
'ORDER BY p.created_at DESC, p.id DESC',
|
|
$limit,
|
|
0,
|
|
false
|
|
);
|
|
|
|
$statement = $this->pdo()->query($sql);
|
|
$products = $this->preparePreviewProducts($statement->fetchAll());
|
|
|
|
foreach ($products as &$product) {
|
|
$product['badge_label'] = 'Новинка';
|
|
$product['badge_kind'] = 'new';
|
|
}
|
|
unset($product);
|
|
|
|
return $products;
|
|
}
|
|
|
|
/**
|
|
* @return array{products: array<int, array<string, mixed>>, total: int}
|
|
*/
|
|
public function newListing(int $limit = 48, int $offset = 0): array
|
|
{
|
|
$products = $this->newPreview(50);
|
|
$total = count($products);
|
|
|
|
return [
|
|
'products' => array_slice($products, max(0, $offset), max(1, min($limit, 48))),
|
|
'total' => $total,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function categoryRetailPreview(int $categoryId, int $limit = 10, float $maxPackageQuantity = 6.0): array
|
|
{
|
|
return $this->categoryRetailShowcase($categoryId, $limit, $maxPackageQuantity)['products'];
|
|
}
|
|
|
|
/**
|
|
* @return array{products: array<int, array<string, mixed>>, total: int}
|
|
*/
|
|
public function categoryRetailShowcase(int $categoryId, int $limit = 10, float $maxPackageQuantity = 6.0): array
|
|
{
|
|
$products = $this->forCategory($categoryId, 5000, 0, false);
|
|
$total = count($products);
|
|
$products = array_values(array_filter(array_map(
|
|
fn (array $product): ?array => $this->withRetailPreviewVariant($product, $maxPackageQuantity),
|
|
$products
|
|
)));
|
|
|
|
usort($products, static function (array $left, array $right): int {
|
|
return [
|
|
(int) sprintf('%u', crc32('home|' . ($left['id'] ?? 0))),
|
|
(string) ($left['name'] ?? ''),
|
|
] <=> [
|
|
(int) sprintf('%u', crc32('home|' . ($right['id'] ?? 0))),
|
|
(string) ($right['name'] ?? ''),
|
|
];
|
|
});
|
|
|
|
return [
|
|
'products' => array_slice($products, 0, max(1, min($limit, 10))),
|
|
'total' => $total,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function catalogPreview(int $limit = 24, int $offset = 0, bool $includeHidden = false): array
|
|
{
|
|
$limit = max(1, min($limit, 5000));
|
|
$offset = max(0, $offset);
|
|
$sql = $this->previewSelectSql(
|
|
'ORDER BY (p.is_published = 0 OR p.is_available = 0), p.sort_order, p.name',
|
|
$limit,
|
|
$offset,
|
|
$includeHidden
|
|
);
|
|
|
|
$statement = $this->pdo()->query($sql);
|
|
|
|
return $this->preparePreviewProducts($statement->fetchAll());
|
|
}
|
|
|
|
public function catalogTotal(bool $includeHidden = false): int
|
|
{
|
|
$statusSql = $includeHidden ? '' : ' AND p.is_published = 1 AND p.is_available = 1';
|
|
$statement = $this->pdo()->query('SELECT COUNT(*) FROM products p WHERE 1=1' . $statusSql);
|
|
|
|
return (int) $statement->fetchColumn();
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array{slug: string, updated_at: string|null, published_at: string|null, created_at: string|null}>
|
|
*/
|
|
public function sitemapProducts(int $limit = 10000): array
|
|
{
|
|
$statement = $this->pdo()->query(
|
|
'SELECT slug, updated_at, published_at, created_at
|
|
FROM products
|
|
WHERE is_published = 1
|
|
AND is_available = 1
|
|
ORDER BY updated_at DESC, published_at DESC, created_at DESC, id DESC
|
|
LIMIT ' . max(1, min($limit, 10000))
|
|
);
|
|
|
|
return $statement->fetchAll();
|
|
}
|
|
|
|
/**
|
|
* @return array{products: array<int, array<string, mixed>>, total: int}
|
|
*/
|
|
public function searchCatalog(string $query, int $limit = 48, int $offset = 0, bool $includeHidden = false): array
|
|
{
|
|
$limit = max(1, min($limit, 5000));
|
|
$offset = max(0, $offset);
|
|
$products = $this->catalogPreview(5000, 0, $includeHidden);
|
|
$rankedProducts = (new SmartSearchService())->rankProducts($query, $products);
|
|
|
|
return [
|
|
'products' => array_slice($rankedProducts, $offset, $limit),
|
|
'total' => count($rankedProducts),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function forCategory(int $categoryId, int $limit = 48, int $offset = 0, bool $includeHidden = false): array
|
|
{
|
|
$limit = max(1, min($limit, 5000));
|
|
$offset = max(0, $offset);
|
|
$categoryIds = (new CategoryRepository())->descendantIds($categoryId);
|
|
$categoryParents = $this->categoryParentMap();
|
|
$requestedRootId = $this->rootCategoryId($categoryId, $categoryParents);
|
|
$isRootCategory = ($categoryParents[$categoryId] ?? null) === null;
|
|
$placeholders = implode(',', array_fill(0, count($categoryIds), '?'));
|
|
$sql = $this->previewSelectSql(
|
|
'AND (
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM product_category_links pcl
|
|
WHERE pcl.product_id = p.id
|
|
AND pcl.category_id IN (' . $placeholders . ')
|
|
)
|
|
OR (
|
|
p.category_id IN (' . $placeholders . ')
|
|
AND (p.legacy_path IS NULL OR p.legacy_path NOT LIKE \'/katalog/product/view/1/%\')
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM product_category_links pcl_any
|
|
WHERE pcl_any.product_id = p.id
|
|
)
|
|
)
|
|
)
|
|
ORDER BY (p.is_published = 0 OR p.is_available = 0), p.sort_order, p.name',
|
|
5000,
|
|
0,
|
|
$includeHidden
|
|
);
|
|
|
|
$statement = $this->pdo()->prepare($sql);
|
|
$statement->execute(array_merge($categoryIds, $categoryIds));
|
|
|
|
$categoryLookup = array_flip($categoryIds);
|
|
$products = $this->preparePreviewProducts($statement->fetchAll());
|
|
$filtered = [];
|
|
|
|
foreach ($products as $product) {
|
|
$leafCategoryIds = $product['category_display_ids'] ?? [];
|
|
$leafRootIds = $product['category_display_root_ids'] ?? [];
|
|
|
|
if ($leafCategoryIds === []) {
|
|
if (isset($categoryLookup[(int) ($product['category_id'] ?? 0)])) {
|
|
$filtered[] = $product;
|
|
}
|
|
} elseif (
|
|
array_intersect($leafCategoryIds, $categoryIds) !== []
|
|
&& (!$isRootCategory || array_values(array_unique($leafRootIds)) === [$requestedRootId])
|
|
) {
|
|
$filtered[] = $product;
|
|
}
|
|
}
|
|
|
|
usort($filtered, static function (array $left, array $right): int {
|
|
$leftUnavailable = ((int) ($left['is_published'] ?? 1) === 0 || (int) ($left['is_available'] ?? 1) === 0) ? 1 : 0;
|
|
$rightUnavailable = ((int) ($right['is_published'] ?? 1) === 0 || (int) ($right['is_available'] ?? 1) === 0) ? 1 : 0;
|
|
|
|
return [$leftUnavailable, (int) ($left['sort_order'] ?? 0), (string) ($left['name'] ?? '')]
|
|
<=> [$rightUnavailable, (int) ($right['sort_order'] ?? 0), (string) ($right['name'] ?? '')];
|
|
});
|
|
|
|
return array_slice($filtered, $offset, $limit);
|
|
}
|
|
|
|
public function categoryTotal(int $categoryId, bool $includeHidden = false): int
|
|
{
|
|
return count($this->forCategory($categoryId, 5000, 0, $includeHidden));
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>|null
|
|
*/
|
|
public function findBySlug(string $slug): ?array
|
|
{
|
|
$statement = $this->pdo()->prepare(
|
|
'SELECT
|
|
p.*,
|
|
COALESCE(linked_c.id, c.id) AS display_category_id,
|
|
COALESCE(linked_c.name, c.name) AS category_name,
|
|
COALESCE(linked_c.slug, c.slug) AS category_slug
|
|
FROM products p
|
|
LEFT JOIN categories c ON c.id = p.category_id
|
|
LEFT JOIN categories linked_c
|
|
ON linked_c.id = (
|
|
SELECT pcl.category_id
|
|
FROM product_category_links pcl
|
|
INNER JOIN categories lc ON lc.id = pcl.category_id
|
|
WHERE pcl.product_id = p.id
|
|
AND lc.is_active = 1
|
|
ORDER BY lc.parent_id IS NULL, pcl.sort_order, pcl.category_id
|
|
LIMIT 1
|
|
)
|
|
WHERE p.slug = :slug
|
|
AND p.is_published = 1
|
|
AND p.is_available = 1
|
|
LIMIT 1'
|
|
);
|
|
$statement->execute(['slug' => $slug]);
|
|
$product = $statement->fetch();
|
|
|
|
return $product ?: null;
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function variantsForProduct(int $productId): array
|
|
{
|
|
$statement = $this->pdo()->prepare(
|
|
'SELECT id, product_id, name, unit, package_quantity, step_quantity, price_per_unit, package_price, old_package_price, is_default
|
|
FROM product_variants
|
|
WHERE product_id = :product_id
|
|
AND is_published = 1
|
|
AND is_available = 1
|
|
ORDER BY is_default DESC, sort_order, id'
|
|
);
|
|
$statement->execute(['product_id' => $productId]);
|
|
|
|
return array_map(
|
|
static function (array $image): array {
|
|
$path = (string) ($image['path'] ?? '');
|
|
$image['preview_path'] = \function_exists('optimized_image_path')
|
|
? \optimized_image_path($path, 'preview')
|
|
: $path;
|
|
|
|
return $image;
|
|
},
|
|
$statement->fetchAll()
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function imagesForProduct(int $productId): array
|
|
{
|
|
$statement = $this->pdo()->prepare(
|
|
'SELECT id, path, alt, sort_order
|
|
FROM product_images
|
|
WHERE product_id = :product_id
|
|
ORDER BY sort_order, id'
|
|
);
|
|
$statement->execute(['product_id' => $productId]);
|
|
|
|
return $statement->fetchAll();
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $items
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function cartSnapshots(array $items): array
|
|
{
|
|
$result = [];
|
|
|
|
foreach ($items as $item) {
|
|
$key = (string) ($item['key'] ?? '');
|
|
$productId = (int) ($item['productId'] ?? 0);
|
|
$variantId = (int) ($item['variantId'] ?? 0);
|
|
$desiredPackageQuantity = (float) ($item['packageQuantity'] ?? 0);
|
|
|
|
if ($key === '' || $productId <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$variantJoin = $variantId > 0
|
|
? 'v.product_id = p.id AND v.id = :variant_id'
|
|
: 'v.id = (
|
|
SELECT pv.id
|
|
FROM product_variants pv
|
|
WHERE pv.product_id = p.id
|
|
ORDER BY pv.is_default DESC, pv.sort_order, pv.id
|
|
LIMIT 1
|
|
)';
|
|
|
|
$statement = $this->pdo()->prepare(
|
|
'SELECT
|
|
p.id AS product_id,
|
|
p.name,
|
|
p.slug,
|
|
p.main_image_path,
|
|
p.is_published AS product_is_published,
|
|
p.is_available AS product_is_available,
|
|
v.id AS variant_id,
|
|
v.name AS variant_name,
|
|
v.unit,
|
|
v.package_quantity,
|
|
v.price_per_unit,
|
|
v.package_price,
|
|
v.is_published AS variant_is_published,
|
|
v.is_available AS variant_is_available
|
|
FROM products p
|
|
LEFT JOIN product_variants v ON ' . $variantJoin . '
|
|
WHERE p.id = :product_id
|
|
LIMIT 1'
|
|
);
|
|
$params = ['product_id' => $productId];
|
|
if ($variantId > 0) {
|
|
$params['variant_id'] = $variantId;
|
|
}
|
|
$statement->execute($params);
|
|
$row = $statement->fetch();
|
|
|
|
if (!$row) {
|
|
$result[] = [
|
|
'key' => $key,
|
|
'isAvailable' => false,
|
|
'reason' => 'Товар больше не найден в каталоге.',
|
|
];
|
|
continue;
|
|
}
|
|
|
|
if (empty($row['variant_id'])) {
|
|
$fallbackStatement = $this->pdo()->prepare(
|
|
'SELECT
|
|
p.id AS product_id,
|
|
p.name,
|
|
p.slug,
|
|
p.main_image_path,
|
|
p.is_published AS product_is_published,
|
|
p.is_available AS product_is_available,
|
|
v.id AS variant_id,
|
|
v.name AS variant_name,
|
|
v.unit,
|
|
v.package_quantity,
|
|
v.price_per_unit,
|
|
v.package_price,
|
|
v.is_published AS variant_is_published,
|
|
v.is_available AS variant_is_available
|
|
FROM products p
|
|
INNER JOIN product_variants v ON v.product_id = p.id
|
|
WHERE p.id = :product_id
|
|
ORDER BY
|
|
(v.is_published = 1 AND v.is_available = 1 AND v.package_price > 0) DESC,
|
|
CASE WHEN :package_quantity_check > 0 THEN ABS(v.package_quantity - :package_quantity) ELSE 0 END,
|
|
v.is_default DESC,
|
|
v.sort_order,
|
|
v.id
|
|
LIMIT 1'
|
|
);
|
|
$fallbackStatement->execute([
|
|
'product_id' => $productId,
|
|
'package_quantity' => $desiredPackageQuantity,
|
|
'package_quantity_check' => $desiredPackageQuantity,
|
|
]);
|
|
$fallbackRow = $fallbackStatement->fetch();
|
|
|
|
if ($fallbackRow) {
|
|
$row = $fallbackRow;
|
|
}
|
|
}
|
|
|
|
$productIsAvailable = (int) ($row['product_is_published'] ?? 0) === 1
|
|
&& (int) ($row['product_is_available'] ?? 0) === 1;
|
|
$variantIsAvailable = !empty($row['variant_id'])
|
|
&& (int) ($row['variant_is_published'] ?? 0) === 1
|
|
&& (int) ($row['variant_is_available'] ?? 0) === 1
|
|
&& (float) ($row['package_price'] ?? 0) > 0;
|
|
|
|
$reason = '';
|
|
if (!$productIsAvailable) {
|
|
$reason = 'Товар сейчас не опубликован или отсутствует в наличии.';
|
|
} elseif (!$variantIsAvailable) {
|
|
$reason = 'Выбранная фасовка сейчас недоступна.';
|
|
}
|
|
|
|
$result[] = [
|
|
'key' => $key,
|
|
'productId' => (int) $row['product_id'],
|
|
'variantId' => !empty($row['variant_id']) ? (int) $row['variant_id'] : $variantId,
|
|
'name' => (string) ($row['name'] ?? ''),
|
|
'slug' => (string) ($row['slug'] ?? ''),
|
|
'image' => \function_exists('optimized_image_path')
|
|
? \optimized_image_path((string) ($row['main_image_path'] ?? ''), 'preview')
|
|
: (string) ($row['main_image_path'] ?? ''),
|
|
'variantName' => (string) ($row['variant_name'] ?? 'Фасовка'),
|
|
'unit' => (string) ($row['unit'] ?? ''),
|
|
'packageQuantity' => (float) ($row['package_quantity'] ?? 0),
|
|
'baseUnitPrice' => (float) ($row['price_per_unit'] ?? 0),
|
|
'basePackagePrice' => (float) ($row['package_price'] ?? 0),
|
|
'isAvailable' => $productIsAvailable && $variantIsAvailable,
|
|
'reason' => $reason,
|
|
];
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
public function adminLatest(int $limit = 30): array
|
|
{
|
|
$limit = max(1, min($limit, 100));
|
|
$statement = $this->pdo()->query(
|
|
'SELECT p.id, p.name, p.slug, p.is_published, c.name AS category_name, p.created_at
|
|
FROM products p
|
|
LEFT JOIN categories c ON c.id = p.category_id
|
|
ORDER BY p.created_at DESC, p.id DESC
|
|
LIMIT ' . $limit
|
|
);
|
|
|
|
return $statement->fetchAll();
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed> $data
|
|
*/
|
|
public function createWithVariant(array $data): int
|
|
{
|
|
if (($data['name'] ?? '') === '' || ($data['slug'] ?? '') === '') {
|
|
throw new \InvalidArgumentException('Название и slug обязательны.');
|
|
}
|
|
|
|
$pdo = $this->pdo();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$statement = $pdo->prepare(
|
|
'INSERT INTO products
|
|
(category_id, name, slug, short_description, seo_title, seo_description, seo_keywords, base_unit, is_published, is_available, published_at)
|
|
VALUES
|
|
(:category_id, :name, :slug, :short_description, :seo_title, :seo_description, :seo_keywords, :base_unit, :is_published, 1, :published_at)'
|
|
);
|
|
$statement->execute([
|
|
'category_id' => $data['category_id'],
|
|
'name' => $data['name'],
|
|
'slug' => $data['slug'],
|
|
'short_description' => $data['short_description'] ?: null,
|
|
'seo_title' => $data['seo_title'] ?: null,
|
|
'seo_description' => $data['seo_description'] ?: null,
|
|
'seo_keywords' => $data['seo_keywords'] ?: null,
|
|
'base_unit' => $data['base_unit'] ?: 'kg',
|
|
'is_published' => $data['is_published'] ?? 0,
|
|
'published_at' => !empty($data['is_published']) ? date('Y-m-d H:i:s') : null,
|
|
]);
|
|
|
|
$productId = (int) $pdo->lastInsertId();
|
|
|
|
if (($data['variant_name'] ?? '') !== '' && (float) ($data['package_price'] ?? 0) > 0) {
|
|
$variantStatement = $pdo->prepare(
|
|
'INSERT INTO product_variants
|
|
(product_id, name, unit, package_quantity, step_quantity, price_per_unit, package_price, old_package_price, discount_type, discount_percent, is_default, is_published, is_available)
|
|
VALUES
|
|
(:product_id, :name, :unit, :package_quantity, :step_quantity, :price_per_unit, :package_price, :old_package_price, :discount_type, :discount_percent, 1, 1, 1)'
|
|
);
|
|
$variantStatement->execute([
|
|
'product_id' => $productId,
|
|
'name' => $data['variant_name'],
|
|
'unit' => $data['base_unit'] ?: 'kg',
|
|
'package_quantity' => $data['package_quantity'] ?: 1,
|
|
'step_quantity' => $data['step_quantity'] ?: ($data['package_quantity'] ?: 1),
|
|
'price_per_unit' => $data['price_per_unit'],
|
|
'package_price' => $data['package_price'],
|
|
'old_package_price' => $data['old_package_price'] ?? null,
|
|
'discount_type' => $data['discount_type'] ?? 'none',
|
|
'discount_percent' => $data['discount_percent'] ?? null,
|
|
]);
|
|
}
|
|
|
|
$pdo->commit();
|
|
|
|
return $productId;
|
|
} catch (\Throwable $exception) {
|
|
$pdo->rollBack();
|
|
throw $exception;
|
|
}
|
|
}
|
|
|
|
private function previewSelectSql(string $tailSql, int $limit, int $offset = 0, bool $includeHidden = false): string
|
|
{
|
|
return 'SELECT
|
|
p.id,
|
|
p.name,
|
|
p.slug,
|
|
p.legacy_path,
|
|
p.h1,
|
|
p.seo_title,
|
|
p.seo_description,
|
|
p.seo_keywords,
|
|
p.category_id,
|
|
p.is_published,
|
|
p.is_available,
|
|
c.name AS category_name,
|
|
c.slug AS category_slug,
|
|
p.main_image_path,
|
|
p.base_unit,
|
|
p.package_display_mode,
|
|
p.sales_count,
|
|
p.sort_order,
|
|
v.id AS variant_id,
|
|
v.name AS variant_name,
|
|
v.unit,
|
|
v.package_quantity,
|
|
v.price_per_unit,
|
|
v.package_price,
|
|
v.old_package_price,
|
|
v.discount_type,
|
|
v.discount_percent
|
|
FROM products p
|
|
LEFT JOIN categories c ON c.id = p.category_id
|
|
LEFT JOIN product_variants v
|
|
ON v.id = (
|
|
SELECT pv.id
|
|
FROM product_variants pv
|
|
WHERE pv.product_id = p.id
|
|
AND pv.is_published = 1
|
|
AND pv.is_available = 1
|
|
ORDER BY pv.is_default DESC, pv.sort_order, pv.id
|
|
LIMIT 1
|
|
)
|
|
WHERE 1=1
|
|
' . ($includeHidden ? '' : 'AND p.is_published = 1 AND p.is_available = 1') . '
|
|
' . $tailSql . '
|
|
LIMIT ' . $limit . ' OFFSET ' . $offset;
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed> $product
|
|
* @return array<string, mixed>|null
|
|
*/
|
|
private function withRetailPreviewVariant(array $product, float $maxPackageQuantity): ?array
|
|
{
|
|
$variants = array_values(array_filter(
|
|
$product['preview_variants'] ?? [],
|
|
static function (array $variant) use ($maxPackageQuantity): bool {
|
|
$packagePrice = (float) ($variant['package_price'] ?? 0);
|
|
$packageQuantity = (float) ($variant['package_quantity'] ?? 0);
|
|
|
|
return $packagePrice > 0
|
|
&& $packageQuantity > 0
|
|
&& $packageQuantity <= $maxPackageQuantity;
|
|
}
|
|
));
|
|
|
|
if ($variants === []) {
|
|
return null;
|
|
}
|
|
|
|
$product['preview_variants'] = $variants;
|
|
|
|
return $this->syncProductVariantFields($product, $variants[0]);
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function manualPromoPreview(int $limit): array
|
|
{
|
|
$statement = $this->pdo()->query(
|
|
'SELECT product_id
|
|
FROM promo_block_items
|
|
WHERE is_active = 1
|
|
ORDER BY sort_order, id
|
|
LIMIT ' . max(1, min($limit, 10))
|
|
);
|
|
$productIds = array_map('intval', $statement->fetchAll(\PDO::FETCH_COLUMN));
|
|
|
|
if ($productIds === []) {
|
|
return [];
|
|
}
|
|
|
|
$placeholders = implode(',', array_fill(0, count($productIds), '?'));
|
|
$sql = $this->previewSelectSql(
|
|
'AND p.id IN (' . $placeholders . ')
|
|
ORDER BY FIELD(p.id, ' . $placeholders . ')',
|
|
count($productIds),
|
|
0,
|
|
false
|
|
);
|
|
|
|
$productsStatement = $this->pdo()->prepare($sql);
|
|
$productsStatement->execute(array_merge($productIds, $productIds));
|
|
$products = $this->preparePreviewProducts($productsStatement->fetchAll());
|
|
$productsById = [];
|
|
|
|
foreach ($products as $product) {
|
|
$retailProduct = $this->withRetailPreviewVariant($product, 6.0);
|
|
if ($retailProduct !== null) {
|
|
$productsById[(int) ($product['id'] ?? 0)] = $retailProduct;
|
|
}
|
|
}
|
|
|
|
$result = [];
|
|
foreach ($productIds as $productId) {
|
|
if (isset($productsById[$productId])) {
|
|
$result[] = $productsById[$productId];
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed> $product
|
|
* @param array<string, mixed> $variant
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function syncProductVariantFields(array $product, array $variant): array
|
|
{
|
|
$product['variant_id'] = $variant['id'] ?? $product['variant_id'] ?? null;
|
|
$product['variant_name'] = $variant['name'] ?? $product['variant_name'] ?? null;
|
|
$product['unit'] = $variant['unit'] ?? $product['unit'] ?? null;
|
|
$product['package_quantity'] = $variant['package_quantity'] ?? $product['package_quantity'] ?? null;
|
|
$product['price_per_unit'] = $variant['price_per_unit'] ?? $product['price_per_unit'] ?? null;
|
|
$product['package_price'] = $variant['package_price'] ?? $product['package_price'] ?? null;
|
|
$product['old_package_price'] = $variant['old_package_price'] ?? $product['old_package_price'] ?? null;
|
|
|
|
return $product;
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed> $product
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function applyPromoPricing(array $product): array
|
|
{
|
|
$discountPercent = 5 + ((int) sprintf('%u', crc32('discount|' . ($product['id'] ?? 0))) % 31);
|
|
$divider = max(0.01, 1 - ($discountPercent / 100));
|
|
$variants = [];
|
|
|
|
foreach (($product['preview_variants'] ?? []) as $variant) {
|
|
$packagePrice = (float) ($variant['package_price'] ?? 0);
|
|
if ($packagePrice > 0) {
|
|
$variant['old_package_price'] = (string) ceil($packagePrice / $divider);
|
|
}
|
|
$variants[] = $variant;
|
|
}
|
|
|
|
$product['preview_variants'] = $variants;
|
|
$product = $variants === [] ? $product : $this->syncProductVariantFields($product, $variants[0]);
|
|
$product['badge_label'] = '-' . $discountPercent . '%';
|
|
$product['badge_kind'] = 'promo';
|
|
$product['promo_discount_percent'] = $discountPercent;
|
|
|
|
return $product;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $products
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function preparePreviewProducts(array $products): array
|
|
{
|
|
return $this->attachPreviewVariants($this->attachDisplayCategories($this->attachPriceTiers($products)));
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $products
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function attachPreviewVariants(array $products): array
|
|
{
|
|
if ($products === []) {
|
|
return [];
|
|
}
|
|
|
|
$productIds = array_map(static fn (array $product): int => (int) $product['id'], $products);
|
|
$placeholders = implode(',', array_fill(0, count($productIds), '?'));
|
|
$statement = $this->pdo()->prepare(
|
|
'SELECT id, product_id, name, unit, package_quantity, step_quantity, price_per_unit, package_price, old_package_price, is_default, sort_order
|
|
FROM product_variants
|
|
WHERE product_id IN (' . $placeholders . ')
|
|
AND is_published = 1
|
|
AND is_available = 1
|
|
ORDER BY product_id, is_default DESC, sort_order, id'
|
|
);
|
|
$statement->execute($productIds);
|
|
|
|
$variantsByProduct = [];
|
|
foreach ($statement->fetchAll() as $variant) {
|
|
$productId = (int) $variant['product_id'];
|
|
if (count($variantsByProduct[$productId] ?? []) >= 6) {
|
|
continue;
|
|
}
|
|
$variantsByProduct[$productId][] = $variant;
|
|
}
|
|
|
|
foreach ($products as &$product) {
|
|
$product['preview_variants'] = $variantsByProduct[(int) $product['id']] ?? [];
|
|
}
|
|
|
|
unset($product);
|
|
|
|
return $products;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $products
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function attachDisplayCategories(array $products): array
|
|
{
|
|
if ($products === []) {
|
|
return [];
|
|
}
|
|
|
|
$productIds = array_map(static fn (array $product): int => (int) $product['id'], $products);
|
|
$placeholders = implode(',', array_fill(0, count($productIds), '?'));
|
|
$statement = $this->pdo()->prepare(
|
|
'SELECT pcl.product_id, c.id, c.parent_id, c.name, c.sort_order
|
|
FROM product_category_links pcl
|
|
INNER JOIN categories c ON c.id = pcl.category_id
|
|
WHERE pcl.product_id IN (' . $placeholders . ')
|
|
AND c.is_active = 1
|
|
ORDER BY pcl.product_id, c.sort_order, c.name'
|
|
);
|
|
$statement->execute($productIds);
|
|
|
|
$categoriesByProduct = [];
|
|
foreach ($statement->fetchAll() as $row) {
|
|
$categoriesByProduct[(int) $row['product_id']][(int) $row['id']] = [
|
|
'id' => (int) $row['id'],
|
|
'parent_id' => $row['parent_id'] === null ? null : (int) $row['parent_id'],
|
|
'name' => (string) $row['name'],
|
|
'sort_order' => (int) $row['sort_order'],
|
|
];
|
|
}
|
|
|
|
$categoryParents = $this->categoryParentMap();
|
|
$categoryRoots = $this->categoryRootMap($categoryParents);
|
|
|
|
foreach ($products as &$product) {
|
|
$linkedCategories = $categoriesByProduct[(int) $product['id']] ?? [];
|
|
|
|
if ($linkedCategories === [] && !empty($product['category_name'])) {
|
|
$product['category_display_name'] = $product['category_name'];
|
|
continue;
|
|
}
|
|
|
|
$leafCategories = [];
|
|
foreach ($linkedCategories as $categoryId => $category) {
|
|
if ($this->hasLinkedDescendant($categoryId, array_keys($linkedCategories), $categoryParents)) {
|
|
continue;
|
|
}
|
|
$leafCategories[] = $category;
|
|
}
|
|
|
|
usort($leafCategories, static function (array $left, array $right): int {
|
|
return [$left['sort_order'], $left['name']] <=> [$right['sort_order'], $right['name']];
|
|
});
|
|
|
|
$names = array_values(array_unique(array_map(static fn (array $category): string => $category['name'], $leafCategories)));
|
|
$product['category_display_ids'] = array_values(array_map(static fn (array $category): int => (int) $category['id'], $leafCategories));
|
|
$product['category_display_root_ids'] = array_values(array_unique(array_map(
|
|
static fn (array $category): int => (int) ($categoryRoots[(int) $category['id']] ?? $category['id']),
|
|
$leafCategories
|
|
)));
|
|
$product['category_display_name'] = $names === []
|
|
? ($product['category_name'] ?? 'Каталог')
|
|
: implode(' / ', $names);
|
|
}
|
|
|
|
unset($product);
|
|
|
|
return $products;
|
|
}
|
|
|
|
/**
|
|
* @return array<int, int|null>
|
|
*/
|
|
private function categoryParentMap(): array
|
|
{
|
|
$statement = $this->pdo()->query('SELECT id, parent_id FROM categories WHERE is_active = 1');
|
|
$parents = [];
|
|
|
|
foreach ($statement->fetchAll() as $category) {
|
|
$parents[(int) $category['id']] = $category['parent_id'] === null ? null : (int) $category['parent_id'];
|
|
}
|
|
|
|
return $parents;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, int|null> $categoryParents
|
|
* @return array<int, int>
|
|
*/
|
|
private function categoryRootMap(array $categoryParents): array
|
|
{
|
|
$roots = [];
|
|
|
|
foreach (array_keys($categoryParents) as $categoryId) {
|
|
$roots[$categoryId] = $this->rootCategoryId((int) $categoryId, $categoryParents);
|
|
}
|
|
|
|
return $roots;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, int|null> $categoryParents
|
|
*/
|
|
private function rootCategoryId(int $categoryId, array $categoryParents): int
|
|
{
|
|
$currentId = $categoryId;
|
|
$parentId = $categoryParents[$currentId] ?? null;
|
|
|
|
while ($parentId !== null) {
|
|
$currentId = $parentId;
|
|
$parentId = $categoryParents[$currentId] ?? null;
|
|
}
|
|
|
|
return $currentId;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, int> $linkedCategoryIds
|
|
* @param array<int, int|null> $categoryParents
|
|
*/
|
|
private function hasLinkedDescendant(int $categoryId, array $linkedCategoryIds, array $categoryParents): bool
|
|
{
|
|
$linkedLookup = array_flip($linkedCategoryIds);
|
|
|
|
foreach ($linkedCategoryIds as $linkedCategoryId) {
|
|
if ($linkedCategoryId === $categoryId) {
|
|
continue;
|
|
}
|
|
|
|
$parentId = $categoryParents[$linkedCategoryId] ?? null;
|
|
while ($parentId !== null) {
|
|
if ($parentId === $categoryId && isset($linkedLookup[$linkedCategoryId])) {
|
|
return true;
|
|
}
|
|
|
|
$parentId = $categoryParents[$parentId] ?? null;
|
|
}
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $products
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function attachPriceTiers(array $products): array
|
|
{
|
|
$priceTierService = new PriceTierService();
|
|
|
|
foreach ($products as &$product) {
|
|
$basePrice = $product['price_per_unit'] ?? $product['package_price'] ?? null;
|
|
$product['price_tiers'] = $basePrice === null
|
|
? []
|
|
: $priceTierService->forAmount((float) $basePrice);
|
|
}
|
|
|
|
unset($product);
|
|
|
|
return $products;
|
|
}
|
|
}
|