gistfile1.php

<?php
/**
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@magentocommerce.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Magento to newer
 * versions in the future. If you wish to customize Magento for your
 * needs please refer to http://www.magentocommerce.com for more information.
 *
 * @category   Mage
 * @package    Mage_Reports
 * @copyright  Copyright (c) 2009 Irubin Consulting Inc. DBA Varien (http://www.varien.com)
 * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 */


/**
 * Report Artistsold Products collection
 *
 * @category   Mage
 * @package    Mage_Reports
 * @author     Magento Core Team <core@magentocommerce.com>
 */
 
class Stereo_Reports_Model_Mysql4_Product_Artistsold_Collection extends Mage_Reports_Model_Mysql4_Product_Collection
{

    /**
     * Set Date range to collection
     *
     * @param int $from
     * @param int $to
     * @return Stereo_Reports_Model_Mysql4_Product_Artistsold_Collection
     */
    public function setDateRange($from, $to)
    {     
        $this->_reset()
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('price')
            ->addOrderedQty($from, $to)
            ->addArtists()
            ->calculateSales()
            ->addFilterRefundState();
            
        //echo $this->getSelect()->__toString()."<br />";
        
        return $this;
    }

    /**
     * Set Store filter to collection
     *
     * @param array $storeIds
     * @return Stereo_Reports_Model_Mysql4_Product_Artistsold_Collection
     */
    public function setStoreIds($storeIds)
    {
        $storeId = array_pop($storeIds);
        $this->setStoreId($storeId);
        $this->addStoreFilter($storeId);
        return $this;
    }
    
    public function calculateSales()
    {
        /*
        $expr = "({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_amount}},0))";
        $attrs = array('base_subtotal', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded');
        $this->addExpressionAttributeToSelect('lifetime', "SUM($expr)", $attrs);
            
        $this->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
            ->groupByAttribute('entity_type_id');
        */
        
        //$this->addFieldToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED));
            
        return $this;
    }
    
    public function addArtists()
    {
        $productEntityIntTable = (string) Mage::getConfig()->getTablePrefix() . 'catalog_product_entity_int';
        $eavAttributeTable = $this->getTable('eav/attribute');


        $this->getSelect()->from("", array(
            'subtotal' => "SUM(order_items.row_total)",
            'discount' => "SUM(order_items.discount_amount)",
            'total' => "SUM(order_items.row_total) - SUM(order_items.discount_amount)"
        ));    
        
        //->addExpressionAttributeToSelect('total', '({{price}}-{{discount_amount}})', array('price', 'discount_amount'))
        
        $order = Mage::getResourceSingleton('sales/order');

        $this->getSelect()
        ->joinInner(
            array('pei' => $productEntityIntTable),
            "e.entity_id = pei.entity_id",
            array())
        ->joinInner(
            array('ea' => $eavAttributeTable),
            $this->getConnection()->quoteInto('pei.attribute_id=ea.attribute_id AND ea.attribute_code=?', 'artist'),
            array())
        ->joinInner(
            array('eao' => $this->getTable('eav/attribute_option')),
            "ea.attribute_id = eao.attribute_id AND pei.value = eao.option_id",
            array())
        ->joinInner(
            array('eaov' => $this->getTable('eav/attribute_option_value')),
            "eao.option_id = eaov.option_id AND eaov.store_id = 0", // store id?
            array('artist'=>'value'));
            
        return $this;
    }
    
    public function addFilterRefundState()
    {
        $this->getSelect()
        ->where('`order`.`total_refunded` <= 0');
        
        return $this;
    }
    
}