X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=81b7f526eb03b1ac53b0db32615a58f7fa7cfae8;hb=fb4d2ffa6aae03b36e4db77be10c3d9dce4413f8;hp=55122a618d247e55a4b7073d976cfa1e748f0949;hpb=b85b47c5656456624239794cf821c824a947a93f;p=kivitendo-erp.git diff --git a/SL/USTVA.pm b/SL/USTVA.pm index 55122a618..81b7f526e 100644 --- a/SL/USTVA.pm +++ b/SL/USTVA.pm @@ -25,6 +25,55 @@ package USTVA; +sub report_variables { + # Get all positions for taxreport out of the database + # Needs Databaseupdate Pg-upgrade2/USTVA_abstraction.pl + + return unless defined wantarray; + + my ( $self, + $arg_ref) = @_; + + my $myconfig = $arg_ref->{myconfig}; + my $form = $arg_ref->{form}; + my $type = $arg_ref->{type}; # 'paied' || 'received' || '' + my $attribute = $arg_ref->{attribute}; # + my $dec_places = (defined $arg_ref->{dec_places}) ? $arg_ref->{dec_places}:undef; + + my $where_type = "AND tax.report_headings.type = '$type'" if ( $type ); + my $where_dcp = "AND tax.report_variables.dec_places = '$dec_places'" if ( defined $dec_places ); + + my $query = qq| + SELECT $attribute + FROM tax.report_variables + LEFT JOIN tax.report_headings + ON (tax.report_variables.heading_id = tax.report_headings.id) + WHERE 1=1 + $where_type + $where_dcp + |; + + $main::lxdebug->message(LXDebug::QUERY, "\$query= \n $query\n"); + + 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 + } + + $sth->finish; + + $dbh->disconnect; + + return @positions; + +} + sub create_steuernummer { $main::lxdebug->enter_sub(); @@ -40,8 +89,8 @@ sub create_steuernummer { my $h = 0; my $i = 0; - $steuernummer_new = $part; - $elstersteuernummer_new = $elster_FFFF; + $steuernummer_new = $part; + $elstersteuernummer_new = $elster_FFFF; $elstersteuernummer_new .= '0'; for ($h = 1; $h < $patterncount; $h++) { @@ -513,6 +562,8 @@ sub process_query { # return unless (-f $filename); + local *FH; + open(FH, "$filename") or $form->error("$filename : $!\n"); my $query = ""; my $sth; @@ -572,18 +623,32 @@ sub ustva { my $last_period = 0; my $category = "pos_ustva"; - 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 @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 - ); + my @category_cent = USTVA->report_variables({ + myconfig => $myconfig, + form => $form, + type => '', + attribute => 'position', + dec_places => '2', + }); + + push @category_cent, qw(83 Z43 Z45 Z53 Z62 Z65 Z67); + + my @category_euro = USTVA->report_variables({ + myconfig => $myconfig, + form => $form, + type => '', + attribute => 'position', + dec_places => '0', + }); + + push @category_euro, USTVA->report_variables({ + myconfig => $myconfig, + form => $form, + type => '', + attribute => 'position', + dec_places => '0', + }); $form->{decimalplaces} *= 1; @@ -593,31 +658,49 @@ sub ustva { foreach $item (@category_euro) { $form->{"$item"} = 0; } + my $coa_name = coa_get($dbh); + $form->{coa} = $coa_name; + + # Controlvariable for templates + $form->{"$coa_name"} = '1'; + + $main::lxdebug->message(LXDebug::DEBUG2, "COA: '$form->{coa}', \$form->{$coa_name} = 1"); &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, $category); + ########################################### + # + # Nationspecific Modfications + # + ########################################### + + # Germany + + if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU'){ + + # 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); + } - # 16%/19% Umstelung - # Umordnen der Kennziffern und abfangen von Fehlern - -# $form->header; -# print $form->{81}; - - 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); } + + # Fixme: Wird auch noch für Oesterreich gebraucht, + # weil kein eigenes Ausgabeformular + # sotte aber aus der allgeméinen Steuerberechnung verschwinden # # Berechnung der USTVA Formularfelder laut Bogen 207 # @@ -649,6 +732,22 @@ sub ustva { $main::lxdebug->leave_sub(); } +sub coa_get { + + my ($dbh) = @_; + + my $query= qq|SELECT coa FROM defaults|; + + my $sth = $dbh->prepare($query); + + $sth->execute || $form->dberror($query); + + ($ref) = $sth->fetchrow_array; + + return $ref; + +}; + sub get_accounts_ustva { $main::lxdebug->enter_sub(); @@ -660,7 +759,7 @@ sub get_accounts_ustva { my $dpt_where; my $dpt_join; my $project; - my $where = "1 = 1"; + my $where = ""; my $glwhere = ""; my $subwhere = ""; my $ARwhere = ""; @@ -668,21 +767,21 @@ sub get_accounts_ustva { 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'"; - $APwhere .= " AND AP.transdate >= '$fromdate'"; + $ARwhere .= " AND acc.transdate >= '$fromdate'"; } + $APwhere .= " AND AP.transdate >= '$fromdate'"; $where .= " AND ac.transdate >= '$fromdate'"; } if ($todate) { $where .= " AND ac.transdate <= '$todate'"; $ARwhere .= " AND acc.transdate <= '$todate'"; - $subwhere .= " AND transdate <= '$todate'"; - $APwhere .= " AND AP.transdate <= '$todate'"; } if ($department_id) { @@ -699,90 +798,63 @@ sub get_accounts_ustva { AND ac.project_id = $form->{project_id} |; } -######################################### -# Method eq 'cash' = IST Versteuerung -######################################### + ############################################ + # Method eq 'cash' = IST Versteuerung + ############################################ + # Betrifft nur die eingenommene Umsatzsteuer + # + ############################################ if ($form->{method} eq 'cash') { $query = qq| - SELECT - -- 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.deliverydate, 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 - - UNION -- alle Ausgaben AP erfassen - - SELECT - sum(ac.amount) AS amount, pos_ustva - FROM acc_trans ac - JOIN AP ON (AP.id = ac.trans_id ) - JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '') - WHERE - 1=1 - $APwhere - $dpt_where - $project - GROUP BY pos_ustva - - UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen - - SELECT sum - ( - CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 - WHEN c.link LIKE '%AP%' THEN ac.amount * 1 - END - ) 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 - $dpt_from - AND NOT (c.link = 'AR' OR c.link = 'AP') - $project - GROUP BY c.$category - |; - - } else { -######################################### -# Method eq 'accrual' = Soll Versteuerung -######################################### + 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.deliverydate,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| @@ -793,22 +865,119 @@ sub get_accounts_ustva { |; } + $query = qq| - SELECT sum - ( - CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 - WHEN c.link LIKE '%AP%' THEN ac.amount * 1 - END - ) 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 - |; + -- 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.deliverydate,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 1=1 + AND chart_id=ac.chart_id + --AND taxkey_id = ac.taxkey + AND startdate <= COALESCE(AP.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + WHERE + 1=1 + $where + $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 NOT $gltaxkey_where + AND startdate <= COALESCE(ac.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + + $dpt_join + WHERE 1 = 1 + $where + $dpt_where + $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 $gltaxkey_where + AND startdate <= COALESCE(ac.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + + $dpt_join + WHERE 1 = 1 + $where + $dpt_where + $project + GROUP BY tk.pos_ustva + + |; my @accno; my $accno; @@ -819,11 +988,12 @@ sub get_accounts_ustva { $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)) { -# Bug 365 solved?! - $ref->{amount} *= -1; + # Bug 365 solved?! + $ref->{amount} *= -1; if ($category eq "pos_bwa") { if ($last_period) { $form->{ $ref->{$category} }{kumm} += $ref->{amount}; @@ -834,11 +1004,51 @@ sub get_accounts_ustva { $form->{ $ref->{$category} } += $ref->{amount}; } } + $sth->finish; $main::lxdebug->leave_sub(); + } +sub get_config { + $main::lxdebug->enter_sub(); + + my ($self, $userpath, $filename) = @_; + + local (*FACONF, *FANEW); + + my $form = $main::form; + + $filename = "$form->{login}_$filename"; + $filename =~ s|.*/||; + $filename = "$userspath/$filename"; + + if (!open(FACONF, "<", $filename)) { + open(FANEW, ">", $filename) || $form->error("$filename : $!"); + close(FANEW); + open(FACONF, "<", $filename) || $form->error("$filename : $!"); + } + + while () { + last if (/^\[/); + next if (/^(\#|\s)/); + + # remove comments + s/\s#.*//g; + + # remove any trailing whitespace + s/^\s*(.*?)\s*$/$1/; + my ($key, $value) = split(/=/, $_, 2); + + $form->{$key} = "$value"; + + } + + close(FACONF); + + $main::lxdebug->leave_sub(); +} 1;