0

I currently use a extention to display the payment method and SKU inside the grid.

I want to expand this with adding the zipcode.

How should I expand my code?

CODE:

app/code/local/Atwix/ExtendedGrid/etc/config.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Atwix_ExtendedGrid>
            <version>1.0.1</version>
        </Atwix_ExtendedGrid>
    </modules>
    <global>
        <blocks>
            <atwix_extendedgrid>
                <class>Atwix_ExtendedGrid_Block</class>
            </atwix_extendedgrid>
        </blocks>
        <helpers>
            <atwix_extendedgrid>
                <class>Atwix_ExtendedGrid_Helper</class>
            </atwix_extendedgrid>
        </helpers>
        <models>
            <atwix_extendedgrid>
                <class>Atwix_ExtendedGrid_Model</class>
            </atwix_extendedgrid>
            <sales_resource>
                <rewrite>
                    <order_grid_collection>Atwix_ExtendedGrid_Model_Resource_Sales_Order_Grid_Collection</order_grid_collection>
                </rewrite>
            </sales_resource>
        </models>
    </global>
    <adminhtml>
        <events>
            <sales_order_grid_collection_load_before>
                <observers>
                    <atwix_exgrid>
                        <model>atwix_extendedgrid/observer</model>
                        <method>salesOrderGridCollectionLoadBefore</method>
                    </atwix_exgrid>
                </observers>
            </sales_order_grid_collection_load_before>
        </events>
        <layout>
            <updates>
                <atwix_extendedgrid>
                    <file>atwix/extendedgrid.xml</file>
                </atwix_extendedgrid>
            </updates>
        </layout>
    </adminhtml>
</config>

app/code/local/Atwix/ExtendedGrid/Helper/Data.php

class Atwix_ExtendedGrid_Helper_Data extends Mage_Core_Helper_Abstract
{
    /**
     * parameters for addColumnAfter method
     * @return array
     */
    public function getSkusColumnParams()
    {
        return array(
            'header' => 'SKUs',
            'index' => 'skus',
            'type' => 'text',
            'filter_condition_callback' => array('Atwix_ExtendedGrid_Model_Observer', 'filterSkus'),
        );
    }
}

app/code/local/Atwix/ExtendedGrid/Model/Observer.php

class Atwix_ExtendedGrid_Model_Observer
{
    /**
     * Joins extra tables for adding custom columns to Mage_Adminhtml_Block_Sales_Order_Grid
     * @param Varien_Object $observer
     * @return Atwix_Exgrid_Model_Observer
     */
    public function salesOrderGridCollectionLoadBefore($observer)
    {
        $collection = $observer->getOrderGridCollection();
        $select = $collection->getSelect();
        $select->joinLeft(array('payment' => $collection->getTable('sales/order_payment')), 'payment.parent_id=main_table.entity_id', array('payment_method' => 'method'));
        $select->join('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 ", ")')));
        $select->group('main_table.entity_id');
    }

    /**
     * callback function used to filter collection
     * @param $collection
     * @param $column
     * @return $this
     */
    public function filterSkus($collection, $column)
    {
        if (!$value = $column->getFilter()->getValue()) {
            return $this;
        }

        $collection->getSelect()->having(
            "group_concat(`sales_flat_order_item`.sku SEPARATOR ', ') like ?", "%$value%");

        return $this;
    }
}

app/code/local/Atwix/ExtendedGrid/Model/Resource/Sales/Order/Grid/Collection.php

class Atwix_ExtendedGrid_Model_Resource_Sales_Order_Grid_Collection extends Mage_Sales_Model_Resource_Order_Grid_Collection
{

    /**
     * Get SQL for get record count
     *
     * @return Varien_Db_Select
     */
    public function getSelectCountSql()
    {
        $countSelect = parent::getSelectCountSql();

        if (Mage::app()->getRequest()->getControllerName() == 'sales_order') {
            $countSelect->reset(Zend_Db_Select::GROUP);
            $countSelect->reset(Zend_Db_Select::COLUMNS);
            $countSelect->columns("COUNT(DISTINCT main_table.entity_id)");

            $havingCondition = $countSelect->getPart(Zend_Db_Select::HAVING);
            if (count($havingCondition)) {
                $countSelect->where(
                    str_replace("group_concat(`sales_flat_order_item`.sku SEPARATOR ', ')", 'sales_flat_order_item.sku', $havingCondition[0])
                );
                $countSelect->reset(Zend_Db_Select::HAVING);
            }
        }

        return $countSelect;
    }

    /**
     * Init select
     * @return Mage_Core_Model_Resource_Db_Collection_Abstract
     */
    protected function _initSelect()
    {
        $this->addFilterToMap('store_id', 'main_table.store_id')
            ->addFilterToMap('created_at', 'main_table.created_at')
            ->addFilterToMap('updated_at', 'main_table.updated_at');
        return parent::_initSelect();
    }
}

app/design/adminhtml/default/default/layout/atwix/extendedgrid.xml

<layout>
    <sales_order_grid_update_handle>
        <reference name="sales_order.grid">
            <action method="addColumnAfter">
                <columnId>payment_method</columnId>
                <arguments>
                    <header>Payment Method</header>
                    <index>payment_method</index>
                    <filter_index>payment.method</filter_index>
                    <type>text</type>
                </arguments>
                <after>shipping_name</after>
            </action>
            <action method="addColumnAfter">
                <columnId>skus</columnId>
                <arguments helper="atwix_extendedgrid/getSkusColumnParams" />
                <after>payment_method</after>
            </action>
        </reference>
    </sales_order_grid_update_handle>
    <adminhtml_sales_order_grid>
        <!-- apply layout handle defined above -->
        <update handle="sales_order_grid_update_handle" />
    </adminhtml_sales_order_grid>
    <adminhtml_sales_order_index>
        <!-- apply layout handle defined above -->
        <update handle="sales_order_grid_update_handle" />
    </adminhtml_sales_order_index>
</layout>
1

Add below code in collection of app/code/local/Atwix/ExtendedGrid/Model/Observer.php:

$collection = $observer->getOrderGridCollection();
        $select = $collection->getSelect();
        $select->joinLeft(array('payment' => $collection->getTable('sales/order_payment')), 'payment.parent_id=main_table.entity_id', array('payment_method' => 'method'));

        $select->join(
                array('address' => $collection->getTable("sales/order_address")),
                'main_table.entity_id = address.parent_id AND address.address_type = "shipping"',
                array('postcode')
            );

        $select->join('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 ", ")')));
        $select->group('main_table.entity_id'); 

add below code in helper file app/code/local/Atwix/ExtendedGrid/Helper/Data.php

 public function getPostcodeColumnParams()
    {
        return array(
            'header' => 'Postcode',
            'index' => 'postcode',
            'type' => 'text',               
        );
    } 

change code of file app/design/adminhtml/default/default/layout/atwix/extendedgrid.xml

 <sales_order_grid_update_handle>
            <reference name="sales_order.grid">
                <action method="addColumnAfter">
                    <columnId>payment_method</columnId>
                    <arguments>
                        <header>Payment Method</header>
                        <index>payment_method</index>
                        <filter_index>payment.method</filter_index>
                        <type>text</type>
                    </arguments>
                    <after>shipping_name</after>
                </action>
                <action method="addColumnAfter">
                    <columnId>skus</columnId>
                    <arguments helper="atwix_extendedgrid/getSkusColumnParams" />
                    <after>payment_method</after>
                </action>
            </reference>
        </sales_order_grid_update_handle>

to:

 <sales_order_grid_update_handle>
        <reference name="sales_order.grid">
            <action method="addColumnAfter">
                <columnId>payment_method</columnId>
                <arguments>
                    <header>Payment Method</header>
                    <index>payment_method</index>
                    <filter_index>payment.method</filter_index>
                    <type>text</type>
                </arguments>
                <after>shipping_name</after>
            </action>
            <action method="addColumnAfter">
                <columnId>skus</columnId>
                <arguments helper="atwix_extendedgrid/getSkusColumnParams" />
                <after>payment_method</after>
            </action>
            <action method="addColumnAfter">
                <columnId>postcode</columnId>
                <arguments helper="atwix_extendedgrid/getPostcodeColumnParams" />
                <after>skus</after>
            </action>
        </reference>
    </sales_order_grid_update_handle>
  • Thanks for your reply. I do not have any grid file, I only have the files mentioned above. Do I need to place this to the layout xml file? – Henk Z Mar 31 '16 at 7:21
  • update answer check it – Prashant Valanda Mar 31 '16 at 7:44
  • Amazing!! Works great – Henk Z Mar 31 '16 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.