459 lines
19 KiB
PHP
459 lines
19 KiB
PHP
<?php
|
|
|
|
namespace Query;
|
|
|
|
use Doctrine\DBAL\Connection;
|
|
use KupShop\I18nBundle\Translations\PhotosTranslation;
|
|
use KupShop\KupShopBundle\Config;
|
|
use KupShop\KupShopBundle\Context\UserContext;
|
|
use KupShop\KupShopBundle\Context\VatContext;
|
|
use KupShop\KupShopBundle\Query\JsonOperator;
|
|
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
|
|
use KupShop\KupShopBundle\Util\Contexts;
|
|
use KupShop\PricelistBundle\Context\PricelistContext;
|
|
use KupShop\SellerBundle\Context\SellerContext;
|
|
|
|
class Product
|
|
{
|
|
public static $fieldInStore = 'p.in_store';
|
|
|
|
public static function inCollectionsProducts(QueryBuilder $qb, array $ids)
|
|
{
|
|
$main_products = sqlQueryBuilder()->select('pc.id_product AS id')
|
|
->from('products_collections', 'pc')
|
|
->where('pc.id_product_related IN (:ids) OR pc.id_product IN (:ids)');
|
|
$related_products = sqlQueryBuilder()->select('pc.id_product_related AS id')
|
|
->from('products_collections', 'pc')
|
|
->leftJoin('pc', 'products_collections', 'pc_main', 'pc_main.id_product = pc.id_product')
|
|
->where('pc_main.id_product IN (:ids) OR pc_main.id_product_related IN (:ids)');
|
|
$qb->setParameter('ids', $ids, Connection::PARAM_INT_ARRAY);
|
|
|
|
return $main_products->getSQL().' UNION '.$related_products->getSQL();
|
|
}
|
|
|
|
public static function collectionsProductsSubQuery($productIds)
|
|
{
|
|
// tabulka [id, main_product, related_product]
|
|
// produkt neni v kolekci -> [id, null, null]
|
|
// produkt je hlavni v kolekci -> [id, id, related_product_1], [id, id, related_product_2], ...
|
|
// produkt je v kolekci -> [id, main_product, related_product_1], [id, main_product, related_product_2], ...
|
|
|
|
$products = sqlQueryBuilder()->select('id')->fromProducts()->where(Operator::inIntArray($productIds, 'id'));
|
|
|
|
return sqlQueryBuilder()
|
|
->select('DISTINCT p.id,
|
|
COALESCE(pc_main.id_product, pc_related.id_product) AS main_product,
|
|
COALESCE(pc_main.id_product_related, pc_related_2.id_product_related) AS related_product')
|
|
->from("({$products->getSQL()})", 'p')->addParameters($products->getParameters(), $products->getParameterTypes())
|
|
->leftJoin('p', 'products_collections', 'pc_main', 'pc_main.id_product = p.id')
|
|
->leftJoin('p', 'products_collections', 'pc_related', 'pc_related.id_product_related = p.id')
|
|
->leftJoin('p', 'products_collections', 'pc_related_2', 'pc_related_2.id_product = pc_related.id_product');
|
|
}
|
|
|
|
public static function isVisible()
|
|
{
|
|
return Filter::byFigure('Y');
|
|
}
|
|
|
|
public static function inSection($sectionId)
|
|
{
|
|
return self::inSections([$sectionId]);
|
|
}
|
|
|
|
public static function inSections(array $sectionIds, $alias = 'ps')
|
|
{
|
|
return function (QueryBuilder $qb) use ($sectionIds) {
|
|
$sectionIds = array_merge($sectionIds, [-1000]); // nonsense id, so there are always at least two ids - hack for better queryplan
|
|
|
|
$existsSubquery = sqlQueryBuilder()->select('id_product')
|
|
->from('products_in_sections')
|
|
->where('id_product = p.id')
|
|
->andWhere(Operator::inIntArray($sectionIds, 'id_section'));
|
|
|
|
return Operator::exists(
|
|
$existsSubquery
|
|
->addParameters($existsSubquery->getParameters(), $existsSubquery->getParameterTypes())
|
|
);
|
|
};
|
|
}
|
|
|
|
public static function inSectionsRecursive(array $sectionIds)
|
|
{
|
|
return self::inSections(
|
|
call_user_func_array('array_merge', array_map('getDescendantCategories', $sectionIds))
|
|
);
|
|
}
|
|
|
|
public static function inSectionsByLevel(array $ancestorAndLevel, array $excludeOptions = [])
|
|
{
|
|
if (isset($excludeOptions['ancestorID']) && isset($excludeOptions['level'])) {
|
|
unset($ancestorAndLevel[$excludeOptions['ancestorID']][$excludeOptions['level']]);
|
|
}
|
|
|
|
$sections = [];
|
|
foreach ($ancestorAndLevel as $ancestorID => $data) {
|
|
foreach ($data as $level => $rawSectionIDs) {
|
|
$sections[] = self::inSections(
|
|
call_user_func_array('array_merge', array_map('getDescendantCategories', $rawSectionIDs)),
|
|
'ps_'.$ancestorID.'_'.$level
|
|
);
|
|
}
|
|
}
|
|
|
|
return Operator::andX($sections);
|
|
}
|
|
|
|
public static function inStore($useVariations = false)
|
|
{
|
|
return function (QueryBuilder $qb) use ($useVariations) {
|
|
if ($useVariations) {
|
|
$qb->joinVariationsOnProducts();
|
|
$inStore = $qb->expr()->gt(static::getInStoreField(true, $qb), 0);
|
|
} else {
|
|
$inStore = $qb->expr()->gt(static::getInStoreField(false, $qb), 0);
|
|
}
|
|
|
|
return $inStore;
|
|
};
|
|
}
|
|
|
|
public static function inStoreVariations()
|
|
{
|
|
return function (QueryBuilder $qb) {
|
|
$qb->joinVariationsOnProducts();
|
|
|
|
// $qb->andWhere('pv.id IS NOT NULL AND pv.in_store > 0');
|
|
$inStore = $qb->expr()->gt('COALESCE(pv.in_store, 1)', 0);
|
|
|
|
return $inStore;
|
|
};
|
|
}
|
|
|
|
public static function inStoreSupplier($useVariations = false)
|
|
{
|
|
if (!findModule(\Modules::PRODUCTS_SUPPLIERS)) {
|
|
return null;
|
|
}
|
|
|
|
return function (QueryBuilder $qb) use ($useVariations) {
|
|
$suppliers = sqlQueryBuilder()->select('1')
|
|
->from('products_of_suppliers', 'pf_pos')
|
|
->andWhere('p.id = pf_pos.id_product')
|
|
->andWhere('pf_pos.in_store > 0');
|
|
if ($useVariations) {
|
|
$qb->joinVariationsOnProducts();
|
|
$suppliers->andWhere('(pv.id IS NULL and pf_pos.id_variation IS NULL) OR (pv.id=pf_pos.id_variation)');
|
|
}
|
|
|
|
return Operator::exists($suppliers);
|
|
};
|
|
}
|
|
|
|
public static function inStoreSeller(): callable
|
|
{
|
|
if (!findModule(\Modules::SELLERS) || !($storeId = Contexts::get(SellerContext::class)->getActive()['id_store'] ?? null)) {
|
|
return self::inStore();
|
|
}
|
|
|
|
return Filter::byStores([$storeId]);
|
|
}
|
|
|
|
public static function withOneProductPhotoId($variationsAsResult = false, $imageKind = null, bool $fallbackToProductPhoto = true)
|
|
{
|
|
return function (QueryBuilder $qb) use ($variationsAsResult, $imageKind, $fallbackToProductPhoto) {
|
|
$photoQb = sqlQueryBuilder()
|
|
->from('products', 'p2')
|
|
->leftJoin('p2', 'products_variations', 'pv2', 'pv2.id_product = p2.id')
|
|
->where('p2.id = p.id AND ((pv.id IS NULL) OR pv.id = pv2.id)')
|
|
->addSelect(self::withProductPhotoId($variationsAsResult, $imageKind, $fallbackToProductPhoto, 'p2', 'pv2', false))
|
|
->setMaxResults(1);
|
|
$qb->addQueryBuilderParameters($photoQb);
|
|
|
|
return '('.$photoQb->getSQL().') as id_photo';
|
|
};
|
|
}
|
|
|
|
public static function withProductPhotoId($variationsAsResult = false, $imageKind = null, bool $fallbackToProductPhoto = true, $aP = 'p', $aPV = 'pv', $selectAdditional = true)
|
|
{
|
|
return function (QueryBuilder $qb) use ($selectAdditional, $variationsAsResult, $imageKind, $fallbackToProductPhoto, $aP, $aPV) {
|
|
$addSelects = [];
|
|
if ($selectAdditional) {
|
|
$addSelects = [['date_update', 'id_photo_update']];
|
|
}
|
|
$joinCondition = "{$aP}.id=ppr.id_product";
|
|
if (findModule(\Modules::PRODUCTS_VARIATIONS_PHOTOS)) {
|
|
if (is_null($imageKind)) {
|
|
$imageKind = $variationsAsResult ? 'N' : 'Y';
|
|
}
|
|
if ($variationsAsResult) {
|
|
// use variation photo if present
|
|
// fallback to product's main photo in case product has no variation
|
|
$joinCondition .= " AND (ppr.id_variation = {$aPV}.id OR ({$aPV}.id IS NULL AND ppr.show_in_lead='Y'))";
|
|
} else {
|
|
$joinCondition .= " AND ppr.show_in_lead='{$imageKind}'";
|
|
}
|
|
if ($fallbackToProductPhoto) {
|
|
// fallback to product photo when variation exists, but has no photo
|
|
$qb->leftJoin($aP, 'photos_products_relation', 'ppr2', "{$aP}.id=ppr2.id_product AND ppr2.show_in_lead='Y'");
|
|
$qb->leftJoin('ppr2', 'photos', 'ph2', 'ph2.id = ppr2.id_photo');
|
|
$aliasesToSelect = ['ph', 'ph2'];
|
|
} else {
|
|
$aliasesToSelect = ['ph'];
|
|
}
|
|
} else {
|
|
$joinCondition .= " AND ppr.show_in_lead='Y'";
|
|
$aliasesToSelect = ['ph'];
|
|
}
|
|
|
|
$qb->leftJoin($aP, 'photos_products_relation', 'ppr', $joinCondition); // + ppr.id_variation
|
|
$qb->leftJoin('ppr', 'photos', 'ph', 'ph.id = ppr.id_photo');
|
|
$qb->setForceIndexForJoin('ph', 'PRIMARY');
|
|
|
|
$createSelect = function ($field, $alias = null) use ($aliasesToSelect) {
|
|
return 'COALESCE('.implode(
|
|
',',
|
|
array_map(
|
|
function ($val) use ($field) {
|
|
return $val.".{$field}";
|
|
},
|
|
$aliasesToSelect
|
|
)
|
|
).') as '.($alias ?? $field);
|
|
};
|
|
|
|
$selects = $createSelect('id', 'id_photo');
|
|
foreach ($addSelects as $aS) {
|
|
$selects .= ', '.$createSelect($aS[0], $aS[1] ?? null);
|
|
}
|
|
|
|
if ($selectAdditional) {
|
|
$qb->andWhere(Translation::coalesceTranslatedFields(PhotosTranslation::class, ['descr' => 'descr_photo']));
|
|
}
|
|
|
|
return $selects;
|
|
};
|
|
}
|
|
|
|
public static function withVatAndDiscount(QueryBuilder $qb, $priceField = 'p.price')
|
|
{
|
|
$qb->joinVariationsOnProducts();
|
|
|
|
$discountField = 'p.discount';
|
|
|
|
if (findModule(\Modules::PRICELISTS)) {
|
|
$pricelistContext = ServiceContainer::getService(PricelistContext::class);
|
|
if ($pricelistContext->getActiveId()) {
|
|
$qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelist($pricelistContext->getActiveId()))
|
|
->leftJoin('prl', 'currencies', 'c', 'c.id=prl.currency');
|
|
|
|
$priceField = 'COALESCE(prlv.price*c.rate, pv.price, prlp.price*c.rate, p.price)';
|
|
$discountField = 'COALESCE(IF(prlv.price IS NULL, NULL, prlp.discount), IF(pv.price IS NULL, NULL, p.discount), IF(prlp.price IS NULL, NULL, prlp.discount), IF(p.price IS NULL, NULL, p.discount))';
|
|
}
|
|
}
|
|
|
|
return "{$priceField} * (1+v.vat/100) * (1-{$discountField}/100)";
|
|
}
|
|
|
|
public static function productsAndVariationsIds(array $items): ?callable
|
|
{
|
|
$specs = [];
|
|
foreach ($items as $productId => $variationId) {
|
|
if (is_array($variationId)) {
|
|
foreach ($variationId as $varId) {
|
|
$specs[] = Operator::equalsNullable(['p.id' => $productId, 'pv.id' => $varId]);
|
|
}
|
|
} elseif (is_null($variationId)) {
|
|
$specs[] = Operator::equalsNullable(['p.id' => $productId]);
|
|
} else {
|
|
$specs[] = Operator::equalsNullable(['p.id' => $productId, 'pv.id' => $variationId]);
|
|
}
|
|
}
|
|
|
|
if (!$specs) {
|
|
return Operator::andX('1 = 0');
|
|
}
|
|
|
|
return Operator::orX($specs);
|
|
}
|
|
|
|
public static function productsIds($products)
|
|
{
|
|
return function (QueryBuilder $qb) use ($products) {
|
|
$qb->setParameter('products', $products, Connection::PARAM_INT_ARRAY)
|
|
->andWhere(Operator::inIntArray($products, 'p.id'))
|
|
->addOrderBy('FIELD(p.id, :products)');
|
|
};
|
|
}
|
|
|
|
public static function getProductInStock(QueryBuilder $qb)
|
|
{
|
|
$query = static::getInStoreField(true, $qb);
|
|
|
|
if (findModule(\Modules::PRODUCTS_SUPPLIERS) && in_array(\Settings::getDefault()->order_availability, [\Settings::ORDER_AVAILABILITY_IN_STORE_OR_SUPPLIER_STORE, \Settings::ORDER_AVAILABILITY_ALL])) {
|
|
// in stock = in_store (pokud je > 0) + skladem u dodavatele (pokud je > 0)
|
|
// aby nedochazelo k dvojitemu odecitani skladovosti (sklad + pos)
|
|
if (!findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_ALLOW_NEGATIVE_IN_STORE)) {
|
|
$query = "GREATEST(0, {$query})";
|
|
}
|
|
$query .= ' + (SELECT GREATEST(0, COALESCE(SUM(pos.in_store), 0)) FROM products_of_suppliers pos WHERE p.id = pos.id_product AND ((pv.id IS NULL and pos.id_variation IS NULL) OR (pv.id=pos.id_variation)))';
|
|
}
|
|
|
|
if (findModule('products', 'showMax')) {
|
|
$cfg = Config::get();
|
|
$query = 'LEAST('.$query.', COALESCE(pv.in_store_show_max, p.in_store_show_max, '.$cfg['Modules']['products']['showMax'].'))';
|
|
}
|
|
|
|
// aby se na FE nedostala zaporna skladovost, minimum je 0
|
|
if (findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_ALLOW_NEGATIVE_IN_STORE)) {
|
|
$query = "GREATEST(0, {$query})";
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
public static function getInStoreField($useVariations = true, $qb = null)
|
|
{
|
|
$field = 'in_store';
|
|
if ($callable = findModule('products', 'in_store_field')) {
|
|
if (is_callable($callable)) {
|
|
$field = call_user_func($callable, $useVariations, $qb);
|
|
}
|
|
}
|
|
|
|
if ($callable = findModule('products', 'in_store_spec')) {
|
|
if (is_callable($callable)) {
|
|
return call_user_func($callable, $useVariations, $qb);
|
|
}
|
|
}
|
|
|
|
return static::applyProductReservationsOnInStoreField(
|
|
$useVariations ? "COALESCE(pv.{$field}, p.{$field})" : 'p.'.$field,
|
|
$useVariations
|
|
);
|
|
}
|
|
|
|
public static function getStoresQuantityField(QueryBuilder $qb): string
|
|
{
|
|
if ($callable = findModule('products', 'stores_quantity_spec')) {
|
|
if (is_callable($callable)) {
|
|
return call_user_func($callable, $qb);
|
|
}
|
|
}
|
|
|
|
return 'SUM(si.quantity)';
|
|
}
|
|
|
|
public static function getDiscountField(&$spec): string
|
|
{
|
|
$spec = null;
|
|
$discountField = 'p.discount';
|
|
if (findModule(\Modules::PRICE_HISTORY)) {
|
|
$price = 'p.price';
|
|
$price_for_discount = 'p.price_for_discount';
|
|
$price_common = 'p.price_common';
|
|
if (findModule(\Modules::PRODUCTS_VARIATIONS)) {
|
|
$spec = function (QueryBuilder $qb) {
|
|
$qb->joinVariationsOnProducts();
|
|
};
|
|
$price = 'COALESCE(pv.price, p.price)';
|
|
$price_for_discount = 'COALESCE(pv.price_for_discount, p.price_for_discount)';
|
|
$price_common = 'COALESCE(pv.price_common, p.price_common)';
|
|
}
|
|
if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_COMMON)) {
|
|
// pokud je price_for_discount deaktivovana, vypocitat slevu z price_common
|
|
$price_for_discount = "IF({$price_for_discount} = -1, {$price_common}, {$price_for_discount})";
|
|
}
|
|
$discountField = "IF({$price_for_discount} > 0, (100 - {$price}*(100-p.discount)/{$price_for_discount}), p.discount)";
|
|
$display_discount = \Settings::getDefault()['prod_display_discount'] ?? null;
|
|
if ($display_discount == 'Y') {
|
|
$discountField = "IF(p.discount > 0, {$discountField}, 0)";
|
|
}
|
|
$display_discount = \Settings::getDefault()['prod_display_discount_price_common'] ?? null;
|
|
if ($display_discount == 'Y') {
|
|
$discountField = "IF({$price_common} > 0, {$discountField}, 0)";
|
|
}
|
|
}
|
|
|
|
return $discountField;
|
|
}
|
|
|
|
public static function withVat()
|
|
{
|
|
$vatContext = Contexts::get(VatContext::class);
|
|
|
|
if ($vatContext->isOssActive()) {
|
|
return function (QueryBuilder $qb) {
|
|
$defaultVat = Contexts::get(VatContext::class)->getDefault();
|
|
$qb->joinVatsOnProducts()
|
|
->addSelect('COALESCE(v.id, :default_vat) vat, p.vat AS original_vat')
|
|
->setParameter('default_vat', $defaultVat['id']);
|
|
};
|
|
} else {
|
|
return function (QueryBuilder $qb) {
|
|
$qb->addSelect('p.vat');
|
|
};
|
|
}
|
|
}
|
|
|
|
public static function withVats($countries)
|
|
{
|
|
return function (QueryBuilder $qb) use ($countries) {
|
|
$defaultCn = \Settings::getDefault()['oss_vats']['default'] ?? null;
|
|
foreach ($countries as $country) {
|
|
$qbVat = sqlQueryBuilder()->select('vv.vat')->from('vats', 'vv')
|
|
->leftJoin('vv', 'vats_cns', 'vatcns', 'vv.id = vatcns.id_vat')
|
|
->andWhere('id_cn = COALESCE(p.id_cn, :defaultCn) AND id_country = :country_'.$country)
|
|
->setParameter('defaultCn', $defaultCn)
|
|
->setParameter('country_'.$country, $country);
|
|
$qb->addSubselect($qbVat, 'vat_'.$country);
|
|
}
|
|
};
|
|
}
|
|
|
|
public static function allowBuyNotInStore()
|
|
{
|
|
// vraci spec s delivery_time, ktere nemaji povolen nakup produktu a variant co nejsou skladem (in_store = 0)
|
|
$deliveryTimeCfg = Config::get()['Products']['DeliveryTimeConfig'] ?? [];
|
|
if (!empty($deliveryTimeCfg)) {
|
|
$notAllowed = array_filter($deliveryTimeCfg, function ($key) {
|
|
if (!array_key_exists('allowBuy', $key)) {
|
|
return false;
|
|
}
|
|
|
|
return !$key['allowBuy'];
|
|
});
|
|
|
|
return Operator::orX(
|
|
Operator::inIntArray(array_keys($notAllowed), 'p.delivery_time'),
|
|
Operator::inIntArray(array_keys($notAllowed), 'pv.delivery_time'));
|
|
}
|
|
|
|
return Operator::andX('0=0');
|
|
}
|
|
|
|
protected static function applyProductReservationsOnInStoreField(string $inStoreField, bool $useVariations): string
|
|
{
|
|
if (!findModule(\Modules::PRODUCT_RESERVATIONS)) {
|
|
return $inStoreField;
|
|
}
|
|
|
|
$subquery = sqlQueryBuilder()
|
|
->select('COALESCE(SUM(pr.quantity), 0)')
|
|
->from('product_reservations', 'pr')
|
|
->andWhere($useVariations ? 'pv.id = pr.id_variation' : 'p.id = pr.id_product');
|
|
|
|
if ($ignoredTypes = Contexts::get(UserContext::class)->getIgnoredReservationTypes()) {
|
|
$subquery->andWhere(
|
|
Operator::not('pr.type IN ('.implode(',', array_map(fn ($x) => '"'.$x.'"', $ignoredTypes)).')')
|
|
);
|
|
}
|
|
|
|
return "({$inStoreField} - ({$subquery->getSQL()}))";
|
|
}
|
|
|
|
public static function generatedCoupons()
|
|
{
|
|
return Operator::equals([JsonOperator::value('p.data', 'generate_coupon') => 'Y']);
|
|
}
|
|
}
|