#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2000
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors: Jim Rawlings <jim@your-dba.com>
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# user related functions
#
#=====================================================================

package LedgerSMB::User;
use LedgerSMB::Sysconfig;
use LedgerSMB::Session;
use Data::Dumper;

sub new {

    my ( $type, $login ) = @_;
    my $self = {};

    if ( $login ne "" ) {

        # use central db
        my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH};

        # for now, this is querying the table directly... ugly
        my $fetchUserPrefs = $dbh->prepare(
            "SELECT acs, address, businessnumber,
                                                   company, countrycode, currency,
                                                   dateformat, dbdriver, dbhost, dbname, 
                                                   dboptions, dbpasswd, dbport, dbuser, 
                                                   email, fax, menuwidth, name, numberformat, 
                                                   password, print, printer, role, sid, 
                                                   signature, stylesheet, tel, templates, 
                                                   timeout, vclimit, u.username
                                              FROM users_conf as uc, users as u
                                             WHERE u.username =  ?
                                               AND u.id = uc.id;"
        );

        $fetchUserPrefs->execute($login);

        my $userHashRef = $fetchUserPrefs->fetchrow_hashref;

        while ( my ( $key, $value ) = each( %{$userHashRef} ) ) {
            $self->{$key} = $value;
        }

        chomp( $self->{dbport} );
        chomp( $self->{dbname} );
        chomp( $self->{dbhost} );

        $self->{dbconnect} =
            'dbi:Pg:dbname='
          . $self->{dbname}
          . ';host='
          . $self->{dbhost}
          . ';port='
          . $self->{dbport};

        if ( $self->{username} ) {
            $self->{login} = $login;
        }
    }

    bless $self, $type;
}

sub country_codes {
    use Locale::Country;
    use Locale::Language;

    my %cc = ();

    # scan the locale directory and read in the LANGUAGE files
    opendir DIR, "${LedgerSMB::Sysconfig::localepath}";

    my @dir = grep !/^\..*$/, readdir DIR;

    foreach my $dir (@dir) {
        $dir = substr( $dir, 0, -3 );
        $cc{$dir} = code2language( substr( $dir, 0, 2 ) );
        $cc{$dir} .= ( "/" . code2country( substr( $dir, 3, 2 ) ) )
          if length($dir) > 2;
        $cc{$dir} .= ( " " . substr( $dir, 6 ) ) if length($dir) > 5;
    }

    closedir(DIR);

    %cc;

}

sub fetch_config {

    #I'm hoping that this function will go and is a temporary bridge
    #until we get rid of %myconfig elsewhere in the code

    my ( $self, $login ) = @_;

    if ( !$login ) {
        &error( $self, "Access Denied" );
    }

    # use central db
    my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH};

    # for now, this is querying the table directly... ugly
    my $fetchUserPrefs = $dbh->prepare(
        "SELECT acs, address, businessnumber,
                                               company, countrycode, currency,
                                               dateformat, dbdriver, dbhost, dbname, 
                                               dboptions, dbpasswd, dbport, dbuser, 
                                               email, fax, menuwidth, name, numberformat, 
                                               password, print, printer, role, sid, 
                                               signature, stylesheet, tel, templates, 
                                               timeout, vclimit, u.username
                                          FROM users_conf as uc, users as u
                                         WHERE u.username =  ?
                                           AND u.id = uc.id;"
    );

    $fetchUserPrefs->execute($login);

    my $userHashRef = $fetchUserPrefs->fetchrow_hashref;
    if ( !$userHashRef ) {
        &error( $self, "Access Denied" );
    }

    while ( my ( $key, $value ) = each( %{$userHashRef} ) ) {
        $myconfig{$key} = $value;
    }

    chomp( $myconfig{'dbport'} );
    chomp( $myconfig{'dbname'} );
    chomp( $myconfig{'dbhost'} );

    $myconfig{'login'} = $login;
    $myconfig{'dbconnect'} =
        'dbi:Pg:dbname='
      . $myconfig{'dbname'}
      . ';host='
      . $myconfig{'dbhost'}
      . ';port='
      . $myconfig{'dbport'};

    return \%myconfig;
}

sub login {

    my ( $self, $form ) = @_;

    my $rc = -1;

    if ( $self->{login} ne "" ) {
        if (
            !Session::password_check(
                $form, $form->{login}, $form->{password}
            )
          )
        {
            return -1;
        }

        #this is really dumb, but %myconfig will have to stay until 1.3
        while ( my ( $key, $value ) = each( %{$self} ) ) {
            $myconfig{$key} = $value;
        }

        # check if database is down
        my $dbh =
          DBI->connect( $myconfig{dbconnect}, $myconfig{dbuser},
            $myconfig{dbpasswd} )
          or $self->error( __FILE__ . ':' . __LINE__ . ': ' . $DBI::errstr );
        $dbh->{pg_enable_utf8} = 1;

        # we got a connection, check the version
        my $query = qq|
            SELECT value FROM defaults 
             WHERE setting_key = 'version'|;
        my $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . $query );

        my ($dbversion) = $sth->fetchrow_array;
        $sth->finish;

        # add login to employee table if it does not exist
        # no error check for employee table, ignore if it does not exist
        my $login = $self->{login};
        $login =~ s/@.*//;
        $query = qq|SELECT id FROM employee WHERE login = ?|;
        $sth   = $dbh->prepare($query);
        $sth->execute($login);

        my ($id) = $sth->fetchrow_array;
        $sth->finish;

        if ( !$id ) {
            my ($employeenumber) =
              $form->update_defaults( \%myconfig, "employeenumber", $dbh );

            $query = qq|
                INSERT INTO employee 
                            (login, employeenumber, name, 
                            workphone, role)
                     VALUES (?, ?, ?, ?, ?)|;
            $sth = $dbh->prepare($query);
            $sth->execute(
                $login,         $employeenumber, $myconfig{name},
                $myconfig{tel}, $myconfig{role}
            );
        }
        $dbh->disconnect;

        $rc = 0;

        if ( $form->{dbversion} ne $dbversion ) {
            $rc = -3;
            $dbupdate =
              ( calc_version($dbversion) < calc_version( $form->{dbversion} ) );
        }

        if ($dbupdate) {
            $rc = -4;

            # if DB2 bale out
            if ( $myconfig{dbdriver} eq 'DB2' ) {
                $rc = -2;
            }
        }
    }

    $rc;

}

sub check_recurring {
    my ( $self, $form ) = @_;

    my $dbh =
      DBI->connect( $self->{dbconnect}, $self->{dbuser}, $self->{dbpasswd} )
      or $form->dberror( __FILE__ . ':' . __LINE__ );
    $dbh->{pg_enable_utf8} = 1;

    my $query = qq|
        SELECT count(*) FROM recurring
         WHERE enddate >= current_date AND nextdate <= current_date|;
    ($_) = $dbh->selectrow_array($query);

    $dbh->disconnect;

    $_;

}

sub dbconnect_vars {
    my ( $form, $db ) = @_;

    my %dboptions = (
        'Pg' => {
            'yy-mm-dd' => 'set DateStyle to \'ISO\'',
            'mm/dd/yy' => 'set DateStyle to \'SQL, US\'',
            'mm-dd-yy' => 'set DateStyle to \'POSTGRES, US\'',
            'dd/mm/yy' => 'set DateStyle to \'SQL, EUROPEAN\'',
            'dd-mm-yy' => 'set DateStyle to \'POSTGRES, EUROPEAN\'',
            'dd.mm.yy' => 'set DateStyle to \'GERMAN\''
        }
    );

    $form->{dboptions} = $dboptions{ $form->{dbdriver} }{ $form->{dateformat} };

    $form->{dbconnect} = "dbi:$form->{dbdriver}:dbname=$db";
    $form->{dbconnect} .= ";host=$form->{dbhost}";
    $form->{dbconnect} .= ";port=$form->{dbport}";

}

sub dbdrivers {

    my @drivers = DBI->available_drivers();

    #  return (grep { /(Pg|Oracle|DB2)/ } @drivers);
    return ( grep { /Pg$/ } @drivers );

}

sub dbsources {
    my ( $self, $form ) = @_;

    my @dbsources = ();
    my ( $sth, $query );

    $form->{dbdefault} = $form->{dbuser} unless $form->{dbdefault};
    $form->{sid} = $form->{dbdefault};
    &dbconnect_vars( $form, $form->{dbdefault} );

    my $dbh =
      DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd} )
      or $form->dberror( __FILE__ . ':' . __LINE__ );
    $dbh->{pg_enable_utf8} = 1;

    if ( $form->{dbdriver} eq 'Pg' ) {

        $query = qq|SELECT datname FROM pg_database|;
        $sth   = $dbh->prepare($query);
        $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . $query );

        while ( my ($db) = $sth->fetchrow_array ) {

            if ( $form->{only_acc_db} ) {

                next if ( $db =~ /^template/ );

                &dbconnect_vars( $form, $db );
                my $dbh =
                  DBI->connect( $form->{dbconnect}, $form->{dbuser},
                    $form->{dbpasswd} )
                  or $form->dberror( __FILE__ . ':' . __LINE__ );
                $dbh->{pg_enable_utf8} = 1;

                $query = qq|
                    SELECT tablename FROM pg_tables
                     WHERE tablename = 'defaults'
                       AND tableowner = ?|;
                my $sth = $dbh->prepare($query);
                $sth->execute( $form->{dbuser} )
                  || $form->dberror( __FILE__ . ':' . __LINE__ . $query );

                if ( $sth->fetchrow_array ) {
                    push @dbsources, $db;
                }
                $sth->finish;
                $dbh->disconnect;
                next;
            }
            push @dbsources, $db;
        }
    }

    $sth->finish;
    $dbh->disconnect;

    return @dbsources;

}

sub dbcreate {
    my ( $self, $form ) = @_;

    my %dbcreate =