00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034
00035 class Mage_Reports_Model_Mysql4_Order_Collection extends Mage_Sales_Model_Entity_Order_Collection
00036 {
00037
00038 public function prepareSummary($range, $customStart, $customEnd, $isFilter=0)
00039 {
00040
00041 if ($isFilter==0) {
00042 $this->addExpressionAttributeToSelect('revenue',
00043 'SUM({{base_grand_total}}*{{base_to_global_rate}})',
00044 array('base_grand_total', 'base_to_global_rate'));
00045 } else{
00046 $this->addExpressionAttributeToSelect('revenue',
00047 'SUM({{base_grand_total}})',
00048 array('base_grand_total'));
00049 }
00050
00051 $this->addExpressionAttributeToSelect('quantity', 'COUNT({{attribute}})', 'entity_id')
00052 ->addExpressionAttributeToSelect('range', $this->_getRangeExpression($range), 'created_at')
00053 ->addAttributeToFilter('created_at', $this->getDateRange($range, $customStart, $customEnd))
00054 ->groupByAttribute('range')
00055 ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
00056 ->getSelect()->order('range', 'asc');
00057
00058 return $this;
00059 }
00060
00061 protected function _getRangeExpression($range)
00062 {
00063
00064
00065
00066 switch ($range)
00067 {
00068 case '24h':
00069 $expression = 'DATE_FORMAT({{attribute}}, \'%Y-%m-%d %H:00\')';
00070
00071 break;
00072 case '7d':
00073 case '1m':
00074 $expression = 'DATE_FORMAT({{attribute}}, \'%Y-%m-%d\')';
00075 break;
00076 case '1y':
00077 case '2y':
00078 case 'custom':
00079 default:
00080 $expression = 'DATE_FORMAT({{attribute}}, \'%Y-%m\')';
00081 break;
00082 }
00083
00084 return $expression;
00085 }
00086
00087 public function getDateRange($range, $customStart, $customEnd, $returnObjects = false)
00088 {
00089 $dateEnd = new Zend_Date(Mage::getModel('core/date')->gmtTimestamp());
00090 $dateStart = clone $dateEnd;
00091
00092
00093 $dateEnd->setHour(23);
00094 $dateEnd->setMinute(59);
00095 $dateEnd->setSecond(59);
00096
00097 $dateStart->setHour(0);
00098 $dateStart->setMinute(0);
00099 $dateStart->setSecond(0);
00100
00101 switch ($range)
00102 {
00103 case '24h':
00104 $dateEnd = new Zend_Date(Mage::getModel('core/date')->gmtTimestamp());
00105 $dateEnd->addHour(1);
00106 $dateStart = clone $dateEnd;
00107 $dateStart->subDay(1);
00108 break;
00109
00110 case '7d':
00111
00112
00113 $dateStart->subDay(6);
00114 break;
00115
00116 case '1m':
00117 $dateStart->setDay(1);
00118 break;
00119
00120 case 'custom':
00121 $dateStart = $customStart ? $customStart : $dateEnd;
00122 $dateEnd = $customEnd ? $customEnd : $dateEnd;
00123 break;
00124
00125 case '1y':
00126 $dateStart->setMonth(1);
00127 $dateStart->setDay(1);
00128 break;
00129 case '2y':
00130 $dateStart->setMonth(1);
00131 $dateStart->setDay(1);
00132 $dateStart->subYear(1);
00133 break;
00134 }
00135
00136 if ($returnObjects) {
00137 return array($dateStart, $dateEnd);
00138 } else {
00139 return array('from'=>$dateStart, 'to'=>$dateEnd, 'datetime'=>true);
00140 }
00141 }
00142
00143 public function addItemCountExpr()
00144 {
00145
00146
00147
00148
00149
00150
00151
00152 $this->getSelect()->join(
00153 array('items'=>$this->getTable('sales/order_item')),
00154 'items.order_id=e.entity_id',
00155 array('items_count'=>new Zend_Db_Expr('COUNT(items.item_id)', 'parent_item'))
00156 )
00157 ->where('items.parent_item_id is NULL')
00158 ->group('e.entity_id');
00159 return $this;
00160 }
00161
00162 public function calculateTotals($isFilter = 0)
00163 {
00164 if ($isFilter == 0) {
00165 $this->addExpressionAttributeToSelect(
00166 'revenue',
00167 'SUM(({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_refunded}},0))*{{base_to_global_rate}})',
00168 array('base_subtotal', 'base_to_global_rate', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded'))
00169 ->addExpressionAttributeToSelect(
00170 'tax',
00171 'SUM(({{base_tax_amount}}-IFNULL({{base_tax_refunded}},0)-IFNULL({{base_tax_canceled}},0))*{{base_to_global_rate}})',
00172 array('base_tax_amount', 'base_tax_canceled', 'base_tax_refunded', 'base_to_global_rate'))
00173 ->addExpressionAttributeToSelect(
00174 'shipping',
00175 'SUM(({{base_shipping_amount}}-IFNULL({{base_shipping_refunded}},0)-IFNULL({{base_shipping_canceled}},0))*{{base_to_global_rate}})',
00176 array('base_shipping_amount', 'base_shipping_refunded', 'base_shipping_canceled', 'base_to_global_rate'));
00177 } else {
00178 $this->addExpressionAttributeToSelect(
00179 'revenue',
00180 'SUM({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_refunded}},0))',
00181 array('base_subtotal', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded'))
00182 ->addExpressionAttributeToSelect(
00183 'tax',
00184 'SUM({{base_tax_amount}}-IFNULL({{base_tax_refunded}},0)-IFNULL({{base_tax_canceled}},0))',
00185 array('base_tax_amount', 'base_tax_refunded', 'base_tax_canceled'))
00186 ->addExpressionAttributeToSelect(
00187 'shipping',
00188 'SUM({{base_shipping_amount}}-IFNULL({{base_shipping_refunded}},0)-IFNULL({{base_shipping_canceled}},0))',
00189 array('base_shipping_amount', 'base_shipping_refunded', 'base_shipping_canceled'));
00190 }
00191
00192 $this->addExpressionAttributeToSelect('quantity', 'COUNT({{entity_id}})', array('entity_id'))
00193 ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
00194 ->groupByAttribute('entity_type_id');
00195
00196 return $this;
00197 }
00198
00199 public function calculateSales($isFilter = 0)
00200 {
00201 if ($isFilter == 0) {
00202 $expr = "({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_refunded}},0))*{{base_to_global_rate}}";
00203 $attrs = array('base_subtotal', 'base_to_global_rate', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded');
00204 $this->addExpressionAttributeToSelect('lifetime', "SUM({$expr})", $attrs)
00205 ->addExpressionAttributeToSelect('average', "AVG({$expr})", $attrs);
00206 } else {
00207 $expr = "({{base_subtotal}}-IFNULL({{base_subtotal_refunded}},0)-IFNULL({{base_subtotal_canceled}},0)-IFNULL({{base_discount_amount}},0)+IFNULL({{base_discount_amount}},0))";
00208 $attrs = array('base_subtotal', 'base_subtotal_refunded', 'base_subtotal_canceled','base_discount_amount','base_discount_refunded');
00209 $this->addExpressionAttributeToSelect('lifetime', "SUM($expr)", $attrs)
00210 ->addExpressionAttributeToSelect('average', "AVG($expr)", $attrs);
00211 }
00212
00213 $this->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
00214 ->groupByAttribute('entity_type_id');
00215 return $this;
00216 }
00217
00218 public function setDateRange($from, $to)
00219 {
00220 $this->_reset()
00221 ->addAttributeToSelect('*')
00222 ->addAttributeToFilter('created_at', array('from' => $from, 'to' => $to))
00223 ->addExpressionAttributeToSelect('orders', 'COUNT(DISTINCT({{entity_id}}))', array('entity_id'))
00224 ->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED))
00225 ->getSelect()->group('("*")');
00226
00227
00228
00229
00230
00231
00232
00233
00234
00235
00236
00237
00238
00239
00240
00241
00242
00243
00244
00245
00246
00247
00248
00249
00250
00251
00252
00253 $countSql = clone $this->getSelect();
00254 $countSql->reset();
00255
00256 $countSql->from(array("order_items" => $this->getTable('sales/order_item')), array("sum(`order_items2`.`qty_ordered`)"))
00257 ->joinLeft(array("order_items2" => $this->getTable('sales/order_item')),
00258 "order_items2.item_id = `order_items`.item_id", array())
00259 ->where("`order_items`.`order_id` = `e`.`entity_id`")
00260 ->where("`order_items2`.`parent_item_id` is NULL");
00261
00262 $this->getSelect()->from("", array("items" => "SUM((".$countSql."))"));
00263
00264 return $this;
00265 }
00266
00267 public function setStoreIds($storeIds)
00268 {
00269 $vals = array_values($storeIds);
00270 if (count($storeIds) >= 1 && $vals[0] != '') {
00271 $this->addAttributeToFilter('store_id', array('in' => (array)$storeIds))
00272 ->addExpressionAttributeToSelect(
00273 'subtotal',
00274 'SUM({{base_subtotal}})',
00275 array('base_subtotal'))
00276 ->addExpressionAttributeToSelect(
00277 'tax',
00278 'SUM({{base_tax_amount}})',
00279 array('base_tax_amount'))
00280 ->addExpressionAttributeToSelect(
00281 'shipping',
00282 'SUM({{base_shipping_amount}})',
00283 array('base_shipping_amount'))
00284 ->addExpressionAttributeToSelect(
00285 'discount',
00286 'SUM({{base_discount_amount}})',
00287 array('base_discount_amount'))
00288 ->addExpressionAttributeToSelect(
00289 'total',
00290 'SUM({{base_grand_total}})',
00291 array('base_grand_total'))
00292 ->addExpressionAttributeToSelect(
00293 'invoiced',
00294 'SUM({{base_total_paid}})',
00295 array('base_total_paid'))
00296 ->addExpressionAttributeToSelect(
00297 'refunded',
00298 'SUM({{base_total_refunded}})',
00299 array('base_total_refunded'));
00300 } else {
00301 $this->addExpressionAttributeToSelect(
00302 'subtotal',
00303 'SUM({{base_subtotal}}*{{base_to_global_rate}})',
00304 array('base_subtotal', 'base_to_global_rate'))
00305 ->addExpressionAttributeToSelect(
00306 'tax',
00307 'SUM({{base_tax_amount}}*{{base_to_global_rate}})',
00308 array('base_tax_amount', 'base_to_global_rate'))
00309 ->addExpressionAttributeToSelect(
00310 'shipping',
00311 'SUM({{base_shipping_amount}}*{{base_to_global_rate}})',
00312 array('base_shipping_amount', 'base_to_global_rate'))
00313 ->addExpressionAttributeToSelect(
00314 'discount',
00315 'SUM({{base_discount_amount}}*{{base_to_global_rate}})',
00316 array('base_discount_amount', 'base_to_global_rate'))
00317 ->addExpressionAttributeToSelect(
00318 'total',
00319 'SUM({{base_grand_total}}*{{base_to_global_rate}})',
00320 array('base_grand_total', 'base_to_global_rate'))
00321 ->addExpressionAttributeToSelect(
00322 'invoiced',
00323 'SUM({{base_total_paid}}*{{base_to_global_rate}})',
00324 array('base_total_paid', 'base_to_global_rate'))
00325 ->addExpressionAttributeToSelect(
00326 'refunded',
00327 'SUM({{base_total_refunded}}*{{base_to_global_rate}})',
00328 array('base_total_refunded', 'base_to_global_rate'));
00329 }
00330
00331 return $this;
00332 }
00333
00334
00335
00336
00337
00338
00339 public function groupByCustomer()
00340 {
00341 $this->groupByAttribute('customer_id');
00342
00343 return $this;
00344 }
00345
00346
00347
00348
00349
00350
00351 public function joinCustomerName()
00352 {
00353
00354 $this->joinAttribute('firstname', 'customer/firstname', 'customer_id');
00355 $this->joinAttribute('lastname', 'customer/lastname', 'customer_id');
00356 $this->getSelect()->from("", array('name' => 'CONCAT(_table_firstname.value," ", _table_lastname.value)'));
00357 return $this;
00358 }
00359
00360
00361
00362
00363
00364
00365 public function addOrdersCount()
00366 {
00367 $this->addAttributeToFilter('state', array('neq' => Mage_Sales_Model_Order::STATE_CANCELED));
00368 $this->getSelect()
00369 ->from('', array("orders_count" => "COUNT(e.entity_id)"));
00370
00371 return $this;
00372 }
00373
00374
00375
00376
00377
00378
00379
00380 public function addSumAvgTotals($storeId = 0)
00381 {
00382 if ($storeId == 0) {
00383
00384
00385
00386 $order = Mage::getResourceSingleton('sales/order');
00387
00388
00389 $attr = $order->getAttribute('base_to_global_rate');
00390
00391 $attrId = $attr->getAttributeId();
00392 $attributeTableName = $attr->getBackend()->getTable();
00393 $baseToGlobalRateTableName = $attr->getBackend()->isStatic() ? 'base_to_global_rate' : 'value';
00394
00395 $this->getSelect()
00396 ->joinLeft(array('_b2gr_'.$baseToGlobalRateTableName => $attributeTableName),
00397 "_b2gr_{$baseToGlobalRateTableName}.entity_id=e.entity_id AND ".
00398 "_b2gr_{$baseToGlobalRateTableName}.attribute_id={$attrId}", array());
00399
00400
00401
00402
00403 $expr = "(e.base_subtotal-IFNULL(e.base_subtotal_refunded,0)-IFNULL(e.base_subtotal_canceled,0))*_b2gr_{$baseToGlobalRateTableName}.{$baseToGlobalRateTableName}";
00404
00405 } else {
00406
00407
00408
00409
00410 $expr = "e.base_subtotal-IFNULL(e.base_subtotal_canceled,0)-IFNULL(e.base_subtotal_refunded,0)";
00411 }
00412
00413 $this->getSelect()
00414 ->from('', array("orders_avg_amount" => "AVG({$expr})"))
00415 ->from('', array("orders_sum_amount" => "SUM({$expr})"));
00416
00417 return $this;
00418 }
00419
00420
00421
00422
00423
00424
00425
00426 public function orderByTotalAmount($dir = 'desc')
00427 {
00428 $this->getSelect()
00429 ->order("orders_sum_amount {$dir}");
00430 return $this;
00431 }
00432
00433 public function orderByOrdersCount($dir = 'desc')
00434 {
00435 $this->getSelect()
00436 ->order("orders_count {$dir}");
00437 return $this;
00438 }
00439
00440 public function orderByCustomerRegistration($dir = 'desc')
00441 {
00442 $this->addAttributeToSort('customer_id', $dir);
00443 return $this;
00444 }
00445
00446 public function getSelectCountSql()
00447 {
00448 $countSelect = clone $this->getSelect();
00449 $countSelect->reset(Zend_Db_Select::ORDER);
00450 $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
00451 $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
00452 $countSelect->reset(Zend_Db_Select::COLUMNS);
00453 $countSelect->reset(Zend_Db_Select::GROUP);
00454 $countSelect->reset(Zend_Db_Select::HAVING);
00455 $countSelect->from("", "count(DISTINCT e.entity_id)");
00456
00457 $sql = $countSelect->__toString();
00458
00459 return $sql;
00460 }
00461 }