Price synchronization is a critical aspect of e-commerce applications, and its importance cannot be understated. Price synchronization in e-commerce applications is essential for maintaining consistency, competitiveness, and customer trust. It also streamlines operations, reduces errors, and enables businesses to adapt to market changes, ultimately leading to better customer experiences and increased revenue.
So it's important to have a fast and efficient synchronization process for syncing prices from another application to Magento. Let's see how we can achieve this.
This is the main function that needs to be called for importing the price. In this, we can see we have imported the price for a multiwebsite setup. If you have a system with a single website, you can remove the loop and use the rest of the code.
public function execute() {
$prices = [];
$websites = $this->systemConfig->getWebsites();
foreach ($websites as $website) {
if ($website->getId() == 0) continue;
$websiteId = $website->getId();
$this->currency = $this->getErpWebsiteCurrency($websiteId);
// Ignore this code, as this is used by us to get the prices from our ERP system.
$url = $this->systemConfig->getConfig('base_url', $websiteId) . self::URL_PATH;
try {
$response = $this->connector->post($url, $this->prepapreFinalPayload(), $this->getEntity());
if (isset($response["Soap:Envelope"]["Soap:Body"]["ReadMultiple_Result"]["ReadMultiple_Result"]["WS_SalesPrices_V3"])) {
$prices = $response["Soap:Envelope"]["Soap:Body"]["ReadMultiple_Result"]["ReadMultiple_Result"]["WS_SalesPrices_V3"];
if (!array_key_exists(0, $prices)) {
$prices = [$prices];
}
}
} catch (ErpConnectionException $e){
$message = __('ERP connection error for syncing price for website %1', $website->getCode());
$this->logger->error($message);
continue;
}
if (empty($prices)) {
$message = __('ERP Synced price data is empty for website %1', $website->getCode());
$this->logger->error($message);
continue;
}
// Main code for importing prices starts from here.
$storeIds = $website->getStoreIds();
foreach ($storeIds as $storeId) {
foreach ($prices as $price) {
$sku = $price['Item_No'];
if (in_array($sku, $this->productNotFound)) continue;
try {
$productRowId = $this->getEntityIdBySku($sku, $website->getId());
$this->insertAndUpdatePrice($sku, $productRowId, $amount, $storeId);
} catch (ProductNotFound $e) {
$this->failureCount++;
array_push($this->productNotFound, $sku);
$this->addToReport($sku, $amount, $storeId, $e->getMessage());
}
}
}
}
}The below function will check if the product exists in Magento or not. If the product doesn't exist, then we have to raise this in the logs. In the above function, you can see we have marked it as a failure and added data to the report.
NOTE: Since we have used this for the cloud version, we have used row_id, but if someone using this code for the community edition, they can return the entity_id from the function and use the code.
/**
* Check if the product exist in system or not.
* Return the row_id from catalog_product_entity
*
* @param $sku
* @param $price
* @param $specialPrice
* @param string $website
* @return string
*/
public function getEntityIdBySku($sku, $website)
{
$connection = $this->resourceConnection->getConnection();
if ($website) {
$query = $connection->select()->from(['main_table' => 'catalog_product_entity'], 'row_id')
->joinLeft(['mapping' => 'catalog_product_website'], 'mapping.product_id = main_table.entity_id')
->where('sku = "' . $sku . '" AND type_id = "simple" AND mapping.website_id = ' . $website)->limit(1);
$entity = $connection->fetchRow($query);
} else {
$query = $connection->select()->from('catalog_product_entity', 'row_id')
->where("type_id = 'simple' AND sku = (?)", $sku)
->limit(1);
$entity = $connection->fetchRow($query);
}
if (empty($entity)) {
throw new ProductNotFound();
}
return $entity['row_id'];
}The below function either inserts the prices or updates them at the store level. It will also delete the special price if needed.
/**
* Insert if the data is not available in database. Else update the data.
*
* @param $sku
* @param $entityId
* @param $prices
* @param $storeId
*/
public function insertAndUpdatePrice($sku, $rowId, $price, $storeId)
{
$connection = $this->resourceConnection->getConnection();
try {
$connection->beginTransaction();
$msg = [];
$tableName = 'catalog_product_entity_decimal';
$isPriceExist = $this->checkIfPriceExist($connection, $tableName, $rowId, $storeId);
if ($isPriceExist) {
$result = $this->updatePrice($connection, $tableName, $isPriceExist, $price);
} else {
$result = $this->insertPrice($connection, $tableName, $rowId, $storeId, $price);
}
if ($this->shouldDeleteSpecialPrice()) {
$deleteWhere = 'row_id = ' . $rowId . ' AND attribute_id = 78 AND store_id != ' . $storeId;
$this->deletePrice($connection, $tableName, $deleteWhere);
}
$connection->commit();
$this->successCount++;
$msg = is_string($result) ? $result : 'success';
$this->addToReport($sku, $price, $storeId, $msg);
} catch (\Exception $e) {
$connection->rollBack();
$this->logger->info(__('Error on updating price for %1 in store %1', [$sku, $storeId]));
$this->failureCount++;
$this->addToReport($sku, $price, $storeId, $e->getMessage());
}
}The below function looks very small but is very important because this checks if the prices are already available at the store level or not. Sometimes product prices are the same for all stores so we manage at the default store but when the business logic changes, we need to update different prices for different stores.
/**
* @param $connection
* @param $tableName
* @param $entityId
* @param $priceAttributeId
* @param $storeId
* @return array
*/
public function checkIfPriceExist($connection, $tableName, $rowId, $storeId)
{
$_price = $connection->select()
->from(['cped' => $tableName], ['value_id', 'value'])
->where('attribute_id = (?)', self::PRICE_ATTRIBUTE_ID)
->where('row_id = (?)', $rowId)
->where('store_id = (?)', $storeId);
return $connection->fetchRow($_price);
}As their names suggest, the functions listed below conduct various CRUD activities. The insert and delete functions are straightforward, but the update function is critical. If the price remains the same and we run an update query, the result will be the same, but it will also trigger the price indexer and create a record in the changelog table. So, in order to minimize indexing, we must skip the same pricing.
/**
* @param $connection
* @param $tableName
* @param $entityId
* @param $attributeId
* @param $storeId
* @param $price
* @return mixed
*/
public function insertPrice($connection, $tableName, $rowId, $storeId, $price)
{
return $connection->insert(
$tableName,
['attribute_id' => self::PRICE_ATTRIBUTE_ID, 'store_id' => $storeId, 'row_id' => $rowId, 'value' => $price]
);
}
/**
* @param $connection
* @param $tableName
* @param $isPriceExist
* @param $price
* @param $attrId
* @return mixed
*/
public function updatePrice($connection, $tableName, $isPriceExist, $price)
{
if ($isPriceExist['value'] != $price) {
$result = $connection->update(
$tableName,
['value' => $price],
['value_id = (?)' => $isPriceExist['value_id']]
);
} else {
$result = 'Price was same so skipped';
}
return $result;
}
/**
* @param $connection
* @param $tableName
* @param $entityId
* @param $attrId
* @param $storeId
*/
public function deletePrice($connection, $tableName, $where)
{
$connection->delete(
$tableName,
$where
);
}The above logic helps you to import the prices for a simple product in Magento.
NOTE: The below point needs to be considered before using the above code:
- The above code is been tested only for simple products. It can be used for other types of products with some alterations if needed.
- While testing, we imported prices for approximately 100k products and it took around 6 to 8 minutes to complete the process.
- As shown above, prices were imported from ERP but it basically needs an array of prices where it should have sku, price & website.
- Currently, it is updating only prices, but you can alter it to import special_price. Or you can contact us for the task.
Conclusion: Only having an import feature will not help the team to work more efficiently, rather the import should provide a line-level log. Line-level logs play a crucial role in maintaining data integrity, troubleshooting errors, and ensuring the quality of bulk data imports. They provide a detailed history of the import process and are indispensable for data validation, compliance, and performance optimization in data-intensive applications.
Comments
Post a Comment