Revision 876169ef
Von Waldemar Toews vor etwa 8 Jahren hinzugefügt
| SL/CT.pm | ||
|---|---|---|
|
my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
|
||
|
my $placeholders = join ", ", ('?') x scalar @ids;
|
||
|
my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
|
||
|
my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
|
||
|
my $query = qq|SELECT id, name, depositor, account_number, bank, bank_code, iban, bic ${c_mandate}
|
||
|
FROM ${table}
|
||
|
WHERE id IN (${placeholders})|;
|
||
|
|
||
| SL/Controller/CsvImport/CustomerVendor.pm | ||
|---|---|---|
|
use SL::DB::CustomVariable;
|
||
|
use SL::DB::CustomVariableConfig;
|
||
|
use SL::DB::PaymentTerm;
|
||
|
use SL::SEPA;
|
||
|
use SL::TransNumber;
|
||
|
|
||
|
use parent qw(SL::Controller::CsvImport::Base);
|
||
| ... | ... | |
|
my $object = $entry->{object};
|
||
|
|
||
|
$self->check_name($entry);
|
||
|
$self->check_depositor($entry);
|
||
|
$self->check_language($entry);
|
||
|
$self->check_business($entry);
|
||
|
$self->check_payment($entry);
|
||
| ... | ... | |
|
next if @{ $entry->{errors} };
|
||
|
|
||
|
my @cleaned_fields = $self->clean_fields(qr{[\r\n]}, $object, qw(name department_1 department_2 street zipcode city country gln contact phone fax homepage email cc bcc
|
||
|
taxnumber account_number bank_code bank username greeting taxzone));
|
||
|
taxnumber account_number bank_code bank username greeting taxzone depositor));
|
||
|
|
||
|
push @{ $entry->{information} }, $::locale->text('Illegal characters have been removed from the following fields: #1', join(', ', @cleaned_fields))
|
||
|
if @cleaned_fields;
|
||
| ... | ... | |
|
return 0;
|
||
|
}
|
||
|
|
||
|
sub check_depositor {
|
||
|
my ($self, $entry) = @_;
|
||
|
|
||
|
my $depositor = $entry->{object}->depositor;
|
||
|
|
||
|
push @{ $entry->{errors} }, $::locale->text('Error: Depositor not SEPA conform') if !SL::SEPA->is_depositor_name_valid($depositor);
|
||
|
}
|
||
|
|
||
|
sub check_language {
|
||
|
my ($self, $entry) = @_;
|
||
|
|
||
| ... | ... | |
|
{ name => 'customernumber', description => $::locale->text('Customer Number') },
|
||
|
{ name => 'department_1', description => $::locale->text('Department 1') },
|
||
|
{ name => 'department_2', description => $::locale->text('Department 2') },
|
||
|
{ name => 'depositor', description => $::locale->text('Depositor') },
|
||
|
{ name => 'delivery_term_id', description => $::locale->text('Delivery terms (database ID)') },
|
||
|
{ name => 'delivery_term', description => $::locale->text('Delivery terms (name)') },
|
||
|
{ name => 'direct_debit', description => $::locale->text('direct debit') },
|
||
| SL/SEPA.pm | ||
|---|---|---|
|
my $q_insert =
|
||
|
qq|INSERT INTO sepa_export_items (id, sepa_export_id, ${arap}_id, chart_id,
|
||
|
amount, requested_execution_date, reference, end_to_end_id,
|
||
|
our_iban, our_bic, vc_iban, vc_bic,
|
||
|
our_iban, our_bic, our_depositor, vc_iban, vc_bic, vc_depositor,
|
||
|
skonto_amount, payment_type ${c_mandate})
|
||
|
VALUES (?, ?, ?, ?,
|
||
|
?, ?, ?, ?,
|
||
|
?, ?, ?, ?, ?, ?,
|
||
|
?, ?, ?, ?,
|
||
|
?, ? ${p_mandate})|;
|
||
|
my $h_insert = prepare_query($form, $dbh, $q_insert);
|
||
| ... | ... | |
|
conv_i($transfer->{"${arap}_id"}), conv_i($transfer->{chart_id}),
|
||
|
$transfer->{amount}, conv_date($transfer->{requested_execution_date}),
|
||
|
$transfer->{reference}, $end_to_end_id,
|
||
|
map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vc));
|
||
|
map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic depositor) } qw(our vc));
|
||
|
# save value of skonto_amount and payment_type
|
||
|
if ( $transfer->{payment_type} eq 'without_skonto' ) {
|
||
|
push(@values, 0);
|
||
| ... | ... | |
|
return 1;
|
||
|
}
|
||
|
|
||
|
sub is_depositor_name_valid {
|
||
|
my ($class, $depositor) = @_;
|
||
|
return $depositor =~ /[^A-Za-z0-9\/\?\:\(\)\.\,\'\+\- ]/ ? 0 : 1;
|
||
|
}
|
||
|
|
||
|
1;
|
||
| bin/mozilla/sepa.pl | ||
|---|---|---|
|
my ($vc_bank_info);
|
||
|
my $error_message;
|
||
|
|
||
|
my @bank_columns = qw(iban bic);
|
||
|
my @bank_columns = qw(iban bic depositor);
|
||
|
push @bank_columns, qw(mandator_id mandate_date_of_signature) if $vc eq 'customer';
|
||
|
|
||
|
if ($form->{confirmation}) {
|
||
| ... | ... | |
|
$error_message = $locale->text('The bank information must not be empty.');
|
||
|
last;
|
||
|
}
|
||
|
if (!SL::SEPA->is_depositor_name_valid($info->{depositor})) { # Checking for Depositor formatting if SEPA conform
|
||
|
$error_message = $locale->text('Incorrect depositor!');
|
||
|
last;
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
'src_bic' => $item->{our_bic},
|
||
|
'dst_iban' => $item->{vc_iban},
|
||
|
'dst_bic' => $item->{vc_bic},
|
||
|
'company' => $item->{vc_name},
|
||
|
'company' => $item->{vc_depositor},
|
||
|
'company_number' => $item->{vc_number},
|
||
|
'amount' => $item->{amount},
|
||
|
'reference' => $item->{reference},
|
||
| locale/de/all | ||
|---|---|---|
|
'Dependencies' => 'Abhängigkeiten',
|
||
|
'Dependency loop detected:' => 'Schleife in den Abhängigkeiten entdeckt:',
|
||
|
'Deposit' => 'Gutschrift',
|
||
|
'Depositor' => 'Kontoinhaber',
|
||
|
'Description' => 'Beschreibung',
|
||
|
'Description (Click on Description for details)' => 'Beschreibung (Klick öffnet einzelne Kontendetails)',
|
||
|
'Description (translation for #1)' => 'Beschreibung (Übersetzung für #1)',
|
||
| ... | ... | |
|
'Error: Customer/vendor missing' => 'Fehler: Kunde/Lieferant fehlt',
|
||
|
'Error: Customer/vendor not found' => 'Fehler: Kunde/Lieferant nicht gefunden',
|
||
|
'Error: Found local bank account number but local bank code doesn\'t match' => 'Fehler: Kontonummer wurde gefunden aber gespeicherte Bankleitzahl stimmt nicht überein',
|
||
|
'Error: Depositor not SEPA conform' => 'Fehler: Kontoinhaber ist nicht SEPA konform',
|
||
|
'Error: Gender (cp_gender) missing or invalid' => 'Fehler: Geschlecht (cp_gender) fehlt oder ungültig',
|
||
|
'Error: Invalid bin' => 'Fehler: Ungültiger Lagerplatz',
|
||
|
'Error: Invalid bin id' => 'Ungültige Lagerplatz-ID',
|
||
| ... | ... | |
|
'Incoming Payments' => 'Zahlungseingänge',
|
||
|
'Incoming invoice number' => 'Eingangsrechnungsnummer',
|
||
|
'Inconsistency in database' => 'Unstimmigkeiten in der Datenbank',
|
||
|
'Incorrect depositor!' => 'Eingabe für Kontoinhaber ist nicht SEPA konform, Änderungen wurden nicht gespeichert!',
|
||
|
'Incorrect password!' => 'Ungültiges Passwort!',
|
||
|
'Incorrect username or password or no access to selected client!' => 'Ungültiger Benutzername oder Passwort oder kein Zugriff auf den ausgewählten Mandanten!',
|
||
|
'Increase' => 'Erhöhen',
|
||
| sql/Pg-upgrade2/format_depositor_sepa_conform.sql | ||
|---|---|---|
|
-- @tag: format_depositor_sepa_conform
|
||
|
-- @description: Formatiert die Datenbank-Daten für Kontoinhaber SEPA konform.
|
||
|
-- @depends: release_3_2_0
|
||
|
|
||
|
UPDATE customer SET depositor =
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(translate(depositor, 'àâáèéêóôùú&`', 'aaeeeoouu+'''),
|
||
|
'ä', 'ae'),
|
||
|
'ö', 'oe'),
|
||
|
'ü', 'ue'),
|
||
|
'Ä', 'Ae'),
|
||
|
'Ö', 'Oe'),
|
||
|
'Ü', 'Ue'),
|
||
|
'ß', 'ss')
|
||
|
WHERE (depositor IS NOT NULL)
|
||
|
AND (depositor <> '');
|
||
|
|
||
|
UPDATE vendor SET depositor =
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(
|
||
|
replace(translate(depositor, 'àâáèéêóôùú&`', 'aaeeeoouu+'''),
|
||
|
'ä', 'ae'),
|
||
|
'ö', 'oe'),
|
||
|
'ü', 'ue'),
|
||
|
'Ä', 'Ae'),
|
||
|
'Ö', 'Oe'),
|
||
|
'Ü', 'Ue'),
|
||
|
'ß', 'ss')
|
||
|
WHERE (depositor IS NOT NULL)
|
||
|
AND (depositor <> '');
|
||
| templates/webpages/sepa/bank_transfer_create.html | ||
|---|---|---|
|
<table>
|
||
|
<tr>
|
||
|
<th class="listheading">[%- IF is_vendor %][% 'Vendor' | $T8 %][%- ELSE %][%- LxERP.t8('Customer') %][%- END %]</th>
|
||
|
<th class="listheading">[% 'Depositor' | $T8 %]</th>
|
||
|
<th class="listheading">[% 'IBAN' | $T8 %]</th>
|
||
|
<th class="listheading">[% 'BIC' | $T8 %]</th>
|
||
|
<th class="listheading">[% 'Bank' | $T8 %]</th>
|
||
| ... | ... | |
|
<input type="hidden" name="vc_bank_info[].name" value="[% HTML.escape(vbi.name) %]">
|
||
|
[% HTML.escape(vbi.name) %]
|
||
|
</td>
|
||
|
<td><input name="vc_bank_info[].depositor" size="20" value="[% HTML.escape(vbi.depositor) %]"></td>
|
||
|
<td><input name="vc_bank_info[].iban" size="34" value="[% HTML.escape(vbi.iban.substr(0, 34)) %]" maxlength="34"></td>
|
||
|
<td><input name="vc_bank_info[].bic" size="20" value="[% HTML.escape(vbi.bic.substr(0, 20)) %]" maxlength="20"></td>
|
||
|
<td><input name="vc_bank_info[].bank" size="30" value="[% HTML.escape(vbi.bank) %]"></td>
|
||
| ... | ... | |
|
</td>
|
||
|
<td align="left" [%- IF bank_transfer.within_skonto_period %]style="background-color: LightGreen"[%- END %]>[%- IF bank_transfer.skonto_amount %] [% LxERP.format_amount(bank_transfer.percent_skonto, 2) %] % = [% LxERP.format_amount(bank_transfer.skonto_amount, 2) %] € [% 'until' | $T8 %] [% bank_transfer.skonto_date %] [% END %]</td>
|
||
|
<td nowrap>
|
||
|
[%- IF !is_vendor %]
|
||
|
[%- IF !is_vendor %]
|
||
|
[% L.date_tag('bank_transfers[].requested_execution_date', bank_transfer.requested_execution_date, readonly => 1) %]
|
||
|
[%- ELSE %]
|
||
|
[%- ELSE %]
|
||
|
[% L.date_tag('bank_transfers[].requested_execution_date', bank_transfer.requested_execution_date) %]
|
||
|
[%- END %]
|
||
|
[%- END %]
|
||
|
</td>
|
||
|
</tr>
|
||
|
[%- END %]
|
||
| templates/webpages/sepa/bank_transfer_edit.html | ||
|---|---|---|
|
[%- END %]
|
||
|
<th class="listheading">[% 'Invoice' | $T8 %]</th>
|
||
|
<th class="listheading">[%- IF is_vendor %][% 'Vendor' | $T8 %][%- ELSE %][%- LxERP.t8('Customer') %][%- END %]</th>
|
||
|
<th class="listheading">[% 'Depositor' | $T8 %]</th>
|
||
|
[%- IF is_vendor %]
|
||
|
<th class="listheading" colspan="2">[% 'Source bank account' | $T8 %]</th>
|
||
|
<th class="listheading" colspan="2">[% 'Target bank account' | $T8 %]</th>
|
||
| ... | ... | |
|
<th class="listheading" align="right">[% 'Execution date' | $T8 %]</th>
|
||
|
</tr>
|
||
|
<tr>
|
||
|
<th class="listheading" colspan="[% IF show_post_payments_button %]3[% ELSE %]2[% END %]"> </th>
|
||
|
<th class="listheading" colspan="[% IF show_post_payments_button %]4[% ELSE %]3[% END %]"> </th>
|
||
|
<th class="listheading">[% 'IBAN' | $T8 %]</th>
|
||
|
<th class="listheading">[% 'BIC' | $T8 %]</th>
|
||
|
<th class="listheading">[% 'IBAN' | $T8 %]</th>
|
||
| ... | ... | |
|
<a href="[% IF item.invoice %][% iris %][% ELSE %][% arap %][% END %].pl?action=edit&type=invoice&id=[% IF is_vendor %][% HTML.url(item.ap_id) %][% ELSE %][% HTML.url(item.ar_id) %][% END %]">[% HTML.escape(item.invnumber) %]</a>
|
||
|
</td>
|
||
|
<td>[% HTML.escape(item.vc_name) %]</td>
|
||
|
<td>[% HTML.escape(item.vc_depositor) %]</td>
|
||
|
<td>[% HTML.escape(item.our_iban) %]</td>
|
||
|
<td>[% HTML.escape(item.our_bic) %]</td>
|
||
|
<td>[% HTML.escape(item.vc_iban) %]</td>
|
||
Auch abrufbar als: Unified diff
SEPA + Stammdaten: Implementierung Kontoinhaber mit Formatierungsprüfung
- Das Eingabefeld 'Kontoinhaber' soll als Firmenname in SEPA-XML-Export übernommen werden.
- Bei der Eingabe der Kontoinhaben wird geprüft, ob Eingabe SEPA konform ist.
- Bei CSV-Import wird auch darauf geprüft.