1<?php
2
3use dokuwiki\Extension\Plugin;
4use dokuwiki\ChangeLog\PageChangeLog;
5use dokuwiki\ErrorHandler;
6use dokuwiki\Extension\AuthPlugin;
7use dokuwiki\plugin\sqlite\SQLiteDB;
8
9/**
10 * DokuWiki Plugin acknowledge (Helper Component)
11 *
12 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
13 * @author  Andreas Gohr, Anna Dabrowska <dokuwiki@cosmocode.de>
14 */
15class helper_plugin_acknowledge extends Plugin
16{
17    protected $db;
18
19    // region Database Management
20
21    /**
22     * Constructor
23     *
24     * @return void
25     * @throws Exception
26     */
27    public function __construct()
28    {
29        if ($this->db === null) {
30            try {
31                $this->db = new SQLiteDB('acknowledgement', __DIR__ . '/db');
32
33                // register our custom functions
34                $this->db->getPdo()->sqliteCreateFunction('AUTH_ISMEMBER', [$this, 'auth_isMember'], -1);
35                $this->db->getPdo()->sqliteCreateFunction('MATCHES_PAGE_PATTERN', [$this, 'matchPagePattern'], 2);
36            } catch (\Exception $exception) {
37                if (defined('DOKU_UNITTEST')) throw new \RuntimeException('Could not load SQLite', 0, $exception);
38                ErrorHandler::logException($exception);
39                msg($this->getLang('error sqlite plugin missing'), -1);
40                throw $exception;
41            }
42        }
43    }
44
45    /**
46     * Wrapper for test DB access
47     *
48     * @return SQLiteDB
49     */
50    public function getDB()
51    {
52        return $this->db;
53    }
54
55    /**
56     * Wrapper function for auth_isMember which accepts groups as string
57     *
58     * @param string $memberList
59     * @param string $user
60     * @param string $groups
61     *
62     * @return bool
63     */
64    // phpcs:ignore PSR1.Methods.CamelCapsMethodName.NotCamelCaps
65    public function auth_isMember($memberList, $user, $groups)
66    {
67        return auth_isMember($memberList, $user, explode('///', $groups));
68    }
69
70    /**
71     * Fills the page index with all unknown pages from the fulltext index
72     * @return void
73     */
74    public function updatePageIndex()
75    {
76        $pages = idx_getIndex('page', '');
77        $sql = "INSERT OR IGNORE INTO pages (page, lastmod) VALUES (?,?)";
78
79        $this->db->getPdo()->beginTransaction();
80        foreach ($pages as $page) {
81            $page = trim($page);
82            $lastmod = @filemtime(wikiFN($page));
83            if ($lastmod) {
84                try {
85                    $this->db->exec($sql, [$page, $lastmod]);
86                } catch (\Exception $exception) {
87                    $this->db->getPdo()->rollBack();
88                    throw $exception;
89                }
90            }
91        }
92        $this->db->getPdo()->commit();
93    }
94
95    /**
96     * Check if the given pattern matches the given page
97     *
98     * @param string $pattern the pattern to check against
99     * @param string $page the cleaned pageid to check
100     * @return bool
101     */
102    public function matchPagePattern($pattern, $page)
103    {
104        if (trim($pattern, ':') == '**') return true; // match all
105
106        // regex patterns
107        if ($pattern[0] == '/') {
108            return (bool)preg_match($pattern, ":$page");
109        }
110
111        $pns = ':' . getNS($page) . ':';
112
113        $ans = ':' . cleanID($pattern) . ':';
114        if (substr($pattern, -2) == '**') {
115            // upper namespaces match
116            if (strpos($pns, $ans) === 0) {
117                return true;
118            }
119        } elseif (substr($pattern, -1) == '*') {
120            // namespaces match exact
121            if ($ans === $pns) {
122                return true;
123            }
124        } elseif (cleanID($pattern) == $page) {
125            // exact match
126            return true;
127        }
128
129        return false;
130    }
131
132    /**
133     * Returns all users, formatted for autocomplete
134     *
135     * @return array
136     */
137    public function getUsers()
138    {
139        /** @var AuthPlugin $auth */
140        global $auth;
141
142        if (!$auth->canDo('getUsers')) {
143            return [];
144        }
145
146        $cb = function ($k, $v) {
147            return [
148              'value' => $k,
149              'label' => $k  . ' (' . $v['name'] . ')'
150            ];
151        };
152        $users = $auth->retrieveUsers();
153        $users = array_map($cb, array_keys($users), array_values($users));
154
155        return $users;
156    }
157
158    // endregion
159    // region Page Data
160
161    /**
162     * Delete a page
163     *
164     * Cascades to delete all assigned data, etc.
165     *
166     * @param string $page Page ID
167     */
168    public function removePage($page)
169    {
170        $sql = "DELETE FROM pages WHERE page = ?";
171        $this->db->exec($sql, $page);
172    }
173
174    /**
175     * Update last modified date of page if content has changed
176     *
177     * @param string $page Page ID
178     * @param int $lastmod timestamp of last non-minor change
179     */
180    public function storePageDate($page, $lastmod, $newContent)
181    {
182        $changelog = new PageChangeLog($page);
183        $revs = $changelog->getRevisions(0, 1);
184
185        // compare content
186        $oldContent = str_replace(NL, '', io_readFile(wikiFN($page, $revs[0])));
187        $newContent = str_replace(NL, '', $newContent);
188        if ($oldContent === $newContent) return;
189
190        $sql = "REPLACE INTO pages (page, lastmod) VALUES (?,?)";
191        $this->db->exec($sql, [$page, $lastmod]);
192    }
193
194    // endregion
195    // region Assignments
196
197    /**
198     * Clears direct assignments for a page
199     *
200     * @param string $page Page ID
201     */
202    public function clearPageAssignments($page)
203    {
204        $sql = "UPDATE assignments SET pageassignees = '' WHERE page = ?";
205        $this->db->exec($sql, $page);
206    }
207
208    /**
209     * Set assignees for a given page as manually specified
210     *
211     * @param string $page Page ID
212     * @param string $assignees
213     * @return void
214     */
215    public function setPageAssignees($page, $assignees)
216    {
217        $assignees = implode(',', array_unique(array_filter(array_map('trim', explode(',', $assignees)))));
218
219        $sql = "REPLACE INTO assignments ('page', 'pageassignees') VALUES (?,?)";
220        $this->db->exec($sql, [$page, $assignees]);
221    }
222
223    /**
224     * Set assignees for a given page from the patterns
225     * @param string $page Page ID
226     */
227    public function setAutoAssignees($page)
228    {
229        $patterns = $this->getAssignmentPatterns();
230
231        // given assignees
232        $assignees = '';
233
234        // find all patterns that match the page and add the configured assignees
235        foreach ($patterns as $pattern => $assignees) {
236            if ($this->matchPagePattern($pattern, $page)) {
237                $assignees .= ',' . $assignees;
238            }
239        }
240
241        // remove duplicates and empty entries
242        $assignees = implode(',', array_unique(array_filter(array_map('trim', explode(',', $assignees)))));
243
244        // store the assignees
245        $sql = "REPLACE INTO assignments ('page', 'autoassignees') VALUES (?,?)";
246        $this->db->exec($sql, [$page, $assignees]);
247    }
248
249    /**
250     * Is the given user one of the assignees for this page
251     *
252     * @param string $page Page ID
253     * @param string $user user name to check
254     * @param string[] $groups groups this user is in
255     * @return bool
256     */
257    public function isUserAssigned($page, $user, $groups)
258    {
259        $sql = "SELECT pageassignees,autoassignees FROM assignments WHERE page = ?";
260        $record = $this->db->queryRecord($sql, $page);
261        if (!$record) return false;
262        $assignees = $record['pageassignees'] . ',' . $record['autoassignees'];
263        return auth_isMember($assignees, $user, $groups);
264    }
265
266    /**
267     * Fetch all assignments for a given user, with additional page information,
268     * by default filtering already granted acknowledgements.
269     * Filter can be switched off via $includeDone
270     *
271     * @param string $user
272     * @param array $groups
273     * @param bool $includeDone
274     *
275     * @return array|bool
276     */
277    public function getUserAssignments($user, $groups, $includeDone = false)
278    {
279        $sql = "SELECT A.page, A.pageassignees, A.autoassignees, B.lastmod, C.user, C.ack FROM assignments A
280                JOIN pages B
281                ON A.page = B.page
282                LEFT JOIN acks C
283                ON A.page = C.page AND ( (C.user = ? AND C.ack > B.lastmod) )
284                WHERE AUTH_ISMEMBER(A.pageassignees || ',' || A.autoassignees , ? , ?)";
285
286        if (!$includeDone) {
287            $sql .= ' AND ack IS NULL';
288        }
289
290        return $this->db->queryAll($sql, $user, $user, implode('///', $groups));
291    }
292
293    /**
294     * Resolve names of users assigned to a given page
295     *
296     * This can be slow on huge user bases!
297     *
298     * @param string $page
299     * @return array|false
300     */
301    public function getPageAssignees($page)
302    {
303        /** @var AuthPlugin $auth */
304        global $auth;
305
306        $sql = "SELECT pageassignees || ',' || autoassignees AS 'assignments'
307                  FROM assignments
308                 WHERE page = ?";
309        $assignments = $this->db->queryValue($sql, $page);
310
311        $users = [];
312        foreach (explode(',', $assignments) as $item) {
313            $item = trim($item);
314            if ($item === '') continue;
315            if ($item[0] == '@') {
316                $users = array_merge(
317                    $users,
318                    array_keys($auth->retrieveUsers(0, 0, ['grps' => substr($item, 1)]))
319                );
320            } else {
321                $users[] = $item;
322            }
323        }
324
325        return array_unique($users);
326    }
327
328    // endregion
329    // region Assignment Patterns
330
331    /**
332     * Get all the assignment patterns
333     * @return array (pattern => assignees)
334     */
335    public function getAssignmentPatterns()
336    {
337        $sql = "SELECT pattern, assignees FROM assignments_patterns";
338        return $this->db->queryKeyValueList($sql);
339    }
340
341    /**
342     * Save new assignment patterns
343     *
344     * This resaves all patterns and reapplies them
345     *
346     * @param array $patterns (pattern => assignees)
347     */
348    public function saveAssignmentPatterns($patterns)
349    {
350        $this->db->getPdo()->beginTransaction();
351        try {
352
353            /** @noinspection SqlWithoutWhere Remove all assignments */
354            $sql = "UPDATE assignments SET autoassignees = ''";
355            $this->db->exec($sql);
356
357            /** @noinspection SqlWithoutWhere Remove all patterns */
358            $sql = "DELETE FROM assignments_patterns";
359            $this->db->exec($sql);
360
361            // insert new patterns and gather affected pages
362            $pages = [];
363
364            $sql = "REPLACE INTO assignments_patterns (pattern, assignees) VALUES (?,?)";
365            foreach ($patterns as $pattern => $assignees) {
366                $pattern = trim($pattern);
367                $assignees = trim($assignees);
368                if (!$pattern || !$assignees) continue;
369                $this->db->exec($sql, [$pattern, $assignees]);
370
371                // patterns may overlap, so we need to gather all affected pages first
372                $affectedPages = $this->getPagesMatchingPattern($pattern);
373                foreach ($affectedPages as $page) {
374                    if (isset($pages[$page])) {
375                        $pages[$page] .= ',' . $assignees;
376                    } else {
377                        $pages[$page] = $assignees;
378                    }
379                }
380            }
381
382            $sql = "INSERT INTO assignments (page, autoassignees) VALUES (?, ?)
383                ON CONFLICT(page)
384                DO UPDATE SET autoassignees = ?";
385            foreach ($pages as $page => $assignees) {
386                // remove duplicates and empty entries
387                $assignees = implode(',', array_unique(array_filter(array_map('trim', explode(',', $assignees)))));
388                $this->db->exec($sql, [$page, $assignees, $assignees]);
389            }
390        } catch (Exception $e) {
391            $this->db->getPdo()->rollBack();
392            throw $e;
393        }
394        $this->db->getPdo()->commit();
395    }
396
397    /**
398     * Get all known pages that match the given pattern
399     *
400     * @param $pattern
401     * @return string[]
402     */
403    public function getPagesMatchingPattern($pattern)
404    {
405        $sql = "SELECT page FROM pages WHERE MATCHES_PAGE_PATTERN(?, page)";
406        $pages = $this->db->queryAll($sql, $pattern);
407
408        return array_column($pages, 'page');
409    }
410
411    // endregion
412    // region Acknowledgements
413
414    /**
415     * Has the given user acknowledged the given page?
416     *
417     * @param string $page
418     * @param string $user
419     * @return bool|int timestamp of acknowledgement or false
420     */
421    public function hasUserAcknowledged($page, $user)
422    {
423        $sql = "SELECT ack
424                  FROM acks A, pages B
425                 WHERE A.page = B.page
426                   AND A.page = ?
427                   AND A.user = ?
428                   AND A.ack >= B.lastmod";
429
430        $acktime = $this->db->queryValue($sql, $page, $user);
431
432        return $acktime ? (int)$acktime : false;
433    }
434
435    /**
436     * Timestamp of the latest acknowledgment of the given page
437     * by the given user
438     *
439     * @param string $page
440     * @param string $user
441     * @return bool|string
442     */
443    public function getLatestUserAcknowledgement($page, $user)
444    {
445        $sql = "SELECT MAX(ack)
446                  FROM acks
447                 WHERE page = ?
448                   AND user = ?";
449
450        return $this->db->queryValue($sql, [$page, $user]);
451    }
452
453    /**
454     * Save user's acknowledgement for a given page
455     *
456     * @param string $page
457     * @param string $user
458     * @return bool
459     */
460    public function saveAcknowledgement($page, $user)
461    {
462        $sql = "INSERT INTO acks (page, user, ack) VALUES (?,?, strftime('%s','now'))";
463
464        $this->db->exec($sql, $page, $user);
465        return true;
466    }
467
468    /**
469     * Get all pages that a user needs to acknowledge and/or the last acknowledgement infos
470     * depending on the (optional) filter based on status of the acknowledgements.
471     *
472     * @param string $user
473     * @param array $groups
474     * @param string $status Optional status filter, can be all (default), current or due
475     *
476     * @return array|bool
477     */
478    public function getUserAcknowledgements($user, $groups, $status = '')
479    {
480        $filterClause = $this->getFilterClause($status, 'B');
481
482        // query
483        $sql = "SELECT A.page, A.pageassignees, A.autoassignees, B.lastmod, C.user, MAX(C.ack) AS ack
484                  FROM assignments A
485                  JOIN pages B
486                    ON A.page = B.page
487             LEFT JOIN acks C
488                    ON A.page = C.page AND C.user = ?
489                 WHERE AUTH_ISMEMBER(A.pageassignees || ',' || A.autoassignees, ? , ?)
490              GROUP BY A.page";
491        $sql .= $filterClause;
492        $sql .= "
493              ORDER BY A.page";
494
495        return $this->db->queryAll($sql, [$user, $user, implode('///', $groups)]);
496    }
497
498    /**
499     * Get ack status for all assigned users of a given page
500     *
501     * This can be slow!
502     *
503     * @param string $page
504     * @param string $user
505     * @param string $status
506     * @param int $max
507     *
508     * @return array
509     */
510    public function getPageAcknowledgements($page, $user = '', $status = '', $max = 0)
511    {
512        $userClause = '';
513        $filterClause = '';
514        $params[] = $page;
515
516        // filtering for user from input or using saved assignees?
517        if ($user) {
518            $users = [$user];
519            $userClause = ' AND (B.user = ? OR B.user IS NULL) ';
520            $params[] = $user;
521        } else {
522            $users = $this->getPageAssignees($page);
523            if (!$users) return [];
524        }
525
526        if ($status === 'current') {
527            $filterClause = ' AND ACK >= A.lastmod ';
528        }
529
530        $ulist = implode(',', array_map([$this->db->getPdo(), 'quote'], $users));
531        $sql = "SELECT A.page, A.lastmod, B.user, MAX(B.ack) AS ack
532                  FROM pages A
533             LEFT JOIN acks B
534                    ON A.page = B.page
535                   AND B.user IN ($ulist)
536                WHERE  A.page = ? $userClause $filterClause";
537        $sql .= " GROUP BY A.page, B.user ";
538        if ($max) $sql .= " LIMIT $max";
539
540        $acknowledgements = $this->db->queryAll($sql, $params);
541
542        if ($status === 'current') {
543            return $acknowledgements;
544        }
545
546        // there should be at least one result, unless the page is unknown
547        if (!count($acknowledgements)) return $acknowledgements;
548
549        $baseinfo = [
550            'page' => $acknowledgements[0]['page'],
551            'lastmod' => $acknowledgements[0]['lastmod'],
552            'user' => null,
553            'ack' => null,
554        ];
555
556        // fill up the result with all users that never acknowledged the page
557        $combined = [];
558        foreach ($acknowledgements as $ack) {
559            if ($ack['user'] !== null) {
560                $combined[$ack['user']] = $ack;
561            }
562        }
563        foreach ($users as $user) {
564            if (!isset($combined[$user])) {
565                $combined[$user] = array_merge($baseinfo, ['user' => $user]);
566            }
567        }
568
569        // finally remove current acknowledgements if filter is used
570        // this cannot be done in SQL without loss of data,
571        // filtering must happen last, otherwise removed current acks will be re-added as due
572        if ($status === 'due') {
573            $combined = array_filter($combined, function ($info) {
574                return $info['ack'] < $info['lastmod'];
575            });
576        }
577
578        ksort($combined);
579        return array_values($combined);
580    }
581
582    /**
583     * Returns all acknowledgements
584     *
585     * @param int $limit maximum number of results
586     * @return array
587     */
588    public function getAcknowledgements($limit = 100)
589    {
590        $sql = '
591            SELECT A.page, A.user, B.lastmod, max(A.ack) AS ack
592              FROM acks A, pages B
593             WHERE A.page = B.page
594          GROUP BY A.user, A.page
595          ORDER BY ack DESC
596             LIMIT ?
597              ';
598        return $this->db->queryAll($sql, $limit);
599    }
600
601    /**
602     * Returns a filter clause for acknowledgement queries depending on wanted status.
603     *
604     * @param string $status
605     * @param string $alias Table alias used in the SQL query
606     * @return string
607     */
608    protected function getFilterClause($status, $alias)
609    {
610        switch ($status) {
611            case 'current':
612                $filterClause = " HAVING ack >= $alias.lastmod ";
613                break;
614            case 'due':
615                $filterClause = " HAVING (ack IS NULL) OR (ack < $alias.lastmod) ";
616                break;
617            case 'outdated':
618                $filterClause = " HAVING ack < $alias.lastmod ";
619                break;
620            case 'all':
621            default:
622                $filterClause = '';
623                break;
624        }
625        return $filterClause;
626    }
627
628    // endregion
629}
630