#! /usr/bin/perl
#
# displayWeather.cgi 
#
# Reads most recent outside temperature record from the weather database  
# and generates a little HTML on stdout with that value embedded in it. 
#
# For more information about:
#
#   Postgres generally www.postgresql.org
#   Postgres' dialect of SQL "man sql"
#   psql, the interactive query tool "man psql"
#   The select statement "man select"
#   To connect to the weather database using psql "psql -d weather" 
#     (when logged-in to quark as weather)
#   Perl5 extension for Postgres "perldoc Pg"
#
# Who		When		 What
# ----------------------------------------------------------------------
# charliep	07-Jan-2000	 Created.
#
# Lew Riley     05-Feb-2000      Added humidity, wind speed & heading,
#                                and "uncalibrated" disclaimer. 
#
# Lew Riley     23-Feb-2000      Added Fahrenheit, mph
# Maduna        Aug 08           Changed from use Pg to use DBI

use DBI; 
use strict;

my $dbuser = "weather";
my $dbname = "weather";
my $dbpass = "th0raz1ne";
my $host = "db.cs.earlham.edu";

my $dbh; 
my $sql; 
my $sth;
my $result; 
my $timestamp;
my $out_temperature;
my $out_humidity;
my $wind_speed;
my $wind_heading;

$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host","$dbuser","$dbpass",
                           {RaiseError => 1, AutoCommit => 0})
        ||die "Database connection not made: $DBI::errstr";

$sql = "select timestamp, out_temperature, out_humidity, wind_speed, wind_heading " . 
       "from readings " . 
       "where timestamp = (select max(timestamp) " . 
       "                   from readings); ";
 
$sth = $dbh->prepare("$sql") || die "Unable to prepare sql: $DBI::errstr.\n";

$sth->execute || die "Unable to execute sql: $DBI::errstr.\n";

($timestamp, $out_temperature, $out_humidity, $wind_speed, $wind_heading) = $sth->fetchrow_array;

my $heading;
if ($wind_heading > 337.5 || $wind_heading <= 22.5) {
    $heading = "N";
} elsif ($wind_heading > 22.5  && $wind_heading <= 67.5) {
    $heading = "NE";
} elsif ($wind_heading > 67.5  && $wind_heading <= 112.5) {
    $heading = "E";
} elsif ($wind_heading > 112.5 && $wind_heading <= 157.5) {
    $heading = "SE";
} elsif ($wind_heading > 157.5 && $wind_heading <= 202.5) {
    $heading = "S";
} elsif ($wind_heading > 202.5 && $wind_heading <= 247.5) {
    $heading = "SW";
} elsif ($wind_heading > 247.5 && $wind_heading <= 292.5) {
    $heading = "W";
} elsif ($wind_heading > 292.5 && $wind_heading <= 337.5) {
    $heading = "NW";
} else {
  print "<h4>Error: illegal wind heading: $wind_heading</h4>\n";
}

my $out_temp_F = $out_temperature * 9/5 + 32;
$out_temperature = sprintf("%3.0f", $out_temperature);
$out_temp_F      = sprintf("%3.0f", $out_temp_F);

my $wind_speed_mph = $wind_speed / 0.447;
$wind_speed_mph = sprintf("%2.0f", $wind_speed_mph);

if ( !defined($timestamp) ) {
  print "<h4>Error retrieving weather data (fetchrow), ", $dbh->errorMessage, "</h4>\n";
} else {
    my $html = <<"_END_";
<b>Conditions on the roof of Dennis Hall as of $timestamp:
<ul>
  <li>Temperature : $out_temperature C ($out_temp_F F)
  <li>Humidity : $out_humidity\%
  <li>Wind: $wind_speed m/s $heading ($wind_speed_mph mph)
</ul>
Caution! Take this data lightly!  We have not yet calibrated our instruments! </b>
_END_

    print "$html\n";
}

while ( ($timestamp, $out_temperature, $out_humidity, $wind_speed, $wind_heading) = $sth->fetchrow_array )
{ }  # deal with the rest of the data returned.  It complains if you don't

$dbh->disconnect();

exit 0;