I Have Create Module That Add Some Field In Sales Order Gid, but That Field Data Are Repeated, When More Order Are Placed.
Product Type, Quantity Ordered, Product Id, Product Name, Sku
are repeated in order grid
Observer.php Code
<?php
class Sigmasolve_Customordergrid_Model_Observer
{
public function salesOrderGridCollectionLoadBefore($observer)
{
$collection = $observer->getOrderGridCollection();
//$select = $collection->getSelect();
$collection->getSelect()->joinLeft
(array
('payment' => $collection->getTable('sales/order_payment')),
'payment.parent_id=main_table.entity_id', array('payment_method' => 'method')
);
$collection->join
(array
('soa' => 'sales/order_address'),
'soa.parent_id=main_table.entity_id and soa.address_type = "billing"',
array('full_address'=>'CONCAT(soa.firstname, " " , soa.lastname, ",<br/>", soa.street, ",<br/>", soa.city, ",<br/>", soa.region, ",<br/>", soa.postcode)' ),
null,'left'
);
$collection->join
(array
('soas' => 'sales/order_address'), 'soas.parent_id=main_table.entity_id and soas.address_type = "shipping"',
array('full_address_ship'=>'CONCAT(soas.firstname, " " , soas.lastname, ",<br/>", soas.street, ",<br/>", soas.city, ",<br/>", soas.region, ",<br/>", soas.postcode)' ),
null,'left'
);
$customer_entity = Mage::getSingleton('core/resource')->getTableName('customer_entity');
$collection->getSelect()->join(
$customer_entity,
'main_table.customer_id = '.$customer_entity.'.entity_id', array('customer_email' => 'email')
);
$collection->getSelect()->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 ", ")'),
'names' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.name SEPARATOR ",")'),
'productids' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.product_id SEPARATOR ",")'),
'quantityordereds' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.qty_ordered SEPARATOR ",")'),
'types' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.product_type SEPARATOR ",")')
)
);
$collection->getSelect()->group('main_table.entity_id');
}
}
Data.php Code
<?php
class Sigmasolve_Customordergrid_Helper_Data extends Mage_Core_Helper_Abstract
{
/* public function getIsEnable()
{
return Mage::getStoreConfig('sigmasolve_customordergrid/general/enabled');
} */
public function getSkusColumnParams()
{
return array(
'header' => 'SKU',
'index' => 'skus',
'type' => 'text',
);
}
public function getNamesColumnParams()
{
return array(
'header' => 'Product Name',
'index' => 'names',
'type' => 'text',
);
}
public function getProductidsColumnParams()
{
return array(
'header' => 'Product Id',
'index' => 'productids',
'type' => 'text',
);
}
public function getQuantityorderedsColumnParams()
{
return array(
'header' => 'Quantity Ordered',
'index' => 'quantityordereds',
'type' => 'text',
);
}
public function getProducttypesColumnParams()
{
return array(
'header' => 'Type',
'index' => 'types',
'type' => 'text',
);
}
public function getImagesColumnParams()
{
return array(
'header' => 'Image',
'align' => 'left',
'index' => 'images',
'width' => '70',
//'filter_condition_callback' => array('Sigmasolve_Customordergrid_Model_Observer', 'filterImages'),
);
}
}
Collection.php Code
<?php
class Sigmasolve_Customordergrid_Model_Resource_Sales_Order_Grid_Collection extends Mage_Sales_Model_Resource_Order_Grid_Collection
{
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->where(
str_replace("group_concat(`sales_flat_order_item`.name SEPARATOR ', ')",'sales_flat_order_item.name', $havingCondition[0])
);
$countSelect->where(
str_replace("group_concat(`sales_flat_order_item`.product_id SEPARATOR ', ')",'sales_flat_order_item.product_id', $havingCondition[0])
);
$countSelect->where(
str_replace("group_concat(`sales_flat_order_item`.qty_ordered SEPARATOR ', ')",'sales_flat_order_item.qty_ordered', $havingCondition[0])
);
$countSelect->where(
str_replace("group_concat(`sales_flat_order_item`.product_type SEPARATOR ', ')",'sales_flat_order_item.product_type', $havingCondition[0])
);
$countSelect->reset(Zend_Db_Select::HAVING);
}
}
return $countSelect;
}
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();
}
}
customordergrid.xml layout file
<?xml version="1.0"?>
<layout>
<sales_order_grid_update_handle>
<reference name="sales_order.grid">
<action method="addColumnAfter">
<columnId>payment_method</columnId>
<arguments>
<header>Payment Type</header>
<index>payment_method</index>
<filter_index>payment.method</filter_index>
<type>text</type>
</arguments>
<after>shipping_name</after>
</action>
<action method="addColumnAfter">
<columnId>customer_email</columnId>
<arguments>
<header>Email</header>
<index>customer_email</index>
<filter_index>customer.email</filter_index>
<type>text</type>
</arguments>
<after>shipping_name</after>
</action>
<action method="addColumnAfter">
<columnId>full_address</columnId>
<arguments>
<header>Billing Address</header>
<index>full_address</index>
<filter_index>soa.billing</filter_index>
<type>text</type>
</arguments>
<after>shipping_name</after>
</action>
<action method="addColumnAfter">
<columnId>full_address_ship</columnId>
<arguments>
<header>Shipping Address</header>
<index>full_address_ship</index>
<filter_index>soas.shipping</filter_index>
<type>text</type>
</arguments>
<after>full_address</after>
</action>
<action method="addColumnAfter">
<columnId>skus</columnId>
<arguments helper="sigmasolve_customordergrid/getSkusColumnParams" />
<after>payment_method</after>
</action>
<action method="addColumnAfter">
<columnId>images</columnId>
<arguments helper="sigmasolve_customordergrid/getImagesColumnParams" />
<after>created_at</after>
</action>
<action method="addColumnAfter">
<columnId>names</columnId>
<arguments helper="sigmasolve_customordergrid/getNamesColumnParams" />
<after>payment_method</after>
</action>
<action method="addColumnAfter">
<columnId>producdtids</columnId>
<arguments helper="sigmasolve_customordergrid/getProductidsColumnParams" />
<after>payment_method</after>
</action>
<action method="addColumnAfter">
<columnId>quantityordereds</columnId>
<arguments helper="sigmasolve_customordergrid/getQuantityorderedsColumnParams" />
<after>payment_method</after>
</action>
<action method="addColumnAfter">
<columnId>types</columnId>
<arguments helper="sigmasolve_customordergrid/getProducttypesColumnParams" />
<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>
