ESP32 e-Paper info screen (client part).
[weathermon.git] / web / cgi.php
1 <?php
2
3 $query=$_REQUEST['query'];
4
5 $client_ip = $_SERVER["REMOTE_ADDR"];
6
7 include('config_local.php');
8
9 function startsWith($haystack, $needle)
10 {
11      $length = strlen($needle);
12      return (substr($haystack, 0, $length) === $needle);
13 }
14
15 if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_schema",$mysql_user,$mysql_pwd,array( PDO::ATTR_PERSISTENT => false)))) {
16   die($err);
17 }  
18
19 $db -> exec('SET CHARACTER SET utf8');
20
21 $auth_token = $_COOKIE["auth-token"];
22 $auth = 0;
23
24 if ($auth_token) {
25
26   $sql = "
27     SELECT COUNT(*) AS auth FROM tokens WHERE str=:s and expires>now()
28   ";
29   
30   $q = $db -> prepare( $sql );
31   $q -> bindParam(':s',$auth_token,PDO::PARAM_INT);
32   $q -> execute();
33
34   $res = [];
35
36   $row = $q -> fetch(PDO::FETCH_ASSOC);
37   $auth = $row['auth'];
38
39
40
41 if ($auth || (strpos($client_ip, "192.168.") === 0) || (strpos($client_ip, "10.8.") === 0)) {
42   $filter = '';
43 } else {
44   $filter = ' and s.is_public=1';
45 }
46
47 $hash = md5($_SERVER['QUERY_STRING'].":".$filter);
48 $redis = new Redis();
49 $redis->pconnect('127.0.0.1', 6379);
50 $results = $redis->get('meteo-'.$hash);
51 if ($results) {
52
53   $results = unserialize($results);
54   print(json_encode($results));
55   return;
56
57 }
58
59 function getYears($db) {
60
61   $sql = "
62     SELECT DISTINCT DATE_FORMAT(day,'%Y') y FROM sensors_ranges ORDER BY y DESC
63   ";
64   
65   $q = $db -> prepare( $sql );
66   $q -> execute();
67
68   $res = [];
69
70   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
71     array_push($res, $row['y']);
72   }
73
74   return $res;  
75
76 }
77
78 function getMonths($db,$year) {
79
80   $y = intval($year);
81
82   $sql = "
83     SELECT DISTINCT DATE_FORMAT(day,'%m') m FROM sensors_ranges WHERE day>=STR_TO_DATE('".strval($y)."-01-01','%Y-%m-%d') and day<STR_TO_DATE('".strval($y+1)."-01-01','%Y-%m-%d') ORDER BY m DESC
84   ";
85
86   $q = $db -> prepare( $sql );
87   $q -> execute();
88
89   $res = [];
90
91   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
92     array_push($res, $row['m']);
93   }
94
95   return $res;  
96
97 }
98
99 function getDays($db,$year,$month) {
100
101   $y = intval($year);
102   $m = intval($month);
103
104   $sql = "
105     SELECT DISTINCT DATE_FORMAT(day,'%d') d FROM sensors_ranges WHERE day>=STR_TO_DATE('".strval($y)."-".strval($m)."-01','%Y-%m-%d') and DATE_ADD(STR_TO_DATE('".strval($y)."-".strval($m)."-01','%Y-%m-%d'), INTERVAL 1 MONTH) ORDER BY d DESC
106   ";
107
108   $q = $db -> prepare( $sql );
109   $q -> execute();
110
111   $res = [];
112
113   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
114     array_push($res, $row['d']);
115   }
116
117   return $res;  
118
119 }
120
121 function getSensors($db,$year,$month,$day) {
122
123   global $filter;
124
125   $y = intval($year);
126   $m = intval($month);
127   $d = intval($day);
128
129   $sql = "
130     SELECT DISTINCT 
131       CONCAT(s_id,'.',t.st_abbr,'.',p.st_name) id
132     FROM
133       sensors_ranges r, sensors s, sensor_types t, st_parameters p
134     WHERE 
135       r.sensor=s.id and r.parameter=p.id and s.st_id=t.id and p.st_id=t.id ".$filter."
136       and r.day=STR_TO_DATE('".strval($y)."-".strval($m)."-".strval($d)."','%Y-%m-%d')
137     UNION
138     SELECT DISTINCT
139       CONCAT(s_id,'.',t.st_abbr,'.*') id
140     FROM
141       sensors_ranges r, sensors s, sensor_types t
142     WHERE 
143       r.sensor=s.id and s.st_id=t.id and t.st_common_description is not null ".$filter."
144       and r.day=STR_TO_DATE('".strval($y)."-".strval($m)."-".strval($d)."','%Y-%m-%d')
145   ";
146
147   $q = $db -> prepare( $sql );
148   $q -> execute();
149
150   $res = [];
151
152   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
153     array_push($res, $row['id']);
154   }
155
156   return $res;  
157
158 }
159
160 function getCurrentValues($db,$s_id,$p_id) {
161
162   global $filter;
163
164   $sql = "
165     SELECT 
166       u.id stored_unit,du.id display_unit 
167     FROM 
168       sensors s,sensor_types t,st_parameters p,units u,units du
169     WHERE s.st_id=t.id and p.st_id=t.id and p.st_unit=u.id and u.unit_group=du.unit_group and du.is_default=1 and s.id=:id and p.id=:param
170   ";
171
172   $q = $db -> prepare( $sql );
173   $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
174   $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
175   $q -> execute();
176   $units = $q -> fetch(PDO::FETCH_ASSOC);
177
178   $sql = "
179       SELECT 
180         CONCAT(subset.t,'5:00') t,UnitConv(avg(subset.y),:stored,:display) y
181       FROM (
182         SELECT 
183           substr(date_format(timestamp,'%Y-%m-%dT%H:%i'),1,15) t,value y
184         FROM 
185           sensor_values
186         WHERE 
187           sensor_id = :id and parameter_id=:param 
188           and timestamp>adddate(now(),-1)
189         ) subset 
190       GROUP BY subset.t
191       ORDER BY subset.t
192     ";
193   
194   $q = $db -> prepare( $sql );
195   $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
196   $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
197   $q -> bindParam(':stored',$units['stored_unit'],PDO::PARAM_INT);
198   $q -> bindParam(':display',$units['display_unit'],PDO::PARAM_INT);
199   $q -> execute();
200
201   return $q -> fetchAll(PDO::FETCH_ASSOC);
202   
203 }
204
205 function getCurrent($db,$id,$type,$param) {
206
207   global $filter;
208
209   if ($param == "*") {
210
211     $sql = "
212       SELECT s.id s_id,p.id p_id, p.st_name p_name 
213       FROM sensors s,sensor_types t, st_parameters p
214       WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type ".$filter."
215     ";
216
217     $q = $db -> prepare( $sql );
218     $q -> bindParam(':id',$id,PDO::PARAM_STR);
219     $q -> bindParam(':type',$type,PDO::PARAM_STR);
220     $q -> execute();
221     $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
222
223     $results = array();
224
225     foreach ($sensors as $sensor) {
226
227       $results[$sensor['p_name']] = getCurrentValues($db,$sensor['s_id'],$sensor['p_id']);
228     
229     }
230
231     return $results;
232   
233   } else {
234
235     $sql = "
236       SELECT s.id s_id,p.id p_id 
237       FROM sensors s,sensor_types t, st_parameters p
238       WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type and p.st_name=:param ".$filter."
239     ";
240
241     $q = $db -> prepare( $sql );
242     $q -> bindParam(':id',$id,PDO::PARAM_STR);
243     $q -> bindParam(':type',$type,PDO::PARAM_STR);
244     $q -> bindParam(':param',$param,PDO::PARAM_STR);
245     $q -> execute();
246     $sensor = $q -> fetch(PDO::FETCH_ASSOC);
247
248     return getCurrentValues($db,$sensor['s_id'],$sensor['p_id']);
249
250   }
251
252 }
253
254 function getArchiveValues($db,$year,$month,$day,$s_id,$p_id) {
255
256   global $filter;
257
258   $sql = "
259     SELECT 
260       u.id stored_unit,du.id display_unit 
261     FROM 
262       sensors s,sensor_types t,st_parameters p,units u,units du
263     WHERE s.st_id=t.id and p.st_id=t.id and p.st_unit=u.id and u.unit_group=du.unit_group and du.is_default=1 and s.id=:id and p.id=:param
264   ";
265
266   $q = $db -> prepare( $sql );
267   $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
268   $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
269   $q -> execute();
270   $units = $q -> fetch(PDO::FETCH_ASSOC);
271
272   $sql = "
273     SELECT 
274       CONCAT(subset.t,'5:00') t,UnitConv(avg(subset.y),:stored,:display) y
275     FROM (
276       SELECT 
277         substr(date_format(timestamp,'%Y-%m-%dT%H:%i'),1,15) t,value y
278       FROM 
279         sensor_values
280       WHERE 
281         sensor_id = :id and parameter_id=:param 
282         and timestamp>=STR_TO_DATE(:d,'%Y-%m-%d')
283         and timestamp<DATE_ADD(STR_TO_DATE(:d,'%Y-%m-%d'), interval 1 day)
284       ) subset 
285     GROUP BY subset.t
286     ORDER BY subset.t
287   ";
288
289   $date = $year.'-'.$month.'-'.$day;
290
291   $q = $db -> prepare( $sql );
292   $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
293   $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
294   $q -> bindParam(':d',$date,PDO::PARAM_STR);
295   $q -> bindParam(':stored',$units['stored_unit'],PDO::PARAM_INT);
296   $q -> bindParam(':display',$units['display_unit'],PDO::PARAM_INT);
297   $q -> execute();
298
299   return $q -> fetchAll(PDO::FETCH_ASSOC);
300
301 }
302
303 function getArchive($db,$year,$month,$day,$id,$type,$param) {
304
305   global $filter;
306
307   $y = intval($year);
308   $m = intval($month);
309   $d = intval($day);
310   
311   $date = strval($y).'-'.strval($m).'-'.strval($d);
312
313   if ($param == "*") {
314
315     $sql = "
316       SELECT s.id s_id,p.id p_id, p.st_name p_name 
317       FROM sensors s,sensor_types t, st_parameters p
318       WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type ".$filter."
319     ";
320
321     $q = $db -> prepare( $sql );
322     $q -> bindParam(':id',$id,PDO::PARAM_STR);
323     $q -> bindParam(':type',$type,PDO::PARAM_STR);
324     $q -> execute();
325     $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
326
327     $results = array();
328
329     foreach ($sensors as $sensor) {
330
331       $result[$sensor['p_name']] = getArchiveValues($db,$year,$month,$day,$sensor['s_id'],$sensor['p_id']);
332
333     }
334     
335     return $result;
336     
337   } else {
338
339     $sql = "
340       SELECT s.id s_id,p.id p_id 
341       FROM sensors s,sensor_types t, st_parameters p
342       WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type and p.st_name=:param ".$filter."
343     ";
344
345     $q = $db -> prepare( $sql );
346     $q -> bindParam(':id',$id,PDO::PARAM_STR);
347     $q -> bindParam(':type',$type,PDO::PARAM_STR);
348     $q -> bindParam(':param',$param,PDO::PARAM_STR);
349     $q -> execute();
350     $sensor = $q -> fetch(PDO::FETCH_ASSOC);
351
352     return getArchiveValues($db,$year,$month,$day,$sensor['s_id'],$sensor['p_id']);
353
354   }
355
356 }
357
358 function getProps($db, $localNet) {
359
360   global $filter;
361   
362   $sql = "
363     SELECT 
364       CONCAT(s_id,'.',t.st_abbr,'.',p.st_name) sensor_id,
365       p.st_description name,
366       du.name_short unit,
367       du.prec prec,
368       pl.place_name,
369       p.st_line_color color
370     FROM 
371       sensors s, sensor_types t, st_parameters p,units u,units du,places pl
372     WHERE 
373       s.st_id=t.id and p.st_id=t.id and p.st_unit=u.id and u.unit_group=du.unit_group and du.is_default=1 and pl.idplaces=s.place_id ".$filter."
374   ";
375   
376   $q = $db -> prepare( $sql );
377   $q -> execute();
378   $reply = [ 
379     "names" => [], 
380     "colors" => [], 
381     "units" => [], 
382     "scale" => [], 
383     "places" => [],
384     "fonts" => [ 
385       "axes" => [ "color" => "black", "size" => 16, "style" => "normal" ], 
386       "legend" => [ "color" => "black", "size" => 16, "style" => "normal" 
387   ] ] ];
388
389   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
390     $reply["names"][$row["sensor_id"]] = $row["name"];
391     $reply["colors"][$row["sensor_id"]] = $row["color"];
392     $reply["units"][$row["sensor_id"]] = $row["unit"];
393     $reply["places"][$row["sensor_id"]] = $row["place_name"];
394     $reply["scale"][$row["sensor_id"]] = [ 0 => 1.0, 1 => $row["prec"] ];
395   }
396
397   $sql = "
398     SELECT DISTINCT
399       CONCAT(s_id,'.',t.st_abbr,'.*') sensor_id,
400       t.st_common_description name,
401       pl.place_name
402     FROM
403       sensors s, sensor_types t, places pl
404     WHERE 
405       pl.idplaces=s.place_id and s.st_id=t.id and t.st_common_description is not null ".$filter."
406   ";
407
408   $q = $db -> prepare( $sql );
409   $q -> execute();
410   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
411     $reply["names"][$row["sensor_id"]] = $row["name"];
412     $reply["places"][$row["sensor_id"]] = $row["place_name"];
413   }
414   
415   return $reply;
416   
417 }
418
419 function getState($db, $localNet) {
420
421   global $filter;
422   
423   $sql = "
424     SELECT 
425       DISTINCT 
426         s.s_id as sensor_id,
427         st.st_abbr,
428         v.sensor as sensor_int_id,
429         pl.place_name s_description,
430         p.id as param_id,
431         p.st_name as param_name,
432         p.st_description,
433         s.place_id,
434         u.id stored_unit_id,
435         du.id unit_id
436     FROM 
437       sensors_ranges v,st_parameters p,sensors s,places pl,sensor_types st,units u,units du
438     WHERE 
439       v.timestamp>addtime(now(), -43200) 
440       and s.st_id=st.id
441       and v.sensor=s.id
442       and s.st_id=st.id
443       and v.parameter=p.id  
444       and s.st_id=p.st_id
445       and p.id>=0
446       and s.place_id=pl.idplaces
447       and p.st_unit=u.id 
448       and u.unit_group=du.unit_group
449       and du.is_default=1
450       ".$filter."
451     ORDER BY
452       s_description,sensor_id,param_id
453   ";
454
455   $q = $db -> prepare( $sql );
456   $q -> execute();
457
458   $reply = [];
459
460   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
461
462     $sensor_id = $row['sensor_id'];
463     $st_id = $row['st_abbr'];
464     $sensor_int_id = $row['sensor_int_id'];
465     $param_id = $row['param_name'];
466     $param_int_id = $row['param_id'];
467     $unit_id = $row['unit_id'];
468     $stored_unit_id = $row['stored_unit_id'];
469     $place_description = $row['s_description'];
470
471     $sql_last_val = "
472       SELECT UnitConv(value,".$stored_unit_id.",".$unit_id.") as val,timestamp
473       FROM
474         sensor_values
475       WHERE 
476         sensor_id = ".$sensor_int_id." and parameter_id=".$param_int_id."
477       ORDER BY
478         timestamp DESC
479       LIMIT 1
480     ";
481
482     $qv = $db -> prepare( $sql_last_val );
483     $qv -> execute();
484
485     $v_row = $qv -> fetch(PDO::FETCH_ASSOC);
486     
487     $value = $v_row["val"];
488     $timestamp = $v_row["timestamp"];
489
490     if (! array_key_exists($place_description,$reply)) {
491       $reply[$place_description] = [];
492     }
493
494     if (! array_key_exists($sensor_id,$reply[$place_description])) {
495       $reply[$place_description][$sensor_id] = [];
496     }
497     
498     if (! array_key_exists($st_id,$reply[$place_description][$sensor_id])) {
499       $reply[$place_description][$sensor_id][$st_id] = [];
500     }
501
502     $reply[$place_description][$sensor_id][$st_id][$param_id] = $value;
503     $reply[$place_description][$sensor_id]['timestamp'] = $timestamp;
504
505   }
506
507   return $reply;
508   
509 }
510
511 function getPanel($db) {
512
513   $sql = "
514     SELECT 
515       DISTINCT 
516         v.sensor as sensor_int_id,
517         p.id as param_id,
518         p.st_name as param_name,
519         p.st_description,
520         u.id stored_unit_id,
521         du.id unit_id
522     FROM 
523       sensors_ranges v,st_parameters p,sensors s,places pl,sensor_types st,units u,units du
524     WHERE 
525       v.timestamp>addtime(now(), -43200) 
526       and s.st_id=st.id
527       and v.sensor=s.id
528       and s.st_id=st.id
529       and v.parameter=p.id  
530       and s.st_id=p.st_id
531       and p.id>=0
532       and s.place_id=pl.idplaces
533       and p.st_unit=u.id 
534       and u.unit_group=du.unit_group
535       and du.is_default=1
536       and (s.s_id,p.st_name) in (select sensor_id,param_name from panel_sensors)
537     ORDER BY
538       param_id
539     ";
540
541   $q = $db -> prepare( $sql );
542   $q -> execute();
543
544   $reply = [];
545
546   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
547
548     $sensor_id = $row['sensor_id'];
549     $sensor_int_id = $row['sensor_int_id'];
550     $param_id = $row['param_name'];
551     $param_int_id = $row['param_id'];
552     $unit_id = $row['unit_id'];
553     $stored_unit_id = $row['stored_unit_id'];
554
555     $sql_last_val = "
556       SELECT UnitConv(value,".$stored_unit_id.",".$unit_id.") as val,timestamp
557       FROM
558         sensor_values
559       WHERE 
560         sensor_id = ".$sensor_int_id." and parameter_id=".$param_int_id."
561         and timestamp > NOW() - interval 30 minute
562       ORDER BY
563         timestamp DESC
564       LIMIT 1
565     ";
566
567     $qv = $db -> prepare( $sql_last_val );
568     $qv -> execute();
569
570     $v_row = $qv -> fetch(PDO::FETCH_ASSOC);
571     
572     $value = $v_row["val"];
573
574     if ($value && $value != "null") {
575       $reply[strtoupper($param_id)] = $value;
576     }
577
578   }
579
580   return $reply;
581   
582 }
583
584 $expire = 60;
585
586 if ($query == 'props') {
587
588   $reply = getProps($db, $local_net);
589   $expire = 60;
590
591 } elseif ($query == 'state') {
592
593   $reply = getState($db, $local_net);
594
595 } elseif ($query == 'panel') {
596
597   $reply = getPanel($db);
598
599 } elseif (startsWith($query,'get/')) {
600
601   $sensor = explode('/',substr($query,strlen('get/')));
602   $reply = getCurrent($db,$sensor[0],$sensor[1],$sensor[2]);
603
604 } elseif ($query == 'years') {
605
606   $reply = getYears($db);
607   $expire = 3600;
608
609 } elseif (startsWith($query,'months/')) {
610
611   $date = explode('/',substr($query,strlen('months/')));
612   $reply = getMonths($db,$date[0]);
613   $expire = 3600;
614
615 } elseif (startsWith($query,'days/')) {
616
617   $date = explode('/',substr($query,strlen('days/')));
618   $reply = getDays($db,$date[0],$date[1]);
619   $expire = 3600;
620
621 } elseif (startsWith($query,'sensors/')) {
622
623   $date = explode('/',substr($query,strlen('sensors/')));
624   $reply = getSensors($db,$date[0],$date[1],$date[2]);
625   $expire = 3600;
626
627 } elseif (startsWith($query,'get-archive/')) {
628
629   $path = explode('/',substr($query,strlen('get-archive/')));
630   $reply = getArchive($db,$path[0],$path[1],$path[2],$path[3],$path[4],$path[5]);
631   $expire = 14400;
632
633 }
634
635 if ($reply) {
636
637   $redis->set('meteo-'.$hash, serialize($reply));
638   $redis->expire('meteo-'.$hash, $expire);
639   
640   print(json_encode($reply));
641
642 }
643
644 ?>