From: Roman Bazalevskiy Date: Thu, 14 Jan 2021 06:43:54 +0000 (+0300) Subject: Weather panel backend call. X-Git-Url: https://git.rvb.name/weathermon.git/commitdiff_plain/afdf338bb671cf6bdb1bd836fd68cf5ee30b614c Weather panel backend call. --- 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/')));