Lines 99.29% 420 / 423
Methods 95.83% 23 / 24
Classes 0.00% 0 / 1
Covered by tests of size
Name Lines Methods CRAP
 __construct 100.00% 1 / 1 100.00% 1 / 1 1
 getNextSolutionId 100.00% 10 / 10 100.00% 1 / 1 4
 getSolutionIdFromId 100.00% 11 / 11 100.00% 1 / 1 2
 hasTranslation 100.00% 9 / 9 100.00% 1 / 1 1
 isFaqVisibleForUser 100.00% 16 / 16 100.00% 1 / 1 1
 isActive 100.00% 16 / 16 100.00% 1 / 1 5
 getIdFromSolutionId 100.00% 22 / 22 100.00% 1 / 1 2
 fetchQuestion 100.00% 16 / 16 100.00% 1 / 1 4
 fetchKeywords 100.00% 11 / 11 100.00% 1 / 1 3
 getFaqResult 100.00% 14 / 14 100.00% 1 / 1 4
 fetchFaqByIdAndCategoryId 100.00% 20 / 20 100.00% 1 / 1 3
 fetchRowBySolutionId 100.00% 31 / 31 100.00% 1 / 1 7
 fetchAvailableFaqsByCategoryId 100.00% 18 / 18 100.00% 1 / 1 1
 fetchFaqsByIds 100.00% 18 / 18 100.00% 1 / 1 2
 fetchStickyFaqs 100.00% 13 / 13 100.00% 1 / 1 1
 fetchAllFaqs 100.00% 19 / 19 100.00% 1 / 1 1
 insert 100.00% 23 / 23 100.00% 1 / 1 4
 update 100.00% 26 / 26 100.00% 1 / 1 5
 deleteByIdAndLanguage 100.00% 47 / 47 100.00% 1 / 1 2
 queryRenderableFaqsByCategoryId 100.00% 16 / 16 100.00% 1 / 1 1
 countRenderableFaqsByCategoryId 100.00% 18 / 18 100.00% 1 / 1 3
 queryRenderableFaqsByIds 100.00% 17 / 17 100.00% 1 / 1 1
 buildConditionWhereClause 87.50% 21 / 24 0.00% 0 / 1 13.33
 fetchAllRows 100.00% 7 / 7 100.00% 1 / 1 3
27final class FaqRepository implements FaqRepositoryInterface
28{
29    public function __construct(
30        private readonly Configuration $configuration,
31    ) {
32    }
33
34    public function getNextSolutionId(): int
35    {
36        $latestId = 0;
37
38        $query = sprintf('SELECT MAX(solution_id) AS solution_id FROM %sfaqdata', Database::getTablePrefix());
39
40        $result = $this->configuration->getDb()->query($query);
41        if ($result) {
42            $row = $this->configuration->getDb()->fetchObject($result);
43            if ($row instanceof \stdClass) {
44                $latestId = (int) $row->solution_id;
45            }
46        }
47
48        if ($latestId < PMF_SOLUTION_ID_START_VALUE) {
49            return PMF_SOLUTION_ID_START_VALUE;
50        }
51
52        return $latestId + PMF_SOLUTION_ID_INCREMENT_VALUE;
53    }
54
55    public function getSolutionIdFromId(int $faqId, string $faqLang): int
56    {
57        $query = sprintf(
58            "SELECT solution_id FROM %sfaqdata WHERE id = %d AND lang = '%s'",
59            Database::getTablePrefix(),
60            $faqId,
61            $this->configuration->getDb()->escape($faqLang),
62        );
63
64        $result = $this->configuration->getDb()->query($query);
65
66        $row = $this->configuration->getDb()->fetchObject($result);
67        if ($row) {
68            return (int) $row->solution_id;
69        }
70
71        return $this->getNextSolutionId();
72    }
73
74    public function hasTranslation(int $faqId, string $faqLang): bool
75    {
76        $query = sprintf(
77            "
78            SELECT
79                id, lang
80            FROM
81                %sfaqdata
82            WHERE
83                id = %d
84            AND
85                lang = '%s'",
86            Database::getTablePrefix(),
87            $faqId,
88            $this->configuration->getDb()->escape($faqLang),
89        );
90
91        $result = $this->configuration->getDb()->query($query);
92        return (bool) $this->configuration->getDb()->numRows($result);
93    }
94
95    /**
96     * Checks whether a FAQ record is visible to the given user and groups in the
97     * given language: active, within its active date window, and permitted.
98     *
99     * @param int[] $groups
100     */
101    public function isFaqVisibleForUser(
102        int $faqId,
103        string $faqLang,
104        int $userId,
105        array $groups,
106        bool $groupSupport,
107    ): bool {
108        $queryHelper = new QueryHelper($userId, $groups);
109        $now = date(format: 'YmdHis');
110
111        $query = sprintf(
112            "
113            SELECT
114                fd.id
115            FROM
116                %sfaqdata AS fd
117            LEFT JOIN
118                %sfaqdata_group AS fdg
119            ON
120                fd.id = fdg.record_id
121            LEFT JOIN
122                %sfaqdata_user AS fdu
123            ON
124                fd.id = fdu.record_id
125            WHERE
126                fd.id = %d
127            AND
128                fd.lang = '%s'
129            AND
130                fd.active = 'yes'
131            AND
132                fd.date_start <= '%s'
133            AND
134                fd.date_end >= '%s'
135                %s",
136            Database::getTablePrefix(),
137            Database::getTablePrefix(),
138            Database::getTablePrefix(),
139            $faqId,
140            $this->configuration->getDb()->escape($faqLang),
141            $now,
142            $now,
143            $queryHelper->queryPermission($groupSupport),
144        );
145
146        $result = $this->configuration->getDb()->query($query);
147
148        return $this->configuration->getDb()->numRows($result) > 0;
149    }
150
151    public function isActive(int $faqId, string $faqLang, string $commentType = 'faq'): bool
152    {
153        $table = 'news' === $commentType ? 'faqnews' : 'faqdata';
154
155        $query = sprintf(
156            "
157            SELECT
158                active
159            FROM
160                %s%s
161            WHERE
162                id = %d
163            AND
164                lang = '%s'",
165            Database::getTablePrefix(),
166            $table,
167            $faqId,
168            $this->configuration->getDb()->escape($faqLang),
169        );
170
171        $result = $this->configuration->getDb()->query($query);
172
173        $row = $this->configuration->getDb()->fetchObject($result);
174        if (!$row) {
175            return false;
176        }
177
178        if ($row->active === 'y' || $row->active === 'yes') {
179            return true;
180        }
181
182        return false;
183    }
184
185    public function getIdFromSolutionId(int $solutionId, int $userId, array $groups, bool $groupSupport): array
186    {
187        $queryHelper = new QueryHelper($userId, $groups);
188        $query = sprintf(
189            '
190            SELECT
191                fd.id,
192                fd.lang,
193                fd.thema AS question,
194                fd.content,
195                fcr.category_id AS category_id
196            FROM
197                %sfaqdata fd
198            LEFT JOIN
199                %sfaqcategoryrelations fcr
200            ON
201                fd.id = fcr.record_id
202            AND
203                fd.lang = fcr.record_lang
204            LEFT JOIN
205                %sfaqdata_group fdg
206            ON
207                fd.id = fdg.record_id
208            LEFT JOIN
209                %sfaqdata_user fdu
210            ON
211                fd.id = fdu.record_id
212            WHERE
213                fd.solution_id = %d
214                %s',
215            Database::getTablePrefix(),
216            Database::getTablePrefix(),
217            Database::getTablePrefix(),
218            Database::getTablePrefix(),
219            $solutionId,
220            $queryHelper->queryPermission($groupSupport),
221        );
222
223        $result = $this->configuration->getDb()->query($query);
224
225        $row = $this->configuration->getDb()->fetchObject($result);
226        if ($row) {
227            return [
228                'id' => $row->id,
229                'lang' => $row->lang,
230                'question' => $row->question,
231                'content' => $row->content,
232                'category_id' => $row->category_id,
233            ];
234        }
235
236        return [];
237    }
238
239    public function fetchQuestion(int $faqId, string $language): ?string
240    {
241        $query = sprintf(
242            "SELECT thema AS question FROM %sfaqdata WHERE id = %d AND lang = '%s'",
243            Database::getTablePrefix(),
244            $faqId,
245            $this->configuration->getDb()->escape($language),
246        );
247        $result = $this->configuration->getDb()->query($query);
248
249        if ($this->configuration->getDb()->numRows($result) === 0) {
250            return null;
251        }
252
253        $question = null;
254        while (true) {
255            $row = $this->configuration->getDb()->fetchObject($result);
256            if (!$row instanceof \stdClass) {
257                break;
258            }
259
260            $question = (string) $row->question;
261        }
262
263        return $question;
264    }
265
266    public function fetchKeywords(int $faqId, string $language): ?string
267    {
268        $query = sprintf(
269            "SELECT keywords FROM %sfaqdata WHERE id = %d AND lang = '%s'",
270            Database::getTablePrefix(),
271            $faqId,
272            $this->configuration->getDb()->escape($language),
273        );
274
275        $result = $this->configuration->getDb()->query($query);
276
277        if ($this->configuration->getDb()->numRows($result) === 0) {
278            return null;
279        }
280
281        $row = $this->configuration->getDb()->fetchObject($result);
282
283        return $row instanceof \stdClass ? (string) $row->keywords : null;
284    }
285
286    public function getFaqResult(
287        int $faqId,
288        string $faqLanguage,
289        ?int $faqRevisionId,
290        bool $isAdmin,
291        int $userId,
292        array $groups,
293        bool $groupSupport,
294    ): mixed {
295        $queryHelper = new QueryHelper($userId, $groups);
296        $query = sprintf(
297            "SELECT
298                 id, lang, solution_id, revision_id, active, sticky, keywords,
299                 thema, content, author, email, comment, updated, date_start,
300                 date_end, created, notes
301            FROM
302                %s%s fd
303            LEFT JOIN
304                %sfaqdata_group fdg
305            ON
306                fd.id = fdg.record_id
307            LEFT JOIN
308                %sfaqdata_user fdu
309            ON
310                fd.id = fdu.record_id
311            WHERE
312                fd.id = %d
313            %s
314            AND
315                fd.lang = '%s'
316                %s",
317            Database::getTablePrefix(),
318            $faqRevisionId !== null ? 'faqdata_revisions' : 'faqdata',
319            Database::getTablePrefix(),
320            Database::getTablePrefix(),
321            $faqId,
322            $faqRevisionId !== null ? 'AND revision_id = ' . $faqRevisionId : '',
323            $this->configuration->getDb()->escape($faqLanguage),
324            $isAdmin ? 'AND 1=1' : $queryHelper->queryPermission($groupSupport),
325        );
326
327        return $this->configuration->getDb()->query($query);
328    }
329
330    public function fetchFaqByIdAndCategoryId(
331        int $faqId,
332        int $categoryId,
333        bool $onlyActive,
334        int $userId,
335        array $groups,
336        bool $groupSupport,
337    ): ?object {
338        $queryHelper = new QueryHelper($userId, $groups);
339        $now = date(format: 'YmdHis');
340        $query = sprintf(
341            "
342            SELECT
343                fd.id AS id,
344                fd.lang AS lang,
345                fd.solution_id AS solution_id,
346                fd.revision_id AS revision_id,
347                fd.active AS active,
348                fd.sticky AS sticky,
349                fd.keywords AS keywords,
350                fd.thema AS question,
351                fd.content AS answer,
352                fd.author AS author,
353                fd.email AS email,
354                fd.comment AS comment,
355                fd.updated AS updated,
356                fd.date_start AS date_start,
357                fd.date_end AS date_end,
358                fd.created AS created,
359                fcr.category_id AS category_id
360            FROM
361                %sfaqdata AS fd
362            LEFT JOIN
363                %sfaqcategoryrelations AS fcr
364            ON
365                fd.id = fcr.record_id
366            AND
367                fd.lang = fcr.record_lang
368            LEFT JOIN
369                %sfaqdata_group AS fdg
370            ON
371                fd.id = fdg.record_id
372            LEFT JOIN
373                %sfaqdata_user AS fdu
374            ON
375                fd.id = fdu.record_id
376            WHERE
377                fd.id = %d
378            AND
379                fcr.category_id = %d
380            AND
381                fd.lang = '%s'
382                %s
383                %s",
384            Database::getTablePrefix(),
385            Database::getTablePrefix(),
386            Database::getTablePrefix(),
387            Database::getTablePrefix(),
388            $faqId,
389            $categoryId,
390            $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
391            $onlyActive
392                ? sprintf("AND fd.active = 'yes' AND fd.date_start <= '%s' AND fd.date_end >= '%s'", $now, $now)
393                : '',
394            $queryHelper->queryPermission($groupSupport),
395        );
396
397        $result = $this->configuration->getDb()->query($query);
398        $row = $this->configuration->getDb()->fetchObject($result);
399
400        return $row instanceof \stdClass ? $row : null;
401    }
402
403    public function fetchRowBySolutionId(int $solutionId, int $userId, array $groups, bool $groupSupport): ?\stdClass
404    {
405        $queryHelper = new QueryHelper($userId, $groups);
406        $query = sprintf(
407            'SELECT
408                fd.*, COALESCE(fdg.group_id, -1) AS group_id, fdu.user_id
409            FROM
410                %sfaqdata fd
411            LEFT JOIN (
412                SELECT record_id, group_id FROM %sfaqdata_group fdg WHERE fdg.group_id <> -1
413                UNION ALL
414                SELECT fd.id AS record_id, -1 AS group_id FROM %sfaqdata fd WHERE fd.solution_id = %d
415            ) AS fdg
416            ON
417                fd.id = fdg.record_id
418            LEFT JOIN
419                %sfaqdata_user fdu
420            ON
421                fd.id = fdu.record_id
422            WHERE
423                fd.solution_id = %d
424                %s',
425            Database::getTablePrefix(),
426            Database::getTablePrefix(),
427            Database::getTablePrefix(),
428            $solutionId,
429            Database::getTablePrefix(),
430            $solutionId,
431            $queryHelper->queryPermission($groupSupport),
432        );
433
434        $result = $this->configuration->getDb()->query($query);
435
436        $row = $this->configuration->getDb()->fetchObject($result);
437
438        if (false === $row || null === $row) {
439            $restrictionQuery = sprintf('SELECT 1
440                FROM %1$sfaqdata fd
441                LEFT JOIN %1$sfaqdata_user fdu ON fd.id = fdu.record_id
442                LEFT JOIN %1$sfaqdata_group fdg ON fd.id = fdg.record_id
443                WHERE fd.solution_id = %2$d
444                AND (fdu.user_id IS NOT NULL OR fdg.group_id IS NOT NULL)
445                LIMIT 1', Database::getTablePrefix(), $solutionId);
446            $restrictionResult = $this->configuration->getDb()->query($restrictionQuery);
447            $hasRestriction =
448                $restrictionResult !== false
449                && $restrictionResult !== null
450                && $this->configuration->getDb()->fetchObject($restrictionResult) instanceof \stdClass;
451
452            if (!$hasRestriction) {
453                $fallbackQuery = sprintf(
454                    'SELECT * FROM %sfaqdata fd WHERE fd.solution_id = %d LIMIT 1',
455                    Database::getTablePrefix(),
456                    $solutionId,
457                );
458                $fallbackResult = $this->configuration->getDb()->query($fallbackQuery);
459                $row = $this->configuration->getDb()->fetchObject($fallbackResult);
460            }
461        }
462
463        return $row instanceof \stdClass ? $row : null;
464    }
465
466    /**
467     * @return list<\stdClass>
468     */
469    public function fetchAvailableFaqsByCategoryId(
470        int $categoryId,
471        string $orderTable,
472        string $orderColumn,
473        string $sortDirection,
474        int $userId,
475        array $groups,
476        bool $groupSupport,
477    ): array {
478        $now = date(format: 'YmdHis');
479        $queryHelper = new QueryHelper($userId, $groups);
480        $query = sprintf(
481            "
482            SELECT
483                fd.id AS id,
484                fd.lang AS lang,
485                fd.thema AS thema,
486                fd.content AS record_content,
487                fd.updated AS updated,
488                fcr.category_id AS category_id,
489                fv.visits AS visits,
490                fd.created AS created
491            FROM
492                %sfaqdata AS fd
493            LEFT JOIN
494                %sfaqcategoryrelations AS fcr
495            ON
496                fd.id = fcr.record_id
497            AND
498                fd.lang = fcr.record_lang
499            LEFT JOIN
500                %sfaqvisits AS fv
501            ON
502                fd.id = fv.id
503            AND
504                fv.lang = fd.lang
505            WHERE
506                fd.date_start <= '%s'
507            AND
508                fd.date_end   >= '%s'
509            AND
510                fd.active = 'yes'
511            AND
512                fcr.category_id = %d
513            AND
514                fd.lang = '%s'
515                %s
516            ORDER BY
517                %s.%s %s",
518            Database::getTablePrefix(),
519            Database::getTablePrefix(),
520            Database::getTablePrefix(),
521            $now,
522            $now,
523            $categoryId,
524            $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
525            $queryHelper->queryPermissionExistsAny($groupSupport),
526            $orderTable,
527            $orderColumn,
528            $sortDirection,
529        );
530
531        return $this->fetchAllRows($this->configuration->getDb()->query($query));
532    }
533
534    /**
535     * @return list<\stdClass>
536     */
537    public function fetchFaqsByIds(
538        string $records,
539        bool $onlyActive,
540        int $userId,
541        array $groups,
542        bool $groupSupport,
543    ): array {
544        $now = date(format: 'YmdHis');
545        $queryHelper = new QueryHelper($userId, $groups);
546        $query = sprintf(
547            "SELECT
548                 fd.id AS id,
549                 fd.lang AS lang,
550                 fd.thema AS question,
551                 fd.content AS answer,
552                 fd.updated AS updated,
553                 fd.created AS created,
554                 fcr.category_id AS category_id,
555                 fv.visits AS visits
556            FROM
557                %sfaqdata fd
558            LEFT JOIN
559                %sfaqcategoryrelations fcr
560            ON
561                fd.id = fcr.record_id
562            AND
563                fd.lang = fcr.record_lang
564            LEFT JOIN
565                %sfaqdata_group fdg
566            ON
567                fd.id = fdg.record_id
568            LEFT JOIN
569                %sfaqvisits AS fv
570            ON
571                fd.id = fv.id
572            AND
573                fv.lang = fd.lang
574            LEFT JOIN
575                %sfaqdata_user fdu
576            ON
577                fd.id = fdu.record_id
578            WHERE
579                fd.id IN (%s)
580            AND
581                fd.lang = '%s'
582                %s
583                %s",
584            Database::getTablePrefix(),
585            Database::getTablePrefix(),
586            Database::getTablePrefix(),
587            Database::getTablePrefix(),
588            Database::getTablePrefix(),
589            $records,
590            $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
591            $onlyActive
592                ? sprintf("AND fd.active = 'yes' AND fd.date_start <= '%s' AND fd.date_end >= '%s'", $now, $now)
593                : '',
594            $queryHelper->queryPermission($groupSupport),
595        );
596
597        return $this->fetchAllRows($this->configuration->getDb()->query($query));
598    }
599
600    /**
601     * @return list<\stdClass>
602     */
603    public function fetchStickyFaqs(int $userId, array $groups, bool $groupSupport): array
604    {
605        $queryHelper = new QueryHelper($userId, $groups);
606        $query = sprintf(
607            "
608            SELECT
609                fd.id AS id,
610                fd.lang AS lang,
611                fd.thema AS thema,
612                fd.sticky_order AS sticky_order,
613                fcr.category_id AS category_id,
614                fv.visits AS visits
615            FROM
616                %sfaqdata fd
617            LEFT JOIN
618                %sfaqvisits fv
619            ON
620                fd.id = fv.id
621            AND
622                fd.lang = fv.lang
623            LEFT JOIN
624                %sfaqcategoryrelations fcr
625            ON
626                fd.id = fcr.record_id
627            AND
628                fd.lang = fcr.record_lang
629            LEFT JOIN
630                %sfaqdata_group AS fdg
631            ON
632                fd.id = fdg.record_id
633            LEFT JOIN
634                %sfaqdata_user AS fdu
635            ON
636                fd.id = fdu.record_id
637            WHERE
638                fd.lang = '%s'
639            AND
640                fd.active = 'yes'
641            AND
642                fd.sticky = 1
643            %s
644            GROUP BY
645                fd.id, fd.lang, fd.thema, fcr.category_id, fv.visits
646            ORDER BY
647                fv.visits DESC",
648            Database::getTablePrefix(),
649            Database::getTablePrefix(),
650            Database::getTablePrefix(),
651            Database::getTablePrefix(),
652            Database::getTablePrefix(),
653            $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
654            $queryHelper->queryPermission($groupSupport),
655        );
656
657        return $this->fetchAllRows($this->configuration->getDb()->query($query));
658    }
659
660    /**
661     * @return list<\stdClass>
662     */
663    public function fetchAllFaqs(
664        ?array $condition,
665        string $orderBy,
666        int $userId,
667        array $groups,
668        bool $groupSupport,
669    ): array {
670        $where = $this->buildConditionWhereClause($condition);
671
672        // prevents multiple display of FAQ in case it is tagged under multiple groups.
673        $groupBy =
674            ' group by fd.id, fcr.category_id,fd.solution_id,fd.revision_id,fd.active,fd.sticky,fd.keywords,'
675            . 'fd.thema,fd.content,fd.author,fd.email,fd.comment,fd.updated,'
676            . 'fd.date_start,fd.date_end,fd.sticky,fd.created,fd.notes,fd.lang ';
677        $queryHelper = new QueryHelper($userId, $groups);
678        $query = sprintf(
679            '
680            SELECT
681                fd.id AS id,
682                fd.lang AS lang,
683                fcr.category_id AS category_id,
684                fd.solution_id AS solution_id,
685                fd.revision_id AS revision_id,
686                fd.active AS active,
687                fd.sticky AS sticky,
688                fd.keywords AS keywords,
689                fd.thema AS thema,
690                fd.content AS content,
691                fd.author AS author,
692                fd.email AS email,
693                fd.comment AS comment,
694                fd.updated AS updated,
695                fd.date_start AS date_start,
696                fd.date_end AS date_end,
697                fd.sticky AS sticky,
698                fd.created AS created,
699                fd.notes AS notes
700            FROM
701                %sfaqdata fd
702            LEFT JOIN
703                %sfaqcategoryrelations fcr
704            ON
705                fd.id = fcr.record_id
706            AND
707                fd.lang = fcr.record_lang
708            LEFT JOIN
709                %sfaqdata_group AS fdg
710            ON
711                fd.id = fdg.record_id
712            LEFT JOIN
713                %sfaqdata_user AS fdu
714            ON
715                fd.id = fdu.record_id
716            %s
717            %s
718            %s
719            %s',
720            Database::getTablePrefix(),
721            Database::getTablePrefix(),
722            Database::getTablePrefix(),
723            Database::getTablePrefix(),
724            $where,
725            $queryHelper->queryPermission($groupSupport),
726            $groupBy,
727            $orderBy,
728        );
729
730        return $this->fetchAllRows($this->configuration->getDb()->query($query));
731    }
732
733    public function insert(FaqEntity $faqEntity): void
734    {
735        $query = sprintf(
736            "INSERT INTO %sfaqdata
737            (id, lang, solution_id, revision_id, active, sticky, keywords, thema, content, author, email, comment,
738            updated, date_start, date_end, created, notes)
739            VALUES
740            (%d, '%s', %d, %d, '%s', %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
741            Database::getTablePrefix(),
742            $faqEntity->getId(),
743            $this->configuration->getDb()->escape($faqEntity->getLanguage()),
744            $faqEntity->getSolutionId(),
745            $faqEntity->getRevisionId(),
746            $faqEntity->isActive() ? 'yes' : 'no',
747            $faqEntity->isSticky() ? 1 : 0,
748            $this->configuration->getDb()->escape($faqEntity->getKeywords()),
749            $this->configuration->getDb()->escape($faqEntity->getQuestion()),
750            $this->configuration->getDb()->escape($faqEntity->getAnswer()),
751            $this->configuration->getDb()->escape($faqEntity->getAuthor()),
752            $this->configuration->getDb()->escape($faqEntity->getEmail()),
753            $faqEntity->isComment() ? 'y' : 'n',
754            date(format: 'YmdHis'),
755            '00000000000000',
756            '99991231235959',
757            date(format: 'Y-m-d H:i:s'),
758            $this->configuration->getDb()->escape($faqEntity->getNotes()),
759        );
760
761        $this->configuration->getDb()->query($query);
762    }
763
764    public function update(FaqEntity $faqEntity): void
765    {
766        $query = sprintf(
767            "UPDATE %sfaqdata SET
768            revision_id = %d,
769            active = '%s',
770            sticky = %d,
771            keywords = '%s',
772            thema = '%s',
773            content = '%s',
774            author = '%s',
775            email = '%s',
776            comment = '%s',
777            date_start = '%s',
778            date_end = '%s',
779            notes = '%s'",
780            Database::getTablePrefix(),
781            $faqEntity->getRevisionId(),
782            $faqEntity->isActive() ? 'yes' : 'no',
783            $faqEntity->isSticky() ? 1 : 0,
784            $this->configuration->getDb()->escape($faqEntity->getKeywords()),
785            $this->configuration->getDb()->escape($faqEntity->getQuestion()),
786            $this->configuration->getDb()->escape($faqEntity->getAnswer()),
787            $this->configuration->getDb()->escape($faqEntity->getAuthor()),
788            $this->configuration->getDb()->escape($faqEntity->getEmail()),
789            $faqEntity->isComment() ? 'y' : 'n',
790            $faqEntity->getValidFrom()->format('YmdHis'),
791            $faqEntity->getValidTo()->format('YmdHis'),
792            $this->configuration->getDb()->escape($faqEntity->getNotes()),
793        );
794
795        // Conditionally add the updated field
796        $updatedDate = $faqEntity->getUpdatedDate();
797        if ($updatedDate instanceof DateTime) {
798            $query .= sprintf(", updated = '%s'", $updatedDate->format('YmdHis'));
799        }
800
801        $query .= sprintf(
802            " WHERE id = %d AND lang = '%s'",
803            $faqEntity->getId(),
804            $this->configuration->getDb()->escape($faqEntity->getLanguage()),
805        );
806
807        $this->configuration->getDb()->query($query);
808    }
809
810    public function deleteByIdAndLanguage(int $faqId, string $faqLang): void
811    {
812        $queries = [
813            sprintf('DELETE FROM %sfaqbookmarks WHERE faqid = %d', Database::getTablePrefix(), $faqId),
814            sprintf(
815                "DELETE FROM %sfaqchanges WHERE beitrag = %d AND lang = '%s'",
816                Database::getTablePrefix(),
817                $faqId,
818                $this->configuration->getDb()->escape($faqLang),
819            ),
820            sprintf(
821                "DELETE FROM %sfaqcategoryrelations WHERE record_id = %d AND record_lang = '%s'",
822                Database::getTablePrefix(),
823                $faqId,
824                $this->configuration->getDb()->escape($faqLang),
825            ),
826            sprintf(
827                "DELETE FROM %sfaqdata WHERE id = %d AND lang = '%s'",
828                Database::getTablePrefix(),
829                $faqId,
830                $this->configuration->getDb()->escape($faqLang),
831            ),
832            sprintf(
833                "DELETE FROM %sfaqdata_revisions WHERE id = %d AND lang = '%s'",
834                Database::getTablePrefix(),
835                $faqId,
836                $this->configuration->getDb()->escape($faqLang),
837            ),
838            sprintf(
839                "DELETE FROM %sfaqvisits WHERE id = %d AND lang = '%s'",
840                Database::getTablePrefix(),
841                $faqId,
842                $this->configuration->getDb()->escape($faqLang),
843            ),
844            sprintf('DELETE FROM %sfaqdata_user WHERE record_id = %d', Database::getTablePrefix(), $faqId),
845            sprintf('DELETE FROM %sfaqdata_group WHERE record_id = %d', Database::getTablePrefix(), $faqId),
846            sprintf('DELETE FROM %sfaqdata_tags WHERE record_id = %d', Database::getTablePrefix(), $faqId),
847            sprintf(
848                'DELETE FROM %sfaqdata_tags WHERE %sfaqdata_tags.record_id NOT IN (SELECT %sfaqdata.id FROM %sfaqdata)',
849                Database::getTablePrefix(),
850                Database::getTablePrefix(),
851                Database::getTablePrefix(),
852                Database::getTablePrefix(),
853            ),
854            sprintf('DELETE FROM %sfaqcomments WHERE id = %d', Database::getTablePrefix(), $faqId),
855            sprintf('DELETE FROM %sfaqvoting WHERE artikel = %d', Database::getTablePrefix(), $faqId),
856        ];
857
858        foreach ($queries as $query) {
859            $this->configuration->getDb()->query($query);
860        }
861    }
862
863    public function queryRenderableFaqsByCategoryId(
864        int $categoryId,
865        string $order,
866        int $userId,
867        array $groups,
868        bool $groupSupport,
869        int $offset = 0,
870        int $rowcount = 0,
871    ): mixed {
872        $now = date(format: 'YmdHis');
873        $queryHelper = new QueryHelper($userId, $groups);
874        $query = sprintf(
875            "
876            SELECT
877                fd.id AS id,
878                fd.lang AS lang,
879                fd.sticky AS sticky,
880                fd.thema AS question,
881                fd.content as answer,
882                fcr.category_id AS category_id,
883                fv.visits AS visits
884            FROM
885                %sfaqdata AS fd
886            LEFT JOIN
887                %sfaqcategoryrelations AS fcr
888            ON
889                fd.id = fcr.record_id
890            AND
891                fd.lang = fcr.record_lang
892            LEFT JOIN
893                %sfaqvisits AS fv
894            ON
895                fd.id = fv.id
896            AND
897                fv.lang = fd.lang
898            WHERE
899                fd.date_start <= '%s'
900            AND
901                fd.date_end   >= '%s'
902            AND
903                fd.active = 'yes'
904            AND
905                fcr.category_id = %d
906            AND
907                fd.lang = '%s'
908            %s
909            %s",
910            Database::getTablePrefix(),
911            Database::getTablePrefix(),
912            Database::getTablePrefix(),
913            $now,
914            $now,
915            $categoryId,
916            $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
917            $queryHelper->queryPermissionExistsAny($groupSupport),
918            $order,
919        );
920
921        return $this->configuration->getDb()->query($query, $offset, $rowcount);
922    }
923
924    /**
925     * Counts the renderable FAQs of one category for the given permission context,
926     * matching the filters of queryRenderableFaqsByCategoryId().
927     */
928    public function countRenderableFaqsByCategoryId(
929        int $categoryId,
930        int $userId,
931        array $groups,
932        bool $groupSupport,
933    ): int {
934        $now = date(format: 'YmdHis');
935        $queryHelper = new QueryHelper($userId, $groups);
936        $query = sprintf(
937            'SELECT COUNT(*) AS total FROM %sfaqdata fd '
938            . "WHERE fd.date_start <= '%s' AND fd.date_end >= '%s' AND fd.active = 'yes' AND fd.lang = '%s' "
939            . 'AND EXISTS (SELECT 1 FROM %sfaqcategoryrelations fcr '
940            . 'WHERE fcr.record_id = fd.id AND fcr.record_lang = fd.lang AND fcr.category_id = %d) %s',
941            Database::getTablePrefix(),
942            $now,
943            $now,
944            $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
945            Database::getTablePrefix(),
946            $categoryId,
947            $queryHelper->queryPermissionExistsAny($groupSupport),
948        );
949
950        $result = $this->configuration->getDb()->query($query);
951        $row = $result !== false ? $this->configuration->getDb()->fetchObject($result) : null;
952
953        return is_object($row) ? (int) $row->total : 0;
954    }
955
956    public function queryRenderableFaqsByIds(
957        string $records,
958        string $orderExpression,
959        string $sortDirection,
960        int $userId,
961        array $groups,
962        bool $groupSupport,
963    ): mixed {
964        $now = date(format: 'YmdHis');
965        $queryHelper = new QueryHelper($userId, $groups);
966        $query = sprintf(
967            "
968            SELECT
969                fd.id AS id,
970                fd.lang AS lang,
971                fd.thema AS question,
972                fd.content AS answer,
973                fcr.category_id AS category_id,
974                fv.visits AS visits
975            FROM
976                %sfaqdata AS fd
977            LEFT JOIN
978                %sfaqcategoryrelations AS fcr
979            ON
980                fd.id = fcr.record_id
981            AND
982                fd.lang = fcr.record_lang
983            LEFT JOIN
984                %sfaqvisits AS fv
985            ON
986                fd.id = fv.id
987            AND
988                fv.lang = fd.lang
989            WHERE
990                fd.date_start <= '%s'
991            AND
992                fd.date_end   >= '%s'
993            AND
994                fd.active = 'yes'
995            AND
996                fd.id IN (%s)
997            AND
998                fd.lang = '%s'
999                %s
1000            ORDER BY
1001                %s %s",
1002            Database::getTablePrefix(),
1003            Database::getTablePrefix(),
1004            Database::getTablePrefix(),
1005            $now,
1006            $now,
1007            $records,
1008            $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
1009            $queryHelper->queryPermissionExistsAny($groupSupport),
1010            $orderExpression,
1011            $sortDirection,
1012        );
1013
1014        return $this->configuration->getDb()->query($query);
1015    }
1016
1017    /**
1018     * Builds the WHERE clause for getAllFaqs() from a field => condition map, escaping values.
1019     *
1020     * @param array<string, mixed>|null $condition
1021     */
1022    private function buildConditionWhereClause(?array $condition): string
1023    {
1024        if ($condition === null) {
1025            return '';
1026        }
1027
1028        $condition = array_filter($condition, static fn($value): bool => $value !== null);
1029
1030        $num = count($condition);
1031        $where = 'WHERE ';
1032        foreach ($condition as $field => $data) {
1033            --$num;
1034            $where .= $field;
1035            if (is_array($data)) {
1036                $where .= ' IN (';
1037                $separator = '';
1038                foreach ($data as $value) {
1039                    $where .= $separator . "'" . $this->configuration->getDb()->escape((string) $value) . "'";
1040                    $separator = ', ';
1041                }
1042
1043                $where .= ')';
1044            }
1045
1046            if (!is_array($data) && $data === 'IS NOT NULL') {
1047                $where .= ' IS NOT NULL';
1048            }
1049
1050            if (!is_array($data) && $data === 'IS NULL') {
1051                $where .= ' IS NULL';
1052            }
1053
1054            if (!is_array($data) && $data !== 'IS NOT NULL' && $data !== 'IS NULL') {
1055                $where .= " = '" . $this->configuration->getDb()->escape((string) $data) . "'";
1056            }
1057
1058            if ($num > 0) {
1059                $where .= ' AND ';
1060            }
1061        }
1062
1063        return $where;
1064    }
1065
1066    /**
1067     * Collects every row of a database result into an array of objects.
1068     *
1069     * @return list<\stdClass>
1070     */
1071    private function fetchAllRows(mixed $result): array
1072    {
1073        $rows = [];
1074        while (true) {
1075            $row = $this->configuration->getDb()->fetchObject($result);
1076            if (!$row instanceof \stdClass) {
1077                break;
1078            }
1079
1080            $rows[] = $row;
1081        }
1082
1083        return $rows;
1084    }
1085}