next up previous contents
Next: graph.php4 Up: Quellcode Previous: menugraf.php4   Contents


showgraf.php4


<?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>



Jens Bierkandt