bin/load_people.pl

   1 #!/usr/bin/perl
   2
   3 =head1
   4
   5 load_people.pl - loading user accounts into cxgn databases
   6
   7 =head1 SYNOPSIS
   8
   9 load_people.pl -H [dbhost] -D [dbname] -i [infile]
  10
  11 =head1 COMMAND-LINE OPTIONS
  12
  13 =head2 ARGUMENTS
  14
  15 =over 5
  16
  17 =item -H
  18
  19 host name (required) e.g. "localhost"
  20
  21 =item -D
  22
  23 database name (required) e.g. "cxgn_cassava"
  24
  25 =item -i
  26
  27 path to infile (required)
  28
  29 =back
  30
  31 =head2 FLAGS
  32
  33 =over 5
  34
  35 =item -t
  36
  37 Test run. Rolling back at the end.
  38
  39 =back
  40
  41 =head1 DESCRIPTION
  42
  43 This script loads user account data into the sgn_people.sp_person and sgn_people.sp_login table. Each column in the spreadsheet represents a single user, and one row will be added to sp_login and one row to sp_person.
  44
  45 The input file is xlsx format, and should have the following columns (column order is not important):
  46
  47 username
  48 first_name
  49 last_name
  50 email
  51 organization
  52 address
  53 country
  54 phone
  55 research_keywords
  56 research_interests
  57 webpage
  58
  59 The first four columns listed are required.
  60
  61 The script outputs the username, first_name, last_name, email and assigned initial random password. This can be used to send the password to the user.
  62
  63 =head1 AUTHOR
  64
  65  Lukas Mueller <lam87@cornell.edu>, Nov 2022
  66
  67 =cut
  68
  69 use strict;
  70
  71 use Getopt::Std;
  72 use Data::Dumper;
  73 use JSON::Any;
  74 use JSON::PP;
  75 use Carp qw /croak/ ;
  76 use Try::Tiny;
  77 use Pod::Usage;
  78 use Spreadsheet::XLSX;
  79 use Bio::Chado::Schema;
  80 use CXGN::People::Person;
  81 use CXGN::People::Schema;
  82 use CXGN::DB::InsertDBH;
  83 use SGN::Model::Cvterm;
  84 use Text::Iconv;
  85 use Crypt::RandPasswd;
  86 our ($opt_H, $opt_D, $opt_i, $opt_t);
  87
  88 getopts('H:i:tD:');
  89
  90 my $dbhost = $opt_H;
  91 my $dbname = $opt_D;
  92 my $file = $opt_i;
  93
  94 print STDERR "Input file: $file\n";
  95 print STDERR "DB host: $dbhost\n";
  96 print STDERR "DB name: $dbname\n";
  97 print STDERR "Rollback: $opt_t\n";
  98
  99 if (!$opt_H || !$opt_D || !$opt_i) {
 100     pod2usage(-verbose => 2, -message => "Must provide options -H (hostname), -D (database name), -i (input file)\n");
 101 }
 102
 103 my $dbh = CXGN::DB::InsertDBH->new( { dbhost=>$dbhost,
 104                                       dbname=>$dbname,
 105                                       dbargs => {AutoCommit => 0,
 106                                                  RaiseError => 1}
 107                                     }
 108     );
 109
 110 my $people_schema = CXGN::People::Schema->connect( sub { $dbh->get_actual_dbh() } );
 111
 112 my $schema= Bio::Chado::Schema->connect( sub { $dbh->get_actual_dbh() } );
 113 $dbh->do('SET search_path TO public,sgn,sgn_people');
 114
 115 my $converter = Text::Iconv->new("utf-8", "windows-1251");
 116
 117 my $excel = Spreadsheet::XLSX->new($opt_i, $converter);
 118
 119 my $coderef = sub {
 120
 121     foreach my $sheet (@{$excel->{Worksheet}}) {
 122
 123         printf("Sheet: %s\n", $sheet->{Name});
 124
 125         $sheet->{MaxRow} ||= $sheet->{MinRow};
 126
 127         print STDERR "MIN ROW = ".$sheet->{MinRow}."\n";
 128
 129         # parse header
 130         #
 131         my @required_headers = qw | first_name last_name username email |;
 132
 133         my %ch;
 134         my @header;
 135
 136         foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
 137             @header = @{$sheet->{Cells}->[0]};
 138
 139             for(my $i =0; $i< @header; $i++) {
 140                 print STDERR $header[$i]->{Val}."\n";
 141                 $ch{$header[$i]->{Val} } = $i;
 142             }
 143         }
 144
 145         print STDERR "HEADERS: ".Dumper(\%ch);
 146         print STDERR "REQUIRED: ".Dumper(\@required_headers);
 147
 148         my @missing_headers;
 149         foreach my $h (@required_headers) {
 150             if (!exists($ch{$h})) {
 151                 push @missing_headers, $h;
 152             }
 153         }
 154
 155         if (@missing_headers) {
 156             print STDERR "Required headers include: ". join(", ", @required_headers)."\n";
 157             print STDERR "Missing: ".join(", ", @missing_headers)."\n";
 158
 159             die "Check file format for header requirements.";
 160         }
 161
 162         foreach my $row (1 .. $sheet->{MaxRow}) {
 163
 164             $sheet->{MaxCol} ||= $sheet->{MinCol};
 165
 166             my @fields = map { $_ ->{Val} } @{$sheet->{Cells}->[$row]};
 167
 168             my %data;
 169             for(my $n =0; $n< @header; $n++) {
 170                 if ($fields[$n]) {
 171                     $data{$header[$n]->{Val}} = $fields[$n];
 172                 }
 173             }
 174
 175             print STDERR "DATA: ".Dumper(\%data);
 176
 177             if (!$data{username} || !$data{email} || !$data{first_name} || !$data{last_name}) {
 178                 print STDERR "Not enough information provided in row ".join(", ", @fields).". Skipping.\n";
 179                 next();
 180             }
 181
 182             my $row = $people_schema->resultset("SpPerson")->find( { username => $data{username} } );
 183             if ($row) {
 184                 print STDERR "Username $data{username} already exists in the database. Skipping this row.\n";
 185                 next();
 186             }
 187
 188             if ($data{email}) {
 189                 my $rs = $people_schema->resultset("SpPerson")
 190                     ->search( { '-or' => [ contact_email => $data{email}, private_email => $data{email}, pending_email => $data{email} ] } );
 191
 192                 if ($rs->count > 0) {
 193                     print STDERR "Email $data{email} already exists in the database in contact_email, pending_email, or private_email field. Skipping this row.\n";
 194                     next();
 195                 }
 196             }
 197
 198             # $row = $people_schema->resultset("SpPerson")->find( { pending_email => $data{email} });
 199             # if ($row) {
 200             #   print STDERR "Email $data{email} already exists in the database in pending_email field. Skipping this row.\n";
 201             #   next();
 202             # }
 203
 204             my $password =Crypt::RandPasswd->word( 8 , 8 );
 205             if ($data{username}) {
 206                 my $login = CXGN::People::Login->new($dbh);
 207
 208                 $login->set_username($data{username});
 209                 $login->set_private_email($data{email});
 210                 $login->set_pending_email($data{email});
 211                 $login->set_organization($data{organization});
 212                 $login->set_password($password);
 213
 214                 print  "$data{first_name}\t$data{last_name}\t$data{username}\t$data{email}\t$password\n";
 215
 216                 my $sp_person_id = $login->store();
 217
 218                 print STDERR "SP PERSON ID = ".$sp_person_id."\n";
 219
 220                 my $person = CXGN::People::Person->new($dbh, $sp_person_id);
 221
 222                 $person->set_first_name($data{first_name});
 223                 $person->set_last_name($data{last_name});
 224                 $person->set_contact_email($data{email});
 225                 $person->set_address($data{address});
 226                 $person->set_country($data{country});
 227                 $person->set_phone_number($data{phone_number});
 228                 $person->set_research_keywords($data{research_keywords});
 229                 $person->set_research_interests($data{research_interests});
 230                 $person->set_webpage($data{webpage});
 231                 $person->store();
 232             }
 233         }
 234     }
 235 };
 236
 237 try {
 238     $schema->txn_do($coderef);
 239     if (!$opt_t) {
 240         print "Transaction succeeded! Commiting user data! \n\n";
 241     }
 242     else {
 243         die "Not storing, rolling back\n";
 244     }
 245
 246 } catch {
 247     # Transaction failed
 248     die "An error occured! Rolling back!" . $_ . "\n";
 249 };
 250
 251 $dbh->disconnect();