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>
Best Answer
There's a fantastic little extension out there that does most of the heavy lifting for you, kalenjordan/custom-reports. Here is some more info on magemail.co.
Install the extension, go to Reports > Special Reports > Add Report, name it 'Customers that have not ordered in the past 30 days' and paste your SQL in there.
Then save it, and click 'Table'. You'll see something like this;