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_Customer_Collection extends Mage_Customer_Model_Entity_Customer_Collection
00036 {
00037
00038 protected $_customerIdTableName;
00039 protected $_customerIdFieldName;
00040 protected $_orderEntityTableName;
00041 protected $_orderEntityFieldName;
00042
00043 public function addCartInfo()
00044 {
00045 foreach ($this->getItems() as $item)
00046 {
00047 $quote = Mage::getModel('sales/quote')->loadByCustomer($item->getId());
00048
00049 if (is_object($quote))
00050 {
00051 $totals = $quote->getTotals();
00052 $item->setTotal($totals['subtotal']->getValue());
00053 $quote_items = Mage::getResourceModel('sales/quote_item_collection')->setQuoteFilter($quote->getId());
00054 $quote_items->load();
00055 $item->setItems($quote_items->count());
00056 } else {
00057 $item->remove();
00058 }
00059
00060 }
00061 return $this;
00062 }
00063
00064 public function addCustomerName()
00065 {
00066 $this->addNameToSelect();
00067 return $this;
00068 }
00069
00070
00071
00072
00073 public function joinOrders($from = '', $to = '')
00074 {
00075 $order = Mage::getResourceSingleton('sales/order');
00076
00077 $attr = $order->getAttribute('customer_id');
00078
00079 $attrId = $attr->getAttributeId();
00080 $this->_customerIdTableName = $attr->getBackend()->getTable();
00081 $this->_customerIdFieldName = $attr->getBackend()->isStatic() ? 'customer_id' : 'value';
00082
00083 if ($from != '' && $to != '') {
00084 $dateFilter = " and {$this->_customerIdTableName}.created_at BETWEEN '{$from}' AND '{$to}'";
00085 } else {
00086 $dateFilter = '';
00087 }
00088
00089 $this->getSelect()
00090 ->joinLeft($this->_customerIdTableName,
00091 "{$this->_customerIdTableName}.{$this->_customerIdFieldName}=e.entity_id".$dateFilter,
00092 array());
00093
00094 return $this;
00095 }
00096
00097 public function addOrdersCount()
00098 {
00099 $order = Mage::getResourceSingleton('sales/order');
00100
00101 $stateAttr = $order->getAttribute('state');
00102 $_joinCondition = "{$this->_customerIdTableName}.entity_id=order_state.entity_id";
00103 $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.attribute_id=? ', $stateAttr->getId());
00104 $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.value<>? ', Mage_Sales_Model_Order::STATE_CANCELED);
00105
00106 $this->getSelect()
00107 ->from('', array("orders_count" => "COUNT(order_state.entity_id)"))
00108 ->joinLeft(
00109 array('order_state' => $stateAttr->getBackend()->getTable()),
00110 $_joinCondition,
00111 array())
00112 ->group("e.entity_id");
00113
00114 return $this;
00115 }
00116
00117
00118
00119
00120
00121 public function addSumAvgTotals($storeId = 0)
00122 {
00123
00124
00125
00126 $order = Mage::getResourceSingleton('sales/order');
00127
00128
00129 if ($storeId == 0) {
00130
00131
00132
00133 $attr = $order->getAttribute('base_to_global_rate');
00134
00135 $attrId = $attr->getAttributeId();
00136 $baseToGlobalRateTableName = $attr->getBackend()->getTable();
00137 $baseToGlobalRateFieldName = $attr->getBackend()->isStatic() ? 'base_to_global_rate' : 'value';
00138
00139 $this->getSelect()
00140 ->joinLeft(array('_b2gr_'.$baseToGlobalRateTableName => $baseToGlobalRateTableName),
00141 "_b2gr_{$baseToGlobalRateTableName}.entity_id={$this->_customerIdTableName}.entity_id AND ".
00142 "_b2gr_{$baseToGlobalRateTableName}.attribute_id={$attrId}", array());
00143
00144
00145
00146
00147 $expr = "({$this->_customerIdTableName}.base_subtotal-IFNULL({$this->_customerIdTableName}.base_subtotal_canceled,0)-IFNULL({$this->_customerIdTableName}.base_subtotal_refunded,0))*_b2gr_{$baseToGlobalRateTableName}.{$baseToGlobalRateFieldName}";
00148
00149 } else {
00150
00151
00152
00153
00154 $expr = "{$this->_customerIdTableName}.base_subtotal-IFNULL({$this->_customerIdTableName}.base_subtotal_canceled,0)-IFNULL({$this->_customerIdTableName}.base_subtotal_refunded,0)";
00155 }
00156
00157 $this->getSelect()
00158 ->from('', array("orders_avg_amount" => "AVG({$expr})"))
00159 ->from('', array("orders_sum_amount" => "SUM({$expr})"));
00160
00161 return $this;
00162 }
00163
00164 public function orderByTotalAmount($dir = 'desc')
00165 {
00166 $this->getSelect()
00167 ->order("orders_sum_amount {$dir}");
00168 return $this;
00169 }
00170
00171 public function orderByCustomerRegistration($dir = 'desc')
00172 {
00173 $this->addAttributeToSort('entity_id', $dir);
00174 return $this;
00175 }
00176
00177 public function getSelectCountSql()
00178 {
00179 $countSelect = clone $this->getSelect();
00180 $countSelect->reset(Zend_Db_Select::ORDER);
00181 $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
00182 $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
00183 $countSelect->reset(Zend_Db_Select::COLUMNS);
00184 $countSelect->reset(Zend_Db_Select::GROUP);
00185 $countSelect->reset(Zend_Db_Select::HAVING);
00186 $countSelect->from("", "count(DISTINCT e.entity_id)");
00187 $sql = $countSelect->__toString();
00188 return $sql;
00189 }
00190 }