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 most viewed collection
 30:  */
 31: class Mage_Reports_Model_Resource_Report_Product_Viewed_Collection
 32:     extends Mage_Reports_Model_Resource_Report_Collection_Abstract
 33: {
 34:     /**
 35:      * Rating limit
 36:      *
 37:      * @var int
 38:      */
 39:     protected $_ratingLimit        = 5;
 40: 
 41:     /**
 42:      * Selected columns
 43:      *
 44:      * @var array
 45:      */
 46:     protected $_selectedColumns    = array();
 47: 
 48:     /**
 49:      * Initialize custom resource model
 50:      *
 51:      */
 52:     public function __construct()
 53:     {
 54:         parent::_construct();
 55:         $this->setModel('adminhtml/report_item');
 56:         $this->_resource = Mage::getResourceModel('sales/report')
 57:             ->init(Mage_Reports_Model_Resource_Report_Product_Viewed::AGGREGATION_DAILY);
 58:         $this->setConnection($this->getResource()->getReadConnection());
 59:         // overwrite default behaviour
 60:         $this->_applyFilters = false;
 61:     }
 62: 
 63:     /**
 64:      * Retrieve selected columns
 65:      *
 66:      * @return array
 67:      */
 68:     protected function _getSelectedColumns()
 69:     {
 70:         $adapter = $this->getConnection();
 71: 
 72:         if (!$this->_selectedColumns) {
 73:             if ($this->isTotals()) {
 74:                 $this->_selectedColumns = $this->getAggregatedColumns();
 75:             } else {
 76:                 $this->_selectedColumns = array(
 77:                     'period'         =>  sprintf('MAX(%s)', $adapter->getDateFormatSql('period', '%Y-%m-%d')),
 78:                     'views_num'      => 'SUM(views_num)',
 79:                     'product_id'     => 'product_id',
 80:                     'product_name'   => 'MAX(product_name)',
 81:                     'product_price'  => 'MAX(product_price)',
 82:                 );
 83:                 if ('year' == $this->_period) {
 84:                     $this->_selectedColumns['period'] = $adapter->getDateFormatSql('period', '%Y');
 85:                 } elseif ('month' == $this->_period) {
 86:                     $this->_selectedColumns['period'] = $adapter->getDateFormatSql('period', '%Y-%m');
 87:                 }
 88:             }
 89:         }
 90:         return $this->_selectedColumns;
 91:     }
 92: 
 93:     /**
 94:      * Make select object for date boundary
 95:      *
 96:      * @param mixed $from
 97:      * @param mixed $to
 98:      * @return Zend_Db_Select
 99:      */
100:     protected function _makeBoundarySelect($from, $to)
101:     {
102:         $adapter = $this->getConnection();
103:         $cols    = $this->_getSelectedColumns();
104:         $cols['views_num'] = 'SUM(views_num)';
105:         $select  = $adapter->select()
106:             ->from($this->getResource()->getMainTable(), $cols)
107:             ->where('period >= ?', $from)
108:             ->where('period <= ?', $to)
109:             ->group('product_id')
110:             ->order('views_num DESC')
111:             ->limit($this->_ratingLimit);
112: 
113:         $this->_applyStoresFilterToSelect($select);
114: 
115:         return $select;
116:     }
117: 
118:     /**
119:      * Init collection select
120:      *
121:      * @return Mage_Reports_Model_Resource_Report_Product_Viewed_Collection
122:      */
123:     protected function _initSelect()
124:     {
125:         $select = $this->getSelect();
126: 
127:         // if grouping by product, not by period
128:         if (!$this->_period) {
129:             $cols = $this->_getSelectedColumns();
130:             $cols['views_num'] = 'SUM(views_num)';
131:             if ($this->_from || $this->_to) {
132:                 $mainTable = $this->getTable(Mage_Reports_Model_Resource_Report_Product_Viewed::AGGREGATION_DAILY);
133:                 $select->from($mainTable, $cols);
134:             } else {
135:                 $mainTable = $this->getTable(Mage_Reports_Model_Resource_Report_Product_Viewed::AGGREGATION_YEARLY);
136:                 $select->from($mainTable, $cols);
137:             }
138: 
139:             //exclude removed products
140:             $subSelect = $this->getConnection()->select();
141:             $subSelect->from(array('existed_products' => $this->getTable('catalog/product')), new Zend_Db_Expr('1)'));
142: 
143:             $select->exists($subSelect, $mainTable . '.product_id = existed_products.entity_id')
144:                 ->group('product_id')
145:                 ->order('views_num ' . Varien_Db_Select::SQL_DESC)
146:                 ->limit($this->_ratingLimit);
147: 
148:             return $this;
149:         }
150: 
151:         if ('year' == $this->_period) {
152:             $mainTable = $this->getTable(Mage_Reports_Model_Resource_Report_Product_Viewed::AGGREGATION_YEARLY);
153:             $select->from($mainTable, $this->_getSelectedColumns());
154:         } elseif ('month' == $this->_period) {
155:             $mainTable = $this->getTable(Mage_Reports_Model_Resource_Report_Product_Viewed::AGGREGATION_MONTHLY);
156:             $select->from($mainTable, $this->_getSelectedColumns());
157:         } else {
158:             $mainTable = $this->getTable(Mage_Reports_Model_Resource_Report_Product_Viewed::AGGREGATION_DAILY);
159:             $select->from($mainTable, $this->_getSelectedColumns());
160:         }
161:         if (!$this->isTotals()) {
162:             $select->group(array('period', 'product_id'));
163:         }
164:         $select->where('rating_pos <= ?', $this->_ratingLimit);
165: 
166:         return $this;
167:     }
168: 
169:     /**
170:      * Get SQL for get record count
171:      *
172:      * @return Varien_Db_Select
173:      */
174:     public function getSelectCountSql()
175:     {
176:         $this->_renderFilters();
177:         $select = clone $this->getSelect();
178:         $select->reset(Zend_Db_Select::ORDER);
179:         return $this->getConnection()->select()->from($select, 'COUNT(*)');
180:     }
181: 
182:     /**
183:      * Set ids for store restrictions
184:      *
185:      * @param  array $storeIds
186:      * @return Mage_Reports_Model_Resource_Report_Product_Viewed_Collection
187:      */
188:     public function addStoreRestrictions($storeIds)
189:     {
190:         if (!is_array($storeIds)) {
191:             $storeIds = array($storeIds);
192:         }
193:         $currentStoreIds = $this->_storesIds;
194:         if (isset($currentStoreIds) && $currentStoreIds != Mage_Core_Model_App::ADMIN_STORE_ID
195:             && $currentStoreIds != array(Mage_Core_Model_App::ADMIN_STORE_ID)) {
196:             if (!is_array($currentStoreIds)) {
197:                 $currentStoreIds = array($currentStoreIds);
198:             }
199:             $this->_storesIds = array_intersect($currentStoreIds, $storeIds);
200:         } else {
201:             $this->_storesIds = $storeIds;
202:         }
203: 
204:         return $this;
205:     }
206: 
207:     /**
208:      * Redeclare parent method for applying filters after parent method
209:      * but before adding unions and calculating totals
210:      *
211:      * @return Mage_Reports_Model_Resource_Report_Product_Viewed_Collection
212:      */
213:     protected function _beforeLoad()
214:     {
215:         parent::_beforeLoad();
216: 
217:         $this->_applyStoresFilter();
218: 
219:         if ($this->_period) {
220:             $selectUnions = array();
221: 
222:             // apply date boundaries (before calling $this->_applyDateRangeFilter())
223:             $dtFormat   = Varien_Date::DATE_INTERNAL_FORMAT;
224:             $periodFrom = (!is_null($this->_from) ? new Zend_Date($this->_from, $dtFormat) : null);
225:             $periodTo   = (!is_null($this->_to)   ? new Zend_Date($this->_to,   $dtFormat) : null);
226:             if ('year' == $this->_period) {
227: 
228:                 if ($periodFrom) {
229:                     // not the first day of the year
230:                     if ($periodFrom->toValue(Zend_Date::MONTH) != 1 || $periodFrom->toValue(Zend_Date::DAY) != 1) {
231:                         $dtFrom = $periodFrom->getDate();
232:                         // last day of the year
233:                         $dtTo = $periodFrom->getDate()->setMonth(12)->setDay(31);
234:                         if (!$periodTo || $dtTo->isEarlier($periodTo)) {
235:                             $selectUnions[] = $this->_makeBoundarySelect(
236:                                 $dtFrom->toString($dtFormat),
237:                                 $dtTo->toString($dtFormat)
238:                             );
239: 
240:                             // first day of the next year
241:                             $this->_from = $periodFrom->getDate()
242:                                 ->addYear(1)
243:                                 ->setMonth(1)
244:                                 ->setDay(1)
245:                                 ->toString($dtFormat);
246:                         }
247:                     }
248:                 }
249: 
250:                 if ($periodTo) {
251:                     // not the last day of the year
252:                     if ($periodTo->toValue(Zend_Date::MONTH) != 12 || $periodTo->toValue(Zend_Date::DAY) != 31) {
253:                         $dtFrom = $periodTo->getDate()->setMonth(1)->setDay(1);  // first day of the year
254:                         $dtTo = $periodTo->getDate();
255:                         if (!$periodFrom || $dtFrom->isLater($periodFrom)) {
256:                             $selectUnions[] = $this->_makeBoundarySelect(
257:                                 $dtFrom->toString($dtFormat),
258:                                 $dtTo->toString($dtFormat)
259:                             );
260: 
261:                             // last day of the previous year
262:                             $this->_to = $periodTo->getDate()
263:                                 ->subYear(1)
264:                                 ->setMonth(12)
265:                                 ->setDay(31)
266:                                 ->toString($dtFormat);
267:                         }
268:                     }
269:                 }
270: 
271:                 if ($periodFrom && $periodTo) {
272:                     // the same year
273:                     if ($periodFrom->toValue(Zend_Date::YEAR) == $periodTo->toValue(Zend_Date::YEAR)) {
274:                         $dtFrom = $periodFrom->getDate();
275:                         $dtTo = $periodTo->getDate();
276:                         $selectUnions[] = $this->_makeBoundarySelect(
277:                             $dtFrom->toString($dtFormat),
278:                             $dtTo->toString($dtFormat)
279:                         );
280: 
281:                         $this->getSelect()->where('1<>1');
282:                     }
283:                 }
284: 
285:             }
286:             else if ('month' == $this->_period) {
287:                 if ($periodFrom) {
288:                     // not the first day of the month
289:                     if ($periodFrom->toValue(Zend_Date::DAY) != 1) {
290:                         $dtFrom = $periodFrom->getDate();
291:                         // last day of the month
292:                         $dtTo = $periodFrom->getDate()->addMonth(1)->setDay(1)->subDay(1);
293:                         if (!$periodTo || $dtTo->isEarlier($periodTo)) {
294:                             $selectUnions[] = $this->_makeBoundarySelect(
295:                                 $dtFrom->toString($dtFormat),
296:                                 $dtTo->toString($dtFormat)
297:                             );
298: 
299:                             // first day of the next month
300:                             $this->_from = $periodFrom->getDate()->addMonth(1)->setDay(1)->toString($dtFormat);
301:                         }
302:                     }
303:                 }
304: 
305:                 if ($periodTo) {
306:                     // not the last day of the month
307:                     if ($periodTo->toValue(Zend_Date::DAY) != $periodTo->toValue(Zend_Date::MONTH_DAYS)) {
308:                         $dtFrom = $periodTo->getDate()->setDay(1);  // first day of the month
309:                         $dtTo = $periodTo->getDate();
310:                         if (!$periodFrom || $dtFrom->isLater($periodFrom)) {
311:                             $selectUnions[] = $this->_makeBoundarySelect(
312:                                 $dtFrom->toString($dtFormat),
313:                                 $dtTo->toString($dtFormat)
314:                             );
315: 
316:                             // last day of the previous month
317:                             $this->_to = $periodTo->getDate()->setDay(1)->subDay(1)->toString($dtFormat);
318:                         }
319:                     }
320:                 }
321: 
322:                 if ($periodFrom && $periodTo) {
323:                     // the same month
324:                     if ($periodFrom->toValue(Zend_Date::YEAR) == $periodTo->toValue(Zend_Date::YEAR)
325:                         && $periodFrom->toValue(Zend_Date::MONTH) == $periodTo->toValue(Zend_Date::MONTH)
326:                     ) {
327:                         $dtFrom = $periodFrom->getDate();
328:                         $dtTo = $periodTo->getDate();
329:                         $selectUnions[] = $this->_makeBoundarySelect(
330:                             $dtFrom->toString($dtFormat),
331:                             $dtTo->toString($dtFormat)
332:                         );
333: 
334:                         $this->getSelect()->where('1<>1');
335:                     }
336:                 }
337: 
338:             }
339: 
340:             $this->_applyDateRangeFilter();
341: 
342:             // add unions to select
343:             if ($selectUnions) {
344:                 $unionParts = array();
345:                 $cloneSelect = clone $this->getSelect();
346:                 $helper = Mage::getResourceHelper('core');
347:                 $unionParts[] = '(' . $cloneSelect . ')';
348:                 foreach ($selectUnions as $union) {
349:                     $query = $helper->getQueryUsingAnalyticFunction($union);
350:                     $unionParts[] = '(' . $query . ')';
351:                 }
352:                 $this->getSelect()->reset()->union($unionParts, Zend_Db_Select::SQL_UNION_ALL);
353:             }
354: 
355:             if ($this->isTotals()) {
356:                 // calculate total
357:                 $cloneSelect = clone $this->getSelect();
358:                 $this->getSelect()->reset()->from($cloneSelect, $this->getAggregatedColumns());
359:             } else {
360:                 // add sorting
361:                 $this->getSelect()->order(array('period ASC', 'views_num DESC'));
362:             }
363:         }
364: 
365:         return $this;
366:     }
367: }
368: