Files
kupshop/bundles/External/ZNZBundle/Synchronizers/ObjectValidationSynchronizer.php
2025-08-02 16:30:27 +02:00

590 lines
22 KiB
PHP

<?php
declare(strict_types=1);
namespace External\ZNZBundle\Synchronizers;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\KupShopBundle\Context\DomainContext;
use KupShop\KupShopBundle\Context\LanguageContext;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\Database\QueryHint;
use KupShop\KupShopBundle\Util\System\PathFinder;
use Query\Operator;
use Query\QueryBuilder;
use Symfony\Contracts\Service\Attribute\Required;
class ObjectValidationSynchronizer extends BaseSynchronizer
{
protected static string $type = 'object_validation';
#[Required]
public ProductSynchronizer $productSynchronizer;
#[Required]
public PathFinder $pathFinder;
private static bool $isDebugMode = false;
public static function getHandledTables(): array
{
return [
'PlatneEntity' => 'processObjectValidation',
];
}
public function processObjectValidation(array $item): void
{
if (!($method = $this->getObjectHandlers()[$item['Tabulka']] ?? null)) {
return;
}
if (!$this->isAllowed()) {
return;
}
QueryHint::withRouteToMaster(fn () => $this->{$method}($item['IDs:'] ?? [], $item['Tabulka']));
}
protected function preprocessItems(array $items): array
{
return [$items];
}
protected function getObjectHandlers(): array
{
return [
'ProduktWebsite' => 'handleProductWebsiteValidation',
'ProduktAtributy1' => 'handleProductValidation',
'ProduktAtributOptions' => 'handleParameterValuesValidation',
// 'ProduktAtributOptions1' => 'handleParameterValuesValidation',
// 'ProduktAtributOptions2' => 'handleParameterValuesValidation',
'ProduktAtributy2' => 'handleProductParameterValidation',
'ProduktObrazek' => 'handleProductPhotosValidation',
'ZakaznickaSkupina' => 'handleUserGroupsValidation',
];
}
/**
* Validace vazby produkt <-> website. Pokud nejakou odebrali, tak ji smazneme.
*/
private function handleProductWebsiteValidation(array $validIds, string $objectName): void
{
$values = [];
foreach ($validIds as $validId) {
$parts = explode('-', $validId);
$id = array_shift($parts);
$website = implode('-', $parts);
if (empty($this->configuration->getSupportedWebsites()[$website])) {
continue;
}
$values[] = [
'id_znz' => $id,
'type' => 'product_website',
'data' => $website,
];
}
$this->withValidItems($values, 'product_website', function (QueryBuilder $validIdsQb) use ($objectName) {
$validIdsQb->addSelect('data as id_website');
$invalidIds = sqlQueryBuilder()
->select('id_znz, id_website')
->from('znz_products_website')
->where(Operator::not(Operator::inSubQuery('(id_znz, id_website)', $validIdsQb)));
$deleteQb = sqlQueryBuilder()
->delete('znz_products_website', 'zpw')
->joinSubQuery('zpw', $invalidIds, 't', 't.id_znz = zpw.id_znz AND t.id_website = zpw.id_website');
if (!$this->isSafeToExecute($deleteQb, $objectName, selectIdField: 'id_znz')) {
return;
}
$deleteQb->execute();
});
}
/**
* Validace a mazani produktu, ktere v Heliosu uz nejsou.
*/
private function handleProductValidation(array $validIds, string $objectName): void
{
$values = [];
foreach ($validIds as $id) {
$values[] = [
'id_znz' => $id,
'type' => ProductSynchronizer::getType(),
'data' => null,
];
}
$this->withValidItems($values, ProductSynchronizer::getType(), function (QueryBuilder $validIdsQb) use ($objectName) {
$invalidProductsSubquery = sqlQueryBuilder()
->select('p.id')
->from('products', 'p')
->join('p', 'znz_products', 'zp', 'zp.id_product = p.id AND zp.id_variation IS NULL')
->andWhere(Operator::not(Operator::inSubQuery('zp.id_znz', $validIdsQb)));
$deleteProductsQb = sqlQueryBuilder()
->delete('products', 'p')
->joinSubQuery('p', $invalidProductsSubquery, 't', 't.id = p.id');
if ($this->isSafeToExecute($deleteProductsQb, "{$objectName} (products)")) {
$deleteProductsQb->execute();
}
$invalidVariationsSubquery = sqlQueryBuilder()
->select('pv.id')
->from('products_variations', 'pv')
->join('pv', 'znz_products', 'zp', 'zp.id_product = pv.id_product AND zp.id_variation = pv.id')
->andWhere(Operator::not(Operator::inSubQuery('zp.id_znz', $validIdsQb)));
$deleteVariationsQb = sqlQueryBuilder()
->delete('products_variations', 'pv')
->joinSubQuery('pv', $invalidVariationsSubquery, 't', 't.id = pv.id');
if ($this->isSafeToExecute($deleteVariationsQb, "{$objectName} (variations)")) {
$deleteVariationsQb->execute();
}
});
}
/**
* Validace a mazani hodnot parametru, ktere v Heliosu uz nejsou.
*/
private function handleParameterValuesValidation(array $validIds, string $objectName): void
{
$values = [];
foreach ($validIds as $validId) {
$parsed = explode('-', $validId);
$id = (int) $parsed[0];
$lang = $parsed[2] ?? '';
if (!($language = $this->getLanguageByLocale($lang))) {
continue;
}
$values[] = [
'id_znz' => $id,
'type' => ParameterSynchronizer::$typeValues,
'data' => json_encode([
'id_parameter' => $this->znzUtil->getParameterByKey($parsed[1]),
'id_language' => $language,
]),
];
}
$generator = function () {
foreach ($this->getLanguageContext()->getAll() as $lang) {
yield [$lang->getId(), Operator::equals(['JSON_VALUE(data, "$.id_language")' => $lang->getId()])];
}
};
$this->withValidItems($values, ParameterSynchronizer::$typeValues, function (QueryBuilder $validIdsQb, string $language) use ($objectName) {
$validIdsQb
->select('CAST(id_znz AS DECIMAL) as id_znz')
->addSelect('CAST(JSON_VALUE(data, "$.id_parameter") AS UNSIGNED) as id_parameter');
$invalidIds = sqlQueryBuilder()
->select('pl.id')
->from('parameters_list', 'pl')
->join('pl', 'znz_parameters_values', 'zpv', 'zpv.id_parameters_value = pl.id')
->andWhere(Operator::not(Operator::inSubQuery('(zpv.id_znz, zpv.id_parameter)', $validIdsQb)));
$isTranslation = $language !== $this->getLanguageContext()->getDefaultId();
$deleteQb = sqlQueryBuilder()
->delete($isTranslation ? 'parameters_list_translations' : 'parameters_list', 'pt')
->joinSubQuery('pt', $invalidIds, 't', 't.id = '.($isTranslation ? 'pt.id_parameters_list' : 'pt.id'));
// keep empty translations
if ($isTranslation) {
$deleteQb->andWhere('value != ""');
}
if ($isTranslation) {
$deleteQb->andWhere(Operator::equals(['id_language' => $language]));
}
if (!$this->isSafeToExecute($deleteQb, $objectName, ['id_language' => $language])) {
return;
}
$deleteQb->execute();
}, $generator());
}
/**
* Validace parametru produktu. Smaze prirazene hodnoty parametru od produktu, ktere uz nejsou v Heliosu.
*/
public function handleProductParameterValidation(array $validIds, string $objectName): void
{
$values = [];
foreach ($validIds as $id) {
$parsed = explode('-', $id);
$values[] = [
// parameters_list id
'id_znz' => $parsed[2],
'type' => 'parameters_products',
'data' => json_encode([
'id_parameter' => $this->znzUtil->getParameterByKey($parsed[1]),
'id_product' => $parsed[0],
]),
];
}
$this->withValidItems($values, 'parameters_products', function (QueryBuilder $validIdsQb) use ($objectName) {
$validIdsQb->select('CAST(id_znz AS DECIMAL) as id_znz')
->addSelect('CAST(JSON_VALUE(data, "$.id_parameter") AS INT) as id_parameter')
->addSelect('CAST(JSON_VALUE(data, "$.id_product") AS INT) as id_product');
$invalidIds = sqlQueryBuilder()
->select('pp.id')
->from('parameters_products', 'pp')
->join('pp', 'parameters_list', 'pl', 'pl.id_parameter = pp.id_parameter AND pl.id = pp.value_list')
->join('pp', 'znz_parameters_values', 'zpv', 'zpv.id_parameter = pp.id_parameter AND zpv.id_parameters_value = pl.id')
->join('zpv', 'znz_parameters', 'zp', 'zp.id_parameter = zpv.id_parameter')
->andWhere(Operator::not(Operator::inStringArray($this->productSynchronizer->getProduktAtributy1Parameters(), 'zp.id_znz')))
->andWhere(Operator::not(Operator::inSubQuery('(zpv.id_znz, zpv.id_parameter, pp.id_product)', $validIdsQb)));
$deleteQb = sqlQueryBuilder()
->delete('parameters_products', 'pp')
->joinSubQuery('pp', $invalidIds, 't', 'pp.id = t.id');
if (!$this->isSafeToExecute($deleteQb, $objectName)) {
return;
}
$deleteQb->execute();
});
}
/**
* Validace fotek. Smaze fotky, ktere uz nejsou v Heliosu.
*/
public function handleProductPhotosValidation(array $validIds, string $objectName): void
{
$values = [];
foreach ($validIds as $id) {
$values[] = [
'id_znz' => explode('-', $id)[0],
'type' => 'photo',
'data' => null,
];
}
$this->withValidItems($values, 'photo', function (QueryBuilder $validIdsQb) use ($objectName) {
$invalidIds = sqlQueryBuilder()
->select('ph.id')
->from('photos', 'ph')
->join('ph', 'znz_photos', 'zph', 'zph.id_photo = ph.id')
->andWhere(Operator::not(Operator::inSubQuery('zph.id_znz', $validIdsQb)));
$deleteQb = sqlQueryBuilder()
->delete('photos', 'ph')
->joinSubQuery('ph', $invalidIds, 't', 'ph.id = t.id');
if (!$this->isSafeToExecute($deleteQb, $objectName)) {
return;
}
$deleteQb->execute();
});
}
/**
* Validace skupin uzivatelu. Smaze skupiny, ktere uz nejsou v Heliosu.
*/
public function handleUserGroupsValidation(array $validIds, string $objectName): void
{
$values = [];
foreach ($validIds as $id) {
$values[] = [
'id_znz' => $id,
'type' => UserSynchronizer::$typeGroups,
'data' => null,
];
}
$this->withValidItems($values, UserSynchronizer::$typeGroups, function (QueryBuilder $validIdsQb) use ($objectName) {
$invalidIds = sqlQueryBuilder()
->select('ug.id')
->from('users_groups', 'ug')
->join('ug', 'znz_users_groups', 'zug', 'zug.id_users_group = ug.id')
->andWhere(Operator::not(Operator::inSubQuery('zug.id_znz', $validIdsQb)));
$deleteQb = sqlQueryBuilder()
->delete('users_groups')
->where(Operator::inSubQuery('id', $invalidIds));
if (!$this->isSafeToExecute($deleteQb, $objectName)) {
return;
}
$deleteQb->execute();
});
}
/**
* @param iterable<array{0: string, 1: callable}>|null $iterator
*/
private function withValidItems(array $validItems, string $type, callable $fn, ?iterable $iterator = null): void
{
// smaznout vsechny valid objekty
sqlQuery('TRUNCATE TABLE znz_valid_objects');
// ted zacnu insertovat nove valid objekty z $validItems
if (!empty($validItems)) {
$baseQb = sqlQueryBuilder()
->insert('znz_valid_objects');
foreach (array_chunk($validItems, 1000) as $chunk) {
$qb = clone $baseQb;
foreach ($chunk as $item) {
$qb->multiDirectValues($item);
}
$qb->execute();
}
}
$validIdsBaseQb = sqlQueryBuilder()
->select('CAST(id_znz AS INT) as id_znz')
->from('znz_valid_objects')
->andWhere(Operator::equals(['type' => $type]));
// pokud nemam iterator tak poslu rovnou validIdBaseQb
if (!$iterator) {
$fn($validIdsBaseQb);
return;
}
// pokud mam iterator, tak pro kazdy iterator generuju specificky query builder
foreach ($iterator as [$value, $spec]) {
$validIdsQb = (clone $validIdsBaseQb);
if ($spec) {
$validIdsQb->andWhere($spec);
}
$fn($validIdsQb, $value);
}
}
private function isSafeToExecute(QueryBuilder $deleteQb, ?string $objectName = null, array $data = [], string $selectIdField = 'id'): bool
{
if ($deleteQb->getQueryPart('from')['alias']) {
$selectIdField = "{$deleteQb->getQueryPart('from')['alias']}.{$selectIdField}";
}
$selectQb = sqlQueryBuilder()
->select($selectIdField)
->from($deleteQb->getQueryPart('from')['table'], $deleteQb->getQueryPart('from')['alias'])
->setParameters($deleteQb->getParameters(), $deleteQb->getParameterTypes());
if ($deleteQb->getQueryPart('where')) {
$selectQb->where($deleteQb->getQueryPart('where'));
}
if ($deleteQb->getQueryPart('join')) {
foreach ($deleteQb->getQueryPart('join') as $alias => $joins) {
foreach ($joins as $join) {
$selectQb->add('join', [$alias => $join], true);
}
}
}
$count = $selectQb->execute()->rowCount();
// zbytecny spoustet pokud neni co smazat
if ($count <= 0) {
return false;
}
// data pro activity log
$activityLogData = [
...['entity' => $objectName, 'deleteCount' => $count],
...$data,
];
// vygeneruju delete report, ktery obsahuje IDcka zaznamu, ktere chceme smaznout
if ($this->isDebugMode() && ($report = $this->generateDebugReport($selectQb, $objectName, $data))) {
$activityLogData['delete_report'] = Contexts::get(DomainContext::class)->getActiveWithScheme().'/'.$report;
}
$prefix = '';
if ($data['id_language'] ?? false) {
$prefix = "[{$data['id_language']}] ";
}
// pokud se toho chce smazat vic jak N, tak to nedovolim a lognu do Activity logu jako chybu
if ($count > ($safeLimit = $this->getSafeLimitByObjectName($objectName))) {
$this->logger->activity(
"{$prefix}PlatneEntity: U objektu \"{$objectName}\" nebylo spuštěné smazání neplatných objektů, protože jich bylo vybráno více jak {$safeLimit} ke smazání: {$count}",
$activityLogData,
);
return false;
}
$this->logger->activity(
"{$prefix}PlatneEntity: U objektu \"{$objectName}\" bylo vybráno \"{$count}\" objektů ke smazání",
$activityLogData,
ActivityLog::SEVERITY_SUCCESS
);
// pokud je to debug mod, tak vracim vzdy false
if ($this->isDebugMode()) {
return false;
}
return true;
}
private function generateDebugReport(QueryBuilder $invalidIdsQb, ?string $objectName = null, array $data = []): ?string
{
if (!($table = $invalidIdsQb->getQueryPart('from')[0]['table'] ?? null)) {
return null;
}
switch ($table) {
case 'products':
$objectName = "{$objectName}_products";
$dataQb = sqlQueryBuilder()
->select('zp.id_znz, p.id, p.code, p.title')
->from('products', 'p')
->leftJoin('p', 'znz_products', 'zp', 'zp.id_product = p.id AND zp.id_variation IS NULL')
->where(Operator::inSubQuery('p.id', $invalidIdsQb));
break;
case 'products_variations':
$objectName = "{$objectName}_variations";
$dataQb = sqlQueryBuilder()
->select('zp.id_znz, pv.id, pv.code, p.title as product_title, pv.title as title')
->from('products_variations', 'pv')
->join('pv', 'products', 'p', 'p.id = pv.id_product')
->leftJoin('pv', 'znz_products', 'zp', 'zp.id_variation = pv.id')
->where(Operator::inSubQuery('pv.id', $invalidIdsQb));
break;
case 'znz_products_website':
$dataQb = sqlQueryBuilder()
->select('zpw.id_znz, zpw.id_website')
->from('znz_products_website', 'zpw')
->where(Operator::inSubQuery('(zpw.id_znz, zpw.id_website)', $invalidIdsQb->addSelect('id_website')));
break;
case 'photos':
$dataQb = sqlQueryBuilder()
->select('zph.id_znz, ph.id')
->from('photos', 'ph')
->join('ph', 'znz_photos', 'zph', 'ph.id = zph.id_photo')
->where(Operator::inSubQuery('ph.id', $invalidIdsQb));
break;
case 'users_groups':
$dataQb = sqlQueryBuilder()
->select('zug.id_znz, ug.id, ug.name')
->from('users_groups', 'ug')
->join('ug', 'znz_users_groups', 'zug', 'ug.id = zug.id_users_group')
->where(Operator::inSubQuery('ug.id', $invalidIdsQb));
break;
case 'parameters_list':
$dataQb = sqlQueryBuilder()
->select('zpl.id_znz, pl.id, pl.value')
->from('parameters_list', 'pl')
->join('pl', 'znz_parameters_values', 'zpl', 'zpl.id_parameter = pl.id_parameter AND zpl.id_parameters_value = pl.id')
->where(Operator::inSubQuery('(pl.id, pl.id_parameter)', $invalidIdsQb->addSelect('id_parameter')));
break;
case 'parameters_list_translations':
$objectName = "{$objectName}_{$data['id_language']}";
$dataQb = sqlQueryBuilder()
->select('zpl.id_znz, plt.id_parameters_list, plt.value, plt.id_language')
->from('parameters_list_translations', 'plt')
->join('plt', 'znz_parameters_values', 'zpl', 'zpl.id_parameters_value = plt.id_parameters_list')
->where(Operator::inSubQuery('plt.id', $invalidIdsQb));
if (!empty($data['id_language'])) {
$dataQb->andWhere(Operator::equals(['plt.id_language' => $data['id_language']]));
}
break;
case 'parameters_products':
$dataQb = sqlQueryBuilder()
->select('zp.id_znz as id_znz_product, zpar.id_znz as id_znz_parameter, zpv.id_znz as id_znz_parameter_value, pl.value as parameter_value')
->from('parameters_products', 'pp')
->join('pp', 'znz_products', 'zp', 'zp.id_product = pp.id_product AND zp.id_variation IS NULL')
->join('pp', 'znz_parameters', 'zpar', 'zpar.id_parameter = pp.id_parameter')
->join('pp', 'parameters_list', 'pl', 'pl.id_parameter AND pp.id_parameter AND pl.id = pp.value_list')
->join('pp', 'znz_parameters_values', 'zpv', 'zpv.id_parameter = pp.id_parameter AND zpv.id_parameters_value = pp.value_list')
->where(Operator::inSubQuery('pp.id', $invalidIdsQb));
break;
default:
return null;
}
$filepath = $this->pathFinder->tmpPath('znz/delete_report_'.$objectName.'_'.time().'.csv');
if (!file_exists($this->pathFinder->tmpPath('znz'))) {
mkdir($this->pathFinder->tmpPath('znz'));
}
$fp = fopen($filepath, 'w');
foreach ($dataQb->execute() as $key => $item) {
if ($key === 0) {
fputcsv($fp, array_keys($item));
}
fputcsv($fp, $item);
}
return $filepath;
}
private function getSafeLimitByObjectName(?string $objectName): int
{
return match ($objectName) {
// vazby produkt <-> parametr
'ProduktAtributy2' => 100000,
// hodnoty parametru
'ProduktAtributOptions' => 10000,
// fotky produktu
'ProduktObrazek' => 5000,
// skupiny zakazniku
'ZakaznickaSkupina' => 10,
// pro ostatni
default => 2000,
};
}
private function getLanguageByLocale(string $locale): ?string
{
$languageContext = $this->getLanguageContext();
// find language
$language = null;
foreach ($languageContext->getAll() as $lang) {
if ($lang->getLocale() === $locale) {
$language = $lang->getId();
}
}
return $language;
}
private function getLanguageContext(): LanguageContext
{
static $languageContext;
return $languageContext ??= Contexts::get(LanguageContext::class);
}
private function isAllowed(): bool
{
return true;
}
private function isDebugMode(): bool
{
return static::$isDebugMode;
}
}