X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=b10fea6377d4d8aae149cf0f87bb3ac30291916d;hb=389007ac0512ed52a2a9bf1bf46f680b5fbb7f57;hp=354e13e186d0e503aa8053ffebb2f128e2c71958;hpb=0246e1250d5b6740e6ac04458714a57393bff4f8;p=kivitendo-erp.git diff --git a/SL/USTVA.pm b/SL/USTVA.pm index 354e13e18..b10fea637 100644 --- a/SL/USTVA.pm +++ b/SL/USTVA.pm @@ -25,6 +25,78 @@ package USTVA; +use SL::DBUtils; + +sub get_coa { + + my ( $self, $form, $myconfig) = @_; + + my $query = q{ SELECT coa FROM defaults }; + + my $dbh = $form->dbconnect($myconfig); + my $sth = $dbh->prepare($query); + $sth->execute() || $form->dberror($query); + + my ($coa) = selectrow_query($form, $dbh, $query); + + $sth->finish; + $dbh->disconnect; + + $form->{coa} = $coa; + $form->{"COA_$coa"} = '1'; + $form->{COA_Germany} = '1' if ( $coa eq 'Germany-DATEV-SKR03EU' or $coa eq 'Germany-DATEV-SKR04EU'); + + return; +} + + +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 + |; + + 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 +112,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++) { @@ -314,11 +386,7 @@ sub info { } else { - if ($form->{error_function}) { - &{ $form->{error_function} }($msg); - } else { - die "Hinweis: $msg\n"; - } + die "Hinweis: $msg\n"; } $main::lxdebug->leave_sub(); @@ -513,12 +581,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 +625,7 @@ sub process_query { } } - close FH; + close $FH; $main::lxdebug->leave_sub(); } @@ -572,18 +640,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,9 +675,13 @@ 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'; - $form->{coa} = coa_get($dbh); - $main::lxdebug->message(LXDebug::DEBUG2, "COA: $form->{coa}"); + $main::lxdebug->message(LXDebug::DEBUG2, "COA: '$form->{coa}', \$form->{$coa_name} = 1"); &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, $category); @@ -684,12 +770,7 @@ sub get_accounts_ustva { 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 = ""; @@ -715,20 +796,21 @@ sub get_accounts_ustva { $ARwhere .= " AND acc.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 - |; - } + my $acc_trans_where = '1=1'; + if ($fromdate || $todate) { + $acc_trans_where = "ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE "; - if ($form->{project_id}) { - $project = qq| - AND ac.project_id = $form->{project_id} - |; + if ($fromdate) { + $acc_trans_where .= "transdate >= '$fromdate'"; + } + if ($todate) { + $acc_trans_where .= " AND " if ($fromdate); + $acc_trans_where .= "transdate <= '$todate'"; + } + + $acc_trans_where .= ")"; } + ############################################ # Method eq 'cash' = IST Versteuerung ############################################ @@ -776,9 +858,7 @@ sub get_accounts_ustva { ) ) WHERE - 1=1 - -- Here no where, please. All Transactions ever should be - -- testet if they are paied in the USTVA report period. + $acc_trans_where GROUP BY tk.pos_ustva |; @@ -787,16 +867,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 @@ -816,14 +886,12 @@ sub get_accounts_ustva { $dpt_join WHERE 1 = 1 $where - $dpt_where - $project GROUP BY tk.pos_ustva |; } else { - $self->error("Unknown tax method: $form->{method}") + $form->error("Unknown tax method: $form->{method}") } @@ -843,8 +911,9 @@ sub get_accounts_ustva { 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 ) @@ -852,8 +921,6 @@ sub get_accounts_ustva { WHERE 1=1 $where - $dpt_where - $project GROUP BY tk.pos_ustva UNION -- Einnahmen direkter gl Buchungen erfassen @@ -877,8 +944,6 @@ sub get_accounts_ustva { $dpt_join WHERE 1 = 1 $where - $dpt_from - $project GROUP BY tk.pos_ustva @@ -903,8 +968,6 @@ sub get_accounts_ustva { $dpt_join WHERE 1 = 1 $where - $dpt_from - $project GROUP BY tk.pos_ustva |; @@ -921,18 +984,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; @@ -944,19 +999,26 @@ sub get_accounts_ustva { sub get_config { $main::lxdebug->enter_sub(); - my ($self, $userpath, $filename) = @_; + my ($self, $userspath, $filename) = @_; + + $form->error("Missing Parameter: @_") if !$userspath || !$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 : $!"); - } + $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 () { + while (<$FACONF>) { last if (/^\[/); next if (/^(\#|\s)/); @@ -971,7 +1033,7 @@ sub get_config { } - close(FACONF); + close $FACONF; $main::lxdebug->leave_sub(); }