package ResultsManager;

use strict;
use warnings;

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use DateTime;

use lib qw(Core);

use Database;

use PDBUtils;

use Configuration;
use LocalConfiguration;
use TargetsManager;

my $results_manager = undef;

sub new {
    my ($class) = @_;
    return $results_manager if(defined($results_manager));
    my %model = (ID => '');

    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD}),
        _model => \%model
    };

    $results_manager = bless $self, $class;
    return $results_manager;
}

sub get_new_model {
    my ($self) = @_;
    my %model = (
########################### system
	id => '',
	predictions_id =>'',
        #DOMAIN_ID => '',
        date => '',
########################## file name
        TARGET => '',
        PFRMAT => '',
        CODE => '',
        MODEL => '',
        parent =>'',
        domain =>'',
	targ_variant => '', # target_variant 
########################### results

####################### LGA 4
################# SUMMARY
	n1_4 => '',
	n2_4 => '',
	dist_4=> '',
	nres_4 => '',
	rmsd_4 => '',
	gdt_ts_4 => '',
	lga_s_4 => '',
	lga_q_4 => '',
	gdt_ca_percentage_4 => '',
	asgn_4 => '',
	np_p_4 => '',
	gdt_ha_4 => '',
	alignment_4 => '',
####################### LGA 5
################# SUMMARY
	n1_5 => '',
	n2_5 => '',
	dist_5 => '',
	nres_5 => '',
	rmsd_5 => '',
	gdt_ts_5 => '',
	lga_s_5 => '',
	lga_q_5 => '',
####################### ACE # BM now ace's fields are filled from lga_sda (with parameters -3 -ie -d:4 -gdc_sc -swap, like former lga_sda_4_tr)
	ace_rms_ca => '',
	ace_rms_all => '',
#########################
	eqv_alignment => '',
	al0 => '',
	al4 => '',
	al4x => '',
	al0p => '',
	al4p => '',
	al4xp => '',
	eqv0 => '',
	eqv4 => '',
	eqvi => '',
	INDEX => 0,
########################### ERRORS CACA
	errors_caca => '',
	mean => '',
	standard_dev => '',
########################### MAMMOTH
	z_score_m => '',
	ln_e_m => '',
########################### DESCRIPTORS
	'n_aa_0' => '',
	'n_aa_4' => '',
	'n_aa_i' => '',
	'superposition_0' => '',
	'superposition_4' => '',
	'superposition_i' => '',
	'dal_0' => '',
	'dal_4' => '',
	'dal_i' => '',
########################### DALI
	'dali_z_score' => '',
	'dali_align_residues' => '',
	'dali_rmsd' => '',
########################### OTHER
	'gdt_4_z_score' => '',
	'al0p_5_z_score' => '',

	'gdt_4_z_score_server' => '',
	'al0p_5_z_score_server' => '',

	'gdt_4_z_score_all' => '',
	'al0p_5_z_score_all' => '',

	'gdt_4_z_score_server_all' => '',
	'al0p_5_z_score_server_all' => '',

	'gdt_ha_4_z_score_server' => '',
	'gdt_ha_4_z_score_all' => '',

	lga_4_tr =>'', # gdc_sc
	gdc_all =>'',
########################### PROSA
	prs_z_comb =>'',

########################### MOLPROBITY
	mp_rot_out =>'',
	mp_ram_out =>'',
	mp_ram_fv =>'',
	mp_clash =>'',
	mp_score =>'',
############################ lDDT
	lddt =>'',
############################ CAD score
	cad_aa => '',
#	cad_am => '',
#	cad_as => '',
#       cad_ma => '',
#       cad_mm => '',
#       cad_ms => '',
#       cad_sa => '',
#       cad_sm => '',
        cad_ss => '',
############################# SPHERE GRINDER LVR
	sg_lvr_6_2 => '',
	sg_lvr_6_2_a => '',
	sg_py => '',
############################# RPF
	rpf => '',
############################# BK's scores
	codm => '',
	codm_ratio => '',
	dfm => '',
	handedness => '',
	handedness_ratio => '',
############################ Grishin's scores (and related)
	sov => '',
	ce => '',
	ce_p_align => '',
	qcs => '',
	conts => '',
	mammoth_p_align =>'',
	contsintra => '',
	dali_n_align => '',
	dali_raw => '',
########################### TM align
	tm_align => '',
	tm_n_align => '',
########################### TM score
        tm_score => '',
########################### FlexE
	flexe => '',
########################### Rf/sigma
	rf => '',
########################### QSE - quality self estimate
	qse => '',
########################### RDC, DP - from Gay Montelione
	rdc1 => '',
	rdc2 => '',
	dp => '',
    );
############ LGA SUMMARY RESULTS EXAMPLE
## #CA            N1   N2   DIST      N    RMSD    GDT_TS    LGA_S3     LGA_Q
## SUMMARY(GDT)   82   83    4.0     79    1.26    86.446    91.767     5.817
#                 [3]                [6]    [7]      [8]         [9]      [10]
#                  n1_x               nres_x rmsd_x   seq_id    lga_s_x   lga_q_x

    return %model;
}

sub is_results_table_column {
my ($self, $colunm_name) = @_;
my $result = 0;
if(	$colunm_name eq 'id' ||
	$colunm_name eq 'predictions_id' ||
	#$colunm_name eq 'domains_id' ||
	$colunm_name eq 'date' ||
	########################## file name
	#$colunm_name eq 'target' ||
	$colunm_name eq 'parent' ||
	$colunm_name eq 'domain' ||
	$colunm_name eq 'targ_variant' ||
	########################### results
	####################### LGA 4
	################# SUMMARY
	$colunm_name eq 'n1_4' ||
	$colunm_name eq 'n2_4' ||
	$colunm_name eq 'dist_4' ||
	$colunm_name eq 'nres_4' ||
	$colunm_name eq 'rmsd_4' ||
	$colunm_name eq 'gdt_ts_4' ||
	$colunm_name eq 'lga_s_4' ||
	$colunm_name eq 'lga_q_4' ||
	$colunm_name eq 'gdt_ca_percentage_4' ||
	$colunm_name eq 'alignment_4' ||
	####################### LGA 5
	################# SUMMARY
	$colunm_name eq 'n1_5' ||
	$colunm_name eq 'n2_5' ||
	$colunm_name eq 'dist_5' ||
	$colunm_name eq 'nres_5' ||
	$colunm_name eq 'rmsd_5' ||
	$colunm_name eq 'gdt_ts_5' ||
	$colunm_name eq 'lga_s_5' ||
	$colunm_name eq 'lga_q_5' ||
	$colunm_name eq 'asgn_4' ||
	$colunm_name eq 'np_p_4' ||
	$colunm_name eq 'gdt_ha_4' ||
	########################### ACE
	$colunm_name eq 'ace_rms_ca' ||
	$colunm_name eq 'ace_rms_all' ||
	$colunm_name eq 'eqv_alignment' ||
	$colunm_name eq 'al0' ||
	$colunm_name eq 'al4' ||
	$colunm_name eq 'al4x' ||
	$colunm_name eq 'al0p' ||
	$colunm_name eq 'al4p' ||
	$colunm_name eq 'al4xp' ||
	$colunm_name eq 'eqv0' ||
	$colunm_name eq 'eqv4' ||
	$colunm_name eq 'eqvi' ||
	########################### Errors CACA
	$colunm_name eq 'errors_caca' ||
	$colunm_name eq 'mean' ||
	$colunm_name eq 'standard_dev' ||
	########################### MAMMOTH
	$colunm_name eq 'z_score_m' ||
	$colunm_name eq 'ln_e_m'    ||
	########################### DESCRIPTORS
	$colunm_name eq 'n_aa_0' ||
	$colunm_name eq 'n_aa_4' ||
	$colunm_name eq 'n_aa_i' ||
	$colunm_name eq 'superposition_0' ||
	$colunm_name eq 'superposition_4' ||
	$colunm_name eq 'superposition_i' ||
	$colunm_name eq 'dal_0' ||
	$colunm_name eq 'dal_4' ||
	$colunm_name eq 'dal_i' ||
########################### DALI
	$colunm_name eq 'dali_z_score' ||
	$colunm_name eq 'dali_align_residues' ||
	$colunm_name eq 'dali_rmsd' ||
########################### OTHER
	$colunm_name eq 'gdt_4_z_score' ||
	$colunm_name eq 'al0p_5_z_score' ||
	$colunm_name eq 'gdt_4_z_score_server' ||
	$colunm_name eq 'al0p_5_z_score_server' ||

	$colunm_name eq 'gdt_4_z_score_all' ||
	$colunm_name eq 'al0p_5_z_score_all' ||
	$colunm_name eq 'gdt_4_z_score_server_all' ||
	$colunm_name eq 'al0p_5_z_score_server_all' ||
	$colunm_name eq 'gdt_ha_4_z_score_server' ||
	$colunm_name eq 'gdt_ha_4_z_score_all' ||
	$colunm_name eq 'lga_4_tr' ||
        $colunm_name eq 'gdc_all' ||
########################### PROSA
	$colunm_name eq 'prs_z_comb' ||
########################### MOLPROBITY
	$colunm_name eq 'mp_rot_out' ||
	$colunm_name eq 'mp_ram_out' ||
	$colunm_name eq 'mp_ram_fv' ||
	$colunm_name eq 'mp_clash' ||
	$colunm_name eq 'mp_score' ||
########################### lDDT
	$colunm_name eq 'lddt' ||
########################### CAD score
	$colunm_name eq 'cad_aa' ||
#	$colunm_name eq 'cad_am' ||
#	$colunm_name eq 'cad_as' ||
#        $colunm_name eq 'cad_ma' ||
#        $colunm_name eq 'cad_mm' ||
#        $colunm_name eq 'cad_ms' ||
#        $colunm_name eq 'cad_sa' ||
#        $colunm_name eq 'cad_sm' ||
        $colunm_name eq 'cad_ss' ||
########################### SPHERE GRINDER LVR
        $colunm_name eq 'sg_lvr_6_2' ||
	$colunm_name eq 'sg_lvr_6_2_a' ||
	$colunm_name eq 'sg_py' ||
########################### RPF
	$colunm_name eq 'rpf' ||
########################### Bk's scores
        $colunm_name eq 'codm' ||
        $colunm_name eq 'codm_ratio' || 
        $colunm_name eq 'dfm' ||
        $colunm_name eq 'handedness' ||
        $colunm_name eq 'handedness_ratio' ||
############################ Grishin's scores (and related)
        $colunm_name eq 'sov' ||
        $colunm_name eq 'ce' ||
        $colunm_name eq 'ce_p_align' ||
        $colunm_name eq 'qcs' ||
        $colunm_name eq 'conts' ||
        $colunm_name eq 'mammoth_p_align' ||
	$colunm_name eq 'contsintra' ||
	$colunm_name eq 'dali_n_align' ||
	$colunm_name eq 'dali_raw' ||
############################ TM score
	$colunm_name eq 'tm_align' ||
	$colunm_name eq 'tm_n_align' ||
############################ TM score
        $colunm_name eq 'tm_score' ||
############################ FlexE
	$colunm_name eq 'flexe' ||
############################ Rf/sigma
        $colunm_name eq 'rf' ||
	$colunm_name eq 'qse' ||
############################ RDC, DP
	$colunm_name eq 'rdc1' ||
	$colunm_name eq 'rdc2' ||
	$colunm_name eq 'dp'
  ) {
	$result = 1;
	}
return $result;
}

sub exist {
    my ($self, $id) = @_;
    my $result = 0;

    my $query = sprintf("SELECT COUNT(id) FROM casp13.results WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my ($count) = $sth->fetchrow_array();
        $result = ($count == 1) ? 1 : 0;
    }
    return $result;
}

sub exist_by_parameters {
    my ($self, %model) = @_;
    my $result = 0;
    my $query = sprintf("SELECT re.id FROM casp13.results re JOIN casp13.predictions pr ON  (re.predictions_id = pr.id)
	JOIN casp13.groups gr ON (pr.groups_id = gr.id)
	WHERE pr.target=\'%s\' and pr.pfrmat=\'%s\' and  pr.model=%d and gr.code=%d and re.parent=%d and re.domain=%d and re.targ_variant=%d",
      	$model{TARGET}, $model{PFRMAT}, $model{MODEL}, $model{CODE}, ($model{parent} eq '')?0:$model{parent}, ($model{domain} eq '')?0:$model{domain}, ($model{targ_variant} eq '')?0:$model{targ_variant});
    my $sth = $self->{_database}->query($query);
    #print $query ."\n";

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

    return $result;
}

sub exist_mvsm {
    my ($self, $id_1, $id_2) = @_;
    my $result = 0;
    my $query = sprintf("SELECT id FROM casp13.results_mvsm WHERE (result1_id=\'%s\' and result2_id=\'%s\')",$id_1, $id_2);
    my $sth = $self->{_database}->query($query);
    #print $query ."\n";

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

    return $result;
}

sub is_uploading {
    my ($self, $target_name) = @_;
    my $result = 0;
    my $query = sprintf("SELECT gr.id FROM casp13.results re JOIN casp13.predictions pr ON  (re.predictions_id = pr.id)
	JOIN casp13.targets gr ON (pr.target_name = gr.name) WHERE pr.target=\'%s\' ",
      	$target_name);
    my $sth = $self->{_database}->query($query);
    #print $query ."\n";

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

    return $result;
}

sub get_predictions_id {
    my ($self, %model) = @_;
    my $result = '';
    my $query = sprintf("SELECT pr.id FROM casp13.predictions pr JOIN casp13.groups gr ON (pr.groups_id = gr.id)
	WHERE pr.target=\'%s\' and pr.pfrmat=\'%s\' and  pr.model=%d and gr.code=%d",
      	$model{TARGET}, $model{PFRMAT}, $model{MODEL}, $model{CODE});
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my ($id) = $sth->fetchrow_array();
        $result = $id;
    }
    return $result;
}

sub update_results {
    my ($self, %model) = @_;

    my $result = 0;
    my $set_query = '';

    while ( my ($key, $value) = each(%model) ) {
	if($key eq 'id' || $value eq '' || !defined($value)) {
		#print "NE---$key---------->$value<----------\n";
	}else{
		#list of columns which not updated
		if($self->is_results_table_column($key) == 1) {
			$set_query .= sprintf(" %s = \'%s\', ", $key, $value);
		}
	}
    }

    return $result if(! $self->exist( $model{id}));
    my $query = sprintf("UPDATE casp13.results SET %s id=id WHERE (id = %d)", $set_query,  $model{id});
	print "------------------------UPDATE: $model{id} \n";
	print $query . "\n";

    my $sth = $self->{_database}->query($query);

    if(defined($sth)) {
        $result = 1;
    }

    return $result;
}

sub add {
    my ($self, %model) = @_;
    my $result = 0;

    my $column_names = '';
    my $column_values = '';
    my $values_count = 0;

    while ( my ($key, $value) = each(%model) ) {
	if($key eq 'id' || $key eq 'date' || $value eq '' || !defined($value)) {
		#print "NE---$key---------->$value<----------\n";
	}else{
		if($self->is_results_table_column($key)==1) {
			$column_names .= sprintf(" %s%s ",($values_count == 0)?'':',', $key);
			$column_values .= sprintf(" %s\'%s\' ",($values_count == 0)?'':',', $value);;
			$values_count++;
		}
	}
    }

    my $query = sprintf("INSERT INTO casp13.results ( %s ) VALUES ( %s ) RETURNING id", $column_names, $column_values);

	my $tmp = $query;

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        #my $query = sprintf("SELECT MAX(id) FROM casp13.results");
        #my $sth = $self->{_database}->query($query);
        # add logger
        ($result) = $sth->fetchrow_array();
    }
	print "---------------------ADD: $result";
	if ($result == 0) {
	print "\n" . $tmp . "\n";
	}

    return $result;
}

sub delete {
    my ($self, $id) = @_;
    my $result = 0;
    return $result if(! $self->exist($id));
    my $query = sprintf("DELETE FROM casp13.results WHERE (id = %d)", $id);
    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        $result = defined($sth) ? 1 : 0;
    }
    return $result;
}

sub get_results {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};
    #my $view_targets = $params->{view_targets};
    #my $view = $params->{view};
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    #foreach my $parameter (keys %{$params}) {
    #	print $parameter . " => " . $params->{$parameter}. "<br>";
    #}
    $field = (!defined($field) || ($field eq '')) ? 're.gdt_ts_4' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;

    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
	while ($field =~ s/\;/ $order,/ || $count_to_replace >20){
	$count_to_replace ++;
    }
    ####
    my $domain = 0;
    my $sub_query = "";
    if(defined($params->{target}) && ($params->{target} ne '')) {
	if ($params->{target} =~ /^R/ && $params->{target} !~ /^R0957s2/) {
	    $sub_query .= sprintf(" and pr.target=\'%s\' ", $params->{target});
	    $domain = 0;
	} elsif ($params->{target} =~ /^N0981-D/) {
            $sub_query .= sprintf(" and pr.target=\'%s\' ", $params->{target});
            $domain = 0;
        } elsif ($params->{target} =~ /^(\S+)-D(\S+)/) {
	    $sub_query .= sprintf(" and pr.target=\'%s\' ", $1);
	    $domain = $2;
	} else {
	    $sub_query .= sprintf(" and pr.target=\'%s\' ", $params->{target});
	    #$sub_query .= sprintf(" and re.domain=\'%s\' ", $domain);
	}

	## domain defenition
	if(defined($params->{domain}) && ($params->{domain} ne '') && $params->{target} !~ /^R/ ) {
	    $sub_query .= sprintf(" and re.domain=\'%s\' ", $params->{domain});
	} else{
	    $sub_query .= sprintf(" and re.domain=\'%s\' ", $domain);
	}
    }



    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
	$sub_query .= sprintf(" and pr.groups_id=\'%s\' ", $params->{groups_id});
    }

    my $multi_sort = "";
    if(defined($params->{multi_sort}) && ($params->{multi_sort} ne '')) {
	$multi_sort = $params->{multi_sort};
	#### DO MULTIPLE SORTING
	my $count_to_replace = 0;
	    while ($multi_sort =~ s/\;/ $order,/ || $count_to_replace >20){
	    $count_to_replace ++;
	}
	$field = "";
	$order = "";
    }

    if(defined($params->{groups_type}) && ($params->{groups_type} ne '')) {
	if($params->{groups_type} eq 'server') {
	    $sub_query .= sprintf(" and gr.type>0 ");
	} elsif ($params->{groups_type} eq 'human') {
	    $sub_query .= sprintf(" and gr.type=0 ");
	}
    }


    if(defined($params->{targets_list}) && ($params->{targets_list} ne '')) {


	my @targets = split(/,/, $params->{targets_list});
	$sub_query .= sprintf(" and (");

	for(my $i = 0; $i < scalar(@targets); $i++) {
	    my @target = split(/-/, $targets[$i]);

	    if ($i>0) {$sub_query .= " or ";}
	    $sub_query .= sprintf(" (tr.id=%s and re.domain=%s) ", $target[0], $target[1]);
	}

	$sub_query .= sprintf(") ");


    }

    if(defined($params->{groups_list}) && ($params->{groups_list} ne '')) {
	$sub_query .= sprintf(" and gr.id IN (%s) ", $params->{groups_list});
    }


    if(defined($params->{where}) && ($params->{where} ne '')) {
	$sub_query .= sprintf(" %s ", $params->{where});
    }

    if(defined($params->{results_id}) && ($params->{results_id} ne '')) {
	$sub_query .= sprintf(" and re.id=%s ", $params->{results_id});
    }



    if(defined($params->{model}) && ($params->{model} ne '') && ($params->{model} ne 'all')) {
	$sub_query .= sprintf(" and pr.model=\'%s\' ", (($params->{model} eq 'first')? '1': $params->{model})); #$params->{model});
    }

    if(defined($params->{access_type}) && ($params->{access_type} ne '') ) {
        $sub_query .= sprintf(" and rt.access_type=\'%s\' ", $params->{access_type});
	$sub_query .= sprintf(" and tr.cancellation_status=0 ");
    }

    my @results = ();

    my $query = sprintf("SELECT (pr.target || pr.pfrmat || to_char(gr.code,'FM000') || '_' || pr.model) as model_name, gr.id, pr.target, pr.pfrmat, gr.code, gr.name, pr.model, re.id, re.predictions_id, re.parent, re.domain, re.date, re.n1_5, re.n2_5, re.gdt_ts_4, re.rmsd_5, re.pc01, re.pc05, re.lga_s_5, re.ace_rms_ca, re.ace_rms_all, eqv0, eqv4, eqvi, eqv_alignment, al0, al4, al4x, al0p, al4p, al4xp, re.asgn_4, re.np_p_4, re.n1_4, re.n2_4, tr.id, re.rank_all, re.rank_model, re.gdt_ha_4, re.alignment_4, re.z_score_m, re.ln_e_m, re.errors_caca, re.mean, re.standard_dev,
	re.n_aa_0, re.n_aa_4, re.n_aa_i, re.superposition_0, re.superposition_4, re.superposition_i, re.dal_0, re.dal_4, re.dal_i,
	re.dali_z_score, re.dali_align_residues, re.dali_rmsd, re.gdt_4_z_score, re.al0p_5_z_score, re.gdt_4_z_score_server, re.al0p_5_z_score_server,
	gr.type, re.gdt_4_z_score_all, re.al0p_5_z_score_all, re.gdt_4_z_score_server_all, re.al0p_5_z_score_server_all, re.gdt_ha_4_z_score_server, re.lga_4_tr, re.gdc_all, re.prs_z_comb, re.mp_rot_out, re.mp_ram_out, re.mp_ram_fv, re.mp_clash, re.mp_score, re.lddt, re.cad_aa, re.cad_ss, re.sg_lvr_6_2, re.sg_lvr_6_2_a, re.rpf, 
	re.codm, re.codm_ratio, re.dfm, re.handedness, re.handedness_ratio, re.sov, re.ce, re.ce_p_align, re.qcs, re.conts, re.mammoth_p_align, re.tm_score, re.tm_n_align, re.contsintra, re.dali_n_align, re.dali_raw, re.flexe, re.qse, re.tm_align, re.sg_py, re.rdc1, re.rdc2, re.dp
	FROM casp13.results re
	JOIN casp13.predictions pr ON  (re.predictions_id = pr.id) JOIN casp13.targets tr ON  (pr.target = tr.name)
	JOIN casp13.groups gr ON (pr.groups_id = gr.id)
	LEFT JOIN casp13.results_targets rt on (tr.id=rt.targets_id and re.domain=rt.domains_index)

    WHERE 1=1 %s and n1_4>19  ORDER BY %s %s %s LIMIT 1200", $sub_query, $field, $order, $multi_sort);
    #WHERE 1=1 and n1_4>19 %s ORDER BY %s %s %s LIMIT 27000", $sub_query, $field, $order, $multi_sort);


    my $sth = $self->{_database}->query($query);
	#print "<br>sort: " . $multi_sort . "<br>";
    #print $query. "\n";
    #exit(0);


    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($model_name, $group_id, $target, $pfrmat, $code, $group_name, $model, $id, $predictions_id, $parent, $domain, $date, $n1_5, $n2_5, $gdt_ts_4, $rmsd_5, $pc01, $pc05, $lga_s_5, $ace_rms_ca, $ace_rms_all, $eqv0, $eqv4, $eqvi, $eqv_alignment, $al0, $al4, $al4x, $al0p, $al4p, $al4xp, $asgn_4, $np_p_4, $n1_4, $n2_4, $target_id, $rank_all, $rank_model, $gdt_ha_4, $alignment_4,$z_score_m, $ln_e_m, $errors_caca, $mean, $standard_dev, $n_aa_0, $n_aa_4, $n_aa_i, $superposition_0, $superposition_4,
		 $superposition_i, $dal_0, $dal_4, $dal_i, $dali_z_score, $dali_align_residues, $dali_rmsd, $gdt_4_z_score, $al0p_5_z_score, $gdt_4_z_score_server, $al0p_5_z_score_server, $group_type,
		 $gdt_4_z_score_all, $al0p_5_z_score_all, $gdt_4_z_score_server_all, $al0p_5_z_score_server_all, $gdt_ha_4_z_score_server,$lga_4_tr, $gdc_all, $prs_z_comb,$mp_rot_out,$mp_ram_out,$mp_ram_fv,$mp_clash,$mp_score, $lddt, $cad_aa, $cad_ss, $sg_lvr, $sg_lvr_a, $rpf, 
		$codm, $codm_ratio, $dfm, $handedness, $handedness_ratio, $sov, $ce, $ce_p_align, $qcs, $conts, $mammoth_p_align, $tm_score, $tm_n_align, $contsintra, $dali_n_align, $dali_raw, $flexe, $qse, $tm_align, $sg_py, $rdc1, $rdc2, $dp
		) = $sth->fetchrow_array()) {
			push(@results, {
				INDEX => $index,
				MODEL_NAME => $model_name,
				GROUP_ID => $group_id,
				TARGET => $target,
				PFRMAT => $pfrmat,
				CODE => sprintf("%03d",$code),
				GROUP_NAME => $group_name,
				MODEL => $model,
				ID => $id,
				PREDICTIONS_ID => $predictions_id,
				PARENT => $parent,
				DOMAIN => $domain,
				DATE => substr($date, 0, 10),
				N1_5 => $n1_5,
				N2_5 => $n2_5,
				GDT_TS_4 => sprintf("%.2f", $gdt_ts_4),
				RMSD_5 => sprintf("%.2f", $rmsd_5),
				PC01 => $pc01,
				PC05 => $pc05,
				LGA_S_5 => $lga_s_5,
				ACE_RMS_CA => $ace_rms_ca,
				ACE_RMS_ALL => $ace_rms_all,
				AL0 => $al0,
				AL4 => $al4,
				AL4X => $al4x,
				AL0P => sprintf("%.2f", $al0p),
				AL4P => sprintf("%.2f", $al4p),
				AL4XP => sprintf("%.2f", $al4xp),
				EQV0 => $eqv0,
				EQV4 => $eqv4,
				EQVi => $eqvi,
				EQV0P => sprintf("%.2f", ($n2_5 == 0) ? 0 : ($eqv0 / $n2_5) * 100),
				EQV4P => sprintf("%.2f", ($n2_5 == 0) ? 0 : ($eqv4 / $n2_5) * 100),
				EQViP => sprintf("%.2f", ($n2_5 == 0) ? 0 : ($eqvi / $n2_5) * 100),
				EQV_ALIGNMENT => $eqv_alignment,
				ASGN_4 => $asgn_4,
				NP_P_4 => sprintf("%.2f", $np_p_4),
				N1_4 => $n1_4,
				N2_4 => $n2_4,
				TARGET_ID => $target_id,
				RANK_ALL => $rank_all,
				RANK_MODEL => $rank_model,
				GDT_HA_4 => sprintf("%.2f", $gdt_ha_4),
				ALIGNMENT_4 => $alignment_4,
				Z_SCORE_M => sprintf("%.2f", $z_score_m),
				LN_E_M => sprintf("%.2f", $ln_e_m),
				ERRORS_CACA => $errors_caca,
				MEAN => $mean,
				STANDARD_DEV => $standard_dev,
				N_AA_0 => $n_aa_0,
				N_AA_4 => $n_aa_4,
				N_AA_I => $n_aa_i,
				SUPERPOSITION_0 => $superposition_0,
				SUPERPOSITION_4 => $superposition_4,
				SUPERPOSITION_I => $superposition_i,
				DAL_0 => sprintf("%.2f",$dal_0),
				DAL_4 => sprintf("%.2f",$dal_4),
				DAL_I => sprintf("%.2f",$dal_i),

				DAL_0_SCORE => sprintf("%.2f",   ($n2_4 == 0 && $n2_4 ne '') ? 0 : (($n_aa_0/$n2_4)*100)),
				DAL_4_SCORE => sprintf("%.2f",   ($n2_4 == 0 && $n2_4 ne '') ? 0 : (($n_aa_4/$n2_4)*100)),
				DAL_I_SCORE => sprintf("%.2f",   ($n2_4 == 0 && $n2_4 ne '') ? 0 : (($n_aa_i/$n2_4)*100)),

				MODEL_NAME => $self->build_model_name($target, $pfrmat, $code, $model, $parent, $domain),
				FULL_TARGET => $self->build_full_target_name($target, $domain),
				DALI_ZSCORE => $dali_z_score,
				DALI_ALIGN_RESIDUES => $dali_align_residues,
				DALI_RMSD => $dali_rmsd,
				GDT_Z_SCORE => sprintf("%.2f",$gdt_4_z_score),
				AL0P_Z_SCORE => sprintf("%.2f",$al0p_5_z_score),
				GDT_Z_SCORE_SERVER => sprintf("%.2f",$gdt_4_z_score_server),
				AL0P_Z_SCORE_SERVER => sprintf("%.2f",$al0p_5_z_score_server),
				GROUP_TYPE => $group_type,

				GDT_Z_SCORE_ALL => sprintf("%.2f",$gdt_4_z_score_all),
				AL0P_Z_SCORE_ALL => sprintf("%.2f",$al0p_5_z_score_all),
				GDT_Z_SCORE_SERVER_ALL => sprintf("%.2f",$gdt_4_z_score_server_all),
				AL0P_Z_SCORE_SERVER_ALL => sprintf("%.2f",$al0p_5_z_score_server_all),
				gdt_ha_4_z_score_server => sprintf("%.2f",$gdt_ha_4_z_score_server),
				gdt_sc => sprintf("%.2f",$lga_4_tr),
				gdc_all => sprintf("%.2f",$gdc_all),
				PRS_Z_COMB => sprintf("%.2f",$prs_z_comb),
				MP_SCORE => sprintf("%.2f",$mp_score),
				MP_CLASH => sprintf("%.2f",$mp_clash),
				MP_ROT_OUT => sprintf("%.2f",$mp_rot_out),
				MP_RAM_OUT => sprintf("%.2f",$mp_ram_out),
				MP_RAM_FV => sprintf("%.2f",$mp_ram_fv),
				LDDT => sprintf("%.2f",$lddt),
				CAD_AA => sprintf("%.2f",$cad_aa),
				CAD_SS => sprintf("%.2f",$cad_ss),
				SG_LVR => sprintf("%.2f",$sg_lvr),
				SG_LVR_A => sprintf("%.2f",$sg_lvr_a),
				SG_PY => (defined($sg_py) && $sg_py ne '' ? sprintf("%.2f", $sg_py) : '-'),
				RPF => sprintf("%.2f",$rpf),
	
				CODM => sprintf("%.2f",$codm),
				CODM_RATIO => sprintf("%.2f",$codm_ratio),
				DFM => sprintf("%.2f",$dfm),
				HANDEDNESS => sprintf("%.2f", $handedness),
				HANDEDNESS_RATIO => sprintf("%.2f", $handedness_ratio),

				SOV => sprintf("%.2f", $sov),
				CE => sprintf("%.2f", $ce),
				CE_P_ALIGN => sprintf("%.2f", $ce_p_align),
				QCS => sprintf("%.2f", $qcs),
				CONTS => sprintf("%.2f", $conts),
				MAMMOTH_P_ALIGN => sprintf("%.2f", $mammoth_p_align),
				TM_SCORE => sprintf("%.2f", $tm_score),

				TM_N_ALIGN => sprintf("%.2f",$tm_n_align),
				CONTSINTRA => sprintf("%.2f",$contsintra),
				DALI_N_ALIGN => sprintf("%.2f",$dali_n_align),
				DALI_RAW => sprintf("%.2f",$dali_raw),
				FLEXE => sprintf("%.2f",$flexe),
				QSE => sprintf("%.2f",$qse),

				TM_ALIGN => sprintf("%.2f",$tm_align),
				
				RDC1 => (defined($rdc1) && $rdc1 ne '' ? sprintf("%.2f", $rdc1) : '-'),
				RDC2 => (defined($rdc2) && $rdc2 ne '' ? sprintf("%.2f", $rdc2) : '-'),
				DP => (defined($dp) && $dp ne '' ? sprintf("%.2f", $dp) : '-'),
				}
			);



			$index++;
        }
    }
    return @results;
}

sub get_mvsm_results {
	my ($self, $params) = @_;
	my $field = $params->{field};
	my $order = $params->{order};
	$field = (!defined($field) || ($field eq '')) ? 're.id' : $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 ++;
	}
	####

#print "Content-Type: text/html; charset=ISO-8859-1\n\n";

	my $sub_query = "";
	if(defined($params->{result_id}) && ($params->{result_id} ne '') && $params->{result_id} != 0) {
		$sub_query .= sprintf(" and re_m.result2_id=\'%s\' ", $params->{result_id});
	} else {
		return 0;
	}

	my @results = ();
	my $index = 1;
################# first row the should be result which we are clustering
	my $query = sprintf("SELECT pr.target, pr.pfrmat, gr.code, gr.type, pr.model, re.id, re.predictions_id, re.parent, re.domain, re.gdt_ts_4, re.pc01, re.pc05, re.lga_s_5, re.ace_rms_ca, re.ace_rms_all, pr.date FROM casp13.results re
	JOIN casp13.predictions pr ON  (re.predictions_id = pr.id)
	JOIN casp13.groups gr ON (pr.groups_id = gr.id)
	WHERE 1=1 and re.id=%s ", $params->{result_id});

	my $sth = $self->{_database}->query($query);

	if(defined($sth) && ($sth->rows() > 0)) {
		while(my($target, $pfrmat, $code, $gr_type, $model, $id, $predictions_id, $parent, $domain, $gdt_ts_4, $pc01, $pc05, $lga_s_5, $ace_rms_ca, $ace_rms_all, $date) = $sth->fetchrow_array()) {
			push(@results,
				{INDEX => $index,
				TARGET => $target,
				PFRMAT => $pfrmat,
				CODE => sprintf("%03d",$code),
				is_server_only => (defined($gr_type)&&($gr_type == 1 || $gr_type == 2) ? 1 : 0),
				MODEL => $model,
				id => $id,
				predictions_id => $predictions_id,
				parent => $parent,
				domain => $domain,
				date => substr($date, 0, 10),
				mvsm_gdt_ts => -1,
				gdt_ts_4 => $gdt_ts_4,
				pc01 => $pc01,
				pc05 => $pc05,
				lga_s_5 => $lga_s_5,
				ace_rms_ca => $ace_rms_ca,
				ace_rms_all => $ace_rms_all,
				mvsm_rms => -1,
				bacground => '#0FFF00',
				date => (defined($date) ? (( substr($date, 0, 10) . "  " . substr($date, 11, 8) )) : '-'),
				MODEL_NAME => $self->build_model_name($target, $pfrmat, $code, $model, $parent, $domain),
				FULL_TARGET => $self->build_full_target_name($target, $domain)
				});
			$index++;
		}
	}

#################

	$query = sprintf("SELECT pr.target, pr.pfrmat, gr.code,gr.type, pr.model, re.id, re.predictions_id, re.parent, re.domain, re.gdt_ts_4, re.pc01, re.pc05, re.lga_s_5, re.ace_rms_ca, re.ace_rms_all, re_m.rms, re_m.gdt_ts, pr.date, re.n1_4, re.n1_5 FROM casp13.results re
	JOIN casp13.predictions pr ON  (re.predictions_id = pr.id)
	JOIN casp13.groups gr ON (pr.groups_id = gr.id)
	JOIN casp13.results_mvsm re_m ON (re_m.result1_id = re.id )
	WHERE 1=1 and re_m.rms < %s  %s ORDER BY %s %s ", $params->{pc}, $sub_query, $field, $order);

	$sth = $self->{_database}->query($query);


	if(defined($sth) && ($sth->rows() > 0)) {

		while(my($target, $pfrmat, $code, $gr_type, $model, $id, $predictions_id, $parent, $domain, $gdt_ts_4, $pc01, $pc05, $lga_s_5, $ace_rms_ca, $ace_rms_all, $mvsm_rms, $mvsm_gdt_ts, $date) = $sth->fetchrow_array()) {
			push(@results,
				{INDEX => $index,
				TARGET => $target,
				PFRMAT => $pfrmat,
				CODE => sprintf("%03d",$code),
				is_server_only => (defined($gr_type)&&($gr_type == 1 || $gr_type == 2) ? 1 : 0),
				MODEL => $model,
				id => $id,
				predictions_id => $predictions_id,
				parent => $parent,
				domain => $domain,
				date => substr($date, 0, 10),
				mvsm_gdt_ts => $mvsm_gdt_ts,
				gdt_ts_4 => $gdt_ts_4,
				pc01 => $pc01,
				pc05 => $pc05,
				lga_s_5 => $lga_s_5,
				ace_rms_ca => $ace_rms_ca,
				ace_rms_all => $ace_rms_all,
				mvsm_rms => $mvsm_rms,
				bacground => '#ffffff',
				date => (defined($date) ? (( substr($date, 0, 10) . "  " . substr($date, 11, 8) )) : '-'),
				MODEL_NAME => $self->build_model_name($target, $pfrmat, $code, $model, $parent, $domain),
				FULL_TARGET => $self->build_full_target_name($target, $domain)
				});
			$index++;
		}
	}
	return @results;
}

sub get_targets_results {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};
    $field = (!defined($field) || ($field eq '')) ? 'tr.name' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;

    $field = 'tr.name';
    $order = 'ASC';


    #### DO MULTIPLE SORTING
    my $count_to_replace = 0;
    while ($field =~ s/, / $order,/ || $count_to_replace >20){
    $count_to_replace ++;
    }
    ####
    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";
    my $sub_query = "";
    #TODO to REMOVE!!! if(defined($params->{result_id}) && ($params->{result_id} ne '') && $params->{result_id} != 0) {
    #	$sub_query .= sprintf(" and re_m.result1_id=\'%s\' ", $params->{result_id});
    #} else {
    #	return 0;
    #}

    my @results = ();
    my $index = 1;
    ###### SQL: JOIN ALL DATA
    #select rt.targets_id, rt.domains_index, dm.index, * from casp13.results_targets rt
    #JOIN casp13.targets tr ON (rt.targets_id = tr.id)
    #JOIN casp13.submitted_sequences sb ON (tr.submitted_sequences_id = sb.id)
    #LEFT OUTER JOIN casp13.domains dm on
    #(dm.targets_id = rt.targets_id and dm.index = rt.domains_index)
    ######

    my $query = sprintf("select tr.id, tr.name, rt.domains_real_index from casp13.results_targets rt
    JOIN casp13.targets tr ON (rt.targets_id = tr.id)
    LEFT OUTER JOIN casp13.domains dm ON (dm.targets_id = rt.targets_id and dm.index = rt.domains_index)
    WHERE rt.is_public=1 %s ORDER BY %s %s ", $sub_query, $field, $order);

#print $query;
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my($target_id, $target, $domains_real_index) = $sth->fetchrow_array()) {
	    push(@results,
		{INDEX => $index,
		TARGET_ID =>$target_id,
		DOMAIN => $domains_real_index,
		TARGET => $target,
		NAME => sprintf("%s%s", $target,(($domains_real_index eq '0')?'':('_D'.$domains_real_index)))
		});
	    $index++;
	}
    }
    #################
    return @results;
}

sub info {
    my ($self, $id) = @_;
    %{$self->{_model}} = $self->get_new_model();
    return () if(! $self->exist($id));

    my $query = sprintf("SELECT re.id, re.predictions_id, re.domain, re.date, re.parent, pr.target, pr.model, pr.pfrmat, g.code FROM casp13.results re JOIN casp13.predictions pr ON (pr.id = re.predictions_id) JOIN casp13.groups g ON (pr.groups_id = g.id) WHERE (re.id = %d)", $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
		(
		${$self->{_model}}{ID},
		${$self->{_model}}{PREDICTION_ID},
		${$self->{_model}}{DOMAIN},
		${$self->{_model}}{DATE},
		${$self->{_model}}{PARENT},
		${$self->{_model}}{TARGET},
		${$self->{_model}}{MODEL},
		${$self->{_model}}{PFRMAT},
		${$self->{_model}}{CODE},
		) = $sth->fetchrow_array();

        ${$self->{_model}}{DATE} = substr(${$self->{_model}}{DATE}, 0, 10);
    }

    return %{$self->{_model}};
}

sub model_name {
    my ($self, $id) = @_;

    my %info = $self->info($id);

	my $result = $self->build_model_name($info{TARGET}, $info{PFRMAT}, $info{CODE}, $info{MODEL}, $info{PARENT}, $info{DOMAIN});

    return $result;
}

sub build_model_name {
    my ($self, $target_name, $pfrmat, $group_code, $index, $parent, $domain_index) = @_;

    my $result = '';
       if (defined($target_name) && defined($pfrmat) && defined( $group_code) && defined($index)){
    $result = sprintf("%s%s%03d_%d%s%s", $target_name, $pfrmat, $group_code, $index, ((defined($parent) && ($parent > 0)) ? sprintf("\_%d", $parent) : ''), ((defined($domain_index) && ($domain_index > 0)) ? sprintf("-D%d", $domain_index) : ''));
    }
    #$result = sprintf("%s%s%03d_%d%s%s", $target_name, $pfrmat, $group_code, $index, (($parent > 0) ? sprintf("\_%d", $parent) : ''), (($domain_index > 0) ? sprintf("-D%d", $domain_index) : ''));

    return $result;
}

sub update_results_mvsm {
    my ($self, $id, $id_1, $id_2, $rms, $gdt_ts) = @_;
    my $result = 0;

    return $result if($id);
    my $query = sprintf("UPDATE casp13.results_mvsm SET result1_id=%s, result2_id=%s, rms=%s, gdt_ts=%s WHERE (id = %d)", $id_1, $id_2, $rms, $gdt_ts, $id);
    my $sth = $self->{_database}->query($query);

    if(defined($sth)) {
        $result = 1;
    }
    print "----- UPDATE $id - $id_1 - $id_2 - $rms";
    return $result;
}

sub add_mvsm {
    my ($self, $id_1, $id_2, $rms, $gdt_ts) = @_;
    my $result = 0;

    my $query = sprintf("INSERT INTO casp13.results_mvsm ( result1_id, result2_id, rms , gdt_ts) VALUES ( %s, %s, %s, %s)", $id_1, $id_2, $rms, $gdt_ts);

    my $sth = $self->{_database}->query($query);
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.results_mvsm");
        my $sth = $self->{_database}->query($query);
        # add logger
        ($result) = $sth->fetchrow_array();
    }
    print "----- ADD: $result - $id_1 - $id_2 - $rms - $gdt_ts";
    return $result;
}

sub get_gdt_results {
    my ($self, $target_id, $full_target_name) = @_;

    my @results = ();

	# stupid detour because table predictions doesn't have targets_id field for some reason!
	# comment: yeah stupied reason!!! and where is domain definition???
	my $targets_manager = new TargetsManager();
	my $name = $targets_manager->name($target_id);


    #print "Content-Type: text/html; charset=ISO-8859-1\n\n";

    my $target_name = $self->get_target_from_full_target_name($full_target_name);
    #added domain definition
    my $domain = $self->get_domain_from_full_target_name($full_target_name);

    my $query = sprintf("SELECT pr.target, pr.pfrmat, gr.code, gr.id, pr.model, re.id, re.predictions_id, re.parent, re.domain, re.gdt_ca_percentage_4 FROM casp13.results re
	    JOIN casp13.predictions pr ON (re.predictions_id = pr.id) JOIN casp13.groups gr ON (pr.groups_id = gr.id) WHERE pr.target='%s' and re.domain=%d ORDER BY pr.pfrmat ASC, gr.code ASC, pr.model DESC", $target_name, $domain);

    #print $query;

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($target, $pfrmat, $code, $groups_id, $model, $id, $predictions_id, $parent, $domain, $gdt_ca_percentage_4) = $sth->fetchrow_array()) {
	    push(@results, {
		INDEX => $index,
		TARGET => $target,
		PFRMAT => $pfrmat,
		CODE => sprintf("%03d", $code),
		GROUP_ID => $groups_id,
		MODEL => $model,
		PARENT => $parent,
		DOMAIN => $domain,
		GDT_CA_PERCENTAGE_4 => $gdt_ca_percentage_4}
	    );
	    $index++;
        }
    }

    return @results;
}

sub groups_per_target {
	my ($self, $target_id, $full_target_name) = @_;

    my @results = ();

    my $target_name = "";

    if ($target_id != 0) {
	# stupid detour because table predictions doesn't have targets_id field for some reason!
	# comment: yeah stupied reason!!! and where is domain definition???
	my $targets_manager = new TargetsManager();
	my $name = $targets_manager->name($target_id);
    } else {
	$target_name = $self->get_target_from_full_target_name($full_target_name);
    }

    my $query = sprintf("SELECT DISTINCT(p.groups_id), g.type, g.name, g.code FROM casp13.predictions p JOIN casp13.groups g ON (p.groups_id = g.id) WHERE ((p.pfrmat='TS') OR (p.pfrmat='AL')) AND (p.target='%s') ORDER BY g.code ASC", $target_name);
    #print "<br>". $query;
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($groups_id, $type, $name, $code) = $sth->fetchrow_array()) {
			push(@results, {
				INDEX => $index,
				GROUP_ID => $groups_id,
				TYPE => $type,
				CODE => sprintf("%03d", $code),
				NAME => $name}
			);
			$index++;
        }
    }

    return @results;
}

sub groups_list {
    my ($self, $params) = @_;
    my @results = ();
    my $sub_query = "";

    my $field = $params->{field};
    my $order = $params->{order};
    $field = (!defined($field) || ($field eq '')) ? 'gr.name' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;


    if(defined($params->{target_id}) && ($params->{target_id} ne '') ) {
	my $targets_manager = new TargetsManager();
	my $name = $targets_manager->name($params->{target_id});
	$sub_query .= sprintf(" and pr.target = \'%s\' ", $name);
    }

    if(defined($params->{target}) && ($params->{target} ne '') ) {
	my $target_name = $self->get_target_from_full_target_name($params->{target});
	$sub_query .= sprintf(" and pr.target = \'%s\' ", $target_name);
    }

    if(defined($params->{where}) && ($params->{where} ne '') ) {
	$sub_query .= sprintf(" %s ", $params->{where});
    }

    if(defined($params->{groups_id}) && ($params->{groups_id} ne '') ) {
	$sub_query .= sprintf(" and gr.id =%s ", $params->{groups_id});
    }



    if(defined($params->{pfrmat}) && ($params->{pfrmat} ne '') ) {
	if($params->{pfrmat} eq "3D") {
	    $sub_query .= sprintf(" and (pr.pfrmat = \'TS\' or pr.pfrmat = \'AL\') ");
	} else {
	    $sub_query .= sprintf(" and pr.pfrmat = \'%s\' ", $params->{pfrmat});
	}
    }


    my $query = sprintf("SELECT DISTINCT(pr.groups_id), gr.type, gr.name, gr.code FROM casp13.predictions pr JOIN casp13.groups gr ON (pr.groups_id = gr.id) WHERE 1=1 %s ORDER BY %s %s ", $sub_query, $field, $order);
    #print "<br>". $query;
    my $sth = $self->{_database}->query($query);

    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($groups_id, $type, $name, $code) = $sth->fetchrow_array()) {
	    push(@results, {
		    INDEX => $index,
		    GROUP_ID => $groups_id, ## I AM NOT SURE IF WE USE IT
		    ID => $groups_id,
		    TYPE => $type,
		    CODE => sprintf("%03d", $code),
		    NAME => $name}
	    );
	    $index++;
        }
    }

    return @results;
}

sub get_domain_from_full_target_name {
    my ($self, $target_name) = @_;
    my $result = 0;
    if(defined($target_name) && ($target_name ne '')) {
	if ($target_name =~ /^R/ && $target_name !~ m/^R0957s2/){
		$result = 0; 
	} elsif ($target_name =~ /^N0981-D/){
                $result = 0;
        } elsif ($target_name =~ /^(\S+)-D(\S+)/) {
		$result = $2;
	}
    }
    return $result;
}

sub get_target_from_full_target_name {
    my ($self, $target_name) = @_;
    my $result = $target_name;
    if(defined($target_name) && ($target_name ne '')) {
	if ($target_name =~ /^R/ && $target_name !~ m/^R0957s2/){
                $result = $target_name;  
        } elsif ($target_name =~ /^N0981-D/){
                $result = $target_name;
        } elsif ($target_name =~ /^(\S+)-D(\S+)/) {
		$result = $1;
	}
    }
    return $result;
}

sub build_full_target_name {
    my ($self, $target_name, $domain_index) = @_;
    my $result = '';
    $result = sprintf("%s%s", $target_name, (($domain_index > 0) ? sprintf("-D%d", $domain_index) : ''));
    return $result;
}

sub ToString {
    my ($self, $PS, %model, $is_return) = @_;
    # $PS -   print separator could be "\n" for text or "<br>" for HTML
    my $str = '';
    while ( my ($key, $value) = each(%model) ) {
	if(defined($is_return) && $is_return == 1) {
        	$str.= sprintf("%s => %s  %s ", $key, $value, $PS);
	} else {
		print sprintf("%s => %s  %s ", $key, $value, $PS);
	}

    }
    #TODO TO REMOVE: OLD SOURSE CODE
    #print sprintf("ID: %s $PS PREDICTION_ID: %s $PS DOMAIN_ID: %s $PS DATE: %s", $model{ID},  $model{PREDICTION_ID}, $model{DOMAIN_ID}, $model{DATE});
    #print sprintf("$PS TARGET: %s $PS PFRMAT: %s $PS CODE: %s $PS MODEL: %s $PS PARENT: %s $PS DOMAIN: %s $PS", $model{TARGET},  $model{PFRMAT}, $model{CODE}, $model{MODEL}, $model{PARENT}, $model{DOMAIN});
    return $str;
}

##########################
##########################
##########just for meeting

sub get_groups {
    my ($self, $params) = @_;
    my $field = $params->{field};
    my $order = $params->{order};
    $field = (!defined($field) || ($field eq '')) ? 'gr.code' : $field;
    $order = (!defined($order) || ($order eq '')) ? 'ASC' : $order;

    my $sub_query = "";

    if(defined($params->{is_server}) && ($params->{is_server} ne '') && $params->{is_server} eq 1) {
    	$sub_query .= sprintf(" and gr.type > 0");
    }

    my @results = ();
    my $index = 1;

    my $query = sprintf("select gr.id, gr.code, gr.name from casp13.groups gr where 1=1 %s ORDER BY %s %s ", $sub_query, $field, $order);

print $query;
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my($id, $code, $name) = $sth->fetchrow_array()) {
	    push(@results,
		{INDEX => $index,
		ID =>$id,
		CODE =>sprintf('%03d', $code),
		NAME => $name
		});
	    $index++;
	}
    }
    #################
    return @results;
}

sub get_domains {
    my ($self, $params) = @_;

    my $sub_query = "";

    if(defined($params->{domain_format}) && ($params->{domain_format} ne '')) {

	if($params->{domain_format} eq "TBM") {
	    $sub_query .= sprintf(" and EXISTS(select id from casp13.domains dom where dom.index = re.domain and  dom.targets_id = tr.id and domain_classifications_id IN (1,2,4)) ");
	} elsif($params->{domain_format} eq "FM") {
	    $sub_query .= sprintf(" and EXISTS(select id from casp13.domains dom where dom.index = re.domain and  dom.targets_id = tr.id and domain_classifications_id IN (3,4)) ");
	}
    }


    if(defined($params->{is_server_only}) && ($params->{is_server_only} ne '')) {
	$sub_query .= sprintf(" and tr.is_server_only=%s", $params->{is_server_only});
    }



    my @results = ();
    my $index = 1;

    my $query = sprintf("select DISTINCT pr.target || re.domain, pr.target, re.domain
from casp13.results re
join casp13.predictions pr on (pr.id = re.predictions_id)
join casp13.groups gr on (gr.id = pr.groups_id)
join casp13.targets tr on (tr.name = pr.target)

where 1=1 and re.domain IN (1,2,3,4,5) %s

ORDER by pr.target ASC, re.domain ASC ", $sub_query);


print $query . "\n";

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my($target_domain, $target, $domain) = $sth->fetchrow_array()) {
	    push(@results,
		{INDEX => $index,
		TARGET =>$target,
		DOMAIN =>$domain
		});
	    $index++;
	}
    }
    #################
    return @results;
}

sub get_gdt_ts {
    my ($self, $params) = @_;
    my $sub_query = "";

    if(defined($params->{domain}) && ($params->{domain} ne '')) {
	$sub_query .= sprintf(" and re.domain=%s ", $params->{domain});
    }
    if(defined($params->{target}) && ($params->{target} ne '')) {
	$sub_query .= sprintf(" and pr.target='%s' ", $params->{target});
    }
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
	$sub_query .= sprintf(" and gr.id=%s ", $params->{groups_id});
    }
    if(defined($params->{model}) && ($params->{model} ne '')) {
	$sub_query .= sprintf(" and pr.model=%s ", $params->{model});
    }

    my @results = ();
    my $index = 1;

    my $query = sprintf("
    select MAX(gdt_ts_4) from casp13.results re
    join casp13.predictions pr on (pr.id = re.predictions_id)
    join casp13.groups gr on (gr.id = pr.groups_id)
    where 1=1 %s and re.n1_4>19 ", $sub_query);

print $query . "\n";
my  $gdt_ts = "X";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	#while(my($gdt) = $sth->fetchrow_array()) {
	$gdt_ts = $sth->fetchrow_array();
	#}

    }

    if(!defined($gdt_ts) || $gdt_ts eq "") {
	$gdt_ts = "X";
    }

    #################

    return $gdt_ts;
}


# the method calculates the 
# average of 20 best server first models for domain
sub get_avg_gdt_ts_20s{
    my ($self, $target, $domain) = @_;
    if (!defined($domain)){
	$domain = 0;
    }
    my $query = sprintf ("
    SELECT  avg(CASE WHEN gdt_ts_4>0 THEN gdt_ts_4 END) FROM (SELECT gdt_ts_4 FROM casp13.results re
    JOIN casp13.predictions pr on re.predictions_id=pr.id
    JOIN casp13.groups gr on pr.groups_id = gr.id
    JOIN casp13.targets t on t.name=pr.target
    WHERE t.name='%s' AND re.domain=%s AND pr.model=1 AND re.parent=0 and gr.type in (1,2) ORDER BY gdt_ts_4 DESC limit 20 ) AS TMP", $target, $domain);
    my $result = '';
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result) = $sth->fetchrow_array();
    }
    return $result;
}

sub get_z_score {
    my ($self, $params) = @_;
    my $sub_query = "";

    if(defined($params->{domain}) && ($params->{domain} ne '')) {
	$sub_query .= sprintf(" and re.domain=%s ", $params->{domain});
    }
    if(defined($params->{target}) && ($params->{target} ne '')) {
	$sub_query .= sprintf(" and pr.target='%s' ", $params->{target});
    }
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
	$sub_query .= sprintf(" and gr.id=%s ", $params->{groups_id});
    }

    if(defined($params->{model}) && ($params->{model} ne '')) {
	$sub_query .= sprintf(" and pr.model=%s ", $params->{model});
    }

    my $kind_of_z_score = "gdt_4_z_score";

    if(defined($params->{is_server_group}) && ($params->{is_server_group} eq '1')) {
	$kind_of_z_score = "gdt_4_z_score_server";
    }


    if(defined($params->{z_score}) && ($params->{z_score} ne '')) {
	$kind_of_z_score = $params->{z_score};
    }



    my @results = ();
    my $index = 1;

    my $query = sprintf("
    select MAX(%s) from casp13.results re
    join casp13.predictions pr on (pr.id = re.predictions_id)
    join casp13.groups gr on (gr.id = pr.groups_id)
    where 1=1 %s and re.n1_4 > 19 ", $kind_of_z_score, $sub_query);

print $query . "\n";
my  $gdt_ts = "X";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	#while(my($gdt) = $sth->fetchrow_array()) {
	$gdt_ts = $sth->fetchrow_array();
	#}

    }

    if(!defined($gdt_ts) || $gdt_ts eq "") {
	$gdt_ts = "X";
    }

    #################

    return $gdt_ts;
}

sub get_best_gdt_ts {
    my ($self, $params) = @_;
    my $sub_query = "";

    if(defined($params->{domain}) && ($params->{domain} ne '')) {
	$sub_query .= sprintf(" and re.domain=%s ", $params->{domain});
    }
    if(defined($params->{target}) && ($params->{target} ne '')) {
	$sub_query .= sprintf(" and pr.target='%s' ", $params->{target});
    }
    if(defined($params->{groups_id}) && ($params->{groups_id} ne '')) {
	$sub_query .= sprintf(" and gr.id=%s ", $params->{groups_id});
    }

    my @results = ();
    my $index = 1;

    my $query = sprintf("
    select MAX(gdt_ts_4) from casp13.results re
    join casp13.predictions pr on (pr.id = re.predictions_id)
    join casp13.groups gr on (gr.id = pr.groups_id)
    where 1=1 %s and re.n1_4>19 ", $sub_query);

print $query . "\n";
my  $gdt_ts = "X";
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	#while(my($gdt) = $sth->fetchrow_array()) {
	$gdt_ts = $sth->fetchrow_array();
	#}

    }

    if(!defined($gdt_ts) || $gdt_ts eq "") {
	$gdt_ts = "X";
    }

    #################

    return $gdt_ts;
}

sub get_gdt_ha_counts {
    my ($self, $params) = @_;

    my $sub_query = "";

    if(defined($params->{is_server_only}) && ($params->{is_server_only} ne '')) {
	$sub_query .= sprintf(" and tr.is_server_only=%s", $params->{is_server_only});
    }

    my @results = ();
    my $index = 1;

    my $query = sprintf("
select DISTINCT pr.target || re.domain, pr.target, re.domain
from casp13.results re
join casp13.predictions pr on (pr.id = re.predictions_id)
join casp13.groups gr on (gr.id = pr.groups_id)
join casp13.targets tr on (tr.name = pr.target)

where 1=1 and re.domain IN (1,2,3,4,5) %s
", $sub_query);


print $query . "\n";

    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
	while(my($target_domain, $target, $domain) = $sth->fetchrow_array()) {
	    push(@results,
		{INDEX => $index,
		TARGET =>$target,
		DOMAIN =>$domain
		});
	    $index++;
	}
    }
    #################
    return @results;
}

sub upload_results_for_target {
	my ($self, $targets, $actions) = @_;
	system ('/local/Projects/Perl/casp13/src/scripts/evaluation_scripts/results_upload.sh', join(',', @{$targets}), join(',', @{$actions}));
}

sub delete_results_for_target {
	my ($self, $target) = @_;
	my $where ;
	if ($target =~ /^(N0981-D[1-9]+)$/) {
                $where = sprintf("predictions_id in (select id from casp13.predictions p where p.target=\'%s\') and casp13.results.domain=0", $1 );
        } elsif ($target =~ /^([TSNFAXLnsfaxl][0-9]{4}(s[1-9]){0,1})-D([1-9]+)$/) {
		$where = sprintf("predictions_id in (select id from casp13.predictions p where p.target=\'%s\') and casp13.results.domain=%s", $1, $3);
	}elsif ($target =~ /^(R0957s2)-D([1-9]+)$/) {
                $where = sprintf("predictions_id in (select id from casp13.predictions p where p.target=\'%s\') and casp13.results.domain=%s", $1, $2);
        }elsif($target =~ /^[TRSNFAXLnsfaxl][0-9]{4}(s[1-9]){0,1}$/ || $target =~ /^(R[0-9]{4}(s[1-9]){0,1})-D([1-9]+)$/){
		$where = sprintf("predictions_id in (select id from casp13.predictions p where p.target=\'%s\') and casp13.results.domain=0", $target);
	}else{
		return;
	}
	$self->{_database}->query("DELETE FROM casp13.results_alignment WHERE results_id IN (SELECT id FROM casp13.results WHERE $where)");
	$self->{_database}->query("DELETE FROM casp13.results WHERE $where");
}

sub delete_results_for_all_domains_for_target {
	my ($self, $target) = @_;
	my $where;
	if ($target =~ /^[TSNFAXLsnfaxl][0-9]{4}/){
		$where = sprintf("predictions_id in (select id from casp13.predictions p where p.target=\'%s\') and casp13.results.domain<>0 ", $target);
	}else{
		return;
	}
#	my $query1 = "DELETE FROM casp13.results_alignment WHERE results_id IN (SELECT id FROM casp13.results WHERE $where)";
#	my $query2 = "DELETE FROM casp13.results WHERE $where";
#	print $query1."\n";
#	print $query2."\n";
#	return;
        $self->{_database}->query("DELETE FROM casp13.results_alignment WHERE results_id IN (SELECT id FROM casp13.results WHERE $where)");
        $self->{_database}->query("DELETE FROM casp13.results WHERE $where");	
}

# the method fetches the all models submitted to any of the assisted targets
# e.g. T[acspx]761
sub getAssistedModels{
    my ($self, $param) = @_;
    my $target = undef; # reference target - the whole regular target or its domain, e.g. T0761
    my $domain = 0; # 
    my $flag_models = undef; # flag whether report all or first models 
    if (defined($param->{target0})){
	if ($param->{target0} =~ m/^(T[0-9]{4}(s[1-9]){0,1})-D([0-9]+)/){
                $target = $1; $domain = $3;
        } else {
                $target = $param->{target0}; $domain = 0;
        } 
    } else {
	return;
    }
    if (!defined($param->{models}) || $param->{models} ne 'all'){
	$flag_models = 'first';
    } else {
	$flag_models = 'all';
    }
    my @results;
    my $query = sprintf("SELECT DISTINCT g.id, g.code , pr.model , re.parent from casp13.results re 
	JOIN casp13.predictions pr ON pr.id=re.predictions_id 
	JOIN casp13.groups g ON pr.groups_id=g.id
	WHERE ((pr.target SIMILAR TO '[SNFAXL]%s' AND re.domain=%d) 
		%s
	) %s
	ORDER BY g.code, pr.model", substr($target, 1), $domain, 
	(substr($target, 1) =~ m/^0981/ ? " OR (case when pr.target similar to \'N0981\%\%\' then re.domain=0 end) " : ''), 
	($flag_models eq 'first' ? ' AND pr.model=1 ' : ''));
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
        while(my($gr_id, $gr_code, $model, $parent) = $sth->fetchrow_array()) {
            push(@results,
                {gr_id => $gr_id,
                gr_code => $gr_code,
                model => $model,
		parent => $parent,
		target0 => $target # the reference target's name (whole regular target or its domain)
                });
        }
    }
    return @results;
}


# the method fetches the results for assisted targets - all in once per target
sub getAssistedResultsForTarget{
    my ($self, $param) = @_;
    my $target; my $domain; # target name  and domain of the reference target
    #printf "%s", $param->{target0};
    if (defined ($param->{target0})){
	if ($param->{target0} =~ m/^(T[0-9]{4}(s[1-9]){0,1})-D([0-9]+)/){
		$target = $1; $domain = $3;
	} else {
		$target = $param->{target0}; $domain = 0;
	}
    } else {
	return undef;
    }
    my $comp_to='';
    if (!defined($param->{comp_to}) || ($param->{comp_to} ne 'best' && $param->{comp_to} ne 'best_all')){
	$comp_to = 'first';
    }
    my $score;
    if (defined($param->{score})){
        $score = $param->{score};
    } else {
        $score = 'gdt_ts_4';
    }
    my $subquery = "";
    if ($score eq "ace_rms_ca" || $score eq "dfm"){
                $subquery .= "min(CASE WHEN re.$score>=0 THEN re.$score ELSE null END) ";
    } elsif ($score eq "mp_score"){
                $subquery .= "min(CASE WHEN re.$score<6.0 THEN re.$score ELSE null END) ";
    } elsif ($score eq "rpf" || $score eq "codm") {
                $subquery .= "max(CASE WHEN re.$score>-1.0 THEN re.$score ELSE null END)";
    } else {
                $subquery .= "max(CASE WHEN re.$score>=0.0 THEN re.$score ELSE null END)";
    }
    my @results;
    my @models = $self->getAssistedModels($param);
    foreach my $m (@models){ # loop over models
	my $query = "SELECT ";
	# part of the query which corresponds to the regular target
	if ($param->{comp_to} eq 'best_all') {
		$query .= sprintf (" (select $subquery from casp13.results re join casp13.predictions pr on pr.id=re.predictions_id 
	            WHERE target='T%s' and domain=%s ) 
	         as t0_score ", substr($target, 1), $domain);
	} else {
		$query .= sprintf (" (SELECT CASE WHEN (select count(re.gdt_ts_4) from casp13.results re 
		   JOIN casp13.predictions pr ON pr.id=re.predictions_id 
		   JOIN casp13.groups gr ON gr.id=pr.groups_id
		   JOIN casp13.targets t ON pr.target=t.name
		   WHERE pr.target='T%s' and gr.id=%s and domain=%s %s and t.cancellation_status<>1 )>0
		THEN 
		   (select $subquery from casp13.results re join casp13.predictions pr on pr.id=re.predictions_id 
		    JOIN casp13.groups gr ON gr.id=pr.groups_id
		    WHERE target='T%s' and gr.id=%s and domain=%s %s) 
		ELSE
		    null    
		END) as t0_score ", substr($target, 1), $m->{gr_id}, $domain, ($comp_to eq 'first' ? ' and model=1 ' : ''),
		substr($target, 1), $m->{gr_id}, $domain, ($comp_to eq 'first' ? ' and model=1 ' : ''));
	}
	# part of the query which corresponds to targets
	my @letters = qw/S N F A X x n/; # code of different types of assisted targets
	foreach my $letter (@letters){
		$query .= sprintf (", (SELECT (CASE WHEN (select count(re.gdt_ts_4) from casp13.results re 
	    JOIN casp13.predictions pr ON pr.id=re.predictions_id 
	    JOIN casp13.groups gr ON gr.id=pr.groups_id
	    JOIN casp13.targets t ON pr.target=t.name
	    WHERE target='%s%s%s' and gr.id=%s and domain=%s and model=%s and re.parent=%s and t.cancellation_status<>1 )>0 
	THEN
	    (select $subquery from casp13.results re join casp13.predictions pr on pr.id=re.predictions_id 
	    JOIN casp13.groups gr ON gr.id=pr.groups_id
	    WHERE target='%s%s%s' and gr.id=%s and domain=%s and model=%s and re.parent=%s) 
	ELSE
	    null
	END)) as t%s_score", $letter, substr($target, 1), ($letter eq 'N' && substr($target, 1) =~ m/^0981/ ? "-D$domain" : ''), $m->{gr_id}, ($letter eq 'N' && substr($target, 1) =~ m/^0981/ ? 0 : $domain), $m->{model}, $m->{parent},
	$letter, substr($target, 1), ($letter eq 'N' && substr($target, 1) =~ m/^0981/ ? "-D$domain" : ''), $m->{gr_id},  ($letter eq 'N' && substr($target, 1) =~ m/^0981/ ? 0 : $domain), $m->{model}, $m->{parent}, $letter);
	}
	#print $query."\n";
	#last;	
	my $sth = $self->{_database}->query($query);
	if(defined($sth) && ($sth->rows() > 0)) {
		my($t0_score, $s_score, $n_score, $f_score, $a_score, $x_score, $_x_score, $_n_score) = $sth->fetchrow_array();
		if (defined($t0_score)){
		   $m->{T0_SCORE} = sprintf("%6.3f",$t0_score);
		} else {
		   $m->{T0_SCORE} = -999.999;
		   $m->{S_SCORE_DIFF} = '-';
		   $m->{N_SCORE_DIFF} = '-';
		   $m->{F_SCORE_DIFF} = '-';
		   $m->{A_SCORE_DIFF} = '-';
		   $m->{X_SCORE_DIFF} = '-';
		   $m->{_x_SCORE_DIFF} = '-';
		   $m->{_n_SCORE_DIFF} = '-';
		}
		# S
		if (defined($s_score)){
                   $m->{S_SCORE} = sprintf("%6.3f",$s_score);
		   if (defined($t0_score)){
			$m->{S_SCORE_DIFF} = sprintf("%6.3f", $s_score - $t0_score);
		   }
                } else {
                   $m->{S_SCORE} = '-';
		   $m->{S_SCORE_DIFF} = '-';
                }
		# N
                if (defined($n_score)){
                   $m->{N_SCORE} = sprintf("%6.3f",$n_score);
                   if (defined($t0_score)){
                        $m->{N_SCORE_DIFF} = sprintf("%6.3f", $n_score - $t0_score);
                   }
                } else {
                   $m->{N_SCORE} = '-';
                   $m->{N_SCORE_DIFF} = '-';
                }
                # n
                if (defined($_n_score)){
                   $m->{_n_SCORE} = sprintf("%6.3f", $_n_score);
                   if (defined($t0_score)){
                        $m->{_n_SCORE_DIFF} = sprintf("%6.3f", $_n_score - $t0_score);
                   }
                } else {
                   $m->{_n_SCORE} = '-';
                   $m->{_n_SCORE_DIFF} = '-';
                }
		# F
                if (defined($f_score)){
                   $m->{F_SCORE} = sprintf("%6.3f",$f_score);
                   if (defined($t0_score)){
                        $m->{F_SCORE_DIFF} = sprintf("%6.3f", $f_score - $t0_score);
                   }
                } else {
                   $m->{F_SCORE} = '-';
                   $m->{F_SCORE_DIFF} = '-';
                }
		# A
		if (defined($a_score)){
                   $m->{A_SCORE} = sprintf("%6.3f", $a_score);
                   if (defined($t0_score)){
                        $m->{A_SCORE_DIFF} = sprintf("%6.3f", $a_score - $t0_score);
                   }
                } else {
                   $m->{A_SCORE} = '-';
                   $m->{A_SCORE_DIFF} = '-';
                }
		# X
                if (defined($x_score)){
                   $m->{X_SCORE} = sprintf("%6.3f", $x_score);
                   if (defined($t0_score)){
                        $m->{X_SCORE_DIFF} = sprintf("%6.3f", $x_score - $t0_score);
                   }
                } else {
                   $m->{X_SCORE} = '-';
                   $m->{X_SCORE_DIFF} = '-';
                }
		# x
                if (defined($_x_score)){
                   $m->{_x_SCORE} = sprintf("%6.3f", $_x_score);
                   if (defined($t0_score)){
                        $m->{_x_SCORE_DIFF} = sprintf("%6.3f", $_x_score - $t0_score);
                   }
                } else {
                   $m->{_x_SCORE} = '-';
                   $m->{_x_SCORE_DIFF} = '-';
                }
		$m->{MODEL_NAME} = sprintf("_%sTS%03d_%d%s", substr($m->{target0},1), $m->{gr_code}, $m->{model}, ($m->{parent} != 0 ? "_".$m->{parent} : ''));
		push @results, $m;
	}
	#last;
    } # end loop over models
#    @results = sort {$b->{T0_SCORE}<=>$a->{T0_SCORE}} @results;
    my $index = 1;
    foreach my $m (@results){
	$m->{INDEX} = $index;
	if ($m->{T0_SCORE} == -999.999){
		$m->{T0_SCORE} = '-';
	}
	$index++;
    }
    return @results;
}

sub getAssistedResultsForTarget_txt{
    my ($self, $param) = @_;
    my @SCORES = qw/
        gdt_ts_4 gdt_ha_4 gdc_all lga_4_tr ace_rms_ca 
        lga_s_5 al0p dali_raw z_score_m mp_score lddt 
        cad_aa cad_ss rpf codm dfm 
        handedness sov qcs conts tm_score sg_py/;
    my $target; my $domain; # target name  and domain of the reference target
    if (defined ($param->{target0})){
        if ($param->{target0} =~ m/^(T[0-9]{4}(s[1-9]){0,1})-D([0-9]+)/){
                $target = $1; $domain = $3;
        } else {
                $target = $param->{target0}; $domain = 0;
        }
    } else {
        return undef;
    }
    my $comp_to;
    if (!defined($param->{comp_to}) || ($param->{comp_to} ne 'best' && $param->{comp_to} ne 'best_all')){
        $comp_to = 'first';
    }
    my @results;
    my @models = $self->getAssistedModels($param);
    # subquery - list of fields-scores from results table
    my $subquery = "";
    foreach my $score(@SCORES){
	if ($score eq "ace_rms_ca" || $score eq "dfm"){
		$subquery .= "min(CASE WHEN re.$score>=0 THEN re.$score ELSE null END) as $score,";
	} elsif ($score eq "mp_score"){
		$subquery .= "min(CASE WHEN re.$score<6.0 THEN re.$score ELSE null END) as $score,";
	} elsif ($score eq "rpf" || $score eq "codm") {
		$subquery .= "max(CASE WHEN re.$score>-1.0 THEN re.$score ELSE null END) as $score,";
	} else {
		$subquery .= "max(CASE WHEN re.$score>=0.0 THEN re.$score ELSE null END) as $score,";
	}
    }
    $subquery =~ s/,$//;

    foreach my $m (@models){ # loop over models
	my $query; 
	if ($param->{comp_to} eq 'best_all') {
	 $query = sprintf("(SELECT target, $subquery FROM casp13.results re 
	    JOIN casp13.predictions pr on pr.id=re.predictions_id 
	    JOIN casp13.targets t ON pr.target=t.name
            WHERE target='T%s' and domain=%s  and t.cancellation_status<>1 GROUP BY target)",
	    substr($target, 1),  $domain
	);
	} else {
	$query = sprintf("(SELECT target, $subquery FROM casp13.results re 
            JOIN casp13.predictions pr on pr.id=re.predictions_id 
            JOIN casp13.groups gr ON gr.id=pr.groups_id
            JOIN casp13.targets t ON pr.target=t.name
            WHERE target='T%s' and gr.id=%s and domain=%s %s and t.cancellation_status<>1 GROUP BY target)",
            substr($target, 1),  $m->{gr_id}, $domain, (defined($comp_to) && ($comp_to eq 'first') ? ' and model=1 ' : '')
        );
	}
	# part of the query which corresponds to targets
        my @letters = qw/S N F A X x n/; # code of different types of assisted targets
        foreach my $letter (@letters){
	    $query .= sprintf ("\nUNION (SELECT target, $subquery FROM casp13.results re 
            	JOIN casp13.predictions pr on pr.id=re.predictions_id 
            	JOIN casp13.groups gr ON gr.id=pr.groups_id
		JOIN casp13.targets t ON pr.target=t.name
	        WHERE target='%s%s%s' and gr.id=%s and domain=%s and model=%s and re.parent=%s and t.cancellation_status<>1 GROUP BY target)",
            	$letter, substr($target, 1), ($letter eq 'N' && substr($target, 1) =~ m/^0981/ ? "-D$domain" : ''), $m->{gr_id}, ($letter eq 'N' && substr($target, 1) =~ m/^0981/ ? 0 : $domain), $m->{model}, $m->{parent}
            );
	}
	$query .= " ORDER BY target";
#	print $query."\n";
	my $sth = $self->{_database}->query($query);
	if (defined($sth) && ($sth->rows() > 0)){
		my $HASH;
		while(defined(my $hash_ref = $sth->fetchrow_hashref())) {
			if ($hash_ref->{target} =~ m/^T/){
				$HASH->{T0} = $hash_ref;
			} elsif ($hash_ref->{target} =~ m/^S/){
				$HASH->{S} = $hash_ref;
			} elsif ($hash_ref->{target} =~ m/^N/){
                                $HASH->{N} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^F/){
                                $HASH->{F} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^A/){
                                $HASH->{A} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^X/){
                                $HASH->{X} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^x/){
                                $HASH->{x} = $hash_ref;
                        } elsif ($hash_ref->{target} =~ m/^n/){
                                $HASH->{n} = $hash_ref;
                        }
		}
		#print scalar(keys(%$HASH))."\n";
		#printf $HASH->{Ts}->{target}."\t";
		foreach my $score (@SCORES){
			#printf "%s ", (defined($HASH->{Ts}->{$score}) ? $HASH->{Ts}->{$score} : '-');
			if (defined($HASH->{"T0"}->{$score})){
				$m->{"T0"."_$score"} = sprintf("%6.3f", $HASH->{"T0"}->{$score});
			} else {
				$m->{"T0"."_$score"} = '-';
			}
			foreach my $letter(@letters){
				my $key = "$letter"."_$score";
				my $key_diff = "$letter"."_diff"."_$score";
				if ($letter =~ m/^[a-z]$/) {
					$key = '_'.$key;
					$key_diff = '_'.$key_diff;
				}
				if (defined($HASH->{"$letter"}->{$score})){
					$m->{$key} = sprintf("%6.3f", $HASH->{"$letter"}->{$score});
					if (defined($HASH->{"T0"}->{$score})){
						$m->{$key_diff} = sprintf("%6.3f", $HASH->{"$letter"}->{$score} - $HASH->{"T0"}->{$score});
					} else {
						$m->{$key_diff} = '-';
					}
				} else {
					$m->{$key} = '-';
					$m->{$key_diff} = '-';
				}
			}
		}
		$m->{MODEL_NAME} = sprintf("_%sTS%03d_%d%s", substr($m->{target0},1), $m->{gr_code}, $m->{model}, ($m->{parent} != 0 ? "_".$m->{parent} : ''));
		push @results, $m; 
		#print "\n";
		
	} # end if 
#	last;
    } # end loop over models
    return @results;
}


sub add_alignment {
    my ($self, $results_id, $flag, $alignment) = @_;
    my $column = undef;
    if ( $flag eq "lddt" ){
        $column = "alignment_lddt";
    } elsif ($flag eq "sg_lvr"){
        $column = "alignment_sg_lvr";
    } elsif ($flag eq "sg_py"){
        $column = "alignment_sg_py";
    }
    if (!defined($column)){
        return undef; # do nothing
    }
    # check if there exists record with the given results_id
    my $id = $self->exist_alignment($results_id);
    if (!defined($id)) { # insert into
      my $query = sprintf("INSERT INTO casp13.results_alignment (results_id, %s) VALUES (%s, \'%s\')", $column, $results_id, $alignment);
      my $sth = $self->{_database}->query($query);
    } else { # update results
      my $query = sprintf("UPDATE casp13.results_alignment SET  %s=\'%s\' WHERE id=%d", $column, $alignment, $id);
      my $sth = $self->{_database}->query($query);
    }
}

sub exist_alignment {
    my ($self, $results_id) = @_;
    my $query = sprintf("SELECT id FROM casp13.results_alignment WHERE results_id=%d", $results_id);
    my $sth = $self->{_database}->query($query);
    my ($id) = $sth->fetchrow_array();
    if (!defined($id) || $id eq '' || $id <= 0) {
        return undef;
    }
    return $id;
}

sub get_alignment{
    my ($self, $param) = @_;
    unless (defined($param->{target}) && defined($param->{groups_id}) && defined($param->{model})) {return (undef, undef);}
    my $query = sprintf(
	"SELECT ra.alignment_lddt, ra.alignment_sg_py FROM casp13.results_alignment ra
	JOIN casp13.results re ON re.id=ra.results_id
	JOIN casp13.predictions pr ON pr.id=re.predictions_id
	WHERE pr.target=\'%s\' AND pr.groups_id=%d AND pr.model=%d AND re.domain=0 LIMIT 1",
	$param->{target}, $param->{groups_id}, $param->{model}
     );
    my $sth = $self->{_database}->query($query);
    if(defined($sth) && ($sth->rows() > 0)) {
       my ($al_lddt, $al_sg_rmsd) = $sth->fetchrow_array();
       return ($al_lddt, $al_sg_rmsd);
    } else {
       return (undef, undef);
    }
}

sub get_results_alignment{
    my ($self, $param, $metric) = @_;
    my $field = $param->{field};
    my $order = $param->{order};
    if ($metric eq 'gdt_ts' || $metric eq 'lga_sda'){
        $field =  're.gdt_ts_4';
        $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
        my $domain_ = 0; my $target_ = $param->{target};
        if ($target_ =~ m/(\S+)-D(\d+)/){
                $target_ = $1;
                $domain_ = $2;
        }
        my $sub_query = sprintf(" AND pr.target=\'%s\' AND re.domain=%d ", $target_, $domain_);
        if(defined($param->{model}) && ($param->{model} ne '') && ($param->{model} ne 'all')) {
                $sub_query .= sprintf(" and pr.model=\'%s\' ", $param->{model});
        }
        my @results = ();
        my $query = sprintf("SELECT  pr.target, re.domain, pr.pfrmat, gr.code, pr.model, re.parent, re.gdt_ts_4, re.gdt_ha_4, re.ace_rms_ca, re.lga_4_tr, re.alignment_4, re.id, gr.id
                FROM casp13.results re JOIN casp13.predictions pr ON  (re.predictions_id = pr.id) 
                JOIN casp13.groups gr ON (pr.groups_id = gr.id)
                WHERE 1=1 %s ORDER BY %s %s LIMIT 2500",  $sub_query, $field, $order);
        my $sth = $self->{_database}->query($query);
        if(defined($sth) && ($sth->rows() > 0)) {
          my $index = 1;
          while(my( $target, $domain, $pfrmat, $code, $model, $parent, $gdt_ts_4, $gdt_ha_4, $rmsd, $gdc_sc, $alignment, $id, $gr_id) = $sth->fetchrow_array()) {
	    $alignment =~ s/^\s+//; $alignment =~ s/\s+$//;
            my @tokens = split(/\s+/, $alignment);
            my %hash_alignment;
	    my $res_no = 0;
            foreach my $token (@tokens) {
		$res_no++;
		if ($token =~ m/X/i) {
		  next;
		} else {
		  $hash_alignment{$res_no} = $token;
		}
            }
            push(@results, {
                INDEX => $index,
                TARGET => sprintf("%s%s", $target, ($domain == 0 ? '' : "-D$domain")),
                PFRMAT => $pfrmat,
                CODE => sprintf("%03d",$code),
		GROUP_ID => $gr_id,
                MODEL => $model,
		gdt_ts => sprintf("%.2f", $gdt_ts_4),
                gdt_ha => sprintf("%.2f", $gdt_ha_4),
		rmsd => sprintf("%.2f", $rmsd),
		gdc_sc => sprintf("%.2f", $gdc_sc),
                HASH_ALIGNMENT => \%hash_alignment,
                MODEL_NAME => sprintf("%s%s%03d_%d%s%s", $target, $pfrmat, $code, $model, ($parent == 0 ? '' : "_".$parent ), ($domain == 0 ? '' : "-D$domain")),
		ID => $id
                }
            );
            $index++;
          }
        }
        return @results;
    } elsif ($metric eq 'lddt') {
        $field = 're.lddt' ;
        $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
        my $domain_ = 0; my $target_ = $param->{target};
        if ($target_ =~ m/(\S+)-D(\d+)/){
                $target_ = $1;
                $domain_ = $2;
        }
        my $sub_query = sprintf(" AND pr.target=\'%s\' AND re.domain=%d ", $target_, $domain_);
        if(defined($param->{model}) && ($param->{model} ne '') && ($param->{model} ne 'all')) {
           $sub_query .= sprintf(" and pr.model=\'%s\' ", $param->{model});
        }
        my @results = ();
        my $query = sprintf("SELECT  pr.target, re.domain, pr.pfrmat, gr.code, pr.model, re.parent, re.lddt, ra.alignment_lddt, gr.id
                FROM casp13.results re JOIN casp13.predictions pr ON  (re.predictions_id = pr.id) 
                JOIN casp13.groups gr ON (pr.groups_id = gr.id)
                JOIN casp13.results_alignment ra ON re.id=ra.results_id
                WHERE 1=1 %s ORDER BY %s %s LIMIT 2500",  $sub_query, $field, $order);
        my $sth = $self->{_database}->query($query);
        if(defined($sth) && ($sth->rows() > 0)) {
          my $index = 1;
          while(my( $target, $domain, $pfrmat, $code, $model, $parent, $lddt,  $alignment, $gr_id) = $sth->fetchrow_array()) {
            my @tokens = split(/\s+/, $alignment);
            my %hash_alignment;
            foreach my $token (@tokens){
                   if ($token =~ m/(\S+):(\S+)/){
                        $hash_alignment{$1} = $2;
                   }
            }
            push(@results, {
                INDEX => $index,
                TARGET => sprintf("%s%s", $target, ($domain == 0 ? '' : "-D$domain")),
                PFRMAT => $pfrmat,
                CODE => sprintf("%03d",$code),
		GROUP_ID => $gr_id,
                MODEL => $model,
                lddt => sprintf("%.2f", $lddt),
                HASH_ALIGNMENT => \%hash_alignment,
                MODEL_NAME => sprintf("%s%s%03d_%d%s%s", $target, $pfrmat, $code, $model, ($parent == 0 ? '' : "_".$parent ), ($domain == 0 ? '': "-D$domain")),
                }
            );
            $index++;
          }
        }
        return @results;
    } elsif ($metric eq 'sg_lvr') {
        $field = 're.sg_lvr_6_2';
        $order = (!defined($order) || ($order eq '')) ? 'DESC' : $order;
        my $domain_ = 0; my $target_ = $param->{target};
        if ($target_ =~ m/(\S+)-D(\d+)/){
                $target_ = $1;
                $domain_ = $2;
        }
        my $sub_query = sprintf(" AND pr.target=\'%s\' AND re.domain=%d ", $target_, $domain_);
        if(defined($param->{model}) && ($param->{model} ne '') && ($param->{model} ne 'all')) {
                $sub_query .= sprintf(" and pr.model=\'%s\' ", $param->{model});
        }
        my @results = ();
        my $query = sprintf("SELECT pr.target, re.domain, pr.pfrmat, gr.code, pr.model, re.parent, re.sg_lvr_6_2, ra.alignment_sg_lvr, gr.id
                FROM casp13.results re JOIN casp13.predictions pr ON  (re.predictions_id = pr.id) 
                JOIN casp13.groups gr ON (pr.groups_id = gr.id)
                JOIN casp13.results_alignment ra ON re.id=ra.results_id
                WHERE 1=1 %s ORDER BY %s %s LIMIT 2500",  $sub_query, $field, $order);
        my $sth = $self->{_database}->query($query);
        if(defined($sth) && ($sth->rows() > 0)) {
          my $index = 1;
          while(my( $target, $domain, $pfrmat, $code, $model, $parent, $sg_lvr_6_2,  $alignment, $gr_id) = $sth->fetchrow_array()) {
            my @tokens = split(/\s+/, $alignment);
            my %hash_alignment;
            foreach my $token (@tokens){
                   if ($token =~ m/(\S+):(\S+)/){
                        $hash_alignment{$1} = $2;
                   }
            }
            push(@results, {
                INDEX => $index,
                TARGET => sprintf("%s%s", $target, ($domain == 0 ? '': "-D$domain")),
                PFRMAT => $pfrmat,
                CODE => sprintf("%03d",$code),
		GROUP_ID => $gr_id,
                MODEL => $model,
                sg_lvr_6_2 => sprintf("%.2f", $sg_lvr_6_2),
                HASH_ALIGNMENT => \%hash_alignment,
                MODEL_NAME => sprintf("%s%s%03d_%d%s%s", $target, $pfrmat, $code, $model, ($parent == 0 ? '' : "_".$parent ), ($domain == 0 ? '': "-D$domain")),
                }
            );
            $index++;
          }
        }
        return @results;
    }
}

1;