From ee2a0ae982050060f37ceed52a4f992c2c771216 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Jan=20B=C3=BCren?= <jan@kivitendo.de>
Date: Thu, 24 Mar 2022 15:46:36 +0100
Subject: [PATCH] =?utf8?q?SKR03/SKR04=20Entfernt=20ggf.=20uralte=20falsch?=
 =?utf8?q?=20verkn=C3=BCpfte=20Automatikkonten?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Laut der DATEV Steuerschlüssel Spezifikation darf es ferner
auch kein Automatik-Konto mehrfach bei Steuerschlüssel verwendet werden.
kivi erlaubt das schon immer, falls möglich setzt das skript den
Wert auf unique
---
 .../delete_wrong_charts_for_taxkeys.pl        | 62 ++++++++++++++++++
 .../delete_wrong_charts_for_taxkeys_04.pl     | 63 +++++++++++++++++++
 2 files changed, 125 insertions(+)
 create mode 100644 sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys.pl
 create mode 100644 sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys_04.pl

diff --git a/sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys.pl b/sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys.pl
new file mode 100644
index 000000000..159d4b0a4
--- /dev/null
+++ b/sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys.pl
@@ -0,0 +1,62 @@
+# @tag: delete_wrong_charts_for_taxkeys
+# @description: Uralte falsch angelegte Automatikkonten raus -> Chance auf tax.chart_id unique setzen
+# @depends: release_3_6_0
+# @ignore: 0
+package SL::DBUpgrade2::delete_wrong_charts_for_taxkeys;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+sub delete_chart_id_tax {
+  my $self = shift;
+
+  my $q_fetch = <<SQL;
+    SELECT chart_id
+    FROM tax where chart_id is not null
+    GROUP BY chart_id HAVING COUNT(*) > 1
+SQL
+
+  # skr03
+  my $q_update = <<SQL;
+    UPDATE tax
+    SET chart_id = NULL
+    WHERE chart_id = ?
+    AND rate = 0.16
+    AND (taxkey = 19 OR taxkey = 13)
+    AND EXISTS (SELECT * FROM defaults WHERE coa = 'Germany-DATEV-SKR03EU')
+SQL
+
+  my $h_fetch = $self->dbh->prepare($q_fetch);
+  $h_fetch->execute || $::form->dberror($q_fetch);
+
+  my $h_update_03 = $self->dbh->prepare($q_update);
+
+  while (my $entry = $h_fetch->fetchrow_hashref) {
+    $h_update_03->execute($entry->{chart_id}) || $::form->dberror($q_update);
+  }
+  # might be unique now
+  $h_fetch->execute || $::form->dberror($q_fetch);
+
+  if (!$h_fetch->fetchrow_hashref) {
+    my $q_unique = <<SQL;
+      alter table tax
+      ADD CONSTRAINT chart_id_unique_tax UNIQUE (chart_id)
+SQL
+    my $q_unique_p = $self->dbh->prepare($q_unique);
+    $q_unique_p->execute || $::form->dberror($q_unique_p);
+  }
+}
+
+sub run {
+  my ($self) = @_;
+
+  return 1 unless $self->check_coa('Germany-DATEV-SKR03EU');
+
+  $self->delete_chart_id_tax;
+
+  return 1;
+}
+
+1;
diff --git a/sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys_04.pl b/sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys_04.pl
new file mode 100644
index 000000000..95e408f7f
--- /dev/null
+++ b/sql/Pg-upgrade2/delete_wrong_charts_for_taxkeys_04.pl
@@ -0,0 +1,63 @@
+# @tag: delete_wrong_charts_for_taxkeys_04
+# @description: SKR04: Uralte falsch angelegte Automatikkonten raus -> Chance auf tax.chart_id unique setzen
+# @depends: release_3_6_0
+# @ignore: 0
+package SL::DBUpgrade2::delete_wrong_charts_for_taxkeys_04;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+sub delete_chart_id_tax {
+  my $self = shift;
+
+  my $q_fetch = <<SQL;
+    SELECT chart_id
+    FROM tax where chart_id is not null
+    GROUP BY chart_id HAVING COUNT(*) > 1
+SQL
+
+  # SKR04
+  my $q_update_04 = <<SQL;
+    UPDATE tax
+    SET chart_id = NULL
+    WHERE chart_id = ?
+    AND rate = 0.16
+    AND (taxkey = 3 OR taxkey = 9)
+    AND EXISTS (SELECT * FROM defaults WHERE coa = 'Germany-DATEV-SKR04EU')
+SQL
+
+
+  my $h_fetch = $self->dbh->prepare($q_fetch);
+  $h_fetch->execute || $::form->dberror($q_fetch);
+
+  my $h_update_04 = $self->dbh->prepare($q_update_04);
+
+  while (my $entry = $h_fetch->fetchrow_hashref) {
+    $h_update_04->execute($entry->{chart_id}) || $::form->dberror($q_update_04);
+  }
+  # might be unique now
+  $h_fetch->execute || $::form->dberror($q_fetch);
+
+  if (!$h_fetch->fetchrow_hashref) {
+    my $q_unique = <<SQL;
+      alter table tax
+      ADD CONSTRAINT chart_id_unique_tax UNIQUE (chart_id)
+SQL
+    my $q_unique_p = $self->dbh->prepare($q_unique);
+    $q_unique_p->execute || $::form->dberror($q_unique_p);
+  }
+}
+
+sub run {
+  my ($self) = @_;
+
+  return 1 unless $self->check_coa('Germany-DATEV-SKR04EU');
+
+  $self->delete_chart_id_tax;
+
+  return 1;
+}
+
+1;
-- 
2.20.1