X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=3655208743eabde393935f023a380ff8f7964135;hb=6b293028a4c1f27bcb07c665a4f43f5362debf1f;hp=4ac3c1a2c49e75aacebdab1eefadc7b7fe2e80ca;hpb=e8d3bc67310c9326192088ad3ef50c7959caa004;p=kivitendo-erp.git diff --git a/SL/USTVA.pm b/SL/USTVA.pm index 4ac3c1a2c..365520874 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,12 +562,12 @@ sub process_query { # return unless (-f $filename); - open(FH, "$filename") or $form->error("$filename : $!\n"); + open my $FH, "<", "$filename" or $form->error("$filename : $!\n"); my $query = ""; my $sth; my @quote_chars; - while () { + while (<$FH>) { # Remove DOS and Unix style line endings. s/[\r\n]//g; @@ -557,7 +606,7 @@ sub process_query { } } - close FH; + close $FH; $main::lxdebug->leave_sub(); } @@ -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,17 +730,28 @@ 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(); my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_; - my ($null, $department_id) = split /--/, $form->{department}; - my $query; - my $dpt_where; - my $dpt_join; - my $project; my $where = ""; my $glwhere = ""; my $subwhere = ""; @@ -665,37 +760,23 @@ 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) { - $dpt_join = qq| - JOIN department t ON (a.department_id = t.id) - |; - $dpt_where = qq| - AND t.id = $department_id - |; } - if ($form->{project_id}) { - $project = qq| - AND ac.project_id = $form->{project_id} - |; - } ############################################ # Method eq 'cash' = IST Versteuerung ############################################ @@ -738,7 +819,7 @@ sub get_accounts_ustva { SELECT id FROM taxkeys WHERE chart_id = ac.chart_id -- AND taxkey_id = ac.taxkey - AND startdate <= COALESCE(ar.deliverydate, ar.transdate) + AND startdate <= COALESCE(ar.deliverydate,ar.transdate) ORDER BY startdate DESC LIMIT 1 ) ) @@ -754,16 +835,6 @@ sub get_accounts_ustva { # Method eq 'accrual' = Soll Versteuerung ######################################### - if ($department_id) { - $dpt_join = qq| - JOIN dpt_trans t ON (t.trans_id = ac.trans_id) - |; - $dpt_where = qq| - AND t.department_id = $department_id - |; - } - - $query = qq| -- Alle Einnahmen AR und pos_ustva erfassen SELECT @@ -776,15 +847,13 @@ sub get_accounts_ustva { tk.id = ( SELECT id FROM taxkeys WHERE chart_id = ac.chart_id - AND startdate <= COALESCE(ar.deliverydate, ar.transdate) + 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 |; @@ -805,25 +874,24 @@ 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 - $dpt_where - $project + $where GROUP BY tk.pos_ustva - UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen + UNION -- Einnahmen direkter gl Buchungen erfassen SELECT sum ( - ac.amount) AS amount, @@ -835,7 +903,31 @@ 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 + ) + ) + + $dpt_join + WHERE 1 = 1 + $where + 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 ) @@ -844,9 +936,8 @@ sub get_accounts_ustva { $dpt_join WHERE 1 = 1 $where - $dpt_from - $project GROUP BY tk.pos_ustva + |; my @accno; @@ -861,18 +952,10 @@ sub get_accounts_ustva { $sth->execute || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { # Bug 365 solved?! $ref->{amount} *= -1; - if ($category eq "pos_bwa") { - if ($last_period) { - $form->{ $ref->{$category} }{kumm} += $ref->{amount}; - } else { - $form->{ $ref->{$category} }{jetzt} += $ref->{amount}; - } - } else { - $form->{ $ref->{$category} } += $ref->{amount}; - } + $form->{ $ref->{$category} } += $ref->{amount}; } $sth->finish; @@ -881,6 +964,47 @@ sub get_accounts_ustva { } +sub get_config { + $main::lxdebug->enter_sub(); + + my ($self, $userspath, $filename) = @_; + + $form->error("Missing Parameter: @_") if !$userspath || !$filename; + + my $form = $main::form; + + $filename = "$form->{login}_$filename"; + $filename =~ s|.*/||; + $filename = "$userspath/$filename"; + open my $FACONF, "<", $filename or sub {# Annon Sub + # catch open error + # create file if file does not exist + open my $FANEW, ">", $filename or $form->error("CREATE: $filename : $!"); + close $FANEW or $form->error("CLOSE: $filename : $!"); + + #try again open file + open my $FACONF, "<", $filename or $form->error("OPEN: $filename : $!"); + }; + + while (<$FACONF>) { + 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;