Erweiterung der Kundenmaske um IBAN und BIC - Patch aus xplace Projekt

diff --git a/SL/BankAccount.pm b/SL/BankAccount.pm
new file mode 100644 (file)
index 0000000..4194d5c
--- /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 3df827a6d0f365c3e77dfe875a0dc20717ef4c4d..f2745fb02644e0788ec20ba6d9ec72cae7bc0474 100644 (file)
--- a/SL/CT.pm
+++ b/SL/CT.pm
@@ -306,6 +306,8 @@ sub save_customer {
     qq|account_number = ?, | .
     qq|bank_code = ?, | .
     qq|bank = ?, | .
     qq|account_number = ?, | .
     qq|bank_code = ?, | .
     qq|bank = ?, | .

+    qq|iban = ?, | .
+    qq|bic = ?, | .

     qq|obsolete = ?, | .
     qq|direct_debit = ?, | .
     qq|ustid = ?, | .
     qq|obsolete = ?, | .
     qq|direct_debit = ?, | .
     qq|ustid = ?, | .


@@ -345,6 +347,8 @@ sub save_customer {
     $form->{account_number},
     $form->{bank_code},
     $form->{bank},
     $form->{account_number},
     $form->{bank_code},
     $form->{bank},

+    $form->{iban},
+    $form->{bic},

     $form->{obsolete} ? 't' : 'f',
     $form->{direct_debit} ? 't' : 'f',
     $form->{ustid},
     $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|  account_number = ?, | .
     qq|  bank_code = ?, | .
     qq|  bank = ?, | .

+    qq|  iban = ?, | .
+    qq|  bic = ?, | .

     qq|  obsolete = ?, | .
     qq|  direct_debit = ?, | .
     qq|  ustid = ?, | .
     qq|  obsolete = ?, | .
     qq|  direct_debit = ?, | .
     qq|  ustid = ?, | .


@@ -548,6 +554,8 @@ sub save_vendor {
     $form->{account_number},
     $form->{bank_code},
     $form->{bank},
     $form->{account_number},
     $form->{bank_code},
     $form->{bank},

+    $form->{iban},
+    $form->{bic},

     $form->{obsolete} ? 't' : 'f',
     $form->{direct_debit} ? 't' : 'f',
     $form->{ustid},
     $form->{obsolete} ? 't' : 'f',
     $form->{direct_debit} ? 't' : 'f',
     $form->{ustid},


@@ -1057,4 +1065,37 @@ sub delete_shipto {
   $main::lxdebug->leave_sub();
 }
 
   $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;
 1;

diff --git a/locale/de/all b/locale/de/all
index cd1215068cc10a6f027cae71c53a1032faba88a2..2be5621844da7a08351ee1bc68839f620ddaabe7 100644 (file)
--- 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',
   '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',
   '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',
   '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',
   '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 (file)
index 0000000..0343aed
--- /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 2555f44a0b0ee934e50f1346536e7fc190a5d964..975a1bf21cb0a00c25d50d2554b2b5c9efb45761 100644 (file)
--- a/templates/webpages/ct/form_header_de.html
+++ b/templates/webpages/ct/form_header_de.html
@@ -2,7 +2,7 @@
 
  <div class="listtop">[% title %]</div>
 
 
  <div class="listtop">[% title %]</div>
 

- <form method="post" name="ct" action="ct.pl">
+ <form method="post" name="ct" action="ct.pl" onKeyUp="highlight(event)" onClick="highlight(event)">

 
   <ul id="maintab" class="shadetabs">
    <li class="selected"><a href="#" rel="billing">Rechnungsadresse</a></li>
 
   <ul id="maintab" class="shadetabs">
    <li class="selected"><a href="#" rel="billing">Rechnungsadresse</a></li>


@@ -139,11 +139,18 @@
 
      <tr>
       <th align="right">Kontonummer</th>
 
      <tr>
       <th align="right">Kontonummer</th>

-      <td><input name="account_number" size="10" maxlength="15" value="[% HTML.escape(account_number) %]"></td>
+      <td><input name="account_number" size="10" maxlength="100" value="[% HTML.escape(account_number) %]"></td>

       <th align="right">Bankleitzahl</th>
       <th align="right">Bankleitzahl</th>

-      <td><input name="bank_code" size="10" maxlength="10" value="[% HTML.escape(bank_code) %]"></td>
+      <td><input name="bank_code" size="10" maxlength="100" value="[% HTML.escape(bank_code) %]"></td>

       <th align="right">Bank</th>
       <th align="right">Bank</th>

-      <td><input name="bank" size="30" value="[% HTML.escape(bank) %]"></td>
+      <td><input name="bank" size="20" value="[% HTML.escape(bank) %]"></td>
+     </tr>
+
+     <tr>
+      <th align="right">IBAN</th>
+      <td><input name="iban" size="10" maxlength="100" value="[% HTML.escape(iban) %]"></td>
+      <th align="right">BIC</th>
+      <td><input name="bic" size="10" maxlength="100" value="[% HTML.escape(bic) %]"></td>

      </tr>
 
      <tr>
      </tr>
 
      <tr>

diff --git a/templates/webpages/ct/form_header_master.html b/templates/webpages/ct/form_header_master.html
index 3fc9acd61ad82ffa7d317f9eb1758d97789dfa09..e4d98a08893ade97e545a58d8b41eef69b3a5880 100644 (file)
--- a/templates/webpages/ct/form_header_master.html
+++ b/templates/webpages/ct/form_header_master.html
@@ -2,7 +2,7 @@
 
  <div class="listtop">[% title %]</div>
 
 
  <div class="listtop">[% title %]</div>
 

- <form method="post" name="ct" action="ct.pl">
+ <form method="post" name="ct" action="ct.pl" onKeyUp="highlight(event)" onClick="highlight(event)">

 
   <ul id="maintab" class="shadetabs">
    <li class="selected"><a href="#" rel="billing"><translate>Billing Address</translate></a></li>
 
   <ul id="maintab" class="shadetabs">
    <li class="selected"><a href="#" rel="billing"><translate>Billing Address</translate></a></li>


@@ -139,11 +139,18 @@
 
      <tr>
       <th align="right"><translate>Account Number</translate></th>
 
      <tr>
       <th align="right"><translate>Account Number</translate></th>

-      <td><input name="account_number" size="10" maxlength="15" value="[% HTML.escape(account_number) %]"></td>
+      <td><input name="account_number" size="10" maxlength="100" value="[% HTML.escape(account_number) %]"></td>

       <th align="right"><translate>Bank Code Number</translate></th>
       <th align="right"><translate>Bank Code Number</translate></th>

-      <td><input name="bank_code" size="10" maxlength="10" value="[% HTML.escape(bank_code) %]"></td>
+      <td><input name="bank_code" size="10" maxlength="100" value="[% HTML.escape(bank_code) %]"></td>

       <th align="right"><translate>Bank</translate></th>
       <th align="right"><translate>Bank</translate></th>

-      <td><input name="bank" size="30" value="[% HTML.escape(bank) %]"></td>
+      <td><input name="bank" size="20" value="[% HTML.escape(bank) %]"></td>
+     </tr>
+
+     <tr>
+      <th align="right"><translate>IBAN</translate></th>
+      <td><input name="iban" size="10" maxlength="100" value="[% HTML.escape(iban) %]"></td>
+      <th align="right"><translate>BIC</translate></th>
+      <td><input name="bic" size="10" maxlength="100" value="[% HTML.escape(bic) %]"></td>

      </tr>
 
      <tr>
      </tr>
 
      <tr>