#! /usr/bin/perl

# sor_lidar_sum - list monthly and yearly sums of data hour

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

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

$sth = $dbh->prepare (qq{
   SELECT month(date) as m, monthname(date) AS month,
	  SUM(actual_time*usable_profile/total_profile) AS total,
	  COUNT(*) AS nday
	  FROM sor_lidar_data_list GROUP BY month ORDER BY m
   });
$sth->execute ();

# print out table of 12-month data hours
print "<TABLE BORDER>\n";
print "<TR align=\"center\">\n";
display_cell ("TH BGCOLOR=#FFFFFF", "Month", 1);
display_cell ("TH BGCOLOR=#FFFFFF", "Hours", 1);
display_cell ("TH BGCOLOR=#FFFFFF", "Nights", 1);
print "</TR>\n";

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

$sth->finish ();

# get total hours
$sth = $dbh->prepare ( qq { SELECT SUM(actual_time*usable_profile/total_profile) FROM sor_lidar_data_list } );
$sth->execute ();
$totalhour = $sth->fetchrow_array ();
$sth->finish ();

# get total number of nights
$sth = $dbh->prepare (qq{ SELECT COUNT(*) FROM sor_lidar_data_list });
$sth->execute ();
$days=$sth->fetchrow_array ();
$sth->finish ();

# get total number of nights with hours >= 2.0
$sth = $dbh->prepare (qq{ SELECT COUNT(*) FROM sor_lidar_data_list WHERE actual_time >= 2.0 });
$sth->execute ();
$days2hr=$sth->fetchrow_array ();
$sth->finish ();

# get total hours of all nights with hours >= 2.0
$sth = $dbh->prepare (qq{ SELECT SUM(actual_time*usable_profile/total_profile) FROM sor_lidar_data_list WHERE actual_time >= 2.0 });
$sth->execute ();
$hrs2hr=$sth->fetchrow_array ();
$sth->finish ();

$totalhour = sprintf ( "%.2f", $totalhour );
$hrs2hr = sprintf ( "%.2f", $hrs2hr );
print "<H2>Total Hours: <FONT COLOR=BLUE>$totalhour</FONT></H2>";
print "<H2>Total Nights: <FONT COLOR=BLUE>$days</FONT></H2>";
print "<h2>There are <FONT COLOR=BLUE>$days2hr</FONT> nights that have data > 2 hours, total of <FONT COLOR=BLUE>$hrs2hr</FONT> hours.</h2>";
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