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