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