lib/SGN/Controller/ExcelScoreSheet.pm

   1
   2 use strict;
   3
   4 package SGN::Controller::ExcelScoreSheet;
   5
   6 use Moose;
   7 use Spreadsheet::WriteExcel;
   8 use File::Slurp qw | read_file |;
   9 use File::Temp;
  10 use Data::Dumper;
  11 use CXGN::Trial::TrialLayout;
  12
  13 BEGIN { extends 'Catalyst::Controller'; }
  14
  15
  16 sub excel_download_trial : Path('/barcode/trial_excel_download') Args(1) {
  17     my $self = shift;
  18     my $c = shift;
  19     my $trial_id=shift;
  20
  21     my $tl = CXGN::Trial::TrialLayout->new( { schema => $c->dbic_schema("Bio::Chado::Schema"), trial_id => $trial_id, experiment_type => 'field_layout' });
  22
  23     my $trial_data = $tl->get_design();
  24
  25     print STDERR Data::Dumper::Dumper($trial_data);
  26
  27
  28
  29
  30
  31 }
  32
  33
  34 sub excel_download : Path('/barcode/excel_download/') Args(0) {
  35     my $self  =shift;
  36     my $c = shift;
  37
  38     my $operator = $c->req->param('operator');
  39     my $date     = $c->req->param('date');
  40     my $project  = $c->req->param('project');
  41     my $location = $c->req->param('location');
  42     my $lines    = $c->req->param('lines');
  43     my @cvterms = $c->req->param('cvterms');
  44         print "MY PROJECT: $operator , $date ,  $location , $lines , my @cvterms\n";
  45     my $schema = $c->dbic_schema("Bio::Chado::Schema");
  46
  47     my $project_row = $schema->resultset("Project::Project")->find({ name => $project});
  48     my $project_name = $project_row->name();
  49     my $project_desc = $project_row->description();
  50
  51
  52     $lines =~ s/\r//g;
  53     my @lines = split /\n/, $lines;
  54
  55     my $plot_of = $schema->resultset("Cv::Cvterm")->find( { name => "plot_of" })->cvterm_id();
  56     print STDERR "PLOT_OF: $plot_of\n";
  57
  58     my $plot_type = $schema->resultset("Cv::Cvterm")->find( { name=>"plot"})->cvterm_id();
  59
  60
  61     # check if all lines exist and bail out if not
  62     my @missing = ();
  63     my @not_plots = ();
  64     my @stock_rows = ();
  65     foreach my $s (@lines) {
  66         my $stock_row = $schema->resultset("Stock::Stock")->find( { name=>$s });
  67         if (!$stock_row) {
  68             print STDERR "Missing: $s\n";
  69             push @missing, $s;
  70         }
  71         elsif ($stock_row->type_id != $plot_type) {
  72             push @not_plots, $s;
  73         }
  74         else {
  75             push @stock_rows, $stock_row;
  76         }
  77     }
  78
  79     if (@missing > 0 || @not_plots > 0) {
  80         $c->res->body("The following plots could not be found in the database or are not of type stocks: ".(join(",", @missing)).", ".(join(",", @not_plots))."<br />Please correct the problem and try again.");
  81         return;
  82     }
  83
  84     my $cvterm_data = [];
  85     my @tools_def = read_file($c->path_to("../cassava/documents/barcode/tools.def"));
  86
  87     my %cvinfo;
  88     for (my $i=0; $i<(@tools_def); $i++) {
  89         chomp($tools_def[$i]);
  90         my ($id, $version, $priority, $values, $name) = split /\t/, $tools_def[$i];
  91         $cvinfo{$id} = { version => $version,
  92                          priorty => $priority,
  93                          values => $values,
  94                          name => $name,
  95         };
  96     }
  97
  98 #    print Data::Dumper::Dumper(\%cvinfo);
  99
 100     my $dir = $c->tempfiles_subdir('/other');
 101     my $tempfile = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'other/excelXXXX');
 102     print STDERR "TEMPFILE: $tempfile\n";
 103     my $wb = Spreadsheet::WriteExcel->new($tempfile);
 104     die "Could not create excel file " if !$wb;
 105
 106     my $ws = $wb->add_worksheet();
 107
 108     my $bold = $wb->add_format();
 109     $bold->set_bold();
 110
 111     my $unique_id = time().$$;
 112     $ws->write(0, '0', 'Spreadsheet ID'); $ws->write('0', '1', $unique_id);
 113     $ws->write(1,'0', 'Trial name');      $ws->write(1, 1, $project_name);
 114     $ws->write(2, 0, $project_desc);
 115     $ws->write(3, 0, "Plants per plot");  $ws->write(3, 1, "PLANTS PER PLOT");
 116     $ws->write(4, '0', 'Operator');       $ws->write(4, '1', $operator, $bold);
 117     $ws->write(5, '0', 'Date');           $ws->write(5, '1', $date, $bold);
 118
 119     for (my $i=0; $i<@cvterms; $i++) {
 120         $ws->write(6, $i+6, $cvinfo{$cvterms[$i]}->{name});
 121     }
 122
 123
 124     for (my $n=0; $n<@lines;  $n++) {
 125         my $parent_id = $schema->resultset("Stock::StockRelationship")->find( { subject_id=>$stock_rows[$n]->stock_id(), type_id=>$plot_of })->object_id();
 126
 127         my $parent = $schema->resultset("Stock::Stock")->find( { stock_id => $parent_id })->name();
 128
 129         my $prop_rs = $schema->resultset("Stock::Stockprop")->search( { stock_id=>$lines[$n] } );
 130         my %props = ();
 131
 132         while (my $prop_rs->next()) {
 133             $props{$prop_rs->type->name()} = $prop_rs->value();
 134
 135         }
 136
 137         my $row_number = $props{row_number};
 138         my $clone_name = $parent;
 139         my $block = $props{block};
 140         my $plot_id = $lines[$n];
 141         my $rep = $props{replicate};
 142         my $number_of_surviving_plants = $props{number_of_surviving_plants};
 143
 144         $ws->write($n+6, 0, $row_number);
 145         $ws->write($n+6, 1, $lines[$n]);
 146         $ws->write($n+6, 2, $block);
 147         $ws->write($n+6, 3, $plot_id);
 148         $ws->write($n+6, 4, $rep);
 149         $ws->write($n+6, 5, $number_of_surviving_plants);
 150
 151         for (my $i=0; $i<@cvterms; $i++) {
 152             if ($cvinfo{$cvterms[$i]}->{values} eq "numeric") {
 153                 $ws->data_validation($n+6, $i+6, { validate => "any" });
 154             }
 155             else {
 156                 $ws->data_validation($n+6, $i+6,
 157                                      {
 158                                          validate => 'list',
 159                                          value    => [ split ",", $cvinfo{$cvterms[$i]}->{values} ],
 160                                      });
 161             }
 162         }
 163     }
 164     $wb->close();
 165     my $contents = read_file($tempfile);
 166     $c->res->content_type('Application/xls');
 167     $c->res->body($contents);
 168 }
 169
 170
 171 1;
 172
 173