From 14304a6a27f1057646fc088364fd4625c24860dd Mon Sep 17 00:00:00 2001 From: "Martin Helmling martin.helmling@octosoft.eu" Date: Fri, 16 Sep 2016 12:44:20 +0200 Subject: [PATCH] MT940-Import: Transaktionstype mit in BankTransaction MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Eine Erweiterung um z.B. Sammellastschriften zu erkennen Dazu wurde ein eigenes Importprofil für aqbanking-cli erstellt und die Tabelle bank_transactions erweitert. Das Profile ist in users/aqbanking.conf --- SL/Controller/CsvImport/BankTransaction.pm | 4 +- SL/DB/MetaSetup/BankTransaction.pm | 2 + SL/Helper/MT940.pm | 13 ++++- locale/de/all | 2 + sql/Pg-upgrade2/bank_transactions_type.sql | 6 ++ users/aqbanking.conf | 66 ++++++++++++++++++++++ 6 files changed, 89 insertions(+), 4 deletions(-) create mode 100644 sql/Pg-upgrade2/bank_transactions_type.sql create mode 100644 users/aqbanking.conf diff --git a/SL/Controller/CsvImport/BankTransaction.pm b/SL/Controller/CsvImport/BankTransaction.pm index 2c2704106..5397d27d9 100644 --- a/SL/Controller/CsvImport/BankTransaction.pm +++ b/SL/Controller/CsvImport/BankTransaction.pm @@ -88,7 +88,6 @@ sub setup_displayable_columns { $self->SUPER::setup_displayable_columns; - # TODO: don't show fields cleared, invoice_amount and transaction_id in the help text, as these should not be imported $self->add_displayable_columns({ name => 'local_bank_code', description => $::locale->text('Own bank code') }, { name => 'local_account_number', description => $::locale->text('Own bank account number or IBAN') }, { name => 'local_bank_account_id', description => $::locale->text('ID of own bank account') }, @@ -101,6 +100,8 @@ sub setup_displayable_columns { { name => 'currency_id', description => $::locale->text('Currency (database ID)') }, { name => 'remote_name', description => $::locale->text('Name of the goal/source (if field names remote_name and remote_name_1 exist they will be combined into field "remote_name")') }, { name => 'purpose', description => $::locale->text('Purpose (if field names purpose, purpose1, purpose2 ... exist they will all combined into the field "purpose")') }, + { name => 'transactionCode', description => $::locale->text('Transaction Code') }, + { name => 'transactionText', description => $::locale->text('Transaction Text') }, ); } @@ -148,7 +149,6 @@ sub check_bank_account { $object->local_bank_account_id($bank_account->id); $entry->{info_data}->{local_bank_name} = $bank_account->name; } - return $object->local_bank_account_id ? 1 : 0; } diff --git a/SL/DB/MetaSetup/BankTransaction.pm b/SL/DB/MetaSetup/BankTransaction.pm index 9f6e17dac..77a24c94e 100644 --- a/SL/DB/MetaSetup/BankTransaction.pm +++ b/SL/DB/MetaSetup/BankTransaction.pm @@ -21,6 +21,8 @@ __PACKAGE__->meta->columns( remote_bank_code => { type => 'text' }, remote_name => { type => 'text' }, transaction_id => { type => 'integer' }, + transactioncode => { type => 'text' }, + transactiontext => { type => 'text' }, transdate => { type => 'date', not_null => 1 }, valutadate => { type => 'date', not_null => 1 }, ); diff --git a/SL/Helper/MT940.pm b/SL/Helper/MT940.pm index 5c8865d02..b4d7fe5b3 100644 --- a/SL/Helper/MT940.pm +++ b/SL/Helper/MT940.pm @@ -1,6 +1,8 @@ package SL::Helper::MT940; use strict; +use File::Path qw(mkpath); +use File::Copy qw(copy); sub convert_mt940_data { my ($mt940_data) = @_; @@ -13,11 +15,18 @@ sub convert_mt940_data { $sfile->fh->print($mt940_data); $sfile->fh->close; + my $todir = $sfile->get_path . '/imexporters/csv/profiles'; + mkpath $todir; + File::Copy::copy('users/aqbanking.conf', $todir.'/kivi.conf'); + my $aqbin = $::lx_office_conf{applications}->{aqbanking}; die "Can't find aqbanking-cli, please check your configuration file.\n" unless -f $aqbin; - my $cmd = "$aqbin --cfgdir=\"" . $sfile->get_path . "\" import --importer=\"swift\" --profile=\"SWIFT-MT940\" -f " . $sfile->get_path . "/$import_filename | $aqbin --cfgdir=\"" . $sfile->get_path . "\" listtrans --exporter=\"csv\" --profile=\"AqMoney2\" "; + my $cmd = "$aqbin --cfgdir=\"" . $sfile->get_path . "\" import --importer=\"swift\" --profile=\"SWIFT-MT940\" -f " . + $sfile->get_path . "/$import_filename | $aqbin --cfgdir=\"" . $sfile->get_path . "\" listtrans --exporter=\"csv\" --profile=kivi 2> /dev/null "; - my $converted_data = '"empty";"local_bank_code";"local_account_number";"remote_bank_code";"remote_account_number";"transdate";"valutadate";"amount";"currency";"remote_name";"remote_name_1";"purpose";"purpose1";"purpose2";"purpose3";"purpose4";"purpose5";"purpose6";"purpose7";"purpose8";"purpose9";"purpose10";"purpose11"' . "\n"; + my $converted_data = '"empty";"local_bank_code";"local_account_number";"remote_bank_code";"remote_account_number";"transdate";"valutadate";"amount";'. + '"currency";"remote_name";"remote_name_1";"purpose";"purpose1";"purpose2";"purpose3";"purpose4";"purpose5";"purpose6";"purpose7";"purpose8";"purpose9";'. + '"purpose10";"purpose11";"transactionKey";"customerReference";"bankReference";"transactionCode";"transactionText"'."\n"; open my $mt, "-|", "$cmd" || die "Problem with executing aqbanking\n"; my $headerline = <$mt>; # discard original aqbanking header line diff --git a/locale/de/all b/locale/de/all index be38d44cd..cd4fe461f 100755 --- a/locale/de/all +++ b/locale/de/all @@ -3187,8 +3187,10 @@ $self->{texts} = { 'Trans Type' => 'Transfertyp', 'Transaction' => 'Buchung', 'Transaction %d cancelled.' => 'Buchung %d erfolgreich storniert.', + 'Transaction Code' => 'Transaktionscode', 'Transaction Date missing!' => 'Buchungsdatum fehlt!', 'Transaction ID missing.' => 'Die Buchungs-ID fehlt.', + 'Transaction Text' => 'Transaktionstext', 'Transaction deleted!' => 'Buchung gelöscht!', 'Transaction description' => 'Vorgangsbezeichnung', 'Transaction has already been cancelled!' => 'Diese Buchung wurde bereits storniert.', diff --git a/sql/Pg-upgrade2/bank_transactions_type.sql b/sql/Pg-upgrade2/bank_transactions_type.sql new file mode 100644 index 000000000..9a3da5389 --- /dev/null +++ b/sql/Pg-upgrade2/bank_transactions_type.sql @@ -0,0 +1,6 @@ +-- @tag: bank_transactions_type +-- @description: Erweitern der Tabelle bank_transactions mit Typ der Transaktion. +-- @depends: bank_transactions + +ALTER TABLE bank_transactions ADD COLUMN transactionCode TEXT; +ALTER TABLE bank_transactions ADD COLUMN transactionText TEXT; diff --git a/users/aqbanking.conf b/users/aqbanking.conf new file mode 100644 index 000000000..01745d8d4 --- /dev/null +++ b/users/aqbanking.conf @@ -0,0 +1,66 @@ + +char name="kivi" +char shortDescr="Profile for kivitendo CSV files" +char version="5.6.4" +char longDescr="This profile supports the CSV format used by AqMoney2" +int import="1" +int export="1" + +char type="csv" +char groupNames="transaction", "transfer", "debitnote", "line" +char dateFormat="YYYYMMDD" +int utc="0" + +params { + # if 1 then values are quoted + quote="1" + + # if 1 then a title line will be written containing the names of each + # column + title="1" + + # special values are "TAB" and "SPACE" + delimiter=";" + + # this is the group name looked for in the given data to GWEN_CSV_Write + # if not given then all groups match + #group="transaction" + + # this group contains the definition for each column + # each variable in this group has its number as name + # you can use index variables in the names (using square brackets) + # the value of each of these variables is the name of a variable to be looked + # up in the data given to GWEN_CSV_Write + columns { + 1="empty" + 2="localBankCode" + 3="localAccountNumber" + 4="remoteBankCode" + 5="remoteAccountNumber" + 6="date" + 7="valutadate" + 8="value/value" + 9="value/currency" + 10="remoteName[0]" + 11="remoteName[1]" + 12="purpose[0]" + 13="purpose[1]" + 14="purpose[2]" + 15="purpose[3]" + 16="purpose[4]" + 17="purpose[5]" + 18="purpose[6]" + 19="purpose[7]" + 20="purpose[8]" + 21="purpose[9]" + 22="purpose[10]" + 23="purpose[11]" + 24="transactionKey" + 25="customerReference" + 26="bankReference" + 27="transactionCode" + 28="transactionText" + } # columns + +} # params + -- 2.20.1