#!/usr/bin/perl

use lib "/var/www/perl";

# use strict;
use DBI;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);

use YearStats;

  ### global variables
  # DB connection
  my $db = 'DBI:mysql:VVSWVSDB;localhost';
  my $username = 'vvswvsweb';
  my $pass = 'dhj89kl0';
  my $host = 'localhost.localdomain';
  
  # logfile
# my $sum_output_file = ">d:\\summarytst.txt"; # offline use
#  my $sum_output_file = ">/var/www/perl/summary_stats.txt";    
  
  # my $timestamp = get_timestamp();
  
#  open(SUM, $sum_output_file);
#  print SUM "timestamp: $timestamp\n";    
  print SUM "test started\n";
  
  print header;
  print start_html('Waarnemers totalen');
  print "<pre>\n";
#  print "<h2>test</h2>\n";
#  print "<br>";


$buffer = $ENV{'QUERY_STRING'};

# Split information into name/value pairs
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
	($name, $value) = split(/=/, $pair);
	$value =~ tr/+/ /;
	$value =~ s/%(..)/pack("C", hex($1))/eg;
	$FORM{$name} = $value;
}
$first_year = $FORM{year};


  ## connect the DB
  my $dbh = DBI->connect($db, $username, $pass);
  if (DBI->errstr) {
  	print "problem connecting DB: ";
  	print DBI->errstr;
  	print "<br>\n";
  	print SUM "problem connecting DB: ";
  	print SUM DBI->errstr;
  	print SUM "\n";
  }
  else {
  	print SUM "DB connected<br>\n";
  }	
  
  ## prepare the handles
  my $sth_min_jd = $dbh->prepare(qq{select MIN(jd) FROM WVS_Observations});
  if (DBI->errstr) {
  	print "problem preparing sth_min_jd: ";  	
  	print DBI->errstr;
  	print "<br>";  	
  	print SUM "problem preparing sth_min_jd: ";
  	print SUM DBI->errstr;
  	print SUM "\n";
  }      
  
  my $sth_count_obs = $dbh->prepare(qq{select obs, COUNT(*) FROM WVS_Observations WHERE jd >= ? and jd < ? GROUP BY obs});
  if (DBI->errstr) {
  	print "problem preparing sth_count_obs: ";
  	print DBI->errstr;
  	print "<br>";

  	print SUM "problem preparing sth_count_obs: ";
  	print SUM DBI->errstr;
  	print SUM "\n";
  }        
  
  my $sth_wvs_observer = $dbh->prepare(qq{select observer FROM WVS_Observers WHERE wvs_ini = ?});
  if (DBI->errstr) {
  	print "problem preparing sth_wvs_observer: ";
  	print DBI->errstr;
  	print "<br>";

  	print SUM "problem preparing sth_count_obs: ";
  	print SUM DBI->errstr;
  	print SUM "\n";
  }          


  my ($sec, $min, $hour, $day, $mon, $year) = localtime(time);
  $mon += 1; 
  $year += 1900; 
  
#  print "localtime: $sec, $min, $hour, $day, $mon, $year<br>\n";
  print SUM "localtime: $sec, $min, $hour, $day, $mon, $year<br>\n";
  #my ($first_year, $first_month, $first_day, $first_hour, $first_min, $first_sec) = get_date_first_obs();
  #print "first_obs: $first_year, $first_month, $first_day, $first_hour, $first_min, $first_sec<br>\n";
  #print SUM "first_obs: $first_year, $first_month, $first_day, $first_hour, $first_min, $first_sec<br>\n";

  my $year_counter = $first_year;
  

#  for ($year_counter; $year_counter <= $year; $year_counter++) {
  	my $current_year_stats = new YearStats();
  	$current_year_stats->setYear($year_counter);
	
	for ($month_counter = 1; $month_counter <= 12; $month_counter++) {


		my $min_jd = date2jd($year_counter, $month_counter, 1, 0, 0, 0);
		if ($month_counter == 12) {
			$yyear = $year_counter + 1;
			$nmonth = 1;
		}
		else {
			$yyear = $year_counter;
			$nmonth = $month_counter + 1;
		}	
		my $max_jd = date2jd($yyear, $nmonth, 1, 0, 0, 0);

		$sth_count_obs->execute($min_jd, $max_jd);
		
		while (my @star_info = $sth_count_obs->fetchrow_array()) {
		 	my ($obsini, $monthtotal) = @star_info;
			$current_year_stats->setObsTotal($obsini, $month_counter, $monthtotal);
		}
	}
	
	my @observers = $current_year_stats->getObservers();
	print "Observer totals for: ";
	print $current_year_stats->getYear();
	print "<br><br>";
        print "OBS   jan   feb   mar   apr   may   jun   jul   aug   sep   oct   nov   dec TOTAAL<br>";
        print "----------------------------------------------------------------------------------<br>";	
	foreach $obs (@observers) {
		print "$obs ";
		for (my $i=1; $i<=12; $i++) {
			my $noObs = $current_year_stats->getObsTotal($obs, $i);
			#print SUM "$noObs %";
			printf "%5d ", $noObs;
		}
		my $totNoObs = $current_year_stats->getObsTotal($obs, 0);
		printf " %5d <br>", $totNoObs;
	}	
        print "----------------------------------------------------------------------------------<br>";	
        print "TOT ";
        
        my $c = 0;
        for (1..12) {
        	$c++;
        	my $month_total = $current_year_stats->getMonthTotal($c);
        	printf "%5d ", $month_total;
        }
        my $year_total = $current_year_stats->getMonthTotal(0);
        printf " %5d <br>", $year_total;
        print "==================================================================================<br><br>";        
        
        print "Observers<br>";
        print "----------------------------------------<br>";
        foreach $obs (@observers) {
        	$sth_wvs_observer->execute($obs);
        	my @observer_data = $sth_wvs_observer->fetchrow_array();
        	
        	print "$obs:  $observer_data[0]<br>";
        }
        print "========================================<br>";
        
        

	
#  }
  
  $sth_count_obs->finish();
  $sth_min_jd->finish();
  
  ($sec, $min, $hour, $day, $mon, $year) = localtime(time);
  $mon += 1; 
  $year += 1900; 
  
#  print "localtime: $sec, $min, $hour, $day, $mon, $year<br>\n";
  print SUM "localtime: $sec, $min, $hour, $day, $mon, $year<br>\n";  
  
  
  sub get_date_first_obs {
  	$sth_min_jd->execute();
  	my $min_jd = $sth_min_jd->fetchrow_array();
  	$sth_min_jd->finish();
  	my @date_first_obs = jd2date($min_jd);
  	
  	return @date_first_obs;
  }
  

  ## procedure to calculate the JD from a date (for verification of jul dates in the future)
  sub date2jd {
  	my($year, $month, $day, $hour, $min, $sec) = @_;
  	my $cor_a = 0;
  	my $cor_b = 0;
  	
  	if ($month <= 2) {
  		$year = $year - 1;
  		$month = $month + 12;
  	}
  	
  	my $greg = $year + $month/100 + $day/10000;
  	
  	if ($greg >= 1582) {
  		$cor_a = int($year/100);
  		$cor_b = 2 - $cor_a + int ($cor_a/4);
  	}
  	
  	my $jd = int(365.25 * $year) + int (30.6001 * ($month + 1)) + $day + $hour/24 + $min/1440 + $sec/86400 + 1720994.5 + $cor_b;
  	
  	return $jd;
  }
  
  
  ## procedure to calculate the date from a JD (inform on the date of the first and last observation)   
  sub jd2date {
  	my $jd = shift(@_);
  	my $a;

  	my $day;
  	my $month;
  	my $year;
  	my $hour;
  	my $min;
  	my $sec;

  	$jd = $jd + 0.5;
  	
  	my $z = int($jd);
  	my $f = $jd - $z;
 	my $at = int (($z - 1867216.25) / 36524.25);
  	$a = $z + 1 + $at - int($at/4);
  	
  	my $b = $a + 1524;
  	my $c = int(($b - 122.1)/365.25);
  	my $d = int(365.25 * $c);
  	my $e = int(($b - $d)/30.6001);
	  	
  	$day = $b - $d - int(30.6001 * $e) + $f;
  	
  	if ($e < 13.5) {
  		$month = $e - 1;
  	}
  	else {
  		$month = $e - 13;
  	}
  	
  	if ($month > 2.5) {
  		$year = $c - 4716;
  	}
  	else {
  		$year = $c - 4715;
  	}
  	
  	$hour = ($day - int($day)) * 24;
  	$min  = ($hour - int($hour)) * 60;
  	$sec  =  ($min - int($min)) * 60;
  	
	$day = int($day);
	$month = int($month);
		
  	$hour = int($hour);
  	$min  = int($min);
  	$sec  = int ($sec);
  	
  	if ($month < 10) {
  		$month = "0" . $month;
  	}
  	if ($day < 10) {
  		$day = "0" . $day;
  	}
  	if ($hour < 10) {
  		$hour = "0" . $hour;
  	}
  	if ($min < 10) {
  		$min = "0" . $min;
  	}  	
  	if ($sec < 10) {
  		$sec = "0" . $sec;
  	}  	  	

  	
  	return ($year, $month, $day, $hour, $min, $sec);
  }