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