Want to create a custom report in Magento Admin?
After taking help from some forums & all I was able to generate a new Report the way I wanted.
I was looking to generate the Report for the Products sold along with the name of the Artist to whom the product belongs to.
These are the steps to be followed / I followed.
1. The title of the report is: ‘Artist Sold Works’. To add the new item under the Reports -> Products.
Open the ‘app/code/code/Mage/Reports/etc/config.xml’
Add the followind code in the ‘children of ‘products’ (near line 221).
<title>Artist Sold Works</title> adminhtml/report_product/artistsold
Add the followind code in the of
(near line 370).
<title>Artists Sold Works</title>
2. Copy files
app/code/core/Mage/Adminhtml/Block/Report/Product/Sold.php to app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold.php.
3. Copy directories
app/code/core/Mage/Adminhtml/Block/Report/Product/Sold to
app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold
app/code/core/Mage/Reports/Model/Mysql4/Product/Sold to
app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold
4. In the file Artistsold.php, change the class name from
Mage_Adminhtml_Block_Report_Product_Sold to Mage_Adminhtml_Block_Report_Product_Artistsold.
Change the lines
$this->_controller = 'report_product_sold';
$this->_headerText = Mage::helper('reports')->__('Products Ordered');
to
$this->_controller = 'report_product_artistsold';
$this->_headerText = Mage::helper('reports')->__('Artist Sold Works');
5. Add/Modify the columns in the
app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold/Grid.php
Here in my case:
$this->addColumn('artistId', array(
'header' =>Mage::helper('reports')->__('Artist'),
'width' =>'120px',
'index' =>'artistname',
));
$this->addColumn('sale_percentage', array(
'header' =>Mage::helper('reports')->__('Artist Share'),
'width' =>'60px',
'index' =>'sale_percentage',
'align' =>'right'
));
$this->addColumn('base_price_total', array(
'header' =>Mage::helper('reports')->__('Total Product Base Price ($)'),
'width' =>'60px',
'index' =>'base_price_total',
'align' =>'right',
'total' =>'sum',
'type' =>'number'
));
$this->addColumn('artist_earned', array(
'header' =>Mage::helper('reports')->__('Artist Earned ($)'),
'width' =>'60px',
'index' =>'artist_earned',
'align' =>'right',
'total' =>'sum',
'type' =>'number'
));
6. Add new functions to
app/code/core/Mage/Adminhtml/controllers/Report/ProductController.php
public function artistsoldAction()
{
$this->_initAction()
->_setActiveMenu('report/product/artistsold')
->_addBreadcrumb(Mage::helper('reports')->__('Artists Sold Works'), Mage::helper('reports')->__('Artists Sold Works'))
->_addContent($this->getLayout()->createBlock('adminhtml/report_product_artistsold'))
->renderLayout();
}
7. Open the file
app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold/Collection.php.
Rename the class name from
Mage_Reports_Model_Mysql4_Product_Sold_Collection to
Mage_Reports_Model_Mysql4_Product_Artistsold_Collection
Customize the function setDateRange() in the as per your need.
Here in my case:
public function setDateRange($frmdate, $todate)
{
$this->_reset()
->addAttributeToSelect('*')
->addOrderedQtyForArtistSold($frmdate,$todate);
return $this;
}
8. To get the new fields, to alter the sql query I copied the function addOrderedQty() to addOrderedQtyForArtistSold() in the file
app/code/core/Mage/Reports/Model/Mysql4/Product/Collection.php
And I did changes in the functions as per my need to get the extra columns.
Here in my case:
public function addOrderedQtyForArtistSold($frm = '', $to = '')
{
if(key_exists('report',$_SESSION)) {
$artistId = $_SESSION['report']['artistid'];
}
else {
$artistId ='';
}
$qtyOrderedTableName = $this->getTable('sales/order_item');
$qtyOrderedFieldName = 'qty_ordered';
$productIdTableName = $this->getTable('sales/order_item');
$productIdFieldName = 'product_id';
$productEntityIntTable = (string)Mage::getConfig()->getTablePrefix() . 'catalog_product_entity_varchar';
$adminUserTable = $this->getTable('admin_user');
$artistsTable = $this->getTable('appartists');
$eavAttributeTable = $this->getTable('eav/attribute');
$compositeTypeIds = Mage::getSingleton('catalog/product_type')->getCompositeTypes();
# This was added by Dev1 to get the configurable items in the list & not to get the simple products
$compositeTypeIds = Array (
'0' => 'grouped',
'1' => 'simple',
'2' => 'bundle'
);
$productTypes = $this->getConnection()->quoteInto(' AND (e.type_id NOT IN (?))', $compositeTypeIds);
if ($frm != '' && $to != '') {
$dateFilter = " AND `order`.created_at BETWEEN '{$frm}' AND '{$to}'";
} else {
$dateFilter = "";
}
$this->getSelect()->reset()->from(
array('order_items' => $qtyOrderedTableName),
array('ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})",'base_price_total' => "SUM(order_items.price)")
);
$order = Mage::getResourceSingleton('sales/order');
$stateAttr = $order->getAttribute('state');
if ($stateAttr->getBackend()->isStatic()) {
$_joinCondition = $this->getConnection()->quoteInto(
'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED
);
$_joinCondition .= $dateFilter;
$this->getSelect()->joinInner(
array('order' => $this->getTable('sales/order')),
$_joinCondition,
array()
);
} else {
$_joinCondition = 'order.entity_id = order_state.entity_id';
$_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.attribute_id=? ', $stateAttr->getId());
$_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.value<>? ', Mage_Sales_Model_Order::STATE_CANCELED);
$this->getSelect()
->joinInner(
array('order' => $this->getTable('sales/order')),
'order.entity_id = order_items.order_id' . $dateFilter,
array())
->joinInner(
array('order_state' => $stateAttr->getBackend()->getTable()),
$_joinCondition,
array());
}
$this->getSelect()
->joinInner(array('e' => $this->getProductEntityTableName()),
"e.entity_id = order_items.{$productIdFieldName}")
->group('e.entity_id')
->having('ordered_qty > 0');
$artistIdConcat = $artistId != '' ? " AND artistId=$artistId" : "";
$this->getSelect()
->joinInner(
array('pei' => $productEntityIntTable),
"e.entity_id = pei.entity_id",
array())
->joinInner(
array('ea' => $eavAttributeTable),
"pei.attribute_id=ea.attribute_id AND ea.attribute_code='artistid'",
array())
->joinInner(
array('au' => $adminUserTable),
"au.user_id=pei.value",
array("artistname" => "CONCAT(firstname, ' ',lastname)"))
->joinInner(
array('ar' => $artistsTable),
"ar.artistId=au.user_id".$artistIdConcat,
array("sale_percentage" => "CONCAT(sale_percentage,'%')","artist_earned" => "((SUM(order_items.price)) * (sale_percentage)) / 100"));
return $this;
}
Related articles
- Magento Vs OsCommerce Comparison for Online Store Owners (seanphpremoteworks.wordpress.com)
- How overwrite magento core block and model for Catalog advanced search (stackoverflow.com)
- Magento folder Sturcture (singhnarendra434.wordpress.com)
- Magento Installation Problem (hardikpatel78501.wordpress.com)
- Get to grips with Magento using Packt’s new book and eBook (packtpub.com)
- Magento : How to add new action in row at admin grid in magneto (nikunjvadariya.wordpress.com)
- Login Problem in Magento as Localhost Admin (getpcsolutions.wordpress.com)
Jeremy's Blog 
Reblogged this on Simple PHP Blog.
It is against best practice to modify core files like that
Lot of thank to you
Hi Jeremy, I’m glad to find your blog post, that’s exactly what I need to to for a customer 🙂
I don’t understand how the artist name is associated to products ? Is artistId a custom metadata you added to the database ?
Thanks