Sales Order Grid Customization

In this blog, we will see how to add columns to magento admin sales order grid.

In this blog i will basically share with you code snippet to add different columns to the admin sales order grid.
In this module i have added the column name

  • Product Count
  • Product Purchased
  • Shipping Method
  • Payment Method
  • Shipping Address, Postcode, Telephone, Region

Using this module source code you can easliy add other fields to the order grid as well.
The module is tested in magento 1.6 but should work on magento 1.5+
[dm]26[/dm]

Explanation

In this module i have overridden two magento core files
1. Mage_Adminhtml_Block_Sales_Order_Grid
2. Mage_Sales_Model_Mysql4_Order_Grid_Collection

the code to override these two files is written in config.xml file

<global>
<models>
            <salesgrid_mysql4>
                <class>Excellence_Salesgrid_Model_Mysql4</class>
                <entities>
                    <salesgrid>
                        <table>salesgrid</table>
                    </salesgrid>
                </entities>
            </salesgrid_mysql4>
</models>
<blocks>
            <adminhtml>
                <rewrite>
                    <sales_order_grid>Excellence_Salesgrid_Block_Adminhtml_Sales_Order_Grid</sales_order_grid>
                </rewrite>
            </adminhtml>
</blocks>
</global>

Next two add columns to the grid we need to
1. First add the column values in the grid collection. This is done in the _beforeLoad() function of the sales_grid collection. In the module source code you can see that i have added joins with many tables to the collection. If you require to add other columns to the grid you need to add similar joins.
2. Second in the grid file we need to add column to grid in the _prepareColumns() function and add code so the searching can take place properly in the _addColumnFilterToCollection() function.

You can download the code snippet to see the changes in code. I have given above a brief explanation of the same, rest you need to understand the code.
  • Thiago Mafort

    How to enable the column Total Shipping

    • Manish Prakash

      What exactly you mean by Total Shipping, total shipping amount?

  • Sounds great, but … 

    If i overwrite the default system files then a can update my magento in future?

    • Manish Prakash

      In this module no default system file has been changed, only overridden.

  • after install this module and searching from created_at default magento column


    SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous

    if you debug SQL see:

    ## 2012-02-23 00:02:00
    ## 2874 ## QUERY
    SQL: SELECT COUNT(*) FROM (SELECT `main_table`.*, `t2`.`shipping_description`, `t3`.`method`, `t4`.`telephone` AS `shipping_telephone`, `t4`.`region` AS `shipping_region`, `t4`.`postcode` AS `shipping_postcode`, `t4`.`city` AS `shipping_city`, count(t5.item_id) AS `product_count` FROM `sales_flat_order_grid` AS `main_table`
    INNER JOIN `sales_flat_order` AS `t2` ON main_table.entity_id =t2.entity_id
    INNER JOIN `sales_flat_order_payment` AS `t3` ON main_table.entity_id =t3.parent_id
    INNER JOIN `sales_flat_order_address` AS `t4` ON main_table.entity_id =t4.parent_id
    INNER JOIN `sales_flat_order_item` AS `t5` ON main_table.entity_id =t5.order_id and t5.parent_item_id IS NULL WHERE (created_at >= :_mage_bind_var_1 AND created_at ‘2012-02-21 23:00:00’,
    ‘:_mage_bind_var_2’ => ‘2012-02-29 22:59:59’,
    )
    TIME: 0.0019

    any one? :S

  • Im just trying to add a rewrite in sales_order_grid, but it gives me the Mage_Adminhtml_Block_Sales_Order Fatal error: Call to a member function
    setSaveParametersInSession() on a non-object in
    /var/www/magento/app/code/core/Mage/Adminhtml/Block/Widget/Grid/Container.php
    on line 73

    I’ve started a thread about it mentioning your article at http://www.magentocommerce.com/boards/viewthread/275543/.

    Thanks for the post.

  • to fixed.

    in function _prepareCollection() add before return

    $collection->addFilterToMap(‘created_at’, ‘main_table.created_at’);

    is de best solution in this case.

    • dw

      tks so much.

  • Patelsunil42

    when i seach any thing into grid it is aromatically  redirect to homepage.

    • Naresh Tank

      I have same problem any solution for this?

  • Hello Could any one please help me to use this Grid as individual module

  • Magento Modules

    Thanks, also how can we add filters on these columns.

  • How can I add a column of ordered items color into a column in sales grid in this page..

  • Dear Manish,

    thank you very much for the extension!

    I just would like to ask you the fastest way to replicate it for the Invoices Grid (instead of Order Grid).

    Thank you very much
    My Best Regards
    Simone

  • Dan Nadel

    I’m using 1.7 and was having an issue with the pager (it wasn’t possible to change pages – it was only showing 1 page). I fixed this by copying the original magento files to /app/code/local/Mage/Sales/Model/Resource/Order/Grid/Collection/Collection.php and /app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php and adding the custom code above directly.

    I was also having an issue that not all of the orders were being displayed which I fixed by changing some of the join() to joinleft() where appropriate.

    • leo

      The pager problem is from /app/code/local/Excellence/Salesgrid/Model/Sales/Mysql4/Order/Grid/Collection.php (problem is it always show 1 page order only)

      //Product Count
      //$this->getSelect()->join(array(‘t5’=>$sales_flat_order_item), ‘main_table.entity_id =t5.order_id and t5.parent_item_id IS NULL’,array(‘product_count’=>new Zend_Db_Expr(‘count(t5.item_id)’)));
      //$this->getSelect()->group(array(‘t5.order_id’));

      I suggest to comment out those line, and directly get the total_qty_ordered column from order, we dont need to count item by ourselves.

  • lalit

    after installing this module my order grid is not opening

  • leo

    change increment_id to real_order_id
    in the file /app/code/local/Excellence/Salesgrid/Block/Adminhtml/Sales/Order/Grid.php

    $this->addColumnAfter(‘skus’, array(
    ‘header’ => Mage::helper(‘sales’)->__(‘Product Purchased’),
    ‘index’ => ‘skus’,
    ),’real_order_id’);

  • Saif

    From where i can download the source code ?