Magento – Show customers last login in customer grid

admincustomer-griddatabasemagento-1module

Im trying to show the customers last login in the customer grid. (with data from the log_customer table)

To accomplish this, i created a Module with the neccesary Files.
With the help from these Links:

I see the empty row in the Customer grid, but all my attempts didnt brought me to the solution.

The Files

app\code\local\MyModule\ExtendCustomerGrid\Block\Adminhtml\Customer\Grid.php

    class MyModule_ExtendCustomerGrid_Block_Adminhtml_Customer_Grid extends Mage_Adminhtml_Block_Customer_Grid { 
    protected function _prepareCollection() {
                        $collection = Mage::getResourceModel('customer/customer_collection')
                                ->addNameToSelect()
                                ->addAttributeToSelect('email')
                                ->addAttributeToSelect('created_at')
                                ->addAttributeToSelect('group_id')
                                ->addAttributeToSelect('last_login')
                                ->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');



                    // Retrieve data from log_customer

                    $sql = '(SELECT MAX(login_at) 
                             FROM log_customer 
                             WHERE log_customer.customer_id = e.entity_id 
                             GROUP BY log_customer.customer_id)';
                    $expr = new Zend_Db_Expr('(' . $sql . ')');

                    $collection->getSelect()->columns(array('last_login' => new Zend_Db_Expr($sql)));




                    $this->setCollection($collection);

                    return parent::_prepareCollection();

}

                protected function _prepareColumns() {
                    ...

                    $this->addColumn('last_login', array(
                        'header' => Mage::helper('customer')->__('Letzte Anmeldung'),
                        'width' => '80',
                        'type' => 'datetime',
                        'align' => 'center',
                        'index' => 'login_at',
                        'filter_index' => '`c_log`.`login_at`',
                        'gmtoffset' => true
                    ));

                    ...

                    $this->addExportType('*/*/exportCsv', Mage::helper('customer')->__('CSV'));
                    $this->addExportType('*/*/exportXml', 

    Mage::helper('customer')->__('Excel XML'));
                        return parent::_prepareColumns();
                    }
     }

app\code\local\MyModule\ExtendCustomerGrid\etc\config.xml

    <config>
        <modules>
            <MyModule_ExtendCustomerGrid>
                <version>0.0.1</version>
            </MyModule__ExtendCustomerGrid>
        </modules>
        <global>
            <blocks>
                <adminhtml>
                    <rewrite>
                        <customer_grid>MyModule__ExtendCustomerGrid_Block_Adminhtml_Customer_Grid</customer_grid><!-- rewrite the customer grid -->
                    </rewrite>
                </adminhtml>
            </blocks>
        </global>
    </config>

app\etc\modules\MyModule_ExtendCustomerGrid.xml

    <config>
        <modules>
            <MyModule_ExtendCustomerGrid>
                <active>true</active>
                <codePool>local</codePool>
                <depends>
                    <Mage_Customer /><!-- module should depend on Mage_Customer -->
                    <Mage_Adminhtml /><!-- module should depend on Mage_Adminhtml also -->
                </depends>
            </MyModule_ExtendCustomerGrid>
        </modules>
    </config>

Thx in advance

Best Answer

Working _prepareCollection method:

protected function _prepareCollection() {
    $collection = Mage::getResourceModel('customer/customer_collection')
    ->addNameToSelect()
    ->addAttributeToSelect('email')
    ->addAttributeToSelect('created_at')
    ->addAttributeToSelect('group_id')
    ->addAttributeToSelect('last_login')
    ->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 MAX(logout_at) FROM log_customer WHERE log_customer.customer_id = e.entity_id GROUP BY log_customer.customer_id)';
    $collection->getSelect()->columns(array('last_login' => new Zend_Db_Expr($sql)));

    $this->setCollection($collection);
    return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
}

As you can see, only change is

return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();

instead of

return parent::_prepareCollection();

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 2 – Add Company Column to Customers Admin Grid with Observer

It is possible thru rewrite or observer: (observer is better to prevent rewrite conflicts)

Using Observer

Configure your 2 observers: loading the column to grid and loading of collection

<adminhtml>
    <events>
        <core_block_abstract_to_html_before>
            <observers>
                <{observer_name}>
                    <class>{namespace}_{module}/observer</class>
                    <method>beforeBlockToHtml</method>
                </{observer_name}>
            </observers>
        </core_block_abstract_to_html_before>
        <eav_collection_abstract_load_before>
            <observers>
                <{observer_name}>
                    <class>{namespace}_{module}/observer</class>
                    <method>beforeCollectionLoad</method>
                </{observer_name}>
            </observers>
        </eav_collection_abstract_load_before>
    </events>
</adminhtml>

Then create your observer class with the methods in the declaration

class {Namespace}_{Module}_Model_Observer
{
    public function beforeBlockToHtml(Varien_Event_Observer $observer)
    {
        $grid = $observer->getBlock();

        /**
         * Mage_Adminhtml_Block_Customer_Grid
         */
        if ($grid instanceof Mage_Adminhtml_Block_Customer_Grid) {
            $grid->addColumnAfter(
                '{column_code}',
                array(
                    'header' => Mage::helper('{Module}_customer')->__('{{column_name}}'),
                    'index'  => '{column_code}'
                ),
                'entity_id'
            );
        }
    }

    public function beforeCollectionLoad(Varien_Event_Observer $observer)
    {
        $collection = $observer->getCollection();
        if (!isset($collection)) {
            return;
        }

        /**
         * Mage_Customer_Model_Resource_Customer_Collection
         */
        if ($collection instanceof Mage_Customer_Model_Resource_Customer_Collection) {
            /* @var $collection Mage_Customer_Model_Resource_Customer_Collection */
            $collection->addAttributeToSelect('{attribute_code}');
        }
    }
}

See this answer: https://magento.stackexchange.com/a/5986/21518

Using Rewrite

Rewrite the grid block's _prepareCollection method and add your column to the collection.

See this answer: https://magento.stackexchange.com/a/5975/21518