Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00%
251 / 251
100.00%
12 / 12
 CRAP
100.00%
1 / 1
Statistics
100.00%
251 / 251
100.00%
12 / 12
 38
100.00%
1 / 1
 __construct
100.00%
3 / 3
100.00%
1 / 1
 2
 totalFaqs
100.00%
12 / 12
100.00%
1 / 1
 3
 getLatest
100.00%
11 / 11
100.00%
1 / 1
 2
 getTopTen
100.00%
29 / 29
100.00%
1 / 1
 4
 getTrending
100.00%
15 / 15
100.00%
1 / 1
 2
 getLatestData
100.00%
35 / 35
100.00%
1 / 1
 4
 getTrendingData
100.00%
35 / 35
100.00%
1 / 1
 4
 getTopTenData
100.00%
38 / 38
100.00%
1 / 1
 4
 getTopVotedData
100.00%
40 / 40
100.00%
1 / 1
 6
 buildStatisticsQuery
100.00%
29 / 29
100.00%
1 / 1
 5
 setUser
100.00%
2 / 2
100.00%
1 / 1
 1
 setGroups
100.00%
2 / 2
100.00%
1 / 1
 1
1<?php
2
3/**
4 * Class for statistics based on FAQs
5 *
6 * This Source Code Form is subject to the terms of the Mozilla Public License,
7 * v. 2.0. If a copy of the MPL was not distributed with this file, You can
8 * obtain one at https://mozilla.org/MPL/2.0/.
9 *
10 * @package   phpMyFAQ
11 * @author    Thorsten Rinne <thorsten@phpmyfaq.de>
12 * @copyright 2024-2026 phpMyFAQ Team
13 * @license   https://www.mozilla.org/MPL/2.0/ Mozilla Public License Version 2.0
14 * @link      https://www.phpmyfaq.de
15 * @since     2024-06-16
16 */
17
18declare(strict_types=1);
19
20namespace phpMyFAQ\Faq;
21
22use phpMyFAQ\Configuration;
23use phpMyFAQ\Database;
24use phpMyFAQ\Date;
25use phpMyFAQ\Filter;
26use phpMyFAQ\Language;
27use phpMyFAQ\Language\Plurals;
28use phpMyFAQ\Link;
29use phpMyFAQ\Link\Util\TitleSlugifier;
30use phpMyFAQ\Translation;
31use phpMyFAQ\Utils;
32use stdClass;
33
34class Statistics
35{
36    /** User */
37    private int $user = -1;
38
39    /** @var int[] Groups */
40    private array $groups = [-1];
41
42    /** Flag for Group support. */
43    private bool $groupSupport = false;
44
45    /** Plural form support. */
46    private readonly Plurals $plurals;
47
48    public function __construct(
49        private readonly Configuration $configuration,
50    ) {
51        $this->plurals = new Plurals();
52
53        if ($this->configuration->get(item: 'security.permLevel') !== 'basic') {
54            $this->groupSupport = true;
55        }
56    }
57
58    /**
59     * Returns the number of activated and not expired FAQs, optionally
60     * not limited to the current language.
61     *
62     * @param string|null $language Language
63     */
64    public function totalFaqs(?string $language = null): int
65    {
66        $now = date(format: 'YmdHis');
67
68        $query = sprintf(
69            "SELECT id FROM %sfaqdata WHERE active = 'yes' %s AND date_start <= '%s' AND date_end >= '%s'",
70            Database::getTablePrefix(),
71            null === $language ? '' : "AND lang = '" . $this->configuration->getDb()->escape($language) . "'",
72            $now,
73            $now,
74        );
75
76        $num = $this->configuration->getDb()->numRows($this->configuration->getDb()->query($query));
77
78        if ($num > 0) {
79            return $num;
80        }
81
82        return 0;
83    }
84
85    /**
86     * This function generates the list with the latest published records.
87     */
88    public function getLatest(): array
89    {
90        $date = new Date($this->configuration);
91        $result = $this->getLatestData(PMF_NUMBER_RECORDS_LATEST, $this->configuration->getLanguage()->getLanguage());
92        $output = [];
93
94        foreach ($result as $row) {
95            $entry = new stdClass();
96            $entry->url = $row['url'];
97            $entry->title = Utils::makeShorterText($row['question'], 8);
98            $entry->preview = $row['question'];
99            $entry->date = $date->format($row['date']);
100            $output[] = $entry;
101        }
102
103        return $output;
104    }
105
106    /**
107     * This function generates a list with the most voted or most visited records.
108     *
109     * @param string $type Type definition visits/voted
110     */
111    public function getTopTen(string $type = 'visits'): array
112    {
113        $output = [];
114
115        if ('visits' === $type) {
116            $result = $this->getTopTenData(
117                PMF_NUMBER_RECORDS_TOPTEN,
118                0,
119                $this->configuration->getLanguage()->getLanguage(),
120            );
121            foreach ($result as $row) {
122                $entry = new stdClass();
123                $entry->title = Utils::makeShorterText($row['question'], 8);
124                $entry->preview = $row['question'];
125                $entry->url = $row['url'];
126                $entry->visits = $this->plurals->get(key: 'plmsgViews', number: $row['visits']);
127                $output[] = $entry;
128            }
129
130            return $output;
131        }
132
133        $result = $this->getTopVotedData(PMF_NUMBER_RECORDS_TOPTEN, $this->configuration->getLanguage()->getLanguage());
134        foreach ($result as $row) {
135            $entry = new stdClass();
136            $entry->title = Utils::makeShorterText($row['question'], 8);
137            $entry->preview = $row['question'];
138            $entry->url = $row['url'];
139            $entry->voted = sprintf(
140                '%s %s 5 - %s',
141                round(num: $row['avg'], precision: 2),
142                Translation::getString(key: 'msgVoteFrom'),
143                $this->plurals->get(key: 'plmsgVotes', number: $row['user']),
144            );
145            $output[] = $entry;
146        }
147
148        return $output;
149    }
150
151    /**
152     * This function generates the list with the most trending FAQs.
153     *
154     * @return stdClass[]
155     */
156    public function getTrending(): array
157    {
158        $date = new Date($this->configuration);
159        $result = $this->getTrendingData(
160            PMF_NUMBER_RECORDS_TRENDING,
161            $this->configuration->getLanguage()->getLanguage(),
162        );
163        $output = [];
164
165        foreach ($result as $row) {
166            $entry = new stdClass();
167            $entry->url = $row['url'];
168            $entry->title = Utils::makeShorterText($row['question'], 8);
169            $entry->preview = $row['question'];
170            $entry->visits = $this->plurals->get(key: 'plmsgViews', number: $row['visits']);
171            $entry->date = $date->format($row['date']);
172            $output[] = $entry;
173        }
174
175        return $output;
176    }
177
178    /**
179     * This function generates an array with a specified number of most recent
180     * published records.
181     *
182     * @param int         $count Number of records
183     * @param string|null $language Language
184     * @return array<int, array{date: string, question: string, answer: string, visits: int, url: string}>
185     */
186    public function getLatestData(int $count = PMF_NUMBER_RECORDS_LATEST, ?string $language = null): array
187    {
188        $query = $this->buildStatisticsQuery(
189            selectList: 'fd.id AS id, fd.lang AS lang, fd.thema AS question, fd.content AS content, '
190            . 'fd.updated AS updated, fv.visits AS visits',
191            sourceTable: 'faqvisits',
192            joinCondition: 'fd.id = fv.id AND fd.lang = fv.lang',
193            language: $language,
194            orderBy: 'fd.updated DESC',
195        );
196
197        $result = $this->configuration->getDb()->query($query, 0, $count);
198        $latest = [];
199        $data = [];
200
201        if ($result) {
202            while (true) {
203                $row = $this->configuration->getDb()->fetchObject($result);
204                if (!$row instanceof stdClass) {
205                    break;
206                }
207
208                $title = (string) $row->question;
209
210                $data['date'] = Date::createIsoDate((string) $row->updated, DATE_ATOM);
211                $data['question'] = Filter::filterVar($title, FILTER_SANITIZE_SPECIAL_CHARS, '');
212                $data['answer'] = (string) $row->content;
213                $data['visits'] = (int) $row->visits;
214
215                $url = sprintf(
216                    '%scontent/%d/%d/%s/%s.html',
217                    $this->configuration->getDefaultUrl(),
218                    (int) $row->category_id,
219                    (int) $row->id,
220                    (string) $row->lang,
221                    TitleSlugifier::slug($title),
222                );
223                $oLink = new Link($url, $this->configuration);
224                $oLink->setTitle($title);
225                $oLink->tooltip = $title;
226                $data['url'] = $oLink->toString();
227
228                $latest[(int) $row->id] = $data;
229            }
230        }
231
232        return $latest;
233    }
234
235    /**
236     * This function generates the Trending data with the most visited records.
237     *
238     * @param int         $count Number of records
239     * @param string|null $language Language
240     * @return array<int, array{date: string, question: string, answer: string, visits: int, url: string}>
241     */
242    public function getTrendingData(int $count = PMF_NUMBER_RECORDS_TRENDING, ?string $language = null): array
243    {
244        $query = $this->buildStatisticsQuery(
245            selectList: 'fd.id AS id, fd.lang AS language, fd.thema AS question, fd.content AS content, '
246            . 'fd.created AS created, fv.visits AS visits',
247            sourceTable: 'faqvisits',
248            joinCondition: 'fd.id = fv.id AND fd.lang = fv.lang',
249            language: $language,
250            orderBy: 'fd.created DESC, fv.visits DESC',
251        );
252
253        $result = $this->configuration->getDb()->query($query, 0, $count);
254        $trending = [];
255        $data = [];
256
257        if ($result) {
258            while (true) {
259                $row = $this->configuration->getDb()->fetchObject($result);
260                if (!$row instanceof stdClass) {
261                    break;
262                }
263
264                $title = (string) $row->question;
265
266                $data['date'] = Filter::filterVar($row->created, FILTER_SANITIZE_SPECIAL_CHARS, '');
267                $data['question'] = Filter::filterVar($title, FILTER_SANITIZE_SPECIAL_CHARS, '');
268                $data['answer'] = (string) $row->content;
269                $data['visits'] = (int) $row->visits;
270
271                $url = sprintf(
272                    '%scontent/%d/%d/%s/%s.html',
273                    $this->configuration->getDefaultUrl(),
274                    (int) $row->category_id,
275                    (int) $row->id,
276                    (string) $row->language,
277                    TitleSlugifier::slug($title),
278                );
279                $oLink = new Link($url, $this->configuration);
280                $oLink->setTitle($title);
281                $oLink->tooltip = $title;
282                $data['url'] = $oLink->toString();
283
284                $trending[(int) $row->id] = $data;
285            }
286        }
287
288        return $trending;
289    }
290
291    /**
292     * This function generates the Top Ten data with the most viewed records.
293     *
294     * @param int  $count Number of records
295     * @param int  $categoryId Entity ID
296     * @param string|null $language Language
297     * @return array<int, array{visits: int, question: string, answer: string, date: string, last_visit: string, url: string}>
298     */
299    public function getTopTenData(
300        int $count = PMF_NUMBER_RECORDS_TOPTEN,
301        int $categoryId = 0,
302        ?string $language = null,
303    ): array {
304        $query = $this->buildStatisticsQuery(
305            selectList: 'fd.id AS id, fd.lang AS lang, fd.thema AS question, fd.content AS answer, '
306            . 'fd.updated AS updated, fv.visits AS visits, fv.last_visit AS last_visit',
307            sourceTable: 'faqvisits',
308            joinCondition: 'fd.id = fv.id AND fd.lang = fv.lang',
309            language: $language,
310            orderBy: 'fv.visits DESC',
311            categoryId: $categoryId,
312        );
313
314        $result = $this->configuration->getDb()->query($query, 0, $count);
315        $topTen = [];
316        $data = [];
317
318        if ($result) {
319            while (true) {
320                $row = $this->configuration->getDb()->fetchObject($result);
321
322                if (!$row instanceof stdClass) {
323                    break;
324                }
325
326                $title = (string) $row->question;
327
328                $data['visits'] = (int) $row->visits;
329                $data['question'] = Filter::filterVar($title, FILTER_SANITIZE_SPECIAL_CHARS, '');
330                $data['answer'] = (string) $row->answer;
331                $data['date'] = Date::createIsoDate((string) $row->updated, DATE_ATOM);
332                $data['last_visit'] = date(format: 'c', timestamp: (int) $row->last_visit);
333
334                $url = sprintf(
335                    '%scontent/%d/%d/%s/%s.html',
336                    $this->configuration->getDefaultUrl(),
337                    (int) $row->category_id,
338                    (int) $row->id,
339                    (string) $row->lang,
340                    TitleSlugifier::slug($title),
341                );
342                $oLink = new Link($url, $this->configuration);
343                $oLink->setTitle($title);
344                $oLink->tooltip = $title;
345                $data['url'] = $oLink->toString();
346
347                $topTen[(int) $row->id] = $data;
348            }
349
350            usort($topTen, static fn(array $first, array $second): int => $second['visits'] <=> $first['visits']);
351        }
352
353        return $topTen;
354    }
355
356    /**
357     * This function generates data-set with the most voted FAQs.
358     *
359     * @param int         $count    Number of records
360     * @param string|null $language Language
361     * @return array<int, array{avg: float, question: string, date: string, user: int, url: string}>
362     */
363    public function getTopVotedData(int $count = PMF_NUMBER_RECORDS_TOPTEN, ?string $language = null): array
364    {
365        $topten = [];
366        $data = [];
367
368        $query = $this->buildStatisticsQuery(
369            selectList: 'fd.id AS id, fd.lang AS lang, fd.thema AS thema, fd.updated AS updated, '
370            . '(fv.vote/fv.usr) AS avg, fv.usr AS user',
371            sourceTable: 'faqvoting',
372            joinCondition: 'fd.id = fv.artikel',
373            language: $language,
374            orderBy: 'avg DESC',
375        );
376
377        $result = $this->configuration->getDb()->query($query, 0, $count);
378
379        $i = 1;
380        $oldId = 0;
381        while (true) {
382            $row = $this->configuration->getDb()->fetchObject($result);
383            if (!$row instanceof stdClass || $i > $count) {
384                break;
385            }
386
387            $faqId = (int) $row->id;
388            if ($oldId !== $faqId) {
389                $title = (string) $row->thema;
390
391                $data['avg'] = is_numeric($row->avg) ? (float) $row->avg : 0.0;
392                $data['question'] = $title;
393                $data['date'] = (string) $row->updated;
394                $data['user'] = (int) $row->user;
395
396                $url = sprintf(
397                    '%scontent/%d/%d/%s/%s.html',
398                    $this->configuration->getDefaultUrl(),
399                    (int) $row->category_id,
400                    $faqId,
401                    (string) $row->lang,
402                    TitleSlugifier::slug($title),
403                );
404                $oLink = new Link($url, $this->configuration);
405                $oLink->setTitle($title);
406                $oLink->tooltip = $title;
407                $data['url'] = $oLink->toString();
408
409                $topten[] = $data;
410                ++$i;
411            }
412
413            $oldId = $faqId;
414        }
415
416        return $topten;
417    }
418
419    /**
420     * Builds the shared statistics query: active FAQs within their publication window,
421     * joined against a per-FAQ source table (visits or votes), permission-filtered via
422     * EXISTS subqueries and enriched with a deterministic category id. Every FAQ maps
423     * to exactly one result row, so callers can rely on the driver-level LIMIT.
424     */
425    private function buildStatisticsQuery(
426        string $selectList,
427        string $sourceTable,
428        string $joinCondition,
429        ?string $language,
430        string $orderBy,
431        int $categoryId = 0,
432    ): string {
433        $now = date(format: 'YmdHis');
434        $queryHelper = new QueryHelper($this->user, $this->groups);
435        $prefix = Database::getTablePrefix();
436
437        $categoryFilter = $categoryId !== 0 ? sprintf(' AND fcr.category_id = %d', $categoryId) : '';
438
439        $query = sprintf(
440            'SELECT %s, (SELECT MIN(fcr.category_id) FROM %sfaqcategoryrelations fcr '
441            . 'WHERE fcr.record_id = fd.id AND fcr.record_lang = fd.lang%s) AS category_id '
442            . 'FROM %sfaqdata fd, %s%s fv '
443            . "WHERE %s AND fd.active = 'yes' AND fd.date_start <= '%s' AND fd.date_end >= '%s'",
444            $selectList,
445            $prefix,
446            $categoryFilter,
447            $prefix,
448            $prefix,
449            $sourceTable,
450            $joinCondition,
451            $now,
452            $now,
453        );
454
455        if ($categoryId !== 0) {
456            $query .= sprintf(
457                ' AND EXISTS (SELECT 1 FROM %sfaqcategoryrelations fcr '
458                . 'WHERE fcr.record_id = fd.id AND fcr.record_lang = fd.lang AND fcr.category_id = %d)',
459                $prefix,
460                $categoryId,
461            );
462        }
463
464        if ($language !== null && Language::isASupportedLanguage($language)) {
465            $query .= sprintf(" AND fd.lang = '%s'", $this->configuration->getDb()->escape($language));
466        }
467
468        return $query . ' ' . $queryHelper->queryPermissionExistsAll($this->groupSupport) . ' ORDER BY ' . $orderBy;
469    }
470
471    public function setUser(int $userId = -1): Statistics
472    {
473        $this->user = $userId;
474        return $this;
475    }
476
477    /**
478     * @param int[] $groups
479     */
480    public function setGroups(array $groups): Statistics
481    {
482        $this->groups = $groups;
483        return $this;
484    }
485}