X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=d7e2e428e870020f64b41a5129897aebd5d22221;hb=1a344dd59350e494024e81054bc6c752b08c668c;hp=84dc4f3122f246bb9687f730df6a1533e71e2e0f;hpb=081a4f9736f3bc345872be8f61632cbed4a8d9b3;p=kivitendo-erp.git diff --git a/SL/USTVA.pm b/SL/USTVA.pm index 84dc4f312..d7e2e428e 100644 --- a/SL/USTVA.pm +++ b/SL/USTVA.pm @@ -63,38 +63,40 @@ sub create_steuernummer { sub steuernummer_input { $main::lxdebug->enter_sub(); - ($elsterland, $elsterFFFF, $steuernummer) = @_; + my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_; + my $steuernummer_input = ''; + $elster_land = $elsterland; $elster_FFFF = $elsterFFFF; $steuernummer = '0000000000' if ($steuernummer eq ''); # $steuernummer formatieren (nur Zahlen) -> $stnr - $stnr = $steuernummer; + my $stnr = $steuernummer; $stnr =~ s/\D+//g; #Pattern description Elstersteuernummer my %elster_STNRformat = ( - 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3 - 'Hessen' => '0FF BBB UUUUP', # ' ' 3 - 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3 - 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2 - 'Berlin' => 'FF/BBB/UUUUP', # '/' 3 - 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3 - 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3 - 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3 - 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2 - 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3 - 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3 - 'Bremen' => 'FF BBB UUUUP', # ' ' 3 - 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3 - 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4 - 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3 - 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3 + 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3 + 'Hessen' => '0FF BBB UUUUP', # ' ' 3 + 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3 + 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2 + 'Berlin' => 'FF/BBB/UUUUP', # '/' 3 + 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3 + 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3 + 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3 + 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2 + 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3 + 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3 + 'Bremen' => 'FF BBB UUUUP', # ' ' 3 + 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3 + 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4 + 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3 + 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3 ); #split the pattern - $elster_pattern = $elster_STNRformat{$elster_land}; + my $elster_pattern = $elster_STNRformat{$elster_land}; my @elster_pattern = split(' ', $elster_pattern); my $delimiter = ' '; my $patterncount = @elster_pattern; @@ -108,76 +110,77 @@ sub steuernummer_input { # no we have an array of patternparts and a delimiter # create the first automated and fixed part and delimiter - print qq||; - $part = ''; + $steuernummer_input .= qq||; + my $part = ''; SWITCH: { $elster_pattern[0] eq 'FFF' && do { $part = substr($elster_FFFF, 1, 4); - print qq|$part|; + $steuernummer_input .= qq|$part|; last SWITCH; }; $elster_pattern[0] eq '0FF' && do { $part = '0' . substr($elster_FFFF, 2, 4); - print qq|$part|; + $steuernummer_input .= qq|$part|; last SWITCH; }; $elster_pattern[0] eq 'FF' && do { $part = substr($elster_FFFF, 2, 4); - print qq|$part|; + $steuernummer_input .= qq|$part|; last SWITCH; }; 1 == 1 && do { - print qq|Fehler!|; + $steuernummer_input .= qq|Fehler!|; last SWITCH; }; } #now the rest of the Steuernummer ... - print qq||; - print qq|\n + $steuernummer_input .= qq||; + $steuernummer_input .= qq|\n |; - my $h = 0; - my $i = 0; - my $j = 0; - $k = 0; - for ($h = 1; $h < $patterncount; $h++) { - print qq| $delimiter \n|; - for ($i = 1; $i <= length($elster_pattern[$h]); $i++) { - print qq|\n|; + $steuernummer_input .= qq|\n|; } } + $main::lxdebug->leave_sub(); + + return $steuernummer_input; } sub fa_auswahl { $main::lxdebug->enter_sub(); - use SL::Form; +# use SL::Form; # Referenz wird übergeben, hash of hash wird nicht # in neues Hash kopiert, sondern direkt über die Referenz verändert # Prototyp für diese Konstruktion - my ($land, $elsterFFFF, $elster_init) = - @_; #Referenz auf Hash von Hash übergeben + my ($self, $land, $elsterFFFF, $elster_init) = @_; + my $terminal = ''; my $FFFF = $elsterFFFF; my $ffff = ''; @@ -191,8 +194,7 @@ sub fa_auswahl { #} #if ( $terminal eq 'mozilla' or $terminal eq 'js' ) { - print qq| -
+ my $fa_auswahl = qq| @@ -236,18 +238,18 @@ sub fa_auswahl { |; if ($elsterFFFF eq '') { - print qq||; + $fa_auswahl .= qq||; } else { foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} } keys(%elster_land_fa) ) { - print qq| + $fa_auswahl .= qq| |; + $fa_auswahl .= qq|>$elster_land_fa{$ffff} ($ffff)|; } } - print qq| + $fa_auswahl .= qq| |; $main::lxdebug->leave_sub(); + return $fa_auswahl; } sub info { @@ -400,7 +403,8 @@ sub stichtag { sub query_finanzamt { $main::lxdebug->enter_sub(); - my ($myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; + my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr); #Test, if table finanzamt exist @@ -568,55 +572,75 @@ sub ustva { my $last_period = 0; my $category = "pos_ustva"; - my @categories_cent = qw(511 861 36 80 971 931 98 96 53 74 - 85 65 66 61 62 67 63 64 59 69 39 83 - Z43 Z45 Z53 Z62 Z65 Z67); + my @category_cent = qw( + 511 861 36 80 971 931 98 96 53 74 + 85 65 66 61 62 67 63 64 59 69 + 39 83 811 891 Z43 Z45 Z53 Z62 Z65 Z67 + ); - my @categories_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93 - 95 94 42 60 45 52 73 84); + my @category_euro = qw( + 41 44 49 43 48 51 + 86 35 77 76 91 97 + 93 95 94 42 60 45 + 52 73 84 81 89 + ); $form->{decimalplaces} *= 1; - foreach $item (@categories_cent) { + foreach $item (@category_cent) { $form->{"$item"} = 0; } - foreach $item (@categories_euro) { + foreach $item (@category_euro) { $form->{"$item"} = 0; } &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, $category); + + # 16%/19% Umstellung + # Umordnen der Kennziffern + if ( $form->{year} < 2007) { + $form->{35} += $form->{81}; + $form->{36} += $form->{811}; + $form->{95} += $form->{89}; + $form->{98} += $form->{891}; + map { delete $form->{$_} } qw(81 811 89 891); + } else { + $form->{35} += $form->{51}; + $form->{36} += $form->{511}; + $form->{95} += $form->{97}; + $form->{98} += $form->{971}; + map { delete $form->{$_} } qw(51 511 97 971); + } + + # - # Berechnung der USTVA Formularfelder + # Berechnung der USTVA Formularfelder laut Bogen 207 # + $form->{"51r"} = $form->{"511"}; $form->{"86r"} = $form->{"861"}; $form->{"97r"} = $form->{"971"}; $form->{"93r"} = $form->{"931"}; - $form->{"Z43"} = - $form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} + - $form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"}; - $form->{"Z45"} = $form->{"Z43"}; - $form->{"Z53"} = $form->{"Z43"}; - $form->{"Z62"} = - $form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} - - $form->{"63"} - $form->{"64"} - $form->{"59"}; - $form->{"Z65"} = $form->{"Z62"} - $form->{"69"}; - $form->{"83"} = $form->{"Z65"} - $form->{"39"}; - - foreach $item (@categories_cent) { - $form->{$item} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2), - 2, '0'); - } - foreach $item (@categories_euro) { - $form->{$item} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0), - 0, '0'); - } + $form->{"Z43"} = $form->{"511"} + $form->{"811"} + $form->{"861"} + + $form->{"36"} + $form->{"80"} + $form->{"971"} + + $form->{"891"} + $form->{"931"} + $form->{"96"} + + $form->{"98"}; + $form->{"Z45"} = $form->{"Z43"}; + + $form->{"Z53"} = $form->{"Z45"} + $form->{"53"} + $form->{"74"} + + $form->{"85"} + $form->{"65"}; + + $form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"} + - $form->{"62"} - $form->{"67"} - $form->{"63"} + - $form->{"64"} - $form->{"59"}; + + $form->{"Z65"} = $form->{"Z62"} - $form->{"69"}; + $form->{"83"} = $form->{"Z65"} - $form->{"39"}; + $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -633,19 +657,22 @@ sub get_accounts_ustva { my $dpt_where; my $dpt_join; my $project; - my $where = "1 = 1"; + my $where = ""; my $glwhere = ""; my $subwhere = ""; my $ARwhere = ""; + my $APwhere = ''; my $arwhere = ""; my $item; + my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)"; if ($fromdate) { if ($form->{method} eq 'cash') { $subwhere .= " AND transdate >= '$fromdate'"; $glwhere = " AND ac.transdate >= '$fromdate'"; - $ARwhere .= " AND acc.transdate >= '$fromdate'"; + $ARwhere .= " AND acc.transdate >= '$fromdate'"; } + $APwhere .= " AND AP.transdate >= '$fromdate'"; $where .= " AND ac.transdate >= '$fromdate'"; } @@ -653,6 +680,7 @@ sub get_accounts_ustva { $where .= " AND ac.transdate <= '$todate'"; $ARwhere .= " AND acc.transdate <= '$todate'"; $subwhere .= " AND transdate <= '$todate'"; + $APwhere .= " AND AP.transdate <= '$todate'"; } if ($department_id) { @@ -669,132 +697,63 @@ sub get_accounts_ustva { AND ac.project_id = $form->{project_id} |; } + ############################################ + # Method eq 'cash' = IST Versteuerung + ############################################ + # Betrifft nur die eingenommene Umsatzsteuer + # + ############################################ - if ($form->{method} eq 'cash') { + if ($form->{method} eq 'cash') { $query = qq| - - SELECT - SUM( ac.amount * - -- Bezahlt / Rechnungssumme - ( - SELECT SUM(acc.amount) - FROM acc_trans acc - INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%') - WHERE - 1=1 - $ARwhere - AND acc.trans_id = ac.trans_id - ) - / - ( - select amount from ar where id = ac.trans_id - ) - ) AS amount, - c.pos_ustva - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - --JOIN ar ON (ar.id = ac.trans_id) - where - 1=1 - -- Here no where, please. All Transactions ever should be - -- testet if they are paied in the USTVA report period. - GROUP BY c.pos_ustva - UNION - - SELECT sum(ac.amount) AS amount, - c.$category - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - JOIN ap a ON (a.id = ac.trans_id) - $dpt_join - WHERE $where - $dpt_where - AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AP_amount%' - $subwhere - ) - - $project - GROUP BY c.$category - - UNION - - SELECT sum(ac.amount) AS amount, - c.$category - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - JOIN gl a ON (a.id = ac.trans_id) - $dpt_join - WHERE $where - $glwhere - $dpt_from - AND NOT (c.link = 'AR' OR c.link = 'AP') - $project - GROUP BY c.$category - - |; - - if ($form->{project_id}) { - - $query .= qq| - - UNION - - SELECT SUM(ac.sellprice * ac.qty) AS amount, - c.$category - FROM invoice ac - JOIN ar a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.income_accno_id = c.id) - $dpt_join - -- use transdate from subwhere - WHERE 1 = 1 $subwhere - AND c.category = 'I' - $dpt_where - AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AR_paid%' - $subwhere - ) - - $project - GROUP BY c.$category - - UNION - - SELECT SUM(ac.sellprice) AS amount, - c.$category - FROM invoice ac - JOIN ap a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.expense_accno_id = c.id) - $dpt_join - WHERE 1 = 1 $subwhere - AND c.category = 'E' - $dpt_where - AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AP_paid%' - $subwhere - ) - - $project - GROUP BY c.$category - |; - } - - } else { + SELECT + -- USTVA IST-Versteuerung + -- + -- Alle tatsaechlichen _Zahlungseingaenge_ + -- im Voranmeldezeitraum erfassen + -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt) + SUM( ac.amount * + -- Bezahlt / Rechnungssumme + ( + SELECT SUM(acc.amount) + FROM acc_trans acc + INNER JOIN chart c ON (acc.chart_id = c.id + AND c.link like '%AR_paid%') + WHERE + 1=1 + $ARwhere + AND acc.trans_id = ac.trans_id + ) + / + ( + SELECT amount FROM ar WHERE id = ac.trans_id + ) + ) AS amount, + tk.pos_ustva + 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 taxkeys tk ON ( + tk.id = ( + SELECT id FROM taxkeys + WHERE chart_id = ac.chart_id + -- AND taxkey_id = ac.taxkey + AND startdate <= COALESCE(ar.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + WHERE + 1=1 + -- Here no where, please. All Transactions ever should be + -- testet if they are paied in the USTVA report period. + GROUP BY tk.pos_ustva + |; + + } elsif ($form->{method} eq 'accrual') { + ######################################### + # Method eq 'accrual' = Soll Versteuerung + ######################################### if ($department_id) { $dpt_join = qq| @@ -805,70 +764,136 @@ sub get_accounts_ustva { |; } - $query = qq| - - SELECT sum(ac.amount) AS amount, - c.$category - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - $dpt_join - WHERE $where - $dpt_where - $project - GROUP BY c.$category - |; - if ($form->{project_id}) { - - $query .= qq| - - UNION - - SELECT SUM(ac.sellprice * ac.qty) AS amount, - c.$category - FROM invoice ac - JOIN ar a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.income_accno_id = c.id) - $dpt_join - -- use transdate from subwhere - WHERE 1 = 1 $subwhere - AND c.category = 'I' - $dpt_where - $project - GROUP BY c.$category - - UNION - - SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount, - c.$category - FROM invoice ac - JOIN ap a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.expense_accno_id = c.id) - $dpt_join - WHERE 1 = 1 $subwhere - AND c.category = 'E' - $dpt_where - $project - GROUP BY c.$category - |; + $query = qq| + -- Alle Einnahmen AR und pos_ustva erfassen + SELECT + - sum(ac.amount) AS amount, + tk.pos_ustva + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ar ON (ar.id = ac.trans_id) + JOIN taxkeys tk ON ( + tk.id = ( + SELECT id FROM taxkeys + WHERE chart_id = ac.chart_id + AND startdate <= COALESCE(ar.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + $dpt_join + WHERE 1 = 1 + $where + $dpt_where + $project + GROUP BY tk.pos_ustva + |; + + } else { + + $self->error("Unknown tax method: $form->{method}") - } } + + ######################################### + # Ausgaben und Gl Buchungen sind gleich + # für Ist- und Soll-Versteuerung + ######################################### + $query .= qq| + UNION -- alle Ausgaben AP erfassen + + SELECT + sum(ac.amount) AS amount, + tk.pos_ustva + FROM acc_trans ac + JOIN AP ON (AP.id = ac.trans_id ) + JOIN chart c ON (c.id = ac.chart_id) + LEFT JOIN taxkeys tk ON ( + tk.id = ( + SELECT id FROM taxkeys + WHERE chart_id=ac.chart_id + --AND taxkey_id=ac.taxkey + AND startdate <= COALESCE(AP.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + WHERE + 1=1 + $APwhere + $dpt_where + $project + GROUP BY tk.pos_ustva + + UNION -- Einnahmen direkter gl Buchungen erfassen + + SELECT sum + ( - ac.amount) AS amount, + tk.pos_ustva + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gl a ON (a.id = ac.trans_id) + LEFT JOIN taxkeys tk ON ( + tk.id = ( + SELECT id FROM taxkeys + WHERE chart_id=ac.chart_id + --AND taxkey_id=ac.taxkey + AND NOT $gltaxkey_where + AND startdate <= COALESCE(ac.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + + $dpt_join + WHERE 1 = 1 + $where + $dpt_from + $project + GROUP BY tk.pos_ustva + + + UNION -- Ausgaben direkter gl Buchungen erfassen + + SELECT sum + (ac.amount) AS amount, + tk.pos_ustva + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gl a ON (a.id = ac.trans_id) + LEFT JOIN taxkeys tk ON ( + tk.id = ( + SELECT id FROM taxkeys + WHERE chart_id=ac.chart_id + --AND taxkey_id=ac.taxkey + AND $gltaxkey_where + AND startdate <= COALESCE(ac.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + + $dpt_join + WHERE 1 = 1 + $where + $dpt_from + $project + GROUP BY tk.pos_ustva + + |; my @accno; my $accno; my $ref; - #print $query; + # Show all $query in Debuglevel LXDebug::QUERY + $callingdetails = (caller (0))[3]; + $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query"); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - if ($ref->{amount} < 0) { - $ref->{amount} *= -1; - } + # Bug 365 solved?! + $ref->{amount} *= -1; if ($category eq "pos_bwa") { if ($last_period) { $form->{ $ref->{$category} }{kumm} += $ref->{amount}; @@ -879,9 +904,13 @@ sub get_accounts_ustva { $form->{ $ref->{$category} } += $ref->{amount}; } } + $sth->finish; $main::lxdebug->leave_sub(); + } + + 1;