lib/CXGN/Cross.pm

   1
   2 =head1 NAME
   3
   4 CXGN::Cross - an object representing a Cross in the database
   5
   6 =head1 DESCRIPTION
   7
   8     my $cross = CXGN::Cross->new( { schema => $schema, cross_stock_id => 37347 });
   9
  10     my $cross_name = $cross->cross_name(); # get cross name
  11     my $female_parent = $cross->female_parent(); #name of female parent
  12     my $female_parent_id = $cross->female_parent_id(); # id of female parent
  13     my $male_parent   = $cross->male_parent(); # etc.
  14     my $progenies = $cross->progenies();
  15     # more ...
  16
  17 =head1 AUTHORS
  18
  19     Titima Tantikanjana
  20     Lukas Mueller
  21     Naama Menda
  22     Jeremy Edwards
  23
  24 =head1 METHODS
  25
  26 =cut
  27
  28 package CXGN::Cross;
  29
  30 use Moose;
  31 use SGN::Model::Cvterm;
  32 use Data::Dumper;
  33 use JSON;
  34
  35 extends 'CXGN::Stock';
  36
  37 has 'cross_stock_id' => (isa => "Maybe[Int]",
  38     is => 'rw',
  39     required => 0,
  40 );
  41
  42 has 'cross_name' => (isa => 'Maybe[Str]',
  43     is => 'rw',
  44 );
  45
  46 has 'female_parent' => (isa => 'Str',
  47     is => 'rw',
  48     required => 0,
  49 );
  50
  51 has 'female_parent_id' => (isa => 'Int',
  52     is => 'rw',
  53     );
  54
  55 has 'male_parent' => (isa => 'Str',
  56     is => 'rw',
  57     required => 0,
  58 );
  59
  60 has 'male_parent_id' => (isa => 'Int',
  61     is => 'rw',
  62     );
  63
  64 has 'trial_id' => (isa => "Int",
  65     is => 'rw',
  66     required => 0,
  67 );
  68
  69 has 'progenies' => (isa => 'Ref',
  70                     is => 'rw',
  71     );
  72
  73
  74 sub BUILD {
  75     my $self = shift;
  76     my $args = shift;
  77
  78     my $schema = $args->{schema};
  79     my $cross_id = $args->{cross_stock_id};
  80
  81     $self->stock_id($cross_id);
  82
  83     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
  84     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
  85     my $cross_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id();
  86
  87     my $row = $schema->resultset("Stock::Stock")->find( { stock_id => $cross_id, type_id => $cross_type_id });
  88
  89     if ($row) {
  90          my $name = $row->uniquename();
  91
  92          $self->cross_name($name);
  93          $self->cross_stock_id($cross_id);
  94
  95     }
  96
  97     # to do: populate female_parent, male_parent etc.
  98     my ($female_parent, $male_parent, @progenies) = $self->get_cross_relationships();
  99     print STDERR Dumper($female_parent);
 100
 101     if (ref($female_parent)) {
 102         $self->female_parent($female_parent->[0]);
 103         $self->female_parent_id($female_parent->[1]);
 104     }
 105     if (ref($male_parent)) {
 106         $self->male_parent($male_parent->[0]);
 107         $self->male_parent_id($male_parent->[1]);
 108     }
 109     if (@progenies) {
 110         $self->progenies(\@progenies);
 111     }
 112 }
 113
 114 sub get_cross_relationships {
 115     my $self = shift;
 116
 117     my $crs = $self->schema->resultset("Stock::StockRelationship")->search( { object_id => $self->cross_stock_id } );
 118
 119     my $maternal_parent = "";
 120     my $paternal_parent = "";
 121     my @progeny = ();
 122
 123     foreach my $child ($crs->all()) {
 124         if ($child->type->name() eq "female_parent") {
 125             $maternal_parent = [ $child->subject->name, $child->subject->stock_id() ];
 126         }
 127         if ($child->type->name() eq "male_parent") {
 128             $paternal_parent = [ $child->subject->name, $child->subject->stock_id() ];
 129         }
 130         if ($child->type->name() eq "offspring_of") {
 131             my $is_progeny_obsolete = $child->subject->is_obsolete();
 132             if ($is_progeny_obsolete == 0 ){
 133                 push @progeny, [ $child->subject->name, $child->subject->stock_id() ]
 134             }
 135         }
 136     }
 137
 138     return ($maternal_parent, $paternal_parent, \@progeny);
 139 }
 140
 141
 142 sub get_membership {
 143     my $self = shift;
 144     my $schema = $self->schema;
 145     my $cross_id = $self->cross_stock_id;
 146
 147     my $cross_member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_member_of", "stock_relationship")->cvterm_id();
 148     my $cross_experiment_type_id =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross_experiment', 'experiment_type')->cvterm_id();
 149     my $family_name_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "family_name", "stock_type")->cvterm_id();
 150
 151     my $q = "SELECT project.project_id, project.name, project.description, stock.stock_id, stock.uniquename
 152         FROM nd_experiment_stock
 153         JOIN nd_experiment ON (nd_experiment_stock.nd_experiment_id = nd_experiment.nd_experiment_id) AND nd_experiment.type_id = ?
 154         JOIN nd_experiment_project ON (nd_experiment_project.nd_experiment_id = nd_experiment.nd_experiment_id)
 155         JOIN project ON (nd_experiment_project.project_id = project.project_id)
 156         LEFT JOIN stock_relationship ON (nd_experiment_stock.stock_id = stock_relationship.subject_id) AND stock_relationship.type_id = ?
 157         LEFT JOIN stock ON (stock_relationship.object_id = stock.stock_id) AND stock.type_id = ?
 158         WHERE nd_experiment_stock.stock_id = ?";
 159
 160      my $h = $schema->storage->dbh()->prepare($q);
 161      $h->execute($cross_experiment_type_id, $cross_member_of_type_id, $family_name_type_id, $cross_id);
 162
 163      my @membership_info = ();
 164      while (my ($crossing_experiment_id, $crossing_experiment_name, $description, $family_id, $family_name) = $h->fetchrow_array()){
 165          push @membership_info, [$crossing_experiment_id, $crossing_experiment_name, $description, $family_id, $family_name]
 166      }
 167
 168      return \@membership_info;
 169 }
 170
 171
 172 =head2 cross_parents
 173
 174  Usage:         CXGN::Cross->cross_parents($schema, $cross_id);
 175  Desc:          Class method
 176  Ret:
 177  Args:
 178  Side Effects:
 179  Example:
 180
 181 =cut
 182
 183 sub cross_parents {
 184     my $self = shift;
 185     my $schema = $self->schema;
 186     my $cross_id = $self->cross_stock_id;
 187
 188     my $female_accession_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
 189     my $female_plot_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_plot_of', 'stock_relationship')->cvterm_id();
 190     my $male_accession_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
 191     my $male_plot_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_plot_of', 'stock_relationship')->cvterm_id();
 192     my $female_plant_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_plant_of', 'stock_relationship')->cvterm_id();
 193     my $male_plant_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_plant_of', 'stock_relationship')->cvterm_id();
 194     my $cross_combination_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'cross_combination', 'stock_property')->cvterm_id();
 195     my $ploidy_level_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'ploidy_level', 'stock_property')->cvterm_id();
 196
 197     my $q ="SELECT female_accession.stock_id, female_accession.uniquename, female_plot.stock_id, female_plot.uniquename, female_plant.stock_id, female_plant.uniquename,
 198         male_accession.stock_id, male_accession.uniquename, male_plot.stock_id, male_plot.uniquename, male_plant.stock_id, male_plant.uniquename,
 199         female_relationship.value, cross_combination.value, female_ploidy.value, male_ploidy.value
 200         FROM stock
 201         JOIN stock_relationship AS female_relationship ON (stock.stock_id = female_relationship.object_id) AND female_relationship.type_id = ?
 202         JOIN stock AS female_accession ON (female_relationship.subject_id = female_accession.stock_id)
 203         LEFT JOIN stockprop AS female_ploidy ON (female_accession.stock_id = female_ploidy.stock_id) AND female_ploidy.type_id = ?
 204         LEFT JOIN stock_relationship AS female_plot_relationship ON (stock.stock_id = female_plot_relationship.object_id) AND female_plot_relationship.type_id = ?
 205         LEFT JOIN stock AS female_plot on (female_plot_relationship.subject_id = female_plot.stock_id)
 206         LEFT JOIN stock_relationship AS female_plant_relationship ON (stock.stock_id = female_plant_relationship.object_id) AND female_plant_relationship.type_id = ?
 207         LEFT JOIN stock AS female_plant ON (female_plant_relationship.subject_id = female_plant.stock_id)
 208         LEFT JOIN stock_relationship AS male_relationship ON (stock.stock_id = male_relationship.object_id) AND male_relationship.type_id = ?
 209         LEFT JOIN stock AS male_accession ON (male_relationship.subject_id = male_accession.stock_id)
 210         LEFT JOIN stockprop AS male_ploidy ON (male_accession.stock_id = male_ploidy.stock_id) AND male_ploidy.type_id = ?
 211         LEFT JOIN stock_relationship AS male_plot_relationship ON (stock.stock_id = male_plot_relationship.object_id) AND male_plot_relationship.type_id = ?
 212         LEFT JOIN stock AS male_plot ON (male_plot_relationship.subject_id = male_plot.stock_id)
 213         LEFT JOIN stock_relationship AS male_plant_relationship ON (stock.stock_id = male_plant_relationship.object_id) AND male_plant_relationship.type_id = ?
 214         LEFT JOIN stock AS male_plant ON (male_plant_relationship.subject_id =male_plant.stock_id)
 215         LEFT JOIN stockprop AS cross_combination ON (stock.stock_id = cross_combination.stock_id) AND cross_combination.type_id =?
 216         WHERE stock.stock_id = ?";
 217
 218         my $h = $schema->storage->dbh()->prepare($q);
 219         $h->execute($female_accession_cvterm, $ploidy_level_cvterm, $female_plot_cvterm, $female_plant_cvterm, $male_accession_cvterm, $ploidy_level_cvterm, $male_plot_cvterm, $male_plant_cvterm, $cross_combination_cvterm, $cross_id);
 220
 221         my @cross_parents = ();
 222         while(my ($female_accession_id, $female_accession_name, $female_plot_id, $female_plot_name, $female_plant_id, $female_plant_name, $male_accession_id, $male_accession_name, $male_plot_id, $male_plot_name, $male_plant_id, $male_plant_name, $cross_type, $cross_combination, $female_ploidy, $male_ploidy) = $h->fetchrow_array()){
 223             push @cross_parents, [$female_accession_id, $female_accession_name, $female_plot_id, $female_plot_name, $female_plant_id, $female_plant_name, $male_accession_id, $male_accession_name, $male_plot_id, $male_plot_name, $male_plant_id, $male_plant_name, $cross_type, $cross_combination, $female_ploidy, $male_ploidy]
 224         }
 225
 226         return \@cross_parents;
 227
 228 }
 229
 230
 231 =head2 get_cross_details
 232
 233  Usage:         CXGN::Cross->get_cross_details( $schema, $female_parent, $male_parent);
 234  Desc:          Class method
 235  Ret:
 236  Args:
 237  Side Effects:
 238  Example:
 239
 240 =cut
 241
 242 sub get_cross_details {
 243     my $class = shift;
 244     my $schema = shift;
 245     my $female_parent = shift;
 246     my $male_parent = shift;
 247
 248     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 249     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 250     my $cross_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
 251     my $family_name_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "family_name", "stock_type")->cvterm_id();
 252     my $member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "member_of", "stock_relationship")->cvterm_id();
 253     my $cross_experiment_cvterm =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross_experiment', 'experiment_type')->cvterm_id();
 254
 255     my $where_clause = "";
 256     if ($female_parent && $male_parent) {
 257         $where_clause = "WHERE female_parent.uniquename = ? AND male_parent.uniquename = ? ORDER BY stock_relationship1.value";
 258     }
 259     elsif ($female_parent) {
 260         $where_clause = "WHERE female_parent.uniquename = ? ORDER BY male_parent.uniquename, stock_relationship1.value";
 261     }
 262     elsif ($male_parent) {
 263         $where_clause = "WHERE male_parent.uniquename = ? ORDER BY female_parent.uniquename, stock_relationship1.value";
 264     }
 265
 266     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, family.stock_id, family.uniquename, project.project_id, project.name
 267     FROM stock as female_parent INNER JOIN stock_relationship AS stock_relationship1 ON (female_parent.stock_id = stock_relationship1.subject_id) AND stock_relationship1.type_id = ?
 268     INNER JOIN stock AS cross_entry ON (cross_entry.stock_id = stock_relationship1.object_id) AND cross_entry.type_id= ?
 269     LEFT JOIN stock_relationship AS stock_relationship2 ON (cross_entry.stock_id = stock_relationship2.object_id) AND stock_relationship2.type_id = ?
 270     LEFT JOIN stock AS male_parent ON (male_parent.stock_id = stock_relationship2.subject_id)
 271     LEFT JOIN stock_relationship AS stock_relationship3 ON (stock_relationship3.subject_id = cross_entry.stock_id) AND stock_relationship3.type_id = ?
 272     LEFT JOIN stock AS family ON (stock_relationship3.object_id = family.stock_id) AND family.type_id = ?
 273     LEFT JOIN nd_experiment_stock ON (nd_experiment_stock.stock_id = cross_entry.stock_id)
 274     LEFT JOIN nd_experiment_project ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id) AND nd_experiment_stock.type_id = ?
 275     LEFT JOIN project ON (nd_experiment_project.project_id = project.project_id)
 276     $where_clause";
 277
 278     my $h = $schema->storage->dbh()->prepare($q);
 279
 280     if ($female_parent && $male_parent) {
 281         $h->execute($female_parent_type_id, $cross_type_id, $male_parent_type_id, $member_of_type_id, $family_name_type_id, $cross_experiment_cvterm, $female_parent, $male_parent);
 282     }
 283     elsif ($female_parent) {
 284         $h->execute($female_parent_type_id, $cross_type_id, $male_parent_type_id, $member_of_type_id, $family_name_type_id, $cross_experiment_cvterm, $female_parent);
 285     }
 286     elsif ($male_parent) {
 287         $h->execute($female_parent_type_id, $cross_type_id, $male_parent_type_id, $member_of_type_id, $family_name_type_id, $cross_experiment_cvterm, $male_parent);
 288     }
 289     else {
 290         $h->execute($female_parent_type_id, $cross_type_id, $male_parent_type_id, $member_of_type_id, $family_name_type_id, $cross_experiment_cvterm);
 291     }
 292
 293     my @cross_details = ();
 294     while (my ($female_parent_id, $male_parent_id, $cross_entry_id, $female_parent_name, $male_parent_name, $cross_name, $cross_type, $family_id, $family_name, $project_id, $project_name) = $h->fetchrow_array()){
 295         push @cross_details, [$female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $cross_entry_id, $cross_name, $cross_type, $family_id, $family_name, $project_id, $project_name]
 296     }
 297 #    print STDERR Dumper(\@cross_details);
 298     return \@cross_details;
 299 }
 300
 301
 302 =head2 get_cross_info_for_progeny
 303
 304  Usage:         CXGN::Cross->get_cross_info_for_progeny( $schema, $female_parent_id, $male_parent_id, $progeny_id);
 305  Desc:          Class method
 306  Ret:           cross info for the cross that created the progeny
 307  Args:
 308  Side Effects:
 309  Example:
 310
 311 =cut
 312
 313 sub get_cross_info_for_progeny {
 314     my $class = shift;
 315     my $schema = shift;
 316     my $female_parent_id = shift;
 317     my $male_parent_id = shift;
 318     my $progeny_id = shift;
 319
 320     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 321     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 322     my $cross_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
 323     my $offspring_of_typeid =  SGN::Model::Cvterm->get_cvterm_row($schema, 'offspring_of', 'stock_relationship')->cvterm_id();
 324
 325     my $cross_experiment_type_cvterm_id =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross_experiment', 'experiment_type')->cvterm_id();
 326     my $project_year_cvterm_id =  SGN::Model::Cvterm->get_cvterm_row($schema, 'project year', 'project_property')->cvterm_id();
 327
 328    my $q = "SELECT cross_entry.stock_id, cross_entry.uniquename, female_stock_relationship.value, year.value
 329         FROM stock_relationship AS female_stock_relationship
 330         JOIN stock AS cross_entry ON (cross_entry.stock_id=female_stock_relationship.object_id)
 331         JOIN stock_relationship AS male_stock_relationship ON (cross_entry.stock_id=male_stock_relationship.object_id)
 332         JOIN stock_relationship AS cross_to_progeny_rel ON (cross_entry.stock_id=cross_to_progeny_rel.object_id)
 333         JOIN nd_experiment_stock ON (cross_entry.stock_id=nd_experiment_stock.stock_id)
 334         JOIN nd_experiment USING(nd_experiment_id)
 335         JOIN nd_experiment_project USING(nd_experiment_id)
 336         JOIN project USING (project_id)
 337         JOIN projectprop as year USING(project_id)
 338         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 = ?
 339         ORDER BY female_stock_relationship.value, male_stock_relationship.subject_id";
 340
 341     my $h = $schema->storage->dbh()->prepare($q);
 342     $h->execute($cross_typeid, $female_parent_typeid, $female_parent_id, $male_parent_typeid, $male_parent_id, $offspring_of_typeid, $progeny_id, $cross_experiment_type_cvterm_id, $project_year_cvterm_id);
 343
 344
 345     my @cross_info = ();
 346     while (my ($cross_entry_id, $cross_name, $cross_type, $year) = $h->fetchrow_array()){
 347         push @cross_info, [$cross_entry_id, $cross_name, $cross_type, $year];
 348     }
 349     #print STDERR Dumper(\@cross_info);
 350     if (scalar(@cross_info)>1){
 351         print STDERR "There is more than one (".scalar(@cross_info).") cross linked to this progeny\n";
 352     }
 353     if (scalar(@cross_info)>0){
 354         return $cross_info[0];
 355     } else {
 356         return undef;
 357     }
 358 }
 359
 360
 361 =head2 get_progeny_info
 362
 363  Usage:         CXGN::Cross->get_progeny_info($schema, $female_parent, $male_parent);
 364  Desc:          Class method. Used for the cross search for searching with either female or male parents.
 365  Ret:
 366  Args:
 367  Side Effects:
 368  Example:
 369
 370 =cut
 371
 372 sub get_progeny_info {
 373     my $class = shift;
 374
 375     my $schema = shift;
 376     my $female_parent = shift;
 377     my $male_parent = shift;
 378
 379     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 380     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 381     my $accession_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
 382
 383     my $where_clause = "";
 384     if ($female_parent && $male_parent) {
 385         $where_clause = "WHERE female_parent.uniquename = ? AND male_parent.uniquename = ?";
 386     }
 387     elsif ($female_parent) {
 388         $where_clause = "WHERE female_parent.uniquename = ? ORDER BY male_parent.uniquename";
 389     }
 390     elsif ($male_parent) {
 391         $where_clause = "WHERE male_parent.uniquename = ? ORDER BY female_parent.uniquename";
 392     }
 393
 394     my $q = "SELECT DISTINCT female_parent.stock_id, female_parent.uniquename, male_parent.stock_id, male_parent.uniquename, progeny.stock_id, progeny.uniquename, stock_relationship1.value
 395         FROM stock_relationship as stock_relationship1
 396         INNER JOIN stock AS female_parent ON (stock_relationship1.subject_id = female_parent.stock_id) AND stock_relationship1.type_id = ?
 397         INNER JOIN stock AS progeny ON (stock_relationship1.object_id = progeny.stock_id) AND progeny.type_id = ?
 398         LEFT JOIN stock_relationship AS stock_relationship2 ON (progeny.stock_id = stock_relationship2.object_id) AND stock_relationship2.type_id = ?
 399         LEFT JOIN stock AS male_parent ON (stock_relationship2.subject_id = male_parent.stock_id)
 400         $where_clause ";
 401
 402     my $h = $schema->storage->dbh()->prepare($q);
 403
 404     if($female_parent && $male_parent){
 405         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid, $female_parent, $male_parent);
 406     }
 407     elsif ($female_parent) {
 408         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid, $female_parent);
 409     }
 410     elsif ($male_parent) {
 411         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid, $male_parent);
 412     }
 413     else {
 414         $h->execute($female_parent_typeid, $accession_typeid, $male_parent_typeid);
 415     }
 416
 417     my @progeny_info = ();
 418     while (my($female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $progeny_id, $progeny_name, $cross_type) = $h->fetchrow_array()){
 419
 420         push @progeny_info, [$female_parent_id, $female_parent_name, $male_parent_id, $male_parent_name, $progeny_id, $progeny_name, $cross_type]
 421     }
 422       #print STDERR Dumper(\@progeny_info);
 423     return \@progeny_info;
 424 }
 425
 426
 427 =head2 get_crosses_in_crossing_experiment
 428
 429     Class method.
 430     Returns all cross names and ids in a specific crossing_experiment.
 431     Example: my @crosses = CXGN::Cross->get_crosses_in_crossing_experiment($schema, $trial_id)
 432
 433 =cut
 434
 435 sub get_crosses_in_crossing_experiment {
 436     my $self = shift;
 437     my $schema = $self->schema;
 438     my $trial_id = $self->trial_id;
 439
 440     my $cross_stock_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id;
 441
 442     my $q = "SELECT stock.stock_id, stock.uniquename FROM nd_experiment_project
 443         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 444         JOIN stock on (nd_experiment_stock.stock_id = stock.stock_id)
 445         WHERE stock.type_id = ? AND nd_experiment_project.project_id = ?";
 446
 447     my $h = $schema->storage->dbh()->prepare($q);
 448     $h->execute($cross_stock_type_id, $trial_id);
 449
 450     my @data = ();
 451     while(my($cross_id, $cross_name) = $h->fetchrow_array()){
 452         push @data, [$cross_id, $cross_name]
 453     }
 454
 455     return \@data;
 456 }
 457
 458
 459 =head2 get_female_accessions_in_crossing_experiment
 460
 461     Class method.
 462     Returns all female accession names and ids in a specific crossing_experiment.
 463     Example: my @female_accessions = CXGN::Cross->get_female_accessions_in_crossing_experiment($schema, $trial_id)
 464
 465 =cut
 466
 467 sub get_female_accessions_in_crossing_experiment {
 468     my $self = shift;
 469     my $schema = $self->schema;
 470     my $trial_id = $self->trial_id;
 471
 472     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 473
 474     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename FROM nd_experiment_project
 475         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 476         JOIN stock_relationship ON (nd_experiment_stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 477         JOIN stock on (stock_relationship.subject_id = stock.stock_id)
 478         WHERE nd_experiment_project.project_id = ?";
 479
 480     my $h = $schema->storage->dbh()->prepare($q);
 481     $h->execute($female_parent_typeid, $trial_id);
 482
 483     my @data = ();
 484     while(my($female_accession_id, $female_accession_name) = $h->fetchrow_array()){
 485         push @data, [$female_accession_id, $female_accession_name]
 486     }
 487
 488     return \@data;
 489 }
 490
 491
 492 =head2 get_male_accessions_in_crossing_experiment
 493
 494     Class method.
 495     Returns all male accession names and ids in a specific crossing_experiment.
 496     Example: my @male_accessions = CXGN::Cross->get_male_accessions_in_crossing_experiment($schema, $trial_id)
 497
 498 =cut
 499
 500 sub get_male_accessions_in_crossing_experiment {
 501     my $self = shift;
 502     my $schema = $self->schema;
 503     my $trial_id = $self->trial_id;
 504
 505     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 506
 507     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename FROM nd_experiment_project
 508         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 509         JOIN stock_relationship ON (nd_experiment_stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 510         JOIN stock on (stock_relationship.subject_id = stock.stock_id)
 511         WHERE nd_experiment_project.project_id = ?";
 512
 513     my $h = $schema->storage->dbh()->prepare($q);
 514     $h->execute($male_parent_typeid, $trial_id);
 515
 516     my @data = ();
 517     while(my($male_accession_id, $male_accession_name) = $h->fetchrow_array()){
 518         push @data, [$male_accession_id, $male_accession_name]
 519     }
 520
 521     return \@data;
 522 }
 523
 524
 525 =head2 get_female_plots_in_crossing_experiment
 526
 527     Class method.
 528     Returns all female plot names and ids in a specific crossing_experiment.
 529     Example: my @female_plots = CXGN::Cross->get_female_plots_in_crossing_experiment($schema, $trial_id)
 530
 531 =cut
 532
 533 sub get_female_plots_in_crossing_experiment {
 534     my $self = shift;
 535     my $schema = $self->schema;
 536     my $trial_id = $self->trial_id;
 537
 538     my $female_plot_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_plot_of", "stock_relationship")->cvterm_id();
 539
 540     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename FROM nd_experiment_project
 541         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 542         JOIN stock_relationship ON (nd_experiment_stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 543         JOIN stock on (stock_relationship.subject_id = stock.stock_id)
 544         WHERE nd_experiment_project.project_id = ?";
 545
 546     my $h = $schema->storage->dbh()->prepare($q);
 547     $h->execute($female_plot_typeid, $trial_id);
 548
 549     my @data = ();
 550     while(my($female_plot_id, $female_plot_name) = $h->fetchrow_array()){
 551         push @data, [$female_plot_id, $female_plot_name]
 552     }
 553
 554     return \@data;
 555 }
 556
 557
 558 =head2 get_male_plots_in_crossing_experiment
 559
 560     Class method.
 561     Returns all male plot names and ids in a specific crossing_experiment.
 562     Example: my @male_plots = CXGN::Cross->get_female_plots_in_crossing_experiment($schema, $trial_id)
 563
 564 =cut
 565
 566 sub get_male_plots_in_crossing_experiment {
 567     my $self = shift;
 568     my $schema = $self->schema;
 569     my $trial_id = $self->trial_id;
 570
 571     my $male_plot_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_plot_of", "stock_relationship")->cvterm_id();
 572
 573     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename FROM nd_experiment_project
 574         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 575         JOIN stock_relationship ON (nd_experiment_stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 576         JOIN stock on (stock_relationship.subject_id = stock.stock_id)
 577         WHERE nd_experiment_project.project_id = ?";
 578
 579     my $h = $schema->storage->dbh()->prepare($q);
 580     $h->execute($male_plot_typeid, $trial_id);
 581
 582     my @data = ();
 583     while(my($male_plot_id, $male_plot_name) = $h->fetchrow_array()){
 584         push @data, [$male_plot_id, $male_plot_name]
 585     }
 586
 587     return \@data;
 588 }
 589
 590
 591 =head2 get_female_plants_in_crossing_experiment
 592
 593     Class method.
 594     Returns all female plant names and ids in a specific crossing_experiment.
 595     Example: my @female_plants = CXGN::Cross->get_female_plants_in_crossing_experiment($schema, $trial_id)
 596
 597 =cut
 598
 599 sub get_female_plants_in_crossing_experiment {
 600     my $self = shift;
 601     my $schema = $self->schema;
 602     my $trial_id = $self->trial_id;
 603
 604     my $female_plant_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_plant_of", "stock_relationship")->cvterm_id();
 605
 606     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename FROM nd_experiment_project
 607         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 608         JOIN stock_relationship ON (nd_experiment_stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 609         JOIN stock on (stock_relationship.subject_id = stock.stock_id)
 610         WHERE nd_experiment_project.project_id = ?";
 611
 612     my $h = $schema->storage->dbh()->prepare($q);
 613     $h->execute($female_plant_typeid, $trial_id);
 614
 615     my @data = ();
 616     while(my($female_plant_id, $female_plant_name) = $h->fetchrow_array()){
 617         push @data, [$female_plant_id, $female_plant_name]
 618     }
 619
 620     return \@data;
 621 }
 622
 623
 624 =head2 get_male_plants_in_crossing_experiment
 625
 626     Class method.
 627     Returns all male plant names and ids in a specific crossing_experiment.
 628     Example: my @male_plants = CXGN::Cross->get_male_plants_in_crossing_experiment($schema, $trial_id)
 629
 630 =cut
 631
 632 sub get_male_plants_in_crossing_experiment {
 633     my $self = shift;
 634     my $schema = $self->schema;
 635     my $trial_id = $self->trial_id;
 636
 637     my $male_plant_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_plant_of", "stock_relationship")->cvterm_id();
 638
 639     my $q = "SELECT DISTINCT stock.stock_id, stock.uniquename FROM nd_experiment_project
 640         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 641         JOIN stock_relationship ON (nd_experiment_stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 642         JOIN stock on (stock_relationship.subject_id = stock.stock_id)
 643         WHERE nd_experiment_project.project_id = ?";
 644
 645     my $h = $schema->storage->dbh()->prepare($q);
 646     $h->execute($male_plant_typeid, $trial_id);
 647
 648     my @data = ();
 649     while(my($male_plant_id, $male_plant_name) = $h->fetchrow_array()){
 650         push @data, [$male_plant_id, $male_plant_name]
 651     }
 652
 653     return \@data;
 654 }
 655
 656
 657 =head2 get_crosses_and_details_in_crossingtrial
 658
 659     Class method.
 660     Returns all cross names, ids, cross_combinations, cross types and parent info in a specific crossing_experiment.
 661     Example: my @crosses_details = CXGN::Cross->get_crosses_and_details_in_crossingtrial($schema, $trial_id)
 662
 663 =cut
 664
 665 sub get_crosses_and_details_in_crossingtrial {
 666     my $self = shift;
 667     my $schema = $self->schema;
 668     my $trial_id = $self->trial_id;
 669
 670     my $cross_combination_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_combination", "stock_property")->cvterm_id();
 671     my $male_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_parent", "stock_relationship")->cvterm_id();
 672     my $female_parent_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
 673     my $female_plot_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_plot_of", "stock_relationship")->cvterm_id();
 674     my $male_plot_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_plot_of", "stock_relationship")->cvterm_id();
 675     my $female_plant_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "female_plant_of", "stock_relationship")->cvterm_id();
 676     my $male_plant_of_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "male_plant_of", "stock_relationship")->cvterm_id();
 677     my $ploidy_level_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "ploidy_level", "stock_property")->cvterm_id();
 678
 679     my $q = "SELECT stock1.stock_id AS cross_id, stock1.uniquename AS cross_name, stockprop.value AS cross_combination, stock_relationship1.value AS cross_type, stock2.stock_id AS female_id,
 680         stock2.uniquename AS female_name, stockprop2.value AS female_ploidy, stock3.stock_id AS male_id, stock3.uniquename AS male_name, stockprop3.value AS male_ploidy, stock4.stock_id AS female_plot_id, stock4.uniquename AS female_plot_name,
 681         stock5.stock_id AS male_plot_id, stock5.uniquename AS male_plot_name, stock6.stock_id AS female_plant_id, stock6.uniquename AS female_plant_name, stock7.stock_id AS male_plant_id, stock7.uniquename AS male_plant_name
 682         FROM nd_experiment_project JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 683         JOIN stock AS stock1 ON (nd_experiment_stock.stock_id = stock1.stock_id)
 684         LEFT JOIN stockprop ON (stock1.stock_id = stockprop.stock_id) AND stockprop.type_id =?
 685         JOIN stock_relationship AS stock_relationship1 ON (stock1.stock_id = stock_relationship1.object_id) AND stock_relationship1.type_id = ?
 686         JOIN stock AS stock2 ON (stock_relationship1.subject_id = stock2.stock_id)
 687         LEFT JOIN stockprop AS stockprop2 ON (stock2.stock_id = stockprop2.stock_id) AND stockprop2.type_id = ?
 688         LEFT JOIN stock_relationship AS stock_relationship2 ON (stock1.stock_id = stock_relationship2.object_id) AND stock_relationship2.type_id = ?
 689         LEFT JOIN stock AS stock3 ON (stock_relationship2.subject_id = stock3.stock_id)
 690         LEFT JOIN stockprop AS stockprop3 ON (stock3.stock_id = stockprop3.stock_id) AND stockprop3.type_id = ?
 691         LEFT JOIN stock_relationship AS stock_relationship3 ON (stock1.stock_id = stock_relationship3.object_id) AND stock_relationship3.type_id = ?
 692         LEFT JOIN stock AS stock4 ON (stock_relationship3.subject_id = stock4.stock_id)
 693         LEFT JOIN stock_relationship AS stock_relationship4 ON (stock1.stock_id = stock_relationship4.object_id) AND stock_relationship4.type_id = ?
 694         LEFT JOIN stock AS stock5 ON (stock_relationship4.subject_id = stock5.stock_id)
 695         LEFT JOIN stock_relationship AS stock_relationship5 ON (stock1.stock_id = stock_relationship5.object_id) AND stock_relationship5.type_id = ?
 696         LEFT JOIN stock AS stock6 ON (stock_relationship5.subject_id = stock6.stock_id)
 697         LEFT JOIN stock_relationship AS stock_relationship6 ON (stock1.stock_id = stock_relationship6.object_id) AND stock_relationship6.type_id = ?
 698         LEFT JOIN stock AS stock7 ON (stock_relationship6.subject_id = stock7.stock_id)
 699         WHERE nd_experiment_project.project_id = ? ORDER BY cross_id ASC";
 700
 701     my $h = $schema->storage->dbh()->prepare($q);
 702
 703     $h->execute($cross_combination_type_id, $female_parent_typeid, $ploidy_level_type_id, $male_parent_typeid, $ploidy_level_type_id, $female_plot_of_typeid, $male_plot_of_typeid, $female_plant_of_typeid, $male_plant_of_typeid, $trial_id);
 704
 705     my @data =();
 706     while(my($cross_id, $cross_name, $cross_combination, $cross_type, $female_parent_id, $female_parent_name, $female_ploidy, $male_parent_id, $male_parent_name, $male_ploidy, $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()){
 707         push @data, [$cross_id, $cross_name, $cross_combination, $cross_type, $female_parent_id, $female_parent_name, $female_ploidy, $male_parent_id, $male_parent_name, $male_ploidy, $female_plot_id, $female_plot_name, $male_plot_id, $male_plot_name, $female_plant_id, $female_plant_name, $male_plant_id, $male_plant_name]
 708     }
 709     return \@data;
 710 }
 711
 712 =head2 get_cross_properties_trial
 713
 714     Class method.
 715     Returns all cross_info in a specific trial.
 716     Example: my @cross_info = CXGN::Cross->get_cross_properties_trial($schema, $trial_id);
 717
 718 =cut
 719
 720 sub get_cross_properties_trial {
 721     my $self = shift;
 722     my $schema = $self->schema;
 723     my $trial_id = $self->trial_id;
 724
 725     my $cross_combination_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_combination", "stock_property")->cvterm_id();
 726     my $cross_props_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "crossing_metadata_json", "stock_property")->cvterm_id();
 727
 728     my $q = "SELECT stock.stock_id, stock.uniquename, stockprop1.value, stockprop2.value FROM nd_experiment_project
 729         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 730         JOIN stock ON (nd_experiment_stock.stock_id = stock.stock_id)
 731         LEFT JOIN stockprop AS stockprop1 ON (stock.stock_id = stockprop1.stock_id) AND stockprop1.type_id = ?
 732         LEFT JOIN stockprop AS stockprop2 ON (stock.stock_id = stockprop2.stock_id) AND stockprop2.type_id = ?
 733         WHERE nd_experiment_project.project_id = ? ORDER BY stock.stock_id ASC";
 734
 735     my $h = $schema->storage->dbh()->prepare($q);
 736
 737     $h->execute($cross_combination_typeid, $cross_props_typeid, $trial_id);
 738
 739
 740     my @data = ();
 741     while(my($cross_id, $cross_name, $cross_combination, $cross_props) = $h->fetchrow_array()){
 742       #print STDERR Dumper $cross_props;
 743         if ($cross_props){
 744             my $cross_props_hash = decode_json$cross_props;
 745             push @data, [$cross_id, $cross_name, $cross_combination, $cross_props_hash]
 746         } else {
 747             push @data, [$cross_id, $cross_name, $cross_combination, $cross_props]
 748         }
 749     }
 750
 751     return \@data;
 752
 753 }
 754
 755
 756 =head2 get_seedlots_from_crossingtrial
 757
 758     Class method.
 759     Returns all seedlots derived from crosses in a specific crossing_experiment.
 760     Example: my @crosses_seedlots = CXGN::Cross->get_seedlots_from_crossingtrial($schema, $trial_id)
 761
 762 =cut
 763
 764 sub get_seedlots_from_crossingtrial {
 765     my $self = shift;
 766     my $schema = $self->schema;
 767     my $trial_id = $self->trial_id;
 768
 769     my $collection_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "collection_of", "stock_relationship")->cvterm_id();
 770
 771     my $q = "SELECT stock1.stock_id, stock1.uniquename, stock2.stock_id, stock2.uniquename FROM nd_experiment_project
 772         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 773         JOIN stock as stock1 on (nd_experiment_stock.stock_id = stock1.stock_id)
 774         LEFT JOIN stock_relationship ON (stock1.stock_id = stock_relationship.subject_id) and stock_relationship.type_id = ?
 775         LEFT JOIN stock as stock2 ON (stock_relationship.object_id = stock2.stock_id)
 776         WHERE nd_experiment_project.project_id = ? ORDER BY stock1.stock_id ASC";
 777
 778     my $h = $schema->storage->dbh()->prepare($q);
 779
 780     $h->execute($collection_of_type_id, $trial_id);
 781
 782     my @data = ();
 783     while(my($cross_id, $cross_name, $seedlot_id, $seedlot_uniquename) = $h->fetchrow_array()){
 784         push @data, [$cross_id, $cross_name, $seedlot_id, $seedlot_uniquename]
 785     }
 786
 787     return \@data;
 788
 789 }
 790
 791 =head2 get_cross_progenies_trial
 792
 793     Class method.
 794     Get numbers of progenies and family names of all the crosses in a crossing_experiment.
 795     Example: my @progenies_info = CXGN::Cross->get_cross_progenies_trial($schema, $trial_id)
 796
 797 =cut
 798
 799 sub get_cross_progenies_trial {
 800     my $self = shift;
 801     my $schema = $self->schema;
 802     my $trial_id = $self->trial_id;
 803
 804     my $cross_combination_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_combination", "stock_property")->cvterm_id();
 805     my $offspring_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "offspring_of", "stock_relationship")->cvterm_id();
 806     my $family_name_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "family_name", "stock_type")->cvterm_id();
 807     my $cross_member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_member_of", "stock_relationship")->cvterm_id();
 808
 809     my $q = "SELECT cross_table.cross_id, cross_table.cross_name, cross_table.cross_combination, cross_table.family_id, cross_table.family_name, progeny_count_table.progeny_number
 810         FROM
 811         (SELECT stock.stock_id AS cross_id, stock.uniquename AS cross_name, stockprop.value AS cross_combination, stock2.stock_id AS family_id, stock2.uniquename AS family_name
 812         FROM nd_experiment_project JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 813         JOIN stock ON (nd_experiment_stock.stock_id = stock.stock_id)
 814         LEFT JOIN stockprop ON (stock.stock_id = stockprop.stock_id) AND stockprop.type_id = ?
 815         LEFT JOIN stock_relationship ON (stock.stock_id = stock_relationship.subject_id) AND stock_relationship.type_id = ?
 816         LEFT JOIN stock AS stock2 ON (stock_relationship.object_id = stock2.stock_id) AND stock2.type_id = ?
 817         WHERE nd_experiment_project.project_id = ?) AS cross_table
 818         LEFT JOIN
 819         (SELECT DISTINCT stock.stock_id AS cross_id, COUNT (stock_relationship.subject_id) AS progeny_number
 820         FROM nd_experiment_project JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 821         JOIN stock ON (nd_experiment_stock.stock_id = stock.stock_id)
 822         LEFT JOIN stock_relationship ON (stock.stock_id = stock_relationship.object_id) AND stock_relationship.type_id = ?
 823         WHERE nd_experiment_project.project_id = ? GROUP BY cross_id) AS progeny_count_table
 824         ON (cross_table.cross_id = progeny_count_table.cross_id) ORDER BY cross_id ASC";
 825
 826     my $h = $schema->storage->dbh()->prepare($q);
 827
 828     $h->execute($cross_combination_type_id, $cross_member_of_type_id, $family_name_type_id, $trial_id, $offspring_of_type_id, $trial_id);
 829
 830     my @data =();
 831     while(my($cross_id, $cross_name, $cross_combination, $family_id, $family_name, $progeny_number) = $h->fetchrow_array()){
 832         push @data, [$cross_id, $cross_name, $cross_combination, $family_id, $family_name, $progeny_number]
 833     }
 834
 835     return \@data;
 836
 837 }
 838
 839
 840
 841 =head2 delete
 842
 843  Usage:        $cross->delete();
 844  Desc:         Deletes a cross
 845  Ret:          error string if error, undef otherwise
 846  Args:         none
 847  Side Effects: deletes project entry, nd_experiment entry, and stock entry.
 848                does not check if
 849  Example:
 850
 851 =cut
 852
 853
 854 sub delete {
 855     my $self = shift;
 856
 857     print STDERR "Delete cross ".$self->cross_name()."\n";
 858     my $dbh = $self->schema()->storage()->dbh();
 859     my $schema = $self->schema();
 860     my $cross_id = $self->cross_stock_id();
 861
 862     eval {
 863         $dbh->begin_work();
 864
 865     my $cross_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
 866     my $cross_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_experiment", "experiment_type")->cvterm_id();
 867     my $collection_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "collection_of", "stock_relationship")->cvterm_id();
 868     my $field_layout_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'field_layout', 'experiment_type')->cvterm_id();
 869     my $plot_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_of', 'stock_relationship')->cvterm_id();
 870
 871     # checking if cross has associated seedlot
 872     my $q = "SELECT stock_relationship.type_id, stock.uniquename FROM stock_relationship JOIN stock ON (stock_relationship.object_id = stock.stock_id) WHERE stock_relationship.subject_id = ?";
 873
 874     my $h = $self->schema->storage->dbh()->prepare($q);
 875
 876     $h->execute($cross_id);
 877
 878     while (my($type_id, $seedlot_name) = $h->fetchrow_array()) {
 879             if ($type_id == $collection_of_type_id) {
 880             print STDERR "Cross has associated seedlot. Cannot delete.\n";
 881                 die "Cross has associated seedlot: $seedlot_name. Cannot delete.\n";
 882             }
 883     }
 884
 885     #checking if cross has associated trial
 886     my $q2 = "SELECT nd_experiment_stock.type_id, project.name FROM stock_relationship JOIN nd_experiment_stock ON (stock_relationship.subject_id = nd_experiment_stock.stock_id) AND stock_relationship.type_id = ?
 887         JOIN nd_experiment_project ON (nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id)
 888         JOIN project ON (nd_experiment_project.project_id = project.project_id) WHERE stock_relationship.object_id = ? ";
 889
 890     my $h2 = $self->schema->storage->dbh()->prepare($q2);
 891
 892     $h2->execute($plot_of_type_id, $cross_id);
 893
 894     while (my($type_id, $project_name) = $h2->fetchrow_array()) {
 895         if ($type_id == $field_layout_type_id) {
 896             print STDERR "Cross has associated trial. Cannot delete.\n";
 897             die "Cross has associated trial: $project_name. Cannot delete.\n";
 898         }
 899     }
 900
 901     #checking if any progeny has associated data
 902         my $properties = $self->progeny_properties();
 903
 904         my $can_delete =
 905             ($properties->{trials} == 0) &&
 906             ($properties->{traits} == 0) &&
 907             ($properties->{genotypes} == 0) &&
 908             ($properties->{images} == 0);
 909
 910         if (! $can_delete) {
 911             print STDERR "Cross has associated data. Cannot delete.\n";
 912             die "Cross has associated data. ($properties->{trials} trials, $properties->{traits} traits and $properties->{genoytpes} genotypes. Cannot delete...\n";
 913         }
 914         else {
 915             print STDERR "This cross has no associated data that would prevent deletion.";
 916         }
 917
 918     #checking if the stock id has cross stock type
 919     my $cross_rs = $schema->resultset("Stock::Stock")->find ({stock_id => $cross_id, type_id => $cross_type_id});
 920     if (!$cross_rs) {
 921         print STDERR "This stock id is not a cross. Cannot delete.\n";
 922             die "This stock id is not a cross. Cannot delete.\n";
 923     }
 924
 925     #get nd_experiment_id with cross_experiment_type
 926     my $experiment_id;
 927     my $nd_q = "SELECT nd_experiment.nd_experiment_id FROM nd_experiment_stock
 928         JOIN nd_experiment ON (nd_experiment_stock.nd_experiment_id = nd_experiment.nd_experiment_id)
 929         WHERE nd_experiment.type_id = ? AND nd_experiment_stock.stock_id = ?";
 930
 931     my $nd_h = $schema->storage->dbh()->prepare($nd_q);
 932     $nd_h->execute($cross_experiment_type_id, $cross_id);
 933     my @nd_experiment_ids= $nd_h->fetchrow_array();
 934     if (scalar @nd_experiment_ids == 1) {
 935         $experiment_id = $nd_experiment_ids[0];
 936     } else {
 937         print STDERR "Error retrieving experiment id"."\n";
 938         die "Error retrieving experiment id";
 939     }
 940 #    print STDERR "ND EXPERIMENT ID =".Dumper($experiment_id)."\n";
 941
 942     #delete the nd_experiment_md_files entries
 943     my $md_files_q = "DELETE FROM phenome.nd_experiment_md_files WHERE nd_experiment_id = ?";
 944     my $md_files_h = $schema->storage->dbh()->prepare($md_files_q);
 945     $md_files_h->execute($experiment_id);
 946
 947         # delete the nd_experiment entries
 948         print STDERR "Deleting nd_experiment entry for cross...\n";
 949         my $q2= "delete from nd_experiment where nd_experiment.nd_experiment_id = ? AND nd_experiment.type_id = ?";
 950         my $h2 = $dbh->prepare($q2);
 951         $h2->execute($experiment_id, $cross_experiment_type_id);
 952
 953         # delete stock owner entries
 954         #
 955         print STDERR "Deleting associated stock_owners...\n";
 956         my $q3 = "delete from phenome.stock_owner where stock_id=?";
 957         my $h3 = $dbh->prepare($q3);
 958         $h3->execute($self->cross_stock_id());
 959
 960         # delete the stock entries
 961         #
 962         print STDERR "Deleting the stock entry...\n";
 963         my $q4 = "delete from stock where stock.stock_id=? and stock.type_id = ?";
 964         my $h4 = $dbh->prepare($q4);
 965         $h4->execute($self->cross_stock_id(), $cross_type_id);
 966
 967         print STDERR Dumper($properties);
 968         # delete the progeny...
 969         #
 970         print STDERR "Deleting the progeny...\n";
 971         my $q5 = "delete from stock where stock_id =?";
 972         my $h5 = $dbh->prepare($q5);
 973         foreach my $progeny (@{$properties->{subjects}}) {
 974
 975             if ($progeny->[2] eq "offspring_of") {
 976                 my $s = CXGN::Stock->new( { schema => $schema, stock_id => $progeny->[0]});
 977                 $s->hard_delete();
 978             }
 979         }
 980
 981     };
 982
 983     if ($@) {
 984         print STDERR "An error occurred while deleting cross id ".$self->cross_stock_id()."$@\n";
 985         $dbh->rollback();
 986         return $@;
 987     }
 988     else {
 989         $dbh->commit();
 990         return 0;
 991     }
 992 }
 993
 994 sub progeny_properties {
 995     my $self = shift;
 996
 997     my $cross_type_id = SGN::Model::Cvterm->get_cvterm_row($self->schema(), "cross", "stock_type")->cvterm_id();
 998
 999     print STDERR "sub cross_deletion_possible...\n";
1000     my $q = "SELECT subject.stock_id, subject.uniquename, cvterm.name from stock join stock_relationship on (stock.stock_id=stock_relationship.object_id) join stock as subject on(stock_relationship.subject_id=subject.stock_id) join cvterm on (stock_relationship.type_id=cvterm.cvterm_id) where stock.stock_id = ? and stock.type_id=?";
1001
1002     my $h = $self->schema->storage->dbh()->prepare($q);
1003
1004     $h->execute($self->cross_stock_id(), $cross_type_id);
1005
1006     my @subjects = ();
1007     my $has_trials = 0;
1008     my $has_traits = 0;
1009     my $has_genotypes = 0;
1010     my $has_images;
1011
1012     while (my($stock_id, $name, $type) = $h->fetchrow_array()) {
1013         print STDERR "ID $stock_id NAME $name TYPE $type\n";
1014         push @subjects, [$stock_id, $name, $type];
1015
1016         if ($type eq "offspring_of") { # child
1017             my $s = CXGN::Stock->new( { schema => $self->schema(),  stock_id => $stock_id });
1018             if (my @traits = $s->get_trait_list()) {
1019                 print STDERR "Associated traits: ".Dumper(\@traits);
1020                 $has_traits += scalar(@traits);
1021             }
1022             if (my @trials = $s->get_trials()) {
1023                 print STDERR "Associated trials: ".Dumper(\@trials);
1024                 $has_trials += scalar(@trials);
1025             }
1026             if (my $genotypeprop_ids = $s->get_genotypeprop_ids()) {
1027                 print STDERR "Associated genotypes: ".Dumper($genotypeprop_ids);
1028                 $has_genotypes += scalar(@$genotypeprop_ids);
1029             }
1030             if (my @image_ids = $s->get_image_ids()) {
1031                 print STDERR "Associated images: ".Dumper(\@image_ids);
1032                 $has_images += scalar(@image_ids);
1033             }
1034         }
1035     }
1036     my $data = {
1037         traits => $has_traits,
1038         trials => $has_trials,
1039         genotypes => $has_genotypes,
1040         images => $has_images,
1041         subjects => \@subjects,
1042     };
1043
1044     print STDERR Dumper($data);
1045     return $data;
1046 }
1047
1048
1049 sub get_cross_tissue_culture_samples {
1050     my $self = shift;
1051     my $cross_samples_cvterm = SGN::Model::Cvterm->get_cvterm_row($self->schema, 'tissue_culture_data_json', 'stock_property')->cvterm_id();
1052     my $cross_samples = $self->schema->resultset("Stock::Stockprop")->find({stock_id => $self->cross_stock_id, type_id => $cross_samples_cvterm});
1053
1054     my $samples_json_string;
1055     if($cross_samples){
1056         $samples_json_string = $cross_samples->value();
1057     }
1058
1059     my $samples_hash_ref ={};
1060     if($samples_json_string){
1061         $samples_hash_ref = decode_json $samples_json_string;
1062     }
1063
1064     return $samples_hash_ref;
1065 }
1066
1067
1068 =head2 get_pedigree_male_parents
1069
1070     Class method.
1071     Returns all male parents that were crossed with a spefified female parent.
1072     Example: my @male_parents = CXGN::Cross->get_pedigree_male_parents($schema, $pedigree_female_parent)
1073
1074 =cut
1075
1076 sub get_pedigree_male_parents {
1077     my $class = shift;
1078     my $schema = shift;
1079     my $pedigree_female_parent = shift;
1080
1081     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
1082     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
1083     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
1084
1085     my $dbh = $schema->storage->dbh();
1086
1087     my $q = "SELECT DISTINCT male_parent.stock_id, male_parent.uniquename FROM stock as female_parent
1088     INNER JOIN stock_relationship AS stock_relationship1 ON (female_parent.stock_id=stock_relationship1.subject_id) AND stock_relationship1.type_id= ?
1089     INNER JOIN stock AS check_type ON (stock_relationship1.object_id=check_type.stock_id) AND check_type.type_id = ?
1090     INNER JOIN stock_relationship AS stock_relationship2 ON (stock_relationship1.object_id=stock_relationship2.object_id) AND stock_relationship2.type_id = ?
1091     INNER JOIN stock AS male_parent ON (male_parent.stock_id=stock_relationship2.subject_id)
1092     WHERE female_parent.uniquename= ? ORDER BY male_parent.uniquename ASC";
1093
1094     my $h = $dbh->prepare($q);
1095     $h->execute($female_parent_type_id, $accession_type_id, $male_parent_type_id, $pedigree_female_parent);
1096
1097     my @male_parents=();
1098     while(my ($male_parent_id, $male_parent_name) = $h->fetchrow_array()){
1099       push @male_parents, [$male_parent_name];
1100     }
1101
1102     return \@male_parents;
1103
1104 }
1105
1106
1107 =head2 get_pedigree_female_parents
1108
1109     Class method.
1110     Returns all female parents that were crossed with a spefified male parent.
1111     Example: my @female_parents = CXGN::Cross->get_pedigree_female_parents($schema, $pedigree_male_parent)
1112
1113 =cut
1114
1115 sub get_pedigree_female_parents {
1116     my $class = shift;
1117     my $schema = shift;
1118     my $pedigree_male_parent = shift;
1119
1120     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
1121     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
1122     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
1123
1124     my $dbh = $schema->storage->dbh();
1125
1126     my $q = "SELECT DISTINCT female_parent.stock_id, female_parent.uniquename FROM stock as male_parent
1127     INNER JOIN stock_relationship AS stock_relationship1 ON (male_parent.stock_id=stock_relationship1.subject_id) AND stock_relationship1.type_id= ?
1128     INNER JOIN stock AS check_type ON (stock_relationship1.object_id=check_type.stock_id) AND check_type.type_id = ?
1129     INNER JOIN stock_relationship AS stock_relationship2 ON (stock_relationship1.object_id=stock_relationship2.object_id) AND stock_relationship2.type_id = ?
1130     INNER JOIN stock AS female_parent ON (female_parent.stock_id=stock_relationship2.subject_id)
1131     WHERE male_parent.uniquename= ? ORDER BY female_parent.uniquename ASC";
1132
1133
1134     my $h = $dbh->prepare($q);
1135     $h->execute($male_parent_type_id, $accession_type_id, $female_parent_type_id, $pedigree_male_parent);
1136
1137     my @female_parents=();
1138     while(my ($female_parent_id, $female_parent_name) = $h->fetchrow_array()){
1139       push @female_parents, [$female_parent_name];
1140     }
1141
1142     return \@female_parents;
1143
1144 }
1145
1146
1147 =head2 get_cross_male_parents
1148
1149     Class method.
1150     Returns all male parents that were crossed with a spefified male parent.
1151     Example: my @male_parents = CXGN::Cross->get_cross_male_parents($schema, $cross_female_parent)
1152
1153 =cut
1154
1155 sub get_cross_male_parents {
1156     my $class = shift;
1157     my $schema = shift;
1158     my $cross_female_parent = shift;
1159
1160     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
1161     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
1162     my $cross_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id();
1163
1164     my $dbh = $schema->storage->dbh();
1165
1166     my $q = "SELECT DISTINCT male_parent.stock_id, male_parent.uniquename FROM stock as female_parent
1167     INNER JOIN stock_relationship AS stock_relationship1 ON (female_parent.stock_id=stock_relationship1.subject_id)
1168     INNER JOIN stock AS check_type ON (stock_relationship1.object_id=check_type.stock_id)
1169     LEFT JOIN stock_relationship AS stock_relationship2 ON (stock_relationship1.object_id = stock_relationship2.object_id)
1170     LEFT JOIN stock AS male_parent ON (male_parent.stock_id=stock_relationship2.subject_id)
1171     WHERE female_parent.uniquename = ? AND stock_relationship1.type_id = ? AND check_type.type_id = ? AND stock_relationship2.type_id = ?
1172     ORDER BY male_parent.uniquename ASC";
1173
1174     my $h = $dbh->prepare($q);
1175     $h->execute($cross_female_parent, $female_parent_type_id, $cross_type_id, $male_parent_type_id );
1176
1177     my @male_parents=();
1178     while(my ($male_parent_id, $male_parent_name) = $h->fetchrow_array()){
1179         push @male_parents, [$male_parent_name];
1180     }
1181
1182     return \@male_parents;
1183
1184 }
1185
1186
1187 =head2 get_cross_female_parents
1188
1189     Class method.
1190     Returns all female parents that were crossed with a spefified male parent.
1191     Example: my @male_parents = CXGN::Cross->get_cross_male_parents($schema, $cross_female_parent)
1192
1193 =cut
1194
1195 sub get_cross_female_parents {
1196     my $class = shift;
1197     my $schema = shift;
1198     my $cross_male_parent = shift;
1199
1200     my $male_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'male_parent', 'stock_relationship')->cvterm_id();
1201     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'female_parent', 'stock_relationship')->cvterm_id();
1202     my $cross_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id();
1203
1204     my $dbh = $schema->storage->dbh();
1205
1206     my $q = "SELECT DISTINCT female_parent.stock_id, female_parent.uniquename FROM stock as male_parent
1207     INNER JOIN stock_relationship AS stock_relationship1 ON (male_parent.stock_id=stock_relationship1.subject_id)
1208     INNER JOIN stock AS check_type ON (stock_relationship1.object_id=check_type.stock_id)
1209     LEFT JOIN stock_relationship AS stock_relationship2 ON (stock_relationship1.object_id = stock_relationship2.object_id)
1210     LEFT JOIN stock AS female_parent ON (female_parent.stock_id=stock_relationship2.subject_id)
1211     WHERE male_parent.uniquename = ? AND stock_relationship1.type_id = ? AND check_type.type_id = ? AND stock_relationship2.type_id = ?
1212     ORDER BY female_parent.uniquename ASC";
1213
1214     my $h = $dbh->prepare($q);
1215     $h->execute($cross_male_parent, $male_parent_type_id, $cross_type_id, $female_parent_type_id );
1216
1217     my @female_parents=();
1218     while(my ($female_parent_id, $female_parent_name) = $h->fetchrow_array()){
1219       push @female_parents, [$female_parent_name];
1220     }
1221
1222     return \@female_parents;
1223
1224 }
1225
1226
1227 =head2 get_cross_identifiers_in_crossing_experiment
1228
1229     Class method.
1230     Returns all cross identifiers in a specific crossing_experiment together with the corresponding cross unique ids.
1231     Example: my $crosses = CXGN::Cross->new({schema => $schema, trial_id => $crossing_experiment_id});
1232              my $identifiers = $crosses->get_cross_identifiers_in_crossing_experiment();
1233
1234 =cut
1235
1236 sub get_cross_identifiers_in_crossing_experiment {
1237     my $self = shift;
1238     my $schema = $self->schema;
1239     my $trial_id = $self->trial_id;
1240
1241     my $cross_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id;
1242     my $cross_identifier_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross_identifier', 'stock_property')->cvterm_id;
1243
1244     my $q = "SELECT stock.uniquename, stockprop.value FROM nd_experiment_project
1245         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
1246         JOIN stock ON (nd_experiment_stock.stock_id = stock.stock_id) AND stock.type_id = ?
1247         JOIN stockprop ON (stock.stock_id = stockprop.stock_id)
1248         WHERE stockprop.type_id = ? AND nd_experiment_project.project_id = ?";
1249
1250     my $h = $schema->storage->dbh()->prepare($q);
1251     $h->execute($cross_type_id, $cross_identifier_type_id, $trial_id);
1252
1253     my %cross_identifier_hash;
1254     while(my($cross_unique_id, $cross_identifier) = $h->fetchrow_array()){
1255         $cross_identifier_hash{$cross_identifier} = $cross_unique_id;
1256     }
1257
1258     return \%cross_identifier_hash;
1259 }
1260
1261
1262 =head2 get_cross_additional_info_trial
1263
1264     Class method.
1265     Returns all cross_additional_info in a specific trial.
1266     Example: my @cross_additional_info = CXGN::Cross->get_cross_additional_trial($schema, $trial_id);
1267
1268 =cut
1269
1270 sub get_cross_additional_info_trial {
1271     my $self = shift;
1272     my $schema = $self->schema;
1273     my $trial_id = $self->trial_id;
1274
1275     my $cross_combination_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_combination", "stock_property")->cvterm_id();
1276     my $cross_additional_info_typeid = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_additional_info", "stock_property")->cvterm_id();
1277
1278     my $q = "SELECT stock.stock_id, stock.uniquename, stockprop1.value, stockprop2.value FROM nd_experiment_project
1279         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
1280         JOIN stock ON (nd_experiment_stock.stock_id = stock.stock_id)
1281         LEFT JOIN stockprop AS stockprop1 ON (stock.stock_id = stockprop1.stock_id) AND stockprop1.type_id = ?
1282         LEFT JOIN stockprop AS stockprop2 ON (stock.stock_id = stockprop2.stock_id) AND stockprop2.type_id = ?
1283         WHERE nd_experiment_project.project_id = ?";
1284
1285     my $h = $schema->storage->dbh()->prepare($q);
1286
1287     $h->execute($cross_combination_typeid, $cross_additional_info_typeid, $trial_id);
1288
1289
1290     my @data = ();
1291     while(my($cross_id, $cross_name, $cross_combination, $cross_additional_info_json) = $h->fetchrow_array()){
1292       #print STDERR Dumper $cross_props;
1293         if ($cross_additional_info_json){
1294             my $cross_additional_info_hash = decode_json$cross_additional_info_json;
1295             push @data, [$cross_id, $cross_name, $cross_combination, $cross_additional_info_hash]
1296         } else {
1297             push @data, [$cross_id, $cross_name, $cross_combination, $cross_additional_info_json]
1298         }
1299     }
1300
1301     return \@data;
1302 }
1303
1304
1305 =head2 get_nd_experiment_id_with_type_cross_experiment
1306
1307
1308 =cut
1309
1310 sub get_nd_experiment_id_with_type_cross_experiment {
1311     my $self = shift;
1312     my $schema = $self->schema;
1313     my $cross_name = $self->cross_name();
1314     my $cross_id;
1315     my $experiment_id;
1316 #    print STDERR "CROSS NAME =".Dumper($cross_name)."\n";
1317     my $cross_experiment_type_id =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross_experiment', 'experiment_type')->cvterm_id;
1318     my $cross_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id();
1319
1320     my $cross_rs = $schema->resultset("Stock::Stock")->find( { uniquename => $cross_name, type_id => $cross_type_id});
1321
1322     if ($cross_rs) {
1323         $cross_id = $cross_rs->stock_id();
1324     } else {
1325         print STDERR "Error retrieving cross ID"."\n";
1326         return;
1327     }
1328
1329     my $q = "SELECT nd_experiment.nd_experiment_id FROM nd_experiment_stock
1330         JOIN nd_experiment ON (nd_experiment_stock.nd_experiment_id = nd_experiment.nd_experiment_id)
1331         WHERE nd_experiment.type_id = ? AND nd_experiment_stock.stock_id = ?";
1332
1333     my $h = $schema->storage->dbh()->prepare($q);
1334     $h->execute($cross_experiment_type_id, $cross_id);
1335     my @nd_experiment_ids= $h->fetchrow_array();
1336     if (scalar @nd_experiment_ids == 1) {
1337         $experiment_id = $nd_experiment_ids[0];
1338     } else {
1339         print STDERR "Error retrieving experiment ID"."\n";
1340         return;
1341     }
1342
1343     return $experiment_id;
1344 }
1345
1346
1347 =head2 get_intercross_file_metadata
1348
1349
1350 =cut
1351
1352 sub get_intercross_file_metadata {
1353     my $self = shift;
1354     my $schema = $self->schema;
1355     my $crossing_experiment_id = $self->trial_id();
1356     my $project_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'crossing_trial', 'project_type')->cvterm_id();
1357     my $file_metadata_json_type_id =  SGN::Model::Cvterm->get_cvterm_row($schema, 'file_metadata_json', 'project_property')->cvterm_id;
1358
1359     my $projectprop_rs = $schema->resultset("Project::Projectprop")->find({ project_id => $crossing_experiment_id, type_id => $file_metadata_json_type_id });
1360
1361     my @file_ids;
1362     my @file_info = ();
1363     my $dbh = $schema->storage->dbh();
1364     if ($projectprop_rs){
1365         my $file_metadata_json = $projectprop_rs->value();
1366         my $file_metadata = decode_json$file_metadata_json;
1367         my $intercross_download_files = $file_metadata->{'intercross_download'};
1368         my %intercross_download_hash = %{$intercross_download_files};
1369         @file_ids = keys %intercross_download_hash;
1370         if (scalar @file_ids > 0) {
1371             foreach my $id (@file_ids){
1372                 my @each_row = ();
1373                 my $q = "SELECT f.file_id, m.create_date, p.sp_person_id, p.username, f.basename, f.dirname, f.filetype
1374                     FROM metadata.md_files AS f
1375                     JOIN metadata.md_metadata as m ON (f.metadata_id = m.metadata_id)
1376                     JOIN sgn_people.sp_person as p ON (p.sp_person_id = m.create_person_id) WHERE f.file_id = ?
1377                    ORDER BY f.file_id ASC";
1378
1379                 my $h = $dbh->prepare($q);
1380                 $h->execute($id);
1381                 @each_row = $h->fetchrow_array();
1382                 push @file_info, [@each_row];
1383             }
1384         }
1385     }
1386
1387     return \@file_info;
1388
1389 }
1390
1391
1392
1393 1;