From 6ff01fdb61880344c2826c86955ef4c1e8f47db4 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 11 May 2009 13:27:06 +0000 Subject: [PATCH] =?utf8?q?Einf=C3=BChrung=20einer=20ID-Spalte=20in=20acc?= =?utf8?q?=5Ftrans?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 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. --- SL/AP.pm | 22 +++--- SL/AR.pm | 22 +++--- SL/AccTransCorrections.pm | 74 +++++++++---------- SL/DATEV.pm | 8 +- SL/DN.pm | 2 +- SL/Form.pm | 2 +- SL/GL.pm | 19 +++-- SL/IR.pm | 20 ++--- SL/IS.pm | 20 ++--- SL/RC.pm | 4 +- sql/Pg-upgrade2/acc_trans_without_oid.sql | 48 ++++++++++++ .../assistant_for_wrong_taxkeys_de.html | 6 +- .../assistant_for_wrong_taxkeys_master.html | 6 +- 13 files changed, 152 insertions(+), 101 deletions(-) create mode 100644 sql/Pg-upgrade2/acc_trans_without_oid.sql diff --git a/SL/AP.pm b/SL/AP.pm index e2709f734..206b9fd7a 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -509,34 +509,34 @@ sub _delete_payments { my ($self, $form, $dbh) = @_; - my @delete_oids; + my @delete_acc_trans_ids; # Delete old payment entries from acc_trans. my $query = - qq|SELECT oid + qq|SELECT acc_trans_id FROM acc_trans WHERE (trans_id = ?) AND fx_transaction UNION - SELECT at.oid + SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND (c.link LIKE '%AP_paid%')|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); $query = - qq|SELECT at.oid + qq|SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id OFFSET 1|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); - if (@delete_oids) { - $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + if (@delete_acc_trans_ids) { + $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|; do_query($form, $dbh, $query); } @@ -589,7 +589,7 @@ sub post_payment { LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{APselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); @@ -760,7 +760,7 @@ sub storno { do_query($form, $dbh, $query, $id); # now copy acc_trans entries - $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|; + $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|; my $rowref = selectall_hashref_query($form, $dbh, $query, $id); # kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/ diff --git a/SL/AR.pm b/SL/AR.pm index 4a29c434c..68b9c1cbf 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -279,34 +279,34 @@ sub _delete_payments { my ($self, $form, $dbh) = @_; - my @delete_oids; + my @delete_acc_trans_ids; # Delete old payment entries from acc_trans. my $query = - qq|SELECT oid + qq|SELECT acc_trans_id FROM acc_trans WHERE (trans_id = ?) AND fx_transaction UNION - SELECT at.oid + SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); $query = - qq|SELECT at.oid + qq|SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id OFFSET 1|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); - if (@delete_oids) { - $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + if (@delete_acc_trans_ids) { + $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|; do_query($form, $dbh, $query); } @@ -359,7 +359,7 @@ sub post_payment { LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{ARselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); @@ -650,7 +650,7 @@ sub storno { do_query($form, $dbh, $query, $id); # now copy acc_trans entries - $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|; + $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|; my $rowref = selectall_hashref_query($form, $dbh, $query, $id); # kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/ diff --git a/SL/AccTransCorrections.pm b/SL/AccTransCorrections.pm index 1d423d6cf..44f3afd6b 100644 --- a/SL/AccTransCorrections.pm +++ b/SL/AccTransCorrections.pm @@ -53,7 +53,7 @@ sub _fetch_transactions { } my $query = qq! - SELECT at.oid, at.*, + SELECT at.*, c.accno, c.description AS chartdescription, c.charttype, c.category AS chartcategory, c.link AS chartlink, COALESCE(gl.reference, COALESCE(ap.invnumber, ar.invnumber)) AS reference, COALESCE(ap.invoice, COALESCE(ar.invoice, FALSE)) AS invoice, @@ -74,7 +74,7 @@ sub _fetch_transactions { LEFT JOIN ap ON (at.trans_id = ap.id) LEFT JOIN ar ON (at.trans_id = ar.id) $where - ORDER BY at.trans_id, at.oid + ORDER BY at.trans_id, at.acc_trans_id !; my @transactions = (); @@ -410,12 +410,12 @@ sub _check_trans_wrong_taxkeys { } foreach my $entry (@{ $data{$side}->{entries} }) { - $entry->{actual_tax} = $form->round_amount(abs($entry->{tax_entry} ? $entry->{tax_entry}->{amount} : 0), 2); - $entry->{expected_tax} = $form->round_amount(abs($entry->{expected_tax}), 2); - $entry->{taxkey_error} = ( $entry->{taxkey} && !$entry->{tax_entry}) - || (!$entry->{taxkey} && $entry->{tax_entry}) - || (abs($entry->{expected_tax} - $entry->{actual_tax}) >= 0.02); - $entry->{tax_entry_oid} = $entry->{tax_entry}->{oid}; + $entry->{actual_tax} = $form->round_amount(abs($entry->{tax_entry} ? $entry->{tax_entry}->{amount} : 0), 2); + $entry->{expected_tax} = $form->round_amount(abs($entry->{expected_tax}), 2); + $entry->{taxkey_error} = ( $entry->{taxkey} && !$entry->{tax_entry}) + || (!$entry->{taxkey} && $entry->{tax_entry}) + || (abs($entry->{expected_tax} - $entry->{actual_tax}) >= 0.02); + $entry->{tax_entry_acc_trans_id} = $entry->{tax_entry}->{acc_trans_id}; delete $entry->{tax_entry}; $entry->{display_amount} = $form->round_amount(abs($entry->{amount}) * $storno_mult, 2); @@ -438,7 +438,7 @@ sub _check_trans_wrong_taxkeys { my $tax_info = $all_taxes{taxkeys}->{$taxkey}; - next if ((!$tax_info || (0 == $tax_info->{taxrate} * 1)) && $entry->{tax_entry_oid}); + next if ((!$tax_info || (0 == $tax_info->{taxrate} * 1)) && $entry->{tax_entry_acc_trans_id}); push @{ $entry->{correct_taxkeys} }, { 'taxkey' => $taxkey, @@ -552,7 +552,7 @@ sub _check_trans_wrong_taxkeys { # $solution->{rows}->[$i]->{taxdescription} .= ' ' . $form->format_amount(\%myconfig, $tax_entry->{taxrate} * 100) . ' %'; # push @{ $solution->{changes} }, { -# 'oid' => $entry->{oid}, +# 'acc_trans_id' => $entry->{acc_trans_id}, # 'taxkey' => $solution->{taxkeys}->[$i], # }; # } @@ -641,7 +641,7 @@ sub fix_ap_ar_wrong_taxkeys { my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); my $query = qq|SELECT 'ap' AS module, - at.oid, at.trans_id, at.chart_id, at.amount, at.taxkey, at.transdate, + at.acc_trans_id, at.trans_id, at.chart_id, at.amount, at.taxkey, at.transdate, c.link FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) @@ -651,14 +651,14 @@ sub fix_ap_ar_wrong_taxkeys { UNION SELECT 'ar' AS module, - at.oid, at.trans_id, at.chart_id, at.amount, at.taxkey, at.transdate, + at.acc_trans_id, at.trans_id, at.chart_id, at.amount, at.taxkey, at.transdate, c.link FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id IN (SELECT id FROM ar WHERE NOT invoice)) AND (taxkey IN (8, 9, 18, 19)) - ORDER BY trans_id, oid|; + ORDER BY trans_id, acc_trans_id|; my $sth = prepare_execute_query($form, $dbh, $query); my @transactions; @@ -706,29 +706,29 @@ sub fix_ap_ar_wrong_taxkeys { my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $non_tax->{transdate}); - push @corrections, ({ 'oid' => $non_tax->{oid}, - 'taxkey' => $taxkey_replacements{$non_tax->{taxkey}}, + push @corrections, ({ 'acc_trans_id' => $non_tax->{acc_trans_id}, + 'taxkey' => $taxkey_replacements{$non_tax->{taxkey}}, }, { - 'oid' => $tax->{oid}, - 'taxkey' => $taxkey_replacements{$non_tax->{taxkey}}, - 'chart_id' => $all_taxes{taxkeys}->{ $taxkey_replacements{$non_tax->{taxkey}} }->{taxchart_id}, + 'acc_trans_id' => $tax->{acc_trans_id}, + 'taxkey' => $taxkey_replacements{$non_tax->{taxkey}}, + 'chart_id' => $all_taxes{taxkeys}->{ $taxkey_replacements{$non_tax->{taxkey}} }->{taxchart_id}, }); } } if (scalar @corrections) { - my $q_taxkey_only = qq|UPDATE acc_trans SET taxkey = ? WHERE oid = ?|; + my $q_taxkey_only = qq|UPDATE acc_trans SET taxkey = ? WHERE acc_trans_id = ?|; my $h_taxkey_only = prepare_query($form, $dbh, $q_taxkey_only); - my $q_taxkey_chart_id = qq|UPDATE acc_trans SET taxkey = ?, chart_id = ? WHERE oid = ?|; + my $q_taxkey_chart_id = qq|UPDATE acc_trans SET taxkey = ?, chart_id = ? WHERE acc_trans_id = ?|; my $h_taxkey_chart_id = prepare_query($form, $dbh, $q_taxkey_chart_id); foreach my $entry (@corrections) { if ($entry->{chart_id}) { - do_statement($form, $h_taxkey_chart_id, $q_taxkey_chart_id, $entry->{taxkey}, $entry->{chart_id}, $entry->{oid}); + do_statement($form, $h_taxkey_chart_id, $q_taxkey_chart_id, $entry->{taxkey}, $entry->{chart_id}, $entry->{acc_trans_id}); } else { - do_statement($form, $h_taxkey_only, $q_taxkey_only, $entry->{taxkey}, $entry->{oid}); + do_statement($form, $h_taxkey_only, $q_taxkey_only, $entry->{taxkey}, $entry->{acc_trans_id}); } } @@ -752,7 +752,7 @@ sub fix_invoice_inventory_with_taxkeys { my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - my $query = qq|SELECT at.oid, at.*, c.link + my $query = qq|SELECT at.*, c.link FROM acc_trans at LEFT JOIN ar ON (at.trans_id = ar.id) LEFT JOIN chart c ON (at.chart_id = c.id) @@ -760,13 +760,13 @@ sub fix_invoice_inventory_with_taxkeys { UNION - SELECT at.oid, at.*, c.link + SELECT at.*, c.link FROM acc_trans at LEFT JOIN ap ON (at.trans_id = ap.id) LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (ap.invoice) - ORDER BY trans_id, oid|; + ORDER BY trans_id, acc_trans_id|; my $sth = prepare_execute_query($form, $dbh, $query); my @transactions; @@ -792,17 +792,17 @@ sub fix_invoice_inventory_with_taxkeys { foreach my $entry (@{ $sub_transaction }) { next if ($entry->{taxkey} == 0); - push @corrections, $entry->{oid}; + push @corrections, $entry->{acc_trans_id}; } } } if (@corrections) { - $query = qq|UPDATE acc_trans SET taxkey = 0 WHERE oid = ?|; + $query = qq|UPDATE acc_trans SET taxkey = 0 WHERE acc_trans_id = ?|; $sth = prepare_query($form, $dbh, $query); - foreach my $oid (@corrections) { - do_statement($form, $sth, $query, $oid); + foreach my $acc_trans_id (@corrections) { + do_statement($form, $sth, $query, $acc_trans_id); } $sth->finish(); @@ -827,23 +827,23 @@ sub fix_wrong_taxkeys { my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - my $q_taxkey_only = qq|UPDATE acc_trans SET taxkey = ? WHERE oid = ?|; + my $q_taxkey_only = qq|UPDATE acc_trans SET taxkey = ? WHERE acc_trans_id = ?|; my $h_taxkey_only = prepare_query($form, $dbh, $q_taxkey_only); - my $q_taxkey_chart = qq|UPDATE acc_trans SET taxkey = ?, chart_id = ? WHERE oid = ?|; + my $q_taxkey_chart = qq|UPDATE acc_trans SET taxkey = ?, chart_id = ? WHERE acc_trans_id = ?|; my $h_taxkey_chart = prepare_query($form, $dbh, $q_taxkey_chart); - my $q_transdate = qq|SELECT transdate FROM acc_trans WHERE oid = ?|; + my $q_transdate = qq|SELECT transdate FROM acc_trans WHERE acc_trans_id = ?|; my $h_transdate = prepare_query($form, $dbh, $q_transdate); foreach my $fix (@{ $params{fixes} }) { - next unless ($fix->{oid}); + next unless ($fix->{acc_trans_id}); - do_statement($form, $h_taxkey_only, $q_taxkey_only, conv_i($fix->{taxkey}), conv_i($fix->{oid})); + do_statement($form, $h_taxkey_only, $q_taxkey_only, conv_i($fix->{taxkey}), conv_i($fix->{acc_trans_id})); - next unless ($fix->{tax_entry_oid}); + next unless ($fix->{tax_entry_acc_trans_id}); - do_statement($form, $h_transdate, $q_transdate, conv_i($fix->{tax_entry_oid})); + do_statement($form, $h_transdate, $q_transdate, conv_i($fix->{tax_entry_acc_trans_id})); my ($transdate) = $h_transdate->fetchrow_array(); my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $transdate); @@ -851,7 +851,7 @@ sub fix_wrong_taxkeys { next unless ($tax_info); - do_statement($form, $h_taxkey_chart, $q_taxkey_chart, conv_i($fix->{taxkey}), conv_i($tax_info->{taxchart_id}), conv_i($fix->{tax_entry_oid})); + do_statement($form, $h_taxkey_chart, $q_taxkey_chart, conv_i($fix->{taxkey}), conv_i($tax_info->{taxchart_id}), conv_i($fix->{tax_entry_acc_trans_id})); } $h_taxkey_only->finish(); diff --git a/SL/DATEV.pm b/SL/DATEV.pm index ac3c11228..407943928 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -332,7 +332,7 @@ sub _get_transactions { my %all_taxchart_ids = selectall_as_map($form, $dbh, qq|SELECT DISTINCT chart_id, TRUE AS is_set FROM tax|, 'chart_id', 'is_set'); my $query = - qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, + qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ar.invnumber, ar.duedate, ar.amount as umsatz, ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link, @@ -347,7 +347,7 @@ sub _get_transactions { UNION ALL - SELECT ac.oid, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, + SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ap.invnumber, ap.duedate, ap.amount as umsatz, ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link, @@ -362,7 +362,7 @@ sub _get_transactions { UNION ALL - SELECT ac.oid, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, + SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, gl.description AS name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link, @@ -374,7 +374,7 @@ sub _get_transactions { AND $fromto $filter - ORDER BY trans_id, oid|; + ORDER BY trans_id, acc_trans_id|; my $sth = prepare_execute_query($form, $dbh, $query); diff --git a/SL/DN.pm b/SL/DN.pm index 6105f83b0..648645591 100644 --- a/SL/DN.pm +++ b/SL/DN.pm @@ -838,7 +838,7 @@ sub print_invoice_for_fees { $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{login}); map { $form->{"employee_${_}"} = $ref->{$_} } keys %{ $ref }; - $query = qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY oid ASC|; + $query = qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY acc_trans_id ASC|; $sth = prepare_execute_query($form, $dbh, $query, $ar_id); my ($row, $fee, $interest) = (0, 0, 0); diff --git a/SL/Form.pm b/SL/Form.pm index d2f8cb2e7..c13422264 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -2829,7 +2829,7 @@ sub create_links { (startdate <= a.transdate) ORDER BY startdate DESC LIMIT 1)) WHERE a.trans_id = ? AND a.fx_transaction = '0' - ORDER BY a.oid, a.transdate|; + ORDER BY a.acc_trans_id, a.transdate|; $sth = $dbh->prepare($query); do_statement($self, $sth, $query, $self->{id}); diff --git a/SL/GL.pm b/SL/GL.pm index 51b1d4b9d..bdd4bcdda 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -344,9 +344,10 @@ sub all_transactions { my $query = qq|SELECT - ac.oid AS acoid, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, + ac.acc_trans_id, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, g.description, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, g.notes, t.chart_id, ac.oid + ac.amount, c.accno, g.notes, t.chart_id, + CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee $project_columns $columns_for_sorting{gl} FROM gl g, acc_trans ac $project_join, chart c @@ -357,9 +358,10 @@ sub all_transactions { UNION - SELECT ac.oid AS acoid, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, + SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, a.notes, t.chart_id, ac.oid + ac.amount, c.accno, a.notes, t.chart_id, + CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee $project_columns $columns_for_sorting{arap} FROM ar a, acc_trans ac $project_join, customer ct, chart c @@ -371,9 +373,10 @@ sub all_transactions { UNION - SELECT ac.oid AS acoid, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, + SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, a.notes, t.chart_id, ac.oid + ac.amount, c.accno, a.notes, t.chart_id, + CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee $project_columns $columns_for_sorting{arap} FROM ap a, acc_trans ac $project_join, vendor ct, chart c @@ -383,7 +386,7 @@ sub all_transactions { AND (a.vendor_id = ct.id) AND (a.id = ac.trans_id) - ORDER BY $sortorder, acoid $sortdir|; + ORDER BY $sortorder, acc_trans_id $sortdir|; my @values = (@glvalues, @arvalues, @apvalues); @@ -635,7 +638,7 @@ sub transaction { ORDER BY startdate DESC LIMIT 1)) WHERE (a.trans_id = ?) AND (a.fx_transaction = '0') - ORDER BY a.oid, a.transdate|; + ORDER BY a.acc_trans_id, a.transdate|; $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id})); } else { diff --git a/SL/IR.pm b/SL/IR.pm index 4c57f856d..e36b7f398 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -1187,34 +1187,34 @@ sub _delete_payments { my ($self, $form, $dbh) = @_; - my @delete_oids; + my @delete_acc_trans_ids; # Delete old payment entries from acc_trans. my $query = - qq|SELECT oid + qq|SELECT acc_trans_id FROM acc_trans WHERE (trans_id = ?) AND fx_transaction UNION - SELECT at.oid + SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND (c.link LIKE '%AP_paid%')|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); $query = - qq|SELECT at.oid + qq|SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id OFFSET 1|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); - if (@delete_oids) { - $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + if (@delete_acc_trans_ids) { + $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|; do_query($form, $dbh, $query); } @@ -1271,7 +1271,7 @@ sub post_payment { LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{AP}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); diff --git a/SL/IS.pm b/SL/IS.pm index cf35f9368..f2eef1edb 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -1063,34 +1063,34 @@ sub _delete_payments { my ($self, $form, $dbh) = @_; - my @delete_oids; + my @delete_acc_trans_ids; # Delete old payment entries from acc_trans. my $query = - qq|SELECT oid + qq|SELECT acc_trans_id FROM acc_trans WHERE (trans_id = ?) AND fx_transaction UNION - SELECT at.oid + SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); $query = - qq|SELECT at.oid + qq|SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id OFFSET 1|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); - if (@delete_oids) { - $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + if (@delete_acc_trans_ids) { + $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|; do_query($form, $dbh, $query); } @@ -1147,7 +1147,7 @@ sub post_payment { LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); diff --git a/SL/RC.pm b/SL/RC.pm index 65ea665f0..aa0b49214 100644 --- a/SL/RC.pm +++ b/SL/RC.pm @@ -91,7 +91,7 @@ sub payment_transactions { ($form->{beginningbalance}, $form->{category}) = selectrow_query($form, $dbh, $query, @values); - my %oid = ('Pg' => 'ac.oid', + my %oid = ('Pg' => 'ac.acc_trans_id', 'Oracle' => 'ac.rowid'); @values = (); $query = @@ -184,7 +184,7 @@ sub reconcile { my $dbh = $form->dbconnect($myconfig); my ($query, $i); - my %oid = ('Pg' => 'oid', + my %oid = ('Pg' => 'acc_trans_id', 'Oracle' => 'rowid'); # clear flags diff --git a/sql/Pg-upgrade2/acc_trans_without_oid.sql b/sql/Pg-upgrade2/acc_trans_without_oid.sql new file mode 100644 index 000000000..04a39fca1 --- /dev/null +++ b/sql/Pg-upgrade2/acc_trans_without_oid.sql @@ -0,0 +1,48 @@ +-- @tag: acc_trans_without_oid +-- @description: Einführen einer ID-Spalte in acc_trans +-- @depends: release_2_4_3 cb_ob_transaction + +CREATE SEQUENCE acc_trans_id_seq; + +CREATE TABLE new_acc_trans ( + acc_trans_id integer DEFAULT nextval('acc_trans_id_seq'), + trans_id integer, + chart_id integer, + amount numeric(15,5), + transdate date DEFAULT date('now'::text), + gldate date DEFAULT date('now'::text), + source text, + cleared boolean DEFAULT false, + fx_transaction boolean DEFAULT false, + ob_transaction boolean DEFAULT false, + cb_transaction boolean DEFAULT false, + project_id integer, + memo text, + taxkey integer, + itime timestamp without time zone DEFAULT now(), + mtime timestamp without time zone +); + +INSERT INTO new_acc_trans (acc_trans_id, trans_id, chart_id, amount, transdate, gldate, source, cleared, + fx_transaction, ob_transaction, cb_transaction, project_id, memo, taxkey, itime, mtime) + SELECT oid, trans_id, chart_id, amount, transdate, gldate, source, cleared, + fx_transaction, ob_transaction, cb_transaction, project_id, memo, taxkey, itime, mtime + FROM acc_trans; + +SELECT setval('acc_trans_id_seq', (SELECT COALESCE((SELECT MAX(oid::integer) FROM acc_trans), 0) + 1)); + +DROP TABLE acc_trans; +ALTER TABLE new_acc_trans RENAME TO acc_trans; + +CREATE INDEX acc_trans_trans_id_key ON acc_trans USING btree (trans_id); +CREATE INDEX acc_trans_chart_id_key ON acc_trans USING btree (chart_id); +CREATE INDEX acc_trans_transdate_key ON acc_trans USING btree (transdate); +CREATE INDEX acc_trans_source_key ON acc_trans USING btree (lower(source)); + +ALTER TABLE ONLY acc_trans + ADD CONSTRAINT "$1" FOREIGN KEY (chart_id) REFERENCES chart(id); + +CREATE TRIGGER mtime_acc_trans + BEFORE UPDATE ON acc_trans + FOR EACH ROW + EXECUTE PROCEDURE set_mtime(); diff --git a/templates/webpages/acctranscorrections/assistant_for_wrong_taxkeys_de.html b/templates/webpages/acctranscorrections/assistant_for_wrong_taxkeys_de.html index b5c3ff929..a5283053b 100644 --- a/templates/webpages/acctranscorrections/assistant_for_wrong_taxkeys_de.html +++ b/templates/webpages/acctranscorrections/assistant_for_wrong_taxkeys_de.html @@ -58,7 +58,7 @@ [%- SET curr_row = 0 %] - [%- IF (problem.data.module == 'ar') || ((problem.data.module == 'gl') && (problem.acc_trans.credit.entries.first.oid < problem.acc_trans.debit.entries.first.oid)) %] + [%- IF (problem.data.module == 'ar') || ((problem.data.module == 'gl') && (problem.acc_trans.credit.entries.first.acc_trans_id < problem.acc_trans.debit.entries.first.acc_trans_id)) %] [%- SET order = [ 'credit', 'debit' ] %] [%- SET other_side = 'debit' %] [%- ELSE %] @@ -94,8 +94,8 @@ [%- IF row.taxkey_error %] - - + + - + +