I have followed this https://magento.stackexchange.com/a/14783/34850 & copied as it is & then made several changes like name space, grid(columns/field names) & query but somehow not getting what I want. Part done so far is as below
app\etc\modules\Gj_Customproductreport.xml
<?xml version="1.0"?>
<config>
<modules>
<Gj_Customproductreport>
<active>true</active>
<codePool>local</codePool>
<version>0.1.0</version>
<depends>
<Mage_Reports />
</depends>
</Gj_Customproductreport>
</modules>
</config>
app\code\local\Gj\Customproductreport\config.xml
<?xml version="1.0"?>
<config>
<modules>
<Gj_Customproductreport>
<version>0.1.0</version>
</Gj_Customproductreport>
</modules>
<admin>
<routers>
<customproductreport>
<use>admin</use>
<args>
<module>Gj_Customproductreport</module>
<frontName>customproductreport</frontName>
</args>
</customproductreport>
</routers>
</admin>
<adminhtml>
<menu>
<report>
<children>
<customproductreport translate="title" module="customproductreport">
<title>Custom Product Report</title>
<action>customproductreport/adminhtml_customproductreport</action>
</customproductreport>
</children>
</report>
</menu>
<acl>
<resources>
<all>
<title>Allow Everything</title>
</all>
<admin>
<children>
<report>
<children>
<customproductreport translate="title" module="customproductreport">
<title>Custom Product Report</title>
<action>customproductreport/adminhtml_customproductreport</action>
</customproductreport>
</children>
</report>
</children>
</admin>
</resources>
</acl>
<layout>
<updates>
<customproductreport>
<file>customproductreport.xml</file>
</customproductreport>
</updates>
</layout>
</adminhtml>
<global>
<models>
<customproductreport>
<class>Gj_Customproductreport_Model</class>
<resourceModel>customproductreport</resourceModel>
</customproductreport>
</models>
<resources>
<customproductreport_setup>
<setup>
<module>Gj_Customproductreport</module>
</setup>
<connection>
<use>core_setup</use>
</connection>
</customproductreport_setup>
<customproductreport_write>
<connection>
<use>core_write</use>
</connection>
</customproductreport_write>
<customproductreport_read>
<connection>
<use>core_read</use>
</connection>
</customproductreport_read>
</resources>
<blocks>
<customproductreport>
<class>Gj_Customproductreport_Block</class>
</customproductreport>
</blocks>
<helpers>
<customproductreport>
<class>Gj_Customproductreport_Helper</class>
</customproductreport>
</helpers>
</global>
</config>
app\design\adminhtml\default\default\layout\customproductreport.xml
<?xml version="1.0"?>
<layout version="0.1.0">
<customproductreport_adminhtml_customproductreport_index>
<reference name="content">
<block type="customproductreport/adminhtml_customproductreport" name="customproductreport" />
</reference>
</customproductreport_adminhtml_customproductreport_index>
</layout>
app\code\local\Gj\Customproductreport\Block\Customproductreport.php
<?php class Gj_Customproductreport_Block_Customproductreport extends Mage_Core_Block_Template {
public function _prepareLayout() {
return parent::_prepareLayout();
}
}
app\code\local\Gj\Customproductreport\Block\Adminhtml\Customproductreport.php
<?php class Gj_Customproductreport_Block_Adminhtml_Customproductreport extends Mage_Adminhtml_Block_Widget_Grid_Container {
public function __construct() {
$this->_controller = 'adminhtml_customproductreport';
$this->_blockGroup = 'customproductreport';
$this->_headerText = Mage::helper('customproductreport')->__('Custom Product Report');
parent::__construct();
$this->_removeButton('add');
}
}
app\code\local\Gj\Customproductreport\Block\Adminhtml\Customproductreport\Grid.php
<?php class Gj_Customproductreport_Block_Adminhtml_Customproductreport_Grid extends Mage_Adminhtml_Block_Report_Grid {
public function __construct() {
parent::__construct();
$this->setId('customproductreportGrid');
$this->setDefaultSort('created_at');
$this->setDefaultDir('ASC');
$this->setSaveParametersInSession(true);
$this->setSubReportSize(false);
}
protected function _prepareCollection() {
parent::_prepareCollection();
$this->getCollection()->initReport('customproductreport/customproductreport');
return $this;
}
protected function _prepareColumns() {
$this->addColumn('sku', array(
'header' =>Mage::helper('reports')->__('Product Sku'),
'index' =>'sku'
));
$this->addColumn('name', array(
'header' =>Mage::helper('reports')->__('Product Name'),
'index' =>'name'
));
$this->addColumn('qty_ordered', array(
'header' =>Mage::helper('reports')->__('Qty Ordered'),
'index' =>'qty_ordered'
));
$this->addColumn('current_stock', array(
'header' =>Mage::helper('reports')->__('Current Stock'),
'index' =>'current_stock'
));
$this->addColumn('difrnc', array(
'header' =>Mage::helper('reports')->__('Difference'),
'index' =>'difrnc'
));
$this->addColumn('costing', array(
'header' =>Mage::helper('reports')->__('Cost Per Unit'),
'index' =>'costing'
));
$this->addColumn('totcsttostock', array(
'header' =>Mage::helper('reports')->__('Total Cost to Stock up'),
'index' =>'totcsttostock'
));
$this->addExportType('*/*/exportCsv', Mage::helper('customproductreport')->__('CSV'));
$this->addExportType('*/*/exportXml', Mage::helper('customproductreport')->__('XML'));
return parent::_prepareColumns();
}
public function getRowUrl($row) {
return false;
}
public function getReport($from, $to) {
if ($from == '') {
$from = $this->getFilter('report_from');
}
if ($to == '') {
$to = $this->getFilter('report_to');
}
$totalObj = Mage::getModel('reports/totals');
$totals = $totalObj->countTotals($this, $from, $to);
$this->setTotals($totals);
$this->addGrandTotals($totals);
return $this->getCollection()->getReport($from, $to);
}
}
app\code\local\Gj\Customproductreport\controllers\Adminhtml\CustomproductreportController.php
<?php class Gj_Customproductreport_Adminhtml_CustomproductreportController extends Mage_Adminhtml_Controller_Action {
protected function _initAction() {
$this->loadLayout();
return $this;
}
public function indexAction() {
$this->_initAction()
->renderLayout();
}
public function exportCsvAction() {
$fileName = 'customproductreport.csv';
$content = $this->getLayout()->createBlock('customproductreport/adminhtml_customproductreport_grid')
->getCsv();
$this->_sendUploadResponse($fileName, $content);
}
public function exportXmlAction() {
$fileName = 'customproductreport.xml';
$content = $this->getLayout()->createBlock('customproductreport/adminhtml_customproductreport_grid')
->getXml();
$this->_sendUploadResponse($fileName, $content);
}
protected function _sendUploadResponse($fileName, $content, $contentType='application/octet-stream') {
$response = $this->getResponse();
$response->setHeader('HTTP/1.1 200 OK', '');
$response->setHeader('Pragma', 'public', true);
$response->setHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0', true);
$response->setHeader('Content-Disposition', 'attachment; filename=' . $fileName);
$response->setHeader('Last-Modified', date('r'));
$response->setHeader('Accept-Ranges', 'bytes');
$response->setHeader('Content-Length', strlen($content));
$response->setHeader('Content-type', $contentType);
$response->setBody($content);
$response->sendResponse();
die;
}
}
app\code\local\Gj\Customproductreport\Helper\Data.php
<?php class Gj_Customproductreport_Helper_Data extends Mage_Core_Helper_Abstract{}
app\code\local\Gj\Customproductreport\Model\Customproductreport.php
<?php class Gj_Customproductreport_Model_Customproductreport extends Mage_Reports_Model_Mysql4_Order_Collection
{
function __construct() {
parent::__construct();
$this->setResourceModel('sales/order_item');
$this->_init('sales/order_item','item_id');
}
public function setDateRange($from, $to) {
$this->_reset();
$this->getSelect()
->joinInner(array('i' => $this->getTable('sales/order_item')),'i.order_id = main_table.entity_id', 'SUM(i.qty_ordered) AS ordered_qty')
->joinLeft(array('ps' => $this->getTable('cataloginventory/stock_item')), 'ps.product_id=i.product_id', 'ps.qty as current_stock, (ps.qty - SUM(i.qty_ordered)) AS difrnc')
->joinLeft(array('e' => $this->getTable('catalog/product')), '(e.type_id NOT IN ("grouped", "configurable", "bundle")) AND e.entity_id = i.product_id AND e.entity_type_id = 4')
->joinLeft(array('c' => 'catalog_product_entity_decimal'), 'c.entity_id=i.product_id and c.attribute_id=79', 'c.value as costing, ((ps.qty - SUM(i.qty_ordered)) * c.value) AS totcsttostock')
->where('i.parent_item_id is null')
->where("i.created_at BETWEEN '".$from."' AND '".$to."'")
->where('main_table.state = \'complete\'')
->columns(array('ordered_qty' => 'count(distinct main_table.entity_id)'));
echo $this->getSelect();
return $this;
}
public function setStoreIds($storeIds)
{
return $this;
}
}
So After implementing mentioned code, I got this
Now I want some changes on which I am finding issue like the 1st, 2nd & 3rd mentioned in image above
1) Not able to make each fields searchable & sortable as other magento grids(ie. product, sales orders, etc.).
2) Not able to remove "show by" filters as I think, It creates issue in fetching results
3) Not able to remove first field = "period" in grid
4) I think the query is complicated on which I have already raised question( convert sql query to magento collection )
The direct SQL query which I want to get in as magento collection should be (in app\code\local\Gj\Customproductreport\Model\Customproductreport.php)
SELECT SUM(order_items.qty_ordered) AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`product_id` AS `entity_id`, `e`.`entity_type_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`sku`, `order`.`created_at`, `order`.`updated_at`, `ps`.`qty` AS `current_stock`, (ps.qty - SUM(order_items.qty_ordered)) AS `difrnc`, `c`.`value` as `costing`, ((ps.qty - SUM(order_items.qty_ordered)) * `c`.`value`) AS `totcsttostock`
FROM `sales_flat_order_item` AS `order_items`
INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state <> 'canceled'
LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4
LEFT JOIN `cataloginventory_stock_item` AS `ps` ON ps.product_id=order_items.product_id
LEFT JOIN `catalog_product_entity_decimal` AS `c` ON c.entity_id=order_items.product_id and c.attribute_id=79
WHERE `order`.`created_at` BETWEEN '2017-09-22 00:00:00' AND '2017-09-22 23:59:59' and (parent_item_id IS NULL)
GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) ORDER BY `ordered_qty` desc
But somehow unable to make it.
Please suggest some solution or Help looking into
