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:  * Bestsellers report resource model
 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 extends Mage_Sales_Model_Resource_Report_Abstract
 36: {
 37:     const AGGREGATION_DAILY   = 'daily';
 38:     const AGGREGATION_MONTHLY = 'monthly';
 39:     const AGGREGATION_YEARLY  = 'yearly';
 40: 
 41:     /**
 42:      * Model initialization
 43:      *
 44:      */
 45:     protected function _construct()
 46:     {
 47:         $this->_init('sales/bestsellers_aggregated_' . self::AGGREGATION_DAILY, 'id');
 48:     }
 49: 
 50:     /**
 51:      * Aggregate Orders data by order created at
 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:         // convert input dates to UTC to be comparable with DATETIME fields in DB
 60:         $from    = $this->_dateToUtc($from);
 61:         $to      = $this->_dateToUtc($to);
 62: 
 63:         $this->_checkDates($from, $to);
 64:         $adapter = $this->_getWriteAdapter();
 65:         //$this->_getWriteAdapter()->beginTransaction();
 66: 
 67:         try {
 68:             if ($from !== null || $to !== null) {
 69:                 $subSelect = $this->_getTableDateRangeSelect(
 70:                     $this->getTable('sales/order'),
 71:                     'created_at', 'updated_at', $from, $to
 72:                 );
 73:             } else {
 74:                 $subSelect = null;
 75:             }
 76: 
 77:             $this->_clearTableByDateRange($this->getMainTable(), $from, $to, $subSelect);
 78:             // convert dates from UTC to current admin timezone
 79:             $periodExpr = $adapter->getDatePartSql(
 80:                 $this->getStoreTZOffsetQuery(
 81:                     array('source_table' => $this->getTable('sales/order')),
 82:                     'source_table.created_at', $from, $to
 83:                 )
 84:             );
 85: 
 86:             $helper                        = Mage::getResourceHelper('core');
 87:             $select = $adapter->select();
 88: 
 89:             $select->group(array(
 90:                 $periodExpr,
 91:                 'source_table.store_id',
 92:                 'order_item.product_id'
 93:             ));
 94: 
 95:             $columns = array(
 96:                 'period'                 => $periodExpr,
 97:                 'store_id'               => 'source_table.store_id',
 98:                 'product_id'             => 'order_item.product_id',
 99:                 'product_name'           => new Zend_Db_Expr(
100:                     sprintf('MIN(%s)',
101:                         $adapter->getIfNullSql('product_name.value','product_default_name.value')
102:                     )
103:                 ),
104:                 'product_price'          => new Zend_Db_Expr(
105:                         sprintf('%s * %s',
106:                             $helper->prepareColumn(
107:                                 sprintf('MIN(%s)',
108:                                     $adapter->getIfNullSql(
109:                                         $adapter->getIfNullSql('product_price.value','product_default_price.value'),0)
110:                                 ),
111:                                 $select->getPart(Zend_Db_Select::GROUP)
112:                             ),
113:                             $helper->prepareColumn(
114:                                 sprintf('MIN(%s)',
115:                                     $adapter->getIfNullSql('source_table.base_to_global_rate', '0')
116:                                 ),
117:                                 $select->getPart(Zend_Db_Select::GROUP)
118:                         )
119:                     )
120:                 ),
121:                 'qty_ordered'            => new Zend_Db_Expr('SUM(order_item.qty_ordered)')
122:             );
123: 
124:             $select
125:                 ->from(
126:                     array(
127:                         'source_table' => $this->getTable('sales/order')),
128:                     $columns)
129:                 ->joinInner(
130:                     array(
131:                         'order_item' => $this->getTable('sales/order_item')),
132:                     'order_item.order_id = source_table.entity_id',
133:                     array()
134:                 )
135:                 ->where('source_table.state != ?', Mage_Sales_Model_Order::STATE_CANCELED);
136: 
137: 
138:             /** @var Mage_Catalog_Model_Resource_Product $product */
139:             $product  = Mage::getResourceSingleton('catalog/product');
140: 
141:             $productTypes = array(
142:                 Mage_Catalog_Model_Product_Type::TYPE_GROUPED,
143:                 Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE,
144:                 Mage_Catalog_Model_Product_Type::TYPE_BUNDLE,
145:             );
146: 
147:             $joinExpr = array(
148:                 'product.entity_id = order_item.product_id',
149:                 $adapter->quoteInto('product.entity_type_id = ?', $product->getTypeId()),
150:                 $adapter->quoteInto('product.type_id NOT IN(?)', $productTypes)
151:             );
152: 
153:             $joinExpr = implode(' AND ', $joinExpr);
154:             $select->joinInner(
155:                 array(
156:                     'product' => $this->getTable('catalog/product')),
157:                 $joinExpr,
158:                 array()
159:             );
160: 
161:             // join product attributes Name & Price
162:             $attr     = $product->getAttribute('name');
163:             $joinExprProductName       = array(
164:                 'product_name.entity_id = product.entity_id',
165:                 'product_name.store_id = source_table.store_id',
166:                 $adapter->quoteInto('product_name.entity_type_id = ?', $product->getTypeId()),
167:                 $adapter->quoteInto('product_name.attribute_id = ?', $attr->getAttributeId())
168:             );
169:             $joinExprProductName        = implode(' AND ', $joinExprProductName);
170:             $joinExprProductDefaultName = array(
171:                 'product_default_name.entity_id = product.entity_id',
172:                 'product_default_name.store_id = 0',
173:                 $adapter->quoteInto('product_default_name.entity_type_id = ?', $product->getTypeId()),
174:                 $adapter->quoteInto('product_default_name.attribute_id = ?', $attr->getAttributeId())
175:             );
176:             $joinExprProductDefaultName = implode(' AND ', $joinExprProductDefaultName);
177:             $select->joinLeft(
178:                 array(
179:                     'product_name' => $attr->getBackend()->getTable()),
180:                 $joinExprProductName,
181:                 array()
182:             )
183:             ->joinLeft(
184:                 array(
185:                     'product_default_name' => $attr->getBackend()->getTable()),
186:                 $joinExprProductDefaultName,
187:                 array()
188:             );
189:             $attr                    = $product->getAttribute('price');
190:             $joinExprProductPrice    = array(
191:                 'product_price.entity_id = product.entity_id',
192:                 'product_price.store_id = source_table.store_id',
193:                 $adapter->quoteInto('product_price.entity_type_id = ?', $product->getTypeId()),
194:                 $adapter->quoteInto('product_price.attribute_id = ?', $attr->getAttributeId())
195:             );
196:             $joinExprProductPrice    = implode(' AND ', $joinExprProductPrice);
197: 
198:             $joinExprProductDefPrice = array(
199:                 'product_default_price.entity_id = product.entity_id',
200:                 'product_default_price.store_id = 0',
201:                 $adapter->quoteInto('product_default_price.entity_type_id = ?', $product->getTypeId()),
202:                 $adapter->quoteInto('product_default_price.attribute_id = ?', $attr->getAttributeId())
203:             );
204:             $joinExprProductDefPrice = implode(' AND ', $joinExprProductDefPrice);
205:             $select->joinLeft(
206:                 array('product_price' => $attr->getBackend()->getTable()),
207:                 $joinExprProductPrice,
208:                 array()
209:             )
210:             ->joinLeft(
211:                 array('product_default_price' => $attr->getBackend()->getTable()),
212:                 $joinExprProductDefPrice,
213:                 array()
214:             );
215: 
216:             if ($subSelect !== null) {
217:                 $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
218:             }
219: 
220: 
221:             $select->useStraightJoin();  // important!
222:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $this->getMainTable(),
223:                 array_keys($columns));
224:             $adapter->query($insertQuery);
225: 
226: 
227:             $columns = array(
228:                 'period'                         => 'period',
229:                 'store_id'                       => new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID),
230:                 'product_id'                     => 'product_id',
231:                 'product_name'                   => new Zend_Db_Expr('MIN(product_name)'),
232:                 'product_price'                  => new Zend_Db_Expr('MIN(product_price)'),
233:                 'qty_ordered'                    => new Zend_Db_Expr('SUM(qty_ordered)'),
234:             );
235: 
236:             $select->reset();
237:             $select->from($this->getMainTable(), $columns)
238:                 ->where('store_id <> ?', 0);
239: 
240:             if ($subSelect !== null) {
241:                 $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
242:             }
243: 
244:             $select->group(array(
245:                 'period',
246:                 'product_id'
247:             ));
248: 
249:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $this->getMainTable(),
250:                 array_keys($columns));
251:             $adapter->query($insertQuery);
252: 
253:             // update rating
254:             $this->_updateRatingPos(self::AGGREGATION_DAILY);
255:             $this->_updateRatingPos(self::AGGREGATION_MONTHLY);
256:             $this->_updateRatingPos(self::AGGREGATION_YEARLY);
257: 
258: 
259:             $this->_setFlagData(Mage_Reports_Model_Flag::REPORT_BESTSELLERS_FLAG_CODE);
260:         } catch (Exception $e) {
261:             //$this->_getWriteAdapter()->rollBack();
262:             throw $e;
263:         }
264: 
265:         //$this->_getWriteAdapter()->commit();
266:         return $this;
267:     }
268: 
269:     /**
270:      * Update rating position
271:      *
272:      * @param string $aggregation One of Mage_Sales_Model_Resource_Report_Bestsellers::AGGREGATION_XXX constants
273:      * @return Mage_Sales_Model_Resource_Report_Bestsellers
274:      */
275:     protected function _updateRatingPos($aggregation)
276:     {
277:         $aggregationTable   = $this->getTable('sales/bestsellers_aggregated_' . $aggregation);
278: 
279:         $aggregationAliases = array(
280:             'daily'   => self::AGGREGATION_DAILY,
281:             'monthly' => self::AGGREGATION_MONTHLY,
282:             'yearly'  => self::AGGREGATION_YEARLY
283:         );
284:         Mage::getResourceHelper('sales')
285:             ->getBestsellersReportUpdateRatingPos($aggregation, $aggregationAliases,
286:                 $this->getMainTable(), $aggregationTable);
287: 
288:         return $this;
289:     }
290: }
291: