From b989d7cfb4bd8de5c3e290b8df0519ad34d5dd0c Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Tue, 25 Jun 2013 13:10:59 +0200 Subject: [PATCH] Auf Datenbankebene Steuerzonen konfigurierbar gemacht MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Damit können jetzt mehr als die 4 Standardsteuerzonen eingerichtet werden. Die bisherigen Steuerzonen wurden aus der Tabelle buchungsgruppen in die neue Tabelle taxzone_charts ausgelagert. Konzeption siehe Ticket #2295 Es wurden noch keinerlei Veränderungen an der Kivitendo Benutzeroberfläche durchgeführt, durch dieses Upgrade können die Buchungsgruppen also nicht mehr konfiguriert werden, dies muß auf Datenbankebene passieren. Rose schon angepasst. Conflicts: SL/DB/MetaSetup/Buchungsgruppe.pm --- SL/AM.pm | 39 ++--------------- SL/DB/Buchungsgruppe.pm | 60 ++++---------------------- SL/DB/Helper/ALL.pm | 1 + SL/DB/Helper/Mappings.pm | 1 + SL/DB/MetaSetup/Buchungsgruppe.pm | 9 ---- SL/DB/MetaSetup/TaxzoneChart.pm | 44 +++++++++++++++++++ SL/DB/Part.pm | 2 +- SL/DB/TaxzoneChart.pm | 15 +++++++ SL/IC.pm | 22 ++++++---- SL/IR.pm | 22 +++++----- SL/IS.pm | 20 ++++----- SL/OE.pm | 4 +- sql/Pg-upgrade2/convert_taxzone.pl | 69 ++++++++++++++++++++++++++++++ sql/Pg-upgrade2/taxzones.sql | 19 ++++++++ 14 files changed, 199 insertions(+), 128 deletions(-) create mode 100644 SL/DB/MetaSetup/TaxzoneChart.pm create mode 100644 SL/DB/TaxzoneChart.pm create mode 100644 sql/Pg-upgrade2/convert_taxzone.pl create mode 100644 sql/Pg-upgrade2/taxzones.sql diff --git a/SL/AM.pm b/SL/AM.pm index 30b1d9301..c18cfd785 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -747,26 +747,10 @@ sub buchungsgruppe { # connect to database my $dbh = $form->dbconnect($myconfig); - + # TODO: extract information about income/expense accounts from new table taxzone_chart my $query = qq|SELECT id, description, inventory_accno_id, - (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno, - income_accno_id_0, - (SELECT accno FROM chart WHERE id = income_accno_id_0) AS income_accno_0, - expense_accno_id_0, - (SELECT accno FROM chart WHERE id = expense_accno_id_0) AS expense_accno_0, - income_accno_id_1, - (SELECT accno FROM chart WHERE id = income_accno_id_1) AS income_accno_1, - expense_accno_id_1, - (SELECT accno FROM chart WHERE id = expense_accno_id_1) AS expense_accno_1, - income_accno_id_2, - (SELECT accno FROM chart WHERE id = income_accno_id_2) AS income_accno_2, - expense_accno_id_2, - (select accno FROM chart WHERE id = expense_accno_id_2) AS expense_accno_2, - income_accno_id_3, - (SELECT accno FROM chart WHERE id = income_accno_id_3) AS income_accno_3, - expense_accno_id_3, - (SELECT accno FROM chart WHERE id = expense_accno_id_3) AS expense_accno_3 + (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno FROM buchungsgruppen ORDER BY sortkey|; @@ -794,25 +778,10 @@ sub get_buchungsgruppe { my $dbh = $form->dbconnect($myconfig); if ($form->{id}) { + # TODO: extract information about income/expense accounts from new table taxzone_chart $query = qq|SELECT description, inventory_accno_id, - (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno, - income_accno_id_0, - (SELECT accno FROM chart WHERE id = income_accno_id_0) AS income_accno_0, - expense_accno_id_0, - (SELECT accno FROM chart WHERE id = expense_accno_id_0) AS expense_accno_0, - income_accno_id_1, - (SELECT accno FROM chart WHERE id = income_accno_id_1) AS income_accno_1, - expense_accno_id_1, - (SELECT accno FROM chart WHERE id = expense_accno_id_1) AS expense_accno_1, - income_accno_id_2, - (SELECT accno FROM chart WHERE id = income_accno_id_2) AS income_accno_2, - expense_accno_id_2, - (select accno FROM chart WHERE id = expense_accno_id_2) AS expense_accno_2, - income_accno_id_3, - (SELECT accno FROM chart WHERE id = income_accno_id_3) AS income_accno_3, - expense_accno_id_3, - (SELECT accno FROM chart WHERE id = expense_accno_id_3) AS expense_accno_3 + (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno FROM buchungsgruppen WHERE id = ?|; my $sth = $dbh->prepare($query); diff --git a/SL/DB/Buchungsgruppe.pm b/SL/DB/Buchungsgruppe.pm index 5f4d831fa..65f75a534 100644 --- a/SL/DB/Buchungsgruppe.pm +++ b/SL/DB/Buchungsgruppe.pm @@ -11,46 +11,6 @@ __PACKAGE__->meta->add_relationship( class => 'SL::DB::Chart', column_map => { inventory_accno_id => 'id' }, }, - income_account_0 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { income_accno_id_0 => 'id' }, - }, - income_account_1 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { income_accno_id_1 => 'id' }, - }, - income_account_2 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { income_accno_id_2 => 'id' }, - }, - income_account_3 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { income_accno_id_3 => 'id' }, - }, - expense_account_0 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { expense_accno_id_0 => 'id' }, - }, - expense_account_1 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { expense_accno_id_1 => 'id' }, - }, - expense_account_2 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { expense_accno_id_2 => 'id' }, - }, - expense_account_3 => { - type => 'many to one', - class => 'SL::DB::Chart', - column_map => { expense_accno_id_3 => 'id' }, - }, ); __PACKAGE__->meta->initialize; @@ -59,33 +19,29 @@ __PACKAGE__->meta->initialize; sub income_accno_id { my ($self, $taxzone) = @_; my $taxzone_id = ref $taxzone && $taxzone->isa('SL::DB::TaxZone') ? $taxzone->id : $taxzone; - my $method = 'income_accno_id_' . $taxzone_id; - - return $self->$method; + my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id); + return $taxzone_chart->income_accno_id if $taxzone_chart; } sub expense_accno_id { my ($self, $taxzone) = @_; my $taxzone_id = ref $taxzone && $taxzone->isa('SL::DB::TaxZone') ? $taxzone->id : $taxzone; - my $method = 'expense_accno_id_' . $taxzone_id; - - return $self->$method; + my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id); + return $taxzone_chart->expense_accno_id if $taxzone_chart; } sub income_account { my ($self, $taxzone) = @_; my $taxzone_id = ref $taxzone && $taxzone->isa('SL::DB::TaxZone') ? $taxzone->id : $taxzone; - my $method = 'income_account_' . $taxzone_id; - - return $self->$method; + my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id); + return $taxzone_chart->income_accno if $taxzone_chart; } sub expense_account { my ($self, $taxzone) = @_; my $taxzone_id = ref $taxzone && $taxzone->isa('SL::DB::TaxZone') ? $taxzone->id : $taxzone; - my $method = 'expense_account_' . $taxzone_id; - - return $self->$method; + my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id); + return $taxzone_chart->expense_accno if $taxzone_chart; } 1; diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm index c7d38e56a..76c4af708 100644 --- a/SL/DB/Helper/ALL.pm +++ b/SL/DB/Helper/ALL.pm @@ -98,6 +98,7 @@ use SL::DB::Status; use SL::DB::Tax; use SL::DB::TaxKey; use SL::DB::TaxZone; +use SL::DB::TaxzoneChart; use SL::DB::TodoUserConfig; use SL::DB::TransferType; use SL::DB::Translation; diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 4c777ba1e..6f2eba731 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -178,6 +178,7 @@ my %kivitendo_package_names = ( tax => 'tax', taxkeys => 'tax_key', tax_zones => 'tax_zone', + taxzone_charts => 'taxzone_chart', todo_user_config => 'todo_user_config', transfer_type => 'transfer_type', translation => 'translation', diff --git a/SL/DB/MetaSetup/Buchungsgruppe.pm b/SL/DB/MetaSetup/Buchungsgruppe.pm index bda9c78bf..b81fb1295 100644 --- a/SL/DB/MetaSetup/Buchungsgruppe.pm +++ b/SL/DB/MetaSetup/Buchungsgruppe.pm @@ -10,15 +10,6 @@ __PACKAGE__->meta->table('buchungsgruppen'); __PACKAGE__->meta->columns( description => { type => 'text' }, - expense_accno_id_0 => { type => 'integer' }, - expense_accno_id_1 => { type => 'integer' }, - expense_accno_id_2 => { type => 'integer' }, - expense_accno_id_3 => { type => 'integer' }, - id => { type => 'integer', not_null => 1, sequence => 'id' }, - income_accno_id_0 => { type => 'integer' }, - income_accno_id_1 => { type => 'integer' }, - income_accno_id_2 => { type => 'integer' }, - income_accno_id_3 => { type => 'integer' }, inventory_accno_id => { type => 'integer' }, sortkey => { type => 'integer', not_null => 1 }, ); diff --git a/SL/DB/MetaSetup/TaxzoneChart.pm b/SL/DB/MetaSetup/TaxzoneChart.pm new file mode 100644 index 000000000..f0cd6dcf1 --- /dev/null +++ b/SL/DB/MetaSetup/TaxzoneChart.pm @@ -0,0 +1,44 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::TaxzoneChart; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->table('taxzone_charts'); + +__PACKAGE__->meta->columns( + id => { type => 'serial', not_null => 1 }, + taxzone_id => { type => 'integer', not_null => 1 }, + buchungsgruppen_id => { type => 'integer', not_null => 1 }, + income_accno_id => { type => 'integer', not_null => 1 }, + expense_accno_id => { type => 'integer', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, +); + +__PACKAGE__->meta->primary_key_columns([ 'id' ]); + +__PACKAGE__->meta->allow_inline_column_values(1); + +__PACKAGE__->meta->foreign_keys( + buchungsgruppen => { + class => 'SL::DB::Buchungsgruppe', + key_columns => { buchungsgruppen_id => 'id' }, + }, + + expense_accno => { + class => 'SL::DB::Chart', + key_columns => { expense_accno_id => 'id' }, + }, + + income_accno => { + class => 'SL::DB::Chart', + key_columns => { income_accno_id => 'id' }, + }, +); + +# __PACKAGE__->meta->initialize; + +1; +; diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm index f5a0cd2cc..adb650e06 100644 --- a/SL/DB/Part.pm +++ b/SL/DB/Part.pm @@ -174,7 +174,7 @@ sub get_chart { require SL::DB::Buchungsgruppe; my $bugru = SL::DB::Buchungsgruppe->load_cached($self->buchungsgruppen_id); my $chart_id = ($type eq 'inventory') ? ($self->inventory_accno_id ? $bugru->inventory_accno_id : undef) - : $bugru->call_sub("${type}_accno_id_${taxzone}"); + : $bugru->call_sub("${type}_accno_id", $taxzone); if ($chart_id) { my $chart = $all_charts->{$chart_id} // SL::DB::Chart->load_cached($chart_id)->load; diff --git a/SL/DB/TaxzoneChart.pm b/SL/DB/TaxzoneChart.pm new file mode 100644 index 000000000..540f49b5b --- /dev/null +++ b/SL/DB/TaxzoneChart.pm @@ -0,0 +1,15 @@ +# This file has been auto-generated only because it didn't exist. +# Feel free to modify it at will; it will not be overwritten automatically. + +package SL::DB::TaxzoneChart; + +use strict; + +use SL::DB::MetaSetup::TaxzoneChart; + +__PACKAGE__->meta->initialize; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/IC.pm b/SL/IC.pm index 163f59069..810e226fa 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -319,9 +319,9 @@ sub save { if ($form->{"item"} ne "assembly") { $subq_expense = - qq|(SELECT bg.expense_accno_id_0 - FROM buchungsgruppen bg - WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|; + qq|(SELECT tc.expense_accno_id + FROM taxzone_charts tc + WHERE tc.buchungsgruppen_id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq| and tc.taxzone_id = 0)|; } else { $subq_expense = "NULL"; } @@ -348,7 +348,7 @@ sub save { buchungsgruppen_id = ?, payment_id = ?, inventory_accno_id = $subq_inventory, - income_accno_id = (SELECT bg.income_accno_id_0 FROM buchungsgruppen bg WHERE bg.id = ?), + income_accno_id = (SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = 0 and tc.buchungsgruppen_id = ?), expense_accno_id = $subq_expense, obsolete = ?, image = ?, @@ -1541,17 +1541,21 @@ sub retrieve_accounts { SELECT p.id, p.inventory_accno_id AS is_part, bg.inventory_accno_id, - bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, - bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, + tc.income_accno_id AS income_accno_id, + tc.expense_accno_id AS expense_accno_id, c1.accno AS inventory_accno, c2.accno AS income_accno, c3.accno AS expense_accno FROM parts p LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id + LEFT JOIN taxzone_charts tc on bg.id = tc.buchungsgruppen_id LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id - LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id - LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id - WHERE p.id IN ($in) + LEFT JOIN chart c2 ON tc.income_accno_id = c2.id + LEFT JOIN chart c3 ON tc.expense_accno_id = c3.id + WHERE + tc.taxzone_id = '$form->{taxzone_id}' + and + p.id IN ($in) SQL my $sth_tax = prepare_query($::form, $dbh, <{taxzone_id} * 1; $query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty, - bg.inventory_accno_id, bg.expense_accno_id_${taxzone} AS expense_accno_id, a.transdate - FROM invoice i, ar a, parts p, buchungsgruppen bg + bg.inventory_accno_id, tc.expense_accno_id AS expense_accno_id, a.transdate + FROM invoice i, ar a, parts p, buchungsgruppen bg, taxzone_charts tc WHERE (i.parts_id = p.id) AND (i.parts_id = ?) AND ((i.base_qty + i.allocated) > 0) AND (i.trans_id = a.id) AND (p.buchungsgruppen_id = bg.id) + AND (tc.buchungsgruppen_id = p.buchungsgruppen_id) + AND (tc.taxzone_id = ${taxzone}) ORDER BY transdate|; # ORDER BY transdate guarantees FIFO @@ -980,8 +982,8 @@ sub retrieve_invoice { FROM invoice i JOIN parts p ON (i.parts_id = p.id) LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id) - LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id) - LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id) + LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id) + LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id) LEFT JOIN project pr ON (i.project_id = pr.id) LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) @@ -1248,13 +1250,13 @@ sub retrieve_item { FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id) LEFT JOIN chart c2 ON - ((SELECT income_accno_id_${taxzone_id} - FROM buchungsgruppen - WHERE id = p.buchungsgruppen_id) = c2.id) + ((SELECT tc.income_accno_id + FROM taxzone_charts tc + WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id) LEFT JOIN chart c3 ON - ((SELECT expense_accno_id_${taxzone_id} - FROM buchungsgruppen - WHERE id = p.buchungsgruppen_id) = c3.id) + ((SELECT tc.expense_accno_id + FROM taxzone_charts tc + WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id) LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id) WHERE $where|; diff --git a/SL/IS.pm b/SL/IS.pm index 63420a544..a1fbeaf6e 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -1392,8 +1392,8 @@ sub cogs { c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid FROM invoice i, parts p LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id) - LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id) - LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id) + LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id) + LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id) WHERE (i.parts_id = p.id) AND (i.parts_id = ?) AND ((i.base_qty + i.allocated) < 0) @@ -1661,8 +1661,8 @@ sub retrieve_invoice { LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id) LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id) - LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id) - LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id) + LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id) + LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id) WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|; @@ -1959,13 +1959,13 @@ sub retrieve_item { FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id) LEFT JOIN chart c2 ON - ((SELECT income_accno_id_${taxzone_id} - FROM buchungsgruppen - WHERE id = p.buchungsgruppen_id) = c2.id) + ((SELECT tc.income_accno_id + FROM taxzone_charts tc + WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id) LEFT JOIN chart c3 ON - ((SELECT expense_accno_id_${taxzone_id} - FROM buchungsgruppen - WHERE id = p.buchungsgruppen_id) = c3.id) + ((SELECT tc.expense_accno_id + FROM taxzone_charts tc + WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id) LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id) WHERE $where|; diff --git a/SL/OE.pm b/SL/OE.pm index 8b073b9c5..e2663c513 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -922,8 +922,8 @@ sub retrieve { JOIN parts p ON (o.parts_id = p.id) JOIN oe ON (o.trans_id = oe.id) LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id) - LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id) - LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id) + LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id) + LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id) LEFT JOIN project pr ON (o.project_id = pr.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | . ($form->{id} diff --git a/sql/Pg-upgrade2/convert_taxzone.pl b/sql/Pg-upgrade2/convert_taxzone.pl new file mode 100644 index 000000000..413d772de --- /dev/null +++ b/sql/Pg-upgrade2/convert_taxzone.pl @@ -0,0 +1,69 @@ +# @tag: convert_taxzone +# @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys +# @depends: taxzone_charts +package SL::DBUpgrade2::convert_taxzone; + +use strict; +use utf8; + +use parent qw(SL::DBUpgrade2::Base); + +sub run { + my ($self) = @_; + + # extract all buchungsgruppen data + my $buchungsgruppen_query = <dbh->prepare($buchungsgruppen_query); + $sth->execute || $::form->dberror($buchungsgruppen_query); + + $::form->{buchungsgruppen} = []; + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + push @{ $::form->{buchungsgruppen} }, $ref; + } + $sth->finish; + + # extract all tax_zone data + my $taxzone_query = <dbh->prepare($taxzone_query); + $sth->execute || $::form->dberror($taxzone_query); + + $::form->{taxzones} = []; + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + push @{ $::form->{taxzones} }, $ref; + } + $sth->finish; + + my $taxzone_charts_update_query; + foreach my $taxzone ( @{$::form->{taxzones}} ) { + foreach my $buchungsgruppe ( @{$::form->{buchungsgruppen}} ) { + my $id = $taxzone->{id}; + my $income_accno_id = $buchungsgruppe->{"income_accno_id_$id"}; + my $expense_accno_id = $buchungsgruppe->{"expense_accno_id_$id"}; + # TODO: check if the variables have a value + $taxzone_charts_update_query .= "INSERT INTO taxzone_charts (taxzone_id, buchungsgruppen_id, income_accno_id, expense_accno_id) VALUES ('$taxzone->{id}', '$buchungsgruppe->{id}', $income_accno_id, $expense_accno_id);\n"; + }; + }; + $self->db_query($taxzone_charts_update_query); + + my $clean_buchungsgruppen_query = <dbh->prepare($clean_buchungsgruppen_query); + $sth->execute || $::form->dberror($clean_buchungsgruppen_query); + return 1; +} # end run + +1; diff --git a/sql/Pg-upgrade2/taxzones.sql b/sql/Pg-upgrade2/taxzones.sql new file mode 100644 index 000000000..ba8b30ea7 --- /dev/null +++ b/sql/Pg-upgrade2/taxzones.sql @@ -0,0 +1,19 @@ +-- @tag: taxzone_charts +-- @description: Neue Tabelle für Buchungskonten der Steuerzonen +-- @depends: release_3_0_0 +-- @ignore: 0 + +CREATE TABLE taxzone_charts ( + id SERIAL PRIMARY KEY, + taxzone_id integer NOT NULL, + buchungsgruppen_id integer NOT NULL, + income_accno_id integer NOT NULL, + expense_accno_id integer NOT NULL, + itime timestamp DEFAULT now(), + FOREIGN KEY (taxzone_id) REFERENCES tax_zones (id), + FOREIGN KEY (income_accno_id) REFERENCES chart (id), + FOREIGN KEY (expense_accno_id) REFERENCES chart (id), + FOREIGN KEY (buchungsgruppen_id) REFERENCES buchungsgruppen (id) +); + + -- 2.20.1