lib/CXGN/Phenotypes/CreateSpreadsheet.pm

   1 package CXGN::Phenotypes::CreateSpreadsheet;
   2
   3 =head1 NAME
   4
   5 CXGN::Phenotypes::CreateSpreadsheet - an object to create a spreadsheet for collecting phenotypes
   6
   7 =head1 USAGE
   8
   9  my $phenotype_spreadsheet = CXGN::Phenotypes::CreateSpreadsheet->new({schema => $schema, trial_id => $trial_id, trait_list => \$trait_list} );
  10  $create_spreadsheet->create();
  11
  12 =head1 DESCRIPTION
  13
  14
  15 =head1 AUTHORS
  16
  17  Jeremy D. Edwards (jde22@cornell.edu)
  18
  19 =cut
  20
  21 use strict;
  22 use warnings;
  23 use Moose;
  24 use MooseX::FollowPBP;
  25 use Moose::Util::TypeConstraints;
  26 use Try::Tiny;
  27 use File::Basename qw | basename dirname|;
  28 use Digest::MD5;
  29 use CXGN::List::Validate;
  30 use Data::Dumper;
  31 use CXGN::Trial::TrialLayout;
  32 use Spreadsheet::WriteExcel;
  33 use CXGN::Trait;
  34 use CXGN::List::Transform;
  35
  36 has 'schema' => (
  37                  is       => 'rw',
  38                  isa      => 'DBIx::Class::Schema',
  39                  required => 1,
  40                 );
  41 has 'trial_id' => (isa => 'Int', is => 'rw', predicate => 'has_trial_id', required => 1);
  42 has 'trait_list' => (isa => 'ArrayRef', is => 'rw', predicate => 'has_trait_list', required => 1);
  43 has 'filename' => (isa => 'Str', is => 'ro',
  44                    predicate => 'has_filename',
  45                    reader => 'get_filename',
  46                    writer => 'set_filename',
  47                    required => 1,
  48                   );
  49 has 'file_metadata' => (isa => 'Str', is => 'rw', predicate => 'has_file_metadata');
  50
  51
  52 sub _verify {
  53     my $self = shift;
  54
  55     my $trial_id = $self->get_trial_id();
  56     my @trait_list = @{$self->get_trait_list()};
  57
  58     return 1;
  59 }
  60
  61
  62 sub create {
  63     my $self = shift;
  64     my $schema = $self->get_schema();
  65     my $trial_id = $self->get_trial_id();
  66     my @trait_list = @{$self->get_trait_list()};
  67     my $spreadsheet_metadata = $self->get_file_metadata();
  68     my $trial_layout = CXGN::Trial::TrialLayout->new({schema => $schema, trial_id => $trial_id, experiment_type=>'field_layout'} );
  69     my %design = %{$trial_layout->get_design()};
  70     my @plot_names = @{$trial_layout->get_plot_names};
  71
  72     my $workbook = Spreadsheet::WriteExcel->new($self->get_filename());
  73     my $ws = $workbook->add_worksheet();
  74
  75     # generate worksheet headers
  76     #
  77     my $trial = CXGN::Trial->new( { bcs_schema => $schema, trial_id => $trial_id });
  78     #$ws->write(0, '0', 'Spreadsheet ID'); $ws->write('0', '1', );#$unique_id);
  79     $ws->write(1, 0, 'Trial name'); $ws->write(1, 1, $trial->get_name());
  80     $ws->write(2, 0, 'Description'); $ws->write(2, 1, $trial->get_description());
  81     #$ws->write(3, 0, "Plants per plot");  $ws->write(3, 1, "unknown");
  82     $ws->write(4, 0, 'Operator');       $ws->write(4, '1', "");
  83     $ws->write(5, 0, 'Date');           $ws->write(5, '1', "");
  84
  85     my @ordered_plots = sort { $a cmp $b} keys(%design);
  86     for(my $n=0; $n<@ordered_plots; $n++) {
  87         my %design_info = %{$design{$ordered_plots[$n]}};
  88
  89         $ws->write($n+6, 0, $ordered_plots[$n]);
  90         $ws->write($n+6, 1, $design_info{accession_name});
  91         $ws->write($n+6, 2, $design_info{plot_number});
  92         $ws->write($n+6, 3, $design_info{block_number});
  93         $ws->write($n+6, 4, $design_info{is_a_control});
  94         $ws->write($n+6, 5, $design_info{rep_number});
  95     }
  96
  97     # write traits and format trait columns
  98     #
  99     my $lt = CXGN::List::Transform->new();
 100
 101     my $transform = $lt->transform($schema, "traits_2_trait_ids", \@trait_list);
 102
 103     if (@{$transform->{missing}}>0) {
 104         print STDERR "Warning: Some traits could not be found. ".join(",",@{$transform->{missing}})."\n";
 105     }
 106     my @trait_ids = @{$transform->{transform}};
 107
 108     my %cvinfo = ();
 109     foreach my $t (@trait_ids) {
 110         my $trait = CXGN::Trait->new( { bcs_schema=> $schema, cvterm_id => $t });
 111         $cvinfo{$trait->display_name()} = $trait;
 112     }
 113
 114     for (my $i = 0; $i < @trait_list; $i++) {
 115         if (exists($cvinfo{$trait_list[$i]})) {
 116             $ws->write(6, $i+6, $cvinfo{$trait_list[$i]}->display_name());
 117         }
 118         else {
 119             print STDERR "Skipping output of trait $trait_list[$i] because it does not exist\n";
 120         }
 121
 122         my $plot_count = scalar(keys(%design));
 123
 124         for (my $n = 0; $n < $plot_count; $n++) {
 125             my $format = $cvinfo{$trait_list[$i]}->format();
 126             if ($format eq "numeric") {
 127                 $ws->data_validation($n+6, $i+6, { validate => "any" });
 128             }
 129             else {
 130                 $ws->data_validation($n+6, $i+6,
 131                                      {
 132                                          validate => 'list',
 133                                          value    => [ split ",", $format ]
 134                                      });
 135             }
 136         }
 137     }
 138
 139     $workbook->close();
 140
 141     return 1;
 142 }
 143
 144
 145
 146 ###
 147 1;
 148 ###