+<?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));
+
+}
+
+?>
projects / weathermon.git / commitdiff
