lib/CXGN/FamilyName.pm

   1 =head1 NAME
   2
   3 CXGN::FamilyName - an object representing a family name in the database
   4
   5 =head1 DESCRIPTION
   6
   7     my $family_name = CXGN::FamilyName->new( { schema => $schema, family_id => xxxxx });
   8
   9
  10 =head1 AUTHORS
  11
  12     Titima Tantikanjana <tt15@cornell.edu>
  13
  14 =head1 METHODS
  15
  16 =cut
  17
  18 package CXGN::FamilyName;
  19
  20 use Moose;
  21 use SGN::Model::Cvterm;
  22 use Data::Dumper;
  23 use JSON;
  24
  25 extends 'CXGN::Stock';
  26
  27 has 'family_stock_id' => (isa => "Int",
  28     is => 'rw',
  29     required => 1,
  30 );
  31
  32 has 'family_name' => (isa => 'Maybe[Str]',
  33     is => 'rw',
  34 );
  35
  36 sub BUILD {
  37     my $self = shift;
  38     my $args = shift;
  39
  40     my $schema = $args->{schema};
  41     my $family_id = $args->{family_stock_id};
  42
  43     $self->stock_id($family_id);
  44
  45     my $family_rs = $schema->resultset("Stock::Stock")->find( { stock_id => $family_id });
  46
  47     if ($family_rs) {
  48         my $family_uniquename = $family_rs->uniquename();
  49         $self->family_name($family_uniquename);
  50         $self->family_stock_id($family_id);
  51     }
  52 }
  53
  54
  55 sub get_family_parents {
  56     my $self = shift;
  57     my $schema = $self->schema();
  58     my $family_stock_id = $self->family_stock_id();
  59     my $family_name_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "family_name", "stock_type")->cvterm_id();
  60     my $family_female_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "family_female_parent_of", "stock_relationship")->cvterm_id();
  61     my $family_male_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "family_male_parent_of", "stock_relationship")->cvterm_id();
  62     my $ploidy_level_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'ploidy_level', 'stock_property')->cvterm_id();
  63
  64     my $q = "SELECT female_parent.stock_id, female_parent.uniquename, cvterm1.name, female_ploidy.value, male_parent.stock_id, male_parent.uniquename, cvterm2.name, male_ploidy.value
  65         FROM stock AS female_parent
  66         JOIN cvterm AS cvterm1 ON (female_parent.type_id = cvterm1.cvterm_id)
  67         JOIN stock_relationship AS stock_relationship1 ON (female_parent.stock_id = stock_relationship1.subject_id) and stock_relationship1.type_id = ?
  68         LEFT JOIN stockprop AS female_ploidy ON (female_parent.stock_id = female_ploidy.stock_id) AND female_ploidy.type_id = ?
  69         LEFT JOIN stock_relationship AS stock_relationship2 ON (stock_relationship1.object_id = stock_relationship2.object_id) and stock_relationship2.type_id = ?
  70         LEFT JOIN stock as male_parent ON (male_parent.stock_id = stock_relationship2.subject_id)
  71         LEFT JOIN stockprop AS male_ploidy ON (male_parent.stock_id = male_ploidy.stock_id) AND male_ploidy.type_id = ?
  72         LEFT JOIN cvterm AS cvterm2 ON (male_parent.type_id = cvterm2.cvterm_id)
  73         WHERE stock_relationship1.object_id = ?";
  74
  75     my $h = $schema->storage->dbh()->prepare($q);
  76
  77     $h->execute($family_female_type_id, $ploidy_level_type_id, $family_male_type_id, $ploidy_level_type_id, $family_stock_id);
  78
  79     my @family_parents = ();
  80     while (my ($female_parent_id,  $female_parent_name, $female_stock_type, $female_ploidy, $male_parent_id, $male_parent_name, $male_stock_type, $male_ploidy) = $h->fetchrow_array()){
  81         push @family_parents, [$female_parent_id,  $female_parent_name, $female_stock_type, $female_ploidy, $male_parent_id, $male_parent_name, $male_stock_type, $male_ploidy]
  82     }
  83 #    print STDERR Dumper(\@family_parents);
  84     return \@family_parents;
  85 }
  86
  87
  88 sub get_family_members {
  89     my $self = shift;
  90     my $schema = $self->schema();
  91     my $family_stock_id = $self->family_stock_id();
  92     my $cross_member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_member_of", "stock_relationship")->cvterm_id();
  93     my $offspring_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "offspring_of", "stock_relationship")->cvterm_id();
  94     my $female_parent_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "female_parent", "stock_relationship")->cvterm_id();
  95     my $cross_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_experiment", "experiment_type")->cvterm_id();
  96
  97     my $q = "SELECT cross_table.cross_id, cross_table.cross_name, cross_table.cross_type, cross_table.crossing_experiment_id, cross_table.crossing_experiment_name, progeny_count_table.progeny_number
  98         FROM
  99         (SELECT stock.stock_id AS cross_id, stock.uniquename AS cross_name, stock_relationship1.value AS cross_type, project.project_id AS crossing_experiment_id, project.name AS crossing_experiment_name
 100         FROM stock JOIN stock_relationship on (stock.stock_id = stock_relationship.subject_id) AND stock_relationship.type_id = ?
 101         JOIN stock_relationship AS stock_relationship1 ON (stock_relationship.subject_id = stock_relationship1.object_id) AND stock_relationship1.type_id = ?
 102         JOIN nd_experiment_stock ON (nd_experiment_stock.stock_id = stock_relationship1.object_id)
 103         JOIN nd_experiment ON (nd_experiment_stock.nd_experiment_id = nd_experiment.nd_experiment_id) AND nd_experiment.type_id = ?
 104         JOIN nd_experiment_project ON (nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id)
 105         JOIN project ON (nd_experiment_project.project_id = project.project_id) WHERE stock_relationship.object_id = ?) AS cross_table
 106         LEFT JOIN
 107         (SELECT DISTINCT stock.stock_id AS cross_id, COUNT (stock_relationship1.subject_id) AS progeny_number
 108         FROM stock JOIN stock_relationship on (stock.stock_id = stock_relationship.subject_id) AND stock_relationship.type_id = ?
 109         LEFT JOIN stock_relationship AS stock_relationship1 ON (stock_relationship.subject_id = stock_relationship1.object_id) AND stock_relationship1.type_id = ?
 110         WHERE stock_relationship.object_id = ? GROUP BY cross_id) AS progeny_count_table
 111         ON (cross_table.cross_id = progeny_count_table.cross_id)";
 112
 113         my $h = $schema->storage->dbh()->prepare($q);
 114
 115         $h->execute($cross_member_of_type_id, $female_parent_type_id, $cross_experiment_type_id, $family_stock_id, $cross_member_of_type_id, $offspring_of_type_id, $family_stock_id);
 116
 117         my @data =();
 118         while(my($cross_id, $cross_name, $cross_type, $crossing_experiment_id, $crossing_experiment_name, $progeny_number) = $h->fetchrow_array()){
 119             push @data, [$cross_id, $cross_name, $cross_type, $crossing_experiment_id, $crossing_experiment_name, $progeny_number]
 120         }
 121
 122 #        print STDERR "CROSS MEMBERS =".Dumper(\@data);
 123         return \@data;
 124 }
 125
 126
 127 sub get_all_progenies {
 128     my $self = shift;
 129     my $schema = $self->schema();
 130     my $family_stock_id = $self->family_stock_id();
 131
 132     my $cross_member_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross_member_of", "stock_relationship")->cvterm_id();
 133     my $offspring_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "offspring_of", "stock_relationship")->cvterm_id();
 134     my $cross_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "cross", "stock_type")->cvterm_id();
 135
 136     my $q = "SELECT progeny.stock_id, progeny.uniquename, stock.stock_id, stock.uniquename
 137         FROM stock_relationship JOIN stock_relationship AS stock_relationship1 ON (stock_relationship.subject_id = stock_relationship1.object_id) AND stock_relationship.type_id = ?
 138         JOIN stock AS progeny ON (stock_relationship1.subject_id = progeny.stock_id) AND stock_relationship1.type_id = ?
 139         JOIN stock ON (stock_relationship1.object_id = stock.stock_id) AND stock.type_id = ?
 140         WHERE stock_relationship.object_id = ?";
 141
 142     my $h = $schema->storage->dbh()->prepare($q);
 143
 144     $h->execute($cross_member_of_type_id, $offspring_of_type_id, $cross_type_id, $family_stock_id);
 145
 146     my @progenies = ();
 147     while (my ($progeny_id,  $progeny_name, $cross_id, $cross_name) = $h->fetchrow_array()){
 148         push @progenies, [$progeny_id, $progeny_name, $cross_id, $cross_name]
 149     }
 150     print STDERR Dumper(\@progenies);
 151     return \@progenies;
 152 }
 153
 154
 155 ###
 156 1;
 157 ###