Mercurial > ~darius > hgwebdir.cgi > wh1080

view web/db2.php @ 8:e3d2b5500b53 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Missed part of earlier commit to only print the query if -vv is used as well as compiling in the mysql stuff (now that I know -n DTRT)
author Daniel O'Connor <darius@dons.net.au>
date Thu, 11 Feb 2010 12:48:57 +1030
parents 9dab44dcb331
children
line wrap: on
line source

<!-- for Emacs, this is a -*- mode: html-fill; coding: utf-8 -*- document -->
<!-- $Id: db2.php,v 1.1 2009/12/23 23:48:03 grog Exp $ -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<?php
{
  $title = "Dereel weather observations";
  $subtitle = "";
  include "header.php";
  include "weathergraph.php";
  $id = '$Id: db2.php,v 1.1 2009/12/23 23:48:03 grog Exp $';
}
?>

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <?php pagetitle0 ($title); ?>
    <meta http-equiv="refresh" content="900" ;="">
  </head>

  <body>
    <?php pageheader0 ($title); ?>

    <div align="justify">
      <p>
	This is an experimental page that I'm working on as part of my weather reporting software.
	It'll grow over time.  In the meantime, you can get more complete version of this
	information from the <?php href
	("http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IVICTORI124",
	"Wunderground page"); ?> for this station.
      </p>

<?php

{
  $doplot = "/home/grog/src/weather/WH-1080/plots/doplots";
  $weatherdir = "/home/grog/public_html/weather";
  $me = basename ($_SERVER ["SCRIPT_FILENAME"]);

  if (array_key_exists ("date", $_GET))
  {
    $mydate = validdate ($_GET ["date"], "date");
    if (is_array ($mydate))                       /* valid date */
      $startdate = formatdate ("Y-m-d", $mydate);
    else
    {
      print <<< EOS
        <p>
        <font color="red">Invalid date: $mydate.  Using today's date</font>
        </p>
EOS;
      $startdate = date ("Y-m-d");
    }
  }
  else
  {
    $mydate = getdate ();
    $startdate = date ("Y-m-d");
  }

  $oneday = 1;      /* Readings for a single day until proven otherwise */
  if (array_key_exists ("enddate", $_GET))
  {
    $myenddate = validdate ($_GET ["enddate"], "date");
    if (is_array ($myenddate))                       /* valid date */
      {
      $oneday = 0;                  /* Multidate XXX check more carefully */
      $enddate = formatdate ("Y-m-d", $myenddate);
      }
    else
    {
      $enddate = date ("Y-m-d", addsecs ($mydate,  86400));
      print <<< EOS
        <p>
        <font color="red">Invalid date: $myenddate.  Using $enddate</font>
        </p>
EOS;
    }
  }
  else
  {
    $myenddate = addsecs ($mydate,  86400);
    $enddate = formatdate ("Y-m-d", $myenddate);
  }

  /*
   * If today, get current readings.  In this case, we ignore enddate, since it
   * can't mean anything. */
  $istoday = $startdate == date ("Y-m-d");

  /* Environment to check for graphs */
  $yesterday = formatdate ("Ymd", (addsecs ($mydate, -86400)));
  $tomorrow = formatdate ("Ymd", (addsecs ($mydate, 86400)));


  print <<< EOS

     <!-- Select new date -->
    <table>
      <tr>
	<td align="left">
          <form action="$me" method="get">
	    <input type="submit" value="Previous day"/>
  	    <input size="20" maxlength="20" type="hidden" name="date" value="$yesterday"/>
          </form>
	</td>

	<td>
	  <form action="$me" method="get">
	    <table summary="Parameter input" cellspacing="2" border="0">
              <tr>
		<td>
		  <input type="submit" value="New date:"/>
		</td>

                <!-- value -->
		<td>
  		  <input size="20" maxlength="20" type="text" name="date" value="$startdate" />
		</td>
              </tr>
	    </table>
	  </form>
	</td>
EOS;

    if (! $istoday)
      print <<< EOS
	<td align="right">
           <form action="$me" method="get">
	         <input type="submit" value="Next day"/>
  	         <input size="20" maxlength="20" type="hidden" name="date" value="$tomorrow"/>
           </form>
	</td>

	<td align="right">
           <form action="$me" method="get">
	         <input type="submit" value="Today"/>
           </form>
	</td>

EOS;
  print <<< EOS

      </tr>
    </table>

EOS;

  /* Set up database stuff */
  /* XXX This stuff should come from config */
  require "db.inc";
  $hostname = "localhost";
  $username = "grog";
  $password = "";
  $database = "weather";
  $dbtable = "observations";
  $station_id = "Dereel";

  /* Connect to the server */
  if (! ($connection = @ mysql_pconnect ($hostname, $username, $password)))
    showerror ();

  if (! mysql_select_db ($database, $connection))
    showerror ();

  if ($istoday)
  /* get current readings.  These are really the average of the last 5 minutes */
  {
    $now = time ();
    $start = time () - 600;   /* 5 minutes ago */

    $wind_directions = array ("N", "NNE", "NE", "ENE", "E", "ESE", "SE", "SSE",
                              "S", "SSW", "SW", "WSW", "W", "WNW", "NW", "NNW");
    $result = mysql_query (<<< EOS
SELECT AVG(inside_humidity),
       AVG(inside_temp),
       AVG(inside_dewpoint),
       AVG(outside_humidity),
       AVG(outside_temp),
       AVG(outside_dewpoint),
       AVG(pressure_msl),
       AVG(wind_speed),
       AVG(wind_gust),
       AVG(wind_direction),
       SUM(rain)
FROM $dbtable
WHERE unix_timestamp(timestamp(date,time)) >= $start
  AND unix_timestamp(timestamp(date, time)) <= $now
  AND station_id = "$station_id"
EOS
                           , $connection );

    if (! $result)
      showerror ();
    if ($row = mysql_fetch_array ($result, MYSQL_NUM))
    {
      $inside_humidity = sprintf ("%2.0f", $row [0]);
      $inside_temperature = sprintf ("%2.1f", $row [1]);
      $inside_dewpoint = sprintf ("%2.1f", $row [2]);
      $outside_humidity = sprintf ("%2.1f", $row [3]);
      $outside_temperature = sprintf ("%2.1f", $row [4]);
      $outside_dewpoint = sprintf ("%2.1f", $row [5]);
      $pressure_msl = sprintf ("%2.1f", $row [6]);
      $wind_speed = sprintf ("%2.1f", $row [7]);
      $wind_gust = sprintf ("%2.1f", $row [8]);
      $wind_direction = sprintf ("%2.1f", $row [9]);
      $rain = sprintf ("%2.1f", $row [10]);
      $wind_direction_text = $wind_directions [($row [9] + 11.25) / 22.5];
    }
  }

  $result = mysql_query (<<< EOS
SELECT @max_inside_humidity := MAX(inside_humidity),
       @min_inside_humidity := MIN(inside_humidity),
       @max_inside_temp := MAX(inside_temp),
       @min_inside_temp := MIN(inside_temp),
       @max_inside_dewpoint := MAX(inside_dewpoint),
       @min_inside_dewpoint := MIN(inside_dewpoint),
       @max_outside_humidity := MAX(outside_humidity),
       @min_outside_humidity := MIN(outside_humidity),
       @max_outside_temp := MAX(outside_temp),
       @min_outside_temp := MIN(outside_temp),
       @max_outside_dewpoint := MAX(outside_dewpoint),
       @min_outside_dewpoint := MIN(outside_dewpoint),
       @max_pressure_msl := MAX(pressure_msl),
       @min_pressure_msl := MIN(pressure_msl),
       @max_wind_speed := MAX(wind_speed),
       @min_wind_speed := MIN(wind_speed),
       @max_wind_gust := MAX(wind_gust),
       @min_wind_gust := MIN(wind_gust),
       SUM(rain)
FROM $dbtable
WHERE date >= "$startdate"
  AND date < "$enddate"
  AND station_id = "$station_id"
EOS
                           , $connection );

    if (! $result)
      showerror ();
    if ($row = mysql_fetch_array ($result, MYSQL_NUM))
    {
	$max_inside_humidity = sprintf ("%2.1f", $row [0]);
	$min_inside_humidity = sprintf ("%2.1f", $row [1]);
	$max_inside_temp = sprintf ("%2.1f", $row [2]);
	$min_inside_temp = sprintf ("%2.1f", $row [3]);
	$max_inside_dewpoint = sprintf ("%2.1f", $row [4]);
	$min_inside_dewpoint = sprintf ("%2.1f", $row [5]);
	$max_outside_humidity = sprintf ("%2.1f", $row [6]);
	$min_outside_humidity = sprintf ("%2.1f", $row [7]);
	$max_outside_temp = sprintf ("%2.1f", $row [8]);
	$min_outside_temp = sprintf ("%2.1f", $row [9]);
	$max_outside_dewpoint = sprintf ("%2.1f", $row [10]);
	$min_outside_dewpoint = sprintf ("%2.1f", $row [11]);
	$max_pressure_msl = sprintf ("%2.1f", $row [12]);
	$min_pressure_msl = sprintf ("%2.1f", $row [13]);
	$max_wind_speed = sprintf ("%2.1f", $row [14]);
	$min_wind_speed = sprintf ("%2.1f", $row [15]);
	$max_wind_gust = sprintf ("%2.1f", $row [16]);
	$min_wind_gust = sprintf ("%2.1f", $row [17]);
	$sum_rain = sprintf ("%2.1f", $row [18]);
    }

    $vars = array ("inside_humidity",
                   "inside_temp",
                   "inside_dewpoint",
                   "outside_humidity",
                   "outside_temp",
                   "outside_dewpoint",
                   "pressure_msl",
                   "wind_speed",
                   "wind_gust");
    foreach ($vars as $var)
    {
  $result = mysql_query (<<< EOS
SELECT date, time from $dbtable
WHERE date >= "$startdate"
  AND date < "$enddate"
  AND station_id = "$station_id"
  AND $var = @max_$var
LIMIT 1
EOS
                           , $connection );

      if (! $result)
        showerror ();
      if ($row = mysql_fetch_array ($result, MYSQL_NUM))
      {
        $max = "max_{$var}_time";
	if ($oneday)
          $$max = $row [1];           /* omit time */
        else
          $$max = $row [0] . " " . $row [1];
      }
    else
    {
      print <<< EOS
        <p>
        No data found for $startdate.
        </p>
  </body>
</html>
EOS;
      exit;
    }
  $result = mysql_query (<<< EOS
SELECT date, time from $dbtable
WHERE date >= "$startdate"
  AND date < "$enddate"
  AND station_id = "$station_id"
  AND $var = @min_$var
LIMIT 1
EOS
                           , $connection );

      if (! $result)
        showerror ();
      if ($row = mysql_fetch_array ($result, MYSQL_NUM))
      {
        $min = "min_{$var}_time";
	if ($oneday)
          $$min = $row [1];           /* omit time */
        else
          $$min = $row [0] . " " . $row [1];
      }
    }
}
  ?>

      <h2>
        <?php
        if ($istoday)
        {
          $timetext = date ("H:i:s");
          print "Readings for today at $timetext";
        }
        else if ($oneday)
          print "Readings for $startdate";
        else
          print "Readings for period $startdate to $enddate";
         ?>

      </h2>
      <table>
	<?php
      if ($istoday)                             /* include current readings */
        makerows (<<< EOS
-				Current			Minimum			At				Maximum			At
Outside temperature (°C)	$outside_temperature	$min_outside_temp	$min_outside_temp_time		$max_outside_temp	$max_outside_temp_time
Outside dewpoint (°C)		$outside_dewpoint	$min_outside_dewpoint	$min_outside_dewpoint_time	$max_outside_dewpoint	$max_outside_dewpoint_time
Outside humidity (%)		$outside_humidity	$min_outside_humidity	$min_outside_humidity_time	$max_outside_humidity	$max_outside_humidity_time

Inside temperature (°C)		$inside_temperature	$min_inside_temp	$min_inside_temp_time		$max_inside_temp	$max_inside_temp_time
Inside dewpoint (°C)		$inside_dewpoint	$min_inside_dewpoint	$min_inside_dewpoint_time	$max_inside_dewpoint	$max_inside_dewpoint_time
Inside humidity (%)		$inside_humidity	$min_inside_humidity	$min_inside_humidity_time	$max_inside_humidity	$max_inside_humidity_time

Pressure (hPa)			$pressure_msl		$min_pressure_msl	$min_pressure_msl_time		$max_pressure_msl	$max_pressure_msl_time
Wind speed (km/h)		$wind_speed		$min_wind_speed		$min_wind_speed_time		$max_wind_speed		$max_wind_speed_time
Wind gust (km/h)		$wind_gust		$min_wind_gust		$min_wind_gust_time		$max_wind_gust		$max_wind_gust_time
Wind direction (°)		$wind_direction ($wind_direction_text)
Day's rainfall (mm)		$sum_rain

EOS
, "lrrcrc");
	else
	makerows (<<< EOS
-	Minimum	At	Maximum	At
Outside temperature (°C)	$min_outside_temp	$min_outside_temp_time	$max_outside_temp	$max_outside_temp_time
Outside dewpoint (°C)	$min_outside_dewpoint	$min_outside_dewpoint_time	$max_outside_dewpoint	$max_outside_dewpoint_time
Outside humidity (%)	$min_outside_humidity	$min_outside_humidity_time	$max_outside_humidity	$max_outside_humidity_time

Inside temperature (°C)	$min_inside_temp	$min_inside_temp_time	$max_inside_temp	$max_inside_temp_time
Inside dewpoint (°C)	$min_inside_dewpoint	$min_inside_dewpoint_time	$max_inside_dewpoint	$max_inside_dewpoint_time
Inside humidity (%)	$min_inside_humidity	$min_inside_humidity_time	$max_inside_humidity	$max_inside_humidity_time

Pressure (hPa)	$min_pressure_msl	$min_pressure_msl_time	$max_pressure_msl	$max_pressure_msl_time
Wind speed (km/h)	$min_wind_speed	$min_wind_speed_time	$max_wind_speed	$max_wind_speed_time
Wind gust (km/h)	$min_wind_gust	$min_wind_gust_time	$max_wind_gust	$max_wind_gust_time
Day's rainfall (mm)	$sum_rain

EOS
, "lrcrc");
?>
	</table>

	<h2>
	Graphs for <?php echo $startdate ?>
	</h2>
        <?php
        /*
         * Ensure that we have the graph files.  Well, ensure that we have at least the first.
         */
        if ($oneday)
            $range = $startdate;
        else
            $range = "$startdate-$enddate";
	if ( ! file_exists ("$weatherdir/rain-$range-small.png"))
          {
          if ($oneday)
            system ("$doplot $startdate 2>/dev/null >/dev/null");
          else
            system ("$doplot $startdate $enddate 2>/dev/null >/dev/null");
          }
        ?>
	<div align="left">
	<?php showgraphs (<<< EOS
outside-temp-$range	Outside temperature
inside-temp-$range	inside-temp
inside-outside-temp-$range	inside-outside-temp
humidity-$range	humidity
wind-$range		wind
pressure-$range		pressure
rain-$range		rain
BoM-Dereel-$range	BoM-Dereel
EOS
			      , $startdate, $enddate);
?>
	</div>
    </div>

<?php pagefooter ($id); ?>