Monday, April 13, 2015

Concatenate two fields in admin grid with filter in magento

i override Mage/Adminhtml/Block/Sales/Order/Grid.php to local and You can use this technique for any Magento core file. This way, you can avoid overriding the core file. This way, you can also avoid the risk of over writing your custom code by the system during the Magento system upgrade operation. Now, go for the main action. Open the Grid.php file from its new local directory and look at the following block of code carefully:
protected function _getCollectionClass()
{
    return 'sales/order_grid_collection';
}
 
protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    /* adding customer name section */       
$customerFirstNameAttr = Mage::getSingleton('customer/customer')->getResource()->getAttribute('firstname');
$customerLastNameAttr = Mage::getSingleton('customer/customer')->getResource()->getAttribute('lastname');
$collection->getSelect()
                    ->joinLeft(
                        array('cusFirstnameTb' => $customerFirstNameAttr->getBackend()->getTable()),
                        'main_table.customer_id = cusFirstnameTb.entity_id AND cusFirstnameTb.attribute_id = '.$customerFirstNameAttr->getId(). ' AND cusFirstnameTb.entity_type_id = '.Mage::getSingleton('customer/customer')->getResource()->getTypeId(),
                        array('cusFirstnameTb.value')
                    );   
     
$collection->getSelect()
                    ->joinLeft(
                        array('cusLastnameTb' => $customerLastNameAttr->getBackend()->getTable()),
                        'main_table.customer_id = cusLastnameTb.entity_id AND cusLastnameTb.attribute_id = '.$customerLastNameAttr->getId(). ' AND cusLastnameTb.entity_type_id = '.Mage::getSingleton('customer/customer')->getResource()->getTypeId(),
                        array('customer_name' => "CONCAT(cusFirstnameTb.value, ' ', cusLastnameTb.value)")
                    ); 
    /* end adding customer name section */ 
    $this->setCollection($collection);
    return parent::_prepareCollection();
}
Now take a deep look at the following portion from the above code section. You’ll understand that this following section is the newly added custom code:
/* adding customer name section */       
    $customerFirstNameAttr = Mage::getSingleton('customer/customer')->getResource()->getAttribute('firstname');
    $customerLastNameAttr = Mage::getSingleton('customer/customer')->getResource()->getAttribute('lastname');
    $collection->getSelect()
                        ->joinLeft(
                            array('cusFirstnameTb' => $customerFirstNameAttr->getBackend()->getTable()),
                            'main_table.customer_id = cusFirstnameTb.entity_id AND cusFirstnameTb.attribute_id = '.$customerFirstNameAttr->getId(). ' AND cusFirstnameTb.entity_type_id = '.Mage::getSingleton('customer/customer')->getResource()->getTypeId(),
                            array('cusFirstnameTb.value')
                        );   
         
    $collection->getSelect()
                        ->joinLeft(
                            array('cusLastnameTb' => $customerLastNameAttr->getBackend()->getTable()),
                            'main_table.customer_id = cusLastnameTb.entity_id AND cusLastnameTb.attribute_id = '.$customerLastNameAttr->getId(). ' AND cusLastnameTb.entity_type_id = '.Mage::getSingleton('customer/customer')->getResource()->getTypeId(),
                            array('customer_name' => "CONCAT(cusFirstnameTb.value, ' ', cusLastnameTb.value)")
                        ); 
/* end adding customer name section */ 
in the admin sales order grid with the name “customer_name” by inserting the following code snippet in the _prepareColumns() method of the Mage_Adminhtml_Block_Sales_Order_Grid class :
$this->addColumn('customer_name', array(
    'header'    => Mage::helper('adminhtml')->__('Customer Name'),
    'index'     => 'customer_name',
    'filter_condition_callback' => array($this, 'customerNameFilter'),
    'width'     => '120px',
)); 
public function customerNameFilter($collection, $column){
    $filterValue = $column->getFilter()->getValue();
    if(!is_null($filterValue)){
        $filterValue = trim($filterValue);
        $filterValue = preg_replace('/[\s]+/', ' ', $filterValue);
 
        $whereArr = array();
        $whereArr[] = $collection->getConnection()->quoteInto("cusFirstnameTb.value = ?", $filterValue);
        $whereArr[] = $collection->getConnection()->quoteInto("cusLastnameTb.value = ?", $filterValue);
        $whereArr[] = $collection->getConnection()->quoteInto("CONCAT(cusFirstnameTb.value, ' ', cusLastnameTb.value) = ?", $filterValue);
        $where = implode(' OR ', $whereArr);
        $collection->getSelect()->where($where);
    }
}