use strict;
use warnings;

require( 'decision.pl' );
my @cv=('#FFFF00','#FFFFFF','#FFF0F0','#FFD0D0','#FF0000');
my @cva=('#FFFF00','#FFFFFF','#FFF0F0','#FFD0D0','#FFB0B0','#FF0000');

our @synthese_keys; # from ../lib/synthese.pl
our @review_keys; # from ../lib/synthese.pl
our %state_char; # from ../lib/gestrfia;
our %state_color; # from ../lib/gestrfia;
our $cgi_base;

sub SQL_REVIEWER
{
    my ($opt) = @_;

    my $select = 
        "SELECT r.id, r.name, r.email, r.affiliation\n".
        "FROM reviewer as r";

    if( exists( $opt->{'rev_id'} ) ) 
    {
        $select .= "\nWHERE r.id=" . 
            ( $opt->{'rev_id'} ? "'$opt->{'rev_id'}'" : '?' );
    } elsif( exists( $opt->{'paper_id'} ) ) {
        $select.= "\nLEFT JOIN review ON r.id=review.rev_id\n" .
            "WHERE review.paper_id=".
                ( $opt->{'paper_id'} ? "'$opt->{'paper_id'}'" : '?' );
    }

    if( DEBUG() ) { dprintsql( 'SQL_REVIEWER', $select ); }
    return $select;
}


sub SQL_REV_COUNT #($id)
{
    my $select = 
        "SELECT COUNT(*)\n" .
        "FROM review\n" .
        "WHERE review.rev_id=?\n" .
        "GROUP BY review.rev_id";
    if( DEBUG() ) { dprintsql( 'SQL_REV_COUNT', $select ); }
    return $select;
}

sub SQL_REV_MARK
{
    my $select = 
        "SELECT reviewer.id, reviewer.name, reviewer.email," .
        "review.state, review.opened,review.reviewfdb,\n".
        join( ', ', @review_keys ). "\n" .
        "FROM review, reviewer\n".
        "WHERE review.paper_id=?\n".
        "AND review.rev_id=reviewer.id";

    if( DEBUG() ) { dprintsql( 'SQL_REV_MARK', $select ); }
    return $select;
}

sub SQL_PAPER
{
    my ($opt, $where, $having, $ac_id, $paper_id) = @_;

	my $select = 
        "SELECT p.id,p.title,s.recommendation,s.more_reviewers,\n".
            "COUNT(review.paper_id) AS nrev\n" .
        "FROM paper AS p\n" .
        "LEFT JOIN synthese AS s ON p.id=s.paper_id\n" .
        "LEFT JOIN review AS r ON p.id=r.paper_id\n" .
        "LEFT JOIN editor AS a ON s.editor_id=a.id\n" .
        "LEFT JOIN reviewer AS ac ON s.editor_id=ac.id\n" .
        "WHERE p.confirm=1";

    # determine subset of papers 
    $ac_id and $select .= " AND s.editor_id = '$ac_id'";
    $paper_id and $select .= " AND s.paper_id = '$paper_id'";

    $where and $select .= "\nAND $where";
    $select .= "\nGROUP BY p.id";
    $having and $select .= "\nHAVING $having";

    # sorting of papers 
    $select .= "\nORDER BY p.id";

    if( DEBUG() ) { dprintsql( 'SQL_PAPER', $select ); }
    return $select;
}

sub SQL_AC_REV_MARK
# papers joined with AC marks 
{
    my ($opt, $where, $having, $ac_id, $paper_id) = @_;
    # papers with AC marks and overall mark 
    # (joined with review for counting purposes)

    my $select = 
        "SELECT p.id,p.title,s.recommendation,p.accept_reject,\n" .
            "p.reviewfdb,\n" .
            "s.Overall,s.Novelty,s.Importance,s.Reference," .
            "s.Clarity,s.Technical,s.Experimental,\n" .
            "IFNULL(s.Overall,9) as ACOverall,\n" . 
            "IF( s.Novelty<4 && s.Importance<4 && s.Reference<4 && " .
                "s.Clarity<4 && s.Technical<4 && s.Experimental<4,\n" .
                "  (s.Novelty+s.Importance+s.Reference+s.Clarity+" .
                   "s.Technical+s.Experimental)/6,\n" .
                "IF(ISNULL(s.Novelty)||ISNULL(s.Importance)||\n".
                    "ISNULL(s.Reference)||ISNULL(s.Clarity)||\n" .
                    "ISNULL(s.Technical)||ISNULL(s.Experimental),9,4)) mark,\n" .
          "COUNT(r.paper_id) AS nrev,\n".
          "a.acronym, ac.name, ac.email\n" .
        "FROM paper AS p\n" .
        "LEFT JOIN synthese AS s ON p.id=s.paper_id\n" .
        "LEFT JOIN review AS r ON p.id=r.paper_id\n" .
        "LEFT JOIN editor AS a ON s.editor_id=a.id\n" .
        "LEFT JOIN reviewer AS ac ON s.editor_id=ac.id\n" .
        "WHERE p.confirm=1";

    # determine subset of papers 
    $ac_id and $select .= " AND s.editor_id = '$ac_id'";
    $paper_id and $select .= " AND s.paper_id = '$paper_id'";

    $where and $select .= "\nAND $where";
    $select .= "\nGROUP BY p.id";
    $having and $select .= "\nHAVING $having";

    # sorting of papers 
    if( $opt->{'sort'} and $opt->{'sort'} eq 'mark' ) {
        $select .= "\nORDER BY ACOverall, mark, p.id";
    } elsif( $opt->{'sort'} and $opt->{'sort'} eq 'dmark' ) {
        $select .= "\nORDER BY IF( LCASE(accept_reject) <=> 'bo', 'orb'," .
                                 "IF( LCASE(accept_reject) <=> 'bp', 'pob',".
                                     "IFNULL(LCASE(accept_reject),'un') ) )".
                                         ",ACOverall, mark, p.id";
    } else { # 'id'
        $select .= "\nORDER BY p.id";
    }

    if( DEBUG() ) { dprintsql( 'SQL_AC_REV_MARK', $select ); }
    return $select;
}

sub list_mark
{
    my ($S) = @_;
    my $o = $S->{'ACOverall'}<= 5 ? $S->{'ACOverall'} : '&nbsp;'; 
    my $co = $cva[$S->{'ACOverall'}<=5 ? $S->{'ACOverall'} : 0 ]; 
    my $m = $S->{'mark'}<= 4 ? sprintf( "%.2f", $S->{'mark'} ) : 
        "&nbsp;&nbsp;"; 
    my $cm = $cv[$S->{'mark'}<=4 ? int( $S->{'mark'}+0.5) : 0 ]; 

    print "<span style='background-color:$co'>$o</span>;";
    print "<span style='background-color:$cm'>$m</span>";
}


sub list_rev_short_head
{
    foreach my $k ( @synthese_keys[2..8] ) {
        print html("<TH ><FONT SIZE=-1>".substr($k,0,5)."</FONT></th>\n");
    }
}

sub list_rev_short
{
    my ($sock, $S) = @_;
    my $sth;

    if( ref( $sock ) eq 'ARRAY' ) # [ $sock, $sth ]
    { ( $sock, $sth ) = ( $sock->[0], $sock->[1] );  }
    else { $sth = $sock->prepare( SQL_REV_MARK )
            or die "DBI ERROR: ". $sock->errstr; }

    $sth->execute( int( $S->{'id'} ) ) or die "DBI ERROR: ". $sock->errstr;

    my %rev_marks;
    foreach my $k ( @synthese_keys[2..8] ) { $rev_marks{$k} = ''; }
    while( my $R = $sth->fetchrow_hashref())
    {
        $rev_marks{'Overall'} .= "<font size=-1 style='background-color:" .
            $cva[$R->{'Overall'}||0] . "'>".($R->{'Overall'}||'&nbsp')."</font>";

        foreach my $k ( @synthese_keys[3..8] ) {
            $rev_marks{$k} .= "<font size=-1 style='background-color:" .
                $cv[$R->{$k}||0] . "'>".($R->{$k}||'&nbsp')."</font>";
        }
    }

    print html("<TH ALIGN=CENTER BGCOLOR=" . $cva[$S->{'Overall'}||0] . ">" .
               ( defined( $S->{'Overall'} ) ? $S->{'Overall'} : '' ) .
               "&nbsp;<br>". $rev_marks{Overall} );


    foreach my $k ( @synthese_keys[3..8] ) {
        print html( "<TD  BGCOLOR=". $cv[$S->{$k}||0] . " ALIGN=CENTER>" .
                    ( defined($S->{$k}) ? $S->{$k} : '' ) ."&nbsp;<br>". $rev_marks{$k}  . "</TD>\n" );
    }
}

sub list_rev_full_head
{
    print "<th>Reviews</th>";
}

sub list_rev_full
{
    my ($sock, $S, $showopenclose, $blind, $orev) = @_;

    my $rev_sth = $sock->prepare( SQL_REV_MARK )
        or die( "ERROR: ". $sock->errstr );
    my $rev_cnt_sth = $sock->prepare( SQL_REV_COUNT )
        or die( "ERROR: ".$sock->errstr );

    $rev_sth->execute( int( $S->{'id'} ) ) 
        or die( "ERROR: ". $sock->errstr );

    print "      <TABLE BORDER=1 FRAME=BOX WIDTH=100%>\n<TR><TD>";

    if (!$orev){
    $S->{'reviewfdb'} and 
        print "<a href='$cgi_base/ac/view_feedback.cgi?".
		   "pap=$S->{'id'}' target=feedback>Authors feedback</A>";
}
    my @rev_keys = @review_keys[ 4, 6, 8, 10,12,14,16];
    my @ac_keys = @synthese_keys[   3, 4,  5, 6, 7, 8];

    foreach my $k (( @review_keys[ 1,2, 4, 6, 8, 10,12,14,16])) {
        print html("<TH><FONT SIZE=-1>".substr($k,0,5)."</FONT>\n");
    }

    defined( $showopenclose ) or $showopenclose = 1;
    $showopenclose &&= is_phase('postreview');
	
    while( my $R = $rev_sth->fetchrow_hashref())
    {

        $rev_cnt_sth->execute( $R->{'id'} ) or die( "ERROR: " . $sock->errstr );
        my $ratecount = " of ".$rev_cnt_sth->fetchrow();
        print html( "<tr><TD>" );
        print_one_rev_person( $R, $S->{'id'}, $showopenclose, $blind,$orev );
        print "</td>\n";
        print html("<TD ALIGN=CENTER>".($R->{$review_keys[1]}||'').
                   "$ratecount</TD>\n");
        print html("<TH ALIGN=CENTER BGCOLOR=".$cva[$R->{$review_keys[2]}||0].
                   ">".
                   ($R->{$review_keys[2]}||0)."</TH>\n");
        foreach my $k ( @review_keys[ 4, 6, 8, 10,12,14,16]) {
           print html("<TD BGCOLOR=".$cv[$R->{$k}||0]." ALIGN=CENTER>".
                      ($R->{$k}||'')."</TD>\n");
        }
        print html("\n</td></tr>\n");
    }
    $rev_sth->finish();

    # ac 
    print html("<TR><Td>" );
    print_one_ac_person( $S, $S->{'id'}, $showopenclose, $blind ,$orev);
    if( is_phase_after( $sock, 'acmeeting'  ) ) {
        OneDecisionFull( $S->{'accept_reject'} );
    }
    print html("<TD ALIGN=CENTER>---</TD>\n");
    print html("<TH ALIGN=CENTER BGCOLOR=" . $cva[$S->{'Overall'}||0] . ">" .
               ( defined( $S->{'Overall'} ) ? $S->{'Overall'} : '' ) );
    print "<TD ALIGN=CENTER>---</TD>\n";


    foreach my $k ( @synthese_keys[3..8] ) {
        print html( "<TD  BGCOLOR=". $cv[$S->{$k}||0] . " ALIGN=CENTER>" .
                    ( defined($S->{$k}) ? $S->{$k} : '' ) . "</TD>\n" );
    }

    print html( "</TR></table>\n" );
}


sub print_one_rev_person
{
    my ($R, $paper_id, $showopenclose, $blind, $orev) = @_;

    if (!$orev){$orev=0;}
    if( $blind ) {
        if ( $R->{'id'} == $orev) {
	    print "You";
	    } else {
		print "Reviewer";
} 
        return;
    } 
    print html( $R->{'state'} ? 
                ( $state_char{$R->{'state'}} .
                  "<a href='mailto:$R->{'email'}'>".
                  "<font color='$state_color{$R->{'state'}}'>$R->{'name'}" .
                  "</font></a>\n" ) :
                "<a href='mailto:$R->{'email'}'>$R->{'name'}</a>\n" );
    
    if( $paper_id )
    {
        my $prid = "pap=$paper_id&rev=$R->{'id'}";
        
        print "<a href='$cgi_base/ac/view_review.cgi?$prid'>review</a>\n";
        $R->{'reviewfdb'} and
            print " <a href='$cgi_base/ac/view_feedback.cgi?$prid'>feedback</A>";
        $showopenclose and print " <a href='$cgi_base/ac/openclose_review.cgi?" .
            "confirm=1&$prid" . 
                ( $R->{'opened'} ? 
                  "'><b style='background-color:#FFFF00'>close</b></a> " :
                  "&open=1'>open</a>" ). "</td>\n";
    }
};

sub print_one_ac_person
{
    my ($A, $paper_id, $showopenclose, $blind) = @_;


    if( $blind ) {
        print "AC Decision: "; 
        return;
    } 

    print html( "<a href='mailto:$A->{'email'}'>$A->{'name'}</a>\n" );
    $paper_id and
        print "<a href='$cgi_base/ac/view_consolidation.cgi?paper_id=$paper_id'>" .
            "consolidation</a>\n";
}

sub print_one_buddy_person
{
    my ($B) = @_;

    if( $B->{'buddy_name'} ) {
        print 
      html( "<a href='mailto:$B->{'buddy_email'}'>$B->{'buddy_name'}</a>\n" );
    } else { print "unassigned\n"; }
}

1;
