Magento | Add "Customer Group" to "Sales Order Grid"

A default installation of Magento does not include information about “customer group” in one of the columns in either the Sales Grid. This can be a serious shortcoming when you want to filter/list/report all of the orders in the Sales Grid which relate to a particular group of customers.

By default “Sales > Orders” grid uses the “sales_flat_order_grid” table for rendering. This table is basically a redundant subset of “sales_flat_order” table. Surprisingly “sales_flat_order” table has the “customer_group_id” column in it, while “sales_flat_order_grid” table does not. So our first step is to add the “customer_group_id” column to the “sales_flat_order_grid” table.

We do so by simple upgrade script that executes the code similar to this:

$installer->getConnection()
        ->addColumn($installer->getTable('sales/order_grid'), 'customer_group_id', array(
            'TYPE' => Varien_Db_Ddl_Table::TYPE_SMALLINT,
            'NULLABLE'  => false,
            'DEFAULT'   => '0',
            'COMMENT' => 'Customer Group'
        ));

If this install/upgrade script executed successfuly we should see the new “customer_group_id” column in the “sales_flat_order_grid” table. Please note that Magento will automatically populate this column from the data in the “sales_flat_order_grid.customer_group_id” once the new orders get saved.

After that we will rewrite the Mage_Adminhtml_Block_Sales_Order_Grid and Mage_Adminhtml_Block_Sales_Order_Create_Customer_Grid but change only the minimum necessary for our new “Customer Group” column to appear.

We do so by adding the proper entries under the config.xml

<global>
<blocks>
    <adminhtml>
        <rewrite>
            <sales_order_create_customer_grid>Mycompany_Myextension_Block_Adminhtml_Sales_Order_Create_Customer_Grid</sales_order_create_customer_grid>
            <sales_order_grid>Mycompany_Myextension_Block_Adminhtml_Sales_Order_Grid</sales_order_grid>
        </rewrite>
    </adminhtml>
</blocks>
</global>

Finally we add the rewrite blocks.

addFieldToFilter('customer_group_id', array('gt'=> 0))
            ->load()
            ->toOptionHash();
        $this->addColumn('customer_group_id', array(
            'header'    =>  Mage::helper('customer')->__('Customer Group'),
            'width'     =>  '100',
            'index'     =>  'customer_group_id',
            'type'      =>  'options',
            'options'   =>  $groups,
        ));
        $this->addColumnsOrder('customer_group_id', 'shipping_name');
        return parent::_prepareColumns();
    }
}
addFieldToFilter('customer_group_id', array('gt'=> 0))
            ->load()
            ->toOptionHash();
        $this->addColumn('group', array(
            'header'    =>  Mage::helper('customer')->__('Group'),
            'width'     =>  '100',
            'index'     =>  'group_id',
            'type'      =>  'options',
            'options'   =>  $groups,
        ));
        $this->addColumnsOrder('group', 'email');
        return parent::_prepareColumns();
    }
}

And that’s it. Now you should have the “Customer Group” columns on your “Sales > Orders” and “Sales > Orders > Create New Order” grids.

 

**** Update – Aug 19, 2014 ****

While the above solution will add the Customer Group column to the sales_order_grid, it will only populate that column with orders created, or edited, after its addition. To populate it with the Customer Group on previous orders we need to force a rebuild of the sales_flat_order grid.

//rebuild sales_flat_order_grid by updating status
$vals = array();
$vals['status'] = $status;
$where = $write->quoteInto('entity_id =?', $order_id);
$write->update("sales_flat_order_grid", $vals ,$where);

2 responses on “Magento | Add “Customer Group” to “Sales Order Grid”

  1. CannyCookie says:

    Good thinking. How do we populate $groups with the customer group model?

  2. Sales Secret says:

    Thanks for your comment Cookie. I updated the solution above to give you some assistance with populating that shiny new column 🙂 Let me know how it works out for you.

    – Robert Hadland

Leave a Reply

Your email address will not be published. Required fields are marked *