2

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 empty column

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

  • I've added the 3 FIles that are involved. – Mage_248 Feb 29 '16 at 15:15
2

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

0

You r getting totally blank rows or just "last login time" ? Check out your Grid.php file and it looks like that prepareCollection method is fine.

Specify in layout.xml file if you haven't did ? or else you can specify in controllers file.

  • Hi Same. I get blank rows. No content. Under /app/design/adminhtml/default/default/layout there is no layout.xml File – Mage_248 Feb 29 '16 at 11:10
  • your Grid.php file is okay. Share your controller file means indexAction or ListAction – sandip Feb 29 '16 at 13:00
  • I dont have a cotroller file . Only the 3 mentioned above. What am i doing wrong? – Mage_248 Feb 29 '16 at 13:10
  • no need to add additional file. Also confirm that you have enabled customer log Now to go System > Configuration > System > log and set Enable Log to Yes Finally, flush your Magento caches under System > Cache Management Magento should longer write logs to these tables – sandip Mar 1 '16 at 5:03
  • I looked into the System > log cleaning. It is disabled. Can you explain why it is neccesary to clear those tables? With the SQL Statement of @Prashant Valanda i get the Values correctly (in PhpMyAdmin) – Mage_248 Mar 1 '16 at 7:55
0

You need to change your sql as below

   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(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 parent::_prepareCollection();
}

protected function _prepareColumns() {
    ...

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

    ...

    $this->addExportType('*/*/exportCsv', Mage::helper('customer')->__('CSV'));
    $this->addExportType('*/*/exportXml', Mage::helper('customer')->__('Excel XML'));
    return parent::_prepareColumns();
}

becuase in your sql "login_max" not found and also spelling mistake at ce.entity_id

enter image description here

  • Thank you for the quick help Prashant, but my Customer Grid (last login) keeps still empty. I added the SQL you gave me and changed logout_at to login_at. – Mage_248 Feb 29 '16 at 9:58
  • Updated answer try this it is working for me. – Prashant Valanda Feb 29 '16 at 14:05
  • It made sense to me when i did compare the changes you made, but it is still the same. Empty Column... So the problem seems to be somewhere else. – Mage_248 Feb 29 '16 at 14:58
  • use code for collection and grid column which i posted because it is working for me. – Prashant Valanda Feb 29 '16 at 16:35
  • 1
    I did, but no values appear in the Grid :( – Mage_248 Mar 1 '16 at 7:57

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.