117 lines
4.5 KiB
PHP
117 lines
4.5 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace KupShop\StoresBundle\Utils;
|
|
|
|
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
|
|
use Query\Operator;
|
|
use Query\QueryBuilder;
|
|
|
|
class MainStoreMigrationUtil
|
|
{
|
|
/**
|
|
* @required
|
|
*/
|
|
public StoresInStore $storesInStore;
|
|
|
|
public function migrate($fix = false): void
|
|
{
|
|
$count = sqlQueryBuilder()->select('COUNT(id) count')
|
|
->from('stores_items')
|
|
->where(Operator::equals(['id_store' => 1]))
|
|
->execute()->fetchOne();
|
|
|
|
// Migrace už proběhla
|
|
if ($count > 0) {
|
|
return;
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()->select('*')->fromProducts()->joinVariationsOnProducts()
|
|
->andWhere($this->addStoresInStoreAmounts([1]))
|
|
->addSelect('pv.id variation_id, p.id product_id')
|
|
->andWhere('p.id > 0');
|
|
|
|
foreach ($qb->execute() as $item) {
|
|
sqlQueryBuilder()->insert('stores_items')
|
|
->directValues([
|
|
'id_store' => 1,
|
|
'id_product' => $item['product_id'],
|
|
'id_variation' => $item['variation_id'],
|
|
'quantity' => $item['store1_in_store'],
|
|
'min_quantity' => $item['store1_min_in_store'],
|
|
])
|
|
->execute();
|
|
}
|
|
|
|
if (findModule(\Modules::WAREHOUSE)) {
|
|
ServiceContainer::getService(StoreWarehouseRecalculate::class)->fixStoreWarehouseErrors(false);
|
|
}
|
|
}
|
|
|
|
public function insertOrderStores()
|
|
{
|
|
if (!findModule(\Modules::SELLERS)) {
|
|
sqlQuery('INSERT INTO order_stores SELECT o.id, COALESCE(s.id, 1) id_store FROM orders o
|
|
INNER JOIN delivery_type dt on o.id_delivery = dt.id
|
|
LEFT JOIN stores s ON dt.id_delivery = s.id_delivery
|
|
WHERE o.id IN (
|
|
SELECT id FROM orders o
|
|
LEFT JOIN order_stores os ON o.id = os.id_order
|
|
WHERE id_order IS NULL)');
|
|
} else {
|
|
sqlQuery('INSERT INTO order_stores SELECT o.id, COALESCE(s.id_store, 1) id_store FROM orders o
|
|
LEFT JOIN order_sellers os on o.id = os.id_order
|
|
LEFT JOIN sellers s on os.id_seller = s.id
|
|
WHERE o.id IN (
|
|
SELECT id FROM orders o
|
|
LEFT JOIN order_stores os ON o.id = os.id_order
|
|
WHERE id_order IS NULL)');
|
|
}
|
|
}
|
|
|
|
private function getQuantityDecrementQueryBuilder(): QueryBuilder
|
|
{
|
|
return sqlQueryBuilder()
|
|
->select('COALESCE(SUM(si0.quantity), 0)')
|
|
->from('stores_items', 'si0')
|
|
->where('si0.id_product = p.id AND si0.id_variation <=> pv.id AND si0.id_store IN (SELECT id FROM stores WHERE type != :externalStoreType)')
|
|
->setParameter('externalStoreType', StoresInStore::TYPE_EXTERNAL_STORE);
|
|
}
|
|
|
|
private function addStoresInStoreAmounts($selectedStores = null): callable
|
|
{
|
|
return function (QueryBuilder $qb) use ($selectedStores) {
|
|
$stores = ServiceContainer::getService(StoresInStore::class)->getStores();
|
|
|
|
$qb->andWhere('1');
|
|
|
|
// quantity decrement - vyselectuje skladovost externich skladu, aby se mohla odecist od celkoveho mnozstvi skladem
|
|
$quantityDecrementQb = $this->getQuantityDecrementQueryBuilder();
|
|
|
|
$store1_in_store = "COALESCE(pv.in_store, p.in_store) - ({$quantityDecrementQb->getSQL()})";
|
|
$qb->addQueryBuilderParameters($quantityDecrementQb);
|
|
|
|
foreach ($stores as $key => $store) {
|
|
if ($key == 1 || ($selectedStores && !in_array($key, $selectedStores))) {
|
|
continue;
|
|
}
|
|
|
|
$table_alias = 'si'.$key;
|
|
|
|
// prepare base sub query
|
|
$subQuery = sqlQueryBuilder()
|
|
->from('stores_items', $table_alias)
|
|
->where("{$table_alias}.id_product = p.id AND {$table_alias}.id_variation <=> pv.id AND {$table_alias}.id_store = {$key}");
|
|
|
|
// add sub query select
|
|
$qb->addSubselect($subQuery->select("SUM(COALESCE({$table_alias}.quantity, 0))"), "store{$key}_in_store");
|
|
$qb->addSubselect($subQuery->select("COALESCE({$table_alias}.min_quantity, 0)"), "store{$key}_min_in_store");
|
|
}
|
|
$store1_in_store = 'CAST(('.$store1_in_store.') AS FLOAT) AS store1_in_store';
|
|
$qb->addSelect($store1_in_store);
|
|
$qb->addSelect('0 AS store1_min_in_store');
|
|
};
|
|
}
|
|
}
|