X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=9f1551ed417c50f7ef418f145f7c642cf609fd04;hb=f6124ea6b4ad154625d1fbce3485884e5f2ce82c;hp=f69cd2dd3f20ff22b2c2eed4be66bae5c37542c9;hpb=f12fcb594dd079ca987edca36cd431a3a2ef155e;p=kivitendo-erp.git diff --git a/SL/USTVA.pm b/SL/USTVA.pm index f69cd2dd3..9f1551ed4 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++) { @@ -572,18 +621,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,28 +656,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% 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); } + # 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 # @@ -646,6 +730,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(); @@ -664,7 +764,8 @@ sub get_accounts_ustva { my $APwhere = ''; my $arwhere = ""; my $item; - my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)"; + + my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)"; if ($fromdate) { if ($form->{method} eq 'cash') { @@ -679,8 +780,6 @@ sub get_accounts_ustva { if ($todate) { $where .= " AND ac.transdate <= '$todate'"; $ARwhere .= " AND acc.transdate <= '$todate'"; - $subwhere .= " AND transdate <= '$todate'"; - $APwhere .= " AND AP.transdate <= '$todate'"; } if ($department_id) { @@ -806,20 +905,21 @@ sub get_accounts_ustva { sum(ac.amount) AS amount, tk.pos_ustva FROM acc_trans ac - JOIN AP ON (AP.id = ac.trans_id ) + 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 + 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 - $APwhere + $where $dpt_where $project GROUP BY tk.pos_ustva @@ -836,7 +936,6 @@ sub get_accounts_ustva { 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 @@ -863,7 +962,6 @@ sub get_accounts_ustva { 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 @@ -911,6 +1009,40 @@ sub get_accounts_ustva { } +sub get_config { + $main::lxdebug->enter_sub(); + + my ($self, $userpath, $filename) = @_; + + my $form = $main::form; + + if (!open(FACONF, "$userpath/$form->{login}_$filename")) { + open(FANEW, ">$userpath/$form->{login}_$filename") || + $form->error("$userpath/$filename : $!"); + close(FANEW); + open(FACONF, "$userpath/$form->{login}_$filename") || + $form->error("$userpath/$form->{username}_$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;