Magento reports section is a powerful tool. If you open Reports > Products > Products Ordered section – you will be able to generate a report of all the recently sold products.
Sometimes, our clients, who mostly have the configurable and bundle products on the web store, ask the question: “Where can we find the exact number, for example, of clothes of each size sold? We see only T-shirt XXX sold number, without any size or colour”. Therefore, we had to find a solution to output the simple products in the report instead of the parents. Here we also describe the way to add another report page, which will allow us to see the sold simple products report in the admin panel. And the most important is that the native functionality is not changed and we do not use rewrites.
As always, we will start from the extension’s declaration with a dependency from Mage_Reports:
<?xml version="1.0"?>
<!-- file: magento_root/app/etc/modules/Atwix_Simpleproductsreport.xml -->
<config>
<modules>
<Atwix_Simpleproductsreport>
<active>true</active>
<codePool>local</codePool>
<depends>
<Mage_Reports />
</depends>
</Atwix_Simpleproductsreport>
</modules>
</config>
On the next step, let’s add an extension’s config file. It will contain a model, block, helper and admin route declaration – as you can see, nothing special at all:
<?xml version="1.0"?>
<!-- file: magento_root/app/code/local/Atwix/Simpleproductsreport/etc/config.xml -->
<config>
<modules>
<Atwix_Simpleproductsreport>
<version>1.0.0</version>
</Atwix_Simpleproductsreport>
</modules>
<global>
<blocks>
<atwix_simpleproductsreport>
<class>Atwix_Simpleproductsreport_Block</class>
</atwix_simpleproductsreport>
</blocks>
<models>
<atwix_simpleproductsreport>
<class>Atwix_Simpleproductsreport_Model</class>
<resourceModel>atwix_simpleproductsreport_resource</resourceModel>
</atwix_simpleproductsreport>
<atwix_simpleproductsreport_resource>
<class>Atwix_Simpleproductsreport_Model_Resource</class>
</atwix_simpleproductsreport_resource>
</models>
<helpers>
<atwix_simpleproductsreport>
<class>Atwix_Simpleproductsreport_Helper</class>
</atwix_simpleproductsreport>
</helpers>
</global>
<admin>
<routers>
<adminhtml>
<args>
<modules>
<Atwix_Simpleproductsreport_Adminhtml after="Mage_Adminhtml">Atwix_Simpleproductsreport_Adminhtml</Atwix_Simpleproductsreport_Adminhtml>
</modules>
</args>
</adminhtml>
</routers>
</admin>
</config>
Furthermore, place a link to the report’s page to the admin menu: it can be achieved by the adminhtml.xml file which adds a menu item and ACL permissions declaration for it. Here we will also have to add a sort_order tag to the original menu items if we want to see our “Simple Products Ordered” right after “Products”. The original Mage_Reports extension’s adminhtml.xml does not have it:
<?xml version="1.0"?>
<!-- file: magento_root/app/code/local/Atwix/Simpleproductsreport/etc/adminhtml.xml -->
<config>
<menu>
<report>
<children>
<products>
<children>
<bestsellers>
<sort_order>10</sort_order>
</bestsellers>
<sold>
<sort_order>20</sort_order>
</sold>
<simple_sold translate="title" module="atwix_simpleproductsreport">
<title>Simple Products Ordered</title>
<action>adminhtml/simpleproductsreport/index</action>
<sort_order>30</sort_order>
</simple_sold>
<viewed translate="title" module="reports">
<sort_order>40</sort_order>
</viewed>
<lowstock translate="title" module="reports">
<sort_order>50</sort_order>
</lowstock>
</children>
</products>
</children>
</report>
</menu>
<acl>
<resources>
<admin>
<children>
<report>
<children>
<products>
<children>
<simple_sold translate="title">
<title>Simple Products Ordered</title>
</simple_sold>
</children>
</products>
</children>
</report>
</children>
</admin>
</resources>
</acl>
</config>
Moving forward, to make that menu item appear we should add a dummy helper:
<?php
/**
* file: magento_root/app/code/local/Atwix/Simpleproductsreport/Helper/Data.php
*/
class Atwix_Simpleproductsreport_Helper_Data extends Mage_Core_Helper_Abstract
{
}
Now, let’s create an admin controller. It will inherit Mage_Adminhtml_Report_ProductController with the updated grid container and export the file names:
<?php
/**
* file: magento_root/app/code/local/Atwix/Simpleproductsreport/controllers/Adminhtml/SimpleproductsreportController.php
*/
require_once(Mage::getModuleDir('controllers','Mage_Adminhtml') . DS . 'Report' . DS . 'ProductController.php');
class Atwix_Simpleproductsreport_Adminhtml_SimpleproductsreportController extends Mage_Adminhtml_Report_ProductController
{
public function indexAction()
{
$this->_title($this->__('Simple Products Ordered'));
$this->_initAction()
->_setActiveMenu('report/product/simple_sold')
->_addBreadcrumb(Mage::helper('atwix_simpleproductsreport')->__('Simple Products Ordered'), Mage::helper('atwix_simpleproductsreport')->__('Simple Products Ordered'))
->_addContent($this->getLayout()->createBlock('atwix_simpleproductsreport/report_product_simplesold'))
->renderLayout();
}
/**
* Export Sold Simple Products report to CSV format action
*
*/
public function exportSoldCsvAction()
{
$fileName = 'simple_products_ordered.csv';
$content = $this->getLayout()
->createBlock('atwix_simpleproductsreport/report_product_simplesold_grid')
->getCsv();
$this->_prepareDownloadResponse($fileName, $content);
}
/**
* Export Sold Simple Products report to XML format action
*
*/
public function exportSoldExcelAction()
{
$fileName = 'simple_products_ordered.xml';
$content = $this->getLayout()
->createBlock('atwix_simpleproductsreport/report_product_simplesold_grid')
->getExcel($fileName);
$this->_prepareDownloadResponse($fileName, $content);
}
}
The grid container itself has nothing special. In a nutshell, there is only proper naming for the grid class instance loading:
<?php
/**
* file: magento_root/app/code/local/Atwix/Simpleproductsreport/Block/Report/Product/Simplesold.php
*/
class Atwix_Simpleproductsreport_Block_Report_Product_Simplesold extends Mage_Adminhtml_Block_Widget_Grid_Container
{
public function __construct()
{
$this->_controller = 'report_product_simplesold';
$this->_blockGroup = 'atwix_simpleproductsreport';
$this->_headerText = Mage::helper('atwix_simpleproductsreport')->__('Simple Products Ordered');
parent::__construct();
$this->_removeButton('add');
}
}
And the grid block inherits the original products sold grid class with the report collection change:
<?php
/**
* file: magento_root/app/code/local/Atwix/Simpleproductsreport/Block/Report/Product/Simplesold/Grid.php
*/
class Atwix_Simpleproductsreport_Block_Report_Product_Simplesold_Grid extends Mage_Adminhtml_Block_Report_Product_Sold_Grid
{
/**
* Setting up proper product collection name for a report
*
* @return Atwix_Simpleproductsreport_Block_Report_Product_Simplesold_Grid
*/
protected function _prepareCollection()
{
Mage_Adminhtml_Block_Report_Grid::_prepareCollection();
$this->getCollection()
->initReport('atwix_simpleproductsreport/simpleproduct_sold_collection');
return $this;
}
}
The collection class (that we’ve changed) will differ only from one row of one method: we will replace “parent_item_id IS NULL” MySQL query condition with “e.type_id = ‘simple’” on line 64:
<?php
/**
* file: magento_root/app/code/local/Atwix/Simpleproductsreport/Model/Resource/Simpleproduct/Sold/Collection.php
*/
class Atwix_Simpleproductsreport_Model_Resource_Simpleproduct_Sold_Collection extends Mage_Reports_Model_Resource_Product_Sold_Collection
{
/**
* Add ordered qty's
* updating condition to show simple products instead of products with NULL parent id
*
* @param string $from
* @param string $to
* @return updating condition to show simple products instead of products with NULL parent id
*/
public function addOrderedQty($from = '', $to = '')
{
$adapter = $this->getConnection();
$compositeTypeIds = Mage::getSingleton('catalog/product_type')->getCompositeTypes();
$orderTableAliasName = $adapter->quoteIdentifier('order');
$orderJoinCondition = array(
$orderTableAliasName . '.entity_id = order_items.order_id',
$adapter->quoteInto("{$orderTableAliasName}.state <> ?", Mage_Sales_Model_Order::STATE_CANCELED),
);
$productJoinCondition = array(
$adapter->quoteInto('(e.type_id NOT IN (?))', $compositeTypeIds),
'e.entity_id = order_items.product_id',
$adapter->quoteInto('e.entity_type_id = ?', $this->getProductEntityTypeId())
);
if ($from != '' && $to != '') {
$fieldName = $orderTableAliasName . '.created_at';
$orderJoinCondition[] = $this->_prepareBetweenSql($fieldName, $from, $to);
}
$this->getSelect()->reset()
->from(
array('order_items' => $this->getTable('sales/order_item')),
array(
'ordered_qty' => 'SUM(order_items.qty_ordered)',
'order_items_name' => 'order_items.name'
))
->joinInner(
array('order' => $this->getTable('sales/order')),
implode(' AND ', $orderJoinCondition),
array())
->joinLeft(
array('e' => $this->getProductEntityTableName()),
implode(' AND ', $productJoinCondition),
array(
'entity_id' => 'order_items.product_id',
'entity_type_id' => 'e.entity_type_id',
'attribute_set_id' => 'e.attribute_set_id',
'type_id' => 'e.type_id',
'sku' => 'e.sku',
'has_options' => 'e.has_options',
'required_options' => 'e.required_options',
'created_at' => 'e.created_at',
'updated_at' => 'e.updated_at'
))
->where('e.type_id = ?', 'simple')
->group('order_items.product_id')
->having('SUM(order_items.qty_ordered) > ?', 0);
return $this;
}
}
We can simply change that line by copying magento_roo/app/code/core/Mage/Reports/Model/Resource/Product/Sold/Collection.php to a local pool and adding addOrderedQty method to it. But we will replace the core functionality in that way.
So, to give a brief statement of the main points – as you can see, creating extensions like this is pretty simple and teaches us how to code without overrides. In addition, the same approach can be used for other report pages in the “Products” section: Low Stock, Best Sellers, Views.
We will be glad to see your feedback or any additional questions in the comments below.
