projekt kivitendo

  'All groups'                  => 'Alle Gruppen',

  'Client #1'                   => 'Mandant #1',

  'Client name'                 => 'Mandantenname',

  'Company name'                => 'Firmenname',

  'Create new client #1'        => 'Neuen Mandanten #1 anlegen',

  'Database settings'           => 'Datenbankeinstellungen',

  'Error message from the database: #1' => 'Fehlermeldung der Datenbank: #1',

  'General settings'            => 'Allgemeine Einstellungen',

  'Groups that are valid for this client for access rights' => 'Gruppen, die für diesen Mandanten gültig sind',

  'Groups valid for this client' => 'Für Mandanten gültige Gruppen',

  'Introduction of clients'     => 'Einführung von Mandanten',

  'It can be changed later but must be unique within the installation.' => 'Er ist nachträglich änderbar, muss aber im System eindeutig sein.',

  'New client #1: The database configuration fields "host", "port", "name" and "user" must not be empty.' => 'Neuer Mandant #1: Die Datenbankkonfigurationsfelder "Host", "Port" und "Name" dürfen nicht leer sein.',

  'New client #1: The name must be unique and not empty.' => 'Neuer Mandant #1: Der Name darf nicht leer und muss eindeutig sein.',

  'Only groups that have been configured for the client the user logs in to will be considered.' => 'Allerdings werden nur diejenigen Gruppen herangezogen, die für den Mandanten konfiguriert sind.',

  'Please correct the settings and try again or deactivate that client.' => 'Bitte korrigieren Sie die Einstellungen und versuchen Sie es erneut, oder deaktivieren Sie diesen Mandanten.',

  'Please select which client configurations you want to create.' => 'Bitte wählen Sie aus, welche Mandanten mit welchen Einstellungen angelegt werden sollen.',

  'Print templates'             => 'Druckvorlagen',

  'The \'name\' is the field shown to the user during login.' => 'Der \'Name\' ist derjenige, der dem Benutzer beim Login angezeigt wird.',

  'The access rights a user has within a client instance is still governed by his group membership.' => 'Welche Zugriffsrechte ein Benutzer innerhalb eines Mandanten hat, wird weiterhin über Gruppenmitgliedschaften geregelt.',

  'The connection to the configured client database "#1" on host "#2:#3" failed.' => 'Die Verbindung zur konfigurierten Datenbank "#1" auf Host "#2:#3" schlug fehl.',

  'The following list has been generated automatically from existing users collapsing users with identical settings into a single entry.' => 'Die folgende Liste wurde automatisch aus den im System vorhandenen Benutzern zusammengestellt, wobei identische Einstellungen zu einem Eintrag zusammengefasst wurden.',

  'The user can chose which client to connect to during login.' => 'Bei der Anmeldung kann der Benutzer auswählen, welchen Mandanten er benutzen möchte.',

  'Therefore several settings that had to be made for each user in the past have been consolidated into the client configuration.' => 'Dazu wurden gewisse Einstellungen, die vorher bei jedem Benutzer vorgenommen werden mussten, in die Konfiguration eines Mandanten verschoben.',

  'Updating the client fields in the database "#1" on host "#2:#3" failed.' => 'Die Aktualisierung der Mandantenfelder in der Datenbank "#1" auf Host "#2:#3" schlug fehl.',

  'User access'                 => 'Benutzerzugriff',

  'User data migration'         => 'Benutzerdatenmigration',

  'Users with access'           => 'Benutzer mit Zugriff',

  'Users with access to this client' => 'Benutzer mit Zugriff auf diesen Mandanten',

  'VAT ID'                      => 'UStdID-Nr',

  'You have to grant users access to one or more clients.' => 'Benutzern muss dann Zugriff auf einzelne Mandanten gewährt werden.',

  'kivitendo has been extended to handle multiple clients within a single installation.' => 'kivitendo wurde um Mandantenfähigkeit erweitert.',

  'kivitendo has been switched to group-based access restrictions.' => 'kivitendo wurde auf eine gruppenbasierte Benutzerzugriffsverwaltung umgestellt.',

# @tag: clients

# @description: Einführung von Mandaten

# @depends: release_3_0_0

# @ignore: 0

package SL::DBUpgrade2::clients;

use strict;

use utf8;

use parent qw(SL::DBUpgrade2::Base);

use List::MoreUtils qw(any all);

use List::Util qw(first);

use SL::DBConnect;

use SL::DBUtils;

use SL::Template;

use SL::Helper::Flash;

use Rose::Object::MakeMethods::Generic (

  scalar                  => [ qw(clients) ],

  'scalar --get_set_init' => [ qw(users groups templates auth_db_settings data_dbhs) ],

);

sub init_users {

  my ($self) = @_;

  my @users  = selectall_hashref_query($::form, $self->dbh, qq|SELECT * FROM auth."user" ORDER BY lower(login)|);

  foreach my $user (@users) {

    my @attributes = selectall_hashref_query($::form, $self->dbh, <<SQL, $user->{id});

     SELECT cfg_key, cfg_value

     FROM auth.user_config

     WHERE user_id = ?

SQL

    $user->{ $_->{cfg_key} } = $_->{cfg_value} for @attributes;

  }

  return \@users;

}

sub init_groups {

  my ($self) = @_;

  return [ selectall_hashref_query($::form, $self->dbh, qq|SELECT * FROM auth."group" ORDER BY lower(name)|) ];

}

sub init_templates {

  my %templates = SL::Template->available_templates;

  return $templates{print_templates};

}

sub init_auth_db_settings {

  my $cfg = $::lx_office_conf{'authentication/database'};

  return {

    dbhost => $cfg->{host} || 'localhost',

    dbport => $cfg->{port} || 5432,

    dbname => $cfg->{name},

  };

}

sub init_data_dbhs {

  return [];

}

sub _clear_field {

  my ($text) = @_;

  $text ||= '';

  $text   =~ s/^\s+|\s+$//g;

  return $text;

}

sub _group_into_clients {

  my ($self) = @_;

  my @match_fields = qw(dbhost dbport dbname);

  my @copy_fields  = (@match_fields, qw(address company co_ustid dbuser dbpasswd duns sepa_creditor_id taxnumber templates));

  my @clients;

  # Group users into clients. Users which have identical database

  # settings (host, port and name) will be grouped. The other fields

  # like tax number etc. are taken from the first user and only filled

  # from user users if they're still unset.

  foreach my $user (@{ $self->users }) {

    $user->{$_} = _clear_field($user->{$_}) for @copy_fields;

    my $existing_client = first { my $client = $_; all { ($user->{$_} || '') eq ($client->{$_} || '') } @match_fields } @clients;

    if ($existing_client) {

      push @{ $existing_client->{users} }, $user->{id};

      $existing_client->{$_} ||= $user->{$_} for @copy_fields;

      next;

    }

    push @clients, {

      map({ $_ => $user->{$_} } @copy_fields),

      name    => $::locale->text('Client #1', scalar(@clients) + 1),

      users   => [ $user->{id} ],

      groups  => [ map { $_->{id} } @{ $self->groups } ],

      enabled => 1,

    };

  }

  # Ignore users (and therefore clients) for which no database

  # configuration has been given.

  @clients = grep { my $client = $_; any { $client->{$_} } @match_fields } @clients;

  # If there's only one client set that one as default.

  $clients[0]->{is_default} = 1 if scalar(@clients) == 1;

  # Set a couple of defaults for database fields.

  my $num = 0;

  foreach my $client (@clients) {

    $num                += 1;

    $client->{dbhost}  ||= 'localhost';

    $client->{dbport}  ||= 5432;

    $client->{templates} =~ s:templates/::;

  }

  $self->clients(\@clients);

}

sub _analyze {

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

  $self->_group_into_clients;

  return $self->_do_convert if !@{ $self->clients };

  print $::form->parse_html_template('dbupgrade/auth/clients', { SELF => $self });

  return 2;

}

sub _verify_clients {

  my ($self) = @_;

  my (%names, @errors);

  my $num = 0;

  foreach my $client (@{ $self->clients }) {

    $num += 1;

    next if !$client->{enabled};

    $client->{$_} = _clear_field($client->{$_}) for qw(address co_ustid company dbhost dbname dbpasswd dbport dbuser duns sepa_creditor_id taxnumber templates);

    if (!$client->{name} || $names{ $client->{name} }) {

      push @errors, $::locale->text('New client #1: The name must be unique and not empty.', $num);

    }

    $names{ $client->{name} } = 1;

    if (any { !$client->{$_} } qw(dbhost dbport dbname dbuser)) {

      push @errors, $::locale->text('New client #1: The database configuration fields "host", "port", "name" and "user" must not be empty.', $num);

    }

  }

  return @errors;

}

sub _alter_auth_database_structure {

  my ($self) = @_;

  my @queries = (

    qq|CREATE TABLE auth.clients (

         id         SERIAL  PRIMARY KEY,

         name       TEXT    NOT NULL UNIQUE,

         dbhost     TEXT    NOT NULL,

         dbport     INTEGER NOT NULL DEFAULT 5432,

         dbname     TEXT    NOT NULL,

         dbuser     TEXT    NOT NULL,

         dbpasswd   TEXT    NOT NULL,

         is_default BOOLEAN NOT NULL DEFAULT FALSE,

         UNIQUE (dbhost, dbport, dbname)

       )|,

    qq|CREATE TABLE auth.clients_users (

         client_id INTEGER NOT NULL REFERENCES auth.clients (id),

         user_id   INTEGER NOT NULL REFERENCES auth."user"  (id),

         PRIMARY KEY (client_id, user_id)

       )|,

    qq|CREATE TABLE auth.clients_groups (

         client_id INTEGER NOT NULL REFERENCES auth.clients (id),

         group_id  INTEGER NOT NULL REFERENCES auth."group" (id),

         PRIMARY KEY (client_id, group_id)

       )|,

  );

  $self->db_query($_, may_fail => 0) for @queries;

}

sub _alter_data_database_structure {

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

  my @queries = (

    qq|ALTER TABLE defaults ADD COLUMN company          TEXT|,

    qq|ALTER TABLE defaults ADD COLUMN address          TEXT|,

    qq|ALTER TABLE defaults ADD COLUMN taxnumber        TEXT|,

    qq|ALTER TABLE defaults ADD COLUMN co_ustid         TEXT|,

    qq|ALTER TABLE defaults ADD COLUMN duns             TEXT|,

    qq|ALTER TABLE defaults ADD COLUMN sepa_creditor_id TEXT|,

    qq|ALTER TABLE defaults ADD COLUMN templates        TEXT|,

    qq|INSERT INTO schema_info (tag, login) VALUES ('clients', 'admin')|,

  );

  foreach my $query (@queries) {

    $dbh->do($query) || die $self->db_errstr($dbh);

  }

}

sub _create_clients_in_auth_database {

  my ($self)  = @_;

  my @client_columns   = qw(name dbhost dbport dbname dbuser dbpasswd is_default);

  my $q_client         = qq|INSERT INTO auth.clients (| . join(', ', @client_columns) . qq|) VALUES (| . join(', ', ('?') x @client_columns) . qq|) RETURNING id|;

  my $sth_client       = $self->dbh->prepare($q_client) || die $self->db_errstr;

  my $q_client_user    = qq|INSERT INTO auth.clients_users (client_id, user_id) VALUES (?, ?)|;

  my $sth_client_user  = $self->dbh->prepare($q_client_user) || die $self->db_errstr;

  my $q_client_group   = qq|INSERT INTO auth.clients_groups (client_id, group_id) VALUES (?, ?)|;

  my $sth_client_group = $self->dbh->prepare($q_client_group) || die $self->db_errstr;

  foreach my $client (@{ $self->clients }) {

    next unless $client->{enabled};

    $client->{is_default} = $client->{is_default} ? 1 : 0;

    $sth_client->execute(@{ $client }{ @client_columns }) || die;

    my $client_id = $sth_client->fetch->[0];

    $sth_client_user ->execute($client_id, $_) || die for @{ $client->{users}  || [] };

    $sth_client_group->execute($client_id, $_) || die for @{ $client->{groups} || [] };

  }

  $sth_client      ->finish;

  $sth_client_user ->finish;

  $sth_client_group->finish;

}

sub _clean_auth_database {

  my ($self) = @_;

  my @keys_to_delete = qw(acs address admin anfragen angebote bestellungen businessnumber charset companies company co_ustid currency dbconnect dbdriver dbhost dbname dboptions dbpasswd dbport dbuser duns

                          einkaufsrechnungen in_numberformat lieferantenbestellungen login pdonumber printer rechnungen role sdonumber sepa_creditor_id sid steuernummer taxnumber templates);

  $self->dbh->do(qq|DELETE FROM auth.user_config WHERE cfg_key IN (| . join(', ', ('?') x @keys_to_delete) . qq|)|, undef, @keys_to_delete)

    || die $self->db_errstr;

}

sub _copy_fields_to_data_database {

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

  my $dbh = SL::DBConnect->connect('dbi:Pg:dbname=' . $client->{dbname} . ';host=' . $client->{dbhost} . ';port=' . $client->{dbport},

                                   $client->{dbuser}, $client->{dbpasswd},

                                   SL::DBConnect->get_options(AutoCommit => 0));

  if (!$dbh) {

    die join("\n",

             $::locale->text('The connection to the configured client database "#1" on host "#2:#3" failed.', $client->{dbname}, $client->{dbhost}, $client->{dbport}),

             $::locale->text('Please correct the settings and try again or deactivate that client.'),

             $::locale->text('Error message from the database: #1', $self->db_errstr('DBI')));

  }

  my ($has_been_applied) = $dbh->selectrow_array(qq|SELECT tag FROM schema_info WHERE tag = 'clients'|);

  if (!$has_been_applied) {

    $self->_alter_data_database_structure($dbh);

  }

  my @columns = qw(company address taxnumber co_ustid duns sepa_creditor_id templates);

  my $query   = join ', ', map { "$_ = ?" } @columns;

  my @values  = @{ $client }{ @columns };

  if (!$dbh->do(qq|UPDATE defaults SET $query|, undef, @values)) {

    die join("\n",

             $::locale->text('Updating the client fields in the database "#1" on host "#2:#3" failed.', $client->{dbname}, $client->{dbhost}, $client->{dbport}),

             $::locale->text('Please correct the settings and try again or deactivate that client.'),

             $::locale->text('Error message from the database: #1', $self->db_errstr('DBI')));

  }

  $self->data_dbhs([ @{ $self->data_dbhs }, $dbh ]);

}

sub _commit_data_database_changes {

  my ($self) = @_;

  foreach my $dbh (@{ $self->data_dbhs }) {

    $dbh->commit;

    $dbh->disconnect;

  }

}

sub _do_convert {

  my ($self) = @_;

  # Skip clients that are not enabled. Clean fields.

  my $num = 0;

  foreach my $client (@{ $self->clients }) {

    $num += 1;

    next if !$client->{enabled};

    $client->{$_}        = _clear_field($client->{$_}) for qw(dbhost dbport dbname dbuser dbpasswd address company co_ustid dbuser dbpasswd duns sepa_creditor_id taxnumber templates);

    $client->{templates} = 'templates/' . $client->{templates};

  }

  $self->_copy_fields_to_data_database($_) for grep { $_->{enabled} } @{ $self->clients };

  $self->_alter_auth_database_structure;

  $self->_create_clients_in_auth_database;

  $self->_clean_auth_database;

  $self->_commit_data_database_changes;

  return 1;

}

sub run {

  my ($self) = @_;

  return $self->_analyze if !$::form->{clients} || !@{ $::form->{clients} };

  $self->clients($::form->{clients});

  my @errors = $self->_verify_clients;

  return $self->_do_convert if !@errors;

  flash('error', @errors);

  print $::form->parse_html_template('dbupgrade/auth/clients', { SELF => $self });

  return 1;

}

1;

[%- USE LxERP -%][%- USE L -%]

[%- INCLUDE 'common/flash.html' %]

[% L.javascript_tag('jquery.selectboxes', 'jquery.multiselect2side') %]

<h1>[%- LxERP.t8("Introduction of clients") %]</h1>

<p>

 [% LxERP.t8("kivitendo has been extended to handle multiple clients within a single installation.") %]

 [% LxERP.t8("Therefore several settings that had to be made for each user in the past have been consolidated into the client configuration.") %]

 [% LxERP.t8("You have to grant users access to one or more clients.") %]

 [% LxERP.t8("The user can chose which client to connect to during login.") %]

</p>

<p>

 [% LxERP.t8("The access rights a user has within a client instance is still governed by his group membership.") %]

 [% LxERP.t8("Only groups that have been configured for the client the user logs in to will be considered.") %]

</p>

<p>

 [% LxERP.t8("The following list has been generated automatically from existing users collapsing users with identical settings into a single entry.") %]

 [% LxERP.t8("Please select which client configurations you want to create.") %]

 [% LxERP.t8("The 'name' is the field shown to the user during login.") %]

 [% LxERP.t8("It can be changed later but must be unique within the installation.") %]

</p>

<form method="post" action="admin.pl">

 [%- FOREACH client = SELF.clients %]

 [%- L.hidden_tag("clients[+].dummy", 1) %]

 <h2>[%- L.checkbox_tag("clients[].enabled", label=LxERP.t8("Create new client #1", loop.count), checked=client.enabled) %]</h2>

 <table>

  <tr>

   <th colspan="6">[%- LxERP.t8("General settings") %]</th>

  </tr>

  <tr>

   <td align="right" valign="top">[%- LxERP.t8("Client name") %]:</td>

   <td valign="top">[%- L.input_tag("clients[].name", client.name) %]</td>

   <td align="right" valign="top">[%- LxERP.t8("Company name") %]:</td>

   <td valign="top">[%- L.input_tag("clients[].company", client.company) %]</td>

   <td align="right" valign="top">[%- LxERP.t8("Address") %]:</td>

   <td valign="top">[%- L.textarea_tag("clients[].address", client.address, rows=4, cols=40) %]</td>

  </tr>

  <tr>

   <td align="right">[%- LxERP.t8("Tax number") %]:</td>

   <td>[%- L.input_tag("clients[].taxnumber", client.taxnumber) %]</td>

   <td align="right">[%- LxERP.t8("VAT ID") %]:</td>

   <td>[%- L.input_tag("clients[].co_ustid", client.co_ustid) %]</td>

   <td align="right">[%- LxERP.t8("DUNS-Nr") %]:</td>

   <td>[%- L.input_tag("clients[].duns", client.duns) %]</td>

  </tr>

  <tr>

   <td align="right">[%- LxERP.t8("SEPA creditor ID") %]:</td>

   <td colspan="5">[%- L.input_tag("clients[].sepa_creditor_id", client.sepa_creditor_id) %]</td>

  </tr>

  <tr>

   <td align="right">[%- LxERP.t8("Print templates") %]:</td>

   <td colspan="5">[%- L.select_tag("clients[].templates", SELF.templates, default=client.templates) %]</td>

  </tr>

  <tr>

   <th colspan="6">[%- LxERP.t8("User access") %]</th>

  </tr>

  <tr>

   <td valign="top">[%- LxERP.t8("Users with access to this client") %]:</td>

   <td valign="top" colspan="6" class="clearfix">

    [% L.select_tag('clients[].users[]', SELF.users, id='users_multi_' _ loop.count, value_key='id', title_key='login', default=client.users, multiple=1) %]

   </td>

  </tr>

  <tr>

   <td valign="top">[%- LxERP.t8("Groups that are valid for this client for access rights") %]:</td>

   <td valign="top" colspan="6" class="clearfix">

    [% L.select_tag('clients[].groups[]', SELF.groups, id='groups_multi_' _ loop.count, value_key='id', title_key='name', default=client.groups, multiple=1) %]

   </td>

  </tr>

  <tr>

   <th colspan="6">[%- LxERP.t8("Database settings") %]</th>

  </tr>

  <tr>

   <td align="right">[%- LxERP.t8("Database Host") %]:</td>

   <td>[%- L.input_tag("clients[].dbhost", client.dbhost) %]</td>

   <td align="right">[%- LxERP.t8("Port") %]:</td>

   <td>[%- L.input_tag("clients[].dbport", (client.dbport || 5432)) %]</td>

   <td align="right">[%- LxERP.t8("Database name") %]:</td>

   <td>[%- L.input_tag("clients[].dbname", client.dbname) %]</td>

  </tr>

  <tr>

   <td align="right">[%- LxERP.t8("User") %]:</td>

   <td>[%- L.input_tag("clients[].dbuser", client.dbuser) %]</td>

   <td align="right">[%- LxERP.t8("Password") %]:</td>

   <td>[%- L.input_tag("clients[].dbpasswd", client.dbpasswd) %]</td>

  </tr>

 </table>

 [% L.multiselect2side('users_multi_'  _ loop.count, labelsx => LxERP.t8('All users'),  labeldx => LxERP.t8('Users with access')) %]

 [% L.multiselect2side('groups_multi_' _ loop.count, labelsx => LxERP.t8('All groups'), labeldx => LxERP.t8('Groups valid for this client')) %]

 [%- END %]

 <p>

  [%- L.hidden_tag('action', 'list_users') %]

  [% L.submit_tag('dummy', LxERP.t8('Continue')) %]

 </p>

</form>