1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #=====================================================================
31 # user related functions
33 #=====================================================================
50 $main::lxdebug->enter_sub();
52 my ($type, $login) = @_;
57 my %user_data = $main::auth->read_user($login);
58 map { $self->{$_} = $user_data{$_} } keys %user_data;
61 $main::lxdebug->leave_sub();
67 $main::lxdebug->enter_sub();
74 # scan the locale directory and read in the LANGUAGE files
75 opendir(DIR, "locale");
77 my @dir = grep(!/(^\.\.?$|\..*)/, readdir(DIR));
79 foreach my $dir (@dir) {
80 next unless open(FH, "locale/$dir/LANGUAGE");
84 $cc{$dir} = "@language";
89 $main::lxdebug->leave_sub();
95 $main::lxdebug->enter_sub();
97 my ($self, $form) = @_;
104 if ($self->{login}) {
105 my %myconfig = $main::auth->read_user($self->{login});
107 # check if database is down
108 my $dbh = SL::DBConnect->connect($myconfig{dbconnect}, $myconfig{dbuser}, $myconfig{dbpasswd})
109 or $self->error($DBI::errstr);
111 # we got a connection, check the version
112 my $query = qq|SELECT version FROM defaults|;
113 my $sth = $dbh->prepare($query);
114 $sth->execute || $form->dberror($query);
116 my ($dbversion) = $sth->fetchrow_array;
119 $self->create_employee_entry($form, $dbh, \%myconfig);
121 $self->create_schema_info_table($form, $dbh);
123 my $dbupdater_auth = SL::DBUpgrade2->new(form => $form, dbdriver => 'Pg', auth => 1)->parse_dbupdate_controls;
124 if ($dbupdater_auth->unapplied_upgrade_scripts($::auth->dbconnect)) {
125 $::lxdebug->leave_sub;
131 my $dbupdater = SL::DBUpgrade2->new(form => $form, dbdriver => $myconfig{dbdriver})->parse_dbupdate_controls;
133 map({ $form->{$_} = $myconfig{$_} } qw(dbname dbhost dbport dbdriver dbuser dbpasswd dbconnect dateformat));
134 dbconnect_vars($form, $form->{dbname});
135 my $update_available = $dbupdater->update_available($dbversion) || $dbupdater->update2_available($dbh);
138 if ($update_available) {
139 $form->{"stylesheet"} = "lx-office-erp.css";
140 $form->{"title"} = $main::locale->text("Dataset upgrade");
142 print $form->parse_html_template("dbupgrade/header");
144 $form->{dbupdate} = "db$myconfig{dbname}";
145 $form->{ $form->{dbupdate} } = 1;
147 if ($form->{"show_dbupdate_warning"}) {
148 print $form->parse_html_template("dbupgrade/warning");
153 if (!open(FH, ">" . $::lx_office_conf{paths}->{userspath} . "/nologin")) {
154 $form->show_generic_error($main::locale->text('A temporary file could not be created. ' .
155 'Please verify that the directory "#1" is writeable by the webserver.',
156 $::lx_office_conf{paths}->{userspath}),
160 # required for Oracle
161 $form->{dbdefault} = $sid;
163 # ignore HUP, QUIT in case the webserver times out
164 $SIG{HUP} = 'IGNORE';
165 $SIG{QUIT} = 'IGNORE';
167 $self->dbupdate($form);
168 $self->dbupdate2($form, $dbupdater);
169 SL::DBUpgrade2->new(form => $::form, dbdriver => 'Pg', auth => 1)->apply_admin_dbupgrade_scripts(0);
174 unlink($::lx_office_conf{paths}->{userspath} . "/nologin");
177 $self->{"menustyle"} eq "v3" ? "menuv3.pl" :
178 $self->{"menustyle"} eq "neu" ? "menunew.pl" :
179 $self->{"menustyle"} eq "js" ? "menujs.pl" :
180 $self->{"menustyle"} eq "xml" ? "menuXML.pl" :
183 print $form->parse_html_template("dbupgrade/footer", { "menufile" => $menufile });
189 $main::lxdebug->leave_sub();
195 $main::lxdebug->enter_sub();
197 my ($form, $db) = @_;
200 'Pg' => { 'yy-mm-dd' => 'set DateStyle to \'ISO\'',
201 'yyyy-mm-dd' => 'set DateStyle to \'ISO\'',
202 'mm/dd/yy' => 'set DateStyle to \'SQL, US\'',
203 'mm-dd-yy' => 'set DateStyle to \'POSTGRES, US\'',
204 'dd/mm/yy' => 'set DateStyle to \'SQL, EUROPEAN\'',
205 'dd-mm-yy' => 'set DateStyle to \'POSTGRES, EUROPEAN\'',
206 'dd.mm.yy' => 'set DateStyle to \'GERMAN\''
209 'yy-mm-dd' => 'ALTER SESSION SET NLS_DATE_FORMAT = \'YY-MM-DD\'',
210 'yyyy-mm-dd' => 'ALTER SESSION SET NLS_DATE_FORMAT = \'YYYY-MM-DD\'',
211 'mm/dd/yy' => 'ALTER SESSION SET NLS_DATE_FORMAT = \'MM/DD/YY\'',
212 'mm-dd-yy' => 'ALTER SESSION SET NLS_DATE_FORMAT = \'MM-DD-YY\'',
213 'dd/mm/yy' => 'ALTER SESSION SET NLS_DATE_FORMAT = \'DD/MM/YY\'',
214 'dd-mm-yy' => 'ALTER SESSION SET NLS_DATE_FORMAT = \'DD-MM-YY\'',
215 'dd.mm.yy' => 'ALTER SESSION SET NLS_DATE_FORMAT = \'DD.MM.YY\'',
218 $form->{dboptions} = $dboptions{ $form->{dbdriver} }{ $form->{dateformat} };
220 if ($form->{dbdriver} eq 'Pg') {
221 $form->{dbconnect} = "dbi:Pg:dbname=$db";
224 if ($form->{dbdriver} eq 'Oracle') {
225 $form->{dbconnect} = "dbi:Oracle:sid=$form->{sid}";
228 if ($form->{dbhost}) {
229 $form->{dbconnect} .= ";host=$form->{dbhost}";
231 if ($form->{dbport}) {
232 $form->{dbconnect} .= ";port=$form->{dbport}";
235 $main::lxdebug->leave_sub();
239 $main::lxdebug->enter_sub();
241 my @drivers = DBI->available_drivers();
243 $main::lxdebug->leave_sub();
245 return (grep { /(Pg|Oracle)/ } @drivers);
249 $main::lxdebug->enter_sub();
251 my ($self, $form) = @_;
256 $form->{dbdefault} = $form->{dbuser} unless $form->{dbdefault};
257 $form->{sid} = $form->{dbdefault};
258 &dbconnect_vars($form, $form->{dbdefault});
260 my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd})
263 if ($form->{dbdriver} eq 'Pg') {
265 qq|SELECT datname FROM pg_database | .
266 qq|WHERE NOT datname IN ('template0', 'template1')|;
267 $sth = $dbh->prepare($query);
268 $sth->execute() || $form->dberror($query);
270 while (my ($db) = $sth->fetchrow_array) {
272 if ($form->{only_acc_db}) {
274 next if ($db =~ /^template/);
276 &dbconnect_vars($form, $db);
277 my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd})
281 qq|SELECT tablename FROM pg_tables | .
282 qq|WHERE (tablename = 'defaults') AND (tableowner = ?)|;
283 my $sth = $dbh->prepare($query);
284 $sth->execute($form->{dbuser}) ||
285 $form->dberror($query . " ($form->{dbuser})");
287 if ($sth->fetchrow_array) {
288 push(@dbsources, $db);
294 push(@dbsources, $db);
298 if ($form->{dbdriver} eq 'Oracle') {
299 if ($form->{only_acc_db}) {
301 qq|SELECT owner FROM dba_objects | .
302 qq|WHERE object_name = 'DEFAULTS' AND object_type = 'TABLE'|;
304 $query = qq|SELECT username FROM dba_users|;
307 $sth = $dbh->prepare($query);
308 $sth->execute || $form->dberror($query);
310 while (my ($db) = $sth->fetchrow_array) {
311 push(@dbsources, $db);
318 $main::lxdebug->leave_sub();
323 sub dbclusterencoding {
324 $main::lxdebug->enter_sub();
326 my ($self, $form) = @_;
328 $form->{dbdefault} ||= $form->{dbuser};
330 dbconnect_vars($form, $form->{dbdefault});
332 my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) || $form->dberror();
333 my $query = qq|SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'template0'|;
334 my ($cluster_encoding) = $dbh->selectrow_array($query);
337 $main::lxdebug->leave_sub();
339 return $cluster_encoding;
343 $main::lxdebug->enter_sub();
345 my ($self, $form) = @_;
347 $form->{sid} = $form->{dbdefault};
348 &dbconnect_vars($form, $form->{dbdefault});
350 SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd})
352 $form->{db} =~ s/\"//g;
354 'Pg' => qq|CREATE DATABASE "$form->{db}"|,
356 qq|CREATE USER "$form->{db}" DEFAULT TABLESPACE USERS | .
357 qq|TEMPORARY TABLESPACE TEMP IDENTIFIED BY "$form->{db}"|
364 push(@{$dboptions{"Pg"}}, "ENCODING = " . $dbh->quote($form->{"encoding"}))
365 if ($form->{"encoding"});
366 if ($form->{"dbdefault"}) {
367 my $dbdefault = $form->{"dbdefault"};
368 $dbdefault =~ s/[^a-zA-Z0-9_\-]//g;
369 push(@{$dboptions{"Pg"}}, "TEMPLATE = $dbdefault");
372 my $query = $dbcreate{$form->{dbdriver}};
373 $query .= " WITH " . join(" ", @{$dboptions{"Pg"}}) if (@{$dboptions{"Pg"}});
375 # Ignore errors if the database exists.
378 if ($form->{dbdriver} eq 'Oracle') {
379 $query = qq|GRANT CONNECT, RESOURCE TO "$form->{db}"|;
380 do_query($form, $dbh, $query);
384 # setup variables for the new database
385 if ($form->{dbdriver} eq 'Oracle') {
386 $form->{dbuser} = $form->{db};
387 $form->{dbpasswd} = $form->{db};
390 &dbconnect_vars($form, $form->{db});
392 $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd})
395 my $db_charset = $Common::db_encoding_to_charset{$form->{encoding}};
396 $db_charset ||= Common::DEFAULT_CHARSET;
398 my $dbupdater = SL::DBUpgrade2->new(form => $form, dbdriver => $form->{dbdriver});
400 $dbupdater->process_query($dbh, "sql/lx-office.sql", undef, $db_charset);
402 # load chart of accounts
403 $dbupdater->process_query($dbh, "sql/$form->{chart}-chart.sql", undef, $db_charset);
405 $query = "UPDATE defaults SET coa = ?";
406 do_query($form, $dbh, $query, $form->{chart});
410 $main::lxdebug->leave_sub();
414 $main::lxdebug->enter_sub();
416 my ($self, $form) = @_;
417 $form->{db} =~ s/\"//g;
418 my %dbdelete = ('Pg' => qq|DROP DATABASE "$form->{db}"|,
419 'Oracle' => qq|DROP USER "$form->{db}" CASCADE|);
421 $form->{sid} = $form->{dbdefault};
422 &dbconnect_vars($form, $form->{dbdefault});
423 my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd})
425 my $query = $dbdelete{$form->{dbdriver}};
426 do_query($form, $dbh, $query);
430 $main::lxdebug->leave_sub();
433 sub dbsources_unused {
434 $main::lxdebug->enter_sub();
436 my ($self, $form) = @_;
438 $form->{only_acc_db} = 1;
440 my %members = $main::auth->read_all_users();
441 my %dbexcl = map { $_ => 1 } grep { $_ } map { $_->{dbname} } values %members;
443 $dbexcl{$form->{dbdefault}} = 1;
444 $dbexcl{$main::auth->{DB_config}->{db}} = 1;
446 my @dbunused = grep { !$dbexcl{$_} } dbsources("", $form);
448 $main::lxdebug->leave_sub();
454 $main::lxdebug->enter_sub();
456 my ($self, $form) = @_;
458 my %members = $main::auth->read_all_users();
459 my $dbupdater = SL::DBUpgrade2->new(form => $form, dbdriver => $form->{dbdriver})->parse_dbupdate_controls;
461 my ($query, $sth, %dbs_needing_updates);
463 foreach my $login (grep /[a-z]/, keys %members) {
464 my $member = $members{$login};
466 map { $form->{$_} = $member->{$_} } qw(dbname dbuser dbpasswd dbhost dbport);
467 dbconnect_vars($form, $form->{dbname});
469 my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd});
475 $query = qq|SELECT version FROM defaults|;
476 $sth = prepare_query($form, $dbh, $query);
477 if ($sth->execute()) {
478 ($version) = $sth->fetchrow_array();
482 $dbh->disconnect and next unless $version;
484 my $update_available = $dbupdater->update_available($version) || $dbupdater->update2_available($dbh);
487 if ($update_available) {
489 map { $dbinfo->{$_} = $member->{$_} } grep /^db/, keys %{ $member };
490 $dbs_needing_updates{$member->{dbhost} . "::" . $member->{dbname}} = $dbinfo;
494 $main::lxdebug->leave_sub();
496 return values %dbs_needing_updates;
500 $main::lxdebug->enter_sub(2);
502 my (@v, $version, $i);
504 @v = split(/\./, $_[0]);
505 while (scalar(@v) < 4) {
509 for ($i = 0; $i < 4; $i++) {
514 $main::lxdebug->leave_sub(2);
518 sub cmp_script_version {
519 my ($a_from, $a_to, $b_from, $b_to);
520 my ($i, $res_a, $res_b);
521 my ($my_a, $my_b) = ($a, $b);
523 $my_a =~ s/.*-upgrade-//;
525 $my_b =~ s/.*-upgrade-//;
527 my ($my_a_from, $my_a_to) = split(/-/, $my_a);
528 my ($my_b_from, $my_b_to) = split(/-/, $my_b);
530 $res_a = calc_version($my_a_from);
531 $res_b = calc_version($my_b_from);
533 if ($res_a == $res_b) {
534 $res_a = calc_version($my_a_to);
535 $res_b = calc_version($my_b_to);
538 return $res_a <=> $res_b;
541 sub create_schema_info_table {
542 $main::lxdebug->enter_sub();
544 my ($self, $form, $dbh) = @_;
546 my $query = "SELECT tag FROM schema_info LIMIT 1";
547 if (!$dbh->do($query)) {
550 qq|CREATE TABLE schema_info (| .
553 qq| itime timestamp DEFAULT now(), | .
554 qq| PRIMARY KEY (tag))|;
555 $dbh->do($query) || $form->dberror($query);
558 $main::lxdebug->leave_sub();
562 $main::lxdebug->enter_sub();
564 my ($self, $form) = @_;
568 $form->{sid} = $form->{dbdefault};
570 my @upgradescripts = ();
574 if ($form->{dbupdate}) {
576 # read update scripts into memory
577 opendir(SQLDIR, "sql/" . $form->{dbdriver} . "-upgrade")
578 or &error("", "sql/" . $form->{dbdriver} . "-upgrade : $!");
580 sort(cmp_script_version
581 grep(/$form->{dbdriver}-upgrade-.*?\.(sql|pl)$/,
586 my $db_charset = $::lx_office_conf{system}->{dbcharset};
587 $db_charset ||= Common::DEFAULT_CHARSET;
589 my $dbupdater = SL::DBUpgrade2->new(form => $form, dbdriver => $form->{dbdriver});
591 foreach my $db (split(/ /, $form->{dbupdate})) {
593 next unless $form->{$db};
595 # strip db from dataset
597 &dbconnect_vars($form, $db);
599 my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd})
602 $dbh->do($form->{dboptions}) if ($form->{dboptions});
605 $query = qq|SELECT version FROM defaults|;
606 my ($version) = selectrow_query($form, $dbh, $query);
608 next unless $version;
610 $version = calc_version($version);
612 foreach my $upgradescript (@upgradescripts) {
613 my $a = $upgradescript;
614 $a =~ s/^\Q$form->{dbdriver}\E-upgrade-|\.(sql|pl)$//g;
616 my ($mindb, $maxdb) = split /-/, $a;
617 my $str_maxdb = $maxdb;
618 $mindb = calc_version($mindb);
619 $maxdb = calc_version($maxdb);
621 next if ($version >= $maxdb);
623 # if there is no upgrade script exit
624 last if ($version < $mindb);
627 $main::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $upgradescript");
628 $dbupdater->process_file($dbh, "sql/" . $form->{"dbdriver"} . "-upgrade/$upgradescript", $str_maxdb, $db_charset);
639 $main::lxdebug->leave_sub();
645 $main::lxdebug->enter_sub();
647 my ($self, $form, $dbupdater) = @_;
649 $form->{sid} = $form->{dbdefault};
652 my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;
654 map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $dbupdater->{all_controls} };
656 foreach my $db (split / /, $form->{dbupdate}) {
657 next unless $form->{$db};
659 # strip db from dataset
661 &dbconnect_vars($form, $db);
663 my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}) or $form->dberror;
665 $dbh->do($form->{dboptions}) if ($form->{dboptions});
667 $self->create_schema_info_table($form, $dbh);
669 my @upgradescripts = $dbupdater->unapplied_upgrade_scripts($dbh);
671 $dbh->disconnect and next if !@upgradescripts;
673 foreach my $control (@upgradescripts) {
675 $main::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
676 print $form->parse_html_template("dbupgrade/upgrade_message2", $control);
678 $dbupdater->process_file($dbh, "sql/" . $form->{"dbdriver"} . "-upgrade2/$control->{file}", $control, $db_charset);
686 $main::lxdebug->leave_sub();
692 $main::lxdebug->enter_sub();
695 my $form = \%main::form;
697 # format dbconnect and dboptions string
698 dbconnect_vars($self, $self->{dbname});
700 map { $self->{$_} =~ s/\r//g; } qw(address signature);
702 $main::auth->save_user($self->{login}, map { $_, $self->{$_} } config_vars());
704 my $dbh = SL::DBConnect->connect($self->{dbconnect}, $self->{dbuser}, $self->{dbpasswd});
706 $self->create_employee_entry($form, $dbh, $self, 1);
710 $main::lxdebug->leave_sub();
713 sub create_employee_entry {
714 $main::lxdebug->enter_sub();
719 my $myconfig = shift;
720 my $update_existing = shift;
722 if (!does_table_exist($dbh, 'employee')) {
723 $main::lxdebug->leave_sub();
727 # add login to employee table if it does not exist
728 # no error check for employee table, ignore if it does not exist
729 my ($id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $self->{login});
732 my $query = qq|INSERT INTO employee (login, name, workphone, role) VALUES (?, ?, ?, ?)|;
733 do_query($form, $dbh, $query, ($self->{login}, $myconfig->{name}, $myconfig->{tel}, "user"));
735 } elsif ($update_existing) {
736 my $query = qq|UPDATE employee SET name = ?, workphone = ?, role = 'user' WHERE id = ?|;
737 do_query($form, $dbh, $query, $myconfig->{name}, $myconfig->{tel}, $id);
740 $main::lxdebug->leave_sub();
744 $main::lxdebug->enter_sub();
746 my @conf = qw(address admin businessnumber company countrycode
747 currency dateformat dbconnect dbdriver dbhost dbport dboptions
748 dbname dbuser dbpasswd email fax name numberformat password
749 printer role sid signature stylesheet tel templates vclimit angebote
750 bestellungen rechnungen anfragen lieferantenbestellungen einkaufsrechnungen
751 taxnumber co_ustid duns menustyle template_format default_media
752 default_printer_id copies show_form_details favorites
753 pdonumber sdonumber hide_cvar_search_options mandatory_departments
756 $main::lxdebug->leave_sub();
762 $main::lxdebug->enter_sub();
764 my ($self, $msg) = @_;
766 $main::lxdebug->show_backtrace();
768 if ($ENV{HTTP_USER_AGENT}) {
769 print qq|Content-Type: text/html
771 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN">
773 <body bgcolor=ffffff>
775 <h2><font color=red>Error!</font></h2>
782 $main::lxdebug->leave_sub();