Option value position (select value sort order)

This Magento shell script sorts the values of a given attribute after the order of values in a given CSV file (sets option value position).

<?php
/**
 *
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * It is  available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 *
 *
 * @category   AskSheldon
 * @package    AskSheldon_Misc
 * @copyright  Copyright (c) 2016 Marcel Lange (https://www.ask-sheldon.com)
 * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 * @author     Marcel Lange <info@ask-sheldon.com>)
 *
 * Changes the sorting of asksheldon_size attribute options (sets sort order)
 * call it with param 'iknowwhatido=yes' to make it work!
 *
 */
require_once('../AskSheldon_Shell_Abstract.php');

class AskSheldon_Misc_Attribute_Option_Sorter extends AskSheldon_Shell_Abstract
{
    const TARGET_ATTRIBUTE = 'asksheldon_size';
    const REFERENCESTORE = 2;

    private $_oWriteConnection = null;
    private $_sOptionTable = null;
    private $_sOptionLableTable = null;


    /*
     * Starter
     * */
    public function run()
    {
        error_reporting(E_ALL);
        ini_set('display_errors', 1);

        if (!$this->getArg('iknowwhatido') || $this->getArg('iknowwhatido') != 'yes') {
            echo $this->usageHelp();
            echo "\n DEACTIVATED (call it with param '-iknowwhatido yes' to make it work!) \n";
            return -1;
        }
        if (!$this->getArg('inputfile')) {
            echo $this->usageHelp();
            echo "\n You have to give me the right params master! \n";
            return -1;
        }

        $rFile = fopen($this->getArg('inputfile'), 'r');
        if ($rFile) {
            $iCount = 1;
            while (($aData = fgetcsv($rFile)) !== false) {
                $sSizeDE = $aData[0];
                $this->_setAttributeOptionSorting($sSizeDE, $iCount++);
            }
        }
    }

    /**
     * Retrieve Usage Help Message
     *
     */
    public function usageHelp()
    {
        return <<<USAGE
Usage:  php -f update_size_attribute_option_order.php -iknowwhatido [options]
  
  iknowwhatido      lifeguard flag
  inputfile         CSV-file with size sorting as the first and only column without header line!!!
  help              This help

USAGE;
    }

    private function _setAttributeOptionSorting($sSize, $iPosition)
    {

        $oSelect = new Varien_Db_Select($this->_getWriteConnection());
        $oSelect->from(
            ['eao' => $this->_getOptionTable()],
            ['option_id']
        )
            ->join(
                ['eaov' => $this->_getOptionLabelTable()],
                "eao.option_id = eaov.option_id AND eaov.store_id = " . self::REFERENCESTORE,
                ''
            )
            ->where('eao.attribute_id = ?', $this->_getAttibuteId())
            ->where('eaov.value = ?', $sSize);

        $aResult = $this->_getWriteConnection()->fetchCol($oSelect);
        foreach ($aResult as $iOptionId) {
            $this->_getWriteConnection()->update(
                $this->_getOptionTable(),
                ['sort_order' => $iPosition],
                $this->_getWriteConnection()->quoteInto('option_id = ?', $iOptionId)
            );
        }
    }

    /**
     * Returs a write connection
     * @return Varien_Db_Adapter_Pdo_Mysql
     */
    private function _getWriteConnection()
    {
        if (is_null($this->_oWriteConnection)) {
            $this->_oWriteConnection = Mage::getSingleton('core/resource')->getConnection('core_write');
        }
        return $this->_oWriteConnection;
    }

    /**
     * @return string table name
     */
    private function _getOptionTable()
    {
        if (is_null($this->_sOptionTable)) {
            $this->_sOptionTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option');
        }
        return $this->_sOptionTable;
    }

    /**
     * @return string table name
     */
    private function _getOptionLabelTable()
    {
        if (is_null($this->_sOptionLableTable)) {
            $this->_sOptionLableTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option_value');
        }
        return $this->_sOptionLableTable;
    }

    /**
     * @return int ID of product attribute asksheldon_size
     */
    private function _getAttibuteId()
    {
        return Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, self::TARGET_ATTRIBUTE)->getId();
    }
}

$oOptionSorter = new AskSheldon_Misc_Attribute_Option_Sorter();
$oOptionSorter->run();

So it is not necessary to set the values via BE-Interface. The value matching to find the respective option-id is done by using the value of the Storeview specified in REFERENCESTORE and the attribute for that the order should be set is determined via TARGET_ATTRIBUTE.
This is how you call the scrip via shell within /mageroot/shell/:

$> php update_size_attribute_option_order.php -iknowwhatido yes -inputfile ../var/import/akssheldon_size_attribute_option_order_import.csv

The CSV has to look like that:

01
02
03
04
8.5-9
9.5-10
10.5-11
19-22
23-24
23-26
25-26
27-28
27-30
29-30
31-32
31-34
33-34
35-36
35-36 W1
35-36 W2
35-38
35-38 W1
35-38 W2
36-38
36-41
37-38
37-38 W1
37-38 W2
38-40
39-40
39-40 W1
39-40 W2
39-40 W3
39-40 W4
39-41
39-42
39-42 W1
39-42 W2
39-42 W3
39-42 W4
40-42
40-46
41-42
41-42 W1
41-42 W2
41-42 W3
41-42 W4
42-43
42-44
43-44
43-44 W3
43-44 W4
43-46
43-46 W3
43-46 W4
44-45
44-46
45-46
45-46 W3
45-46 W4
46
46-48
46-50
47-48
47-50
48
48-50
49-50
50
50-56
51-52
52
54
56
58
62-68
74-80
80-92
98-104
110-116
122-128
134-140
134-146
146-152
152-164
158-164
170-176
ONESIZE
CALF 1
CALF 2
CALF 3
CALF 4
L
L  44-46
L-XL
M
M  40-42
M-L
S
S  36-38
S-M
XL
XL 46-48
XS
XS 34-36
XS-S
XXL
3XL
4XL

One thought on “Option value position (select value sort order)

Leave a Reply

Your email address will not be published.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.