#! /usr/bin/perl

# samp_browse - Allow samp_db database browsing using Web browser

# PREAMBLE
use DBI;
use CGI;
use strict;

# default connection  parameters - all missing
my ($host_name, $user_name, $password) = (undef, undef, undef);
my ($db_name) = "eosldata";

# construct data source
my ($dsn) = "dbi:mysql:$db_name";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=/users/eosl3/www/.my.cnf";

# connect to server
my (%attr) = ( RaiseError => 1 );
my ($dbh) = DBI->connect ($dsn, $user_name, $password, \%attr);
# PREAMBLE

# MAIN-BODY
my ($cgi) = new CGI;

# put out initial part of page
my ($title) = "South Pole Fe Lidar Data Catalog";
print $cgi->header ();
print $cgi->start_html (-title => $title);
print "<div align=center>\n";
print $cgi->h1 ($title);

# parameters to look for in URL
my ($sort_column) = $cgi->param ("sort_column");
my ($year) = $cgi->param ("year");

my ($url);

print "<table>\n";

print "<tr><td>\n";
$url = $cgi->script_name ();
$url .= sprintf ("?year=%s", "1999");
print $cgi->a ({-href => $url}, "1999" );

print "</td> <td>\n";
$url = $cgi->script_name ();
$url .= sprintf ("?year=%s", "2000");
print $cgi->a ({-href => $url}, "2000" );

print "</td> <td>\n";
$url = $cgi->script_name ();
$url .= sprintf ("?year=%s", "2001");
print $cgi->a ({-href => $url}, "2001" );

print "</td> </tr> </table>\n";

if (!$year)
{
  $year = 2001;
}

display_table ($sort_column, $year);

print "<hr width=80%>";
print "<A href=\"/Research/Data/index.html\">Back to Data Catalog</A>";

print "</div>\n";

print $cgi->end_html ();
# MAIN-BODY

$dbh->disconnect ();
#exit (0);

# DISPLAY_CELL
# display a value in a table cell; put non-breaking
# space in "empty" cells so borders show up

sub display_cell
{
my ($tag, $value, $encode) = @_;

	$value = $cgi->escapeHTML ($value) if $encode;
	$value = "&nbsp;" unless $value;
	print "<$tag><font face=\"Geneve\" size=\"-1\">$value</font></$tag>\n";
}
# DISPLAY_CELL

# need the _X to make the listelim tag not a subset of the previous one
# DISPLAY_TABLE_X
sub display_table
{
my ($sort_column, $year) = @_;
my ($sth, $url);

	# if sort column not specified, use first column
	$sort_column = "1" unless $sort_column;

	$sth = $dbh->prepare (qq{
		SELECT * FROM sp_lidar_data_list WHERE YEAR(start_date) = $year ORDER BY $sort_column
		});
	$sth->execute ();

	# display table with a border
	print "<TABLE BORDER>\n";
	# use column names for table headings; make each heading a link
	# that sorts output on the corresponding column
      
	print "<TR align=\"center\">\n";

	$url = $cgi->script_name ();
	$url .= sprintf ("?sort_column=%s", $cgi->escape (${$sth->{NAME}}[0]));
	$url .= sprintf ("&year=%s", $cgi->escape ($year));
	display_cell ("TH BG=#FFFFFF", $cgi->a ({-href => $url}, "Start Date"), 0);

	$url = $cgi->script_name ();
	$url .= sprintf ("?sort_column=%s", $cgi->escape (${$sth->{NAME}}[1]));
	$url .= sprintf ("&year=%s", $cgi->escape ($year));
	display_cell ("TH BG=#FFFFFF", $cgi->a ({-href => $url}, "Start Time"), 0);

	$url = $cgi->script_name ();
	$url .= sprintf ("?sort_column=%s", $cgi->escape (${$sth->{NAME}}[2]));
	$url .= sprintf ("&year=%s", $cgi->escape ($year));
	display_cell ("TH BG=#FFFFFF", $cgi->a ({-href => $url}, "End Date"), 0);

	$url = $cgi->script_name ();
	$url .= sprintf ("?sort_column=%s", $cgi->escape (${$sth->{NAME}}[3]));
	$url .= sprintf ("&year=%s", $cgi->escape ($year));
	display_cell ("TH BG=#FFFFFF", $cgi->a ({-href => $url}, "End Time"), 0);

	$url = $cgi->script_name ();
	$url .= sprintf ("?sort_column=%s", $cgi->escape (${$sth->{NAME}}[4]));
	$url .= sprintf ("&year=%s", $cgi->escape ($year));
	display_cell ("TH BG=#FFFFFF", $cgi->a ({-href => $url}, "Actual Time"), 0);

	print "</TR>\n";

	# display table rows
	while (my @ary = $sth->fetchrow_array ())
	{
		print "<TR>\n";

		display_cell ("TD BGCOLOR=#FFFFFF align=right", $ary[0], 1);
		display_cell ("TD BGCOLOR=#FFFFCC align=right", $ary[1], 1);
		display_cell ("TD BGCOLOR=#FFFFFF align=right", $ary[2], 1);
		display_cell ("TD BGCOLOR=#FFFFCC align=right", $ary[3], 1);
		display_cell ("TD BGCOLOR=#FFFFFF align=right", $ary[4], 1);

		print "</TR>\n";
	}

	$sth->finish ();
	print "</TABLE>\n";
}
# DISPLAY_TABLE_X