I would like to create a reposrt which gives the result of customer those haven't created any orders in last 30 days.
I referred below:
Creating new completely custom reports
By referring above link I can create a menu under report tab and upon clicking it redirect to the page. But I could not able to render the query result into the grid.
Someone please help me.
Below are my module folder structure:
app
etc
modules
UCS_Report.xml
code
local
UCS
Report
etc
config.xml
Model
Lastpurchase.php
Block
Adminhtml
Purchasereport.php
Purchasereport
Grid.php
controllers
Adminhtml
PurchasereportController.php
Helper
etc/confix.xml as below
<?xml version="1.0"?>
<config>
<modules>
<UCS_Report>
<version>0.1.0</version>
</UCS_Report>
</modules>
<admin>
<routers>
<latspurchase>
<use>admin</use>
<args>
<module>UCS_Report</module>
<frontName>latspurchase</frontName>
</args>
</latspurchase>
</routers>
</admin>
<adminhtml>
<menu>
<report translate="title" module="reports">
<children>
<purchase_report translate="title" module="reports">
<title>Purchase Report</title>
<sort_order>10</sort_order>
<children>
<last_product translate="title" module="reports">
<title>Last Purchase</title>
<action>latspurchase/adminhtml_purchasereport</action>
</last_product>
</children>
</purchase_report>
</children>
</report>
</menu>
<acl>
<resources>
<all>
<title>Allow Everything</title>
</all>
<admin>
<children>
<report>
<children>
<last_product translate="title" module="reports">
<title>Last Purchase</title>
<action>latspurchase/adminhtml_purchasereport</action>
</last_product>
</children>
</report>
</children>
</admin>
</resources>
</acl>
<layout>
<updates>
<latspurchase>
<file>purchasereport.xml</file>
</latspurchase>
</updates>
</layout>
</adminhtml>
<global>
<models>
<report>
<class>UCS_Report_Model</class>
<resourceModel>report</resourceModel>
</report>
</models>
<resources>
<report_setup>
<setup>
<module>UCS_Report</module>
</setup>
<connection>
<use>core_setup</use>
</connection>
</report_setup>
<report_write>
<connection>
<use>core_write</use>
</connection>
</report_write>
<report_read>
<connection>
<use>core_read</use>
</connection>
</report_read>
</resources>
<blocks>
<lastpurchase>
<class>UCS_Report_Block</class>
</lastpurchase>
</blocks>
<helpers>
<lastpurchasereport>
<class>UCS_Report_Helper</class>
</lastpurchasereport>
</helpers>
<routers>
<latspurchase>
<use>standard</use>
<args>
<module>UCS_Report</module>
<frontName>latspurchase</frontName>
</args>
</latspurchase>
</routers>
</global>
</config>
Model/Lastpurchase.php
<?php
class UCS_Report_Model_Lastpurchase extends Mage_Reports_Model_Mysql4_Order_Collection {
}
?>
Block/Adminhtml/Purchasereport.php
<?php
class UCS_Report_Block_Adminhtml_Purchasereport extends Mage_Adminhtml_Block_Widget_Grid_Container {
public function __construct() {
$this->_controller = 'adminhtml_purchasereport';
$this->_blockGroup = 'lastpurchase';
$this->_headerText = Mage::helper('lastpurchasereport')->__('Last Purchase History');
parent::__construct();
$this->_removeButton('add');
}
}
Block/Adminhtml/Purchasereport/Grid.php
<?php
//class UCS_Report_Block_Adminhtml_Purchasereport_Grid extends Mage_Adminhtml_Block_Report_Grid {
class UCS_Report_Block_Adminhtml_Purchasereport_Grid extends Mage_Adminhtml_Block_Report_Grid {
public function __construct() {
parent::__construct();
$this->setId('mymoduleGrid');
$this->setDefaultSort('created_at');
$this->setDefaultDir('ASC');
$this->setSaveParametersInSession(true);
$this->setSubReportSize(false);
}
protected function _prepareCollection() {
parent::_prepareCollection();
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$sql= "select DATEDIFF(CURDATE(), x.created_at) AS DiffDate, x.created_at as order_date, x.customer_email, case
when x.customer_type = 0
then 'Registered'
when x.customer_type = 1
then 'Not Registered'
else 'None' END as user_type from(
SELECT `customer_email`, MAX(created_at)as created_at, `customer_is_guest` as customer_type FROM `sales_flat_order` GROUP BY `customer_email` ORDER BY `created_at` DESC ) as x WHERE DATEDIFF(CURDATE(), x.created_at) > 30";
$rows = $connection->fetchAll($sql);
//echo "<pre>";print_r($rows);echo "</pre>";
//echo 'Test'; die;
$this->getCollection()->initReport('mymodule/mymodule');
return $this;
}
protected function _prepareColumns() {
$this->addColumn('customer_email', array(
'header' =>Mage::helper('reports')->__('Customer Email'),
'align' =>'right',
'index' =>'customer_email',
'total' =>'sum',
'type' =>'number'
));
$this->addColumn('customer_type', array(
'header' =>Mage::helper('reports')->__('Customer Type'),
'align' =>'right',
'index' =>'customer_type',
'total' =>'sum',
'type' =>'number'
));
$this->addColumn('order_date', array(
'header' =>Mage::helper('reports')->__('Last Order Date'),
'align' =>'right',
'index' =>'order_date',
'total' =>'sum',
'type' =>'number'
));
$this->addColumn('DiffDate', array(
'header' =>Mage::helper('reports')->__('No. of Days Since Last Order'),
'align' =>'right',
'index' =>'DiffDate',
'total' =>'sum',
'type' =>'number'
));
$this->addExportType('*/*/exportCsv', Mage::helper('lastpurchasereport')->__('CSV'));
$this->addExportType('*/*/exportXml', Mage::helper('lastpurchasereport')->__('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);*/
echo 'UCS POP';
return $this->getCollection()->getReport($from, $to);
}
}
controllers/PurchasereportContoller.php
<?php
class UCS_Report_Adminhtml_PurchasereportController extends Mage_Adminhtml_Controller_Action {
protected function _initAction() {
$this->loadLayout();
return $this;
}
public function indexAction() {
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$sql= "select DATEDIFF(CURDATE(), x.created_at) AS DiffDate, x.created_at as order_date, x.customer_email, case
when x.customer_type = 0
then 'Registered'
when x.customer_type = 1
then 'Not Registered'
else 'None' END as user_type from(
SELECT `customer_email`, MAX(created_at)as created_at, `customer_is_guest` as customer_type FROM `sales_flat_order` GROUP BY `customer_email` ORDER BY `created_at` DESC ) as x WHERE DATEDIFF(CURDATE(), x.created_at) > 30";
$rows = $connection->fetchAll($sql);
Mage::register('rows', $rows);
$this->_initAction()->renderLayout();
}
}
app/design/adminhtml/default/default/layout/purchasereport.xml
<?xml version="1.0"?>
<layout version="0.1.0">
<latspurchase_adminhtml_purchasereport_index>
<reference name="content">
<block type="lastpurchase/adminhtml_purchasereport" name="lastpurchase" />
<!--<block type="my_reports/adminhtml_report" template="report/grid/container.phtml" name="my_reports_report_grid_container">
<block type="my_reports/adminhtml_filter_form" name="grid.filter.form" />
</block>-->
<block type="lastpurchase/adminhtml_purchasereport" template="report/last_purchase.phtml" name="last_purchase_data" />
</reference>
</latspurchase_adminhtml_purchasereport_index>
</layout>
