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_Log_Model_Mysql4_Visitor_Collection extends Varien_Data_Collection_Db
00036 {
00037
00038
00039
00040
00041
00042 protected $_visitorTable;
00043
00044
00045
00046
00047
00048
00049 protected $_visitorInfoTable;
00050
00051
00052
00053
00054
00055
00056 protected $_customerTable;
00057
00058
00059
00060
00061
00062
00063 protected $_urlTable;
00064
00065
00066
00067
00068
00069
00070 protected $_urlInfoTable;
00071
00072
00073
00074
00075
00076
00077 protected $_summaryTable;
00078
00079
00080
00081
00082
00083
00084 protected $_summaryTypeTable;
00085
00086
00087
00088
00089
00090
00091 protected $_quoteTable;
00092
00093 protected $_isOnlineFilterUsed = false;
00094
00095 protected $_fieldMap = array(
00096 'customer_firstname' => 'customer_firstname_table.value',
00097 'customer_lastname' => 'customer_lastname_table.value',
00098 'customer_email' => 'customer_email_table.email',
00099 'customer_id' => 'customer_table.customer_id',
00100 'url' => 'url_info_table.url'
00101 );
00102
00103
00104
00105
00106
00107 function __construct()
00108 {
00109 $resource = Mage::getSingleton('core/resource');
00110 parent::__construct($resource->getConnection('log_read'));
00111
00112 $this->_visitorTable = $resource->getTableName('log/visitor');
00113 $this->_visitorInfoTable = $resource->getTableName('log/visitor_info');
00114 $this->_urlTable = $resource->getTableName('log/url_table');
00115 $this->_urlInfoTable = $resource->getTableName('log/url_info_table');
00116 $this->_customerTable = $resource->getTableName('log/customer');
00117 $this->_summaryTable = $resource->getTableName('log/summary_table');
00118 $this->_summaryTypeTable = $resource->getTableName('log/summary_type_table');
00119 $this->_quoteTable = $resource->getTableName('log/quote_table');
00120
00121 $this->setItemObjectClass(Mage::getConfig()->getModelClassName('log/visitor'));
00122 }
00123
00124
00125
00126
00127
00128
00129
00130 public function useOnlineFilter($minutes=null)
00131 {
00132 if (is_null($minutes)) {
00133 $minutes = Mage_Log_Model_Visitor::getOnlineMinutesInterval();
00134 }
00135 $this->_select->from(array('visitor_table'=>$this->_visitorTable))
00136
00137 ->joinLeft(array('info_table'=>$this->_visitorInfoTable), 'info_table.visitor_id=visitor_table.visitor_id')
00138 ->joinLeft(array('customer_table'=>$this->_customerTable),
00139 'customer_table.visitor_id = visitor_table.visitor_id AND customer_table.logout_at IS NULL',
00140 array('log_id', 'customer_id', 'login_at', 'logout_at'))
00141 ->joinLeft(array('url_info_table'=>$this->_urlInfoTable),
00142 'url_info_table.url_id = visitor_table.last_url_id')
00143
00144 ->where( 'visitor_table.last_visit_at >= ( ? - INTERVAL '.$minutes.' MINUTE)', now() );
00145
00146
00147 $customersCollection = Mage::getModel('customer/customer')->getCollection();
00148
00149 $firstname = $customersCollection->getAttribute('firstname');
00150 $lastname = $customersCollection->getAttribute('lastname');
00151 $email = $customersCollection->getAttribute('email');
00152
00153 $this->_select
00154 ->from('', array('type' => 'IF(customer_id, \''.Mage_Log_Model_Visitor::VISITOR_TYPE_CUSTOMER.'\', \''.Mage_Log_Model_Visitor::VISITOR_TYPE_VISITOR.'\')'))
00155 ->joinLeft(
00156 array('customer_lastname_table'=>$lastname->getBackend()->getTable()),
00157 'customer_lastname_table.entity_id=customer_table.customer_id
00158 AND customer_lastname_table.attribute_id = '.(int) $lastname->getAttributeId() . '
00159 ',
00160 array('customer_lastname'=>'value')
00161 )
00162 ->joinLeft(
00163 array('customer_firstname_table'=>$firstname->getBackend()->getTable()),
00164 'customer_firstname_table.entity_id=customer_table.customer_id
00165 AND customer_firstname_table.attribute_id = '.(int) $firstname->getAttributeId() . '
00166 ',
00167 array('customer_firstname'=>'value')
00168 )
00169 ->joinLeft(
00170 array('customer_email_table'=>$email->getBackend()->getTable()),
00171 'customer_email_table.entity_id=customer_table.customer_id',
00172 array('customer_email'=>'email')
00173 );
00174 $this->_isOnlineFilterUsed = true;
00175 return $this;
00176 }
00177
00178 public function showCustomersOnly()
00179 {
00180 $this->_select->where('customer_table.customer_id > 0')
00181 ->group('customer_table.customer_id');
00182 return $this;
00183 }
00184
00185 public function getAggregatedData($period=720, $type_code=null, $customFrom=null, $customTo=null)
00186 {
00187
00188
00189
00190 $timeZoneOffset = Mage::getModel('core/date')->getGmtOffset();//Mage::app()->getLocale()->date()->getGmtOffset();
00191 $this->_itemObjectClass = 'Varien_Object';
00192 $this->_setIdFieldName('summary_id');
00193
00194 /*
00195 $this->_select->from(array('summary'=>$this->_summaryTable), array('summary_id','customer_count','visitor_count','add_date'=>"DATE_SUB(summary.add_date, INTERVAL $timeZoneOffset SECOND)"))
00196 ->join(array('type'=>$this->_summaryTypeTable), 'type.type_id=summary.type_id', array());
00197
00198 if (is_null($customFrom) && is_null($customTo)) {
00199 $this->_select->where( "DATE_SUB(summary.add_date, INTERVAL $timeZoneOffset SECOND) >= ( DATE_SUB(?, INTERVAL $timeZoneOffset SECOND) - INTERVAL {$period} {$this->_getRangeByType($type_code)} )", now() );
00200 } else {
00201 if($customFrom) {
00202 $this->_select->where( "DATE_SUB(summary.add_date, INTERVAL $timeZoneOffset SECOND) >= ", $this->_read->convertDate($customFrom));
00203 }
00204 if($customTo) {
00205 $this->_select->where( "DATE_SUB(summary.add_date, INTERVAL $timeZoneOffset SECOND) <= ", $this->_read->convertDate($customTo));
00206 }
00207 }
00208
00209
00210 if( is_null($type_code) ) {
00211 $this->_select->where("summary.type_id IS NULL");
00212 } else {
00213 $this->_select->where("type.type_code = ? ", $type_code);
00214 }
00215 */
00216
00217 $this->_select->from(array('summary'=>$this->_summaryTable),
00218 array('summary_id',
00219 'customer_count'=>'SUM(customer_count)',
00220 'visitor_count'=>'SUM(visitor_count)',
00221 'add_date'=>"DATE_ADD(summary.add_date, INTERVAL $timeZoneOffset SECOND)"
00222 ));
00223
00224 $this->_select->where("DATE_SUB(summary.add_date, INTERVAL $timeZoneOffset SECOND) >= ( DATE_SUB(?, INTERVAL $timeZoneOffset SECOND) - INTERVAL {$period} {$this->_getRangeByType($type_code)} )", now() );
00225 $this->_select->group('DATE_FORMAT(add_date, \''.$this->_getGroupByDateFormat($type_code).'\')');
00226 $this->_select->order('add_date ASC');
00227
00228 return $this;
00229 }
00230
00231 protected function _getGroupByDateFormat($type)
00232 {
00233 switch ($type) {
00234 case 'day':
00235 $format = '%Y-%m-%d';
00236 break;
00237 default:
00238 case 'hour':
00239 $format = '%Y-%m-%d %H';
00240 break;
00241 }
00242 return $format;
00243 }
00244
00245 protected function _getRangeByType($type_code)
00246 {
00247 switch ($type_code)
00248 {
00249 case 'day':
00250 $range = 'DAY';
00251 break;
00252 case 'hour':
00253 $range = 'HOUR';
00254 break;
00255 case 'minute':
00256 default:
00257 $range = 'MINUTE';
00258 break;
00259
00260 }
00261
00262 return $range;
00263 }
00264
00265
00266
00267
00268
00269
00270
00271
00272 public function addFieldToFilter($fieldName, $condition=null)
00273 {
00274 if ($fieldName == 'type' && is_array($condition) && isset($condition['eq'])) {
00275 $fieldName = 'customer_id';
00276 if ($condition['eq'] === Mage_Log_Model_Visitor::VISITOR_TYPE_VISITOR) {
00277 $condition = array('null' => 1);
00278 } else {
00279 $condition = array('moreq' => 1);
00280 }
00281 }
00282 return parent::addFieldToFilter($this->_getFieldMap($fieldName), $condition);
00283 }
00284
00285 protected function _getFieldMap($fieldName)
00286 {
00287 if(isset($this->_fieldMap[$fieldName])) {
00288 return $this->_fieldMap[$fieldName];
00289 } else {
00290 return 'visitor_table.' . $fieldName;
00291 }
00292 }
00293
00294 public function load($printQuery = false, $logQuery = false)
00295 {
00296 if ($this->isLoaded()) {
00297 return $this;
00298 }
00299 Mage::dispatchEvent('log_visitor_collection_load_before', array('collection' => $this));
00300 return parent::load($printQuery, $logQuery);
00301 }
00302
00303 public function getIsOnlineFilterUsed()
00304 {
00305 return $this->_isOnlineFilterUsed;
00306 }
00307
00308
00309
00310
00311
00312
00313 public function addVisitorStoreFilter($storeIds)
00314 {
00315 $this->_select->where('visitor_table.store_id IN (?)', $storeIds);
00316 }
00317 }