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 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 extends Mage_Core_Model_Resource_Db_Abstract
 35: {
 36:     const RATING_STATUS_APPROVED = 'Approved';
 37: 
 38:     /**
 39:      * Resource initialization
 40:      */
 41:     protected function _construct()
 42:     {
 43:         $this->_init('rating/rating', 'rating_id');
 44:     }
 45: 
 46:     /**
 47:      * Initialize unique fields
 48:      *
 49:      * @return Mage_Rating_Model_Resource_Rating
 50:      */
 51:     protected function _initUniqueFields()
 52:     {
 53:         $this->_uniqueFields = array(array(
 54:             'field' => 'rating_code',
 55:             'title' => /* Mage::helper('rating')->__('Rating with the same title')*/ ''
 56:         ));
 57:         return $this;
 58:     }
 59: 
 60:     /**
 61:      * Retrieve select object for load object data
 62:      *
 63:      * @param string $field
 64:      * @param mixed $value
 65:      * @param Mage_Rating_Model_Rating $object
 66:      * @return Varien_Db_Select
 67:      */
 68:     protected function _getLoadSelect($field, $value, $object)
 69:     {
 70:         $adapter    = $this->_getReadAdapter();
 71: 
 72:         $table      = $this->getMainTable();
 73:         $storeId    = (int)Mage::app()->getStore()->getId();
 74:         $select     = parent::_getLoadSelect($field, $value, $object);
 75:         $codeExpr   = $adapter->getIfNullSql('title.value', "{$table}.rating_code");
 76: 
 77:         $select->joinLeft(
 78:             array('title' => $this->getTable('rating/rating_title')),
 79:             $adapter->quoteInto("{$table}.rating_id = title.rating_id AND title.store_id = ?", $storeId),
 80:             array('rating_code' => $codeExpr));
 81: 
 82:         return $select;
 83:     }
 84: 
 85:     /**
 86:      * Actions after load
 87:      *
 88:      * @param Mage_Rating_Model_Rating $object
 89:      * @return Mage_Rating_Model_Resource_Rating
 90:      */
 91:     protected function _afterLoad(Mage_Core_Model_Abstract $object)
 92:     {
 93:         parent::_afterLoad($object);
 94: 
 95:         if (!$object->getId()) {
 96:             return $this;
 97:         }
 98: 
 99:         $adapter = $this->_getReadAdapter();
100:         $bind    = array(':rating_id' => (int)$object->getId());
101:         // load rating titles
102:         $select  = $adapter->select()
103:             ->from($this->getTable('rating/rating_title'), array('store_id', 'value'))
104:             ->where('rating_id=:rating_id');
105: 
106:         $result  = $adapter->fetchPairs($select, $bind);
107:         if ($result) {
108:             $object->setRatingCodes($result);
109:         }
110: 
111:         // load rating available in stores
112:         $object->setStores($this->getStores((int)$object->getId()));
113: 
114:         return $this;
115:     }
116: 
117:     /**
118:      * Retrieve store IDs related to given rating
119:      *
120:      * @param  int $ratingId
121:      * @return array
122:      */
123:     public function getStores($ratingId)
124:     {
125:         $select = $this->_getReadAdapter()->select()
126:             ->from($this->getTable('rating/rating_store'), 'store_id')
127:             ->where('rating_id = ?', $ratingId);
128:         return $this->_getReadAdapter()->fetchCol($select);
129:     }
130: 
131:     /**
132:      * Actions after save
133:      *
134:      * @param Mage_Rating_Model_Rating $object
135:      * @return Mage_Rating_Model_Resource_Rating
136:      */
137:     protected function _afterSave(Mage_Core_Model_Abstract $object)
138:     {
139:         parent::_afterSave($object);
140: 
141:         $adapter  = $this->_getWriteAdapter();
142:         $ratingId = (int)$object->getId();
143: 
144:         if ($object->hasRatingCodes()) {
145:             $ratingTitleTable = $this->getTable('rating/rating_title');
146:             $adapter->beginTransaction();
147:             try {
148:                 $select = $adapter->select()
149:                     ->from($ratingTitleTable, array('store_id', 'value'))
150:                     ->where('rating_id = :rating_id');
151:                 $old    = $adapter->fetchPairs($select, array(':rating_id' => $ratingId));
152:                 $new    = array_filter(array_map('trim', $object->getRatingCodes()));
153: 
154:                 $insert = array_diff_assoc($new, $old);
155:                 $delete = array_diff_assoc($old, $new);
156:                 if (!empty($delete)) {
157:                     $where = array(
158:                         'rating_id = ?' => $ratingId,
159:                         'store_id IN(?)' => array_keys($delete)
160:                     );
161:                     $adapter->delete($ratingTitleTable, $where);
162:                 }
163: 
164:                 if ($insert) {
165:                     $data = array();
166:                     foreach ($insert as $storeId => $title) {
167:                         $data[] = array(
168:                             'rating_id' => $ratingId,
169:                             'store_id'  => (int)$storeId,
170:                             'value'     => $title
171:                         );
172:                     }
173:                     if (!empty($data)) {
174:                         $adapter->insertMultiple($ratingTitleTable, $data);
175:                     }
176:                 }
177:                 $adapter->commit();
178:             } catch (Exception $e) {
179:                 Mage::logException($e);
180:                 $adapter->rollBack();
181:             }
182:         }
183: 
184:         if ($object->hasStores()) {
185:             $ratingStoreTable = $this->getTable('rating/rating_store');
186:             $adapter->beginTransaction();
187:             try {
188:                 $select = $adapter->select()
189:                     ->from($ratingStoreTable, array('store_id'))
190:                     ->where('rating_id = :rating_id');
191:                 $old = $adapter->fetchCol($select, array(':rating_id' => $ratingId));
192:                 $new = $object->getStores();
193: 
194:                 $insert = array_diff($new, $old);
195:                 $delete = array_diff($old, $new);
196: 
197:                 if ($delete) {
198:                     $where = array(
199:                         'rating_id = ?' => $ratingId,
200:                         'store_id IN(?)' => $delete
201:                     );
202:                     $adapter->delete($ratingStoreTable, $where);
203:                 }
204: 
205:                 if ($insert) {
206:                     $data = array();
207:                     foreach ($insert as $storeId) {
208:                         $data[] = array(
209:                             'rating_id' => $ratingId,
210:                             'store_id'  => (int)$storeId,
211:                         );
212:                     }
213:                     $adapter->insertMultiple($ratingStoreTable, $data);
214:                 }
215: 
216:                 $adapter->commit();
217:             } catch (Exception $e) {
218:                 Mage::logException($e);
219:                 $adapter->rollBack();
220:             }
221:         }
222: 
223:         return $this;
224:     }
225: 
226:     /**
227:      * Perform actions after object delete
228:      * Prepare rating data for reaggregate all data for reviews
229:      *
230:      * @param Mage_Rating_Model_Rating $object
231:      * @return Mage_Rating_Model_Resource_Rating
232:      */
233:     protected function _afterDelete(Mage_Core_Model_Abstract $object)
234:     {
235:         parent::_afterDelete($object);
236:         if (!Mage::helper('rating')->isModuleEnabled('Mage_Review')) {
237:             return $this;
238:         }
239:         $data = $this->_getEntitySummaryData($object);
240:         $summary = array();
241:         foreach ($data as $row) {
242:             $clone = clone $object;
243:             $clone->addData($row);
244:             $summary[$clone->getStoreId()][$clone->getEntityPkValue()] = $clone;
245:         }
246:         Mage::getResourceModel('review/review_summary')->reAggregate($summary);
247:         return $this;
248:     }
249: 
250:     /**
251:      * Return array of rating summary
252:      *
253:      * @param Mage_Rating_Model_Rating $object
254:      * @param boolean $onlyForCurrentStore
255:      * @return array
256:      */
257:     public function getEntitySummary($object, $onlyForCurrentStore = true)
258:     {
259:         $data = $this->_getEntitySummaryData($object);
260: 
261:         if ($onlyForCurrentStore) {
262:             foreach ($data as $row) {
263:                 if ($row['store_id']==Mage::app()->getStore()->getId()) {
264:                     $object->addData($row);
265:                 }
266:             }
267:             return $object;
268:         }
269: 
270:         $result = array();
271: 
272:         //$stores = Mage::app()->getStore()->getResourceCollection()->load();
273:         $stores = Mage::getModel('core/store')->getResourceCollection()->load();
274: 
275:         foreach ($data as $row) {
276:             $clone = clone $object;
277:             $clone->addData($row);
278:             $result[$clone->getStoreId()] = $clone;
279:         }
280: 
281:         $usedStoresId = array_keys($result);
282: 
283:         foreach ($stores as $store) {
284:             if (!in_array($store->getId(), $usedStoresId)) {
285:                 $clone = clone $object;
286:                 $clone->setCount(0);
287:                 $clone->setSum(0);
288:                 $clone->setStoreId($store->getId());
289:                 $result[$store->getId()] = $clone;
290:             }
291:         }
292: 
293:         return array_values($result);
294:     }
295: 
296:     /**
297:      * Return data of rating summary
298:      *
299:      * @param Mage_Rating_Model_Rating $object
300:      * @return array
301:      */
302:     protected function _getEntitySummaryData($object)
303:     {
304:         $adapter    = $this->_getReadAdapter();
305: 
306:         $sumColumn      = new Zend_Db_Expr("SUM(rating_vote.{$adapter->quoteIdentifier('percent')})");
307:         $countColumn    = new Zend_Db_Expr("COUNT(*)");
308: 
309:         $select = $adapter->select()
310:             ->from(array('rating_vote' => $this->getTable('rating/rating_option_vote')),
311:                 array(
312:                     'entity_pk_value' => 'rating_vote.entity_pk_value',
313:                     'sum'             => $sumColumn,
314:                     'count'           => $countColumn))
315:             ->join(array('review' => $this->getTable('review/review')),
316:                 'rating_vote.review_id=review.review_id',
317:                 array())
318:             ->joinLeft(array('review_store' => $this->getTable('review/review_store')),
319:                 'rating_vote.review_id=review_store.review_id',
320:                 array('review_store.store_id'))
321:             ->join(array('rating_store' => $this->getTable('rating/rating_store')),
322:                 'rating_store.rating_id = rating_vote.rating_id AND rating_store.store_id = review_store.store_id',
323:                 array())
324:             ->join(array('review_status' => $this->getTable('review/review_status')),
325:                 'review.status_id = review_status.status_id',
326:                 array())
327:             ->where('review_status.status_code = :status_code')
328:             ->group('rating_vote.entity_pk_value')
329:             ->group('review_store.store_id');
330:         $bind = array(':status_code' => self::RATING_STATUS_APPROVED);
331: 
332:         $entityPkValue = $object->getEntityPkValue();
333:         if ($entityPkValue) {
334:             $select->where('rating_vote.entity_pk_value = :pk_value');
335:             $bind[':pk_value'] = $entityPkValue;
336:         }
337: 
338:         return $adapter->fetchAll($select, $bind);
339:     }
340: 
341:     /**
342:      * Review summary
343:      *
344:      * @param Mage_Rating_Model_Rating $object
345:      * @param boolean $onlyForCurrentStore
346:      * @return array
347:      */
348:     public function getReviewSummary($object, $onlyForCurrentStore = true)
349:     {
350:         $adapter = $this->_getReadAdapter();
351: 
352:         $sumColumn      = new Zend_Db_Expr("SUM(rating_vote.{$adapter->quoteIdentifier('percent')})");
353:         $countColumn    = new Zend_Db_Expr('COUNT(*)');
354:         $select = $adapter->select()
355:             ->from(array('rating_vote' => $this->getTable('rating/rating_option_vote')),
356:                 array(
357:                     'sum'   => $sumColumn,
358:                     'count' => $countColumn
359:                 ))
360:             ->joinLeft(array('review_store' => $this->getTable('review/review_store')),
361:                 'rating_vote.review_id = review_store.review_id',
362:                 array('review_store.store_id'))
363:             ->join(array('rating_store' => $this->getTable('rating/rating_store')),
364:                 'rating_store.rating_id = rating_vote.rating_id AND rating_store.store_id = review_store.store_id',
365:                 array())
366:             ->where('rating_vote.review_id = :review_id')
367:             ->group('rating_vote.review_id')
368:             ->group('review_store.store_id');
369: 
370:         $data = $adapter->fetchAll($select, array(':review_id' => $object->getReviewId()));
371: 
372:         if ($onlyForCurrentStore) {
373:             foreach ($data as $row) {
374:                 if ($row['store_id'] == Mage::app()->getStore()->getId()) {
375:                     $object->addData($row);
376:                 }
377:             }
378:             return $object;
379:         }
380: 
381:         $result = array();
382: 
383:         $stores = Mage::app()->getStore()->getResourceCollection()->load();
384: 
385:         foreach ($data as $row) {
386:             $clone = clone $object;
387:             $clone->addData($row);
388:             $result[$clone->getStoreId()] = $clone;
389:         }
390: 
391:         $usedStoresId = array_keys($result);
392: 
393:         foreach ($stores as $store) {
394:                if (!in_array($store->getId(), $usedStoresId)) {
395:                    $clone = clone $object;
396:                 $clone->setCount(0);
397:                 $clone->setSum(0);
398:                 $clone->setStoreId($store->getId());
399:                 $result[$store->getId()] = $clone;
400: 
401:                }
402:         }
403: 
404:         return array_values($result);
405:     }
406: 
407:     /**
408:      * Get rating entity type id by code
409:      *
410:      * @param string $entityCode
411:      * @return int
412:      */
413:     public function getEntityIdByCode($entityCode)
414:     {
415:         $select = $this->_getReadAdapter()->select()
416:             ->from($this->getTable('rating/rating_entity'), array('entity_id'))
417:             ->where('entity_code = :entity_code');
418: 
419:         return $this->_getReadAdapter()->fetchOne($select, array(':entity_code' => $entityCode));
420:     }
421: 
422:     /**
423:      * Delete ratings by product id
424:      *
425:      * @param int $productId
426:      * @return Mage_Rating_Model_Resource_Rating
427:      */
428:     public function deleteAggregatedRatingsByProductId($productId)
429:     {
430:         $entityId = $this->getEntityIdByCode(Mage_Rating_Model_Rating::ENTITY_PRODUCT_CODE);
431:         $adapter  = $this->_getWriteAdapter();
432:         $select   = $adapter->select()
433:             ->from($this->getMainTable(), 'rating_id')
434:             ->where('entity_id = :entity_id');
435:         $ratingIds = $adapter->fetchCol($select, array(':entity_id' => $entityId));
436: 
437:         if ($ratingIds) {
438:             $where = array(
439:                 'entity_pk_value = ?' => (int) $productId,
440:                 'rating_id IN(?)'     => $ratingIds
441:             );
442:             $adapter->delete($this->getTable('rating/rating_vote_aggregated'), $where);
443:         }
444: 
445:         return $this;
446:     }
447: }
448: