#!/usr/bin/perl

# This is the home page to the gas prices site.  It will (hopefully)
# be able to access the gasstations database

use CGI qw(:standard);
use DBI;

$title = "Soaring Gas Prices";
$heading = "Gas prices are still high";
$driver = "mysql";
$database = "gasstations";
$dbhost = "mysql";
$dbuser = "dave";
$password = "RcRacing";

$dsn = "DBI:$driver:database=$database;host=$dbhost";
$dbh = DBI->connect($dsn, $dbuser, $password);


##################

print header(), start_html($title);

print <<EndOfStyle;
<style>
<!--
.price
{
   font-family: monospace;
}
.modify
{
   text-align: center;
}
-->
</style>

EndOfStyle

print h1($heading);

print <<EndOfTop;

<p>
The purpose of this web page is to help the people of San Luis Obispo track 
the gas prices on a station-by-station basis.
<p>
Also, if you think you can describe the location of a particular station
in terms of latitude and longitude, feel free to e-mail me the coordinates.
<a href="mailto:dave\@oksner.org">dave\@oksner.org</a>
<p>

EndOfTop

if ($dbh == NULL)
{
	print ("<p><br>Sorry, couldn't connect to the database.  I promise that I'll notice and do something about it!\n");
	die;
}
	
###########
# update or re-sort
#

#foreach $key (sort keys %ENV) {
#	print ("$key=$ENV{$key}<br>\n");
#}

$order = "name";
$webuser = $ENV{"REMOTE_USER"};

#print("username: $webuser<br>\n");

if (param()) 
{
	if (defined(param('sort'))) 
	{
		$sortreq = param('sort');
		if ($sortreq ne "name") 
		{
			$order = "$sortreq,name";
		}
	}

	if ($ENV{"REQUEST_METHOD"} eq "POST") 
	{

		$id = param('id');

		if (length($webuser) == 0) 
		{
			print("If you'd like to modify this, e-mail\n");
			print("<a href=\"mailto:dave\@oksner\.org\">dave</a> for a username.<br>\n");
		}
		else
		{
			$newdate = `date +%Y-%m-%d`;

			foreach $grade (octane87, octane89, octane92, diesel)
			{
				$price = param($grade);

				$statement = "select * from station where id = \"$id\"";
				$sth = $dbh->prepare("$statement");
				$sth->execute() or die "Can't execute $statement: $dbh->errstr\n";

				#there'd better only be one; we used the primary key!
				$station = $sth->fetchrow_hashref;

				if ($price != $station->{$grade}) 
				{
					$statement ="update station set $grade = \"$price\" where id = \"$id\"";
					#print ("$statement<br>\n");
					$sth = $dbh->do("$statement") or die "Can't execute $statement: $dbh->errstr\n";
					$statement="update station set " . $grade . "_chg_user = \"$webuser\" where id = \"$id\"";
					#print ("$statement<br>\n");
					$sth = $dbh->do("$statement") or die "Can't execute $statement: $dbh->errstr\n";
					$statement="update station set " . $grade . "_chg_date = \"$newdate\" where id = \"$id\"";
					#print ("$statement<br>\n");
					$sth = $dbh->do("$statement") or die "Can't execute $statement: $dbh->errstr\n";
				}
			}

			$statement="update station set chg_user = \"$webuser\" where id = \"$id\"";
			#print ("$statement<br>\n");
			$sth = $dbh->do("$statement") or die "Can't execute $statement: $dbh->errstr\n";
			$statement="update station set chg_date = \"$newdate\" where id = \"$id\"";
			#print ("$statement<br>\n");
			$sth = $dbh->do("$statement") or die "Can't execute $statement: $dbh->errstr\n";
		}
	}
}

if (!($ENV{"REMOTE_USER"}))
{
	print("If you'd like to modify this and you don't already have a username, e-mail\n");
	print("<a href=\"mailto:dave\@oksner\.org\">dave</a> for a username.<br>\n");
}

# end of update or resort
#################

print <<TopOfTable;

<table border=3>
 <tr>
 <td colspan=7><a href="http://www.mapblast.com"><img src="http://www.mapblast.com/mblast/images/logos/mblogo_small.gif" border="1" width=61 height=40 align=absmiddle></a>   Brought to you by <a href="http://www.mapblast.com/mblast/map.mb?CMD=LFILL&&CT=35.291037:-120.6593:10000&IC=35.291037:-120.6593:8:&GAD2=1340+Taft+St&GAD3=San+Luis+Obispo%2c+CA++93405-1975&W=150&H=150&DU=MI">MapBlast!</a>   (Click on map to navigate)
 </td>
 </tr>

 <tr>
 <td></td>
 <td><a href="?sort=name">Name</a><br>Address<br>City</td>
 <td><a href="?sort=octane87">87 Octane</a></td>
 <td><a href="?sort=octane89">89 Octane</a></td>
 <td><a href="?sort=octane92">92 Octane</a></td>
 <td><a href="?sort=diesel">Diesel</a></td>
 </td>

TopOfTable

#############
# this is the main part of the program; it cycles though each station in 
# the database, creating one table row for each

$sth = $dbh->prepare("select * from station order by $order");
$sth->execute();

while($station = $sth->fetchrow_hashref) {
	$addr  = $station->{address};
	$addr =~ s/ /+/g;
	$cty   = $station->{city};
	$cty  =~ s/ /+/g;
	$latlon = $station->{latitude} . ":" . $station->{longitude};

	$href  = "http://www.mapblast.com/mblast/map.mb?";
	$href .= "CMD=LFILL\&\&CT=";
	$href .= $latlon;
	$href .= ":10000\&IC=";
	$href .= $latlon;
	$href .= ":8:\&GAD2=";
	$href .= $addr;
	$href .= "\&GAD3=";
	$href .= $cty;
	$href .= "%2c+";
	$href .= $station->{state} . "++" . $station->{zip};
	$href .= "\&W=150\&H=150\&DU=MI";

	$img  = "http://www.mapblast.com/gif?";
	$img .= "\&CT=";
	$img .= $latlon;
	$img .= ":10000\&LB=";
	$img .= $addr;
	$img .= "\&IC=";
	$img .= $latlon;
	$img .= ":8:\&W=150\&H=150\&FAM=mblast";

	print (" <form method=POST action=\"/~dave/gasprices/\" enctype=\"application/x-www-form-urlencoded\">\n");
	#print (" <form method=POST enctype=\"application/x-www-form-urlencoded\">\n");
	print (" <input type=hidden name=id value=\"$station->{id}\">\n");
	print (" <input type=hidden name=sort value=\"$order\">\n");
	print (" <tr>\n");
	print (" <td rowspan=2><a href=\"$href\"><img src=\"$img\"></a></td>\n");
	print (" <td>$station->{name}<br>$station->{address}<br>$station->{city}</td>\n");
	print (" <td class=price><input type=text size=5 maxlength=5 name=octane87 value=\"$station->{octane87}\"</td>\n");
	print (" <td class=price><input type=text size=5 maxlength=5 name=octane89 value=\"$station->{octane89}\"</td>\n");
	print (" <td class=price><input type=text size=5 maxlength=5 name=octane92 value=\"$station->{octane92}\"</td>\n");
	print (" <td class=price><input type=text size=5 maxlength=5 name=diesel value=\"$station->{diesel}\"</td>\n");
	print (" <td class=modify rowspan=2><input type=submit value=\"Modify\">");
#	if (defined($station->{chg_user})) {
#		print ("<br>Last modified by<br>$station->{chg_user} on $station->{chg_date}");
#	}
	print ("</td>\n");
	print (" </tr>\n");

	print (" <tr>\n");
	print (" <td align=right>Modified by:</td>\n");
	if (defined($station->{octane87_chg_user})) {
		print ("<td class=modify>$station->{octane87_chg_user}<br>on<br>$station->{octane87_chg_date}</td>\n");
	} 
	else
	{
		print ("<td>&nbsp;</td>\n");
	}

	if (defined($station->{octane89_chg_user})) {
		print ("<td class=modify>$station->{octane89_chg_user}<br>on<br>$station->{octane89_chg_date}</td>\n");
	} 
	else
	{
		print ("<td>&nbsp;</td>\n");
	}

	if (defined($station->{octane92_chg_user})) {
		print ("<td class=modify>$station->{octane92_chg_user}<br>on<br>$station->{octane92_chg_date}</td>\n");
	} 
	else
	{
		print ("<td>&nbsp;</td>\n");
	}

	if (defined($station->{diesel_chg_user})) {
		print ("<td class=modify>$station->{diesel_chg_user}<br>on<br>$station->{diesel_chg_date}</td>\n");
	} 
	else
	{
		print ("<td>&nbsp;</td>\n");
	}

	print (" </tr>\n");

	print (" </form>\n");
}

$sth->finish;

print ("</table>\n");
print end_html();