lib/SGN/Controller/FieldBook.pm

   1
   2 package SGN::Controller::FieldBook;
   3
   4 use Moose;
   5 use URI::FromHash 'uri';
   6 use Spreadsheet::WriteExcel;
   7 use File::Slurp qw | read_file |;
   8 use File::Temp;
   9 use Data::Dumper;
  10 use CXGN::Trial::TrialLayout;
  11 use Try::Tiny;
  12 use File::Basename qw | basename dirname|;
  13 use File::Spec::Functions;
  14 use CXGN::BreedersToolbox::Projects;
  15 use SGN::Model::Cvterm;
  16
  17 BEGIN { extends 'Catalyst::Controller'; }
  18
  19 sub field_book :Path("/fieldbook") Args(0) {
  20     my ($self , $c) = @_;
  21
  22     if (!$c->user()) {
  23         # redirect to login page
  24         $c->res->redirect( uri( path => '/user/login', query => { goto_url => $c->req->uri->path_query } ) );
  25         return;
  26     }
  27     my $user_id = $c->user()->get_object()->get_sp_person_id();
  28
  29     my $metadata_schema = $c->dbic_schema('CXGN::Metadata::Schema', undef, $user_id);
  30     my $phenome_schema = $c->dbic_schema('CXGN::Phenome::Schema', undef, $user_id);
  31     my $schema = $c->dbic_schema('Bio::Chado::Schema', 'sgn_chado', $user_id);
  32     my @rows = $schema->resultset('Project::Project')->all();
  33     #limit to owner
  34     my @projects = ();
  35     my @file_metadata = ();
  36     my $bp = CXGN::BreedersToolbox::Projects->new( { schema=>$schema });
  37     my $breeding_programs = $bp->get_breeding_programs();
  38     my @layout_files = ();
  39     my @phenotype_files = ();
  40     my @removed_phenotype_files = ();
  41
  42
  43     my $field_layout_cvterm = SGN::Model::Cvterm->get_cvterm_row($schema, 'field_layout' , 'experiment_type' ) ;
  44
  45 #    foreach my $row (@rows) {
  46     #   my $experiment_rs = $schema->resultset('NaturalDiversity::NdExperiment')->search({
  47     #                                                  #                                           'nd_experiment_projects.project_id' => $row->project_id,
  48     #                                                                              type_id => $field_layout_cvterm->cvterm_id(),
  49     #                                                                             },
  50     #                                                                             {
  51     #                                                                              join => 'nd_experiment_projects',
  52     #                                                                             });
  53     # while (my $experiment = $experiment_rs->next()) {
  54     #  my $experiment_files = $phenome_schema->resultset("NdExperimentMdFiles")->search({nd_experiment_id => $experiment->nd_experiment_id(),});
  55
  56     my $q = "SELECT md_files.file_id, metadata.md_files.basename, metadata.md_files.dirname, metadata.md_files.filetype, metadata.md_files.comment, md_metadata.metadata_id FROM nd_experiment_project JOIN nd_experiment USING(nd_experiment_id)  JOIN  phenome.nd_experiment_md_files ON (nd_experiment.nd_experiment_id=nd_experiment_md_files.nd_experiment_id) JOIN metadata.md_files USING (file_id) LEFT JOIN metadata.md_metadata USING(metadata_id) WHERE nd_experiment.type_id=".$field_layout_cvterm->cvterm_id()." and metadata.md_metadata.create_person_id=$user_id and filetype = 'tablet field layout xls'";
  57     my $h = $c->dbc->dbh->prepare($q);
  58     $h->execute();
  59 #      while (my $experiment_file = $experiment_files->next) {
  60     while (my ($file_id, $basename, $dirname, $filetype, $comment, $metadata_id) = $h->fetchrow_array()) {
  61         #my $file_row = $metadata_schema->resultset("MdFiles")->find({file_id => $experiment_file->file_id});
  62         #if ($filetype eq 'tablet field layout xls') {
  63
  64 #         my $metadata_id = $file_row->metadata_id->metadata_id;
  65           if ($metadata_id) {
  66
  67          #   my $file_metadata = $metadata_schema->resultset("MdMetadata")->find({metadata_id => $metadata_id});
  68           #  if ( $file_metadata->create_person_id() eq $user_id) {
  69                 #my $file_destination =  catfile($file_row->dirname, $file_row->basename);
  70                 my $file_destination =  catfile($dirname, $basename);
  71               #push @projects, [ $row->project_id, $row->name, $row->description, $file_row->dirname,$file_row->basename, $file_row->file_id];
  72               push @file_metadata, [ $dirname, $basename, $file_id, $comment ] ;
  73               push @layout_files, $file_destination;
  74           #  }
  75           #}
  76         }
  77     }
  78    # }
  79    #}
  80
  81     my @trait_files = ();
  82     #limit to those owned by user
  83     my $md_files = $metadata_schema->resultset("MdFiles")->search({filetype=>'tablet trait file'});
  84     while (my $md_file = $md_files->next) {
  85       my $metadata_id = $md_file->metadata_id->metadata_id;
  86       my $file_metadata = $metadata_schema->resultset("MdMetadata")->find({metadata_id => $metadata_id});
  87       if ( $file_metadata->create_person_id() eq $user_id) {
  88         push @trait_files, [$md_file->basename,$md_file->file_id];
  89       }
  90     }
  91
  92     my $uploaded_md_files = $metadata_schema->resultset("MdFiles")->search({filetype=>'tablet phenotype file'});
  93     while (my $md_file = $uploaded_md_files->next) {
  94         my $metadata_id = $md_file->metadata_id->metadata_id;
  95         my $file_metadata = $metadata_schema->resultset("MdMetadata")->find({metadata_id => $metadata_id });
  96         if ( ($file_metadata->obsolete==0) && ($file_metadata->create_person_id() eq $user_id)) {
  97             push @phenotype_files, [$md_file->basename,$md_file->file_id];
  98         }
  99         elsif ( ($file_metadata->obsolete==1) && ($file_metadata->create_person_id() eq $user_id)) {
 100           push @removed_phenotype_files, [$md_file->basename, $md_file->file_id];
 101         }
 102     }
 103
 104     $c->stash->{projects} = \@projects;
 105     $c->stash->{file_metadata} = \@file_metadata;
 106     $c->stash->{programs} = $breeding_programs;
 107     $c->stash->{layout_files} = \@projects;
 108     $c->stash->{trait_files} = \@trait_files;
 109     $c->stash->{phenotype_files} = \@phenotype_files;
 110     $c->stash->{removed_phenotype_files} = \@removed_phenotype_files;
 111
 112     # get roles
 113     my @roles = $c->user->roles();
 114     $c->stash->{roles}=\@roles;
 115     $c->stash->{template} = '/fieldbook/home.mas';
 116 }
 117
 118
 119 sub trial_field_book_download : Path('/fieldbook/trial_download/') Args(1) {
 120     my $self  =shift;
 121     my $c = shift;
 122     my $file_id = shift;
 123     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
 124     my $metadata_schema = $c->dbic_schema('CXGN::Metadata::Schema', undef, $sp_person_id);
 125     my $file_row = $metadata_schema->resultset("MdFiles")->find({file_id => $file_id});
 126     my $file_destination =  catfile($file_row->dirname, $file_row->basename);
 127     print STDERR "\n\n\nfile name:".$file_row->basename."\n";
 128     my $contents = read_file($file_destination);
 129     my $file_name = $file_row->basename;
 130     $c->res->content_type('Application/xls');
 131     $c->res->header('Content-Disposition', qq[attachment; filename="$file_name"]);
 132     $c->res->body($contents);
 133 }
 134
 135 sub tablet_trait_file_download : Path('/fieldbook/trait_file_download/') Args(1) {
 136     my $self  =shift;
 137     my $c = shift;
 138     my $file_id = shift;
 139     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
 140     my $metadata_schema = $c->dbic_schema('CXGN::Metadata::Schema', undef, $sp_person_id);
 141     my $file_row = $metadata_schema->resultset("MdFiles")->find({file_id => $file_id});
 142     my $file_destination =  catfile($file_row->dirname, $file_row->basename);
 143     print STDERR "\n\n\nfile name:".$file_row->basename."\n";
 144     my $contents = read_file($file_destination);
 145     my $file_name = $file_row->basename;
 146
 147     $c->res->content_type('Application/trt');
 148     $c->res->header('Content-Disposition', qq[attachment; filename="$file_name"]);
 149     $c->res->body($contents);
 150 }
 151
 152 sub trial_field_book_download_old : Path('/fieldbook/trial_download_old/') Args(1) {
 153     my $self  =shift;
 154     my $c = shift;
 155     my $trial_id = shift;
 156     die "No trial id supplied" if !$trial_id;
 157     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
 158     my $schema = $c->dbic_schema('Bio::Chado::Schema', 'sgn_chado', $sp_person_id);
 159     my $trial = $schema->resultset('Project::Project')->find({project_id => $trial_id});
 160     die "Trial does not exist with id $trial_id" if !$trial;
 161     my $dir = $c->tempfiles_subdir('/other');
 162     my $tempfile = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'other/excelXXXX');
 163     my $wb = Spreadsheet::WriteExcel->new($tempfile);
 164     die "Could not create excel file " if !$wb;
 165     my $ws = $wb->add_worksheet();
 166     my $trial_layout = CXGN::Trial::TrialLayout->new({schema => $schema, trial_id => $trial_id, experiment_type => 'field_layout' });
 167     my $trial_name =  $trial_layout->get_trial_name();
 168
 169     $ws->write(0, 0, 'plot_id');
 170     $ws->write(0, 1, 'range');
 171     $ws->write(0, 2, 'plot');
 172     $ws->write(0, 3, 'rep');
 173     $ws->write(0, 4, 'accession');
 174     $ws->write(0, 5, 'is_a_control');
 175
 176     my %design = %{$trial_layout->get_design()};
 177     my $row_num = 1;
 178     foreach my $key (sort { $a <=> $b} keys %design) {
 179       my %design_info = %{$design{$key}};
 180       $ws->write($row_num,0,$design_info{'plot_name'});
 181       $ws->write($row_num,1,$design_info{'block_number'});
 182       $ws->write($row_num,2,$design_info{'plot_number'});
 183       $ws->write($row_num,3,$design_info{'rep_number'});
 184       $ws->write($row_num,4,$design_info{'accession_name'});
 185       $ws->write($row_num,5,$design_info{'is_a_control'});
 186       $row_num++;
 187     }
 188     $wb->close();
 189     my $contents = read_file($tempfile);
 190     $c->res->content_type('Application/xls');
 191     $c->res->header('Content-Disposition', qq[attachment; filename="fieldbook_layout_$trial_name.xls"]);
 192     $c->res->body($contents);
 193 }
 194
 195 sub delete_file : Path('/fieldbook/delete_file/') Args(1) {
 196      my $self  =shift;
 197      my $c = shift;
 198      my $json = new JSON;
 199      my $file_id = shift;
 200      my $decoded;
 201      if ($file_id){
 202                  $decoded = $json->allow_nonref->utf8->decode($file_id);
 203      }
 204         #print STDERR Dumper($file_id);
 205         print "File ID: $file_id\n";
 206      my $dbh = $c->dbc->dbh();
 207      my $h_nd_exp_md_files = $dbh->prepare("delete from phenome.nd_experiment_md_files where file_id=?;");
 208      $h_nd_exp_md_files->execute($decoded);
 209
 210      my $h_md_files = $dbh->prepare("delete from metadata.md_files where file_id=?;");
 211      $h_md_files->execute($decoded);
 212      print STDERR "File successfully deleted.\n";
 213         $c->response->redirect('/fieldbook');
 214 }
 215
 216 1;