Revision 6ff01fdb
Von Moritz Bunkus vor mehr als 15 Jahren hinzugefügt
| SL/AP.pm | ||
|---|---|---|
| 509 | 509 |
|
| 510 | 510 |
my ($self, $form, $dbh) = @_; |
| 511 | 511 |
|
| 512 |
my @delete_oids;
|
|
| 512 |
my @delete_acc_trans_ids;
|
|
| 513 | 513 |
|
| 514 | 514 |
# Delete old payment entries from acc_trans. |
| 515 | 515 |
my $query = |
| 516 |
qq|SELECT oid
|
|
| 516 |
qq|SELECT acc_trans_id
|
|
| 517 | 517 |
FROM acc_trans |
| 518 | 518 |
WHERE (trans_id = ?) AND fx_transaction |
| 519 | 519 |
|
| 520 | 520 |
UNION |
| 521 | 521 |
|
| 522 |
SELECT at.oid
|
|
| 522 |
SELECT at.acc_trans_id
|
|
| 523 | 523 |
FROM acc_trans at |
| 524 | 524 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
| 525 | 525 |
WHERE (trans_id = ?) AND (c.link LIKE '%AP_paid%')|; |
| 526 |
push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
|
|
| 526 |
push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
|
|
| 527 | 527 |
|
| 528 | 528 |
$query = |
| 529 |
qq|SELECT at.oid
|
|
| 529 |
qq|SELECT at.acc_trans_id
|
|
| 530 | 530 |
FROM acc_trans at |
| 531 | 531 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
| 532 | 532 |
WHERE (trans_id = ?) |
| 533 | 533 |
AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) |
| 534 |
ORDER BY at.oid
|
|
| 534 |
ORDER BY at.acc_trans_id
|
|
| 535 | 535 |
OFFSET 1|; |
| 536 |
push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 536 |
push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 537 | 537 |
|
| 538 |
if (@delete_oids) {
|
|
| 539 |
$query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
|
|
| 538 |
if (@delete_acc_trans_ids) {
|
|
| 539 |
$query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
|
|
| 540 | 540 |
do_query($form, $dbh, $query); |
| 541 | 541 |
} |
| 542 | 542 |
|
| ... | ... | |
| 589 | 589 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
| 590 | 590 |
WHERE (trans_id = ?) |
| 591 | 591 |
AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) |
| 592 |
ORDER BY at.oid
|
|
| 592 |
ORDER BY at.acc_trans_id
|
|
| 593 | 593 |
LIMIT 1|; |
| 594 | 594 |
|
| 595 | 595 |
($form->{APselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
|
| ... | ... | |
| 760 | 760 |
do_query($form, $dbh, $query, $id); |
| 761 | 761 |
|
| 762 | 762 |
# now copy acc_trans entries |
| 763 |
$query = qq|SELECT a.*, c.link FROM acc_trans a LEFT JOIN chart c ON a.chart_id = c.id WHERE a.trans_id = ? ORDER BY a.oid|;
|
|
| 763 |
$query = qq|SELECT a.*, c.link FROM acc_trans a LEFT JOIN chart c ON a.chart_id = c.id WHERE a.trans_id = ? ORDER BY a.acc_trans_id|;
|
|
| 764 | 764 |
my $rowref = selectall_hashref_query($form, $dbh, $query, $id); |
| 765 | 765 |
|
| 766 | 766 |
# kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/ |
Auch abrufbar als: Unified diff
Einführung einer ID-Spalte in acc_trans
Die Benutzung der von PostgreSQL zur Verfügung gestellten
Spalte "oid" hat ihre Tücken. Über diese wird in Lx-Office die
Reihenfolge der Einträge in acc_trans geregelt. Wird aber ein
UPDATE-SQL-Query auf acc_trans ausgeführt, so kann es (anscheinend
je nach Datenbankversion) dazu kommen, dass die Zeile eine neue
oid erhält, wodurch die Reihenfolge nicht mehr stimmt.