Nov 29

AheadWorks Advanced Reports Extension – Including a Payment Method column

By Jamie Saunders

The Advanced Reports extension improves functionality of native Magento reports and creates a complete picture of your business situation. Advanced Reports is a perfect solution for all your reporting needs whether you want to display data from various charts, aggregate information by week etc.

Today one of my tasks was to extend the extension for one of our clients by creating a new payment method column. Completing this task may seem a bit tricky at first but it is rather simple if you follow these steps:

Step 1

Locate the file below in your chosen editor, I prefer to use Panic’s Coda.

app/code/local/AW/Advancedreports/Block/Advanced/Sales/Grid.php

Create a new column for the payment method to be displayed by adding the following code to the _prepareColumns() method.

$this->addColumn('payment_method', array(
    'header'    =>Mage::helper('advancedreports')->__('Payment Method'),
    'width'     =>'80px',
    'type'      =>'text',
    'index'     =>'payment_method',
    'column_css_class' => 'nowrap'
));

This creates our new payment method column, the index is important as will become apparent in step 3.

Step 2

Within _prepareCollection() method add the following lines of code before the _prepareData() method is called.

$this->_addPaymentMethod($collection);

This line of code will call the addPaymentMethod function which we are going to create in Step 2.

Step 3

Next to create the addPaymentMethod() method which will grab the data we need from the database.

protected function _addPaymentMethod($collection)
{	
	$orderPaymentTable = $this->getTable('sales_flat_order_payment');
	$collection->getSelect() 
		->join( array( '_payment'=>$orderPaymentTable ), "_payment.parent_id = item.order_id", array( 'payment_method' => 'method') );
 
}

The method should be protected so it can only be accessed from within the class or a class that inherits the AW_Advancedreports_Block_Advanced_Sales_Grid.

The $collection object is passed into the method as this contains our reporting data, we then grab the already instantiated select query and join the sales_flat_order_payment table where the payment method identifier is kept for each order. The relationship between the sales_flat_order_payment table and the order item is between the parent_id of the sales_flat_payment table and the order_id of the order item.

We create an alias of the column we want to retrieve from the SQL query by adding the optional array in the 3rd argument of the join. The alias represents the index we talked about in step 1.

Summary

In conclusion this is just one of many examples that could be implemented. For example, using the same method you could display a ‘product option’ column within the advanced reports table to display which product options were selected for each order item. Ultimately we are simply modifying the SELECT query being run to retrieve the data, to add in extra fields to the report.

On a final note I have attached a patch with this post to allow you to easily apply these changes to your installation. This patch will work with version 2.1.1 of the Advanced Reports extension:

Download patch
Download ZIP archive (suggested for most users)

Leave a Reply