Projekt

Allgemein

Profil

Herunterladen (10,5 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/sql/Pg-upgrade2-auth/clients.pl @ 1466e54a

3277b6bd Moritz Bunkus
# @tag: clients
8993832d Geoffrey Richardson
# @description: Einführung von Mandanten
3277b6bd Moritz Bunkus
# @depends: release_3_0_0
# @ignore: 0
4eab0ccf Moritz Bunkus
package SL::DBUpgrade2::Auth::clients;
3277b6bd Moritz Bunkus
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),
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;
dec3d0e2 Moritz Bunkus
$client->{name} ||= $::locale->text('Client #1', $num);
3277b6bd Moritz Bunkus
$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;