Abandoned Carts Don't Show in Reports Magento 1.9.2.2 [duplicate]

Question

I have upgraded my Magento CE platform from 1.9.0.1 to 1.9.2.2 and few days ago, I noticed that abandoned carts report is not updated with new carts. Where as I have checked in Customer Edit form, we can see all the items in shopping cart, also they are shown in sales_order_quote in Db, which means carts are being created, but they are not listed in reports.

I have tested it by creating a test customer account, and the problem is same. Any idea where in the code I should look for...?

Thanks

Answer 1

This is a magento core bug. What happens is, when customers are registered on website, the middle name attribute entity value is not created at the customer_entity_varchar table. The value should be NULL if the middle name is empty. But It will be created when you edit the customer from the admin panel.

FIX: Override "app/code/core/Mage/Reports/Model/Resource/Quote/Collection.php" by copying it to local path "app/code/local/Mage/Reports/Model/Resource/Quote/Collection.php"

And change line 209 from "joinInner" to "joinLeft" will solve the problem.

Answer 2

Solutions: Yes this is known bug caused by the middle name attribute in recent versions of Magento. Here is the solution for this issue, we need to insert records for middle name attribute for old customers, here is the script to update database table

require 'app/Mage.php';
Mage::app('default');

$db_read = Mage::getSingleton('core/resource')->getConnection('core_read');
$tablePrefix = (string) Mage::getConfig()->getTablePrefix();

$sql = 'SELECT entity_id FROM ' . $tablePrefix .'customer_entity where entity_id NOT IN (SELECT entity_id FROM '. $tablePrefix.'customer_entity_varchar where attribute_id = 6)';

$db_write1= Mage::getSingleton('core/resource')->getConnection('core_write');
$value=$db_read->query($sql);
echo "<br>Total: ".count($value);
$i =1;
while ($row = $value->fetch())
{
    //print_r($row);
    echo "<br>".$i." - ".$row['entity_id'];
    $sql = 'INSERT INTO '. $tablePrefix.'customer_entity_varchar (entity_type_id, attribute_id, entity_id) VALUES (1, 6, '.$row['entity_id'].' )';
    $db_write1->query($sql);  
};

Answer 3

Yes this is known bug caused by the middle name attribute in recent versions of Magento.

I could not find better than this post (see the comment of the answer)

Get all abandoned carts in Magento

I know there's a proper fix somewhere I just can't get my hands on it, will update my answer when I find it