2

I am trying to add a total orders of particular customer column in customer grid. It is added successfully but How can I filter it?

Code is :

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel('customer/customer_collection')
            ->addNameToSelect()
            ->addAttributeToSelect('email')
            ->addAttributeToSelect('created_at')
            ->addAttributeToSelect('group_id')
            ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
            ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
            ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
            ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
            ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left');

        $sql ='SELECT COUNT(*)'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
        . ' WHERE o.customer_id = e.entity_id GROUP BY e.entity_id';
        $expr = new Zend_Db_Expr('(' . $sql . ')'); 

        $collection->getSelect()->from(null, array('orders_count'=>$expr));
        echo $collection->getSelect();die;
        $this->setCollection($collection);

        return parent::_prepareCollection();
    }

    protected function _addColumnFilterToCollection($column)
    {   

        if ($column->getId() == 'orders_count') {

            if (!$value = $column->getFilter()->getValue()) {
                return $this;
            }
            if (!empty($value)) {
                $from = $value['from'];
                $to = $value['to'];           
                $this->getCollection()->getSelect()->where(
                     "orders_count >= $from AND orders_count <= $to");
            }        
            return $this;

        } else {
            parent::_addColumnFilterToCollection($column);
        }
        return $this;
    } 
protected function _prepareColumns()
    {
        $this->addColumn('orders_count', array(
            'header'    => Mage::helper('customer')->__('Total Orders'),
            'align'     => 'left',
            'width'     => '40px',
            'index'     => 'orders_count',
            'type'  => 'number',
            'sortable' => true,
        ));
...}
6
0

IN prepare column add your column as below

    $this->addColumn('orders_count', array(
        'header'    => Mage::helper('customer')->__('Total Orders'),
        'align'     => 'left',
        'width'     => '40px',
        'index'     => 'orders_count',
        'type'  => 'number',
        'sortable' => true,
        'filter_condition_callback' => array($this, 'yourMethodName'),
    ));

after _prepareColumns method create another method name as yourMethodName just like below

protected function yourMethodName($collection, $column)
{
if (!$value = $column->getFilter()->getValue()) {
    return $this;
}
// your code for filter 
//$this->getCollection()->getSelect()->where("shipping_name like ?", "%$value%");

return $this;
}
0

My solution is easy and working :)

Override Grid

  1. Move file app/code/core/Mage/Adminhtml/Block/Customer/Grid.php to app/code/local/Mage/Adminhtml/Block/Customer/Grid.php
  2. Add to method following code, before $this->setCollection($collection); Will add order data, orders filtering by Status - Complete

    /**
     *
     */
    $readAdapter = Mage::getSingleton('core/resource')->getConnection('core_read');
    $select = $readAdapter->select()
        ->from('sales_flat_order_grid')
        ->where('status=?', 'complete')
        ->columns(array(
                'orders_total' => new Zend_Db_Expr('CEIL(SUM(grand_total))'),
                'orders_count' => new Zend_Db_Expr('COUNT(*)')
            )
        )->group('customer_id');
    $collection->getSelect()->joinLeft(
        array('orders_count' => new Zend_Db_Expr('(' . $select . ')')),
        'e.entity_id = orders_count.customer_id',
        array('orders_count.orders_count', 'orders_count.orders_total')
    );
    $collection->addFilterToMap('orders_count', 'orders_count.orders_count');
    $collection->addFilterToMap('orders_total', 'orders_count.orders_total');
    /**
     *
     */
    
  3. Add columns - in method _prepareColumns, add following code

    $this->addColumn('orders_count', array(
        'header'    => Mage::helper('customer')->__('Total No. of Orders'),
        'align'     => 'left',
        'width'     => '40px',
        'index'     => 'orders_count',
        'type'  => 'number',
        'filter_condition_callback' => array($this, '_ordersCountFilterCallBack'),
        'sortable' => true,
    ));
    
    
    $this->addColumn('orders_total', array(
        'header'    => Mage::helper('customer')->__('Total Sum. of Orders'),
        'align'     => 'left',
        'width'     => '40px',
        'index'     => 'orders_total',
        'filter_condition_callback' => array($this, '_ordersTotalFilterCallBack'),
        'sortable' => true,
        'type'              => 'currency',
        'currency_code'     => 'UAH',
    ));
    
  4. Add 2 methods for filter callback

protected function _ordersCountFilterCallBack($collection, $column) { if (!$value = $column->getFilter()->getValue()) { return $this; }

    if (!isset($value['from'])) {
        $value['from'] = '';
    }

    if (!isset($value['to'])) {
        $value['to'] = '';
    }

    $from = ($value['from']) ? $value['from'] : 0;
    $to = ($value['to']) ? $value['to'] : 999999;
    $collection->getSelect()
        ->where("orders_count.orders_count >= $from AND  orders_count.orders_count <= $to");
    return $this;
}

protected function _ordersTotalFilterCallBack($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    if (!isset($value['from'])) {
        $value['from'] = '';
    }

    if (!isset($value['to'])) {
        $value['to'] = '';
    }

    $from = ($value['from']) ? $value['from'] : 0;
    $to = ($value['to']) ? $value['to'] : 999999;
    $collection->getSelect()
        ->where("orders_count.orders_total >= $from AND  orders_count.orders_total <= $to");
    return $this;
}
  1. Add fix for Sort functional

protected function _setCollectionOrder($column) { if (!$dir = $column->getDir()) { return $this; }

    if ($column->getIndex() == 'orders_count') {
        $collection = $this->getCollection();
        $collection->getSelect()
            ->order("orders_count " . strtoupper($column->getDir()));
        return $this;
    }

    if ($column->getIndex() == 'orders_total') {
        $collection = $this->getCollection();
        $collection->getSelect()
            ->order("orders_total " . strtoupper($column->getDir()));
        return $this;
    }

    return parent::_setCollectionOrder($column);
}

And will appears 2 new columns with total count orders of customer, and total sum orders of customer.

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.