1<?php 2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project 3// 4// All Rights Reserved. See copyright.txt for details and a complete list of authors. 5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details. 6// $Id$ 7 8//this script may only be included - so its better to die if called directly. 9if (strpos($_SERVER["SCRIPT_NAME"], basename(__FILE__)) !== false) { 10 header("location: index.php"); 11 exit; 12} 13 14/** 15 * 16 */ 17class StatsLib extends TikiLib 18{ 19 /** 20 * Check if the prerequisites for recording a statistics hit are fulfilled 21 */ 22 public static function is_stats_hit() 23 { 24 global $prefs, $user; 25 return $prefs['feature_stats'] === 'y' && ( $prefs['count_admin_pvs'] === 'y' || $user != 'admin' ); 26 } 27 28 // obsolete, but keeped for compatibility purposes 29 // use Tikilib::list_pages() instead 30 /** 31 * @param int $offset 32 * @param $maxRecords 33 * @param string $sort_mode 34 * @param string $find 35 * @param bool $onlyCant 36 * @return array 37 */ 38 public function list_orphan_pages($offset = 0, $maxRecords = -1, $sort_mode = 'pageName_desc', $find = '', $onlyCant = false) 39 { 40 return $this->list_pages($offset, $maxRecords, $sort_mode, $find, '', true, true, true, true, false, '', $onlyCant); 41 } 42 43 /** 44 * @return array 45 */ 46 public function wiki_stats() 47 { 48 $stats = []; 49 50 $stats["pages"] = $this->getOne("select count(*) from `tiki_pages`", []); 51 $stats["versions"] = $this->getOne("select count(*) from `tiki_history`", []); 52 53 if ($stats["pages"]) { 54 $stats["vpp"] = $stats["versions"] / $stats["pages"]; 55 } else { 56 $stats["vpp"] = 0; 57 } 58 $stats["visits"] = $this->getOne("select sum(`hits`) from `tiki_pages`", []); 59 $or = $this->list_orphan_pages(0, -1, 'pageName_desc', '', true); 60 $stats["orphan"] = $or["cant"]; 61 $links = $this->getOne("select count(*) from `tiki_links`", []); 62 63 if ($stats["pages"]) { 64 $stats["lpp"] = $links / $stats["pages"]; 65 } else { 66 $stats["lpp"] = 0; 67 } 68 $stats["size"] = $this->getOne("select sum(`page_size`) from `tiki_pages`", []); 69 70 if ($stats["pages"]) { 71 $stats["bpp"] = $stats["size"] / $stats["pages"]; 72 } else { 73 $stats["bpp"] = 0; 74 } 75 $stats["size"] = $stats["size"] / 1000000; 76 return $stats; 77 } 78 79 /** 80 * @return array 81 */ 82 public function quiz_stats() 83 { 84 TikiLib::lib('quiz')->compute_quiz_stats(); 85 86 $stats = []; 87 $stats["quizzes"] = $this->getOne("select count(*) from `tiki_quizzes`", []); 88 $stats["questions"] = $this->getOne("select count(*) from `tiki_quiz_questions`", []); 89 if ($stats["quizzes"]) { 90 $stats["qpq"] = $stats["questions"] / $stats["quizzes"]; 91 } else { 92 $stats["qpq"] = 0; 93 } 94 $stats["visits"] = $this->getOne("select sum(`timesTaken`) from `tiki_quiz_stats_sum`", []); 95 $stats["avg"] = $this->getOne("select avg(`avgavg`) from `tiki_quiz_stats_sum`", []); 96 $stats["avgtime"] = $this->getOne("select avg(`avgtime`) from `tiki_quiz_stats_sum`", []); 97 return $stats; 98 } 99 100 /** 101 * @return array 102 */ 103 public function image_gal_stats() 104 { 105 $stats = []; 106 $stats["galleries"] = $this->getOne("select count(*) from `tiki_galleries`", []); 107 $stats["images"] = $this->getOne("select count(*) from `tiki_images`", []); 108 $stats["ipg"] = ($stats["galleries"] ? $stats["images"] / $stats["galleries"] : 0); 109 $stats["size"] = $this->getOne("select sum(`filesize`) from `tiki_images_data` where `type`=?", ['o']); 110 $stats["bpi"] = ($stats["images"] ? $stats["size"] / $stats["images"] : 0); 111 $stats["size"] = $stats["size"] / 1000000; 112 $stats["visits"] = $this->getOne("select sum(`hits`) from `tiki_galleries`", []); 113 return $stats; 114 } 115 116 /** 117 * @return array 118 */ 119 public function file_gal_stats() 120 { 121 $stats = []; 122 $stats["galleries"] = $this->getOne("select count(*) from `tiki_file_galleries`", []); 123 $stats["files"] = $this->getOne("select count(*) from `tiki_files`", []); 124 $stats["fpg"] = ($stats["galleries"] ? $stats["files"] / $stats["galleries"] : 0); 125 $stats["size"] = $this->getOne("select sum(`filesize`) from `tiki_files`", []); 126 $stats["size"] = $stats["size"] / 1000000; 127 $stats["bpf"] = ($stats["files"] ? $stats["size"] / $stats["files"] : 0); 128 $stats["visits"] = $this->getOne("select sum(`hits`) from `tiki_file_galleries`", []); 129 $stats["hits"] = $this->getOne("select sum(`hits`) from `tiki_files`", []); 130 return $stats; 131 } 132 133 /** 134 * @return array 135 */ 136 public function cms_stats() 137 { 138 $stats = []; 139 140 $stats["articles"] = $this->getOne("select count(*) from `tiki_articles`", []); 141 $stats["reads"] = $this->getOne("select sum(`nbreads`) from `tiki_articles`", []); 142 $stats["rpa"] = ($stats["articles"] ? $stats["reads"] / $stats["articles"] : 0); 143 $stats["size"] = $this->getOne("select sum(`size`) from `tiki_articles`", []); 144 $stats["bpa"] = ($stats["articles"] ? $stats["size"] / $stats["articles"] : 0); 145 $stats["topics"] = $this->getOne("select count(*) from `tiki_topics` where `active`=?", ['y']); 146 return $stats; 147 } 148 149 /** 150 * @return array 151 */ 152 public function forum_stats() 153 { 154 $stats = []; 155 $stats["forums"] = $this->getOne("select count(*) from `tiki_forums`", []); 156 $stats["topics"] = $this->getOne( 157 "select count(*) from `tiki_comments`,`tiki_forums`" . 158 " where `object`=`forumId` and `objectType`=? and `parentId`=?", 159 ['forum',0] 160 ); 161 $stats["threads"] = $this->getOne( 162 "select count(*) from `tiki_comments`,`tiki_forums`" . 163 " where `object`=`forumId` and `objectType`=? and `parentId`<>?", 164 ['forum',0] 165 ); 166 $stats["tpf"] = ($stats["forums"] ? $stats["topics"] / $stats["forums"] : 0); 167 $stats["tpt"] = ($stats["topics"] ? $stats["threads"] / $stats["topics"] : 0); 168 $stats["visits"] = $this->getOne("select sum(`hits`) from `tiki_forums`", []); 169 return $stats; 170 } 171 172 /** 173 * @return array 174 */ 175 public function blog_stats() 176 { 177 $stats = []; 178 $stats["blogs"] = $this->getOne("select count(*) from `tiki_blogs`", []); 179 $stats["posts"] = $this->getOne("select count(*) from `tiki_blog_posts`", []); 180 $stats["ppb"] = ($stats["blogs"] ? $stats["posts"] / $stats["blogs"] : 0); 181 $stats["size"] = $this->getOne("select sum(`data_size`) from `tiki_blog_posts`", []); 182 $stats["bpp"] = ($stats["posts"] ? $stats["size"] / $stats["posts"] : 0); 183 $stats["visits"] = $this->getOne("select sum(`hits`) from `tiki_blogs`", []); 184 return $stats; 185 } 186 187 /** 188 * @return array 189 */ 190 public function poll_stats() 191 { 192 $stats = []; 193 $stats["polls"] = $this->getOne("select count(*) from `tiki_polls`", []); 194 $stats["votes"] = $this->getOne("select sum(`votes`) from `tiki_poll_options`", []); 195 $stats["vpp"] = ($stats["polls"] ? $stats["votes"] / $stats["polls"] : 0); 196 return $stats; 197 } 198 199 /** 200 * @return array 201 */ 202 public function faq_stats() 203 { 204 $stats = []; 205 $stats["faqs"] = $this->getOne("select count(*) from `tiki_faqs`", []); 206 $stats["questions"] = $this->getOne("select count(*) from `tiki_faq_questions`", []); 207 $stats["qpf"] = ($stats["faqs"] ? $stats["questions"] / $stats["faqs"] : 0); 208 return $stats; 209 } 210 211 /** 212 * @return array 213 */ 214 public function user_stats() 215 { 216 $stats = []; 217 $stats["users"] = $this->getOne("select count(*) from `users_users`", []); 218 $stats["bookmarks"] = $this->getOne("select count(*) from `tiki_user_bookmarks_urls`", []); 219 $stats["bpu"] = ($stats["users"] ? $stats["bookmarks"] / $stats["users"] : 0); 220 return $stats; 221 } 222 223 /** 224 * @return array 225 */ 226 public function site_stats() 227 { 228 $tikilib = TikiLib::lib('tiki'); 229 $stats = []; 230 $rows = $this->getOne("select count(*) from `tiki_pageviews`", []); 231 232 if ($rows > 0) { 233 //get max pageview number 234 //sum by day as there are sometimes multiple unixstamps per day 235 $max = $this->fetchAll( 236 "SELECT SUM(`pageviews`) AS views, `day` AS unixtime" . 237 " FROM `tiki_pageviews`" . 238 " GROUP BY FROM_UNIXTIME(`day`, '%Y-%m-%d'), day" . 239 " ORDER BY views DESC" . 240 " LIMIT 1" 241 ); 242 $maxvar = $max[0]['views']; 243 244 //get min pageview number 245 $min = $this->fetchAll( 246 "SELECT SUM(`pageviews`) AS views, `day` AS unixtime" . 247 " FROM `tiki_pageviews`" . 248 " GROUP BY FROM_UNIXTIME(`day`, '%Y-%m-%d'), day" . 249 " ORDER BY views ASC" . 250 " LIMIT 1" 251 ); 252 $minvar = $min[0]['views']; 253 254 //pull all dates with max or min because there may be more than one for each 255 $views = $this->fetchAll( 256 "SELECT SUM(`pageviews`) AS views, FROM_UNIXTIME(`day`, '%Y-%m-%d') AS date, `day` AS unixtime" . 257 " FROM `tiki_pageviews`" . 258 " GROUP BY FROM_UNIXTIME(`day`, '%Y-%m-%d'), day" . 259 " HAVING SUM(`pageviews`) = '$maxvar' OR SUM(`pageviews`) = '$minvar'" . 260 " ORDER BY date ASC" 261 ); 262 263 $start = $this->getOne("select min(`day`) from `tiki_pageviews`", []); 264 $stats['started'] = $start; 265 $stats['days'] = floor(($tikilib->now - $start) / 86400); 266 $stats['pageviews'] = $this->getOne("select sum(`pageviews`) from `tiki_pageviews`"); 267 $stats['ppd'] = sprintf("%.2f", ($stats['days'] ? $stats['pageviews'] / $stats['days'] : 0)); 268 $b = 0; 269 $w = 0; 270 //for each in case there's more than one max day and more than one min day 271 foreach ($views as $view) { 272 if ($view['views'] == $maxvar) { 273 $stats['bestday'] .= $tikilib->get_long_date($view['unixtime']) . ' (' . $maxvar . ' ' . tra('pvs') . ')<br />'; 274 $b > 0 ? $stats['bestdesc'] = tra('Days with the most pageviews') : $stats['bestdesc'] = tra('Day with the most pageviews'); 275 $b++; 276 } 277 if ($view['views'] == $minvar) { 278 $stats['worstday'] .= $tikilib->get_long_date($view['unixtime']) . ' (' . $minvar . ' ' . tra('pvs') . ')<br />'; 279 $w > 0 ? $stats['worstdesc'] = tra('Days with the fewest pageviews') : $stats['worstdesc'] = tra('Day with the fewest pageviews'); 280 $w++; 281 } 282 } 283 } else { 284 $stats['started'] = tra('No pageviews yet'); 285 $stats['days'] = tra('n/a'); 286 $stats['pageviews'] = tra('n/a'); 287 $stats['ppd'] = tra('n/a'); 288 $stats['bestpvs'] = tra('n/a'); 289 $stats['bestday'] = tra('n/a'); 290 $stats['worstpvs'] = tra('n/a'); 291 $stats['worstday'] = tra('n/a'); 292 } 293 return $stats; 294 } 295 296 /** 297 * @param $object 298 * @param $type 299 * @param null $id 300 * @return bool 301 */ 302 public function stats_hit($object, $type, $id = null) 303 { 304 if (empty($object) || empty($type) || ! StatsLib::is_stats_hit()) { 305 return false; 306 } 307 308 list($month, $day, $year) = explode(',', $this->date_format("%m,%d,%Y")); 309 $dayzero = $this->make_time(0, 0, 0, $month, $day, $year); 310 311 if (! is_null($id)) { 312 $object = $id . "?" . $object; 313 } 314 315 $cant = $this->getOne( 316 "select count(*) from `tiki_stats` where `object`=? and `type`=? and `day`=?", 317 [$object, $type, (int) $dayzero] 318 ); 319 320 if ($cant) { 321 $query = "update `tiki_stats` set `hits`=`hits`+1 where `object`=? and `type`=? and `day`=?"; 322 } else { 323 $query = "insert into `tiki_stats` (`object`,`type`,`day`,`hits`) values(?,?,?,1)"; 324 } 325 326 return $this->query($query, [$object, $type, (int) $dayzero], -1, -1, false); 327 } 328 329 /** 330 * @param int $max 331 * @param int $days 332 * @param int $startDate 333 * @param int $endDate 334 * @return array 335 */ 336 public function best_overall_object_stats($max = 20, $days = 0, $startDate = 0, $endDate = 0) 337 { 338 $stats = []; 339 $bindvars = []; 340 if ($days != 0) { 341 $mid = "WHERE `day` >= ?"; 342 $bindvars[] = $this->make_time( 343 0, 344 0, 345 0, 346 $this->date_format("%m"), 347 $this->date_format("%d") - $days, 348 $this->date_format("%Y") 349 ); 350 } else { 351 $mid = "WHERE `day` <> 'NULL' "; 352 } 353 354 if ($startDate) { 355 $mid .= " and `day` > '" . $startDate . "' "; 356 } 357 if ($endDate) { 358 $mid .= " and `day` < '" . $endDate . "' "; 359 } 360 361 $query = "SELECT `object`, `type`, sum(`hits`) AS `hits` FROM `tiki_stats` " . 362 $mid . 363 " GROUP BY `object`,`type` ORDER BY `hits` DESC"; 364 $result = $this->query($query, $bindvars, $max, 0); 365 $i = 0; 366 367 while ($res = $result->fetchRow()) { 368 if (strpos($res["object"], "?")) { 369 list($stats[$i]->ID,$stats[$i]->object) = explode("?", $res["object"], 2); 370 } else { 371 $stats[$i]->object = $res["object"]; 372 $stats[$i]->ID = $res["object"]; 373 } 374 $stats[$i]->type = $res["type"]; 375 $stats[$i]->hits = $res["hits"]; 376 $i++; 377 } 378 return $stats; 379 } 380 381 /** 382 * @param $object 383 * @param $type 384 * @param int $days 385 * @param int $startDate 386 * @param int $endDate 387 * @return mixed 388 */ 389 public function object_hits($object, $type, $days = 0, $startDate = 0, $endDate = 0) 390 { 391 $bindvars = [$object, $type]; 392 if ($days != 0) { 393 $mid = "AND `day` >= ? "; 394 $bindvars[] = $this->make_time( 395 0, 396 0, 397 0, 398 $this->date_format("%m"), 399 $this->date_format("%d") - $days, 400 $this->date_format("%Y") 401 ); 402 } else { 403 $mid = ''; 404 } 405 406 if ($startDate) { 407 $mid .= " and `day` > '" . $startDate . "' "; 408 } 409 if ($endDate) { 410 $mid .= " and `day` < '" . $endDate . "' "; 411 } 412 413 $query_cant = "SELECT sum(`hits`) AS `hits` FROM `tiki_stats` WHERE `object`=? AND `type`=? " . 414 $mid . 415 " GROUP BY `object`,`type`"; 416 $cant = $this->getOne($query_cant, $bindvars); 417 return $cant; 418 } 419 420 /** 421 * @param int $days 422 * @return array 423 */ 424 public function get_daily_usage_chart_data($days = 30) 425 { 426 $bindvars = []; 427 428 if ($days != 0) { 429 $mid = "WHERE `day` >= ? "; 430 $bindvars[] = $this->make_time( 431 0, 432 0, 433 0, 434 $this->date_format("%m"), 435 $this->date_format("%d") - $days, 436 $this->date_format("%Y") 437 ); 438 } else { 439 $mid = ""; 440 } 441 442 $query = "SELECT `day`,sum(`hits`) AS `hits` FROM `tiki_stats` " . $mid . " GROUP BY `day`"; 443 $result = $this->query($query, $bindvars, -1, 0); 444 $data = []; 445 446 while ($res = $result->fetchRow()) { 447 $data['xdata'][] = $this->date_format("%Y/%m/%d", $res['day']); 448 $data['ydata'][] = $res['hits']; 449 } 450 451 return $data; 452 } 453 454 /** 455 * Transform a last period to a 2 dates 456 * 457 */ 458 public function period2dates($when) 459 { 460 global $prefs; 461 $tikilib = TikiLib::lib('tiki'); 462 $now = $tikilib->now; 463 $sec = TikiLib::date_format("%s", $now); 464 $min = TikiLib::date_format("%i", $now); 465 $hour = TikiLib::date_format("%H", $now); 466 $day = TikiLib::date_format("%d", $now); 467 $month = TikiLib::date_format("%m", $now); 468 $year = TikiLib::date_format("%Y", $now); 469 switch ($when) { 470 case 'lasthour': 471 $begin = $now - 60 * 60; 472 break; 473 474 case 'day': 475 $begin = TikiLib::make_time(0, 0, 0, $month, $day, $year); 476 break; 477 478 case 'lastday': 479 $begin = Tikilib::make_time($hour - 24, $min, $sec, $month, $day, $year); 480 break; 481 482 case 'week': 483 $iweek = TikiLib::date_format("%w", $now);// 0 for Sunday... 484 $calendarlib = TikiLib::lib('calendar'); 485 $firstDayofWeek = $calendarlib->firstDayofWeek(); 486 $iweek -= $firstDayofWeek; 487 if ($iweek < 0) { 488 $iweek += 7; 489 } 490 $begin = TikiLib::make_time(0, 0, 0, $month, $day - ($iweek ), $year); 491 break; 492 493 case 'lastweek': 494 $begin = Tikilib::make_time($hour, $min, $sec, $month, $day - 7, $year); 495 break; 496 497 case 'month': 498 $begin = TikiLib::make_time(0, 0, 0, $month, 1, $year); 499 break; 500 501 case 'lastmonth': 502 $begin = TikiLib::make_time($hour, $min, $sec, $month - 1, $day, $year); 503 break; 504 505 case 'year': 506 $begin = TikiLib::make_time(0, 0, 0, 1, 1, $year); 507 break; 508 509 case 'lastyear': 510 $begin = TikiLib::make_time($hour, $min, $sec, $month, $day, $year - 1); 511 break; 512 513 default: 514 $begin = $now; 515 break; 516 } 517 return [(int) $begin, (int) $now]; 518 } 519 520 /** 521 * count the number of created or modified for this day, this month, this year 522 * 523 */ 524 public function count_this_period($table = 'tiki_pages', $column = 'created', $when = 'daily', $parentColumn = '', $parentId = '') 525 { 526 $bindvars = $this->period2dates($when); 527 $where = ''; 528 if (! empty($parentColumn) && ! empty($parentId)) { 529 $where = " and `$parentColumn` = ?"; 530 $bindvars[] = (int) $parentId; 531 } 532 $query = "select count(*) from `$table` where `$column` >= ? and `$column` <= ? $where"; 533 $count = $this->getOne($query, $bindvars); 534 return $count; 535 } 536 537 /** 538 * count the number of viewed for this day, this month, this year 539 * 540 */ 541 public function hit_this_period($type = 'wiki', $when = 'daily') 542 { 543 $bindvars = $this->period2dates($when); 544 $bindvars[1] = $type; 545 $query = "select sum(`hits`) from `tiki_stats` where `day` >=? and `type`=?"; 546 $count = $this->getOne($query, $bindvars); 547 if ($count == '') { 548 $count = 0; 549 } 550 return $count; 551 } 552 553 public function add_pageview() 554 { 555 $dayzero = $this->make_time( 556 0, 557 0, 558 0, 559 $this->date_format("%m", $this->now), 560 $this->date_format("%d", $this->now), 561 $this->date_format("%Y", $this->now) 562 ); 563 564 $conditions = ['day' => (int) $dayzero,]; 565 566 $pageviews = $this->table('tiki_pageviews'); 567 $cant = $pageviews->fetchCount($conditions); 568 569 if ($cant) { 570 $pageviews->update(['pageviews' => $pageviews->increment(1),], $conditions); 571 } else { 572 $pageviews->insert(['day' => (int) $dayzero,'pageviews' => 1,]); 573 } 574 } 575 576 /** 577 * @param $days 578 * @return array 579 */ 580 public function get_pv_chart_data($days) 581 { 582 $now = $this->make_time(0, 0, 0, $this->date_format("%m"), $this->date_format("%d"), $this->date_format("%Y")); 583 $dfrom = 0; 584 if ($days != 0) { 585 $dfrom = $now - ($days * 24 * 60 * 60); 586 } 587 588 $query = "select `day`, `pageviews` from `tiki_pageviews` where `day`<=? and `day`>=?"; 589 $result = $this->fetchAll($query, [(int) $now, (int) $dfrom]); 590 $ret = []; 591 $n = ceil(count($result) / 10); 592 $i = 0; 593 $xdata = []; 594 $ydata = []; 595 596 foreach ($result as $res) { 597 if ($i % $n == 0) { 598 $xdata[] = $this->date_format("%e %b", $res["day"]); 599 } else { 600 $xdata = ''; 601 } 602 $ydata[] = $res["pageviews"]; 603 } 604 $ret['xdata'] = $xdata; 605 $ret['ydata'] = $ydata; 606 return $ret; 607 } 608} 609