Add Total Number of Orders and Revenue Column to Customer Grid in Magento 1.9

customergridmagento-1.8magento-1.9

I want to add 2 columns to customer gird:
– Total number orders customer made.
– Total price of these orders.

Can anyone help me please ?

Best Answer

First,please follow @Amast post

You can get Customer total order from this link...

But for getting total price .use below code

     $total_pricesql ='SELECT sum(o.base_grand_total)'
            . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
            . ' WHERE o.customer_id = e.entity_id ';
        $expr = new Zend_Db_Expr('(' . $total_pricesql . ')'); 

        $collection->getSelect()->from(null, array('total_price'=>$expr));
    $Totalsql ='SELECT COUNT(*)'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
        . ' WHERE o.customer_id = e.entity_id ';
    $Totalsqlexpr = new Zend_Db_Expr('(' . $Totalsql . ')'); 

    $collection->getSelect()->from(null, array('orders_count'=>$Totalsqlexpr));




$this->addColumn('orders_count', array(
        'header'    => Mage::helper('customer')->__('Orders Count'),
        'index'     => 'orders_count',
    ));

            $this->addColumn('total_price', array(
                'header' => Mage::helper('sales')->__('Total),
                'index' => 'total_price,
                'type'  => 'currency',
                'currency' => 'base_currency_code',
            ));

Edit:

You custom renderer class .Please check http://inchoo.net/magento/how-to-add-custom-renderer-for-a-custom-column-in-magento-grid/ first.

From:

 $this->addColumn('total_price', array(
                'header' => Mage::helper('sales')->__('Total),
                'index' => 'total_price,
                'type'  => 'currency',
                'currency' => 'base_currency_code',
            ));

change:

$this->addColumn('total_price',
array(
'header'=> Mage::helper('total_price')->__('Order total price'),
'index' => 'total_price',
'renderer'  => 'Mage_Adminhtml_Block_Catalog_Product_Renderer_Red',// THIS IS WHAT THIS POST IS ALL ABOUT
));

}

Add

public function render(Varien_Object $row)
{
$value =  $row->getData($this->getColumn()->getIndex());
return '$'.$value.'</span>';

}

Related Solutions

Magento Customer Grid – Display Last Order Date in Magento 1.9

Here is working example for CE 1.8.1:

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');



    // add 2 new fields as sub queries      
    $sql ='SELECT MAX(o.created_at)'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
        . ' WHERE o.customer_id = e.entity_id ';
    $expr = new Zend_Db_Expr('(' . $sql . ')'); 

    $collection->getSelect()->from(null, array('last_order_date'=>$expr));

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

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

    //echo $collection->getSelect(); exit;      

    $this->setCollection($collection);

    return parent::_prepareCollection();
}

and

protected function _prepareColumns()
{
    $this->addColumn('entity_id', array(
        'header'    => Mage::helper('customer')->__('ID'),
        'width'     => '50px',
        'index'     => 'entity_id',
        'type'  => 'number',
    ));

    $this->addColumn('last_order_date', array(
        'header'    => Mage::helper('customer')->__('Last Order Date'),
        'type'      => 'datetime',
        'align'     => 'center',
        'index'     => 'last_order_date',
        'gmtoffset' => true,        
    ));

    $this->addColumn('orders_count', array(
        'header'    => Mage::helper('customer')->__('Orders Count'),
        'index'     => 'orders_count',
    ));

    ........
    ........

The output is

orders

Magento – How to add column for total orders in customer grid in magento

According to your Question asked, it seems like you have successfully added a Grand Total Column, you just need to add range boxes, that can help in filter.

Find the code below to add in customer Grid -

$this->addColumn('orders_count', array(
            'header'    => Mage::helper('customer')->__('Orders Count'),
            'width'     => '50px',
            'index'     => 'orders_count',
            'type'      => 'number',
        ));

[EDITED]

I have gone little bit deep in this Question and find that we can provide filter in the following manner -

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

and add the following function -

public function _filterHasOrderTotalBetweenCallback($collection, $column){

    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }
    if (!empty($value)) {
        $from = $value['from'];
        $to = $value['to'];           

        $this->getCollection()->getSelect()->having(
             "orders_count>=$from AND orders_count<=$to");
        Mage::log((string)$this->getCollection()->getSelect(),null,'customer.log');
    }        
    return $this;
}

Adding the above function is providing the result in the form of query which is running fine in MYSQL , but not accepted in Magento

Best Answer

Edit:

Related Solutions

Magento Customer Grid – Display Last Order Date in Magento 1.9

Magento – How to add column for total orders in customer grid in magento

Related Topic