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.
1. The title of the report is: ‘Artist Sold Works’. To add the new item under the Reports -> Products.
Open the ‘app/code/core/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 line (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.
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;
}
Hope it Helps... Thanks...
