lib/CXGN/Stock/RelatedStocks.pm

   1
   2 package CXGN::Stock::RelatedStocks;
   3
   4 use strict;
   5 use warnings;
   6 use Moose;
   7 use SGN::Model::Cvterm;
   8
   9 has 'dbic_schema' => (isa => 'Bio::Chado::Schema',
  10         is => 'rw',
  11         required => 1,
  12 );
  13
  14 has 'stock_id' => (isa => 'Str',
  15         is => 'rw',
  16         required => 1,
  17 );
  18
  19
  20 sub get_trial_related_stock {
  21     my $self = shift;
  22     my $ stock_id = $self->stock_id;
  23     my $ schema = $self->dbic_schema();
  24     my $plot_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_of', 'stock_relationship')->cvterm_id();
  25     my $plant_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plant_of', 'stock_relationship')->cvterm_id();
  26     my $subplot_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'subplot_of', 'stock_relationship')->cvterm_id();
  27     my $plant_of_subplot_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plant_of_subplot', 'stock_relationship')->cvterm_id();
  28     my $seed_transaction_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seed transaction', 'stock_relationship')->cvterm_id();
  29     my $tissue_sample_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_sample_of', 'stock_relationship')->cvterm_id();
  30
  31     my $q = "SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship
  32             INNER JOIN stock ON (stock_relationship.subject_id = stock.stock_id)
  33             INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
  34             WHERE stock_relationship.object_id = ? AND (stock_relationship.type_id = ?
  35             OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ?)
  36
  37             UNION ALL
  38
  39             SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship
  40             INNER JOIN stock ON (stock_relationship.object_id = stock.stock_id)
  41             INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
  42             WHERE stock_relationship.subject_id = ? AND (stock_relationship.type_id = ?
  43             OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ?) ";
  44
  45     my $h = $schema->storage->dbh()->prepare($q);
  46
  47     $h->execute($stock_id, $plot_of_type_id, $plant_of_type_id, $subplot_of_type_id, $plant_of_subplot_type_id, $seed_transaction_type_id, $tissue_sample_of_type_id, $stock_id, $plot_of_type_id, $plant_of_type_id, $subplot_of_type_id, $plant_of_subplot_type_id, $seed_transaction_type_id, $tissue_sample_of_type_id);
  48
  49     my @trial_related_stock =();
  50     while(my($stock_id, $stock_name, $cvterm_name) = $h->fetchrow_array()){
  51
  52       push @trial_related_stock, [$stock_id, $stock_name, $cvterm_name]
  53     }
  54
  55     return\@trial_related_stock;
  56 }
  57
  58
  59 sub get_progenies {
  60     my $self = shift;
  61     my $stock_id = $self->stock_id;
  62     my $ schema = $self->dbic_schema();
  63     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
  64     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
  65     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
  66
  67     my $q = "SELECT cvterm.name, stock.stock_id, stock.uniquename FROM stock_relationship
  68              INNER JOIN stock ON (stock_relationship.object_id = stock.stock_id)
  69              INNER JOIN cvterm ON (stock_relationship.type_id =cvterm.cvterm_id)
  70              WHERE stock_relationship.subject_id = ? AND(stock_relationship.type_id =?
  71              OR stock_relationship.type_id = ?) AND stock.type_id = ? ORDER BY cvterm.name DESC, stock.uniquename ASC";
  72
  73     my $h = $schema->storage->dbh->prepare($q);
  74     $h->execute($stock_id, $female_parent_type_id, $male_parent_type_id, $accession_type_id);
  75
  76     my @progenies =();
  77         while(my($cvterm_name, $stock_id, $stock_name) = $h->fetchrow_array()){
  78         push @progenies, [$cvterm_name, $stock_id, $stock_name]
  79         }
  80
  81         return\@progenies;
  82
  83 }
  84
  85
  86 sub get_group_and_member {
  87     my $self = shift;
  88     my $ stock_id = $self->stock_id;
  89     my $ schema = $self->dbic_schema();
  90     my $member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'member_of', 'stock_relationship')->cvterm_id();
  91     my $q = "SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
  92              ON (stock_relationship.object_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
  93              WHERE stock_relationship.subject_id = ? and stock_relationship.type_id = ?
  94
  95              UNION ALL
  96
  97              SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
  98              ON (stock_relationship.subject_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
  99              WHERE stock_relationship.object_id = ? and stock_relationship.type_id = ?
 100
 101              ";
 102
 103     my $h = $schema->storage->dbh->prepare($q);
 104     $h->execute($stock_id, $member_of_type_id, $stock_id, $member_of_type_id);
 105
 106     my @group =();
 107         while(my($stock_id, $stock_name, $cvterm_name) = $h->fetchrow_array()){
 108         push @group, [$stock_id, $stock_name, $cvterm_name]
 109         }
 110
 111         return\@group;
 112
 113 }
 114
 115
 116 sub get_stock_for_tissue {
 117     my $self = shift;
 118     my $ stock_id = $self->stock_id;
 119     my $ schema = $self->dbic_schema();
 120     my $tissue_sample_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_sample_of', 'stock_relationship')->cvterm_id();
 121     my $q = "SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
 122              ON (stock_relationship.object_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 123              WHERE stock_relationship.subject_id = ? and stock_relationship.type_id = ?
 124
 125              UNION ALL
 126
 127              SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
 128              ON (stock_relationship.subject_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 129              WHERE stock_relationship.object_id = ? and stock_relationship.type_id = ?
 130
 131              ";
 132
 133     my $h = $schema->storage->dbh->prepare($q);
 134     $h->execute($stock_id, $tissue_sample_of_type_id, $stock_id, $tissue_sample_of_type_id);
 135
 136     my @tissue_stocks =();
 137         while(my($stock_id, $stock_name, $cvterm_name) = $h->fetchrow_array()){
 138         push @tissue_stocks, [$stock_id, $stock_name, $cvterm_name]
 139         }
 140
 141         return\@tissue_stocks;
 142
 143 }
 144
 145
 146
 147 1;