lib/CXGN/Transformation/Transformation.pm

   1 =head1 NAME
   2
   3 CXGN::Transformation::Transformation - an object representing transformation in the database
   4
   5 =head1 DESCRIPTION
   6
   7
   8 =head1 AUTHORS
   9
  10     Titima Tantikanjana <tt15@cornell.edu>
  11
  12 =head1 METHODS
  13
  14 =cut
  15
  16 package CXGN::Transformation::Transformation;
  17
  18 use Moose;
  19 use SGN::Model::Cvterm;
  20 use Data::Dumper;
  21 use JSON;
  22
  23 has 'schema' => (
  24     isa => 'DBIx::Class::Schema',
  25     is => 'rw',
  26     required => 1,
  27 );
  28
  29 has 'dbh' => (
  30     is  => 'rw',
  31     required => 1,
  32 );
  33
  34 has 'project_id' => (
  35     isa => "Int",
  36     is => 'rw',
  37 );
  38
  39 has 'transformation_stock_id' => (
  40     isa => "Int",
  41     is => 'rw',
  42 );
  43
  44
  45 sub get_active_transformations_in_project {
  46     my $self = shift;
  47     my $schema = $self->schema();
  48     my $project_id = $self->project_id();
  49     my $transformation_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformation", "stock_type")->cvterm_id();
  50         my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
  51         my $vector_construct_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "vector_construct", "stock_type")->cvterm_id();
  52     my $plant_material_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "plant_material_of", "stock_relationship")->cvterm_id();
  53     my $vector_construct_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "vector_construct_of", "stock_relationship")->cvterm_id();
  54     my $transformation_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transformation_experiment', 'experiment_type')->cvterm_id();
  55     my $transformation_notes_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transformation_notes', 'stock_property')->cvterm_id();
  56     my $completed_metadata_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'completed_metadata', 'stock_property')->cvterm_id;
  57     my $terminated_metadata_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'terminated_metadata', 'stock_property')->cvterm_id;
  58
  59     my $q = "SELECT transformation.stock_id, transformation.uniquename, plant.stock_id, plant.uniquename, vector.stock_id, vector.uniquename, stockprop.value
  60         FROM nd_experiment_project
  61         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
  62         JOIN stock AS transformation ON (nd_experiment_stock.stock_id = transformation.stock_id) AND transformation.type_id = ?
  63         JOIN stock_relationship AS plant_relationship ON (plant_relationship.object_id = transformation.stock_id) AND plant_relationship.type_id = ?
  64         JOIN stock AS plant ON (plant_relationship.subject_id = plant.stock_id) AND plant.type_id = ?
  65         JOIN stock_relationship AS vector_relationship ON (vector_relationship.object_id = transformation.stock_id) AND vector_relationship.type_id = ?
  66         JOIN stock AS vector ON (vector_relationship.subject_id = vector.stock_id) AND vector.type_id = ?
  67         LEFT JOIN stockprop ON (stockprop.stock_id = transformation.stock_id) AND stockprop.type_id = ?
  68         LEFT JOIN stockprop AS stockprop2 ON (stockprop2.stock_id = transformation.stock_id) AND stockprop2.type_id in (?, ?)
  69         WHERE nd_experiment_project.project_id = ? AND stockprop2.value IS NULL";
  70
  71     my $h = $schema->storage->dbh()->prepare($q);
  72
  73     $h->execute($transformation_type_id, $plant_material_of_type_id, $accession_type_id, $vector_construct_of_type_id, $vector_construct_type_id, $transformation_notes_type_id, $completed_metadata_type_id, $terminated_metadata_type_id, $project_id);
  74
  75     my @transformations = ();
  76     while (my ($transformation_id,  $transformation_name, $plant_id, $plant_name, $vector_id, $vector_name, $notes) = $h->fetchrow_array()){
  77         push @transformations, [$transformation_id,  $transformation_name, $plant_id, $plant_name, $vector_id, $vector_name, $notes]
  78     }
  79
  80     return \@transformations;
  81 }
  82
  83
  84 sub get_inactive_transformations_in_project {
  85     my $self = shift;
  86     my $schema = $self->schema();
  87     my $project_id = $self->project_id();
  88     my $transformation_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformation", "stock_type")->cvterm_id();
  89         my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
  90         my $vector_construct_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "vector_construct", "stock_type")->cvterm_id();
  91     my $plant_material_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "plant_material_of", "stock_relationship")->cvterm_id();
  92     my $vector_construct_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "vector_construct_of", "stock_relationship")->cvterm_id();
  93     my $transformation_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transformation_experiment', 'experiment_type')->cvterm_id();
  94     my $transformation_notes_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transformation_notes', 'stock_property')->cvterm_id();
  95     my $completed_metadata_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'completed_metadata', 'stock_property')->cvterm_id;
  96     my $terminated_metadata_type_id  =  SGN::Model::Cvterm->get_cvterm_row($schema, 'terminated_metadata', 'stock_property')->cvterm_id;
  97
  98     my $q = "SELECT transformation.stock_id, transformation.uniquename, plant.stock_id, plant.uniquename, vector.stock_id, vector.uniquename, stockprop.value, cvterm.name
  99         FROM nd_experiment_project
 100         JOIN nd_experiment_stock ON (nd_experiment_project.nd_experiment_id = nd_experiment_stock.nd_experiment_id)
 101         JOIN stock AS transformation ON (nd_experiment_stock.stock_id = transformation.stock_id) AND transformation.type_id = ?
 102         JOIN stock_relationship AS plant_relationship ON (plant_relationship.object_id = transformation.stock_id) AND plant_relationship.type_id = ?
 103         JOIN stock AS plant ON (plant_relationship.subject_id = plant.stock_id) AND plant.type_id = ?
 104         JOIN stock_relationship AS vector_relationship ON (vector_relationship.object_id = transformation.stock_id) AND vector_relationship.type_id = ?
 105         JOIN stock AS vector ON (vector_relationship.subject_id = vector.stock_id) AND vector.type_id = ?
 106         LEFT JOIN stockprop ON (stockprop.stock_id = transformation.stock_id) AND stockprop.type_id = ?
 107         JOIN stockprop AS stockprop2 ON (stockprop2.stock_id = transformation.stock_id) AND stockprop2.type_id in (?, ?)
 108         JOIN cvterm ON (stockprop2.type_id = cvterm.cvterm_id)
 109         WHERE nd_experiment_project.project_id = ?";
 110
 111     my $h = $schema->storage->dbh()->prepare($q);
 112
 113     $h->execute($transformation_type_id, $plant_material_of_type_id, $accession_type_id, $vector_construct_of_type_id, $vector_construct_type_id, $transformation_notes_type_id, $completed_metadata_type_id, $terminated_metadata_type_id, $project_id);
 114
 115     my @transformations = ();
 116     while (my ($transformation_id,  $transformation_name, $plant_id, $plant_name, $vector_id, $vector_name, $notes, $status_type) = $h->fetchrow_array()){
 117         push @transformations, [$transformation_id,  $transformation_name, $plant_id, $plant_name, $vector_id, $vector_name, $notes, $status_type]
 118     }
 119
 120     return \@transformations;
 121 }
 122
 123
 124 sub get_transformants {
 125     my $self = shift;
 126     my $schema = $self->schema();
 127     my $transformation_stock_id = $self->transformation_stock_id();
 128     my $transformation_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformation", "stock_type")->cvterm_id();
 129     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
 130     my $transformant_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformant_of", "stock_relationship")->cvterm_id();
 131
 132     my $q = "SELECT stock.stock_id, stock.uniquename
 133         FROM stock_relationship
 134         JOIN stock ON (stock_relationship.subject_id = stock.stock_id) and stock_relationship.type_id = ?
 135         where stock_relationship.object_id = ? AND stock.is_obsolete = 'F' ";
 136
 137     my $h = $schema->storage->dbh()->prepare($q);
 138
 139     $h->execute($transformant_of_type_id, $transformation_stock_id);
 140
 141     my @transformants = ();
 142     while (my ($stock_id,  $stock_name) = $h->fetchrow_array()){
 143         push @transformants, [$stock_id,  $stock_name]
 144     }
 145
 146     return \@transformants;
 147 }
 148
 149
 150 sub get_obsoleted_transformants {
 151     my $self = shift;
 152     my $schema = $self->schema();
 153     my $transformation_stock_id = $self->transformation_stock_id();
 154     my $transformation_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformation", "stock_type")->cvterm_id();
 155     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
 156     my $transformant_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformant_of", "stock_relationship")->cvterm_id();
 157
 158     my $q = "SELECT stock.stock_id, stock.uniquename
 159         FROM stock_relationship
 160         JOIN stock ON (stock_relationship.subject_id = stock.stock_id) and stock_relationship.type_id = ?
 161         where stock_relationship.object_id = ? AND stock.is_obsolete != 'F' ";
 162
 163     my $h = $schema->storage->dbh()->prepare($q);
 164
 165     $h->execute($transformant_of_type_id, $transformation_stock_id);
 166
 167     my @obsoleted_transformants = ();
 168     while (my ($stock_id,  $stock_name) = $h->fetchrow_array()){
 169         push @obsoleted_transformants, [$stock_id,  $stock_name]
 170     }
 171
 172     return \@obsoleted_transformants;
 173 }
 174
 175
 176 sub get_transformation_info {
 177     my $self = shift;
 178     my $schema = $self->schema();
 179     my $transformation_stock_id = $self->transformation_stock_id();
 180     my $transformation_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "transformation", "stock_type")->cvterm_id();
 181     my $accession_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "accession", "stock_type")->cvterm_id();
 182     my $vector_construct_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "vector_construct", "stock_type")->cvterm_id();
 183     my $plant_material_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "plant_material_of", "stock_relationship")->cvterm_id();
 184     my $vector_construct_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "vector_construct_of", "stock_relationship")->cvterm_id();
 185     my $transformation_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transformation_experiment', 'experiment_type')->cvterm_id();
 186     my $transformation_notes_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transformation_notes', 'stock_property')->cvterm_id();
 187     my $completed_metadata_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'completed_metadata', 'stock_property')->cvterm_id();
 188     my $terminated_metadata_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'terminated_metadata', 'stock_property')->cvterm_id();
 189
 190     my $q = "SELECT plant.stock_id, plant.uniquename, vector.stock_id, vector.uniquename, stockprop.value, cvterm.name
 191         FROM stock AS transformation
 192         JOIN stock_relationship AS plant_relationship ON (transformation.stock_id = plant_relationship.object_id) AND plant_relationship.type_id = ?
 193         JOIN stock AS plant ON (plant_relationship.subject_id = plant.stock_id) AND plant.type_id = ?
 194         JOIN stock_relationship AS vector_relationship ON (vector_relationship.object_id = transformation.stock_id) AND vector_relationship.type_id = ?
 195         JOIN stock AS vector ON (vector_relationship.subject_id = vector.stock_id) AND vector.type_id = ?
 196         LEFT JOIN stockprop ON (stockprop.stock_id = transformation.stock_id) AND stockprop.type_id = ?
 197         LEFT JOIN stockprop AS stockprop2 ON (stockprop2.stock_id = transformation.stock_id) AND stockprop2.type_id in (?, ?)
 198         LEFT JOIN cvterm ON (stockprop2.type_id = cvterm.cvterm_id)
 199         WHERE transformation.stock_id = ?";
 200
 201     my $h = $schema->storage->dbh()->prepare($q);
 202
 203     $h->execute($plant_material_of_type_id, $accession_type_id, $vector_construct_of_type_id, $vector_construct_type_id, $transformation_notes_type_id, $completed_metadata_type_id, $terminated_metadata_type_id, $transformation_stock_id);
 204
 205     my @transformation_info = ();
 206     while (my ($plant_id, $plant_name, $vector_id, $vector_name, $notes, $updated_status) = $h->fetchrow_array()){
 207         push @transformation_info, [$plant_id, $plant_name, $vector_id, $vector_name, $notes, $updated_status]
 208     }
 209
 210     return \@transformation_info;
 211
 212 }
 213
 214
 215 sub get_associated_projects {
 216     my $self = shift;
 217     my $schema = $self->schema();
 218     my $transformation_stock_id = $self->transformation_stock_id();
 219     my $program_relationship_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'breeding_program_trial_relationship', 'project_relationship')->cvterm_id();
 220
 221     my $q = "SELECT project.project_id, project.name, program.project_id, program.name
 222         FROM nd_experiment_stock
 223         JOIN nd_experiment_project ON (nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id)
 224         JOIN project ON (nd_experiment_project.project_id = project.project_id)
 225         JOIN project_relationship ON (project.project_id = project_relationship.subject_project_id) AND project_relationship.type_id = ?
 226         JOIN project AS program ON (project_relationship.object_project_id = program.project_id)
 227         WHERE nd_experiment_stock.stock_id = ? ";
 228
 229     my $h = $schema->storage->dbh()->prepare($q);
 230
 231     $h->execute($program_relationship_cvterm_id, $transformation_stock_id);
 232
 233     my @associated_projects = ();
 234     while (my ($project_id,  $project_name, $program_id, $program_name) = $h->fetchrow_array()){
 235         push @associated_projects, [$project_id,  $project_name, $program_id, $program_name]
 236     }
 237
 238     return \@associated_projects;
 239 }
 240
 241
 242 sub get_tracking_identifier {
 243     my $self = shift;
 244     my $schema = $self->schema();
 245     my $transformation_stock_id = $self->transformation_stock_id();
 246     my $material_of_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, "material_of", "stock_relationship")->cvterm_id();
 247
 248     my $q = "SELECT stock.stock_id, stock.uniquename
 249         FROM stock_relationship
 250         JOIN stock ON (stock_relationship.object_id = stock.stock_id) and stock_relationship.type_id = ?
 251         where stock_relationship.subject_id = ?";
 252
 253     my $h = $schema->storage->dbh()->prepare($q);
 254
 255     $h->execute($material_of_type_id, $transformation_stock_id);
 256
 257     my @tracking_identifiers = ();
 258     while (my ($stock_id,  $stock_name) = $h->fetchrow_array()){
 259         push @tracking_identifiers, [$stock_id,  $stock_name]
 260     }
 261
 262     return \@tracking_identifiers;
 263 }
 264
 265
 266 sub get_autogenerated_name_format {
 267     my $self = shift;
 268     my $schema = $self->schema();
 269     my $project_id = $self->project_id();
 270     my $format;
 271
 272     my $name_format_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'autogenerated_name_format', 'project_property')->cvterm_id();
 273     my $name_format_rs = $schema->resultset("Project::Projectprop")->find ({
 274         project_id =>  $project_id,
 275         type_id => $name_format_cvterm_id
 276     });
 277
 278     if($name_format_rs) {
 279         $format = $name_format_rs->value;
 280     }
 281
 282     return $format;
 283
 284 }
 285
 286
 287 sub get_default_plant_material {
 288     my $self = shift;
 289     my $schema = $self->schema();
 290     my $project_id = $self->project_id();
 291     my @default_plant_material = ();
 292
 293     my $default_plant_material_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'default_plant_material', 'project_property')->cvterm_id();
 294     my $accession_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id();
 295
 296     my $default_plant_material_rs = $schema->resultset("Project::Projectprop")->find ({
 297         project_id =>  $project_id,
 298         type_id => $default_plant_material_cvterm_id
 299     });
 300
 301     if($default_plant_material_rs) {
 302         my $default_plant_material_id = $default_plant_material_rs->value;
 303         my $plant_material_name = $schema->resultset("Stock::Stock")->find ({ stock_id =>  $default_plant_material_id, type_id => $accession_cvterm_id })->uniquename();
 304         push @default_plant_material, ($default_plant_material_id, $plant_material_name);
 305     }
 306
 307     return \@default_plant_material;
 308
 309 }
 310
 311
 312 ###
 313 1;
 314 ###