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:  * Reports orders collection
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Reports
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Reports_Model_Resource_Order_Collection extends Mage_Sales_Model_Resource_Order_Collection
 36: {
 37:     /**
 38:      * Is live
 39:      *
 40:      * @var boolean
 41:      */
 42:     protected $_isLive   = false;
 43: 
 44:     /**
 45:      * Check range for live mode
 46:      *
 47:      * @param unknown_type $range
 48:      * @return Mage_Reports_Model_Resource_Order_Collection
 49:      */
 50:     public function checkIsLive($range)
 51:     {
 52:         $this->_isLive = (bool)!Mage::getStoreConfig('sales/dashboard/use_aggregated_data');
 53:         return $this;
 54:     }
 55: 
 56:     /**
 57:      * Retrieve is live flag for rep
 58:      *
 59:      * @return boolean
 60:      */
 61:     public function isLive()
 62:     {
 63:         return $this->_isLive;
 64:     }
 65: 
 66:     /**
 67:      * Prepare report summary
 68:      *
 69:      * @param string $range
 70:      * @param mixed $customStart
 71:      * @param mixed $customEnd
 72:      * @param int $isFilter
 73:      * @return Mage_Reports_Model_Resource_Order_Collection
 74:      */
 75:     public function prepareSummary($range, $customStart, $customEnd, $isFilter = 0)
 76:     {
 77:         $this->checkIsLive($range);
 78:         if ($this->_isLive) {
 79:             $this->_prepareSummaryLive($range, $customStart, $customEnd, $isFilter);
 80:         } else {
 81:             $this->_prepareSummaryAggregated($range, $customStart, $customEnd, $isFilter);
 82:         }
 83: 
 84:         return $this;
 85:     }
 86: 
 87:     /**
 88:      * Prepare report summary from live data
 89:      *
 90:      * @param string $range
 91:      * @param mixed $customStart
 92:      * @param mixed $customEnd
 93:      * @param int $isFilter
 94:      * @return Mage_Reports_Model_Resource_Order_Collection
 95:      */
 96:     protected function _prepareSummaryLive($range, $customStart, $customEnd, $isFilter = 0)
 97:     {
 98:         $this->setMainTable('sales/order');
 99:         $adapter = $this->getConnection();
100: 
101:         /**
102:          * Reset all columns, because result will group only by 'created_at' field
103:          */
104:         $this->getSelect()->reset(Zend_Db_Select::COLUMNS);
105: 
106:         $expression = sprintf('%s - %s - %s - (%s - %s - %s)',
107:             $adapter->getIfNullSql('main_table.base_total_invoiced', 0),
108:             $adapter->getIfNullSql('main_table.base_tax_invoiced', 0),
109:             $adapter->getIfNullSql('main_table.base_shipping_invoiced', 0),
110:             $adapter->getIfNullSql('main_table.base_total_refunded', 0),
111:             $adapter->getIfNullSql('main_table.base_tax_refunded', 0),
112:             $adapter->getIfNullSql('main_table.base_shipping_refunded', 0)
113:         );
114:         if ($isFilter == 0) {
115:             $this->getSelect()->columns(array(
116:                 'revenue' => new Zend_Db_Expr(
117:                     sprintf('SUM((%s) * %s)', $expression,
118:                         $adapter->getIfNullSql('main_table.base_to_global_rate', 0)
119:                     )
120:                  )
121:             ));
122:         } else {
123:             $this->getSelect()->columns(array(
124:                 'revenue' => new Zend_Db_Expr(sprintf('SUM(%s)', $expression))
125:             ));
126:         }
127: 
128:         $dateRange = $this->getDateRange($range, $customStart, $customEnd);
129: 
130:         $tzRangeOffsetExpression = $this->_getTZRangeOffsetExpression(
131:             $range, 'created_at', $dateRange['from'], $dateRange['to']
132:         );
133: 
134:         $this->getSelect()
135:             ->columns(array(
136:                 'quantity' => 'COUNT(main_table.entity_id)',
137:                 'range' => $tzRangeOffsetExpression,
138:             ))
139:             ->where('main_table.state NOT IN (?)', array(
140:                 Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
141:                 Mage_Sales_Model_Order::STATE_NEW)
142:             )
143:             ->order('range', Zend_Db_Select::SQL_ASC)
144:             ->group($tzRangeOffsetExpression);
145: 
146:         $this->addFieldToFilter('created_at', $dateRange);
147: 
148:         return $this;
149:     }
150: 
151:     /**
152:      * Prepare report summary from aggregated data
153:      *
154:      * @param string $range
155:      * @param mixed $customStart
156:      * @param mixed $customEnd
157:      * @return Mage_Reports_Model_Resource_Order_Collection
158:      */
159:     protected function _prepareSummaryAggregated($range, $customStart, $customEnd)
160:     {
161:         $this->setMainTable('sales/order_aggregated_created');
162:         /**
163:          * Reset all columns, because result will group only by 'created_at' field
164:          */
165:         $this->getSelect()->reset(Zend_Db_Select::COLUMNS);
166:         $rangePeriod = $this->_getRangeExpressionForAttribute($range, 'main_table.period');
167: 
168:         $tableName = $this->getConnection()->quoteIdentifier('main_table.period');
169:         $rangePeriod2 = str_replace($tableName, "MIN($tableName)", $rangePeriod);
170: 
171:         $this->getSelect()->columns(array(
172:             'revenue'  => 'SUM(main_table.total_revenue_amount)',
173:             'quantity' => 'SUM(main_table.orders_count)',
174:             'range' => $rangePeriod2,
175:         ))
176:         ->order('range')
177:         ->group($rangePeriod);
178: 
179:         $this->getSelect()->where(
180:             $this->_getConditionSql('main_table.period', $this->getDateRange($range, $customStart, $customEnd))
181:         );
182: 
183:         $statuses = Mage::getSingleton('sales/config')
184:             ->getOrderStatusesForState(Mage_Sales_Model_Order::STATE_CANCELED);
185: 
186:         if (empty($statuses)) {
187:             $statuses = array(0);
188:         }
189:         $this->addFieldToFilter('main_table.order_status', array('nin' => $statuses));
190: 
191:         return $this;
192:     }
193: 
194:     /**
195:      * Get range expression
196:      *
197:      * @param string $range
198:      * @return Zend_Db_Expr
199:      */
200:     protected function _getRangeExpression($range)
201:     {
202:         switch ($range)
203:         {
204:             case '24h':
205:                 $expression = $this->getConnection()->getConcatSql(array(
206:                     $this->getConnection()->getDateFormatSql('{{attribute}}', '%Y-%m-%d %H:'),
207:                     $this->getConnection()->quote('00')
208:                 ));
209:                 break;
210:             case '7d':
211:             case '1m':
212:                 $expression = $this->getConnection()->getDateFormatSql('{{attribute}}', '%Y-%m-%d');
213:                 break;
214:             case '1y':
215:             case '2y':
216:             case 'custom':
217:             default:
218:                 $expression = $this->getConnection()->getDateFormatSql('{{attribute}}', '%Y-%m');
219:                 break;
220:         }
221: 
222:         return $expression;
223:     }
224: 
225:     /**
226:      * Retrieve range expression adapted for attribute
227:      *
228:      * @param string $range
229:      * @param string $attribute
230:      * @return string
231:      */
232:     protected function _getRangeExpressionForAttribute($range, $attribute)
233:     {
234:         $expression = $this->_getRangeExpression($range);
235:         return str_replace('{{attribute}}', $this->getConnection()->quoteIdentifier($attribute), $expression);
236:     }
237: 
238:     /**
239:      * Retrieve query for attribute with timezone conversion
240:      *
241:      * @param string $range
242:      * @param string $attribute
243:      * @param mixed $from
244:      * @param mixed $to
245:      * @return string
246:      */
247:     protected function _getTZRangeOffsetExpression($range, $attribute, $from = null, $to = null)
248:     {
249:         return str_replace(
250:             '{{attribute}}',
251:             Mage::getResourceModel('sales/report_order')
252:                     ->getStoreTZOffsetQuery($this->getMainTable(), $attribute, $from, $to),
253:             $this->_getRangeExpression($range)
254:         );
255:     }
256: 
257:     /**
258:      * Retrieve range expression with timezone conversion adapted for attribute
259:      *
260:      * @param string $range
261:      * @param string $attribute
262:      * @param string $tzFrom
263:      * @param string $tzTo
264:      * @return string
265:      */
266:     protected function _getTZRangeExpressionForAttribute($range, $attribute, $tzFrom = '+00:00', $tzTo = null)
267:     {
268:         if (null == $tzTo) {
269:             $tzTo = Mage::app()->getLocale()->storeDate()->toString(Zend_Date::GMT_DIFF_SEP);
270:         }
271:         $adapter = $this->getConnection();
272:         $expression = $this->_getRangeExpression($range);
273:         $attribute  = $adapter->quoteIdentifier($attribute);
274:         $periodExpr = $adapter->getDateAddSql($attribute, $tzTo, Varien_Db_Adapter_Interface::INTERVAL_HOUR);
275: 
276:         return str_replace('{{attribute}}', $periodExpr, $expression);
277:     }
278: 
279:     /**
280:      * Calculate From and To dates (or times) by given period
281:      *
282:      * @param string $range
283:      * @param string $customStart
284:      * @param string $customEnd
285:      * @param boolean $returnObjects
286:      * @return array
287:      */
288:     public function getDateRange($range, $customStart, $customEnd, $returnObjects = false)
289:     {
290:         $dateEnd   = Mage::app()->getLocale()->date();
291:         $dateStart = clone $dateEnd;
292: 
293:         // go to the end of a day
294:         $dateEnd->setHour(23);
295:         $dateEnd->setMinute(59);
296:         $dateEnd->setSecond(59);
297: 
298:         $dateStart->setHour(0);
299:         $dateStart->setMinute(0);
300:         $dateStart->setSecond(0);
301: 
302:         switch ($range)
303:         {
304:             case '24h':
305:                 $dateEnd = Mage::app()->getLocale()->date();
306:                 $dateEnd->addHour(1);
307:                 $dateStart = clone $dateEnd;
308:                 $dateStart->subDay(1);
309:                 break;
310: 
311:             case '7d':
312:                 // substract 6 days we need to include
313:                 // only today and not hte last one from range
314:                 $dateStart->subDay(6);
315:                 break;
316: 
317:             case '1m':
318:                 $dateStart->setDay(Mage::getStoreConfig('reports/dashboard/mtd_start'));
319:                 break;
320: 
321:             case 'custom':
322:                 $dateStart = $customStart ? $customStart : $dateEnd;
323:                 $dateEnd   = $customEnd ? $customEnd : $dateEnd;
324:                 break;
325: 
326:             case '1y':
327:             case '2y':
328:                 $startMonthDay = explode(',', Mage::getStoreConfig('reports/dashboard/ytd_start'));
329:                 $startMonth = isset($startMonthDay[0]) ? (int)$startMonthDay[0] : 1;
330:                 $startDay = isset($startMonthDay[1]) ? (int)$startMonthDay[1] : 1;
331:                 $dateStart->setMonth($startMonth);
332:                 $dateStart->setDay($startDay);
333:                 if ($range == '2y') {
334:                     $dateStart->subYear(1);
335:                 }
336:                 break;
337:         }
338: 
339:         $dateStart->setTimezone('Etc/UTC');
340:         $dateEnd->setTimezone('Etc/UTC');
341: 
342:         if ($returnObjects) {
343:             return array($dateStart, $dateEnd);
344:         } else {
345:             return array('from' => $dateStart, 'to' => $dateEnd, 'datetime' => true);
346:         }
347:     }
348: 
349:     /**
350:      * Add item count expression
351:      *
352:      * @return Mage_Reports_Model_Resource_Order_Collection
353:      */
354:     public function addItemCountExpr()
355:     {
356:         $this->getSelect()->columns(array('items_count' => 'total_item_count'), 'main_table');
357:         return $this;
358:     }
359: 
360:     /**
361:      * Calculate totals report
362:      *
363:      * @param int $isFilter
364:      * @return Mage_Reports_Model_Resource_Order_Collection
365:      */
366:     public function calculateTotals($isFilter = 0)
367:     {
368:         if ($this->isLive()) {
369:             $this->_calculateTotalsLive($isFilter);
370:         } else {
371:             $this->_calculateTotalsAggregated($isFilter);
372:         }
373: 
374:         return $this;
375:     }
376: 
377:     /**
378:      * Calculate totals live report
379:      *
380:      * @param int $isFilter
381:      * @return Mage_Reports_Model_Resource_Order_Collection
382:      */
383:     protected function _calculateTotalsLive($isFilter = 0)
384:     {
385:         $this->setMainTable('sales/order');
386:         $this->removeAllFieldsFromSelect();
387: 
388:         $adapter = $this->getConnection();
389: 
390:         $baseTotalInvoiced    = $adapter->getIfNullSql('main_table.base_total_invoiced', 0);
391:         $baseTotalRefunded    = $adapter->getIfNullSql('main_table.base_total_refunded', 0);
392:         $baseTaxInvoiced      = $adapter->getIfNullSql('main_table.base_tax_invoiced', 0);
393:         $baseTaxRefunded      = $adapter->getIfNullSql('main_table.base_tax_refunded', 0);
394:         $baseShippingInvoiced = $adapter->getIfNullSql('main_table.base_shipping_invoiced', 0);
395:         $baseShippingRefunded = $adapter->getIfNullSql('main_table.base_shipping_refunded', 0);
396: 
397:         $revenueExp = sprintf('%s - %s - %s - (%s - %s - %s)',
398:             $baseTotalInvoiced,
399:             $baseTaxInvoiced,
400:             $baseShippingInvoiced,
401:             $baseTotalRefunded,
402:             $baseTaxRefunded,
403:             $baseShippingRefunded
404:         );
405:         $taxExp = sprintf('%s - %s', $baseTaxInvoiced, $baseTaxRefunded);
406:         $shippingExp = sprintf('%s - %s', $baseShippingInvoiced, $baseShippingRefunded);
407: 
408:         if ($isFilter == 0) {
409:             $rateExp = $adapter->getIfNullSql('main_table.base_to_global_rate', 0);
410:             $this->getSelect()->columns(
411:                 array(
412:                     'revenue'  => new Zend_Db_Expr(sprintf('SUM((%s) * %s)', $revenueExp, $rateExp)),
413:                     'tax'      => new Zend_Db_Expr(sprintf('SUM((%s) * %s)', $taxExp, $rateExp)),
414:                     'shipping' => new Zend_Db_Expr(sprintf('SUM((%s) * %s)', $shippingExp, $rateExp))
415:                 )
416:             );
417:         } else {
418:             $this->getSelect()->columns(
419:                 array(
420:                     'revenue'  => new Zend_Db_Expr(sprintf('SUM(%s)', $revenueExp)),
421:                     'tax'      => new Zend_Db_Expr(sprintf('SUM(%s)', $taxExp)),
422:                     'shipping' => new Zend_Db_Expr(sprintf('SUM(%s)', $shippingExp))
423:                 )
424:             );
425:         }
426: 
427:         $this->getSelect()->columns(array(
428:             'quantity' => 'COUNT(main_table.entity_id)'
429:         ))
430:         ->where('main_table.state NOT IN (?)', array(
431:             Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
432:             Mage_Sales_Model_Order::STATE_NEW)
433:          );
434: 
435:         return $this;
436:     }
437: 
438:     /**
439:      * Calculate totals aggregated report
440:      *
441:      * @param int $isFilter
442:      * @return Mage_Reports_Model_Resource_Order_Collection
443:      */
444:     protected function _calculateTotalsAggregated($isFilter = 0)
445:     {
446:         $this->setMainTable('sales/order_aggregated_created');
447:         $this->removeAllFieldsFromSelect();
448: 
449:         $this->getSelect()->columns(array(
450:             'revenue'  => 'SUM(main_table.total_revenue_amount)',
451:             'tax'      => 'SUM(main_table.total_tax_amount_actual)',
452:             'shipping' => 'SUM(main_table.total_shipping_amount_actual)',
453:             'quantity' => 'SUM(orders_count)',
454:         ));
455: 
456:         $statuses = Mage::getSingleton('sales/config')
457:             ->getOrderStatusesForState(Mage_Sales_Model_Order::STATE_CANCELED);
458: 
459:         if (empty($statuses)) {
460:             $statuses = array(0);
461:         }
462: 
463:         $this->getSelect()->where('main_table.order_status NOT IN(?)', $statuses);
464: 
465:         return $this;
466:     }
467: 
468:     /**
469:      * Calculate lifitime sales
470:      *
471:      * @param int $isFilter
472:      * @return Mage_Reports_Model_Resource_Order_Collection
473:      */
474:     public function calculateSales($isFilter = 0)
475:     {
476:         $statuses = Mage::getSingleton('sales/config')
477:             ->getOrderStatusesForState(Mage_Sales_Model_Order::STATE_CANCELED);
478: 
479:         if (empty($statuses)) {
480:             $statuses = array(0);
481:         }
482:         $adapter = $this->getConnection();
483: 
484:         if (Mage::getStoreConfig('sales/dashboard/use_aggregated_data')) {
485:             $this->setMainTable('sales/order_aggregated_created');
486:             $this->removeAllFieldsFromSelect();
487:             $averageExpr = $adapter->getCheckSql(
488:                 'SUM(main_table.orders_count) > 0',
489:                 'SUM(main_table.total_revenue_amount)/SUM(main_table.orders_count)',
490:                 0);
491:             $this->getSelect()->columns(array(
492:                 'lifetime' => 'SUM(main_table.total_revenue_amount)',
493:                 'average'  => $averageExpr
494:             ));
495: 
496:             if (!$isFilter) {
497:                 $this->addFieldToFilter('store_id',
498:                     array('eq' => Mage::app()->getStore(Mage_Core_Model_Store::ADMIN_CODE)->getId())
499:                 );
500:             }
501:             $this->getSelect()->where('main_table.order_status NOT IN(?)', $statuses);
502:         } else {
503:             $this->setMainTable('sales/order');
504:             $this->removeAllFieldsFromSelect();
505: 
506:             $expr = sprintf('%s - %s - %s - (%s - %s - %s)',
507:                 $adapter->getIfNullSql('main_table.base_total_invoiced', 0),
508:                 $adapter->getIfNullSql('main_table.base_tax_invoiced', 0),
509:                 $adapter->getIfNullSql('main_table.base_shipping_invoiced', 0),
510:                 $adapter->getIfNullSql('main_table.base_total_refunded', 0),
511:                 $adapter->getIfNullSql('main_table.base_tax_refunded', 0),
512:                 $adapter->getIfNullSql('main_table.base_shipping_refunded', 0)
513:             );
514: 
515:             if ($isFilter == 0) {
516:                 $expr = '(' . $expr . ') * main_table.base_to_global_rate';
517:             }
518: 
519:             $this->getSelect()
520:                 ->columns(array(
521:                     'lifetime' => "SUM({$expr})",
522:                     'average'  => "AVG({$expr})"
523:                 ))
524:                 ->where('main_table.status NOT IN(?)', $statuses)
525:                 ->where('main_table.state NOT IN(?)', array(
526:                     Mage_Sales_Model_Order::STATE_NEW,
527:                     Mage_Sales_Model_Order::STATE_PENDING_PAYMENT)
528:                 );
529:         }
530:         return $this;
531:     }
532: 
533:     /**
534:      * Set date range
535:      *
536:      * @param string $from
537:      * @param string $to
538:      * @return Mage_Reports_Model_Resource_Order_Collection
539:      */
540:     public function setDateRange($from, $to)
541:     {
542:         $this->_reset()
543:             ->addFieldToFilter('created_at', array('from' => $from, 'to' => $to))
544:             ->addFieldToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
545:             ->getSelect()
546:                 ->columns(array('orders' => 'COUNT(DISTINCT(main_table.entity_id))'))
547:                 ->group('entity_id');
548: 
549:         $this->getSelect()->columns(array(
550:             'items' => 'SUM(main_table.total_qty_ordered)')
551:         );
552: 
553:         return $this;
554:     }
555: 
556:     /**
557:      * Set store filter collection
558:      *
559:      * @param array $storeIds
560:      * @return Mage_Reports_Model_Resource_Order_Collection
561:      */
562:     public function setStoreIds($storeIds)
563:     {
564:         $adapter = $this->getConnection();
565:         $baseSubtotalInvoiced = $adapter->getIfNullSql('main_table.base_subtotal_invoiced', 0);
566:         $baseDiscountRefunded = $adapter->getIfNullSql('main_table.base_discount_refunded', 0);
567:         $baseSubtotalRefunded = $adapter->getIfNullSql('main_table.base_subtotal_refunded', 0);
568:         $baseDiscountInvoiced = $adapter->getIfNullSql('main_table.base_discount_invoiced', 0);
569:         $baseTotalInvocedCost = $adapter->getIfNullSql('main_table.base_total_invoiced_cost', 0);
570:         if ($storeIds) {
571:             $this->getSelect()->columns(array(
572:                 'subtotal'  => 'SUM(main_table.base_subtotal)',
573:                 'tax'       => 'SUM(main_table.base_tax_amount)',
574:                 'shipping'  => 'SUM(main_table.base_shipping_amount)',
575:                 'discount'  => 'SUM(main_table.base_discount_amount)',
576:                 'total'     => 'SUM(main_table.base_grand_total)',
577:                 'invoiced'  => 'SUM(main_table.base_total_paid)',
578:                 'refunded'  => 'SUM(main_table.base_total_refunded)',
579:                 'profit'    => "SUM($baseSubtotalInvoiced) "
580:                                 . "+ SUM({$baseDiscountRefunded}) - SUM({$baseSubtotalRefunded}) "
581:                                 . "- SUM({$baseDiscountInvoiced}) - SUM({$baseTotalInvocedCost})"
582:             ));
583:         } else {
584:             $this->getSelect()->columns(array(
585:                 'subtotal'  => 'SUM(main_table.base_subtotal * main_table.base_to_global_rate)',
586:                 'tax'       => 'SUM(main_table.base_tax_amount * main_table.base_to_global_rate)',
587:                 'shipping'  => 'SUM(main_table.base_shipping_amount * main_table.base_to_global_rate)',
588:                 'discount'  => 'SUM(main_table.base_discount_amount * main_table.base_to_global_rate)',
589:                 'total'     => 'SUM(main_table.base_grand_total * main_table.base_to_global_rate)',
590:                 'invoiced'  => 'SUM(main_table.base_total_paid * main_table.base_to_global_rate)',
591:                 'refunded'  => 'SUM(main_table.base_total_refunded * main_table.base_to_global_rate)',
592:                 'profit'    => "SUM({$baseSubtotalInvoiced} *  main_table.base_to_global_rate) "
593:                                 . "+ SUM({$baseDiscountRefunded} * main_table.base_to_global_rate) "
594:                                 . "- SUM({$baseSubtotalRefunded} * main_table.base_to_global_rate) "
595:                                 . "- SUM({$baseDiscountInvoiced} * main_table.base_to_global_rate) "
596:                                 . "- SUM({$baseTotalInvocedCost} * main_table.base_to_global_rate)"
597:             ));
598:         }
599: 
600:         return $this;
601:     }
602: 
603:     /**
604:      * Add group By customer attribute
605:      *
606:      * @return Mage_Reports_Model_Resource_Order_Collection
607:      */
608:     public function groupByCustomer()
609:     {
610:         $this->getSelect()
611:             ->where('main_table.customer_id IS NOT NULL')
612:             ->group('main_table.customer_id');
613: 
614:         /*
615:          * Allow Analytic functions usage
616:          */
617:         $this->_useAnalyticFunction = true;
618: 
619:         return $this;
620:     }
621: 
622:     /**
623:      * Join Customer Name (concat)
624:      *
625:      * @param string $alias
626:      * @return Mage_Reports_Model_Resource_Order_Collection
627:      */
628:     public function joinCustomerName($alias = 'name')
629:     {
630:         $fields      = array('main_table.customer_firstname', 'main_table.customer_lastname');
631:         $fieldConcat = $this->getConnection()->getConcatSql($fields, ' ');
632:         $this->getSelect()->columns(array($alias => $fieldConcat));
633:         return $this;
634:     }
635: 
636:     /**
637:      * Add Order count field to select
638:      *
639:      * @return Mage_Reports_Model_Resource_Order_Collection
640:      */
641:     public function addOrdersCount()
642:     {
643:         $this->addFieldToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED));
644:         $this->getSelect()
645:             ->columns(array('orders_count' => 'COUNT(main_table.entity_id)'));
646: 
647:         return $this;
648:     }
649: 
650:     /**
651:      * Add revenue
652:      *
653:      * @param boolean $convertCurrency
654:      * @return Mage_Reports_Model_Resource_Order_Collection
655:      */
656:     public function addRevenueToSelect($convertCurrency = false)
657:     {
658:         if ($convertCurrency) {
659:             $this->getSelect()->columns(array(
660:                 'revenue' => '(main_table.base_grand_total * main_table.base_to_global_rate)'
661:             ));
662:         } else {
663:             $this->getSelect()->columns(array(
664:                 'revenue' => 'base_grand_total'
665:             ));
666:         }
667: 
668:         return $this;
669:     }
670: 
671:     /**
672:      * Add summary average totals
673:      *
674:      * @param int $storeId
675:      * @return Mage_Reports_Model_Resource_Order_Collection
676:      */
677:     public function addSumAvgTotals($storeId = 0)
678:     {
679:         $adapter = $this->getConnection();
680:         $baseSubtotalRefunded = $adapter->getIfNullSql('main_table.base_subtotal_refunded', 0);
681:         $baseSubtotalCanceled = $adapter->getIfNullSql('main_table.base_subtotal_canceled', 0);
682:         $baseDiscountCanceled = $adapter->getIfNullSql('main_table.base_discount_canceled', 0);
683: 
684:         /**
685:          * calculate average and total amount
686:          */
687:         $expr = ($storeId == 0)
688:             ? "(main_table.base_subtotal -
689:             {$baseSubtotalRefunded} - {$baseSubtotalCanceled} - ABS(main_table.base_discount_amount) -
690:             {$baseDiscountCanceled}) * main_table.base_to_global_rate"
691:             : "main_table.base_subtotal - {$baseSubtotalCanceled} - {$baseSubtotalRefunded} -
692:             ABS(main_table.base_discount_amount) - {$baseDiscountCanceled}";
693: 
694:         $this->getSelect()
695:             ->columns(array('orders_avg_amount' => "AVG({$expr})"))
696:             ->columns(array('orders_sum_amount' => "SUM({$expr})"));
697: 
698:         return $this;
699:     }
700: 
701:     /**
702:      * Sort order by total amount
703:      *
704:      * @param string $dir
705:      * @return Mage_Reports_Model_Resource_Order_Collection
706:      */
707:     public function orderByTotalAmount($dir = self::SORT_ORDER_DESC)
708:     {
709:         $this->getSelect()->order('orders_sum_amount ' . $dir);
710:         return $this;
711:     }
712: 
713:     /**
714:      * Order by orders count
715:      *
716:      * @param unknown_type $dir
717:      * @return Mage_Reports_Model_Resource_Order_Collection
718:      */
719:     public function orderByOrdersCount($dir = self::SORT_ORDER_DESC)
720:     {
721:         $this->getSelect()->order('orders_count ' . $dir);
722:         return $this;
723:     }
724: 
725:     /**
726:      * Order by customer registration
727:      *
728:      * @param unknown_type $dir
729:      * @return Mage_Reports_Model_Resource_Order_Collection
730:      */
731:     public function orderByCustomerRegistration($dir = self::SORT_ORDER_DESC)
732:     {
733:         $this->setOrder('customer_id', $dir);
734:         return $this;
735:     }
736: 
737:     /**
738:      * Sort order by order created_at date
739:      *
740:      * @param string $dir
741:      * @return Mage_Reports_Model_Resource_Order_Collection
742:      */
743:     public function orderByCreatedAt($dir = self::SORT_ORDER_DESC)
744:     {
745:         $this->setOrder('created_at', $dir);
746:         return $this;
747:     }
748: 
749:     /**
750:      * Get select count sql
751:      *
752:      * @return unknown
753:      */
754:     public function getSelectCountSql()
755:     {
756:         $countSelect = clone $this->getSelect();
757:         $countSelect->reset(Zend_Db_Select::ORDER);
758:         $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
759:         $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
760:         $countSelect->reset(Zend_Db_Select::COLUMNS);
761:         $countSelect->reset(Zend_Db_Select::GROUP);
762:         $countSelect->reset(Zend_Db_Select::HAVING);
763:         $countSelect->columns("COUNT(DISTINCT main_table.entity_id)");
764: 
765:         return $countSelect;
766:     }
767: 
768:     /**
769:      * Initialize initial fields to select
770:      *
771:      * @return Mage_Reports_Model_Resource_Order_Collection
772:      */
773:     protected function _initInitialFieldsToSelect()
774:     {
775:         // No fields should be initialized
776:         return $this;
777:     }
778: 
779:     /**
780:      * Add period filter by created_at attribute
781:      *
782:      * @param string $period
783:      * @return Mage_Reports_Model_Resource_Order_Collection
784:      */
785:     public function addCreateAtPeriodFilter($period)
786:     {
787:         list($from, $to) = $this->getDateRange($period, 0, 0, true);
788: 
789:         $this->checkIsLive($period);
790: 
791:         if ($this->isLive()) {
792:             $fieldToFilter = 'created_at';
793:         } else {
794:             $fieldToFilter = 'period';
795:         }
796: 
797:         $this->addFieldToFilter($fieldToFilter, array(
798:             'from'  => $from->toString(Varien_Date::DATETIME_INTERNAL_FORMAT),
799:             'to'    => $to->toString(Varien_Date::DATETIME_INTERNAL_FORMAT)
800:         ));
801: 
802:         return $this;
803:     }
804: }
805: