Add custom column to Magento report and sales dashboard

Question

I've been searching for a module or a way to deal with the fallowing situation:

A customer orders a product that is 100$ and pays shipping of 10$. That would charge him a total amount of 110$. By the time the product arrives he notices that the product is a little scratched and instead of sending it back he accepts to receive a discount.

For this to happen properly I would make a credit memo with an adjustment refund of let's say 30$.

I need to see the total amount that remains after this operation is done (80$) in a separate column in either reports or the sales dashboard.

For this particular task we have also installed a module called "Advanced Orders Manager by Iksanika" but this appears to only get the data that already exists in the database and is not allowing us to use variable for let's say a substraction.

Also in Magento reports we use Reports > Sales > Orders but that gives us only the total figures and we cannot find anywhere a "total amount charged" that would give us the exact final figure (80$).

This is a particular request of the accounting dpt of an online store.

Answer 1

What you want to do is to add a custom grid column to the sales order grid.

Magento uses the database table sales_flat_order to fill the values in the sales order grid. Unfortunately the sales_flat_order table doesn't provide the information you need (grandtotal minus refunded amount) but both values separately (grand_total and total_refunded). Because of that you have multiple options:

• Extend the table sales_flat_order.
• Add a custom renderer for your custom sales order grid column.

As everything in this world both methods have advantages and disadvantages.

If you extend the sales_flat_order table you have to make sure the value of your new database column is set when creating new orders. On the other hand, as the value is persisted in your database, you can use the column for other extensions.

With a custom renderer you don't have to care about persistence. You have the opportunity to do operations and return your result which will be displayed in your custom sales order grid column. As we have both grand_total and total_refunded saved already you can return the grandtotal minus refunded amount.

I'll describe how to add a custom sales order grid column and add a custom renderer to return the value of grandtotal minus refund amount.

Part 1: How to add custom column to sales order grid in backend?

To add a custom column to sales order grid first add your own sales order grid block XX_ModuleName_Block_Adminhtml_Order_Grid.

Rewrite magentos *Mage_Adminhtml_Block_Sales_Order_Grid (app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php) and extend it with your sales order grid.

To add your custom column override _prepareColumns() method. Inside the overwritten _prepareColumns() you want to first add the column from parent class. Finally, add your custom column:

note: you can download the example module.

$this->addColumn('my_column', array(
    'header' => Mage::helper('sales')->__('My Column'),
    'index' => 'my_column',
    'type' => 'text',
    'renderer'  => 'XX_ModuleName_Block_Adminhtml_Order_Grid'
));

Example etc/config.xml:

<?xml version="1.0"?>
<config>
    <modules>
        <EG_AdminSalesOrder>
            <version>1.0.0</version>
        </EG_AdminSalesOrder>
    </modules>
    <global>
        <blocks>
            <eg_adminsalesorder>
                <class>EG_AdminSalesOrder_Block</class>
            </eg_adminsalesorder>
            <adminhtml>
                <rewrite>
                    <sales_order_grid>EG_AdminSalesOrder_Block_Adminhtml_Order_Grid</sales_order_grid>
                </rewrite>
            </adminhtml>
        </blocks>
        <helpers>
            <eg_adminsalesorder>
                <class>EG_AdminSalesOrder_Helper</class>
            </eg_adminsalesorder>
        </helpers>
    </global>
</config>

Example custom sales order grid block:

class EG_AdminSalesOrder_Block_Adminhtml_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{
    /**
     * Add custom column to sales order grid
     *
     * @return Mage_Adminhtml_Block_Widget_Grid
     * @throws Exception
     */
    protected function _prepareColumns()
    {
        $this->addColumn('real_order_id', array(
            'header'=> Mage::helper('sales')->__('Order #'),
            'width' => '80px',
            'type'  => 'text',
            'index' => 'increment_id',
        ));

        if (!Mage::app()->isSingleStoreMode()) {
            $this->addColumn('store_id', array(
                'header'    => Mage::helper('sales')->__('Purchased From (Store)'),
                'index'     => 'store_id',
                'type'      => 'store',
                'store_view'=> true,
                'display_deleted' => true,
            ));
        }

        $this->addColumn('created_at', array(
            'header' => Mage::helper('sales')->__('Purchased On'),
            'index' => 'created_at',
            'type' => 'datetime',
            'width' => '100px',
        ));

        $this->addColumn('billing_name', array(
            'header' => Mage::helper('sales')->__('Bill to Name'),
            'index' => 'billing_name',
        ));

        $this->addColumn('shipping_name', array(
            'header' => Mage::helper('sales')->__('Ship to Name'),
            'index' => 'shipping_name',
        ));

        $this->addColumn('base_grand_total', array(
            'header' => Mage::helper('sales')->__('G.T. (Base)'),
            'index' => 'base_grand_total',
            'type'  => 'currency',
            'currency' => 'base_currency_code',
        ));

        $this->addColumn('grand_total', array(
            'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
            'index' => 'grand_total',
            'type'  => 'currency',
            'currency' => 'order_currency_code',
        ));


        $this->addColumn('refunded', array(
            'header' => Mage::helper('sales')->__('Total - Refund'),
            'index' => 'refunded',
            'type' => 'text',
            'renderer'  => 'EG_AdminSalesOrder_Block_Adminhtml_Sales_Order_Grid_Widget_Renderer_Refunded'
        ));

        parent::_prepareColumns();
    }
}

Part 2: How to add a custom renderer for my custom column?

Now you can add your custom renderer to fill the values into your custom sales order grid column.

First add a cusom renderer class XX_ModuleName_Block_Adminhtml_Sales_Order_Grid_Widget_Renderer_MyColumn.

Then extend Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract.

Override the render(Varien_Object $row) method. Here you can do your specific operation and return the string that should be displayed in your grid. In your case you want to load the order collection for the current $row param, get the total refunded amount, subtract the grandtotal with the refunded amount and return the value.

Example custom renderer block:

class EG_AdminSalesOrder_Block_Adminhtml_Sales_Order_Grid_Widget_Renderer_Refunded
    extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{
    /**
     * @param Varien_Object $row
     * @return string
     */
    public function render(Varien_Object $row)
    {
        $currentOrderId = $row->getId();
        $currentOrderGrandTotal = $row->getGrandTotal();

        $orderCollection = Mage::getModel('sales/order')->getCollection();
        $orderCollection->addFieldToSelect('total_refunded');
        $orderCollection->addFieldToFilter('entity_id', array('eq' => $currentOrderId));
        $orderCollectionItem = $orderCollection->getFirstItem();
        $refundedAmount = $orderCollectionItem->getTotalRefunded();

        $grandTotalWithoutRefundedAmount = (float)$currentOrderGrandTotal - (float)$refundedAmount;
        $grandTotalWithoutRefundedAmount = Mage::helper('core')->currency($grandTotalWithoutRefundedAmount);

        return (string)$grandTotalWithoutRefundedAmount;
    }
}

You can download the example module.