1 | <?php |
---|
2 | $USE_CACHE = true; |
---|
3 | class Cache{ |
---|
4 | private $cache_file; |
---|
5 | private $cache_dir; |
---|
6 | private $data_file; |
---|
7 | private $data; |
---|
8 | private $cache_timeout=3600; |
---|
9 | |
---|
10 | function Cache(){ |
---|
11 | $this->cache_dir = sys_get_temp_dir(); |
---|
12 | $this->cache_file = "cache_analytics.db.tmp"; |
---|
13 | $this->datafile=$this->cache_dir.'/'.$this->cache_file; |
---|
14 | $this->data = array(); |
---|
15 | } |
---|
16 | function readFile(){ |
---|
17 | if (is_file($this->datafile) and is_writable($this->datafile)){ |
---|
18 | $this->filecontents = file_get_contents($this->datafile); |
---|
19 | }else{ |
---|
20 | // do nothing or throw error |
---|
21 | return false; |
---|
22 | } |
---|
23 | return true; |
---|
24 | } |
---|
25 | function newFile($data=array()){ |
---|
26 | // create new file |
---|
27 | $obj=$data; |
---|
28 | $json=''; |
---|
29 | try{ |
---|
30 | $json=json_encode($obj); |
---|
31 | }catch(Exception $e){ |
---|
32 | return false; |
---|
33 | } |
---|
34 | file_put_contents($this->datafile,$json); |
---|
35 | } |
---|
36 | function parseJson(){ |
---|
37 | try{ |
---|
38 | $json=json_decode($this->filecontents,true); |
---|
39 | $this->data=$json; |
---|
40 | }catch(Exception $e){ |
---|
41 | return false; |
---|
42 | } |
---|
43 | return true; |
---|
44 | } |
---|
45 | function process(){ |
---|
46 | $continue = false; |
---|
47 | if ($this->readFile()){ |
---|
48 | $continue = true; |
---|
49 | }else{ |
---|
50 | $this->newFile(); |
---|
51 | if ($this->readFile()){ |
---|
52 | $continue=true; |
---|
53 | } |
---|
54 | } |
---|
55 | if (! $continue){ |
---|
56 | return false; |
---|
57 | } |
---|
58 | if (! $this->parseJson()){ |
---|
59 | return false; |
---|
60 | } |
---|
61 | return true; |
---|
62 | } |
---|
63 | function get($key){ |
---|
64 | global $USE_CACHE; |
---|
65 | if (! $USE_CACHE){ |
---|
66 | return false; |
---|
67 | } |
---|
68 | if (! $this->process()){ |
---|
69 | return false; |
---|
70 | } |
---|
71 | try{ |
---|
72 | $data=$this->data; |
---|
73 | if ($data == null or ! is_array($data) or ! array_key_exists($key,$data)) |
---|
74 | return false; |
---|
75 | $data=$data[$key]; |
---|
76 | if (time() - (int)$data['timestamp'] > $this->cache_timeout){ |
---|
77 | return false; //'invalid'; |
---|
78 | }else{ |
---|
79 | return $data['value']; |
---|
80 | } |
---|
81 | }catch(Exception $e){ |
---|
82 | return false; |
---|
83 | } |
---|
84 | } |
---|
85 | function store($obj,$key){ |
---|
86 | if (! $this->process()){ |
---|
87 | return false; |
---|
88 | } |
---|
89 | try{ |
---|
90 | $this->data[$key]=['timestamp'=>time(),'value'=>$obj]; |
---|
91 | return $this->newFile($this->data); |
---|
92 | }catch(Exception $e){ |
---|
93 | return false; |
---|
94 | } |
---|
95 | } |
---|
96 | } |
---|
97 | |
---|
98 | class DB{ |
---|
99 | |
---|
100 | private $dbhost; |
---|
101 | private $dbname; |
---|
102 | private $dbuser; |
---|
103 | private $dbpass; |
---|
104 | private $ka_file='/var/run/analyticsd.keepalive'; |
---|
105 | private $alias; |
---|
106 | public $dbconn; |
---|
107 | |
---|
108 | function DB(){ |
---|
109 | require_once('config.php'); |
---|
110 | global $dbhost,$dbname,$dbpass,$dbuser,$distros; |
---|
111 | |
---|
112 | $this->dbhost=$dbhost; |
---|
113 | $this->dbname=$dbname; |
---|
114 | $this->dbpass=$dbpass; |
---|
115 | $this->dbuser=$dbuser; |
---|
116 | $this->alias=array(); |
---|
117 | $this->info_distro=json_decode($distros,true); |
---|
118 | if ($this->info_distro == NULL){ |
---|
119 | die('Error: Wrong json in Config.php'); |
---|
120 | } |
---|
121 | $this->init_dates(); |
---|
122 | $this->times=0; |
---|
123 | } |
---|
124 | function init_dates(){ |
---|
125 | $this->dates=array(); |
---|
126 | $this->dates['today']=date("Y-m-d"); |
---|
127 | $this->dates['first_current']=date("Y-m-").'01'; |
---|
128 | $this->dates['last_old']=date("Y-m-d",strtotime($this->dates['first_current']." -1 days")); |
---|
129 | $this->dates['first_old']=date("Y-m-",strtotime($this->dates['today']. "-1 months")).'01'; |
---|
130 | $this->dates['last_very_old']=date("Y-m-d",strtotime($this->dates['first_old']." -1 days")); |
---|
131 | $this->dates['first_very_old']=date("Y-m-",strtotime($this->dates['first_old']." -1 days")).'01'; |
---|
132 | $this->dates['date_current']="(date between '".$this->dates['first_current']."' and '".$this->dates['today']."')"; |
---|
133 | $this->dates['date_old']="(date between '".$this->dates['first_old']."' and '".$this->dates['last_old']."')"; |
---|
134 | $this->dates['date_very_old']="(date between '".$this->dates['first_very_old']."' and '".$this->dates['last_very_old']."')"; |
---|
135 | $this->dates['date_range_last_three_months']="(date between '".$this->dates['first_very_old']."' and '".$this->dates['today']."')"; |
---|
136 | } |
---|
137 | function connect(){ |
---|
138 | $this->dbconn=new mysqli($this->dbhost, $this->dbuser , $this->dbpass, $this->dbname); |
---|
139 | if ($this->dbconn->connect_error) { |
---|
140 | die('Connect Error:'. $this->dbconn->connect_error); |
---|
141 | } |
---|
142 | } |
---|
143 | function disconnect(){ |
---|
144 | $this->dbconn->close(); |
---|
145 | } |
---|
146 | function init_trans(){ |
---|
147 | $this->dbconn->autocommit(FALSE); |
---|
148 | $this->dbconn->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); |
---|
149 | } |
---|
150 | |
---|
151 | function send_data($user,$version,$sabor,$apps,$specs,$date=''){ |
---|
152 | $spec_sql_names = ''; |
---|
153 | $spec_sql_values = ''; |
---|
154 | |
---|
155 | $ltsp_sql_names = ''; |
---|
156 | $ltsp_sql_values = ''; |
---|
157 | |
---|
158 | $use_data=array(); |
---|
159 | $use_name=array(); |
---|
160 | if ($specs != false){ |
---|
161 | if (array_key_exists('arch',$specs)){ |
---|
162 | $arch = $specs['arch']; |
---|
163 | $use_data[]="'$arch'"; |
---|
164 | $use_name[]='arch'; |
---|
165 | } |
---|
166 | if (array_key_exists('mem',$specs)){ |
---|
167 | $mem = $specs['mem']; |
---|
168 | if (is_numeric($mem)){ |
---|
169 | $mem=(int)$mem; |
---|
170 | } |
---|
171 | $use_data[]=$mem; |
---|
172 | $use_name[]='mem'; |
---|
173 | } |
---|
174 | if (array_key_exists('vga',$specs)){ |
---|
175 | $vga = substr($specs['vga'],0,80); |
---|
176 | $use_name[]='vga'; |
---|
177 | $use_data[]="'$vga'"; |
---|
178 | } |
---|
179 | if (array_key_exists('cpu',$specs)){ |
---|
180 | if (array_key_exists('model',$specs['cpu'])){ |
---|
181 | $cpu = substr($specs['cpu']['model'],0,80); |
---|
182 | $use_name[]='cpu'; |
---|
183 | $use_data[]="'$cpu'"; |
---|
184 | } |
---|
185 | if (array_key_exists('ncpus',$specs['cpu'])){ |
---|
186 | $ncpu = $specs['cpu']['ncpus']; |
---|
187 | if (is_numeric($ncpu)){ |
---|
188 | $ncpu=(int)$ncpu; |
---|
189 | } |
---|
190 | $use_name[]='ncpu'; |
---|
191 | $use_data[]=$ncpu; |
---|
192 | } |
---|
193 | } |
---|
194 | if (count($use_data) > 0){ |
---|
195 | $spec_sql_names = ','; |
---|
196 | $spec_sql_names .= implode(',',$use_name); |
---|
197 | $spec_sql_values = ','; |
---|
198 | $spec_sql_values .= implode(',',$use_data); |
---|
199 | }else{ |
---|
200 | $spec_sql_names = ''; |
---|
201 | $spec_sql_values = ''; |
---|
202 | } |
---|
203 | $use_data=array(); |
---|
204 | $use_name=array(); |
---|
205 | if (array_key_exists('subtype',$specs)){ |
---|
206 | if (array_key_exists('LTSP',$specs['subtype'])){ |
---|
207 | $ltsp = $specs['subtype']['LTSP']; |
---|
208 | if ($ltsp){ |
---|
209 | $ltsp='TRUE'; |
---|
210 | }else{ |
---|
211 | $ltsp='FALSE'; |
---|
212 | } |
---|
213 | $use_name[]='ltsp'; |
---|
214 | $use_data[]=$ltsp; |
---|
215 | } |
---|
216 | if (array_key_exists('MODE',$specs['subtype'])){ |
---|
217 | $ltsp_mode = $specs['subtype']['MODE']; |
---|
218 | $ltsp_mode = substr($ltsp_mode,0,4); |
---|
219 | if (strtolower($ltsp_mode) != 'null'){ |
---|
220 | $use_name[]='mode'; |
---|
221 | $use_data[]="'$ltsp_mode'"; |
---|
222 | } |
---|
223 | } |
---|
224 | } |
---|
225 | if (count($use_name) > 0){ |
---|
226 | $ltsp_sql_names = ','; |
---|
227 | $ltsp_sql_names .= implode(',',$use_name); |
---|
228 | $ltsp_sql_values = ','; |
---|
229 | $ltsp_sql_values .= implode(',',$use_data); |
---|
230 | }else{ |
---|
231 | $ltsp_sql_names = ''; |
---|
232 | $ltsp_sql_values = ""; |
---|
233 | } |
---|
234 | } |
---|
235 | if ($date == ''){ |
---|
236 | $sql="INSERT INTO tmp_clients(user,version,sabor,status $spec_sql_names $ltsp_sql_names) values ('$user','$version','$sabor',0 $spec_sql_values $ltsp_sql_values)"; |
---|
237 | }else{ |
---|
238 | $sql="INSERT INTO tmp_clients(user,version,sabor,status,date $spec_sql_names $ltsp_sql_names) values ('$user','$version','$sabor',0,'$date' $spec_sql_values $ltsp_sql_values)"; |
---|
239 | } |
---|
240 | $retry=1; |
---|
241 | $done=false; |
---|
242 | $cli_id=false; |
---|
243 | while (! $done and $retry < 4){ |
---|
244 | $res=$this->dbconn->query($sql); |
---|
245 | if ($res){ |
---|
246 | $cli_id=$this->dbconn->insert_id; |
---|
247 | $done=true; |
---|
248 | }else{ |
---|
249 | $retry+=1; |
---|
250 | sleep($retry); |
---|
251 | } |
---|
252 | } |
---|
253 | if ($retry == 4 or $cli_id == false) |
---|
254 | throw new Exception('Error sending client data: '.$this->dbconn->error); |
---|
255 | $err_apps=false; |
---|
256 | $err_exception=false; |
---|
257 | if (count($apps) != 0){ |
---|
258 | if ($date == ''){ |
---|
259 | $sql="insert into tmp_packages(client,app,value) values"; |
---|
260 | }else{ |
---|
261 | $sql="insert into tmp_packages(client,app,value,date) values"; |
---|
262 | } |
---|
263 | $values=array(); |
---|
264 | // Prevent DoS attack |
---|
265 | $i = 1000; |
---|
266 | foreach ($apps as $app => $value){ |
---|
267 | // Max 1000 apps |
---|
268 | if ( $i > 0 ){ |
---|
269 | $i = $i - 1; |
---|
270 | }else{ |
---|
271 | throw new Exception('*** DoS detected, aborting more processing on this request ***'); |
---|
272 | } |
---|
273 | |
---|
274 | if (trim($app) == '' or trim($value) == ''){ |
---|
275 | $err_apps=true; |
---|
276 | $err_exception=new Exception('Wrong application values'); |
---|
277 | continue; |
---|
278 | } |
---|
279 | if ($date == ''){ |
---|
280 | $values[]="($cli_id,'$app',$value)"; |
---|
281 | }else{ |
---|
282 | $values[]="($cli_id,'$app',$value,'$date')"; |
---|
283 | } |
---|
284 | } |
---|
285 | if (count($values) > 0){ |
---|
286 | $sql.=implode(',',$values); |
---|
287 | $done=false; |
---|
288 | $retry=1; |
---|
289 | while (! $done and $retry < 4){ |
---|
290 | $res=$this->dbconn->query($sql); |
---|
291 | if ($res){ |
---|
292 | $done=true; |
---|
293 | }else{ |
---|
294 | $retry += 1; |
---|
295 | sleep($retry); |
---|
296 | } |
---|
297 | } |
---|
298 | if ($retry == 4 or ! $done){ |
---|
299 | $err_apps=true; |
---|
300 | $err_exception=new Exception('Error sending client app data: '.$this->dbconn->error.' QUERY='.$sql); |
---|
301 | } |
---|
302 | } |
---|
303 | } |
---|
304 | //End operations |
---|
305 | $sql = "Update tmp_clients set status=1 where id = $cli_id and status=0"; |
---|
306 | $retry=1; |
---|
307 | $done=false; |
---|
308 | while (! $done and $retry < 4){ |
---|
309 | $res=$this->dbconn->query($sql); |
---|
310 | if ($res){ |
---|
311 | $done=true; |
---|
312 | }else{ |
---|
313 | $retry+=1; |
---|
314 | sleep($retry); |
---|
315 | } |
---|
316 | } |
---|
317 | if ($retry == 4 or $cli_id == false){ |
---|
318 | throw new Exception('Error commiting client data: '.$this->dbconn->error); |
---|
319 | } |
---|
320 | if ($err_apps){ |
---|
321 | throw $err_exception; |
---|
322 | } |
---|
323 | } |
---|
324 | |
---|
325 | private function load_alias(){ |
---|
326 | $sql="SELECT name,alias from Alias"; |
---|
327 | $result=$this->dbconn->query($sql); |
---|
328 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
329 | $this->alias[$row['name']]=$row['alias']; |
---|
330 | } |
---|
331 | } |
---|
332 | function get_system_data(){ |
---|
333 | $sql="select * from Config"; |
---|
334 | $result=$this->dbconn->query($sql); |
---|
335 | if ($result){ |
---|
336 | $tmp=[]; |
---|
337 | while ($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
338 | $tmp[$row['name']]=$row['value']; |
---|
339 | } |
---|
340 | return json_encode($tmp); |
---|
341 | } |
---|
342 | } |
---|
343 | function get_extended_data($app){ |
---|
344 | $today=date("Y-m-d"); |
---|
345 | $min_date=date("Y-m",strtotime($today." -1 year")).'-01'; |
---|
346 | $this->times=0; |
---|
347 | // CLIENTS DISTRIBUTION PER RELEASE/FLAVOUR |
---|
348 | $sql="select year(date) as year,month(date) as month,Releases_name,Flavours_name,count(*) as num_hosts from (select distinct Client_uid,date,Releases_name,Flavours_name from Client_Versions where date >= '$min_date' )t group by year,month,Releases_name,Flavours_name order by year Desc,month desc,Releases_name asc,num_hosts desc"; |
---|
349 | |
---|
350 | $cache = new Cache; |
---|
351 | $cache_key='extended_1'; |
---|
352 | $data = $cache->get($cache_key); |
---|
353 | $stime=microtime(true); |
---|
354 | if ($data != false){ |
---|
355 | $clients_month=$data; |
---|
356 | }else{ |
---|
357 | $result=$this->dbconn->query($sql); |
---|
358 | if ($result){ |
---|
359 | $this->times+=microtime(true)-$stime; |
---|
360 | $clients_month=[]; |
---|
361 | $tmp=[]; |
---|
362 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
363 | $date=$row['year'].'_'.$row['month']; |
---|
364 | $tmp[$date][$row['Releases_name']][$row['Flavours_name']]=intval($row['num_hosts']); |
---|
365 | } |
---|
366 | foreach ($tmp as $date){ |
---|
367 | $clients_month[]=$date; |
---|
368 | } |
---|
369 | $cache->store($clients_month,$cache_key); |
---|
370 | }else{ |
---|
371 | $clients_month=$this->dbconn->error; |
---|
372 | } |
---|
373 | } |
---|
374 | // architecture |
---|
375 | $sql="select year(date) as year, month(date) as month, Releases_name, count(arch) as count, arch from Client_Versions group by Releases_name,year,month,arch order by year desc,month desc,Releases_name asc,arch asc;"; |
---|
376 | $cache_key='extended_arch'; |
---|
377 | $data = $cache->get($cache_key); |
---|
378 | $stime=microtime(true); |
---|
379 | if ($data != false){ |
---|
380 | $clients_arch=$data; |
---|
381 | }else{ |
---|
382 | $result=$this->dbconn->query($sql); |
---|
383 | if ($result){ |
---|
384 | $this->times+=microtime(true)-$stime; |
---|
385 | $clients_arch=[]; |
---|
386 | $tmp=[]; |
---|
387 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
388 | $date=$row['year'].'_'.$row['month']; |
---|
389 | $tmp[$date][$row['Releases_name']][$row['arch']]=intval($row['count']); |
---|
390 | } |
---|
391 | foreach ($tmp as $date){ |
---|
392 | $clients_arch[]=$date; |
---|
393 | } |
---|
394 | $cache->store($clients_arch,$cache_key); |
---|
395 | }else{ |
---|
396 | $clients_arch=$this->dbconn->error; |
---|
397 | } |
---|
398 | } |
---|
399 | // memory |
---|
400 | $sql_2G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem < 2048000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;"; |
---|
401 | $cache_key='extended_memory_2G'; |
---|
402 | $data = $cache->get($cache_key); |
---|
403 | $stime=microtime(true); |
---|
404 | if ($data != false){ |
---|
405 | $clients_2G=$data; |
---|
406 | }else{ |
---|
407 | $result=$this->dbconn->query($sql_2G); |
---|
408 | if ($result){ |
---|
409 | $this->times+=microtime(true)-$stime; |
---|
410 | $clients_2G=[]; |
---|
411 | $tmp=[]; |
---|
412 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
413 | $date=$row['year'].'_'.$row['month']; |
---|
414 | $tmp[$date][$row['rel']]=intval($row['count']); |
---|
415 | } |
---|
416 | foreach ($tmp as $date){ |
---|
417 | $clients_2G[]=$date; |
---|
418 | } |
---|
419 | $cache->store($clients_2G,$cache_key); |
---|
420 | }else{ |
---|
421 | $clients_2G=$this->dbconn->error; |
---|
422 | } |
---|
423 | } |
---|
424 | $sql_4G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 2048000 and mem < 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;"; |
---|
425 | $cache_key='extended_memory_4G'; |
---|
426 | $data = $cache->get($cache_key); |
---|
427 | $stime=microtime(true); |
---|
428 | if ($data != false){ |
---|
429 | $clients_4G=$data; |
---|
430 | }else{ |
---|
431 | $result=$this->dbconn->query($sql_4G); |
---|
432 | if ($result){ |
---|
433 | $this->times+=microtime(true)-$stime; |
---|
434 | $clients_4G=[]; |
---|
435 | $tmp=[]; |
---|
436 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
437 | $date=$row['year'].'_'.$row['month']; |
---|
438 | $tmp[$date][$row['rel']]=intval($row['count']); |
---|
439 | } |
---|
440 | foreach ($tmp as $date){ |
---|
441 | $clients_4G[]=$date; |
---|
442 | } |
---|
443 | $cache->store($clients_4G,$cache_key); |
---|
444 | }else{ |
---|
445 | $clients_4G=$this->dbconn->error; |
---|
446 | } |
---|
447 | } |
---|
448 | $sql_8G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;"; |
---|
449 | $cache_key='extended_memory_8G'; |
---|
450 | $data = $cache->get($cache_key); |
---|
451 | $stime=microtime(true); |
---|
452 | if ($data != false){ |
---|
453 | $clients_8G=$data; |
---|
454 | }else{ |
---|
455 | $result=$this->dbconn->query($sql_8G); |
---|
456 | if ($result){ |
---|
457 | $this->times+=microtime(true)-$stime; |
---|
458 | $clients_8G=[]; |
---|
459 | $tmp=[]; |
---|
460 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
461 | $date=$row['year'].'_'.$row['month']; |
---|
462 | $tmp[$date][$row['rel']]=intval($row['count']); |
---|
463 | } |
---|
464 | foreach ($tmp as $date){ |
---|
465 | $clients_8G[]=$date; |
---|
466 | } |
---|
467 | $cache->store($clients_8G,$cache_key); |
---|
468 | }else{ |
---|
469 | $clients_8G=$this->dbconn->error; |
---|
470 | } |
---|
471 | } |
---|
472 | // cpu |
---|
473 | $sql_mono = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu = 1 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;"; |
---|
474 | $cache_key='extended_memory_cpu_mono'; |
---|
475 | $data = $cache->get($cache_key); |
---|
476 | $stime=microtime(true); |
---|
477 | if ($data != false){ |
---|
478 | $clients_mono=$data; |
---|
479 | }else{ |
---|
480 | $result=$this->dbconn->query($sql_mono); |
---|
481 | if ($result){ |
---|
482 | $this->times+=microtime(true)-$stime; |
---|
483 | $clients_mono=[]; |
---|
484 | $tmp=[]; |
---|
485 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
486 | $date=$row['year'].'_'.$row['month']; |
---|
487 | $tmp[$date][$row['rel']]=intval($row['count']); |
---|
488 | } |
---|
489 | foreach ($tmp as $date){ |
---|
490 | $clients_mono[]=$date; |
---|
491 | } |
---|
492 | $cache->store($clients_mono,$cache_key); |
---|
493 | }else{ |
---|
494 | $clients_mono=$this->dbconn->error; |
---|
495 | } |
---|
496 | } |
---|
497 | //$sql_dualquad = "select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where ncpu > 1 and ncpu < 5 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;"; |
---|
498 | $sql_dualquad = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 1 and ncpu < 5 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;"; |
---|
499 | $cache_key='extended_memory_cpu_dualquad'; |
---|
500 | $data = $cache->get($cache_key); |
---|
501 | $stime=microtime(true); |
---|
502 | if ($data != false){ |
---|
503 | $clients_dualquad=$data; |
---|
504 | }else{ |
---|
505 | $result=$this->dbconn->query($sql_dualquad); |
---|
506 | if ($result){ |
---|
507 | $this->times+=microtime(true)-$stime; |
---|
508 | $clients_dualquad=[]; |
---|
509 | $tmp=[]; |
---|
510 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
511 | $date=$row['year'].'_'.$row['month']; |
---|
512 | $tmp[$date][$row['rel']]=intval($row['count']); |
---|
513 | } |
---|
514 | foreach ($tmp as $date){ |
---|
515 | $clients_dualquad[]=$date; |
---|
516 | } |
---|
517 | $cache->store($clients_dualquad,$cache_key); |
---|
518 | }else{ |
---|
519 | $clients_dualquad=$this->dbconn->error; |
---|
520 | } |
---|
521 | } |
---|
522 | $sql_more = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 4 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;"; |
---|
523 | $cache_key='extended_memory_cpu_other'; |
---|
524 | $data = $cache->get($cache_key); |
---|
525 | $stime=microtime(true); |
---|
526 | if ($data != false){ |
---|
527 | $clients_more=$data; |
---|
528 | }else{ |
---|
529 | $result=$this->dbconn->query($sql_more); |
---|
530 | if ($result){ |
---|
531 | $this->times+=microtime(true)-$stime; |
---|
532 | $clients_more=[]; |
---|
533 | $tmp=[]; |
---|
534 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
535 | $date=$row['year'].'_'.$row['month']; |
---|
536 | $tmp[$date][$row['rel']]=intval($row['count']); |
---|
537 | } |
---|
538 | foreach ($tmp as $date){ |
---|
539 | $clients_more[]=$date; |
---|
540 | } |
---|
541 | $cache->store($clients_more,$cache_key); |
---|
542 | }else{ |
---|
543 | $clients_more=$this->dbconn->error; |
---|
544 | } |
---|
545 | } |
---|
546 | // CLIENT UPDATES |
---|
547 | $sql="select year,month,count(*) as nclients,sum(cnt)-count(*) as nclients_updated from (select Client_uid,count(Client_uid) as cnt,year(date) as year,month(date) as month from Client_Versions where date >= '$min_date' GROUP by Client_uid,year,month having count(Client_uid) >= 1 ) t group by year,month order by year desc,month desc"; |
---|
548 | $sql="select year,month,count(*) as nclients,sum(cnt)-count(*) as nclients_updated,Releases_name as rel,Flavours_name as fla from (select Client_uid,count(Client_uid) as cnt,year(date) as year,month(date) as month,Releases_name,Flavours_name from Client_Versions where date >= '$min_date' GROUP by Client_uid,year,month,Releases_name,Flavours_name having count(Client_uid) >= 1 ) t group by year,month,Releases_name,Flavours_name order by year desc,month desc"; |
---|
549 | |
---|
550 | $cache_key='extended_2'; |
---|
551 | $data = $cache->get($cache_key); |
---|
552 | $stime=microtime(true); |
---|
553 | if ($data != false){ |
---|
554 | $num_updates_month=$data; |
---|
555 | }else{ |
---|
556 | $result=$this->dbconn->query($sql); |
---|
557 | |
---|
558 | if ($result){ |
---|
559 | $this->times+=microtime(true)-$stime; |
---|
560 | $i=0; |
---|
561 | $tmp=[]; |
---|
562 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
563 | $date=$row['year'].'_'.$row['month']; |
---|
564 | $tmp[$date][$row['rel']][$row['fla']]=intval($row['nclients_updated']); |
---|
565 | } |
---|
566 | foreach($tmp as $date){ |
---|
567 | $num_updates_month[]=$date; |
---|
568 | } |
---|
569 | $cache->store($num_updates_month,$cache_key); |
---|
570 | }else{ |
---|
571 | $num_updates_month=$this->dbconn->error; |
---|
572 | } |
---|
573 | } |
---|
574 | |
---|
575 | |
---|
576 | // CLIENT CHANGE RELEASE |
---|
577 | $sql="select year,month,count(*) as upgrades_en_mes from (select year,month,Client_uid as cliente_upgradeado from (select Client_uid,year(date) as year,month(date) as month from Client_Versions where date >= '$min_date' GROUP by Client_uid,Releases_name,year,month)t group by month,year,Client_uid having(count(*))>1)t group by year,month order by year desc,month desc limit 12"; |
---|
578 | |
---|
579 | |
---|
580 | $cache_key='extended_3'; |
---|
581 | $data = $cache->get($cache_key); |
---|
582 | $stime=microtime(true); |
---|
583 | if ($data != false){ |
---|
584 | $change_releases=$data; |
---|
585 | }else{ |
---|
586 | $result=$this->dbconn->query($sql); |
---|
587 | |
---|
588 | if ($result){ |
---|
589 | $this->times+=microtime(true)-$stime; |
---|
590 | $change_releases=[0,0,0,0,0,0,0,0,0,0,0,0]; |
---|
591 | $i=0; |
---|
592 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
593 | $change_releases[$i++]=intval($row['upgrades_en_mes']); |
---|
594 | } |
---|
595 | $cache->store($change_releases,$cache_key); |
---|
596 | }else{ |
---|
597 | $change_releases=$this->dbconn->error; |
---|
598 | } |
---|
599 | } |
---|
600 | |
---|
601 | // CLIENT CHANGE FLAVOUR |
---|
602 | $sql="select year,month,count(*) as cambio_sabor_en_mes from (select year,month,Client_uid as cliente_upgradeado from (select Client_uid,year(date) as year,month(date) as month from Client_Versions where date >= '$min_date' GROUP by Client_uid,Flavours_name,year,month)t group by month,year,Client_uid having(count(*))>1)t group by year,month order by year desc,month desc limit 12"; |
---|
603 | |
---|
604 | $cache_key='extended_4'; |
---|
605 | $data = $cache->get($cache_key); |
---|
606 | $stime=microtime(true); |
---|
607 | if ($data != false){ |
---|
608 | $change_flavour=$data; |
---|
609 | }else{ |
---|
610 | $result=$this->dbconn->query($sql); |
---|
611 | |
---|
612 | if ($result){ |
---|
613 | $this->times+=microtime(true)-$stime; |
---|
614 | $i=0; |
---|
615 | $change_flavour=[0,0,0,0,0,0,0,0,0,0,0,0]; |
---|
616 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
617 | $change_flavour[$i++]=intval($row['cambio_sabor_en_mes']); |
---|
618 | } |
---|
619 | $cache->store($change_flavour,$cache_key); |
---|
620 | }else{ |
---|
621 | $change_flavour=$this->dbconn->error; |
---|
622 | } |
---|
623 | } |
---|
624 | |
---|
625 | |
---|
626 | //$sql= "select ltsp,count(*) as total from Client_Versions group by ltsp"; |
---|
627 | $sql="select compact.year as year,compact.month as month,if(compact.type='NONE',null,compact.type) as type,ifnull(results.total,0) as total from (select * from |
---|
628 | ( SELECT * FROM ( SELECT * FROM ( SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) t INNER JOIN ( SELECT YEAR(NOW()) AS year UNION ALL SELECT YEAR( DATE_SUB(NOW(), INTERVAL 1 YEAR)) ) t2 ) dates WHERE str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') >= DATE_SUB(NOW(),INTERVAL 1 YEAR) and str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') <= now() GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ) last_times inner join (select 'NONE' as type union all select 0 union all select 1) types) compact left join (select month(date) as month,year(date) as year,ifnull(ltsp,'NONE') as type,count(ifnull(ltsp,1)) as total from Client_Versions where Flavours_name = 'client' group by year(date),month(date),ltsp order by year(date) desc ,month(date) desc, ltsp desc ) results on compact.month = results.month and compact.year = results.year and compact.type = results.type"; |
---|
629 | //$count_ltsp=array('null'=>0,'true'=>0,'false'=>0); |
---|
630 | $count_ltsp=array(); |
---|
631 | $cache_key='extended_5'; |
---|
632 | $data=$cache->get($cache_key); |
---|
633 | $stime=microtime(true); |
---|
634 | if ($data != false){ |
---|
635 | $count_ltsp=$data; |
---|
636 | }else{ |
---|
637 | $result=$this->dbconn->query($sql); |
---|
638 | if ($result){ |
---|
639 | $this->times+=microtime(true)-$stime; |
---|
640 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
641 | if (isset($row['type']) and $row['type'] != NULL){ |
---|
642 | $count_ltsp[]=array($row['type'],$row['total']); |
---|
643 | } |
---|
644 | } |
---|
645 | $cache->store($count_ltsp,$cache_key); |
---|
646 | }else{ |
---|
647 | $count_ltsp=$this->dbconn->error; |
---|
648 | } |
---|
649 | } |
---|
650 | $cache_key='extended_6'; |
---|
651 | //$sql = "select t1.mode,count(t2.uuid) as total from (select 'THIN' as mode union all select 'SEMI' UNION ALL select 'FAT') t1 left join (select uuid,mode from Client_Versions where Flavours_name = 'client') t2 on t1.mode = t2.mode group by t1.mode"; |
---|
652 | $sql= "select compact.year as year,compact.month as month,if(compact.mode='NONE',null,compact.mode) as mode,ifnull(results.total,0) as total from (select * from ( SELECT * FROM ( SELECT * FROM ( SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) t INNER JOIN ( SELECT YEAR(NOW()) AS year UNION ALL SELECT YEAR( DATE_SUB(NOW(), INTERVAL 1 YEAR)) ) t2 ) dates WHERE str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') >= DATE_SUB(NOW(),INTERVAL 1 YEAR) and str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') <= now() GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ) last_times inner join (select 'NONE' as mode union all select 'THIN' union all select 'SEMI' union all select 'FAT') types) compact left join (select month(date) as month,year(date) as year,ifnull(mode,'NONE') as mode,count(ifnull(mode,1)) as total from Client_Versions where Flavours_name = 'client' group by year(date),month(date),mode order by year(date) desc ,month(date) desc, mode desc ) results on compact.month = results.month and compact.year = results.year and compact.mode = results.mode"; |
---|
653 | $data= $cache->get($cache_key); |
---|
654 | $stime=microtime(true); |
---|
655 | //$count_mode=array('thin'=>0,'semi'=>0,'fat'=>0); |
---|
656 | $count_mode=array(); |
---|
657 | if ($data != false){ |
---|
658 | $count_mode=$data; |
---|
659 | }else{ |
---|
660 | $result=$this->dbconn->query($sql); |
---|
661 | if ($result){ |
---|
662 | $this->times+=microtime(true)-$stime; |
---|
663 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
664 | if (isset($row['mode']) and $row['mode'] != NULL){ |
---|
665 | $count_mode[]=array(strtolower($row['mode']),$row['total']); |
---|
666 | } |
---|
667 | } |
---|
668 | $cache->store($count_mode,$cache_key); |
---|
669 | }else{ |
---|
670 | $count_mode=$this->dbconn->error; |
---|
671 | } |
---|
672 | } |
---|
673 | |
---|
674 | //sanitize input |
---|
675 | if ($app != NULL){ |
---|
676 | $app=preg_grep('/^[a-zA-Z0-9\-_]+$/',array($app)); |
---|
677 | if ($app != NULL and isset($app[0])){ |
---|
678 | $app=$this->dbconn->real_escape_string($app[0]); |
---|
679 | $stats['apps']=[]; |
---|
680 | $stats['apps']['app']=$app; |
---|
681 | $sql="select year(date) as year,month(date) as month,string,Releases_name as rel,Flavours_name as fla,sum(count) as count from RecvPackages where string='$app' and date >= '$min_date' group by year,month,Releases_name,Flavours_name order by year desc,month desc,sum(count) desc"; |
---|
682 | |
---|
683 | $cache_key="extended_app_$app"; |
---|
684 | $data = $cache->get($cache_key); |
---|
685 | $stime=microtime(true); |
---|
686 | if ($data != false){ |
---|
687 | $app_use=$data; |
---|
688 | $stats['apps']['app_use']=$app_use; |
---|
689 | }else{ |
---|
690 | $result=$this->dbconn->query($sql); |
---|
691 | if ($result){ |
---|
692 | $this->times+=microtime(true)-$stime; |
---|
693 | $tmp=[]; |
---|
694 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
695 | $date=$row['year'].'_'.$row['month']; |
---|
696 | $tmp[$date][$row['rel']][$row['fla']]=intval($row['count']); |
---|
697 | } |
---|
698 | foreach($tmp as $date){ |
---|
699 | $app_use[]=$date; |
---|
700 | } |
---|
701 | $cache->store($app_use,$cache_key); |
---|
702 | $stats['apps']['app_use']=$app_use; |
---|
703 | }else{ |
---|
704 | $app_use=$this->dbconn->error; |
---|
705 | } |
---|
706 | } |
---|
707 | |
---|
708 | } |
---|
709 | } |
---|
710 | // FINALIZATION & WRITE STRUCTURE |
---|
711 | if (isset($clients_month)){ |
---|
712 | $stats['clients']['clients_per_month']=$clients_month; |
---|
713 | } |
---|
714 | if (isset($num_updates_month)){ |
---|
715 | $stats['clients']['freq_updates_per_month']=$num_updates_month; |
---|
716 | } |
---|
717 | if (isset($change_releases)){ |
---|
718 | $stats['clients']['change_releases']=$change_releases; |
---|
719 | } |
---|
720 | if (isset($change_flavour)){ |
---|
721 | $stats['clients']['change_flavours']=$change_flavour; |
---|
722 | } |
---|
723 | if (isset($clients_arch)){ |
---|
724 | $stats['clients']['architectures']=$clients_arch; |
---|
725 | } |
---|
726 | if (isset($clients_2G)){ |
---|
727 | $stats['clients']['mem']['2G']=$clients_2G; |
---|
728 | } |
---|
729 | if (isset($clients_4G)){ |
---|
730 | $stats['clients']['mem']['4G']=$clients_4G; |
---|
731 | } |
---|
732 | if (isset($clients_8G)){ |
---|
733 | $stats['clients']['mem']['8G']=$clients_8G; |
---|
734 | } |
---|
735 | if (isset($clients_mono)){ |
---|
736 | $stats['clients']['cpu']['mono']=$clients_mono; |
---|
737 | } |
---|
738 | if (isset($clients_dualquad)){ |
---|
739 | $stats['clients']['cpu']['dualquad']=$clients_dualquad; |
---|
740 | } |
---|
741 | if (isset($clients_more)){ |
---|
742 | $stats['clients']['cpu']['more']=$clients_more; |
---|
743 | } |
---|
744 | if (isset($count_ltsp)){ |
---|
745 | $stats['clients']['count_ltsp']=$count_ltsp; |
---|
746 | } |
---|
747 | if (isset($clients_more)){ |
---|
748 | $stats['clients']['count_mode']=$count_mode; |
---|
749 | } |
---|
750 | $stats['debug_query_time']=strval(number_format($this->times,5)); |
---|
751 | if (file_exists($this->ka_file)){ |
---|
752 | $stats['debug_keep_alive']=date('Y-m-d H:i',file_get_contents($this->ka_file)); |
---|
753 | } |
---|
754 | return json_encode($stats); |
---|
755 | } |
---|
756 | |
---|
757 | function get_historic_data($typechart=''){ |
---|
758 | $this->load_alias(); |
---|
759 | $obj=[]; |
---|
760 | $this->times=0; |
---|
761 | foreach ($this->info_distro['distros'] as $distro){ |
---|
762 | $dname=$distro['name']; |
---|
763 | $dlike=$distro['like']; |
---|
764 | $obj[$dname]=array(); |
---|
765 | foreach ($distro['sabor'] as $sabor){ |
---|
766 | $sname=$sabor['name']; |
---|
767 | $slike=$sabor['like']; |
---|
768 | $obj[$dname][$sname][]=$this->get_chart($dlike,$slike,'current',$typechart); |
---|
769 | $obj[$dname][$sname][]=$this->get_chart($dlike,$slike,'old',$typechart); |
---|
770 | $obj[$dname][$sname][]=$this->get_chart($dlike,$slike,'very_old',$typechart); |
---|
771 | } |
---|
772 | } |
---|
773 | $obj['debug_query_time']=strval(number_format($this->times,3)); |
---|
774 | |
---|
775 | if (file_exists($this->ka_file)){ |
---|
776 | $obj['debug_keep_alive']=date('Y-m-d H:i',file_get_contents($this->ka_file)); |
---|
777 | } |
---|
778 | return json_encode($obj); |
---|
779 | } |
---|
780 | |
---|
781 | function get_chart($version='',$sabor='',$type='current',$typechart=''){ |
---|
782 | if ($typechart == ''){ |
---|
783 | $cache_key="$version"."_"."$sabor"."_"."$type"; |
---|
784 | $limit = "limit 10"; |
---|
785 | $sql_limit=10; |
---|
786 | $and = ""; |
---|
787 | }else{ |
---|
788 | $cache_key="$version"."_"."$sabor"."_"."$type"."_"."$typechart"; |
---|
789 | $limit = ""; |
---|
790 | $sql_limit=1000; |
---|
791 | $and = "and string in ( select `name` from `PackagesWhitelist` where status = '1' )"; |
---|
792 | } |
---|
793 | if ($version != ''){ |
---|
794 | $version = " and Releases_name = '$version' "; |
---|
795 | } |
---|
796 | if ($sabor != ''){ |
---|
797 | $sabor = " and Flavours_name = '$sabor' "; |
---|
798 | } |
---|
799 | $order=" order by count desc $limit "; |
---|
800 | $group=" group by app "; |
---|
801 | |
---|
802 | $where=$this->dates['date_'.$type]." $version $sabor "; |
---|
803 | $where_clients=$this->dates['date_'.$type]." $version $sabor "; |
---|
804 | |
---|
805 | $sql="SELECT string as app,sum(count) as count from RecvPackages where $where $and $group $order"; |
---|
806 | $sql_clients = "select count(distinct Client_uid) as count from Client_Versions where $where_clients $order"; |
---|
807 | $cache = new Cache; |
---|
808 | $data = $cache->get($cache_key); |
---|
809 | if ($data != false){ |
---|
810 | return $data; |
---|
811 | }else{ |
---|
812 | $data=array($this->get_result_from_sql($sql,$sql_limit),$this->get_clients_from_sql($sql_clients)); |
---|
813 | $cache->store($data,$cache_key); |
---|
814 | return $data; |
---|
815 | } |
---|
816 | } |
---|
817 | function get_clients_from_sql($sql){ |
---|
818 | $stime=microtime(true); |
---|
819 | if ($result=$this->dbconn->query($sql)){ |
---|
820 | $etime=microtime(true); |
---|
821 | $this->times+=($etime-$stime); |
---|
822 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
823 | if (isset($row['count'])){ |
---|
824 | return array('nclients'=>$row['count']); |
---|
825 | } |
---|
826 | } |
---|
827 | return array('nclients'=>'not available'); |
---|
828 | }else{ |
---|
829 | return array('nclients'=>$this->dbconn->error); |
---|
830 | } |
---|
831 | |
---|
832 | } |
---|
833 | function get_result_from_sql($sql,$limit){ |
---|
834 | $stime=microtime(true); |
---|
835 | if ($result=$this->dbconn->query($sql)){ |
---|
836 | $etime=microtime(true); |
---|
837 | $this->times+=($etime-$stime); |
---|
838 | $obj2=[]; |
---|
839 | $nobj=0; |
---|
840 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
841 | if (array_key_exists($row['app'],$this->alias)){ |
---|
842 | if (! empty($this->alias[$row['app']])){ |
---|
843 | if ($nobj < $limit) |
---|
844 | $obj2[$this->alias[$row['app']]]=$row['count']; |
---|
845 | $nobj++; |
---|
846 | } |
---|
847 | }else{ |
---|
848 | if ($nobj < $limit) |
---|
849 | $obj2[$row['app']]=$row['count']; |
---|
850 | $nobj++; |
---|
851 | } |
---|
852 | } |
---|
853 | return $obj2; |
---|
854 | }else{ |
---|
855 | return $this->dbconn->error; |
---|
856 | } |
---|
857 | } |
---|
858 | function get_whitelist_form(){ |
---|
859 | $sql = "select distinct `string` from `RecvPackages` order by `string` asc"; |
---|
860 | $sql2 = "select `name`,`status` from `PackagesWhitelist`"; |
---|
861 | $result=$this->dbconn->query($sql); |
---|
862 | $all_items=array(); |
---|
863 | if ($result){ |
---|
864 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
865 | $all_items[]=$row; |
---|
866 | } |
---|
867 | } |
---|
868 | $whitelist=array(); |
---|
869 | $result = $this->dbconn->query($sql2); |
---|
870 | if ($result){ |
---|
871 | while($row=$result->fetch_array(MYSQLI_ASSOC)){ |
---|
872 | $whitelist[] = $row; |
---|
873 | } |
---|
874 | } |
---|
875 | if (count($all_items) == 0){ |
---|
876 | return false; |
---|
877 | } |
---|
878 | $classify = ['in'=> array(), 'out'=>array(), 'unk' => array()]; |
---|
879 | $clasified = array(); |
---|
880 | foreach ($whitelist as $tmp){ |
---|
881 | if ($tmp['status'] == '0'){ |
---|
882 | $classify['out'][]=$tmp['name']; |
---|
883 | $clasified[] = $tmp['name']; |
---|
884 | }else if($tmp['status'] == '1'){ |
---|
885 | $classify['in'][]=$tmp['name']; |
---|
886 | $clasified[] = $tmp['name']; |
---|
887 | } |
---|
888 | } |
---|
889 | foreach ($all_items as $tmp){ |
---|
890 | if (! in_array($tmp['string'],$clasified)){ |
---|
891 | $classify['unk'][] = $tmp['string']; |
---|
892 | } |
---|
893 | } |
---|
894 | return $classify; |
---|
895 | } |
---|
896 | } |
---|
897 | |
---|
898 | ?> |
---|