#! /usr/bin/perl

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

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

$sth = $dbh->prepare (qq{
   SELECT month(start_date) as m,
	  monthname(start_date) AS month,
	  SUM(time_to_sec(end_time-start_time)),
	  COUNT(*) AS nday
   FROM sp_imager_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=#FFFFFF align=right", sprintf ( "%.2f", $ary[2]/3600 ), 1);
   display_cell ("TD BGCOLOR=#FFFFFF align=right", $ary[3], 1);
   print "</TR>\n";
}
print "</TABLE>\n";

$sth->finish ();

# get total hours
$sth = $dbh->prepare ( qq { 
   SELECT SUM(time_to_sec(end_time-start_time))
   FROM sp_imager_data_list } );
$sth->execute ();
$totalhour = $sth->fetchrow_array ();
$sth->finish ();

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

$totalhour = sprintf ( "%.2f", $totalhour/3600 );
print "<h2>Total Hours <FONT COLOR=BLUE>$totalhour</FONT></h2>";
print "<h2>Total Nights <FONT COLOR=BLUE>$days</FONT></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