1<?php
2
3namespace App\Repository;
4
5/**
6 * Repository class for fetching data from the bugdb_comments table.
7 */
8class CommentRepository
9{
10    /**
11     * Database handler.
12     * @var \PDO
13     */
14    private $dbh;
15
16    /**
17     * Class constructor.
18     */
19    public function __construct(\PDO $dbh)
20    {
21        $this->dbh = $dbh;
22    }
23
24    /**
25     * Fetch bug comments
26     */
27    public function findByBugId(int $id): array
28    {
29        $sql = 'SELECT c.id, c.email, c.comment, c.comment_type,
30                    UNIX_TIMESTAMP(c.ts) AS added,
31                    c.reporter_name AS comment_name
32                FROM bugdb_comments c
33                WHERE c.bug = ?
34                GROUP BY c.id ORDER BY c.ts
35        ';
36
37        $statement = $this->dbh->prepare($sql);
38        $statement->execute([$id]);
39
40        return $statement->fetchAll();
41    }
42
43    /**
44     * Find all log comments for documentation.
45     * TODO: Check if this method is still used in the api.php endpoint.
46     */
47    public function findDocsComments(int $interval): array
48    {
49        $sql = "SELECT bugdb_comments.reporter_name, COUNT(*) as count
50                FROM bugdb_comments, bugdb
51                WHERE comment_type =  'log'
52                    AND (package_name IN ('Doc Build problem', 'Documentation problem', 'Translation problem', 'Online Doc Editor problem') OR bug_type = 'Documentation Problem')
53                    AND comment LIKE  '%+Status:      Closed</span>%'
54                    AND date_sub(curdate(), INTERVAL ? DAY) <= ts
55                    AND bugdb.id = bugdb_comments.bug
56                GROUP BY bugdb_comments.reporter_name
57                ORDER BY count DESC
58        ";
59
60        $statement = $this->dbh->prepare($sql);
61        $statement->execute([$interval]);
62
63        return $statement->fetchAll();
64    }
65}
66