Magento module to change dashboard graph

Question

I am following along with this post Change the Dashboard Graph in version 1.7/1.12 of Magento to allow the sales of "processing" orders to show up on the dashboard graph. My files are below and within the right directories as well as showing up as active in config>advanced. I have also reindexed, refreshed cache, and refreshed lifetime statistics. I am seeing no errors in the logs. Can you see what is wrong? I have firegento and have enabled logging, but that's not working either.

EDIT: The Revenue total on the dashboard seems correct, but its not reflecting on the timeline graph. For example there may be a net 30 terms order of $2000 at 10AM but it doesn't show on the time graph. Bounty for whoever can fix the script below to reflect on the timeline for me!

CaitlinHavener/Dashboard/etc/config.xml:

<?xml version="1.0"?>
<config>
    <modules>
        <CaitlinHavener_Dashboard>
            <version>1.0</version>
        </CaitlinHavener_Dashboard>
    </modules>
    <global>
        <models>
            <CaitlinHavener_Dashboard>
                <class>CaitlinHavener_Dashboard_Model</class>
            </CaitlinHavener_Dashboard>
            <reports_resource>
                <rewrite>
                    <order_collection>CaitlinHavener_Dashboard_Model_Reports_Resource_Order_Collection</order_collection>
                </rewrite>
            </reports_resource>
        </models>
    </global>
</config>

CaitlinHavener/Dashboard/Model/Reports/Resource/Order/Collection.php

    <?php
/**
 * Show all orders, not only the invoiced one
 */
class CaitlinHavener_Dashboard_Model_Reports_Resource_Order_Collection extends Mage_Reports_Model_Resource_Order_Collection
{
    protected function _prepareSummaryLive($range, $customStart, $customEnd, $isFilter = 0)
    {
        $this->setMainTable('sales/order');
        $adapter = $this->getConnection(); 
        /**
         * Reset all columns, because result will group only by 'created_at' field
         */
        $this->getSelect()->reset(Zend_Db_Select::COLUMNS);

       /*  $expression = sprintf('%s - %s - %s - (%s - %s - %s)',
            $adapter->getIfNullSql('main_table.base_total_invoiced', 0),
            $adapter->getIfNullSql('main_table.base_tax_invoiced', 0),
            $adapter->getIfNullSql('main_table.base_shipping_invoiced', 0),
            $adapter->getIfNullSql('main_table.base_total_refunded', 0),
            $adapter->getIfNullSql('main_table.base_tax_refunded', 0),
            $adapter->getIfNullSql('main_table.base_shipping_refunded', 0)
        ); */

        $expression = sprintf('%s - %s - %s - (%s - %s - %s)',
            $adapter->getIfNullSql('main_table.base_total_invoiced', 'main_table.base_grand_total'),
            $adapter->getIfNullSql('main_table.base_tax_invoiced', 'main_table.base_tax_amount'),
            $adapter->getIfNullSql('main_table.base_shipping_invoiced',  'main_table.base_shipping_amount'),
            $adapter->getIfNullSql('main_table.base_total_refunded', 0),
            $adapter->getIfNullSql('main_table.base_tax_refunded', 0),
            $adapter->getIfNullSql('main_table.base_shipping_refunded', 0)
        );


        if ($isFilter == 0) {
            $this->getSelect()->columns(array(
                'revenue' => new Zend_Db_Expr(
                    sprintf('SUM((%s) * %s)', $expression,
                        $adapter->getIfNullSql('main_table.base_to_global_rate', 0)
                    )
                 )
            ));
        } else {
            $this->getSelect()->columns(array(
                'revenue' => new Zend_Db_Expr(sprintf('SUM(%s)', $expression))
            ));
        }

        $dateRange = $this->getDateRange($range, $customStart, $customEnd);

        $tzRangeOffsetExpression = $this->_getTZRangeOffsetExpression(
            $range, 'created_at', $dateRange['from'], $dateRange['to']
        );

        $this->getSelect()
            ->columns(array(
                'quantity' => 'COUNT(main_table.entity_id)',
                'range' => $tzRangeOffsetExpression,
            ))
            //BOF modification
           ->where('main_table.state NOT IN (?)', array(
                Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
               // Mage_Sales_Model_Order::STATE_NEW
               )
            )
            //EOF modification
            ->order('range', Zend_Db_Select::SQL_ASC)
            ->group($tzRangeOffsetExpression);

        $this->addFieldToFilter('created_at', $dateRange);

        return $this;
    }

    protected function _calculateTotalsLive($isFilter = 0)
    {
        $this->setMainTable('sales/order');
        $this->removeAllFieldsFromSelect();

        $adapter = $this->getConnection();

        // $baseTotalInvoiced    = $adapter->getIfNullSql('main_table.base_grand_total', 0);
        // $baseTotalRefunded    = $adapter->getIfNullSql('main_table.base_discount_refunded', 0);
        // $baseTaxInvoiced      = $adapter->getIfNullSql('main_table.base_tax_amount', 0);
        // $baseTaxRefunded      = $adapter->getIfNullSql('main_table.base_tax_refunded', 0);
        // $baseShippingInvoiced = $adapter->getIfNullSql('main_table.base_shipping_amount', 0);
        // $baseShippingRefunded = $adapter->getIfNullSql('main_table.base_shipping_refunded', 0);
        // $baseShippingRefunded = $adapter->getIfNullSql('main_table.base_shipping_refunded', 0);

        $baseTotalInvoiced    = $adapter->getIfNullSql('main_table.base_total_invoiced', 'main_table.base_grand_total'); // This will check if there is no invoice it will calculate based on the grand totals ( so when you generate and invoice u will have no issues with the numbers also )
$baseTotalRefunded    = $adapter->getIfNullSql('main_table.base_total_refunded', 0);
$baseTaxInvoiced      = $adapter->getIfNullSql('main_table.base_tax_invoiced', 'main_table.base_tax_amount'); // Same here for taxes
$baseTaxRefunded      = $adapter->getIfNullSql('main_table.base_tax_refunded', 0);
$baseShippingInvoiced = $adapter->getIfNullSql('main_table.base_shipping_invoiced', 'main_table.base_shipping_amount'); // Same here for shipping 
$baseShippingRefunded = $adapter->getIfNullSql('main_table.base_shipping_refunded', 0);

        $revenueExp = sprintf('%s - %s - %s - (%s - %s - %s)',
            $baseTotalInvoiced,
            $baseTaxInvoiced,
            $baseShippingInvoiced,
            $baseTotalRefunded,
            $baseTaxRefunded,
            $baseShippingRefunded
        );
        $taxExp = sprintf('%s - %s', $baseTaxInvoiced, $baseTaxRefunded);
        $shippingExp = sprintf('%s - %s', $baseShippingInvoiced, $baseShippingRefunded);

        if ($isFilter == 0) {
            $rateExp = $adapter->getIfNullSql('main_table.base_to_global_rate', 0);
            $this->getSelect()->columns(
                array(
                    'revenue'  => new Zend_Db_Expr(sprintf('SUM((%s) * %s)', $revenueExp, $rateExp)),
                    'tax'      => new Zend_Db_Expr(sprintf('SUM((%s) * %s)', $taxExp, $rateExp)),
                    'shipping' => new Zend_Db_Expr(sprintf('SUM((%s) * %s)', $shippingExp, $rateExp))
                )
            );
        } else {
            $this->getSelect()->columns(
                array(
                    'revenue'  => new Zend_Db_Expr(sprintf('SUM(%s)', $revenueExp)),
                    'tax'      => new Zend_Db_Expr(sprintf('SUM(%s)', $taxExp)),
                    'shipping' => new Zend_Db_Expr(sprintf('SUM(%s)', $shippingExp))
                )
            );
        }


        $this->getSelect()->columns(array(
            'quantity' => 'COUNT(main_table.entity_id)'
        ))
        ->where('main_table.state NOT IN (?)', array(
            Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
            //Mage_Sales_Model_Order::STATE_NEW
            )
         );

        return $this;
    }

}
?>

Answer 1

You have to understand the Order process in Magento so you can fully understand how the chart being populated

1. Order come to the Store as new Order ( like post paid orders - Cash on Delivery, ... ) or Pending order ( credit card orders or paypal etc.. all online payment methods ).
   • Order can be canceled at this step ( as no invoice generated ).
2. In case of online payment, payment gone through successfully ( So the order is invoiced - invoice generated ).
3. Order Shipped after being invoiced ( Shipment is generated ).
4. Order refunded ( partial or complete ) so credit memos generated.

Now if you look at the previous steps and you will see that the Revenue not supposed to be calculated if the Order is in NEW and CANCELED STATE ( not status )

It should be calculated when the Order gets invoiced ( so you will get the revenue and shipping fees and the refunds )

So to count the orders in NEW STATE in the revenue and the graph Override the method

app/code/core/Mage/Reports/Model/Resource/Order/Collection.php
/**
 * Calculate totals live report
 *
 * @param int $isFilter
 * @return Mage_Reports_Model_Resource_Order_Collection
 */
protected function _calculateTotalsLive($isFilter = 0){}

   // Line 430
    ->where('main_table.state NOT IN (?)', array(
        Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
        Mage_Sales_Model_Order::STATE_NEW // Comment this line 
        )
     );

So now if you comment this line the order Will be counted in the ( ORDERS chart graph )

but the totals is still not calculated ( because there is no invoice / shipments / refunds )

So you need to change the calculation process from using the invoiced amount to use the TOTALS only even before invoice With small changes you can achieve what you want without mistakes Code below

Change the lines ( 390 and below to be like the following ):

$baseTotalInvoiced    = $adapter->getIfNullSql('main_table.base_total_invoiced', 'main_table.base_grand_total'); // This will check if there is no invoice it will calculate based on the grand totals ( so when you generate and invoice u will have no issues with the numbers also )
$baseTotalRefunded    = $adapter->getIfNullSql('main_table.base_total_refunded', 0);
$baseTaxInvoiced      = $adapter->getIfNullSql('main_table.base_tax_invoiced', 'main_table.base_tax_amount'); // Same here for taxes
$baseTaxRefunded      = $adapter->getIfNullSql('main_table.base_tax_refunded', 0);
$baseShippingInvoiced = $adapter->getIfNullSql('main_table.base_shipping_invoiced', 'main_table.base_shipping_amount'); // Same here for shipping 
$baseShippingRefunded = $adapter->getIfNullSql('main_table.base_shipping_refunded', 0);

So In simple answer the solution is not changed the Way of calculation, But the solution change the Default values of the calculations

Follow this changes and you will get what you exactly want :)