lib/SGN/Controller/AJAX/PhenotypesUpload.pm

   1
   2 =head1 NAME
   3
   4 SGN::Controller::AJAX::PhenotypesUpload - a REST controller class to provide the
   5 backend for uploading phenotype spreadsheets
   6
   7 =head1 DESCRIPTION
   8
   9 Uploading Phenotype Spreadsheets
  10
  11 =head1 AUTHOR
  12
  13 Jeremy Edwards <jde22@cornell.edu>
  14 Naama Menda <nm249@cornell.edu>
  15 Alex Ogbonna <aco46@cornell.edu>
  16 Nicolas Morales <nm529@cornell.edu>
  17
  18 =cut
  19
  20 package SGN::Controller::AJAX::PhenotypesUpload;
  21
  22 use Moose;
  23 use Try::Tiny;
  24 use DateTime;
  25 use File::Slurp;
  26 use File::Spec::Functions;
  27 use File::Copy;
  28 use Data::Dumper;
  29 use CXGN::Phenotypes::ParseUpload;
  30 use CXGN::Phenotypes::StorePhenotypes;
  31 use List::MoreUtils qw /any /;
  32
  33 BEGIN { extends 'Catalyst::Controller::REST' }
  34
  35 __PACKAGE__->config(
  36     default   => 'application/json',
  37     stash_key => 'rest',
  38     map       => { 'application/json' => 'JSON', 'text/html' => 'JSON' },
  39    );
  40
  41
  42 sub upload_phenotype_verify :  Path('/ajax/phenotype/upload_verify') : ActionClass('REST') { }
  43 sub upload_phenotype_verify_POST : Args(1) {
  44     my ($self, $c, $file_type) = @_;
  45     my $schema = $c->dbic_schema("Bio::Chado::Schema");
  46     my $metadata_schema = $c->dbic_schema("CXGN::Metadata::Schema");
  47     my $phenome_schema = $c->dbic_schema("CXGN::Phenome::Schema");
  48
  49     my ($success_status, $error_status, $parsed_data, $plots, $traits, $phenotype_metadata, $timestamp_included, $overwrite_values, $image_zip, $user_id) = _prep_upload($c, $file_type, $schema);
  50     if (scalar(@$error_status)>0) {
  51         $c->stash->{rest} = {success => $success_status, error => $error_status };
  52         return;
  53     }
  54
  55     my $timestamp = 0;
  56     if ($timestamp_included) {
  57         $timestamp = 1;
  58     }
  59     my $store_phenotypes = CXGN::Phenotypes::StorePhenotypes->new(
  60         bcs_schema=>$schema,
  61         metadata_schema=>$metadata_schema,
  62         phenome_schema=>$phenome_schema,
  63         user_id=>$user_id,
  64         stock_list=>$plots,
  65         trait_list=>$traits,
  66         values_hash=>$parsed_data,
  67         has_timestamps=>$timestamp,
  68         metadata_hash=>$phenotype_metadata,
  69         image_zipfile_path=>$image_zip,
  70     );
  71
  72     my $warning_status;
  73     my ($verified_warning, $verified_error) = $store_phenotypes->verify();
  74     if ($verified_error) {
  75         push @$error_status, $verified_error;
  76         $c->stash->{rest} = {success => $success_status, error => $error_status };
  77         return;
  78     }
  79     if ($verified_warning) {
  80         push @$warning_status, $verified_warning;
  81     }
  82     push @$success_status, "File data verified. Plot names and trait names are valid.";
  83
  84     $c->stash->{rest} = {success => $success_status, warning => $warning_status, error => $error_status};
  85 }
  86
  87 sub upload_phenotype_store :  Path('/ajax/phenotype/upload_store') : ActionClass('REST') { }
  88 sub upload_phenotype_store_POST : Args(1) {
  89     my ($self, $c, $file_type) = @_;
  90     my $schema = $c->dbic_schema("Bio::Chado::Schema");
  91     my $metadata_schema = $c->dbic_schema("CXGN::Metadata::Schema");
  92     my $phenome_schema = $c->dbic_schema("CXGN::Phenome::Schema");
  93
  94     my ($success_status, $error_status, $parsed_data, $plots, $traits, $phenotype_metadata, $timestamp_included, $overwrite_values, $image_zip, $user_id) = _prep_upload($c, $file_type, $schema);
  95     if (scalar(@$error_status)>0) {
  96         $c->stash->{rest} = {success => $success_status, error => $error_status };
  97         return;
  98     }
  99     my $overwrite = 0;
 100     if ($overwrite_values) {
 101         $overwrite = 1;
 102     }
 103     my $timestamp = 0;
 104     if ($timestamp_included) {
 105         $timestamp = 1;
 106     }
 107
 108     my $store_phenotypes = CXGN::Phenotypes::StorePhenotypes->new(
 109         bcs_schema=>$schema,
 110         metadata_schema=>$metadata_schema,
 111         phenome_schema=>$phenome_schema,
 112         user_id=>$user_id,
 113         stock_list=>$plots,
 114         trait_list=>$traits,
 115         values_hash=>$parsed_data,
 116         has_timestamps=>$timestamp,
 117         overwrite_values=>$overwrite,
 118         metadata_hash=>$phenotype_metadata,
 119         image_zipfile_path=>$image_zip,
 120     );
 121
 122     #upload_phenotype_store function redoes the same verification that upload_phenotype_verify does before actually uploading. maybe this should be commented out.
 123     #my ($verified_warning, $verified_error) = $store_phenotypes->verify($c,$plots,$traits, $parsed_data, $phenotype_metadata);
 124     #if ($verified_error) {
 125         #push @$error_status, $verified_error;
 126         #$c->stash->{rest} = {success => $success_status, error => $error_status };
 127         #return;
 128     #}
 129     #push @$success_status, "File data verified. Plot names and trait names are valid.";
 130
 131     my ($stored_phenotype_error, $stored_phenotype_success) = $store_phenotypes->store();
 132     if ($stored_phenotype_error) {
 133         push @$error_status, $stored_phenotype_error;
 134         $c->stash->{rest} = {success => $success_status, error => $error_status};
 135         return;
 136     }
 137     if ($stored_phenotype_success) {
 138         push @$success_status, $stored_phenotype_success;
 139     }
 140
 141     if ($image_zip) {
 142         my $image = SGN::Image->new( $c->dbc->dbh, undef, $c );
 143         my $image_error = $image->upload_fieldbook_zipfile($image_zip, $user_id);
 144         if ($image_error) {
 145             push @$error_status, $image_error;
 146         }
 147     }
 148
 149     push @$success_status, "Metadata saved for archived file.";
 150
 151     $c->stash->{rest} = {success => $success_status, error => $error_status};
 152 }
 153
 154 sub _prep_upload {
 155     my ($c, $file_type, $schema) = @_;
 156         my @success_status;
 157         my @error_status;
 158
 159         my $user = $c->user();
 160         if (!$user) {
 161                 push @error_status, 'Must be logged in to upload phenotypes!';
 162                 return (\@success_status, \@error_status);
 163         }
 164
 165     my $user_id = $c->can('user_exists') ? $c->user->get_object->get_sp_person_id : $c->sp_person_id;
 166     my $parser = CXGN::Phenotypes::ParseUpload->new();
 167     my $timestamp_included;
 168     my $upload;
 169     my $subdirectory;
 170     my $validate_type;
 171     my $metadata_file_type;
 172     my $data_level;
 173     my $image_zip;
 174     if ($file_type eq "spreadsheet") {
 175         print STDERR "Spreadsheet \n";
 176         my $spreadsheet_format = $c->req->param('upload_spreadsheet_phenotype_file_format'); #simple or detailed
 177         if ($spreadsheet_format eq 'detailed'){
 178             $validate_type = "phenotype spreadsheet";
 179         }
 180         if ($spreadsheet_format eq 'simple'){
 181             $validate_type = "phenotype spreadsheet simple";
 182         }
 183         $subdirectory = "spreadsheet_phenotype_upload";
 184         $metadata_file_type = "spreadsheet phenotype file";
 185         $timestamp_included = $c->req->param('upload_spreadsheet_phenotype_timestamp_checkbox');
 186         $data_level = $c->req->param('upload_spreadsheet_phenotype_data_level') || 'plots';
 187         $upload = $c->req->upload('upload_spreadsheet_phenotype_file_input');
 188     }
 189     elsif ($file_type eq "fieldbook") {
 190         print STDERR "Fieldbook \n";
 191         $subdirectory = "tablet_phenotype_upload";
 192         $validate_type = "field book";
 193         $metadata_file_type = "tablet phenotype file";
 194         $timestamp_included = 1;
 195         $upload = $c->req->upload('upload_fieldbook_phenotype_file_input');
 196         $image_zip = $c->req->upload('upload_fieldbook_phenotype_images_zipfile');
 197         $data_level = $c->req->param('upload_fieldbook_phenotype_data_level') || 'plots';
 198     }
 199     elsif ($file_type eq "datacollector") {
 200         print STDERR "Datacollector \n";
 201         $subdirectory = "data_collector_phenotype_upload";
 202         $validate_type = "datacollector spreadsheet";
 203         $metadata_file_type = "data collector phenotype file";
 204         $timestamp_included = $c->req->param('upload_datacollector_phenotype_timestamp_checkbox');
 205         $upload = $c->req->upload('upload_datacollector_phenotype_file_input');
 206     }
 207
 208     my $user_type = $user->get_object->get_user_type();
 209     if ($user_type ne 'submitter' && $user_type ne 'curator') {
 210         push @error_status, 'Must have submitter privileges to upload phenotypes! Please contact us!';
 211         return (\@success_status, \@error_status);
 212     }
 213
 214     my $overwrite_values = $c->req->param('phenotype_upload_overwrite_values');
 215     if ($overwrite_values) {
 216         #print STDERR $user_type."\n";
 217         if ($user_type ne 'curator') {
 218             push @error_status, 'Must be a curator to overwrite values! Please contact us!';
 219             return (\@success_status, \@error_status);
 220         }
 221     }
 222
 223     my $upload_original_name = $upload->filename();
 224     my $upload_tempfile = $upload->tempname;
 225     my %phenotype_metadata;
 226     my $time = DateTime->now();
 227     my $timestamp = $time->ymd()."_".$time->hms();
 228
 229     my $uploader = CXGN::UploadFile->new({
 230         tempfile => $upload_tempfile,
 231         subdirectory => $subdirectory,
 232         archive_path => $c->config->{archive_path},
 233         archive_filename => $upload_original_name,
 234         timestamp => $timestamp,
 235         user_id => $user_id,
 236         user_role => $user_type
 237     });
 238     my $archived_filename_with_path = $uploader->archive();
 239     my $md5 = $uploader->get_md5($archived_filename_with_path);
 240     if (!$archived_filename_with_path) {
 241         push @error_status, "Could not save file $upload_original_name in archive.";
 242         return (\@success_status, \@error_status);
 243     } else {
 244         push @success_status, "File $upload_original_name saved in archive.";
 245     }
 246     unlink $upload_tempfile;
 247     #print STDERR "Archived Phenotype File: $archived_filename_with_path\n";
 248
 249     my $archived_image_zipfile_with_path;
 250     if ($image_zip) {
 251         my $upload_original_name = $image_zip->filename();
 252         my $upload_tempfile = $image_zip->tempname;
 253         my $uploader = CXGN::UploadFile->new({
 254             tempfile => $upload_tempfile,
 255             subdirectory => $subdirectory."_images",
 256             archive_path => $c->config->{archive_path},
 257             archive_filename => $upload_original_name,
 258             timestamp => $timestamp,
 259             user_id => $user_id,
 260             user_role => $user_type
 261         });
 262         $archived_image_zipfile_with_path = $uploader->archive();
 263         my $md5 = $uploader->get_md5($archived_image_zipfile_with_path);
 264         if (!$archived_image_zipfile_with_path) {
 265             push @error_status, "Could not save images zipfile $upload_original_name in archive.";
 266             return (\@success_status, \@error_status);
 267         } else {
 268             push @success_status, "Images Zip File $upload_original_name saved in archive.";
 269         }
 270         unlink $upload_tempfile;
 271         #print STDERR "Archived Zipfile: $archived_image_zipfile_with_path\n";
 272     }
 273
 274     ## Validate and parse uploaded file
 275     my $validate_file = $parser->validate($validate_type, $archived_filename_with_path, $timestamp_included, $data_level, $schema);
 276     if (!$validate_file) {
 277         push @error_status, "Archived file not valid: $upload_original_name.";
 278         return (\@success_status, \@error_status);
 279     }
 280     if ($validate_file == 1){
 281         push @success_status, "File valid: $upload_original_name.";
 282     } else {
 283         if ($validate_file->{'error'}) {
 284             push @error_status, $validate_file->{'error'};
 285         }
 286         return (\@success_status, \@error_status);
 287     }
 288
 289     ## Set metadata
 290     $phenotype_metadata{'archived_file'} = $archived_filename_with_path;
 291     $phenotype_metadata{'archived_file_type'} = $metadata_file_type;
 292     my $operator = $user->get_object()->get_username();
 293     $phenotype_metadata{'operator'} = $operator;
 294     $phenotype_metadata{'date'} = $timestamp;
 295
 296     my $parsed_file = $parser->parse($validate_type, $archived_filename_with_path, $timestamp_included, $data_level, $schema);
 297     if (!$parsed_file) {
 298         push @error_status, "Error parsing file $upload_original_name.";
 299         return (\@success_status, \@error_status);
 300     }
 301     if ($parsed_file->{'error'}) {
 302         push @error_status, $parsed_file->{'error'};
 303     }
 304     my %parsed_data;
 305     my @plots;
 306     my @traits;
 307     if (scalar(@error_status) == 0) {
 308         if ($parsed_file && !$parsed_file->{'error'}) {
 309             %parsed_data = %{$parsed_file->{'data'}};
 310             @plots = @{$parsed_file->{'plots'}};
 311             @traits = @{$parsed_file->{'traits'}};
 312             push @success_status, "File data successfully parsed.";
 313         }
 314     }
 315
 316     return (\@success_status, \@error_status, \%parsed_data, \@plots, \@traits, \%phenotype_metadata, $timestamp_included, $overwrite_values, $archived_image_zipfile_with_path, $user_id);
 317 }
 318
 319 sub update_plot_phenotype :  Path('/ajax/phenotype/plot_phenotype_upload') : ActionClass('REST') { }
 320 sub update_plot_phenotype_POST : Args(0) {
 321   my $self = shift;
 322   my $c = shift;
 323   my $plot_name = $c->req->param("plot_name");
 324   my $trait_id = $c->req->param("trait");
 325   my $trait_value = $c->req->param("trait_value");
 326   my $trait_list_option = $c->req->param("trait_list_option");
 327   my $time = DateTime->now();
 328   my $timestamp = $time->ymd()."_".$time->hms();
 329   my $dbh = $c->dbc->dbh();
 330   my $schema = $c->dbic_schema("Bio::Chado::Schema");
 331   my $metadata_schema = $c->dbic_schema("CXGN::Metadata::Schema");
 332   my $phenome_schema = $c->dbic_schema("CXGN::Phenome::Schema");
 333   my (@plots, @traits, %data, $trait);
 334   my $accession_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type' )->cvterm_id();
 335   print "MY LIST OPTION:  $trait_list_option\n";
 336   my $plot = $schema->resultset("Stock::Stock")->find( { uniquename=>$plot_name });
 337   my $plot_type_id = $plot->type_id();
 338
 339   if (!$c->user()) {
 340     print STDERR "User not logged in... not uploading phenotype.\n";
 341     $c->stash->{rest} = {error => "You need to be logged in to upload phenotype." };
 342     return;
 343   }
 344   if (!any { $_ eq "curator" || $_ eq "submitter" } ($c->user()->roles)  ) {
 345     $c->stash->{rest} = {error =>  "You have insufficient privileges to upload phenotype." };
 346     return;
 347   }
 348
 349   if ($plot_type_id == $accession_cvterm_id) {
 350     print "You are using accessions\n";
 351     $c->stash->{rest} = {error => "Used only for Plot Phenotyping."};
 352     return;
 353   }
 354
 355   if (!$trait_list_option){
 356       my $h = $dbh->prepare("SELECT cvterm.cvterm_id AS trait_id, (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name FROM cvterm JOIN dbxref ON cvterm.dbxref_id = dbxref.dbxref_id JOIN db ON dbxref.db_id = db.db_id WHERE db.db_id = (( SELECT dbxref_1.db_id FROM stock JOIN nd_experiment_stock USING (stock_id) JOIN nd_experiment_phenotype USING (nd_experiment_id) JOIN phenotype USING (phenotype_id) JOIN cvterm cvterm_1 ON phenotype.cvalue_id = cvterm_1.cvterm_id JOIN dbxref dbxref_1 ON cvterm_1.dbxref_id = dbxref_1.dbxref_id LIMIT 1)) AND cvterm_id =? GROUP BY cvterm.cvterm_id, ((((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text);");
 357       $h->execute($trait_id);
 358       while (my ($id, $trait_name) = $h->fetchrow_array()) {
 359         $trait = $trait_name;
 360       }
 361   }
 362   else {
 363       $trait = $trait_id;
 364   }
 365   push @plots, $plot_name;
 366   push @traits, $trait;
 367
 368   $data{$plot_name}->{$trait} = [$trait_value,$timestamp];
 369
 370   my %phenotype_metadata;
 371   $phenotype_metadata{'archived_file'} = 'none';
 372   $phenotype_metadata{'archived_file_type'}="direct phenotyping";
 373   $phenotype_metadata{'operator'}=$c->user()->get_object()->get_sp_person_id();
 374   $phenotype_metadata{'date'}="$timestamp";
 375   my $user_id = $c->can('user_exists') ? $c->user->get_object->get_sp_person_id : $c->sp_person_id;
 376
 377   my $store_phenotypes = CXGN::Phenotypes::StorePhenotypes->new(
 378       bcs_schema=>$schema,
 379       metadata_schema=>$metadata_schema,
 380       phenome_schema=>$phenome_schema,
 381       user_id=>$user_id,
 382       stock_list=>\@plots,
 383       trait_list=>\@traits,
 384       values_hash=>\%data,
 385       has_timestamps=> 1,
 386       overwrite_values=> 1,
 387       metadata_hash=>\%phenotype_metadata,
 388   );
 389
 390   my ($verified_warning, $verified_error) = $store_phenotypes->verify();
 391   if ($verified_error){
 392     $c->stash->{rest} = {error => $verified_error};
 393     $c->detach;
 394   }
 395
 396   my ($store_error, $store_success) = $store_phenotypes->store();
 397   if ($store_error) {
 398       $c->stash->{rest} = {error => $store_error};
 399       $c->detach;
 400   }
 401
 402   $c->stash->{rest} = {success => 1};
 403 }
 404
 405 sub retrieve_plot_phenotype :  Path('/ajax/phenotype/plot_phenotype_retrieve') : ActionClass('REST') { }
 406 sub retrieve_plot_phenotype_POST : Args(0) {
 407   my $self = shift;
 408   my $c = shift;
 409   my $dbh = $c->dbc->dbh();
 410   my $schema = $c->dbic_schema("Bio::Chado::Schema");
 411   my $plot_name = $c->req->param("plot_name");
 412   my $trait_id = $c->req->param("trait");
 413   my $trait_list_option = $c->req->param("trait_list_option");
 414   my $trait_value;
 415   my $stock = $schema->resultset("Stock::Stock")->find( { uniquename=>$plot_name });
 416   my $stock_id = $stock->stock_id();
 417
 418   if ($trait_list_option){
 419       my $h = $dbh->prepare("SELECT cvterm.cvterm_id AS trait_id, (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name FROM cvterm JOIN dbxref ON cvterm.dbxref_id = dbxref.dbxref_id JOIN db ON dbxref.db_id = db.db_id WHERE db.db_id = (( SELECT dbxref_1.db_id FROM stock JOIN nd_experiment_stock USING (stock_id) JOIN nd_experiment_phenotype USING (nd_experiment_id) JOIN phenotype USING (phenotype_id) JOIN cvterm cvterm_1 ON phenotype.cvalue_id = cvterm_1.cvterm_id JOIN dbxref dbxref_1 ON cvterm_1.dbxref_id = dbxref_1.dbxref_id LIMIT 1)) AND (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text =? GROUP BY cvterm.cvterm_id, ((((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text);");
 420       $h->execute($trait_id);
 421       while (my ($id, $trait_name) = $h->fetchrow_array()) {
 422         $trait_id = $id;
 423       }
 424   }
 425
 426   my $h = $dbh->prepare("SELECT phenotype.value FROM stock
 427     JOIN nd_experiment_stock USING(stock_id)
 428     JOIN nd_experiment_phenotype USING(nd_experiment_id)
 429     JOIN phenotype USING(phenotype_id)
 430     WHERE cvalue_id =? and stock_id=?;"
 431     );
 432   $h->execute($trait_id,$stock_id);
 433   while (my ($plot_value) = $h->fetchrow_array()) {
 434     $trait_value = $plot_value;
 435   }
 436
 437   $c->stash->{rest} = {trait_value => $trait_value};
 438
 439 }
 440
 441 #########
 442 1;
 443 #########