659 lines
21 KiB
PHP
659 lines
21 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace External\HannahBundle\SAP\Util;
|
|
|
|
use External\HannahBundle\SAP\Exception\SAPException;
|
|
use External\HannahBundle\SAP\Synchronizer\POSOrderSynchronizer;
|
|
use External\HannahBundle\Util\FTP\SFTPClient;
|
|
use KupShop\AdminBundle\Util\ActivityLog;
|
|
use KupShop\KupShopBundle\Util\Functional\Mapping;
|
|
use KupShop\KupShopBundle\Util\Logging\SentryLogger;
|
|
use KupShop\KupShopBundle\Util\System\PathFinder;
|
|
use KupShop\SynchronizationBundle\Logger\SynchronizationLogger;
|
|
use KupShop\SynchronizationBundle\Util\SynchronizationLoggerFactory;
|
|
use Query\Operator;
|
|
|
|
class SAPUtil
|
|
{
|
|
private ?SynchronizationLogger $logger = null;
|
|
|
|
public function __construct(
|
|
private SAPLocator $sapLocator,
|
|
private SFTPClient $sftpClient,
|
|
private ActivityLog $activityLog,
|
|
private SentryLogger $sentryLogger,
|
|
private SynchronizationLoggerFactory $loggerFactory,
|
|
) {
|
|
}
|
|
|
|
public function getOrderCreatedCoupon(\Order $order): array
|
|
{
|
|
$coupons = [];
|
|
|
|
foreach ($order->fetchItems() as $item) {
|
|
if (!empty($item['note']['created_coupons'])) {
|
|
$coupons = array_merge($coupons, $item['note']['created_coupons']);
|
|
}
|
|
}
|
|
|
|
return array_unique($coupons);
|
|
}
|
|
|
|
/**
|
|
* Koukne, zda jsou data v JSON souboru a pripadne ho naloaduje a vrati jeho data. V opacnem pripade vrati stejna
|
|
* data, ktera prisly.
|
|
*/
|
|
public function loadSAPData(array $data): ?array
|
|
{
|
|
ini_set('memory_limit', '4096M');
|
|
|
|
$convertToItemsField = ['Catalog', 'Dispo'];
|
|
|
|
// kouknu jestli je to zmena, ktera vyzaduje nacteni souboru
|
|
if ($filename = $this->getAdditionalFieldValue($data['Additionals'] ?? [], 'filename')) {
|
|
try {
|
|
// nactu soubor z FTP
|
|
$file = $this->sftpClient->getRemoteJSONFile($filename);
|
|
} catch (\Throwable $e) {
|
|
throw new SAPException(sprintf('Download of JSON file from FTP failed: %s', $e->getMessage()));
|
|
}
|
|
|
|
if (!file_exists($file)) {
|
|
throw new SAPException(sprintf('Missing JSON file: "%s"', $file));
|
|
}
|
|
|
|
$data = json_decode(file_get_contents($file), true);
|
|
// poladim strukturu, aby odpovidala tomu, co zpracovavame standartne
|
|
foreach ($convertToItemsField as $field) {
|
|
if (isset($data[$field])) {
|
|
$data['Items']['item'] = $data[$field];
|
|
unset($data[$field]);
|
|
}
|
|
}
|
|
|
|
$data['isFileImport'] = true;
|
|
$data['filename'] = $filename;
|
|
|
|
if (!isset($data['Additionals'])) {
|
|
$data['Additionals'] = [];
|
|
}
|
|
|
|
$filesize = filesize($file) / (1024 * 1024);
|
|
|
|
$this->addActivityLog(
|
|
sprintf(sprintf('Úspěšně načten JSON: %s', $filename)),
|
|
[
|
|
'path' => $file,
|
|
'size' => $filesize.' MB',
|
|
],
|
|
ActivityLog::SEVERITY_SUCCESS
|
|
);
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
public function updateJSONFileAsDone(string $filename): void
|
|
{
|
|
try {
|
|
$this->sftpClient->renameRemoteFile($filename, $filename.'.DONE');
|
|
} catch (\Throwable $e) {
|
|
$this->sentryLogger->captureException($e);
|
|
}
|
|
}
|
|
|
|
public function getAdditionalFieldValue(array $data, string $field): ?string
|
|
{
|
|
foreach ($data['item'] ?? [] as $item) {
|
|
if (($item['Fieldname'] ?? '') === $field || ($item['FIELDNAME'] ?? '') === $field) {
|
|
return $item['Fieldvalue'] ?? $item['FIELDVALUE'] ?? null;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
public function getMappingData(string $type, int $id): array
|
|
{
|
|
$data = sqlQueryBuilder()
|
|
->select('data')
|
|
->from('sap_'.$type.'s')
|
|
->where(
|
|
Operator::equals(
|
|
[
|
|
'id_'.$type => $id,
|
|
]
|
|
)
|
|
)
|
|
->execute()->fetchColumn();
|
|
|
|
if (!$data) {
|
|
return [];
|
|
}
|
|
|
|
return json_decode($data, true) ?? [];
|
|
}
|
|
|
|
public function setMappingData(string $type, int $id, array $data): void
|
|
{
|
|
$data = array_merge(
|
|
$this->getMappingData($type, $id),
|
|
$data
|
|
);
|
|
|
|
sqlQueryBuilder()
|
|
->update('sap_'.$type.'s')
|
|
->directValues(
|
|
[
|
|
'data' => json_encode($data),
|
|
]
|
|
)
|
|
->where(
|
|
Operator::equals(
|
|
[
|
|
'id_'.$type => $id,
|
|
]
|
|
)
|
|
)
|
|
->execute();
|
|
}
|
|
|
|
public function createMapping(string $type, $sapId, int $id): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->insert('sap_'.$type.'s')
|
|
->directValues(
|
|
[
|
|
'id_sap' => $sapId,
|
|
'id_'.$type => $id,
|
|
]
|
|
)
|
|
->execute();
|
|
}
|
|
|
|
public function getMapping(string $type, $sapId): ?int
|
|
{
|
|
$id = sqlQueryBuilder()
|
|
->select('id_'.$type)
|
|
->from('sap_'.$type.'s')
|
|
->where(Operator::equals(['id_sap' => $sapId]))
|
|
->execute()->fetchColumn();
|
|
|
|
if (!$id) {
|
|
return null;
|
|
}
|
|
|
|
return (int) $id;
|
|
}
|
|
|
|
public function getSapId(string $type, int $id): ?string
|
|
{
|
|
$sapId = sqlQueryBuilder()
|
|
->select('id_sap')
|
|
->from('sap_'.$type.'s')
|
|
->where(Operator::equals(['id_'.$type => $id]))
|
|
->execute()->fetchOne();
|
|
|
|
if (!$sapId) {
|
|
return null;
|
|
}
|
|
|
|
return $sapId;
|
|
}
|
|
|
|
public function deleteMapping(string $type, int $id): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->delete('sap_'.$type.'s')
|
|
->where(
|
|
Operator::equals(
|
|
[
|
|
'id_'.$type => $id,
|
|
]
|
|
)
|
|
)
|
|
->execute();
|
|
}
|
|
|
|
public function deleteItemMapping($sapId, int $productId, ?int $variationId = null): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->delete('sap_products')
|
|
->where(
|
|
Operator::equals(
|
|
[
|
|
'id_sap' => $sapId,
|
|
'id_product' => $productId,
|
|
'id_variation' => $variationId,
|
|
]
|
|
)
|
|
)->execute();
|
|
}
|
|
|
|
public function createItemMapping($sapId, int $productId, ?int $variationId = null): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->insert('sap_products')
|
|
->directValues(
|
|
[
|
|
'id_sap' => $sapId,
|
|
'id_product' => $productId,
|
|
'id_variation' => $variationId,
|
|
]
|
|
)->execute();
|
|
}
|
|
|
|
public function getItemMapping($sapId): ?array
|
|
{
|
|
static $itemMappingCache;
|
|
|
|
// cache reset
|
|
if ($itemMappingCache !== null && $itemMappingCache['timestamp'] < time()) {
|
|
$itemMappingCache = null;
|
|
}
|
|
|
|
// init cache
|
|
if ($itemMappingCache === null) {
|
|
$itemMappingCache = [
|
|
'data' => null,
|
|
'timestamp' => time() + 1800,
|
|
];
|
|
}
|
|
|
|
if (($itemMappingCache['data'] ?? null) === null) {
|
|
$itemMappingCache['data'] = Mapping::mapKeys(sqlQueryBuilder()
|
|
->select('id_sap, id_product, id_variation')
|
|
->from('sap_products')
|
|
->sendToMaster()
|
|
->execute(), fn ($k, $v) => [$v['id_sap'], [$v['id_product'], $v['id_variation']]]);
|
|
}
|
|
|
|
// fetch data and cache it
|
|
if (!($itemMappingCache['data'][$sapId] ?? null)) {
|
|
$data = sqlQueryBuilder()
|
|
->select('id_product, id_variation')
|
|
->from('sap_products')
|
|
->where(Operator::equals(['id_sap' => $sapId]))
|
|
->orderBy('id_variation', 'DESC')
|
|
->sendToMaster()
|
|
->execute()->fetchAssociative();
|
|
|
|
if ($data) {
|
|
$itemMappingCache['data'][$sapId] = [(int) $data['id_product'], $data['id_variation'] ? (int) $data['id_variation'] : null];
|
|
}
|
|
}
|
|
|
|
return $itemMappingCache['data'][$sapId] ?? null;
|
|
}
|
|
|
|
public function getItemSapId(int $productId, ?int $variationId = null): ?string
|
|
{
|
|
$sapId = sqlQueryBuilder()
|
|
->select('id_sap')
|
|
->from('sap_products')
|
|
->where(
|
|
Operator::equalsNullable(
|
|
[
|
|
'id_product' => $productId,
|
|
'id_variation' => $variationId,
|
|
]
|
|
)
|
|
)
|
|
->execute()->fetchColumn();
|
|
|
|
if (!$sapId) {
|
|
return null;
|
|
}
|
|
|
|
return $sapId;
|
|
}
|
|
|
|
public function formatZip(string $zip, string $country): string
|
|
{
|
|
$formatZipPL = function (string $zip): string {
|
|
$parts = explode('-', $zip);
|
|
$zip = implode('', $parts);
|
|
|
|
return substr($zip, 0, 2).'-'.substr($zip, 2);
|
|
};
|
|
|
|
$formatZipNL = function (string $zip): string {
|
|
$zip = str_replace(' ', '', $zip);
|
|
|
|
// nizozemskej zip na konci muze/nemusi mit dve pismena
|
|
$zipLetters = is_numeric(substr($zip, -2)) ? ' ' : substr($zip, -2);
|
|
|
|
return substr($zip, 0, 4).' '.$zipLetters;
|
|
};
|
|
|
|
$countryFormatZip = match ($country) {
|
|
'DE', 'RO', 'AT', 'IT', 'FR', 'ES', 'BE', 'HR', 'HU' => str_replace(' ', '', $zip),
|
|
'PL' => $formatZipPL($zip),
|
|
'NL' => $formatZipNL($zip),
|
|
default => null,
|
|
};
|
|
|
|
if ($countryFormatZip) {
|
|
return $countryFormatZip;
|
|
}
|
|
|
|
if (strlen($zip) !== 6) {
|
|
$zip = substr($zip, 0, 3).' '.substr($zip, 3);
|
|
}
|
|
|
|
return $zip;
|
|
}
|
|
|
|
public function formatPhone(string $phone): string
|
|
{
|
|
return str_replace(['+420', '+421'], ['', ''], $phone);
|
|
}
|
|
|
|
public function formatCity(string $city): string
|
|
{
|
|
return mb_substr($city, 0, 40);
|
|
}
|
|
|
|
public function updateProductPricesByVariations(): 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.figure = "Y"), p.price);');
|
|
}
|
|
|
|
public function recalculateUpdatedProducts(): void
|
|
{
|
|
sqlGetConnection()->transactional(function () {
|
|
$qb = sqlQueryBuilder()
|
|
->select('id, id_product')
|
|
->from('stores_items')
|
|
->where(Operator::equals(['sap_updated' => 1]))
|
|
->forUpdate();
|
|
|
|
$doUpdateItems = function (array $updateItems) {
|
|
$this->recalculateStores(array_unique(array_map(fn ($x) => $x['id_product'], $updateItems)));
|
|
sqlQueryBuilder()
|
|
->update('stores_items')
|
|
->directValues(['sap_updated' => 0])
|
|
->where(Operator::inIntArray(array_unique(array_map(fn ($x) => $x['id'], $updateItems)), 'id'))
|
|
->execute();
|
|
};
|
|
|
|
$updateItems = [];
|
|
foreach ($qb->execute() as $item) {
|
|
$updateItems[] = $item;
|
|
|
|
if (count($updateItems) >= 1000) {
|
|
$doUpdateItems($updateItems);
|
|
$updateItems = [];
|
|
}
|
|
}
|
|
|
|
if (!empty($updateItems)) {
|
|
$doUpdateItems($updateItems);
|
|
}
|
|
});
|
|
}
|
|
|
|
public function recalculateStores(array $productIds = []): void
|
|
{
|
|
sqlGetConnection()->transactional(function () use ($productIds) {
|
|
$getQuantitySubQuery = function (bool $variations = false) {
|
|
$qb = sqlQueryBuilder()
|
|
->select('COALESCE(SUM(quantity), 0)')
|
|
->from('stores_items', 'si')
|
|
->join('si', 'stores', 's', 's.id = si.id_store')
|
|
->andWhere(Operator::equals(['s.figure' => 'Y']));
|
|
|
|
if ($variations) {
|
|
$qb->andWhere('si.id_product = pv.id_product AND si.id_variation = pv.id');
|
|
} else {
|
|
$qb->andWhere('si.id_product = p.id AND si.id_variation IS NULL');
|
|
}
|
|
|
|
return $qb->getRunnableSQL();
|
|
};
|
|
|
|
$productsQuantitySubQuery = $getQuantitySubQuery();
|
|
|
|
$productsQb = sqlQueryBuilder()
|
|
->update('products', 'p')
|
|
->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
|
|
->set('p.in_store', "({$productsQuantitySubQuery})")
|
|
->andWhere("in_store != ({$productsQuantitySubQuery})");
|
|
|
|
if (!empty($productIds)) {
|
|
$productsQb->andWhere(Operator::inIntArray($productIds, 'p.id'));
|
|
}
|
|
|
|
$productsQb->execute();
|
|
|
|
$variationsQuantitySubQuery = $getQuantitySubQuery(true);
|
|
|
|
$variationsQb = sqlQueryBuilder()
|
|
->update('products_variations', 'pv')
|
|
->join('pv', 'sap_products', 'sp', 'sp.id_product = pv.id_product AND sp.id_variation = pv.id')
|
|
->set('pv.in_store', "({$variationsQuantitySubQuery})")
|
|
->andWhere("pv.in_store != ({$variationsQuantitySubQuery})");
|
|
|
|
if (!empty($productIds)) {
|
|
$variationsQb->andWhere(Operator::inIntArray($productIds, 'pv.id_product'));
|
|
}
|
|
|
|
$variationsQb->execute();
|
|
|
|
\Variations::recalcInStore();
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Skrývání produktů, které se delší dobu neaktualizovaly.
|
|
*
|
|
* Pokud SAP produkt nebyl přes synchronizace aktualizován více jak X hodin, tak produkt automaticky skryjeme.
|
|
* SAP každou noc posílá do synchronizace celý katalog a tímpádem jakmile něco není aktualizované, tak to bylo v SAPu
|
|
* z katalogu odebrané.
|
|
*/
|
|
public function updateProductsVisibility(int $hours = 50, ?array $deadlockedProducts = null): void
|
|
{
|
|
$productsToHide = sqlQueryBuilder()
|
|
->select('p.id, p.code, sp.date_updated as sap_date_updated')
|
|
->from('products', 'p')
|
|
->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
|
|
->andWhere(Operator::equals(['p.figure' => 'Y']))
|
|
->andWhere('TIMESTAMPDIFF(HOUR, sp.date_updated, NOW()) > '.$hours)
|
|
->andWhere('p.in_store > 0')
|
|
// pokud nemam prázdný pole nacpu podmínku jinak '1'
|
|
->andWhere(!empty($deadlockedProducts) ? Operator::not(Operator::inIntArray($deadlockedProducts, 'p.id')) : '1')
|
|
->sendToMaster()
|
|
->execute()->fetchAllAssociative();
|
|
// skryjeme produkty, ktere se vice jak X hodin neaktualizovaly
|
|
sqlQueryBuilder()
|
|
->update('products', 'p')
|
|
->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
|
|
->directValues(
|
|
[
|
|
'figure' => 'O',
|
|
]
|
|
)
|
|
->andWhere(Operator::equals(['p.figure' => 'Y']))
|
|
->andWhere('TIMESTAMPDIFF(HOUR, sp.date_updated, NOW()) > '.$hours)
|
|
->andWhere('p.in_store > 0')
|
|
// pokud nemam prázdný pole nacpu podmínku jinak '1'
|
|
->andWhere(!empty($deadlockedProducts) ? Operator::not(Operator::inIntArray($deadlockedProducts, 'p.id')) : '1')
|
|
->execute();
|
|
// // zobrazime produktu, ktere jsou aktualizovane a jsou v prodeji ukoncen
|
|
// sqlQueryBuilder()
|
|
// ->update('products', 'p')
|
|
// ->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
|
|
// ->directValues(
|
|
// [
|
|
// 'figure' => 'Y',
|
|
// ]
|
|
// )
|
|
// ->andWhere(Operator::equals(['p.figure' => 'O']))
|
|
// ->andWhere('TIMESTAMPDIFF(HOUR, sp.date_updated, NOW()) < '.$hours)
|
|
// ->andWhere('p.in_store > 0')
|
|
// ->execute();
|
|
|
|
if (!empty($productsToHide)) {
|
|
$this->addActivityLog(
|
|
sprintf('Byly skryty produkty, které nebyly ve zpracovaném FULL katalogu: %s', count($productsToHide)),
|
|
['products' => $productsToHide],
|
|
ActivityLog::SEVERITY_NOTICE
|
|
);
|
|
}
|
|
}
|
|
|
|
public function prepareSAPItems(object $data): array
|
|
{
|
|
$items = [];
|
|
|
|
if (isset($data->item)) {
|
|
if (!is_array($data->item)) {
|
|
$items = [$data->item];
|
|
} else {
|
|
$items = $data->item ?? [];
|
|
}
|
|
}
|
|
|
|
return $items;
|
|
}
|
|
|
|
public function synchronize(...$types): void
|
|
{
|
|
foreach ($types as $type) {
|
|
$synchronizer = $this->sapLocator->get($type);
|
|
|
|
try {
|
|
$synchronizer->processToSAP();
|
|
} catch (\Throwable $e) {
|
|
if (isDevelopment()) {
|
|
throw $e;
|
|
}
|
|
|
|
if (!($e instanceof SAPException)) {
|
|
$this->sentryLogger->captureException($e);
|
|
}
|
|
|
|
$this->addActivityLog(
|
|
sprintf('Synchronizace typu %s nebyla úspěšná', $type),
|
|
[
|
|
'message' => $e->getMessage(),
|
|
]
|
|
);
|
|
}
|
|
}
|
|
}
|
|
|
|
public function synchronizeSales(?callable $processItemCallback = null): void
|
|
{
|
|
$synchronizer = $this->sapLocator->get(POSOrderSynchronizer::getType());
|
|
|
|
if ($processItemCallback) {
|
|
$synchronizer->setProcessItemCallback($processItemCallback);
|
|
}
|
|
|
|
$synchronizer->process([]);
|
|
}
|
|
|
|
public function updateUser(\User $user, object $customer): void
|
|
{
|
|
// nactu si karty uzivatele
|
|
$cards = [];
|
|
if (!empty($customer->PersonalData->PartnerCards)) {
|
|
foreach ($this->prepareSAPItems($customer->PersonalData->PartnerCards) as $card) {
|
|
$cards[] = $card->PartnerCard;
|
|
}
|
|
}
|
|
|
|
if ($user->figure !== 'Y') {
|
|
$userFields = [
|
|
'figure' => 'Y',
|
|
];
|
|
|
|
if (!isset($user->date_reg)) {
|
|
$userFields['date_reg'] = (new \DateTime())->format('Y-m-d H:i:s');
|
|
}
|
|
|
|
sqlQueryBuilder()
|
|
->update('users')
|
|
->directValues($userFields)
|
|
->where(Operator::equals(['id' => $user->id]))
|
|
->execute();
|
|
}
|
|
|
|
$user->setCustomData('sap', [
|
|
'discountIndex' => $customer->PersonalData->DiscountIndex,
|
|
'cards' => $cards,
|
|
]);
|
|
|
|
$this->updateUserDiscount($user, $customer->PersonalData->DiscountIndex);
|
|
}
|
|
|
|
public function updateUserDiscount(\User $user, string $discountIndex): void
|
|
{
|
|
// update user discount index
|
|
$priceLevelId = sqlQueryBuilder()
|
|
->select('id')
|
|
->from('price_levels')
|
|
->where(Operator::equals(['descr' => $discountIndex]))
|
|
->execute()->fetchColumn();
|
|
|
|
sqlGetConnection()->transactional(function () use ($user, $priceLevelId) {
|
|
sqlQueryBuilder()
|
|
->delete('users_dealer_price_level')
|
|
->where(Operator::equals(
|
|
[
|
|
'id_user' => $user->id,
|
|
]
|
|
))->execute();
|
|
|
|
if ($priceLevelId) {
|
|
sqlQueryBuilder()
|
|
->insert('users_dealer_price_level')
|
|
->directValues(
|
|
[
|
|
'id_user' => $user->id,
|
|
'id_price_level' => $priceLevelId,
|
|
]
|
|
)->execute();
|
|
}
|
|
});
|
|
}
|
|
|
|
public function cleanupJsonFiles(): void
|
|
{
|
|
$pathFinder = PathFinder::getService();
|
|
|
|
foreach (scandir($pathFinder->getTmpDir()) as $file) {
|
|
if (preg_match('/(.*)_(.*)_FULL_(.*).json$/', $file)) {
|
|
unlink($pathFinder->tmpPath($file));
|
|
}
|
|
}
|
|
}
|
|
|
|
public function addActivityLog(string $message, array $data = [], string $severity = ActivityLog::SEVERITY_ERROR): bool
|
|
{
|
|
$this->activityLog->addActivityLog($severity, ActivityLog::TYPE_SYNC, sprintf('[SAP] %s', $message), $data);
|
|
|
|
return true;
|
|
}
|
|
|
|
public function getLogger(): SynchronizationLogger
|
|
{
|
|
if (!$this->logger) {
|
|
$this->logger = $this->loggerFactory->create(
|
|
[SAPException::class],
|
|
'SAP'
|
|
);
|
|
}
|
|
|
|
return $this->logger;
|
|
}
|
|
}
|