lib/CXGN/Cross.pm

   1
   2 package CXGN::Cross;
   3
   4 use Moose;
   5 use SGN::Model::Cvterm;
   6 use Data::Dumper;
   7 use JSON;
   8
   9 has 'bcs_schema' => ( isa => 'Bio::Chado::Schema',
  10     is => 'rw',
  11     required => 1,
  12 );
  13
  14 has 'cross_stock_id' => (isa => "Int",
  15     is => 'rw',
  16     required => 0,
  17 );
  18
  19 has 'female_parent' => (isa => 'Str',
  20     is => 'rw',
  21     required => 0,
  22 );
  23
  24 has 'male_parent' => (isa => 'Str',
  25     is => 'rw',
  26     required => 0,
  27 );
  28
  29 has 'trial_id' => (isa => "Int",
  30     is => 'rw',
  31     required => 0,
  32 );
  33
  34
  35
  36 sub BUILD {
  37     my $self = shift;
  38     my $args = shift;
  39 }
  40
  41 sub get_cross_relationships {
  42     my $self = shift;
  43
  44     my $crs = $self->bcs_schema->resultset("Stock::StockRelationship")->search( { object_id => $self->cross_stock_id } );
  45
  46     my $maternal_parent = "";
  47     my $paternal_parent = "";
  48     my @progeny = ();
  49
  50     foreach my $child ($crs->all()) {
  51         if ($child->type->name() eq "female_parent") {
  52           $maternal_parent = [ $child->subject->name, $child->subject->stock_id() ];
  53         }
  54         if ($child->type->name() eq "male_parent") {
  55           $paternal_parent = [ $child->subject->name, $child->subject->stock_id() ];
  56         }
  57         if ($child->type->name() eq "member_of") {
  58           push @progeny, [ $child->subject->name, $child->subject->stock_id() ];
  59         }
  60     }
  61     return ($maternal_parent, $paternal_parent, \@progeny);
  62 }
  63
  64 =head2 get_cross_info
  65
  66  Usage:         CXGN::Cross->get_cross_info( $schema, $female_parent, $male_parent);
  67  Desc:          Class method
  68  Ret:
  69  Args:
  70  Side Effects:
  71  Example:
  72
  73 =cut
  74
  75 sub get_cross_info {
  76     my $class = shift;
  77     my $schema = shift;
  78     my $female_parent = shift;
  79     my $male_parent = shift;
  80
  81     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
  82     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
  83     my $cross_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
  84
  85     my $where_female = "";
  86     if ($female_parent){
  87         $where_female = " WHERE female_parent.uniquename = ?";
  88     };
  89
  90     my $where_male ="";
  91     if ($male_parent){
  92         $where_male = " AND male_parent.uniquename = ?";
  93     }
  94
  95    my $q = "SELECT female_parent.stock_id, male_parent.stock_id, cross_entry.stock_id, female_parent.uniquename, male_parent.uniquename, cross_entry.uniquename, stock_relationship1.value
  96         FROM stock as female_parent INNER JOIN stock_relationship AS stock_relationship1 ON (female_parent.stock_id=stock_relationship1.subject_id)
  97         AND stock_relationship1.type_id= ? INNER JOIN stock AS cross_entry ON (cross_entry.stock_id=stock_relationship1.object_id) AND cross_entry.type_id= ?
  98         LEFT JOIN stock_relationship AS stock_relationship2 ON (cross_entry.stock_id=stock_relationship2.object_id) AND stock_relationship2.type_id= ?
  99         LEFT JOIN stock AS male_parent ON (male_parent.stock_id=stock_relationship2.subject_id)
 100         $where_female $where_male ORDER BY stock_relationship1.value, male_parent.uniquename";
 101
 102     my $h = $schema->storage->dbh()->prepare($q);
 103
 104     if ($female_parent && $male_parent) {
 105         $h->execute($female_parent_typeid, $cross_typeid, $male_parent_typeid, $female_parent, $male_parent);
 106     }
 107     elsif ($female_parent) {
 108         $h->execute($female_parent_typeid, $cross_typeid, $male_parent_typeid, $female_parent);
 109     }
 110     elsif ($male_parent) {
 111         $h->execute($female_parent_typeid, $cross_typeid, $male_parent_typeid, $male_parent);
 112     }
 113     else {
 114         $h->execute($female_parent_typeid, $cross_typeid, $male_parent_typeid);
 115     }
 116
 117     my @cross_info = ();
 118     while (my ($female_parent_id, $male_parent_id, $cross_entry_id, $female_parent_name, $male_parent_name, $cross_name, $cross_type) = $h->fetchrow_array()){
 119         push @cross_info, [$female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $cross_entry_id, $cross_name, $cross_type]
 120     }
 121     #print STDERR Dumper(\@cross_info);
 122     return \@cross_info;
 123 }
 124
 125
 126 =head2 get_cross_info_for_progeny
 127
 128  Usage:         CXGN::Cross->get_cross_info_for_progeny( $schema, $female_parent_id, $male_parent_id, $progeny_id);
 129  Desc:          Class method
 130  Ret:           cross info for the cross that created the progeny
 131  Args:
 132  Side Effects:
 133  Example:
 134
 135 =cut
 136
 137 sub get_cross_info_for_progeny {
 138     my $class = shift;
 139     my $schema = shift;
 140     my $female_parent_id = shift;
 141     my $male_parent_id = shift;
 142     my $progeny_id = shift;
 143
 144     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 145     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 146     my $cross_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
 147     my $member_typeid =  SGN::Model::Cvterm->get_cvterm_row($schema, 'member_of', 'stock_relationship')->cvterm_id();
 148     my $cross_experiment_type_cvterm_id =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross_experiment', 'experiment_type')->cvterm_id();
 149     my $project_year_cvterm_id =  SGN::Model::Cvterm->get_cvterm_row($schema, 'project year', 'project_property')->cvterm_id();
 150
 151    my $q = "SELECT cross_entry.stock_id, cross_entry.uniquename, female_stock_relationship.value, year.value
 152         FROM stock_relationship AS female_stock_relationship
 153         JOIN stock AS cross_entry ON (cross_entry.stock_id=female_stock_relationship.object_id)
 154         JOIN stock_relationship AS male_stock_relationship ON (cross_entry.stock_id=male_stock_relationship.object_id)
 155         JOIN stock_relationship AS cross_to_progeny_rel ON (cross_entry.stock_id=cross_to_progeny_rel.object_id)
 156         JOIN nd_experiment_stock ON (cross_entry.stock_id=nd_experiment_stock.stock_id)
 157         JOIN nd_experiment USING(nd_experiment_id)
 158         JOIN nd_experiment_project USING(nd_experiment_id)
 159         JOIN project USING (project_id)
 160         JOIN projectprop as year USING(project_id)
 161         WHERE cross_entry.type_id= ? AND female_stock_relationship.type_id= ? AND female_stock_relationship.subject_id = ? AND male_stock_relationship.type_id= ? AND male_stock_relationship.subject_id = ? AND cross_to_progeny_rel.type_id = ? AND cross_to_progeny_rel.subject_id = ? AND nd_experiment.type_id = ? AND year.type_id = ?
 162         ORDER BY female_stock_relationship.value, male_stock_relationship.subject_id";
 163
 164     my $h = $schema->storage->dbh()->prepare($q);
 165     $h->execute($cross_typeid, $female_parent_typeid, $female_parent_id, $male_parent_typeid, $male_parent_id, $member_typeid, $progeny_id, $cross_experiment_type_cvterm_id, $project_year_cvterm_id);
 166
 167     my @cross_info = ();
 168     while (my ($cross_entry_id, $cross_name, $cross_type, $year) = $h->fetchrow_array()){
 169         push @cross_info, [$cross_entry_id, $cross_name, $cross_type, $year];
 170     }
 171     #print STDERR Dumper(\@cross_info);
 172     if (scalar(@cross_info)>1){
 173         print STDERR "There is more than one (".scalar(@cross_info).") cross linked to this progeny\n";
 174     }
 175     if (scalar(@cross_info)>0){
 176         return $cross_info[0];
 177     } else {
 178         return undef;
 179     }
 180 }
 181
 182
 183 =head2 get_progeny_info
 184
 185  Usage:         CXGN::Cross->get_progeny_info( $schema, $female_parent, $male_parent);
 186  Desc:          Class method
 187  Ret:
 188  Args:
 189  Side Effects:
 190  Example:
 191
 192 =cut
 193
 194 sub get_progeny_info {
 195     my $class = shift;
 196     my $schema = shift;
 197     my $female_parent = shift;
 198     my $male_parent = shift;
 199
 200     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 201     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 202     my $accession_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
 203     my $member_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "member_of", "stock_relationship")->cvterm_id();
 204
 205     my $where_female = "";
 206     if ($female_parent){
 207         $where_female = " WHERE female_parent.uniquename = ?";
 208     };
 209
 210     my $where_male ="";
 211     if ($male_parent){
 212         $where_male = " AND male_parent.uniquename = ?";
 213     }
 214
 215     my $q = "SELECT DISTINCT female_parent.stock_id, female_parent.uniquename, male_parent.stock_id, male_parent.uniquename, progeny.stock_id, progeny.uniquename, CONCAT(stock_relationship1.value, stock_relationship4.value) AS type
 216         FROM stock_relationship as stock_relationship1
 217         INNER JOIN stock AS female_parent ON (stock_relationship1.subject_id = female_parent.stock_id) AND stock_relationship1.type_id = ?
 218         INNER JOIN stock AS progeny ON (stock_relationship1.object_id = progeny.stock_id) AND progeny.type_id = ?
 219         LEFT JOIN stock_relationship AS stock_relationship2 ON (progeny.stock_id = stock_relationship2.object_id) AND stock_relationship2.type_id = ?
 220         LEFT JOIN stock AS male_parent ON (stock_relationship2.subject_id = male_parent.stock_id)
 221         LEFT JOIN stock_relationship AS stock_relationship3 ON (progeny.stock_id = stock_relationship3.subject_id) AND stock_relationship3.type_id = ?
 222         LEFT JOIN stock_relationship AS stock_relationship4 ON (stock_relationship3.object_id = stock_relationship4.object_id) AND stock_relationship4.type_id = ?
 223         $where_female $where_male ORDER BY male_parent.uniquename";
 224
 225     my $h = $schema->storage->dbh()->prepare($q);
 226
 227     if($female_parent && $male_parent){
 228         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid, $member_typeid, $female_parent_typeid, $female_parent, $male_parent);
 229     }
 230     elsif ($female_parent) {
 231         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid, $member_typeid, $female_parent_typeid, $female_parent);
 232     }
 233     elsif ($male_parent) {
 234         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid, $member_typeid, $female_parent_typeid, $male_parent);
 235     }
 236     else {
 237         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid, $member_typeid, $female_parent_typeid);
 238     }
 239
 240     my @progeny_info = ();
 241     while (my($female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $progeny_id, $progeny_name, $cross_type) = $h->fetchrow_array()){
 242
 243         push @progeny_info, [$female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $progeny_id, $progeny_name, $cross_type]
 244     }
 245       #print STDERR Dumper(\@progeny_info);
 246     return \@progeny_info;
 247 }
 248
 249 =head2 get_crosses_in_trial
 250
 251
 252 =cut
 253
 254 sub get_crosses_in_trial {
 255     my $self = shift;
 256     my $schema = $self->bcs_schema;
 257     my $trial_id = $self->trial_id;
 258
 259     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 260     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 261     my $female_plot_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_plot_of", "stock_relationship")->cvterm_id();
 262     my $male_plot_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_plot_of", "stock_relationship")->cvterm_id();
 263     my $female_plant_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_plant_of", "stock_relationship")->cvterm_id();
 264     my $male_plant_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_plant_of", "stock_relationship")->cvterm_id();
 265
 266     my $q = "SELECT stock1.stock_id, stock1.uniquename, stock_relationship1.value, stock2.stock_id, stock2.uniquename, stock3.stock_id, stock3.uniquename, stock4.stock_id, stock4.uniquename, stock5.stock_id, stock5.uniquename, stock6.stock_id, stock6.uniquename, stock7.stock_id, stock7.uniquename
 267         FROM nd_experiment_project JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 268         JOIN stock AS stock1 ON (nd_experiment_stock.stock_id = stock1.stock_id)
 269         LEFT JOIN stock_relationship AS stock_relationship1 ON (stock1.stock_id = stock_relationship1.object_id) AND stock_relationship1.type_id = ?
 270         LEFT JOIN stock AS stock2 ON (stock_relationship1.subject_id = stock2.stock_id)
 271         LEFT JOIN stock_relationship AS stock_relationship2 ON (stock1.stock_id = stock_relationship2.object_id) AND stock_relationship2.type_id = ?
 272         LEFT JOIN stock AS stock3 ON (stock_relationship2.subject_id = stock3.stock_id)
 273         LEFT JOIN stock_relationship AS stock_relationship3 ON (stock1.stock_id = stock_relationship3.object_id) AND stock_relationship3.type_id = ?
 274         LEFT JOIN stock AS stock4 ON (stock_relationship3.subject_id = stock4.stock_id)
 275         LEFT JOIN stock_relationship AS stock_relationship4 ON (stock1.stock_id = stock_relationship4.object_id) AND stock_relationship4.type_id = ?
 276         LEFT JOIN stock AS stock5 ON (stock_relationship4.subject_id = stock5.stock_id)
 277         LEFT JOIN stock_relationship AS stock_relationship5 ON (stock1.stock_id = stock_relationship5.object_id) AND stock_relationship5.type_id = ?
 278         LEFT JOIN stock AS stock6 ON (stock_relationship5.subject_id = stock6.stock_id)
 279         LEFT JOIN stock_relationship AS stock_relationship6 ON (stock1.stock_id = stock_relationship6.object_id) AND stock_relationship6.type_id = ?
 280         LEFT JOIN stock AS stock7 ON (stock_relationship6.subject_id = stock7.stock_id)
 281         WHERE nd_experiment_project.project_id = ?";
 282
 283     my $h = $schema->storage->dbh()->prepare($q);
 284
 285     $h->execute($female_parent_typeid, $male_parent_typeid, $female_plot_of_typeid, $male_plot_of_typeid, $female_plant_of_typeid, $male_plant_of_typeid, $trial_id);
 286
 287     my @data =();
 288     while(my($cross_id, $cross_name, $cross_type, $female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $female_plot_id, $female_plot_name, $male_plot_id, $male_plot_name, $female_plant_id, $female_plant_name, $male_plant_id, $male_plant_name) = $h->fetchrow_array()){
 289         push @data, [$cross_id, $cross_name, $cross_type, $female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $female_plot_id, $female_plot_name, $male_plot_id, $male_plot_name, $female_plant_id, $female_plant_name, $male_plant_id, $male_plant_name]
 290     }
 291     return \@data;
 292 }
 293
 294 =head2 get_cross_properties_trial
 295
 296
 297 =cut
 298
 299 sub get_cross_properties_trial {
 300     my $self = shift;
 301     my $schema = $self->bcs_schema;
 302     my $trial_id = $self->trial_id;
 303
 304     my $cross_props_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "crossing_metadata_json", "stock_property")->cvterm_id();
 305
 306     my $q = "SELECT stock.stock_id, stock.uniquename, stockprop.value FROM nd_experiment_project
 307         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 308         LEFT JOIN stockprop ON (nd_experiment_stock.stock_id = stockprop.stock_id)
 309         LEFT JOIN stock ON (stockprop.stock_id = stock.stock_id)
 310         WHERE stockprop.type_id = ? AND nd_experiment_project.project_id = ?";
 311
 312     my $h = $schema->storage->dbh()->prepare ($q);
 313
 314     $h->execute($cross_props_typeid, $trial_id);
 315
 316     my @data = ();
 317     while(my($cross_id, $cross_name, $cross_props) = $h->fetchrow_array()){
 318       #print STDERR Dumper $cross_props;
 319         my $cross_props_hash = decode_json$cross_props;
 320         push @data, [$cross_id, $cross_name, $cross_props_hash]
 321     }
 322
 323     return \@data;
 324
 325 }
 326
 327 =head2 get_cross_progenies_trial
 328
 329
 330 =cut
 331
 332 sub get_cross_progenies_trial {
 333     my $self = shift;
 334     my $schema = $self->bcs_schema;
 335     my $trial_id = $self->trial_id;
 336
 337     my $member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "member_of", "stock_relationship")->cvterm_id();
 338     my $family_name_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "family_name", "stock_property")->cvterm_id();
 339
 340     my $q = "SELECT progeny_count_table.cross_id, progeny_count_table.cross_name, progeny_count_table.progeny_number, stockprop.value
 341         FROM
 342         (SELECT DISTINCT stock.stock_id AS cross_id, stock.uniquename AS cross_name, COUNT (stock_relationship.subject_id) AS progeny_number
 343         FROM nd_experiment_project JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 344         JOIN stock ON (nd_experiment_stock.stock_id = stock.stock_id)
 345         LEFT JOIN stock_relationship ON (stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 346         WHERE nd_experiment_project.project_id = ? GROUP BY cross_id)
 347         AS progeny_count_table
 348         LEFT JOIN stockprop ON (progeny_count_table.cross_id = stockprop.stock_id) AND stockprop.type_id = ?";
 349
 350     my $h = $schema->storage->dbh()->prepare($q);
 351
 352     $h->execute($member_of_type_id, $trial_id, $family_name_type_id);
 353
 354     my @data =();
 355     while(my($cross_id, $cross_name, $progeny_number, $family_name) = $h->fetchrow_array()){
 356         push @data, [$cross_id, $cross_name, $progeny_number, $family_name]
 357     }
 358
 359     return \@data;
 360 }
 361
 362
 363
 364
 365 =head2 delete
 366
 367  Usage:        $cross->delete();
 368  Desc:         Deletes a cross
 369  Ret:          error string if error, undef otherwise
 370  Args:         none
 371  Side Effects: deletes project entry, nd_experiment entry, and stock entry.
 372                does not check if
 373  Example:
 374
 375 =cut
 376
 377
 378 sub delete {
 379     my $self = shift;
 380
 381     my $dbh = $self->bcs_schema()->storage()->dbh();
 382     my $schema = $self->bcs_schema();
 383
 384     eval {
 385
 386         $dbh->begin_work();
 387
 388         my $cross_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
 389         # delete the project entries
 390         #
 391         print STDERR "Deleting project entry for cross...\n";
 392         my $q1 = "delete from project where project_id=(SELECT project_id FROM nd_experiment_project JOIN nd_experiment_stock USING (nd_experiment_id) JOIN stock USING(stock_id) where stock_id=? and type_id = ?)";
 393         my $h1 = $dbh->prepare($q1);
 394         $h1->execute($self->cross_stock_id(), $cross_typeid);
 395
 396         # delete the nd_experiment entries
 397         #
 398         print STDERR "Deleting nd_experiment entry for cross...\n";
 399         my $q2= "delete from nd_experiment where nd_experiment.nd_experiment_id=(SELECT nd_experiment_id FROM nd_experiment_stock JOIN stock USING (stock_id) where stock.stock_id=? and stock.type_id =?)";
 400         my $h2 = $dbh->prepare($q2);
 401         $h2->execute($self->cross_stock_id(), $cross_typeid);
 402
 403         # delete the stock entries
 404         #
 405         my $q3 = "delete from stock where stock.stock_id=523823 and stock.type_id = ?";
 406         my $h3 = $dbh->prepare($q3);
 407         $h3->execute($self->cross_stock_id(), $cross_typeid);
 408     };
 409
 410     if ($@) {
 411         print STDERR "An error occurred while deleting cross id ".$self->cross_stock_id()."\n";
 412         $dbh->rollback();
 413         return $@;
 414     }
 415     else {
 416         $dbh->commit();
 417     }
 418 }
 419
 420 1;