#!/usr/bin/perl

        $whichmethod = $ENV{'REQUEST_METHOD'};
        if($whichmethod eq "GET"){ 
            $forminfo = $ENV{"QUERY_STRING"};
        }else{
	    read(STDIN, $forminfo, $ENV{'CONTENT_LENGTH'});
        } 

      @key_value_pairs = split(/&/,$forminfo); 

      foreach $pair (@key_value_pairs){
           ($key,$value) = split(/=/,$pair);
           $value =~ s/\+/ /g; 
           $value =~ s/%([0-9a-fA-F][0-9a-fA-F])/pack("C", hex($1))/eg;
           $FORM_DATA{$key} = $value;
      }


$where_clause = $FORM_DATA{'where_clause'};
$group_clause = $FORM_DATA{'group_clause'};

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

$cginame="http://$ENV{'SERVER_NAME'}$ENV{'SCRIPT_NAME'}";


print "<form action='$cginame' method=post>\n";

print "<p><b>Enter the where clause (boolean condition)</b>:<br>";
print "For example, event <> \"Training\"";
print "<textarea rows='3' cols='80' name=where_clause></textarea>\n";

print "<p><b>Enter the group clause (list of fields separated by #).</b><br>";
print "Potential fields are any from the rwsurvey, rwlit, rworganism, rwlocation, rwRHSites, rwSeasons.<br>";
print "Examples are:<br>";
print "&nbsp;&nbsp;rh_name [Reef Health site name]"; 
print "&nbsp;&nbsp;year(date); month(date);<br>";
print "&nbsp;&nbsp;season; if(month(date)<12,year(date),year(date)+1) [the year but with December included in the following year]<br>";
print "Therefore seasonal data summaries would be:<br>";
print "&nbsp;&nbsp;rh_name#if(month(date)<12,year(date),year(date)+1)#season<br>";

print "<textarea rows='3' cols='80' name=group_clause></textarea>\n";
print "<br><input type=submit></form>\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:reefwatch:xxxxxxxxxxxxxx", "xxxxxxxx", "xxxxxxx");
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 ";

$output_counter= 0;

############################################################
#
# Get summary of surveys and metres for each group
#
############################################################
$stmt = "select $group_clause, count(distinct rwlit.survey_id), count(distinct CONCAT(rwsurvey.survey_id,'_',metre)) from $linked_tables $where_clause GROUP BY $group_clause";

#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 $where_clause $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>";
    }
}