1<?php
2
3/**
4 * List related pages based on similar data in the given column(s)
5 *
6 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
7 * @author     Andreas Gohr <andi@splitbrain.org>
8 */
9
10/**
11 * Class syntax_plugin_data_related
12 */
13class syntax_plugin_data_related extends syntax_plugin_data_table
14{
15    /**
16     * Connect pattern to lexer
17     */
18    public function connectTo($mode)
19    {
20        $this->Lexer->addSpecialPattern(
21            '----+ *datarelated(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',
22            $mode,
23            'plugin_data_related'
24        );
25    }
26
27    /**
28     * Handles the actual output creation.
29     *
30     * @param string $format output format being rendered
31     * @param Doku_Renderer $renderer the current renderer object
32     * @param array $data data created by handler()
33     * @return  boolean                 rendered correctly? (however, returned value is not used at the moment)
34     */
35    public function render($format, Doku_Renderer $renderer, $data)
36    {
37        if ($format != 'xhtml') return false;
38        if (is_null($data)) return false;
39        if (!$this->dthlp->ready()) return false;
40
41        $sqlite = $this->dthlp->getDB();
42        if (!$sqlite) return false;
43
44        if (!$data['sql']) return true; // sql build
45        $this->dthlp->replacePlaceholdersInSQL($data);
46
47        $rows = $sqlite->queryAll($data['sql']);
48        if (!$rows) return true; // no rows matched
49
50        $renderer->doc .= '<dl class="' . $data['classes'] . '">';
51        $renderer->doc .= '<dt>' . htmlspecialchars($data['title']) . '</dt>';
52        $renderer->doc .= '<dd>';
53        $renderer->listu_open();
54        foreach ($rows as $row) {
55            $renderer->listitem_open(1);
56            $renderer->internallink($row['page']);
57            $renderer->listitem_close();
58        }
59        $renderer->listu_close();
60        $renderer->doc .= '</dd>';
61        $renderer->doc .= '</dl>';
62
63        return true;
64    }
65
66    /**
67     * Builds the SQL query from the given data
68     */
69    public function buildSQL(&$data, $id = null)
70    {
71        global $ID;
72        if (is_null($id)) $id = $ID;
73
74        $cnt = 1;
75        $tables = [];
76        $cond = [];
77        $from = '';
78        $where = '';
79
80        $sqlite = $this->dthlp->getDB();
81        if (!$sqlite) return false;
82
83        // prepare the columns to match against
84        $found = false;
85        foreach (array_keys($data['cols']) as $col) {
86            // get values for current page:
87            $sql = "SELECT A.value
88                      FROM data A, pages B
89                     WHERE key = ?
90                       AND A.pid = B.pid
91                       AND B.page = ?";
92            $rows = $sqlite->queryAll($sql, $col, $id);
93            if (!$rows) continue; // no values? ignore the column.
94            $values = array_column($rows, 'value');
95            $found = true;
96
97            $in = implode(',', array_map([$sqlite->getPdo(), 'quote'], $values));
98            $cond[] = " ( T1.key = " . $sqlite->getPdo()->quote($col) .
99                " AND T1.value IN (" . $in . ") )\n";
100        }
101        $where .= ' AND (' . implode(' OR ', $cond) . ') ';
102
103        // any tags to compare?
104        if (!$found) return false;
105
106        // prepare sorting
107        if ($data['sort'][0]) {
108            $col = $data['sort'][0];
109
110            if ($col == '%pageid%') {
111                $order = ', pages.page ' . $data['sort'][1];
112            } elseif ($col == '%title%') {
113                $order = ', pages.title ' . $data['sort'][1];
114            } else {
115                // sort by hidden column?
116                if (!$tables[$col]) {
117                    $tables[$col] = 'T' . (++$cnt);
118                    $from .= ' LEFT JOIN data AS ' . $tables[$col] . ' ON ' . $tables[$col] . '.pid = pages.pid';
119                    $from .= ' AND ' . $tables[$col] . ".key = " . $sqlite->getPdo()->quote($col);
120                }
121
122                $order = ', ' . $tables[$col] . '.value ' . $data['sort'][1];
123            }
124        } else {
125            $order = ', pages.page';
126        }
127
128        // add filters
129        if (is_array($data['filter']) && count($data['filter'])) {
130            $where .= ' AND ( 1=1 ';
131
132            foreach ($data['filter'] as $filter) {
133                // note: value is already escaped
134                $col = $filter['key'];
135                $closecompare = ($filter['compare'] == 'IN(' ? ')' : '');
136
137                if ($col == '%pageid%') {
138                    $where .= " " . $filter['logic'] . " pages.page "
139                        . $filter['compare'] . " "
140                        . $filter['value'] . $closecompare;
141                } elseif ($col == '%title%') {
142                    $where .= " "
143                        . $filter['logic'] . " pages.title "
144                        . $filter['compare'] . " "
145                        . $filter['value'] . $closecompare;
146                } else {
147                    // filter by hidden column?
148                    if (!$tables[$col]) {
149                        $tables[$col] = 'T' . (++$cnt);
150                        $from .= ' LEFT JOIN data AS ' . $tables[$col] . ' ON ' . $tables[$col] . '.pid = pages.pid';
151                        $from .= ' AND ' . $tables[$col] . ".key = " . $sqlite->getPdo()->quote($col);
152                    }
153
154                    $where .= ' ' . $filter['logic'] . ' ' . $tables[$col] . '.value ' . $filter['compare'] .
155                        " " . $filter['value'] . $closecompare;
156                }
157            }
158
159            $where .= ' ) ';
160        }
161
162        // build the query
163        $sql = "SELECT pages.pid, pages.page as page, pages.title as title, COUNT(*) as rel
164                  FROM pages, data as T1 $from
165                 WHERE pages.pid = T1.pid
166                   AND pages.page != " . $sqlite->getPdo()->quote($id) . "
167                       $where
168              GROUP BY pages.pid
169              ORDER BY rel DESC$order";
170
171        // limit
172        if ($data['limit']) {
173            $sql .= ' LIMIT ' . ($data['limit']);
174        }
175
176        return $sql;
177    }
178}
179