I need to update or create tier prices in bulk. I’ve created a custom command and what I do is to update the table ‘catalog_product_entity_tier_price’ directly with a query and the info comming from a CSV file. I know is not the best practice but I prefer to do this way because is faster and there’s only one table involved (as far as I know). But once I update/create the tier prices information it doesn’t shows as expected on the admin or frontend side. I’ve been digging in to the database to know what other table could be involved but not have luck. I can’t find anything. Could somebody help me please?.
Here is my command… maybe it could help someone in the future:
<?php
declare(strict_types=1);
namespace NamespaceUpdaterConsoleCommand;
use SymfonyComponentConsoleCommandCommand;
use SymfonyComponentConsoleInputInputArgument;
use SymfonyComponentConsoleInputInputInterface;
use SymfonyComponentConsoleInputInputOption;
use SymfonyComponentConsoleOutputOutputInterface;
use MagentoFrameworkAppFilesystemDirectoryList;
use MagentoFrameworkAppResourceConnection;
class Tierprices extends Command
{
const NAME_ARGUMENT = "name";
const NAME_OPTION = "option";
/*
* @var MagentoFrameworkObjectManagerInterface
*/
private $objectManager;
protected $directoryList;
protected $resourceConnection;
protected $_coreSession;
public function __construct(
MagentoFrameworkObjectManagerInterface $objectmanager,
DirectoryList $directoryList,
ResourceConnection $resourceConnection,
MagentoFrameworkSessionSessionManagerInterface $coreSession
){
$this->objectManager = $objectmanager;
$this->directoryList = $directoryList;
$this->resourceConnection = $resourceConnection;
$this->_coreSession = $coreSession;
parent::__construct();
}
/**
* {@inheritdoc}
*/
protected function execute(
InputInterface $input,
OutputInterface $output
) {
$exitCode = 0;
$name = $input->getArgument(self::NAME_ARGUMENT);
$option = $input->getOption(self::NAME_OPTION);
$output->writeln("Hello " . $name);
//Precios
try {
$csvFile = $this->directoryList->getRoot() .'/var/consisa/import/Promocion.csv'; #EDIT - The path to import CSV file (Relative to Magento2 Root)
$csvData = $this->readCsvRows($csvFile);
$headers = $this->setHeaders(array_shift($csvData));
$count = 0;
//Vaciamos la tabla
$connection = $this->_getWriteConnection();
$table = $connection->getTableName('catalog_product_entity_tier_price');
$query_vaciar = "TRUNCATE TABLE ". $table;
$resultado = $connection->query($query_vaciar);
foreach($csvData as $_data) {
$count++;
$sku = $_data[$this->getIndex('Cve_Prod')];
$customer_group_id = 2; //Solo socios
$qty = $_data[$this->getIndex('CantidadMinima')];
$value = $_data[$this->getIndex('PrecioPromo')];
if ( ! $this->checkIfSkuExists($sku)) {
$message = '<error>'. $count .'. FAILURE:: Product with SKU (' . $sku . ') doesn't exist. </error>';
$this->mpLogAndPrint($message, $input, $output);
continue;
}
//Tier Prices
try {
//$this->updateTierPrices($sku, $price);
$this->updateTierPrices($sku, $customer_group_id, $qty, $value);
$message = '<info>'. $count . '. SUCCESS:: Promocion para SKU (' . $sku . ') cargada '.$qty.' '.$value.' </info>';
$this->mpLogAndPrint($message, $input, $output);
} catch(Exception $e) {
$message = '<error>'. $count . '. ERROR:: Promocion para SKU (' . $sku . ') Fallo => ' . $e->getMessage(). '</error>';
$this->mpLogAndPrint($message, $input, $output);
$exitCode = 1;
}
}
} catch (Exception $e) {
$message = '<error> EXCEPTION ::'. $e->getTraceAsString() .'</error>';
$this->mpLogAndPrint($message
);
$exitCode = 1;
}
return $exitCode;
}
/**
* {@inheritdoc}
*/
protected function configure()
{
$this->setName("namespace_updater:tierprices");
$this->setDescription("Actualiza los Tier Prices para las promociones de EGT");
$this->setDefinition([
new InputArgument(self::NAME_ARGUMENT, InputArgument::OPTIONAL, "Name"),
new InputOption(self::NAME_OPTION, "-a", InputOption::VALUE_NONE, "Option functionality")
]);
parent::configure();
}
public function _mpLog($data, $includeSep = false)
{
$fileName = $this->directoryList->getRoot() . '/var/log/updater-tier-prices.log';
if ($includeSep) {
$separator = str_repeat('=', 70);
file_put_contents($fileName, $separator . '<br />' . PHP_EOL, FILE_APPEND | LOCK_EX);
}
file_put_contents($fileName, $data . '<br />' .PHP_EOL, FILE_APPEND | LOCK_EX);
}
public function mpLogAndPrint($message, InputInterface $input, OutputInterface $output, $separator = false)
{
$this->_mpLog($message, $separator);
if (is_array($message) || is_object($message)) {
//print_r($message);
$output->writeln("$message");
} else {
//echo $message . '<br />' . PHP_EOL;
$output->writeln("$message.");
}
if ($separator) {
//echo str_repeat('=', 70) . '<br />' . PHP_EOL;
$salida = str_repeat('=', 70);
$output->writeln("$salida.");
}
}
public function getIndex($field)
{
$headers = $this->getHeaders();
$index = array_search($field, $headers);
/*
if ( !strlen($index)) {
$index = -1;
}*/
return $index;
}
public function readCsvRows($csvFile)
{
$rows = [];
$fileHandle = fopen($csvFile, 'r');
while(($row = fgetcsv($fileHandle, 0, ';', '"', '"')) !== false) {
$rows[] = $row;
}
fclose($fileHandle);
return $rows;
}
public function _getResourceConnection()
{
return $this->resourceConnection;
}
public function _getReadConnection()
{
return $this->_getConnection('core_read');
}
public function _getWriteConnection()
{
return $this->_getConnection('core_write');
}
public function _getConnection($type = 'core_read')
{
return $this->_getResourceConnection()->getConnection($type);
}
public function _getTableName($tableName)
{
return $this->_getResourceConnection()->getTableName($tableName);
}
public function _getAttributeId($attributeCode)
{
$connection = $this->_getReadConnection();
$sql = "SELECT attribute_id FROM " . $this->_getTableName('eav_attribute') . " WHERE entity_type_id = ? AND attribute_code = ?";
return $connection->fetchOne(
$sql,
[
$this->_getEntityTypeId('catalog_product'),
$attributeCode
]
);
}
public function _getEntityTypeId($entityTypeCode)
{
$connection = $this->_getConnection('core_read');
$sql = "SELECT entity_type_id FROM " . $this->_getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
return $connection->fetchOne(
$sql,
[
$entityTypeCode
]
);
}
public function _getIdFromSku($sku)
{
$connection = $this->_getConnection('core_read');
$sql = "SELECT entity_id FROM " . $this->_getTableName('catalog_product_entity') . " WHERE sku = ?";
return $connection->fetchOne(
$sql,
[
$sku
]
);
}
public function checkIfSkuExists($sku)
{
$connection = $this->_getConnection('core_read');
$sql = "SELECT COUNT(*) AS count_no FROM " . $this->_getTableName('catalog_product_entity') . " WHERE sku = ?";
return $connection->fetchOne($sql, [$sku]);
}
public function updateTierPrices($sku, $customer_group_id, $qty, $value, $website_id = 0)
{
$connection = $this->_getWriteConnection();
$entityId = $this->_getIdFromSku($sku);
$all_groups = ($customer_group_id > 0) ? 0 : 1;
$percentage_value = 'NULL';
$table = $connection->getTableName('catalog_product_entity_tier_price');
$message = '<info> dentro de... SUCCESS:: Promocion para SKU (' . $sku . ') '.$qty.' '.$value.' '.$customer_group_id.' </info>';
print_r($message);
$sql = "INSERT INTO " . $table . " (entity_id, all_groups, customer_group_id, qty, value, website_id, percentage_value) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE value=VALUES(value)";
$connection->query(
$sql,
[
$entityId,
$all_groups,
$customer_group_id,
$qty,
$value,
$website_id,
$percentage_value
]
);
}
public function setHeaders($headers){
$this->_coreSession->start();
$this->_coreSession->setHeaders($headers);
}
public function getHeaders(){
$this->_coreSession->start();
return $this->_coreSession->getHeaders();
}
public function unSetHeaders(){
$this->_coreSession->start();
return $this->_coreSession->unsHeaders();
}
}
