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