#!/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 "<html>\n";
print "<strong>This script cannot be executed via http. You must run it via the command line.</strong>\n";
print "</html>\n";
exit;
}
my $membersfile = $ARGV[0];
if (length($membersfile) < 2){
print "\nUsage: import_members.pl path/to/members\n\n";
print "You must supply the path to the members file. Default location\n";
print "is users/members. In this case do this:\n\n";
print " ./import_members.pl users/members\n\n";
exit;
}
my @users = ();
open(FH, "$membersfile") || die ("Couldn't open members file!");
while (<FH>) {
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);
print "\n\nParsing members file completed. Now trying to import user data.\n\n";
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 count(*) FROM users_conf WHERE id = ?");
$userConfCheck->execute($userID);
($userConfExists) = $userConfCheck->fetchrow_array;
}
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 $sth = $dbh->prepare("SELECT id FROM employee WHERE login = ?;");
$sth->execute($login);
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;
}
}