package NumbersManager;
use strict;
use warnings;
use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use Data::Dumper;
use lib qw(Core);
use Database;

use Configuration;
use LocalConfiguration;
use Dumpvalue;

### METHODS (last update 05/26/2010 M. Wojciechowski):
#   models_count (counts all predictions sent to CASP)
#   models_count_tr (counts all refinement predictions)
#   contributing_groups_count (counts all groups contributing predictions)
#   contributing_tr_groups_count (counts all groups contributing refinement predictions)
#   groups_count (counts registered groups)
#   target_released_count (counts targets released in CASP)
#   target_canceled_count (counts targets cancelled in CASP)
#   target_expired_count (counts targets expired in CASP)
#   target_available_count (counts targets available for prediction)
#   target_available_qa_count (counts QA targets available for prediction)
#   get_domains (returns domains)
#   predictions_count (predictions for domains)
#   targets_count (counts all targets in CASP for domains)
#   sgi_center_stats (returns stats for sgi centers)
######################################################

sub new {
    my ($class) = @_;
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD})        
        
    };
    
    # check database connection if not connect throw an exception
    
    bless $self, $class;
    return $self;
}

sub models_count {
    my ($self, $pfrmat, $model_number, $assisted_targets) = @_;
    
    my $sub_query = '';
    if(defined($pfrmat) && $pfrmat ne '' && $pfrmat ne 'all' && $pfrmat ne 'QS' && $pfrmat ne 'RR' ) {
        
        $sub_query .= sprintf(" AND (pfrmat = \'%s\') and target similar to  'T([0-9])%%' ", $pfrmat); # regular targets
    }
    if(defined($pfrmat) && $pfrmat eq 'RR' ) {
        $sub_query .= sprintf(" AND (pfrmat = '%s') ", $pfrmat); # RR 
    }
    if(defined($pfrmat) && $pfrmat eq 'QS' ) {
	$pfrmat = 'TS';
        $sub_query .= sprintf(" AND (pfrmat = '%s') and target similar to  '(H|O)([0-9])%%' ", $pfrmat); # assembly targets
    }
    if (defined($assisted_targets) && $assisted_targets ne ''){
        $sub_query = sprintf(" AND (pfrmat = '%s') and target similar to '(N|S|F|A|X|L|n|s|f|a|x|l)([0-9])%%' ", $pfrmat); # assisted prediction targets
    }


    if(defined($model_number) && $model_number ne '' ) {
        
        $sub_query .= sprintf(" AND (model = '%s')", $model_number);
    }
    
    my $result = 0;
    my $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE (1=1) %s ", $sub_query);
            
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
    
            
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub models_count_tr {
    my ($self, $model_number) = @_;
    my $sub_query = '';
    if(defined($model_number) && $model_number ne '' ) {
        $sub_query .= sprintf(' AND (model = \'%s\')', $model_number);
    }
    my $result = 0;
    my $query = sprintf("SELECT count(*) FROM casp13.predictions WHERE target similar to 'R%%' and pfrmat='TS'  %s ", $sub_query);
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    return $result;
}


sub contributing_groups_count {
    my ($self, $pfrmat, $is_server, $assisted_targets) = @_;
    
    my $sub_query = '';
    if(defined($pfrmat) && $pfrmat ne '' && $pfrmat ne 'all' && $pfrmat ne 'QS' ) {
        
        $sub_query .= sprintf(" AND (pr.pfrmat = '%s') and pr.target similar to 'T([0-9])%%' ", $pfrmat); # regular targets
    }
    if(defined($pfrmat) && $pfrmat eq 'QS' ) {
	$pfrmat = 'TS';
        $sub_query .= sprintf(" AND (pr.pfrmat = '%s') and pr.target similar to '(H|O)([0-9])%%' ", $pfrmat); # assembly targets
    }    

    if (defined($assisted_targets) && $assisted_targets ne ''){
	$sub_query = sprintf(" AND (pr.pfrmat = '%s') and pr.target similar to '(N|S|F|A|X|L|n|s|f|a|x|l)([0-9])%%' ", $pfrmat); # assissted targets
    }

    if(defined($is_server) && $is_server ne '' ) {
        
        $sub_query .= sprintf(' AND (gr.type > 0) AND (gr.type < 3)');
    }
    
    
    #SELECT count(DISTINCT pr.groups_id) FROM casp13.predictions pr JOIN casp13.groups gr ON (gr.id = pr.groups_id)
    #WHERE (1 = 1) and gr.type > 0
    
    
    
    my $result = 0;
    my $query = sprintf("SELECT count(DISTINCT pr.groups_id) FROM casp13.predictions pr JOIN casp13.groups gr ON (gr.id = pr.groups_id) WHERE (1=1) %s ", $sub_query);
            
#    print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#    print "Info $query<br>";  
    
            
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}


sub contributing_tr_groups_count {
    my ($self, $is_server) = @_;
    my $sub_query = '';
    if(defined($is_server) && $is_server ne '' ) {
        $sub_query .= sprintf(' AND (gr.type > 0) AND (gr.type < 3)');
    }
    my $result = 0;
    my $query = sprintf("SELECT count(DISTINCT pr.groups_id) FROM casp13.predictions pr JOIN casp13.groups gr ON (gr.id = pr.groups_id) WHERE pr.target similar to 'R%%' and pr.pfrmat='TS'  %s ", $sub_query);
#    print "Content-Type: text/html; charset=ISO-8859-1\n\n";
#    print "Info $query<br>";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    return $result;
}



sub groups_count {
    my ($self, $is_server) = @_;
    
    my $sub_query = '';
    
    if(defined($is_server) && $is_server ne '' ) {
        if ($is_server eq 'true') {
            $sub_query .= sprintf(' AND (type > 0) AND (type < 3)');
        } else {
            $sub_query .= sprintf(' AND (type = 0)');
        }
    }
    
    #SELECT count(DISTINCT pr.groups_id) FROM casp13.predictions pr JOIN casp13.groups gr ON (gr.id = pr.groups_id)
    #WHERE (1 = 1) and gr.type > 0
    
    my $result = 0;
    my $query = sprintf("SELECT count(DISTINCT id) FROM casp13.groups WHERE (1 = 1) %s ", $sub_query);
            
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
    
            
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub target_released_count {
    my ($self, $type) = @_;
	#print "Content-Type: text/html; charset=ISO-8859-1\n\n";
	my $subquery = '';
	if(defined($type) && $type ne '') {
		if ($type eq 'all') { 	
			$subquery = "AND is_server_only = 0 ";
		}
	}
    my $result = 0;
    my $query = sprintf("select count(*) from casp13.targets where (release_date + interval '%s hours' + interval '%s minutes') <= now() and is_refinement=0 and name similar to 'T[0-9]%%' %s", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $subquery); 
    #print "Info $query<br>";
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub target_multimer_released_count {
    my ($self, $type) = @_;
        #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
        my $subquery = '';
        if(defined($type) && $type ne '') {
                if ($type eq 'all') {
                        $subquery = "AND is_server_only = 0 ";
                }
        }
    my $result = 0;
    my $query = sprintf("select count(*) from casp13.targets where (release_date + interval '%s hours' + interval '%s minutes') <= now() and is_refinement=0 and name similar to '(H|O)[0-9]%%' %s", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $subquery);
    #print "Info $query<br>";

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }

    return $result;
}


sub target_tr_released_count {
    my ($self, $type) = @_;
        #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
        my $subquery = '';
        if(defined($type) && $type ne '') {
                if ($type eq 'all') {
                        $subquery = "AND is_server_only = 0 ";
                }
        }
    my $result = 0;
    my $query = sprintf("select count(*) from casp13.targets where (release_date + interval '%s hours' + interval '%s minutes') <= now() and is_refinement=1 %s", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $subquery);
    #print "Info $query<br>";

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }

    return $result;
}

sub target_other_released_count {
    my ($self, $type) = @_;
        #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
        my $subquery = '';
        if(defined($type) && $type ne '') {
                if ($type eq 'all') {
                        $subquery = "AND is_server_only = 0 ";
                }
        }
    my $result = 0;
    my $query = sprintf("select count(*) from casp13.targets where (release_date + interval '%s hours' + interval '%s minutes') <= now() and is_refinement=0 and name  similar to '(N|S|F|A|X|L|n|s|f|a|x|l)[0-9]%%' %s", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $subquery);
    #print "Info $query<br>";

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }

    return $result;
}

sub target_canceled_humans_count {
    my ($self) = @_;
    my $result = 0;
    #my $query = sprintf("select count(*) from casp13.targets where cancelation_date <= now() and is_refinement=0 ");
    my $query = sprintf("select count(*) from casp13.targets where (cancelation_date + interval '%s hours' + interval '%s minutes') <= now() and is_refinement=0
                        and (release_date + interval '%s hours' + interval '%s minutes') <= now()", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES});    
    
                
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub target_canceled_all_count {
    my ($self) = @_;
    my $result = 0;
    #my $query = sprintf("select count(*) from casp13.targets where cancelation_date <= now() and is_refinement=0 ");
    my $query = sprintf("select count(*) from casp13.targets where cancellation_status = 1 and (cancelation_date + interval '%s hours' + interval '%s minutes') <= now() and is_refinement=0
                        and (release_date + interval '%s hours' + interval '%s minutes') <= now()", $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES});    
    
                
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
} 

#and (cancelation_date + interval '%s hours' + interval '%s minutes') > now() 


sub target_expired_count {
    my ($self, $type, $target_type) = @_;
    my $result = 0;
    my $expired_for = '';
    my $expire_server_status = '';
    my $sub_queue = '';
    if(defined($type) && $type ne '' ) {
        if ($type eq 'human') {
            $expired_for = sprintf('human_expiration_date');
	    $sub_queue = sprintf(' and is_server_only=0 ');
        } else {
            $expired_for = sprintf('server_expiration_date');
	    $expire_server_status = " or cancellation_status = 2 ";
        }
    }

    if(defined($target_type) && $target_type ne '' ){
	if($target_type eq 'others'){
		$sub_queue .= sprintf(" and is_refinement=0 and name similar to '(N|S|F|A|X|L|n|s|f|a|x|l)[0-9]%%' ");
	}elsif($target_type eq 'refinement'){
                $sub_queue .= sprintf(" and is_refinement=1 ");
        }elsif($target_type eq 'multimer'){
                $sub_queue .= sprintf(" and is_refinement=0 and name similar to '(H|O)[0-9]%%' ");
        }else{
		$sub_queue .= sprintf(" and is_refinement=0 and name similar to 'T[0-9]%%' ");
	}
    }else{
	$sub_queue = sprintf(" and is_refinement=0 ");
    }

    my $query = sprintf("select count(*) from casp13.targets 
    			where ((%s + interval '%s hours' + interval '%s minutes') <= now() 
				and (cancellation_status = 0 %s) 
				%s
                        	and (release_date + interval '%s hours' + interval '%s minutes') <= now())",
			$expired_for, $LOCAL_CONFIG->{EXPIRED_HOURS}, $LOCAL_CONFIG->{EXPIRED_MINUTES},
			$expire_server_status, $sub_queue, 
                        $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES});
                
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
} 


sub target_available_count {
    my ($self, $type, $target_type) = @_;
    my $result = 0;
    my $expired_for = '';
    my $sub_queue = '';
    if(defined($type) && $type ne '' ) {
        if ($type eq 'human') {
            $expired_for = sprintf('human_expiration_date');
	    $sub_queue .= sprintf(" and is_server_only=0 ");
        } else {
            $expired_for = sprintf('server_expiration_date');
        }
    }
    
    if(defined($target_type) && $target_type ne '' ){
        if($target_type eq 'others'){
                $sub_queue .= sprintf(" and is_refinement=0 and name similar to '(N|S|F|A|X|L|n|s|f|a|x|l)[0-9]%%' ");
        }elsif($target_type eq 'refinement'){
                $sub_queue .= sprintf(" and is_refinement=1 ");
        }elsif($target_type eq 'multimer'){
                $sub_queue .= sprintf(" and is_refinement=0 and name similar to '(H|O)[0-9]%%' ");
        }else{
                $sub_queue .= sprintf(" and is_refinement=0 and name similar to 'T[0-9]%%' ");
        }
    }else{
        $sub_queue .= sprintf(" and is_refinement=0 ");
    }


    my $query = sprintf("select count(*) from casp13.targets where (%s + interval '%s hours' + interval '%s minutes') > now()
                        and (release_date + interval '%s hours' + interval '%s minutes') <= now()
                        and (cancelation_date + interval '%s hours' + interval '%s minutes') > now() 
			and cancellation_status=0 
                         %s ", $expired_for, $LOCAL_CONFIG->{EXPIRED_HOURS}, $LOCAL_CONFIG->{EXPIRED_MINUTES},
                        $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES}, $sub_queue);
                
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}



sub target_available_qa_count {
    my ($self, $type) = @_;
    my $result = 0;
    my $expired_for = '';
    if(defined($type) && $type ne '' ) {
        if ($type eq 'human') {
            $expired_for = sprintf('human_expiration_date');
        } else {
            $expired_for = sprintf('server_expiration_date');
        }
    }
    
    my $query = sprintf("select count(*) from casp13.targets where (%s + interval '6 days' + interval '%s hours' + interval '%s minutes') >= now() and
                        (%s + interval '1 days' + interval '%s hours' + interval '%s minutes') < now() and  is_refinement=0
                        and (release_date + interval '%s hours' + interval '%s minutes') <= now()
                        and (cancelation_date + interval '%s hours' + interval '%s minutes') > now()
			and name similar to 'T[0-9]%%' ",
                        $expired_for, $LOCAL_CONFIG->{EXPIRED_HOURS}, $LOCAL_CONFIG->{EXPIRED_MINUTES}, $expired_for,
                        $LOCAL_CONFIG->{EXPIRED_HOURS}, $LOCAL_CONFIG->{EXPIRED_MINUTES},
                        $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES},
                        $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES});
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub target_expired_qa_count {
    my ($self, $type) = @_;
    my $result = 0;
    my $expired_for = '';
    my $expire_server_status = '';
    if(defined($type) && $type ne '' ) {
        if ($type eq 'human') {
            $expired_for = sprintf('human_expiration_date');
        } else {
            $expired_for = sprintf('server_expiration_date');
	    $expire_server_status = " or cancellation_status = 2 ";
        }
    }
    
    my $query = sprintf("select count(*) from casp13.targets where (%s + interval '6 days' + interval '%s hours' + interval '%s minutes') < now() and  is_refinement=0
    			and (cancellation_status = 0 %s)
                        and (release_date + interval '%s hours' + interval '%s minutes') <= now()
			and name similar to 'T[0-9]%%' ",
                        $expired_for, $LOCAL_CONFIG->{EXPIRED_HOURS}, $LOCAL_CONFIG->{EXPIRED_MINUTES},
			$expire_server_status, 
                        $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES});
    #            
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub count_structures_published {
    my ($self, $target_id) = @_;
    
    my $result = 0;
    
    my $query = sprintf("SELECT count(id) FROM casp13.structures WHERE is_published = 1", $target_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub count_targets_with_domains {
    my ($self) = @_;
    my $result;
    my $query = sprintf("select count(DISTINCT d.targets_id) from casp13.domains d join casp13.targets t on t.id=d.targets_id WHERE t.name similar to 'T([0-9])%%' ");
    #            
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub count_results_with_domains {
    my ($self) = @_;
    my $result;
#   my $query = sprintf("select count(DISTINCT targets_id) from casp13.results_targets domains_index <>0 ");
    my $query = sprintf("select count(DISTINCT targets_id) from casp13.results_targets rt join casp13.targets t on rt.targets_id=t.id where rt.domains_index <>0 and t.is_final_eval=1 and t.name similar to 'T([0-9])%%' ");
    #            
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub count_target_results {
    my ($self, $params) = @_;
    my $result = 0;
    my $subquery = " WHERE t.is_final_eval=1 and t.name similar to 'T[0-9]%%' ";
    if (defined($params) && defined($params->{view}) && $params->{view} eq "others"){
	$subquery = " WHERE t.name similar to '(N|S|F|A|X|L|n|s|f|a|x|l)[0-9]%%' ";
    } 
    if (defined($params) && defined($params->{view}) && $params->{view} eq "refinement"){
        $subquery = " WHERE t.name similar to 'R%%' ";
    }
    if (defined($params) && defined($params->{view}) && $params->{view} eq "multimer"){
        $subquery = " WHERE t.name similar to '(H|O)[0-9]%%' ";
    }

    my $query = sprintf("select count(DISTINCT targets_id) from casp13.results_targets rt join casp13.targets t on rt.targets_id=t.id %s ", $subquery);
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }

    return $result;
} 

####### STATISTICS


sub get_domains{
    my ($self, $params, $html_format) = @_;
    my $field = $params->{field};
    my $order = $params->{order};    
    $field = (!defined($field) || ($field eq '')) ? 'tr.name' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;
    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/, / $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    ####    
    my $sub_query = " and 1=1 ";
    #my $sub_query = " and rt.domains_index IN (0,1,2,3,4,5) ";
    
    
    my @results = ();
    my $query = sprintf("select tr.name, dm.index, dm.length, dm.range, tr.is_server_only, tr.pdb_code, dmc.name, ss.protein_name, ss.organism_name
        from casp13.results_targets rt
        join casp13.domains dm on (dm.targets_id = rt.targets_id and dm.index = rt.domains_index)
        join casp13.domain_classifications dmc on (dm.domain_classifications_id = dmc.id)
        
        join casp13.targets tr on (rt.targets_id = tr.id)
        join casp13.submitted_sequences ss on (ss.id = tr.submitted_sequences_id)
        WHERE 1=1 %s ORDER BY %s %s ", $sub_query, $field, $order);
    
    #print $query . "\n";
    
    my $sth = $self->{_database}->query($query);  

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($target, $domain, $length, $range, $is_server_only, $pdb_code, $classification, $protein_name, $organism_name) = $sth->fetchrow_array()) {
##	    push(@results, {
##		INDEX => $index,
##		target => $target,
##                domain => $domain,
##                length => $length,
##                range => $range,
##                is_server_only => $is_server_only,
##                pdb_code => $pdb_code,
##                classification => $classification,
##                protein_name => $protein_name,
##                organism_name => $organism_name		
##		}
##	    );
            
            if( ($index > 0 ) && ($results[$index-1]{target} eq $target)) {
                $results[$index-1]{domain_definition} .= sprintf("<br>D%s: %s ", $domain, $range),
                $results[$index-1]{classification} .= sprintf("<br>%s", $classification),
                $results[$index-1]{length} .= sprintf("<br>%s", $length), 
            } else {            
                push(@results, {
                    INDEX => $index,
                    target => $target,
                    domain_definition => sprintf("D%s: %s ", $domain, $range),
                    length => $length,
                    classification => $classification,
                    is_server_only => $is_server_only,
                    pdb_code => $pdb_code,                
                    protein_name => $protein_name,
                    organism_name => $organism_name		
                    }
                );
                $index++;
            }            
            
            
	    
        }
    }
    return @results;
}



sub predictions_count {
    my ($self, $params) = @_;
    my $result = 0;
    my $sub_query = "";    
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
 	$sub_query .= sprintf(" and pr.groups_id=\%s\ ", $params->{groups_id});	
    }
    
    if(defined($params->{pfrmat}) && ($params->{pfrmat} ne '')) {
 	$sub_query .= sprintf(" and pr.pfrmat=\'%s'\ ", $params->{pfrmat});	
    }
    
    my $query = sprintf("select count(*) from casp13.predictions pr
                        join casp13.targets tr on (tr.name = pr.target)
                        where exists (select id from casp13.results_targets where domains_index=1 and targets_id = tr.id) %s ", $sub_query);
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}

sub predictions_count_per_group {
    my ($self, $params) = @_;
    my $result = 0;
    my $sub_query = "";
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
        $sub_query .= sprintf(" and pr.groups_id=\%s\ ", $params->{groups_id});
    } else {
	return $result;
    }

    if(defined($params->{pfrmat}) && ($params->{pfrmat} ne '')) {
        $sub_query .= sprintf(" and pr.pfrmat=\'%s'\ ", $params->{pfrmat});
    } else {
	$sub_query .= sprintf(" and pr.pfrmat='%s' ", 'TS'); # TS format by default
    }

    if (defined($params->{target_type}) && ($params->{target_type} eq 'refine')){
	$sub_query .= sprintf(" and pr.target similar to 'R%%' ") ;
    } elsif (defined($params->{target_type}) && ($params->{target_type} eq 'assisted')){
	$sub_query .= sprintf(" and pr.target similar to '(N|S|F|A|X|L|n|s|f|a|x|l)[0-9]%%' ") ;
    } elsif (defined($params->{target_type}) && ($params->{target_type} eq 'multimer')){
        $sub_query .= sprintf(" and pr.target similar to '(H|O)[0-9]%%' ") ; 
    } else{
	$sub_query .= sprintf(" and pr.target similar to 'T[0-9]%%' ") ; # regular target by default
    }

    
    my $query = sprintf("select count(*) from casp13.predictions pr
                        where 1=1 %s ", $sub_query);

    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }

    return $result;
}


sub targets_count_per_group {
    my ($self, $params) = @_;
    my $result = 0;
    my $sub_query = "";
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
        $sub_query .= sprintf(" and pr.groups_id=\%s\ ", $params->{groups_id});
    } else {
        return $result;
    }

    if(defined($params->{pfrmat}) && ($params->{pfrmat} ne '')) {
        $sub_query .= sprintf(" and pr.pfrmat=\'%s'\ ", $params->{pfrmat});
    } else {
        $sub_query .= sprintf(" and pr.pfrmat='%s' ", 'TS'); # TS format by default
    }

    if (defined($params->{target_type}) && ($params->{target_type} eq 'refine')){
        $sub_query .= sprintf(" and pr.target similar to 'R%%' ") ;
    } elsif (defined($params->{target_type}) && ($params->{target_type} eq 'assisted')){
        $sub_query .= sprintf(" and pr.target similar to '(N|S|F|A|X|L|n|s|f|a|x|l)[0-9]%%' ") 
    } elsif (defined($params->{target_type}) && ($params->{target_type} eq 'multimer')){
        $sub_query .= sprintf(" and pr.target similar to '(H|O)[0-9]%%' ")
    } else {
        $sub_query .= sprintf(" and pr.target similar to 'T[0-9]%%' ") ; # regular target by default
    }

    
    my $query = sprintf("select count(distinct target) from casp13.predictions pr
                        where 1=1 %s ", $sub_query);

    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }

    return $result;
}



sub targets_count {
    my ($self, $params) = @_;
    my $result = 0;
    my $sub_query = "";    
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
 	$sub_query .= sprintf(" and pr.groups_id=\%s\ ", $params->{groups_id});	
    }
    
    if(defined($params->{pfrmat}) && ($params->{pfrmat} ne '')) {
 	$sub_query .= sprintf(" and pr.pfrmat=\'%s'\ ", $params->{pfrmat});	
    }
    
    my $query = sprintf("select count(distinct pr.target) from casp13.predictions pr join casp13.targets tr on (tr.name = pr.target)
                        where exists (select id from casp13.results_targets where domains_index=1 and targets_id = tr.id) %s ", $sub_query);
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       $result = $sth->fetchrow_array();
    }
    
    return $result;
}


sub sgi_center_stats {
    my ($self) = @_;
    my @result = ();
    
    my $query = sprintf("select sc.id, sc.name, sc.full_name, count(distinct ss.id) from casp13.targets t join casp13.submitted_sequences ss
     on (t.submitted_sequences_id = ss.id) join casp13.sgi_center sc
     on (ss.sgi_center_id = sc.id) where (t.release_date + interval '%s hours' + interval '%s minutes') <= now() group by sc.id, sc.name, sc.full_name order by sc.id",
     $LOCAL_CONFIG->{RELESE_HOURS}, $LOCAL_CONFIG->{RELESE_MINUTES});
    
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #print "Info $query<br>";  
           
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($id, $name, $full_name, $count) = $sth->fetchrow_array()) {
                       
            push(@result, {
                     ID => $id,
        	     NAME => $name,
	             FULL_NAME => $full_name,
		     COUNT => $count
            	}
            );
        }       
    }
    
    return @result;
}

1;