t/selenium2/breeders/upload_multi_trial_file.t

   1 use strict;
   2 use lib 't/lib';
   3
   4 use Test::More;
   5 use SGN::Test::Fixture;
   6 use SimulateC;
   7 use CXGN::UploadFile;
   8 use CXGN::Trial;
   9 use CXGN::Trial::TrialCreate;
  10 use CXGN::Trial::ParseUpload;
  11 use SGN::Model::Cvterm;
  12 use Data::Dumper;
  13 use CXGN::Location::LocationLookup;
  14 use CXGN::Stock::StockLookup;
  15 use CXGN::List;
  16 use CXGN::Trial::TrialDesign;
  17 use CXGN::BreedersToolbox::Projects;
  18 use CXGN::Genotype::StoreGenotypingProject;
  19 use DateTime;
  20 use Test::WWW::Mechanize;
  21 use LWP::UserAgent;
  22 use JSON;
  23 use Spreadsheet::Read;
  24 use Text::CSV;
  25
  26 my $f = SGN::Test::Fixture->new();
  27
  28 for my $extension ("xlsx", "xls") {
  29
  30     my $c = SimulateC->new({
  31                 dbh                     => $f->dbh(),
  32         bcs_schema      => $f->bcs_schema(),
  33         metadata_schema => $f->metadata_schema(),
  34         phenome_schema  => $f->phenome_schema(),
  35         sp_person_id    =>  41,
  36     });
  37
  38     my $pre_project_count               = $c->bcs_schema->resultset('Project::Project')->search({})->count();
  39         my $pre_nd_experiment_count         = $c->bcs_schema->resultset('NaturalDiversity::NdExperiment')->search({})->count();
  40         my $pre_nd_experimentprop_count     = $c->bcs_schema->resultset('NaturalDiversity::NdExperimentprop')->search({})->count();
  41         my $pre_nd_experiment_proj_count    = $c->bcs_schema->resultset('NaturalDiversity::NdExperimentProject')->search({})->count();
  42         my $pre_project_prop_count          = $c->bcs_schema->resultset('Project::Projectprop')->search({})->count();
  43         my $pre_stock_count                 = $c->bcs_schema->resultset('Stock::Stock')->search({})->count();
  44         my $pre_stock_prop_count            = $c->bcs_schema->resultset('Stock::Stockprop')->search({})->count();
  45         my $pre_stock_relationship_count    = $c->bcs_schema->resultset('Stock::StockRelationship')->search({})->count();
  46         my $pre_nd_experiment_stock_count   = $c->bcs_schema->resultset('NaturalDiversity::NdExperimentStock')->search({})->count();
  47         my $pre_project_relationship_count  = $c->bcs_schema->resultset('Project::ProjectRelationship')->search({})->count();
  48         my $chado_schema                                        = $c->bcs_schema();
  49     #first upload excel trial file with multiple trials
  50     my %upload_metadata;
  51     my $file_name = "t/data/trial/demo_multiple_trial_design.$extension";
  52     my $time      = DateTime->now();
  53     my $timestamp = $time->ymd() . "_" . $time->hms();
  54
  55     #Test Archive upload file
  56     my $uploader = CXGN::UploadFile->new({
  57         tempfile         => $file_name,
  58         subdirectory     => "trial_upload",
  59         archive_path     => '/tmp',
  60         archive_filename => "demo_multiple_trial_design.$extension",
  61         timestamp        => $timestamp,
  62         user_id          => 41,
  63         user_role        => 'curator'
  64     });
  65
  66     ##store uploaded temprarly file info in archive
  67     my $archived_filename_with_path = $uploader->archive();
  68     my $md5 = $uploader->get_md5($archived_filename_with_path);
  69     ok($archived_filename_with_path, "Uploaded file archived");
  70     ok($md5, "Uploaded file md5");
  71
  72     $upload_metadata{'archived_file'}      = $archived_filename_with_path;
  73     $upload_metadata{'archived_file_type'} = 'trial phenotypes';
  74     $upload_metadata{'user_id'}            = $c->sp_person_id;
  75     $upload_metadata{'date'}               = "2018-02-14_10:10:10";
  76
  77     #parse uploaded file with appropriate plugin
  78     my $parser  = CXGN::Trial::ParseUpload->new(chado_schema=> $chado_schema, filename => $archived_filename_with_path);
  79     $parser->load_plugin('MultipleTrialDesignExcelFormat');
  80     my $parsed_data = $parser->parse();
  81         print STDERR "Parsed data: " . Dumper($parsed_data);
  82     ok(!$parsed_data->{error}, 'check if parse validate igd file fails for excel file');
  83     ok(!$parser->has_parse_errors, 'check if parser error occurs');
  84
  85     my $parsed_data_check = {
  86                 '198667HBEPR_popa' => {
  87             'planting_date' => '1984-09-17',
  88             'plot_length'   => '5',
  89             'design_type'   => 'Augmented',
  90             'harvest_date'  => '1985-08-16',
  91             'year'          => '1999',
  92             'description'   => 'EPR',
  93             'entry_numbers' => undef,
  94             'design_details'=> {
  95                 '15' => {
  96                     'plot_name'    => '198667HBEPR_popa_rep1_BRA33_9',
  97                     'range_number' => '6',
  98                     'is_a_control' => 1,
  99                     'plot_number'  => '9',
 100                     'stock_name'   => 'UG120022',
 101                     'rep_number'   => '1',
 102                     'block_number' => '1',
 103                     'row_number'   => '9',
 104                     'col_number'   => '3'
 105                 },
 106                 '14' => {
 107                     'is_a_control' => 1,
 108                     'plot_number'  => '8',
 109                     'stock_name'   => 'UG120021',
 110                     'rep_number'   => '1',
 111                     'block_number' => '1',
 112                     'row_number'   => '8',
 113                     'col_number'   => '3',
 114                     'range_number' => '6',
 115                     'plot_name'    => '198667HBEPR_popa_rep1_BRA131_8',
 116                     'range_number' => '6',
 117                     'row_number'   => '8',
 118                     'col_number'   => '3',
 119                     'block_number' => '1'
 120                 },
 121                 '13' => {
 122                     'stock_name'    => 'UG120019',
 123                     'rep_number'    => '1',
 124                     'plot_number'   => '7',
 125                     'is_a_control'  => 1,
 126                     'range_number'  => '6',
 127                     'plot_name'     => '198667HBEPR_popa_rep1_BRA30_7',
 128                     'col_number'    => '3',
 129                     'row_number'    => '7',
 130                     'block_number'  => '1'
 131                 },
 132                 '11' => {
 133                     'plot_name'     => '198667HBEPR_popa_rep1_BRA128_5',
 134                     'range_number'  => '6',
 135                     'is_a_control'  => 1,
 136                     'plot_number'   => '5',
 137                     'stock_name'    => 'UG120010',
 138                     'rep_number'    => '2',
 139                     'block_number'  => '1',
 140                     'row_number'    => '5',
 141                     'col_number'    => '3'
 142                 },
 143                 '12' => {
 144                     'range_number' => '6',
 145                     'plot_name'    => '198667HBEPR_popa_rep1_BRA28_6',
 146                     'rep_number'   => '2',
 147                     'stock_name'   => 'UG120017',
 148                     'is_a_control' => 1,
 149                     'plot_number'  => '6',
 150                     'block_number' => '1',
 151                     'col_number'   => '3',
 152                     'row_number'   => '6'
 153                 }
 154             },
 155             'location'         => 'test_location',
 156             'trial_type'       => 76515,
 157             'breeding_program' => 'test',
 158             'plot_width'       => '5',
 159             'field_size'       => '8'
 160         },
 161         '199275HBEPR_stom' => {
 162             'breeding_program' => 'test',
 163             'plot_width'       => '5',
 164             'field_size'       => '8',
 165             'description'      => 'EPR',
 166             'entry_numbers'    => undef,
 167             'location'         => 'test_location',
 168             'design_details'   => {
 169                 '19' => {
 170                     'row_number'   => '33',
 171                     'col_number'   => '2',
 172                     'block_number' => '1',
 173                     'plot_number'  => '33',
 174                     'is_a_control' => 1,
 175                     'rep_number'   => '1',
 176                     'stock_name'   => 'XG120071',
 177                     'plot_name'    => '199275HBEPR_stom_rep1_CG1420-1_33',
 178                     'range_number' => '6'
 179                 },
 180                 '17' => {
 181                     'range_number' => '6',
 182                     'plot_name'    => '199275HBEPR_stom_rep1_SOLITA_31',
 183                     'rep_number'   => '1',
 184                     'stock_name'   => 'XG120061',
 185                     'plot_number'  => '31',
 186                     'is_a_control' => 1,
 187                     'block_number' => '1',
 188                     'col_number'   => '2',
 189                     'row_number'   => '31'
 190                 },
 191                 '20' => {
 192                     'stock_name'   => 'XG120073',
 193                     'rep_number'   => '1',
 194                     'is_a_control' => 1,
 195                     'plot_number'  => '34',
 196                     'range_number' => '6',
 197                     'plot_name'    => '199275HBEPR_stom_rep1_CM1785-6_34',
 198                     'col_number'   => '3',
 199                     'row_number'   => '34',
 200                     'block_number' => '1'
 201                 },
 202                 '18' => {
 203                     'row_number'    => '32',
 204                     'col_number'    => '3',
 205                     'block_number'  => '1',
 206                     'is_a_control'  => 1,
 207                     'plot_number'   => '32',
 208                     'stock_name'    => 'XG120068',
 209                     'rep_number'    => '1',
 210                     'plot_name'     => '199275HBEPR_stom_rep1_CG917-5_32',
 211                     'range_number'  => '6'
 212                 },
 213                 '16' => {
 214                     'block_number'  => '1',
 215                     'col_number'    => '2',
 216                     'row_number'    => '30',
 217                     'range_number'  => '6',
 218                     'plot_name'     => '199275HBEPR_stom_rep1_CM3306-4_30',
 219                     'stock_name'    => 'XG120030',
 220                     'rep_number'    => '1',
 221                     'is_a_control'  => 1,
 222                     'plot_number'   => '30'
 223                 }
 224             },
 225             'trial_type'    => 76515,
 226             'harvest_date'  => '1993-08-04',
 227             'year'          => '1999',
 228             'planting_date' => '1992-09-19',
 229             'plot_length'   => '5',
 230             'design_type'   => 'Augmented'
 231         },
 232         '199934HBEPR_cara' => {
 233             'description'    => 'EPR',
 234             'trial_type'     => 76515,
 235             'location'       => 'test_location',
 236             'design_details' => {
 237                 '3' => {
 238                     'col_number'   => '3',
 239                     'row_number'   => '30',
 240                     'block_number' => '1',
 241                     'rep_number'   => '2',
 242                     'stock_name'   => 'UG120006',
 243                     'plot_number'  => '3',
 244                     'is_a_control' => 1,
 245                     'range_number' => '4',
 246                     'plot_name'    => '199934HBEPR_cara_rep1_UG120006_3'
 247                 },
 248                 '4' => {
 249                     'block_number' => '2',
 250                     'row_number'   => '40',
 251                     'col_number'   => '4',
 252                     'plot_name'    => '199934HBEPR_cara_rep1_UG120008_4',
 253                     'range_number' => '5',
 254                     'plot_number'  => '4',
 255                     'is_a_control' => 1,
 256                     'rep_number'   => '2',
 257                     'stock_name'   => 'UG120008'
 258                 },
 259                 '5' => {
 260                     'col_number'   => '5',
 261                     'row_number'   => '50',
 262                     'block_number' => '2',
 263                     'rep_number'   => '2',
 264                     'stock_name'   => 'UG120009',
 265                     'is_a_control' => 1,
 266                     'plot_number'  => '5',
 267                     'range_number' => '5',
 268                     'plot_name'    => '199934HBEPR_cara_rep1_UG120009_5'
 269                 },
 270                 '1' => {
 271                     'stock_name'   => 'UG120002',
 272                     'rep_number'   => '2',
 273                     'is_a_control' => 1,
 274                     'plot_number'  => '1',
 275                     'range_number' => '4',
 276                     'plot_name'    => '199934HBEPR_cara_rep1_UG120002_1',
 277                     'col_number'   => '1',
 278                     'row_number'   => '10',
 279                     'block_number' => '1'
 280                 },
 281                 '2' => {
 282                     'rep_number'   => '2',
 283                     'stock_name'   => 'UG120004',
 284                     'is_a_control' => 1,
 285                     'plot_number'  => '2',
 286                     'range_number' => '4',
 287                     'plot_name'    => '199934HBEPR_cara_rep1_UG120004_2',
 288                     'col_number'   => '2',
 289                     'row_number'   => '20',
 290                     'block_number' => '1'
 291                 }
 292             },
 293             'entry_numbers'    => undef,
 294             'breeding_program' => 'test',
 295             'field_size'       => '8',
 296             'plot_width'       => '5',
 297             'planting_date'    => '1999-06-04',
 298             'design_type'      => 'Augmented',
 299             'plot_length'      => '5',
 300             'harvest_date'     => '2000-03-14',
 301             'year'             => '1999'
 302         },
 303         '199947HBEPR_mora' => {
 304             'year'         => '1999',
 305             'harvest_date' => '2000-10-19',
 306             'plot_length'  => '5',
 307             'design_type'  => 'Augmented',
 308             'planting_date'=> '1999-06-23',
 309             'plot_width'   => '5',
 310             'field_size'   => '8',
 311             'breeding_program' => 'test',
 312             'entry_numbers' => undef,
 313             'design_details'=> {
 314                 '7' => {
 315                     'col_number'    => '6',
 316                     'row_number'    => '60',
 317                     'block_number'  => '1',
 318                     'stock_name'    => 'UG120158',
 319                     'rep_number'    => '2',
 320                     'plot_number'   => '2',
 321                     'is_a_control'  => 1,
 322                     'range_number'  => '6',
 323                     'plot_name'     => '199947HBEPR_mora_rep1_UG120158_2'
 324                 },
 325                 '9' => {
 326                     'block_number' => '1',
 327                     'row_number'   => '80',
 328                     'col_number'   => '8',
 329                     'plot_name'    => '199947HBEPR_mora_rep1_UG120160_4',
 330                     'range_number' => '8',
 331                     'is_a_control' => 1,
 332                     'plot_number'  => '4',
 333                     'rep_number'   => '2',
 334                     'stock_name'   => 'UG120160'
 335                 },
 336                 '8' => {
 337                     'row_number'   => '70',
 338                     'col_number'   => '7',
 339                     'block_number' => '1',
 340                     'is_a_control' => 1,
 341                     'plot_number'  => '3',
 342                     'stock_name'   => 'UG120159',
 343                     'rep_number'   => '2',
 344                     'plot_name'    => '199947HBEPR_mora_rep1_UG120159_3',
 345                     'range_number' => '7'
 346                 },
 347                 '6' => {
 348                     'plot_name'    => '199947HBEPR_mora_rep1_UG120157_1',
 349                     'range_number' => '6',
 350                     'plot_number'  => '1',
 351                     'is_a_control' => 1,
 352                     'rep_number'   => '2',
 353                     'stock_name'   => 'UG120157',
 354                     'block_number' => '1',
 355                     'row_number'   => '50',
 356                     'col_number'   => '5'
 357                 },
 358                 '10' => {
 359                     'range_number' => '8',
 360                     'plot_name'    => '199947HBEPR_mora_rep1_UG120161_5',
 361                     'rep_number'   => '2',
 362                     'stock_name'   => 'UG120161',
 363                     'is_a_control' => 1,
 364                     'plot_number'  => '5',
 365                     'block_number' => '1',
 366                     'col_number'   => '9',
 367                     'row_number'   => '90'
 368                 }
 369             },
 370             'location'    => 'test_location',
 371             'trial_type'  => 76515,
 372             'description' => 'EPR'
 373         }
 374     };
 375
 376         is_deeply($parsed_data, $parsed_data_check, 'Check if parsed data is correct for excel file');
 377
 378         foreach my $trial_name (keys %$parsed_data) {
 379         my $trial_data = $parsed_data->{$trial_name};
 380
 381         my $expected_project_name = $trial_name;
 382         my $expected_project_description = $trial_data->{description} || 'EPR';
 383
 384                 my $trial_create = CXGN::Trial::TrialCreate->new({
 385             chado_schema                => $c->bcs_schema(),
 386             dbh                                 => $c->dbh(),
 387             owner_id                    => 41,
 388                 trial_year                      => $trial_data->{year},
 389                 trial_description       => $expected_project_description, #$trial_data->{description},
 390                 trial_name                      => $trial_name,
 391                 design_type             => $trial_data->{design_type},
 392                 design                          => $trial_data->{design_details},
 393                 program                         => $trial_data->{breeding_program},
 394                         trial_location          => $trial_data->{location},
 395             operator                    => "janedoe",
 396         });
 397
 398         my $save = $trial_create->save_trial();
 399         ok($save->{'trial_id'}, "Test saving trial '$trial_name' with multiple trial designs");
 400
 401         my $project = $c->bcs_schema()->resultset('Project::Project')->search({}, { order_by => { -desc => 'project_id'} })->first();
 402         my $project_name = $project->name();
 403         print STDERR "Debug: actual project name: '$project_name'\n";
 404         ok($project_name eq $expected_project_name, "Test project name for trial '$trial_name' matches expected name");
 405
 406         my $project = $c->bcs_schema()->resultset('Project::Project')->search({}, { order_by => { -desc => 'project_id'} })->first();
 407         my $project_desc = $project->description();
 408         print STDERR "Debug: actual project desc: '$project_desc'\n";
 409         ok($project_desc eq $expected_project_description, "Test project description for trial '$trial_name' matches expected description");
 410
 411         my $post_project_count = $c->bcs_schema->resultset('Project::Project')->search({})->count();
 412         my $post1_project_diff = $post_project_count - $pre_project_count;
 413         print STDERR "Project diff: " . $post1_project_diff . "\n";
 414         ok($post1_project_diff == 1, "Check if project count is correct for trial with multiple trial designs");
 415
 416         my $post_nd_experiment_count = $c->bcs_schema->resultset('NaturalDiversity::NdExperiment')->search({})->count();
 417                 my $post1_nd_experiment_diff = $post_nd_experiment_count - $pre_nd_experiment_count;
 418                 print STDERR "NdExperiment: " . $post1_nd_experiment_diff . "\n";
 419                 ok($post1_nd_experiment_diff == 1, "check ndexperiment table after upload excel trial");
 420
 421                 my $post_nd_experiment_proj_count = $c->bcs_schema->resultset('NaturalDiversity::NdExperimentProject')->search({})->count();
 422                 my $post1_nd_experiment_proj_diff = $post_nd_experiment_proj_count - $pre_nd_experiment_proj_count;
 423                 print STDERR "NdExperimentProject: " . $post1_nd_experiment_proj_diff . "\n";
 424                 ok($post1_nd_experiment_proj_diff == 1, "check ndexperimentproject table after upload excel trial");
 425
 426         my $post_nd_experimentprop_count = $c->bcs_schema->resultset('NaturalDiversity::NdExperimentprop')->search({})->count();
 427                 my $post1_nd_experimentprop_diff = $post_nd_experimentprop_count - $pre_nd_experimentprop_count;
 428                 print STDERR "NdExperimentprop: " . $post1_nd_experimentprop_diff . "\n";
 429                 ok($post1_nd_experimentprop_diff == 0, "check ndexperimentprop table after upload excel trial");
 430
 431                 my $post_project_prop_count = $c->bcs_schema->resultset('Project::Projectprop')->search({})->count();
 432                 my $post1_project_prop_diff = $post_project_prop_count - $pre_project_prop_count;
 433                 print STDERR "Projectprop: " . $post1_project_prop_diff . "\n";
 434                 ok($post1_project_prop_diff == 4, "check projectprop table after upload excel trial");
 435
 436                 my $post_stock_count = $c->bcs_schema->resultset('Stock::Stock')->search({})->count();
 437                 my $post1_stock_diff = $post_stock_count - $pre_stock_count;
 438                 print STDERR "Stock: " . $post1_stock_diff . "\n";
 439                 ok($post1_stock_diff == 5, "check stock table after upload excel trial");
 440
 441                 my $post_stock_prop_count = $c->bcs_schema->resultset('Stock::Stockprop')->search({})->count();
 442                 my $post1_stock_prop_diff = $post_stock_prop_count - $pre_stock_prop_count;
 443                 print STDERR "Stockprop: " . $post1_stock_prop_diff . "\n";
 444                 ok($post1_stock_prop_diff == 35, "check stockprop table after upload excel trial");
 445
 446                 my $post_stock_relationship_count = $c->bcs_schema->resultset('Stock::StockRelationship')->search({})->count();
 447                 my $post1_stock_relationship_diff = $post_stock_relationship_count - $pre_stock_relationship_count;
 448                 print STDERR "StockRelationship: " . $post1_stock_relationship_diff . "\n";
 449                 ok($post1_stock_relationship_diff == 5, "check stockrelationship table after upload excel trial");
 450
 451                 my $post_nd_experiment_stock_count = $c->bcs_schema->resultset('NaturalDiversity::NdExperimentStock')->search({})->count();
 452                 my $post1_nd_experiment_stock_diff = $post_nd_experiment_stock_count - $pre_nd_experiment_stock_count;
 453                 print STDERR "NdExperimentStock: " . $post1_nd_experiment_stock_diff . "\n";
 454                 ok($post1_nd_experiment_stock_diff == 5, "check ndexperimentstock table after upload excel trial");
 455
 456                 my $post_project_relationship_count = $c->bcs_schema->resultset('Project::ProjectRelationship')->search({})->count();
 457                 my $post1_project_relationship_diff = $post_project_relationship_count - $pre_project_relationship_count;
 458                 print STDERR "ProjectRelationship: " . $post1_project_relationship_diff . "\n";
 459                 ok($post1_project_relationship_diff == 1, "check projectrelationship table after upload excel trial");
 460
 461         $f->clean_up_db();
 462         }
 463 }
 464
 465 done_testing();