1<?php
2/**
3 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
4 * @author     Martyn Eggleton <martyn@access-space.org>
5 */
6// must be run within Dokuwiki
7if(!defined('DOKU_INC')) die();
8require_once(DOKU_PLUGIN.'syntax.php');
9
10class syntax_plugin_dataloop_roll extends DokuWiki_Syntax_Plugin {
11
12    /**
13     * will hold the data helper plugin
14     */
15    var $dthlp = null;
16		var $loophelper = null;
17
18
19    /**
20     * Constructor. Load helper plugin
21     */
22    function syntax_plugin_dataloop_roll(){
23        $this->dthlp =& plugin_load('helper', 'data');
24        if(!$this->dthlp) msg('Loading the data helper failed. Make sure the data plugin is installed.',-1);
25
26				$this->loophelper =& plugin_load('helper', 'dataloop');
27        if(!$this->loophelper) msg('Loading the dataloop helper failed. Make sure the data plugin is installed.',-1);
28    }
29
30    /**
31     * Return some info
32     */
33    function getInfo(){
34        return $this->loophelper->getInfo('Roll');
35    }
36
37    /**
38     * What kind of syntax are we?
39     */
40    function getType(){
41        return 'substition';
42    }
43
44    /**
45     * What about paragraphs?
46     */
47    function getPType(){
48        return 'block';
49    }
50
51    /**
52     * Where to sort in?
53     */
54    function getSort(){
55        return 155;
56    }
57
58
59    /**
60     * Connect pattern to lexer
61     */
62    function connectTo($mode) {
63        $this->Lexer->addSpecialPattern('----+ *dataroll(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_dataloop_roll');
64    }
65
66
67    /**
68     * Handle the match - parse the data
69     */
70    function handle($match, $state, $pos, &$handler){
71        // get lines and additional class
72        $lines = explode("\n",$match);
73        array_pop($lines);
74        $class = array_shift($lines);
75        $class = str_replace('dataroll','',$class);
76        $class = trim($class,'- ');
77
78        $data = array();
79        $data['classes'] = $class;
80
81        // parse info
82        foreach ( $lines as $line ) {
83            // ignore comments
84            $line = preg_replace('/(?<![&\\\\])#.*$/','',$line);
85            $line = str_replace('\\#','#',$line);
86            $line = trim($line);
87            if(empty($line)) continue;
88            $line = preg_split('/\s*:\s*/',$line,2);
89						$line[0] = strtolower($line[0]);
90
91            $logic = 'OR';
92            // handle line commands (we allow various aliases here)
93            switch($line[0]){
94                case 'select':
95                case 'cols':
96                        $cols = explode(',',$line[1]);
97                        foreach($cols as $col){
98                            $col = trim($col);
99                            if(!$col) continue;
100                            list($key,$type) = $this->dthlp->_column($col);
101                            $data['cols'][$key] = $type;
102
103                            // fix type for special type
104                            if($key == '%pageid%') $data['cols'][$key] = 'page';
105                            if($key == '%title%') $data['cols'][$key] = 'title';
106                        }
107                    break;
108                case 'title':
109                case 'titles':
110                case 'head':
111								case 'headings':
112                case 'header':
113                case 'headers':
114												$headlevelsplits = explode(';',$line[1]);
115												foreach($headlevelsplits as $iLevel => $sCols)
116												{
117													$cols = explode(',',$sCols);
118													foreach($cols as $col){
119															$col = trim($col);
120															$col = trim($col);
121															if(!$col) continue;
122															list($key,$type) = $this->dthlp->_column($col);
123															$data['headings'][$key] = $iLevel+1;
124													}
125												}
126												//msg("data['headings']= '".var_export($data['headings'], true)."'",-1);
127                    break;
128                case 'limit':
129                case 'max':
130                        $data['limit'] = abs((int) $line[1]);
131                    break;
132                case 'order':
133                case 'sort':
134                        list($sort) = $this->dthlp->_column($line[1]);
135                        if(substr($sort,0,1) == '^'){
136                            $data['sort'] = array(substr($sort,1),'DESC');
137                        }else{
138                            $data['sort'] = array($sort,'ASC');
139                        }
140                    break;
141                case 'where':
142                case 'filter':
143                case 'filterand':
144                case 'and':
145                    $logic = 'AND';
146                case 'filteror':
147                case 'or':
148                        if(preg_match('/^(.*?)(=|<|>|<=|>=|<>|!=|=~|~)(.*)$/',$line[1],$matches)){
149                            list($key) = $this->dthlp->_column(trim($matches[1]));
150                            $val = trim($matches[3]);
151                            $val = sqlite_escape_string($val); //pre escape
152                            $com = $matches[2];
153                            if($com == '<>'){
154                                $com = '!=';
155                            }elseif($com == '=~' || $com == '~'){
156                                $com = 'LIKE';
157                                $val = str_replace('*','%',$val);
158                            }
159
160                            $data['filter'][] = array('key'     => $key,
161                                                      'value'   => $val,
162                                                      'compare' => $com,
163                                                      'logic'   => $logic
164                                                     );
165                        }
166                    break;
167                default:
168                    msg("data plugin: unknown option '".hsc($line[0])."'",-1);
169            }
170        }
171
172        // if no header titles were given, use column names
173        if(!is_array($data['headers'])){
174            foreach(array_keys($data['cols']) as $col){
175                if($col == '%pageid%'){
176                    $data['headers'][] = 'pagename'; #FIXME add lang string
177                }elseif($col == '%title%'){
178                    $data['headers'][] = 'page'; #FIXME add lang string
179                }else{
180                    $data['headers'][] = $col;
181                }
182            }
183        }
184
185        return $data;
186    }
187
188    /**
189     * Create output or save the data
190     */
191    function render($format, &$renderer, $data) {
192        global $ID;
193
194        if($format != 'xhtml') return false;
195        if(!$this->dthlp->_dbconnect()) return false;
196        $renderer->info['cache'] = false;
197
198        #dbg($data);
199        $sql = $this->_buildSQL($data); // handles GET params, too
200        #dbg($sql);
201
202        // register our custom aggregate function
203        sqlite_create_aggregate($this->dthlp->db,'group_concat',
204                                array($this,'_sqlite_group_concat_step'),
205                                array($this,'_sqlite_group_concat_finalize'), 2);
206
207
208        // run query
209        $types = array_values($data['cols']);
210        $res = sqlite_query($this->dthlp->db,$sql);
211
212        // build roll
213        $renderer->doc .= '<div class="inline dataplugin_roll '.$data['classes'].'">';
214
215        // build column headers
216        $cols = array_keys($data['cols']);
217
218        // build data rows
219        $cnt = 0;
220        while ($row = sqlite_fetch_array($res, SQLITE_NUM)) {
221            foreach($row as $num => $col){
222							 //msg("num= '$num'  col='".var_export($col, true)."' cols[num]='".var_export($cols[$num], true)."'",-1);
223							 if(isset($data['headings'][$cols[$num]]))
224							 {
225								 $iHeadingLevel = $data['headings'][$cols[$num]];
226
227								 $renderer->doc .= '<h'.$iHeadingLevel.' class="inline dataplugin_roll '.$cols[$num].'">'.$this->dthlp->_formatData($cols[$num],$col,$types[$num],$renderer).'</h'.$iHeadingLevel.'>';
228							 }
229							 else
230							 {
231                $renderer->doc .= '<p class="inline dataplugin_roll '.$cols[$num].'">'.$this->dthlp->_formatData($cols[$num],$col,$types[$num],$renderer).'</p>';
232							 }
233            }
234            $cnt++;
235            if($data['limit'] && ($cnt == $data['limit'])) break; // keep an eye on the limit
236        }
237
238        // if limit was set, add control
239        if($data['limit']){
240            $renderer->doc .= '<div class="dataplugin_roll limit">';
241            $offset = (int) $_GET['dataofs'];
242            if($offset){
243                $prev = $offset - $data['limit'];
244                if($prev < 0) $prev = 0;
245
246                $renderer->doc .= '<a href="'.wl($ID,array('datasrt'=>$_GET['datasrt'], 'dataofs'=>$prev, 'dataflt'=>$_GET['dataflt'] )).
247                              '" title="'.$this->getLang('prev').'" class="prev">'.$this->getLang('prev').'</a>';
248            }
249
250            $renderer->doc .= '&nbsp;';
251
252            if(sqlite_num_rows($res) > $data['limit']){
253                $next = $offset + $data['limit'];
254                $renderer->doc .= '<a href="'.wl($ID,array('datasrt'=>$_GET['datasrt'], 'dataofs'=>$next, 'dataflt'=>$_GET['dataflt'] )).
255                              '" title="'.$this->getLang('next').'" class="next">'.$this->getLang('next').'</a>';
256            }
257            $renderer->doc .= '</div>';
258        }
259
260        $renderer->doc .= '</div>';
261
262        return true;
263    }
264
265    /**
266     * Builds the SQL query from the given data
267     */
268    function _buildSQL(&$data){
269        $cnt    = 0;
270        $tables = array();
271        $select = array();
272        $from   = '';
273        $where  = '';
274        $order  = '';
275
276
277        // take overrides from HTTP GET params into account
278        if($_GET['datasrt']){
279            if($_GET['datasrt']{0} == '^'){
280                $data['sort'] = array(substr($_GET['datasrt'],1),'DESC');
281            }else{
282                $data['sort'] = array($_GET['datasrt'],'ASC');
283            }
284        }
285
286
287        // prepare the columns to show
288        foreach (array_keys($data['cols']) as $col){
289            if($col == '%pageid%'){
290                $select[] = 'pages.page';
291            }elseif($col == '%title%'){
292                $select[] = "pages.page || '|' || pages.title";
293            }else{
294                if(!$tables[$col]){
295                    $tables[$col] = 'T'.(++$cnt);
296                    $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
297                    $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
298                }
299                $select[] = 'group_concat('.$tables[$col].".value,'\n')";
300            }
301        }
302
303        // prepare sorting
304        if($data['sort'][0]){
305            $col = $data['sort'][0];
306
307            if($col == '%pageid%'){
308                $order = 'ORDER BY pages.page '.$data['sort'][1];
309            }elseif($col == '%title%'){
310                $order = 'ORDER BY pages.title '.$data['sort'][1];
311            }else{
312                // sort by hidden column?
313                if(!$tables[$col]){
314                    $tables[$col] = 'T'.(++$cnt);
315                    $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
316                    $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
317                }
318
319                $order = 'ORDER BY '.$tables[$col].'.value '.$data['sort'][1];
320            }
321        }else{
322            $order = 'ORDER BY 1 ASC';
323        }
324
325        // add filters
326        if(is_array($data['filter']) && count($data['filter'])){
327            $where .= ' AND ( 1=1 ';
328
329            foreach($data['filter'] as $filter){
330                $col = $filter['key'];
331
332                if($col == '%pageid%'){
333                    $where .= " ".$filter['logic']." pages.page ".$filter['compare']." '".$filter['value']."'";
334                }elseif($col == '%title%'){
335                    $where .= " ".$filter['logic']." pages.title ".$filter['compare']." '".$filter['value']."'";
336                }else{
337                    // filter by hidden column?
338                    if(!$tables[$col]){
339                        $tables[$col] = 'T'.(++$cnt);
340                        $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
341                        $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
342                    }
343
344                    $where .= ' '.$filter['logic'].' '.$tables[$col].'.value '.$filter['compare'].
345                              " '".$filter['value']."'"; //value is already escaped
346                }
347            }
348
349            $where .= ' ) ';
350        }
351
352        // add GET filter
353        if($_GET['dataflt']){
354            list($col,$val) = split(':',$_GET['dataflt'],2);
355            if(!$tables[$col]){
356                $tables[$col] = 'T'.(++$cnt);
357                $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
358                $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
359            }
360
361            $where .= ' AND '.$tables[$col].".value = '".sqlite_escape_string($val)."'";
362        }
363
364        // were any data tables used?
365        if(count($tables)){
366            $where = 'pages.pid = T1.pid '.$where;
367        }else{
368            $where = '1 = 1 '.$where;
369        }
370
371        // build the query
372        $sql = "SELECT ".join(', ',$select)."
373                  FROM pages $from
374                 WHERE $where
375              GROUP BY pages.page
376                $order";
377
378        // offset and limit
379        if($data['limit']){
380            $sql .= ' LIMIT '.($data['limit'] + 1);
381
382            if((int) $_GET['dataofs']){
383                $sql .= ' OFFSET '.((int) $_GET['dataofs']);
384            }
385        }
386
387
388        return $sql;
389    }
390
391    /**
392     * Aggregation function for SQLite
393     *
394     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
395     */
396    function _sqlite_group_concat_step(&$context, $string, $separator = ',') {
397         $context['sep']    = $separator;
398         $context['data'][] = $string;
399    }
400
401    /**
402     * Aggregation function for SQLite
403     *
404     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
405     */
406    function _sqlite_group_concat_finalize(&$context) {
407         $context['data'] = array_unique($context['data']);
408         return join($context['sep'],$context['data']);
409    }
410}
411
412