From c77e88d3c1dd8e53079c246e2571259f3ac27627 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Fri, 22 Feb 2019 10:35:32 +0100 Subject: [PATCH] Neue Helper-Tabelle SL/DB/BankTransactionAccTrans.pm MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Hintergrund: Verbuchte Bankbewegungen sind nur über einen löschbaren RecordLink aktuell zuordenbar. Das macht ein verlässliche Aussage über die Verbuchungen der Bankbewegung schwierig. Besser wäre es eine Tabelle reconciliation_links direkt bei der Verbuchung zu füllen und die gesetzten Constraints so zu lassen (ER-Fehler mit aussagekräftigerer Fehlerwarnung an den Nutzer) .... Da die Bankverbuchungen seit 66d468b09 (2016) in einer Transaktion laufen, wird über record_link und itime eine Rekonstruktion der Zusammenhänge für die alten Einträge versucht herzustellen. Wichtig: Dieser Commit ist Vorbedingung für das Neuverbuchen von importierten Bankbewegungen. Zusätzlich beißt sich das mit der Anforderung das Zahlungen manuell vom Anwender geändert werden können (s.a. hierzu c923fff436). --- SL/DB/BankTransactionAccTrans.pm | 13 +++ SL/DB/Helper/Mappings.pm | 1 + SL/DB/Manager/BankTransactionAccTrans.pm | 14 ++++ SL/DB/MetaSetup/BankTransactionAccTrans.pm | 54 +++++++++++++ sql/Pg-upgrade2/record_links_bt_acc_trans.pl | 84 ++++++++++++++++++++ 5 files changed, 166 insertions(+) create mode 100644 SL/DB/BankTransactionAccTrans.pm create mode 100644 SL/DB/Manager/BankTransactionAccTrans.pm create mode 100644 SL/DB/MetaSetup/BankTransactionAccTrans.pm create mode 100644 sql/Pg-upgrade2/record_links_bt_acc_trans.pl diff --git a/SL/DB/BankTransactionAccTrans.pm b/SL/DB/BankTransactionAccTrans.pm new file mode 100644 index 000000000..ef2052ccc --- /dev/null +++ b/SL/DB/BankTransactionAccTrans.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::BankTransactionAccTrans; + +use strict; + +use SL::DB::MetaSetup::BankTransactionAccTrans; +use SL::DB::Manager::BankTransactionAccTrans; + +__PACKAGE__->meta->initialize; + +1; diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 12e56d060..4d1c5af31 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -105,6 +105,7 @@ my %kivitendo_package_names = ( ap => 'purchase_invoice', bank_accounts => 'bank_account', bank_transactions => 'bank_transaction', + bank_transaction_acc_trans => 'bank_transaction_acc_trans', buchungsgruppen => 'buchungsgruppe', bin => 'bin', business => 'business', diff --git a/SL/DB/Manager/BankTransactionAccTrans.pm b/SL/DB/Manager/BankTransactionAccTrans.pm new file mode 100644 index 000000000..6e05779d3 --- /dev/null +++ b/SL/DB/Manager/BankTransactionAccTrans.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::BankTransactionAccTrans; + +use strict; + +use parent qw(SL::DB::Helper::Manager); + +sub object_class { 'SL::DB::BankTransactionAccTrans' } + +__PACKAGE__->make_manager_methods; + +1; diff --git a/SL/DB/MetaSetup/BankTransactionAccTrans.pm b/SL/DB/MetaSetup/BankTransactionAccTrans.pm new file mode 100644 index 000000000..6428c997e --- /dev/null +++ b/SL/DB/MetaSetup/BankTransactionAccTrans.pm @@ -0,0 +1,54 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::BankTransactionAccTrans; + +use strict; + +use parent qw(SL::DB::Object); + +__PACKAGE__->meta->table('bank_transaction_acc_trans'); + +__PACKAGE__->meta->columns( + acc_trans_id => { type => 'bigint', not_null => 1 }, + ap_id => { type => 'integer' }, + ar_id => { type => 'integer' }, + bank_transaction_id => { type => 'integer', not_null => 1 }, + gl_id => { type => 'integer' }, + id => { type => 'serial', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, +); + +__PACKAGE__->meta->primary_key_columns([ 'id' ]); + +__PACKAGE__->meta->allow_inline_column_values(1); + +__PACKAGE__->meta->foreign_keys( + acc_transaction => { + class => 'SL::DB::AccTransaction', + key_columns => { acc_trans_id => 'acc_trans_id' }, + }, + + ap => { + class => 'SL::DB::PurchaseInvoice', + key_columns => { ap_id => 'id' }, + }, + + ar => { + class => 'SL::DB::Invoice', + key_columns => { ar_id => 'id' }, + }, + + bank_transaction => { + class => 'SL::DB::BankTransaction', + key_columns => { bank_transaction_id => 'id' }, + }, + + gl => { + class => 'SL::DB::GLTransaction', + key_columns => { gl_id => 'id' }, + }, +); + +1; +; diff --git a/sql/Pg-upgrade2/record_links_bt_acc_trans.pl b/sql/Pg-upgrade2/record_links_bt_acc_trans.pl new file mode 100644 index 000000000..e84155ba9 --- /dev/null +++ b/sql/Pg-upgrade2/record_links_bt_acc_trans.pl @@ -0,0 +1,84 @@ +# @tag: record_links_bt_acc_trans +# @description: RecordLinks von bt nach acc_trans +# @depends: release_3_5_3 +package SL::DBUpgrade2::record_links_bt_acc_trans; + +use strict; +use utf8; + +use parent qw(SL::DBUpgrade2::Base); + +use SL::DBUtils; +use SL::RecordLinks; + + +sub run { + my ($self) = @_; + + my $query_table = + qq| + + CREATE SEQUENCE bank_transaction_acc_trans_id_seq; + CREATE TABLE bank_transaction_acc_trans ( + id integer NOT NULL DEFAULT nextval('bank_transaction_acc_trans_id_seq'), + bank_transaction_id integer NOT NULL, + acc_trans_id bigint NOT NULL, + ar_id integer, + ap_id integer, + gl_id integer, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + PRIMARY KEY (bank_transaction_id, acc_trans_id), + FOREIGN KEY (bank_transaction_id) REFERENCES bank_transactions (id), + FOREIGN KEY (acc_trans_id) REFERENCES acc_trans (acc_trans_id), + FOREIGN KEY (ar_id) REFERENCES ar (id), + FOREIGN KEY (ap_id) REFERENCES ap (id), + FOREIGN KEY (gl_id) REFERENCES gl (id));|; + + $self->db_query($query_table); + + + my $query = qq|SELECT to_id, itime, from_id, to_table + FROM record_links + WHERE from_table='bank_transactions'|; + + my $sth = $self->dbh->prepare($query); + + my $sql = <dbh->prepare($sql) or die $self->dbh->errstr; + + my $sql_insert = <dbh->prepare($sql_insert) or die $self->dbh->errstr; + + + # get all current record links from bank to arap + $sth->execute() or die $self->dbh->errstr; + + while (my $rl_ref = $sth->fetchrow_hashref("NAME_lc")) { + + # get all concurrent acc_trans entries (payment) for this transaction + $sth_acc_trans_ids->execute($rl_ref->{to_id}, $rl_ref->{itime}) or die $self->dbh->errstr; + while (my $ac_ref = $sth_acc_trans_ids->fetchrow_hashref("NAME_lc")) { + my $ar_id = $rl_ref->{to_table} eq 'ar' ? $rl_ref->{to_id} : undef; + my $ap_id = $rl_ref->{to_table} eq 'ap' ? $rl_ref->{to_id} : undef; + my $gl_id = $rl_ref->{to_table} eq 'gl' ? $rl_ref->{to_id} : undef; + $sth_insert->execute($rl_ref->{from_id},$ac_ref->{acc_trans_id}, + $ar_id, $ap_id, $gl_id) or die $self->dbh->errstr; + } + } + return 1; +} + +1; -- 2.20.1