2

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>
0

1 Answer 1

6

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;

Customers that have not ordered in the past 30 days

2
  • Its amazing. Working fine. Anyway I fixed the issue on my code also.
    – Butterfly
    Jan 17, 2016 at 4:35
  • What was the issue with your code? Could you update your question so others will know what to look for in the future? Jan 17, 2016 at 9:00

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.