SEPA: speichern, in welchen Nachrichten-IDs (MsgId) Exporte verwendet wurden

Manche Banken zeigen in ihren Auszügen nur die MsgId an, und es gibt
keine Möglichkeit, die darin enthaltenen einzelnen Überweisungen
angezeigt zu bekommen.

Diese MsgId muss allerdings bei jeder eingereichten Nachricht eindeutig
sein. Daher wird sie bei jedem Download zufällig erzeugt. Weiterhin kann
jeder Download eine beliebige Kombination von Exporten beinhalten.

Um eine einfacherer Nachverfolgbarkeit für solche Fälle zu ermöglichen,
wird nun bei jedem Download die dort verwendete MsgId bei allen
beteiligten Exporten gespeichert.

diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm
index 3ab2943..356d831 100644 (file)
--- a/SL/DB/Helper/ALL.pm
+++ b/SL/DB/Helper/ALL.pm
@@ -103,6 +103,7 @@ use SL::DB::RequirementSpec;
 use SL::DB::SchemaInfo;
 use SL::DB::SepaExport;
 use SL::DB::SepaExportItem;
+use SL::DB::SepaExportMessageId;
 use SL::DB::Shipto;
 use SL::DB::Status;
 use SL::DB::Tax;

diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm
index aa25bd4..976dfa7 100644 (file)
--- a/SL/DB/Helper/Mappings.pm
+++ b/SL/DB/Helper/Mappings.pm
@@ -182,6 +182,7 @@ my %kivitendo_package_names = (
   requirement_specs                    => 'RequirementSpec',
   sepa_export                    => 'sepa_export',
   sepa_export_items              => 'sepa_export_item',
+  sepa_export_message_ids        => 'SepaExportMessageId',
   schema_info                    => 'schema_info',
   shipto                         => 'shipto',
   status                         => 'status',

diff --git a/SL/DB/Manager/SepaExportMessageId.pm b/SL/DB/Manager/SepaExportMessageId.pm
new file mode 100644 (file)
index 0000000..d7d9f89
--- /dev/null
+++ b/SL/DB/Manager/SepaExportMessageId.pm
@@ -0,0 +1,14 @@
+# 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::Manager::SepaExportMessageId;
+
+use strict;
+
+use parent qw(SL::DB::Helper::Manager);
+
+sub object_class { 'SL::DB::SepaExportMessageId' }
+
+__PACKAGE__->make_manager_methods;
+
+1;

diff --git a/SL/DB/MetaSetup/SepaExportMessageId.pm b/SL/DB/MetaSetup/SepaExportMessageId.pm
new file mode 100644 (file)
index 0000000..2162f38
--- /dev/null
+++ b/SL/DB/MetaSetup/SepaExportMessageId.pm
@@ -0,0 +1,27 @@
+# This file has been auto-generated. Do not modify it; it will be overwritten
+# by rose_auto_create_model.pl automatically.
+package SL::DB::SepaExportMessageId;
+
+use strict;
+
+use parent qw(SL::DB::Object);
+
+__PACKAGE__->meta->table('sepa_export_message_ids');
+
+__PACKAGE__->meta->columns(
+  id             => { type => 'serial', not_null => 1 },
+  message_id     => { type => 'text', not_null => 1 },
+  sepa_export_id => { type => 'integer', not_null => 1 },
+);
+
+__PACKAGE__->meta->primary_key_columns([ 'id' ]);
+
+__PACKAGE__->meta->foreign_keys(
+  sepa_export => {
+    class       => 'SL::DB::SepaExport',
+    key_columns => { sepa_export_id => 'id' },
+  },
+);
+
+1;
+;

diff --git a/SL/DB/SepaExport.pm b/SL/DB/SepaExport.pm
index 7bec1b9..137d048 100644 (file)
--- a/SL/DB/SepaExport.pm
+++ b/SL/DB/SepaExport.pm
@@ -1,15 +1,19 @@
-# 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::SepaExport;
 
 use strict;
 
 use SL::DB::MetaSetup::SepaExport;
 
+__PACKAGE__->meta->add_relationship(
+  message_ids  => {
+    type       => 'one to many',
+    class      => 'SL::DB::SepaExportMessageId',
+    column_map => { id => 'sepa_export_id' },
+  },
+);
+
 __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/DB/SepaExportMessageId.pm b/SL/DB/SepaExportMessageId.pm
new file mode 100644 (file)
index 0000000..4660dfc
--- /dev/null
+++ b/SL/DB/SepaExportMessageId.pm
@@ -0,0 +1,13 @@
+# 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::SepaExportMessageId;
+
+use strict;
+
+use SL::DB::MetaSetup::SepaExportMessageId;
+use SL::DB::Manager::SepaExportMessageId;
+
+__PACKAGE__->meta->initialize;
+
+1;

diff --git a/bin/mozilla/sepa.pl b/bin/mozilla/sepa.pl
index ac018e2..8e5f807 100755 (executable)
--- a/bin/mozilla/sepa.pl
+++ b/bin/mozilla/sepa.pl
@@ -6,6 +6,7 @@ use POSIX qw(strftime);
 
 use Data::Dumper;
 use SL::DB::BankAccount;
+use SL::DB::SepaExport;
 use SL::Chart;
 use SL::CT;
 use SL::Form;
@@ -546,6 +547,15 @@ sub bank_transfer_download_sepa_xml {
                                  'date_of_signature' => $item->{mandate_date_of_signature}, });
   }
 
+  # Store the message ID used in each of the entries in order to
+  # facilitate finding them by looking at bank statements.
+  foreach my $id (@ids) {
+    SL::DB::SepaExportMessageId->new(
+      sepa_export_id => $id,
+      message_id     => $message_id,
+    )->save;
+  }
+
   my $xml = $sepa_xml->to_xml();
 
   print $cgi->header('-type'                => 'application/octet-stream',

diff --git a/sql/Pg-upgrade2/sepa_contained_in_message_ids.sql b/sql/Pg-upgrade2/sepa_contained_in_message_ids.sql
new file mode 100644 (file)
index 0000000..0a4c6f1
--- /dev/null
+++ b/sql/Pg-upgrade2/sepa_contained_in_message_ids.sql
@@ -0,0 +1,11 @@
+-- @tag: sepa_contained_in_message_ids
+-- @description: SEPA: Feld zum Merken, in welchen XML-Dokumenten (MsgId) ein Export vorkam
+-- @depends: release_3_3_0
+CREATE TABLE sepa_export_message_ids (
+  id             SERIAL,
+  sepa_export_id INTEGER NOT NULL,
+  message_id     TEXT    NOT NULL,
+
+  PRIMARY KEY (id),
+  FOREIGN KEY (sepa_export_id) REFERENCES sepa_export (id) ON DELETE CASCADE
+);