From b7915484395a88fe3ab8f9a66abb65e73bf17437 Mon Sep 17 00:00:00 2001 From: christopherm Date: Sun, 12 Nov 2006 19:01:17 +0000 Subject: Adding script to convert users in members file to central db. It works well except if there is a user that has a bogus or non existing dataset, in which case the script will die trying to import that user git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@528 4979c152-3d1c-0410-bac9-87ea11338e46 --- import_members.pl | 243 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 243 insertions(+) create mode 100755 import_members.pl diff --git a/import_members.pl b/import_members.pl new file mode 100755 index 00000000..9cf35b96 --- /dev/null +++ b/import_members.pl @@ -0,0 +1,243 @@ +#!/usr/bin/perl +##################################################################### +# Data import script to migrate from version 1.1's flat file based +# users to version 1.2's central database. +# +# NOTE: This script will die if the user it tries to import is +# tied to a bogus or non-existing dataset/database. The fix +# is to edit the members file and either manually set the right +# database configuration for that user, or delete the user entirely. +# +# If the script makes it through all users it will print: +# +# 'SUCCESS! It seems that every user in the members file was imported!' +# +# If you don't get that output at the end of the script, something +# went wrong in the import. +# +# For help/troubleshooting please join the LedgerSMB users mailing +# list. Info on how to subscribe can be found here: +# +# http://lists.sourceforge.net/mailman/listinfo/ledger-smb-users +# +# Other info on how to get help, including commercial support +# can be found at: +# +# http://www.ledgersmb.org/help/ +# + +use LedgerSMB::User; +use LedgerSMB::Form; +use LedgerSMB::Sysconfig; + +if($ENV{HTTP_HOST}){ + print "Content-type: text/html\n\n"; + print "\n"; + print "This script cannot be executed via http. You must run it via the command line.\n"; + print "\n"; + exit; +} + +my $membersfile = 'users/members'; + +my @users = (); + +open(FH, "$membersfile") || die ("Couldn't open members file!"); + +while () { + + chop; + + if (/^\[.*\]/) { + $login = $_; + $login =~ s/(\[|\])//g; + + if($login eq 'admin'){ + + print "\nIMPORT FAILED: User 'admin' was found.\n\n"; + print "Please change this user's name to something else. In LedgerSMB version 1.2, \n"; + print "'admin' is a reserved user for the administration of the entire system.\n"; + print "To change the user's name, find the line in the members file that looks \n"; + print "like [admin] and change 'admin' to something else (keep the '[' and ']').\n"; + print "Save the file and run this script again.\n\n"; + exit; + + } elsif($login ne 'root login'){ + push @users, $login; + $member{$login}{'login'} = $login; + } + next; + } + + if($login ne 'root login'){ + if( ($key, $value) = split /=/, $_, 2){ + if($key eq 'dbpasswd'){ + $member{$login}{$key} = unpack 'u', $value; + } elsif($key eq 'password') { + $member{$login}{'crypted_password'} = $value; + } else { + $member{$login}{$key} = $value; + } + } + } +} + +close(FH); + +foreach (@users) { + + $myUser = $member{$_}; + &save_member($myUser); + print "Import of user '$_' seems to have succeeded.\n"; + +} + +print "\nSUCCESS! It seems that every user in the members file was imported!\n\n"; + +sub save_member { + + # a slightly modified version of LegerSBM::User::save_member + # with special handling of the password -> crypted_password + + my ($self) = @_; + + # replace \r\n with \n + for (qw(address signature)) { $self->{$_} =~ s/\r?\n/\\n/g } + + # use central db + my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH}; + + #check to see if the user exists already + my $userCheck = $dbh->prepare("SELECT id FROM users WHERE username = ?"); + $userCheck->execute($self->{login}); + my ($userID) = $userCheck->fetchrow_array; + + if($userID){ + #got an id, check to see if it's in the users_conf table + my $userConfCheck = $dbh->prepare("SELECT id FROM users_conf WHERE id = ?"); + $userConfCheck->execute($userID); + + if($userConfCheck->rows){ + my $userConfExists = 1; + } + } + else{ + my $userConfAdd = $dbh->prepare("SELECT create_user(?);"); + $userConfAdd->execute($self->{login}); + ($userID) = $userConfAdd->fetchrow_array; + } + + if($userConfExists){ + + my $userConfUpdate = $dbh->prepare("UPDATE users_conf + SET acs = ?, address = ?, businessnumber = ?, + company = ?, countrycode = ?, currency = ?, + dateformat = ?, dbdriver = ?, + dbhost = ?, dbname = ?, dboptions = ?, + dbpasswd = ?, dbport = ?, dbuser = ?, + email = ?, fax = ?, menuwidth = ?, + name = ?, numberformat = ?, crypted_password = ?, + print = ?, printer = ?, role = ?, + sid = ?, signature = ?, stylesheet = ?, + tel = ?, templates = ?, timeout = ?, + vclimit = ? + WHERE id = ?;"); + + $userConfUpdate->execute($self->{acs}, $self->{address}, $self->{businessnumber}, + $self->{company}, $self->{countrycode}, $self->{currency}, + $self->{dateformat}, $self->{dbdriver}, + $self->{dbhost}, $self->{dbname}, $self->{dboptions}, + $self->{dbpasswd}, $self->{dbport}, $self->{dbuser}, + $self->{email}, $self->{fax}, $self->{menuwidth}, + $self->{name}, $self->{numberformat}, $self->{crypted_password}, + $self->{print}, $self->{printer}, $self->{role}, + $self->{sid}, $self->{signature}, $self->{stylesheet}, + $self->{tel}, $self->{templates}, $self->{timeout}, + $self->{vclimit}, $userID); + + + } + else{ + + my $userConfInsert = $dbh->prepare("INSERT INTO users_conf(acs, address, businessnumber, + company, countrycode, currency, + dateformat, dbdriver, + dbhost, dbname, dboptions, dbpasswd, + dbport, dbuser, email, fax, menuwidth, + name, numberformat, print, printer, role, + sid, signature, stylesheet, tel, templates, + timeout, vclimit, id, crypted_password) + VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"); + + $userConfInsert->execute($self->{acs}, $self->{address}, $self->{businessnumber}, + $self->{company}, $self->{countrycode}, $self->{currency}, + $self->{dateformat}, $self->{dbdriver}, + $self->{dbhost}, $self->{dbname}, $self->{dboptions}, + $self->{dbpasswd}, $self->{dbport}, $self->{dbuser}, + $self->{email}, $self->{fax}, $self->{menuwidth}, + $self->{name}, $self->{numberformat}, + $self->{print}, $self->{printer}, $self->{role}, + $self->{sid}, $self->{signature}, $self->{stylesheet}, + $self->{tel}, $self->{templates}, $self->{timeout}, + $self->{vclimit}, $userID, $self->{crypted_password}); + + } + + if (! $self->{'admin'}) { + + $self->{dbpasswd} =~ s/\\'/'/g; + $self->{dbpasswd} =~ s/\\\\/\\/g; + + # format dbconnect and dboptions string + LedgerSMB::User::dbconnect_vars($self, $self->{dbname}); + + # check if login is in database + my $dbh = DBI->connect( + $self->{dbconnect}, $self->{dbuser}, $self->{dbpasswd}, + {AutoCommit => 0}) + or $self->error($DBI::errstr); + + # add login to employee table if it does not exist + my $login = $self->{login}; + $login =~ s/@.*//; + my $query = qq|SELECT id FROM employee WHERE login = '$login'|; + my $sth = $dbh->prepare($query); + $sth->execute; + + my ($id) = $sth->fetchrow_array; + $sth->finish; + my $employeenumber; + my @values; + if ($id) { + + $query = qq|UPDATE employee SET + role = ?, + email = ?, + name = ? + WHERE login = ?|; + + @values = ($self->{role}, $self->{email}, $self->{name}, $login); + + } else { + + my ($employeenumber) = Form::update_defaults( + "", \%$self, "employeenumber", $dbh); + $query = qq| + INSERT INTO employee + (login, employeenumber, name, + workphone, role, email, sales) + VALUES (?, ?, ?, ?, ?, ?, '1')|; + + @values = ($login, $employeenumber, $self->{name}, $self->{tel}, + $self->{role}, $self->{email}) + } + + $sth = $dbh->prepare($query); + $sth->execute(@values); + $dbh->commit; + $dbh->disconnect; + + } +} -- cgit v1.2.3