1

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 enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.