Lines 76.50% 127 / 166
Methods 60.00% 9 / 15
Classes 0.00% 0 / 1
Covered by tests of size
Name Lines Methods CRAP
 __construct 100.00% 1 / 1 100.00% 1 / 1 1
 getAllFaqsByCategory 89.47% 34 / 38 0.00% 0 / 1 9.09
 updateRecordFlag 100.00% 16 / 16 100.00% 1 / 1 7
 getInactiveFaqsData 100.00% 22 / 22 100.00% 1 / 1 6
 getOrphanedFaqs 100.00% 24 / 24 100.00% 1 / 1 6
 getContentHealthStatistics 0.00% 0 / 19 0.00% 0 / 1 2
 countQuery 0.00% 0 / 6 0.00% 0 / 1 12
 setStickyFaqOrder 100.00% 17 / 17 100.00% 1 / 1 4
 setLanguage 100.00% 2 / 2 100.00% 1 / 1 1
 getLanguage 100.00% 1 / 1 100.00% 1 / 1 1
 userCanEditFaq 83.33% 5 / 6 0.00% 0 / 1 2.02
 buildGroupAwareFaqAccessQuery 0.00% 0 / 3 0.00% 0 / 1 2
 buildBasicFaqAccessQuery 100.00% 2 / 2 100.00% 1 / 1 1
 sanitizeFaqIds 100.00% 3 / 3 100.00% 1 / 1 1
 sanitizePermissionIds 0.00% 0 / 6 0.00% 0 / 1 6
26class Faq
27{
28    private ?string $language = null;
29
30    public function __construct(
31        private readonly Configuration $configuration,
32    ) {
33    }
34
35    /**
36     * Get all FAQs by category
37     */
38    public function getAllFaqsByCategory(int $categoryId, bool $onlyInactive = false, bool $onlyNew = false): array
39    {
40        $faqData = [];
41
42        $query = sprintf(
43            "
44            SELECT
45                fd.id AS id,
46                fd.lang AS lang,
47                fd.solution_id AS solution_id,
48                fd.active AS active,
49                fd.sticky AS sticky,
50                fd.thema AS question,
51                fd.updated AS updated,
52                fcr.category_id AS category_id,
53                fv.visits AS visits,
54                fd.created AS created
55            FROM
56                %sfaqdata AS fd
57            LEFT JOIN
58                %sfaqcategoryrelations AS fcr
59            ON
60                fd.id = fcr.record_id
61            AND
62                fd.lang = fcr.record_lang
63            LEFT JOIN
64                %sfaqvisits AS fv
65            ON
66                fd.id = fv.id
67            AND
68                fv.lang = fd.lang
69            WHERE
70                fcr.category_id = %d
71            AND
72                fd.lang = '%s'
73            %s
74            %s
75            ORDER BY
76                fd.id ASC",
77            Database::getTablePrefix(),
78            Database::getTablePrefix(),
79            Database::getTablePrefix(),
80            $categoryId,
81            $this->configuration->getDb()->escape($this->getLanguage() ?? ''),
82            $onlyInactive ? "AND fd.active = 'no'" : '',
83            $onlyNew
84                ? sprintf("AND fd.created > '%s'", date(
85                    format: 'Y-m-d H:i:s',
86                    timestamp: (int) strtotime(datetime: '-1 month'),
87                ))
88                : '',
89        );
90
91        $result = $this->configuration->getDb()->query($query);
92        $num = $this->configuration->getDb()->numRows($result);
93
94        if ($num > 0) {
95            while (true) {
96                $row = $this->configuration->getDb()->fetchObject($result);
97                if ($row === false || $row === null || $row === []) {
98                    break;
99                }
100
101                $visits = (int) ($row->visits ?? 0);
102
103                $faqData[] = [
104                    'id' => (int) $row->id,
105                    'language' => $row->lang,
106                    'solution_id' => (int) $row->solution_id,
107                    'active' => $row->active,
108                    'sticky' => $row->sticky ? 'yes' : 'no',
109                    'category_id' => (int) $row->category_id,
110                    'question' => $row->question,
111                    'updated' => $row->updated,
112                    'visits' => (int) $visits,
113                    'created' => $row->created,
114                ];
115            }
116        }
117
118        return $faqData;
119    }
120
121    /**
122     * Set or unset a faq item flag.
123     *
124     * @param int    $faqId       FAQ id
125     * @param string $faqLanguage Language code which is valid with Language::isASupportedLanguage
126     * @param bool   $flag        FAQ is set to sticky or not
127     * @param string $type        Type of the flag to set, use the column name
128     */
129    public function updateRecordFlag(int $faqId, string $faqLanguage, bool $flag, string $type): bool
130    {
131        $flag = match ($type) {
132            'sticky' => $flag ? 1 : 0,
133            'active' => $flag ? "'yes'" : "'no'",
134            default => null,
135        };
136
137        if (null !== $flag) {
138            $update = sprintf(
139                "
140                UPDATE 
141                    %sfaqdata 
142                SET 
143                    %s = %s 
144                WHERE 
145                    id = %d 
146                AND 
147                    lang = '%s'",
148                Database::getTablePrefix(),
149                $type,
150                $flag,
151                $faqId,
152                $this->configuration->getDb()->escape($faqLanguage),
153            );
154
155            return (bool) $this->configuration->getDb()->query($update);
156        }
157
158        return false;
159    }
160
161    /**
162     * Returns the inactive records with admin URL to edit the FAQ and title.
163     */
164    public function getInactiveFaqsData(): array
165    {
166        $language = $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage());
167        $query = sprintf("
168            SELECT
169                fd.id AS id,
170                fd.lang AS lang,
171                fd.thema AS thema
172            FROM
173                %sfaqdata fd
174            WHERE
175                fd.lang = '%s'
176            AND 
177                fd.active = 'no'
178            GROUP BY
179                fd.id, fd.lang, fd.thema
180            ORDER BY
181                fd.id DESC", Database::getTablePrefix(), $language);
182
183        $result = $this->configuration->getDb()->query($query);
184        $inactive = [];
185        $data = [];
186
187        $oldId = 0;
188        while (true) {
189            $row = $this->configuration->getDb()->fetchObject($result);
190            if ($row === false || $row === null || $row === []) {
191                break;
192            }
193
194            if ($oldId !== $row->id) {
195                $data['question'] = $row->thema;
196                $data['url'] = sprintf(
197                    '%sadmin/faq/edit/%d/%s',
198                    $this->configuration->getDefaultUrl(),
199                    (int) $row->id,
200                    (string) $row->lang,
201                );
202                $inactive[] = $data;
203            }
204
205            $oldId = $row->id;
206        }
207
208        return $inactive;
209    }
210
211    /**
212     * Returns the orphaned records with admin URL to edit the FAQ and title.
213     *
214     * @return stdClass[]
215     */
216    public function getOrphanedFaqs(): array
217    {
218        $query = sprintf("
219                SELECT
220                    fd.id AS id,
221                    fd.lang AS lang,
222                    fd.thema AS question
223                FROM
224                    %sfaqdata fd
225                WHERE
226                    fd.active = 'yes'
227                AND
228                    fd.id NOT IN (
229                        SELECT
230                            record_id
231                        FROM
232                            %sfaqcategoryrelations
233                        WHERE
234                            record_lang = fd.lang
235                    )
236                GROUP BY
237                    fd.id, fd.lang, fd.thema
238                ORDER BY
239                    fd.id DESC", Database::getTablePrefix(), Database::getTablePrefix());
240
241        $result = $this->configuration->getDb()->query($query);
242        $orphaned = [];
243        $seen = [];
244        while (true) {
245            $row = $this->configuration->getDb()->fetchObject($result);
246            if ($row === false || $row === null || $row === []) {
247                break;
248            }
249
250            $key = (int) $row->id . '-' . (string) $row->lang;
251
252            if (($seen[$key] ?? false) === false) {
253                $seen[$key] = true;
254                $data = new stdClass();
255                $data->faqId = $row->id;
256                $data->language = $row->lang;
257                $data->question = $row->question;
258                $data->url = sprintf(
259                    '%sadmin/faq/edit/%d/%s',
260                    $this->configuration->getDefaultUrl(),
261                    (int) $row->id,
262                    (string) $row->lang,
263                );
264                $orphaned[] = $data;
265            }
266        }
267
268        return $orphaned;
269    }
270
271    /**
272     * Returns aggregated content health counters for the admin dashboard.
273     *
274     * - orphaned: active FAQs not assigned to any category
275     * - stale:    active FAQs not updated within the last $staleDays days
276     *
277     * @return array{orphaned: int, stale: int}
278     */
279    public function getContentHealthStatistics(int $staleDays = 180): array
280    {
281        $prefix = Database::getTablePrefix();
282
283        $orphanedQuery = sprintf(
284            "SELECT COUNT(*) AS num FROM %sfaqdata fd WHERE fd.active = 'yes' "
285            . 'AND fd.id NOT IN ('
286            . 'SELECT record_id FROM %sfaqcategoryrelations WHERE record_lang = fd.lang)',
287            $prefix,
288            $prefix,
289        );
290
291        // The "updated" column is stored as a YmdHis string, so a string comparison is valid.
292        $threshold = date('YmdHis', (int) strtotime(sprintf('-%d days', $staleDays)));
293        $staleQuery = sprintf(
294            'SELECT COUNT(*) AS num FROM %sfaqdata fd '
295            . "WHERE fd.active = 'yes' AND fd.updated <> '' AND fd.updated < '%s'",
296            $prefix,
297            $threshold,
298        );
299
300        return [
301            'orphaned' => $this->countQuery($orphanedQuery),
302            'stale' => $this->countQuery($staleQuery),
303        ];
304    }
305
306    /**
307     * Runs a COUNT(*) query and returns the resulting integer.
308     */
309    private function countQuery(string $query): int
310    {
311        $database = $this->configuration->getDb();
312        $result = $database->query($query);
313
314        if ($result === false) {
315            return 0;
316        }
317
318        $row = $database->fetchObject($result);
319
320        return is_object($row) ? (int) $row->num : 0;
321    }
322
323    /**
324     * Returns true if saving the order of the sticky faqs was successfully.
325     *
326     * @param array<int|string> $faqIds Order of record id's
327     * @param int[] $currentGroups
328     */
329    public function setStickyFaqOrder(array $faqIds, int $currentUserId = -1, array $currentGroups = [-1]): bool
330    {
331        $faqIds = $this->sanitizeFaqIds($faqIds);
332
333        foreach ($faqIds as $faqId) {
334            if (!$this->userCanEditFaq($faqId, $currentUserId, $currentGroups)) {
335                return false;
336            }
337        }
338
339        $normalizedFaqIds = array_map(static fn($faqId): int => (int) $faqId, $faqIds);
340        $count = 1;
341        $counter = count($normalizedFaqIds);
342        for ($i = 0; $i < $counter; ++$i) {
343            $query = sprintf(
344                'UPDATE %sfaqdata SET sticky_order=%d WHERE id=%d',
345                Database::getTablePrefix(),
346                $count,
347                $normalizedFaqIds[$i],
348            );
349            $this->configuration->getDb()->query($query);
350            ++$count;
351        }
352
353        return true;
354    }
355
356    public function setLanguage(string $language): Faq
357    {
358        $this->language = $language;
359        return $this;
360    }
361
362    public function getLanguage(): ?string
363    {
364        return $this->language;
365    }
366
367    /**
368     * @param int[] $currentGroups
369     */
370    private function userCanEditFaq(int $faqId, int $currentUserId, array $currentGroups): bool
371    {
372        $query = $this->configuration->get(item: 'security.permLevel') !== 'basic'
373            ? $this->buildGroupAwareFaqAccessQuery($faqId, $currentUserId, $currentGroups)
374            : $this->buildBasicFaqAccessQuery($faqId, $currentUserId);
375
376        $result = $this->configuration->getDb()->query($query);
377        $row = $this->configuration->getDb()->fetchObject($result);
378
379        return is_object($row);
380    }
381
382    /**
383     * @param int[] $currentGroups
384     */
385    private function buildGroupAwareFaqAccessQuery(int $faqId, int $currentUserId, array $currentGroups): string
386    {
387        $groupIds = $this->sanitizePermissionIds($currentGroups);
388
389        return sprintf('SELECT id FROM %1$sfaqdata fd WHERE fd.id = %2$d AND (
390                EXISTS (
391                    SELECT 1 FROM %1$sfaqdata_user fdu
392                    WHERE fdu.record_id = fd.id AND fdu.user_id IN (-1, %3$d)
393                )
394                OR EXISTS (
395                    SELECT 1 FROM %1$sfaqdata_group fdg
396                    WHERE fdg.record_id = fd.id AND fdg.group_id IN (%4$s)
397                )
398                OR (
399                    NOT EXISTS (SELECT 1 FROM %1$sfaqdata_user fdu_all WHERE fdu_all.record_id = fd.id)
400                    AND NOT EXISTS (SELECT 1 FROM %1$sfaqdata_group fdg_all WHERE fdg_all.record_id = fd.id)
401                )
402            )', Database::getTablePrefix(), $faqId, $currentUserId, implode(', ', $groupIds));
403    }
404
405    private function buildBasicFaqAccessQuery(int $faqId, int $currentUserId): string
406    {
407        return sprintf('SELECT id FROM %1$sfaqdata fd WHERE fd.id = %2$d AND (
408                EXISTS (
409                    SELECT 1 FROM %1$sfaqdata_user fdu
410                    WHERE fdu.record_id = fd.id AND fdu.user_id IN (-1, %3$d)
411                )
412                OR NOT EXISTS (
413                    SELECT 1 FROM %1$sfaqdata_user fdu_all WHERE fdu_all.record_id = fd.id
414                )
415            )', Database::getTablePrefix(), $faqId, $currentUserId);
416    }
417
418    /**
419     * @param array<int|string> $faqIds
420     * @return int[]
421     */
422    private function sanitizeFaqIds(array $faqIds): array
423    {
424        $sanitizedFaqIds = array_map(static fn(int|string $faqId): int => (int) $faqId, $faqIds);
425        $sanitizedFaqIds = array_filter($sanitizedFaqIds, static fn(int $faqId): bool => $faqId > 0);
426
427        return array_values(array_unique($sanitizedFaqIds));
428    }
429
430    /**
431     * @param array<int|string> $permissionIds
432     * @return int[]
433     */
434    private function sanitizePermissionIds(array $permissionIds): array
435    {
436        $sanitizedPermissionIds = array_map(
437            static fn(int|string $permissionId): int => (int) $permissionId,
438            $permissionIds,
439        );
440        $sanitizedPermissionIds = array_values(array_unique($sanitizedPermissionIds));
441
442        return $sanitizedPermissionIds === [] ? [-1] : $sanitizedPermissionIds;
443    }
444}