1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25:
26:
27:
28: 29: 30: 31: 32: 33: 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: 41: 42: 43:
44: protected $_productEntityId;
45:
46: 47: 48: 49: 50:
51: protected $_productEntityTableName;
52:
53: 54: 55: 56: 57:
58: protected $_productEntityTypeId;
59:
60: 61: 62: 63: 64:
65: protected $_selectCountSqlType = 0;
66:
67: 68: 69: 70:
71: public function __construct()
72: {
73: $product = Mage::getResourceSingleton('catalog/product');
74:
75: $this->setProductEntityId($product->getEntityIdField());
76: $this->setProductEntityTableName($product->getEntityTable());
77: $this->setProductEntityTypeId($product->getTypeId());
78:
79: parent::__construct();
80: }
81: 82: 83: 84: 85: 86:
87: public function setSelectCountSqlType($type)
88: {
89: $this->_selectCountSqlType = $type;
90: return $this;
91: }
92:
93: 94: 95: 96: 97: 98:
99: public function setProductEntityId($entityId)
100: {
101: $this->_productEntityId = (int)$entityId;
102: return $this;
103: }
104:
105: 106: 107: 108: 109:
110: public function getProductEntityId()
111: {
112: return $this->_productEntityId;
113: }
114:
115: 116: 117: 118: 119: 120:
121: public function setProductEntityTableName($value)
122: {
123: $this->_productEntityTableName = $value;
124: return $this;
125: }
126:
127: 128: 129: 130: 131:
132: public function getProductEntityTableName()
133: {
134: return $this->_productEntityTableName;
135: }
136:
137: 138: 139: 140: 141: 142:
143: public function setProductEntityTypeId($value)
144: {
145: $this->_productEntityTypeId = $value;
146: return $this;
147: }
148:
149: 150: 151: 152: 153:
154: public function getProductEntityTypeId()
155: {
156: return $this->_productEntityTypeId;
157: }
158:
159: 160: 161: 162: 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: 177: 178: 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: 210: 211: 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: 234: 235: 236: 237: 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: 269: 270: 271: 272: 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: 330: 331: 332: 333: 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: 348: 349: 350: 351: 352:
353: public function addViewsCount($from = '', $to = '')
354: {
355: 356: 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: 390: 391: 392: 393: 394: 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: 407: 408: 409: 410: 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: