connect_error) { die("Connection failed: " . $conn->connect_error); } $DownloadDataXML = isset($_GET["DownloadDataXML"]) ? $_GET["DownloadDataXML"] : ""; $DownloadDataCSV = isset($_GET["DownloadDataCSV"]) ? $_GET["DownloadDataCSV"] : ""; $SensorID = isset($_GET["SensorID"]) ? $_GET["SensorID"] : ""; $month = isset($_GET["month"]) ? $_GET["month"] : ""; $day = isset($_GET["day"]) ? $_GET["day"] : ""; $year = isset($_GET["year"]) ? $_GET["year"] : ""; $hour = isset($_GET["hour"]) ? $_GET["hour"] : ""; $minute = isset($_GET["minute"]) ? $_GET["minute"] : ""; $name = $SensorID; $sql = "SELECT * FROM sensors_info WHERE sensor_id = '".$SensorID."'"; $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { if ($row["sensor_name"] != "") { $name = $row["sensor_name"]; } if ($row["sensor_type"] != "") { $sensor_type = $row["sensor_type"]; } } } $temp_type = "Fahrenheit"; $sql = "SELECT value FROM sensors_system WHERE name = 'server_temperature'"; $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { if ($row["value"] == "Celsius") { $temp_type = "Celsius"; } else { $temp_type = "Fahrenheit"; } } } # rrdtool fetch /opt/sensiplicity/rrds/28-00000624113a.rrd AVERAGE -s `date -d '2015-04-19 15:00' +%s` -e `date -d '2015-04-19 18:00' +%s` $start_date = $year.'-'.$month.'-'.$day.' '.$hour.':'.$minute.''; $start = exec("date -d '".$start_date."' +%s"); #$fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'.rrd AVERAGE -s '.$start.' -e '.$end.''; $i = 0; $sensor_data = array(); $csv_data = "\"Sensor ID\",\"Type\",\"Date\",\"Data (".$temp_type.")\"\n"; $xml_data = "\n\n\n"; if ($sensor_type == "Soil Sensor") { # atemp $type = "atemp"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ #if ($i < 2) { #$i++; #continue; #} list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # humidity $type = "humidity"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # light $type = "light"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # moist1 $type = "moist1"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # moist2 $type = "moist2"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # moist3 $type = "moist3"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # moist4 $type = "moist4"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # stemp1 $type = "stemp1"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # stemp2 $type = "stemp2"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # stemp3 $type = "stemp3"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } # stemp4 $type = "stemp4"; $i = 0; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'_'.$type.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$type.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } } else { $type = "temp"; $fetch_rrd = '/usr/bin/rrdtool fetch /opt/sensiplicity/rrds/'.$SensorID.'.rrd AVERAGE -s '.$start.' | grep ":"'; $sensor_success = exec($fetch_rrd, $sensor_data); array_pop($sensor_data); foreach ( $sensor_data as $sensor_temp ){ list($time, $temp) = explode(': ',$sensor_temp); $temp = str_replace("nan","sensor off",$temp); $real_time = exec("date -d '@".$time."'"); $csv_data .= '"'.$name.'","'.$real_time.'",'.$temp."\n"; $xml_data .= "\n"; } } $xml_data .= "\n"; if ($DownloadDataCSV != "") { //OUPUT HEADERS header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Cache-Control: private",false); header("Content-Type: application/octet-stream"); header("Content-Disposition: attachment; filename=\"$name.csv\";" ); header("Content-Transfer-Encoding: binary"); echo($csv_data); } if ($DownloadDataXML != "") { //OUPUT HEADERS header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Cache-Control: private",false); header("Content-Type: application/octet-stream"); header("Content-Disposition: attachment; filename=\"$name.xml\";" ); header("Content-Transfer-Encoding: binary"); echo($xml_data); } ?>