1 # @tag: steuerfilterung
2 # @description: Steuern in Dialogbuchungen filtern.
3 # @depends: release_3_0_0
4 package SL::DBUpgrade2::steuerfilterung;
8 use List::Util qw(first);
10 use parent qw(SL::DBUpgrade2::Base);
18 if ( $::form->{'continued'} ) {
20 foreach my $i (1 .. $::form->{rowcount}) {
21 $tax_id = $::form->{"tax_id_$i"};
23 $categories .= 'A' if $::form->{"asset_$i"};
24 $categories .= 'L' if $::form->{"liability_$i"};
25 $categories .= 'Q' if $::form->{"equity_$i"};
26 $categories .= 'C' if $::form->{"costs_$i"};
27 $categories .= 'I' if $::form->{"revenue_$i"};
28 $categories .= 'E' if $::form->{"expense_$i"};
29 $update_query = qq|UPDATE tax SET chart_categories = '$categories' WHERE id=$tax_id;|;
30 $self->db_query($update_query);
32 $update_query = qq|ALTER TABLE tax ALTER COLUMN chart_categories SET NOT NULL|;
33 $self->db_query($update_query);
38 my $query = qq|ALTER TABLE tax ADD chart_categories TEXT|;
39 $self->db_query($query);
42 my @well_known_taxes = (
43 { taxkey => 0, rate => 0, taxdescription => qr{keine.*steuer}i, categories => 'ALQCIE' },
44 { taxkey => 1, rate => 0, taxdescription => qr{frei}i, categories => 'ALQCIE' },
45 { taxkey => 2, rate => 0.07, taxdescription => qr{umsatzsteuer}i, categories => 'I' },
46 { taxkey => 3, rate => 0.16, taxdescription => qr{umsatzsteuer}i, categories => 'I' },
47 { taxkey => 3, rate => 0.19, taxdescription => qr{umsatzsteuer}i, categories => 'I' },
48 { taxkey => 8, rate => 0.07, taxdescription => qr{vorsteuer}i, categories => 'E' },
49 { taxkey => 9, rate => 0.16, taxdescription => qr{vorsteuer}i, categories => 'E' },
50 { taxkey => 9, rate => 0.19, taxdescription => qr{vorsteuer}i, categories => 'E' },
51 { taxkey => 10, rate => 0, taxdescription => qr{andere.*steuerpflichtige.*lieferung}i, categories => 'I' },
52 { taxkey => 11, rate => 0, taxdescription => qr{frei.*innergem.*mit}i, categories => 'I' },
53 { taxkey => 12, rate => 0.07, taxdescription => qr{steuerpflichtig.*lieferung.*erm}i, categories => 'I' },
54 { taxkey => 13, rate => 0.16, taxdescription => qr{steuerpflichtig.*lieferung.*voll}i, categories => 'I' },
55 { taxkey => 13, rate => 0.19, taxdescription => qr{steuerpflichtig.*lieferung.*voll}i, categories => 'I' },
56 { taxkey => 18, rate => 0.07, taxdescription => qr{innergem.*erwerb.*erm}i, categories => 'E' },
57 { taxkey => 19, rate => 0.16, taxdescription => qr{innergem.*erwerb.*voll}i, categories => 'E' },
58 { taxkey => 19, rate => 0.19, taxdescription => qr{innergem.*erwerb.*voll}i, categories => 'E' },
61 $query = qq|SELECT taxkey, taxdescription, rate, id AS tax_id FROM tax order by taxkey, rate;|;
63 my $sth = $self->dbh->prepare($query);
64 $sth->execute || $::form->dberror($query);
68 $::form->{PARTS} = [];
69 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
70 $well_known_tax = first {
71 ($ref->{taxkey} == $_->{taxkey})
72 && ($ref->{rate} == $_->{rate})
73 && ($ref->{taxdescription} =~ $_->{taxdescription})
75 if ($well_known_tax) {
76 $categories = $well_known_tax->{categories};
77 $tax_id = $ref->{tax_id};
78 $query = qq|UPDATE tax SET chart_categories = '$categories' WHERE id=$tax_id;|;
79 $self->db_query($query);
81 $ref->{rate} = $::form->format_amount(\%::myconfig, $::form->round_amount($ref->{rate} * 100));
82 push @{ $::form->{PARTS} }, $ref;
86 if (scalar @{ $::form->{PARTS} } > 0){
90 $query = qq|ALTER TABLE tax ALTER COLUMN chart_categories SET NOT NULL|;
91 $self->db_query($query);
97 print $::form->parse_html_template("dbupgrade/steuerfilterung");