1<?php 2 3namespace dokuwiki\plugin\statistics; 4 5use dokuwiki\plugin\sqlite\SQLiteDB; 6use helper_plugin_statistics; 7 8/** 9 * This class defines a bunch of SQL queries to fetch various statistics from the database 10 */ 11class Query 12{ 13 protected helper_plugin_statistics $hlp; 14 protected SQLiteDB $db; 15 protected string $from; 16 protected string $to; 17 protected string $limit = ''; 18 19 /** 20 * @param helper_plugin_statistics $hlp 21 */ 22 public function __construct(helper_plugin_statistics $hlp) 23 { 24 $this->hlp = $hlp; 25 $this->db = $hlp->getDB(); 26 $today = date('Y-m-d'); 27 $this->setTimeFrame($today, $today); 28 $this->setPagination(0, 20); 29 } 30 31 /** 32 * Set the time frame for all queries 33 * 34 * @param string $from The start date as YYYY-MM-DD 35 * @param string $to The end date as YYYY-MM-DD 36 */ 37 public function setTimeFrame(string $from, string $to): void 38 { 39 try { 40 $from = new \DateTime($from); 41 $to = new \DateTime($to); 42 } catch (\Exception $e) { 43 $from = new \DateTime(); 44 $to = new \DateTime(); 45 } 46 $from->setTime(0, 0); 47 $to->setTime(23, 59, 59); 48 49 $this->from = $from->format('Y-m-d H:i:s'); 50 $this->to = $to->format('Y-m-d H:i:s'); 51 } 52 53 /** 54 * Set the pagination settings for some queries 55 * 56 * @param int $start The start offset 57 * @param int $limit The number of results. If one more is returned, there is another page 58 * @return void 59 */ 60 public function setPagination(int $start, int $limit) 61 { 62 // when a limit is set, one more is fetched to indicate when a next page exists 63 if ($limit) $limit += 1; 64 65 if ($limit) { 66 $this->limit = " LIMIT $start,$limit"; 67 } elseif ($start) { 68 $this->limit = " OFFSET $start"; 69 } 70 } 71 72 /** 73 * Return some aggregated statistics 74 */ 75 public function aggregate(): array 76 { 77 // init some values that might not be set 78 $data = [ 79 'referers' => 0, // total number of (external) referrers 80 'external' => 0, // external referrers 81 'search' => 0, // search engine referrers 82 'direct' => 0, // direct referrers 83 'internal' => 0, // internal referrers 84 'bouncerate' => 0, 85 'newvisitors' => 0, 86 ]; 87 88 // Count referrer types by joining with referers table 89 $sql = "SELECT 90 CASE 91 WHEN R.engine IS NOT NULL THEN 'search' 92 WHEN R.url = '' THEN 'direct' 93 WHEN R.url IS NOT NULL THEN 'external' 94 ELSE 'internal' 95 END as ref_type, 96 COUNT(*) as cnt 97 FROM pageviews as P 98 LEFT JOIN referers as R ON P.ref_id = R.id 99 LEFT JOIN sessions as S ON P.session = S.session 100 WHERE P.dt >= ? AND P.dt <= ? 101 AND S.ua_type = ? 102 GROUP BY ref_type"; 103 $result = $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 104 105 foreach ($result as $row) { 106 if ($row['ref_type'] == 'search') { 107 $data['search'] = $row['cnt']; 108 $data['referers'] += $row['cnt']; 109 } 110 if ($row['ref_type'] == 'direct') { 111 $data['direct'] = $row['cnt']; 112 $data['referers'] += $row['cnt']; 113 } 114 if ($row['ref_type'] == 'external') { 115 $data['external'] = $row['cnt']; 116 $data['referers'] += $row['cnt']; 117 } 118 if ($row['ref_type'] == 'internal') { 119 $data['internal'] = $row['cnt']; 120 } 121 } 122 123 // general user and session info 124 $sql = "SELECT COUNT(DISTINCT P.session) as sessions, 125 COUNT(P.session) as views, 126 COUNT(DISTINCT S.user) as users, 127 COUNT(DISTINCT S.uid) as visitors 128 FROM pageviews as P 129 LEFT JOIN sessions as S ON P.session = S.session 130 WHERE P.dt >= ? AND P.dt <= ? 131 AND S.ua_type = ?"; 132 $result = $this->db->queryRecord($sql, [$this->from, $this->to, 'browser']); 133 134 $data['users'] = $result['users']; 135 $data['sessions'] = $result['sessions']; 136 $data['pageviews'] = $result['views']; 137 $data['visitors'] = $result['visitors']; 138 139 // calculate bounce rate (sessions with only 1 page view) 140 if ($data['sessions']) { 141 $sql = "SELECT COUNT(*) as cnt 142 FROM ( 143 SELECT P.session, COUNT(*) as views 144 FROM pageviews as P 145 LEFT JOIN sessions as S ON P.session = S.session 146 WHERE P.dt >= ? AND P.dt <= ? 147 AND S.ua_type = ? 148 GROUP BY P.session 149 HAVING views = 1 150 )"; 151 $count = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']); 152 $data['bouncerate'] = $count * 100 / $data['sessions']; 153 $data['newvisitors'] = $count * 100 / $data['sessions']; 154 } 155 156 // calculate avg. number of views per session 157 $sql = "SELECT AVG(views) as cnt 158 FROM ( 159 SELECT P.session, COUNT(*) as views 160 FROM pageviews as P 161 LEFT JOIN sessions as S ON P.session = S.session 162 WHERE P.dt >= ? AND P.dt <= ? 163 AND S.ua_type = ? 164 GROUP BY P.session 165 )"; 166 $data['avgpages'] = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']); 167 168 // average time spent on the site 169 $sql = "SELECT AVG((julianday(end) - julianday(dt)) * 24 * 60) as time 170 FROM sessions as S 171 WHERE S.dt >= ? AND S.dt <= ? 172 AND S.dt != S.end 173 AND DATE(S.dt) = DATE(S.end) 174 AND S.ua_type = ?"; 175 $data['timespent'] = $this->db->queryValue($sql, [$this->from, $this->to, 'browser']); 176 177 // logins 178 $sql = "SELECT COUNT(*) as logins 179 FROM logins as A 180 WHERE A.dt >= ? AND A.dt <= ? 181 AND (type = ? OR type = ?)"; 182 $data['logins'] = $this->db->queryValue($sql, [$this->from, $this->to, 'l', 'p']); 183 184 // registrations 185 $sql = "SELECT COUNT(*) as registrations 186 FROM logins as A 187 WHERE A.dt >= ? AND A.dt <= ? 188 AND type = ?"; 189 $data['registrations'] = $this->db->queryValue($sql, [$this->from, $this->to, 'C']); 190 191 // current users (based on recent sessions) 192 $sql = "SELECT COUNT(DISTINCT uid) as current 193 FROM sessions 194 WHERE end >= datetime('now', '-10 minutes')"; 195 $data['current'] = $this->db->queryValue($sql); 196 197 return $data; 198 } 199 200 201 /** 202 * Return some trend data about visits and edits in the wiki 203 * 204 * @param bool $hours Use hour resolution rather than days 205 * @return array 206 */ 207 public function dashboardviews(bool $hours = false): array 208 { 209 if ($hours) { 210 $TIME = 'strftime(\'%H\', P.dt)'; 211 } else { 212 $TIME = 'DATE(P.dt)'; 213 } 214 215 $data = []; 216 217 // access trends 218 $sql = "SELECT $TIME as time, 219 COUNT(DISTINCT P.session) as sessions, 220 COUNT(P.session) as pageviews, 221 COUNT(DISTINCT S.uid) as visitors 222 FROM pageviews as P 223 LEFT JOIN sessions as S ON P.session = S.session 224 WHERE P.dt >= ? AND P.dt <= ? 225 AND S.ua_type = ? 226 GROUP BY $TIME 227 ORDER BY time"; 228 $result = $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 229 foreach ($result as $row) { 230 $data[$row['time']]['sessions'] = $row['sessions']; 231 $data[$row['time']]['pageviews'] = $row['pageviews']; 232 $data[$row['time']]['visitors'] = $row['visitors']; 233 } 234 return $data; 235 } 236 237 /** 238 * @param bool $hours Use hour resolution rather than days 239 * @return array 240 */ 241 public function dashboardwiki(bool $hours = false): array 242 { 243 if ($hours) { 244 $TIME = 'strftime(\'%H\', dt)'; 245 } else { 246 $TIME = 'DATE(dt)'; 247 } 248 249 $data = []; 250 251 // edit trends 252 foreach (['E', 'C', 'D'] as $type) { 253 $sql = "SELECT $TIME as time, 254 COUNT(*) as cnt 255 FROM edits as A 256 WHERE A.dt >= ? AND A.dt <= ? 257 AND type = ? 258 GROUP BY $TIME 259 ORDER BY time"; 260 $result = $this->db->queryAll($sql, [$this->from, $this->to, $type]); 261 foreach ($result as $row) { 262 $data[$row['time']][$type] = $row['cnt']; 263 } 264 } 265 ksort($data); 266 return $data; 267 } 268 269 /** 270 * @param string $info Which type of history to select (FIXME which ones are there?) 271 * @param string $interval Group data by this interval (days, weeks, months) 272 * @return array 273 */ 274 public function history(string $info, string $interval = 'day'): array 275 { 276 if ($interval == 'weeks') { 277 $TIME = 'strftime(\'%Y\', dt), strftime(\'%W\', dt)'; 278 } elseif ($interval == 'months') { 279 $TIME = 'strftime(\'%Y-%m\', dt)'; 280 } else { 281 $TIME = 'strftime(\'%d-%m\', dt)'; 282 } 283 284 $mod = 1; 285 if ($info == 'media_size' || $info == 'page_size') { 286 $mod = 1024 * 1024; 287 } 288 289 $sql = "SELECT $TIME as time, 290 AVG(value)/$mod as cnt 291 FROM history as A 292 WHERE A.dt >= ? AND A.dt <= ? 293 AND info = ? 294 GROUP BY $TIME 295 ORDER BY $TIME"; 296 return $this->db->queryAll($sql, [$this->from, $this->to, $info]); 297 } 298 299 /** 300 * @return array 301 */ 302 public function searchengines(): array 303 { 304 $sql = "SELECT COUNT(*) as cnt, R.engine 305 FROM pageviews as P 306 LEFT JOIN referers as R ON P.ref_id = R.id 307 WHERE P.dt >= ? AND P.dt <= ? 308 AND R.engine IS NOT NULL 309 AND R.engine != '' 310 GROUP BY R.engine 311 ORDER BY cnt DESC, R.engine" . 312 $this->limit; 313 return $this->db->queryAll($sql, [$this->from, $this->to]); 314 } 315 316 /** 317 * @return array 318 */ 319 public function searchphrases(): array 320 { 321 $sql = "SELECT COUNT(*) as cnt, query, query as ilookup 322 FROM search 323 WHERE dt >= ? AND dt <= ? 324 GROUP BY query 325 ORDER BY cnt DESC, query" . 326 $this->limit; 327 return $this->db->queryAll($sql, [$this->from, $this->to]); 328 } 329 330 /** 331 * @return array 332 */ 333 public function searchwords(): array 334 { 335 $sql = "SELECT COUNT(*) as cnt, SW.word, SW.word as ilookup 336 FROM search as S, 337 searchwords as SW 338 WHERE S.dt >= ? AND S.dt <= ? 339 AND S.id = SW.sid 340 GROUP BY SW.word 341 ORDER BY cnt DESC, SW.word" . 342 $this->limit; 343 return $this->db->queryAll($sql, [$this->from, $this->to]); 344 } 345 346 /** 347 * @return array 348 */ 349 public function outlinks(): array 350 { 351 $sql = "SELECT COUNT(*) as cnt, link as url 352 FROM outlinks as A 353 WHERE A.dt >= ? AND A.dt <= ? 354 GROUP BY link 355 ORDER BY cnt DESC, link" . 356 $this->limit; 357 return $this->db->queryAll($sql, [$this->from, $this->to]); 358 } 359 360 /** 361 * @return array 362 */ 363 public function pages(): array 364 { 365 $sql = "SELECT COUNT(*) as cnt, P.page 366 FROM pageviews as P 367 LEFT JOIN sessions as S ON P.session = S.session 368 WHERE P.dt >= ? AND P.dt <= ? 369 AND S.ua_type = ? 370 GROUP BY P.page 371 ORDER BY cnt DESC, P.page" . 372 $this->limit; 373 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 374 } 375 376 /** 377 * @return array 378 */ 379 public function edits(): array 380 { 381 $sql = "SELECT COUNT(*) as cnt, page 382 FROM edits as A 383 WHERE A.dt >= ? AND A.dt <= ? 384 GROUP BY page 385 ORDER BY cnt DESC, page" . 386 $this->limit; 387 return $this->db->queryAll($sql, [$this->from, $this->to]); 388 } 389 390 /** 391 * @return array 392 */ 393 public function images(): array 394 { 395 $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize 396 FROM media as A 397 WHERE A.dt >= ? AND A.dt <= ? 398 AND mime1 = ? 399 GROUP BY media 400 ORDER BY cnt DESC, media" . 401 $this->limit; 402 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 403 } 404 405 /** 406 * @return array 407 */ 408 public function imagessum(): array 409 { 410 $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize 411 FROM media as A 412 WHERE A.dt >= ? AND A.dt <= ? 413 AND mime1 = ?"; 414 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 415 } 416 417 /** 418 * @return array 419 */ 420 public function downloads(): array 421 { 422 $sql = "SELECT COUNT(*) as cnt, media, SUM(size) as filesize 423 FROM media as A 424 WHERE A.dt >= ? AND A.dt <= ? 425 AND mime1 != ? 426 GROUP BY media 427 ORDER BY cnt DESC, media" . 428 $this->limit; 429 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 430 } 431 432 /** 433 * @return array 434 */ 435 public function downloadssum(): array 436 { 437 $sql = "SELECT COUNT(*) as cnt, SUM(size) as filesize 438 FROM media as A 439 WHERE A.dt >= ? AND A.dt <= ? 440 AND mime1 != ?"; 441 return $this->db->queryAll($sql, [$this->from, $this->to, 'image']); 442 } 443 444 /** 445 * @return array 446 */ 447 public function referer(): array 448 { 449 $sql = "SELECT COUNT(*) as cnt, R.url 450 FROM pageviews as P 451 LEFT JOIN sessions as S ON P.session = S.session 452 LEFT JOIN referers as R ON P.ref_id = R.id 453 WHERE P.dt >= ? AND P.dt <= ? 454 AND S.ua_type = ? 455 AND R.url IS NOT NULL 456 AND R.url != '' 457 AND R.engine IS NULL 458 GROUP BY R.url 459 ORDER BY cnt DESC, R.url" . 460 $this->limit; 461 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 462 } 463 464 /** 465 * @return array 466 */ 467 public function newreferer(): array 468 { 469 $sql = "SELECT COUNT(*) as cnt, R.url 470 FROM pageviews as P 471 LEFT JOIN sessions as S ON P.session = S.session 472 LEFT JOIN referers as R ON P.ref_id = R.id 473 WHERE P.dt >= ? AND P.dt <= ? 474 AND S.ua_type = ? 475 AND R.url IS NOT NULL 476 AND R.url != '' 477 AND R.engine IS NULL 478 AND R.dt >= ? 479 GROUP BY R.url 480 ORDER BY cnt DESC, R.url" . 481 $this->limit; 482 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', $this->from]); 483 } 484 485 /** 486 * @return array 487 */ 488 public function countries(): array 489 { 490 $sql = "SELECT COUNT(DISTINCT P.session) as cnt, I.country 491 FROM pageviews as P 492 LEFT JOIN iplocation as I ON P.ip = I.ip 493 WHERE P.dt >= ? AND P.dt <= ? 494 AND I.country IS NOT NULL 495 AND I.country != '' 496 GROUP BY I.code 497 ORDER BY cnt DESC, I.country" . 498 $this->limit; 499 return $this->db->queryAll($sql, [$this->from, $this->to]); 500 } 501 502 /** 503 * @param bool $ext return extended information 504 * @return array 505 */ 506 public function browsers(bool $ext = false): array 507 { 508 if ($ext) { 509 $sel = 'S.ua_info as browser, S.ua_ver'; 510 $grp = 'S.ua_info, S.ua_ver'; 511 } else { 512 $sel = 'S.ua_info as browser'; 513 $grp = 'S.ua_info'; 514 } 515 516 $sql = "SELECT COUNT(DISTINCT S.session) as cnt, $sel 517 FROM sessions as S 518 WHERE S.dt >= ? AND S.dt <= ? 519 AND S.ua_type = ? 520 GROUP BY $grp 521 ORDER BY cnt DESC, S.ua_info" . 522 $this->limit; 523 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 524 } 525 526 /** 527 * @return array 528 */ 529 public function os(): array 530 { 531 $sql = "SELECT COUNT(DISTINCT S.session) as cnt, S.os 532 FROM sessions as S 533 WHERE S.dt >= ? AND S.dt <= ? 534 AND S.ua_type = ? 535 GROUP BY S.os 536 ORDER BY cnt DESC, S.os" . 537 $this->limit; 538 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 539 } 540 541 /** 542 * @return array 543 */ 544 public function topdomain(): array 545 { 546 $sql = "SELECT COUNT(*) as cnt, U.domain 547 FROM pageviews as P, 548 sessions as S, 549 users as U 550 WHERE P.dt >= ? AND P.dt <= ? 551 AND P.session = S.session 552 AND S.user = U.user 553 AND S.ua_type = ? 554 AND S.user IS NOT NULL 555 AND S.user != ? 556 GROUP BY U.domain 557 ORDER BY cnt DESC, U.domain" . 558 $this->limit; 559 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', '']); 560 } 561 562 /** 563 * @return array 564 */ 565 public function topuser(): array 566 { 567 $sql = "SELECT COUNT(*) as cnt, S.user 568 FROM pageviews as P 569 LEFT JOIN sessions as S ON P.session = S.session 570 WHERE P.dt >= ? AND P.dt <= ? 571 AND S.ua_type = ? 572 AND S.user IS NOT NULL 573 AND S.user != ? 574 GROUP BY S.user 575 ORDER BY cnt DESC, S.user" . 576 $this->limit; 577 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', '']); 578 } 579 580 /** 581 * @return array 582 */ 583 public function topeditor(): array 584 { 585 $sql = "SELECT COUNT(*) as cnt, user 586 FROM edits as E 587 LEFT JOIN sessions as S ON E.session = S.session 588 WHERE E.dt >= ? AND E.dt <= ? 589 AND S.user != ? 590 GROUP BY user 591 ORDER BY cnt DESC, user" . 592 $this->limit; 593 return $this->db->queryAll($sql, [$this->from, $this->to, '']); 594 } 595 596 /** 597 * @return array 598 */ 599 public function topgroup(): array 600 { 601 $sql = "SELECT COUNT(*) as cnt, G.`group` 602 FROM pageviews as P 603 LEFT JOIN sessions as S ON P.session = S.session 604 LEFT JOIN groups as G ON S.user = G.user 605 WHERE P.dt >= ? AND P.dt <= ? 606 AND S.ua_type = ? 607 AND G.`group` IS NOT NULL 608 GROUP BY G.`group` 609 ORDER BY cnt DESC, G.`group`" . 610 $this->limit; 611 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser']); 612 } 613 614 /** 615 * @return array 616 */ 617 public function topgroupedit(): array 618 { 619 $sql = "SELECT COUNT(*) as cnt, G.`group` 620 FROM edits as E 621 LEFT JOIN sessions as S ON E.session = S.session 622 LEFT JOIN groups as G ON S.user = G.user 623 WHERE E.dt >= ? AND E.dt <= ? 624 AND G.`group` IS NOT NULL 625 GROUP BY G.`group` 626 ORDER BY cnt DESC, G.`group`" . 627 $this->limit; 628 return $this->db->queryAll($sql, [$this->from, $this->to]); 629 } 630 631 632 /** 633 * @return array 634 */ 635 public function resolution(): array 636 { 637 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 638 ROUND(P.screen_x/100)*100 as res_x, 639 ROUND(P.screen_y/100)*100 as res_y, 640 CAST(ROUND(P.screen_x/100)*100 AS int) || 'x' || CAST(ROUND(P.screen_y/100)*100 AS int) as resolution 641 FROM pageviews as P 642 LEFT JOIN sessions as S ON P.session = S.session 643 WHERE P.dt >= ? AND P.dt <= ? 644 AND S.ua_type = ? 645 AND P.screen_x != ? 646 AND P.screen_y != ? 647 GROUP BY resolution 648 ORDER BY cnt DESC" . 649 $this->limit; 650 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', 0, 0]); 651 } 652 653 /** 654 * @return array 655 */ 656 public function viewport(): array 657 { 658 $sql = "SELECT COUNT(DISTINCT S.uid) as cnt, 659 ROUND(P.view_x/100)*100 as res_x, 660 ROUND(P.view_y/100)*100 as res_y, 661 CAST(ROUND(P.view_x/100)*100 AS int) || 'x' || CAST(ROUND(P.view_y/100)*100 AS int) as resolution 662 FROM pageviews as P 663 LEFT JOIN sessions as S ON P.session = S.session 664 WHERE P.dt >= ? AND P.dt <= ? 665 AND S.ua_type = ? 666 AND P.view_x != ? 667 AND P.view_y != ? 668 GROUP BY resolution 669 ORDER BY cnt DESC" . 670 $this->limit; 671 672 return $this->db->queryAll($sql, [$this->from, $this->to, 'browser', 0, 0]); 673 } 674 675 /** 676 * @return array 677 */ 678 public function seenusers(): array 679 { 680 $sql = "SELECT `user`, MAX(`dt`) as dt 681 FROM users 682 WHERE `user` IS NOT NULL 683 AND `user` != '' 684 GROUP BY `user` 685 ORDER BY `dt` DESC" . 686 $this->limit; 687 688 return $this->db->queryAll($sql); 689 } 690} 691