1 package AccTransCorrections;
6 use List::Util qw(first);
18 $self->{taxkeys} = Taxkeys->new();
23 sub _fetch_transactions {
24 $main::lxdebug->enter_sub();
29 my $myconfig = \%main::myconfig;
30 my $form = $main::form;
32 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
34 my (@where, @values) = ((), ());
36 if ($params{transdate_from}) {
37 push @where, qq|at.transdate >= ?|;
38 push @values, $params{transdate_from};
41 if ($params{transdate_to}) {
42 push @where, qq|at.transdate <= ?|;
43 push @values, $params{transdate_to};
46 if ($params{trans_id}) {
47 push @where, qq|at.trans_id = ?|;
48 push @values, $params{trans_id};
53 $where = 'WHERE ' . join(' AND ', map { "($_)" } @where);
58 c.accno, c.description AS chartdescription, c.charttype, c.category AS chartcategory, c.link AS chartlink,
59 COALESCE(gl.reference, COALESCE(ap.invnumber, ar.invnumber)) AS reference,
60 COALESCE(ap.invoice, COALESCE(ar.invoice, FALSE)) AS invoice,
62 WHEN gl.id IS NOT NULL THEN gl.storno AND (gl.storno_id IS NOT NULL)
63 WHEN ap.id IS NOT NULL THEN ap.storno AND (ap.storno_id IS NOT NULL)
64 ELSE ar.storno AND (ar.storno_id IS NOT NULL)
67 WHEN gl.id IS NOT NULL THEN 'gl'
68 WHEN ap.id IS NOT NULL THEN 'ap'
73 LEFT JOIN chart c ON (at.chart_id = c.id)
74 LEFT JOIN gl ON (at.trans_id = gl.id)
75 LEFT JOIN ap ON (at.trans_id = ap.id)
76 LEFT JOIN ar ON (at.trans_id = ar.id)
78 ORDER BY at.trans_id, at.acc_trans_id
81 my @transactions = ();
82 my $last_trans = undef;
84 foreach my $entry (@{ selectall_hashref_query($form, $dbh, $query, @values) }) {
85 if (!$last_trans || ($last_trans->[0]->{trans_id} != $entry->{trans_id})) {
87 push @transactions, $last_trans;
90 push @{ $last_trans }, $entry;
93 $main::lxdebug->leave_sub();
99 $main::lxdebug->enter_sub();
104 my $transaction = $params{transaction};
105 my $callback = $params{callback};
107 my $myconfig = \%main::myconfig;
108 my $form = $main::form;
128 foreach my $entry (@{ $transaction }) {
129 $entry->{chartlinks} = { map { $_ => 1 } split(m/:/, $entry->{chartlink}) };
130 delete $entry->{chartlink};
133 # Verknüpfungen zwischen Steuerschlüsseln und zum Zeitpunkt der Transaktion
134 # gültigen Steuersätze
135 my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $transaction->[0]->{transdate});
137 my ($trans_type, $previous_non_tax_entry);
139 my $first_sub_trans = 1;
141 my $storno_mult = $transaction->[0]->{is_storno} ? -1 : 1;
143 # Aufteilung der Buchungspositionen in Soll-, Habenseite sowie
144 # getrennte Auflistung der Positionen, die auf Steuerkonten gebucht werden.
145 foreach my $entry (@{ $transaction }) {
146 if (!$first_sub_trans && ($entry->{chartlinks}->{AP_paid} || $entry->{chartlinks}->{AR_paid})) {
147 push @{ $data->{payments} }, $entry;
151 my $tax_info = $all_taxes{taxkeys}->{ $entry->{taxkey} };
153 $entry->{taxdescription} = $tax_info->{taxdescription} . ' ' . $form->format_amount($myconfig, $tax_info->{taxrate} * 100) . ' %';
156 if ($entry->{chartlinks}->{AP}) {
158 } elsif ($entry->{chartlinks}->{AR}) {
162 my $idx = 0 < ($entry->{amount} * $storno_mult) ? 'credit' : 'debit';
164 if ($entry->{chartlinks}->{AP_tax} || $entry->{chartlinks}->{AR_tax}) {
165 $data->{$idx}->{tax_sum} += $entry->{amount};
166 push @{ $data->{$idx}->{tax_entries} }, $entry;
168 if ($previous_non_tax_entry) {
169 $previous_non_tax_entry->{tax_entry} = $entry;
170 undef $previous_non_tax_entry;
174 $data->{$idx}->{sum} += $entry->{amount};
175 push @{ $data->{$idx}->{entries} }, $entry;
177 $previous_non_tax_entry = $entry;
180 $sum += $entry->{amount};
182 if (abs($sum) < 0.02) {
184 $first_sub_trans = 0;
188 # Alle Einträge entfernen, die die Gegenkonten zu Zahlungsein- und
189 # -ausgängen darstellen.
190 foreach my $payment (@{ $data->{payments} }) {
191 my $idx = 0 < $payment->{amount} ? 'debit' : 'credit';
193 foreach my $i (0 .. scalar(@{ $data->{$idx}->{entries} }) - 1) {
194 my $entry = $data->{$idx}->{entries}->[$i];
196 next if ((($payment->{amount} * -1) != $entry->{amount}) || ($payment->{transdate} ne $entry->{transdate}));
198 splice @{ $data->{$idx}->{entries} }, $i, 1;
204 delete $data->{payments};
206 map { $data->{$_}->{num} = scalar @{ $data->{$_}->{entries} } } qw(credit debit);
208 my $info = $transaction->[0];
209 my $script = ($info->{module} eq 'ar') && $info->{invoice} ? 'is'
210 : ($info->{module} eq 'ap') && $info->{invoice} ? 'ir'
215 'trans_type' => $trans_type,
216 'all_taxes' => { %all_taxes },
217 'transaction' => $transaction,
218 'full_analysis' => $params{full_analysis},
221 'link' => $script . ".pl?action=edit${callback}&id=" . $info->{trans_id},
225 $main::lxdebug->leave_sub();
230 sub _group_sub_transactions {
231 $main::lxdebug->enter_sub();
234 my $transaction = shift;
236 my @sub_transactions = ();
239 foreach my $i (0 .. scalar(@{ $transaction }) - 1) {
240 my $entry = $transaction->[$i];
242 if (abs($sum) <= 0.01) {
243 push @sub_transactions, [];
246 $sum += $entry->{amount};
248 push @{ $sub_transactions[-1] }, $entry;
251 $main::lxdebug->leave_sub();
253 return @sub_transactions;
256 # Problemfall: Verkaufsrechnungen, bei denen Buchungen auf Warenbestandskonten
257 # mit Steuerschlüssel != 0 durchgeführt wurden. Richtig wäre, dass alle
258 # Steuerschlüssel für solche Warenbestandsbuchungen 0 sind.
259 sub _check_trans_invoices_inventory_with_taxkeys {
260 $main::lxdebug->enter_sub();
265 if (!$params{transaction}->[0]->{invoice}) {
266 $main::lxdebug->leave_sub();
270 my @sub_transactions = $self->_group_sub_transactions($params{transaction});
272 foreach my $sub_transaction (@sub_transactions) {
273 my $is_cogs = first { $_->{chartlinks}->{IC_cogs} } @{ $sub_transaction };
274 next unless ($is_cogs);
276 my $needs_fixing = first { $_->{taxkey} != 0 } @{ $sub_transaction };
277 next unless ($needs_fixing);
279 $params{problem}->{type} = 'invoice_inventory_with_taxkeys';
280 push @{ $self->{invoice_inventory_taxkey_problems} }, $params{problem};
282 $main::lxdebug->leave_sub();
287 $main::lxdebug->leave_sub();
292 # Problemfall: Verkaufsrechnungen, bei denen Steuern verbucht wurden, obwohl
293 # kein Steuerschlüssel eingetragen ist.
294 sub _check_missing_taxkeys_in_invoices {
295 $::lxdebug->enter_sub;
299 my $transaction = $params{transaction};
300 my $found_broken = 0;
302 $::lxdebug->leave_sub and return 0
303 if !$transaction->[0]->{invoice};
305 my @sub_transactions = $self->_group_sub_transactions($transaction);
307 for my $sub_transaction (@sub_transactions) {
308 $::lxdebug->leave_sub and return 0
309 if _is_split_transaction($sub_transaction)
310 || _is_simple_transaction($sub_transaction);
312 my $split_side_entries = _get_splitted_side($sub_transaction);
315 for my $entry (@{ $split_side_entries }) {
316 my $is_tax = grep { m/(?:AP_tax|AR_tax)/ } keys %{ $entry->{chartlinks} };
318 $num_tax_rows++ if $is_tax;
319 $num_taxed_rows++ if !$is_tax && $entry->{tax_key} != 0;
322 # now if this has tax rows but NO taxed rows, something is wrong.
323 if ($num_tax_rows > 0 && $num_taxed_rows == 0) {
324 $params{problem}->{type} = 'missing_taxkeys_in_invoices';
325 push @{ $self->{missing_taxkeys_in_invoices} ||= [] }, $params{problem};
330 $::lxdebug->leave_sub;
332 return $found_broken;
335 # Problemfall: Kreditorenbuchungen, bei denen mit Umsatzsteuerschlüsseln
336 # gebucht wurde und Debitorenbuchungen, bei denen mit Vorsteuerschlüsseln
338 sub _check_trans_ap_ar_wrong_taxkeys {
339 $main::lxdebug->enter_sub();
346 if (!$params{transaction}->[0]->{invoice}
347 && (( ($params{transaction}->[0]->{module} eq 'ap')
348 && (first { my $taxkey = $_->{taxkey}; first { $taxkey == $_ } (2, 3, 12, 13) } @{ $params{transaction} }))
350 ( ($params{transaction}->[0]->{module} eq 'ar')
351 && (first { my $taxkey = $_->{taxkey}; first { $taxkey == $_ } (8, 9, 18, 19) } @{ $params{transaction} })))) {
352 $params{problem}->{type} = 'ap_ar_wrong_taxkeys';
353 push @{ $self->{ap_ar_taxkey_problems} }, $params{problem};
358 $main::lxdebug->leave_sub();
363 # Problemfall: Splitbuchungen, die mehrere Haben- und Sollkonten ansprechen.
364 # Aber nur für Debitoren- und Kreditorenbuchungen, weil das bei Einkaufs- und
365 # Verkaufsrechnungen hingegen völlig normal ist.
366 sub _check_trans_split_multiple_credit_and_debit {
367 $main::lxdebug->enter_sub();
374 if ( !$params{transaction}->[0]->{invoice}
375 && (1 < $params{data}->{credit}->{num})
376 && (1 < $params{data}->{debit}->{num})) {
377 $params{problem}->{type} = 'split_multiple_credit_and_debit';
378 push @{ $self->{problems} }, $params{problem};
383 $main::lxdebug->leave_sub();
388 # Problemfall: Buchungen, bei denen Steuersummen nicht mit den Summen
389 # übereinstimmen, die nach ausgewähltem Steuerschlüssel hätten auftreten müssen.
390 sub _check_trans_wrong_taxkeys {
391 $main::lxdebug->enter_sub();
396 my $form = $main::form;
398 my %data = %{ $params{data} };
399 my $transaction = $params{transaction};
401 if ( $transaction->[0]->{invoice}
402 || $transaction->[0]->{ob_transaction}
403 || $transaction->[0]->{cb_transaction}
404 || (!scalar @{ $data{credit}->{entries} } && !scalar @{ $data{debit}->{entries} })
405 || ( ($transaction->[0]->{module} eq 'gl')
406 && (!scalar @{ $data{credit}->{entries} } || !scalar @{ $data{debit}->{entries} }))) {
407 $main::lxdebug->leave_sub();
413 my ($side, $other_side);
414 if ( (grep { $_->{taxkey} * 1 } @{ $data{credit}->{entries} })
415 || (scalar @{ $data{credit}->{tax_entries} })) {
417 $other_side = 'debit';
419 } elsif ( (grep { $_->{taxkey} * 1 } @{ $data{debit}->{entries} })
420 || (scalar @{ $data{debit}->{tax_entries} })) {
422 $other_side = 'credit';
426 $main::lxdebug->leave_sub();
430 my $expected_tax = 0;
431 my %num_entries_per_chart = ();
432 my $num_taxed_entries = 0;
434 foreach my $entry (@{ $data{$side}->{entries} }) {
435 my $taxinfo = $params{all_taxes}->{taxkeys}->{$entry->{taxkey}} || { };
436 $entry->{expected_tax} = $entry->{amount} * $taxinfo->{taxrate};
437 $expected_tax += $entry->{expected_tax};
439 $num_taxed_entries++ if ($taxinfo->{taxrate} * 1);
441 my $chart_key = $entry->{chart_id} . "-" . $entry->{taxkey};
442 $num_entries_per_chart{$chart_key} ||= 0;
443 $num_entries_per_chart{$chart_key}++;
446 # $main::lxdebug->message(0, "side $side trans_id $transaction->[0]->{trans_id} expected tax $expected_tax actual tax $data{$side}->{tax_sum}");
448 if (abs($expected_tax - $data{$side}->{tax_sum}) >= (0.01 * ($num_taxed_entries + 1))) {
449 if ($params{full_analysis}) {
450 my $storno_mult = $data{$side}->{entries}->[0]->{is_storno} ? -1 : 1;
452 foreach my $entry (@{ $data{$other_side}->{entries} }) {
453 $entry->{display_amount} = $form->round_amount(abs($entry->{amount}) * $storno_mult, 2);
456 foreach my $entry (@{ $data{$side}->{entries} }) {
457 $entry->{actual_tax} = $form->round_amount(abs($entry->{tax_entry} ? $entry->{tax_entry}->{amount} : 0), 2);
458 $entry->{expected_tax} = $form->round_amount(abs($entry->{expected_tax}), 2);
459 $entry->{taxkey_error} = ( $entry->{taxkey} && !$entry->{tax_entry})
460 || (!$entry->{taxkey} && $entry->{tax_entry})
461 || (abs($entry->{expected_tax} - $entry->{actual_tax}) >= 0.02);
462 $entry->{tax_entry_acc_trans_id} = $entry->{tax_entry}->{acc_trans_id};
463 delete $entry->{tax_entry};
465 $entry->{display_amount} = $form->round_amount(abs($entry->{amount}) * $storno_mult, 2);
466 $entry->{display_actual_tax} = $entry->{actual_tax} * $storno_mult;
467 $entry->{display_expected_tax} = $entry->{expected_tax} * $storno_mult;
469 if ($entry->{taxkey_error}) {
470 $self->{negative_taxkey_filter} ||= {
471 'ar' => { map { $_ => 1 } ( 8, 9, 18, 19) },
472 'ap' => { map { $_ => 1 } (1, 2, 3, 12, 13) },
476 $entry->{correct_taxkeys} = [];
478 my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $entry->{transdate});
480 foreach my $taxkey (sort { $a <=> $b } keys %{ $all_taxes{taxkeys} }) {
481 next if ($self->{negative_taxkey_filter}->{ $entry->{module} }->{$taxkey});
483 my $tax_info = $all_taxes{taxkeys}->{$taxkey};
485 next if ((!$tax_info || (0 == $tax_info->{taxrate} * 1)) && $entry->{tax_entry_acc_trans_id});
487 push @{ $entry->{correct_taxkeys} }, {
489 'tax' => $form->round_amount(abs($entry->{amount}) * $tax_info->{taxrate}, 2),
490 'description' => sprintf("%s %d%%", $tax_info->{taxdescription}, int($tax_info->{taxrate} * 100)),
497 if (first { $_ > 1 } values %num_entries_per_chart) {
498 $params{problem}->{type} = 'wrong_taxkeys';
500 $params{problem}->{type} = 'wrong_taxes';
503 $params{problem}->{acc_trans} = { %data };
504 push @{ $self->{problems} }, $params{problem};
509 $main::lxdebug->leave_sub();
514 # Inaktiver Code für das Erraten möglicher Verteilungen von
515 # Steuerschlüsseln. Deaktiviert, weil er exponentiell Zeit
518 # if (abs($expected_tax - $data{$side}->{tax_sum}) >= 0.02) {
519 # my @potential_taxkeys = $trans_type eq 'AP' ? (0, 8, 9) : (0, 1, 2, 3);
521 # $main::lxdebug->dump(0, "pota", \@potential_taxkeys);
523 # # Über alle Kombinationen aus Buchungssätzen und potenziellen Steuerschlüsseln
524 # # iterieren und jeweils die Summe ermitteln.
525 # my $num_entries = scalar @{ $data{$side}->{entries} };
526 # my @taxkey_indices = (0) x $num_entries;
528 # my @solutions = ();
530 # my $start_time = time();
532 # $main::lxdebug->message(0, "num_entries $num_entries");
534 # while ($num_entries == scalar @taxkey_indices) {
535 # my @tax_cache = ();
537 # # Berechnen der Steuersumme für die aktuell angenommenen Steuerschlüssel.
539 # foreach my $i (0 .. $num_entries - 1) {
540 # my $taxkey = $potential_taxkeys[$taxkey_indices[$i]];
541 # my $entry = $data{$side}->{entries}->[$i];
542 # my $taxinfo = $all_taxes{taxkeys}->{ $taxkey } || { };
543 # $tax_cache[$i] = $entry->{amount} * $taxinfo->{taxrate};
544 # $tax_sum += $tax_cache[$i];
547 # # Entspricht die Steuersumme mit den aktuell angenommenen Steuerschlüsseln
548 # # der verbuchten Steuersumme? Wenn ja, dann ist das eine potenzielle
550 # if (abs($tax_sum - $data{$side}->{tax_sum}) < 0.02) {
552 # 'taxkeys' => [ @potential_taxkeys[@taxkey_indices] ],
553 # 'taxes' => [ @tax_cache ],
557 # # Weiterzählen der Steuerschlüsselindices zum Interieren über
558 # # alle möglichen Kombinationen.
561 # $taxkey_indices[$i]++;
562 # last if ($taxkey_indices[$i] < scalar @potential_taxkeys);
564 # $taxkey_indices[$i] = 0;
569 # if (($now - $start_time) >= 5) {
570 # $main::lxdebug->message(0, " " . join("", @taxkey_indices));
571 # $start_time = $now;
575 # foreach my $solution (@solutions) {
576 # $solution->{rows} = [];
577 # $solution->{changes} = [];
580 # foreach my $i (0 .. $num_entries - 1) {
581 # if ($solution->{taxes}->[$i]) {
582 # my $tax_rounded = $form->round_amount($solution->{taxes}->[$i] + $error, 2);
583 # $error = $solution->{taxes}->[$i] + $error - $tax_rounded;
584 # $solution->{taxes}->[$i] = $tax_rounded;
587 # my $entry = $data{$side}->{entries}->[$i];
588 # my $tax_entry = $all_taxes{taxkeys}->{ $solution->{taxkeys}->[$i] };
590 # push @{ $solution->{rows} }, {
593 # 'taxamount' => $solution->{taxes}->[$i],
596 # $solution->{rows}->[$i]->{taxdescription} .= ' ' . $form->format_amount(\%myconfig, $tax_entry->{taxrate} * 100) . ' %';
598 # push @{ $solution->{changes} }, {
599 # 'acc_trans_id' => $entry->{acc_trans_id},
600 # 'taxkey' => $solution->{taxkeys}->[$i],
604 # push @{ $solution->{rows} }, @{ $data{$other_side}->{entries} };
606 # delete @{ $solution }{ qw(taxes taxkeys) };
609 # $problem->{type} = 'wrong_taxkeys';
610 # $problem->{solutions} = [ @solutions ];
611 # $problem->{acc_trans} = { %data };
612 # push @problems, $problem;
618 $main::lxdebug->enter_sub();
623 my $myconfig = \%main::myconfig;
624 my $form = $main::form;
626 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
628 my @transactions = $self->_fetch_transactions(%params, 'dbh' => $dbh);
630 if (!scalar @transactions) {
631 $main::lxdebug->leave_sub();
635 my $callback = $params{callback} ? '&callback=' . $params{callback} : '';
637 $self->{problems} = [];
638 $self->{ap_ar_taxkey_problems} = [];
639 $self->{invoice_inventory_taxkey_problems} = [];
641 foreach my $transaction (@transactions) {
642 my %common_args = $self->_prepare_data('transaction' => $transaction, 'callback' => $callback, 'full_analysis' => $params{full_analysis});
644 next if ($self->_check_trans_ap_ar_wrong_taxkeys(%common_args));
645 next if ($self->_check_trans_invoices_inventory_with_taxkeys(%common_args));
646 next if ($self->_check_trans_split_multiple_credit_and_debit(%common_args));
647 next if ($self->_check_trans_wrong_taxkeys(%common_args));
650 my @problems = @{ $self->{problems} };
652 if (0 != scalar @{ $self->{ap_ar_taxkey_problems} }) {
654 'type' => 'ap_ar_wrong_taxkeys',
655 'ap_problems' => [ grep { $_->{data}->{module} eq 'ap' } @{ $self->{ap_ar_taxkey_problems} } ],
656 'ar_problems' => [ grep { $_->{data}->{module} eq 'ar' } @{ $self->{ap_ar_taxkey_problems} } ],
658 unshift @problems, $problem;
661 if (0 != scalar @{ $self->{invoice_inventory_taxkey_problems} }) {
663 'type' => 'invoice_inventory_with_taxkeys',
664 'ap_problems' => [ grep { $_->{data}->{module} eq 'ap' } @{ $self->{invoice_inventory_taxkey_problems} } ],
665 'ar_problems' => [ grep { $_->{data}->{module} eq 'ar' } @{ $self->{invoice_inventory_taxkey_problems} } ],
667 unshift @problems, $problem;
670 if (0 != scalar @{ $self->{missing_taxkeys_in_invoices} }) {
672 'type' => 'missing_taxkeys_in_invoices',
673 'ap_problems' => [ grep { $_->{data}->{module} eq 'ap' } @{ $self->{missing_taxkeys_in_invoices} } ],
674 'ar_problems' => [ grep { $_->{data}->{module} eq 'ar' } @{ $self->{missing_taxkeys_in_invoices} } ],
676 unshift @problems, $problem;
679 $main::lxdebug->leave_sub();
681 # $::lxdebug->dump(0, 'problems:', \@problems);
686 sub fix_ap_ar_wrong_taxkeys {
687 $main::lxdebug->enter_sub();
692 my $myconfig = \%main::myconfig;
693 my $form = $main::form;
695 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
697 my $query = qq|SELECT 'ap' AS module,
698 at.acc_trans_id, at.trans_id, at.chart_id, at.amount, at.taxkey, at.transdate,
701 LEFT JOIN chart c ON (at.chart_id = c.id)
702 WHERE (trans_id IN (SELECT id FROM ap WHERE NOT invoice))
703 AND (taxkey IN (2, 3, 12, 13))
707 SELECT 'ar' AS module,
708 at.acc_trans_id, at.trans_id, at.chart_id, at.amount, at.taxkey, at.transdate,
711 LEFT JOIN chart c ON (at.chart_id = c.id)
712 WHERE (trans_id IN (SELECT id FROM ar WHERE NOT invoice))
713 AND (taxkey IN (8, 9, 18, 19))
715 ORDER BY trans_id, acc_trans_id|;
717 my $sth = prepare_execute_query($form, $dbh, $query);
720 while (my $ref = $sth->fetchrow_hashref()) {
721 if ((!scalar @transactions) || ($ref->{trans_id} != $transactions[-1]->[0]->{trans_id})) {
722 push @transactions, [];
725 push @{ $transactions[-1] }, $ref;
730 @transactions = grep { (scalar(@transactions) % 2) == 0 } @transactions;
732 my %taxkey_replacements = (
744 'ap' => { map { $_ => 1 } (2, 3, 12, 13) },
745 'ar' => { map { $_ => 1 } (8, 9, 18, 19) },
748 my @corrections = ();
750 foreach my $transaction (@transactions) {
752 for (my $i = 0; $i < scalar @{ $transaction }; $i += 2) {
753 my ($non_tax_idx, $tax_idx) = abs($transaction->[$i]->{amount}) > abs($transaction->[$i + 1]->{amount}) ? ($i, $i + 1) : ($i + 1, $i);
754 my ($non_tax, $tax) = @{ $transaction }[$non_tax_idx, $tax_idx];
756 last if ($non_tax->{link} =~ m/(:?AP|AR)_tax(:?$|:)/);
757 last if ($tax->{link} !~ m/(:?AP|AR)_tax(:?$|:)/);
759 next if (!$bad_taxkeys{ $non_tax->{module} }->{ $non_tax->{taxkey} });
761 my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $non_tax->{transdate});
763 push @corrections, ({ 'acc_trans_id' => $non_tax->{acc_trans_id},
764 'taxkey' => $taxkey_replacements{$non_tax->{taxkey}},
767 'acc_trans_id' => $tax->{acc_trans_id},
768 'taxkey' => $taxkey_replacements{$non_tax->{taxkey}},
769 'chart_id' => $all_taxes{taxkeys}->{ $taxkey_replacements{$non_tax->{taxkey}} }->{taxchart_id},
774 if (scalar @corrections) {
775 my $q_taxkey_only = qq|UPDATE acc_trans SET taxkey = ? WHERE acc_trans_id = ?|;
776 my $h_taxkey_only = prepare_query($form, $dbh, $q_taxkey_only);
778 my $q_taxkey_chart_id = qq|UPDATE acc_trans SET taxkey = ?, chart_id = ? WHERE acc_trans_id = ?|;
779 my $h_taxkey_chart_id = prepare_query($form, $dbh, $q_taxkey_chart_id);
781 foreach my $entry (@corrections) {
782 if ($entry->{chart_id}) {
783 do_statement($form, $h_taxkey_chart_id, $q_taxkey_chart_id, $entry->{taxkey}, $entry->{chart_id}, $entry->{acc_trans_id});
785 do_statement($form, $h_taxkey_only, $q_taxkey_only, $entry->{taxkey}, $entry->{acc_trans_id});
789 $h_taxkey_only->finish();
790 $h_taxkey_chart_id->finish();
792 $dbh->commit() unless ($params{dbh});
795 $main::lxdebug->leave_sub();
798 sub fix_invoice_inventory_with_taxkeys {
799 $main::lxdebug->enter_sub();
804 my $myconfig = \%main::myconfig;
805 my $form = $main::form;
807 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
809 my $query = qq|SELECT at.*, c.link
811 LEFT JOIN ar ON (at.trans_id = ar.id)
812 LEFT JOIN chart c ON (at.chart_id = c.id)
819 LEFT JOIN ap ON (at.trans_id = ap.id)
820 LEFT JOIN chart c ON (at.chart_id = c.id)
823 ORDER BY trans_id, acc_trans_id|;
825 my $sth = prepare_execute_query($form, $dbh, $query);
828 while (my $ref = $sth->fetchrow_hashref()) {
829 if ((!scalar @transactions) || ($ref->{trans_id} != $transactions[-1]->[0]->{trans_id})) {
830 push @transactions, [];
833 push @{ $transactions[-1] }, $ref;
838 my @corrections = ();
840 foreach my $transaction (@transactions) {
841 my @sub_transactions = $self->_group_sub_transactions($transaction);
843 foreach my $sub_transaction (@sub_transactions) {
844 my $is_cogs = first { $_->{link} =~ m/IC_cogs/ } @{ $sub_transaction };
845 next unless ($is_cogs);
847 foreach my $entry (@{ $sub_transaction }) {
848 next if ($entry->{taxkey} == 0);
849 push @corrections, $entry->{acc_trans_id};
855 $query = qq|UPDATE acc_trans SET taxkey = 0 WHERE acc_trans_id = ?|;
856 $sth = prepare_query($form, $dbh, $query);
858 foreach my $acc_trans_id (@corrections) {
859 do_statement($form, $sth, $query, $acc_trans_id);
864 $dbh->commit() unless ($params{dbh});
868 $main::lxdebug->leave_sub();
871 sub fix_wrong_taxkeys {
872 $main::lxdebug->enter_sub();
877 Common::check_params(\%params, qw(fixes));
879 my $myconfig = \%main::myconfig;
880 my $form = $main::form;
882 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
884 my $q_taxkey_only = qq|UPDATE acc_trans SET taxkey = ? WHERE acc_trans_id = ?|;
885 my $h_taxkey_only = prepare_query($form, $dbh, $q_taxkey_only);
887 my $q_taxkey_chart = qq|UPDATE acc_trans SET taxkey = ?, chart_id = ? WHERE acc_trans_id = ?|;
888 my $h_taxkey_chart = prepare_query($form, $dbh, $q_taxkey_chart);
890 my $q_transdate = qq|SELECT transdate FROM acc_trans WHERE acc_trans_id = ?|;
891 my $h_transdate = prepare_query($form, $dbh, $q_transdate);
893 foreach my $fix (@{ $params{fixes} }) {
894 next unless ($fix->{acc_trans_id});
896 do_statement($form, $h_taxkey_only, $q_taxkey_only, conv_i($fix->{taxkey}), conv_i($fix->{acc_trans_id}));
898 next unless ($fix->{tax_entry_acc_trans_id});
900 do_statement($form, $h_transdate, $q_transdate, conv_i($fix->{tax_entry_acc_trans_id}));
901 my ($transdate) = $h_transdate->fetchrow_array();
903 my %all_taxes = $self->{taxkeys}->get_full_tax_info('transdate' => $transdate);
904 my $tax_info = $all_taxes{taxkeys}->{ $fix->{taxkey} };
906 next unless ($tax_info);
908 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}));
911 $h_taxkey_only->finish();
912 $h_taxkey_chart->finish();
913 $h_transdate->finish();
916 $dbh->commit() unless ($params{dbh});
918 $main::lxdebug->leave_sub();
921 sub delete_transaction {
922 $main::lxdebug->enter_sub();
927 Common::check_params(\%params, qw(trans_id));
929 my $myconfig = \%main::myconfig;
930 my $form = $main::form;
932 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
934 do_query($form, $dbh, qq|UPDATE ar SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
935 do_query($form, $dbh, qq|UPDATE ap SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
936 do_query($form, $dbh, qq|UPDATE gl SET storno_id = NULL WHERE storno_id = ?|, conv_i($params{trans_id}));
938 do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, conv_i($params{trans_id}));
939 do_query($form, $dbh, qq|DELETE FROM ap WHERE id = ?|, conv_i($params{trans_id}));
940 do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($params{trans_id}));
941 do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, conv_i($params{trans_id}));
944 $dbh->commit() unless ($params{dbh});
946 $main::lxdebug->leave_sub();