Merge pull request #5199 from solgenomics/topic/tracking_transformation
[sgn.git] / bin / update_stock_props.pl
blobe8e0aa73733558e8835d99146409658001df40ee
1 #!/usr/bin/perl
3 =head1 NAME
5 update_stock_props.pl - updates stock props
7 =head1 DESCRIPTION
9 update_stock_props -H [database host] -D [database name] update_stock_prop_file.xlsx
11 Options:
13 -H the database host
14 -D the database name
16 update_stock_prop_file.xlsx: a file with three columns:
17 accession_name
18 <stock_attribute>_old
19 <stock_attribute>_new
21 The script will remove the _new and _old extension, compare if they are the same, and then start replacing the values in the old column with the values in the new column for each accession_name.
23 =head1 AUTHOR
25 Lukas Mueller <lam87@cornell.edu.
27 =cut
30 use strict;
31 use warnings;
32 use Bio::Chado::Schema;
33 use Getopt::Std;
34 use SGN::Model::Cvterm;
35 use CXGN::DB::InsertDBH;
36 use Spreadsheet::ParseExcel;
37 use Spreadsheet::ParseXLSX;
40 our ($opt_H, $opt_D);
41 getopts("H:D:");
42 my $dbhost = $opt_H;
43 my $dbname = $opt_D;
44 my $file = shift;
45 my @traits;
46 my @formulas;
47 my @array_ref;
49 my $dbh = CXGN::DB::InsertDBH->new( { dbhost=>"$dbhost",
50 dbname=>"$dbname",
51 dbargs => {AutoCommit => 1,
52 RaiseError => 1,
55 } );
58 my $schema= Bio::Chado::Schema->connect( sub { $dbh->get_actual_dbh() });
60 my $formula_cvterm = $schema->resultset("Cv::Cvterm")->create_with({
61 name => "formula",
62 cv => "cvterm_property",
63 });
65 my $type_id = $formula_cvterm->cvterm_id();
68 # Match a dot, extension .xls / .xlsx
69 my ($extension) = $file =~ /(\.[^.]+)$/;
70 my $parser;
72 if ($extension eq '.xlsx') {
73 $parser = Spreadsheet::ParseXLSX->new();
75 else {
76 $parser = Spreadsheet::ParseExcel->new();
79 #try to open the excel file and report any errors
80 my $excel_obj = $parser->parse($file);
82 if ( !$excel_obj ) {
83 die "Input file error: ".$parser->error()."\n";
86 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
87 my ( $row_min, $row_max ) = $worksheet->row_range();
88 my ( $col_min, $col_max ) = $worksheet->col_range();
90 if (($col_max - $col_min) < 1 || ($row_max - $row_min) < 1 ) { #must have header and at least one row of phenotypes
91 die "Input file error: spreadsheet is missing header\n";
94 # read header line
96 my ($accession_name, $cvterm_old, $cvterm_new);
98 if ($worksheet->get_cell(0,0)) {
99 $accession_name = $worksheet->get_cell(0,0)->value();
101 if ($worksheet->get_cell(0,1)) {
102 $cvterm_old = $worksheet->get_cell(0,1)->value();
103 $cvterm_old =~ s/(.*)\_old/$1/g;
104 if (! $cvterm_old) { die "cvterm needs to be cvterm with _old extension in old column"; }
107 if ($worksheet->get_cell(0,2)) {
108 $cvterm_new = $worksheet->get_cell(0,2)->value();
109 $cvterm_new =~ s/(.*)\_new/$1/g;
110 if (! $cvterm_new) { die "cvterm needs to be cvterm with _new extension in old column"; }
113 if ($cvterm_new ne $cvterm_old) {
114 die "cvterm_new must be the same as cvterm_old without the extension, currently $cvterm_new vs $cvterm_old";
117 print STDERR "Working with stock property $cvterm_new\n";
118 my $cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, $cvterm_new, 'stock_property')->cvterm_id();
120 # read data lines
122 my ($cvterm_new_value, $cvterm_old_value);
124 for (my $n=1; $n<$row_max; $n++) {
126 if ($worksheet->get_cell($n,0)) {
127 $accession_name = $worksheet->get_cell($n,0)->value();
129 if ($worksheet->get_cell($n,1)) {
130 $cvterm_old_value = $worksheet->get_cell($n,1)->value();
132 if ($worksheet->get_cell($n,2)) {
133 $cvterm_new_value = $worksheet->get_cell($n,2)->value();
136 my $accession = $schema->resultset("Stock::Stock")->find( { uniquename => $accession_name } );
137 if (!$accession) { die "Accession $accession does not exist. Please fix and try again."; }
139 my $current_entry = $schema->resultset("Stock::Stockprop")->find( { value => $cvterm_old_value, stock_id => $accession->stock_id(), type_id => $cvterm_id });
141 if (! $current_entry) {
142 die "The accession $accession_name does not have a value of $cvterm_old_value of type $cvterm_new. Please fix and try again";
146 $current_entry->update(
148 value => $cvterm_new_value
154 print STDERR "Done.\n";