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
8 if(!defined('DOKU_INC')) die();
9 
10 
11 class 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