From 63302324c0dcfcd8d4fe1d9ccffa65417202e139 Mon Sep 17 00:00:00 2001 From: nickmorales Date: Wed, 21 Mar 2018 13:36:55 -0400 Subject: [PATCH] seedlot upload with accession synonyms. seedlot upload works to update existing seedlots --- lib/CXGN/Stock/Seedlot.pm | 2 +- .../Stock/Seedlot/ParseUpload/Plugin/SeedlotXLS.pm | 53 +++++++++++---- lib/SGN/Controller/AJAX/Seedlot.pm | 77 +++++++++++++++++++--- 3 files changed, 107 insertions(+), 25 deletions(-) diff --git a/lib/CXGN/Stock/Seedlot.pm b/lib/CXGN/Stock/Seedlot.pm index b38d3f5c89..ebbba15e63 100644 --- a/lib/CXGN/Stock/Seedlot.pm +++ b/lib/CXGN/Stock/Seedlot.pm @@ -861,7 +861,7 @@ sub get_current_count_property { my $self = shift; my $current_count_cvterm = SGN::Model::Cvterm->get_cvterm_row($self->schema, 'current_count', 'stock_property'); my $recorded_current_count = $self->stock()->find_related('stockprops', {'me.type_id'=>$current_count_cvterm->cvterm_id}); - return $recorded_current_count->value(); + return $recorded_current_count ? $recorded_current_count->value() : ''; } =head2 Method current_weight() diff --git a/lib/CXGN/Stock/Seedlot/ParseUpload/Plugin/SeedlotXLS.pm b/lib/CXGN/Stock/Seedlot/ParseUpload/Plugin/SeedlotXLS.pm index dca897e07c..434e3d8786 100644 --- a/lib/CXGN/Stock/Seedlot/ParseUpload/Plugin/SeedlotXLS.pm +++ b/lib/CXGN/Stock/Seedlot/ParseUpload/Plugin/SeedlotXLS.pm @@ -201,14 +201,15 @@ sub _validate_with_plugin { $errors{'missing_crosses'} = \@crosses_missing; } - my @seedlots = keys %seen_seedlot_names; - my $rs = $schema->resultset("Stock::Stock")->search({ - 'is_obsolete' => { '!=' => 't' }, - 'uniquename' => { -in => \@seedlots } - }); - while (my $r=$rs->next){ - push @error_messages, "Cell A".$seen_seedlot_names{$r->uniquename}.": seedlot name already exists in database: ".$r->uniquename; - } + # Not checking if seedlot name already exists because the database will just update the seedlot entries + # my @seedlots = keys %seen_seedlot_names; + # my $rs = $schema->resultset("Stock::Stock")->search({ + # 'is_obsolete' => { '!=' => 't' }, + # 'uniquename' => { -in => \@seedlots } + # }); + # while (my $r=$rs->next){ + # push @error_messages, "Cell A".$seen_seedlot_names{$r->uniquename}.": seedlot name already exists in database: ".$r->uniquename; + # } #store any errors found in the parsed file to parse_errors accessor if (scalar(@error_messages) >= 1) { @@ -241,9 +242,15 @@ sub _parse_with_plugin { my %seen_accession_names; my %seen_cross_names; + my %seen_seedlot_names; for my $row ( 1 .. $row_max ) { + my $seedlot_name; my $accession_name; my $cross_name; + if ($worksheet->get_cell($row,0)) { + $seedlot_name = $worksheet->get_cell($row,0)->value(); + $seen_seedlot_names{$seedlot_name}++; + } if ($worksheet->get_cell($row,1)) { $accession_name = $worksheet->get_cell($row,1)->value(); $seen_accession_names{$accession_name}++; @@ -255,26 +262,38 @@ sub _parse_with_plugin { } my $accession_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession', 'stock_type')->cvterm_id(); my $cross_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'cross', 'stock_type')->cvterm_id(); + my $seedlot_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seedlot', 'stock_type')->cvterm_id(); + my @accessions = keys %seen_accession_names; my $rs = $schema->resultset("Stock::Stock")->search({ - 'is_obsolete' => { '!=' => 't' }, - 'uniquename' => { -in => \@accessions }, - 'type_id' => $accession_cvterm_id - }); + 'me.is_obsolete' => { '!=' => 't' }, + -or => ['me.uniquename' => { -in => \@accessions }, 'stockprops.value' => { -in => \@accessions}], + 'me.type_id' => $accession_cvterm_id, + },{join => 'stockprops'}); my %accession_lookup; while (my $r=$rs->next){ $accession_lookup{$r->uniquename} = $r->stock_id; } my @crosses = keys %seen_cross_names; - my $rs = $schema->resultset("Stock::Stock")->search({ + my $cross_rs = $schema->resultset("Stock::Stock")->search({ 'is_obsolete' => { '!=' => 't' }, 'uniquename' => { -in => \@crosses }, 'type_id' => $cross_cvterm_id }); my %cross_lookup; - while (my $r=$rs->next){ + while (my $r=$cross_rs->next){ $cross_lookup{$r->uniquename} = $r->stock_id; } + my @seedlots = keys %seen_seedlot_names; + my $seedlot_rs = $schema->resultset("Stock::Stock")->search({ + 'is_obsolete' => { '!=' => 't' }, + 'uniquename' => { -in => \@seedlots }, + 'type_id' => $seedlot_cvterm_id + }); + my %seedlot_lookup; + while (my $r=$seedlot_rs->next){ + $seedlot_lookup{$r->uniquename} = $r->stock_id; + } for my $row ( 1 .. $row_max ) { my $seedlot_name; @@ -316,7 +335,12 @@ sub _parse_with_plugin { next; } + if (!$accession_lookup{$accession_name} && !$cross_lookup{$cross_name}){ + die "Seedlot $seedlot_name source material not found. Accession $accession_name or cross $cross_name . this should not happen!\n"; + } + $parsed_seedlots{$seedlot_name} = { + seedlot_id => $seedlot_lookup{$seedlot_name}, #If seedlot name already exists, this will allow us to update information for the seedlot accession => $accession_name, accession_stock_id => $accession_lookup{$accession_name}, cross_name => $cross_name, @@ -328,6 +352,7 @@ sub _parse_with_plugin { operator_name => $operator_name }; } + #print STDERR Dumper \%parsed_seedlots; $self->_set_parsed_data(\%parsed_seedlots); return 1; diff --git a/lib/SGN/Controller/AJAX/Seedlot.pm b/lib/SGN/Controller/AJAX/Seedlot.pm index e81ba083ec..699cf46075 100644 --- a/lib/SGN/Controller/AJAX/Seedlot.pm +++ b/lib/SGN/Controller/AJAX/Seedlot.pm @@ -449,6 +449,9 @@ sub upload_seedlots_POST : Args(0) { eval { while (my ($key, $val) = each(%$parsed_data)){ my $sl = CXGN::Stock::Seedlot->new(schema => $schema); + if ($val->{seedlot_id}){ + $sl->seedlot_id($val->{seedlot_id}); #this allow us to update existing seedlot entries + } $sl->uniquename($key); $sl->location_code($location); $sl->box_name($val->{box_name}); @@ -463,16 +466,70 @@ sub upload_seedlots_POST : Args(0) { my $from_stock_id = $val->{accession_stock_id} ? $val->{accession_stock_id} : $val->{cross_stock_id}; my $from_stock_name = $val->{accession} ? $val->{accession} : $val->{cross_name}; - my $transaction = CXGN::Stock::Seedlot::Transaction->new(schema => $schema); - $transaction->factor(1); - $transaction->from_stock([$from_stock_id, $from_stock_name]); - $transaction->to_stock([$seedlot_id, $key]); - $transaction->amount($val->{amount}); - $transaction->weight_gram($val->{weight_gram}); - $transaction->timestamp($timestamp); - $transaction->description($val->{description}); - $transaction->operator($val->{operator_name}); - $transaction->store(); + + my $transaction_amount; + my $transaction_weight; + # If seedlot already exists in database, the system will update so that the current weight and current count match what was uploaded. + if ($val->{seedlot_id}){ + my $sl = CXGN::Stock::Seedlot->new(schema => $schema, seedlot_id => $val->{seedlot_id}); + my $current_stored_count = $sl->get_current_count_property() && $sl->get_current_count_property() ne 'NA' ? $sl->get_current_count_property() : 0; + my $current_stored_weight = $sl->get_current_weight_property() && $sl->get_current_weight_property() ne 'NA' ? $sl->get_current_weight_property() : 0; + + $val->{description} .= " Info: Seedlot XLS upload update."; + + if ($val->{weight_gram} ne 'NA'){ + my $weight_difference = $val->{weight_gram} - $current_stored_weight; + my $weight_factor; + if ($weight_difference >= 0){ + $weight_factor = 1; + } else { + $weight_factor = -1; + $weight_difference = $weight_difference * -1; #Store positive values only + } + my $transaction = CXGN::Stock::Seedlot::Transaction->new(schema => $schema); + $transaction->from_stock([$seedlot_id, $key]); + $transaction->to_stock([$seedlot_id, $key]); + $transaction->weight_gram($weight_difference); + $transaction->timestamp($timestamp); + $transaction->description($val->{description}); + $transaction->operator($val->{operator_name}); + $transaction->factor($weight_factor); + $transaction->store(); + } + + if ($val->{amount} ne 'NA'){ + my $amount_difference = $val->{amount} - $current_stored_count; + my $amount_factor; + if ($amount_difference >= 0){ + $amount_factor = 1; + } else { + $amount_factor = -1; + $amount_difference = $amount_difference * -1; #Store positive values only + } + my $transaction = CXGN::Stock::Seedlot::Transaction->new(schema => $schema); + $transaction->from_stock([$seedlot_id, $key]); + $transaction->to_stock([$seedlot_id, $key]); + $transaction->amount($amount_difference); + $transaction->timestamp($timestamp); + $transaction->description($val->{description}); + $transaction->operator($val->{operator_name}); + $transaction->factor($amount_factor); + $transaction->store(); + } + } + # If this is not updating an existing seedlot, then it just the initial transaction + else { + my $transaction = CXGN::Stock::Seedlot::Transaction->new(schema => $schema); + $transaction->factor(1); + $transaction->from_stock([$from_stock_id, $from_stock_name]); + $transaction->to_stock([$seedlot_id, $key]); + $transaction->amount($val->{amount}); + $transaction->weight_gram($val->{weight_gram}); + $transaction->timestamp($timestamp); + $transaction->description($val->{description}); + $transaction->operator($val->{operator_name}); + $transaction->store(); + } my $sl_new = CXGN::Stock::Seedlot->new(schema => $schema, seedlot_id=>$seedlot_id); $sl_new->set_current_count_property(); -- 2.11.4.GIT