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