Revision 6ff01fdb
Von Moritz Bunkus vor mehr als 15 Jahren hinzugefügt
| SL/AR.pm | ||
|---|---|---|
| 279 | 279 |
|
| 280 | 280 |
my ($self, $form, $dbh) = @_; |
| 281 | 281 |
|
| 282 |
my @delete_oids;
|
|
| 282 |
my @delete_acc_trans_ids;
|
|
| 283 | 283 |
|
| 284 | 284 |
# Delete old payment entries from acc_trans. |
| 285 | 285 |
my $query = |
| 286 |
qq|SELECT oid
|
|
| 286 |
qq|SELECT acc_trans_id
|
|
| 287 | 287 |
FROM acc_trans |
| 288 | 288 |
WHERE (trans_id = ?) AND fx_transaction |
| 289 | 289 |
|
| 290 | 290 |
UNION |
| 291 | 291 |
|
| 292 |
SELECT at.oid
|
|
| 292 |
SELECT at.acc_trans_id
|
|
| 293 | 293 |
FROM acc_trans at |
| 294 | 294 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
| 295 | 295 |
WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|; |
| 296 |
push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
|
|
| 296 |
push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
|
|
| 297 | 297 |
|
| 298 | 298 |
$query = |
| 299 |
qq|SELECT at.oid
|
|
| 299 |
qq|SELECT at.acc_trans_id
|
|
| 300 | 300 |
FROM acc_trans at |
| 301 | 301 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
| 302 | 302 |
WHERE (trans_id = ?) |
| 303 | 303 |
AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) |
| 304 |
ORDER BY at.oid
|
|
| 304 |
ORDER BY at.acc_trans_id
|
|
| 305 | 305 |
OFFSET 1|; |
| 306 |
push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 306 |
push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 307 | 307 |
|
| 308 |
if (@delete_oids) {
|
|
| 309 |
$query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
|
|
| 308 |
if (@delete_acc_trans_ids) {
|
|
| 309 |
$query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
|
|
| 310 | 310 |
do_query($form, $dbh, $query); |
| 311 | 311 |
} |
| 312 | 312 |
|
| ... | ... | |
| 359 | 359 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
| 360 | 360 |
WHERE (trans_id = ?) |
| 361 | 361 |
AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) |
| 362 |
ORDER BY at.oid
|
|
| 362 |
ORDER BY at.acc_trans_id
|
|
| 363 | 363 |
LIMIT 1|; |
| 364 | 364 |
|
| 365 | 365 |
($form->{ARselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
|
| ... | ... | |
| 650 | 650 |
do_query($form, $dbh, $query, $id); |
| 651 | 651 |
|
| 652 | 652 |
# now copy acc_trans entries |
| 653 |
$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|;
|
|
| 653 |
$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|;
|
|
| 654 | 654 |
my $rowref = selectall_hashref_query($form, $dbh, $query, $id); |
| 655 | 655 |
|
| 656 | 656 |
# 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.