lib/SGN/Controller/AJAX/Accession_usage.pm

   1 package SGN::Controller::AJAX::Accession_usage;
   2
   3 use Moose;
   4 use Data::Dumper;
   5 use Scalar::Util qw(looks_like_number);
   6 use CXGN::Cross;
   7
   8 BEGIN { extends 'Catalyst::Controller::REST'; }
   9
  10 __PACKAGE__->config(
  11     default   => 'application/json',
  12     stash_key => 'rest',
  13     map       => { 'application/json' => 'JSON' },
  14    );
  15
  16
  17 sub accession_usage_trials: Path('/ajax/accession_usage_trials') :Args(0){
  18
  19     my $self = shift;
  20     my $c = shift;
  21
  22     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
  23     my $schema = $c->dbic_schema("Bio::Chado::Schema", 'sgn_chado', $sp_person_id);
  24     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
  25     my $plot_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_of', 'stock_relationship')->cvterm_id();
  26     my $field_layout_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'field_layout', 'experiment_type')->cvterm_id();
  27
  28     my $dbh = $schema->storage->dbh();
  29
  30     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename, COUNT(DISTINCT project.project_id) AS trials, COUNT(stock_relationship.subject_id)
  31             FROM stock JOIN stock_relationship on (stock.stock_id=stock_relationship.object_id) AND stock_relationship.type_id=?
  32             JOIN nd_experiment_stock ON (stock_relationship.subject_id=nd_experiment_stock.stock_id) AND nd_experiment_stock.type_id =?
  33             JOIN nd_experiment_project USING (nd_experiment_id)
  34             JOIN project USING (project_id) WHERE stock.type_id =? GROUP BY stock.stock_id ORDER BY trials DESC";
  35
  36     my $h = $dbh->prepare($q);
  37     $h->execute($plot_of_type_id, $field_layout_type_id, $accession_type_id);
  38
  39     my@accessions_trials =();
  40     while (my ($accession_id, $accession_name, $trial_count, $plot_count) = $h->fetchrow_array()){
  41
  42         push @accessions_trials,[qq{<a href="/stock/$accession_id/view">$accession_name</a>}, $trial_count, $plot_count];
  43     }
  44
  45     $c->stash->{rest}={data=>\@accessions_trials};
  46
  47 }
  48
  49
  50 sub accession_usage_female: Path('/ajax/accession_usage_female') :Args(0){
  51
  52     my $self = shift;
  53     my $c = shift;
  54     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
  55     my $schema = $c->dbic_schema("Bio::Chado::Schema", undef, $sp_person_id);
  56
  57     my $cross_obj = CXGN::Cross->new({schema => $schema, parent_type => 'female_parent'});
  58     my $data = $cross_obj->get_parents_and_numbers_of_progenies();
  59     my @all_female_parents = @$data;
  60     my @results;
  61     foreach my $each_female_parent (@all_female_parents) {
  62         my $female_id = $each_female_parent->[0];
  63         my $female_name = $each_female_parent->[1];
  64         my $number_of_progenies = $each_female_parent->[2];
  65         push @results, [qq{<a href="/stock/$female_id/view">$female_name</a>},$number_of_progenies];
  66     }
  67     $c->stash->{rest}={data=>\@results};
  68
  69 }
  70
  71
  72 sub accession_usage_male: Path('/ajax/accession_usage_male') :Args(0){
  73
  74     my $self = shift;
  75     my $c = shift;
  76     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
  77     my $schema = $c->dbic_schema("Bio::Chado::Schema", undef, $sp_person_id);
  78
  79     my $cross_obj = CXGN::Cross->new({schema => $schema, parent_type => 'male_parent'});
  80     my $data = $cross_obj->get_parents_and_numbers_of_progenies();
  81     my @all_male_parents = @$data;
  82     my @results;
  83     foreach my $each_male_parent (@all_male_parents) {
  84         my $male_id = $each_male_parent->[0];
  85         my $male_name = $each_male_parent->[1];
  86         my $number_of_progenies = $each_male_parent->[2];
  87         push @results, [qq{<a href="/stock/$male_id/view">$male_name</a>},$number_of_progenies];
  88     }
  89     $c->stash->{rest}={data=>\@results};
  90
  91
  92 }
  93
  94 sub accession_usage_phenotypes: Path('/ajax/accession_usage_phenotypes') :Args(0){
  95     my $self = shift;
  96     my $c = shift;
  97     my $params = $c->req->params() || {};
  98     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
  99     my $schema = $c->dbic_schema("Bio::Chado::Schema", undef, $sp_person_id);
 100
 101     my $round = Math::Round::Var->new(0.01);
 102     my $dbh = $c->dbc->dbh();
 103     my $display = $c->req->param('display');
 104
 105     my $stock_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot', 'stock_type')->cvterm_id();
 106     my $rel_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_of', 'stock_relationship')->cvterm_id();
 107     my $accesion_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
 108
 109     my $limit = $c->req->param('length');
 110     my $offset = $c->req->param('start');
 111
 112     my $limit_clause = '';
 113     my $offset_clause = '';
 114     if (defined($limit)) {
 115         $limit_clause = ' LIMIT '.$limit;
 116     }
 117     if (defined($offset)) {
 118         $offset_clause = ' OFFSET '.$offset;
 119     }
 120
 121     my $h = $dbh->prepare("SELECT (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait,
 122         cvterm.cvterm_id,
 123         count(phenotype.value),
 124         to_char(avg(phenotype.value::real), 'FM999990.990'),
 125         to_char(max(phenotype.value::real), 'FM999990.990'),
 126         to_char(min(phenotype.value::real), 'FM999990.990'),
 127         to_char(stddev(phenotype.value::real), 'FM999990.990'),
 128         accession.uniquename,
 129         accession.stock_id,
 130         count(cvterm.cvterm_id) OVER() AS full_count
 131         FROM cvterm
 132             JOIN phenotype ON (cvterm_id=cvalue_id)
 133             JOIN nd_experiment_phenotype USING(phenotype_id)
 134             JOIN nd_experiment_project USING(nd_experiment_id)
 135             JOIN nd_experiment_stock USING(nd_experiment_id)
 136             JOIN stock as plot USING(stock_id)
 137             JOIN stock_relationship on (plot.stock_id = stock_relationship.subject_id)
 138             JOIN stock as accession on (accession.stock_id = stock_relationship.object_id)
 139             JOIN dbxref ON cvterm.dbxref_id = dbxref.dbxref_id JOIN db ON dbxref.db_id = db.db_id
 140         WHERE phenotype.value~?
 141             AND stock_relationship.type_id=?
 142             AND plot.type_id=?
 143             AND accession.type_id=?
 144         GROUP BY (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text, cvterm.cvterm_id, accession.stock_id, accession.uniquename
 145         ORDER BY cvterm.name ASC
 146         ,accession.uniquename DESC
 147         $limit_clause
 148         $offset_clause;");
 149
 150     my $numeric_regex = '^-?[0-9]+([,.][0-9]+)?$';
 151     $h->execute($numeric_regex, $rel_type_id, $stock_type_id, $accesion_type_id);
 152
 153     my @phenotype_data;
 154
 155     my $total_count;
 156     while (my ($trait, $trait_id, $count, $average, $max, $min, $stddev, $stock_name, $stock_id, $full_count) = $h->fetchrow_array()) {
 157         $total_count = $full_count;
 158         if (looks_like_number($average)){
 159             my $cv = 0;
 160             if ($stddev && $average != 0) {
 161                 $cv = ($stddev /  $average) * 100;
 162                 $cv = $round->round($cv) . '%';
 163             }
 164             if ($average) { $average = $round->round($average); }
 165             if ($min) { $min = $round->round($min); }
 166             if ($max) { $max = $round->round($max); }
 167             if ($stddev) { $stddev = $round->round($stddev); }
 168
 169             my @return_array = ( qq{<a href="/stock/$stock_id/view">$stock_name</a>}, qq{<a href="/cvterm/$trait_id/view">$trait</a>}, $average, $min, $max, $stddev, $cv, $count );
 170             push @phenotype_data, \@return_array;
 171         }
 172     }
 173     my $draw = $c->req->param('draw');
 174     if ($draw){
 175         $draw =~ s/\D//g; # cast to int
 176     }
 177
 178     $c->stash->{rest} = { data => \@phenotype_data, draw => $draw, recordsTotal => $total_count,  recordsFiltered => $total_count };
 179 }
 180
 181
 182 1;