#!/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);
$dbh->{pg_enable_utf8} = 1;
# 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;
}
}