Mage_Reports_Model_Mysql4_Order_Collection Class Reference

Inheritance diagram for Mage_Reports_Model_Mysql4_Order_Collection:

Mage_Sales_Model_Entity_Order_Collection Mage_Eav_Model_Entity_Collection_Abstract Varien_Data_Collection_Db Varien_Data_Collection Mage_Reports_Model_Mysql4_Customer_Orders_Collection Mage_Reports_Model_Mysql4_Customer_Totals_Collection

List of all members.

Public Member Functions

 prepareSummary ($range, $customStart, $customEnd, $isFilter=0)
 getDateRange ($range, $customStart, $customEnd, $returnObjects=false)
 addItemCountExpr ()
 calculateTotals ($isFilter=0)
 calculateSales ($isFilter=0)
 setDateRange ($from, $to)
 setStoreIds ($storeIds)
 groupByCustomer ()
 joinCustomerName ()
 addOrdersCount ()
 addSumAvgTotals ($storeId=0)
 orderByTotalAmount ($dir= 'desc')
 orderByOrdersCount ($dir= 'desc')
 orderByCustomerRegistration ($dir= 'desc')
 getSelectCountSql ()

Protected Member Functions

 _getRangeExpression ($range)


Detailed Description

Definition at line 35 of file Collection.php.


Member Function Documentation

_getRangeExpression ( range  )  [protected]

Definition at line 61 of file Collection.php.

00062     {
00063         // dont need of this offset bc we are format date in block
00064         //$timeZoneOffset = Mage::getModel('core/date')->getGmtOffset();
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     }

addItemCountExpr (  ) 

Reimplemented from Mage_Sales_Model_Entity_Order_Collection.

Definition at line 143 of file Collection.php.

00144     {
00145 //        $orderItemEntityTypeId = Mage::getResourceSingleton('sales/order_item')->getTypeId();
00146 //        $this->getSelect()->join(
00147 //                array('items'=>Mage::getResourceSingleton('sales/order_item')->getEntityTable()),
00148 //                'items.parent_id=e.entity_id and items.entity_type_id='.$orderItemEntityTypeId,
00149 //                array('items_count'=>new Zend_Db_Expr('COUNT(items.entity_id)'))
00150 //            )
00151 //            ->group('e.entity_id');
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     }

addOrdersCount (  ) 

Add Order count field to select

Returns:
Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 365 of file Collection.php.

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     }

addSumAvgTotals ( storeId = 0  ) 

Add summary average totals

Parameters:
int $storeId
Returns:
Mage_Reports_Model_Mysql4_Order_Collection

Join store_to_base_rate attribute

calculate average and total amount

calculate average and total amount

Definition at line 380 of file Collection.php.

00381     {
00382         if ($storeId == 0) {
00383             /**
00384              * Join store_to_base_rate attribute
00385              */
00386             $order = Mage::getResourceSingleton('sales/order');
00387             /* @var $order Mage_Sales_Model_Entity_Order */
00388 
00389             $attr = $order->getAttribute('base_to_global_rate');
00390             /* @var $attr Mage_Eav_Model_Entity_Attribute_Abstract */
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              * calculate average and total amount
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              * calculate average and total amount
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     }

calculateSales ( isFilter = 0  ) 

Definition at line 199 of file Collection.php.

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     }

calculateTotals ( isFilter = 0  ) 

Definition at line 162 of file Collection.php.

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     }

getDateRange ( range,
customStart,
customEnd,
returnObjects = false 
)

Definition at line 87 of file Collection.php.

00088     {
00089         $dateEnd = new Zend_Date(Mage::getModel('core/date')->gmtTimestamp());
00090         $dateStart = clone $dateEnd;
00091 
00092         // go to the end of a day
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                 // substract 6 days we need to include
00112                 // only today and not hte last one from range
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     }

getSelectCountSql (  ) 

Get SQL for get record count

Returns:
Varien_Db_Select

Reimplemented from Varien_Data_Collection_Db.

Definition at line 446 of file Collection.php.

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     }

groupByCustomer (  ) 

Add group By customer attribute

Returns:
Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 339 of file Collection.php.

00340     {
00341         $this->groupByAttribute('customer_id');
00342 
00343         return $this;
00344     }

joinCustomerName (  ) 

Join Customer Name (concat)

Returns:
Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 351 of file Collection.php.

00352     {
00353         //TODO: add full name logic
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     }

orderByCustomerRegistration ( dir = 'desc'  ) 

Definition at line 440 of file Collection.php.

00441     {
00442         $this->addAttributeToSort('customer_id', $dir);
00443         return $this;
00444     }

orderByOrdersCount ( dir = 'desc'  ) 

Definition at line 433 of file Collection.php.

00434     {
00435         $this->getSelect()
00436             ->order("orders_count {$dir}");
00437         return $this;
00438     }

orderByTotalAmount ( dir = 'desc'  ) 

Sort order by total amount

Parameters:
string $dir
Returns:
Mage_Reports_Model_Mysql4_Order_Collection

Definition at line 426 of file Collection.php.

00427     {
00428         $this->getSelect()
00429             ->order("orders_sum_amount {$dir}");
00430         return $this;
00431     }

prepareSummary ( range,
customStart,
customEnd,
isFilter = 0 
)

Definition at line 38 of file Collection.php.

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     }

setDateRange ( from,
to 
)

getting qty count for each order

Reimplemented in Mage_Reports_Model_Mysql4_Customer_Orders_Collection, and Mage_Reports_Model_Mysql4_Customer_Totals_Collection.

Definition at line 218 of file Collection.php.

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          * getting qty count for each order
00229          */
00230 
00231 //        $orderItem = Mage::getResourceSingleton('sales/order_item');
00232 //        /* @var $orderItem Mage_Sales_Model_Entity_Quote */
00233 //        $attr = $orderItem->getAttribute('parent_id');
00234 //        /* @var $attr Mage_Eav_Model_Entity_Attribute_Abstract */
00235 //        $attrId = $attr->getAttributeId();
00236 //        $tableName = $attr->getBackend()->getTable();
00237 //
00238 //        $this->getSelect()
00239 //            ->joinLeft(array("order_items" => $tableName),
00240 //                "order_items.parent_id = e.entity_id and order_items.entity_type_id=".$orderItem->getTypeId(), array());
00241 //
00242 //        $attr = $orderItem->getAttribute('qty_ordered');
00243 //        /* @var $attr Mage_Eav_Model_Entity_Attribute_Abstract */
00244 //        $attrId = $attr->getAttributeId();
00245 //        $tableName = $attr->getBackend()->getTable();
00246 //        $fieldName = $attr->getBackend()->isStatic() ? 'qty_ordered' : 'value';
00247 //
00248 //        $this->getSelect()
00249 //            ->joinLeft(array("order_items2" => $tableName),
00250 //                "order_items2.entity_id = `order_items`.entity_id and order_items2.attribute_id = {$attrId}", array())
00251 //            ->from("", array("items" => "sum(order_items2.{$fieldName})"));
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     }

setStoreIds ( storeIds  ) 

Reimplemented in Mage_Reports_Model_Mysql4_Customer_Orders_Collection, and Mage_Reports_Model_Mysql4_Customer_Totals_Collection.

Definition at line 267 of file Collection.php.

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     }


The documentation for this class was generated from the following file:

Generated on Sat Jul 4 17:24:36 2009 for Magento by  doxygen 1.5.8