import_members.pl



#!/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 = 'users/members';

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);

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;

    }
}