From 79c0cb3ee3c502c560d4b541eeb5e662fe50bbbd Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet-services.de>
Date: Fri, 19 Jul 2019 15:16:25 +0200
Subject: [PATCH] =?utf8?q?Mahnungs-Tabelle:=20Fremdschl=C3=BCsselverkn?=
 =?utf8?q?=C3=BCpfung=20auf=20Rechnungstabelle?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 SL/DB/MetaSetup/Dunning.pm                         |  7 ++++++-
 scripts/rose_auto_create_model.pl                  |  2 ++
 .../dunning_foreign_key_for_trans_id.sql           | 14 ++++++++++++++
 3 files changed, 22 insertions(+), 1 deletion(-)
 create mode 100644 sql/Pg-upgrade2/dunning_foreign_key_for_trans_id.sql

diff --git a/SL/DB/MetaSetup/Dunning.pm b/SL/DB/MetaSetup/Dunning.pm
index a0098e776..ee6ab4ef4 100644
--- a/SL/DB/MetaSetup/Dunning.pm
+++ b/SL/DB/MetaSetup/Dunning.pm
@@ -33,10 +33,15 @@ __PACKAGE__->meta->foreign_keys(
     key_columns => { dunning_config_id => 'id' },
   },
 
-  fee_interest_ar => {
+  fee_interest_invoice => {
     class       => 'SL::DB::Invoice',
     key_columns => { fee_interest_ar_id => 'id' },
   },
+
+  invoice => {
+    class       => 'SL::DB::Invoice',
+    key_columns => { trans_id => 'id' },
+  },
 );
 
 1;
diff --git a/scripts/rose_auto_create_model.pl b/scripts/rose_auto_create_model.pl
index 5a0f7c23c..2ab34cb90 100755
--- a/scripts/rose_auto_create_model.pl
+++ b/scripts/rose_auto_create_model.pl
@@ -82,6 +82,8 @@ our %foreign_key_name_map     = (
 
     assembly                  => { parts_id => 'part', id => 'assembly_part' },
     assortment_items          => { parts_id => 'part' },
+
+    dunning                   => { trans_id => 'invoice', fee_interest_ar_id => 'fee_interest_invoice' },
   },
 );
 
diff --git a/sql/Pg-upgrade2/dunning_foreign_key_for_trans_id.sql b/sql/Pg-upgrade2/dunning_foreign_key_for_trans_id.sql
new file mode 100644
index 000000000..e84892e8a
--- /dev/null
+++ b/sql/Pg-upgrade2/dunning_foreign_key_for_trans_id.sql
@@ -0,0 +1,14 @@
+-- @tag: dunning_foreign_key_for_trans_id
+-- @description: Mahnungstabelle: Fremdschlüssel für Verknüpfung zur Rechnungstabelle
+-- @depends: release_3_5_3
+DELETE FROM dunning
+WHERE NOT EXISTS (
+  SELECT ar.id
+  FROM ar
+  WHERE ar.id = dunning.trans_id
+);
+
+ALTER TABLE dunning
+ADD CONSTRAINT dunning_trans_id_fkey
+FOREIGN KEY (trans_id) REFERENCES ar (id)
+ON DELETE CASCADE;
-- 
2.20.1