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