package ZscoresManager; use strict; use warnings; use DBI; use lib qw(Core); use Database; use PDBUtils; use Configuration; use LocalConfiguration; use TargetsManager; use GroupsManager; use Statistics; my $zscores_manager = undef; my $OUTLIER_CUTOFF = -2.0; my @SCORES = qw/ gdt_ts_4 gdt_ha_4 gdc_all lga_4_tr ace_rms_ca lga_s_5 al0 dali_raw z_score_m mp_score lddt cad_aa cad_ss rpf codm dfm handedness sov qcs conts tm_score sg_lvr_6_2 tens rf qse ace_rms_all sg_py rdc1 rdc2 dp /; sub new { my ($class) = @_; return $zscores_manager if(defined($zscores_manager)); my $self = { _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD}), }; $zscores_manager = bless $self, $class; return $zscores_manager; } ####################################### # # Methods to upload data to database # ######################################## sub get_new_record{ my ($self) = @_; my %record = ( id => '', groups_id => '', targets_id => '', domain => '', gdt_ts_4 => '', gdt_ha_4 => '', gdc_all =>'', lga_4_tr =>'', # gdc_sc ace_rms_ca => '', lga_s_5 => '', al0 => '', dali_raw => '', z_score_m => '', mp_score =>'', lddt =>'', cad_aa => '', cad_ss => '', rpf => '', codm => '', dfm => '', handedness => '', sov => '', qcs => '', conts => '', tm_score => '', sg_lvr_6_2 => '', rf => '', qse => '', ace_rms_all =>'', sg_py => '', #rdc1 => '', #rdc2 => '', #dp => '' ); return %record; } sub is_valid_columname { my ($self, $colname) = @_; if ('id' eq $colname || 'groups_id' eq $colname || 'targets_id' eq $colname || 'domain' eq $colname || 'gdt_ts_4' eq $colname || 'gdt_ha_4' eq $colname || 'gdc_all' eq $colname || 'lga_4_tr' eq $colname || # gdc_sc 'ace_rms_ca' eq $colname || 'lga_s_5' eq $colname || 'al0' eq $colname || 'dali_raw' eq $colname || 'z_score_m' eq $colname || 'mp_score' eq $colname || 'lddt' eq $colname || 'cad_aa' eq $colname || 'cad_ss' eq $colname || 'rpf' eq $colname || 'codm' eq $colname || 'dfm' eq $colname || 'handedness' eq $colname || 'sov' eq $colname || 'qcs' eq $colname || 'conts' eq $colname || 'tm_score' eq $colname || 'sg_lvr_6_2' eq $colname || 'tens' eq $colname || 'rf' eq $colname || 'qse' eq $colname || 'ace_rms_all' eq $colname || 'sg_py' eq $colname || 'rdc1' eq $colname || 'rdc2' eq $colname || 'dp' eq $colname ){ return 1; } else { return 0; } } sub getRawScores{ my ($self, $param ) = @_; my $query = " SELECT gr.id, tr.id, re.domain, max(re.gdt_ts_4) as gdt_ts_4, max(re.gdt_ha_4) as gdt_ha_4, max(re.gdc_all) as gdc_all, max(re.lga_4_tr) as lga_4_tr, max(-re.ace_rms_ca) as ace_rms_ca, max(re.lga_s_5) as lga_s_5, max(re.al0) as al0, max(re.dali_raw) as dali_raw, max(re.z_score_m) as z_score_m, max(-re.mp_score) as mp_score, max(re.lddt) as lddt, max(re.cad_aa) as cad_aa, max(re.cad_ss) as cad_ss, max(re.rpf) as rpf, max(re.codm) as codm, max(-re.dfm) as dfm, max(re.handedness) as handedness, max(re.sov) as sov, max(re.qcs) as qcs, max(re.conts) as conts, max(re.tm_score) as tm_score, max(re.sg_lvr_6_2) as sg_lvr_6_2, max(re.rf) as rf, max(re.qse) as qse, max(-re.ace_rms_all) as ace_rms_all, max(re.sg_py) as sg_py --max(-re.rdc1) as rdc1, --max(-re.rdc2) as rdc2, --max(dp) as dp 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 tr ON tr.name::text=pr.target " ; my $subquery; if (defined($param->{target})){ $subquery = sprintf (" WHERE pr.target=\'%s\' ", $param->{target}); } else { return undef; } if (defined($param->{domain})){ $subquery .= sprintf " AND re.domain=%s ", $param->{domain}; } else { $subquery .= " AND re.domain=0 "; } if ($param->{gr_type} eq "server_only"){ $subquery .= " AND gr.type>0 "; } else { # for "human/server" groups we process only "human/server" targets $subquery .= " AND tr.is_server_only=0 " ; } if ($param->{model_type} eq "first"){ $subquery .= " AND pr.model=1 "; } $subquery .= " GROUP BY gr.id, tr.id, re.domain"; $query .= $subquery; my $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows() > 0)){ my @data; while(my ($gr_id, $tr_id, $domain, $gdt_ts_4, $gdt_ha_4, $gdc_all, $lga_4_tr, $ace_rms_ca, $lga_s_5, $al0, $dali_raw, $z_score_m, $mp_score, $lddt, $cad_aa, $cad_ss, $rpf, $codm, $dfm, $handedness, $sov, $qcs, $conts, $tm_score, $sg_lvr_6_2, $rf, $qse, $ace_rms_all, $sg_py #, $rdc1, $rdc2, $dp ) = $sth->fetchrow_array()){ push @data, { id => '', groups_id => $gr_id, targets_id => $tr_id, domain => $domain, gdt_ts_4 => $gdt_ts_4, gdt_ha_4 => $gdt_ha_4, gdc_all => $gdc_all, lga_4_tr => $lga_4_tr, ace_rms_ca => $ace_rms_ca, lga_s_5 => $lga_s_5, al0 => $al0, dali_raw => $dali_raw, z_score_m => $z_score_m, mp_score => $mp_score, lddt => $lddt, cad_aa => $cad_aa, cad_ss => $cad_ss, rpf => $rpf, codm => $codm, dfm => $dfm, handedness => $handedness, sov => $sov, qcs => $qcs, conts => $conts, tm_score => $tm_score, sg_lvr_6_2 => $sg_lvr_6_2, rf => $rf, qse => $qse, ace_rms_all => $ace_rms_all, sg_py => $sg_py, # rdc1 => $rdc1, # rdc2 => $rdc2, # dp => $dp }; } return \@data; } return undef; } sub calcZScore{ my ($self, $refData) = @_; foreach my $score (@SCORES){ if ($score eq 'tens'){next;} my @arr ; # array to store raw scores foreach my $el (@{$refData}){ if ($score eq "ace_rms_ca") { if ($el->{$score} <= 0){ push @arr, $el->{$score}; } } elsif ($score eq "ace_rms_all") { if ($el->{$score} <= 0){ push @arr, $el->{$score}; } } elsif ($score eq "rdc1") { if ($el->{$score} <= 0){ push @arr, $el->{$score}; } } elsif ($score eq "rdc2") { if ($el->{$score} <= 0){ push @arr, $el->{$score}; } } elsif ($score eq "mp_score"){ if ($el->{$score} > -6.0 ){ push @arr, $el->{$score}; } } elsif ($score eq "z_score_m") { if ($el->{$score} > -999.0 ){ push @arr, $el->{$score}; } } elsif ($score eq "rpf"){ if ($el->{$score} > -0.2 ){ push @arr, $el->{$score}; } } elsif ($score eq "codm"){ if ($el->{$score} > -1.0 ){ push @arr, $el->{$score}; } } elsif ($score eq "dfm"){ if ($el->{$score} <= 0.0 ){ push @arr, $el->{$score}; } } elsif ($score eq "qse"){ if ($el->{$score} >= 0.0 ){ push @arr, $el->{$score}; } } else { if ($el->{$score} >= 0.0 ){ push @arr, $el->{$score}; } } } # calculate mean and standard deviation my ($m, $std) = $self->calcMeanStdWithoutOutliers(\@arr); # calculate z-score foreach my $el (@{$refData}){ my $zscore; if ($score eq "ace_rms_ca") { if ($el->{$score} <= 0){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "ace_rms_all") { if ($el->{$score} <= 0){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "rdc1") { if ($el->{$score} <= 0){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "rdc2") { if ($el->{$score} <= 0){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "mp_score"){ if ($el->{$score} > -6.0 ){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "z_score_m") { if ($el->{$score} > -999.0 ){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "rpf"){ if ($el->{$score} > -0.2 ){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "dfm"){ if ($el->{$score} <= 0.0 ){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "qse"){ if ($el->{$score} >= 0.0 ){ $zscore = ($el->{$score} - $m)/$std; } } elsif ($score eq "codm"){ if ($el->{$score} > -1.0 ){ $zscore = ($el->{$score} - $m)/$std; } } else { if ($el->{$score} >= 0.0 ){ if ($std == 0){ printf "$score: %5.4f\n", $el->{$score}; $zscore = undef; } else { $zscore = ($el->{$score} - $m)/$std; } } } if (defined($zscore) && $zscore < $OUTLIER_CUTOFF){ $zscore = $OUTLIER_CUTOFF; } if (defined($zscore)) { $el->{$score} = $zscore; } else { $el->{$score} = $OUTLIER_CUTOFF; } } } return $refData; } sub upload_all{ my ($self, $param, $refData) = @_; my @data = @$refData; my $table; if ($param->{gr_type} eq 'server_only'){ if ($param->{model_type} eq 'first'){ $table = 'zscores_1ms'; } else { $table = 'zscores_bms'; } } else { if ($param->{model_type} eq 'first'){ $table = 'zscores_1m'; } else { $table = 'zscores_bm'; } } foreach my $record (@data) { $self->upload($record, $table); } } sub upload{ my ($self, $record, $table) = @_; my $id = $self->get_id_record($record, $table); if ($id){ $record->{id} = $id; $self->update($record, $table); } else { $self->add($record, $table); } } # check if the record exists in the table # if it exists return id else 0 sub get_id_record{ my ($self, $record, $table) = @_; my $query = sprintf("SELECT id FROM casp13.%s WHERE groups_id=%d AND targets_id=%d AND domain=%d", $table, $record->{groups_id}, $record->{targets_id}, $record->{domain}); my $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows() > 0)){ while(my ($id) = $sth->fetchrow_array()) { if ($id eq '' || $id == 0){ return 0; } else { return $id; } } } } # add one record to table sub add{ my ($self, $record, $table) = @_; my %hash = %$record; my $result = 0; my $column_names = ''; my $column_values = ''; #my $values_count = 0; while(my ($key, $value) = each(%hash)){ if ($key eq 'id'){ next; } if ($self->is_valid_columname($key)){ $column_names .= sprintf(" %s,", $key); $column_values .= sprintf(" \'%s\',", $value); } } $column_names =~ s/,$//; $column_values =~ s/,$//; my $query = sprintf("INSERT INTO casp13.$table ( %s ) VALUES ( %s )", $column_names, $column_values); # print $query."\n"; # return $result; my $sth = $self->{_database}->query($query); if(defined($sth)) { my $query = sprintf("SELECT MAX(id) FROM casp13.$table"); my $sth = $self->{_database}->query($query); # add logger ($result) = $sth->fetchrow_array(); } return $result; } # update one record to table sub update{ my ($self, $record, $table) = @_; my %hash = %$record; my $result = 0; my $set_query = ''; while ( my ($key, $value) = each(%hash) ) { if ($key eq 'id'){ next; } if ($self->is_valid_columname($key)){ $set_query .= sprintf(" %s = \'%s\',", $key, $value); } } $set_query =~ s/,$//; my $query = sprintf("UPDATE casp13.$table SET %s WHERE id=%d ", $set_query, $hash{id}); # print $query."\n"; # return $result; my $sth = $self->{_database}->query($query); if(defined($sth)) { $result = 1; } return $result; } sub mean{ my ($self, $refArr) = @_; my $res = 0.0; my @arr = @{$refArr}; if (scalar(@arr) == 0){ return undef; } foreach my $el (@arr){ $res += $el; } return $res/scalar(@arr); } sub std{ my ($self, $refArr) = @_; my $res = 0.0; my @arr = @{$refArr}; if (scalar(@arr) == 0 || scalar(@arr) == 1){ return undef; } my $m = $self->mean($refArr); foreach my $el (@arr) { $res += ($el-$m)*($el-$m); } $res = sqrt($res/(scalar(@arr) - 1)); return $res; } sub calcMeanStdWithoutOutliers{ my ($self, $refArr) = @_; my @arr = @{$refArr}; my @nArr ; my $m = $self->mean($refArr); my $std = $self->std($refArr); foreach my $el (@arr){ if ($el - $m >= $OUTLIER_CUTOFF * $std){ push @nArr, $el; } } $m = $self->mean(\@nArr); $std = $self->std(\@nArr); return ($m, $std); } #----------------------------------------------- # patch for tens score - Grishin's score # #----------------------------------------------- sub uploadGrishinTenS{ my ($self, $fileName) = @_; my ($target, $domain, $model_type); # model_type first or best # T0819-D1.GrishinScores.best.csv if ($fileName =~ m/(T0[0-9]{3})-D([1-6])\.GrishinScores\.([a-z]{4,5})\.csv/){ $target = $1; $domain = $2; $model_type = $3; } if ($fileName =~ m/(T[acspx][0-9]{3})\.GrishinScores\.([a-z]{4,5})\.csv/){ $target = $1; $domain = 0; $model_type = $2; } if ($fileName =~ m/(T[acspx][0-9]{3})-D([1-6])\.GrishinScores\.([a-z]{4,5})\.csv/){ $target = $1; $domain = $2; $model_type = $3; } if (!defined($target) || !defined($domain) || !defined($model_type)){ # do nothing return; } my $targets_manager = new TargetsManager(); my $target_id = $targets_manager->get_id_by_name($target); my $record = { targets_id => $target_id, domain => $domain, groups_id => 0, tens => '', }; my $groups_manager = new GroupsManager(); # parse file open F, "< $fileName"; my $l = ; # read header while(defined($l = )){ my @tokens = split(/\s+/, $l); my $gr_code = $tokens[1]; my $tens = $tokens[15]; $gr_code =~ s/^TS//; $gr_code =~ s/^0+//; my $groups_id = $groups_manager->get_id_by_code($gr_code); $record->{groups_id} = $groups_id; $record->{tens} = $tens; my $table ; if ($model_type eq 'first'){ $table = "zscores_1m"; my $id = $self->get_id_record($record, $table); if ($id > 0){ $record->{id} = $id; $self->update($record, $table); } $table = "zscores_1ms"; $id = $self->get_id_record($record, $table); if ($id > 0){ $record->{id} = $id; $self->update($record, $table); } } elsif ($model_type eq 'best'){ $table = "zscores_bm"; my $id = $self->get_id_record($record, $table); if ($id > 0){ $record->{id} = $id; $self->update($record, $table); } $table = "zscores_bms"; $id = $self->get_id_record($record, $table); if ($id > 0){ $record->{id} = $id; $self->update($record, $table); } } # last; } close F; } ################################################## # # methods for retrieving data from database # ################################################### sub get_rows{ my ($self, $param) = @_; my $table; if ($param->{gr_type} eq 'server_only'){ if ($param->{model_type} eq 'first'){ $table = 'zscores_1ms'; } else { $table = 'zscores_bms'; } } else { if ($param->{model_type} eq 'first'){ $table = 'zscores_1m'; } else { $table = 'zscores_bm'; } } my $subque = ''; if ($param->{tbm_hard} eq 'on'){ $subque .= "1,"; } if ($param->{tbm} eq 'on'){ $subque .= "2,"; } if ($param->{tbmfm} eq 'on'){ $subque .= "4,"; } if ($param->{fm} eq 'on'){ $subque .= "3,"; } if ($param->{fm_sp} eq 'on'){ $subque .= "6,"; } $subque =~ s/,$//; if ($subque ne ''){ $subque = " WHERE d.domain_classifications_id IN ($subque) AND t.name SIMILAR TO \'T%\' "; } else { $subque = " WHERE t.name SIMILAR TO \'T%\' "; } my $subque2 = ''; if (defined($param->{xray}) && ($param->{xray} eq 'on')){ $subque2 .= "0,"; } if (defined($param->{nmr}) && ($param->{nmr} eq 'on')){ $subque2 .= "1,"; } if (defined($param->{cryoem}) && ($param->{cryoem} eq 'on')){ $subque2 .= "2,"; } $subque2 =~ s/,$//; if ($subque2 ne ''){ if ($subque eq '') { $subque2 = " WHERE ss.is_nmr IN ($subque2)"; } else { $subque2 = " AND ss.is_nmr IN ($subque2)"; } } # query to get no_domains my $query = "SELECT COUNT(DISTINCT d.id) FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id JOIN casp13.domains d ON (d.targets_id=t.id AND z.domain=d.index) JOIN casp13.groups gr ON z.groups_id=gr.id JOIN casp13.submitted_sequences ss ON t.submitted_sequences_id=ss.id $subque $subque2 "; my $sth = $self->{_database}->query($query); my $no_domains; if (defined($sth) && ($sth->rows() > 0)){ ($no_domains) = $sth->fetchrow_array(); } # main query my %weights = %{$param->{weights}}; my $sum_minus2_query = ''; my $sum_0_query = ''; my $avrg_minus2_query = ''; my $avrg_0_query = ''; foreach my $score (@SCORES){ #if ($score eq 'rf'){next;} #if ($score eq 'qse'){next;} if ($score eq 'tens'){next;} if ($score eq 'ace_rms_all'){next;} if ($score eq 'rdc1'){next;} if ($score eq 'rdc2'){next;} if ($score eq 'dp'){next;} if (defined($weights{"w_$score"})){ $sum_minus2_query .= sprintf("%.3f*z.%s\+", $weights{"w_$score"}, $score); $sum_0_query .= sprintf("%.3f*(CASE WHEN z.%s > 0 THEN z.%s ELSE 0.0 END)\+", $weights{"w_$score"}, $score, $score); } } $sum_minus2_query =~ s/\+$//; $sum_0_query =~ s/\+$//; $avrg_minus2_query = $sum_minus2_query; $avrg_0_query = $sum_0_query; $query = "SELECT gr.code, gr.name, count(z.*) as count_domains, SUM($sum_minus2_query) as sum_minus2, SUM($sum_0_query) as sum_0, AVG($avrg_minus2_query) as avg_mimus2, AVG($avrg_0_query) as avg_0 FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id JOIN casp13.domains d ON (d.targets_id=t.id AND z.domain=d.index) JOIN casp13.groups gr ON z.groups_id=gr.id JOIN casp13.submitted_sequences ss ON t.submitted_sequences_id=ss.id $subque $subque2 GROUP BY gr.code, gr.name ORDER BY sum_minus2 "; $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows() > 0)){ my @data; while(my ($gr_code, $gr_name, $count_domains, $sum_minus2, $sum_0, $avg_minus2, $avg_0) = $sth->fetchrow_array()){ push @data, { GR_CODE => sprintf("%03d", $gr_code), GR_NAME => $gr_name, COUNT_DOMAINS => $count_domains, SUM_MINUS2 => (defined($sum_minus2)?$sum_minus2: 0) + ($no_domains - $count_domains) * $OUTLIER_CUTOFF, SUM_0 => (defined($sum_0) ? $sum_0 : 0), AVG_MINUS2 => (defined($avg_minus2) ? $avg_minus2 : 0), AVG_0 => $avg_0 }; } return $self->calcFinalRanks(\@data); } } sub calcFinalRanks{ my ($self, $refData) = @_; my @data = @{$refData}; my @KEYS = qw(SUM_MINUS2 AVG_MINUS2 AVG_0 SUM_0); foreach my $key (@KEYS){ @data = sort {$b->{$key}<=>$a->{$key}} @data; my $cur_rank = 1; my $prev_value; for (my $i = 0; $i < scalar(@data); $i++){ if ($i == 0){ $prev_value = $data[$i]->{$key}; $data[$i]->{"RANK_$key"} = $cur_rank; next; } if ($prev_value != $data[$i]->{$key}){ $prev_value = $data[$i]->{$key}; $cur_rank = $i + 1; } $data[$i]->{"RANK_$key"} = $cur_rank; } } for(my $index = 0; $index < scalar(@data); $index++){ $data[$index]->{"INDEX"} = $index + 1; } return @data; } sub get_refinement_rows{ my ($self, $param) = @_; my $table; if ($param->{model_type} eq 'first'){ $table = 'zscores_1m'; } else { $table = 'zscores_bm'; } # count TR targets my $subque = "WHERE t.name similar to \'R%\'"; my $query = "SELECT COUNT(DISTINCT t.id) FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id $subque "; my $sth = $self->{_database}->query($query); my $no_domains; if (defined($sth) && ($sth->rows() > 0)){ ($no_domains) = $sth->fetchrow_array(); } # main query my %weights = %{$param->{weights}}; my $sum_minus2_query = ''; my $sum_0_query = ''; my $avrg_minus2_query = ''; my $avrg_0_query = ''; foreach my $score (@SCORES){ if ($score eq 'tens'){next;} if ($score eq 'rf'){next;} if ($score eq 'rdc1'){next;} if ($score eq 'rdc2'){next;} if ($score eq 'dp'){next;} #---if ($score eq 'qse'){next;} #if ($score eq 'ace_rms_all'){next;} if (defined($weights{"w_$score"})){ $sum_minus2_query .= sprintf("%.3f*z.%s\+", $weights{"w_$score"}, $score); $sum_0_query .= sprintf("%.3f*(CASE WHEN z.%s > 0 THEN z.%s ELSE 0.0 END)\+", $weights{"w_$score"}, $score, $score); } } $sum_minus2_query =~ s/\+$//; $sum_0_query =~ s/\+$//; $avrg_minus2_query = $sum_minus2_query; $avrg_0_query = $sum_0_query; $query = "SELECT gr.code, gr.name, count(z.*) as count_domains, SUM($sum_minus2_query) as sum_minus2, SUM($sum_0_query) as sum_0, AVG($avrg_minus2_query) as avg_mimus2, AVG($avrg_0_query) as avg_0 FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id JOIN casp13.groups gr ON z.groups_id=gr.id $subque GROUP BY gr.code, gr.name ORDER BY sum_minus2 "; $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows()>0)){ my @data; while(my ($gr_code, $gr_name, $count_domains, $sum_minus2, $sum_0, $avg_minus2, $avg_0) = $sth->fetchrow_array()){ push @data, { GR_CODE => sprintf("%03d", $gr_code), GR_NAME => $gr_name, COUNT_DOMAINS => $count_domains, SUM_MINUS2 => $sum_minus2 + ($no_domains - $count_domains) * $OUTLIER_CUTOFF, SUM_0 => $sum_0, AVG_MINUS2 => $avg_minus2, AVG_0 => $avg_0 }; } return $self->calcFinalRanks(\@data); } } # the method handles with both whole targets and domains # if a domain(s) is defined for a target te results for a domain is picked up # if not the results for the whole target are taken sub get_refinement_domain_rows{ my ($self, $param) = @_; my $table; if ($param->{model_type} eq 'first'){ $table = 'zscores_1m'; } else { $table = 'zscores_bm'; } # count TR targets # my $subque = "WHERE t.name similar to \'TR%\'"; my $subque = "WHERE z.id in (SELECT z.id FROM casp13.$table z JOIN casp13.targets t ON t.id=z.targets_id WHERE t.name SIMILAR TO 'R%' AND z.domain<>0 UNION SELECT z.id FROM casp13.$table z JOIN casp13.targets t ON t.id=z.targets_id WHERE t.name SIMILAR TO 'R%' AND z.domain=0 AND t.name NOT IN ( SELECT DISTINCT t.name FROM casp13.$table z JOIN casp13.targets t ON t.id=z.targets_id WHERE t.name SIMILAR TO 'R%' AND z.domain<>0) )"; my $query = "SELECT COUNT(DISTINCT (t.id, z.domain)) FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id $subque "; my $sth = $self->{_database}->query($query); my $no_domains; if (defined($sth) && ($sth->rows() > 0)){ ($no_domains) = $sth->fetchrow_array(); } # main query my %weights = %{$param->{weights}}; my $sum_minus2_query = ''; my $sum_0_query = ''; my $avrg_minus2_query = ''; my $avrg_0_query = ''; foreach my $score (@SCORES){ if ($score eq 'tens'){next;} if ($score eq 'rf'){next;} if ($score eq 'rdc1'){next;} if ($score eq 'rdc2'){next;} if ($score eq 'dp'){next;} #if ($score eq 'qse'){next;} #if ($score eq 'ace_rms_all'){next;} if (defined($weights{"w_$score"})){ $sum_minus2_query .= sprintf("%.3f*z.%s\+", $weights{"w_$score"}, $score); $sum_0_query .= sprintf("%.3f*(CASE WHEN z.%s > 0 THEN z.%s ELSE 0.0 END)\+", $weights{"w_$score"}, $score, $score); } } $sum_minus2_query =~ s/\+$//; $sum_0_query =~ s/\+$//; $avrg_minus2_query = $sum_minus2_query; $avrg_0_query = $sum_0_query; $query = "SELECT gr.code, gr.name, count(z.*) as count_domains, SUM($sum_minus2_query) as sum_minus2, SUM($sum_0_query) as sum_0, AVG($avrg_minus2_query) as avg_minus2, AVG($avrg_0_query) as avg_0 FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id JOIN casp13.groups gr ON z.groups_id=gr.id $subque GROUP BY gr.code, gr.name ORDER BY sum_minus2 "; $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows()>0)) { my @data; while(my ($gr_code, $gr_name, $count_domains, $sum_minus2, $sum_0, $avg_minus2, $avg_0) = $sth->fetchrow_array()){ push @data, { GR_CODE => sprintf("%03d", $gr_code), GR_NAME => $gr_name, COUNT_DOMAINS => $count_domains, SUM_MINUS2 => $sum_minus2 + ($no_domains - $count_domains) * $OUTLIER_CUTOFF, SUM_0 => $sum_0, AVG_MINUS2 => $avg_minus2, AVG_0 => $avg_0 }; } return $self->calcFinalRanks(\@data); } } # the method handles with both whole targets and domains # if a domain(s) is defined for a target te results for a domain is picked up # if not the results for the domain designated as D1 are taken # the difference with method get_refinement_domain2_rows is in how method treats whole targets sub get_refinement_domain2_rows{ my ($self, $param) = @_; my $table; if ($param->{model_type} eq 'first'){ $table = 'zscores_1m'; } else { $table = 'zscores_bm'; } my $where = ' WHERE 1=1 '; my $dom_class = ''; if ($param->{tbm_hard} eq 'on'){ $dom_class .= "1,"; } if ($param->{tbm} eq 'on'){ $dom_class .= "2,"; } if ($param->{tbmfm} eq 'on'){ $dom_class .= "4,"; } if ($param->{fm} eq 'on'){ $dom_class .= "3,"; } $dom_class =~ s/,$//; if ($dom_class ne ''){ $where .= " AND d.domain_classifications_id in ($dom_class) "; } # count TR targets my $subque_z = "(SELECT zz.*, t2.id AS t_targets_id, CASE WHEN position('D' in t1.name)=0 THEN 1 ELSE substring(t1.name from position('D' in t1.name)+1 for 1)::integer END AS t_domain FROM casp13.$table zz JOIN casp13.targets t1 ON t1.id=zz.targets_id JOIN casp13.targets t2 ON 'T'||substring(t1.name from 2 for case when position('D' in t1.name)=0 THEN char_length(t1.name)-1 ELSE position('D' in t1.name)-3 END )::text=t2.name::text WHERE t1.name similar to 'R%') z "; my $query = "SELECT COUNT(DISTINCT (z.t_targets_id, z.t_domain)) FROM $subque_z JOIN casp13.targets t ON z.t_targets_id=t.id JOIN casp13.domains d ON d.targets_id=t.id AND z.t_domain=d.index $where "; my $sth = $self->{_database}->query($query); my $no_domains; if (defined($sth) && ($sth->rows() > 0)){ ($no_domains) = $sth->fetchrow_array(); } # main query my %weights = %{$param->{weights}}; my $sum_minus2_query = ''; my $sum_0_query = ''; my $avrg_minus2_query = ''; my $avrg_0_query = ''; foreach my $score (@SCORES){ if ($score eq 'tens'){next;} if ($score eq 'rf'){next;} if ($score eq 'rdc1'){next;} if ($score eq 'rdc2'){next;} if ($score eq 'dp'){next;} #if ($score eq 'qse'){next;} #if ($score eq 'ace_rms_all'){next;} if (defined($weights{"w_$score"})){ $sum_minus2_query .= sprintf("%.3f*z.%s\+", $weights{"w_$score"}, $score); $sum_0_query .= sprintf("%.3f*(CASE WHEN z.%s > 0 THEN z.%s ELSE 0.0 END)\+", $weights{"w_$score"}, $score, $score); } } $sum_minus2_query =~ s/\+$//; $sum_0_query =~ s/\+$//; $avrg_minus2_query = $sum_minus2_query; $avrg_0_query = $sum_0_query; $query = "SELECT gr.code, gr.name, count(z.*) as count_domains, SUM($sum_minus2_query) as sum_minus2, SUM($sum_0_query) as sum_0, AVG($avrg_minus2_query) as avg_minus2, AVG($avrg_0_query) as avg_0 FROM $subque_z JOIN casp13.targets t ON z.t_targets_id=t.id JOIN casp13.domains d ON d.targets_id=t.id AND z.t_domain=d.index JOIN casp13.groups gr ON z.groups_id=gr.id $where GROUP BY gr.code, gr.name ORDER BY sum_minus2 "; $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows()>0)) { my @data; while(my ($gr_code, $gr_name, $count_domains, $sum_minus2, $sum_0, $avg_minus2, $avg_0) = $sth->fetchrow_array()){ push @data, { GR_CODE => sprintf("%03d", $gr_code), GR_NAME => $gr_name, COUNT_DOMAINS => $count_domains, SUM_MINUS2 => $sum_minus2 + ($no_domains - $count_domains) * $OUTLIER_CUTOFF, SUM_0 => $sum_0, AVG_MINUS2 => $avg_minus2, AVG_0 => $avg_0 }; } return $self->calcFinalRanks(\@data); } } sub get_Tacspx_rows{ my ($self, $param) = @_; my $table; if ($param->{model_type} eq 'first'){ $table = 'zscores_1m'; } else { $table = 'zscores_bm'; } # count targets my $subque ; if ($param->{target_flag} eq 'A') { $subque = "WHERE t.name similar to \'[Aa]%\'"; } elsif ($param->{target_flag} eq 'N') { $subque = "WHERE t.name similar to \'[Nn]%\'"; } elsif ($param->{target_flag} eq 'S') { $subque = "WHERE t.name similar to \'[Ss]%\'"; } elsif ($param->{target_flag} eq 'F') { $subque = "WHERE t.name similar to \'[Ff]%\'"; } elsif ($param->{target_flag} eq 'X') { $subque = "WHERE t.name similar to \'[Xx]%\'"; } elsif ($param->{target_flag} eq 'L') { $subque = "WHERE t.name similar to \'[Ll]%\'"; } else { $subque = "WHERE t.name similar to \'[NSFAXLnsfaxl]%\'"; } $subque .= " AND t.cancellation_status=0 "; if (defined($param->{perdomain_flag}) && $param->{perdomain_flag} eq 'true' ) { $subque .= " AND (z.domain<>0 OR (z.domain=0 AND t.name SIMILAR TO '%%-D%%')) "; } else { $subque .= " AND z.domain=0 "; } my $query = "SELECT COUNT(DISTINCT t.id) FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id $subque "; my $sth = $self->{_database}->query($query); my $no_domains; if (defined($sth) && ($sth->rows() > 0)){ ($no_domains) = $sth->fetchrow_array(); } # main query my %weights = %{$param->{weights}}; my $sum_minus2_query = ''; my $sum_0_query = ''; my $avrg_minus2_query = ''; my $avrg_0_query = ''; foreach my $score (@SCORES){ if ($score eq 'tens'){next;} if ($score eq 'rf'){next;} if ($score eq 'qse'){next;} if ($score eq 'ace_rms_all'){next;} if ($param->{target_flag} ne 'N'){ if ($score eq 'rdc1'){next;} if ($score eq 'rdc2'){next;} if ($score eq 'dp'){next;} } if (defined($weights{"w_$score"})){ $sum_minus2_query .= sprintf("%.3f*z.%s\+", $weights{"w_$score"}, $score); $sum_0_query .= sprintf("%.3f*(CASE WHEN z.%s > 0 THEN z.%s ELSE 0.0 END)\+", $weights{"w_$score"}, $score, $score); } } $sum_minus2_query =~ s/\+$//; $sum_0_query =~ s/\+$//; $avrg_minus2_query = $sum_minus2_query; $avrg_0_query = $sum_0_query; $query = "SELECT gr.code, gr.name, count(z.*) as count_domains, SUM($sum_minus2_query) as sum_minus2, SUM($sum_0_query) as sum_0, AVG($avrg_minus2_query) as avg_mimus2, AVG($avrg_0_query) as avg_0 FROM casp13.$table z JOIN casp13.targets t ON z.targets_id=t.id JOIN casp13.groups gr ON z.groups_id=gr.id $subque GROUP BY gr.code, gr.name ORDER BY sum_minus2 "; $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows()>0)){ my @data; while(my ($gr_code, $gr_name, $count_domains, $sum_minus2, $sum_0, $avg_minus2, $avg_0) = $sth->fetchrow_array()){ if ($count_domains < 3) {next;} push @data, { GR_CODE => sprintf("%03d", $gr_code), GR_NAME => $gr_name, COUNT_DOMAINS => $count_domains, SUM_MINUS2 => $sum_minus2 + ($no_domains - $count_domains) * $OUTLIER_CUTOFF, SUM_0 => $sum_0, AVG_MINUS2 => $avg_minus2, AVG_0 => $avg_0 }; } return $self->calcFinalRanks(\@data); } } # the method generates a list of targets to be uploaded into db sub getTargetsWaitingToBeProcessed{ my ($self) = @_; my @result; my $query = "SELECT t.name || '-D' || d.index AS full_target FROM casp13.domains d JOIN casp13.targets t ON t.id=d.targets_id WHERE index>=1 AND index<=7 AND t.is_final_eval = 1 AND (t.id,d.index) NOT IN (SELECT DISTINCT tt.id, z.domain FROM casp13.targets tt JOIN casp13.zscores_bms z ON z.targets_id=tt.id UNION SELECT DISTINCT tt.id, z.domain FROM casp13.targets tt JOIN casp13.zscores_bm z ON z.targets_id=tt.id ) UNION SELECT t.name AS full_target FROM casp13.targets t JOIN casp13.results_targets rt ON rt.targets_id=t.id WHERE t.name SIMILAR TO '[RNSFAXLnsfaxl]%' AND t.id NOT IN (SELECT DISTINCT targets_id FROM casp13.zscores_bm) ;"; my $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows()>0)){ while(my ($target) = $sth->fetchrow_array()){ push @result, $target; } @result = sort @result; } return @result; } ########################################################### # methods for bootstraping and ttest ########################################################### sub get_data_for_two_groups{ my ($self, $param, $gr_code1, $gr_code2) = @_; my $table; if ($param->{gr_type} eq 'server_only'){ if ($param->{model_type} eq 'first'){ $table = 'zscores_1ms'; } else { $table = 'zscores_bms'; } } else { if ($param->{model_type} eq 'first'){ $table = 'zscores_1m'; } else { $table = 'zscores_bm'; } } my $subque = ''; if (defined($param->{tbm_hard}) && $param->{tbm_hard} eq 'on'){ $subque .= "1,"; } if (defined($param->{tbm}) && $param->{tbm} eq 'on'){ $subque .= "2,"; } if (defined($param->{fm}) && $param->{fm} eq 'on'){ $subque .= "3,"; } if (defined($param->{tbmfm}) && $param->{tbmfm} eq 'on'){ $subque .= "4,"; } $subque =~ s/,$//; if ($subque ne ''){ $subque = " AND d.domain_classifications_id IN ($subque) "; } my $subque2 = ''; if (defined($param->{target_flag})){ if ($param->{target_flag} eq 'Ta') { $subque2 = " AND t.name similar to \'Ta%\'"; $subque = ''; } elsif ($param->{target_flag} eq 'Tc') { $subque2 = " AND t.name similar to \'Tc%\'"; $subque = ''; } elsif ($param->{target_flag} eq 'Tp') { $subque2 = " AND t.name similar to \'Tp%\'"; $subque = ''; } elsif ($param->{target_flag} eq 'Ts') { $subque2 = " AND t.name similar to \'Ts%\'"; $subque = ''; } elsif ($param->{target_flag} eq 'Tx') { $subque2 = " AND t.name similar to \'Tx%\'"; $subque = ''; } elsif ($param->{target_flag} eq 'TR') { $subque2 = " AND t.name similar to \'TR%\'"; $subque = ''; } # elsif ($param->{target_flag} eq 'TaD') { # $subque2 = " AND t.name similar to \'Ta%\' AND z.domain<>0"; # $subque = ''; # } elsif ($param->{target_flag} eq 'TcD') { # $subque2 = " AND t.name similar to \'Tc%\' AND z.domain<>0"; # $subque = ''; # } elsif ($param->{target_flag} eq 'TpD') { # $subque2 = " AND t.name similar to \'Tp%\' AND z.domain<>0"; # $subque = ''; # } elsif ($param->{target_flag} eq 'TsD') { # $subque2 = " AND t.name similar to \'Ts%\' AND z.domain<>0"; # $subque = ''; # } elsif ($param->{target_flag} eq 'TxD') { # $subque2 = " AND t.name similar to \'Tx%\' AND z.domain<>0"; # $subque = ''; # } if ($param->{target_flag} =~ m/^T[acspx]/){ if (defined($param->{perdomain_flag}) && $param->{perdomain_flag} eq 'true' ) { $subque2 .= " AND z.domain<>0 "; } else { $subque2 .= " AND z.domain=0 "; } } $subque2 .= " AND t.cancellation_status=0 "; } # main query my %weights = %{$param->{weights}}; my $score_query = ''; foreach my $score (@SCORES){ if ($score eq 'rf'){next;} if ($score eq 'ace_rms_all'){next;} if ($score eq 'tens'){next;} if (defined($param->{target_flag}) && $score eq 'qse'){next;} if (defined($weights{"w_$score"})){ $score_query .= sprintf("%.3f*z.%s\+", $weights{"w_$score"}, $score); } } $score_query =~ s/\+$//; my $intersect_query = "SELECT DISTINCT targets_id||'_'||domain as dom_id FROM casp13.$table z JOIN casp13.groups gr ON gr.id=z.groups_id WHERE gr.code=$gr_code1 INTERSECT SELECT DISTINCT targets_id||'_'||domain as dom_id FROM casp13.$table z JOIN casp13.groups gr ON gr.id=z.groups_id WHERE gr.code=$gr_code2"; my $query = "SELECT gr.code, gr.name, z.targets_id || \'_\' || domain AS dom_id, $score_query AS score FROM casp13.$table z JOIN casp13.groups gr ON z.groups_id=gr.id ". ($subque eq '' ? '' : " JOIN casp13.domains d ON (d.targets_id=z.targets_id AND z.domain=d.index)"). " JOIN casp13.targets t ON z.targets_id=t.id WHERE z.targets_id||\'_\'||domain IN ($intersect_query) AND gr.code IN ($gr_code1, $gr_code2) $subque $subque2 ORDER BY dom_id DESC, gr.code ASC"; # print $query."\n"; my $sth = $self->{_database}->query($query); if (defined($sth) && ($sth->rows() > 0)){ my @data1; my @data2; my %tmp_hash; while(my ($gr_code, $gr_name, $dom_id, $score) = $sth->fetchrow_array()){ if (defined($param->{target_flag}) && $param->{target_flag} =~ m/^TR/){ my ($target_id, $dom) = split(/_/, $dom_id); if ($dom == 0 && exists $tmp_hash{$target_id}){ next; } if ($dom > 0){ $tmp_hash{$target_id} = 1; } } if($gr_code1 == $gr_code){ push @data1, { GR_CODE => sprintf("%03d", $gr_code), GR_NAME => $gr_name, DOM_ID => $dom_id, SCORE => $score }; } if ($gr_code2 == $gr_code) { push @data2, { GR_CODE => sprintf("%03d", $gr_code), GR_NAME => $gr_name, DOM_ID => $dom_id, SCORE => $score }; } } return (\@data1,\@data2); } else { return (undef,undef); } } sub stat_significance_test{ my ($self, $param, $gr_code1, $gr_code2) = @_; my ($refArr1, $refArr2) = $self->get_data_for_two_groups($param, $gr_code1, $gr_code2); if (!defined($refArr1) || !defined($refArr2)){ return (0,'-','-');} my $no_common = scalar(@$refArr1); if ( $no_common == 1){ return (0,'-','-');} my @arr1; my @arr2; for (my $i = 0; $i{DOM_ID} eq $$refArr2[$i]->{DOM_ID}){ push @arr1, $$refArr1[$i]->{SCORE}; push @arr2, $$refArr2[$i]->{SCORE}; } } # ttest my ($t, $prob) = Statistics::ttest_matched(\@arr1,\@arr2); # bootstraping my $N = 1000; # number of repetitions my $count = 0 ; my $count_1_over_2 = 0; while(($count++) < $N){ my @indexes = Statistics::sample(scalar(@arr1), scalar(@arr2)); my $sum_1 = 0; my $sum_2 = 0; foreach my $index (@indexes){ $sum_1 += $arr1[$index]; $sum_2 += $arr2[$index]; } if ($sum_1 > $sum_2){ $count_1_over_2++; } } return ($no_common, sprintf("%5.4f",$prob), sprintf("%5.4f",$count_1_over_2/$N)); } sub head2head_test{ my ($self, $param, $gr_code1, $gr_code2) = @_; my ($refArr1, $refArr2) = $self->get_data_for_two_groups($param, $gr_code1, $gr_code2); if (!defined($refArr1) || !defined($refArr2)){ return (0,'-','-','-','-');} my $no_common = scalar(@$refArr1); if ( $no_common == 1){ return (0,'-','-','-','-');} my @arr1; my @arr2; for (my $i = 0; $i{DOM_ID} eq $$refArr2[$i]->{DOM_ID} && defined($$refArr1[$i]->{SCORE}) && defined($$refArr2[$i]->{SCORE})){ push @arr1, $$refArr1[$i]->{SCORE}; push @arr2, $$refArr2[$i]->{SCORE}; } } my $count_1_over_2 = 0; my $count_2_over_1 = 0; for (my $i = 0; $i < scalar(@arr1); $i++){ if ($arr1[$i] > $arr2[$i]) { $count_1_over_2++; } if ($arr2[$i] > $arr1[$i]) { $count_2_over_1++; } } if (scalar(@arr1) == 0){ return (0, '-', '-', '-', '-'); } else { return (scalar(@arr1), sprintf("%4.3f", ($count_1_over_2)/scalar(@arr1)), sprintf("%4.3f",($count_2_over_1)/scalar(@arr1)), $count_1_over_2, $count_2_over_1 ); } } 1;