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>
Copy files
app/code/core/Mage/Adminhtml/Block/Report/Product/Sold.php to app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold.php.
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;
}