1<?php
2
3use dokuwiki\Extension\AuthPlugin;
4
5/**
6 * DokuWiki Plugin acknowledge (Helper Component)
7 *
8 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
9 * @author  Andreas Gohr, Anna Dabrowska <dokuwiki@cosmocode.de>
10 */
11class helper_plugin_acknowledge extends DokuWiki_Plugin
12{
13
14    /**
15     * @return helper_plugin_sqlite|null
16     */
17    public function getDB()
18    {
19        /** @var \helper_plugin_sqlite $sqlite */
20        $sqlite = plugin_load('helper', 'sqlite');
21        if ($sqlite === null) {
22            msg($this->getLang('error sqlite plugin missing'), -1);
23            return null;
24        }
25        if (!$sqlite->init('acknowledgement', __DIR__ . '/db')) {
26            return null;
27        }
28
29        $this->registerUDF($sqlite);
30
31        return $sqlite;
32    }
33
34    /**
35     * Register user defined functions
36     *
37     * @param helper_plugin_sqlite $sqlite
38     */
39    protected function registerUDF($sqlite)
40    {
41        $sqlite->create_function('AUTH_ISMEMBER', [$this, 'auth_isMember'], -1);
42    }
43
44    /**
45     * Wrapper function for auth_isMember which accepts groups as string
46     *
47     * @param string $memberList
48     * @param string $user
49     * @param string $groups
50     * @return bool
51     */
52    public function auth_isMember($memberList, $user, $groups)
53    {
54        return auth_isMember($memberList, $user, explode('///', $groups));
55    }
56
57    /**
58     * Delete a page
59     *
60     * Cascades to delete all assigned data, etc.
61     *
62     * @param string $page Page ID
63     */
64    public function removePage($page)
65    {
66        $sqlite = $this->getDB();
67        if (!$sqlite) return;
68
69        $sql = "DELETE FROM pages WHERE page = ?";
70        $sqlite->query($sql, $page);
71    }
72
73    /**
74     * Update last modified date of page if content has changed
75     *
76     * @param string $page Page ID
77     * @param int $lastmod timestamp of last non-minor change
78     */
79    public function storePageDate($page, $lastmod, $newContent)
80    {
81        $changelog = new \dokuwiki\ChangeLog\PageChangeLog($page);
82        $revs = $changelog->getRevisions(0, 1);
83
84        // compare content
85        $oldContent = str_replace(NL, '', io_readFile(wikiFN($page, $revs[0])));
86        $newContent = str_replace(NL, '', $newContent);
87        if ($oldContent === $newContent) return;
88
89        $sqlite = $this->getDB();
90        if (!$sqlite) return;
91
92        $sql = "REPLACE INTO pages (page, lastmod) VALUES (?,?)";
93        $sqlite->query($sql, $page, $lastmod);
94    }
95
96    /**
97     * @param string $page Page ID
98     * @param string $assignees comma separated list of users and groups
99     */
100    public function setAssignees($page, $assignees)
101    {
102        $sqlite = $this->getDB();
103        if (!$sqlite) return;
104
105        $sql = "REPLACE INTO assignments ('page', 'assignee') VALUES (?,?)";
106        $sqlite->query($sql, $page, $assignees);
107    }
108
109    /**
110     * Clears assignments for a page
111     *
112     * @param string $page Page ID
113     */
114    public function clearAssignments($page)
115    {
116        $sqlite = $this->getDB();
117        if (!$sqlite) return;
118
119        $sql = "DELETE FROM assignments WHERE page = ?";
120        $sqlite->query($sql, $page);
121    }
122
123    /**
124     * Is the given user one of the assignees for this page
125     *
126     * @param string $page Page ID
127     * @param string $user user name to check
128     * @param string[] $groups groups this user is in
129     * @return bool
130     */
131    public function isUserAssigned($page, $user, $groups)
132    {
133        $sqlite = $this->getDB();
134        if (!$sqlite) return false;
135
136        $sql = "SELECT assignee FROM assignments WHERE page = ?";
137        $result = $sqlite->query($sql, $page);
138        $assignees = (string)$sqlite->res2single($result);
139        $sqlite->res_close($result);
140
141        return auth_isMember($assignees, $user, $groups);
142    }
143
144    /**
145     * Has the given user acknowledged the given page?
146     *
147     * @param string $page
148     * @param string $user
149     * @return bool|int timestamp of acknowledgement or false
150     */
151    public function hasUserAcknowledged($page, $user)
152    {
153        $sqlite = $this->getDB();
154        if (!$sqlite) return false;
155
156        $sql = "SELECT ack
157                  FROM acks A, pages B
158                 WHERE A.page = B.page
159                   AND A.page = ?
160                   AND A.user = ?
161                   AND A.ack >= B.lastmod";
162
163        $result = $sqlite->query($sql, $page, $user);
164        $acktime = $sqlite->res2single($result);
165        $sqlite->res_close($result);
166
167        return $acktime ? (int)$acktime : false;
168    }
169
170    /**
171     * Timestamp of the latest acknowledgment of the given page
172     * by the given user
173     *
174     * @param string $page
175     * @param string $user
176     * @return bool|string
177     */
178    public function getLatestUserAcknowledgement($page, $user)
179    {
180        $sqlite = $this->getDB();
181        if (!$sqlite) return false;
182
183        $sql = "SELECT MAX(ack)
184                  FROM acks
185                 WHERE page = ?
186                   AND user = ?";
187
188        $result = $sqlite->query($sql, $page, $user);
189        $latestAck = $sqlite->res2single($result);
190        $sqlite->res_close($result);
191
192        return $latestAck;
193    }
194
195    /**
196     * Save user's acknowledgement for a given page
197     *
198     * @param string $page
199     * @param string $user
200     * @return bool
201     */
202    public function saveAcknowledgement($page, $user)
203    {
204        $sqlite = $this->getDB();
205        if (!$sqlite) return false;
206
207        $sql = "INSERT INTO acks (page, user, ack) VALUES (?,?, strftime('%s','now'))";
208
209        $result = $sqlite->query($sql, $page, $user);
210        $sqlite->res_close($result);
211        return true;
212
213    }
214
215    /**
216     * Fetch all assignments for a given user, with additional page information,
217     * filtering already granted acknowledgements.
218     *
219     * @param string $user
220     * @param array $groups
221     * @return array|bool
222     */
223    public function getUserAssignments($user, $groups)
224    {
225        $sqlite = $this->getDB();
226        if (!$sqlite) return false;
227
228        $sql = "SELECT A.page, A.assignee, B.lastmod, C.user, C.ack FROM assignments A
229                JOIN pages B
230                ON A.page = B.page
231                LEFT JOIN acks C
232                ON A.page = C.page AND ( (C.user = ? AND C.ack > B.lastmod) )
233                WHERE AUTH_ISMEMBER(A.assignee, ? , ?)
234                AND ack IS NULL";
235
236        $result = $sqlite->query($sql, $user, $user, implode('///', $groups));
237        $assignments = $sqlite->res2arr($result);
238        $sqlite->res_close($result);
239
240        return $assignments;
241    }
242
243    /**
244     * Get all pages a user needs to acknowledge and the last acknowledge date
245     *
246     * @param string $user
247     * @param array $groups
248     * @return array|bool
249     */
250    public function getUserAcknowledgements($user, $groups)
251    {
252        $sqlite = $this->getDB();
253        if (!$sqlite) return false;
254
255        $sql = "SELECT A.page, A.assignee, B.lastmod, C.user, MAX(C.ack) AS ack
256                  FROM assignments A
257                  JOIN pages B
258                    ON A.page = B.page
259             LEFT JOIN acks C
260                    ON A.page = C.page AND C.user = ?
261                 WHERE AUTH_ISMEMBER(A.assignee, ? , ?)
262            GROUP BY A.page
263            ORDER BY A.page
264            ";
265
266        $result = $sqlite->query($sql, $user, $user, implode('///', $groups));
267        $assignments = $sqlite->res2arr($result);
268        $sqlite->res_close($result);
269
270        return $assignments;
271    }
272
273    /**
274     * Resolve names of users assigned to a given page
275     *
276     * This can be slow on huge user bases!
277     *
278     * @param string $page
279     * @return array|false
280     */
281    public function getPageAssignees($page)
282    {
283        $sqlite = $this->getDB();
284        if (!$sqlite) return false;
285        /** @var AuthPlugin $auth */
286        global $auth;
287
288        $sql = "SELECT assignee
289                  FROM assignments
290                 WHERE page = ?";
291        $result = $sqlite->query($sql, $page);
292        $assignments = $sqlite->res2single($result);
293        $sqlite->res_close($result);
294
295        $users = [];
296        foreach (explode(',', $assignments) as $item) {
297            $item = trim($item);
298            if ($item === '') continue;
299            if ($item[0] == '@') {
300                $users = array_merge(
301                    $users,
302                    array_keys($auth->retrieveUsers(0, 0, ['grps' => substr($item, 1)]))
303                );
304            } else {
305                $users[] = $item;
306            }
307        }
308
309        return array_unique($users);
310    }
311
312    /**
313     * Get ack status for all assigned users of a given page
314     *
315     * This can be slow!
316     *
317     * @param string $page
318     * @return array|false
319     */
320    public function getPageAcknowledgements($page)
321    {
322        $users = $this->getPageAssignees($page);
323        if ($users === false) return false;
324        $sqlite = $this->getDB();
325        if (!$sqlite) return false;
326
327        $ulist = $sqlite->quote_and_join($users);
328        $sql = "SELECT A.page, A.lastmod, B.user, MAX(B.ack) AS ack
329                  FROM pages A
330             LEFT JOIN acks B
331                    ON A.page = B.page
332                   AND B.user IN ($ulist)
333                WHERE  A.page = ?
334              GROUP BY A.page, B.user
335                 ";
336        $result = $sqlite->query($sql, $page);
337        $acknowledgements = $sqlite->res2arr($result);
338        $sqlite->res_close($result);
339
340        // there should be at least one result, unless the page is unknown
341        if (!count($acknowledgements)) return false;
342
343        $baseinfo = [
344            'page' => $acknowledgements[0]['page'],
345            'lastmod' => $acknowledgements[0]['lastmod'],
346            'user' => null,
347            'ack' => null,
348        ];
349
350        // fill up the result with all users that never acknowledged the page
351        $combined = [];
352        foreach ($acknowledgements as $ack) {
353            if ($ack['user'] !== null) {
354                $combined[$ack['user']] = $ack;
355            }
356        }
357        foreach ($users as $user) {
358            if (!isset($combined[$user])) {
359                $combined[$user] = array_merge($baseinfo, ['user' => $user]);
360            }
361        }
362
363        ksort($combined);
364        return array_values($combined);
365    }
366
367    /**
368     * Returns all acknowledgements
369     *
370     * @param int $limit maximum number of results
371     * @return array|bool
372     */
373    public function getAcknowledgements($limit = 100)
374    {
375        $sqlite = $this->getDB();
376        if (!$sqlite) return false;
377
378        $sql = '
379            SELECT A.page, A.user, B.lastmod, max(A.ack) AS ack
380              FROM acks A, pages B
381             WHERE A.page = B.page
382          GROUP BY A.user, A.page
383          ORDER BY ack DESC
384             LIMIT ?
385              ';
386        $result = $sqlite->query($sql, $limit);
387        $acknowledgements = $sqlite->res2arr($result);
388        $sqlite->res_close($result);
389
390        return $acknowledgements;
391    }
392}
393
394