package AccTransCorrections;
+use utf8;
use strict;
use List::Util qw(first);
use SL::DBUtils;
use SL::Taxkeys;
+use SL::DB;
sub new {
my $type = shift;
push @where, qq|at.transdate <= ?|;
push @values, $params{transdate_to};
}
-# $params{trans_id} = 3150;
+
if ($params{trans_id}) {
push @where, qq|at.trans_id = ?|;
push @values, $params{trans_id};
}
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,
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 = ();
delete $entry->{chartlink};
}
- # Verknüpfungen zwischen Steuerschlüsseln und zum Zeitpunkt der Transaktion
- # gültigen Steuersätze
+ # Verknüpfungen zwischen Steuerschlüsseln und zum Zeitpunkt der Transaktion
+ # gültigen Steuersätze
my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $transaction->[0]->{transdate});
my ($trans_type, $previous_non_tax_entry);
}
}
- # Alle Einträge entfernen, die die Gegenkonten zu Zahlungsein- und
- # -ausgängen darstellen.
+ # Alle Einträge entfernen, die die Gegenkonten zu Zahlungsein- und
+ # -ausgängen darstellen.
foreach my $payment (@{ $data->{payments} }) {
my $idx = 0 < $payment->{amount} ? 'debit' : 'credit';
}
# Problemfall: Verkaufsrechnungen, bei denen Buchungen auf Warenbestandskonten
-# mit Steuerschlüssel != 0 durchgeführt wurden. Richtig wäre, dass alle
-# Steuerschlüssel für solche Warenbestandsbuchungen 0 sind.
+# mit Steuerschlüssel != 0 durchgeführt wurden. Richtig wäre, dass alle
+# Steuerschlüssel für solche Warenbestandsbuchungen 0 sind.
sub _check_trans_invoices_inventory_with_taxkeys {
$main::lxdebug->enter_sub();
my $self = shift;
my %params = @_;
+ # ist nur für bestandsmethode notwendig. bei der Aufwandsmethode
+ # können Warenkonten mit Steuerschlüssel sein (5400 in SKR04)
+ return 0 if $::instance_conf->get_inventory_system eq 'periodic';
+
if (!$params{transaction}->[0]->{invoice}) {
$main::lxdebug->leave_sub();
return 0;
return 0;
}
-# Problemfall: Kreditorenbuchungen, bei denen mit Umsatzsteuerschlüsseln
-# gebucht wurde und Debitorenbuchungen, bei denen mit Vorsteuerschlüsseln
+# Problemfall: Verkaufsrechnungen, bei denen Steuern verbucht wurden, obwohl
+# kein Steuerschlüssel eingetragen ist.
+sub _check_missing_taxkeys_in_invoices {
+ $::lxdebug->enter_sub;
+
+ my $self = shift;
+ my %params = @_;
+ my $transaction = $params{transaction};
+ my $found_broken = 0;
+
+ $::lxdebug->leave_sub and return 0
+ if !$transaction->[0]->{invoice};
+
+ my @sub_transactions = $self->_group_sub_transactions($transaction);
+
+ for my $sub_transaction (@sub_transactions) {
+ $::lxdebug->leave_sub and return 0
+ if _is_split_transaction($sub_transaction)
+ || _is_simple_transaction($sub_transaction);
+
+ my $split_side_entries = _get_splitted_side($sub_transaction);
+ my $num_tax_rows;
+ my $num_taxed_rows;
+ for my $entry (@{ $split_side_entries }) {
+ my $is_tax = grep { m/(?:AP_tax|AR_tax)/ } keys %{ $entry->{chartlinks} };
+
+ $num_tax_rows++ if $is_tax;
+ $num_taxed_rows++ if !$is_tax && $entry->{tax_key} != 0;
+ }
+
+ # now if this has tax rows but NO taxed rows, something is wrong.
+ if ($num_tax_rows > 0 && $num_taxed_rows == 0) {
+ $params{problem}->{type} = 'missing_taxkeys_in_invoices';
+ push @{ $self->{missing_taxkeys_in_invoices} ||= [] }, $params{problem};
+ $found_broken = 1;
+ }
+ }
+
+ $::lxdebug->leave_sub;
+
+ return $found_broken;
+}
+
+# Problemfall: Kreditorenbuchungen, bei denen mit Umsatzsteuerschlüsseln
+# gebucht wurde und Debitorenbuchungen, bei denen mit Vorsteuerschlüsseln
# gebucht wurde.
sub _check_trans_ap_ar_wrong_taxkeys {
$main::lxdebug->enter_sub();
}
# Problemfall: Splitbuchungen, die mehrere Haben- und Sollkonten ansprechen.
-# Aber nur für Debitoren- und Kreditorenbuchungen, weil das bei Einkaufs- und
-# Verkaufsrechnungen hingegen völlig normal ist.
+# Aber nur für Debitoren- und Kreditorenbuchungen, weil das bei Einkaufs- und
+# Verkaufsrechnungen hingegen völlig normal ist.
sub _check_trans_split_multiple_credit_and_debit {
$main::lxdebug->enter_sub();
}
# Problemfall: Buchungen, bei denen Steuersummen nicht mit den Summen
-# übereinstimmen, die nach ausgewähltem Steuerschlüssel hätten auftreten müssen.
+# übereinstimmen, die nach ausgewähltem Steuerschlüssel hätten auftreten müssen.
sub _check_trans_wrong_taxkeys {
$main::lxdebug->enter_sub();
}
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);
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,
return $retval;
}
-# Inaktiver Code für das Erraten möglicher Verteilungen von
-# Steuerschlüsseln. Deaktiviert, weil er exponentiell Zeit
-# benötigt.
+# Inaktiver Code für das Erraten möglicher Verteilungen von
+# Steuerschlüsseln. Deaktiviert, weil er exponentiell Zeit
+# benötigt.
# if (abs($expected_tax - $data{$side}->{tax_sum}) >= 0.02) {
# my @potential_taxkeys = $trans_type eq 'AP' ? (0, 8, 9) : (0, 1, 2, 3);
# $main::lxdebug->dump(0, "pota", \@potential_taxkeys);
-# # Über alle Kombinationen aus Buchungssätzen und potenziellen Steuerschlüsseln
+# # Über alle Kombinationen aus Buchungssätzen und potenziellen Steuerschlüsseln
# # iterieren und jeweils die Summe ermitteln.
# my $num_entries = scalar @{ $data{$side}->{entries} };
# my @taxkey_indices = (0) x $num_entries;
# while ($num_entries == scalar @taxkey_indices) {
# my @tax_cache = ();
-# # Berechnen der Steuersumme für die aktuell angenommenen Steuerschlüssel.
+# # Berechnen der Steuersumme für die aktuell angenommenen Steuerschlüssel.
# my $tax_sum = 0;
# foreach my $i (0 .. $num_entries - 1) {
# my $taxkey = $potential_taxkeys[$taxkey_indices[$i]];
# $tax_sum += $tax_cache[$i];
# }
-# # Entspricht die Steuersumme mit den aktuell angenommenen Steuerschlüsseln
+# # Entspricht die Steuersumme mit den aktuell angenommenen Steuerschlüsseln
# # der verbuchten Steuersumme? Wenn ja, dann ist das eine potenzielle
-# # Lösung.
+# # Lösung.
# if (abs($tax_sum - $data{$side}->{tax_sum}) < 0.02) {
# push @solutions, {
# 'taxkeys' => [ @potential_taxkeys[@taxkey_indices] ],
# }
# }
-# # Weiterzählen der Steuerschlüsselindices zum Interieren über
-# # alle möglichen Kombinationen.
+# # Weiterzählen der Steuerschlüsselindices zum Interieren über
+# # alle möglichen Kombinationen.
# my $i = 0;
# while (1) {
# $taxkey_indices[$i]++;
# $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],
# };
# }
my @problems = @{ $self->{problems} };
+ map { $self->{$_} ||= [] } qw(ap_ar_taxkey_problems invoice_inventory_taxkey_problems missing_taxkeys_in_invoices);
+
if (0 != scalar @{ $self->{ap_ar_taxkey_problems} }) {
my $problem = {
'type' => 'ap_ar_wrong_taxkeys',
unshift @problems, $problem;
}
+ if (0 != scalar @{ $self->{missing_taxkeys_in_invoices} }) {
+ my $problem = {
+ 'type' => 'missing_taxkeys_in_invoices',
+ 'ap_problems' => [ grep { $_->{data}->{module} eq 'ap' } @{ $self->{missing_taxkeys_in_invoices} } ],
+ 'ar_problems' => [ grep { $_->{data}->{module} eq 'ar' } @{ $self->{missing_taxkeys_in_invoices} } ],
+ };
+ unshift @problems, $problem;
+ }
+
$main::lxdebug->leave_sub();
+# $::lxdebug->dump(0, 'problems:', \@problems);
return @problems;
}
my $myconfig = \%main::myconfig;
my $form = $main::form;
- my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
+ my $dbh = $params{dbh} || SL::DB->client->dbh;
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)
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;
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 $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 $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});
- } else {
- do_statement($form, $h_taxkey_only, $q_taxkey_only, $entry->{taxkey}, $entry->{oid});
+ SL::DB->client->with_transaction(sub {
+ 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 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->{acc_trans_id});
+ } else {
+ do_statement($form, $h_taxkey_only, $q_taxkey_only, $entry->{taxkey}, $entry->{acc_trans_id});
+ }
}
- }
- $h_taxkey_only->finish();
- $h_taxkey_chart_id->finish();
-
- $dbh->commit() unless ($params{dbh});
+ $h_taxkey_only->finish();
+ $h_taxkey_chart_id->finish();
+ 1;
+ }) or do { die SL::DB->client->error };
}
$main::lxdebug->leave_sub();
my $self = shift;
my %params = @_;
+ # ist nur für bestandsmethode notwendig. bei der Aufwandsmethode
+ # können Warenkonten mit Steuerschlüssel sein (5400 in SKR04)
+ return 0 if $::instance_conf->get_inventory_system eq 'periodic';
+
my $myconfig = \%main::myconfig;
my $form = $main::form;
- my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
+ my $dbh = $params{dbh} || SL::DB->client->dbh;
- 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)
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;
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 = ?|;
- $sth = prepare_query($form, $dbh, $query);
-
- foreach my $oid (@corrections) {
- do_statement($form, $sth, $query, $oid);
- }
+ SL::DB->client->with_transaction(sub {
+ $query = qq|UPDATE acc_trans SET taxkey = 0 WHERE acc_trans_id = ?|;
+ $sth = prepare_query($form, $dbh, $query);
- $sth->finish();
+ foreach my $acc_trans_id (@corrections) {
+ do_statement($form, $sth, $query, $acc_trans_id);
+ }
- $dbh->commit() unless ($params{dbh});
-# $dbh->rollback();
+ $sth->finish();
+ 1;
+ }) or do { die SL::DB->client->error };
}
$main::lxdebug->leave_sub();
my $myconfig = \%main::myconfig;
my $form = $main::form;
- my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
+ my $dbh = $params{dbh} || SL::DB->client->dbh;
- my $q_taxkey_only = qq|UPDATE acc_trans SET taxkey = ? WHERE oid = ?|;
- my $h_taxkey_only = prepare_query($form, $dbh, $q_taxkey_only);
+ SL::DB->client->with_transaction(sub {
+ 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 $h_taxkey_chart = prepare_query($form, $dbh, $q_taxkey_chart);
+ 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 $h_transdate = prepare_query($form, $dbh, $q_transdate);
+ 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});
+ foreach my $fix (@{ $params{fixes} }) {
+ 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}));
- my ($transdate) = $h_transdate->fetchrow_array();
+ 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);
- my $tax_info = $all_taxes{taxkeys}->{ $fix->{taxkey} };
+ my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $transdate);
+ my $tax_info = $all_taxes{taxkeys}->{ $fix->{taxkey} };
- next unless ($tax_info);
+ 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}));
- }
-
- $h_taxkey_only->finish();
- $h_taxkey_chart->finish();
- $h_transdate->finish();
+ 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}));
+ }
-# $dbh->rollback();
- $dbh->commit() unless ($params{dbh});
+ $h_taxkey_only->finish();
+ $h_taxkey_chart->finish();
+ $h_transdate->finish();
+ 1;
+ }) or do { die SL::DB->client->error };
$main::lxdebug->leave_sub();
}
my $myconfig = \%main::myconfig;
my $form = $main::form;
- my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
-
- do_query($form, $dbh, qq|UPDATE ar SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
- do_query($form, $dbh, qq|UPDATE ap SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
- do_query($form, $dbh, qq|UPDATE gl SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
+ my $dbh = $params{dbh} || SL::DB->client->dbh;
- do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, conv_i($params{trans_id}));
- do_query($form, $dbh, qq|DELETE FROM ap WHERE id = ?|, conv_i($params{trans_id}));
- do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($params{trans_id}));
- do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, conv_i($params{trans_id}));
+ SL::DB->client->with_transaction(sub {
+ do_query($form, $dbh, qq|UPDATE ar SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
+ do_query($form, $dbh, qq|UPDATE ap SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
+ do_query($form, $dbh, qq|UPDATE gl SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
-# $dbh->rollback();
- $dbh->commit() unless ($params{dbh});
+ do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, conv_i($params{trans_id}));
+ do_query($form, $dbh, qq|DELETE FROM ap WHERE id = ?|, conv_i($params{trans_id}));
+ do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($params{trans_id}));
+ do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, conv_i($params{trans_id}));
+ 1;
+ }) or do { die SL::DB->client->error };
$main::lxdebug->leave_sub();
}
projects / kivitendo-erp.git / blobdiff