0

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 Namespace\Updater\Console\Command;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

use Magento\Framework\App\Filesystem\DirectoryList;
use Magento\Framework\App\ResourceConnection;

class Tierprices extends Command
{

    const NAME_ARGUMENT = "name";
    const NAME_OPTION = "option";

    /*
     * @var \Magento\Framework\ObjectManagerInterface
     */
    private $objectManager;

    protected $directoryList;

    protected $resourceConnection;

    protected $_coreSession;


    public function __construct(
        \Magento\Framework\ObjectManagerInterface $objectmanager,
        DirectoryList $directoryList,
        ResourceConnection $resourceConnection,
        \Magento\Framework\Session\SessionManagerInterface $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();
    }




}

1 Answer 1

0

I had an error with the logic of the class. Before the update I truncated the catalog_product_entity_tier_price table but it was wrong. The correct way to achieve this is to delete only the products where qty > 1. Then a final result would be:

//Vaciamos la tabla
        $connection     = $this->_getWriteConnection();
        $table = $connection->getTableName('catalog_product_entity_tier_price');
        $query_vaciar = "DELETE FROM ". $table .' WHERE qty > 1';
        $resultado = $connection->query($query_vaciar);

And that did the trick. Hope this helps someone else.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.