='$first' AND thedate <= '$last' GROUP BY sensor,thedate ORDER BY thedate"; } else { if ( preg_match("/^\d\d\d\d-\d\d-\d\d$/",$first) ) { # Show one day $query = "SELECT hour,sensor,temperature FROM temperature WHERE thedate='$first' ORDER BY hour"; } else if ( preg_match("/^\d\d\d\d-\d\d$/",$first) ) { # Show one month $query = "SELECT thedate,sensor,AVG(temperature) FROM temperature WHERE thedate LIKE '$first%' GROUP BY sensor,thedate ORDER BY thedate"; } else if ( preg_match("/^\d\d\d\d$/",$first) ) { # Show one year $query = "SELECT thedate,sensor,AVG(temperature) FROM temperature WHERE thedate LIKE '$first%' GROUP BY sensor,thedate ORDER BY thedate"; } } $res = getArray(mysql_query($query)); $header = "Temperaturer ".$first; if ( $last != "" ) $header .= " - $last"; echo displayPlot($header,$res,$links); } else { # get list of dates with observations $query = "SELECT DISTINCT thedate FROM temperature ORDER BY thedate"; $res = mysql_query($query); while ( $tmp = mysql_fetch_row($res) ) $dates[count($dates)] = $tmp[0]; echo get_select_table($dates); } ######################################################################################## # # # Data handling functions # # # ######################################################################################## function update_db($date,$hour,$sensor,$temp) { $query = "DELETE FROM temperature WHERE thedate='$date' AND hour='$hour' AND sensor='$sensor'"; echo $query; mysql_query($query); if ( $temp != "NULL" ) { $query = "INSERT INTO temperature (thedate,hour,sensor,temperature) VALUES ('$date','$hour','$sensor','$temp')"; echo $query; mysql_query($query); } } function getArray($mysql_res) { # expects (time,sensor,temperature) tuples where time is hour or date # returns array[sensor][time] with termerature values while ( $row = mysql_fetch_row($mysql_res) ) { $toReturn[$row[1]][$row[0]] = $row[2]; } return $toReturn; } ######################################################################################## # # # Plot functions # # # ######################################################################################## function makePlot($title,$temps,$outfile) { # expects the format temps[sensor][time] $fp = fopen("/tmp/gnuData.txt","w"); $keys = array_keys($temps[0]); $ymin = 0; for ( $i = 0; $i < count($keys); $i++ ) { fwrite($fp,$keys[$i]); for ( $j = 0; $j < count($temps); $j++ ) { if ( $temps[$j][$keys[$i]] != "" ) { fwrite($fp,"\t".$temps[$j][$keys[$i]]); if ( $temps[$j][$keys[$i]] < $ymin ) $ymin = $temps[$j][$keys[$i]]; $has_values[$j] = 1; } else fwrite($fp,"\t."); } fwrite($fp,"\n"); } fclose($fp); $xmin = min($keys); $xmax = max($keys); $ymin = floor($ymin); # Determine values on x-axis based upon the time data if ( preg_match("/^\d{4}-\d\d-\d\d$/",$keys[0]) ) { $timefmt = "%Y-%m-%d"; if ( timediff($xmin,$xmax) == 60*60*24*6 ) { $xformat = "%a"; $xtic = "auto"; } else { $xformat = "%d"; $xtic = "(\"".implode("\",\"",$keys)."\")"; } } else if ( preg_match("/^\d{4}-\d\d$/",$keys[0]) ) { echo "Dette sker aldrig!"; $xformat = "%m"; $timefmt = "%Y-%m-%d"; $xtic = "(\"".implode("\",\"",$keys)."\")"; } else if ( preg_match("/^\d$/",$keys[0]) || preg_match("/^\d\d$/",$keys[0]) ) { $xformat = "%H"; $timefmt = "%H"; $xtic = "auto"; } $gnuCommand = "set terminal png\n"; $gnuCommand .= "set grid\n"; $gnuCommand .= "set style line 1\n"; $gnuCommand .= "set title \"$title\"\n"; $gnuCommand .= "set key bottom right\n"; $gnuCommand .= "set key box\n"; $gnuCommand .= "set xdata time\n"; $gnuCommand .= "set timefmt \"$timefmt\"\n"; $gnuCommand .= "set format x \"$xformat\"\n"; $gnuCommand .= "set xrange [\"$xmin\":\"$xmax\"]\n"; $gnuCommand .= "set yrange [$ymin:*]\n"; $gnuCommand .= "set xtics $xtic\n"; $gnuCommand .= "set ytics auto\n"; $gnuCommand .= "set pointsize 1.5\n"; $gnuCommand .= "set output \"$outfile\"\n"; $gnuCommand .= "plot "; for ( $i = 0; $i < count($temps); $i++ ) { if ( $has_values[$i] ) { if ( $already_plotting ) $gnuCommand .= ", "; if ( $i == 0 ) { $gnuCommand .= "\"/tmp/gnuData.txt\" using 1:".($i+2)." smooth unique title \"Inde\""; } else if ( $i == 1 ) { $gnuCommand .= "\"/tmp/gnuData.txt\" using 1:".($i+2)." smooth unique title \"Ude\""; } else { $gnuCommand .= "\"/tmp/gnuData.txt\" using 1:".($i+2)." smooth unique title \"Sensor $i\""; } $already_plotting = 1; } } $gnuCommand .= "\n"; $gnuCommand .= "set output\n"; $fp = fopen("/tmp/gnuCommand.txt","w"); fwrite($fp,$gnuCommand); fclose($fp); $dir = preg_replace("/\/[^\/]*$/","",__file__); shell_exec("/usr/bin/gnuplot /tmp/gnuCommand.txt"); } function displayPlot($header,$temps,$links) { #$toReturn = "

$header

\n"; $toReturn = ""; if ( $links['previous'] != "" ) { $toReturn .= "Forrige \n"; } $toReturn .= "Op \n"; if ( $links['next'] != "" ) { $toReturn .= "Næste \n"; } $toReturn .= "
\n"; makePlot($header,$temps,"plot.png"); $toReturn .= "\n"; $toReturn .= getTable($temps); return $toReturn; } function getTable($temps) { $toReturn .= "\n"; $keys = array_keys($temps[0]); $toReturn .= "\n"; $toReturn .= "\n"; for ( $i = 0; $i < count($keys); $i++ ) { $toReturn .= "\n"; } $toReturn .= "\n"; for ( $i = 0; $i < count($temps); $i++ ) { $keys = array_keys($temps[$i]); for ( $j = 0; $j < count($keys); $j++ ) { if ( $temps[$i][$keys[$j]] != "" ) { $has_values[$i] = 1; break; } } if ( $has_values[$i] ) { $toReturn .= ""; for ( $j = 0; $j < count($keys); $j++ ) { $toReturn .= ""; } $toReturn .= "\n"; } } $toReturn .= "
Sensor".$keys[$i]."
$i".$temps[$i][$keys[$j]]."
\n"; return $toReturn; } function timediff($date1,$date2) { preg_match("/(\d\d\d\d)-(\d\d)-(\d\d)/",$date1,$match); $y1 = $match[1]; $m1 = $match[2]; $d1 = $match[3]; preg_match("/(\d\d\d\d)-(\d\d)-(\d\d)/",$date2,$match); $y2 = $match[1]; $m2 = $match[2]; $d2 = $match[3]; return mktime(0,0,0,$m2,$d2,$y2) - mktime(0,0,0,$m1,$d1,$y1); } function get_select_table($dates) { $toReturn = "\n"; $toReturn .= "\n"; $toReturn .= "\n"; $toReturn .= "\n"; $toReturn .= "\n"; $toReturn .= "\n"; $toReturn .= "\n\n"; # Choose day $toReturn .= "\n"; # Choose week $weeks = getWeeks($dates); $toReturn .= "\n"; # Choose month $months = getMonths($dates); $toReturn .= "\n"; # Choose year $years = getYears($dates); $toReturn .= "\n"; $toReturn .= "\n"; $toReturn .= "
DagUgeMånedÅr
\n
\n\n
\n		\n
\n
\n
\n
\n
\n
\n"; return $toReturn; } function getWeeks($dates) { $toReturn['first'][0] = $dates[0]; preg_match("/^(\d\d\d\d)-(\d\d)-(\d\d)$/",$dates[0],$matches); $toReturn['week_number'][0] = date("W",mktime(0,0,0,$matches[2],$matches[3],$matches[1])); for ( $i = 0; $i < count($dates); $i++ ) { preg_match("/^(\d\d\d\d)-(\d\d)-(\d\d)$/",$dates[$i],$matches); if ( date("w",mktime(0,0,0,$matches[2],$matches[3],$matches[1])) == 1 ) { $toReturn['first'][count($toReturn['first'])] = $dates[$i]; $toReturn['week_number'][count($toReturn['week_number'])] = date("W",mktime(0,0,0,$matches[2],$matches[3],$matches[1])); if ( $i > 0 ) { $toReturn['last'][count($toReturn['last'])] = $dates[$i-1]; } } } $toReturn['last'][count($toReturn['last'])] = $dates[count($dates)-1]; return $toReturn; } function getMonths($dates) { for ( $i = 0; $i < count($dates); $i++ ) { $tmp = substr($dates[$i],0,7); if ( $tmp != $toReturn[count($toReturn)-1] ) $toReturn[count($toReturn)] = $tmp; } return $toReturn; } function getYears($dates) { for ( $i = 0; $i < count($dates); $i++ ) { $tmp = substr($dates[$i],0,4); if ( $tmp != $toReturn[count($toReturn)-1] ) $toReturn[count($toReturn)] = $tmp; } return $toReturn; } function getPreviousNext($first,$last="") { if ( $last != "" ) { preg_match("/(\d\d\d\d)-(\d\d)-(\d\d)/",$first,$match); $query = "SELECT MIN(thedate) FROM temperature WHERE thedate>='".date("Y-m-d",mktime(0,0,0,$match[2],$match[3],$match[1])-7*24*60*60)."'"; $res = mysql_query($query); $row = mysql_fetch_row($res); if ( $row[0] < $first ) { $toReturn['previous'] = $row[0].":".date("Y-m-d",mktime(0,0,0,$match[2],$match[3],$match[1])-24*60*60); } else { $toReturn['previous'] = NULL; } preg_match("/(\d\d\d\d)-(\d\d)-(\d\d)/",$last,$match); $query = "SELECT MAX(thedate) FROM temperature WHERE thedate<='".date("Y-m-d",mktime(0,0,0,$match[2],$match[3],$match[1])+7*24*60*60)."'"; $res = mysql_query($query); $row = mysql_fetch_row($res); if ( $row[0] > $last ) { $toReturn['next'] = date("Y-m-d",mktime(0,0,0,$match[2],$match[3],$match[1])+24*60*60).":".$row[0]; } else { $toReturn['next'] = NULL; } } else { $query = "SELECT MAX(thedate) FROM temperature WHERE LEFT(thedate,".strlen($first).")<'$first'"; $res = mysql_query($query); $row = mysql_fetch_row($res); $toReturn['previous'] = substr($row[0],0,strlen($first)); $query = "SELECT MIN(thedate) FROM temperature WHERE LEFT(thedate,".strlen($first).")>'$first'"; $res = mysql_query($query); $row = mysql_fetch_row($res); $toReturn['next'] = substr($row[0],0,strlen($first)); } return $toReturn; } ?> Sommerhustemperaturer