projekt kivitendo

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;

    qq|iban = ?, | .

    qq|bic = ?, | .

    $form->{iban},

    $form->{bic},

    qq|  iban = ?, | .

    qq|  bic = ?, | .

    $form->{iban},

    $form->{bic},

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;

}

  'BIC'                         => 'BIC',

  'IBAN'                        => 'IBAN',

-- @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 = '';

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

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

      <td><input name="bank_code" size="10" maxlength="100" value="[% HTML.escape(bank_code) %]"></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>

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

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

      <td><input name="bank_code" size="10" maxlength="100" value="[% HTML.escape(bank_code) %]"></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>