From afdf338bb671cf6bdb1bd836fd68cf5ee30b614c Mon Sep 17 00:00:00 2001
From: Roman Bazalevskiy <rvb@rvb.name>
Date: Thu, 14 Jan 2021 09:43:54 +0300
Subject: [PATCH] Weather panel backend call.

---
 web/cgi.php | 283 ++++++++++++++++++++++++++++++++++++++++++----------
 1 file changed, 230 insertions(+), 53 deletions(-)

diff --git a/web/cgi.php b/web/cgi.php
index facdc70..bfe4263 100644
--- a/web/cgi.php
+++ b/web/cgi.php
@@ -134,6 +134,14 @@ function getSensors($db,$year,$month,$day) {
     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')
+    UNION
+    SELECT DISTINCT
+      CONCAT(s_id,'.',t.st_abbr,'.*') id
+    FROM
+      sensors_ranges r, sensors s, sensor_types t
+    WHERE 
+      r.sensor=s.id and s.st_id=t.id and t.st_common_description is not null ".$filter."
+      and r.day=STR_TO_DATE('".strval($y)."-".strval($m)."-".strval($d)."','%Y-%m-%d')
   ";
 
   $q = $db -> prepare( $sql );
@@ -149,23 +157,10 @@ function getSensors($db,$year,$month,$day) {
 
 }
 
-function getCurrent($db,$id,$type,$param) {
+function getCurrentValues($db,$s_id,$p_id) {
 
   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 
@@ -175,60 +170,90 @@ function getCurrent($db,$id,$type,$param) {
   ";
 
   $q = $db -> prepare( $sql );
-  $q -> bindParam(':id',$sensor['s_id'],PDO::PARAM_INT);
-  $q -> bindParam(':param',$sensor['p_id'],PDO::PARAM_INT);
+  $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
+  $q -> bindParam(':param',$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
-  ";
+        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(':id',$s_id,PDO::PARAM_INT);
+  $q -> bindParam(':param',$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) {
+function getCurrent($db,$id,$type,$param) {
 
   global $filter;
 
-  $y = intval($year);
-  $m = intval($month);
-  $d = intval($day);
+  if ($param == "*") {
+
+    $sql = "
+      SELECT s.id s_id,p.id p_id, p.st_name p_name 
+      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 ".$filter."
+    ";
+
+    $q = $db -> prepare( $sql );
+    $q -> bindParam(':id',$id,PDO::PARAM_STR);
+    $q -> bindParam(':type',$type,PDO::PARAM_STR);
+    $q -> execute();
+    $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
+
+    $results = array();
+
+    foreach ($sensors as $sensor) {
+
+      $results[$sensor['p_name']] = getCurrentValues($db,$sensor['s_id'],$sensor['p_id']);
+    
+    }
+
+    return $results;
   
-  $date = strval($y).'-'.strval($m).'-'.strval($d);
+  } else {
 
-  $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."
-  ";
+    $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);
+    $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);
+
+    return getCurrentValues($db,$sensor['s_id'],$sensor['p_id']);
+
+  }
+
+}
+
+function getArchiveValues($db,$year,$month,$day,$s_id,$p_id) {
+
+  global $filter;
 
   $sql = "
     SELECT 
@@ -239,8 +264,8 @@ function getArchive($db,$year,$month,$day,$id,$type,$param) {
   ";
 
   $q = $db -> prepare( $sql );
-  $q -> bindParam(':id',$sensor['s_id'],PDO::PARAM_INT);
-  $q -> bindParam(':param',$sensor['p_id'],PDO::PARAM_INT);
+  $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
+  $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
   $q -> execute();
   $units = $q -> fetch(PDO::FETCH_ASSOC);
 
@@ -261,9 +286,11 @@ function getArchive($db,$year,$month,$day,$id,$type,$param) {
     ORDER BY subset.t
   ";
 
+  $date = $year.'-'.$month.'-'.$day;
+
   $q = $db -> prepare( $sql );
-  $q -> bindParam(':id',$sensor['s_id'],PDO::PARAM_INT);
-  $q -> bindParam(':param',$sensor['p_id'],PDO::PARAM_INT);
+  $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
+  $q -> bindParam(':param',$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);
@@ -273,6 +300,61 @@ function getArchive($db,$year,$month,$day,$id,$type,$param) {
 
 }
 
+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);
+
+  if ($param == "*") {
+
+    $sql = "
+      SELECT s.id s_id,p.id p_id, p.st_name p_name 
+      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 ".$filter."
+    ";
+
+    $q = $db -> prepare( $sql );
+    $q -> bindParam(':id',$id,PDO::PARAM_STR);
+    $q -> bindParam(':type',$type,PDO::PARAM_STR);
+    $q -> execute();
+    $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
+
+    $results = array();
+
+    foreach ($sensors as $sensor) {
+
+      $result[$sensor['p_name']] = getArchiveValues($db,$year,$month,$day,$sensor['s_id'],$sensor['p_id']);
+
+    }
+    
+    return $result;
+    
+  } else {
+
+    $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);
+
+    return getArchiveValues($db,$year,$month,$day,$sensor['s_id'],$sensor['p_id']);
+
+  }
+
+}
+
 function getProps($db, $localNet) {
 
   global $filter;
@@ -311,6 +393,24 @@ function getProps($db, $localNet) {
     $reply["places"][$row["sensor_id"]] = $row["place_name"];
     $reply["scale"][$row["sensor_id"]] = [ 0 => 1.0, 1 => $row["prec"] ];
   }
+
+  $sql = "
+    SELECT DISTINCT
+      CONCAT(s_id,'.',t.st_abbr,'.*') sensor_id,
+      t.st_common_description name,
+      pl.place_name
+    FROM
+      sensors s, sensor_types t, places pl
+    WHERE 
+      pl.idplaces=s.place_id and s.st_id=t.id and t.st_common_description is not null ".$filter."
+  ";
+
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+    $reply["names"][$row["sensor_id"]] = $row["name"];
+    $reply["places"][$row["sensor_id"]] = $row["place_name"];
+  }
   
   return $reply;
   
@@ -408,17 +508,94 @@ function getState($db, $localNet) {
   
 }
 
+function getPanel($db) {
+
+  $sql = "
+    SELECT 
+      DISTINCT 
+        v.sensor as sensor_int_id,
+        p.id as param_id,
+        p.st_name as param_name,
+        p.st_description,
+        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
+      and (s.s_id,p.st_name) in (select sensor_id,param_name from panel_sensors)
+    ORDER BY
+      param_id
+    ";
+
+  $q = $db -> prepare( $sql );
+  $q -> execute();
+
+  $reply = [];
+
+  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
+
+    $sensor_id = $row['sensor_id'];
+    $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'];
+
+    $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."
+        and timestamp > NOW() - interval 30 minute
+      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"];
+
+    if ($value && $value != "null") {
+      $reply[strtoupper($param_id)] = $value;
+    }
+
+  }
+
+  return $reply;
+  
+}
+
 $expire = 60;
 
 if ($query == 'props') {
 
   $reply = getProps($db, $local_net);
-  $expire = 600;
+  $expire = 60;
 
 } elseif ($query == 'state') {
 
   $reply = getState($db, $local_net);
 
+} elseif ($query == 'panel') {
+
+  $reply = getPanel($db);
+
 } elseif (startsWith($query,'get/')) {
 
   $sensor = explode('/',substr($query,strlen('get/')));
-- 
2.34.1