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