Add SKU column Magento admin sales > order

Question

I've managed to get the fields I want showing up but I can't pull the data in e.g. the SKU data should come from the catalog > manage products.

Magento version in use is 1.5.1.0

Basically i need to pull data from 'customer/customer_collection' and 'sales/order_grid_collection' in the same class, is that possible?

Answer 1

Note: For not to override magento core functionality, You can create your own module and use the below code in Grid.php

Open app\code\core\Mage\Adminhtml\Block\Sales\Order\Grid.php

Replace the below function

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $collection->getSelect()->joinLeft(
            'sales_flat_order_item',
            '`sales_flat_order_item`.order_id=`main_table`.entity_id',
            array('skus'  => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.sku SEPARATOR ",")'))
    );
    $collection->getSelect()->group('entity_id');

    $this->setCollection($collection);
    return parent::_prepareCollection();
}

Now put the below code in _prepareColumns() function

$this->addColumn('sku', array(
    'header' => Mage::helper('sales')->__('SKU'),
    'index' => 'skus',
    'type' => 'text',
    'width' => '100px',
    'filter'    => false,
));

Source: http://chandreshrana.blogspot.in/2016/12/how-to-add-sku-column-in-sales-order.html

Answer 2

Step 1:  Create the Renderer directory under /app/code/core/Mage/Adminhtml/Block/Sales/Order

Step 2:
Create a Block File in /app/code/core/Mage/Adminhtml/Block/Sales/Order/Renderer/Red.php (You can use any name)
<?php


class
Mage_Adminhtml_Block_Sales_Order_Renderer_Red extends
Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract

    {
      public function render(Varien_Object $row)

       {

         //$value = $row->getData($this->getColumn()->getIndex());



        $order_id=$row->getData('increment_id');

        $order = Mage::getModel('sales/order')->loadByIncrementID($order_id);

        $items = $order->getAllItems();

        // print_r($items);exit();

            foreach ($items as $itemId => $item)

                {

                if($item->getSku())

                    {

                    $sku[] = $item->getSku();

                    }

                }

                if(count($sku))

                    {

                    $skutext = implode(',',$sku);

                    }

            $conbinetext=$skutext;

            return $conbinetext;

        }

    }



?>
Step 3: Add the Below Code in
/app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php

$this->addColumn('Shopby',


  array(


  'header'=>Mage::helper('catalog')->__('Sku'),

  'index' => 'shopby',

  'filter'=> false,


  'sortable'=>false,

  'renderer'=> 'Mage_Adminhtml_Block_Sales_Order_Renderer_Red',// THIS IS WHAT THIS POST IS ALL ABOUT

  ));

Answer 3

I have followed the completed step which were mentioned above in "seanbreeden" post https://stackoverflow.com/posts/15589455/revisions

But for the SKU search functionality I added the one line in this function

public function filter_skus($collection, $column) {
   if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }
$this->getCollection()->getSelect()->join( array('table_alias'=>'sales_flat_order_item'), 'main_table.entity_id = table_alias.order_id',array('table_alias.sku'));
$this->getCollection()->getSelect()->where("sku like ?", "%$value%");

    return $this;
}

After adding this line search is working fine for me

Answer 4

To get all of the Skus in the order, you could use frame_callback to look up the Skus for the order in a separate function.

In the _prepareColumns() function for your Sku column:

...

$this->addColumn('increment_id', array(
                 'header' => Mage::helper('sales')->__('SKU'),
                 'index' => 'increment_id',
                 'frame_callback' => array($this, 'callback_skus')
));

...

Then add this new function somewhere in your Grid.php.

public function callback_skus($value, $row, $column, $isExport) {
    $increment_id = $value;
    $_order = Mage::getModel('sales/order')->loadByIncrementId($increment_id);
    $_items = $_order->getAllItems();
    $skus="";
    foreach ($_items as $item) {
            $skus .= $item->getSku()."<br/>";
    }
    return $skus;
}

This will return all of the Skus for the order without a lot of complex joins.

---

EDIT - 2nd part of the answer

To enable searching/filtering on the above, you'll need to update the _prepareCollection() to the following:

protected function _prepareCollection()
{

    $collection = Mage::getResourceModel($this->_getCollectionClass())
    ->join(
           'sales/order_item',
           '`sales/order_item`.order_id=`main_table`.entity_id',
               array(
                     'skus'  => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ",")'),
               )
    );
    $collection->getSelect()->group('entity_id');
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

Add a new function called filter_skus():

public function filter_skus($collection, $column) {
   if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    $this->getCollection()->getSelect()->where(
        "sku like ?"
    , "%$value%");

    return $this;

}

and update the addColumn for sku like this:

...

$this->addColumn('sku', array(
                 'header' => Mage::helper('sales')->__('SKU'),
                 'index' => 'increment_id',
                 'frame_callback' => array($this, 'callback_skus'),
                 'filter_condition_callback' => array($this, 'filter_skus'),
));

...

Notice the new call to filter_condition_callback that adds the where condition necessary to make the search function.