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_loop 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_loop(){
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 dataloop plugin is installed.',-1);
28    }
29
30    /**
31     * Return some info
32     */
33    function getInfo(){
34        return $this->loophelper->getInfo('Data 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('baseonly','formatting', 'substition', 'disabled','container','paragraphs'); }
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('<dataloop.*?>(?=.*?</dataloop>)',$mode,'plugin_dataloop_loop');
67       //$this->Lexer->addPattern('----+ *dataloop(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_dataloop_loop');
68    }
69
70		function postConnect() {
71			$this->Lexer->addExitPattern('</dataloop>','plugin_dataloop_loop');
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('dataloop','',$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 = "£$%DATALOOP£$%";
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								//add some preg matching for heading stuff it doesnt do automatically
235								preg_match_all('/[ \t]*(={2,})([^\n=]+)\s*=+/', $sBlockText, $aMatches);
236								//dbg($aMatches);
237											//foreach($aMatches[1] as $iKey => $sCmd)
238											//{
239
240								//[ \t]*={2,}[^\n]+={2,}[ \t]*(?=\n)
241
242								if (count($aMatches[0]))
243								{
244									$iCurrLevel = 0;
245									$iOpenLevels = 0;
246									//put this into a loop and try and do '<div class="level'.$level.'">
247									// get level and title
248									foreach($aMatches[0] as $iKey => $sHeadString)
249									{
250										$title = trim($sHeadString);
251										$level = 7 - strspn($title,'=');
252										if($level < 1) $level = 1;
253										$title = trim($title,'=');
254										$title = trim($title);
255										$sStr = "<h$level>$title</h$level>";
256
257										if($level <= $iCurrLevel)
258										{
259											$sStr = '</div>'.$sStr;
260											$iOpenLevels --;
261										}
262
263										if($level >= $iCurrLevel)
264										{
265											$sStr = $sStr.'<div class="level'.$level.'">';
266											$iOpenLevels ++;
267										}
268										$iCurrLevel = $level;
269
270										$sBlockText = str_replace($sHeadString, $sStr, $sBlockText);
271									}
272									$sBlockText .= str_repeat("</div>", $iOpenLevels);
273
274								}
275
276								//echo '$sBlockText='.htmlentities(var_export($sBlockText, true));
277								//echo '$format='.htmlentities(var_export($format, true));
278								//echo '$renderer='.htmlentities(var_export($renderer, true));
279								//dbg($sBlockText);
280								//dbg($data);
281								$sql = $this->_buildSQL($data); // handles GET params, too
282								//dbg($sql);
283
284								// register our custom aggregate function
285								sqlite_create_aggregate($this->dthlp->db,'group_concat',
286																				array($this,'_sqlite_group_concat_step'),
287																				array($this,'_sqlite_group_concat_finalize'), 2);
288
289
290								// run query
291								$types = array_values($data['cols']);
292								$res = sqlite_query($this->dthlp->db,$sql);
293
294								// build loop
295								//$renderer->doc .= '<div class="inline dataplugin_loop '.$data['classes'].'">';
296
297								// build column headers
298								$cols = array_keys($data['cols']);
299
300								// build data rows
301								$cnt = 0;
302								while ($row = sqlite_fetch_array($res, SQLITE_NUM)) {
303									$sCurrentText = $sBlockText;
304
305										foreach($row as $num => $col){
306											$aMatches = null;
307											preg_match_all('/\@\@('.$cols[$num].'[^\@]*)\@\@/i', $sCurrentText, $aMatches);
308											$xValue =  $this->dthlp->_formatData($cols[$num],$col,$types[$num],$renderer);
309
310											//dbg($aMatches);
311											foreach($aMatches[1] as $iKey => $sCmd)
312											{
313												//dbg($sCmd);
314												$aCommand = split('_', $sCmd);
315												//dbg($aCommand);
316
317
318												switch($aCommand[1])
319												{
320													case 'url':
321														if(!isset($aCommand[2]))
322														{
323															$xValue2 = $xValue;
324														}
325														else
326														{
327															$iColNum = array_search($aCommand[2], $row);
328															//no error checking here
329															$xValue2 =  $this->dthlp->_formatData($aCommand[2],$row[$iColNum],$types[$iColNum],$renderer);
330														}
331
332														$sReplace = '<a href="'.$xValue.'">'.$xValue2.'</a>';
333														//dbg($sReplace);
334													break;
335													default:
336														$sReplace = $xValue;
337													break;
338												}
339												$sCurrentText = str_ireplace($aMatches[0][$iKey], $sReplace, $sCurrentText);
340											}
341
342
343										}
344										$cnt++;
345										$renderer->doc .= $sCurrentText;
346										if($data['limit'] && ($cnt == $data['limit'])) break; // keep an eye on the limit
347								}
348
349								/*
350								// if limit was set, add control
351								if($data['limit']){
352										$renderer->doc .= '<div class="dataplugin_loop limit">';
353										$offset = (int) $_GET['dataofs'];
354										if($offset){
355												$prev = $offset - $data['limit'];
356												if($prev < 0) $prev = 0;
357
358												$renderer->doc .= '<a href="'.wl($ID,array('datasrt'=>$_GET['datasrt'], 'dataofs'=>$prev, 'dataflt'=>$_GET['dataflt'] )).
359																			'" title="'.$this->getLang('prev').'" class="prev">'.$this->getLang('prev').'</a>';
360										}
361
362										$renderer->doc .= '&nbsp;';
363
364										if(sqlite_num_rows($res) > $data['limit']){
365												$next = $offset + $data['limit'];
366												$renderer->doc .= '<a href="'.wl($ID,array('datasrt'=>$_GET['datasrt'], 'dataofs'=>$next, 'dataflt'=>$_GET['dataflt'] )).
367																			'" title="'.$this->getLang('next').'" class="next">'.$this->getLang('next').'</a>';
368										}
369										$renderer->doc .= '</div>';
370								}
371				*/
372								//$renderer->doc .= '</div>';
373
374								break;
375
376            }
377            return true;
378    }
379
380    /**
381     * Builds the SQL query from the given data
382     */
383    function _buildSQL(&$data){
384				//dbg($data);
385        $cnt    = 0;
386        $tables = array();
387        $select = array();
388        $from   = '';
389        $where  = '';
390        $order  = '';
391
392
393        // take overrides from HTTP GET params into account
394        if($_GET['datasrt']){
395            if($_GET['datasrt']{0} == '^'){
396                $data['sort'] = array(substr($_GET['datasrt'],1),'DESC');
397            }else{
398                $data['sort'] = array($_GET['datasrt'],'ASC');
399            }
400        }
401
402
403        // prepare the columns to show
404        foreach (array_keys($data['cols']) as $col){
405            if($col == '%pageid%'){
406                $select[] = 'pages.page';
407            }elseif($col == '%title%'){
408                $select[] = "pages.page || '|' || pages.title";
409            }else{
410                if(!$tables[$col]){
411                    $tables[$col] = 'T'.(++$cnt);
412                    $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
413                    $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
414                }
415                $select[] = 'group_concat('.$tables[$col].".value,'\n')";
416            }
417        }
418
419        // prepare sorting
420        if($data['sort'][0]){
421            $col = $data['sort'][0];
422
423            if($col == '%pageid%'){
424                $order = 'ORDER BY pages.page '.$data['sort'][1];
425            }elseif($col == '%title%'){
426                $order = 'ORDER BY pages.title '.$data['sort'][1];
427            }else{
428                // sort by hidden column?
429                if(!$tables[$col]){
430                    $tables[$col] = 'T'.(++$cnt);
431                    $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
432                    $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
433                }
434
435                $order = 'ORDER BY '.$tables[$col].'.value '.$data['sort'][1];
436            }
437        }else{
438            $order = 'ORDER BY 1 ASC';
439        }
440
441        // add filters
442        if(is_array($data['filter']) && count($data['filter'])){
443            $where .= ' AND ( 1=1 ';
444
445            foreach($data['filter'] as $filter){
446                $col = $filter['key'];
447
448                if($col == '%pageid%'){
449                    $where .= " ".$filter['logic']." pages.page ".$filter['compare']." '".$filter['value']."'";
450                }elseif($col == '%title%'){
451                    $where .= " ".$filter['logic']." pages.title ".$filter['compare']." '".$filter['value']."'";
452                }else{
453                    // filter by hidden column?
454                    if(!$tables[$col]){
455                        $tables[$col] = 'T'.(++$cnt);
456                        $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
457                        $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
458                    }
459
460                    $where .= ' '.$filter['logic'].' '.$tables[$col].'.value '.$filter['compare'].
461                              " '".$filter['value']."'"; //value is already escaped
462                }
463            }
464
465            $where .= ' ) ';
466        }
467
468        // add GET filter
469        if($_GET['dataflt']){
470            list($col,$val) = split(':',$_GET['dataflt'],2);
471            if(!$tables[$col]){
472                $tables[$col] = 'T'.(++$cnt);
473                $from  .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid';
474                $from  .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'";
475            }
476
477            $where .= ' AND '.$tables[$col].".value = '".sqlite_escape_string($val)."'";
478        }
479
480        // were any data tables used?
481        if(count($tables)){
482            $where = 'pages.pid = T1.pid '.$where;
483        }else{
484            $where = '1 = 1 '.$where;
485        }
486
487        // build the query
488        $sql = "SELECT ".join(', ',$select)."
489                  FROM pages $from
490                 WHERE $where
491              GROUP BY pages.page
492                $order";
493
494        // offset and limit
495        if($data['limit']){
496            $sql .= ' LIMIT '.($data['limit'] + 1);
497
498            if((int) $_GET['dataofs']){
499                $sql .= ' OFFSET '.((int) $_GET['dataofs']);
500            }
501        }
502
503
504        return $sql;
505    }
506
507    /**
508     * Aggregation function for SQLite
509     *
510     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
511     */
512    function _sqlite_group_concat_step(&$context, $string, $separator = ',') {
513         $context['sep']    = $separator;
514         $context['data'][] = $string;
515    }
516
517    /**
518     * Aggregation function for SQLite
519     *
520     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
521     */
522    function _sqlite_group_concat_finalize(&$context) {
523         $context['data'] = array_unique($context['data']);
524         return join($context['sep'],$context['data']);
525    }
526}
527
528