115 lines
5.2 KiB
PHP
115 lines
5.2 KiB
PHP
<?php
|
|
|
|
namespace KupShop\StoresBundle\Query;
|
|
|
|
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
|
|
use KupShop\StoresBundle\Utils\StoresInStore;
|
|
use Query\Operator;
|
|
use Query\QueryBuilder;
|
|
|
|
class StoresQuery
|
|
{
|
|
public static function addStoresInStoreAmounts($selectedStores = null, bool $addQuantity = true, $addMinQuantity = true): callable
|
|
{
|
|
return function (QueryBuilder $qb) use ($selectedStores, $addQuantity, $addMinQuantity) {
|
|
$stores = ServiceContainer::getService(StoresInStore::class)->getStores();
|
|
|
|
$qb->andWhere('1');
|
|
|
|
// quantity decrement - vyselectuje skladovost externich skladu, aby se mohla odecist od celkoveho mnozstvi skladem
|
|
$quantityDecrementQb = static::getQuantityDecrementQueryBuilder();
|
|
|
|
$qb->addQueryBuilderParameters($quantityDecrementQb);
|
|
|
|
foreach ($stores as $key => $store) {
|
|
if ($selectedStores && !in_array($key, $selectedStores)) {
|
|
continue;
|
|
}
|
|
|
|
$table_alias = 'si'.$key;
|
|
|
|
// prepare base sub query
|
|
$subQuery = sqlQueryBuilder()
|
|
// temporary fix of mariadb bug - use index
|
|
->from('stores_items', $table_alias.' USE INDEX (stores_items_products_id_fk)')
|
|
->where("{$table_alias}.id_product = p.id AND {$table_alias}.id_variation <=> pv.id AND {$table_alias}.id_store = {$key}");
|
|
|
|
// add sub query select
|
|
/*
|
|
* Na detailu produktu se ovečkárně přestala zobrazovat dostupnost pouze na prodejně.
|
|
* Při příchodu na detail produktu není vybrána varianta a ve StoresQuery addStoresInStoreAmounts se nezobrazuje celkový součet napříč všech variant.
|
|
* Nevěděli jsme, proč bylo: $qb->addSubselect($subQuery->select("SUM(COALESCE({$table_alias}.quantity, 0))"), "store{$key}_in_store");
|
|
*/
|
|
if ($addQuantity) {
|
|
$qb->addSubselect($subQuery->select("COALESCE(SUM({$table_alias}.quantity), 0)"), "store{$key}_in_store");
|
|
}
|
|
|
|
if ($addMinQuantity) {
|
|
$qb->addSubselect($subQuery->select("COALESCE({$table_alias}.min_quantity, 0)"), "store{$key}_min_in_store");
|
|
}
|
|
}
|
|
};
|
|
}
|
|
|
|
public static function filterByStoresInStore(array $storeIds): callable
|
|
{
|
|
return function (QueryBuilder $qb) use ($storeIds) {
|
|
$qb->joinVariationsOnProducts();
|
|
|
|
$filterSpecs = [];
|
|
|
|
foreach ($storeIds as $storeId) {
|
|
$existsSubQuery = sqlQueryBuilder()
|
|
->select('si_filter.id')
|
|
->from('stores_items', 'si_filter')
|
|
->andWhere(
|
|
Operator::andX(
|
|
'si_filter.id_product = p.id AND pv.id <=> si_filter.id_variation',
|
|
Operator::equals(['si_filter.id_store' => $storeId]),
|
|
'si_filter.quantity > 0'
|
|
)
|
|
);
|
|
|
|
$filterSpecs[] = Operator::exists($existsSubQuery);
|
|
}
|
|
|
|
return Operator::orX($filterSpecs);
|
|
};
|
|
}
|
|
|
|
public static function filterOrdersByStoreDelivery(int $id_store): callable
|
|
{
|
|
return function (QueryBuilder $qb) use ($id_store) {
|
|
$delivery_operator = 'false';
|
|
if ($store = ServiceContainer::getService(StoresInStore::class)->getStores()[$id_store] ?? null) {
|
|
$id_delivery = $store['id_delivery'];
|
|
if ($id_delivery) {
|
|
$id_delivery_types = sqlFetchAll(sqlQueryBuilder()->select('id')->from('delivery_type')
|
|
->andWhere(Operator::equals(['id_delivery' => $id_delivery]))->execute(),
|
|
['id' => 'id']);
|
|
$delivery_operator = Operator::inIntArray($id_delivery_types, 'o.id_delivery');
|
|
} elseif ($store['type'] == StoresInStore::TYPE_MAIN_STORE) {
|
|
$id_delivery = array_filter(array_column(ServiceContainer::getService(StoresInStore::class)->getStores(), 'id_delivery'));
|
|
if ($id_delivery) {
|
|
$id_delivery_types = sqlFetchAll(sqlQueryBuilder()->select('id')->from('delivery_type')
|
|
->andWhere(Operator::inIntArray($id_delivery, 'id_delivery'))->execute(),
|
|
['id' => 'id']);
|
|
$delivery_operator = Operator::not(Operator::inIntArray($id_delivery_types, 'o.id_delivery'));
|
|
}
|
|
}
|
|
}
|
|
|
|
$qb->andWhere($delivery_operator);
|
|
};
|
|
}
|
|
|
|
private static function getQuantityDecrementQueryBuilder(): QueryBuilder
|
|
{
|
|
return sqlQueryBuilder()
|
|
->select('COALESCE(SUM(quantity), 0)')
|
|
->from('stores_items USE INDEX(stores_items_products_id_fk)')
|
|
->where('id_product = p.id AND id_variation <=> pv.id AND id_store IN (SELECT id FROM stores WHERE type != :externalStoreType)')
|
|
->setParameter('externalStoreType', StoresInStore::TYPE_EXTERNAL_STORE);
|
|
}
|
|
}
|