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