This post is going to be LONG. And not fun. To be honest, I’m not sure how some of this stuff works, but I do know that I have successfully created two different kinds of reports.
Some things to note:
- You should never create reports off of a transactional table. Running the report could potentially create locks that would affect your customer facing website.
- This blog post will not contain examples of “aggregation observers and tables”, but you should use them when creating reports. You run them through cron and an example observer can be found here: Mage_Sales_Model_Observer.
Let’s get started shall we? I will give examples of two different kinds of reports in this post. They will be called:
- Simple – A report that has the thin/horizontal form for selecting “to”, “from” and “period”. (See Reports > Customers > New Accounts)
- Complex – A report that has a seperatly defined filter block for filtering the report data. ( See Reports > Products > Best Sellers)
Simple Report
Table structure (setup script):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<<?php $installer = $this; $installer->startSetup(); $installer->run(" -- DROP TABLE IF EXISTS {$this->getTable('super_awesome_example_simple')}; CREATE TABLE {$this->getTable('super_awesome_example_simple')} ( `id` INT NOT NULL AUTO_INCREMENT , `description` VARCHAR( 100 ) NOT NULL , `value` DECIMAL(12,2) NOT NULL , `period` DATE NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM ; INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example One Description', 10.00, '2011-02-01'); INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Two Description', 12.50, '2011-02-15'); INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Three Description', 5.35, '2011-03-01'); INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Four Description', 7.67, '2011-03-04'); INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Dupe', 1.23, '2011-03-01'); INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Dupe', 2.34, '2011-03-02'); INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Dupe', 3.45, '2011-03-03'); "); $installer->endSetup(); |
The types of classes needed:
- Report Controller
- Report Block
- Report Grid
- Configs (Menu and Table Definitions)
- Model, Resource Model, Collection Model
The Folder Structure (can be different if you want it to be):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Super |_ Awesome |_Block | |_Adminhtml | |_Report | |_Simple | | |_Grid.php | |_Simple.php |_controllers | |_Adminhtml | |_Report | |_ExampleController.php |_etc | |_adminhtml.xml | |_config.xml |_Helper | |_Data.php |_Model |_Mysql4 | |_Report | | |_Simple | | |_Collection.php | |_Simple.php |_Simple.php |
The first thing we need is to create a menu item to get to the report. Most likely, you will put your reports under the “Report” section, but I choose to put it in my own section:
Contents of adminhtml.xml –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?xml version="1.0"?> <config> <menu> <awesome translate="title" module="awesome"> <title>Awesome</title> <sort_order>15</sort_order> <children> <simple translate="title" module="awesome"> <title>Simple Report</title> <sort_order>1</sort_order> <action>adminhtml/report_example/simple</action> </simple> </children> </awesome> </menu> </config> |
Now that you have an “action” element pointing to “adminhtml/report_example/simple”, we need to make sure that route works. To do that, we need to configure the route in the config.xml and then create the controller. While we are in the config.xml, I will put my table definitions in.
Partial of config.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<admin> <!-- Here we are telling the Magento router to look for the controllers in the Super_Awesome_controllers_Adminhtml before we look in the Mage_Adminhtml module for all urls that begin with /admin/controller_name --> <routers> <adminhtml> <args> <modules> <awesome before="Mage_Adminhtml">Super_Awesome_Adminhtml</awesome> </modules> </args> </adminhtml> </routers> </admin> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
<models> <awesome> <class>Super_Awesome_Model</class> <resourceModel>awesome_mysql4</resourceModel> </awesome> <awesome_mysql4> <class>Super_Awesome_Model_Mysql4</class> <entities> <simple> <table>super_awesome_example_simple</table> </simple> </entities> </awesome_mysql4> </models> <resources> <awesome_setup> <setup> <module>Super_Awesome</module> </setup> <connection> <use>core_setup</use> </connection> </awesome_setup> <awesome_write> <connection> <use>core_write</use> </connection> </awesome_write> <awesome_read> <connection> <use>core_read</use> </connection> </awesome_read> </resources> |
The contents of Super_Awesome_Adminhtml_Report_ExampleController:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
<?php class Super_Awesome_Adminhtml_Report_ExampleController extends Mage_Adminhtml_Controller_Action { public function _initAction() { $this->loadLayout() ->_addBreadcrumb(Mage::helper('awesome')->__('Awesome'), Mage::helper('awesome')->__('Awesome')); return $this; } public function simpleAction() { $this->_title($this->__('Awesome'))->_title($this->__('Reports'))->_title($this->__('Simple Report')); $this->_initAction() ->_setActiveMenu('awesome/report') ->_addBreadcrumb(Mage::helper('awesome')->__('Simple Example Report'), Mage::helper('awesome')->__('Simple Example Report')) ->_addContent($this->getLayout()->createBlock('awesome/adminhtml_report_simple')) ->renderLayout(); } public function exportSimpleCsvAction() { $fileName = 'simple.csv'; $content = $this->getLayout()->createBlock('awesome/adminhtml_report_simple_grid') ->getCsv(); $this->_prepareDownloadResponse($fileName, $content); } } |
The line that says: “->_addContent($this->getLayout()->createBlock(‘awesome/adminhtml_report_simple’))” really is the important one here. This is telling Magento which block to use to drive the WHOLE report. Oh, and look at the exportSimpleCsvAction() – that all you need to export a grid into CSV.
So let’s take a look at the block Super_Awesome_Block_Adminhtml_Report_Simple –
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?php class Super_Awesome_Block_Adminhtml_Report_Simple extends Mage_Adminhtml_Block_Widget_Grid_Container { public function __construct() { $this->_blockGroup = 'awesome'; $this->_controller = 'adminhtml_report_simple'; $this->_headerText = Mage::helper('awesome')->__('Simple Report'); parent::__construct(); $this->_removeButton('add'); } } |
There isn’t much to it. It will use the parent class to auto generate the name of the “grid” block (aka the block with all of the report data):
Here is the content of that block (Super_Awesome_Block_Adminhtml_Report_Simple_Grid) –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<?php class Super_Awesome_Block_Adminhtml_Report_Simple_Grid extends Mage_Adminhtml_Block_Report_Grid { public function __construct() { parent::__construct(); $this->setId('gridSimple'); } protected function _prepareCollection() { parent::_prepareCollection(); $this->getCollection()->initReport('awesome/report_simple_collection'); } protected function _prepareColumns() { $this->addColumn('description', array( 'header' =>Mage::helper('reports')->__('Description'), 'index' =>'description', 'sortable' => false )); $currencyCode = $this->getCurrentCurrencyCode(); $this->addColumn('value', array( 'header' =>Mage::helper('reports')->__('Value'), 'index' =>'value', 'currency_code' => $currencyCode, 'total' =>'sum', 'type' =>'currency' )); $this->addExportType('*/*/exportSimpleCsv', Mage::helper('reports')->__('CSV')); return parent::_prepareColumns(); } } |
The important parts:
- The fact that it extends Mage_Adminhtml_Block_Report_Grid – This will require you to do things specific ways and will also automatically put the filter piece at the top of your report so you can query by date and period.
- The collection that it is using – By default it uses the Mage::getResourceModel(‘reports/report_collection’) collection, but you need to call the initReport() function on that collection with the collection class that will handle YOUR data. Ours is: initReport(‘awesome/report_simple_collection’)
- The columns – Like I said earlier, you really should be running these reports on “already aggregated” data, but you still might want to sum some things up for totals. Any column that has a ‘total’ => ‘sum” attribute will be included in the total section correctly.
- The export types – One thing that I discovered while doing this is that every grid has the ability to export itself as CSV or XML file. Cool huh?
We mentioned the collection in #2, so let’s take a look at that. The contents of the collection –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
<?php class Super_Awesome_Model_Mysql4_Report_Simple_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract { protected function _construct() { $this->_init('awesome/simple'); } protected function _joinFields($from = '', $to = '') { $this->addFieldToFilter('period' , array("from" => $from, "to" => $to, "datetime" => true)); $this->getSelect()->group('description'); $this->getSelect()->columns(array('value' => 'SUM(value)')); return $this; } public function setDateRange($from, $to) { $this->_reset() ->_joinFields($from, $to); return $this; } public function load($printQuery = false, $logQuery = false) { if ($this->isLoaded()) { return $this; } parent::load($printQuery, $logQuery); return $this; } public function setStoreIds($storeIds) { return $this; } } |
There really isn’t anything special about this collection. It is a normal collection that inits the resource model (so you will need a normal one of those). The only hitch here is that the Mage_Adminhtml_Block_Report_Grid is expecting specific methods to be in this collection. Those methods are:
- public function setStoreIds($storeIds)
- public function setDateRange($from, $to)
I am not running the query by store, so I don’t care about that. There are examples in other classes if you need to see how to do it, but basically it is adding the store_id to the where clause.
I do however want to add some date range stuff, so I simply call a protected method in my collection to set up the SQL.
If you are having trouble with your SQL, you can debug it easily by changing your load to: parent::load(true, true). This will print out the SQL to the screen and the system.log. Also something that stumped me for a while was that my data was not necessarily showing up on the correct date in my report. This has to do with some code in Mage_Reports_Model_Mysql4_Report_Collection class.
1 2 3 4 5 6 7 8 9 |
public function getReportFull($from, $to) { return $this->_model->getReportFull($this->timeShift($from), $this->timeShift($to)); } public function timeShift($datetime) { return Mage::app()->getLocale()->utcDate(null, $datetime, true, Varien_Date::DATETIME_INTERNAL_FORMAT)->toString(Varien_Date::DATETIME_INTERNAL_FORMAT); } |
It does some “time shifting” based on locale, so BE AWARE.
The only other things I haven’t talked about are the (empty) Data.php (helper) –
1 2 3 4 5 6 |
<?php class Super_Awesome_Helper_Data extends Mage_Core_Helper_Abstract { } |
…and the normal model class that you should already know how to create. All that has is the init of the resource model in the construct –
1 2 3 4 5 6 7 8 9 10 |
<?php class Super_Awesome_Model_Simple extends Mage_Core_Model_Abstract { protected function _construct() { parent::_construct(); $this->_init('awesome/simple'); } } |
Complex Report
With this, you will see a lot of duplication, but I want to be thorough.
The table structure (setup script):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<?php $installer = $this; $installer->startSetup(); $installer->run(" -- DROP TABLE IF EXISTS {$this->getTable('super_awesome_example_complex')}; CREATE TABLE {$this->getTable('super_awesome_example_complex')} ( `id` INT NOT NULL AUTO_INCREMENT , `description` VARCHAR( 100 ) NOT NULL , `value` DECIMAL(12,2) NOT NULL , `period` DATE NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM ; INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example One Description', 10.00, '2011-02-01'); INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Two Description', 12.50, '2011-02-15'); INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Three Description', 5.35, '2011-03-01'); INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Four Description', 7.67, '2011-03-04'); INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Dupe', 1.23, '2011-03-01'); INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Dupe', 2.34, '2011-03-02'); INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Dupe', 3.45, '2011-03-03'); "); $installer->endSetup(); |
The types of classes needed:
- Report Controller
- Report Block
- Report Grid
- Configs (Menu and Table Definitions)
- Model, Resource Model, Collection Model
- The layout.
The Folder Structure (can be different if you want it to be):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Super |_ Awesome |_Block | |_Adminhtml | |_Report | |_Complex | | |_Grid.php | |_Complex.php |_controllers | |_Adminhtml | |_Report | |_ExampleController.php |_etc | |_adminhtml.xml | |_config.xml |_Helper | |_Data.php |_Model |_Mysql4 | |_Report | | |_Complex | | |_Collection.php | |_Complex.php |_Complex.php And we also have the awesome.xml in design/adminhtml/default/default/layout. |
First we create the menu item –
Contents of adminhtml.xml –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?xml version="1.0"?> <config> <menu> <awesome translate="title" module="awesome"> <title>Awesome</title> <sort_order>15</sort_order> <children> <complex translate="title" module="awesome"> <title>Complex Report</title> <sort_order>1</sort_order> <action>adminhtml/report_example/complex</action> </complex> </children> </awesome> </menu> </config> |
Then we create the necessary config.xml settings:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
<config> <modules> <Super_Awesome> <version>0.1.2</version> </Super_Awesome> </modules> <adminhtml> <!-- The <layout> updates allow us to define our block layouts in a seperate file so are aren't messin' with the magento layout files. --> <layout> <updates> <awesome> <file>awesome.xml</file> </awesome> </updates> </layout> </adminhtml> <admin> <!-- Here we are telling the Magento router to look for the controllers in the Super_Awesome_controllers_Adminhtml before we look in the Mage_Adminhtml module for all urls that begin with /admin/controller_name --> <routers> <adminhtml> <args> <modules> <awesome before="Mage_Adminhtml">Super_Awesome_Adminhtml</awesome> </modules> </args> </adminhtml> </routers> </admin> <global> <models> <awesome> <class>Super_Awesome_Model</class> <resourceModel>awesome_mysql4</resourceModel> </awesome> <awesome_mysql4> <class>Super_Awesome_Model_Mysql4</class> <entities> <complex> <table>super_awesome_example_complex</table> </complex> </entities> </awesome_mysql4> </models> <resources> <awesome_setup> <setup> <module>Super_Awesome</module> </setup> <connection> <use>core_setup</use> </connection> </awesome_setup> <awesome_write> <connection> <use>core_write</use> </connection> </awesome_write> <awesome_read> <connection> <use>core_read</use> </connection> </awesome_read> </resources> <blocks> <awesome> <class>Super_Awesome_Block</class> </awesome> </blocks> <helpers> <awesome> <class>Super_Awesome_Helper</class> </awesome> </helpers> </global> </config> |
Now we create our controller –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
<?php class Super_Awesome_Adminhtml_Report_ExampleController extends Mage_Adminhtml_Controller_Action { public function _initAction() { $this->loadLayout() ->_addBreadcrumb(Mage::helper('awesome')->__('Awesome'), Mage::helper('awesome')->__('Awesome')); return $this; } public function _initReportAction($blocks) { if (!is_array($blocks)) { $blocks = array($blocks); } $requestData = Mage::helper('adminhtml')->prepareFilterString($this->getRequest()->getParam('filter')); $requestData = $this->_filterDates($requestData, array('from', 'to')); $params = new Varien_Object(); foreach ($requestData as $key => $value) { if (!empty($value)) { $params->setData($key, $value); } } foreach ($blocks as $block) { if ($block) { $block->setPeriodType($params->getData('period_type')); $block->setFilterData($params); } } return $this; } public function complexAction() { $this->_title($this->__('Awesome'))->_title($this->__('Reports'))->_title($this->__('Complex Report')); $this->_initAction() ->_setActiveMenu('awesome/report') ->_addBreadcrumb(Mage::helper('awesome')->__('Complex Example Report'), Mage::helper('awesome')->__('Complex Example Report')); $gridBlock = $this->getLayout()->getBlock('adminhtml_report_complex.grid'); $filterFormBlock = $this->getLayout()->getBlock('grid.filter.form'); $this->_initReportAction(array( $gridBlock, $filterFormBlock )); $this->renderLayout(); } public function exportComplexCsvAction() { $fileName = 'complex.csv'; $grid = $this->getLayout()->createBlock('awesome/adminhtml_report_complex_grid'); $this->_initReportAction($grid); $this->_prepareDownloadResponse($fileName, $grid->getCsvFile($fileName)); } } |
So this is where the “complex” report is a little more complex. The layout that is loaded at first is from the awesome.xml file:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<adminhtml_report_example_complex> <update handle="report_awesome"> <reference name="content"> <block type="awesome/adminhtml_report_complex" template="report/grid/container.phtml" name="awesome.report.grid.container"> <block type="adminhtml/report_filter_form" name="grid.filter.form"> <action method="setFieldVisibility"> <field>report_type</field> <visibility>0</visibility> </action> </block> </block> </reference> </update></adminhtml_report_example_complex> |
So with the layout and the controller combined we know that we have to worry about 3 different blocks:
- awesome.xml: awesome/adminhtml_report_complex (Super_Awesome_Block_Adminhtml_Report_Complex)
- controller: adminhtml_report_complex.grid (Super_Awesome_Block_Adminhtml_Report_Complex_Grid)
- awesome.xml and controller: grid.filter.form (Mage_Adminhtml_Block_Report_Filter_Form) – This is a Mage class and we don’t need to touch this.
The first block is the Grid container. Here is his contents –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<?php class Super_Awesome_Block_Adminhtml_Report_Complex extends Mage_Adminhtml_Block_Widget_Grid_Container { public function __construct() { $this->_blockGroup = 'awesome'; $this->_controller = 'adminhtml_report_complex'; $this->_headerText = Mage::helper('awesome')->__('Complex Report'); $this->setTemplate('report/grid/container.phtml'); parent::__construct(); $this->_removeButton('add'); $this->addButton('filter_form_submit', array( 'label' => Mage::helper('awesome')->__('Show Report'), 'onclick' => 'filterFormSubmit()' )); } public function getFilterUrl() { $this->getRequest()->setParam('filter', null); return $this->getUrl('*/*/complex', array('_current' => true)); } } |
Every line is important, so pay close attention to this file. In the parent of this class, it has the function …
1 2 3 4 5 6 7 |
protected function _prepareLayout() { $this->setChild( 'grid', $this->getLayout()->createBlock( $this->_blockGroup.'/' . $this->_controller . '_grid', $this->_controller . '.grid')->setSaveParametersInSession(true) ); return parent::_prepareLayout(); } |
… which auto generates the block “awesome/adminhtml_report_complex_grid” with the name adminhtml_report_complex.grid. This is #2 in the “blocks we care about” list.
Here is the contents of Super_Awesome_Block_Adminhtml_Report_Complex_Grid –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
<?php class Super_Awesome_Block_Adminhtml_Report_Complex_Grid extends Mage_Adminhtml_Block_Report_Grid_Abstract { protected $_columnGroupBy = 'period'; public function __construct() { parent::__construct(); $this->setCountTotals(true); } public function getResourceCollectionName() { return 'awesome/report_complex_collection'; } protected function _prepareColumns() { $this->addColumn('period', array( 'header' => Mage::helper('awesome')->__('Period'), 'index' => 'period', 'width' => 100, 'sortable' => false, 'period_type' => $this->getPeriodType(), 'renderer' => 'adminhtml/report_sales_grid_column_renderer_date', 'totals_label' => Mage::helper('adminhtml')->__('Total'), 'html_decorators' => array('nobr'), )); $this->addColumn('description', array( 'header' =>Mage::helper('awesome')->__('Description'), 'index' =>'description', 'sortable' => false )); $currencyCode = $this->getCurrentCurrencyCode(); $this->addColumn('value', array( 'header' => Mage::helper('awesome')->__('Value'), 'currency_code' => $currencyCode, 'index' =>'value', 'type' => 'currency', 'total' => 'sum', 'sortable' => false )); $this->addExportType('*/*/exportComplexCsv', Mage::helper('awesome')->__('CSV')); return parent::_prepareColumns(); } |
Notice that we are extending Mage_Adminhtml_Block_Report_Grid_Abstract (this is different than the “simple” report example).
In here, the part that we don’t want to screw up is the getResourceCollectionName() function. It contains the Collection model that is the bane of my existance. Ready to see it?
BAAAAAAAAAAAAAAAAAM –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 |
<?php class Super_Awesome_Model_Mysql4_Report_Complex_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract { protected $_periodFormat; protected $_selectedColumns = array(); protected $_from = null; protected $_to = null; protected $_orderStatus = null; protected $_period = null; protected $_storesIds = 0; protected $_applyFilters = true; protected $_isTotals = false; protected $_isSubTotals = false; protected $_aggregatedColumns = array(); /** * Initialize custom resource model * * @param array $parameters */ public function __construct() { $this->setModel('adminhtml/report_item'); $this->_resource = Mage::getResourceModel('awesome/complex')->init('awesome/complex'); $this->setConnection($this->getResource()->getReadConnection()); $this->_applyFilters = false; } protected function _getSelectedColumns() { if (!$this->_selectedColumns) { if ($this->isTotals()) { $this->_selectedColumns = $this->getAggregatedColumns(); } else { $this->_selectedColumns = array( 'period' => 'period', 'value' => 'value', 'description' => 'description', ); if ('year' == $this->_period) { $this->_selectedColumns['period'] = 'YEAR(period)'; } else if ('month' == $this->_period) { $this->_selectedColumns['period'] = "DATE_FORMAT(period, '%Y-%m')"; } } } return $this->_selectedColumns; } protected function _initSelect() { if (!$this->_period) { $cols = $this->_getSelectedColumns(); $cols['value'] = 'SUM(value)'; $this->getSelect()->from($this->getTable('awesome/complex'), $cols); $this->_applyDateRangeFilter(); $this->getSelect() ->group('description') ->order('value DESC'); return $this; } $this->getSelect()->from($this->getTable('awesome/complex'), $this->_getSelectedColumns()); if (!$this->isTotals()) { $this->getSelect()->group(array('period', 'description')); } // $selectUnions = array(); // apply date boundaries (before calling $this->_applyDateRangeFilter()) $dtFormat = Varien_Date::DATE_INTERNAL_FORMAT; $periodFrom = (!is_null($this->_from) ? new Zend_Date($this->_from, $dtFormat) : null); $periodTo = (!is_null($this->_to) ? new Zend_Date($this->_to, $dtFormat) : null); if ('year' == $this->_period) { if ($periodFrom) { if ($periodFrom->toValue(Zend_Date::MONTH) != 1 || $periodFrom->toValue(Zend_Date::DAY) != 1) { // not the first day of the year $dtFrom = $periodFrom->getDate(); $dtTo = $periodFrom->getDate()->setMonth(12)->setDay(31); // last day of the year if (!$periodTo || $dtTo->isEarlier($periodTo)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->_from = $periodFrom->getDate()->addYear(1)->setMonth(1)->setDay(1)->toString($dtFormat); // first day of the next year } } } if ($periodTo) { if ($periodTo->toValue(Zend_Date::MONTH) != 12 || $periodTo->toValue(Zend_Date::DAY) != 31) { // not the last day of the year $dtFrom = $periodTo->getDate()->setMonth(1)->setDay(1); // first day of the year $dtTo = $periodTo->getDate(); if (!$periodFrom || $dtFrom->isLater($periodFrom)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->_to = $periodTo->getDate()->subYear(1)->setMonth(12)->setDay(31)->toString($dtFormat); // last day of the previous year } } } if ($periodFrom && $periodTo) { if ($periodFrom->toValue(Zend_Date::YEAR) == $periodTo->toValue(Zend_Date::YEAR)) { // the same year $dtFrom = $periodFrom->getDate(); $dtTo = $periodTo->getDate(); $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->getSelect()->where('1<>1'); } } } else if ('month' == $this->_period) { // Start of custom hackish... if (!$this->isTotals()) { $columns = $this->getSelect()->getPart('columns'); foreach($columns as $index => $column){ if ($column[1] == 'value'){ $column[1] = new Zend_Db_Expr('sum(value)'); $columns[$index] = $column; } } $this->getSelect()->setPart('columns', $columns); } $this->getSelect()->reset('group'); $this->getSelect()->group(array(new Zend_Db_Expr("DATE_FORMAT(period, '%Y-%m')"), 'description')); // End of custom hackish... if ($periodFrom) { if ($periodFrom->toValue(Zend_Date::DAY) != 1) { // not the first day of the month $dtFrom = $periodFrom->getDate(); $dtTo = $periodFrom->getDate()->addMonth(1)->setDay(1)->subDay(1); // last day of the month if (!$periodTo || $dtTo->isEarlier($periodTo)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->_from = $periodFrom->getDate()->addMonth(1)->setDay(1)->toString($dtFormat); // first day of the next month } } } if ($periodTo) { if ($periodTo->toValue(Zend_Date::DAY) != $periodTo->toValue(Zend_Date::MONTH_DAYS)) { // not the last day of the month $dtFrom = $periodTo->getDate()->setDay(1); // first day of the month $dtTo = $periodTo->getDate(); if (!$periodFrom || $dtFrom->isLater($periodFrom)) { $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->_to = $periodTo->getDate()->setDay(1)->subDay(1)->toString($dtFormat); // last day of the previous month } } } if ($periodFrom && $periodTo) { if ($periodFrom->toValue(Zend_Date::YEAR) == $periodTo->toValue(Zend_Date::YEAR) && $periodFrom->toValue(Zend_Date::MONTH) == $periodTo->toValue(Zend_Date::MONTH)) { // the same month $dtFrom = $periodFrom->getDate(); $dtTo = $periodTo->getDate(); $selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat)); $this->getSelect()->where('1<>1'); } } } $this->_applyDateRangeFilter(); // add unions to select if ($selectUnions) { $unionParts = array(); $cloneSelect = clone $this->getSelect(); $unionParts[] = '(' . $cloneSelect . ')'; foreach ($selectUnions as $union) { $unionParts[] = '(' . $union . ')'; } $this->getSelect()->reset()->union($unionParts, Zend_Db_Select::SQL_UNION_ALL); } if ($this->isTotals()) { // calculate total $cloneSelect = clone $this->getSelect(); $this->getSelect()->reset()->from($cloneSelect, $this->getAggregatedColumns()); } else { // add sorting $this->getSelect()->order(array('period ASC', 'value DESC')); } return $this; } protected function _makeBoundarySelect($from, $to) { $cols = $this->_getSelectedColumns(); $cols['value'] = 'SUM(value)'; $sel = $this->getConnection()->select() ->from($this->getResource()->getMainTable(), $cols) ->where('period >= ?', $from) ->where('period <= ?', $to) ->group('description') ->order('value DESC'); return $sel; } public function addStoreFilter($storeIds) { $this->_storesIds = $storeIds; return $this; } public function addOrderStatusFilter($orderStatus) { $this->_orderStatus = $orderStatus; return $this; } protected function _applyStoresFilterToSelect(Zend_Db_Select $select) { return $this; } public function setAggregatedColumns(array $columns) { $this->_aggregatedColumns = $columns; return $this; } public function getAggregatedColumns() { return $this->_aggregatedColumns; } public function setDateRange($from = null, $to = null) { $this->_from = $from; $this->_to = $to; return $this; } public function setPeriod($period) { $this->_period = $period; return $this; } protected function _applyDateRangeFilter() { if (!is_null($this->_from)) { $this->getSelect()->where('period >= ?', $this->_from); } if (!is_null($this->_to)) { $this->getSelect()->where('period <= ?', $this->_to); } return $this; } public function setApplyFilters($flag) { $this->_applyFilters = $flag; return $this; } public function isTotals($flag = null) { if (is_null($flag)) { return $this->_isTotals; } $this->_isTotals = $flag; return $this; } public function isSubTotals($flag = null) { if (is_null($flag)) { return $this->_isSubTotals; } $this->_isSubTotals = $flag; return $this; } public function load($printQuery = false, $logQuery = false) { if ($this->isLoaded()) { return $this; } $this->_initSelect(); if ($this->_applyFilters) { $this->_applyDateRangeFilter(); } return parent::load($printQuery, $logQuery); } } |
So remember the part about “I’m not sure how it all works”? Yeah. So I will talk about snippets that I found are important, but might not have a lot of info on why:
1. When I didn’t have this, it complained about “period” in the where clause:
1 |
$this->_applyFilters = false; |
2. Because I am not already aggregating the sums in my table (BAD ME!), I had to introduce the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
// Start of custom hackish... if (!$this->isTotals()) { $columns = $this->getSelect()->getPart('columns'); foreach($columns as $index => $column){ if ($column[1] == 'value'){ $column[1] = new Zend_Db_Expr('sum(value)'); $columns[$index] = $column; } } $this->getSelect()->setPart('columns', $columns); } $this->getSelect()->reset('group'); $this->getSelect()->group(array(new Zend_Db_Expr("DATE_FORMAT(period, '%Y-%m')"), 'description')); // End of custom hackish... |
I found that if I wanted to group my period by a date that was formated, I had to include the SAME formatted date in the group clause.
3. I wanted the SUM of the value column, so you will see this in lots of places:
1 |
$cols['value'] = 'SUM(value)'; |
Just like in the simple report, this report also needs the “resource model”, the “model”, and the empty helper.
I hope this gives you SOME hints and/or direction in creating reports in magento.