Magento - How to get Revenue by Product

Question

I'm fighting with this on Magento.

I added to the Product Ordered report a few columns and i want to have one with the Total Revenue for that specific product.

Check this example: http://awesomescreenshot.com/0e94d0yi29

The total revenue number is wrong, i did the review of each order and it's not that number.

What am i doing wrong? Here's the code:

site>app>code>core>Mage>Adminhtml>Block>Report>Product>Sold

class Mage_Adminhtml_Block_Report_Product_Sold_Grid extends Mage_Adminhtml_Block_Report_Grid
{
/**
 * Sub report size
 *
 * @var int
 */
protected $_subReportSize = 0;

/**
 * Initialize Grid settings
 *
 */
public function __construct()
{
    parent::__construct();
    $this->setId('gridProductsSold');
}

protected function _afterLoadCollection()
{
    $totalObj = new Mage_Reports_Model_Totals();
    $this->setTotals($totalObj->countTotals($this));
}

/**
 * Prepare collection object for grid
 *
 * @return Mage_Adminhtml_Block_Report_Product_Sold_Grid
 */
protected function _prepareCollection()
{
    parent::_prepareCollection();
    $this->getCollection()
            ->initReport('reports/product_sold_collection');

    return $this;
}


protected function _getStore()
{
    $storeId = (int) $this->getRequest()->getParam('store', 0);
    return Mage::app()->getStore($storeId);
}

/**
 * Prepare Grid columns
 *
 * @return Mage_Adminhtml_Block_Report_Product_Sold_Grid
 */
protected function _prepareColumns()
{
    $store = $this->_getStore();
    $collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('sku')
        ->addAttributeToSelect('name')
        ->addAttributeToSelect('attribute_set_id')
        ->addAttributeToSelect('type_id');

    $collection->addAttributeToSelect('producer_tag');

    if (Mage::helper('catalog')->isModuleEnabled('Mage_CatalogInventory')) {
        $collection->joinField('qty',
            'cataloginventory/stock_item',
            'qty',
            'product_id=entity_id',
            '{{table}}.stock_id=1',
            'left');
    }
    if ($store->getId()) {
        //$collection->setStoreId($store->getId());
        $adminStore = Mage_Core_Model_App::ADMIN_STORE_ID;
        $collection->addStoreFilter($store);
        $collection->joinAttribute(
            'name',
            'catalog_product/name',
            'entity_id',
            null,
            'inner',
            $adminStore
        );
        $collection->joinAttribute(
            'custom_name',
            'catalog_product/name',
            'entity_id',
            null,
            'inner',
            $store->getId()
        );
        $collection->joinAttribute(
            'status',
            'catalog_product/status',
            'entity_id',
            null,
            'inner',
            $store->getId()
        );
        $collection->joinAttribute(
            'visibility',
            'catalog_product/visibility',
            'entity_id',
            null,
            'inner',
            $store->getId()
        );
        $collection->joinAttribute(
            'price',
            'catalog_product/price',
            'entity_id',
            null,
            'left',
            $store->getId()
        );
    }
    else {
        $collection->addAttributeToSelect('price');
        $collection->joinAttribute('status', 'catalog_product/status', 'entity_id', null, 'inner');
        $collection->joinAttribute('visibility', 'catalog_product/visibility', 'entity_id', null, 'inner');
    }


    $this->addColumn('name', array(
        'header'    =>Mage::helper('reports')->__('Product Name'),
        'index'     =>'order_items_name'
    ));

      $sets = Mage::getResourceModel('eav/entity_attribute_set_collection')
        ->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId())
        ->load()
        ->toOptionHash();

    $this->addColumn('set_name',
        array(
            'header'=> Mage::helper('catalog')->__('Category'),
            'width' => '150px',
            'index' => 'attribute_set_id',
            'type'  => 'options',
            'options' => $sets,
    ));

    $this->addColumn('producer_tag',
        array(
            'header'=> Mage::helper('catalog')->__('Producer Tag'),
            'width' => '150px',
            'index' => 'producer_tag',
            'type'  => 'text'
    ));


    $store = $this->_getStore();
    $this->addColumn('price',
        array(
            'header'=> Mage::helper('catalog')->__('Price'),
            'type'  => 'price',
            'currency_code' => $store->getBaseCurrency()->getCode(),
            'index' => 'price',
    ));


    $this->addColumn('ordered_qty', array(
        'header'    =>Mage::helper('reports')->__('Quantity Ordered'),
        'width'     =>'120px',
        'align'     =>'right',
        'index'     =>'ordered_qty',
        'total'     =>'sum',
        'type'      =>'number'
    ));

 $currencyCode = $this->getCurrentCurrencyCode();
    $rate = $this->getRate($currencyCode);
    $this->addColumn('revenue',
        array(
            'header'=> Mage::helper('catalog')->__('Revenue (Only Complete Orders)'),
            'type'  => 'currency',
            'index' => 'price',
            'total' => 'sum',
            'type'  => 'currency',
            'currency_code' => $currencyCode,
            'rate'  => $rate,
    ));

    $this->addExportType('*/*/exportSoldCsv', Mage::helper('reports')->__('CSV'));
    $this->addExportType('*/*/exportSoldExcel', Mage::helper('reports')->__('Excel XML'));
    return parent::_prepareColumns();
}
}

Don't get what i'm doing wrong here.

My goal is to have a report of how much money i did with each of those products.

Any help or ideal will be welcome.

Thanks in advance.

Manoj Chaurasia Manoj Chaurasia · Accepted Answer · 2015-02-09T18:07:17

See the answer posted here for the basis of my code suggestion

Magento: How to display how many times a product has been sold?

since you are wanting to display this value in the frontend, i'm assuming phtml, so I'll code it that way, and just attempt to multiply final price by qty sold.

<?php
        // Enter your product ID here 
        $id = 123; 

        // Load the product collection and do some filters   
        $_productCollection = Mage::getResourceModel('reports/product_collection')
            ->addOrderedQty()
            ->addAttributeToFilter('id', $id)
            ->setOrder('ordered_qty', 'desc')
            ->getFirstItem();

        // Since the filter selects one product, we can use it here as the single result
        $product = $_productCollection;

        // Load the tax helper
        $_taxHelper  = Mage::helper('tax');

        // Get the final price of the product
        $finalprice = $_taxHelper->getPrice($product, $product->getFinalPrice(), true);


        // Print the results
        echo $product->getName() . " total revenue: " . (int)$product->ordered_qty * $finalprice;
?>

This is untested, coded on the fly, but the concepts are pretty basic so you should have no trouble adapting what I've shown to do what you need.

Magento - How to get Revenue by Product

1 Answers