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:  * Products Report collection
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Reports
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Reports_Model_Resource_Product_Collection extends Mage_Catalog_Model_Resource_Product_Collection
 36: {
 37:     const SELECT_COUNT_SQL_TYPE_CART           = 1;
 38: 
 39:     /**
 40:      * Product entity identifier
 41:      *
 42:      * @var int
 43:      */
 44:     protected $_productEntityId;
 45: 
 46:     /**
 47:      * Product entity table name
 48:      *
 49:      * @var string
 50:      */
 51:     protected $_productEntityTableName;
 52: 
 53:     /**
 54:      * Product entity type identifier
 55:      *
 56:      * @var int
 57:      */
 58:     protected $_productEntityTypeId;
 59: 
 60:     /**
 61:      * select count
 62:      *
 63:      * @var int
 64:      */
 65:     protected $_selectCountSqlType               = 0;
 66: 
 67:     /**
 68:      * Init main class options
 69:      *
 70:      */
 71:     public function __construct()
 72:     {
 73:         $product = Mage::getResourceSingleton('catalog/product');
 74:         /* @var $product Mage_Catalog_Model_Entity_Product */
 75:         $this->setProductEntityId($product->getEntityIdField());
 76:         $this->setProductEntityTableName($product->getEntityTable());
 77:         $this->setProductEntityTypeId($product->getTypeId());
 78: 
 79:         parent::__construct();
 80:     }
 81:     /**
 82:      * Set Type for COUNT SQL Select
 83:      *
 84:      * @param int $type
 85:      * @return Mage_Reports_Model_Resource_Product_Collection
 86:      */
 87:     public function setSelectCountSqlType($type)
 88:     {
 89:         $this->_selectCountSqlType = $type;
 90:         return $this;
 91:     }
 92: 
 93:     /**
 94:      * Set product entity id
 95:      *
 96:      * @param int $value
 97:      * @return Mage_Reports_Model_Resource_Product_Collection
 98:      */
 99:     public function setProductEntityId($entityId)
100:     {
101:         $this->_productEntityId = (int)$entityId;
102:         return $this;
103:     }
104: 
105:     /**
106:      * Get product entity id
107:      *
108:      * @return int
109:      */
110:     public function getProductEntityId()
111:     {
112:         return $this->_productEntityId;
113:     }
114: 
115:     /**
116:      * Set product entity table name
117:      *
118:      * @param string $value
119:      * @return Mage_Reports_Model_Resource_Product_Collection
120:      */
121:     public function setProductEntityTableName($value)
122:     {
123:         $this->_productEntityTableName = $value;
124:         return $this;
125:     }
126: 
127:     /**
128:      * Get product entity table name
129:      *
130:      * @return string
131:      */
132:     public function getProductEntityTableName()
133:     {
134:         return $this->_productEntityTableName;
135:     }
136: 
137:     /**
138:      * Set product entity type id
139:      *
140:      * @param int $value
141:      * @return Mage_Reports_Model_Resource_Product_Collection
142:      */
143:     public function setProductEntityTypeId($value)
144:     {
145:         $this->_productEntityTypeId = $value;
146:         return $this;
147:     }
148: 
149:     /**
150:      * Get product entity tyoe id
151:      *
152:      * @return int
153:      */
154:     public function getProductEntityTypeId()
155:     {
156:         return  $this->_productEntityTypeId;
157:     }
158: 
159:     /**
160:      * Join fields
161:      *
162:      * @return Mage_Reports_Model_Resource_Product_Collection
163:      */
164:     protected function _joinFields()
165:     {
166:         $this->_totals = new Varien_Object();
167: 
168:         $this->addAttributeToSelect('entity_id')
169:             ->addAttributeToSelect('name')
170:             ->addAttributeToSelect('price');
171: 
172:         return $this;
173:     }
174: 
175:     /**
176:      * Get select count sql
177:      *
178:      * @return unknown
179:      */
180:     public function getSelectCountSql()
181:     {
182:         if ($this->_selectCountSqlType == self::SELECT_COUNT_SQL_TYPE_CART) {
183:             $countSelect = clone $this->getSelect();
184:             $countSelect->reset()
185:                 ->from(
186:                     array('quote_item_table' => $this->getTable('sales/quote_item')),
187:                     array('COUNT(DISTINCT quote_item_table.product_id)'))
188:                 ->join(
189:                     array('quote_table' => $this->getTable('sales/quote')),
190:                     'quote_table.entity_id = quote_item_table.quote_id AND quote_table.is_active = 1',
191:                     array()
192:                 );
193:             return $countSelect;
194:         }
195: 
196:         $countSelect = clone $this->getSelect();
197:         $countSelect->reset(Zend_Db_Select::ORDER);
198:         $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
199:         $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
200:         $countSelect->reset(Zend_Db_Select::COLUMNS);
201:         $countSelect->reset(Zend_Db_Select::GROUP);
202:         $countSelect->reset(Zend_Db_Select::HAVING);
203:         $countSelect->columns("count(DISTINCT e.entity_id)");
204: 
205:         return $countSelect;
206:     }
207: 
208:     /**
209:      * Add carts count
210:      *
211:      * @return Mage_Reports_Model_Resource_Product_Collection
212:      */
213:     public function addCartsCount()
214:     {
215:         $countSelect = clone $this->getSelect();
216:         $countSelect->reset();
217: 
218:         $countSelect->from(array('quote_items' => $this->getTable('sales/quote_item')), 'COUNT(*)')
219:             ->join(array('quotes' => $this->getTable('sales/quote')),
220:                 'quotes.entity_id = quote_items.quote_id AND quotes.is_active = 1',
221:                 array())
222:             ->where("quote_items.product_id = e.entity_id");
223: 
224:         $this->getSelect()
225:             ->columns(array("carts" => "({$countSelect})"))
226:             ->group("e.{$this->getProductEntityId()}")
227:             ->having('carts > ?', 0);
228: 
229:         return $this;
230:     }
231: 
232:     /**
233:      * Add orders count
234:      *
235:      * @param string $from
236:      * @param string $to
237:      * @return Mage_Reports_Model_Resource_Product_Collection
238:      */
239:     public function addOrdersCount($from = '', $to = '')
240:     {
241:         $orderItemTableName = $this->getTable('sales/order_item');
242:         $productFieldName   = sprintf('e.%s', $this->getProductEntityId());
243: 
244:         $this->getSelect()
245:             ->joinLeft(
246:                 array('order_items' => $orderItemTableName),
247:                 "order_items.product_id = {$productFieldName}",
248:                 array())
249:             ->columns(array('orders' => 'COUNT(order_items2.item_id)'))
250:             ->group($productFieldName);
251: 
252:         $dateFilter = array('order_items2.item_id = order_items.item_id');
253:         if ($from != '' && $to != '') {
254:             $dateFilter[] = $this->_prepareBetweenSql('order_items2.created_at', $from, $to);
255:         }
256: 
257:         $this->getSelect()
258:             ->joinLeft(
259:                 array('order_items2' => $orderItemTableName),
260:                 implode(' AND ', $dateFilter),
261:                 array()
262:             );
263: 
264:         return $this;
265:     }
266: 
267:     /**
268:      * Add ordered qty's
269:      *
270:      * @param string $from
271:      * @param string $to
272:      * @return Mage_Reports_Model_Resource_Product_Collection
273:      */
274:     public function addOrderedQty($from = '', $to = '')
275:     {
276:         $adapter              = $this->getConnection();
277:         $compositeTypeIds     = Mage::getSingleton('catalog/product_type')->getCompositeTypes();
278:         $orderTableAliasName  = $adapter->quoteIdentifier('order');
279: 
280:         $orderJoinCondition   = array(
281:             $orderTableAliasName . '.entity_id = order_items.order_id',
282:             $adapter->quoteInto("{$orderTableAliasName}.state <> ?", Mage_Sales_Model_Order::STATE_CANCELED),
283: 
284:         );
285: 
286:         $productJoinCondition = array(
287:             $adapter->quoteInto('(e.type_id NOT IN (?))', $compositeTypeIds),
288:             'e.entity_id = order_items.product_id',
289:             $adapter->quoteInto('e.entity_type_id = ?', $this->getProductEntityTypeId())
290:         );
291: 
292:         if ($from != '' && $to != '') {
293:             $fieldName            = $orderTableAliasName . '.created_at';
294:             $orderJoinCondition[] = $this->_prepareBetweenSql($fieldName, $from, $to);
295:         }
296: 
297:         $this->getSelect()->reset()
298:             ->from(
299:                 array('order_items' => $this->getTable('sales/order_item')),
300:                 array(
301:                     'ordered_qty' => 'SUM(order_items.qty_ordered)',
302:                     'order_items_name' => 'order_items.name'
303:                 ))
304:             ->joinInner(
305:                 array('order' => $this->getTable('sales/order')),
306:                 implode(' AND ', $orderJoinCondition),
307:                 array())
308:             ->joinLeft(
309:                 array('e' => $this->getProductEntityTableName()),
310:                 implode(' AND ', $productJoinCondition),
311:                 array(
312:                     'entity_id' => 'order_items.product_id',
313:                     'entity_type_id' => 'e.entity_type_id',
314:                     'attribute_set_id' => 'e.attribute_set_id',
315:                     'type_id' => 'e.type_id',
316:                     'sku' => 'e.sku',
317:                     'has_options' => 'e.has_options',
318:                     'required_options' => 'e.required_options',
319:                     'created_at' => 'e.created_at',
320:                     'updated_at' => 'e.updated_at'
321:                 ))
322:             ->where('parent_item_id IS NULL')
323:             ->group('order_items.product_id')
324:             ->having('SUM(order_items.qty_ordered) > ?', 0);
325:         return $this;
326:     }
327: 
328:     /**
329:      * Set order
330:      *
331:      * @param string $attribute
332:      * @param string $dir
333:      * @return Mage_Reports_Model_Resource_Product_Collection
334:      */
335:     public function setOrder($attribute, $dir = self::SORT_ORDER_DESC)
336:     {
337:         if (in_array($attribute, array('carts', 'orders', 'ordered_qty'))) {
338:             $this->getSelect()->order($attribute . ' ' . $dir);
339:         } else {
340:             parent::setOrder($attribute, $dir);
341:         }
342: 
343:         return $this;
344:     }
345: 
346:     /**
347:      * Add views count
348:      *
349:      * @param string $from
350:      * @param string $to
351:      * @return Mage_Reports_Model_Resource_Product_Collection
352:      */
353:     public function addViewsCount($from = '', $to = '')
354:     {
355:         /**
356:          * Getting event type id for catalog_product_view event
357:          */
358:         foreach (Mage::getModel('reports/event_type')->getCollection() as $eventType) {
359:             if ($eventType->getEventName() == 'catalog_product_view') {
360:                 $productViewEvent = (int)$eventType->getId();
361:                 break;
362:             }
363:         }
364: 
365:         $this->getSelect()->reset()
366:             ->from(
367:                 array('report_table_views' => $this->getTable('reports/event')),
368:                 array('views' => 'COUNT(report_table_views.event_id)'))
369:             ->join(array('e' => $this->getProductEntityTableName()),
370:                 $this->getConnection()->quoteInto(
371:                     "e.entity_id = report_table_views.object_id AND e.entity_type_id = ?",
372:                     $this->getProductEntityTypeId()))
373:             ->where('report_table_views.event_type_id = ?', $productViewEvent)
374:             ->group('e.entity_id')
375:             ->order('views ' . self::SORT_ORDER_DESC)
376:             ->having('COUNT(report_table_views.event_id) > ?', 0);
377: 
378:         if ($from != '' && $to != '') {
379:             $this->getSelect()
380:                 ->where('logged_at >= ?', $from)
381:                 ->where('logged_at <= ?', $to);
382:         }
383: 
384:         $this->_useAnalyticFunction = true;
385:         return $this;
386:     }
387: 
388:     /**
389:      * Prepare between sql
390:      *
391:      * @param  string $fieldName Field name with table suffix ('created_at' or 'main_table.created_at')
392:      * @param  string $from
393:      * @param  string $to
394:      * @return string Formatted sql string
395:      */
396:     protected function _prepareBetweenSql($fieldName, $from, $to)
397:     {
398:         return sprintf('(%s BETWEEN %s AND %s)',
399:             $fieldName,
400:             $this->getConnection()->quote($from),
401:             $this->getConnection()->quote($to)
402:         );
403:     }
404: 
405:     /**
406:      * Add store restrictions to product collection
407:      *
408:      * @param  array $storeIds
409:      * @param  array $websiteIds
410:      * @return Mage_Reports_Model_Resource_Product_Collection
411:      */
412:     public function addStoreRestrictions($storeIds, $websiteIds)
413:     {
414:         if (!is_array($storeIds)) {
415:             $storeIds = array($storeIds);
416:         }
417:         if (!is_array($websiteIds)) {
418:             $websiteIds = array($websiteIds);
419:         }
420: 
421:         $filters = $this->_productLimitationFilters;
422:         if (isset($filters['store_id'])) {
423:             if (!in_array($filters['store_id'], $storeIds)) {
424:                 $this->addStoreFilter($filters['store_id']);
425:             } else {
426:                 $this->addStoreFilter($this->getStoreId());
427:             }
428:         } else {
429:             $this->addWebsiteFilter($websiteIds);
430:         }
431: 
432:         return $this;
433:     }
434: }
435: