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:  * Most viewed product report aggregate resource model
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Reports
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Reports_Model_Resource_Report_Product_Viewed extends Mage_Sales_Model_Resource_Report_Abstract
 36: {
 37:     const AGGREGATION_DAILY   = 'reports/viewed_aggregated_daily';
 38:     const AGGREGATION_MONTHLY = 'reports/viewed_aggregated_monthly';
 39:     const AGGREGATION_YEARLY  = 'reports/viewed_aggregated_yearly';
 40: 
 41:     /**
 42:      * Model initialization
 43:      *
 44:      */
 45:     protected function _construct()
 46:     {
 47:         $this->_init(self::AGGREGATION_DAILY, 'id');
 48:     }
 49: 
 50:     /**
 51:      * Aggregate products view data
 52:      *
 53:      * @param mixed $from
 54:      * @param mixed $to
 55:      * @return Mage_Sales_Model_Resource_Report_Bestsellers
 56:      */
 57:     public function aggregate($from = null, $to = null)
 58:     {
 59:         $mainTable   = $this->getMainTable();
 60:         $adapter = $this->_getWriteAdapter();
 61: 
 62:         // convert input dates to UTC to be comparable with DATETIME fields in DB
 63:         $from = $this->_dateToUtc($from);
 64:         $to = $this->_dateToUtc($to);
 65: 
 66:         $this->_checkDates($from, $to);
 67: 
 68:         if ($from !== null || $to !== null) {
 69:             $subSelect = $this->_getTableDateRangeSelect(
 70:                 $this->getTable('reports/event'),
 71:                 'logged_at', 'logged_at', $from, $to
 72:             );
 73:         } else {
 74:             $subSelect = null;
 75:         }
 76:         $this->_clearTableByDateRange($mainTable, $from, $to, $subSelect);
 77:         // convert dates from UTC to current admin timezone
 78:         $periodExpr = $adapter->getDatePartSql(
 79:             $this->getStoreTZOffsetQuery(
 80:                 array('source_table' => $this->getTable('reports/event')),
 81:                 'source_table.logged_at', $from, $to
 82:             )
 83:         );
 84: 
 85:         $helper = Mage::getResourceHelper('core');
 86:         $select = $adapter->select();
 87: 
 88:         $select->group(array(
 89:             $periodExpr,
 90:             'source_table.store_id',
 91:             'source_table.object_id'
 92:         ));
 93: 
 94:         $viewsNumExpr = new Zend_Db_Expr('COUNT(source_table.event_id)');
 95: 
 96:         $columns = array(
 97:             'period'                 => $periodExpr,
 98:             'store_id'               => 'source_table.store_id',
 99:             'product_id'             => 'source_table.object_id',
100:             'product_name'           => new Zend_Db_Expr(
101:                 sprintf('MIN(%s)',
102:                     $adapter->getIfNullSql('product_name.value','product_default_name.value')
103:                 )
104:             ),
105:             'product_price'          => new Zend_Db_Expr(
106:                 sprintf('%s',
107:                     $helper->prepareColumn(
108:                         sprintf('MIN(%s)',
109:                             $adapter->getIfNullSql(
110:                                 $adapter->getIfNullSql('product_price.value','product_default_price.value'), 0)
111:                         ),
112:                         $select->getPart(Zend_Db_Select::GROUP)
113:                     )
114:                 )
115:             ),
116:             'views_num'            => $viewsNumExpr
117:         );
118: 
119:         $select
120:             ->from(
121:                 array(
122:                     'source_table' => $this->getTable('reports/event')),
123:                 $columns)
124:             ->where('source_table.event_type_id = ?', Mage_Reports_Model_Event::EVENT_PRODUCT_VIEW);
125: 
126:         /** @var Mage_Catalog_Model_Resource_Product $product */
127:         $product  = Mage::getResourceSingleton('catalog/product');
128: 
129:         $select->joinInner(
130:             array(
131:                 'product' => $this->getTable('catalog/product')),
132:             'product.entity_id = source_table.object_id',
133:             array()
134:         );
135: 
136:         // join product attributes Name & Price
137:         $nameAttribute = $product->getAttribute('name');
138:         $joinExprProductName       = array(
139:             'product_name.entity_id = product.entity_id',
140:             'product_name.store_id = source_table.store_id',
141:             $adapter->quoteInto('product_name.attribute_id = ?', $nameAttribute->getAttributeId())
142:         );
143:         $joinExprProductName        = implode(' AND ', $joinExprProductName);
144:         $joinExprProductDefaultName = array(
145:             'product_default_name.entity_id = product.entity_id',
146:             'product_default_name.store_id = 0',
147:             $adapter->quoteInto('product_default_name.attribute_id = ?', $nameAttribute->getAttributeId())
148:         );
149:         $joinExprProductDefaultName = implode(' AND ', $joinExprProductDefaultName);
150:         $select->joinLeft(
151:             array(
152:                 'product_name' => $nameAttribute->getBackend()->getTable()),
153:             $joinExprProductName,
154:             array()
155:         )
156:         ->joinLeft(
157:             array(
158:                 'product_default_name' => $nameAttribute->getBackend()->getTable()),
159:             $joinExprProductDefaultName,
160:             array()
161:         );
162:         $priceAttribute                    = $product->getAttribute('price');
163:         $joinExprProductPrice    = array(
164:             'product_price.entity_id = product.entity_id',
165:             'product_price.store_id = source_table.store_id',
166:             $adapter->quoteInto('product_price.attribute_id = ?', $priceAttribute->getAttributeId())
167:         );
168:         $joinExprProductPrice    = implode(' AND ', $joinExprProductPrice);
169: 
170:         $joinExprProductDefPrice = array(
171:             'product_default_price.entity_id = product.entity_id',
172:             'product_default_price.store_id = 0',
173:             $adapter->quoteInto('product_default_price.attribute_id = ?', $priceAttribute->getAttributeId())
174:         );
175:         $joinExprProductDefPrice = implode(' AND ', $joinExprProductDefPrice);
176:         $select->joinLeft(
177:             array('product_price' => $priceAttribute->getBackend()->getTable()),
178:             $joinExprProductPrice,
179:             array()
180:         )
181:         ->joinLeft(
182:             array('product_default_price' => $priceAttribute->getBackend()->getTable()),
183:             $joinExprProductDefPrice,
184:             array()
185:         );
186: 
187:         $havingPart = array($adapter->prepareSqlCondition($viewsNumExpr, array('gt' => 0)));
188:         if (!is_null($subSelect)) {
189:             $subSelectHavingPart = $this->_makeConditionFromDateRangeSelect($subSelect, 'period');
190:             if ($subSelectHavingPart) {
191:                 $havingPart[] = '(' . $subSelectHavingPart . ')';
192:             }
193:         }
194:         $select->having(implode(' AND ', $havingPart));
195: 
196:         $select->useStraightJoin();
197:         $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $this->getMainTable(),
198:             array_keys($columns));
199:         $adapter->query($insertQuery);
200: 
201:         Mage::getResourceHelper('reports')
202:             ->updateReportRatingPos('day', 'views_num', $mainTable, $this->getTable(self::AGGREGATION_DAILY));
203:         Mage::getResourceHelper('reports')
204:             ->updateReportRatingPos('month', 'views_num', $mainTable, $this->getTable(self::AGGREGATION_MONTHLY));
205:         Mage::getResourceHelper('reports')
206:             ->updateReportRatingPos('year', 'views_num', $mainTable, $this->getTable(self::AGGREGATION_YEARLY));
207: 
208:         $this->_setFlagData(Mage_Reports_Model_Flag::REPORT_PRODUCT_VIEWED_FLAG_CODE);
209: 
210:         return $this;
211:     }
212: }
213: