lib/SGN/Controller/AJAX/Accession_usage.pm

   1 package SGN::Controller::AJAX::Accession_usage;
   2
   3 use Moose;
   4 use Data::Dumper;
   5
   6 BEGIN { extends 'Catalyst::Controller::REST'; }
   7
   8 __PACKAGE__->config(
   9     default   => 'application/json',
  10     stash_key => 'rest',
  11     map       => { 'application/json' => 'JSON', 'text/html' => 'JSON' },
  12    );
  13
  14
  15 sub accession_usage_trials: Path('/ajax/accession_usage_trials') :Args(0){
  16
  17     my $self = shift;
  18     my $c = shift;
  19
  20     my $schema = $c->dbic_schema("Bio::Chado::Schema", 'sgn_chado');
  21     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
  22     my $plot_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_of', 'stock_relationship')->cvterm_id();
  23     my $field_layout_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'field_layout', 'experiment_type')->cvterm_id();
  24
  25     my $dbh = $schema->storage->dbh();
  26
  27     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename, COUNT(DISTINCT project.project_id) AS trials, COUNT(stock_relationship.subject_id)
  28             FROM stock JOIN stock_relationship on (stock.stock_id=stock_relationship.object_id) AND stock_relationship.type_id=?
  29             JOIN nd_experiment_stock ON (stock_relationship.subject_id=nd_experiment_stock.stock_id) AND nd_experiment_stock.type_id =?
  30             JOIN nd_experiment_project USING (nd_experiment_id)
  31             JOIN project USING (project_id) WHERE stock.type_id =? GROUP BY stock.stock_id ORDER BY trials DESC";
  32
  33     my $h = $dbh->prepare($q);
  34     $h->execute($plot_of_type_id, $field_layout_type_id, $accession_type_id);
  35
  36     my@accessions_trials =();
  37     while (my ($accession_id, $accession_name, $trial_count, $plot_count) = $h->fetchrow_array()){
  38
  39         push @accessions_trials,[qq{<a href="/stock/$accession_id/view">$accession_name</a>}, $trial_count, $plot_count];
  40     }
  41
  42     $c->stash->{rest}={data=>\@accessions_trials};
  43
  44 }
  45
  46
  47 sub accession_usage_female: Path('/ajax/accession_usage_female') :Args(0){
  48
  49     my $self = shift;
  50     my $c = shift;
  51
  52     my $schema = $c->dbic_schema("Bio::Chado::Schema");
  53     my $female_parent_typeid = $c->model("Cvterm")->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
  54   #  my $cross_typeid = $c->model("Cvterm")->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id()
  55     my $accession_typeid = $c->model("Cvterm")->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
  56     my $dbh = $schema->storage->dbh();
  57
  58 #   my $q = "SELECT DISTINCT female_parent.stock_id, female_parent.uniquename, COUNT (DISTINCT cross_id.stock_id) AS cross_number
  59 #            FROM stock as female_parent JOIN stock_relationship ON (female_parent.stock_id=stock_relationship.subject_id) AND stock_relationship.type_id=?
  60 #            JOIN stock AS cross_id ON (cross_id.stock_id=stock_relationship.object_id) AND cross_id.type_id=?
  61 #            GROUP BY female_parent.stock_id ORDER BY cross_number DESC";
  62
  63     my $q = "SELECT DISTINCT female_parent.stock_id, female_parent.uniquename, COUNT (DISTINCT stock_relationship.object_id) AS num_of_progenies
  64             FROM stock_relationship INNER JOIN stock AS check_type ON (stock_relationship.object_id = check_type.stock_id)
  65             INNER JOIN stock AS female_parent ON (stock_relationship.subject_id = female_parent.stock_id)
  66             WHERE stock_relationship.type_id = ? AND check_type.type_id = ?
  67             GROUP BY female_parent.stock_id ORDER BY num_of_progenies DESC";
  68
  69     my $h = $dbh->prepare($q);
  70     $h->execute($female_parent_typeid, $accession_typeid);
  71
  72     my@female_parents =();
  73     while (my ($female_parent_id, $female_parent_name, $num_of_progenies) = $h->fetchrow_array()){
  74
  75     push @female_parents, [qq{<a href="/stock/$female_parent_id/view">$female_parent_name</a>},$num_of_progenies];
  76         }
  77
  78     $c->stash->{rest}={data=>\@female_parents};
  79 }
  80
  81 sub accession_usage_male: Path('/ajax/accession_usage_male') :Args(0){
  82
  83     my $self = shift;
  84     my $c = shift;
  85
  86     my $schema = $c->dbic_schema("Bio::Chado::Schema");
  87     my $male_parent_typeid = $c->model("Cvterm")->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
  88     #my $cross_typeid = $c->model("Cvterm")->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
  89     my $accession_typeid = $c->model("Cvterm")->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
  90     my $dbh = $schema->storage->dbh();
  91
  92     #my $q = "SELECT DISTINCT male_parent.stock_id, male_parent.uniquename, COUNT (DISTINCT cross_id.stock_id) AS cross_number
  93     #           FROM stock as male_parent JOIN stock_relationship ON (male_parent.stock_id=stock_relationship.subject_id) AND stock_relationship.type_id=?
  94     #           JOIN stock AS cross_id ON (cross_id.stock_id=stock_relationship.object_id) and cross_id.type_id=?
  95     #           GROUP BY male_parent.stock_id ORDER BY cross_number DESC";
  96
  97     my $q = "SELECT DISTINCT male_parent.stock_id, male_parent.uniquename, COUNT (DISTINCT stock_relationship.object_id) AS num_of_progenies
  98             FROM stock_relationship INNER JOIN stock AS check_type ON (stock_relationship.object_id = check_type.stock_id)
  99             INNER JOIN stock AS male_parent ON (stock_relationship.subject_id = male_parent.stock_id)
 100             WHERE stock_relationship.type_id = ? AND check_type.type_id = ?
 101             GROUP BY male_parent.stock_id ORDER BY num_of_progenies DESC";
 102
 103     my $h = $dbh->prepare($q);
 104     $h->execute($male_parent_typeid, $accession_typeid);
 105
 106     my@male_parents =();
 107     while (my ($male_parent_id, $male_parent_name, $num_of_progenies) = $h->fetchrow_array()){
 108
 109     push @male_parents, [qq{<a href="/stock/$male_parent_id/view">$male_parent_name</a>}, $num_of_progenies];
 110     }
 111
 112     $c->stash->{rest}={data=>\@male_parents};
 113 }
 114
 115   1;