X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/72d93b0b6cbca4fc4dabb116f20c1b88a22fde88..eb0c10b0a09da8ff4590c3043a9fa6dd10775ee4:/SL/AccTransCorrections.pm diff --git a/SL/AccTransCorrections.pm b/SL/AccTransCorrections.pm new file mode 100644 index 000000000..610d87e7e --- /dev/null +++ b/SL/AccTransCorrections.pm @@ -0,0 +1,895 @@ +package AccTransCorrections; + +use strict; + +use List::Util qw(first); + +use SL::DBUtils; +use SL::Taxkeys; + +sub new { + my $type = shift; + + my $self = {}; + + bless $self, $type; + + $self->{taxkeys} = Taxkeys->new(); + + return $self; +} + +sub _fetch_transactions { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my (@where, @values) = ((), ()); + + if ($params{transdate_from}) { + push @where, qq|at.transdate >= ?|; + push @values, $params{transdate_from}; + } + + if ($params{transdate_to}) { + 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 $where = ''; + if (scalar @where) { + $where = 'WHERE ' . join(' AND ', map { "($_)" } @where); + } + + my $query = qq! + SELECT at.oid, 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, + CASE + WHEN gl.id IS NOT NULL THEN gl.storno AND (gl.storno_id IS NOT NULL) + WHEN ap.id IS NOT NULL THEN ap.storno AND (ap.storno_id IS NOT NULL) + ELSE ar.storno AND (ar.storno_id IS NOT NULL) + END AS is_storno, + CASE + WHEN gl.id IS NOT NULL THEN 'gl' + WHEN ap.id IS NOT NULL THEN 'ap' + ELSE 'ar' + END AS module + + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + LEFT JOIN gl ON (at.trans_id = gl.id) + 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 +!; + + my @transactions = (); + my $last_trans = undef; + + foreach my $entry (@{ selectall_hashref_query($form, $dbh, $query, @values) }) { + if (!$last_trans || ($last_trans->[0]->{trans_id} != $entry->{trans_id})) { + $last_trans = []; + push @transactions, $last_trans; + } + + push @{ $last_trans }, $entry; + } + + $main::lxdebug->leave_sub(); + + return @transactions; +} + +sub _prepare_data { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $transaction = $params{transaction}; + my $callback = $params{callback}; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $data = { + 'credit' => { + 'num' => 0, + 'sum' => 0, + 'entries' => [], + 'tax_sum' => 0, + 'tax_entries' => [], + }, + 'debit' => { + 'num' => 0, + 'sum' => 0, + 'entries' => [], + 'tax_sum' => 0, + 'tax_entries' => [], + }, + 'payments' => [], + }; + + foreach my $entry (@{ $transaction }) { + $entry->{chartlinks} = { map { $_ => 1 } split(m/:/, $entry->{chartlink}) }; + delete $entry->{chartlink}; + } + + # 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); + my $sum = 0; + my $first_sub_trans = 1; + + my $storno_mult = $transaction->[0]->{is_storno} ? -1 : 1; + + # Aufteilung der Buchungspositionen in Soll-, Habenseite sowie + # getrennte Auflistung der Positionen, die auf Steuerkonten gebucht werden. + foreach my $entry (@{ $transaction }) { + if (!$first_sub_trans && ($entry->{chartlinks}->{AP_paid} || $entry->{chartlinks}->{AR_paid})) { + push @{ $data->{payments} }, $entry; + next; + } + + my $tax_info = $all_taxes{taxkeys}->{ $entry->{taxkey} }; + if ($tax_info) { + $entry->{taxdescription} = $tax_info->{taxdescription} . ' ' . $form->format_amount($myconfig, $tax_info->{taxrate} * 100) . ' %'; + } + + if ($entry->{chartlinks}->{AP}) { + $trans_type = 'AP'; + } elsif ($entry->{chartlinks}->{AR}) { + $trans_type = 'AR'; + } + + my $idx = 0 < ($entry->{amount} * $storno_mult) ? 'credit' : 'debit'; + + if ($entry->{chartlinks}->{AP_tax} || $entry->{chartlinks}->{AR_tax}) { + $data->{$idx}->{tax_sum} += $entry->{amount}; + push @{ $data->{$idx}->{tax_entries} }, $entry; + + if ($previous_non_tax_entry) { + $previous_non_tax_entry->{tax_entry} = $entry; + undef $previous_non_tax_entry; + } + + } else { + $data->{$idx}->{sum} += $entry->{amount}; + push @{ $data->{$idx}->{entries} }, $entry; + + $previous_non_tax_entry = $entry; + } + + $sum += $entry->{amount}; + + if (abs($sum) < 0.02) { + $sum = 0; + $first_sub_trans = 0; + } + } + + # 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'; + + foreach my $i (0 .. scalar(@{ $data->{$idx}->{entries} }) - 1) { + my $entry = $data->{$idx}->{entries}->[$i]; + + next if ((($payment->{amount} * -1) != $entry->{amount}) || ($payment->{transdate} ne $entry->{transdate})); + + splice @{ $data->{$idx}->{entries} }, $i, 1; + + last; + } + } + + delete $data->{payments}; + + map { $data->{$_}->{num} = scalar @{ $data->{$_}->{entries} } } qw(credit debit); + + my $info = $transaction->[0]; + my $script = ($info->{module} eq 'ar') && $info->{invoice} ? 'is' + : ($info->{module} eq 'ap') && $info->{invoice} ? 'ir' + : $info->{module}; + + my %common_args = ( + 'data' => $data, + 'trans_type' => $trans_type, + 'all_taxes' => { %all_taxes }, + 'transaction' => $transaction, + 'full_analysis' => $params{full_analysis}, + 'problem' => { + 'data' => $info, + 'link' => $script . ".pl?action=edit${callback}&id=" . $info->{trans_id}, + }, + ); + + $main::lxdebug->leave_sub(); + + return %common_args; +} + +sub _group_sub_transactions { + $main::lxdebug->enter_sub(); + + my $self = shift; + my $transaction = shift; + + my @sub_transactions = (); + my $sum = 0; + + foreach my $i (0 .. scalar(@{ $transaction }) - 1) { + my $entry = $transaction->[$i]; + + if (abs($sum) <= 0.01) { + push @sub_transactions, []; + $sum = 0; + } + $sum += $entry->{amount}; + + push @{ $sub_transactions[-1] }, $entry; + } + + $main::lxdebug->leave_sub(); + + return @sub_transactions; +} + +# 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. +sub _check_trans_invoices_inventory_with_taxkeys { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + if (!$params{transaction}->[0]->{invoice}) { + $main::lxdebug->leave_sub(); + return 0; + } + + my @sub_transactions = $self->_group_sub_transactions($params{transaction}); + + foreach my $sub_transaction (@sub_transactions) { + my $is_cogs = first { $_->{chartlinks}->{IC_cogs} } @{ $sub_transaction }; + next unless ($is_cogs); + + my $needs_fixing = first { $_->{taxkey} != 0 } @{ $sub_transaction }; + next unless ($needs_fixing); + + $params{problem}->{type} = 'invoice_inventory_with_taxkeys'; + push @{ $self->{invoice_inventory_taxkey_problems} }, $params{problem}; + + $main::lxdebug->leave_sub(); + + return 1; + } + + $main::lxdebug->leave_sub(); + + return 0; +} + +# 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(); + + my $self = shift; + my %params = @_; + + my $retval = 0; + + if (!$params{transaction}->[0]->{invoice} + && (( ($params{transaction}->[0]->{module} eq 'ap') + && (first { my $taxkey = $_->{taxkey}; first { $taxkey == $_ } (2, 3, 12, 13) } @{ $params{transaction} })) + || + ( ($params{transaction}->[0]->{module} eq 'ar') + && (first { my $taxkey = $_->{taxkey}; first { $taxkey == $_ } (8, 9, 18, 19) } @{ $params{transaction} })))) { + $params{problem}->{type} = 'ap_ar_wrong_taxkeys'; + push @{ $self->{ap_ar_taxkey_problems} }, $params{problem}; + + $retval = 1; + } + + $main::lxdebug->leave_sub(); + + return $retval; +} + +# 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. +sub _check_trans_split_multiple_credit_and_debit { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $retval = 0; + + if ( !$params{transaction}->[0]->{invoice} + && (1 < $params{data}->{credit}->{num}) + && (1 < $params{data}->{debit}->{num})) { + $params{problem}->{type} = 'split_multiple_credit_and_debit'; + push @{ $self->{problems} }, $params{problem}; + + $retval = 1; + } + + $main::lxdebug->leave_sub(); + + return $retval; +} + +# Problemfall: Buchungen, bei denen Steuersummen nicht mit den Summen +# übereinstimmen, die nach ausgewähltem Steuerschlüssel hätten auftreten müssen. +sub _check_trans_wrong_taxkeys { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $form = $main::form; + + my %data = %{ $params{data} }; + my $transaction = $params{transaction}; + + if ( $transaction->[0]->{invoice} + || $transaction->[0]->{ob_transaction} + || $transaction->[0]->{cb_transaction} + || (!scalar @{ $data{credit}->{entries} } && !scalar @{ $data{debit}->{entries} }) + || ( ($transaction->[0]->{module} eq 'gl') + && (!scalar @{ $data{credit}->{entries} } || !scalar @{ $data{debit}->{entries} }))) { + $main::lxdebug->leave_sub(); + return 0; + } + + my $retval = 0; + + my ($side, $other_side); + if ( (grep { $_->{taxkey} * 1 } @{ $data{credit}->{entries} }) + || (scalar @{ $data{credit}->{tax_entries} })) { + $side = 'credit'; + $other_side = 'debit'; + + } elsif ( (grep { $_->{taxkey} * 1 } @{ $data{debit}->{entries} }) + || (scalar @{ $data{debit}->{tax_entries} })) { + $side = 'debit'; + $other_side = 'credit'; + } + + if (!$side) { + $main::lxdebug->leave_sub(); + return 0; + } + + my $expected_tax = 0; + my %num_entries_per_chart = (); + my $num_taxed_entries = 0; + + foreach my $entry (@{ $data{$side}->{entries} }) { + my $taxinfo = $params{all_taxes}->{taxkeys}->{$entry->{taxkey}} || { }; + $entry->{expected_tax} = $entry->{amount} * $taxinfo->{taxrate}; + $expected_tax += $entry->{expected_tax}; + + $num_taxed_entries++ if ($taxinfo->{taxrate} * 1); + + my $chart_key = $entry->{chart_id} . "-" . $entry->{taxkey}; + $num_entries_per_chart{$chart_key} ||= 0; + $num_entries_per_chart{$chart_key}++; + } + +# $main::lxdebug->message(0, "side $side trans_id $transaction->[0]->{trans_id} expected tax $expected_tax actual tax $data{$side}->{tax_sum}"); + + if (abs($expected_tax - $data{$side}->{tax_sum}) >= (0.01 * ($num_taxed_entries + 1))) { + if ($params{full_analysis}) { + my $storno_mult = $data{$side}->{entries}->[0]->{is_storno} ? -1 : 1; + + foreach my $entry (@{ $data{$other_side}->{entries} }) { + $entry->{display_amount} = $form->round_amount(abs($entry->{amount}) * $storno_mult, 2); + } + + 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}; + delete $entry->{tax_entry}; + + $entry->{display_amount} = $form->round_amount(abs($entry->{amount}) * $storno_mult, 2); + $entry->{display_actual_tax} = $entry->{actual_tax} * $storno_mult; + $entry->{display_expected_tax} = $entry->{expected_tax} * $storno_mult; + + if ($entry->{taxkey_error}) { + $self->{negative_taxkey_filter} ||= { + 'ar' => { map { $_ => 1 } ( 8, 9, 18, 19) }, + 'ap' => { map { $_ => 1 } (1, 2, 3, 12, 13) }, + 'gl' => { }, + }; + + $entry->{correct_taxkeys} = []; + + my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $entry->{transdate}); + + foreach my $taxkey (sort { $a <=> $b } keys %{ $all_taxes{taxkeys} }) { + next if ($self->{negative_taxkey_filter}->{ $entry->{module} }->{$taxkey}); + + my $tax_info = $all_taxes{taxkeys}->{$taxkey}; + + next if ((!$tax_info || (0 == $tax_info->{taxrate} * 1)) && $entry->{tax_entry_oid}); + + push @{ $entry->{correct_taxkeys} }, { + 'taxkey' => $taxkey, + 'tax' => $form->round_amount(abs($entry->{amount}) * $tax_info->{taxrate}, 2), + 'description' => sprintf("%s %d%%", $tax_info->{taxdescription}, int($tax_info->{taxrate} * 100)), + }; + } + } + } + } + + if (first { $_ > 1 } values %num_entries_per_chart) { + $params{problem}->{type} = 'wrong_taxkeys'; + } else { + $params{problem}->{type} = 'wrong_taxes'; + } + + $params{problem}->{acc_trans} = { %data }; + push @{ $self->{problems} }, $params{problem}; + + $retval = 1; + } + + $main::lxdebug->leave_sub(); + + return $retval; +} + +# 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 +# # iterieren und jeweils die Summe ermitteln. +# my $num_entries = scalar @{ $data{$side}->{entries} }; +# my @taxkey_indices = (0) x $num_entries; + +# my @solutions = (); + +# my $start_time = time(); + +# $main::lxdebug->message(0, "num_entries $num_entries"); + +# while ($num_entries == scalar @taxkey_indices) { +# my @tax_cache = (); + +# # 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]]; +# my $entry = $data{$side}->{entries}->[$i]; +# my $taxinfo = $all_taxes{taxkeys}->{ $taxkey } || { }; +# $tax_cache[$i] = $entry->{amount} * $taxinfo->{taxrate}; +# $tax_sum += $tax_cache[$i]; +# } + +# # Entspricht die Steuersumme mit den aktuell angenommenen Steuerschlüsseln +# # der verbuchten Steuersumme? Wenn ja, dann ist das eine potenzielle +# # Lösung. +# if (abs($tax_sum - $data{$side}->{tax_sum}) < 0.02) { +# push @solutions, { +# 'taxkeys' => [ @potential_taxkeys[@taxkey_indices] ], +# 'taxes' => [ @tax_cache ], +# } +# } + +# # Weiterzählen der Steuerschlüsselindices zum Interieren über +# # alle möglichen Kombinationen. +# my $i = 0; +# while (1) { +# $taxkey_indices[$i]++; +# last if ($taxkey_indices[$i] < scalar @potential_taxkeys); + +# $taxkey_indices[$i] = 0; +# $i++; +# } + +# my $now = time(); +# if (($now - $start_time) >= 5) { +# $main::lxdebug->message(0, " " . join("", @taxkey_indices)); +# $start_time = $now; +# } +# } + +# foreach my $solution (@solutions) { +# $solution->{rows} = []; +# $solution->{changes} = []; +# my $error = 0; + +# foreach my $i (0 .. $num_entries - 1) { +# if ($solution->{taxes}->[$i]) { +# my $tax_rounded = $form->round_amount($solution->{taxes}->[$i] + $error, 2); +# $error = $solution->{taxes}->[$i] + $error - $tax_rounded; +# $solution->{taxes}->[$i] = $tax_rounded; +# } + +# my $entry = $data{$side}->{entries}->[$i]; +# my $tax_entry = $all_taxes{taxkeys}->{ $solution->{taxkeys}->[$i] }; + +# push @{ $solution->{rows} }, { +# %{ $entry }, +# %{ $tax_entry }, +# 'taxamount' => $solution->{taxes}->[$i], +# }; + +# $solution->{rows}->[$i]->{taxdescription} .= ' ' . $form->format_amount(\%myconfig, $tax_entry->{taxrate} * 100) . ' %'; + +# push @{ $solution->{changes} }, { +# 'oid' => $entry->{oid}, +# 'taxkey' => $solution->{taxkeys}->[$i], +# }; +# } + +# push @{ $solution->{rows} }, @{ $data{$other_side}->{entries} }; + +# delete @{ $solution }{ qw(taxes taxkeys) }; +# } + +# $problem->{type} = 'wrong_taxkeys'; +# $problem->{solutions} = [ @solutions ]; +# $problem->{acc_trans} = { %data }; +# push @problems, $problem; + +# next; +# } + +sub analyze { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my @transactions = $self->_fetch_transactions(%params, 'dbh' => $dbh); + + if (!scalar @transactions) { + $main::lxdebug->leave_sub(); + return (); + } + + my $callback = $params{callback} ? '&callback=' . $params{callback} : ''; + + $self->{problems} = []; + $self->{ap_ar_taxkey_problems} = []; + $self->{invoice_inventory_taxkey_problems} = []; + + foreach my $transaction (@transactions) { + my %common_args = $self->_prepare_data('transaction' => $transaction, 'callback' => $callback, 'full_analysis' => $params{full_analysis}); + + next if ($self->_check_trans_ap_ar_wrong_taxkeys(%common_args)); + next if ($self->_check_trans_invoices_inventory_with_taxkeys(%common_args)); + next if ($self->_check_trans_split_multiple_credit_and_debit(%common_args)); + next if ($self->_check_trans_wrong_taxkeys(%common_args)); + } + + my @problems = @{ $self->{problems} }; + + if (0 != scalar @{ $self->{ap_ar_taxkey_problems} }) { + my $problem = { + 'type' => 'ap_ar_wrong_taxkeys', + 'ap_problems' => [ grep { $_->{data}->{module} eq 'ap' } @{ $self->{ap_ar_taxkey_problems} } ], + 'ar_problems' => [ grep { $_->{data}->{module} eq 'ar' } @{ $self->{ap_ar_taxkey_problems} } ], + }; + unshift @problems, $problem; + } + + if (0 != scalar @{ $self->{invoice_inventory_taxkey_problems} }) { + my $problem = { + 'type' => 'invoice_inventory_with_taxkeys', + 'ap_problems' => [ grep { $_->{data}->{module} eq 'ap' } @{ $self->{invoice_inventory_taxkey_problems} } ], + 'ar_problems' => [ grep { $_->{data}->{module} eq 'ar' } @{ $self->{invoice_inventory_taxkey_problems} } ], + }; + unshift @problems, $problem; + } + + $main::lxdebug->leave_sub(); + + + return @problems; +} + +sub fix_ap_ar_wrong_taxkeys { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + 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, + c.link + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (trans_id IN (SELECT id FROM ap WHERE NOT invoice)) + AND (taxkey IN (2, 3, 12, 13)) + + UNION + + SELECT 'ar' AS module, + at.oid, 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|; + + my $sth = prepare_execute_query($form, $dbh, $query); + my @transactions; + + while (my $ref = $sth->fetchrow_hashref()) { + if ((!scalar @transactions) || ($ref->{trans_id} != $transactions[-1]->[0]->{trans_id})) { + push @transactions, []; + } + + push @{ $transactions[-1] }, $ref; + } + + $sth->finish(); + + @transactions = grep { (scalar(@transactions) % 2) == 0 } @transactions; + + my %taxkey_replacements = ( + 2 => 8, + 3 => 9, + 8 => 2, + 9 => 3, + 12 => 18, + 13 => 19, + 18 => 12, + 19 => 13, + ); + + my %bad_taxkeys = ( + 'ap' => { map { $_ => 1 } (2, 3, 12, 13) }, + 'ar' => { map { $_ => 1 } (8, 9, 18, 19) }, + ); + + my @corrections = (); + + foreach my $transaction (@transactions) { + + for (my $i = 0; $i < scalar @{ $transaction }; $i += 2) { + my ($non_tax_idx, $tax_idx) = abs($transaction->[$i]->{amount}) > abs($transaction->[$i + 1]->{amount}) ? ($i, $i + 1) : ($i + 1, $i); + my ($non_tax, $tax) = @{ $transaction }[$non_tax_idx, $tax_idx]; + + last if ($non_tax->{link} =~ m/(:?AP|AR)_tax(:?$|:)/); + last if ($tax->{link} !~ m/(:?AP|AR)_tax(:?$|:)/); + + next if (!$bad_taxkeys{ $non_tax->{module} }->{ $non_tax->{taxkey} }); + + 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}}, + }, + { + 'oid' => $tax->{oid}, + '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}); + } + } + + $h_taxkey_only->finish(); + $h_taxkey_chart_id->finish(); + + $dbh->commit() unless ($params{dbh}); + } + + $main::lxdebug->leave_sub(); +} + +sub fix_invoice_inventory_with_taxkeys { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $query = qq|SELECT at.oid, 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) + WHERE (ar.invoice) + + UNION + + SELECT at.oid, 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|; + + my $sth = prepare_execute_query($form, $dbh, $query); + my @transactions; + + while (my $ref = $sth->fetchrow_hashref()) { + if ((!scalar @transactions) || ($ref->{trans_id} != $transactions[-1]->[0]->{trans_id})) { + push @transactions, []; + } + + push @{ $transactions[-1] }, $ref; + } + + $sth->finish(); + + my @corrections = (); + + foreach my $transaction (@transactions) { + my @sub_transactions = $self->_group_sub_transactions($transaction); + + foreach my $sub_transaction (@sub_transactions) { + my $is_cogs = first { $_->{link} =~ m/IC_cogs/ } @{ $sub_transaction }; + next unless ($is_cogs); + + foreach my $entry (@{ $sub_transaction }) { + next if ($entry->{taxkey} == 0); + push @corrections, $entry->{oid}; + } + } + } + + 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); + } + + $sth->finish(); + + $dbh->commit() unless ($params{dbh}); +# $dbh->rollback(); + } + + $main::lxdebug->leave_sub(); +} + +sub fix_wrong_taxkeys { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(fixes)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + 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 = qq|UPDATE acc_trans SET taxkey = ?, chart_id = ? WHERE oid = ?|; + 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); + + foreach my $fix (@{ $params{fixes} }) { + next unless ($fix->{oid}); + + do_statement($form, $h_taxkey_only, $q_taxkey_only, conv_i($fix->{taxkey}), conv_i($fix->{oid})); + + next unless ($fix->{tax_entry_oid}); + + do_statement($form, $h_transdate, $q_transdate, conv_i($fix->{tax_entry_oid})); + 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} }; + + 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(); + +# $dbh->rollback(); + $dbh->commit() unless ($params{dbh}); + + $main::lxdebug->leave_sub(); +} + +sub delete_transaction { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(trans_id)); + + 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})); + + 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})); + +# $dbh->rollback(); + $dbh->commit() unless ($params{dbh}); + + $main::lxdebug->leave_sub(); +} + +1;