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_Quote_Collection extends Mage_Sales_Model_Mysql4_Quote_Collection
00036 {
00037 protected $_joinedFields = array();
00038
00039 public function prepareForAbandonedReport($storeIds, $filter = null)
00040 {
00041 $this->addFieldToFilter('items_count', array('neq' => '0'))
00042 ->addFieldToFilter('main_table.is_active', '1')
00043 ->addSubtotal($storeIds, $filter)
00044 ->addCustomerData($filter)
00045 ->setOrder('updated_at');
00046
00047 if (is_array($storeIds)) {
00048 $this->addFieldToFilter('main_table.store_id', array('in' => $storeIds));
00049 }
00050 return $this;
00051 }
00052
00053 public function addCustomerData($filter = null)
00054 {
00055 $customerEntity = Mage::getResourceSingleton('customer/customer');
00056 $attrFirstname = $customerEntity->getAttribute('firstname');
00057 $attrFirstnameId = $attrFirstname->getAttributeId();
00058 $attrFirstnameTableName = $attrFirstname->getBackend()->getTable();
00059
00060 $attrLastname = $customerEntity->getAttribute('lastname');
00061 $attrLastnameId = $attrLastname->getAttributeId();
00062 $attrLastnameTableName = $attrLastname->getBackend()->getTable();
00063
00064 $attrEmail = $customerEntity->getAttribute('email');
00065 $attrEmailTableName = $attrEmail->getBackend()->getTable();
00066
00067 $this->getSelect()
00068 ->joinInner(
00069 array('cust_email'=>$attrEmailTableName),
00070 'cust_email.entity_id=main_table.customer_id',
00071 array('email'=>'cust_email.email')
00072 )
00073 ->joinInner(
00074 array('cust_fname'=>$attrFirstnameTableName),
00075 'cust_fname.entity_id=main_table.customer_id and cust_fname.attribute_id='.$attrFirstnameId,
00076 array('firstname'=>'cust_fname.value')
00077 )
00078 ->joinInner(
00079 array('cust_lname'=>$attrLastnameTableName),
00080 'cust_lname.entity_id=main_table.customer_id and cust_lname.attribute_id='.$attrLastnameId,
00081 array(
00082 'lastname'=>'cust_lname.value',
00083 'customer_name' => new Zend_Db_Expr('CONCAT(cust_fname.value, " ", cust_lname.value)')
00084 )
00085 );
00086
00087 $this->_joinedFields['customer_name'] = 'CONCAT(cust_fname.value, " ", cust_lname.value)';
00088 $this->_joinedFields['email'] = 'cust_email.email';
00089
00090 if ($filter) {
00091 if (isset($filter['customer_name'])) {
00092 $this->getSelect()->where($this->_joinedFields['customer_name'] . ' LIKE "%' . $filter['customer_name'] . '%"');
00093 }
00094 if (isset($filter['email'])) {
00095 $this->getSelect()->where($this->_joinedFields['email'] . ' LIKE "%' . $filter['email'] . '%"');
00096 }
00097 }
00098
00099 return $this;
00100 }
00101
00102 public function addSubtotal($storeIds = '', $filter = null)
00103 {
00104 if (is_array($storeIds)) {
00105 $this->getSelect()->from("", array("subtotal" => "(main_table.base_subtotal_with_discount*main_table.base_to_global_rate)"));
00106 $this->_joinedFields['subtotal'] = '(main_table.base_subtotal_with_discount*main_table.base_to_global_rate)';
00107 } else {
00108 $this->getSelect()->from("", array("subtotal" => "main_table.base_subtotal_with_discount"));
00109 $this->_joinedFields['subtotal'] = 'main_table.base_subtotal_with_discount';
00110 }
00111
00112 if ($filter && is_array($filter) && isset($filter['subtotal'])) {
00113 if (isset($filter['subtotal']['from'])) {
00114 $this->getSelect()->where($this->_joinedFields['subtotal'] . ' >= ' . $filter['subtotal']['from']);
00115 }
00116 if (isset($filter['subtotal']['to'])) {
00117 $this->getSelect()->where($this->_joinedFields['subtotal'] . ' <= ' . $filter['subtotal']['to']);
00118 }
00119 }
00120
00121 return $this;
00122 }
00123
00124 public function getSelectCountSql()
00125 {
00126 $countSelect = clone $this->getSelect();
00127 $countSelect->reset(Zend_Db_Select::ORDER);
00128 $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
00129 $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
00130 $countSelect->reset(Zend_Db_Select::COLUMNS);
00131 $countSelect->reset(Zend_Db_Select::GROUP);
00132
00133 $countSelect->from("", "count(DISTINCT main_table.entity_id)");
00134 $sql = $countSelect->__toString();
00135
00136 return $sql;
00137 }
00138 }