Revision 6b1f644c
Von Moritz Bunkus vor fast 14 Jahren hinzugefügt
| SL/SEPA.pm | ||
|---|---|---|
| 16 | 16 |
my $form = $main::form; |
| 17 | 17 |
|
| 18 | 18 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
| 19 |
my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap';
|
|
| 20 |
my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor';
|
|
| 19 | 21 |
|
| 20 | 22 |
my $query = |
| 21 | 23 |
qq| |
| 22 |
SELECT ap.id, ap.invnumber, ap.vendor_id, ap.amount AS invoice_amount, ap.invoice,
|
|
| 23 |
v.name AS vendorname, ap.duedate as duedate,
|
|
| 24 |
SELECT ${arap}.id, ${arap}.invnumber, ${arap}.${vc}_id, ${arap}.amount AS invoice_amount, ${arap}.invoice,
|
|
| 25 |
vc.name AS vcname, ${arap}.duedate as duedate,
|
|
| 24 | 26 |
|
| 25 |
COALESCE(v.iban, '') <> '' AND COALESCE(v.bic, '') <> '' AS vendor_bank_info_ok,
|
|
| 27 |
COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' AS vc_bank_info_ok,
|
|
| 26 | 28 |
|
| 27 |
ap.amount - ap.paid - COALESCE(open_transfers.amount, 0) AS open_amount
|
|
| 29 |
${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount
|
|
| 28 | 30 |
|
| 29 |
FROM ap
|
|
| 30 |
LEFT JOIN vendor v ON (ap.vendor_id = v.id)
|
|
| 31 |
FROM ${arap}
|
|
| 32 |
LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id)
|
|
| 31 | 33 |
LEFT JOIN (SELECT sei.ap_id, SUM(sei.amount) AS amount |
| 32 | 34 |
FROM sepa_export_items sei |
| 33 | 35 |
LEFT JOIN sepa_export se ON (sei.sepa_export_id = se.id) |
| 34 | 36 |
WHERE NOT se.closed |
| 37 |
AND (se.vc = '${vc}')
|
|
| 35 | 38 |
GROUP BY sei.ap_id) |
| 36 |
AS open_transfers ON (ap.id = open_transfers.ap_id)
|
|
| 39 |
AS open_transfers ON (${arap}.id = open_transfers.ap_id)
|
|
| 37 | 40 |
|
| 38 |
WHERE ap.amount > (COALESCE(open_transfers.amount, 0) + ap.paid)
|
|
| 41 |
WHERE ${arap}.amount > (COALESCE(open_transfers.amount, 0) + ${arap}.paid)
|
|
| 39 | 42 |
|
| 40 |
ORDER BY lower(v.name) ASC, lower(ap.invnumber) ASC
|
|
| 43 |
ORDER BY lower(vc.name) ASC, lower(${arap}.invnumber) ASC
|
|
| 41 | 44 |
|; |
| 42 | 45 |
|
| 43 | 46 |
my $results = selectall_hashref_query($form, $dbh, $query); |
Auch abrufbar als: Unified diff
Unterscheidung Bankeinzug/Überweisung in erster Maske (Auswahl Kunden/Lieferanten)