How to refresh reports aggregation data on plugin install

Question

I'm building a module where i'm adding a few extra columns to the default magento reports under Reports > Sales > Orders & Reports > Sales > Tax

I'm adding the extra columns to the corresponding aggregation tables from the module's install script. Now after adding the columns, I have to truncate the table & then aggregate all the existing data all over again. I got it working on my local with some sample data & it worked as expected.

The problem is that I added set_time_limit(0) to index.php so that it doesn't timeout & even though it worked correctly it took like 10 minutes. It might get even worse when the amount of existing data grows.

So my question is if there is any better way to do it? Maybe putting some consecutive crons that run one after other & refresh all the data in batches?

Thanks

UPDATE

I decided it would be better to just ask users to run the refresh script from the terminal. The idea here is that a user would run it from ssh & keep it running for maybe a day & all the data gets refreshed in that time. So, working on that, I now have a file that I'm running from the command line. Below is the code (with time recording, Mage::log & echo statements stripped out)

require_once 'app/Mage.php';
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app();

$from = strtotime('-1 day');
$to   = strtotime('now');
$day = $to - $from;
do {
    try {
        Mage::getResourceModel('sales/report_order')->aggregate($from, $to);
        echo 'success.';
        $to   = $from;
        $from = $from - $day;
    } catch(Mage_Core_Exception $e) {
        echo $e->getMessage();
    } catch(Exception $e) {
        echo 'Failed due to ' . $e->getMessage();
    }
    sleep(4);
} while($to > strtotime($argv[1]));

This also works but still, it's taking forever. By judging from the logs etc. it seems that it just randomly selects days on which it has to spend 5-10 minutes & there are others that are done within seconds. The code, again, is working. But whenever it chooses to take 5-10 minutes, then during that time the server is in a crisis. Another interesting thing is that none of the queries actually failed or threw any exception. It's always a success. Any thoughts?

Answer 1

You can do the below from a cron, though, if your module has an installer, inside of that installer you can call the aggregator as you wrap up. This example assumes that you do it at the end of your install or upgrade script.

<?php
/* @var $installer MyCompany_MyClass_Model_Entity_Setup */
$installer = $this;
$installer->startSetup();

//.... install stuff


try {

$aliases = array(
    'sales'       => 'sales/report_order',
    'tax'         => 'tax/report_tax',
    'shipping'    => 'sales/report_shipping',
    'invoiced'    => 'sales/report_invoiced',
    'refunded'    => 'sales/report_refunded',
    'coupons'     => 'salesrule/report_rule',
    'bestsellers' => 'sales/report_bestsellers',
);

foreach ($aliases as $key=>$val) {
        Mage::getResourceModel($val)->aggregate();
    }
    Mage::getSingleton('adminhtml/session')->addSuccess(Mage::helper('adminhtml')->__('Lifetime statistics have been updated.'));
} catch (Mage_Core_Exception $e) {
    Mage::getSingleton('adminhtml/session')->addError($e->getMessage());
} catch (Exception $e) {
    Mage::getSingleton('adminhtml/session')->addError(Mage::helper('adminhtml')->__('Unable to refresh lifetime statistics.'));
    Mage::logException($e);
}

$installer->endSetup();