Create custom Reports in Magento Admin

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;
   }
Artist_sold_works_report
Advertisements

4 thoughts on “Create custom Reports in Magento Admin

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s