1220 lines
42 KiB
PHP
1220 lines
42 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace External\ZNZBundle\Util;
|
|
|
|
use External\ZNZBundle\MappingType;
|
|
use External\ZNZBundle\Synchronizers\ParameterSynchronizer;
|
|
use External\ZNZBundle\Synchronizers\PriceListSynchronizer;
|
|
use KupShop\AdminBundle\Util\ActivityLog;
|
|
use KupShop\CatalogBundle\Parameters\ParameterFinder;
|
|
use KupShop\I18nBundle\Translations\VariationsValuesTranslation;
|
|
use KupShop\KupShopBundle\Context\LanguageContext;
|
|
use KupShop\KupShopBundle\Context\UserContext;
|
|
use KupShop\KupShopBundle\Util\Contexts;
|
|
use KupShop\KupShopBundle\Util\Functional\Mapping;
|
|
use KupShop\StoresBundle\Utils\StoresInStore;
|
|
use Query\Operator;
|
|
|
|
class ZNZUtil
|
|
{
|
|
public const STORES_CACHE_KEY = 'store_StoreIdsZNZ';
|
|
|
|
private array $znzMaxIdsCache = [];
|
|
|
|
public function __construct(
|
|
private readonly ParameterFinder $parameterFinder,
|
|
private readonly StoresInStore $storesInStore,
|
|
private readonly VariationsValuesTranslation $variationsValuesTranslation,
|
|
private readonly ZNZConfiguration $configuration,
|
|
) {
|
|
}
|
|
|
|
public static function withRetryStrategy(callable $fn, bool $silent = true, int $maxTries = 3): mixed
|
|
{
|
|
$try = 0;
|
|
|
|
do {
|
|
$try++;
|
|
|
|
try {
|
|
return $fn();
|
|
} catch (\Throwable $e) {
|
|
if (!$silent && $try >= $maxTries) {
|
|
throw $e;
|
|
}
|
|
|
|
sleep(1);
|
|
}
|
|
} while ($try < $maxTries);
|
|
|
|
return false;
|
|
}
|
|
|
|
public function withActivityLog(callable $fn, string $activityName): void
|
|
{
|
|
addActivityLog(ActivityLog::SEVERITY_NOTICE, ActivityLog::TYPE_SYNC, "[ZNZ] {$activityName}: spouštím");
|
|
|
|
try {
|
|
$fn();
|
|
} catch (\Throwable $e) {
|
|
addActivityLog(ActivityLog::SEVERITY_ERROR, ActivityLog::TYPE_SYNC, "[ZNZ] {$activityName}: vyskytla se chyba", [
|
|
'error' => $e->getMessage(),
|
|
]);
|
|
|
|
return;
|
|
}
|
|
|
|
addActivityLog(ActivityLog::SEVERITY_NOTICE, ActivityLog::TYPE_SYNC, "[ZNZ] {$activityName}: dokončeno");
|
|
}
|
|
|
|
public function getOrderNumberWithPrefix(\Order $order): string
|
|
{
|
|
if ($prefix = $this->configuration->getOrderPrefix($order->getLanguage())) {
|
|
return $prefix.$order->order_no;
|
|
}
|
|
|
|
return $order->order_no;
|
|
}
|
|
|
|
public function getCurrentWebsite(?string $language = null): string
|
|
{
|
|
$language = $language ?: Contexts::get(LanguageContext::class)->getActiveId();
|
|
|
|
foreach ($this->configuration->getSupportedWebsites() as $website => $cfg) {
|
|
if (is_array($cfg['language']) && in_array($language, $cfg['language'])) {
|
|
return $website;
|
|
}
|
|
|
|
if ($cfg['language'] === $language) {
|
|
return $website;
|
|
}
|
|
}
|
|
|
|
return array_keys($this->configuration->getSupportedWebsites())[0];
|
|
}
|
|
|
|
public function getActiveStoreIds(?string $language = null): array
|
|
{
|
|
$language ??= Contexts::get(LanguageContext::class)->getActiveId();
|
|
|
|
if ($user = Contexts::get(UserContext::class)->getActive()) {
|
|
foreach ($user->getGroups() as $group) {
|
|
if (!empty($group['data']['znz']['stores'])) {
|
|
return array_values($group['data']['znz']['stores']);
|
|
}
|
|
}
|
|
}
|
|
|
|
static $storesByLanguage = [];
|
|
|
|
if ($storesByLanguage[$language] ?? false) {
|
|
return $storesByLanguage[$language];
|
|
}
|
|
|
|
if (!($stores = getCache(self::STORES_CACHE_KEY))) {
|
|
$qb = sqlQueryBuilder()
|
|
->select('id, data')
|
|
->from('stores')
|
|
->where(Operator::equals(['figure' => 'Y']));
|
|
|
|
$stores = [];
|
|
foreach ($qb->execute() as $item) {
|
|
$stores[$item['id']] = json_decode($item['data'] ?: '', true)['languages'] ?? [];
|
|
}
|
|
|
|
setCache(self::STORES_CACHE_KEY, $stores);
|
|
}
|
|
|
|
$result = [];
|
|
foreach ($stores as $storeId => $languages) {
|
|
if (in_array($language, $languages)) {
|
|
$result[] = $storeId;
|
|
}
|
|
}
|
|
|
|
return $storesByLanguage[$language] = $result;
|
|
}
|
|
|
|
public function updateParametersProducers(): void
|
|
{
|
|
sqlQuery('INSERT IGNORE INTO parameters_producers (id_parameter, id_producer)
|
|
SELECT pp.id_parameter, pr.id
|
|
FROM parameters_producers pp, producers pr
|
|
GROUP BY pr.id, pp.id_parameter;');
|
|
}
|
|
|
|
public function generateProductsRelated(?int $productId = null): void
|
|
{
|
|
$parameterId = $this->getParameterByKey('Rada');
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('p.id, GROUP_CONCAT(DISTINCT p2.id) as ids')
|
|
->from('products', 'p')
|
|
->join('p', 'products', 'p2', 'p.producer = p2.producer')
|
|
->join('p', 'parameters_products', 'pp', 'p.id = pp.id_product AND pp.id_parameter = :parameterId')
|
|
->join('p2', 'parameters_products', 'pp2', 'p2.id = pp2.id_product AND pp2.id_parameter = pp.id_parameter AND pp.value_list = pp2.value_list')
|
|
->andWhere('p2.id NOT IN ((SELECT pc.id_product AS id FROM products_collections pc WHERE pc.id_product_related = p.id OR pc.id_product = p.id
|
|
UNION
|
|
SELECT pc.id_product_related AS id FROM products_collections pc
|
|
LEFT JOIN products_collections pc_main ON pc_main.id_product = pc.id_product
|
|
WHERE pc_main.id_product = p.id OR pc_main.id_product_related = p.id))')
|
|
->setParameter('parameterId', $parameterId)
|
|
->groupBy('p.id, CONCAT(p.producer, "-", pp.value_list)');
|
|
|
|
if ($productId) {
|
|
$qb->andWhere(Operator::equals(['p.id' => $productId]));
|
|
}
|
|
|
|
// ID typu souvisejiciho zbozi
|
|
$serieRelatedType = $this->configuration->getSerieRelatedTypeId();
|
|
|
|
foreach ($qb->execute() as $item) {
|
|
$topProductId = $item['id'];
|
|
|
|
// rozparsuju vsechny idcka, ktere se maji nachazet v souvisejicim
|
|
$ids = array_unique(array_map(fn ($x) => (int) $x, explode(',', $item['ids'])));
|
|
$ids = array_filter($ids, fn ($x) => $topProductId != $x);
|
|
|
|
$current = sqlQueryBuilder()
|
|
->select('id_rel_product')
|
|
->from('products_related')
|
|
->where(Operator::equals(['id_top_product' => $topProductId]))
|
|
->orderBy('position')
|
|
->execute()->fetchFirstColumn();
|
|
|
|
// avoid unnecessary delete/insert to decrease binlog size
|
|
if (array_values($current) === array_values($ids)) {
|
|
continue;
|
|
}
|
|
|
|
// provedu smazani souvisejiciho zbozi od konkretniho produktu
|
|
sqlQueryBuilder()
|
|
->delete('products_related')
|
|
->where(Operator::equals(['id_top_product' => $topProductId]))
|
|
->execute();
|
|
|
|
$insertQb = sqlQueryBuilder()
|
|
->insert('products_related');
|
|
|
|
foreach ($ids as $position => $relatedProductId) {
|
|
$insertQb->multiDirectValues(
|
|
[
|
|
'id_top_product' => $topProductId,
|
|
'id_rel_product' => $relatedProductId,
|
|
'position' => $position,
|
|
'type' => $serieRelatedType,
|
|
]
|
|
);
|
|
}
|
|
|
|
if (!empty($ids)) {
|
|
static::withRetryStrategy(fn () => $insertQb->execute());
|
|
}
|
|
}
|
|
}
|
|
|
|
/** Nastaví cenu produktu podle nejnižší ceny u variant */
|
|
public function updateProductPrices(): void
|
|
{
|
|
// update product prices
|
|
sqlQuery('UPDATE products p
|
|
JOIN products_variations pv ON p.id = pv.id_product
|
|
SET p.price = COALESCE((SELECT MIN(pv2.price)
|
|
FROM products_variations pv2
|
|
WHERE pv2.id_product = p.id AND pv2.price > 0 AND pv2.in_store > 0 AND pv2.figure = "Y"), p.price);');
|
|
|
|
if ($this->configuration->isB2BMode()) {
|
|
sqlQuery('INSERT INTO pricelists_products (id_pricelist, id_product, price)
|
|
SELECT
|
|
id_pricelist, id_product, MIN(price) as price
|
|
FROM pricelists_products
|
|
WHERE id_variation IS NOT NULL AND price > 0
|
|
GROUP BY id_pricelist, id_product
|
|
ON DUPLICATE KEY UPDATE price = VALUES(price);');
|
|
}
|
|
}
|
|
|
|
public function generateProductCommonPrices(): void
|
|
{
|
|
// aktualizace price_common pro produkty
|
|
sqlQueryBuilder()
|
|
->update('products', 'p')
|
|
->join('p', 'znz_products', 'zp', 'zp.id_product=p.id AND zp.id_variation IS NULL')
|
|
->join('zp', 'znz_products', 'zp2', 'zp2.id_znz = JSON_VALUE(zp.data, "$.discountProductId")')
|
|
->join('zp2', 'products', 'p2', 'p2.id = zp2.id_product')
|
|
->join('p2', 'vats', 'v', 'v.id = p2.vat')
|
|
->leftJoin('zp2', 'products_variations', 'pv2', 'pv2.id = zp2.id_variation')
|
|
->set('p.price_common', '(COALESCE(pv2.price, p2.price) * ((v.vat + 100) / 100))')
|
|
->where('JSON_VALUE(zp.data, "$.discountProductId") IS NOT NULL')
|
|
->execute();
|
|
|
|
// aktualizace price_common pro varianty
|
|
sqlQueryBuilder()
|
|
->update('products_variations', 'pv')
|
|
->join('pv', 'znz_products', 'zp', 'pv.id_product = zp.id_product AND pv.id = zp.id_variation')
|
|
->join('zp', 'znz_products', 'zp2', 'zp2.id_znz = JSON_VALUE(zp.data, "$.discountProductId")')
|
|
->join('zp2', 'products', 'p2', 'p2.id = zp2.id_product')
|
|
->join('p2', 'vats', 'v', 'v.id = p2.vat')
|
|
->leftJoin('zp2', 'products_variations', 'pv2', 'pv2.id = zp2.id_variation')
|
|
->set('pv.price_common', '(COALESCE(pv2.price, p2.price) * ((v.vat + 100) / 100))')
|
|
->where('JSON_VALUE(zp.data, "$.discountProductId") IS NOT NULL')
|
|
->execute();
|
|
}
|
|
|
|
public function updateProductsPositions(): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->update('products', 'p')
|
|
->join('p', 'parameters_products', 'pp', 'p.id = pp.id_product AND id_parameter = (SELECT id_parameter FROM znz_parameters WHERE id_znz = \'Baleni\')')
|
|
->join('pp', 'parameters_list', 'pl', ' pl.id = pp.value_list')
|
|
->set('p.position', 10000)
|
|
->where(Operator::equals(['pl.value' => 'Vzorek']))
|
|
->execute();
|
|
}
|
|
|
|
/**
|
|
* Aktualizace viditelnosti produktu.
|
|
*/
|
|
public function updateProductsVisibility(): void
|
|
{
|
|
// zobrazim produkty, ktere maji cenu a maji byt videt
|
|
sqlQueryBuilder()
|
|
->update('products', 'p')
|
|
->leftJoin('p', 'znz_products', 'zp', 'p.id = zp.id_product AND zp.id_variation IS NULL')
|
|
->leftJoin('zp', 'znz_products_website', 'zpw', 'zpw.id_znz = zp.id_znz')
|
|
->set('p.figure', ':figure')
|
|
->andWhere('p.price > 0 AND ((p.figure = "N" AND JSON_VALUE(zp.data, "$.znzFigure") = "Y" AND zpw.id_znz IS NOT NULL) OR zp.id_znz IS NULL)')
|
|
->setParameter('figure', 'Y')
|
|
->execute();
|
|
|
|
// zobrazim varianty, ktere maji cenu a maji byt videt
|
|
sqlQueryBuilder()
|
|
->update('products_variations', 'pv')
|
|
->leftJoin('pv', 'znz_products', 'zp', 'pv.id_product = zp.id_product AND pv.id = zp.id_variation')
|
|
->leftJoin('zp', 'znz_products_website', 'zpw', 'zpw.id_znz = zp.id_znz')
|
|
->set('pv.figure', ':figure')
|
|
->andWhere('pv.price > 0 AND ((pv.figure = "N" AND JSON_VALUE(zp.data, "$.znzFigure") = "Y" AND zpw.id_znz IS NOT NULL) OR zp.id_znz IS NULL)')
|
|
->setParameter('figure', 'Y')
|
|
->execute();
|
|
|
|
// skryju produkty, ktere maji nulovou cenu
|
|
sqlQueryBuilder()
|
|
->update('products', 'p')
|
|
->leftJoin('p', 'products_variations', 'pv', 'p.id = pv.id_product')
|
|
->set('p.figure', ':figure')
|
|
->andWhere(Operator::equals(['p.figure' => 'Y']))
|
|
->andWhere('COALESCE(pv.price, p.price) <= 0')
|
|
->setParameter('figure', 'N')
|
|
->execute();
|
|
|
|
// skryju varianty, ktere maji nulovou cenu
|
|
sqlQueryBuilder()
|
|
->update('products_variations', 'pv')
|
|
->join('pv', 'products', 'p', 'p.id = pv.id_product')
|
|
->set('pv.figure', ':figure')
|
|
->andWhere(Operator::equals(['pv.figure' => 'Y']))
|
|
->andWhere('COALESCE(pv.price, p.price) <= 0')
|
|
->setParameter('figure', 'N')
|
|
->execute();
|
|
}
|
|
|
|
public function getUserNameParts(string $name): array
|
|
{
|
|
$userName = explode(' ', $name);
|
|
$name = array_shift($userName);
|
|
$surname = implode(' ', $userName);
|
|
|
|
return [
|
|
'name' => trim($name),
|
|
'surname' => trim($surname),
|
|
];
|
|
}
|
|
|
|
public function getProductVat(int $productId): \Decimal
|
|
{
|
|
static $productVats = [];
|
|
|
|
if (!($productVats[$productId] ?? false)) {
|
|
$vat = sqlQueryBuilder()
|
|
->select('v.vat')
|
|
->from('products', 'p')
|
|
->join('p', 'vats', 'v', 'v.id = p.vat')
|
|
->where(Operator::equals(['p.id' => $productId]))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
if ($vat === false) {
|
|
$vat = getVat();
|
|
}
|
|
|
|
$productVats[$productId] = toDecimal((float) $vat);
|
|
}
|
|
|
|
return $productVats[$productId];
|
|
}
|
|
|
|
public function getProductMapping($znzId): array
|
|
{
|
|
$item = sqlQueryBuilder()
|
|
->select('id_product, id_variation')
|
|
->from('znz_products')
|
|
->where(Operator::equals(['id_znz' => $znzId]))
|
|
// posilam na master, protoze kdyz nacitam mapping, tak to potrebuju mit vzdycky aktualni
|
|
->sendToMaster()
|
|
->execute()->fetchAssociative();
|
|
|
|
if ($item) {
|
|
return [(int) $item['id_product'], !empty($item['id_variation']) ? (int) $item['id_variation'] : null];
|
|
}
|
|
|
|
return [null, null];
|
|
}
|
|
|
|
public function setProductMappingData($znzId, array $data): void
|
|
{
|
|
$qb = sqlQueryBuilder()
|
|
->update('znz_products')
|
|
->where(Operator::equals(['id_znz' => $znzId]));
|
|
|
|
foreach ($data as $key => $value) {
|
|
$valueKey = 'value_'.$key;
|
|
$qb->set('data', 'JSON_SET(COALESCE(data, "{}"), "$.'.$key.'", :'.$valueKey.')')
|
|
->setParameter($valueKey, $value);
|
|
}
|
|
|
|
$qb->execute();
|
|
}
|
|
|
|
public function getProductMappingData(int $znzId, string $key): mixed
|
|
{
|
|
$result = sqlQueryBuilder()
|
|
->select('JSON_VALUE(data, "$.'.$key.'")')
|
|
->from('znz_products')
|
|
->where(Operator::equals(['id_znz' => $znzId]))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
if ($result) {
|
|
return $result;
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public function createProductMapping($znzId, int $productId, ?int $variationId): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->insert('znz_products')
|
|
->directValues(
|
|
[
|
|
'id_znz' => $znzId,
|
|
'id_product' => $productId,
|
|
'id_variation' => $variationId,
|
|
]
|
|
)
|
|
->execute();
|
|
}
|
|
|
|
public function getParameterValueMapping(int $parameterId, $znzId): ?int
|
|
{
|
|
$id = sqlQueryBuilder()
|
|
->select('id_parameters_value')
|
|
->from('znz_parameters_values')
|
|
->where(Operator::equals(['id_znz' => $znzId, 'id_parameter' => $parameterId]))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
if (!$id) {
|
|
return null;
|
|
}
|
|
|
|
return (int) $id;
|
|
}
|
|
|
|
public function getLabelValueMapping(int $labelId, mixed $znzId): ?int
|
|
{
|
|
$id = sqlQueryBuilder()
|
|
->select('id_label_value')
|
|
->from('znz_label_values')
|
|
->where(Operator::equals(['id_znz' => $znzId, 'id_label' => $labelId]))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
if (!$id) {
|
|
return null;
|
|
}
|
|
|
|
return (int) $id;
|
|
}
|
|
|
|
public function createParameterValueMapping(int $parameterId, int $parameterValueId, $znzId): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->insert('znz_parameters_values')
|
|
->directValues(
|
|
[
|
|
'id_znz' => $znzId,
|
|
'id_parameter' => $parameterId,
|
|
'id_parameters_value' => $parameterValueId,
|
|
]
|
|
)
|
|
->execute();
|
|
}
|
|
|
|
public function getUserMapping($znzId): ?array
|
|
{
|
|
$data = sqlQueryBuilder()
|
|
->select('*')
|
|
->from('znz_users')
|
|
->where(Operator::equals(['id_znz' => $znzId]))
|
|
->execute()->fetchAssociative();
|
|
|
|
if (!$data) {
|
|
return null;
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
public function getZNZId(string $type, int $id)
|
|
{
|
|
$id = sqlQueryBuilder()
|
|
->select('id_znz')
|
|
->from('znz_'.$type.'s')
|
|
->where(Operator::equals(['id_'.$type => $id]))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
if (!$id) {
|
|
return null;
|
|
}
|
|
|
|
return $id;
|
|
}
|
|
|
|
public function getMapping(string $type, mixed $znzId): ?int
|
|
{
|
|
$id = sqlQueryBuilder()
|
|
->select('id_'.$type)
|
|
->from('znz_'.$type.'s')
|
|
->where(Operator::equals(['id_znz' => $znzId]))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
if (!$id) {
|
|
return null;
|
|
}
|
|
|
|
return (int) $id;
|
|
}
|
|
|
|
public function createMapping(string $type, $znzId, int $id): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->insert('znz_'.$type.'s')
|
|
->directValues(
|
|
[
|
|
'id_znz' => $znzId,
|
|
'id_'.$type => $id,
|
|
]
|
|
)
|
|
->execute();
|
|
}
|
|
|
|
public function getProductByCode(string $code): array
|
|
{
|
|
$productId = sqlQueryBuilder()
|
|
->select('id')
|
|
->from('products')
|
|
->where(Operator::equals(['code' => $code]))
|
|
->execute()->fetchOne();
|
|
|
|
if ($productId) {
|
|
return [(int) $productId, null];
|
|
}
|
|
|
|
$variation = sqlQueryBuilder()
|
|
->select('id, id_product')
|
|
->from('products_variations')
|
|
->where(Operator::equals(['code' => $code]))
|
|
->execute()->fetchAssociative();
|
|
|
|
if ($variation) {
|
|
return [(int) $variation['id_product'], (int) $variation['id']];
|
|
}
|
|
|
|
return [null, null];
|
|
}
|
|
|
|
public function getPriceListByZNZId(mixed $znzId): ?int
|
|
{
|
|
static $priceListCache = [];
|
|
|
|
if (!array_key_exists($znzId, $priceListCache)) {
|
|
$priceListCache[$znzId] = $this->getMapping(PriceListSynchronizer::getType(), $znzId);
|
|
}
|
|
|
|
return $priceListCache[$znzId];
|
|
}
|
|
|
|
public function getParameterValueByZNZId(int $id, ?int $parameterId): ?string
|
|
{
|
|
if ($parameterId) {
|
|
$parameterValueId = $this->getParameterValueMapping($parameterId, $id);
|
|
} else {
|
|
$parameterValueId = $this->getMapping(ParameterSynchronizer::$typeValues, $id);
|
|
}
|
|
|
|
// kouknu do parametru, ze tam ta hodnota je, abych ji mel odkud vzit
|
|
if (!$parameterValueId) {
|
|
return null;
|
|
}
|
|
|
|
// nactu si hodnotu parametru
|
|
return (string) sqlQueryBuilder()
|
|
->select('value')
|
|
->from('parameters_list')
|
|
->where(Operator::equals(['id' => $parameterValueId]))
|
|
->execute()->fetchOne();
|
|
}
|
|
|
|
/**
|
|
* Aktualizuje hodnoty jmenovky varianty podle parametru. Protoze pro tvoreni hodnoty pouzivame parametrovy
|
|
* ciselnik, takze je potreba pri aktualizaci parametru provadet i aktualizaci hodnoty jmenovky.
|
|
*/
|
|
public function updateLabelValueByParameterValue(int $labelValueId, int $parameterValueId, ?string $language = null): void
|
|
{
|
|
$value = sqlQueryBuilder()
|
|
->select('value')
|
|
->from('parameters_list')
|
|
->where(Operator::equals(['id' => $parameterValueId]))
|
|
->execute()->fetchOne();
|
|
|
|
if (!$value) {
|
|
return;
|
|
}
|
|
|
|
// aktualizuju hodnoty jmenovky varianty podle hodnoty z parametru
|
|
sqlQueryBuilder()
|
|
->update('products_variations_choices_values')
|
|
->directValues(
|
|
[
|
|
'value' => $value,
|
|
]
|
|
)
|
|
->where(Operator::equals(['id' => $labelValueId]))
|
|
->execute();
|
|
|
|
// pokud updatuju pouze vychozi jazyk, tak preklady nemusim resit
|
|
if ($language === Contexts::get(LanguageContext::class)->getDefaultId()) {
|
|
return;
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('id_language, value')
|
|
->from('parameters_list_translations')
|
|
->where(Operator::equals(['id_parameters_list' => $parameterValueId]));
|
|
|
|
if ($language) {
|
|
$qb->andWhere(Operator::equals(['id_language' => $language]));
|
|
}
|
|
|
|
// zaktualizuju preklady jmenovky varianty podle hodnoty parametru
|
|
foreach ($qb->execute() as $item) {
|
|
$this->variationsValuesTranslation->saveSingleObject($item['id_language'], $labelValueId, [
|
|
'value' => $item['value'],
|
|
]);
|
|
}
|
|
}
|
|
|
|
public function getLabelByKey(string $key): int
|
|
{
|
|
static $labels = [];
|
|
|
|
if ($labels[$key] ?? false) {
|
|
return $labels[$key];
|
|
}
|
|
|
|
if (!($labelId = $this->getMapping(MappingType::MAPPING_LABEL, $key))) {
|
|
$labelId = $this->getLabelId($key);
|
|
|
|
$this->createMapping(MappingType::MAPPING_LABEL, $key, $labelId);
|
|
}
|
|
|
|
return $labels[$key] = $labelId;
|
|
}
|
|
|
|
public function getLabelId(string $label): int
|
|
{
|
|
$labelId = sqlQueryBuilder()
|
|
->select('id')
|
|
->from('products_variations_choices_labels')
|
|
->where(Operator::equals(['label' => trim($label)]))
|
|
->execute()->fetchOne();
|
|
|
|
if (!$labelId) {
|
|
$labelId = sqlGetConnection()->transactional(function () use ($label) {
|
|
sqlQueryBuilder()
|
|
->insert('products_variations_choices_labels')
|
|
->directValues(['label' => $label])
|
|
->execute();
|
|
|
|
return (int) sqlInsertId();
|
|
});
|
|
}
|
|
|
|
return (int) $labelId;
|
|
}
|
|
|
|
public function getParameterByKey(string $key, string $type = 'list', bool $createIfMissing = true): ?int
|
|
{
|
|
static $parameters;
|
|
|
|
if ($parameters === null) {
|
|
$parameters = Mapping::mapKeys(sqlQueryBuilder()
|
|
->select('id_znz, id_parameter')
|
|
->from('znz_parameters')
|
|
->execute()->fetchAllAssociative(), fn ($k, $v) => [$v['id_znz'], $v['id_parameter']]);
|
|
}
|
|
|
|
// pokud poslu $createIfMissing=false, tak vracim null kdyz parametr neexistuje
|
|
if (!($parameters[$key] ?? false) && $createIfMissing === false) {
|
|
return null;
|
|
}
|
|
|
|
if (!($parameters[$key] ?? false)) {
|
|
// pokud neexistuje, tak ho vytvorim
|
|
$parameterId = (int) $this->parameterFinder->findParameter($key, $type);
|
|
// ulozim si mapping
|
|
$this->createMapping(ParameterSynchronizer::getType(), $key, $parameterId);
|
|
|
|
$parameters[$key] = $parameterId;
|
|
}
|
|
|
|
return $parameters[$key];
|
|
}
|
|
|
|
public function recalculateStores(?array $productIds = null, bool $withVariationsInStoreRecalculate = true): void
|
|
{
|
|
$getQuantitySubQuery = function (bool $variations = false) {
|
|
$alias = 'p';
|
|
$productIdColumn = 'id';
|
|
if ($variations) {
|
|
$alias = 'pv';
|
|
$productIdColumn = 'id_product';
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('COALESCE(SUM(GREATEST(si.quantity, 0)), 0)')
|
|
->from('stores_items', 'si')
|
|
->join('si', 'stores', 's', 's.id = si.id_store')
|
|
->andWhere('si.id_product = '.$alias.'.'.$productIdColumn)
|
|
->andWhere(Operator::equals(['s.figure' => 'Y']));
|
|
|
|
if ($variations) {
|
|
$qb->andWhere('si.id_variation = pv.id');
|
|
} else {
|
|
$qb->andWhere('si.id_variation IS NULL');
|
|
}
|
|
|
|
return $qb;
|
|
};
|
|
|
|
/**
|
|
* Explanation for the "wierd" logic bellow:
|
|
* Amount of locked rows is highly reduced when update is executed only on specific ids.
|
|
* So when $productIds parameter is empty, we still try to find the ids to update before running the actual update query
|
|
*/
|
|
|
|
// ----------- Products recalc query (optimized for reducing db locks -> reducing deadlocks)
|
|
$productsSubQuery = $getQuantitySubQuery();
|
|
|
|
$productsRecalcBaseQb = sqlQueryBuilder()
|
|
->leftJoin('p', 'products_variations', 'pv', 'pv.id_product = p.id')
|
|
->andWhere('p.in_store != ('.$productsSubQuery->getSQL().')')
|
|
// recalc products chci spoustet pouze pro nevariatni produkty, protoze by to tady nastavilo akorat nesmysl, ktery to nize stejnak prerazi
|
|
->andWhere('pv.id IS NULL')
|
|
->addQueryBuilderParameters($productsSubQuery);
|
|
|
|
$productUpdateIds = $productIds;
|
|
|
|
// When all products should be recalculated, update only products with incorrect in_store
|
|
if ($productUpdateIds === null) {
|
|
$productUpdateIds = (clone $productsRecalcBaseQb)->select('p.id')->from('products', 'p')->groupBy('p.id')->execute()->fetchFirstColumn();
|
|
$productUpdateIds = count($productUpdateIds) > 1000 ? null : $productUpdateIds;
|
|
}
|
|
|
|
$productsUpdateQb = $productsRecalcBaseQb
|
|
->update('products', 'p')
|
|
->set('p.in_store', '('.$productsSubQuery->getSQL().')');
|
|
|
|
if ($productUpdateIds !== null) {
|
|
$productsUpdateQb->andWhere(Operator::inIntArray($productUpdateIds, 'p.id'));
|
|
}
|
|
|
|
// ---------- Variations recalc query (optimized for reducing db locks -> reducing deadlocks)
|
|
$variationsSubQuery = $getQuantitySubQuery(true);
|
|
|
|
$variationsRecalcBaseQb = sqlQueryBuilder()
|
|
->andWhere('pv.in_store != ('.$variationsSubQuery->getSQL().')')
|
|
->addQueryBuilderParameters($variationsSubQuery);
|
|
|
|
$variationsUpdateIds = $productIds;
|
|
|
|
// When all variations should be recalculated, update only variations with incorrect in_store
|
|
if ($variationsUpdateIds === null) {
|
|
$variationsUpdateIds = (clone $variationsRecalcBaseQb)->select('pv.id_product')
|
|
->from('products_variations', 'pv')->groupBy('pv.id_product')->execute()->fetchFirstColumn();
|
|
$variationsUpdateIds = count($variationsUpdateIds) > 1000 ? null : $variationsUpdateIds;
|
|
}
|
|
|
|
$variationsUpdateQb = $variationsRecalcBaseQb
|
|
->update('products_variations', 'pv')
|
|
->set('pv.in_store', '('.$variationsSubQuery->getSQL().')');
|
|
|
|
if ($variationsUpdateIds !== null) {
|
|
$variationsUpdateQb->andWhere(Operator::inIntArray($variationsUpdateIds, 'pv.id_product'));
|
|
}
|
|
|
|
sqlGetConnection()->transactional(function () use ($productsUpdateQb, $variationsUpdateQb, $withVariationsInStoreRecalculate) {
|
|
// recalculate products
|
|
$productsUpdateQb->execute();
|
|
|
|
// recalculate variations
|
|
$variationsUpdateQb->execute();
|
|
|
|
// prenest sklad variant na produkt - pokud je zapnuto
|
|
if ($withVariationsInStoreRecalculate) {
|
|
\Variations::recalcInStore();
|
|
}
|
|
});
|
|
}
|
|
|
|
public function getZNZMaxId(int $znzId, string $table, string $key): ?int
|
|
{
|
|
$cacheKey = implode('-', [$znzId, $table, $key]);
|
|
|
|
return $this->znzMaxIdsCache[$cacheKey] ?? null;
|
|
|
|
// zkusme to bez toho, teoreticky staci to mit v pameti, porotoze primarne jde o zmeny, ktere chodi blizko sebe
|
|
// if (isset($this->znzMaxIdsCache[$cacheKey])) {
|
|
// return $this->znzMaxIdsCache[$cacheKey];
|
|
// }
|
|
//
|
|
// $maxId = sqlQueryBuilder()
|
|
// ->select('id_max')
|
|
// ->from('znz_max_ids')
|
|
// ->where(
|
|
// Operator::equals(
|
|
// [
|
|
// 'id_znz' => $znzId,
|
|
// 'znz_table' => $table,
|
|
// 'znz_key' => $key,
|
|
// ]
|
|
// )
|
|
// )
|
|
// ->sendToMaster()
|
|
// ->execute()->fetchOne();
|
|
//
|
|
// return $this->znzMaxIdsCache[$cacheKey] = $maxId ?: null;
|
|
}
|
|
|
|
public function updateZNZMaxId(int $znzId, string $table, string $key, int $maxId): void
|
|
{
|
|
$cacheKey = implode('-', [$znzId, $table, $key]);
|
|
|
|
// update cache to make sync faster
|
|
$this->znzMaxIdsCache[$cacheKey] = $maxId;
|
|
|
|
// zkusme to bez toho, teoreticky staci to mit v pameti, porotoze primarne jde o zmeny, ktere chodi blizko sebe
|
|
// sqlQueryBuilder()
|
|
// ->insert('znz_max_ids')
|
|
// ->directValues(
|
|
// [
|
|
// 'id_znz' => $znzId,
|
|
// 'znz_table' => $table,
|
|
// 'znz_key' => $key,
|
|
// 'id_max' => $maxId,
|
|
// ]
|
|
// )
|
|
// ->onDuplicateKeyUpdate(['id_max'])
|
|
// ->execute();
|
|
}
|
|
|
|
public function sortColorLabelValues(): void
|
|
{
|
|
$colorLabelId = $this->getLabelByKey('Barva');
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('pvcv.id, pv.data')
|
|
->from('products_variations_choices_values', 'pvcv')
|
|
->join('pvcv', 'products_variations_combination', 'pvc', 'pvc.id_label = pvcv.id_label AND pvc.id_value = pvcv.id')
|
|
->join('pvc', 'products_variations', 'pv', 'pv.id = pvc.id_variation')
|
|
->where(Operator::equals(['pvcv.id_label' => $colorLabelId]))
|
|
->groupBy('pvcv.id');
|
|
|
|
$colors = [];
|
|
foreach ($qb->execute() as $item) {
|
|
$data = json_decode($item['data'] ?: '', true) ?: [];
|
|
$color = $data['color'][0] ?? null;
|
|
if (!$color) {
|
|
$colors[] = [
|
|
'id' => $item['id'],
|
|
'rgb' => [255, 255, 255],
|
|
'position' => 99999,
|
|
];
|
|
continue;
|
|
}
|
|
|
|
$rgb = sscanf("#{$color}", '#%02x%02x%02x');
|
|
|
|
$colors[] = [
|
|
'id' => $item['id'],
|
|
'rgb' => $rgb,
|
|
];
|
|
}
|
|
|
|
usort($colors, function ($a, $b) {
|
|
$hsv1 = $this->rgbToHsv($a['rgb']);
|
|
$hsv2 = $this->rgbToHsv($b['rgb']);
|
|
|
|
return ($hsv1['hue'] + $hsv1['saturation'] + $hsv1['value']) <=> ($hsv2['hue'] + $hsv2['saturation'] + $hsv2['value']);
|
|
});
|
|
|
|
foreach ($colors as $position => $item) {
|
|
if (!empty($item['position'])) {
|
|
$position = $item['position'];
|
|
}
|
|
|
|
sqlQueryBuilder()
|
|
->update('products_variations_choices_values')
|
|
->directValues(['sort' => $position])
|
|
->where(Operator::equals(['id' => $item['id']]))
|
|
->execute();
|
|
}
|
|
}
|
|
|
|
public function sortParameterAlphabet(int $parameterId): void
|
|
{
|
|
$parameters = sqlQueryBuilder()->select('id, value')
|
|
->from('parameters_list', 'pl')
|
|
->where(Operator::equals(['id_parameter' => $parameterId]))
|
|
->execute()->fetchAllAssociative();
|
|
|
|
$collator = new \Collator(Contexts::get(LanguageContext::class)->getActive()->getLocale());
|
|
$collator->setAttribute(\Collator::NUMERIC_COLLATION, \Collator::ON);
|
|
|
|
usort($parameters, function ($a, $b) use ($collator) {
|
|
return $collator->compare($a['value'], $b['value']);
|
|
});
|
|
|
|
$i = 0;
|
|
foreach ($parameters as $parameter) {
|
|
sqlQueryBuilder()->update('parameters_list', 'pl')
|
|
->directValues(['position' => ++$i])
|
|
->where(Operator::equals(['id' => $parameter['id']]))
|
|
->execute();
|
|
}
|
|
}
|
|
|
|
public function getSeriesTemplateCategoryId(): int
|
|
{
|
|
static $seriesTemplateCategoryId;
|
|
|
|
if (!$seriesTemplateCategoryId) {
|
|
$tmpCategoryId = sqlQueryBuilder()
|
|
->select('id')
|
|
->from('templates_categories')
|
|
->where(Operator::equals(['name' => 'Značka řada']))
|
|
->execute()->fetchOne();
|
|
|
|
if (!$tmpCategoryId) {
|
|
$tmpCategoryId = sqlGetConnection()->transactional(function () {
|
|
sqlQueryBuilder()
|
|
->insert('templates_categories')
|
|
->directValues(['name' => 'Značka řada'])
|
|
->execute();
|
|
|
|
return (int) sqlInsertId();
|
|
});
|
|
}
|
|
|
|
$seriesTemplateCategoryId = $tmpCategoryId;
|
|
}
|
|
|
|
return $seriesTemplateCategoryId;
|
|
}
|
|
|
|
/** Vrati ID spolecne sablony, nebo vytvori spolecnou sablonu pro radu a vyrobce produktu */
|
|
public function getSeriesTemplateId(int $znzOptionId): int
|
|
{
|
|
static $templates;
|
|
|
|
if ($templates === null) {
|
|
$templates = Mapping::mapKeys(sqlQueryBuilder()
|
|
->select('id, JSON_VALUE(data, "$.znzOptionId") as id_znz')
|
|
->from('templates')
|
|
->where('JSON_VALUE(data, "$.znzOptionId") IS NOT NULL')
|
|
->execute()->fetchAllAssociative(), fn ($k, $v) => [$v['id_znz'], $v['id']]);
|
|
}
|
|
|
|
if (!($templates[$znzOptionId] ?? null)) {
|
|
$parameterId = $this->getParameterByKey('ZnackaRadaOption');
|
|
|
|
$templateName = $znzOptionId;
|
|
if ($valueId = $this->getParameterValueMapping($parameterId, $znzOptionId)) {
|
|
$parameterValue = sqlQueryBuilder()
|
|
->select('value')
|
|
->from('parameters_list')
|
|
->where(Operator::equals(['id' => $valueId]))
|
|
->execute()->fetchOne();
|
|
|
|
if ($parameterValue) {
|
|
// add znzOptionId to avoid duplicates
|
|
$templateName = "{$parameterValue} ({$znzOptionId})";
|
|
}
|
|
}
|
|
|
|
$templates[$znzOptionId] = sqlGetConnection()->transactional(function () use ($znzOptionId, $templateName) {
|
|
sqlQueryBuilder()
|
|
->insert('templates')
|
|
->directValues(
|
|
[
|
|
'id_category' => $this->getSeriesTemplateCategoryId(),
|
|
'name' => $templateName,
|
|
'data' => json_encode(['znzOptionId' => $znzOptionId]),
|
|
]
|
|
)->execute();
|
|
|
|
return (int) sqlInsertId();
|
|
});
|
|
}
|
|
|
|
return $templates[$znzOptionId];
|
|
}
|
|
|
|
public function getDefaultStore(?string $language = null): ?array
|
|
{
|
|
$stores = $this->getEnabledStores();
|
|
$language = $language ?: Contexts::get(LanguageContext::class)->getActiveId();
|
|
|
|
$filtered = [];
|
|
foreach ($stores as $store) {
|
|
if (empty($store['data']['languages']) || in_array($language, $store['data']['languages'])) {
|
|
$filtered[] = $store['id'];
|
|
}
|
|
}
|
|
|
|
if ($storeId = reset($filtered)) {
|
|
return $stores[$storeId];
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public function getStoreByDeliveryId(int $deliveryId): ?array
|
|
{
|
|
foreach ($this->getEnabledStores() as $store) {
|
|
if ($deliveryId == $store['id_delivery']) {
|
|
return $store;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public function getEnabledStores(): array
|
|
{
|
|
return array_filter($this->storesInStore->getStores(), fn ($x) => $x['figure'] === 'Y');
|
|
}
|
|
|
|
public function cleanMovedProducts(): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->delete('products')
|
|
->where(Operator::like(['code' => 'MOVED_%']))
|
|
->execute();
|
|
|
|
sqlQueryBuilder()
|
|
->delete('products_variations')
|
|
->where(Operator::like(['code' => 'MOVED_%']))
|
|
->execute();
|
|
}
|
|
|
|
/**
|
|
* Tuhle fci pouzivam na mistech, kde potrebuji resit variantu podle skladu.
|
|
*
|
|
* Varianta per sklad je pouze na B2B a je to kvuli ruznym cenam na ruznych skladech, pripadne i moznosti vyberu
|
|
* z jakeho skladu chce B2B zbozi objednat.
|
|
*/
|
|
public function getProductVariationIdWithStoreCheck(int $productId, ?int $variationId, ?string $znzStoreId): ?int
|
|
{
|
|
// pokud nejsem v B2B modu, tak vracim rovnou ID varianty, ktere jsem dostal
|
|
if (!$this->configuration->isB2BMode()) {
|
|
return $variationId;
|
|
}
|
|
|
|
if (!$znzStoreId) {
|
|
return null;
|
|
}
|
|
|
|
// pokud jsem v B2B modu, tak vracim variantu per sklad
|
|
return $this->withRuntimeCache(
|
|
fn () => $this->getProductVariationByStore($productId, $znzStoreId),
|
|
['B2BVariations', $productId, $variationId]
|
|
);
|
|
}
|
|
|
|
public function isMessageWithStoreRequiredValid(array $item): bool
|
|
{
|
|
if (!$this->configuration->isB2BMode()) {
|
|
return true;
|
|
}
|
|
|
|
return !empty($item['IdSklad']);
|
|
}
|
|
|
|
/**
|
|
* Najde / vytvori variantu u produktu podle ID skladu (id skladu ze ZNZ).
|
|
*/
|
|
public function getProductVariationByStore(int $productId, string $znzStoreId): int
|
|
{
|
|
$znzId = "{$productId}-{$znzStoreId}";
|
|
|
|
$variationId = sqlQueryBuilder()
|
|
->select('id_variation')
|
|
->from('znz_b2b_variations')
|
|
->where(Operator::equals(['id_znz' => $znzId]))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
// variation does not exists, create one
|
|
if (!$variationId) {
|
|
$variationId = sqlGetConnection()->transactional(function () use ($znzId, $productId, $znzStoreId) {
|
|
$variationId = (int) \Variations::createProductVariation($productId, [
|
|
$this->getLabelId('Sklad') => $znzStoreId,
|
|
]);
|
|
|
|
// get product code
|
|
$productCode = sqlQueryBuilder()
|
|
->select('code')
|
|
->from('products')
|
|
->where(Operator::equals(['id' => $productId]))
|
|
->execute()->fetchOne() ?: $znzId;
|
|
|
|
// set variation code to productCode_znzStoreId
|
|
sqlQueryBuilder()
|
|
->update('products_variations')
|
|
->directValues(['code' => "{$productCode}_{$znzStoreId}"])
|
|
->where(Operator::equals(['id' => $variationId]))
|
|
->execute();
|
|
|
|
// create variation mapping
|
|
sqlQueryBuilder()
|
|
->insert('znz_b2b_variations')
|
|
->directValues(
|
|
[
|
|
'id_znz' => $znzId,
|
|
'id_znz_store' => $znzStoreId,
|
|
'id_product' => $productId,
|
|
'id_variation' => $variationId,
|
|
]
|
|
)->execute();
|
|
|
|
return $variationId;
|
|
});
|
|
}
|
|
|
|
return $variationId;
|
|
}
|
|
|
|
public function withRuntimeCache(callable $getter, array $cacheKey): mixed
|
|
{
|
|
static $runtimeCache = [];
|
|
|
|
$key = implode('-', array_filter($cacheKey));
|
|
|
|
if ($runtimeCache[$key] ?? null) {
|
|
return $runtimeCache[$key];
|
|
}
|
|
|
|
return $runtimeCache[$key] = $getter();
|
|
}
|
|
|
|
public function getCountryCodeForHelios(string $country): string
|
|
{
|
|
return $this->getCountryMap()[$country] ?? $country;
|
|
}
|
|
|
|
public function getCountryCodeByHelios(string $country): string
|
|
{
|
|
return array_flip($this->getCountryMap())[$country] ?? $country;
|
|
}
|
|
|
|
public function getDownloader(): \Downloader
|
|
{
|
|
static $downloader;
|
|
|
|
if (!$downloader) {
|
|
$downloader = new \Downloader();
|
|
$downloader->setMethod('curl');
|
|
}
|
|
|
|
return $downloader;
|
|
}
|
|
|
|
/**
|
|
* Returns specific country aliases for Helios.
|
|
*/
|
|
private function getCountryMap(): array
|
|
{
|
|
return [
|
|
'GR' => 'EL',
|
|
];
|
|
}
|
|
|
|
private function rgbToHsv(array $rgb): array
|
|
{
|
|
$red = $rgb[0] / 255;
|
|
$green = $rgb[1] / 255;
|
|
$blue = $rgb[2] / 255;
|
|
|
|
$min = min($red, $green, $blue);
|
|
$max = max($red, $green, $blue);
|
|
|
|
switch ($max) {
|
|
case 0:
|
|
// If the max value is 0.
|
|
$hue = 0;
|
|
$saturation = 0;
|
|
$value = 0;
|
|
break;
|
|
case $min:
|
|
// If the maximum and minimum values are the same.
|
|
$hue = 0;
|
|
$saturation = 0;
|
|
$value = round($max, 4);
|
|
break;
|
|
default:
|
|
$delta = $max - $min;
|
|
if ($red == $max) {
|
|
$hue = 0 + ($green - $blue) / $delta;
|
|
} elseif ($green == $max) {
|
|
$hue = 2 + ($blue - $red) / $delta;
|
|
} else {
|
|
$hue = 4 + ($red - $green) / $delta;
|
|
}
|
|
$hue *= 60;
|
|
if ($hue < 0) {
|
|
$hue += 360;
|
|
}
|
|
$saturation = $delta / $max;
|
|
$value = round($max, 4);
|
|
}
|
|
|
|
return ['hue' => $hue, 'saturation' => $saturation, 'value' => $value];
|
|
}
|
|
}
|