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