#! /usr/bin/perl

# sor_lidar_imager_match - list dates when both lidar and imager data are available

# 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) = "SOR Na Lidar and Imager Data Match";
print $cgi->header ();
print $cgi->start_html (-title => $title );
print "<div align=center>\n";
print $cgi->h1 ("SOR Na Lidar and Imager Data Match");

my ($sth, $url, $totalhour, $days);

$sth = $dbh->prepare (qq{
   SELECT sor_lidar_data_list.date as datelidar,
	  sor_imager_data_list.date as dateimager,
	  sor_lidar_data_list.start_time as lidar_start,
	  sor_lidar_data_list.end_time as lidar_end,
	  sor_imager_data_list.start_time as imager_start,
	  sor_imager_data_list.end_time as imager_end
   FROM   sor_lidar_data_list,
	  sor_imager_data_list
   WHERE  sor_lidar_data_list.date = sor_imager_data_list.date
   ORDER BY datelidar
   });
$sth->execute ();

# print out table of 12-month data hours
print "<TABLE BORDER>\n";
print "<TR align=\"center\">\n";
display_cell ("TH BGCOLOR=#FFFFFF", "Date", 1);
display_cell ("TH BGCOLOR=#FFFFFF", "Lidar Start Time", 1);
display_cell ("TH BGCOLOR=#FFFFFF", "Lidar End Time", 1);
display_cell ("TH BGCOLOR=#FFFFFF", "Imager Start Time", 1);
display_cell ("TH BGCOLOR=#FFFFFF", "Imager End Time", 1);
print "</TR>\n";

while (my @ary = $sth->fetchrow_array ())
{
   print "<TR>\n";
   display_cell ("TD BGCOLOR=#FFFFFF align=right", $ary[0], 1);
   display_cell ("TD BGCOLOR=#CCFFFF align=right", $ary[2], 1);
   display_cell ("TD BGCOLOR=#CCFFFF align=right", $ary[3], 1);
   display_cell ("TD BGCOLOR=#FFFFCC align=right", $ary[4], 1);
   display_cell ("TD BGCOLOR=#FFFFCC align=right", $ary[5], 1);
   print "</TR>\n";
}
print "</TABLE>\n";

$sth->finish ();

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=\"Geneva\" size=\"-1\">$value</font></$tag>\n";
}
# DISPLAY_CELL