#=====================================================================
# LX-Office ERP
# Copyright (C) 2004
# Based on SQL-Ledger Version 2.1.9
# Web http://www.lx-office.org
#
#=====================================================================
# SQL-Ledger Accounting
# Copyright (C) 2001
#
#  Author: Dieter Simader
#   Email: dsimader@sql-ledger.org
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#=====================================================================
#
# user related functions
#
#=====================================================================

package User;

use IO::File;
use Fcntl qw(:seek);

#use SL::Auth;
use SL::DBConnect;
use SL::DBUpgrade2;
use SL::DBUtils;
use SL::Iconv;
use SL::Inifile;
use SL::System::InstallationLock;

use strict;

sub new {
  $main::lxdebug->enter_sub();

  my ($type, %params) = @_;

  my $self = {};

  if ($params{id} || $params{login}) {
    my %user_data = $main::auth->read_user(%params);
    map { $self->{$_} = $user_data{$_} } keys %user_data;
  }

  $main::lxdebug->leave_sub();

  bless $self, $type;
}

sub country_codes {
  $main::lxdebug->enter_sub();

  local *DIR;

  my %cc       = ();
  my @language = ();

  # scan the locale directory and read in the LANGUAGE files
  opendir(DIR, "locale");

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

  foreach my $dir (@dir) {
    next unless open(my $fh, '<:encoding(UTF-8)', "locale/$dir/LANGUAGE");
    @language = <$fh>;
    close $fh;

    $cc{$dir} = "@language";
  }

  closedir(DIR);

  $main::lxdebug->leave_sub();

  return %cc;
}

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

  return -3 if !$self->{login} || !$::auth->client;

  my %myconfig = $main::auth->read_user(login => $self->{login});

  # check if database is down
  my $dbh = $form->dbconnect_noauto;

  # we got a connection, check the version
  my ($dbversion) = $dbh->selectrow_array(qq|SELECT version FROM defaults|);

  $self->create_employee_entry($form, $dbh, \%myconfig);

  $self->create_schema_info_table($form, $dbh);

  # Auth DB upgrades available?
  my $dbupdater_auth = SL::DBUpgrade2->new(form => $form, auth => 1)->parse_dbupdate_controls;
  return -3 if $dbupdater_auth->unapplied_upgrade_scripts($::auth->dbconnect);

  my $dbupdater = SL::DBUpgrade2->new(form => $form)->parse_dbupdate_controls;

  $form->{$_} = $::auth->client->{$_} for qw(dbname dbhost dbport dbuser dbpasswd);
  $form->{$_} = $myconfig{$_}         for qw(dateformat);

  dbconnect_vars($form, $form->{dbname});

  my $update_available = $dbupdater->update_available($dbversion) || $dbupdater->update2_available($dbh);
  $dbh->disconnect;

  return 0 if !$update_available;
  $form->{"title"} = $main::locale->text("Dataset upgrade");
  $form->header(no_layout => $form->{no_layout});
  print $form->parse_html_template("dbupgrade/header");

  $form->{dbupdate} = "db" . $form->{dbname};

  if ($form->{"show_dbupdate_warning"}) {
    print $form->parse_html_template("dbupgrade/warning");
    ::end_of_request();
  }

  # update the tables
  SL::System::InstallationLock->lock;

  # ignore HUP, QUIT in case the webserver times out
  $SIG{HUP}  = 'IGNORE';
  $SIG{QUIT} = 'IGNORE';

  $self->dbupdate($form);
  $self->dbupdate2($form, $dbupdater);
  SL::DBUpgrade2->new(form => $::form, auth => 1)->apply_admin_dbupgrade_scripts(0);

  SL::System::InstallationLock->unlock;

  print $form->parse_html_template("dbupgrade/footer");

  return -2;
}

sub dbconnect_vars {
  $main::lxdebug->enter_sub();

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

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

  $form->{dboptions} = $dboptions{ $form->{dateformat} };
  $form->{dbconnect} = "dbi:Pg:dbname=${db};host=" . ($form->{dbhost} || 'localhost') . ";port=" . ($form->{dbport} || 5432);

  $main::lxdebug->leave_sub();
}

sub dbsources {
  $main::lxdebug->enter_sub();

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

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

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

  my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options)
    or $form->dberror;

  $query =
    qq|SELECT datname FROM pg_database | .
    qq|WHERE NOT datname IN ('template0', 'template1')|;
  $sth = $dbh->prepare($query);
  $sth->execute() || $form->dberror($query);

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

    if ($form->{only_acc_db}) {

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

      &dbconnect_vars($form, $db);
      my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options)
        or $form->dberror;

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

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

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

  $main::lxdebug->leave_sub();

  return @dbsources;
}

sub dbclusterencoding {
  $main::lxdebug->enter_sub();

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

  $form->{dbdefault} ||= $form->{dbuser};

  dbconnect_vars($form, $form->{dbdefault});

  my $dbh                = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options) || $form->dberror();
  my $query              = qq|SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'template0'|;
  my ($cluster_encoding) = $dbh->selectrow_array($query);
  $dbh->disconnect();

  $main::lxdebug->leave_sub();

  return $cluster_encoding;
}

sub dbcreate {
  $main::lxdebug->enter_sub();

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

  &dbconnect_vars($form, $form->{dbdefault});
  my $dbh =
    SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options)
    or $form->dberror;
  $form->{db} =~ s/\"//g;

  my @dboptions;

  push @dboptions, "ENCODING = " . $dbh->quote($form->{"encoding"}) if $form->{"encoding"};
  if ($form->{"dbdefault"}) {
    my $dbdefault = $form->{"dbdefault"};
    $dbdefault =~ s/[^a-zA-Z0-9_\-]//g;
    push @dboptions, "TEMPLATE = $dbdefault";
  }

  my $query = qq|CREATE DATABASE "$form->{db}"|;
  $query   .= " WITH " . join(" ", @dboptions) if @dboptions;

  # Ignore errors if the database exists.
  $dbh->do($query);

  $dbh->disconnect;

  &dbconnect_vars($form, $form->{db});

  $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options)
    or $form->dberror;

  my $db_charset = $Common::db_encoding_to_charset{$form->{encoding}};
  $db_charset ||= Common::DEFAULT_CHARSET;

  my $dbupdater = SL::DBUpgrade2->new(form => $form);
  # create the tables
  $dbupdater->process_query($dbh, "sql/lx-office.sql", undef, $db_charset);

  # load chart of accounts
  $dbupdater->process_query($dbh, "sql/$form->{chart}-chart.sql", undef, $db_charset);

  $query = "UPDATE defaults SET coa = ?";
  do_query($form, $dbh, $query, $form->{chart});
  $query = "UPDATE defaults SET accounting_method = ?";
  do_query($form, $dbh, $query, $form->{accounting_method});
  $query = "UPDATE defaults SET profit_determination = ?";
  do_query($form, $dbh, $query, $form->{profit_determination});
  $query = "UPDATE defaults SET inventory_system = ?";
  do_query($form, $dbh, $query, $form->{inventory_system});
  $query = "UPDATE defaults SET curr = ?";
  do_query($form, $dbh, $query, $form->{defaultcurrency});

  $dbh->disconnect;

  $main::lxdebug->leave_sub();
}

sub dbdelete {
  $main::lxdebug->enter_sub();

  my ($self, $form) = @_;
  $form->{db} =~ s/\"//g;

  &dbconnect_vars($form, $form->{dbdefault});
  my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options)
    or $form->dberror;
  my $query = qq|DROP DATABASE "$form->{db}"|;
  do_query($form, $dbh, $query);

  $dbh->disconnect;

  $main::lxdebug->leave_sub();
}

sub dbsources_unused {
  $main::lxdebug->enter_sub();

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

  $form->{only_acc_db} = 1;

  my %members = $main::auth->read_all_users();
  my %dbexcl  = map { $_ => 1 } grep { $_ } map { $_->{dbname} } values %members;

  $dbexcl{$form->{dbdefault}}             = 1;
  $dbexcl{$main::auth->{DB_config}->{db}} = 1;

  my @dbunused = grep { !$dbexcl{$_} } dbsources("", $form);

  $main::lxdebug->leave_sub();

  return @dbunused;
}

sub dbneedsupdate {
  $main::lxdebug->enter_sub();

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

  my %members   = $main::auth->read_all_users();
  my $dbupdater = SL::DBUpgrade2->new(form => $form)->parse_dbupdate_controls;

  my ($query, $sth, %dbs_needing_updates);

  foreach my $login (grep /[a-z]/, keys %members) {
    my $member = $members{$login};

    map { $form->{$_} = $member->{$_} } qw(dbname dbuser dbpasswd dbhost dbport);
    dbconnect_vars($form, $form->{dbname});

    my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options);

    next unless $dbh;

    my $version;

    $query = qq|SELECT version FROM defaults|;
    $sth = prepare_query($form, $dbh, $query);
    if ($sth->execute()) {
      ($version) = $sth->fetchrow_array();
    }
    $sth->finish();

    $dbh->disconnect and next unless $version;

    my $update_available = $dbupdater->update_available($version) || $dbupdater->update2_available($dbh);
    $dbh->disconnect;

   if ($update_available) {
      my $dbinfo = {};
      map { $dbinfo->{$_} = $member->{$_} } grep /^db/, keys %{ $member };
      $dbs_needing_updates{$member->{dbhost} . "::" . $member->{dbname}} = $dbinfo;
    }
  }

  $main::lxdebug->leave_sub();

  return values %dbs_needing_updates;
}

sub calc_version {
  $main::lxdebug->enter_sub(2);

  my (@v, $version, $i);

  @v = split(/\./, $_[0]);
  while (scalar(@v) < 4) {
    push(@v, 0);
  }
  $version = 0;
  for ($i = 0; $i < 4; $i++) {
    $version *= 1000;
    $version += $v[$i];
  }

  $main::lxdebug->leave_sub(2);
  return $version;
}

sub cmp_script_version {
  my ($a_from, $a_to, $b_from, $b_to);
  my ($i, $res_a, $res_b);
  my ($my_a, $my_b) = ($a, $b);

  $my_a =~ s/.*-upgrade-//;
  $my_a =~ s/.sql$//;
  $my_b =~ s/.*-upgrade-//;
  $my_b =~ s/.sql$//;
  my ($my_a_from, $my_a_to) = split(/-/, $my_a);
  my ($my_b_from, $my_b_to) = split(/-/, $my_b);

  $res_a = calc_version($my_a_from);
  $res_b = calc_version($my_b_from);

  if ($res_a == $res_b) {
    $res_a = calc_version($my_a_to);
    $res_b = calc_version($my_b_to);
  }

  return $res_a <=> $res_b;
}

sub create_schema_info_table {
  $main::lxdebug->enter_sub();

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

  my $query = "SELECT tag FROM schema_info LIMIT 1";
  if (!$dbh->do($query)) {
    $dbh->rollback();
    $query =
      qq|CREATE TABLE schema_info (| .
      qq|  tag text, | .
      qq|  login text, | .
      qq|  itime timestamp DEFAULT now(), | .
      qq|  PRIMARY KEY (tag))|;
    $dbh->do($query) || $form->dberror($query);
  }

  $main::lxdebug->leave_sub();
}

sub dbupdate {
  $main::lxdebug->enter_sub();

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

  local *SQLDIR;

  my @upgradescripts = ();
  my $query;
  my $rc = -2;

  if ($form->{dbupdate}) {

    # read update scripts into memory
    opendir(SQLDIR, "sql/Pg-upgrade")
      or &error("", "sql/Pg-upgrade : $!");
    @upgradescripts =
      sort(cmp_script_version
           grep(/Pg-upgrade-.*?\.(sql|pl)$/,
                readdir(SQLDIR)));
    closedir(SQLDIR);
  }

  my $db_charset = $::lx_office_conf{system}->{dbcharset};
  $db_charset ||= Common::DEFAULT_CHARSET;

  my $dbupdater = SL::DBUpgrade2->new(form => $form);

  foreach my $db (split(/ /, $form->{dbupdate})) {

    next unless $form->{$db};

    # strip db from dataset
    $db =~ s/^db//;
    &dbconnect_vars($form, $db);

    my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options)
      or $form->dberror;

    $dbh->do($form->{dboptions}) if ($form->{dboptions});

    # check version
    $query = qq|SELECT version FROM defaults|;
    my ($version) = selectrow_query($form, $dbh, $query);

    next unless $version;

    $version = calc_version($version);

    foreach my $upgradescript (@upgradescripts) {
      my $a = $upgradescript;
      $a =~ s/^Pg-upgrade-|\.(sql|pl)$//g;

      my ($mindb, $maxdb) = split /-/, $a;
      my $str_maxdb = $maxdb;
      $mindb = calc_version($mindb);
      $maxdb = calc_version($maxdb);

      next if ($version >= $maxdb);

      # if there is no upgrade script exit
      last if ($version < $mindb);

      # apply upgrade
      $main::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $upgradescript");
      $dbupdater->process_file($dbh, "sql/Pg-upgrade/$upgradescript", $str_maxdb, $db_charset);

      $version = $maxdb;

    }

    $rc = 0;
    $dbh->disconnect;

  }

  $main::lxdebug->leave_sub();

  return $rc;
}

sub dbupdate2 {
  $main::lxdebug->enter_sub();

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

  my $rc         = -2;
  my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;

  map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $dbupdater->{all_controls} };

  foreach my $db (split / /, $form->{dbupdate}) {
    next unless $form->{$db};

    # strip db from dataset
    $db =~ s/^db//;
    &dbconnect_vars($form, $db);

    my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options) or $form->dberror;

    $dbh->do($form->{dboptions}) if ($form->{dboptions});

    $self->create_schema_info_table($form, $dbh);

    my @upgradescripts = $dbupdater->unapplied_upgrade_scripts($dbh);

    $dbh->disconnect and next if !@upgradescripts;

    foreach my $control (@upgradescripts) {
      # apply upgrade
      $main::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
      print $form->parse_html_template("dbupgrade/upgrade_message2", $control);

      $dbupdater->process_file($dbh, "sql/Pg-upgrade2/$control->{file}", $control, $db_charset);
    }

    $rc = 0;
    $dbh->disconnect;

  }

  $main::lxdebug->leave_sub();

  return $rc;
}

sub save_member {
  $main::lxdebug->enter_sub();

  my ($self) = @_;

  # format dbconnect and dboptions string
  dbconnect_vars($self, $self->{dbname});

  map { $self->{$_} =~ s/\r//g; } qw(address signature);

  $main::auth->save_user($self->{login}, map { $_, $self->{$_} } config_vars());

  my $dbh = SL::DBConnect->connect($self->{dbconnect}, $self->{dbuser}, $self->{dbpasswd}, SL::DBConnect->get_options);
  if ($dbh) {
    $self->create_employee_entry($::form, $dbh, $self, 1);
    $dbh->disconnect();
  }

  $main::lxdebug->leave_sub();
}

sub create_employee_entry {
  $main::lxdebug->enter_sub();

  my $self            = shift;
  my $form            = shift;
  my $dbh             = shift;
  my $myconfig        = shift;
  my $update_existing = shift;

  if (!does_table_exist($dbh, 'employee')) {
    $main::lxdebug->leave_sub();
    return;
  }

  # add login to employee table if it does not exist
  # no error check for employee table, ignore if it does not exist
  my ($id)         = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $self->{login});
  my ($good_db)    = selectrow_query($form, $dbh, qq|select * from pg_tables where tablename = ? and schemaname = ?|, 'schema_info', 'public');
  my  $can_delete;
     ($can_delete) = selectrow_query($form, $dbh, qq|SELECT tag FROM schema_info WHERE tag = ?|, 'employee_deleted') if $good_db;

  if (!$id) {
    my $query = qq|INSERT INTO employee (login, name, workphone, role) VALUES (?, ?, ?, ?)|;
    do_query($form, $dbh, $query, ($self->{login}, $myconfig->{name}, $myconfig->{tel}, "user"));

  } elsif ($update_existing && $can_delete) {
    my $query = qq|UPDATE employee SET name = ?, workphone = ?, role = 'user', deleted = 'f' WHERE id = ?|;
    do_query($form, $dbh, $query, $myconfig->{name}, $myconfig->{tel}, $id);
  }

  $main::lxdebug->leave_sub();
}

sub config_vars {
  $main::lxdebug->enter_sub();

  my @conf = qw(copies countrycode dateformat default_media default_printer_id email favorites fax hide_cvar_search_options mandatory_departments menustyle name
                numberformat show_form_details signature stylesheet taxincluded_checked tel template_format vclimit);

  $main::lxdebug->leave_sub();

  return @conf;
}

sub data {
  +{ %{ $_[0] } }
}

1;