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

1

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.