X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=6daa0130118c8b055a04d66e6c345c490a2f59e9;hb=3270e88361efc8ceff21314b489732229f0f98f7;hp=84dc4f3122f246bb9687f730df6a1533e71e2e0f;hpb=54e4131e091831e00a861fe2c4f53e344b87ddca;p=kivitendo-erp.git

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 84dc4f312..6daa01301 100644
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -1,5 +1,5 @@
 #=====================================================================
-# Lx-Office ERP
+# kivitendo ERP
 # Copyright (c) 2004 by Udo Spallek, Aachen
 #
 #  Author: Udo Spallek
@@ -25,13 +25,137 @@
 
 package USTVA;
 
+use List::Util qw(first);
+
+use SL::DBUtils;
+
+use utf8;
+use strict;
+
+my @tax_office_information = (
+  { 'id' =>  8, 'name' => 'Baden-Württemberg',      'taxbird_nr' => '0',  'elster_format' => 'FF/BBB/UUUUP',  },
+  { 'id' =>  9, 'name' => 'Bayern',                 'taxbird_nr' => '1',  'elster_format' => 'FFF/BBB/UUUUP', },
+  { 'id' => 11, 'name' => 'Berlin',                 'taxbird_nr' => '2',  'elster_format' => 'FF/BBB/UUUUP',  },
+  { 'id' => 12, 'name' => 'Brandenburg',            'taxbird_nr' => '3',  'elster_format' => 'FFF/BBB/UUUUP', },
+  { 'id' =>  4, 'name' => 'Bremen',                 'taxbird_nr' => '4',  'elster_format' => 'FF BBB UUUUP',  },
+  { 'id' =>  2, 'name' => 'Hamburg',                'taxbird_nr' => '5',  'elster_format' => 'FF/BBB/UUUUP',  },
+  { 'id' =>  6, 'name' => 'Hessen',                 'taxbird_nr' => '6',  'elster_format' => '0FF BBB UUUUP', },
+  { 'id' => 13, 'name' => 'Mecklenburg-Vorpommern', 'taxbird_nr' => '7',  'elster_format' => 'FFF/BBB/UUUUP', },
+  { 'id' =>  3, 'name' => 'Niedersachsen',          'taxbird_nr' => '8',  'elster_format' => 'FF/BBB/UUUUP',  },
+  { 'id' =>  5, 'name' => 'Nordrhein-Westfalen',    'taxbird_nr' => '9',  'elster_format' => 'FFF/BBBB/UUUP', },
+  { 'id' =>  7, 'name' => 'Rheinland-Pfalz',        'taxbird_nr' => '10', 'elster_format' => 'FF/BBB/UUUU/P', },
+  { 'id' => 10, 'name' => 'Saarland',               'taxbird_nr' => '11', 'elster_format' => 'FFF/BBB/UUUUP', },
+  { 'id' => 14, 'name' => 'Sachsen',                'taxbird_nr' => '12', 'elster_format' => 'FFF/BBB/UUUUP', },
+  { 'id' => 15, 'name' => 'Sachsen-Anhalt',         'taxbird_nr' => '13', 'elster_format' => 'FFF/BBB/UUUUP', },
+  { 'id' =>  1, 'name' => 'Schleswig-Holstein',     'taxbird_nr' => '14', 'elster_format' => 'FF BBB UUUUP',  },
+  { 'id' => 16, 'name' => 'Thüringen',              'taxbird_nr' => '15', 'elster_format' => 'FFF/BBB/UUUUP', },
+  );
+
+sub new {
+  my $type = shift;
+
+  my $self = {};
+
+  bless $self, $type;
+
+  $self->_init(@_);
+
+  return $self;
+}
+
+sub _init {
+  my $self = shift;
+
+  $self->{tax_office_information} = [];
+
+  foreach (@tax_office_information) {
+    my $entry      = \%{ $_ };
+    $entry->{name} = $::locale->{iconv_utf8}->convert($entry->{name});
+    push @{ $self->{tax_office_information} }, $entry;
+  }
+}
+
+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 =~ m/^germany/i);
+
+  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 = $type ? "AND tax.report_headings.type = '$type'" : '';
+  my $where_dcp  = defined $dec_places ? "AND tax.report_variables.dec_places = '$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();
 
-  $part           = $form->{part};
-  $patterncount   = $form->{patterncount};
-  $delimiter      = $form->{delimiter};
-  $elster_pattern = $form->{elster_pattern};
+  my $form = $main::form;
+
+  our ($elster_FFFF);
+
+  my $part           = $form->{part};
+  my $patterncount   = $form->{patterncount};
+  my $delimiter      = $form->{delimiter};
+  my $elster_pattern = $form->{elster_pattern};
 
   # rebuild steuernummer and elstersteuernummer
   # es gibt eine gespeicherte steuernummer $form->{steuernummer}
@@ -40,9 +164,9 @@ sub create_steuernummer {
   my $h = 0;
   my $i = 0;
 
-  $steuernummer_new       = $part;
-  $elstersteuernummer_new = $elster_FFFF;
-  $elstersteuernummer_new .= '0';
+  my $steuernummer_new        = $part;
+  my $elstersteuernummer_new  = $elster_FFFF;
+  $elstersteuernummer_new    .= '0';
 
   for ($h = 1; $h < $patterncount; $h++) {
     $steuernummer_new .= qq|$delimiter|;
@@ -63,38 +187,24 @@ sub create_steuernummer {
 sub steuernummer_input {
   $main::lxdebug->enter_sub();
 
-  ($elsterland, $elsterFFFF, $steuernummer) = @_;
+  my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;
+  our ($elster_FFFF, $elster_land);
+
+  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
-  );
 
   #split the pattern
-  $elster_pattern = $elster_STNRformat{$elster_land};
+  my $tax_office     = first { $_->{name} eq $elster_land } @{ $self->{tax_office_information} };
+  my $elster_pattern = $tax_office->{elster_format};
   my @elster_pattern = split(' ', $elster_pattern);
   my $delimiter      = '&nbsp;';
   my $patterncount   = @elster_pattern;
@@ -108,108 +218,102 @@ sub steuernummer_input {
   # no we have an array of patternparts and a delimiter
   # create the first automated and fixed part and delimiter
 
-  print qq|<b><font size="+1">|;
-  $part = '';
+  $steuernummer_input .= qq|<b><font size="+1">|;
+  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|</b></font>|;
-  print qq|\n
+  $steuernummer_input .= qq|</b></font>|;
+  $steuernummer_input .= qq|\n
            <input type=hidden name="elster_pattern" value="$elster_pattern">
            <input type=hidden name="patterncount" value="$patterncount">
            <input type=hidden name="patternlength" value="$patterncount">
            <input type=hidden name="delimiter" value="$delimiter">
            <input type=hidden name="part" value="$part">
   |;
-  my $h = 0;
-  my $i = 0;
-  my $j = 0;
-  $k = 0;
 
-  for ($h = 1; $h < $patterncount; $h++) {
-    print qq|&nbsp;$delimiter&nbsp;\n|;
-    for ($i = 1; $i <= length($elster_pattern[$h]); $i++) {
-      print qq|<select name="part_$h\_$i">\n|;
+  my $k = 0;
 
-      for ($j = 0; $j <= 9; $j++) {
-        print qq|      <option value="$j"|;
+  for (my $h = 1; $h < $patterncount; $h++) {
+    $steuernummer_input .= qq|&nbsp;$delimiter&nbsp;\n|;
+    for (my $i = 1; $i <= length($elster_pattern[$h]); $i++) {
+      $steuernummer_input .= qq|<select name="part_$h\_$i">\n|;
+
+      for (my $j = 0; $j <= 9; $j++) {
+        $steuernummer_input .= qq|      <option value="$j"|;
         if ($steuernummer ne '') {
           if ($j eq substr($stnr, length($part) + $k, 1)) {
-            print qq| selected|;
+            $steuernummer_input .= qq| selected|;
           }
         }
-        print qq|>$j</option>\n|;
+        $steuernummer_input .= qq|>$j</option>\n|;
       }
       $k++;
-      print qq|</select>\n|;
+      $steuernummer_input .= qq|</select>\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
 
-  # 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 ($self, $land, $elsterFFFF, $elster_init) = @_;
 
-  my ($land, $elsterFFFF, $elster_init) =
-    @_;    #Referenz auf Hash von Hash übergeben
   my $terminal = '';
   my $FFFF     = $elsterFFFF;
   my $ffff     = '';
   my $checked  = '';
   $checked = 'checked' if ($elsterFFFF eq '' and $land eq '');
+  my %elster_land_fa;
 
-  #if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
-  #$terminal='mozilla';
-  #} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
-  #$terminal = 'lynx';
-  #}
-
-  #if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
-  print qq|
-        <br>
+  my $fa_auswahl = qq|
         <script language="Javascript">
         function update_auswahl()
         {
                 var elsterBLAuswahl = document.verzeichnis.elsterland_new;
                 var elsterFAAuswahl = document.verzeichnis.elsterFFFF_new;
 
-                elsterFAAuswahl.options.length = 0; // dropdown aufräumen
+                elsterFAAuswahl.options.length = 0; // dropdown aufräumen
                 |;
 
-  foreach $elster_land (sort keys %$elster_init) {
-    print qq|
+  foreach my $elster_land (sort keys %$elster_init) {
+    $fa_auswahl .= qq|
                if (elsterBLAuswahl.options[elsterBLAuswahl.selectedIndex].
                value == "$elster_land")
                {
                |;
     my $j              = 0;
-    my %elster_land_fa = ();
+    %elster_land_fa = ();
     $FFFF = '';
     for $FFFF (keys %{ $elster_init->{$elster_land} }) {
       $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
@@ -217,14 +321,14 @@ sub fa_auswahl {
     foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
                    keys(%elster_land_fa)
       ) {
-      print qq|
+      $fa_auswahl .= qq|
                    elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
       $j++;
     }
-    print qq|
+    $fa_auswahl .= qq|
                }|;
   }
-  print qq|
+  $fa_auswahl .= qq|
         }
         </script>
 
@@ -236,18 +340,18 @@ sub fa_auswahl {
             <td>
               <select size="1" name="elsterland_new" onchange="update_auswahl()">|;
   if ($land eq '') {
-    print qq|<option value="Auswahl" $checked>hier auswählen...</option>\n|;
+    $fa_auswahl .= qq|<option value="Auswahl" $checked>| . $main::locale->text('Select federal state...') . qq|</option>\n|;
   }
-  foreach $elster_land (sort keys %$elster_init) {
-    print qq|
+  foreach my $elster_land (sort keys %$elster_init) {
+    $fa_auswahl .= qq|
                   <option value="$elster_land"|;
     if ($elster_land eq $land and $checked eq '') {
-      print qq| selected|;
+      $fa_auswahl .= qq| selected|;
     }
-    print qq|>$elster_land</option>
+    $fa_auswahl .= qq|>$elster_land</option>
              |;
   }
-  print qq|
+  $fa_auswahl .= qq|
             </td>
           </tr>
           |;
@@ -259,34 +363,36 @@ sub fa_auswahl {
     $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
   }
 
-  print qq|
+  $fa_auswahl .= qq|
            <tr>
               <td>Finanzamt
               </td>
               <td>
                  <select size="1" name="elsterFFFF_new">|;
   if ($elsterFFFF eq '') {
-    print qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
+    $fa_auswahl .= qq|<option value="Auswahl" $checked>| . $main::locale->text('Select tax office...') . qq|</option>|;
   } else {
     foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
                    keys(%elster_land_fa)
       ) {
 
-      print qq|
+      $fa_auswahl .= qq|
                         <option value="$ffff"|;
       if ($ffff eq $elsterFFFF and $checked eq '') {
-        print qq| selected|;
+        $fa_auswahl .= qq| selected|;
       }
-      print qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
+      $fa_auswahl .= qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
     }
   }
-  print qq|
+  $fa_auswahl .= qq|
                  </td>
               </tr>
             </table>
             </select>|;
 
   $main::lxdebug->leave_sub();
+
+  return $fa_auswahl;
 }
 
 sub info {
@@ -303,30 +409,27 @@ sub info {
     <br>
     <br>
     <hr>
-    <input type=button value="zurück" onClick="history.go(-1)">
+    <input type=button value="| . $main::locale->text('Back') . qq|" onClick="history.go(-1)">
     </body>
     |;
 
-    exit;
+    ::end_of_request();
 
   } else {
 
-    if ($form->{error_function}) {
-      &{ $form->{error_function} }($msg);
-    } else {
-      die "Hinweis: $msg\n";
-    }
+    die "Hinweis: $msg\n";
   }
 
   $main::lxdebug->leave_sub();
 }
 
+# 20.10.2009 sschoeling: this sub seems to be orphaned.
 sub stichtag {
   $main::lxdebug->enter_sub();
 
   # noch nicht fertig
-  # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
-  # den Termin der nächsten USTVA anzeigt.
+  # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
+  # den Termin der nächsten USTVA anzeigt.
   #
   #
   my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;
@@ -335,41 +438,43 @@ sub stichtag {
 
   #$today =today * 1;
   $today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
-  $year     = $1;
-  $month    = $2;
-  $day      = $3;
-  $yy       = $year;
-  $mm       = $month;
-  $yymmdd   = "$year$month$day" * 1;
-  $mmdd     = "$month$day" * 1;
-  $stichtag = '';
+  my $year     = $1;
+  my $month    = $2;
+  my $day      = $3;
+  my $yy       = $year;
+  my $mm       = $month;
+  my $yymmdd   = "$year$month$day" * 1;
+  my $mmdd     = "$month$day" * 1;
+  my $stichtag = '';
 
   #$tage_bis = '1234';
   #$ical = '...vcal format';
 
   #if ($FA_voranmeld eq 'month'){
 
-  %liste = ("0110" => 'December',
-            "0210" => 'January',
-            "0310" => 'February',
-            "0410" => 'March',
-            "0510" => 'April',
-            "0610" => 'May',
-            "0710" => 'June',
-            "0810" => 'July',
-            "0910" => 'August',
-            "1010" => 'September',
-            "1110" => 'October',
-            "1210" => 'November');
+  my %liste = (
+    "0110" => 'December',
+    "0210" => 'January',
+    "0310" => 'February',
+    "0410" => 'March',
+    "0510" => 'April',
+    "0610" => 'May',
+    "0710" => 'June',
+    "0810" => 'July',
+    "0910" => 'August',
+    "1010" => 'September',
+    "1110" => 'October',
+    "1210" => 'November',
+  );
 
   #$mm += $dauerfrist
   #$month *= 1;
   $month += 1 if ($day > 10);
   $month    = sprintf("%02d", $month);
   $stichtag = $year . $month . "10";
-  $ust_va   = $month . "10";
+  my $ust_va   = $month . "10";
 
-  foreach $date (%liste) {
+  foreach my $date (%liste) {
     $ust_va = $liste{$date} if ($date eq $stichtag);
   }
 
@@ -393,6 +498,10 @@ sub stichtag {
   #$stichtag =~ /([\d]\d)(\d\d)$/
   #$stichtag = "$1.$2.$yy"
   #$stichtag=$1;
+  our $description; # most probably not existent.
+  our $tage_bis;    # most probably not existent.
+  our $ical;        # most probably not existent.
+
   $main::lxdebug->leave_sub();
   return ($stichtag, $description, $tage_bis, $ical);
 }
@@ -400,7 +509,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
@@ -408,18 +518,16 @@ sub query_finanzamt {
   my $filename = "sql/$table.sql";
 
   my $tst = $dbh->prepare("SELECT * FROM $table");
-  $tst->execute;
-  if ($DBI::err) {
-
+  $tst->execute || do {
     #There is no table, read the table from sql/finanzamt.sql
     print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
-    $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
+    $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
     process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
 
     #execute second last call
     my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
     $dbh->disconnect();
-  }
+  };
   $tst->finish();
 
   #$dbh->disconnect();
@@ -427,7 +535,7 @@ sub query_finanzamt {
   my @vars = (
     'FA_Land_Nr',             #  0
     'FA_BUFA_Nr',             #  1
-                              #'FA_Verteiler',				#  2
+                              #'FA_Verteiler',                             #  2
     'FA_Name',                #  3
     'FA_Strasse',             #  4
     'FA_PLZ',                 #  5
@@ -440,20 +548,20 @@ sub query_finanzamt {
     'FA_BLZ_1',               # 12
     'FA_Kontonummer_1',       # 13
     'FA_Bankbezeichnung_1',   # 14
-                              #'FA_BankIBAN_1',				# 15
-                              #'FA_BankBIC_1',				# 16
-                              #'FA_BankInhaber_BUFA_Nr_1',			# 17
+                              #'FA_BankIBAN_1',                            # 15
+                              #'FA_BankBIC_1',                             # 16
+                              #'FA_BankInhaber_BUFA_Nr_1',                 # 17
     'FA_BLZ_2',               # 18
     'FA_Kontonummer_2',       # 19
     'FA_Bankbezeichnung_2',   # 20
-                              #'FA_BankIBAN_2',				# 21
-                              #'FA_BankBIC_2',				# 22
-                              #'FA_BankInhaber_BUFA_Nr_2',			# 23
+                              #'FA_BankIBAN_2',                            # 21
+                              #'FA_BankBIC_2',                             # 22
+                              #'FA_BankInhaber_BUFA_Nr_2',                 # 23
     'FA_Oeffnungszeiten',     # 24
     'FA_Email',               # 25
     'FA_Internet'             # 26
-                              #'FA_zustaendige_Hauptstelle_BUFA_Nr',		# 27
-                              #'FA_zustaendige_vorgesetzte_Finanzbehoerde'	# 28
+                              #'FA_zustaendige_Hauptstelle_BUFA_Nr',       # 27
+                              #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
   );
 
   my $field = join(', ', @vars);
@@ -463,24 +571,13 @@ sub query_finanzamt {
   $sth->execute || $form->dberror($query);
   my $array_ref = $sth->fetchall_arrayref();
   my $land      = '';
+  my %finanzamt;
   foreach my $row (@$array_ref) {
     my $FA_finanzamt = $row;
-    $land = 'Schleswig Holstein'     if (@$FA_finanzamt[0] eq '1');
-    $land = 'Hamburg'                if (@$FA_finanzamt[0] eq '2');
-    $land = 'Niedersachsen'          if (@$FA_finanzamt[0] eq '3');
-    $land = 'Bremen'                 if (@$FA_finanzamt[0] eq '4');
-    $land = 'Nordrhein Westfalen'    if (@$FA_finanzamt[0] eq '5');
-    $land = 'Hessen'                 if (@$FA_finanzamt[0] eq '6');
-    $land = 'Rheinland Pfalz'        if (@$FA_finanzamt[0] eq '7');
-    $land = 'Baden Würtemberg'       if (@$FA_finanzamt[0] eq '8');
-    $land = 'Bayern'                 if (@$FA_finanzamt[0] eq '9');
-    $land = 'Saarland'               if (@$FA_finanzamt[0] eq '10');
-    $land = 'Berlin'                 if (@$FA_finanzamt[0] eq '11');
-    $land = 'Brandenburg'            if (@$FA_finanzamt[0] eq '12');
-    $land = 'Mecklenburg Vorpommern' if (@$FA_finanzamt[0] eq '13');
-    $land = 'Sachsen'                if (@$FA_finanzamt[0] eq '14');
-    $land = 'Sachsen Anhalt'         if (@$FA_finanzamt[0] eq '15');
-    $land = 'Thüringen'              if (@$FA_finanzamt[0] eq '16');
+    my $tax_office   = first { $_->{id} == $FA_finanzamt->[0] } @{ $self->{tax_office_information} };
+    $land            = $tax_office->{name};
+
+    # $land = $main::locale->{iconv}->convert($land);
 
     my $ffff = @$FA_finanzamt[1];
 
@@ -509,12 +606,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 (<FH>) {
+  while (<$FH>) {
 
     # Remove DOS and Unix style line endings.
     s/[\r\n]//g;
@@ -553,7 +650,7 @@ sub process_query {
     }
   }
 
-  close FH;
+  close $FH;
 
   $main::lxdebug->leave_sub();
 }
@@ -568,320 +665,405 @@ 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 @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_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;
 
-  foreach $item (@categories_cent) {
+  foreach my $item (@category_cent) {
     $form->{"$item"} = 0;
   }
-  foreach $item (@categories_euro) {
+  foreach my $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);
+    }
+
+  }
+
+
+  # Fixme: Wird auch noch für Oesterreich gebraucht,
+  # weil kein eigenes Ausgabeformular
+  # sotte aber aus der allgeméinen Steuerberechnung verschwinden
   #
-  # 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->{"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->{"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->{"Z53"} = $form->{"Z45"}     + $form->{"47"}  + $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();
 }
 
+sub coa_get {
+
+  my ($dbh) = @_;
+  my $form  = $main::form;
+
+  my $query= qq|SELECT coa FROM defaults|;
+
+  my $sth = $dbh->prepare($query);
+
+  $sth->execute || $form->dberror($query);
+
+  my ($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};
+  our ($dpt_join);
 
   my $query;
-  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 = 46) OR (tk.pos_ustva>=59 AND tk.pos_ustva<=66) or (tk.pos_ustva>=89 AND tk.pos_ustva<=93))";
+
   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'";
     $where .= " AND ac.transdate >= '$fromdate'";
   }
 
   if ($todate) {
     $where    .= " AND ac.transdate <= '$todate'";
     $ARwhere  .= " AND acc.transdate <= '$todate'";
-    $subwhere .= " AND 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
+  ############################################
+  # Betrifft nur die eingenommene Umsatzsteuer
+  #
+  ############################################
+
   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%')
+       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 
-        $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 {
+       $acc_trans_where
+       GROUP BY tk.pos_ustva
+    |;
 
-    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
-	      |;
-    }
+  } elsif ($form->{method} eq 'accrual') {
+    #########################################
+    # Method eq 'accrual' = Soll Versteuerung
+    #########################################
 
     $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.deliverydate,ar.transdate)
+           ORDER BY startdate DESC LIMIT 1
+         )
+       )
+       $dpt_join
+       WHERE 1 = 1
+       $where
+       GROUP BY tk.pos_ustva
+  |;
 
-		 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
-		 |;
+  } else {
+
+    $form->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
+       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
+       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
+       GROUP BY tk.pos_ustva
+
+  |;
+
   my @accno;
   my $accno;
   my $ref;
 
-  #print $query;
+  # Show all $query in Debuglevel LXDebug::QUERY
+  my $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;
-    }
-    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};
-    }
+  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+    # Bug 365 solved?!
+    $ref->{amount} *= -1;
+    $form->{ $ref->{$category} } += $ref->{amount};
   }
+
   $sth->finish;
 
   $main::lxdebug->leave_sub();
+
 }
 
+sub get_config {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $userspath, $filename) = @_;
+
+  my $form = $main::form;
+
+  $form->error("Missing Parameter: @_") if !$userspath || !$filename;
+
+  $filename = "$form->{login}_$filename";
+  $filename =~ s|.*/||;
+  $filename = "$userspath/$filename";
+  open my $FACONF, "<", $filename or do {# 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;