<commit_before>
=head1 NAME

SGN::Controller::AJAX::FieldBook - a REST controller class to provide the
backend for field book operations

=head1 DESCRIPTION

Creating and viewing trials

=head1 AUTHOR

<NAME> <<EMAIL>>

=cut

package SGN::Controller::AJAX::FieldBook;

use Moose;

use List::MoreUtils qw /any /;
use Try::Tiny;
use Scalar::Util qw(looks_like_number);
use File::Slurp;
use Data::Dumper;
use CXGN::Trial::TrialDesign;
use CXGN::Trial::TrialCreate;
use JSON -support_by_pp;
use SGN::View::Trial qw/design_layout_view design_info_view/;
use CXGN::Location::LocationLookup;
use CXGN::Stock::StockLookup;
use CXGN::Trial::TrialLayout;
use Spreadsheet::WriteExcel;
use Digest::MD5;
use File::Basename qw | basename dirname|;

BEGIN { extends 'Catalyst::Controller::REST' }

__PACKAGE__->config(
    default   => 'application/json',
    stash_key => 'rest',
    map       => { 'application/json' => 'JSON', 'text/html' => 'JSON' },
   );

has 'schema' => (
		 is       => 'rw',
		 isa      => 'DBIx::Class::Schema',
		 lazy_build => 1,
		);

sub create_fieldbook_from_trial : Path('/ajax/fieldbook/create') : ActionClass('REST') { }

sub create_fieldbook_from_trial_GET : Args(0) {
  my ($self, $c) = @_;
  my $schema = $c->dbic_schema('Bio::Chado::Schema', 'sgn_chado');
  my $trial_id = $c->req->param('trial_id');
  my $metadata_schema = $c->dbic_schema('CXGN::Metadata::Schema');
  chomp($trial_id);
  if (!$c->user()) {
    $c->stash->{rest} = {error => "You need to be logged in to create a field book" };
    return;
  }
  if (!any { $_ eq "curator" || $_ eq "submitter" } ($c->user()->roles)  ) {
    $c->stash->{rest} = {error =>  "You have insufficient privileges to create a field book." };
    return;
  }
  if (!$trial_id) {
    $c->stash->{rest} = {error =>  "No trial ID supplied." };
    return;
  }
  my $trial = $schema->resultset('Project::Project')->find({project_id => $trial_id});
  if (!$trial) {
    $c->stash->{rest} = {error =>  "Trial does not exist with id $trial_id." };
    return;
  }
  my $dir = $c->tempfiles_subdir('/other');
  my $tempfile = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'other/excelXXXX');
  my $wb = Spreadsheet::WriteExcel->new($tempfile);
  if (!$wb) {
    $c->stash->{rest} = {error =>  "Could not create file" };
    return;
  }
  my $ws = $wb->add_worksheet();
  my $trial_layout;
  print STDERR "\n\nTrial id: ($trial_id)\n\n"; 
  try {
    $trial_layout = CXGN::Trial::TrialLayout->new({schema => $schema, trial_id => $trial_id} );
  };
  if (!$trial_layout) {
    $c->stash->{rest} = {error =>  "Trial does not have a valid field design" };
    return;
  }
  my $trial_name =  $trial_layout->get_trial_name();
  $ws->write(0, 0, 'plot_id');
  $ws->write(0, 1, 'range');
  $ws->write(0, 2, 'plot');
  $ws->write(0, 3, 'rep');
  $ws->write(0, 4, 'accession');
  $ws->write(0, 5, 'is_a_control');

  my %design = %{$trial_layout->get_design()};
  my $row_num = 1;
  foreach my $key (sort { $a <=> $b} keys %design) {
    my %design_info = %{$design{$key}};
    $ws->write($row_num,0,$design_info{'plot_name'});
    $ws->write($row_num,1,$design_info{'block_number'});
    $ws->write($row_num,2,$design_info{'plot_number'});
    $ws->write($row_num,3,$design_info{'rep_number'});
    $ws->write($row_num,4,$design_info{'accession_name'});
    $ws->write($row_num,5,$design_info{'is_a_control'});
    $row_num++;
  }
  $wb->close();

  my $user_id = $c->user()->get_object()->get_sp_person_id();
  open(my $F, "<", $tempfile) || die "Can't open file ".$self->filename();
  binmode $F;
  my $md5 = Digest::MD5->new();
  $md5->addfile($F);
  close($F);
  my $md_row = $metadata_schema->resultset("MdMetadata")->create({
								  create_person_id => $user_id,
								 });
  $md_row->insert();
  my $file_row = $metadata_schema->resultset("MdFiles")->create({
								     basename => basename($tempfile),
								     dirname => dirname($tempfile),
								     filetype => 'field layout xls',
								     md5checksum => $md5->digest(),
								     metadata_id => $md_row->metadata_id(),
								    });
  $file_row->insert();

  my $field_layout_cvterm = $schema->resultset('Cv::Cvterm')
    ->create_with({
		   name   => 'field layout',
		   cv     => 'experiment type',
		   db     => 'null',
		   dbxref => 'field layout',
		  });


  my $project = $trial_layout->project;
#  my $field_layout_experiment = $project->;

  my $experiment = $schema->resultset('NaturalDiversity::NdExperiment')->find(


  print STDERR "create field book here\n";
  $c->stash->{rest} = {
		       success => "1",
		       result => $tempfile,
		       file => "",
		      };<commit_msg>link field layout file for tablet to field layout experiment<commit_after>
=head1 NAME

SGN::Controller::AJAX::FieldBook - a REST controller class to provide the
backend for field book operations

=head1 DESCRIPTION

Creating and viewing trials

=head1 AUTHOR

<NAME> <<EMAIL>>

=cut

package SGN::Controller::AJAX::FieldBook;

use Moose;

use List::MoreUtils qw /any /;
use Try::Tiny;
use Scalar::Util qw(looks_like_number);
use File::Slurp;
use Data::Dumper;
use CXGN::Trial::TrialDesign;
use CXGN::Trial::TrialCreate;
use JSON -support_by_pp;
use SGN::View::Trial qw/design_layout_view design_info_view/;
use CXGN::Location::LocationLookup;
use CXGN::Stock::StockLookup;
use CXGN::Trial::TrialLayout;
use Spreadsheet::WriteExcel;
use Digest::MD5;
use File::Basename qw | basename dirname|;

BEGIN { extends 'Catalyst::Controller::REST' }

__PACKAGE__->config(
    default   => 'application/json',
    stash_key => 'rest',
    map       => { 'application/json' => 'JSON', 'text/html' => 'JSON' },
   );

has 'schema' => (
		 is       => 'rw',
		 isa      => 'DBIx::Class::Schema',
		 lazy_build => 1,
		);

sub create_fieldbook_from_trial : Path('/ajax/fieldbook/create') : ActionClass('REST') { }

sub create_fieldbook_from_trial_GET : Args(0) {
  my ($self, $c) = @_;
  my $schema = $c->dbic_schema('Bio::Chado::Schema', 'sgn_chado');
  my $trial_id = $c->req->param('trial_id');
  my $metadata_schema = $c->dbic_schema('CXGN::Metadata::Schema');
  my $phenome_schema = $c->dbic_schema('CXGN::Phenome::Schema');

  chomp($trial_id);
  if (!$c->user()) {
    $c->stash->{rest} = {error => "You need to be logged in to create a field book" };
    return;
  }
  if (!any { $_ eq "curator" || $_ eq "submitter" } ($c->user()->roles)  ) {
    $c->stash->{rest} = {error =>  "You have insufficient privileges to create a field book." };
    return;
  }
  if (!$trial_id) {
    $c->stash->{rest} = {error =>  "No trial ID supplied." };
    return;
  }
  my $trial = $schema->resultset('Project::Project')->find({project_id => $trial_id});
  if (!$trial) {
    $c->stash->{rest} = {error =>  "Trial does not exist with id $trial_id." };
    return;
  }
  my $dir = $c->tempfiles_subdir('/other');
  my $tempfile = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'other/excelXXXX');
  my $wb = Spreadsheet::WriteExcel->new($tempfile);
  if (!$wb) {
    $c->stash->{rest} = {error =>  "Could not create file" };
    return;
  }
  my $ws = $wb->add_worksheet();
  my $trial_layout;
  print STDERR "\n\nTrial id: ($trial_id)\n\n"; 
  try {
    $trial_layout = CXGN::Trial::TrialLayout->new({schema => $schema, trial_id => $trial_id} );
  };
  if (!$trial_layout) {
    $c->stash->{rest} = {error =>  "Trial does not have a valid field design" };
    return;
  }
  my $trial_name =  $trial_layout->get_trial_name();
  $ws->write(0, 0, 'plot_id');
  $ws->write(0, 1, 'range');
  $ws->write(0, 2, 'plot');
  $ws->write(0, 3, 'rep');
  $ws->write(0, 4, 'accession');
  $ws->write(0, 5, 'is_a_control');

  my %design = %{$trial_layout->get_design()};
  my $row_num = 1;
  foreach my $key (sort { $a <=> $b} keys %design) {
    my %design_info = %{$design{$key}};
    $ws->write($row_num,0,$design_info{'plot_name'});
    $ws->write($row_num,1,$design_info{'block_number'});
    $ws->write($row_num,2,$design_info{'plot_number'});
    $ws->write($row_num,3,$design_info{'rep_number'});
    $ws->write($row_num,4,$design_info{'accession_name'});
    $ws->write($row_num,5,$design_info{'is_a_control'});
    $row_num++;
  }
  $wb->close();

  my $user_id = $c->user()->get_object()->get_sp_person_id();
  open(my $F, "<", $tempfile) || die "Can't open file ".$self->filename();
  binmode $F;
  my $md5 = Digest::MD5->new();
  $md5->addfile($F);
  close($F);
  my $md_row = $metadata_schema->resultset("MdMetadata")->create({
								  create_person_id => $user_id,
								 });
  $md_row->insert();
  my $file_row = $metadata_schema->resultset("MdFiles")->create({
								     basename => basename($tempfile),
								     dirname => dirname($tempfile),
								     filetype => 'field layout xls',
								     md5checksum => $md5->digest(),
								     metadata_id => $md_row->metadata_id(),
								    });
  $file_row->insert();

  my $field_layout_cvterm = $schema->resultset('Cv::Cvterm')
    ->create_with({
		   name   => 'field layout',
		   cv     => 'experiment type',
		   db     => 'null',
		   dbxref => 'field layout',
		  });

  my $project = $trial_layout->get_project;
  my $experiment = $schema->resultset('NaturalDiversity::NdExperiment')->find({
									       'nd_experiment_projects.project_id' => $project->project_id,
									       type_id => $field_layout_cvterm->cvterm_id(),
									      },
									      {
									       join => 'nd_experiment_projects',
									      });


  my $experiment_files = $phenome_schema->resultset("NdExperimentMdFiles")->create({
										    nd_experiment_id => $experiment->nd_experiment_id(),
										    file_id => $file_row->file_id(),
										   });


  print STDERR "create field book here\n";
  $c->stash->{rest} = {
		       success => "1",
		       result => $tempfile,
		       file => "",
		      };
{'fraction_non_alphanumeric': 0.19759625178244042, 'fraction_numerical': 0.006314931757995518, 'mean_word_length': 3.564853556485356, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 218, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '913212', 'n_tokens_mistral': 3964, 'n_tokens_neox': 3544, 'n_words': 708}