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) = @_;
    
    my $sub_query = '';
    if(defined($pfrmat) && $pfrmat ne '' && $pfrmat ne 'all' ) {
        
        $sub_query .= sprintf(' AND (pfrmat = \'%s\') and target like \'T0\%\' ', $pfrmat);
    }
    
    if(defined($model_number) && $model_number ne '' ) {
        
        $sub_query .= sprintf(' AND (model = \'%s\')', $model_number);
    }
    
    my $result = 0;
    my $query = sprintf("SELECT count(*) FROM casp9.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 casp9.predictions WHERE target like 'TR%' 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) = @_;
    
    my $sub_query = '';
    if(defined($pfrmat) && $pfrmat ne '' && $pfrmat ne 'all' ) {
        
        $sub_query .= sprintf(' AND (pr.pfrmat = \'%s\') and pr.target like \'T0\%\' ', $pfrmat);
    }
    
    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 casp9.predictions pr JOIN casp9.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 casp9.predictions pr JOIN casp9.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 casp9.predictions pr JOIN casp9.groups gr ON (gr.id = pr.groups_id) WHERE pr.target like 'TR%' 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 casp9.predictions pr JOIN casp9.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 casp9.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 casp9.targets where (release_date + interval '%s hours' + interval '%s minutes') <= now() and is_refinement=0 %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 casp9.targets where cancelation_date <= now() and is_refinement=0 ");
    my $query = sprintf("select count(*) from casp9.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 casp9.targets where cancelation_date <= now() and is_refinement=0 ");
    my $query = sprintf("select count(*) from casp9.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) = @_;
    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 casp9.targets 
    			where ((%s + interval '%s hours' + interval '%s minutes') <= now() 
				and (cancellation_status = 0 %s) 
				and is_refinement=0
                        	and (release_date + interval '%s hours' + interval '%s minutes') <= now())",
			$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 target_available_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 casp9.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 is_refinement=0 ", $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_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 casp9.targets where (%s + interval '4 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()",
                        $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 casp9.targets where (%s + interval '4 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()",
                        $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 casp9.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 targets_id) from casp9.domains");
    #            
    #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 casp9.results_targets where domains_index <>0");
    #            
    #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;
}

####### 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 casp9.results_targets rt
        join casp9.domains dm on (dm.targets_id = rt.targets_id and dm.index = rt.domains_index)
        join casp9.domain_classifications dmc on (dm.domain_classifications_id = dmc.id)
        
        join casp9.targets tr on (rt.targets_id = tr.id)
        join casp9.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 casp9.predictions pr
                        join casp9.targets tr on (tr.name = pr.target)
                        where exists (select id from casp9.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 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 casp9.predictions pr join casp9.targets tr on (tr.name = pr.target)
                        where exists (select id from casp9.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 casp9.targets t join casp9.submitted_sequences ss
     on (t.submitted_sequences_id = ss.id) join casp9.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;