<?php
/*******************************************
/* Programm showgraph.php4
/* controls input from menugraf
/* and displays the data
/* (c) Copyright 2002, Jens Bierkandt
/* e-mail: jens@bierkandt.org
/* Entstanden im Rahmen meiner Diplomarbeit
/*******************************************
*/
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . "
GMT");
// always modified
header("Cache-Control: no-store, no-cache,
must-revalidate");
// HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: public");
// HTTP/1.0
set_time_limit(600);
error_reporting(63); // 0 = none, 63 = full
include("./config.php4");
// Check variables and initialize
if (!isset($table))
Header("Location: index.php4"); // Forward user to main
page to reselect table
$yestdate = time()-60*60*24;
$yestyear = date("Y", $yestdate);
$yestmonth = date("m", $yestdate);
$yestday = date("d", $yestdate);
if (!isset($average)) $average = 10;
if ($average > 99999) $average = 99999;
if (!isset($timedef)) {
$from_time = $yestyear.$yestmonth.$yestday."000000";
$to_time = $yestyear.$yestmonth.$yestday."235959";
} else {
if ($timedef == "yesterday") {
$from_time = $yestyear.$yestmonth.$yestday."000000";
$to_time = $yestyear.$yestmonth.$yestday."235959";
}
if ($timedef == "since") {
$fromdate = time()-60*60*24*$days;
$fromyear = date("Y", $fromdate);
$frommonth = date("m", $fromdate);
$fromday = date("d", $fromdate);
$from_time = $fromyear.$frommonth.$fromday."000000";
$to_time = $yestyear.$yestmonth.$yestday."235959";
}
if ($timedef == "diff") {
if ($from_d < 1 || $from_d > 31
|| $from_m < 1 || $from_m > 12
|| $from_y < 0 || $from_y > 99
|| $from_h < 0 || $from_h > 24
|| $from_min < 0 || $from_min > 59
|| $from_s < 0 || $from_s > 59
|| $to_d < 1 || $to_d > 31
|| $to_m < 1 || $to_m > 12
|| $to_y < 0 || $to_y > 99
|| $to_h < 0 || $to_h > 24
|| $to_min < 0 || $to_min > 59
|| $to_s < 0 || $to_s > 59
) {
echo "Falsche Datumeingabe";
return 0;
} else {
$unix_from_time = mktime($from_h, $from_min,
$from_s, $from_m, $from_d, $from_y);
$unix_to_time = mktime($to_h, $to_min, $to_s,
$to_m, $to_d, $to_y);
$from_time = date("YmdHis", $unix_from_time);
$to_time = date("YmdHis", $unix_to_time);
}
}
if ($scale == "man" AND (!isset($scale_min) OR
!isset($scale_max))) $scale = "auto";
};
// Initialize connection to DB
$db = mysql_connect($hostname, $username, $password);
if (!$db) {
return;
}
mysql_select_db($database, $db);
echo mysql_error();
// To supress some overlayed high waves, we average the
values by given number and put it in a temp table
mysql_query("DROP TABLE tmp", $db);
// Create temp table
$result = "CREATE TABLE tmp (
id bigint(20) unsigned NOT NULL,
timestamp datetime NOT NULL default '0000-00-00 00:00:00',
mictime double unsigned NOT NULL default '0',";
for ($a = 0; $a < sizeof($sensor); $a++) {
$result .= "sensor".$sensor[$a]." FLOAT(3,2) NULL ,";
}
$result = substr($result, 0, strlen($result)-1);
$result .= ") TYPE=MyISAM COMMENT='Temp data';";
$result = mysql_query($result, $db);
echo mysql_error();
$t = time();
// average the data I
/*
mysql_query("LOCK TABLES `tmp` WRITE, `$table`
READ;",$db);
echo mysql_error();
for ($i=0;$i<$average;$i++) {
$result="INSERT IGNORE INTO `tmp` SELECT `id`,
`timestamp`, `mictime`, ";
for ($a=0;$a<sizeof($sensor);$a++) {
$result.="AVG(`sensor".$sensor[$a]."`),";
}
$result=substr($result,0,strlen($result)-1);
$result.=" FROM `$table` WHERE timestamp>=$from_time AND
timestamp<=$to_time GROUP BY
((id-$i)-((id-$i)%$average))/$average;";
$result=mysql_query($result,$db);
echo mysql_error();
}
mysql_query("UNLOCK TABLES;",$db);
//echo time()-$t;
*/
// Select data II
$result = "SELECT id, timestamp, mictime, ";
for ($b = 0; $b < sizeof($sensor); $b++) {
$result .= "sensor".$sensor[$b]." ,";
}
$result = substr($result, 0, strlen($result)-1);
$result .= "FROM `$table` WHERE timestamp>=$from_time AND
timestamp<=$to_time ORDER BY id";
// initialize values
for ($a = 0; $a < $average; $a++) {
for ($b = 0; $b < sizeof($sensor); $b++) {
$arr[$b][$a] = 0;
}
}
for ($b = 0; $b < sizeof($sensor); $b++) {
$last_avg[$b] = 0;
}
$first_avg = 0;
$query = mysql_query($result, $db);
echo mysql_error($db);
$fp_out = fopen("MySQL_tmp.txt", "w+");
while($row = mysql_fetch_row($query)) {
$outstr = $row[0].";".$row[1].";".$row[2];
for ($b = 0; $b < sizeof($sensor); $b++) {
$arr[$b][$first_avg] = $row[$b+3];
$last_avg[$b] += $row[$b+3];
$outstr .= ";".($last_avg[$b]/$average);
}
$first_avg++;
if ($first_avg == $average) $first_avg = 0;
for ($b = 0; $b < sizeof($sensor); $b++) {
$last_avg[$b] -= $arr[$b][$first_avg];
}
fwrite($fp_out, $outstr."\n");
}
fclose($fp_out);
mysql_query("LOAD DATA LOCAL INFILE 'MySQL_tmp.txt'
IGNORE INTO TABLE `tmp` FIELDS TERMINATED BY ';' LINES
TERMINATED BY '\n' ", $db);
//echo time()-$t;
$result = mysql_query("SELECT MIN(id), MAX(id) FROM
tmp;", $db);
$row = mysql_fetch_row($result);
if ($row[0] == "") {
// No data available
echo "<b>Fehler:</b> Keine Daten im Zeitraum mit
angegebenen Glättungsfaktor";
return;
}
$min_id = $row[0];
$max_id = $row[1];
// We only use the values, that have used the average
completely
mysql_query("DELETE FROM tmp where
id<".($min_id+$average-1).";", $db);
mysql_query("DELETE FROM tmp where
id>".($max_id-$average+1).";", $db);
$result = mysql_query("SELECT MIN(id) FROM tmp;", $db);
$row = mysql_fetch_row($result);
if ($row[0] == "") {
// No data available
echo "<b>Fehler:</b> Keine Daten im Zeitraum durch
angegebenen Glättungsfaktor";
return;
}
if (isset($size)) {
if ($size && strpos($size, "x")) {
$arr = explode('x', $size);
$width_graph = $arr[0];
$height_graph = $arr[1];
};
};
if (!(isset($width_graph) || isset($height_graph))) {
$width_graph = 600;
$height_graph = 400;
};
if (!isset($diff_min)) $diff_min = 5;
if (!isset($diff_max)) $diff_max = 30;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN">
<html>
<head>
<title>Anzeige der Messdaten</title>
</head>
<body>
<?php
if (isset($type)) {
if ($type == "temp") {
echo "<p><img
src=\"graph.php4?from_time=$from_time&to_time=$to_t
ime&width_graph=$width_graph&height_graph=$height_gr
aph&table=$table&";
for ($i = 0; $i < sizeof($sensor); $i++) {
echo "sensor[]=$sensor[$i]&";
}
if (isset($max)) {
for ($i = 0; $i < sizeof($max); $i++) {
echo "sendmax[]=$max[$i]&";
}
}
if (isset($min)) {
for ($i = 0; $i < sizeof($min); $i++) {
echo "sendmin[]=$min[$i]&";
}
}
if ($scale == "man") echo
"scale_min=$scale_min&scale_max=$scale_max&";
echo
"scale=$scale&computer=$computer&average=$average;
\" alt=\"Temperaturkurve\"></p>";
}
if ($type == "diff_count") {
include("./diff_count.php4");
}
if ($type == "count") {
echo "<p><img
src=\"count.php4?from_time=$from_time&to_time=$to_t
ime&width_graph=$width_graph&height_graph=$height_gr
aph&table=$table&";
for ($i = 0; $i < sizeof($sensor); $i++) {
echo "sensor[]=$sensor[$i]&";
}
echo "computer=$computer&average=$average
\" alt=\"Häufigkeit\"></p>";
}
}
if (isset($stat)) {
echo "
<table border=1><tr><td>
<p><h2>Statistik</h2></p>
</td></tr>";
// display exact time selected
$result = mysql_query("SELECT MIN(timestamp),
MAX(timestamp) FROM `tmp` WHERE
timestamp>=$from_time AND timestamp<=$to_time", $db);
$row = mysql_fetch_row($result);
$from_time1 = $row[0];
$to_time1 = $row[1];
echo "<tr><td>
Erster Zeitstempel: ".$from_time1."
</tr></td>";
echo "<tr><td>
Letzter Zeitstempel: ".$to_time1."
</td></tr>";
// Get number of values
$result = mysql_query("SELECT COUNT(*) FROM `tmp` WHERE
timestamp>=$from_time AND timestamp<=$to_time", $db);
$row = mysql_fetch_row($result);
$rownumber = $row[0];
echo "<tr><td>
Anzahl der Werte pro Sensor: $rownumber
</td> </tr>";
echo "<tr><td>Glättung über $average Werte</td></tr>";
echo "</table>";
echo "<table border=1>";
echo "<tr><td></td>";
for ($a = 0; $a < sizeof($sensor); $a++) {
echo "<td width=70>Sensor $sensor[$a]</td>";
}
echo "</tr><tr>";
echo "<td>Minimalwert</td>";
for ($a = 0; $a < sizeof($sensor); $a++) {
$result = mysql_query("SELECT
MIN(sensor".$sensor[$a].")
FROM `tmp` WHERE timestamp>=$from_time AND
timestamp<=$to_time", $db);
$row = mysql_fetch_row($result);
$min_sensor = $row[0];
echo "<td>
".$min_sensor."
</td>";
}
echo "</tr><tr>";
echo "<td>Maximalwert</td>";
for ($a = 0; $a < sizeof($sensor); $a++) {
$result = mysql_query("SELECT
MAX(sensor".$sensor[$a].")
FROM `tmp` WHERE timestamp>=$from_time AND
timestamp<=$to_time", $db);
$row = mysql_fetch_row($result);
$min_sensor = $row[0];
echo "<td>
".$min_sensor."
</td>";
}
echo "</tr><tr>";
echo "<td>Durchschnitt</td>";
for ($a = 0; $a < sizeof($sensor); $a++) {
$result = mysql_query("SELECT
AVG(sensor".$sensor[$a].")
FROM `tmp` WHERE timestamp>=$from_time AND
timestamp<=$to_time", $db);
$row = mysql_fetch_row($result);
$min_sensor = $row[0];
echo "<td>
".$min_sensor."
</td>";
}
echo "</tr>";
echo "</table>";
}
mysql_close($db);
?>
</body>
</html>