1<?php
2/**
3 *
4 * @license    GPL 3 (http://www.gnu.org/licenses/gpl.html)
5 * @author     Julian Kunkel <Julian.Kunkel@googlemail.com>
6 */
7// must be run within Dokuwiki
8if(!defined('DOKU_INC')) die();
9
10
11class syntax_plugin_datagraph extends DokuWiki_Syntax_Plugin {
12    protected $db = null;
13
14    function syntax_plugin_datagraph() {
15       $sqlite = plugin_load('helper', 'sqlite');
16	if(!$sqlite){
17	    msg('This plugin requires the sqlite plugin. Please install it', -1);
18	    return;
19	}
20	// initialize the database connection
21	if(! $sqlite->init('data', dirname(__FILE__) . '/db/')){
22            msg('Could not load the database');
23	    return;
24	}
25	$this->db = $sqlite;
26    }
27
28    function getType() {
29        return 'substition';
30    }
31
32    function getSort() {
33        return 160;
34    }
35
36    function getPType() {
37        return 'block';
38    }
39
40    function connectTo($mode) {
41        $this->Lexer->addSpecialPattern('----+ *datagraph *-+\n.*?\n---+', $mode, 'plugin_datagraph');
42    }
43
44    function handle($match, $state, $pos, Doku_Handler $handler) {
45	$kv = array("group by" => "", "aggregate" => "", "digits" => "1", "choosableGroups" => "", "choosableAggregates" => "", "filterClass" => NULL, "choosableYear" => "");
46
47	$match = explode("\n", $match);
48	foreach($match as $line){
49		$d = explode(":", $line, 2);
50		if(count($d) == 2){
51			$kv[trim($d[0])] = trim($d[1]);
52		}
53	}
54        return $kv;
55    }
56
57    function render($format, Doku_Renderer $R, $kv) {
58        if($format != 'xhtml') return false;
59	ob_start();
60	$format = intval($kv["digits"]);
61	$groups = explode(",",$kv["choosableGroups"]);
62	$aggregates = explode(",",$kv["choosableAggregates"]);
63  $years = explode(",",$kv["choosableYear"]);
64  $filter = $kv["filterClass"];
65  $filter_short = "";
66  $filter2value = $years[0]; // query only the first year
67  $filter2key = "year";
68
69  if ($_GET["year"] != NULL){
70    $filter2value = $_GET["year"];
71  }
72
73  if($filter != NULL){
74    # TODO prevent SQL injections
75    $filter_short= 'p.class = "' .$filter . '"';
76  }
77
78	// defaults:
79	$groupBy = $kv["group by"] ;
80	$agg = $kv["aggregate"];
81
82	if (count($groups) > 0){
83		// allow to select the grouping.
84		$v = $_GET["group"];
85		if ($v != NULL){
86			// check if the selection is allowed.
87			foreach($groups as $g){
88				if(trim($g) == $v){
89					$groupBy = $v;
90					break;
91				}
92			}
93		}
94	}
95	if (count($aggregates) > 0){
96		// allow to select the grouping.
97		$v = $_GET["aggregate"];
98		if ($v != NULL){
99			// check if the selection is allowed.
100			foreach($aggregates as $g){
101				if(trim($g) == $v){
102					$agg = $v;
103					break;
104				}
105			}
106		}
107	}
108
109	if (count($groups) > 0){
110		// create link map:
111		print("\nGroup by: ");
112		$first = True;
113		foreach($groups as $g){
114			$g = trim($g);
115			if (! $first){
116				print(", ");
117			}
118			if($groupBy == $g){
119				$mark = "**";
120			}else{
121				$mark = "";
122			}
123			print($mark . "[[?group=". $g . "&aggregate=". $agg ."|". $g . "]]". $mark);
124			$first = False;
125		}
126		print("\n");
127	}
128	if (count($groups) > 0){
129		// create link map:
130		print("\nAggregate: ");
131		$first = True;
132		foreach($aggregates as $g){
133			$g = trim($g);
134			if (! $first){
135				print(", ");
136			}
137			if($agg == $g){
138				$mark = "**";
139			}else{
140				$mark = "";
141			}
142			print($mark."[[?group=". $groupBy . "&aggregate=". $g ."|". $g . "]]".$mark);
143			$first = False;
144		}
145		print("\n");
146	}
147
148	$detailedGrouping = $_GET["groupDetails"];
149	if ($detailedGrouping == NULL){
150		$detailedGrouping = FALSE;
151	}
152
153	$arr = $this->db->res2arr($this->db->query('select d2.value as k, sum(d.value) as s, count(d.value) as m, d.value as metrics, group_concat(page) as pages from pages as p join data as d on d.pid = p.pid  join data as d2 on d2.pid = p.pid   join data as d3 on d3.pid = p.pid  ' . $filter_joins . ' where ' . $filter_short . ' and d.key="' . $agg . '" and d2.key="' . $groupBy. '" and d3.key = "' . $filter2key . '" and d3.value = "' . $filter2value . '" group by d2.value'));
154
155	$metrics = explode(" ", $arr[0]["metrics"]);
156	if( count($metrics == 2)){
157		$metrics = " " . $metrics[1];
158	}else{
159		$metrics = "";
160	}
161
162
163	$count = count($arr);
164	for( $i=0; $i < $count; $i++){
165		if(strlen($arr[$i]["k"]) < 2){
166			$arr[$i]["k"] = "Unknown";
167		}
168		// remove rows with a subprefix
169		if( ! $detailedGrouping){
170			$curname = explode(" ", trim($arr[$i]["k"]));
171			if(count($curname) > 1 || ctype_lower($curname[0][0])){
172				unset($arr[$i]);
173			}
174		}
175	}
176
177	print("<c3>{  data: {    columns: [");
178	foreach( $arr as $line ){
179        	print("['" . $line["k"] .  "', ".  $line["s"]  ."], ");
180	}
181	print("],    type : 'pie',  }}</c3>\n");
182
183	// header
184	print("=== " . ucfirst($agg) . " by "  .  $groupBy . "  ====\n");
185
186	print("^ " .  ucfirst($groupBy)  . "^  (SUM)  ^   mean  ^  # systems  ^  System list  ^\n");
187	// body
188	$sum = 0;
189  $systemCount = 0;
190	foreach( $arr as $line ){
191		$sum = $sum + $line["s"];
192    $systemCount = $systemCount + $line["m"];
193        	print("|" . $line["k"] .  "|   ".  number_format($line["s"], $format) . $metrics   ."  |  " .  number_format($line["s"]/$line["m"], $format)   . $metrics   . "  |  " .   $line["m"] . "|  ");
194		$systems = "";
195		foreach (explode(",", $line["pages"]) as $page){
196			$systems .= "[[". $page .  "]], "; // . "|" . explode("/", $page, 2)[1]
197		}
198		print( trim($systems, " ,") . "|\n");
199	}
200	print("| (SUM) **all systems** |  " . $sum  . $metrics . " |    " .  number_format(($sum / $systemCount), 1) . $metrics . "|  " . $systemCount .   "| \n");
201
202
203	//print("<c3>{  data: {    columns: [      ['data1', 30],      ['data2', 120],    ],    type : 'pie',  }}</c3>");
204	$R->doc .= p_render( "xhtml", p_get_instructions( ob_get_contents() ), $info);
205	ob_end_clean();
206
207        return true;
208    }
209}
210