Magento E-commerce Platform Q/ A

How to Add New Fields into Order Table in Magento (Admin Area)

Rewrite class: Mage_Adminhtml_Block_Widget_Grid. File is stored here: /app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php

To add new data to the collection, modify method: _prepareCollection as shown in the example below (by doing so, you’ll make the necessary order information displayed on frontend):

 protected function _prepareCollection() 
{
        $collection = Mage::getResourceModel($this->_getCollectionClass());

        $collection->getSelect()->joinLeft('sales_flat_order_address', "main_table.entity_id = sales_flat_order_address.parent_id AND sales_flat_order_address.address_type='billing'", array('company'));

        $collection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address'), 'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"', array('sfoa.street', 'sfoa.city', 'sfoa.region', 'sfoa.postcode', 'sfoa.telephone'));

        $collection->getSelect()->joinLeft('sales_flat_order_payment', 'main_table.entity_id = sales_flat_order_payment.parent_id', array('method'));

        $collection->getSelect()->joinLeft('sales_flat_order', "main_table.entity_id = sales_flat_order.entity_id", array('quote_id', 'coupon_code', 'is_virtual', 'tax_amount', 'shipping_amount', 'discount_amount', 'shipping_description'));
        
        $collection->getSelect()->joinLeft('customer_entity', "sales_flat_order.customer_id = customer_entity.entity_id", array('group_id'));

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

 

Now you need to add this information into the table, add this code into function _prepareColumns (thus you'll create new table columns). The code can be added to the end of the method after the announcement of the column 'action':

 $groups = Mage::getResourceModel('customer/group_collection')
                ->addFieldToFilter('customer_group_id', array('gt'=> 0))
                ->load()
                ->toOptionHash();
            $this->addColumn('group_id', array(
                'header'    =>  Mage::helper('customer')->__('Customer Group'),
                'index'     =>  'group_id',
                'type'      =>  'options',
                'options'   =>  $groups,
                'filter_condition_callback' => array($this, '_addEGColumnFilter'),
            ));
            $this->addColumn('company', array(
                'header' => Mage::helper('sales')->__('Company'),
                'index' => 'company',
                'filter_condition_callback' => array($this, '_addEGColumnFilter'),
            ));
            $this->addColumn('region', array(
                'header' => Mage::helper('sales')->__('Region'),
                'index' => 'region',
                'filter_condition_callback' => array($this, '_addEGColumnFilter'),
            ));
            $this->addColumn('city', array(
                'header' => Mage::helper('sales')->__('City'),
                'index' => 'city', 'filter_condition_callback' => array($this, '_addEGColumnFilter'),
            ));
            $this->addColumn('street', array(
                'header' => Mage::helper('sales')->__('Street'),
                'index' => 'street',
                'filter_condition_callback' => array($this, '_addEGColumnFilter'),
            ));
            $this->addColumn('postcode', array(
                'header' => Mage::helper('sales')->__('Postcode'),
                'index' => 'postcode',
                'filter_condition_callback' => array($this, '_addEGColumnFilter'),
            ));
            $this->addColumn('telephone', array(
                'header' => Mage::helper('sales')->__('Telephone'),
                'index' => 'telephone',
                'filter_condition_callback' => array($this, '_ customColumnFilter’),
            ));
            $this->addColumn('coupon_code', array(
                'header' => Mage::helper('sales')->__('Coupon Code'),
                'index' => 'coupon_code',
                'filter_condition_callback' => array($this, '_ customColumnFilter’),
            ));
            $this->addColumn('is_virtual', array(
                'header' => Mage::helper('sales')->__('Is Virtual'),
                'index' => 'is_virtual',
                'type' => 'options',
                'options' => array('0' => 'No', '1' => 'Yes'),
            ));
            $this->addColumn('tax_amount', array(
                'header' => Mage::helper('sales')->__('Tax Amount'),
                'index' => 'tax_amount',
                'type' => 'currency',
                'filter_condition_callback' => array($this, '_ customColumnFilter'),
            ));
            $this->addColumn('shipping_amount', array(
                'header' => Mage::helper('sales')->__('Shipping Amount'),
                'index' => 'shipping_amount',
                'type' => 'currency',
                'filter_condition_callback' => array($this, '_ customColumnFilter’),
            ));
            $this->addColumn('discount_amount', array(
                'header' => Mage::helper('sales')->__('Discount Amount'),
                'index' => 'discount_amount',
                'type' => 'currency',
                'filter_condition_callback' => array($this, '_ customColumnFilter’),
            ));
            $this->addColumn('shipping_description', array(
                'header' => Mage::helper('sales')->__('Shipping Method'),
                'index' => 'shipping_description',
                'filter_condition_callback' => array($this, '_ customColumnFilter’),
            ));
            $payments = Mage::getSingleton('payment/config')->getActiveMethods();
            foreach ($payments as $paymentCode => $paymentModel) {
                $paymentTitle = Mage::getStoreConfig('payment/' . $paymentCode . '/title');
                $pm[$paymentCode] = $paymentTitle;
            }
            $this->addColumn('method', array(
                'header' => Mage::helper('sales')->__('Payment Method'),
                'index' => 'method',
                'type' => 'options',
                'options' => $pm,
                'filter_condition_callback' => array($this, '_ customColumnFilter’),
            ));

 

You can see that the new rows appeared in the table. The only thing that remains is to add '_ customColumnFilter' method. As you would be able to see, the majority of added rows have 'filter_condition_callback' parameter which refers to '_ customColumnFilter' method that is why you need to add it. This method will allow you to filter table's fields by its values.

protected function _customColumnFilter($collection, $column) {
        $condition = $column->getFilter()->getCondition();

        $col_id = $column->getId();
        if (in_array($col_id, array('company', 'street', 'city', 'region', 'postcode', 'telephone'))) {
            $filter = $condition['like'];
            $collection->getSelect()->where("sales_flat_order_address.$col_id LIKE $filter");
        } elseif ($col_id == 'method') {
            $filter = $condition['eq'];
            $collection->getSelect()->where("sales_flat_order_payment.method = '$filter'");
        } elseif ($col_id == 'coupon_code') {
            $filter = $condition['like'];
            $collection->getSelect()->where("sales_flat_order.$col_id LIKE $filter");
        } elseif (in_array($col_id, array('is_virtual', 'shipping_description'))) {
            $filter = $condition['eq'];
            $collection->getSelect()->where("sales_flat_order.$col_id = '$filter'");
        } elseif (in_array($col_id, array('tax_amount', 'shipping_amount', 'discount_amount'))) {
            if ($filter = $condition['from'] * 1)
                $collection->getSelect()->where("sales_flat_order.$col_id >= $filter");
            if ($filter = $condition['to'] * 1)
                $collection->getSelect()->where("sales_flat_order.$col_id <= $filter");
        } elseif ($col_id == 'group_id') {
            $filter = $condition['eq'];
            $collection->getSelect()->where("customer_entity.$col_id = '$filter'");
        }
        return $this;
    }