Auf Datenbankebene Steuerzonen konfigurierbar gemacht

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

diff --git a/SL/AM.pm b/SL/AM.pm
index 30b1d9301936d3337fb8301e719fc103cf8552ed..c18cfd785b269b24ca15421b06ad0edb85db121b 100644 (file)
--- a/SL/AM.pm
+++ b/SL/AM.pm
@@ -747,26 +747,10 @@ sub buchungsgruppe {
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
   # 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,
   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|;
 
                  FROM buchungsgruppen
                  ORDER BY sortkey|;
 


@@ -794,25 +778,10 @@ sub get_buchungsgruppe {
   my $dbh = $form->dbconnect($myconfig);
 
   if ($form->{id}) {
   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,
     $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);
          FROM buchungsgruppen
          WHERE id = ?|;
     my $sth = $dbh->prepare($query);

diff --git a/SL/DB/Buchungsgruppe.pm b/SL/DB/Buchungsgruppe.pm
index 5f4d831faf11237225f01c46958807506de8f33d..65f75a534d5beca83383b5eaa5ba19aa94b3ab52 100644 (file)
--- 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' },
   },
     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;
 );
 
 __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;
 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;
 }
 
 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;
 }
 
 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;
 }
 
 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;
 }
 
 1;

diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm
index c7d38e56a79bc42a86c5d9d186ae895099699d5c..76c4af7089eba968e42d3f9a430ecdb7dda1e061 100644 (file)
--- 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::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;
 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 4c777ba1e48da3cca5ba10f5b7299db1572a09ea..6f2eba7311c1408d8e25fd39a9557746f399ad9c 100644 (file)
--- 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',
   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',
   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 bda9c78bf022e886d0968a044e659a52a9a51dbd..b81fb1295252770b8455a4ec6b944567a06d84ff 100644 (file)
--- 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' },
 
 __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 },
 );
   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 (file)
index 0000000..f0cd6dc
--- /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 f5a0cd2ccce7e1115bfd252799258d71c88f1ba0..adb650e0646e3bd7db0bd59f507995f1c2b1ee70 100644 (file)
--- 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)
     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;
 
     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 (file)
index 0000000..540f49b
--- /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 163f590696a14956833795e5032290e60c5dfb98..810e226fac194758e20ea143ffa27388f8261ae4 100644 (file)
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -319,9 +319,9 @@ sub save {
 
   if ($form->{"item"} ne "assembly") {
     $subq_expense =
 
   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";
   }
   } else {
     $subq_expense = "NULL";
   }


@@ -348,7 +348,7 @@ sub save {
          buchungsgruppen_id = ?,
          payment_id = ?,
          inventory_accno_id = $subq_inventory,
          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 = ?,
          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,
     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
       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 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, <<SQL);
 SQL
 
   my $sth_tax = prepare_query($::form, $dbh, <<SQL);

diff --git a/SL/IR.pm b/SL/IR.pm
index 931df6b4487849d59318ca6814843200f7f7df65..c13d84b1c7b0f2e19988e0d1a9de892a98cf4242 100644 (file)
--- a/SL/IR.pm
+++ b/SL/IR.pm
@@ -221,13 +221,15 @@ sub post_invoice {
       my $taxzone = $form->{taxzone_id} * 1;
       $query =
         qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty,
       my $taxzone = $form->{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)
            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
 
            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)
         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)
 
         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
            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
        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|;
        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 63420a54484a67bd9505c586e7150770932ef325..a1fbeaf6e97a95f6188713f99442fbdc433c8ec8 100644 (file)
--- 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)
          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)
        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 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|;
 
 
          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
            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
        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|;
        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 8b073b9c58c9c288a421e238d68ddffca67f5246..e2663c513a442afd3beff6c4272704601f28d6c7 100644 (file)
--- 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)
          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}
          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 (file)
index 0000000..413d772
--- /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 = <<SQL;
+      SELECT * from buchungsgruppen;
+SQL
+
+    my $sth = $self->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 = <<SQL;
+      SELECT * from tax_zones;
+SQL
+
+    $sth = $self->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 = <<SQL;
+alter table buchungsgruppen drop column income_accno_id_0;
+alter table buchungsgruppen drop column income_accno_id_1;
+alter table buchungsgruppen drop column income_accno_id_2;
+alter table buchungsgruppen drop column income_accno_id_3;
+alter table buchungsgruppen drop column expense_accno_id_0;
+alter table buchungsgruppen drop column expense_accno_id_1;
+alter table buchungsgruppen drop column expense_accno_id_2;
+alter table buchungsgruppen drop column expense_accno_id_3;
+SQL
+  $sth = $self->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 (file)
index 0000000..ba8b30e
--- /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)
+);
+
+