1<?php 2/** 3 * statistics plugin 4 * 5 * @license GPL 2 (http://www.gnu.org/licenses/gpl.html) 6 * @author Andreas Gohr <gohr@cosmocode.de> 7 */ 8 9// must be run within Dokuwiki 10if(!defined('DOKU_INC')) die(); 11 12if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/'); 13require_once(DOKU_PLUGIN.'admin.php'); 14 15/** 16 * All DokuWiki plugins to extend the admin function 17 * need to inherit from this class 18 */ 19class admin_plugin_statistics extends DokuWiki_Admin_Plugin { 20 var $dblink = null; 21 var $opt = ''; 22 var $from = ''; 23 var $to = ''; 24 var $start = ''; 25 var $tlimit = ''; 26 27 /** 28 * return some info 29 */ 30 function getInfo(){ 31 return confToHash(dirname(__FILE__).'/info.txt'); 32 } 33 34 /** 35 * Access for managers allowed 36 */ 37 function forAdminOnly(){ 38 return false; 39 } 40 41 /** 42 * return sort order for position in admin menu 43 */ 44 function getMenuSort() { 45 return 150; 46 } 47 48 /** 49 * handle user request 50 */ 51 function handle() { 52 $this->opt = preg_replace('/[^a-z]+/','',$_REQUEST['opt']); 53 $this->start = (int) $_REQUEST['s']; 54 $this->setTimeframe($_REQUEST['f'],$_REQUEST['t']); 55 } 56 57 /** 58 * set limit clause 59 */ 60 function setTimeframe($from,$to){ 61 // fixme add better sanity checking here: 62 $from = preg_replace('/[^\d\-]+/','',$from); 63 $to = preg_replace('/[^\d\-]+/','',$to); 64 if(!$from) $from = date('Y-m-d'); 65 if(!$to) $to = date('Y-m-d'); 66 67 //setup limit clause 68 $tlimit = "A.dt >= '$from 00:00:00' AND A.dt <= '$to 23:59:59'"; 69 $this->tlimit = $tlimit; 70 $this->from = $from; 71 $this->to = $to; 72 } 73 74 /** 75 * fixme build statistics here 76 */ 77 function html() { 78 $this->html_toc(); 79 echo '<h1>Access Statistics</h1>'; 80 $this->html_timeselect(); 81 82 switch($this->opt){ 83 case 'country': 84 $this->html_country(); 85 break; 86 case 'page': 87 $this->html_page(); 88 break; 89 case 'browser': 90 $this->html_browser(); 91 break; 92 case 'os': 93 $this->html_os(); 94 break; 95 case 'referer': 96 $this->html_referer(); 97 break; 98 case 'newreferer': 99 $this->html_newreferer(); 100 break; 101 case 'outlinks': 102 $this->html_outlinks(); 103 break; 104 case 'resolution': 105 $this->html_resolution(); 106 break; 107 case 'searchphrases': 108 $this->html_searchphrases(); 109 break; 110 case 'searchwords': 111 $this->html_searchwords(); 112 break; 113 case 'searchengines': 114 $this->html_searchengines(); 115 break; 116 default: 117 $this->html_dashboard(); 118 } 119 } 120 121 function html_toc(){ 122 echo '<div class="toc">'; 123 echo '<div class="tocheader toctoggle" id="toc__header">'; 124 echo 'Detailed Statistics'; 125 echo '</div>'; 126 echo '<div id="toc__inside">'; 127 echo '<ul class="toc">'; 128 129 echo '<li><div class="li">'; 130 echo '<a href="?do=admin&page=statistics&opt=&f='.$this->from.'&t='.$this->to.'">Dashboard</a>'; 131 echo '</div></li>'; 132 133 echo '<li><div class="li">'; 134 echo '<a href="?do=admin&page=statistics&opt=page&f='.$this->from.'&t='.$this->to.'">Pages</a>'; 135 echo '</div></li>'; 136 137 echo '<li><div class="li">'; 138 echo '<a href="?do=admin&page=statistics&opt=referer&f='.$this->from.'&t='.$this->to.'">Incoming Links</a>'; 139 echo '</div></li>'; 140 141 echo '<li><div class="li">'; 142 echo '<a href="?do=admin&page=statistics&opt=newreferer&f='.$this->from.'&t='.$this->to.'">New Incoming Links</a>'; 143 echo '</div></li>'; 144 145 echo '<li><div class="li">'; 146 echo '<a href="?do=admin&page=statistics&opt=outlinks&f='.$this->from.'&t='.$this->to.'">Outgoing Links</a>'; 147 echo '</div></li>'; 148 149 echo '<li><div class="li">'; 150 echo '<a href="?do=admin&page=statistics&opt=searchphrases&f='.$this->from.'&t='.$this->to.'">Search Phrases</a>'; 151 echo '</div></li>'; 152 153 echo '<li><div class="li">'; 154 echo '<a href="?do=admin&page=statistics&opt=searchwords&f='.$this->from.'&t='.$this->to.'">Search Words</a>'; 155 echo '</div></li>'; 156 157 echo '<li><div class="li">'; 158 echo '<a href="?do=admin&page=statistics&opt=searchengines&f='.$this->from.'&t='.$this->to.'">Search Engines</a>'; 159 echo '</div></li>'; 160 161 162 echo '<li><div class="li">'; 163 echo '<a href="?do=admin&page=statistics&opt=browser&f='.$this->from.'&t='.$this->to.'">Browsers</a>'; 164 echo '</div></li>'; 165 166 echo '<li><div class="li">'; 167 echo '<a href="?do=admin&page=statistics&opt=os&f='.$this->from.'&t='.$this->to.'">Operating Systems</a>'; 168 echo '</div></li>'; 169 170 echo '<li><div class="li">'; 171 echo '<a href="?do=admin&page=statistics&opt=country&f='.$this->from.'&t='.$this->to.'">Countries</a>'; 172 echo '</div></li>'; 173 174 echo '<li><div class="li">'; 175 echo '<a href="?do=admin&page=statistics&opt=resolution&f='.$this->from.'&t='.$this->to.'">Resolution</a>'; 176 echo '</div></li>'; 177 178 echo '</ul>'; 179 echo '</div>'; 180 echo '</div>'; 181 } 182 183 function html_pager($limit,$next){ 184 echo '<div class="plg_stats_pager">'; 185 186 if($this->start > 0){ 187 $go = max($this->start - $limit, 0); 188 echo '<a href="?do=admin&page=statistics&opt='.$this->opt.'&f='.$this->from.'&t='.$this->to.'&s='.$go.'" class="prev">previous page</a>'; 189 } 190 191 if($next){ 192 $go = $this->start + $limit; 193 echo '<a href="?do=admin&page=statistics&opt='.$this->opt.'&f='.$this->from.'&t='.$this->to.'&s='.$go.'" class="next">next page</a>'; 194 } 195 echo '</div>'; 196 } 197 198 /** 199 * Print the time selection menu 200 */ 201 function html_timeselect(){ 202 $now = date('Y-m-d'); 203 $yday = date('Y-m-d',time()-(60*60*24)); 204 $week = date('Y-m-d',time()-(60*60*24*7)); 205 $month = date('Y-m-d',time()-(60*60*24*30)); 206 207 echo '<div class="plg_stats_timeselect">'; 208 echo '<span>Select the timeframe:</span>'; 209 echo '<ul>'; 210 211 echo '<li>'; 212 echo '<a href="?do=admin&page=statistics&opt='.$this->opt.'&f='.$now.'&t='.$now.'">'; 213 echo 'today'; 214 echo '</a>'; 215 echo '</li>'; 216 217 echo '<li>'; 218 echo '<a href="?do=admin&page=statistics&opt='.$this->opt.'&f='.$yday.'&t='.$yday.'">'; 219 echo 'yesterday'; 220 echo '</a>'; 221 echo '</li>'; 222 223 echo '<li>'; 224 echo '<a href="?do=admin&page=statistics&opt='.$this->opt.'&f='.$week.'&t='.$now.'">'; 225 echo 'last 7 days'; 226 echo '</a>'; 227 echo '</li>'; 228 229 echo '<li>'; 230 echo '<a href="?do=admin&page=statistics&opt='.$this->opt.'&f='.$month.'&t='.$now.'">'; 231 echo 'last 30 days'; 232 echo '</a>'; 233 echo '</li>'; 234 235 echo '</ul>'; 236 237 238 echo '<form action="" method="get">'; 239 echo '<input type="hidden" name="do" value="admin" />'; 240 echo '<input type="hidden" name="page" value="statistics" />'; 241 echo '<input type="hidden" name="opt" value="'.$this->opt.'" />'; 242 echo '<input type="text" name="f" value="'.$this->from.'" class="edit" />'; 243 echo '<input type="text" name="t" value="'.$this->to.'" class="edit" />'; 244 echo '<input type="submit" value="go" class="button" />'; 245 echo '</form>'; 246 247 echo '</div>'; 248 } 249 250 251 /** 252 * Print an introductionary screen 253 */ 254 function html_dashboard(){ 255 echo '<p>This page gives you a quick overview on what is happening in your Wiki. For detailed lists 256 choose a topic from the list.</p>'; 257 258 259 echo '<div class="plg_stats_dashboard">'; 260 261 // general info 262 echo '<div class="plg_stats_top">'; 263 $result = $this->sql_aggregate($this->tlimit); 264 echo '<ul>'; 265 echo '<li><span>'.$result['pageviews'].'</span> page views </li>'; 266 echo '<li><span>'.$result['sessions'].'</span> visits (sessions) </li>'; 267 echo '<li><span>'.$result['visitors'].'</span> unique visitors </li>'; 268 echo '<li><span>'.$result['users'].'</span> logged in users</li>'; 269 270 echo '</ul>'; 271 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=trend&f='.$this->from.'&t='.$this->to.'" />'; 272 echo '</div>'; 273 274 275 // top pages today 276 echo '<div>'; 277 echo '<h2>Most popular pages</h2>'; 278 $result = $this->sql_pages($this->tlimit,$this->start,15); 279 $this->html_resulttable($result); 280 echo '<a href="?do=admin&page=statistics&opt=page&f='.$this->from.'&t='.$this->to.'" class="more">more</a>'; 281 echo '</div>'; 282 283 // top referer today 284 echo '<div>'; 285 echo '<h2>Newest incoming links</h2>'; 286 $result = $this->sql_newreferer($this->tlimit,$this->start,15); 287 $this->html_resulttable($result); 288 echo '<a href="?do=admin&page=statistics&opt=newreferer&f='.$this->from.'&t='.$this->to.'" class="more">more</a>'; 289 echo '</div>'; 290 291 // top searches today 292 echo '<div>'; 293 echo '<h2>Top search phrases</h2>'; 294 $result = $this->sql_searchphrases($this->tlimit,$this->start,15); 295 $this->html_resulttable($result); 296 echo '<a href="?do=admin&page=statistics&opt=searchphrases&f='.$this->from.'&t='.$this->to.'" class="more">more</a>'; 297 echo '</div>'; 298 299 echo '</div>'; 300 } 301 302 function html_country(){ 303 echo '<div class="plg_stats_full">'; 304 echo '<h2>Visitor\'s Countries</h2>'; 305 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=country&f='.$this->from.'&t='.$this->to.'" />'; 306 $result = $this->sql_countries($this->tlimit,$this->start,150); 307 $this->html_resulttable($result,'',150); 308 echo '</div>'; 309 } 310 311 function html_page(){ 312 echo '<div class="plg_stats_full">'; 313 echo '<h2>Popular Pages</h2>'; 314 $result = $this->sql_pages($this->tlimit,$this->start,150); 315 $this->html_resulttable($result,'',150); 316 echo '</div>'; 317 } 318 319 function html_browser(){ 320 echo '<div class="plg_stats_full">'; 321 echo '<h2>Browser Shootout</h2>'; 322 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=browser&f='.$this->from.'&t='.$this->to.'" />'; 323 $result = $this->sql_browsers($this->tlimit,$this->start,150,true); 324 $this->html_resulttable($result,'',150); 325 echo '</div>'; 326 } 327 328 function html_os(){ 329 echo '<div class="plg_stats_full">'; 330 echo '<h2>Operating Systems</h2>'; 331 $result = $this->sql_os($this->tlimit,$this->start,150,true); 332 $this->html_resulttable($result,'',150); 333 echo '</div>'; 334 } 335 336 function html_referer(){ 337 echo '<div class="plg_stats_full">'; 338 echo '<h2>Incoming Links</h2>'; 339 $result = $this->sql_aggregate($this->tlimit); 340 341 $all = $result['search']+$result['external']+$result['direct']; 342 343 if($all){ 344 printf("<p>Of all %d external visits, %d (%.1f%%) were bookmarked (direct) accesses, 345 %d (%.1f%%) came from search engines and %d (%.1f%%) were referred through 346 links from other pages.</p>",$all,$result['direct'],(100*$result['direct']/$all), 347 $result['search'],(100*$result['search']/$all),$result['external'], 348 (100*$result['external']/$all)); 349 } 350 351 $result = $this->sql_referer($this->tlimit,$this->start,150); 352 $this->html_resulttable($result,'',150); 353 echo '</div>'; 354 } 355 356 function html_newreferer(){ 357 echo '<div class="plg_stats_full">'; 358 echo '<h2>New Incoming Links</h2>'; 359 echo '<p>The following incoming links where first logged in the selected time frame, 360 and have never been seen before.</p>'; 361 362 $result = $this->sql_newreferer($this->tlimit,$this->start,150); 363 $this->html_resulttable($result,'',150); 364 echo '</div>'; 365 } 366 367 function html_outlinks(){ 368 echo '<div class="plg_stats_full">'; 369 echo '<h2>Outgoing Links</h2>'; 370 371 $result = $this->sql_outlinks($this->tlimit,$this->start,150); 372 $this->html_resulttable($result,'',150); 373 echo '</div>'; 374 } 375 376 function html_searchphrases(){ 377 echo '<div class="plg_stats_full">'; 378 echo '<h2>Search Phrases</h2>'; 379 380 $result = $this->sql_searchphrases($this->tlimit,$this->start,150); 381 $this->html_resulttable($result,'',150); 382 echo '</div>'; 383 } 384 385 function html_searchwords(){ 386 echo '<div class="plg_stats_full">'; 387 echo '<h2>Search Words</h2>'; 388 389 $result = $this->sql_searchwords($this->tlimit,$this->start,150); 390 $this->html_resulttable($result,'',150); 391 echo '</div>'; 392 } 393 394 function html_searchengines(){ 395 echo '<div class="plg_stats_full">'; 396 echo '<h2>Search Engines</h2>'; 397 398 $result = $this->sql_searchengines($this->tlimit,$this->start,150); 399 $this->html_resulttable($result,'',150); 400 echo '</div>'; 401 } 402 403 404 function html_resolution(){ 405 echo '<div class="plg_stats_full">'; 406 echo '<h2>Resolution</h2>'; 407 $result = $this->sql_resolution($this->tlimit,$this->start,150); 408 $this->html_resulttable($result,'',150); 409 410 echo '<p>While the data above gives you some info about the resolution your visitors use, it does not tell you 411 much about about the real size of their browser windows. The graphic below shows the size distribution of 412 the view port (document area) of your visitor\'s browsers. Please note that this data can not be logged 413 in all browsers. Because users may resize their browser window while browsing your site the statistics may 414 be flawed. Take it with a grain of salt.</p>'; 415 416 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/img.php?img=view&f='.$this->from.'&t='.$this->to.'" />'; 417 echo '</div>'; 418 } 419 420 421 /** 422 * Display a result in a HTML table 423 */ 424 function html_resulttable($result,$header='',$pager=0){ 425 echo '<table>'; 426 if(is_array($header)){ 427 echo '<tr>'; 428 foreach($header as $h){ 429 echo '<th>'.hsc($h).'</th>'; 430 } 431 echo '</tr>'; 432 } 433 434 $count = 0; 435 if(is_array($result)) foreach($result as $row){ 436 echo '<tr>'; 437 foreach($row as $k => $v){ 438 echo '<td class="plg_stats_X'.$k.'">'; 439 if($k == 'page'){ 440 echo '<a href="'.wl($v).'" class="wikilink1">'; 441 echo hsc($v); 442 echo '</a>'; 443 }elseif($k == 'url'){ 444 $url = hsc($v); 445 $url = preg_replace('/^https?:\/\/(www\.)?/','',$url); 446 if(strlen($url) > 45){ 447 $url = substr($url,0,30).' … '.substr($url,-15); 448 } 449 echo '<a href="'.$v.'" class="urlextern">'; 450 echo $url; 451 echo '</a>'; 452 }elseif($k == 'lookup'){ 453 echo '<a href="http://www.google.com/search?q='.rawurlencode($v).'">'; 454 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/search/google.png" alt="lookup in Google" border="0" />'; 455 echo '</a> '; 456 457 echo '<a href="http://search.yahoo.com/search?p='.rawurlencode($v).'">'; 458 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/search/yahoo.png" alt="lookup in Yahoo" border="0" />'; 459 echo '</a> '; 460 461 echo '<a href="http://search.msn.com/results.aspx?q='.rawurlencode($v).'">'; 462 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/search/msn.png" alt="lookup in MSN Live" border="0" />'; 463 echo '</a> '; 464 465 }elseif($k == 'engine'){ 466 include_once(dirname(__FILE__).'/inc/search_engines.php'); 467 echo $SearchEnginesHashLib[$v]; 468 }elseif($k == 'browser'){ 469 include_once(dirname(__FILE__).'/inc/browsers.php'); 470 echo $BrowsersHashIDLib[$v]; 471 }elseif($k == 'bflag'){ 472 include_once(dirname(__FILE__).'/inc/browsers.php'); 473 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/browser/'.$BrowsersHashIcon[$v].'.png" alt="'.hsc($v).'" />'; 474 }elseif($k == 'os'){ 475 if(empty($v)){ 476 echo 'unknown'; 477 }else{ 478 include_once(dirname(__FILE__).'/inc/operating_systems.php'); 479 echo $OSHashLib[$v]; 480 } 481 }elseif($k == 'osflag'){ 482 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/os/'.hsc($v).'.png" alt="'.hsc($v).'" />'; 483 }elseif($k == 'cflag'){ 484 echo '<img src="'.DOKU_BASE.'lib/plugins/statistics/ico/flags/'.hsc($v).'.png" alt="'.hsc($v).'" width="18" height="12" />'; 485 }elseif($k == 'html'){ 486 echo $v; 487 }else{ 488 echo hsc($v); 489 } 490 echo '</td>'; 491 } 492 echo '</tr>'; 493 494 if($pager && ($count == $pager)) break; 495 $count++; 496 } 497 echo '</table>'; 498 499 if($pager) $this->html_pager($pager,count($result) > $pager); 500 } 501 502 /** 503 * Create an image 504 */ 505 function img_build($img){ 506 include(dirname(__FILE__).'/inc/AGC.class.php'); 507 508 switch($img){ 509 case 'country': 510 // build top countries + other 511 $result = $this->sql_countries($this->tlimit,$this->start,0); 512 $data = array(); 513 $top = 0; 514 foreach($result as $row){ 515 if($top < 7){ 516 $data[$row['country']] = $row['cnt']; 517 }else{ 518 $data['other'] += $row['cnt']; 519 } 520 $top++; 521 } 522 $pie = new AGC(300, 200); 523 $pie->setProp("showkey",true); 524 $pie->setProp("showval",false); 525 $pie->setProp("showgrid",false); 526 $pie->setProp("type","pie"); 527 $pie->setProp("keyinfo",1); 528 $pie->setProp("keysize",8); 529 $pie->setProp("keywidspc",-50); 530 $pie->setProp("key",array_keys($data)); 531 $pie->addBulkPoints(array_values($data)); 532 @$pie->graph(); 533 $pie->showGraph(); 534 break; 535 case 'browser': 536 // build top browsers + other 537 include_once(dirname(__FILE__).'/inc/browsers.php'); 538 539 $result = $this->sql_browsers($this->tlimit,$this->start,0,false); 540 $data = array(); 541 $top = 0; 542 foreach($result as $row){ 543 if($top < 5){ 544 $data[strip_tags($BrowsersHashIDLib[$row['ua_info']])] = $row['cnt']; 545 }else{ 546 $data['other'] += $row['cnt']; 547 } 548 $top++; 549 } 550 $pie = new AGC(300, 200); 551 $pie->setProp("showkey",true); 552 $pie->setProp("showval",false); 553 $pie->setProp("showgrid",false); 554 $pie->setProp("type","pie"); 555 $pie->setProp("keyinfo",1); 556 $pie->setProp("keysize",8); 557 $pie->setProp("keywidspc",-50); 558 $pie->setProp("key",array_keys($data)); 559 $pie->addBulkPoints(array_values($data)); 560 @$pie->graph(); 561 $pie->showGraph(); 562 break; 563 case 'view': 564 565 $graph = new AGC(400, 200); 566 $graph->setColor('color',0,'blue'); 567 $graph->setColor('color',1,'red'); 568 $graph->setProp("showkey",true); 569 $graph->setProp("key",'view port width',0); 570 $graph->setProp("key",'view port height',1); 571 572 $result = $this->sql_viewport($this->tlimit,0,0,true); 573 foreach($result as $row){ 574 $graph->addPoint($row['cnt'],$row['res_x'],0); 575 } 576 577 $result = $this->sql_viewport($this->tlimit,0,0,false); 578 foreach($result as $row){ 579 $graph->addPoint($row['cnt'],$row['res_y'],1); 580 } 581 582 @$graph->graph(); 583 $graph->showGraph(); 584 585 break; 586 case 'trend': 587 $hours = ($this->from == $this->to); 588 $result = $this->sql_trend($this->tlimit,$hours); 589 $data1 = array(); 590 $data2 = array(); 591 592 $graph = new AGC(400, 150); 593 $graph->setProp("type","bar"); 594 $graph->setProp("showgrid",false); 595 $graph->setProp("barwidth",.8); 596 597 $graph->setColor('color',0,'blue'); 598 $graph->setColor('color',1,'red'); 599 $graph->setColor('color',2,'yellow'); 600 601 if($hours){ 602 //preset $hours 603 for($i=0;$i<24;$i++){ 604 $data1[$i] = 0; 605 $data2[$i] = 0; 606 $data3[$i] = 0; 607 $graph->setProp("scale",array(' 0h',' 4h',' 8h',' 12h',' 16h',' 20h',' 24h')); 608 } 609 }else{ 610 $graph->setProp("scale",array(next(array_keys($data1)),$this->to)); 611 } 612 613 foreach($result as $row){ 614 $data1[$row['time']] = $row['pageviews']; 615 $data2[$row['time']] = $row['sessions']; 616 $data3[$row['time']] = $row['visitors']; 617 } 618 619 foreach($data1 as $key => $val){ 620 $graph->addPoint($val,$key,0); 621 } 622 foreach($data2 as $key => $val){ 623 $graph->addPoint($val,$key,1); 624 } 625 foreach($data3 as $key => $val){ 626 $graph->addPoint($val,$key,2); 627 } 628 629 @$graph->graph(); 630 $graph->showGraph(); 631 632 default: 633 $this->sendGIF(); 634 } 635 } 636 637 638 /** 639 * Return some aggregated statistics 640 */ 641 function sql_aggregate($tlimit){ 642 $data = array(); 643 644 $sql = "SELECT ref_type, COUNT(*) as cnt 645 FROM ".$this->getConf('db_prefix')."access as A 646 WHERE $tlimit 647 AND ua_type = 'browser' 648 GROUP BY ref_type"; 649 $result = $this->runSQL($sql); 650 651 if(is_array($result)) foreach($result as $row){ 652 if($row['ref_type'] == 'search') $data['search'] = $row['cnt']; 653 if($row['ref_type'] == 'external') $data['external'] = $row['cnt']; 654 if($row['ref_type'] == 'internal') $data['internal'] = $row['cnt']; 655 if($row['ref_type'] == '') $data['direct'] = $row['cnt']; 656 } 657 658 $sql = "SELECT COUNT(DISTINCT session) as sessions, 659 COUNT(session) as views, 660 COUNT(DISTINCT user) as users, 661 COUNT(DISTINCT uid) as visitors 662 FROM ".$this->getConf('db_prefix')."access as A 663 WHERE $tlimit 664 AND ua_type = 'browser'"; 665 $result = $this->runSQL($sql); 666 667 $data['users'] = max($result[0]['users'] - 1,0); // subtract empty user 668 $data['sessions'] = $result[0]['sessions']; 669 $data['pageviews'] = $result[0]['views']; 670 $data['visitors'] = $result[0]['visitors']; 671 672 $sql = "SELECT COUNT(id) as robots 673 FROM ".$this->getConf('db_prefix')."access as A 674 WHERE $tlimit 675 AND ua_type = 'robot'"; 676 $result = $this->runSQL($sql); 677 $data['robots'] = $result[0]['robots']; 678 679 return $data; 680 } 681 682 /** 683 * standard statistics follow, only accesses made by browsers are counted 684 * for general stats like browser or OS only visitors not pageviews are counted 685 */ 686 function sql_trend($tlimit,$hours=false){ 687 if($hours){ 688 $sql = "SELECT HOUR(dt) as time, 689 COUNT(DISTINCT session) as sessions, 690 COUNT(session) as pageviews, 691 COUNT(DISTINCT uid) as visitors 692 FROM ".$this->getConf('db_prefix')."access as A 693 WHERE $tlimit 694 AND ua_type = 'browser' 695 GROUP BY HOUR(dt) 696 ORDER BY time"; 697 }else{ 698 $sql = "SELECT DATE(dt) as time, 699 COUNT(DISTINCT session) as sessions, 700 COUNT(session) as pageviews, 701 COUNT(DISTINCT uid) as visitors 702 FROM ".$this->getConf('db_prefix')."access as A 703 WHERE $tlimit 704 AND ua_type = 'browser' 705 GROUP BY DATE(dt) 706 ORDER BY time"; 707 } 708 return $this->runSQL($sql); 709 } 710 711 function sql_searchengines($tlimit,$start=0,$limit=20){ 712 $sql = "SELECT COUNT(*) as cnt, engine 713 FROM ".$this->getConf('db_prefix')."search as A 714 WHERE $tlimit 715 GROUP BY engine 716 ORDER BY cnt DESC, engine". 717 $this->sql_limit($start,$limit); 718 return $this->runSQL($sql); 719 } 720 721 function sql_searchphrases($tlimit,$start=0,$limit=20){ 722 $sql = "SELECT COUNT(*) as cnt, query, query as lookup 723 FROM ".$this->getConf('db_prefix')."search as A 724 WHERE $tlimit 725 GROUP BY query 726 ORDER BY cnt DESC, query". 727 $this->sql_limit($start,$limit); 728 return $this->runSQL($sql); 729 } 730 731 function sql_searchwords($tlimit,$start=0,$limit=20){ 732 $sql = "SELECT COUNT(*) as cnt, word, word as lookup 733 FROM ".$this->getConf('db_prefix')."search as A, 734 ".$this->getConf('db_prefix')."searchwords as B 735 WHERE $tlimit 736 AND A.id = B.sid 737 GROUP BY word 738 ORDER BY cnt DESC, word". 739 $this->sql_limit($start,$limit); 740 return $this->runSQL($sql); 741 } 742 743 function sql_outlinks($tlimit,$start=0,$limit=20){ 744 $sql = "SELECT COUNT(*) as cnt, link as url 745 FROM ".$this->getConf('db_prefix')."outlinks as A 746 WHERE $tlimit 747 GROUP BY link 748 ORDER BY cnt DESC, link". 749 $this->sql_limit($start,$limit); 750 return $this->runSQL($sql); 751 } 752 753 function sql_pages($tlimit,$start=0,$limit=20){ 754 $sql = "SELECT COUNT(*) as cnt, page 755 FROM ".$this->getConf('db_prefix')."access as A 756 WHERE $tlimit 757 AND ua_type = 'browser' 758 GROUP BY page 759 ORDER BY cnt DESC, page". 760 $this->sql_limit($start,$limit); 761 return $this->runSQL($sql); 762 } 763 764 function sql_referer($tlimit,$start=0,$limit=20){ 765 $sql = "SELECT COUNT(*) as cnt, ref as url 766 FROM ".$this->getConf('db_prefix')."access as A 767 WHERE $tlimit 768 AND ua_type = 'browser' 769 AND ref_type = 'external' 770 GROUP BY ref_md5 771 ORDER BY cnt DESC, url". 772 $this->sql_limit($start,$limit); 773 return $this->runSQL($sql); 774 } 775 776 function sql_newreferer($tlimit,$start=0,$limit=20){ 777 $sql = "SELECT COUNT(*) as cnt, ref as url 778 FROM ".$this->getConf('db_prefix')."access as B, 779 ".$this->getConf('db_prefix')."refseen as A 780 WHERE $tlimit 781 AND ua_type = 'browser' 782 AND ref_type = 'external' 783 AND A.ref_md5 = B.ref_md5 784 GROUP BY A.ref_md5 785 ORDER BY cnt DESC, url". 786 $this->sql_limit($start,$limit); 787 return $this->runSQL($sql); 788 } 789 790 function sql_countries($tlimit,$start=0,$limit=20){ 791 $sql = "SELECT COUNT(DISTINCT session) as cnt, B.code AS cflag, B.country 792 FROM ".$this->getConf('db_prefix')."access as A, 793 ".$this->getConf('db_prefix')."iplocation as B 794 WHERE $tlimit 795 AND A.ip = B.ip 796 GROUP BY B.country 797 ORDER BY cnt DESC, B.country". 798 $this->sql_limit($start,$limit); 799 return $this->runSQL($sql); 800 } 801 802 function sql_browsers($tlimit,$start=0,$limit=20,$ext=true){ 803 if($ext){ 804 $sel = 'ua_info as bflag, ua_info as browser, ua_ver'; 805 $grp = 'ua_info, ua_ver'; 806 }else{ 807 $grp = 'ua_info'; 808 $sel = 'ua_info'; 809 } 810 811 $sql = "SELECT COUNT(DISTINCT session) as cnt, $sel 812 FROM ".$this->getConf('db_prefix')."access as A 813 WHERE $tlimit 814 AND ua_type = 'browser' 815 GROUP BY $grp 816 ORDER BY cnt DESC, ua_info". 817 $this->sql_limit($start,$limit); 818 return $this->runSQL($sql); 819 } 820 821 function sql_os($tlimit,$start=0,$limit=20){ 822 $sql = "SELECT COUNT(DISTINCT session) as cnt, os as osflag, os 823 FROM ".$this->getConf('db_prefix')."access as A 824 WHERE $tlimit 825 AND ua_type = 'browser' 826 GROUP BY os 827 ORDER BY cnt DESC, os". 828 $this->sql_limit($start,$limit); 829 return $this->runSQL($sql); 830 } 831 832 function sql_resolution($tlimit,$start=0,$limit=20){ 833 $sql = "SELECT COUNT(DISTINCT session) as cnt, CONCAT(screen_x,'x',screen_y) as res 834 FROM ".$this->getConf('db_prefix')."access as A 835 WHERE $tlimit 836 AND ua_type = 'browser' 837 AND screen_x != 0 838 GROUP BY screen_x, screen_y 839 ORDER BY cnt DESC, screen_x". 840 $this->sql_limit($start,$limit); 841 return $this->runSQL($sql); 842 } 843 844 function sql_viewport($tlimit,$start=0,$limit=20,$x=true){ 845 if($x){ 846 $col = 'view_x'; 847 $res = 'res_x'; 848 }else{ 849 $col = 'view_y'; 850 $res = 'res_y'; 851 } 852 853 $sql = "SELECT COUNT(*) as cnt, 854 ROUND($col/10)*10 as $res 855 FROM ".$this->getConf('db_prefix')."access as A 856 WHERE $tlimit 857 AND ua_type = 'browser' 858 AND $col != 0 859 GROUP BY $res 860 ORDER BY cnt DESC, $res". 861 $this->sql_limit($start,$limit); 862 return $this->runSQL($sql); 863 } 864 865 866 /** 867 * Builds a limit clause 868 */ 869 function sql_limit($start,$limit){ 870 $start = (int) $start; 871 $limit = (int) $limit; 872 if($limit){ 873 $limit += 1; 874 return " LIMIT $start,$limit"; 875 }elseif($start){ 876 return " OFFSET $start"; 877 } 878 return ''; 879 } 880 881 /** 882 * Return a link to the DB, opening the connection if needed 883 */ 884 function dbLink(){ 885 // connect to DB if needed 886 if(!$this->dblink){ 887 $this->dblink = mysql_connect($this->getConf('db_server'), 888 $this->getConf('db_user'), 889 $this->getConf('db_password')); 890 if(!$this->dblink){ 891 msg('DB Error: connection failed',-1); 892 return null; 893 } 894 // set utf-8 895 if(!mysql_db_query($this->getConf('db_database'),'set names utf8',$this->dblink)){ 896 msg('DB Error: could not set UTF-8 ('.mysql_error($this->dblink).')',-1); 897 return null; 898 } 899 } 900 return $this->dblink; 901 } 902 903 /** 904 * Simple function to run a DB query 905 */ 906 function runSQL($sql_string) { 907 $link = $this->dbLink(); 908 909 $result = mysql_db_query($this->conf['db_database'],$sql_string,$link); 910 if(!$result){ 911 msg('DB Error: '.mysql_error($link).' '.hsc($sql_string),-1); 912 return null; 913 } 914 915 $resultarray = array(); 916 917 //mysql_db_query returns 1 on a insert statement -> no need to ask for results 918 if ($result != 1) { 919 for($i=0; $i< mysql_num_rows($result); $i++) { 920 $temparray = mysql_fetch_assoc($result); 921 $resultarray[]=$temparray; 922 } 923 mysql_free_result($result); 924 } 925 926 if (mysql_insert_id($link)) { 927 $resultarray = mysql_insert_id($link); //give back ID on insert 928 } 929 930 return $resultarray; 931 } 932 933 /** 934 * Returns a short name for a User Agent and sets type, version and os info 935 */ 936 function ua_info($ua,&$type,&$ver,&$os){ 937 $ua = strtr($ua,' +','__'); 938 $ua = strtolower($ua); 939 940 // common browsers 941 $regvermsie = '/msie([+_ ]|)([\d\.]*)/i'; 942 $regvernetscape = '/netscape.?\/([\d\.]*)/i'; 943 $regverfirefox = '/firefox\/([\d\.]*)/i'; 944 $regversvn = '/svn\/([\d\.]*)/i'; 945 $regvermozilla = '/mozilla(\/|)([\d\.]*)/i'; 946 $regnotie = '/webtv|omniweb|opera/i'; 947 $regnotnetscape = '/gecko|compatible|opera|galeon|safari/i'; 948 949 $name = ''; 950 # IE ? 951 if(preg_match($regvermsie,$ua,$m) && !preg_match($regnotie,$ua)){ 952 $type = 'browser'; 953 $ver = $m[2]; 954 $name = 'msie'; 955 } 956 # Firefox ? 957 elseif (preg_match($regverfirefox,$ua,$m)){ 958 $type = 'browser'; 959 $ver = $m[1]; 960 $name = 'firefox'; 961 } 962 # Subversion ? 963 elseif (preg_match($regversvn,$ua,$m)){ 964 $type = 'rcs'; 965 $ver = $m[1]; 966 $name = 'svn'; 967 } 968 # Netscape 6.x, 7.x ... ? 969 elseif (preg_match($regvernetscape,$ua,$m)){ 970 $type = 'browser'; 971 $ver = $m[1]; 972 $name = 'netscape'; 973 } 974 # Netscape 3.x, 4.x ... ? 975 elseif(preg_match($regvermozilla,$ua,$m) && !preg_match($regnotnetscape,$ua)){ 976 $type = 'browser'; 977 $ver = $m[2]; 978 $name = 'netscape'; 979 }else{ 980 include(dirname(__FILE__).'/inc/browsers.php'); 981 foreach($BrowsersSearchIDOrder as $regex){ 982 if(preg_match('/'.$regex.'/',$ua)){ 983 // it's a browser! 984 $type = 'browser'; 985 $name = strtolower($regex); 986 break; 987 } 988 } 989 } 990 991 // check versions for Safari and Opera 992 if($name == 'safari'){ 993 if(preg_match('/safari\/([\d\.]*)/i',$ua,$match)){ 994 $ver = $BrowsersSafariBuildToVersionHash[$match[1]]; 995 } 996 }elseif($name == 'opera'){ 997 if(preg_match('/opera[\/ ]([\d\.]*)/i',$ua,$match)){ 998 $ver = $match[1]; 999 } 1000 } 1001 1002 1003 // check OS for browsers 1004 if($type == 'browser'){ 1005 include(dirname(__FILE__).'/inc/operating_systems.php'); 1006 foreach($OSSearchIDOrder as $regex){ 1007 if(preg_match('/'.$regex.'/',$ua)){ 1008 $os = $OSHashID[$regex]; 1009 break; 1010 } 1011 } 1012 1013 } 1014 1015 // are we done now? 1016 if($name) return $name; 1017 1018 include(dirname(__FILE__).'/inc/robots.php'); 1019 foreach($RobotsSearchIDOrder as $regex){ 1020 if(preg_match('/'.$regex.'/',$ua)){ 1021 // it's a robot! 1022 $type = 'robot'; 1023 return strtolower($regex); 1024 } 1025 } 1026 1027 // dunno 1028 return ''; 1029 } 1030 1031 /** 1032 * Log search queries 1033 */ 1034 function log_search($referer,&$type){ 1035 $referer = strtolower($referer); 1036 $ref = strtr($referer,' +','__'); 1037 1038 include(dirname(__FILE__).'/inc/search_engines.php'); 1039 1040 foreach($SearchEnginesSearchIDOrder as $regex){ 1041 if(preg_match('/'.$regex.'/',$ref)){ 1042 if(!$NotSearchEnginesKeys[$regex] || 1043 !preg_match('/'.$NotSearchEnginesKeys[$regex].'/',$ref)){ 1044 // it's a search engine! 1045 $type = 'search'; 1046 break; 1047 } 1048 } 1049 } 1050 if($type != 'search') return; // we're done here 1051 1052 // extract query 1053 $engine = $SearchEnginesHashID[$regex]; 1054 $param = $SearchEnginesKnownUrl[$engine]; 1055 if($param && preg_match('/'.$param.'(.*?)[&$]/',$referer,$match)){ 1056 $query = array_pop($match); 1057 }elseif(preg_match('/'.$WordsToExtractSearchUrl.'(.*?)[&$]/',$referer,$match)){ 1058 $query = array_pop($match); 1059 } 1060 if(!$query) return; // we failed 1061 1062 // clean the query 1063 $query = preg_replace('/^(cache|related):[^\+]+/','',$query); // non-search queries 1064 $query = preg_replace('/%0[ad]/',' ',$query); // LF CR 1065 $query = preg_replace('/%2[02789abc]/',' ',$query); // space " ' ( ) * + , 1066 $query = preg_replace('/%3a/',' ',$query); // : 1067 $query = strtr($query,'+\'()"*,:',' '); // badly encoded 1068 $query = preg_replace('/ +/',' ',$query); // ws compact 1069 $query = trim($query); 1070 $query = urldecode($query); 1071 if(!utf8_check($query)) $query = utf8_encode($query); // assume latin1 if not utf8 1072 $query = utf8_strtolower($query); 1073 1074 // log it! 1075 $page = addslashes($_REQUEST['p']); 1076 $query = addslashes($query); 1077 $sql = "INSERT INTO ".$this->getConf('db_prefix')."search 1078 SET dt = NOW(), 1079 page = '$page', 1080 query = '$query', 1081 engine = '$engine'"; 1082 $id = $this->runSQL($sql); 1083 if(is_null($id)){ 1084 global $MSG; 1085 print_r($MSG); 1086 return; 1087 } 1088 1089 // log single keywords 1090 $words = explode(' ',utf8_stripspecials($query,' ','\._\-:\*')); 1091 foreach($words as $word){ 1092 if(!$word) continue; 1093 $word = addslashes($word); 1094 $sql = "INSERT DELAYED INTO ".$this->getConf('db_prefix')."searchwords 1095 SET sid = $id, 1096 word = '$word'"; 1097 $ok = $this->runSQL($sql); 1098 if(is_null($ok)){ 1099 global $MSG; 1100 print_r($MSG); 1101 } 1102 } 1103 } 1104 1105 /** 1106 * Resolve IP to country/city 1107 */ 1108 function log_ip($ip){ 1109 // check if IP already known and up-to-date 1110 $sql = "SELECT ip 1111 FROM ".$this->getConf('db_prefix')."iplocation 1112 WHERE ip ='".addslashes($ip)."' 1113 AND lastupd > DATE_SUB(CURDATE(),INTERVAL 30 DAY)"; 1114 $result = $this->runSQL($sql); 1115 if($result[0]['ip']) return; 1116 1117 $http = new DokuHTTPClient(); 1118 $http->timeout = 10; 1119 $data = $http->get('http://api.hostip.info/get_html.php?ip='.$ip); 1120 1121 if(preg_match('/^Country: (.*?) \((.*?)\)\nCity: (.*?)$/s',$data,$match)){ 1122 $country = addslashes(trim($match[1])); 1123 $code = addslashes(strtolower(trim($match[2]))); 1124 $city = addslashes(trim($match[3])); 1125 $host = addslashes(gethostbyaddr($ip)); 1126 $ip = addslashes($ip); 1127 1128 $sql = "REPLACE INTO ".$this->getConf('db_prefix')."iplocation 1129 SET ip = '$ip', 1130 country = '$country', 1131 code = '$code', 1132 city = '$city', 1133 host = '$host'"; 1134 $this->runSQL($sql); 1135 } 1136 } 1137 1138 /** 1139 * log a click on an external link 1140 * 1141 * called from log.php 1142 */ 1143 function log_outgoing(){ 1144 if(!$_REQUEST['ol']) return; 1145 1146 $link_md5 = md5($link); 1147 $link = addslashes($_REQUEST['ol']); 1148 $session = addslashes(session_id()); 1149 $page = addslashes($_REQUEST['p']); 1150 1151 $sql = "INSERT DELAYED INTO ".$this->getConf('db_prefix')."outlinks 1152 SET dt = NOW(), 1153 session = '$session', 1154 page = '$page', 1155 link_md5 = '$link_md5', 1156 link = '$link'"; 1157 $ok = $this->runSQL($sql); 1158 if(is_null($ok)){ 1159 global $MSG; 1160 print_r($MSG); 1161 } 1162 } 1163 1164 /** 1165 * log a page access 1166 * 1167 * called from log.php 1168 */ 1169 function log_access(){ 1170 if(!$_REQUEST['p']) return; 1171 1172 # FIXME check referer against blacklist and drop logging for bad boys 1173 1174 // handle referer 1175 $referer = trim($_REQUEST['r']); 1176 if($referer){ 1177 $ref = addslashes($referer); 1178 $ref_md5 = ($ref) ? md5($referer) : ''; 1179 if(strpos($referer,DOKU_URL) === 0){ 1180 $ref_type = 'internal'; 1181 }else{ 1182 $ref_type = 'external'; 1183 $this->log_search($referer,$ref_type); 1184 } 1185 }else{ 1186 $ref = ''; 1187 $ref_md5 = ''; 1188 $ref_type = ''; 1189 } 1190 1191 // handle user agent 1192 $agent = trim($_SERVER['HTTP_USER_AGENT']); 1193 1194 $ua = addslashes($agent); 1195 $ua_type = ''; 1196 $ua_ver = ''; 1197 $os = ''; 1198 $ua_info = addslashes($this->ua_info($agent,$ua_type,$ua_ver,$os)); 1199 1200 $page = addslashes($_REQUEST['p']); 1201 $ip = addslashes($_SERVER['REMOTE_ADDR']); 1202 $sx = (int) $_REQUEST['sx']; 1203 $sy = (int) $_REQUEST['sy']; 1204 $vx = (int) $_REQUEST['vx']; 1205 $vy = (int) $_REQUEST['vy']; 1206 $js = (int) $_REQUEST['js']; 1207 $uid = addslashes($_REQUEST['uid']); 1208 $user = addslashes($_SERVER['REMOTE_USER']); 1209 $session = addslashes(session_id()); 1210 if(!$uid) $uid = $session; 1211 1212 $sql = "INSERT DELAYED INTO ".$this->getConf('db_prefix')."access 1213 SET dt = NOW(), 1214 page = '$page', 1215 ip = '$ip', 1216 ua = '$ua', 1217 ua_info = '$ua_info', 1218 ua_type = '$ua_type', 1219 ua_ver = '$ua_ver', 1220 os = '$os', 1221 ref = '$ref', 1222 ref_md5 = '$ref_md5', 1223 ref_type = '$ref_type', 1224 screen_x = '$sx', 1225 screen_y = '$sy', 1226 view_x = '$vx', 1227 view_y = '$vy', 1228 js = '$js', 1229 user = '$user', 1230 session = '$session', 1231 uid = '$uid'"; 1232 $ok = $this->runSQL($sql); 1233 if(is_null($ok)){ 1234 global $MSG; 1235 print_r($MSG); 1236 } 1237 1238 $sql = "INSERT DELAYED IGNORE INTO ".$this->getConf('db_prefix')."refseen 1239 SET ref_md5 = '$ref_md5', 1240 dt = NOW()"; 1241 $ok = $this->runSQL($sql); 1242 if(is_null($ok)){ 1243 global $MSG; 1244 print_r($MSG); 1245 } 1246 1247 // resolve the IP 1248 $this->log_ip($_SERVER['REMOTE_ADDR']); 1249 } 1250 1251 /** 1252 * Just send a 1x1 pixel blank gif to the browser 1253 * 1254 * @called from log.php 1255 * 1256 * @author Andreas Gohr <andi@splitbrain.org> 1257 * @author Harry Fuecks <fuecks@gmail.com> 1258 */ 1259 function sendGIF(){ 1260 $img = base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAEALAAAAAABAAEAAAIBTAA7'); 1261 header('Content-Type: image/gif'); 1262 header('Content-Length: '.strlen($img)); 1263 header('Connection: Close'); 1264 print $img; 1265 flush(); 1266 // Browser should drop connection after this 1267 // Thinks it's got the whole image 1268 } 1269 1270} 1271
