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 3ab29433b6868cf721c296127362ced2fd1652ab..356d83194a6c10d54ca13f1fcbabbc238b863d98 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::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;
 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 aa25bd4492dd58181e2694df3bac41f993386eaa..976dfa7428b26e172ebd521c004d8802d800d2da 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',
   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',
   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 7bec1b9857728635a189db391f9a3d3447eaf83d..137d0485b570d416f9daebf7509d0ee7c8403cdd 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 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;
 
 __PACKAGE__->meta->initialize;
 

-# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

 __PACKAGE__->meta->make_manager_class;
 
 1;
 __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 ac018e2580f4a9fe45430bcf291e600a93009310..8e5f807ed3e7e7655e79725a126a73b0963a10f7 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 Data::Dumper;
 use SL::DB::BankAccount;

+use SL::DB::SepaExport;

 use SL::Chart;
 use SL::CT;
 use SL::Form;
 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}, });
   }
 
                                  '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',
   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
+);