Revision 1df09f01
Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt
| sql/Pg-upgrade2/steuerfilterung.pl | ||
|---|---|---|
| 5 | 5 |
|
| 6 | 6 |
use strict; |
| 7 | 7 |
use utf8; |
| 8 |
use List::Util qw(first); |
|
| 8 | 9 |
|
| 9 | 10 |
use parent qw(SL::DBUpgrade2::Base); |
| 10 | 11 |
|
| 11 | 12 |
sub run {
|
| 12 | 13 |
my ($self) = @_; |
| 13 | 14 |
|
| 15 |
my $categories; |
|
| 16 |
my $tax_id; |
|
| 17 |
|
|
| 14 | 18 |
if ( $::form->{'continued'} ) {
|
| 15 |
my $update_query = qq|ALTER TABLE tax ADD chart_categories TEXT|; |
|
| 16 |
$self->db_query($update_query); |
|
| 17 |
my $categories; |
|
| 18 |
my $tax_id; |
|
| 19 |
my $update_query; |
|
| 19 | 20 |
foreach my $i (1 .. $::form->{rowcount}) {
|
| 20 | 21 |
$tax_id = $::form->{"tax_id_$i"};
|
| 21 | 22 |
$categories = ''; |
| ... | ... | |
| 25 | 26 |
$categories .= 'C' if $::form->{"costs_$i"};
|
| 26 | 27 |
$categories .= 'I' if $::form->{"revenue_$i"};
|
| 27 | 28 |
$categories .= 'E' if $::form->{"expense_$i"};
|
| 28 |
$update_query = qq|UPDATE tax SET chart_categories = '$categories' WHERE id=$tax_id|; |
|
| 29 |
$update_query = qq|UPDATE tax SET chart_categories = '$categories' WHERE id=$tax_id;|;
|
|
| 29 | 30 |
$self->db_query($update_query); |
| 30 | 31 |
} |
| 31 | 32 |
$update_query = qq|ALTER TABLE tax ALTER COLUMN chart_categories SET NOT NULL|; |
| ... | ... | |
| 34 | 35 |
return 1; |
| 35 | 36 |
} |
| 36 | 37 |
|
| 37 |
my $query = qq|SELECT taxkey, taxdescription, rate, id AS tax_id FROM tax order by taxkey, rate|; |
|
| 38 |
my $query = qq|ALTER TABLE tax ADD chart_categories TEXT|; |
|
| 39 |
$self->db_query($query); |
|
| 40 |
$self->dbh->commit(); |
|
| 41 |
|
|
| 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' },
|
|
| 59 |
); |
|
| 60 |
|
|
| 61 |
$query = qq|SELECT taxkey, taxdescription, rate, id AS tax_id FROM tax order by taxkey, rate;|; |
|
| 38 | 62 |
|
| 39 | 63 |
my $sth = $self->dbh->prepare($query); |
| 40 | 64 |
$sth->execute || $::form->dberror($query); |
| 41 | 65 |
|
| 66 |
my $well_known_tax; |
|
| 67 |
|
|
| 42 | 68 |
$::form->{PARTS} = [];
|
| 43 | 69 |
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
| 44 |
$ref->{rate} = $::form->format_amount(\%::myconfig, $::form->round_amount($ref->{rate} * 100));
|
|
| 45 |
push @{ $::form->{PARTS} }, $ref;
|
|
| 70 |
$well_known_tax = first {
|
|
| 71 |
($ref->{taxkey} == $_->{taxkey})
|
|
| 72 |
&& ($ref->{rate} == $_->{rate})
|
|
| 73 |
&& ($ref->{taxdescription} =~ $_->{taxdescription})
|
|
| 74 |
} @well_known_taxes; |
|
| 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); |
|
| 80 |
} else {
|
|
| 81 |
$ref->{rate} = $::form->format_amount(\%::myconfig, $::form->round_amount($ref->{rate} * 100));
|
|
| 82 |
push @{ $::form->{PARTS} }, $ref;
|
|
| 83 |
} |
|
| 46 | 84 |
} |
| 47 | 85 |
|
| 48 |
&print_message; |
|
| 49 |
return 2; |
|
| 86 |
if (scalar @{ $::form->{PARTS} } > 0){
|
|
| 87 |
&print_message; |
|
| 88 |
return 2; |
|
| 89 |
} else {
|
|
| 90 |
$query = qq|ALTER TABLE tax ALTER COLUMN chart_categories SET NOT NULL|; |
|
| 91 |
$self->db_query($query); |
|
| 92 |
return 1; |
|
| 93 |
} |
|
| 50 | 94 |
} # end run |
| 51 | 95 |
|
| 52 | 96 |
sub print_message {
|
| templates/webpages/dbupgrade/steuerfilterung.html | ||
|---|---|---|
| 31 | 31 |
<td align="right">[% HTML.escape(row.taxkey) %]</td> |
| 32 | 32 |
<td align="left"> [% HTML.escape(row.taxdescription) %]</a></td> |
| 33 | 33 |
<td align="right">[% HTML.escape(row.rate) %] %</td> |
| 34 |
<td align="center">[% IF row.taxkey == 0 or row.taxkey == 1 %] |
|
| 35 |
[% L.checkbox_tag('asset_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]
|
|
| 36 |
[% ELSE %] |
|
| 37 |
[% L.checkbox_tag('asset_' _ loop.count, value => 1, checked => 0, class => 'checkbox') %]
|
|
| 38 |
[% END %]</td> |
|
| 39 |
|
|
| 40 |
<td align="center">[% IF row.taxkey == 0 or row.taxkey == 1 %] |
|
| 41 |
[% L.checkbox_tag('liability_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]
|
|
| 42 |
[% ELSE %] |
|
| 43 |
[% L.checkbox_tag('liability_' _ loop.count, value => 1, checked => 0, class => 'checkbox') %]
|
|
| 44 |
[% END %]</td> |
|
| 45 |
|
|
| 46 |
<td align="center">[% IF row.taxkey == 0 or row.taxkey == 1 %] |
|
| 47 |
[% L.checkbox_tag('equity_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]
|
|
| 48 |
[% ELSE %] |
|
| 49 |
[% L.checkbox_tag('equity_' _ loop.count, value => 1, checked => 0, class => 'checkbox') %]
|
|
| 50 |
[% END %]</td> |
|
| 51 |
|
|
| 52 |
<td align="center">[% IF row.taxkey == 0 or row.taxkey == 1 %] |
|
| 53 |
[% L.checkbox_tag('costs_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]
|
|
| 54 |
[% ELSE %] |
|
| 55 |
[% L.checkbox_tag('costs_' _ loop.count, value => 1, checked => 0, class => 'checkbox') %]
|
|
| 56 |
[% END %]</td> |
|
| 57 |
|
|
| 58 |
<td align="center">[% IF row.taxkey == 8 or row.taxkey == 9 or row.taxkey == 18 or row.taxkey == 19%] |
|
| 59 |
[% L.checkbox_tag('revenue_' _ loop.count, value => 1, checked => 0, class => 'checkbox') %]
|
|
| 60 |
[% ELSE %] |
|
| 61 |
[% L.checkbox_tag('revenue_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]
|
|
| 62 |
[% END %]</td> |
|
| 63 |
|
|
| 64 |
<td align="center">[% IF row.taxkey == 2 or row.taxkey == 3 or row.taxkey == 10 or row.taxkey == 11 or row.taxkey == 12 or row.taxkey == 13 %] |
|
| 65 |
[% L.checkbox_tag('expense_' _ loop.count, value => 1, checked => 0, class => 'checkbox') %]
|
|
| 66 |
[% ELSE %] |
|
| 67 |
[% L.checkbox_tag('expense_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]
|
|
| 68 |
[% END %]</td> |
|
| 34 |
<td align="center">[% L.checkbox_tag('asset_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]</td>
|
|
| 35 |
<td align="center">[% L.checkbox_tag('liability_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]</td>
|
|
| 36 |
<td align="center">[% L.checkbox_tag('equity_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]</td>
|
|
| 37 |
<td align="center">[% L.checkbox_tag('costs_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]</td>
|
|
| 38 |
<td align="center">[% L.checkbox_tag('revenue_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]</td>
|
|
| 39 |
<td align="center">[% L.checkbox_tag('expense_' _ loop.count, value => 1, checked => 1, class => 'checkbox') %]</td>
|
|
| 69 | 40 |
</tr> |
| 70 | 41 |
<input type="hidden" name="tax_id_[% loop.count %]" value="[% row.tax_id %]"> |
| 71 | 42 |
[% SET rowcount = loop.count %] |
Auch abrufbar als: Unified diff
Verbesserung Upgrate
Das Upgrate-Script steuerfilterung.pl ordnet jetzt kivitendo be-
kannten Steuern schon automatisch Kontoarten zu.