X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=ee2d21f78252815877764987ca08a37feaaca98e;hb=3782a90c336bc6c506f572e607c8526cb5e79ea3;hp=33a29ddd880a3a9712bc5ba7679dd484f391c197;hpb=a68089fb83a276f3ce78e5bd145c1cf05cc9a762;p=kivitendo-erp.git diff --git a/SL/USTVA.pm b/SL/USTVA.pm index 33a29ddd8..ee2d21f78 100644 --- a/SL/USTVA.pm +++ b/SL/USTVA.pm @@ -18,18 +18,23 @@ # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, +# MA 02110-1335, USA. #====================================================================== # Utilities for ustva #===================================================================== package USTVA; +use Carp; +use Data::Dumper; use List::Util qw(first); +use SL::DB; use SL::DBUtils; use SL::DB::Default; use SL::DB::Finanzamt; +use SL::Locale::String qw(t8); use utf8; use strict; @@ -57,9 +62,9 @@ my @tax_office_information = ( fa_steuerberater_street fa_steuerberater_tel fa_voranmeld); my @fiamt_finanzamt = qw( - fa_land_nr fa_bufa_nr fa_name fa_strasse + fa_land_nr fa_bufa_nr fa_name fa_strasse fa_plz fa_ort fa_telefon fa_fax - fa_plz_grosskunden fa_plz_postfach fa_postfach + fa_plz_grosskunden fa_plz_postfach fa_postfach fa_blz_1 fa_kontonummer_1 fa_bankbezeichnung_1 fa_blz_2 fa_kontonummer_2 fa_bankbezeichnung_2 fa_oeffnungszeiten fa_email fa_internet); @@ -128,23 +133,23 @@ sub report_variables { $where_dcp |; - my $dbh = $form->dbconnect($myconfig); - my $sth = $dbh->prepare($query); - - $sth->execute() || $form->dberror($query); - my @positions; - while ( my $row_ref = $sth->fetchrow_arrayref() ) { - push @positions, @$row_ref; # Copy the array contents - } + SL::DB->client->with_transaction(sub { + my $dbh = SL::DB->client->dbh; + my $sth = $dbh->prepare($query); - $sth->finish; + $sth->execute() || $form->dberror($query); - $dbh->disconnect; + while ( my $row_ref = $sth->fetchrow_arrayref() ) { + push @positions, @$row_ref; # Copy the array contents + } - return @positions; + $sth->finish; + 1; + }) or do { die SL::DB->client->error }; + return @positions; } @@ -417,95 +422,12 @@ sub info { $main::lxdebug->leave_sub(); } -# 20.10.2009 sschoeling: this sub seems to be orphaned. -sub stichtag { - $main::lxdebug->enter_sub(); - - # noch nicht fertig - # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch - # den Termin der nächsten USTVA anzeigt. - # - # - my ($today, $FA_dauerfrist, $FA_voranmeld) = @_; - - #$today zerlegen: - - #$today =today * 1; - $today =~ /(\d\d\d\d)(\d\d)(\d\d)/; - my $year = $1; - my $month = $2; - my $day = $3; - my $yy = $year; - my $mm = $month; - my $yymmdd = "$year$month$day" * 1; - my $mmdd = "$month$day" * 1; - my $stichtag = ''; - - #$tage_bis = '1234'; - #$ical = '...vcal format'; - - #if ($FA_voranmeld eq 'month'){ - - my %liste = ( - "0110" => 'December', - "0210" => 'January', - "0310" => 'February', - "0410" => 'March', - "0510" => 'April', - "0610" => 'May', - "0710" => 'June', - "0810" => 'July', - "0910" => 'August', - "1010" => 'September', - "1110" => 'October', - "1210" => 'November', - ); - - #$mm += $dauerfrist - #$month *= 1; - $month += 1 if ($day > 10); - $month = sprintf("%02d", $month); - $stichtag = $year . $month . "10"; - my $ust_va = $month . "10"; - - foreach my $date (%liste) { - $ust_va = $liste{$date} if ($date eq $stichtag); - } - - #} elsif ($FA_voranmeld eq 'quarter'){ - #1; - - #} - - #@stichtag = ('10.04.2004', '10.05.2004'); - - #@liste = ['0110', '0210', '0310', '0410', '0510', '0610', '0710', '0810', '0910', - # '1010', '1110', '1210', ]; - # - #foreach $key (@liste){ - # #if ($ddmm < ('0110' * 1)); - # if ($ddmm ){} - # $stichtag = $liste[$key - 1] if ($ddmm > $key); - # - #} - # - #$stichtag =~ /([\d]\d)(\d\d)$/ - #$stichtag = "$1.$2.$yy" - #$stichtag=$1; - our $description; # most probably not existent. - our $tage_bis; # most probably not existent. - our $ical; # most probably not existent. - - $main::lxdebug->leave_sub(); - return ($stichtag, $description, $tage_bis, $ical); -} - sub query_finanzamt { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr); + my $dbh = SL::DB->client->dbh; #Test, if table finanzamt exist my $table = 'finanzamt'; @@ -516,11 +438,10 @@ sub query_finanzamt { #There is no table, read the table from sql/finanzamt.sql print qq|

Bitte warten, Tabelle $table wird einmalig in Datenbank: $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...

|; - process_query($form, $dbh, $filename) || $self->error(DBI->errstr); - - #execute second last call - my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr); - $dbh->disconnect(); + SL::DB->client->with_transaction(sub { + process_query($form, $dbh, $filename) || $self->error(DBI->errstr); + 1; + }) or do { die SL::DB->client->error }; }; $tst->finish(); @@ -542,11 +463,8 @@ sub query_finanzamt { sub process_query { $main::lxdebug->enter_sub(); - # Copyright D. Simander -> SL::Form under Gnu GPL. my ($form, $dbh, $filename) = @_; - # return unless (-f $filename); - open my $FH, "<", "$filename" or $form->error("$filename : $!\n"); my $query = ""; my $sth; @@ -601,14 +519,16 @@ sub ustva { my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->get_standard_dbh; + my $dbh = SL::DB->client->dbh; my $last_period = 0; my $category = "pos_ustva"; $form->{coa} = $::instance_conf->get_coa; + unless ($form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') { + croak t8("Advance turnover tax return only valid for SKR03 or SKR04"); + } my @category_cent = USTVA->report_variables({ myconfig => $myconfig, form => $form, @@ -616,7 +536,7 @@ sub ustva { attribute => 'position', dec_places => '2', }); - + push @category_cent, ("pos_ustva_811b_kivi", "pos_ustva_861b_kivi"); if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') { push @category_cent, qw(Z43 Z45 Z53 Z54 Z62 Z65 Z67); } @@ -627,7 +547,7 @@ sub ustva { attribute => 'position', dec_places => '0', }); - + push @category_euro, ("pos_ustva_81b_kivi", "pos_ustva_86b_kivi"); @{$form->{category_cent}} = @category_cent; @{$form->{category_euro}} = @category_euro; $form->{decimalplaces} *= 1; @@ -654,7 +574,7 @@ sub ustva { # Germany - if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU'){ + if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') { # 16%/19% Umstellung # Umordnen der Kennziffern @@ -677,7 +597,7 @@ sub ustva { # Fixme: Wird auch noch für Oesterreich gebraucht, # weil kein eigenes Ausgabeformular - # sotte aber aus der allgeméinen Steuerberechnung verschwinden + # sollte aber aus der allgemeinen Steuerberechnung verschwinden # # Berechnung der USTVA Formularfelder laut Bogen 207 # @@ -704,8 +624,6 @@ sub ustva { $form->{"Z65"} = $form->{"Z62"} - $form->{"69"}; $form->{"83"} = $form->{"Z65"} - $form->{"39"}; - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -783,16 +701,16 @@ sub get_accounts_ustva { 1=1 $ARwhere AND acc.trans_id = ac.trans_id - ) - / + ) / ( SELECT amount FROM ar WHERE id = ac.trans_id ) ) AS amount, - tk.pos_ustva + tk.pos_ustva, t.rate, c.accno FROM acc_trans ac LEFT JOIN chart c ON (c.id = ac.chart_id) LEFT JOIN ar ON (ar.id = ac.trans_id) + LEFT JOIN tax t ON (t.id = ac.tax_id) LEFT JOIN taxkeys tk ON ( tk.id = ( SELECT id FROM taxkeys @@ -804,7 +722,7 @@ sub get_accounts_ustva { ) WHERE $acc_trans_where - GROUP BY tk.pos_ustva + GROUP BY tk.pos_ustva, t.rate, c.accno |; } elsif ($form->{accounting_method} eq 'accrual') { @@ -816,10 +734,11 @@ sub get_accounts_ustva { -- Alle Einnahmen AR und pos_ustva erfassen SELECT - sum(ac.amount) AS amount, - tk.pos_ustva + tk.pos_ustva, t.rate, c.accno FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) JOIN ar ON (ar.id = ac.trans_id) + JOIN tax t ON (t.id = ac.tax_id) JOIN taxkeys tk ON ( tk.id = ( SELECT id FROM taxkeys @@ -831,7 +750,7 @@ sub get_accounts_ustva { $dpt_join WHERE 1 = 1 $where - GROUP BY tk.pos_ustva + GROUP BY tk.pos_ustva, t.rate, c.accno |; } else { @@ -849,10 +768,11 @@ sub get_accounts_ustva { SELECT sum(ac.amount) AS amount, - tk.pos_ustva + tk.pos_ustva, t.rate, c.accno FROM acc_trans ac JOIN ap ON (ap.id = ac.trans_id ) JOIN chart c ON (c.id = ac.chart_id) + JOIN tax t ON (t.id = ac.tax_id) LEFT JOIN taxkeys tk ON ( tk.id = ( SELECT id FROM taxkeys @@ -866,16 +786,17 @@ sub get_accounts_ustva { WHERE 1=1 $where - GROUP BY tk.pos_ustva + GROUP BY tk.pos_ustva, t.rate, c.accno UNION -- Einnahmen direkter gl Buchungen erfassen SELECT sum ( - ac.amount) AS amount, - tk.pos_ustva + tk.pos_ustva, t.rate, c.accno FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) JOIN gl a ON (a.id = ac.trans_id) + JOIN tax t ON (t.id = ac.tax_id) LEFT JOIN taxkeys tk ON ( tk.id = ( SELECT id FROM taxkeys @@ -889,17 +810,18 @@ sub get_accounts_ustva { $dpt_join WHERE 1 = 1 $where - GROUP BY tk.pos_ustva + GROUP BY tk.pos_ustva, t.rate, c.accno UNION -- Ausgaben direkter gl Buchungen erfassen SELECT sum (ac.amount) AS amount, - tk.pos_ustva + tk.pos_ustva, t.rate, c.accno FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) JOIN gl a ON (a.id = ac.trans_id) + JOIN tax t ON (t.id = ac.tax_id) LEFT JOIN taxkeys tk ON ( tk.id = ( SELECT id FROM taxkeys @@ -913,14 +835,10 @@ sub get_accounts_ustva { $dpt_join WHERE 1 = 1 $where - GROUP BY tk.pos_ustva + GROUP BY tk.pos_ustva, t.rate, c.accno |; - my @accno; - my $accno; - my $ref; - # Show all $query in Debuglevel LXDebug::QUERY my $callingdetails = (caller (0))[3]; $main::lxdebug->message(LXDebug->QUERY(), "$callingdetails \$query=\n $query"); @@ -928,11 +846,53 @@ sub get_accounts_ustva { my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); + # ugly, but we need to use static accnos + my ($accno_five, $accno_sixteen, $corr); + + if ($form->{coa} eq 'Germany-DATEV-SKR03EU') { + $accno_five = 1773; + $accno_sixteen = 1775; + } elsif (($form->{coa} eq 'Germany-DATEV-SKR04EU')) { + $accno_five = 3803; # SKR04 + $accno_sixteen = 3805; # SKR04 + } else {die "wrong call"; } while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { - # Bug 365 solved?! + next unless $ref->{$category}; + $corr = 0; $ref->{amount} *= -1; - $form->{ $ref->{$category} } += $ref->{amount}; + # USTVA Pos 35 + if ($ref->{pos_ustva} eq '35') { + if ($ref->{rate} == 0.16) { + $form->{"pos_ustva_81b_kivi"} += $ref->{amount}; + } elsif ($ref->{rate} == 0.05) { + $form->{"pos_ustva_86b_kivi"} += $ref->{amount}; + } elsif ($ref->{rate} == 0.19) { + # pos_ustva says 16, but rate says 19 + # (pos_ustva should be tax dependent and not taxkeys dependent) + # correction hotfix for this case: + # bookings exists with 19% -> + # move 19% bookings to the 19% position + # Dont rely on dates of taxkeys + $corr = 1; + $form->{"81"} += $ref->{amount}; + } elsif ($ref->{rate} == 0.07) { + # pos_ustva says 5, but rate says 7 + # see comment above: + # Dont rely on dates of taxkeys + $corr = 1; + $form->{"86"} += $ref->{amount}; + } else {die ("No valid tax rate for pos 35" . Dumper($ref)); } + } + # USTVA Pos 36 (Steuerkonten) + if ($ref->{pos_ustva} eq '36') { + if ($ref->{accno} =~ /^$accno_sixteen/) { + $form->{"pos_ustva_811b_kivi"} += $ref->{amount}; + } elsif ($ref->{accno} =~ /^$accno_five/) { + $form->{"pos_ustva_861b_kivi"} += $ref->{amount}; + } else { die ("No valid accno for pos 36" . Dumper($ref)); } + } + $form->{ $ref->{$category} } += $ref->{amount} unless $corr; } $sth->finish; @@ -1119,7 +1079,7 @@ sub get_oldconfig { # remove any trailing whitespace s/^\s*(.*?)\s*$/$1/; my ($key, $value) = split(/=/, $_, 2); - + $main::lxdebug->message(LXDebug->DEBUG2(), "oldkey: ".$key." val=".$value." newkey=". $oldkeys{$key}." oval=".$::form->{$oldkeys{$key}}); if ( $oldkeys{$key} && $::form->{$oldkeys{$key}} eq '' ) {