From 1ac4c07d0d3e6ffa8b39221dd865a6080bbae808 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 1 Oct 2015 14:47:28 +0200 Subject: [PATCH] SEPA: speichern, in welchen Nachrichten-IDs (MsgId) Exporte verwendet wurden MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 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. --- SL/DB/Helper/ALL.pm | 1 + SL/DB/Helper/Mappings.pm | 1 + SL/DB/Manager/SepaExportMessageId.pm | 14 ++++++++++ SL/DB/MetaSetup/SepaExportMessageId.pm | 27 +++++++++++++++++++ SL/DB/SepaExport.pm | 12 ++++++--- SL/DB/SepaExportMessageId.pm | 13 +++++++++ bin/mozilla/sepa.pl | 10 +++++++ .../sepa_contained_in_message_ids.sql | 11 ++++++++ 8 files changed, 85 insertions(+), 4 deletions(-) create mode 100644 SL/DB/Manager/SepaExportMessageId.pm create mode 100644 SL/DB/MetaSetup/SepaExportMessageId.pm create mode 100644 SL/DB/SepaExportMessageId.pm create mode 100644 sql/Pg-upgrade2/sepa_contained_in_message_ids.sql diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm index 3ab29433b..356d83194 100644 --- 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 aa25bd449..976dfa742 100644 --- 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 index 000000000..d7d9f8931 --- /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 index 000000000..2162f3852 --- /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 7bec1b985..137d0485b 100644 --- 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 index 000000000..4660dfcca --- /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 ac018e258..8e5f807ed 100755 --- 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 index 000000000..0a4c6f199 --- /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 +); -- 2.20.1