From 0ce1b04e2a9bbd52692e118debb272e628f327f4 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Thu, 23 Apr 2009 10:31:10 +0000 Subject: [PATCH] Erweiterung der Kundenmaske um IBAN und BIC - Patch aus xplace Projekt --- SL/BankAccount.pm | 98 +++++++++++++++++++ SL/CT.pm | 41 ++++++++ locale/de/all | 2 + sql/Pg-upgrade2/bank_accounts.sql | 23 +++++ templates/webpages/ct/form_header_de.html | 15 ++- templates/webpages/ct/form_header_master.html | 15 ++- 6 files changed, 186 insertions(+), 8 deletions(-) create mode 100644 SL/BankAccount.pm create mode 100644 sql/Pg-upgrade2/bank_accounts.sql diff --git a/SL/BankAccount.pm b/SL/BankAccount.pm new file mode 100644 index 000000000..4194d5c5c --- /dev/null +++ b/SL/BankAccount.pm @@ -0,0 +1,98 @@ +package SL::BankAccount; + +use strict; + +use SL::DBUtils; + +sub save { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + if (!$params{id}) { + ($params{id}) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('id')|); + do_query($form, $dbh, qq|INSERT INTO bank_accounts (id, chart_id) + VALUES (?, (SELECT id FROM chart LIMIT 1))|, conv_i($params{id})); + } + + my $query = + qq|UPDATE bank_accounts + SET account_number = ?, bank_code = ?, bank = ?, iban = ?, bic = ?, chart_id = ? + WHERE id = ?|; + my @values = (@params{qw(account_number bank_code bank iban bic)}, conv_i($params{chart_id}), conv_i($params{id})); + + do_query($form, $dbh, $query, @values); + + $dbh->commit() unless ($params{dbh}); + + $main::lxdebug->leave_sub(); + + return $params{id}; +} + +sub retrieve { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(id)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $query = qq|SELECT * FROM bank_accounts WHERE id = ?|; + my $account = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id})); + + $main::lxdebug->leave_sub(); + + return $account; +} + +sub list { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my %sort_columns = ( + 'account_number' => [ 'ba.account_number', ], + 'bank_code' => [ 'ba.bank_code', 'ba.account_number', ], + 'bank' => [ 'ba.bank', 'ba.account_number', ], + 'iban' => [ 'ba.iban', 'ba.account_number', ], + 'bic' => [ 'ba.bic', 'ba.account_number', ], + 'chart_accno' => [ 'c.accno', ], + 'chart_description' => [ 'c.description', ], + ); + + my %sort_spec = create_sort_spec('defs' => \%sort_columns, 'default' => 'bank', 'column' => $params{sortorder}, 'dir' => $params{sortdir}); + + my $query = + qq|SELECT ba.id, ba.account_number, ba.bank_code, ba.bank, ba.iban, ba.bic, ba.chart_id, + c.accno AS chart_accno, c.description AS chart_description + FROM bank_accounts ba + LEFT JOIN chart c ON (ba.chart_id = c.id) + ORDER BY $sort_spec{sql}|; + + my $results = selectall_hashref_query($form, $dbh, $query); + + $main::lxdebug->leave_sub(); + + return $results; +} + + +1; diff --git a/SL/CT.pm b/SL/CT.pm index 3df827a6d..f2745fb02 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -306,6 +306,8 @@ sub save_customer { qq|account_number = ?, | . qq|bank_code = ?, | . qq|bank = ?, | . + qq|iban = ?, | . + qq|bic = ?, | . qq|obsolete = ?, | . qq|direct_debit = ?, | . qq|ustid = ?, | . @@ -345,6 +347,8 @@ sub save_customer { $form->{account_number}, $form->{bank_code}, $form->{bank}, + $form->{iban}, + $form->{bic}, $form->{obsolete} ? 't' : 'f', $form->{direct_debit} ? 't' : 'f', $form->{ustid}, @@ -511,6 +515,8 @@ sub save_vendor { qq| account_number = ?, | . qq| bank_code = ?, | . qq| bank = ?, | . + qq| iban = ?, | . + qq| bic = ?, | . qq| obsolete = ?, | . qq| direct_debit = ?, | . qq| ustid = ?, | . @@ -548,6 +554,8 @@ sub save_vendor { $form->{account_number}, $form->{bank_code}, $form->{bank}, + $form->{iban}, + $form->{bic}, $form->{obsolete} ? 't' : 'f', $form->{direct_debit} ? 't' : 'f', $form->{ustid}, @@ -1057,4 +1065,37 @@ sub delete_shipto { $main::lxdebug->leave_sub(); } +sub get_bank_info { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(vc id)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id}); + my $placeholders = ('?') x scalar @ids; + my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic + FROM ${table} + WHERE id IN (${placeholders})|; + + my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids); + + if (ref $params{id} eq 'ARRAY') { + $result = { map { $_->{id} => $_ } @{ $result } }; + } else { + $result = $result->[0] || { 'id' => $params{id} }; + } + + $main::lxdebug->leave_sub(); + + return $result; +} + 1; diff --git a/locale/de/all b/locale/de/all index cd1215068..2be562184 100644 --- a/locale/de/all +++ b/locale/de/all @@ -212,6 +212,7 @@ $self->{texts} = { 'Automatically created invoice for fee and interest for dunning %s' => 'Automatisch erzeugte Rechnung für Gebühren und Zinsen zu Mahnung %s', 'Available qty' => 'Lagerbestand', 'BALANCE SHEET' => 'BILANZ', + 'BIC' => 'BIC', 'BOM' => 'Stückliste', 'BWA' => 'BWA', 'Back' => 'Zurück', @@ -724,6 +725,7 @@ $self->{texts} = { 'Host' => 'Datenbankcomputer', 'However, you can create a new part which will then be selected.' => 'Sie können jedoch einen neuen Artikel anlegen, der dann automatisch ausgewählt wird.', 'I' => 'I', + 'IBAN' => 'IBAN', 'ID' => 'Buchungsnummer', 'ID-Nummer' => 'ID-Nummer (intern)', 'II' => 'II', diff --git a/sql/Pg-upgrade2/bank_accounts.sql b/sql/Pg-upgrade2/bank_accounts.sql new file mode 100644 index 000000000..0343aed11 --- /dev/null +++ b/sql/Pg-upgrade2/bank_accounts.sql @@ -0,0 +1,23 @@ +-- @tag: bank_accounts +-- @description: Tabelle für Bankkonten (u.a. für SEPA-Export) +-- @depends: release_2_4_3 +CREATE TABLE bank_accounts ( + id integer NOT NULL DEFAULT nextval('id'), + account_number varchar(100), + bank_code varchar(100), + iban varchar(100), + bic varchar(100), + bank text, + chart_id integer NOT NULL, + + PRIMARY KEY (id), + FOREIGN KEY (chart_id) REFERENCES chart (id) +); + +ALTER TABLE customer ADD COLUMN iban varchar(100); +ALTER TABLE customer ADD COLUMN bic varchar(100); +UPDATE customer SET iban = '', bic = ''; + +ALTER TABLE vendor ADD COLUMN iban varchar(100); +ALTER TABLE vendor ADD COLUMN bic varchar(100); +UPDATE vendor SET iban = '', bic = ''; diff --git a/templates/webpages/ct/form_header_de.html b/templates/webpages/ct/form_header_de.html index 2555f44a0..975a1bf21 100644 --- a/templates/webpages/ct/form_header_de.html +++ b/templates/webpages/ct/form_header_de.html @@ -2,7 +2,7 @@
[% title %]
-
+