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:  * Customers 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_Customer_Collection extends Mage_Customer_Model_Resource_Customer_Collection
 36: {
 37:     /**
 38:      * Add order statistics flag
 39:      *
 40:      * @var boolean
 41:      */
 42:     protected $_addOrderStatistics           = false;
 43: 
 44:     /**
 45:      * Add order statistics is filter flag
 46:      *
 47:      * @var boolean
 48:      */
 49:     protected $_addOrderStatisticsIsFilter   = false;
 50: 
 51:     /**
 52:      * Customer id table name
 53:      *
 54:      * @var string
 55:      */
 56:     protected $_customerIdTableName;
 57: 
 58:     /**
 59:      * Customer id field name
 60:      *
 61:      * @var string
 62:      */
 63:     protected $_customerIdFieldName;
 64: 
 65:     /**
 66:      * Order entity table name
 67:      *
 68:      * @var string
 69:      */
 70:     protected $_orderEntityTableName;
 71: 
 72:     /**
 73:      * Order entity field name
 74:      *
 75:      * @var string
 76:      */
 77:     protected $_orderEntityFieldName;
 78: 
 79:     /**
 80:      * Add cart info to collection
 81:      *
 82:      * @return Mage_Reports_Model_Resource_Customer_Collection
 83:      */
 84:     public function addCartInfo()
 85:     {
 86:         foreach ($this->getItems() as $item) {
 87:             $quote = Mage::getModel('sales/quote')->loadByCustomer($item->getId());
 88: 
 89:             if ($quote instanceof Mage_Sales_Model_Quote) {
 90:                 $totals = $quote->getTotals();
 91:                 $item->setTotal($totals['subtotal']->getValue());
 92:                 $quoteItems = Mage::getResourceModel('sales/quote_item_collection')->setQuoteFilter($quote->getId());
 93:                 $quoteItems->load();
 94:                 $item->setItems($quoteItems->count());
 95:             } else {
 96:                 $item->remove();
 97:             }
 98: 
 99:         }
100:         return $this;
101:     }
102: 
103:     /**
104:      * Add customer name to results
105:      *
106:      * @return Mage_Reports_Model_Resource_Customer_Collection
107:      */
108:     public function addCustomerName()
109:     {
110:         $this->addNameToSelect();
111:         return $this;
112:     }
113: 
114:     /**
115:      * Order for each customer
116:      *
117:      * @param string $from
118:      * @param string $to
119:      * @return Mage_Reports_Model_Resource_Customer_Collection
120:      */
121:     public function joinOrders($from = '', $to = '')
122:     {
123:         if ($from != '' && $to != '') {
124:             $dateFilter = " AND orders.created_at BETWEEN '{$from}' AND '{$to}'";
125:         } else {
126:             $dateFilter = '';
127:         }
128: 
129:         $this->getSelect()
130:             ->joinLeft(array('orders' => $this->getTable('sales/order')),
131:                 "orders.customer_id = e.entity_id".$dateFilter,
132:             array());
133: 
134:         return $this;
135:     }
136: 
137:     /**
138:      * Add orders count
139:      *
140:      * @return Mage_Reports_Model_Resource_Customer_Collection
141:      */
142:     public function addOrdersCount()
143:     {
144:         $this->getSelect()
145:             ->columns(array("orders_count" => "COUNT(orders.entity_id)"))
146:             ->where('orders.state <> ?', Mage_Sales_Model_Order::STATE_CANCELED)
147:             ->group("e.entity_id");
148: 
149:         return $this;
150:     }
151: 
152:     /**
153:      * Order summary info for each customer
154:      * such as orders_count, orders_avg_amount, orders_total_amount
155:      *
156:      * @param int $storeId
157:      * @return Mage_Reports_Model_Resource_Customer_Collection
158:      */
159:     public function addSumAvgTotals($storeId = 0)
160:     {
161:         $adapter = $this->getConnection();
162:         $baseSubtotalRefunded   = $adapter->getIfNullSql('orders.base_subtotal_refunded', 0);
163:         $baseSubtotalCanceled   = $adapter->getIfNullSql('orders.base_subtotal_canceled', 0);
164: 
165:         /**
166:          * calculate average and total amount
167:          */
168:         $expr = ($storeId == 0)
169:             ? "(orders.base_subtotal - {$baseSubtotalCanceled} - {$baseSubtotalRefunded}) * orders.base_to_global_rate"
170:             : "orders.base_subtotal - {$baseSubtotalCanceled} - {$baseSubtotalRefunded}";
171: 
172:         $this->getSelect()
173:             ->columns(array("orders_avg_amount" => "AVG({$expr})"))
174:             ->columns(array("orders_sum_amount" => "SUM({$expr})"));
175: 
176:         return $this;
177:     }
178: 
179:     /**
180:      * Order by total amount
181:      *
182:      * @param string $dir
183:      * @return Mage_Reports_Model_Resource_Customer_Collection
184:      */
185:     public function orderByTotalAmount($dir = self::SORT_ORDER_DESC)
186:     {
187:         $this->getSelect()
188:             ->order("orders_sum_amount {$dir}");
189:         return $this;
190:     }
191: 
192:     /**
193:      * Add order statistics
194:      *
195:      * @param boolean $isFilter
196:      * @return Mage_Reports_Model_Resource_Customer_Collection
197:      */
198:     public function addOrdersStatistics($isFilter = false)
199:     {
200:         $this->_addOrderStatistics          = true;
201:         $this->_addOrderStatisticsIsFilter  = (bool)$isFilter;
202:         return $this;
203:     }
204: 
205:     /**
206:      * Add orders statistics to collection items
207:      *
208:      * @return Mage_Reports_Model_Resource_Customer_Collection
209:      */
210:     protected function _addOrdersStatistics()
211:     {
212:         $customerIds = $this->getColumnValues($this->getResource()->getIdFieldName());
213: 
214:         if ($this->_addOrderStatistics && !empty($customerIds)) {
215:             $adapter = $this->getConnection();
216:             $baseSubtotalRefunded   = $adapter->getIfNullSql('orders.base_subtotal_refunded', 0);
217:             $baseSubtotalCanceled   = $adapter->getIfNullSql('orders.base_subtotal_canceled', 0);
218: 
219:             $totalExpr = ($this->_addOrderStatisticsIsFilter)
220:                 ? "(orders.base_subtotal-{$baseSubtotalCanceled}-{$baseSubtotalRefunded})*orders.base_to_global_rate"
221:                 : "orders.base_subtotal-{$baseSubtotalCanceled}-{$baseSubtotalRefunded}";
222: 
223:             $select = $this->getConnection()->select();
224:             $select->from(array('orders'=>$this->getTable('sales/order')), array(
225:                 'orders_avg_amount' => "AVG({$totalExpr})",
226:                 'orders_sum_amount' => "SUM({$totalExpr})",
227:                 'orders_count' => 'COUNT(orders.entity_id)',
228:                 'customer_id'
229:             ))->where('orders.state <> ?', Mage_Sales_Model_Order::STATE_CANCELED)
230:               ->where('orders.customer_id IN(?)', $customerIds)
231:               ->group('orders.customer_id');
232: 
233:             /*
234:              * Analytic functions usage
235:              */
236:             $select = Mage::getResourceHelper('core')->getQueryUsingAnalyticFunction($select);
237: 
238:             foreach ($this->getConnection()->fetchAll($select) as $ordersInfo) {
239:                 $this->getItemById($ordersInfo['customer_id'])->addData($ordersInfo);
240:             }
241:         }
242: 
243:         return $this;
244:     }
245: 
246:     /**
247:      * Collection after load operations like adding orders statistics
248:      *
249:      * @return Mage_Reports_Model_Resource_Customer_Collection
250:      */
251:     protected function _afterLoad()
252:     {
253:         $this->_addOrdersStatistics();
254:         return $this;
255:     }
256: 
257:     /**
258:      * Order by customer registration
259:      *
260:      * @param string $dir
261:      * @return Mage_Reports_Model_Resource_Customer_Collection
262:      */
263:     public function orderByCustomerRegistration($dir = self::SORT_ORDER_DESC)
264:     {
265:         $this->addAttributeToSort('entity_id', $dir);
266:         return $this;
267:     }
268: 
269:     /**
270:      * Get select count sql
271:      *
272:      * @return string
273:      */
274:     public function getSelectCountSql()
275:     {
276:         $countSelect = clone $this->getSelect();
277:         $countSelect->reset(Zend_Db_Select::ORDER);
278:         $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
279:         $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
280:         $countSelect->reset(Zend_Db_Select::COLUMNS);
281:         $countSelect->reset(Zend_Db_Select::GROUP);
282:         $countSelect->reset(Zend_Db_Select::HAVING);
283:         $countSelect->columns("count(DISTINCT e.entity_id)");
284: 
285:         return $countSelect;
286:     }
287: }
288: