In Magento Admin: How to add “Color” attribute coloumn under “Products in Cart” Report

Question

In Magento Admin: Under Reports/Shopping Cart/Products in Cart.
I would like to add "Color" attribute column under "Products in Cart" grid. Assuming all products in webshop are configurable products.
i.e; If from Webshop - Customer selects Test Product(configurable product) with Color "Red" option, then this attribute value should be displayed in the report.

Please suggest the best possible way to achieve this!

Answer 1

copy \app\code\core\Mage\Reports\Model\Resource\Quote\Collection.php and paste in app\code\local\Mage\Reports\Model\Resource\Quote\Collection.php

Override public function prepareForProductsInCarts() function

public function prepareForProductsInCarts()
    {
        $productEntity          = Mage::getResourceSingleton('catalog/product_collection');
        $productAttrName        = $productEntity->getAttribute('name');
        $productAttrNameId      = (int) $productAttrName->getAttributeId();
        $productAttrNameTable   = $productAttrName->getBackend()->getTable();
        $productAttrPrice       = $productEntity->getAttribute('price');
        $productAttrPriceId     = (int) $productAttrPrice->getAttributeId();
        $productAttrPriceTable  = $productAttrPrice->getBackend()->getTable();

        $ordersSubSelect = clone $this->getSelect();
        $ordersSubSelect->reset()
            ->from(
                array('oi' => $this->getTable('sales/order_item')),
                array(
                   'orders' => new Zend_Db_Expr('COUNT(1)'),
                   'product_id'))
            ->group('oi.product_id');

        $this->getSelect()
            ->useStraightJoin(true)
            ->reset(Zend_Db_Select::COLUMNS)
            ->joinInner(
                array('quote_items' => $this->getTable('sales/quote_item')),
                'quote_items.quote_id = main_table.entity_id',
                null)
            ->joinInner(
                array('e' => $this->getTable('catalog/product')),
                'e.entity_id = quote_items.product_id',
                null)
            ->joinInner(
                array('product_name' => $productAttrNameTable),
                "product_name.entity_id = e.entity_id AND product_name.attribute_id = {$productAttrNameId}",
                array('name'=>'product_name.value'))
            ->joinInner(
                array('product_price' => $productAttrPriceTable),
                "product_price.entity_id = e.entity_id AND product_price.attribute_id = {$productAttrPriceId}",
                array('price' => new Zend_Db_Expr('product_price.value * main_table.base_to_global_rate')))
            ->joinLeft(
                array('order_items' => new Zend_Db_Expr(sprintf('(%s)', $ordersSubSelect))),
                'order_items.product_id = e.entity_id',
                array()
            )
            ->columns('e.*')
            ->columns(array('carts' => new Zend_Db_Expr('COUNT(quote_items.item_id)')))
            ->columns('order_items.orders')
            ->where('main_table.is_active = ?', 1)
            ->group('quote_items.product_id');

        return $this;
    }

And replace with below function.

public function prepareForProductsInCarts()
    {
        $productEntity          = Mage::getResourceSingleton('catalog/product_collection');
        $productAttrName        = $productEntity->getAttribute('name');
        $productAttrNameId      = (int) $productAttrName->getAttributeId();
        $productAttrNameTable   = $productAttrName->getBackend()->getTable();
        $productAttrPrice       = $productEntity->getAttribute('price');
        $productAttrPriceId     = (int) $productAttrPrice->getAttributeId();
        $productAttrPriceTable  = $productAttrPrice->getBackend()->getTable();

        $ordersSubSelect = clone $this->getSelect();
        $ordersSubSelect->reset()
            ->from(
                array('oi' => $this->getTable('sales/order_item')),
                array(
                   'orders' => new Zend_Db_Expr('COUNT(1)'),
                   'product_id'))
            ->group('oi.product_id');

        $this->getSelect()
            ->useStraightJoin(true)
            ->reset(Zend_Db_Select::COLUMNS)
            ->joinInner(
                array('quote_items' => $this->getTable('sales/quote_item')),
                'quote_items.quote_id = main_table.entity_id',
                null)
            ->joinInner(
                array('e' => $this->getTable('catalog/product')),
                'e.entity_id = quote_items.product_id',
                null)
            ->joinInner(
                array('product_name' => $productAttrNameTable),
                "product_name.entity_id = e.entity_id AND product_name.attribute_id = {$productAttrNameId}",
                array('name'=>'product_name.value'))
            ->joinInner(
                array('product_price' => $productAttrPriceTable),
                "product_price.entity_id = e.entity_id AND product_price.attribute_id = {$productAttrPriceId}",
                array('price' => new Zend_Db_Expr('product_price.value * main_table.base_to_global_rate')))

            ### Newly added script ###
            ->joinLeft(
            array('cpei' => 'catalog_product_entity_int'),
            'cpei.entity_id = e.entity_id AND cpei.attribute_id = 92',
            array()
            )
            ->joinLeft(
            array('eaov' => 'eav_attribute_option_value'),
            'eaov.option_id = cpei.value',
            array('color'=>'eaov.value')
           )
           ### End script ###

            ->joinLeft(
                array('order_items' => new Zend_Db_Expr(sprintf('(%s)', $ordersSubSelect))),
                'order_items.product_id = e.entity_id',
                array()
            )
            ->columns('e.*')
            ->columns(array('carts' => new Zend_Db_Expr('COUNT(quote_items.item_id)')))
            ->columns('order_items.orders')
            ->where('main_table.is_active = ?', 1)
            ->group('quote_items.product_id');

        return $this;
    }

Here cpei.attribute_id = 92 is my "color" attribute id. You can change as per your requirement.

Also put below code in \app\code\core\Mage\Adminhtml\Block\Report\Shopcart\Product\grid.php file.

$this->addColumn('color', array(
            'header'    =>Mage::helper('reports')->__('Color'),
            'index'     =>'color',

Yes we can copy grid.php file from core to local folder. Works and tested in magento 1.7