#!/usr/bin/perl

        


$where_clause = "event <> 'Training' and (year(date)-2011)*12+month(date) > 6";
$group_clause = "rh_name#if(month(date)<12,year(date),year(date)+1)#season";


print "Content-type: text/html\n\n";



die unless length($where_clause) > 0;

$where_clause = "rwsurvey.type = 'LIT' and ".$where_clause;


@groups = split('#',$group_clause); 
$group_clause = join(",",@groups);
		

use DBI;
$dbh = DBI->connect("DBI:mysql:conserva_reefwatch:localhost", "conserva", "w399eWkWp1");
if (!$dbh) {
    $dbh = DBI->connect("DBI:mysql:reefwatch:localhost", "root", "mysql");
       die "Cannot connect: $D: $DBI::errstr\n"
	   unless $dbh;
    print "<p>Connected locally</p>";
}



$linked_tables = "rwsurvey, rwlit, rworganism, rwRHSites, rwSeasons, rwlocation where rwlit.organism_id = rworganism.org_id and rwsurvey.survey_id = rwlit.survey_id and rwRHSites.site_id = rwsurvey.site_id and rwRHSites.exposure = rwsurvey.exposure and rwlocation.site_id = rwsurvey.site_id and season_id = if(month(date)>0,floor(month(date) % 12/3),-1) and ".$where_clause;

$output_counter= 0;

############################################################
#
# Get summary of surveys and metres for each group
#
############################################################
$stmt = "select rh_name,if(month(date)<12,year(date),year(date)+1),season, count(distinct rwlit.survey_id), count(distinct CONCAT(rwsurvey.survey_id,'_',metre)) from $linked_tables GROUP BY rh_name,if(month(date)<12,year(date),year(date)+1),season";

#print "<p>$stmt"; #com

$sth = $dbh->prepare($stmt) || print "Can't prepare $stmt: ".$dbh->errstr()."\n";

$sth->execute || print "can't execute the query: ".$sth->errstr()."\n";

while (@res = $sth->fetchrow)
{ 
    $line = join(',',@res);

    push (@group_val_list, $line);
}

#print @group_val_list; #com

############################################################
#
# Extract the data for each group
#
############################################################

foreach $line (@group_val_list)
{
    $output_counter++;

    print "A,$output_counter,$line<br>";

    @vals = split (/,/, $line);
    $last_id = $#vals - 2;
    @vals = @vals[0..$last_id];

    $extra_clause = "";
    for ($i=0; $i<=$#vals; $i++)
    {
	$extra_clause .= " and $groups[$i] = '$vals[$i]'";
    }

    ############################################################
    #
    # Type B: Raw data
    #
    ############################################################

    $stmt = "SELECT rwsurvey.survey_id, metre, transition, code from $linked_tables  $extra_clause order by rwsurvey.survey_id, metre, transition";

#    print "<p>$stmt</p>";

    $sth = $dbh->prepare($stmt) || print "Can't prepare $stmt: ".$dbh->errstr()."\n";

    $sth->execute || print "can't execute the query: ".$sth->errstr()."\n";

    $last_survey_id = -1;
    $total_metres = 0;
    $total_surveys = 0;
    undef %cover;

    while (($survey_id, $metre, $transition, $code) = $sth->fetchrow) 
    {
	$output_counter++;
	print "B,$output_counter,$survey_id,$metre,$transition,$code<br>";

	if ($survey_id != $last_survey_id)
	{
	    $total_surveys++;

	    if ($last_survey_id != -1)
	    {
		$total_metres += ($last_metre - $min_metre + 1);
	    }
	    $last = ($metre-1)*100;
	    $min_metre = $metre;
	    if (($transition != 0) or ($code != 'START')) 
	    {
		$output_counter++;
		print "D,$output_counter,'Problem: $survey_id, $last_survey_id, $transition, $code'<br>";
	    }

	}
	else
	{

	    $current = ($metre-1) * 100 + $transition;
	    $last_metre = $metre;
	    $delta = $current - $last;
	    #print "delta = $delta<br>";
	    $cover{$code} += $delta;
	    if ($delta < 0)
	    {
		$output_counter++;
		print "D,$output_counter,'Problem: negative $current, $last, $metre, $transition, $code'<br>";
	    }
		
	    $last = $current;
	}

	$last_survey_id = $survey_id;

    }

    $total_metres += ($last_metre - $min_metre + 1);

    #print "<p>$total_surveys surveys (total of $total_metres metres)";


    ############################################################
    #
    # Type C: Percentage cover
    #
    ############################################################

    while (($lifeform, $total_length) = each(%cover))
    {
	$percent_cover = sprintf("%0.1f",$total_length / $total_metres);
	$val_list = join(',', @vals);

	$output_counter++;
	print "C,$output_counter,$val_list,$lifeform,$total_length,$percent_cover<br>";
    }
}