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 => 'Maybe[Int]',
  15         is => 'rw',
  16 );
  17
  18
  19 sub get_trial_related_stock {
  20     my $self = shift;
  21     my $stock_id = $self->stock_id;
  22     my $schema = $self->dbic_schema();
  23     my $plot_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_of', 'stock_relationship')->cvterm_id();
  24     my $plant_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plant_of', 'stock_relationship')->cvterm_id();
  25     my $subplot_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'subplot_of', 'stock_relationship')->cvterm_id();
  26     my $plant_of_subplot_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plant_of_subplot', 'stock_relationship')->cvterm_id();
  27 #    my $seed_transaction_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seed transaction', 'stock_relationship')->cvterm_id();
  28     my $tissue_sample_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_sample_of', 'stock_relationship')->cvterm_id();
  29
  30     my $q = "SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship
  31             INNER JOIN stock ON (stock_relationship.subject_id = stock.stock_id)
  32             INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
  33             WHERE stock_relationship.object_id = ? AND (stock_relationship.type_id = ?
  34             OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? )
  35
  36             UNION ALL
  37
  38             SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship
  39             INNER JOIN stock ON (stock_relationship.object_id = stock.stock_id)
  40             INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
  41             WHERE stock_relationship.subject_id = ? AND (stock_relationship.type_id = ?
  42             OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? OR stock_relationship.type_id = ? ) ";
  43
  44     my $h = $schema->storage->dbh()->prepare($q);
  45
  46     $h->execute($stock_id, $plot_of_type_id, $plant_of_type_id, $subplot_of_type_id, $plant_of_subplot_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, $tissue_sample_of_type_id);
  47
  48     my @trial_related_stock =();
  49     while(my($stock_id, $stock_name, $cvterm_name) = $h->fetchrow_array()){
  50
  51       push @trial_related_stock, [$stock_id, $stock_name, $cvterm_name]
  52     }
  53
  54     return\@trial_related_stock;
  55 }
  56
  57
  58 sub get_progenies {
  59     my $self = shift;
  60     my $stock_id = $self->stock_id;
  61     my $schema = $self->dbic_schema();
  62     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
  63     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
  64     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
  65
  66     my $q = "SELECT cvterm.name, stock.stock_id, stock.uniquename, stock_relationship.value FROM stock_relationship
  67              INNER JOIN stock ON (stock_relationship.object_id = stock.stock_id)
  68              INNER JOIN cvterm ON (stock_relationship.type_id =cvterm.cvterm_id)
  69              WHERE stock_relationship.subject_id = ? AND(stock_relationship.type_id =?
  70              OR stock_relationship.type_id = ?) AND stock.type_id = ? ORDER BY cvterm.name DESC, stock.uniquename ASC";
  71
  72     my $h = $schema->storage->dbh->prepare($q);
  73     $h->execute($stock_id, $female_parent_type_id, $male_parent_type_id, $accession_type_id);
  74
  75     my @progenies =();
  76         while(my($cvterm_name, $stock_id, $stock_name, $cross_type) = $h->fetchrow_array()){
  77         push @progenies, [$cvterm_name, $stock_id, $stock_name, $cross_type]
  78         }
  79
  80         return\@progenies;
  81
  82 }
  83
  84
  85 sub get_group_and_member {
  86     my $self = shift;
  87     my $stock_id = $self->stock_id;
  88     my $schema = $self->dbic_schema();
  89     my $member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'member_of', 'stock_relationship')->cvterm_id();
  90     my $offspring_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'offspring_of', 'stock_relationship')->cvterm_id();
  91
  92     my $q = "SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
  93              ON (stock_relationship.object_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
  94              WHERE stock_relationship.subject_id = ? and stock_relationship.type_id = ?
  95
  96              UNION ALL
  97
  98              SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
  99              ON (stock_relationship.subject_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 100              WHERE stock_relationship.object_id = ? and stock_relationship.type_id = ?
 101
 102              UNION ALL
 103
 104              SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
 105              ON (stock_relationship.object_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 106              WHERE stock_relationship.subject_id = ? and stock_relationship.type_id = ?
 107
 108              ";
 109
 110     my $h = $schema->storage->dbh->prepare($q);
 111     $h->execute($stock_id, $member_of_type_id, $stock_id, $member_of_type_id, $stock_id, $offspring_of_type_id);
 112
 113     my @group =();
 114         while(my($stock_id, $stock_name, $cvterm_name) = $h->fetchrow_array()){
 115         push @group, [$stock_id, $stock_name, $cvterm_name]
 116         }
 117
 118         return\@group;
 119
 120 }
 121
 122
 123 sub get_stock_for_tissue {
 124     my $self = shift;
 125     my $stock_id = $self->stock_id;
 126     my $schema = $self->dbic_schema();
 127     my $tissue_sample_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_sample_of', 'stock_relationship')->cvterm_id();
 128     my $q = "SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
 129              ON (stock_relationship.object_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 130              WHERE stock_relationship.subject_id = ? and stock_relationship.type_id = ?
 131
 132              UNION ALL
 133
 134              SELECT stock.stock_id, stock.uniquename, cvterm.name FROM stock_relationship INNER JOIN stock
 135              ON (stock_relationship.subject_id = stock.stock_id) INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 136              WHERE stock_relationship.object_id = ? and stock_relationship.type_id = ?
 137
 138              ";
 139
 140     my $h = $schema->storage->dbh->prepare($q);
 141     $h->execute($stock_id, $tissue_sample_of_type_id, $stock_id, $tissue_sample_of_type_id);
 142
 143     my @tissue_stocks =();
 144         while(my($stock_id, $stock_name, $cvterm_name) = $h->fetchrow_array()){
 145         push @tissue_stocks, [$stock_id, $stock_name, $cvterm_name]
 146         }
 147
 148         return\@tissue_stocks;
 149
 150 }
 151
 152
 153 sub get_cross_of_progeny {
 154     my $self = shift;
 155     my $progeny_name = shift;
 156     my $schema = shift;
 157     my $offspring_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'offspring_of', 'stock_relationship')->cvterm_id();
 158     my $cross_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id();
 159
 160     my $q = "SELECT cross_stock.stock_id, cross_stock.uniquename FROM stock
 161             JOIN stock_relationship ON (stock.stock_id = stock_relationship.subject_id) AND stock_relationship.type_id = ?
 162             JOIN stock AS cross_stock on (stock_relationship.object_id = cross_stock.stock_id) AND cross_stock.type_id = ?
 163             WHERE stock.uniquename = ?
 164             ";
 165
 166     my $h = $schema->storage->dbh->prepare($q);
 167              $h->execute($offspring_of_type_id, $cross_type_id, $progeny_name);
 168
 169              my @cross =();
 170             while(my($stock_id, $stock_name) = $h->fetchrow_array()){
 171                  push @cross, [$stock_id, $stock_name]
 172             }
 173
 174             return\@cross;
 175 }
 176
 177
 178 sub get_plot_plant_related_seedlots {
 179     my $self = shift;
 180     my $stock_id = $self->stock_id;
 181     my $schema = $self->dbic_schema();
 182     my $seed_transaction_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seed transaction', 'stock_relationship')->cvterm_id();
 183
 184     my @related_seedlots;
 185
 186     my $q1 = "SELECT distinct(stock.stock_id), stock.uniquename, cvterm.name FROM stock_relationship
 187             INNER JOIN stock ON (stock_relationship.subject_id = stock.stock_id)
 188             INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 189             WHERE stock_relationship.object_id = ? AND stock_relationship.type_id = ? ";
 190
 191     my $h1 = $schema->storage->dbh()->prepare($q1);
 192
 193     $h1->execute($stock_id, $seed_transaction_type_id);
 194
 195     while(my($stock_id, $stock_name, $stock_type) = $h1->fetchrow_array()){
 196       push @related_seedlots, ['source of', $stock_type, $stock_id, $stock_name]
 197     }
 198
 199     my $q2 = "SELECT distinct(stock.stock_id), stock.uniquename, cvterm.name FROM stock_relationship
 200             INNER JOIN stock ON (stock_relationship.object_id = stock.stock_id)
 201             INNER JOIN cvterm ON (stock.type_id = cvterm.cvterm_id)
 202             WHERE stock_relationship.subject_id = ? AND stock_relationship.type_id = ? ";
 203
 204     my $h2 = $schema->storage->dbh()->prepare($q2);
 205
 206     $h2->execute($stock_id, $seed_transaction_type_id);
 207
 208     while(my($stock_id, $stock_name, $stock_type) = $h2->fetchrow_array()){
 209       push @related_seedlots, ['derived from', $stock_type, $stock_id, $stock_name]
 210     }
 211
 212     return\@related_seedlots;
 213
 214 }
 215
 216
 217 sub get_vector_related_stocks {
 218     my $self = shift;
 219     my $stock_id = $self->stock_id;
 220     my $schema = $self->dbic_schema();
 221
 222     my $transformation_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformation", "stock_type")->cvterm_id();
 223     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
 224     my $vector_construct_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "vector_construct", "stock_type")->cvterm_id();
 225     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
 226     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
 227     my $transformant_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transformant_of', 'stock_relationship')->cvterm_id();
 228
 229
 230     my $q = "SELECT transformant.stock_id, transformant.uniquename, vector.stock_id, vector.uniquename, plant.stock_id, plant.uniquename, transformation.stock_id, transformation.uniquename
 231         FROM stock AS transformant
 232         JOIN stock_relationship AS plant_relationship ON (plant_relationship.object_id = transformant.stock_id) AND plant_relationship.type_id = ?
 233         JOIN stock AS plant ON (plant_relationship.subject_id = plant.stock_id) AND plant.type_id = ?
 234         JOIN stock_relationship AS vector_relationship ON (vector_relationship.object_id = transformant.stock_id) AND vector_relationship.type_id = ?
 235         JOIN stock as vector ON (vector_relationship.subject_id = vector.stock_id) AND vector.type_id = ?
 236         LEFT JOIN stock_relationship AS transformation_relationship ON (transformation_relationship.subject_id = transformant.stock_id) AND transformation_relationship.type_id = ?
 237         LEFT JOIN stock AS transformation ON (transformation_relationship.object_id = transformation.stock_id) AND transformation.type_id = ?
 238         WHERE vector.stock_id = ? ORDER BY transformation.uniquename, transformant.uniquename";
 239
 240     my $h = $schema->storage->dbh->prepare($q);
 241     $h->execute($female_parent_type_id, $accession_type_id, $male_parent_type_id, $vector_construct_type_id,  $transformant_of_type_id, $transformation_type_id, $stock_id);
 242
 243     my @related_stocks =();
 244     while(my($transformant_id, $transformant_name, $vector_id, $vector_name, $plant_id, $plant_name, $transformation_id, $transformation_name) = $h->fetchrow_array()){
 245         push @related_stocks, [$transformant_id, $transformant_name, $vector_id, $vector_name, $plant_id, $plant_name, $transformation_id, $transformation_name]
 246     }
 247
 248     return \@related_stocks;
 249
 250 }
 251
 252
 253
 254 1;