From a9da5102a870336ee4d347117ca7a33eeaff64ea Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Mon, 4 Jan 2016 16:24:19 +0100 Subject: [PATCH] swiss: Datenbankupgrades verschoben --- SL/DBUpgrade2/Base.pm | 5 ++- SL/User.pm | 44 ++++++++++++++----- sql/Pg-upgrade2/chart_pos_er.sql | 1 + sql/Pg-upgrade2/steuerfilterung.pl | 11 ++++- sql/Switzerland-deutsch-MWST-2014-chart.sql | 2 + ...witzerland-deutsch-ohneMWST-2014-chart.sql | 2 + templates/webpages/admin/create_dataset.html | 2 +- 7 files changed, 53 insertions(+), 14 deletions(-) diff --git a/SL/DBUpgrade2/Base.pm b/SL/DBUpgrade2/Base.pm index 00df6d2e8..594fb7fba 100644 --- a/SL/DBUpgrade2/Base.pm +++ b/SL/DBUpgrade2/Base.pm @@ -11,6 +11,7 @@ use File::Basename (); use File::Copy (); use File::Path (); use List::MoreUtils qw(uniq); +use SL::DBUtils qw(selectfirst_hashref_query); use version; use Rose::Object::MakeMethods::Generic ( @@ -97,7 +98,9 @@ sub add_print_templates { croak "File '${src_dir}/$_' does not exist" unless -f "${src_dir}/$_"; } - return 1 unless my $template_dir = $::instance_conf->reload->get_templates; + # can't use Rose or InstanceConf here because defaults might not be fully upgraded yet. + my $defaults = selectfirst_hashref_query($::form, $::form->get_standard_dbh, "SELECT * FROM defaults"); + return 1 unless my $template_dir = $defaults->{template}; $::lxdebug->message(LXDebug::DEBUG1(), "add_print_templates: template_dir $template_dir"); foreach my $src_file (@files) { diff --git a/SL/User.pm b/SL/User.pm index 38154283e..45f5f553d 100644 --- a/SL/User.pm +++ b/SL/User.pm @@ -277,23 +277,44 @@ sub dbcreate { &dbconnect_vars($form, $form->{db}); + # make a shim myconfig so that rose db connections work + $::myconfig{$_} = $form->{$_} for qw(dbhost dbport dbuser dbpasswd); + $::myconfig{dbname} = $form->{db}; + $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options) or $form->dberror; - my $dbupdater = SL::DBUpgrade2->new(form => $form); + my $dbupdater = SL::DBUpgrade2->new(form => $form, return_on_error => 1, silent => 1)->parse_dbupdate_controls; # create the tables $dbupdater->process_query($dbh, "sql/lx-office.sql"); + $dbupdater->process_query($dbh, "sql/$form->{chart}-chart.sql"); - # process update-scripts needed before 1st user-login - $self->create_schema_info_table($form, $dbh); - $dbupdater->process_query($dbh, "sql/Pg-upgrade2/defaults_add_precision.sql"); - $dbh->do("INSERT INTO schema_info (tag, login) VALUES ('defaults_add_precision', 'admin')"); + $query = qq|UPDATE defaults SET coa = ?|; + do_query($form, $dbh, $query, map { $form->{$_} } qw(chart)); - # load chart of accounts - $dbupdater->process_query($dbh, "sql/$form->{chart}-chart.sql"); + $dbh->disconnect; + + # update new database + $self->dbupdate2(form => $form, updater => $dbupdater, database => $form->{db}, silent => 1); - $query = qq|UPDATE defaults SET coa = ?, accounting_method = ?, profit_determination = ?, inventory_system = ?, curr = ?, precision = ?|; - do_query($form, $dbh, $query, map { $form->{$_} } qw(chart accounting_method profit_determination inventory_system defaultcurrency precision countrymode)); + $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options) + or $form->dberror; + + $query = "SELECT * FROM currencies WHERE name = ?"; + my $curr = selectfirst_hashref_query($form, $dbh, $query, $form->{defaultcurrency}); + if (!$curr->{id}) { + do_query($form, $dbh, "INSERT INTO currencies (name) VALUES (?)", $form->{defaultcurrency}); + $curr = selectfirst_hashref_query($form, $dbh, $query, $form->{defaultcurrency}); + } + + $query = qq|UPDATE defaults SET accounting_method = ?, profit_determination = ?, inventory_system = ?, precision = ?, currency_id = ?|; + do_query($form, $dbh, $query, + $form->{accounting_method}, + $form->{profit_determination}, + $form->{inventory_system}, + $form->parse_amount(\%::myconfig, $form->{precision_as_number}), + $curr->{id}, + ); $dbh->disconnect; @@ -385,13 +406,14 @@ sub dbupdate2 { my $form = $params{form}; my $dbupdater = $params{updater}; my $db = $params{database}; + my $silent = $params{silent}; map { $_->{description} = SL::Iconv::convert($_->{charset}, 'UTF-8', $_->{description}) } values %{ $dbupdater->{all_controls} }; &dbconnect_vars($form, $db); # Flush potentially held database locks. - $form->get_standard_dbh->commit; +# $form->get_standard_dbh->commit; my $dbh = SL::DBConnect->connect($form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd}, SL::DBConnect->get_options) or $form->dberror; @@ -406,7 +428,7 @@ sub dbupdate2 { # been applied correctly and if the update has not requested user # interaction. $main::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}"); - print $form->parse_html_template("dbupgrade/upgrade_message2", $control); + print $form->parse_html_template("dbupgrade/upgrade_message2", $control) unless $silent; $dbupdater->process_file($dbh, "sql/Pg-upgrade2/$control->{file}", $control); } diff --git a/sql/Pg-upgrade2/chart_pos_er.sql b/sql/Pg-upgrade2/chart_pos_er.sql index 92c9abb42..1d7f9cf15 100644 --- a/sql/Pg-upgrade2/chart_pos_er.sql +++ b/sql/Pg-upgrade2/chart_pos_er.sql @@ -2,5 +2,6 @@ -- @description: pos_er Feld in Konten für die Position ind er Erfolgsrechnung -- @depends: release_3_3_0 -- @encoding: utf-8 +-- @may_fail: 1 ALTER TABLE chart ADD COLUMN pos_er INTEGER; diff --git a/sql/Pg-upgrade2/steuerfilterung.pl b/sql/Pg-upgrade2/steuerfilterung.pl index 655d28f5e..b7be34167 100644 --- a/sql/Pg-upgrade2/steuerfilterung.pl +++ b/sql/Pg-upgrade2/steuerfilterung.pl @@ -36,6 +36,7 @@ sub run { } my @well_known_taxes = ( + # German SKR03 { taxkey => 0, rate => 0, taxdescription => qr{keine.*steuer}i, categories => 'ALQCIE' }, { taxkey => 1, rate => 0, taxdescription => qr{frei}i, categories => 'ALQCIE' }, { taxkey => 2, rate => 0.07, taxdescription => qr{umsatzsteuer}i, categories => 'I' }, @@ -56,7 +57,15 @@ sub run { { taxkey => 18, rate => 0.07, taxdescription => qr{innergem.*erwerb.*erm}i, categories => 'E' }, { taxkey => 19, rate => 0.16, taxdescription => qr{innergem.*erwerb.*voll}i, categories => 'E' }, { taxkey => 19, rate => 0.19, taxdescription => qr{innergem.*erwerb.*voll}i, categories => 'E' }, - ); + + # Swiss + { taxkey => 2, rate => 0.08, taxdescription => qr{mwst}i, categories => 'I' }, + { taxkey => 3, rate => 0.025, taxdescription => qr{mwst}i, categories => 'I' }, + { taxkey => 4, rate => 0.08, taxdescription => qr{mwst}i, categories => 'E' }, + { taxkey => 5, rate => 0.025, taxdescription => qr{mwst}i, categories => 'E' }, + { taxkey => 6, rate => 0.08, taxdescription => qr{mwst}i, categories => 'E' }, + { taxkey => 7, rate => 0.025, taxdescription => qr{mwst}i, categories => 'E' }, + ); $query = qq|SELECT taxkey, taxdescription, rate, id AS tax_id FROM tax order by taxkey, rate;|; diff --git a/sql/Switzerland-deutsch-MWST-2014-chart.sql b/sql/Switzerland-deutsch-MWST-2014-chart.sql index e7474e1f3..19f9dc7e9 100644 --- a/sql/Switzerland-deutsch-MWST-2014-chart.sql +++ b/sql/Switzerland-deutsch-MWST-2014-chart.sql @@ -3,6 +3,8 @@ -- Korrigiert: November 2015 -- Grundlage: Revision OR Stand 1.1.2013, insbesondere Art. 957a Abs. 2 +ALTER TABLE chart ADD COLUMN pos_er INTEGER; + DELETE FROM chart; INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_er, datevautomatik, valid_from) VALUES diff --git a/sql/Switzerland-deutsch-ohneMWST-2014-chart.sql b/sql/Switzerland-deutsch-ohneMWST-2014-chart.sql index 31d1b0e37..cda2f7656 100644 --- a/sql/Switzerland-deutsch-ohneMWST-2014-chart.sql +++ b/sql/Switzerland-deutsch-ohneMWST-2014-chart.sql @@ -3,6 +3,8 @@ -- Korrigiert: November 2015 -- Grundlage: Revision OR Stand 1.1.2013, insbesondere Art. 957a Abs. 2 +ALTER TABLE chart ADD COLUMN pos_er INTEGER; + DELETE FROM chart; INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_er, datevautomatik, valid_from) VALUES diff --git a/templates/webpages/admin/create_dataset.html b/templates/webpages/admin/create_dataset.html index d02865aff..741a941db 100644 --- a/templates/webpages/admin/create_dataset.html +++ b/templates/webpages/admin/create_dataset.html @@ -33,7 +33,7 @@ [% LxERP.t8('Precision') %] - [% L.input_tag('precision', FORM.precision) %] + [% L.input_tag('precision_as_number', LxERP.format_amount(FORM.precision, 2)) %] -- 2.20.1