1<?php
2/**
3 * DokuWiki Plugin simplemysqlclient (Syntax Component)
4 *
5 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
6 * @author  Heiko Heinz <heiko.heinz@soft2c.de>
7 */
8
9// must be run within Dokuwiki
10if (! defined ( 'DOKU_INC' ))
11	die ();
12class syntax_plugin_simplemysqlclient_simplemysqlclient extends DokuWiki_Syntax_Plugin {
13	/**
14	 *
15	 * @return string Syntax mode type
16	 */
17	public function getType() {
18		return 'container';
19	}
20	/**
21	 *
22	 * @return string Paragraph type
23	 */
24	public function getPType() {
25		return 'block';
26	}
27	/**
28	 *
29	 * @return int Sort order - Low numbers go before high numbers
30	 */
31	public function getSort() {
32		return 260;
33	}
34
35	/**
36	 * Connect lookup pattern to lexer.
37	 *
38	 * @param string $mode
39	 *        	Parser mode
40	 */
41	public function connectTo($mode) {
42		$this->Lexer->addSpecialPattern ( '\{\{simplemysqlclient\}\}', $mode, 'plugin_simplemysqlclient_simplemysqlclient' );
43	}
44
45	// public function postConnect() {
46	// $this->Lexer->addExitPattern('</FIXME>','plugin_simplemysqlclient_simplemysqlclient');
47	// }
48
49	/**
50	 * Handle matches of the simplemysqlclient syntax
51	 *
52	 * @param string $match
53	 *        	The match of the syntax
54	 * @param int $state
55	 *        	The state of the handler
56	 * @param int $pos
57	 *        	The position in the document
58	 * @param Doku_Handler $handler
59	 *        	The handler
60	 * @return array Data for the renderer
61	 */
62	public function handle($match, $state, $pos, Doku_Handler &$handler) {
63		$data = array ();
64
65		return $data;
66	}
67
68	/**
69	 * Render xhtml output or metadata
70	 *
71	 * @param string $mode
72	 *        	Renderer mode (supported modes: xhtml)
73	 * @param Doku_Renderer $renderer
74	 *        	The renderer
75	 * @param array $data
76	 *        	The data from the handler() function
77	 * @return bool If rendering was successful.
78	 */
79	public function render($mode, Doku_Renderer &$renderer, $data) {
80
81		if ($mode != 'xhtml')
82			return false;
83
84		$renderer->info ['cache'] = false;
85
86		if (! $this->isAuthorized ()) {
87			echo '<div class="error">' . $this->getLang ( 'missingpermission' ) . '</div>';
88			return true;
89		}
90
91		$button = $this->getLang ( 'submit_button' );
92
93		$query='';
94		if(isset($_GET ['q']))
95		$query = trim ( $_GET ['q'] );
96
97		$queryEsc = strtr ( $query, array (
98				'<' => '&lt;'
99		) );
100
101		$shortcuts = $this->getShortCuts ();
102		$templates = $this->getTemplates ();
103
104		$emptyresult = $this->getLang ( 'emptyresult' );
105
106		echo <<<EOT
107<script type="text/javascript"><!--
108function simplemysqlclient_load(query)
109{
110	with ( document.simplemysqlclient.q )
111	{
112		value = query;
113		focus();
114	}
115
116	return false;
117}
118//--></script>
119<form action="$_SERVER[PHP_SELF]" method="GET" name="simplemysqlclient" id="simplemysqlclient">
120 <input type="hidden" name="do" value="$_REQUEST[do]" />
121 <input type="hidden" name="page" value="$_REQUEST[page]" />
122 <input type="hidden" name="id" value="$_REQUEST[id]" />
123 <div>
124  $shortcuts
125 </div>
126 <div>
127  $templates
128 </div>
129 $history
130 <textarea name="q" rows="5" cols="100" style="width: 100%;">$queryEsc</textarea>
131 <div>
132  <input style="float:right;padding:4px;margin:10px" type="submit" value="$button" />
133 </div>
134</form>
135<script type="text/javascript"><!--
136document.simplemysqlclient.q.focus();
137document.simplemysqlclient.q.select();
138//--></script>
139EOT;
140		if ($query !== '') {
141
142			echo <<<EOT
143<div style="padding-top: 1em; margin-top: 1em; border-top: 1px solid #888888;">
144EOT;
145
146			try {
147
148				$db = $this->connectToDb ();
149
150				if (! $db) {
151					echo '<div class="error">' . $this->getLang ( 'errordbconnection' ) . '</div>';
152					return true;
153				}
154				if ($db->multi_query ( $query )) {
155					echo '<u>' . $this->getLang ( 'firstresult' ) . '</u><br>';
156					do {
157						$result = $db->store_result ();
158						if ($result instanceof mysqli_result) {
159							try {
160								$rows = $this->fetch_all( $result);
161								if (sizeof ( $rows ) <= 0) {
162									echo $this->getLang ( 'emptyresult' );
163								}
164								$cols = empty ( $first ) ? array () : array_keys ( $first );
165
166								echo $this->__renderTable ( $cols, $rows );
167							} catch ( Exception $e ) {
168								echo '<div class="error">' . $e->getMessage () . '</div>';
169							} finally {
170								$result->free ();
171							}
172						} else if (is_bool ( $result )) {
173							$err = mysqli_errno ( $db );
174							echo $this->getLang ( 'queryexecuted' ) . ($err != 0 ? $this->getLang ( 'no' ) : $this->getLang ( 'yes' ));
175						} else
176							var_dump ( $result );
177
178						if ($db->more_results ()) {
179							echo '<br><u>' . $this->getLang ( 'nextresult' ) . '</u><br>';
180						}
181					} while ( $db->next_result () );
182				} else {
183					echo $this->getLang ( 'queriesnotexecuted' );
184				}
185			} catch ( Exception $e ) {
186				echo '<div class="error">' . $e->getMessage () . '</div>';
187			} finally {
188				if ($db) {
189					$db->close ();
190				}
191			}
192
193			echo '</div>';
194		}
195
196		return true;
197	}
198
199	private function fetch_all($result)
200	{
201		if (method_exists('mysqli_result', 'fetch_all')) # Compatibility layer with PHP < 5.3
202			$res = mysqli_fetch_all ( $result, MYSQLI_ASSOC );
203		else
204			for ($res = array(); $tmp = mysqli_fetch_array($result, MYSQLI_ASSOC);) $res[] = $tmp;
205
206		return $res;
207	}
208	private function __renderTable($cols, $rows) {
209
210		/*
211		 * - collect header row according to listed rows
212		 * - transform all listed rows to properly defined HTML table cells
213		 */
214		$headers = array ();
215		$meta = array ();
216		$counter = 0;
217
218		foreach ( $rows as $nr => $row ) {
219
220			// convert all values in current row to table cells
221			$i = 0;
222
223			foreach ( $row as $column => $value ) {
224
225				$def = $meta [$column];
226
227				$headers [$column] = $def ['label'] ? $def ['label'] : $column;
228				$class = $def ['format'] ? $def ['format'] : 'na';
229				$class .= ' col' . ++ $i;
230
231				$value = $this->valueFromDB ( $column, $value, $def );
232
233				$cell = $this->renderValue ( $column, $value, $def, false, false );
234
235				$row [$column] = "<td class=\"$class\">" . $cell . "</td>\n";
236			}
237
238			// prepend cell for counter
239			array_unshift ( $row, '<td class="counter col0 rightalign">' . ++ $counter . "</td>\n" );
240
241			// convert set of values into HTML table row
242			$classes = array ();
243
244			if ($nr == 0)
245				$classes [] = 'first';
246			if ($nr == count ( $rows ) - 1)
247				$classes [] = 'last';
248
249			$classes [] = ($nr % 2) ? 'even' : 'odd';
250			$classes [] = 'row' . ($nr + 1);
251
252			$classes = implode ( ' ', $classes );
253
254			$rows [$nr] = '<tr class="' . $classes . '">' . implode ( '', $row ) . "</tr>\n";
255		}
256
257		// finally convert all HTML table rows into single HTML table body
258		$rows = implode ( '', $rows );
259
260		/*
261		 * compile header row
262		 */
263
264		// ensure to have row of headers (missing on an empty list of rows)
265		if (empty ( $headers ))
266			foreach ( $cols as $column ) {
267
268				unset ( $def );
269
270				if (is_array ( $meta [$column] ))
271					$def = $meta [$column];
272				else {
273					// missing meta information on current "column name"
274					// --> might be an alias definition
275					// --> extract originally selected column name from that
276
277					$pos = strripos ( $column, ' AS ' );
278					if ($pos !== false) {
279
280						$temp = substr ( $column, $pos + 4 );
281						if ($meta [$temp]) {
282							// found definition on extracted column name
283
284							$def = $meta [$temp];
285							$column = $temp;
286						}
287					}
288				}
289
290				$headers [$column] = $def ['label'] ? $def ['label'] : $column;
291			}
292
293		foreach ( $headers as $column => $label ) {
294
295			$headers [$column] = "<th class=\"label\">$label</th>\n";
296		}
297
298		// compile row of header cells
299		$headers = implode ( '', $headers );
300
301		/*
302		 * render list of rows as HTML table
303		 */
304
305		$width = intval ( $this->options ['width'] ) ? ' width="' . $this->options ['width'] . '"' : '';
306
307		$trClass = $this->options ['wikistyle'] ? '' : ' class="data-list"';
308		$tableClass = $this->options ['wikistyle'] ? ' class="inline"' : '';
309
310		$table = <<<EOT
311<div style="overflow-x: scroll;">
312   <table width="100%"$tableClass>
313    <thead>
314     <tr class="row0">
315      <th class="counter"></th>
316      $headers
317     </tr>
318    </thead>
319    <tbody>
320     $rows
321    </tbody>
322   </table>
323</div>
324EOT;
325
326		return $table;
327	}
328	protected function valueFromDB($column, $value, $def) {
329		switch ($def ['format']) {
330
331			case 'image' :
332			case 'file' :
333				if (is_null ( $value ))
334					return null;
335
336				if ($value === '||')
337					return null;
338
339					// parse file for internally used structure
340				$a = strpos ( $value, '|' );
341				if (! $a)
342					// externally provided file --> don't touch
343					return (strlen ( $value ) > 0);
344
345				$b = strpos ( $value, '|', $a + 1 );
346				if (! $b)
347					// externally provided file --> don't touch
348					return true;
349
350				$temp = array (
351						'mime' => substr ( $value, 0, $a ),
352						'name' => substr ( $value, $a + 1, $b - $a - 1 ),
353						'file' => substr ( $value, $b + 1 )
354				);
355
356				if (! preg_match ( '#^[a-z0-9-]+/[+a-z0-9-]+$#i', $temp ['mime'] ))
357					// externally provided file --> don't touch
358					return true;
359
360				if (trim ( $temp ['name'] ) === '')
361					// externally provided file --> don't touch
362					return true;
363
364				return $temp;
365
366			case 'date' :
367				if ($def ['options'] ['unixts'])
368					return $value;
369
370				if ((trim ( $value ) === '') || ($value == '0000-00-00'))
371					return 0;
372
373				return self::parseDBDateTime ( $value, true );
374
375			case 'time' :
376				return $value;
377
378			case 'datetime' :
379				if ($def ['options'] ['unixts'])
380					return $value;
381
382				$value = substr ( $value, 0, 19 );
383				if ((trim ( $value ) === '') || ($value == '0000-00-00T00:00:00') || ($value == '0000-00-00 00:00:00'))
384					return 0;
385
386				return self::parseDBDateTime ( $value, false );
387
388			case 'bool' :
389				$value = trim ( $value );
390				switch ($def ['options'] ['booltype']) {
391
392					case 'int' :
393						return (intval ( $value ) != 0);
394
395					case 'xmark' :
396						return (strtolower ( $value [0] ) == 'x');
397
398					case 'yesno' :
399					default :
400						return (strtolower ( $value [0] ) == 'y');
401				}
402
403			case 'enum' :
404				$value = trim ( $value );
405				$value = array_search ( $value, $def ['options'] ['selectables'] );
406				if ($value === false)
407					$value = null;
408				else
409					$value = intval ( $value );
410
411				break;
412
413			case 'related' :
414				if (is_numeric ( $value ))
415					$value = intval ( $value );
416
417				break;
418
419			case 'monetary' :
420			case 'real' :
421			/**
422			 *
423			 * @todo manage decimal point conversions
424			 */
425
426			case 'url' :
427			case 'email' :
428			case 'phone' :
429			case 'fax' :
430			case 'text' :
431			case 'integer' :
432			// keep value as is ...
433		}
434
435		return $value;
436	}
437	protected function isAuthorized() {
438		global $INPUT;
439		$remoteUser = $INPUT->server->str ( 'REMOTE_USER' );
440
441		if (! $remoteUser) {
442			return false;
443		}
444
445		global $USERINFO;
446		$groups = $USERINFO ['grps'];
447		$allowedUserGroups = $this->getConf ( 'allowedUserGroups' );
448
449		$allowedUserGroups = utf8_strtolower ( $allowedUserGroups );
450		$members = explode ( ',', $allowedUserGroups );
451		$members = array_map ( 'trim', $members );
452		$members = array_unique ( $members );
453		$members = array_filter ( $members );
454
455		// compare cleaned values
456		foreach ( $members as $member ) {
457			if ($member == 'ALL')
458				return true;
459			if (in_array ( $member, $groups ))
460				return true;
461			else {
462				if ($member == $remoteUser)
463					return true;
464			}
465		}
466		return false;
467	}
468	protected function renderValue($column, $value, $def, $mayBeSkipped = false, $inEditor = false) {
469		if (is_null ( $value ) && ($def ['type'] != 'data'))
470			return $mayBeSkipped ? null : '';
471
472		switch ($def ['format']) {
473
474			case 'text' :
475				return trim ( $value );
476
477			case 'image' :
478			case 'file' :
479				if ($value === true)
480					return '<span class="info">' . $this->getLang ( 'fileexternalfound' ) . '</span>';
481				else if ($value === false)
482					return '<span class="info">' . $this->getLang ( 'fileexternalnotfound' ) . '</span>';
483				else {
484
485					$temp = is_array ( $value ) ? trim ( implode ( '', $value ) ) : '';
486
487					if (! $inEditor)
488						$url .= '&thumb=150';
489
490					if ($def ['format'] === 'image')
491						return "<img src=\"$url\" alt=\"" . sprintf ( $this->getLang ( 'fileimagealt' ), $column, $value ['mime'] ) . "\" />";
492
493					return "<a href=\"$url\" title=\"" . $this->getLang ( 'filedlhint' ) . '">' . $this->getLang ( 'cmddl' ) . '</a>';
494				}
495				break;
496
497			case 'email' :
498				if ((trim ( $value ) === '') && $mayBeSkipped)
499					return null;
500
501				return DokuWiki_Plugin::email ( $value, $email );
502
503			case 'url' :
504				if ((trim ( $value ) === '') && $mayBeSkipped)
505					return null;
506
507				return DokuWiki_Plugin::external_link ( $value );
508
509			case 'phone' :
510			case 'fax' :
511				if ((trim ( $value ) === ''))
512					return null;
513
514				return $value;
515
516			case 'bool' :
517				if (! $value)
518					return null;
519				return $value ? '&#10007;' : '&ndash;';
520
521			case 'date' :
522				if (! $value)
523					return $mayBeSkipped ? null : '';
524
525				return strftime ( strtok ( trim ( $this->getConf ( 'dformat' ) ), ' ' ), $value );
526
527			case 'datetime' :
528				if (! $value)
529					return $mayBeSkipped ? null : '';
530
531				return strftime ( $this->getConf ( 'dformat' ), $value );
532
533			case 'time' :
534				if ($value && (substr ( $value, - 3 ) == ':00') && (strlen ( $value ) > 5))
535					$value = substr ( $value, 0, - 3 );
536
537				return $value;
538
539			case 'integer' :
540			case 'monetary' :
541			case 'real' :
542				if (! $value && $mayBeSkipped)
543					return null;
544
545				return $value;
546
547			case 'enum' :
548			case 'related' :
549				if (is_integer ( $value ))
550					$value = $def ['options'] ['selectables'] [$value];
551
552				return $value;
553
554			default :
555				return $value;
556		}
557	}
558	private function getTemplates() {
559		$ret = '<u><b>' . $this->getLang ( 'templatesLabel' ) . '</b></u><br>';
560
561		$text = $this->getLang ( 'template.1.text' );
562		if ($text == '') {
563			return '';
564		}
565
566		for($i = 1; $i <= true; $i ++) {
567			$text = $this->getLang ( 'template.' . $i . '.text' );
568			if ($text == '') {
569				return $ret;
570			}
571
572			$sql = $this->getLang ( 'template.' . $i . '.sql' );
573			$ret = $ret . '<button style="margin:4px" onclick="return simplemysqlclient_load(\'' . $sql . '\')">' . $text . '</button>';
574		}
575
576		return $ret;
577	}
578	private function getShortCuts() {
579		$ret = '<u><b>' . $this->getLang ( 'shortcutslabel' ) . '</b></u><br>';
580
581		$text = $this->getLang ( 'shortcut.1.text' );
582		if ($text == '') {
583			return '';
584		}
585
586		for($i = 1; true; $i ++) {
587			$text = $this->getLang ( 'shortcut.' . $i . '.text' );
588			if ($text == '') {
589				return $ret;
590			}
591
592			$sql = $this->getLang ( 'shortcut.' . $i . '.sql' );
593			$sql = str_replace ( '#DATABASENAME#', $this->getConf ( 'dbName' ), $sql );
594			$ret = $ret . '<button style="margin:4px" onclick="return simplemysqlclient_load(\'' . $sql . '\')">' . $text . '</button>';
595		}
596
597		return $ret;
598	}
599	private function connectToDb() { // DB-Verbindung herstellen - eRent
600		$serverName = $this->getConf ( 'serverName' );
601		$userName = $this->getConf ( 'userName' );
602		$passWord = $this->getConf ( 'passWord' );
603		$dbName = $this->getConf ( 'dbName' );
604
605		$dbCon = new mysqli ( $serverName, $userName, $passWord, $dbName );
606		mysqli_autocommit ( $dbCon, TRUE );
607		mysqli_set_charset ( $dbCon, 'utf8' ); // muss auf UTF8 umgesetzt werden
608		mysqli_select_db ( $dbCon, $dbName ); // DB-Auswahl
609
610		return $dbCon;
611	}
612}
613
614// vim:ts=4:sw=4:et:
615