X-Git-Url: https://git.rvb.name/weathermon.git/blobdiff_plain/7d6cb9a3d58687e0bf3ca3c30882782fe7ae12b8..2094fb89e05795f5daee526dc4617a169faba201:/web/cgi.php?ds=inline

diff --git a/web/cgi.php b/web/cgi.php
new file mode 100644
index 0000000..facdc70
--- /dev/null
+++ b/web/cgi.php
@@ -0,0 +1,467 @@
+<?php
+
+$query=$_REQUEST['query'];
+
+$client_ip = $_SERVER["REMOTE_ADDR"];
+
+include('config_local.php');
+
+function startsWith($haystack, $needle)
+{
+     $length = strlen($needle);
+     return (substr($haystack, 0, $length) === $needle);
+}
+
+if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_schema",$mysql_user,$mysql_pwd,array( PDO::ATTR_PERSISTENT => false)))) {
+  die($err);
+}  
+
+$db -> exec('SET CHARACTER SET utf8');
+
+$auth_token = $_COOKIE["auth-token"];
+$auth = 0;
+
+if ($auth_token) {
+
+  $sql = "
+    SELECT COUNT(*) AS auth FROM tokens WHERE str=:s and expires>now()
+  ";
+  
+  $q = $db -> prepare( $sql );
+  $q -> bindParam(':s',$auth_token,PDO::PARAM_INT);
+  $q -> execute();
+
+  $res = [];
+
+  $row = $q -> fetch(PDO::FETCH_ASSOC);
+  $auth = $row['auth'];
+
+} 
+
+if ($auth || (strpos($client_ip, "192.168.") === 0) || (strpos($client_ip, "10.8.") === 0)) {
+  $filter = '';
+} else {
+  $filter = ' and s.is_public=1';
+}
+
+$hash = md5($_SERVER['QUERY_STRING'].":".$filter);
+$redis = new Redis();
+$redis->pconnect('127.0.0.1', 6379);
+$results = $redis->get('meteo-'.$hash);
+if ($results) {
+
+  $results = unserialize($results);
+  print(json_encode($results));
+  return;
+
+}
+
+function getYears($db) {
+
+  $sql = "
+    SELECT DISTINCT DATE_FORMAT(day,'%Y') y FROM sensors_ranges ORDER BY y DESC
+  ";
+  
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+
+  $res = [];
+
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+    array_push($res, $row['y']);
+  }
+
+  return $res;  
+
+}
+
+function getMonths($db,$year) {
+
+  $y = intval($year);
+
+  $sql = "
+    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
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+
+  $res = [];
+
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+    array_push($res, $row['m']);
+  }
+
+  return $res;  
+
+}
+
+function getDays($db,$year,$month) {
+
+  $y = intval($year);
+  $m = intval($month);
+
+  $sql = "
+    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
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+
+  $res = [];
+
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+    array_push($res, $row['d']);
+  }
+
+  return $res;  
+
+}
+
+function getSensors($db,$year,$month,$day) {
+
+  global $filter;
+
+  $y = intval($year);
+  $m = intval($month);
+  $d = intval($day);
+
+  $sql = "
+    SELECT DISTINCT 
+      CONCAT(s_id,'.',t.st_abbr,'.',p.st_name) id
+    FROM
+      sensors_ranges r, sensors s, sensor_types t, st_parameters p
+    WHERE 
+      r.sensor=s.id and r.parameter=p.id and s.st_id=t.id and p.st_id=t.id ".$filter."
+      and r.day=STR_TO_DATE('".strval($y)."-".strval($m)."-".strval($d)."','%Y-%m-%d')
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+
+  $res = [];
+
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+    array_push($res, $row['id']);
+  }
+
+  return $res;  
+
+}
+
+function getCurrent($db,$id,$type,$param) {
+
+  global $filter;
+
+  $sql = "
+    SELECT s.id s_id,p.id p_id 
+    FROM sensors s,sensor_types t, st_parameters p
+    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."
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> bindParam(':id',$id,PDO::PARAM_STR);
+  $q -> bindParam(':type',$type,PDO::PARAM_STR);
+  $q -> bindParam(':param',$param,PDO::PARAM_STR);
+  $q -> execute();
+  $sensor = $q -> fetch(PDO::FETCH_ASSOC);
+
+  $sql = "
+    SELECT 
+      u.id stored_unit,du.id display_unit 
+    FROM 
+      sensors s,sensor_types t,st_parameters p,units u,units du
+    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
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> bindParam(':id',$sensor['s_id'],PDO::PARAM_INT);
+  $q -> bindParam(':param',$sensor['p_id'],PDO::PARAM_INT);
+  $q -> execute();
+  $units = $q -> fetch(PDO::FETCH_ASSOC);
+
+  $sql = "
+    SELECT 
+      CONCAT(subset.t,'5:00') t,UnitConv(avg(subset.y),:stored,:display) y
+    FROM (
+      SELECT 
+        substr(date_format(timestamp,'%Y-%m-%dT%H:%i'),1,15) t,value y
+      FROM 
+        sensor_values
+      WHERE 
+        sensor_id = :id and parameter_id=:param 
+        and timestamp>adddate(now(),-1)
+      ) subset 
+    GROUP BY subset.t
+    ORDER BY subset.t
+  ";
+  
+  $q = $db -> prepare( $sql );
+  $q -> bindParam(':id',$sensor['s_id'],PDO::PARAM_INT);
+  $q -> bindParam(':param',$sensor['p_id'],PDO::PARAM_INT);
+  $q -> bindParam(':stored',$units['stored_unit'],PDO::PARAM_INT);
+  $q -> bindParam(':display',$units['display_unit'],PDO::PARAM_INT);
+  $q -> execute();
+
+  return $q -> fetchAll(PDO::FETCH_ASSOC);
+
+}
+
+function getArchive($db,$year,$month,$day,$id,$type,$param) {
+
+  global $filter;
+
+  $y = intval($year);
+  $m = intval($month);
+  $d = intval($day);
+  
+  $date = strval($y).'-'.strval($m).'-'.strval($d);
+
+  $sql = "
+    SELECT s.id s_id,p.id p_id 
+    FROM sensors s,sensor_types t, st_parameters p
+    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."
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> bindParam(':id',$id,PDO::PARAM_STR);
+  $q -> bindParam(':type',$type,PDO::PARAM_STR);
+  $q -> bindParam(':param',$param,PDO::PARAM_STR);
+  $q -> execute();
+  $sensor = $q -> fetch(PDO::FETCH_ASSOC);
+
+  $sql = "
+    SELECT 
+      u.id stored_unit,du.id display_unit 
+    FROM 
+      sensors s,sensor_types t,st_parameters p,units u,units du
+    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
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> bindParam(':id',$sensor['s_id'],PDO::PARAM_INT);
+  $q -> bindParam(':param',$sensor['p_id'],PDO::PARAM_INT);
+  $q -> execute();
+  $units = $q -> fetch(PDO::FETCH_ASSOC);
+
+  $sql = "
+    SELECT 
+      CONCAT(subset.t,'5:00') t,UnitConv(avg(subset.y),:stored,:display) y
+    FROM (
+      SELECT 
+        substr(date_format(timestamp,'%Y-%m-%dT%H:%i'),1,15) t,value y
+      FROM 
+        sensor_values
+      WHERE 
+        sensor_id = :id and parameter_id=:param 
+        and timestamp>=STR_TO_DATE(:d,'%Y-%m-%d')
+        and timestamp<DATE_ADD(STR_TO_DATE(:d,'%Y-%m-%d'), interval 1 day)
+      ) subset 
+    GROUP BY subset.t
+    ORDER BY subset.t
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> bindParam(':id',$sensor['s_id'],PDO::PARAM_INT);
+  $q -> bindParam(':param',$sensor['p_id'],PDO::PARAM_INT);
+  $q -> bindParam(':d',$date,PDO::PARAM_STR);
+  $q -> bindParam(':stored',$units['stored_unit'],PDO::PARAM_INT);
+  $q -> bindParam(':display',$units['display_unit'],PDO::PARAM_INT);
+  $q -> execute();
+
+  return $q -> fetchAll(PDO::FETCH_ASSOC);
+
+}
+
+function getProps($db, $localNet) {
+
+  global $filter;
+  
+  $sql = "
+    SELECT 
+      CONCAT(s_id,'.',t.st_abbr,'.',p.st_name) sensor_id,
+      p.st_description name,
+      du.name_short unit,
+      du.prec prec,
+      pl.place_name,
+      p.st_line_color color
+    FROM 
+      sensors s, sensor_types t, st_parameters p,units u,units du,places pl
+    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 pl.idplaces=s.place_id ".$filter."
+  ";
+  
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+  $reply = [ 
+    "names" => [], 
+    "colors" => [], 
+    "units" => [], 
+    "scale" => [], 
+    "places" => [],
+    "fonts" => [ 
+      "axes" => [ "color" => "black", "size" => 16, "style" => "normal" ], 
+      "legend" => [ "color" => "black", "size" => 16, "style" => "normal" 
+  ] ] ];
+
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+    $reply["names"][$row["sensor_id"]] = $row["name"];
+    $reply["colors"][$row["sensor_id"]] = $row["color"];
+    $reply["units"][$row["sensor_id"]] = $row["unit"];
+    $reply["places"][$row["sensor_id"]] = $row["place_name"];
+    $reply["scale"][$row["sensor_id"]] = [ 0 => 1.0, 1 => $row["prec"] ];
+  }
+  
+  return $reply;
+  
+}
+
+function getState($db, $localNet) {
+
+  global $filter;
+  
+  $sql = "
+    SELECT 
+      DISTINCT 
+        s.s_id as sensor_id,
+        st.st_abbr,
+        v.sensor as sensor_int_id,
+        pl.place_name s_description,
+        p.id as param_id,
+        p.st_name as param_name,
+        p.st_description,
+        s.place_id,
+        u.id stored_unit_id,
+        du.id unit_id
+    FROM 
+      sensors_ranges v,st_parameters p,sensors s,places pl,sensor_types st,units u,units du
+    WHERE 
+      v.timestamp>addtime(now(), -43200) 
+      and s.st_id=st.id
+      and v.sensor=s.id
+      and s.st_id=st.id
+      and v.parameter=p.id  
+      and s.st_id=p.st_id
+      and p.id>=0
+      and s.place_id=pl.idplaces
+      and p.st_unit=u.id 
+      and u.unit_group=du.unit_group
+      and du.is_default=1
+      ".$filter."
+    ORDER BY
+      s_description,sensor_id,param_id
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+
+  $reply = [];
+
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+
+    $sensor_id = $row['sensor_id'];
+    $st_id = $row['st_abbr'];
+    $sensor_int_id = $row['sensor_int_id'];
+    $param_id = $row['param_name'];
+    $param_int_id = $row['param_id'];
+    $unit_id = $row['unit_id'];
+    $stored_unit_id = $row['stored_unit_id'];
+    $place_description = $row['s_description'];
+
+    $sql_last_val = "
+      SELECT UnitConv(value,".$stored_unit_id.",".$unit_id.") as val,timestamp
+      FROM
+        sensor_values
+      WHERE 
+        sensor_id = ".$sensor_int_id." and parameter_id=".$param_int_id."
+      ORDER BY
+        timestamp DESC
+      LIMIT 1
+    ";
+
+    $qv = $db -> prepare( $sql_last_val );
+    $qv -> execute();
+
+    $v_row = $qv -> fetch(PDO::FETCH_ASSOC);
+    
+    $value = $v_row["val"];
+    $timestamp = $v_row["timestamp"];
+
+    if (! array_key_exists($place_description,$reply)) {
+      $reply[$place_description] = [];
+    }
+
+    if (! array_key_exists($sensor_id,$reply[$place_description])) {
+      $reply[$place_description][$sensor_id] = [];
+    }
+    
+    if (! array_key_exists($st_id,$reply[$place_description][$sensor_id])) {
+      $reply[$place_description][$sensor_id][$st_id] = [];
+    }
+
+    $reply[$place_description][$sensor_id][$st_id][$param_id] = $value;
+    $reply[$place_description][$sensor_id]['timestamp'] = $timestamp;
+
+  }
+
+  return $reply;
+  
+}
+
+$expire = 60;
+
+if ($query == 'props') {
+
+  $reply = getProps($db, $local_net);
+  $expire = 600;
+
+} elseif ($query == 'state') {
+
+  $reply = getState($db, $local_net);
+
+} elseif (startsWith($query,'get/')) {
+
+  $sensor = explode('/',substr($query,strlen('get/')));
+  $reply = getCurrent($db,$sensor[0],$sensor[1],$sensor[2]);
+
+} elseif ($query == 'years') {
+
+  $reply = getYears($db);
+  $expire = 3600;
+
+} elseif (startsWith($query,'months/')) {
+
+  $date = explode('/',substr($query,strlen('months/')));
+  $reply = getMonths($db,$date[0]);
+  $expire = 3600;
+
+} elseif (startsWith($query,'days/')) {
+
+  $date = explode('/',substr($query,strlen('days/')));
+  $reply = getDays($db,$date[0],$date[1]);
+  $expire = 3600;
+
+} elseif (startsWith($query,'sensors/')) {
+
+  $date = explode('/',substr($query,strlen('sensors/')));
+  $reply = getSensors($db,$date[0],$date[1],$date[2]);
+  $expire = 3600;
+
+} elseif (startsWith($query,'get-archive/')) {
+
+  $path = explode('/',substr($query,strlen('get-archive/')));
+  $reply = getArchive($db,$path[0],$path[1],$path[2],$path[3],$path[4],$path[5]);
+  $expire = 14400;
+
+}
+
+if ($reply) {
+
+  $redis->set('meteo-'.$hash, serialize($reply));
+  $redis->expire('meteo-'.$hash, $expire);
+  
+  print(json_encode($reply));
+
+}
+
+?>