300 lines
10 KiB
PHP
300 lines
10 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace External\HannahBundle\Util;
|
|
|
|
use Doctrine\DBAL\Connection;
|
|
use External\HannahBundle\Resources\script\ImportProductPositionScript;
|
|
use External\HannahBundle\Util\FTP\FTPClient;
|
|
use KupShop\CatalogBundle\Product\ProductFinder;
|
|
use KupShop\KupShopBundle\Util\System\PathFinder;
|
|
use KupShop\PricelistBundle\Util\PriceListWorker;
|
|
use League\Csv\Reader;
|
|
use Query\Operator;
|
|
|
|
class OCImportUtil
|
|
{
|
|
public function __construct(
|
|
private readonly Configuration $configuration,
|
|
private readonly FTPClient $ftpClient,
|
|
private readonly ProductFinder $productFinder,
|
|
private readonly ?PriceListWorker $priceListWorker,
|
|
) {
|
|
}
|
|
|
|
public function importProductPositions(bool $progress = false): void
|
|
{
|
|
$script = new ImportProductPositionScript();
|
|
$script->dispatch(json_encode([
|
|
'file' => $this->getFileMap()[$this->configuration->getShopId()],
|
|
'progress' => $progress,
|
|
]));
|
|
}
|
|
|
|
public function importProductPrices(): void
|
|
{
|
|
if (!$this->priceListWorker) {
|
|
return;
|
|
}
|
|
|
|
$folders = [
|
|
'/' => 'wpj_prices_(\d+).csv',
|
|
'/marketplace' => 'expanze_ceny_(\d+).csv',
|
|
];
|
|
|
|
$this->ftpClient->withCredentials(
|
|
['wpj_prices', '5T2xNu14'],
|
|
fn () => $this->processProductPricesFolders($folders),
|
|
'/'
|
|
);
|
|
}
|
|
|
|
private function processProductPricesFolders(array $folders): void
|
|
{
|
|
// process each folder
|
|
foreach ($folders as $folder => $regexp) {
|
|
// load prices files
|
|
if (!($files = $this->ftpClient->getPricesFiles($folder))) {
|
|
continue;
|
|
}
|
|
|
|
$file = end($files);
|
|
|
|
if (!preg_match('/'.$regexp.'/', $file) || str_contains($file, '.DONE')) {
|
|
continue;
|
|
}
|
|
|
|
$pathInfo = pathinfo($file);
|
|
$tmpFilePath = PathFinder::getService()->tmpPath($pathInfo['basename']);
|
|
|
|
// copy to tmp file
|
|
$this->ftpClient->copyFile($file, $tmpFilePath);
|
|
|
|
$csv = Reader::createFromPath($tmpFilePath);
|
|
$csv->setHeaderOffset(0);
|
|
|
|
// process CSV in chunks
|
|
foreach ($csv->chunkBy(1000) as $chunk) {
|
|
$updatePriceListVariationsData = [];
|
|
// process each item
|
|
foreach ($chunk as $row) {
|
|
if (!($productId = $this->productFinder->getProductIdByCode($row['code']))) {
|
|
continue;
|
|
}
|
|
|
|
$data = [];
|
|
foreach ($row as $key => $value) {
|
|
if ($key === 'code') {
|
|
continue;
|
|
}
|
|
|
|
if (str_contains($key, 'dmoc')) {
|
|
continue;
|
|
}
|
|
|
|
$data[$key] = [
|
|
'basePrice' => $row["dmoc_{$key}"] ?? null,
|
|
'actionPrice' => $value,
|
|
];
|
|
}
|
|
|
|
$productVat = $this->getProductVat($productId);
|
|
|
|
foreach ($data as $key => $item) {
|
|
if (!($priceListId = $this->getPriceListByKey($key))) {
|
|
continue;
|
|
}
|
|
|
|
$actionPrice = toDecimal($item['actionPrice']);
|
|
if ($actionPrice->isNegative()) {
|
|
$actionPrice = \DecimalConstants::zero();
|
|
}
|
|
|
|
if (!($basePrice = ($item['basePrice'] ? toDecimal($item['basePrice']) : null))) {
|
|
$basePrice = $this->priceListWorker->getPriceListItem(
|
|
$priceListId,
|
|
$productId
|
|
)['price'] ?? null;
|
|
|
|
if (!$basePrice) {
|
|
continue;
|
|
}
|
|
|
|
$basePrice = toDecimal($basePrice)->addVat($productVat);
|
|
}
|
|
|
|
// calculate discount
|
|
if ($basePrice->isZero()) {
|
|
$discount = \DecimalConstants::zero();
|
|
} else {
|
|
$discount = toDecimal($basePrice)->sub($actionPrice)->div($basePrice)->mul(\DecimalConstants::hundred());
|
|
}
|
|
|
|
if (!$discount->isPositive()) {
|
|
$this->updatePriceList(
|
|
$priceListId,
|
|
$productId,
|
|
null,
|
|
$actionPrice->removeVat($productVat),
|
|
);
|
|
|
|
$updatePriceListVariationsData[$priceListId][] = $productId;
|
|
|
|
continue;
|
|
}
|
|
|
|
$this->updatePriceList(
|
|
$priceListId,
|
|
$productId,
|
|
null,
|
|
$basePrice->removeVat($productVat),
|
|
$discount
|
|
);
|
|
|
|
$updatePriceListVariationsData[$priceListId][] = $productId;
|
|
}
|
|
}
|
|
|
|
// do mass update of variations in pricelists
|
|
$this->updatePriceListVariations($updatePriceListVariationsData);
|
|
}
|
|
|
|
foreach ($files as $ftpFile) {
|
|
$this->ftpClient->renameFile($ftpFile, $ftpFile.'.DONE');
|
|
}
|
|
|
|
unlink($tmpFilePath);
|
|
}
|
|
}
|
|
|
|
private function getPriceListByKey(string $key): ?int
|
|
{
|
|
static $priceLists = [];
|
|
|
|
if (!($priceLists[$key] ?? false)) {
|
|
$priceListData = $this->getPriceListData($key);
|
|
|
|
$priceListId = sqlQueryBuilder()
|
|
->select('id')
|
|
->from('pricelists')
|
|
->where(Operator::equals(
|
|
[
|
|
'JSON_VALUE(data, "$.sap.import_key")' => $key,
|
|
]
|
|
))
|
|
->sendToMaster()
|
|
->execute()->fetchOne();
|
|
|
|
if (!$priceListId) {
|
|
$priceListId = sqlGetConnection()->transactional(function () use ($priceListData, $key) {
|
|
sqlQueryBuilder()
|
|
->insert('pricelists')
|
|
->directValues([
|
|
'name' => $priceListData['name'],
|
|
'currency' => $priceListData['currency'],
|
|
'data' => json_encode([
|
|
'sap' => [
|
|
'import_key' => $key,
|
|
'language' => $priceListData['language'],
|
|
'currency' => $priceListData['currency'],
|
|
],
|
|
]),
|
|
])
|
|
->execute();
|
|
});
|
|
}
|
|
|
|
$priceLists[$key] = $priceListId;
|
|
}
|
|
|
|
return $priceLists[$key];
|
|
}
|
|
|
|
/**
|
|
* Nacte data o ceniku, abych vedel, ktery cenik hledat/zalozit.
|
|
*/
|
|
private function getPriceListData(string $priceListKey): array
|
|
{
|
|
static $currencyMap = [
|
|
'zl' => 'PLN',
|
|
'lei' => 'RON',
|
|
'ft' => 'HUF',
|
|
];
|
|
|
|
$parts = explode('_', $priceListKey);
|
|
|
|
$priceListName = 'Výchozí ceník';
|
|
if (count($parts) == 3) {
|
|
$priceListName = match ($parts[0] ?? '') {
|
|
'amz' => 'Amazon',
|
|
'kauf' => 'Kaufland',
|
|
default => $parts[0],
|
|
};
|
|
|
|
if (empty($priceListName)) {
|
|
throw new \RuntimeException('PriceList name cannot be empty!');
|
|
}
|
|
|
|
array_shift($parts);
|
|
}
|
|
|
|
[$language, $currency] = $parts;
|
|
|
|
return [
|
|
'name' => "[{$language}] {$priceListName}",
|
|
'language' => $language,
|
|
'currency' => strtoupper($currencyMap[$currency] ?? $currency),
|
|
];
|
|
}
|
|
|
|
private function getProductVat(int $productId): float
|
|
{
|
|
$vatId = sqlQueryBuilder()
|
|
->select('vat')
|
|
->from('products')
|
|
->where(Operator::equals(['id' => $productId]))
|
|
->execute()->fetchOne();
|
|
|
|
return (float) getVat($vatId);
|
|
}
|
|
|
|
private function updatePriceList(int $priceListId, int $productId, ?int $variationId, \Decimal $price, ?\Decimal $discount = null): void
|
|
{
|
|
sqlQueryBuilder()
|
|
->insert('pricelists_products')
|
|
->directValues([
|
|
'id_pricelist' => $priceListId,
|
|
'id_product' => $productId,
|
|
'id_variation' => $variationId,
|
|
'price' => $price,
|
|
'discount' => $discount,
|
|
])
|
|
->onDuplicateKeyUpdate(['price', 'discount'])
|
|
->execute();
|
|
}
|
|
|
|
private function updatePriceListVariations(array $data): void
|
|
{
|
|
foreach ($data as $priceListId => $productIds) {
|
|
sqlQuery('INSERT INTO pricelists_products (id_pricelist, id_product, id_variation, price)
|
|
SELECT :pricelistId, pv.id_product, pv.id, pp.price
|
|
FROM products_variations pv
|
|
JOIN pricelists_products pp ON pp.id_product = pv.id_product AND pp.id_pricelist = :pricelistId AND id_variation IS NULL
|
|
WHERE pv.id_product IN (:productIds)
|
|
ON DUPLICATE KEY UPDATE price=VALUES(price)',
|
|
['productIds' => $productIds, 'pricelistId' => $priceListId], ['productIds' => Connection::PARAM_INT_ARRAY]);
|
|
}
|
|
}
|
|
|
|
public static function getFileMap(): array
|
|
{
|
|
return [
|
|
Configuration::SHOP_ROCKPOINT => '/rockpoint_serazeno.csv',
|
|
Configuration::SHOP_HANNAH => '/hannah_serazeno.csv',
|
|
Configuration::SHOP_RAFIKI => '/rafiki_serazeno.csv',
|
|
Configuration::SHOP_KEEN => '/keen_serazeno.csv',
|
|
];
|
|
}
|
|
}
|