Revision 0e36c22a
Von Moritz Bunkus vor fast 11 Jahren hinzugefügt
| SL/SEPA.pm | ||
|---|---|---|
| 19 | 19 |
my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap';
|
| 20 | 20 |
my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor';
|
| 21 | 21 |
|
| 22 |
my $mandate = $params{vc} eq 'customer' ? " AND COALESCE(vc.mandator_id, '') <> '' AND vc.mandate_date_of_signature IS NOT NULL " : '';
|
|
| 23 |
|
|
| 22 | 24 |
my $query = |
| 23 | 25 |
qq| |
| 24 | 26 |
SELECT ${arap}.id, ${arap}.invnumber, ${arap}.${vc}_id as vc_id, ${arap}.amount AS invoice_amount, ${arap}.invoice,
|
| 25 | 27 |
vc.name AS vcname, vc.language_id, ${arap}.duedate as duedate, ${arap}.direct_debit,
|
| 26 | 28 |
|
| 27 |
COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' AS vc_bank_info_ok, |
|
| 29 |
COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' ${mandate} AS vc_bank_info_ok,
|
|
| 28 | 30 |
|
| 29 | 31 |
${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount
|
| 30 | 32 |
|
| ... | ... | |
| 76 | 78 |
|
| 77 | 79 |
my $q_item_id = qq|SELECT nextval('id')|;
|
| 78 | 80 |
my $h_item_id = prepare_query($form, $dbh, $q_item_id); |
| 81 |
my $c_mandate = $params{vc} eq 'customer' ? ', vc_mandator_id, vc_mandate_date_of_signature' : '';
|
|
| 82 |
my $p_mandate = $params{vc} eq 'customer' ? ', ?, ?' : '';
|
|
| 79 | 83 |
|
| 80 | 84 |
my $q_insert = |
| 81 | 85 |
qq|INSERT INTO sepa_export_items (id, sepa_export_id, ${arap}_id, chart_id,
|
| 82 | 86 |
amount, requested_execution_date, reference, end_to_end_id, |
| 83 |
our_iban, our_bic, vc_iban, vc_bic) |
|
| 87 |
our_iban, our_bic, vc_iban, vc_bic ${c_mandate})
|
|
| 84 | 88 |
VALUES (?, ?, ?, ?, |
| 85 | 89 |
?, ?, ?, ?, |
| 86 |
?, ?, ?, ?)|; |
|
| 90 |
?, ?, ?, ? ${p_mandate})|;
|
|
| 87 | 91 |
my $h_insert = prepare_query($form, $dbh, $q_insert); |
| 88 | 92 |
|
| 89 | 93 |
my $q_reference = |
| ... | ... | |
| 130 | 134 |
$transfer->{reference}, $end_to_end_id,
|
| 131 | 135 |
map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vc));
|
| 132 | 136 |
|
| 137 |
push @values, $transfer->{vc_mandator_id}, conv_date($transfer->{vc_mandate_date_of_signature}) if $params{vc} eq 'customer';
|
|
| 138 |
|
|
| 133 | 139 |
do_statement($form, $h_insert, $q_insert, @values); |
| 134 | 140 |
} |
| 135 | 141 |
|
| ... | ... | |
| 177 | 183 |
if ($export->{id}) {
|
| 178 | 184 |
my ($columns, $joins); |
| 179 | 185 |
|
| 180 |
my $mandator_id = $params{vc} eq 'customer' ? ', mandator_id' : '';
|
|
| 186 |
my $mandator_id = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
|
|
| 181 | 187 |
|
| 182 | 188 |
if ($params{details}) {
|
| 183 |
$columns = qq|, arap.invnumber, arap.invoice, arap.transdate AS reference_date, vc.name AS vc_name, vc.${vc}number AS vc_number, c.accno AS chart_accno, c.description AS chart_description, ${mandator_id}|;
|
|
| 189 |
$columns = qq|, arap.invnumber, arap.invoice, arap.transdate AS reference_date, vc.name AS vc_name, vc.${vc}number AS vc_number, c.accno AS chart_accno, c.description AS chart_description ${mandator_id}|;
|
|
| 184 | 190 |
$joins = qq|LEFT JOIN ${arap} arap ON (sei.${arap}_id = arap.id)
|
| 185 | 191 |
LEFT JOIN ${vc} vc ON (arap.${vc}_id = vc.id)
|
| 186 | 192 |
LEFT JOIN chart c ON (sei.chart_id = c.id)|; |
Auch abrufbar als: Unified diff
SEPA: Feld »Signaturdatum«; Bearbeiten der Felder in Stammdatenmasken; Speichern in sepa_export_items