1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_Reports
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: 
 28: /**
 29:  * Report Products Review collection
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Reports
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Reports_Model_Resource_Review_Product_Collection extends Mage_Catalog_Model_Resource_Product_Collection
 36: {
 37:     protected function _construct()
 38:     {
 39:         parent::_construct();
 40:         $this->_useAnalyticFunction = true;
 41:     }
 42:     /**
 43:      * Join review table to result
 44:      *
 45:      * @return Mage_Reports_Model_Resource_Review_Product_Collection
 46:      */
 47:     public function joinReview()
 48:     {
 49:         $helper    = Mage::getResourceHelper('core');
 50: 
 51:         $subSelect = clone $this->getSelect();
 52:         $subSelect->reset()
 53:             ->from(array('rev' => $this->getTable('review/review')), 'COUNT(DISTINCT rev.review_id)')
 54:             ->where('e.entity_id = rev.entity_pk_value');
 55: 
 56:         $this->addAttributeToSelect('name');
 57: 
 58:         $this->getSelect()
 59:             ->join(
 60:                 array('r' => $this->getTable('review/review')),
 61:                 'e.entity_id = r.entity_pk_value',
 62:                 array(
 63:                     'review_cnt'    => new Zend_Db_Expr(sprintf('(%s)', $subSelect)),
 64:                     'last_created'  => 'MAX(r.created_at)',))
 65:             ->group('e.entity_id');
 66: 
 67:         $joinCondition      = array(
 68:             'e.entity_id = table_rating.entity_pk_value',
 69:             $this->getConnection()->quoteInto('table_rating.store_id > ?', 0)
 70:         );
 71: 
 72:         /**
 73:          * @var $groupByCondition array of group by fields
 74:          */
 75:         $groupByCondition   = $this->getSelect()->getPart(Zend_Db_Select::GROUP);
 76:         $percentField       = $this->getConnection()->quoteIdentifier('table_rating.percent');
 77:         $sumPercentField    = $helper->prepareColumn("SUM({$percentField})", $groupByCondition);
 78:         $sumPercentApproved = $helper->prepareColumn('SUM(table_rating.percent_approved)', $groupByCondition);
 79:         $countRatingId      = $helper->prepareColumn('COUNT(table_rating.rating_id)', $groupByCondition);
 80: 
 81:         $this->getSelect()
 82:             ->joinLeft(
 83:                 array('table_rating' => $this->getTable('rating/rating_vote_aggregated')),
 84:                 implode(' AND ', $joinCondition),
 85:                 array(
 86:                     'avg_rating'          => sprintf('%s/%s', $sumPercentField, $countRatingId),
 87:                     'avg_rating_approved' => sprintf('%s/%s', $sumPercentApproved, $countRatingId),
 88:             ));
 89: 
 90:         return $this;
 91:     }
 92: 
 93:     /**
 94:      * Add attribute to sort
 95:      *
 96:      * @param string $attribute
 97:      * @param string $dir
 98:      * @return Mage_Reports_Model_Resource_Review_Product_Collection
 99:      */
100:     public function addAttributeToSort($attribute, $dir = self::SORT_ORDER_ASC)
101:     {
102:         if (in_array($attribute, array('review_cnt', 'last_created', 'avg_rating', 'avg_rating_approved'))) {
103:             $this->getSelect()->order($attribute.' '.$dir);
104:             return $this;
105:         }
106: 
107:         return parent::addAttributeToSort($attribute, $dir);
108:     }
109: 
110:     /**
111:      * Get select count sql
112:      *
113:      * @return Varien_Db_Select
114:      */
115:     public function getSelectCountSql()
116:     {
117:         $this->_renderFilters();
118: 
119:         /* @var Varien_Db_Select $select */
120:         $select = clone $this->getSelect();
121:         $select->reset(Zend_Db_Select::ORDER);
122:         $select->reset(Zend_Db_Select::LIMIT_COUNT);
123:         $select->reset(Zend_Db_Select::LIMIT_OFFSET);
124:         $select->reset(Zend_Db_Select::COLUMNS);
125:         $select->resetJoinLeft();
126:         $select->columns(new Zend_Db_Expr('1'));
127: 
128:         /* @var Varien_Db_Select $countSelect */
129:         $countSelect = clone $select;
130:         $countSelect->reset();
131:         $countSelect->from($select, "COUNT(*)");
132: 
133:         return $countSelect;
134:     }
135: }
136: