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_Rating
 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:  * Rating option resource model
 29:  *
 30:  * @category    Mage
 31:  * @package     Mage_Rating
 32:  * @author      Magento Core Team <core@magentocommerce.com>
 33:  */
 34: class Mage_Rating_Model_Resource_Rating_Option extends Mage_Core_Model_Resource_Db_Abstract
 35: {
 36:     /**
 37:      * Review table
 38:      *
 39:      * @var string
 40:      */
 41:     protected $_reviewTable;
 42: 
 43:     /**
 44:      * Rating option table
 45:      *
 46:      * @var string
 47:      */
 48:     protected $_ratingOptionTable;
 49: 
 50:     /**
 51:      * Rating vote table
 52:      *
 53:      * @var string
 54:      */
 55:     protected $_ratingVoteTable;
 56: 
 57:     /**
 58:      * Aggregate table
 59:      *
 60:      * @var string
 61:      */
 62:     protected $_aggregateTable;
 63: 
 64:     /**
 65:      * Review store table
 66:      *
 67:      * @var string
 68:      */
 69:     protected $_reviewStoreTable;
 70: 
 71:     /**
 72:      * Rating store table
 73:      *
 74:      * @var string
 75:      */
 76:     protected $_ratingStoreTable;
 77: 
 78:     /**
 79:     * Option data
 80:     *
 81:     * @var array
 82:     */
 83:     protected $_optionData;
 84: 
 85:     /**
 86:     * Option id
 87:     *
 88:     * @var int
 89:     */
 90:     protected $_optionId;
 91: 
 92:     /**
 93:      * Resource initialization. Define other tables name
 94:      *
 95:      */
 96:     protected function _construct()
 97:     {
 98:         $this->_init('rating/rating_option', 'option_id');
 99: 
100:         $this->_reviewTable         = $this->getTable('review/review');
101:         $this->_ratingOptionTable   = $this->getTable('rating/rating_option');
102:         $this->_ratingVoteTable     = $this->getTable('rating/rating_option_vote');
103:         $this->_aggregateTable      = $this->getTable('rating/rating_vote_aggregated');
104:         $this->_reviewStoreTable    = $this->getTable('review/review_store');
105:         $this->_ratingStoreTable    = $this->getTable('rating/rating_store');
106:     }
107: 
108:     /**
109:      * Add vote
110:      *
111:      * @param Mage_Rating_Model_Rating_Option $option
112:      * @return Mage_Rating_Model_Resource_Rating_Option
113:      */
114:     public function addVote($option)
115:     {
116:         $adapter = $this->_getWriteAdapter();
117:         $optionData = $this->loadDataById($option->getId());
118:         $data = array(
119:             'option_id'     => $option->getId(),
120:             'review_id'     => $option->getReviewId(),
121:             'percent'       => (($optionData['value'] / 5) * 100),
122:             'value'         => $optionData['value']
123:         );
124: 
125:         if (!$option->getDoUpdate()) {
126:             $data['remote_ip']       = Mage::helper('core/http')->getRemoteAddr();
127:             $data['remote_ip_long']  = Mage::helper('core/http')->getRemoteAddr(true);
128:             $data['customer_id']     = Mage::getSingleton('customer/session')->getCustomerId();
129:             $data['entity_pk_value'] = $option->getEntityPkValue();
130:             $data['rating_id']       = $option->getRatingId();
131:         }
132: 
133:         $adapter->beginTransaction();
134:         try {
135:             if ($option->getDoUpdate()) {
136:                 $condition = array(
137:                     'vote_id = ?'   => $option->getVoteId(),
138:                     'review_id = ?' => $option->getReviewId()
139:                 );
140:                 $adapter->update($this->_ratingVoteTable, $data, $condition);
141:                 $this->aggregate($option);
142:             } else {
143:                 $adapter->insert($this->_ratingVoteTable, $data);
144:                 $option->setVoteId($adapter->lastInsertId($this->_ratingVoteTable));
145:                 $this->aggregate($option);
146:             }
147:             $adapter->commit();
148:         } catch (Exception $e) {
149:             $adapter->rollback();
150:             throw new Exception($e->getMessage());
151:         }
152:         return $this;
153:     }
154: 
155:     /**
156:      * Aggregate options
157:      *
158:      * @param Mage_Rating_Model_Rating_Option $option
159:      */
160:     public function aggregate($option)
161:     {
162:         $vote = Mage::getModel('rating/rating_option_vote')->load($option->getVoteId());
163:         $this->aggregateEntityByRatingId($vote->getRatingId(), $vote->getEntityPkValue());
164:     }
165: 
166:     /**
167:      * Aggregate entity by rating id
168:      *
169:      * @param int $ratingId
170:      * @param int $entityPkValue
171:      */
172:     public function aggregateEntityByRatingId($ratingId, $entityPkValue)
173:     {
174:         $readAdapter  = $this->_getReadAdapter();
175:         $writeAdapter = $this->_getWriteAdapter();
176: 
177:         $select = $readAdapter->select()
178:             ->from($this->_aggregateTable, array('store_id', 'primary_id'))
179:             ->where('rating_id = :rating_id')
180:             ->where('entity_pk_value = :pk_value');
181:         $bind = array(':rating_id' => $ratingId, ':pk_value' => $entityPkValue);
182:         $oldData = $readAdapter->fetchPairs($select, $bind);
183: 
184:         $appVoteCountCond    = $readAdapter->getCheckSql('review.status_id=1', 'vote.vote_id', 'NULL');
185:         $appVoteValueSumCond = $readAdapter->getCheckSql('review.status_id=1', 'vote.value', '0');
186: 
187:         $select = $readAdapter->select()
188:             ->from(array('vote'=>$this->_ratingVoteTable),
189:                 array(
190:                     'vote_count'         => new Zend_Db_Expr('COUNT(vote.vote_id)'),
191:                     'vote_value_sum'     => new Zend_Db_Expr('SUM(vote.value)'),
192:                     'app_vote_count'     => new Zend_Db_Expr("COUNT({$appVoteCountCond})"),
193:                     'app_vote_value_sum' => new Zend_Db_Expr("SUM({$appVoteValueSumCond})") ))
194:             ->join(array('review'   =>$this->_reviewTable),
195:                 'vote.review_id=review.review_id',
196:                 array())
197:             ->joinLeft(array('store'=>$this->_reviewStoreTable),
198:                 'vote.review_id=store.review_id', 'store_id')
199:             ->join(array('rstore'   =>$this->_ratingStoreTable),
200:                 'vote.rating_id=rstore.rating_id AND rstore.store_id=store.store_id',
201:                 array())
202:             ->where('vote.rating_id = :rating_id')
203:             ->where('vote.entity_pk_value = :pk_value')
204:             ->group(array(
205:                 'vote.rating_id',
206:                 'vote.entity_pk_value',
207:                 'store.store_id'
208:             ));
209: 
210:         $perStoreInfo = $readAdapter->fetchAll($select, $bind);
211: 
212:         $usedStores = array();
213:         foreach ($perStoreInfo as $row) {
214:             $saveData = array(
215:                 'rating_id'        => $ratingId,
216:                 'entity_pk_value'  => $entityPkValue,
217:                 'vote_count'       => $row['vote_count'],
218:                 'vote_value_sum'   => $row['vote_value_sum'],
219:                 'percent'          => (($row['vote_value_sum']/$row['vote_count'])/5) * 100,
220:                 'percent_approved' => ($row['app_vote_count'] ? ((($row['app_vote_value_sum']/$row['app_vote_count'])/5) * 100) : 0),
221:                 'store_id'         => $row['store_id'],
222:             );
223: 
224:             if (isset($oldData[$row['store_id']])) {
225:                 $condition = array('primary_id = ?' => $oldData[$row['store_id']]);
226:                 $writeAdapter->update($this->_aggregateTable, $saveData, $condition);
227:             } else {
228:                 $writeAdapter->insert($this->_aggregateTable, $saveData);
229:             }
230: 
231:             $usedStores[] = $row['store_id'];
232:         }
233: 
234:         $toDelete = array_diff(array_keys($oldData), $usedStores);
235: 
236:         foreach ($toDelete as $storeId) {
237:             $condition = array('primary_id = ?' => $oldData[$storeId]);
238:             $writeAdapter->delete($this->_aggregateTable, $condition);
239:         }
240:     }
241: 
242:     /**
243:      * Load object data by optionId
244:      * Method renamed from 'load'.
245:      *
246:      * @param int $optionId
247:      * @return array
248:      */
249:     public function loadDataById($optionId)
250:     {
251:         if (!$this->_optionData || $this->_optionId != $optionId) {
252:             $adapter = $this->_getReadAdapter();
253:             $select = $adapter->select();
254:             $select->from($this->_ratingOptionTable)
255:                 ->where('option_id = :option_id');
256: 
257:             $data = $adapter->fetchRow($select, array(':option_id' => $optionId));
258: 
259:             $this->_optionData = $data;
260:             $this->_optionId = $optionId;
261:             return $data;
262:         }
263: 
264:         return $this->_optionData;
265:     }
266: 
267: }
268: