SL/USTVA.pm

   1 #=====================================================================
   2 # kivitendo ERP
   3 # Copyright (c) 2004 by Udo Spallek, Aachen
   4 #
   5 #  Author: Udo Spallek
   6 #   Email: udono@gmx.net
   7 #     Web: http://www.lx-office.org
   8 #
   9 #
  10 # This program is free software; you can redistribute it and/or modify
  11 # it under the terms of the GNU General Public License as published by
  12 # the Free Software Foundation; either version 2 of the License, or
  13 # (at your option) any later version.
  14 #
  15 # This program is distributed in the hope that it will be useful,
  16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18 # GNU General Public License for more details.
  19 # You should have received a copy of the GNU General Public License
  20 # along with this program; if not, write to the Free Software
  21 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
  22 # MA 02110-1335, USA.
  23 #======================================================================
  24 # Utilities for ustva
  25 #=====================================================================
  26
  27 package USTVA;
  28
  29 use Data::Dumper;
  30 use List::Util qw(first);
  31
  32 use SL::DB;
  33 use SL::DBUtils;
  34 use SL::DB::Default;
  35 use SL::DB::Finanzamt;
  36
  37 use utf8;
  38 use strict;
  39
  40 my @tax_office_information = (
  41   { 'id' =>  8, 'name' => 'Baden-Württemberg',      'taxbird_nr' => '1',  'elster_format' => 'FFBBB/UUUUP',  },
  42   { 'id' =>  9, 'name' => 'Bayern',                 'taxbird_nr' => '2',  'elster_format' => 'FFF/BBB/UUUUP', },
  43   { 'id' => 11, 'name' => 'Berlin',                 'taxbird_nr' => '3',  'elster_format' => 'FF/BBB/UUUUP',  },
  44   { 'id' => 12, 'name' => 'Brandenburg',            'taxbird_nr' => '4',  'elster_format' => 'FFF/BBB/UUUUP', },
  45   { 'id' =>  4, 'name' => 'Bremen',                 'taxbird_nr' => '5',  'elster_format' => 'FF BBB UUUUP',  },
  46   { 'id' =>  2, 'name' => 'Hamburg',                'taxbird_nr' => '6',  'elster_format' => 'FF/BBB/UUUUP',  },
  47   { 'id' =>  6, 'name' => 'Hessen',                 'taxbird_nr' => '7',  'elster_format' => '0FF BBB UUUUP', },
  48   { 'id' => 13, 'name' => 'Mecklenburg-Vorpommern', 'taxbird_nr' => '8',  'elster_format' => 'FFF/BBB/UUUUP', },
  49   { 'id' =>  3, 'name' => 'Niedersachsen',          'taxbird_nr' => '9',  'elster_format' => 'FF/BBB/UUUUP',  },
  50   { 'id' =>  5, 'name' => 'Nordrhein-Westfalen',    'taxbird_nr' => '10', 'elster_format' => 'FFF/BBBB/UUUP', },
  51   { 'id' =>  7, 'name' => 'Rheinland-Pfalz',        'taxbird_nr' => '11', 'elster_format' => 'FF/BBB/UUUUP', },
  52   { 'id' => 10, 'name' => 'Saarland',               'taxbird_nr' => '12', 'elster_format' => 'FFF/BBB/UUUUP', },
  53   { 'id' => 14, 'name' => 'Sachsen',                'taxbird_nr' => '13', 'elster_format' => 'FFF/BBB/UUUUP', },
  54   { 'id' => 15, 'name' => 'Sachsen-Anhalt',         'taxbird_nr' => '14', 'elster_format' => 'FFF/BBB/UUUUP', },
  55   { 'id' =>  1, 'name' => 'Schleswig-Holstein',     'taxbird_nr' => '15', 'elster_format' => 'FF BBB UUUUP',  },
  56   { 'id' => 16, 'name' => 'Thüringen',              'taxbird_nr' => '16', 'elster_format' => 'FFF/BBB/UUUUP', },
  57   );
  58
  59   my @fiamt_config = qw(taxnumber fa_bufa_nr fa_dauerfrist fa_steuerberater_city fa_steuerberater_name
  60   fa_steuerberater_street fa_steuerberater_tel fa_voranmeld);
  61
  62   my @fiamt_finanzamt = qw(
  63     fa_land_nr          fa_bufa_nr            fa_name             fa_strasse
  64     fa_plz              fa_ort                fa_telefon          fa_fax
  65     fa_plz_grosskunden  fa_plz_postfach       fa_postfach
  66     fa_blz_1 fa_kontonummer_1 fa_bankbezeichnung_1
  67     fa_blz_2 fa_kontonummer_2 fa_bankbezeichnung_2 fa_oeffnungszeiten
  68     fa_email fa_internet);
  69
  70
  71 sub new {
  72   my $type = shift;
  73
  74   my $self = {};
  75
  76   bless $self, $type;
  77
  78   $self->_init(@_);
  79
  80   return $self;
  81 }
  82
  83 sub _init {
  84   my $self = shift;
  85
  86   $self->{tax_office_information} = [];
  87
  88   foreach (@tax_office_information) {
  89     my $entry      = \%{ $_ };
  90     $entry->{name} = $::locale->{iconv_utf8}->convert($entry->{name});
  91     push @{ $self->{tax_office_information} }, $entry;
  92   }
  93 }
  94
  95 sub get_coa {
  96
  97   my ( $self, $form ) = @_;
  98
  99   my $coa = $::instance_conf->get_coa;
 100   $form->{coa} = $coa;
 101   $form->{"COA_$coa"} = '1';
 102   $form->{COA_Germany} = '1' if ($coa =~ m/^germany/i);
 103
 104   return;
 105 }
 106
 107
 108 sub report_variables {
 109   # Get all positions for taxreport out of the database
 110   # Needs Databaseupdate Pg-upgrade2/USTVA_abstraction.pl
 111
 112   my ( $self,
 113        $arg_ref) = @_;
 114
 115   my $myconfig   = $arg_ref->{myconfig};
 116   my $form       = $arg_ref->{form};
 117   my $type       = $arg_ref->{type}; # 'paied' || 'received' || ''
 118   my $attribute  = $arg_ref->{attribute}; #
 119   my $dec_places = (defined $arg_ref->{dec_places}) ? $arg_ref->{dec_places}:undef;
 120
 121   my $where_type = $type ? "AND tax.report_headings.type = '$type'" : '';
 122   my $where_dcp  = defined $dec_places ? "AND tax.report_variables.dec_places = '$dec_places'" : '';
 123
 124   my $query = qq|
 125     SELECT $attribute
 126     FROM tax.report_variables
 127     LEFT JOIN tax.report_headings
 128       ON (tax.report_variables.heading_id = tax.report_headings.id)
 129     WHERE 1=1
 130     $where_type
 131     $where_dcp
 132   |;
 133
 134   my @positions;
 135
 136   SL::DB->client->with_transaction(sub {
 137     my $dbh = SL::DB->client->dbh;
 138     my $sth = $dbh->prepare($query);
 139
 140     $sth->execute() || $form->dberror($query);
 141
 142     while ( my $row_ref = $sth->fetchrow_arrayref() ) {
 143       push @positions, @$row_ref;  # Copy the array contents
 144     }
 145
 146     $sth->finish;
 147     1;
 148   }) or do { die SL::DB->client->error };
 149
 150   return @positions;
 151 }
 152
 153
 154 sub steuernummer_input {
 155   $main::lxdebug->enter_sub();
 156
 157   my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;
 158   our ($elster_FFFF, $elster_land);
 159
 160   my $steuernummer_input = '';
 161
 162   $elster_land  = $elsterland;
 163   $elster_FFFF  = $elsterFFFF;
 164   $steuernummer = '0000000000' if ($steuernummer eq '');
 165
 166   # $steuernummer formatieren (nur Zahlen) -> $stnr
 167   my $stnr = $steuernummer;
 168   $stnr =~ s/\D+//g;
 169
 170   #Pattern description Elstersteuernummer
 171
 172   #split the pattern
 173   my $tax_office     = first { $_->{id} eq $elster_land } @{ $self->{tax_office_information} };
 174   my $elster_pattern = $tax_office->{elster_format};
 175  # $::lxdebug->message(LXDebug->DEBUG2, "stnr=".$stnr." elster_FFFF=".$elster_FFFF.
 176  #                     " pattern=".$elster_pattern." land=".$elster_land);
 177   my @elster_pattern = split(' ', $elster_pattern);
 178   my $delimiter1      = '&nbsp;';
 179   my $delimiter2      = '&nbsp;';
 180   my $patterncount   = @elster_pattern;
 181   if ($patterncount < 2) {
 182     @elster_pattern = ();
 183     @elster_pattern = split('/', $elster_pattern);
 184     $delimiter1      = '/';
 185     $delimiter2      = '/';
 186     $patterncount   = @elster_pattern;
 187     if ($patterncount < 2) {
 188         @elster_pattern = ();
 189         @elster_pattern = split(' ', $elster_pattern);
 190         $delimiter1      = ' ';
 191         $delimiter2      = ' ';
 192         $patterncount   = @elster_pattern;
 193     }
 194   }
 195
 196   # no we have an array of patternparts and a delimiter
 197   # create the first automated and fixed part and delimiter
 198
 199   $steuernummer_input .= qq|<b><font size="+1">|;
 200   my $part = '';
 201 #  $::lxdebug->message(LXDebug->DEBUG2, "pattern0=".$elster_pattern[0]);
 202 SWITCH: {
 203     $elster_pattern[0] eq 'FFF' && do {
 204       $part = substr($elster_FFFF, 1, 4);
 205       $steuernummer_input .= qq|$part|;
 206       last SWITCH;
 207     };
 208     $elster_pattern[0] eq '0FF' && do {
 209       $part = '0' . substr($elster_FFFF, 2, 4);
 210       $steuernummer_input .= qq|$part|;
 211       last SWITCH;
 212     };
 213     $elster_pattern[0] eq 'FFBBB' && do {
 214       $part = substr($elster_FFFF, 2, 4);
 215       $steuernummer_input .= qq|$part|;
 216       $delimiter1 = '';
 217       $patterncount++ ;
 218       # Sonderfall BW
 219       @elster_pattern = ('FF','BBB','UUUUP');
 220       last SWITCH;
 221     };
 222     $elster_pattern[0] eq 'FF' && do {
 223       $part = substr($elster_FFFF, 2, 4);
 224       $steuernummer_input .= qq|$part|;
 225       last SWITCH;
 226     };
 227     1 == 1 && do {
 228       $steuernummer_input .= qq|Fehler!|;
 229       last SWITCH;
 230     };
 231   }
 232
 233   #now the rest of the Steuernummer ...
 234   $steuernummer_input .= qq|</font></b>|;
 235   $steuernummer_input .= qq|\n
 236            <input type=hidden name="elster_pattern" value="$elster_pattern">
 237            <input type=hidden name="patterncount" value="$patterncount">
 238            <input type=hidden name="patternlength" value="$patterncount">
 239            <input type=hidden name="delimiter1" value="$delimiter1">
 240            <input type=hidden name="delimiter2" value="$delimiter2">
 241            <input type=hidden name="part" value="$part">
 242   |;
 243
 244   my $k = 0;
 245
 246   for (my $h = 1; $h < $patterncount; $h++) {
 247     my $delimiter = ( $h==1?$delimiter1:$delimiter2);
 248     $steuernummer_input .= qq|&nbsp;$delimiter&nbsp;\n|;
 249 #  $::lxdebug->message(LXDebug->DEBUG2, "pattern[$h]=".$elster_pattern[$h]);
 250     for (my $i = 1; $i <= length($elster_pattern[$h]); $i++) {
 251       $steuernummer_input .= qq|<select name="part_$h\_$i">\n|;
 252
 253       for (my $j = 0; $j <= 9; $j++) {
 254         $steuernummer_input .= qq|      <option value="$j"|;
 255         if ($steuernummer ne '') {
 256           if ($j eq substr($stnr, length($part) + $k, 1)) {
 257             $steuernummer_input .= qq| selected|;
 258           }
 259         }
 260         $steuernummer_input .= qq|>$j</option>\n|;
 261       }
 262       $k++;
 263       $steuernummer_input .= qq|</select>\n|;
 264     }
 265   }
 266
 267   $main::lxdebug->leave_sub();
 268
 269   return $steuernummer_input;
 270 }
 271
 272 sub fa_auswahl {
 273   $main::lxdebug->enter_sub();
 274
 275   # Referenz wird übergeben, hash of hash wird nicht
 276   # in neues  Hash kopiert, sondern direkt über die Referenz verändert
 277   # Prototyp für diese Konstruktion
 278
 279   my ($self, $land, $elsterFFFF, $elster_init) = @_;
 280
 281 #  $::lxdebug->message(LXDebug->DEBUG2,"land=".$land." amt=".$elsterFFFF);
 282   my $terminal = '';
 283   my $FFFF     = $elsterFFFF;
 284   my $ffff     = '';
 285   my $checked  = '';
 286   $checked = 'checked' if ($elsterFFFF eq '' and $land eq '');
 287   my %elster_land_fa;
 288   my %elster_land_name = ();
 289
 290   my $fa_auswahl = qq|
 291         <script language="Javascript">
 292         function update_auswahl()
 293         {
 294                 var elsterBLAuswahl = document.verzeichnis.fa_land_nr_new;
 295                 var elsterFAAuswahl = document.verzeichnis.fa_bufa_nr_new;
 296
 297                 elsterFAAuswahl.options.length = 0; // dropdown aufräumen
 298                 |;
 299
 300   foreach my $elster_land (sort keys %$elster_init) {
 301     $fa_auswahl .= qq|
 302                if (elsterBLAuswahl.options[elsterBLAuswahl.selectedIndex].value == "$elster_land")
 303                {
 304                |;
 305     my $j              = 0;
 306     %elster_land_fa = ();
 307     $FFFF = '';
 308     for $FFFF (keys %{ $elster_init->{$elster_land} }) {
 309         if ( $FFFF eq 'name' ) {
 310             $elster_land_name{$elster_land} = $elster_init->{$elster_land}{$FFFF};
 311             delete $elster_init->{$elster_land}{$FFFF};
 312         } else {
 313             $elster_land_fa{$FFFF} = $elster_init->{$elster_land}{$FFFF}->fa_name;
 314        }
 315     }
 316     foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
 317                    keys(%elster_land_fa)
 318       ) {
 319       $fa_auswahl .= qq|
 320                    elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
 321       $j++;
 322     }
 323     $fa_auswahl .= qq|
 324                }|;
 325   }
 326   $fa_auswahl .= qq|
 327         }
 328         </script>
 329
 330         <table width="100%">
 331           <tr>
 332             <td>
 333                Bundesland
 334             </td>
 335             <td>
 336               <select size="1" name="fa_land_nr_new" onchange="update_auswahl()">|;
 337   if ($land eq '') {
 338     $fa_auswahl .= qq|<option value="Auswahl" $checked>| . $main::locale->text('Select federal state...') . qq|</option>\n|;
 339   }
 340   foreach my $elster_land (sort keys %$elster_init) {
 341     $fa_auswahl .= qq|
 342                   <option value="$elster_land"|;
 343 #  $::lxdebug->message(LXDebug->DEBUG2,"land=".$land." elster_land=".$elster_land." lname=".$elster_land_name{$elster_land});
 344     if ($elster_land eq $land and $checked eq '') {
 345       $fa_auswahl .= qq| selected|;
 346     }
 347     $fa_auswahl .= qq|>$elster_land_name{$elster_land}</option>
 348              |;
 349   }
 350   $fa_auswahl .= qq|
 351               </select>
 352             </td>
 353           </tr>
 354           |;
 355
 356   my $elster_land = '';
 357   $elster_land = ($land ne '') ? $land : '';
 358   %elster_land_fa = ();
 359   for $FFFF (keys %{ $elster_init->{$elster_land} }) {
 360     $elster_land_fa{$FFFF} = $elster_init->{$elster_land}{$FFFF}->fa_name;
 361   }
 362
 363   $fa_auswahl .= qq|
 364            <tr>
 365               <td>Finanzamt
 366               </td>
 367               <td>
 368                  <select size="1" name="fa_bufa_nr_new">|;
 369   if ($elsterFFFF eq '') {
 370     $fa_auswahl .= qq|<option value="Auswahl" $checked>| . $main::locale->text('Select tax office...') . qq|</option>|;
 371   } else {
 372     foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
 373                    keys(%elster_land_fa)
 374       ) {
 375
 376       $fa_auswahl .= qq|
 377                         <option value="$ffff"|;
 378       if ($ffff eq $elsterFFFF and $checked eq '') {
 379         $fa_auswahl .= qq| selected|;
 380       }
 381       $fa_auswahl .= qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
 382     }
 383   }
 384   $fa_auswahl .= qq|
 385                  </select>
 386               </td>
 387           </tr>
 388         </table>|;
 389
 390   $main::lxdebug->leave_sub();
 391
 392   return $fa_auswahl;
 393 }
 394
 395 sub info {
 396   $main::lxdebug->enter_sub();
 397
 398   my $msg = $_[0];
 399
 400   if ($ENV{HTTP_USER_AGENT}) {
 401     $msg =~ s/\n/<br>/g;
 402
 403     print qq|<body><h2 class=info>Hinweis</h2>
 404
 405     <p><b>$msg</b>
 406     <br>
 407     <br>
 408     <hr>
 409     <input type=button value="| . $main::locale->text('Back') . qq|" onClick="history.go(-1)">
 410     </body>
 411     |;
 412
 413     $::dispatcher->end_request;
 414
 415   } else {
 416
 417     die "Hinweis: $msg\n";
 418   }
 419
 420   $main::lxdebug->leave_sub();
 421 }
 422
 423 sub query_finanzamt {
 424   $main::lxdebug->enter_sub();
 425
 426   my ($self, $myconfig, $form) = @_;
 427
 428   my $dbh = SL::DB->client->dbh;
 429
 430   #Test, if table finanzamt exist
 431   my $table    = 'finanzamt';
 432   my $filename = "sql/$table.sql";
 433
 434   my $tst = $dbh->prepare("SELECT * FROM $table");
 435   $tst->execute || do {
 436     #There is no table, read the table from sql/finanzamt.sql
 437     print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
 438     $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
 439     SL::DB->client->with_transaction(sub {
 440       process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
 441       1;
 442     }) or do { die SL::DB->client->error };
 443   };
 444   $tst->finish();
 445
 446
 447   my $fiamt =  SL::DB::Finanzamt->_get_manager_class->get_all(sort => 'fa_land_nr');
 448   my $land      = 0;
 449   my %finanzamt;
 450   foreach my $row (@$fiamt) {
 451     my $tax_office   = first { $_->{id} == $row->fa_land_nr } @{ $self->{tax_office_information} };
 452     $land            = $tax_office->{id};
 453     $finanzamt{$land}{$row->fa_bufa_nr}  = $row;
 454     $finanzamt{$land}{'name'} ||= $tax_office->{name};
 455   }
 456   $main::lxdebug->leave_sub();
 457
 458   return \%finanzamt;
 459 }
 460
 461 sub process_query {
 462   $main::lxdebug->enter_sub();
 463
 464   my ($form, $dbh, $filename) = @_;
 465
 466   open my $FH, "<", "$filename" or $form->error("$filename : $!\n");
 467   my $query = "";
 468   my $sth;
 469   my @quote_chars;
 470
 471   while (<$FH>) {
 472
 473     # Remove DOS and Unix style line endings.
 474     s/[\r\n]//g;
 475
 476     # don't add comments or empty lines
 477     next if /^(--.*|\s+)$/;
 478
 479     for (my $i = 0; $i < length($_); $i++) {
 480       my $char = substr($_, $i, 1);
 481
 482       # Are we inside a string?
 483       if (@quote_chars) {
 484         if ($char eq $quote_chars[-1]) {
 485           pop(@quote_chars);
 486         }
 487         $query .= $char;
 488
 489       } else {
 490         if (($char eq "'") || ($char eq "\"")) {
 491           push(@quote_chars, $char);
 492
 493         } elsif ($char eq ";") {
 494
 495           # Query is complete. Send it.
 496
 497           $sth = $dbh->prepare($query);
 498           $sth->execute || $form->dberror($query);
 499           $sth->finish;
 500
 501           $char  = "";
 502           $query = "";
 503         }
 504
 505         $query .= $char;
 506       }
 507     }
 508   }
 509
 510   close $FH;
 511
 512   $main::lxdebug->leave_sub();
 513 }
 514
 515 sub ustva {
 516   $main::lxdebug->enter_sub();
 517
 518   my ($self, $myconfig, $form) = @_;
 519
 520   my $dbh = SL::DB->client->dbh;
 521
 522   my $last_period     = 0;
 523   my $category        = "pos_ustva";
 524
 525   $form->{coa} = $::instance_conf->get_coa;
 526
 527   unless ($form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') {
 528     croak t8("Advance turnover tax return only valid for SKR03 or SKR04");
 529   }
 530   my @category_cent = USTVA->report_variables({
 531       myconfig    => $myconfig,
 532       form        => $form,
 533       type        => '',
 534       attribute   => 'position',
 535       dec_places  => '2',
 536   });
 537   push @category_cent, ("pos_ustva_811b_kivi", "pos_ustva_861b_kivi");
 538   if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') {
 539       push @category_cent, qw(Z43  Z45  Z53  Z54  Z62  Z65  Z67);
 540   }
 541   my @category_euro = USTVA->report_variables({
 542       myconfig    => $myconfig,
 543       form        => $form,
 544       type        => '',
 545       attribute   => 'position',
 546       dec_places  => '0',
 547   });
 548   push @category_euro, ("pos_ustva_81b_kivi", "pos_ustva_86b_kivi");
 549   @{$form->{category_cent}} = @category_cent;
 550   @{$form->{category_euro}} = @category_euro;
 551   $form->{decimalplaces} *= 1;
 552
 553   foreach my $item (@category_cent) {
 554     $form->{"$item"} = 0;
 555   }
 556   foreach my $item (@category_euro) {
 557     $form->{"$item"} = 0;
 558   }
 559
 560   # Controlvariable for templates
 561   my $coa_name = $form->{coa};
 562   $form->{"$coa_name"} = '1';
 563
 564   &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
 565                       $form, $category);
 566
 567   ###########################################
 568   #
 569   # Nationspecific Modfications
 570   #
 571   ###########################################
 572
 573   # Germany
 574
 575   if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') {
 576
 577     # 16%/19% Umstellung
 578     # Umordnen der Kennziffern
 579     if ( $form->{year} < 2007) {
 580       $form->{35} += $form->{81};
 581       $form->{36} += $form->{811};
 582       $form->{95} += $form->{89};
 583       $form->{98} += $form->{891};
 584       map { delete $form->{$_} } qw(81 811 89 891);
 585     } else {
 586       $form->{35} += $form->{51};
 587       $form->{36} += $form->{511};
 588       $form->{95} += $form->{97};
 589       $form->{98} += $form->{971};
 590       map { delete $form->{$_} } qw(51 511 97 971);
 591     }
 592
 593   }
 594
 595
 596   # Fixme: Wird auch noch für Oesterreich gebraucht,
 597   # weil kein eigenes Ausgabeformular
 598   # sollte aber aus der allgemeinen Steuerberechnung verschwinden
 599   #
 600   # Berechnung der USTVA Formularfelder laut Bogen 207
 601   #
 602
 603   $form->{"51r"} = $form->{"511"};
 604   $form->{"86r"} = $form->{"861"};
 605   $form->{"97r"} = $form->{"971"};
 606   $form->{"93r"} = $form->{"931"};
 607
 608   $form->{"Z43"} = $form->{"511"}     + $form->{"811"} + $form->{"861"}
 609                      + $form->{"36"}  + $form->{"80"}  + $form->{"971"}
 610                      + $form->{"891"} + $form->{"931"} + $form->{"96"}
 611                      + $form->{"98"};
 612
 613   $form->{"Z45"} = $form->{"Z43"};
 614
 615   $form->{"Z53"} = $form->{"Z45"}     + $form->{"47"}  + $form->{"53"}  + $form->{"74"}
 616                      + $form->{"85"}  + $form->{"65"};
 617
 618   $form->{"Z62"} = $form->{"Z53"}     - $form->{"66"}  - $form->{"61"}
 619                      - $form->{"62"}  - $form->{"67"}  - $form->{"63"}
 620                      - $form->{"64"}  - $form->{"59"};
 621
 622   $form->{"Z65"} = $form->{"Z62"}     - $form->{"69"};
 623   $form->{"83"}  = $form->{"Z65"}     - $form->{"39"};
 624
 625   $main::lxdebug->leave_sub();
 626 }
 627
 628 sub get_accounts_ustva {
 629   $main::lxdebug->enter_sub();
 630
 631   my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
 632   our ($dpt_join);
 633
 634   my $query;
 635   my $where    = "";
 636   my $glwhere  = "";
 637   my $subwhere = "";
 638   my $ARwhere  = "";
 639   my $APwhere  = '';
 640   my $arwhere  = "";
 641   my $item;
 642
 643   my $gltaxkey_where = "((tk.pos_ustva = 46) OR (tk.pos_ustva>=59 AND tk.pos_ustva<=67) or (tk.pos_ustva>=89 AND tk.pos_ustva<=93))";
 644
 645   if ($fromdate) {
 646     if ($form->{accounting_method} eq 'cash') {
 647       $subwhere .= " AND transdate >= '$fromdate'";
 648       $glwhere = " AND ac.transdate >= '$fromdate'";
 649       $ARwhere .= " AND acc.transdate >= '$fromdate'";
 650     }
 651     $APwhere .= " AND AP.transdate >= '$fromdate'";
 652     $where .= " AND ac.transdate >= '$fromdate'";
 653   }
 654
 655   if ($todate) {
 656     $where    .= " AND ac.transdate <= '$todate'";
 657     $ARwhere  .= " AND acc.transdate <= '$todate'";
 658   }
 659
 660   my $acc_trans_where = '1=1';
 661   if ($fromdate || $todate) {
 662     $acc_trans_where = "ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE ";
 663
 664     if ($fromdate) {
 665       $acc_trans_where .= "transdate >= '$fromdate'";
 666     }
 667     if ($todate) {
 668       $acc_trans_where .= " AND " if ($fromdate);
 669       $acc_trans_where .= "transdate <= '$todate'";
 670     }
 671
 672     $acc_trans_where .= ")";
 673   }
 674
 675   ############################################
 676   # Method eq 'cash' = IST Versteuerung
 677   ############################################
 678   # Betrifft nur die eingenommene Umsatzsteuer
 679   #
 680   ############################################
 681
 682   if ($form->{accounting_method} eq 'cash') {
 683
 684     $query = qq|
 685        SELECT
 686          -- USTVA IST-Versteuerung
 687          --
 688          -- Alle tatsaechlichen _Zahlungseingaenge_
 689          -- im Voranmeldezeitraum erfassen
 690          -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
 691          SUM( ac.amount *
 692             -- Bezahlt / Rechnungssumme
 693            (
 694              SELECT SUM(acc.amount)
 695              FROM acc_trans acc
 696              INNER JOIN chart c ON (acc.chart_id   =   c.id
 697                                     AND c.link   like  '%AR_paid%')
 698              WHERE
 699               1=1
 700               $ARwhere
 701               AND acc.trans_id = ac.trans_id
 702               )           /
 703            (
 704             SELECT amount FROM ar WHERE id = ac.trans_id
 705            )
 706          ) AS amount,
 707          tk.pos_ustva,  t.rate, c.accno
 708        FROM acc_trans ac
 709        LEFT JOIN chart c ON (c.id  = ac.chart_id)
 710        LEFT JOIN ar      ON (ar.id = ac.trans_id)
 711        LEFT JOIN tax t   ON (t.id = ac.tax_id)
 712        LEFT JOIN taxkeys tk ON (
 713          tk.id = (
 714            SELECT id FROM taxkeys
 715            WHERE chart_id   = ac.chart_id
 716              -- AND taxkey_id  = ac.taxkey
 717              AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
 718            ORDER BY startdate DESC LIMIT 1
 719          )
 720        )
 721        WHERE
 722        $acc_trans_where
 723        GROUP BY tk.pos_ustva, t.rate, c.accno
 724     |;
 725
 726   } elsif ($form->{accounting_method} eq 'accrual') {
 727     #########################################
 728     # Method eq 'accrual' = Soll Versteuerung
 729     #########################################
 730
 731     $query = qq|
 732        -- Alle Einnahmen AR und pos_ustva erfassen
 733        SELECT
 734          - sum(ac.amount) AS amount,
 735          tk.pos_ustva, t.rate, c.accno
 736        FROM acc_trans ac
 737        JOIN chart c ON (c.id = ac.chart_id)
 738        JOIN ar ON (ar.id = ac.trans_id)
 739        JOIN tax t ON (t.id = ac.tax_id)
 740        JOIN taxkeys tk ON (
 741          tk.id = (
 742            SELECT id FROM taxkeys
 743            WHERE chart_id   = ac.chart_id
 744              AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
 745            ORDER BY startdate DESC LIMIT 1
 746          )
 747        )
 748        $dpt_join
 749        WHERE 1 = 1
 750        $where
 751        GROUP BY tk.pos_ustva, t.rate, c.accno
 752   |;
 753
 754   } else {
 755
 756     $form->error("Unknown tax method: $form->{accounting_method}")
 757
 758   }
 759
 760   #########################################
 761   # Ausgaben und Gl Buchungen sind gleich
 762   # für Ist- und Soll-Versteuerung
 763   #########################################
 764   $query .= qq|
 765      UNION -- alle Ausgaben AP erfassen
 766
 767        SELECT
 768          sum(ac.amount) AS amount,
 769          tk.pos_ustva, t.rate, c.accno
 770        FROM acc_trans ac
 771        JOIN ap ON (ap.id = ac.trans_id )
 772        JOIN chart c ON (c.id = ac.chart_id)
 773        JOIN tax t ON (t.id = ac.tax_id)
 774        LEFT JOIN taxkeys tk ON (
 775            tk.id = (
 776              SELECT id FROM taxkeys
 777              WHERE 1=1
 778                AND chart_id=ac.chart_id
 779                --AND taxkey_id = ac.taxkey
 780                AND startdate <= COALESCE(AP.transdate)
 781              ORDER BY startdate DESC LIMIT 1
 782            )
 783        )
 784        WHERE
 785        1=1
 786        $where
 787        GROUP BY tk.pos_ustva, t.rate, c.accno
 788
 789      UNION -- Einnahmen direkter gl Buchungen erfassen
 790
 791        SELECT sum
 792          ( - ac.amount) AS amount,
 793          tk.pos_ustva, t.rate, c.accno
 794        FROM acc_trans ac
 795        JOIN chart c ON (c.id = ac.chart_id)
 796        JOIN gl a ON (a.id = ac.trans_id)
 797        JOIN tax t ON (t.id = ac.tax_id)
 798        LEFT JOIN taxkeys tk ON (
 799          tk.id = (
 800            SELECT id FROM taxkeys
 801            WHERE chart_id=ac.chart_id
 802              AND NOT $gltaxkey_where
 803              AND startdate <= COALESCE(ac.transdate)
 804            ORDER BY startdate DESC LIMIT 1
 805          )
 806        )
 807
 808        $dpt_join
 809        WHERE 1 = 1
 810        $where
 811        GROUP BY tk.pos_ustva, t.rate, c.accno
 812
 813
 814      UNION -- Ausgaben direkter gl Buchungen erfassen
 815
 816        SELECT sum
 817          (ac.amount) AS amount,
 818          tk.pos_ustva, t.rate, c.accno
 819        FROM acc_trans ac
 820        JOIN chart c ON (c.id = ac.chart_id)
 821        JOIN gl a ON (a.id = ac.trans_id)
 822        JOIN tax t ON (t.id = ac.tax_id)
 823        LEFT JOIN taxkeys tk ON (
 824          tk.id = (
 825            SELECT id FROM taxkeys
 826            WHERE chart_id=ac.chart_id
 827              AND $gltaxkey_where
 828              AND startdate <= COALESCE(ac.transdate)
 829            ORDER BY startdate DESC LIMIT 1
 830          )
 831        )
 832
 833        $dpt_join
 834        WHERE 1 = 1
 835        $where
 836        GROUP BY tk.pos_ustva, t.rate, c.accno
 837
 838   |;
 839
 840   # Show all $query in Debuglevel LXDebug::QUERY
 841   my $callingdetails = (caller (0))[3];
 842   $main::lxdebug->message(LXDebug->QUERY(), "$callingdetails \$query=\n $query");
 843
 844   my $sth = $dbh->prepare($query);
 845
 846   $sth->execute || $form->dberror($query);
 847   # ugly, but we need to use static accnos
 848   my ($accno_five, $accno_sixteen, $corr);
 849
 850   if ($form->{coa} eq 'Germany-DATEV-SKR03EU') {
 851     $accno_five     = 1773;
 852     $accno_sixteen  = 1775;
 853   } elsif (($form->{coa} eq 'Germany-DATEV-SKR04EU')) {
 854     $accno_five     = 3803; # SKR04
 855     $accno_sixteen  = 3805; # SKR04
 856   } else {die "wrong call"; }
 857
 858   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
 859     next unless $ref->{$category};
 860     $corr = 0;
 861     $ref->{amount} *= -1;
 862     # USTVA Pos 35
 863     if ($ref->{pos_ustva} eq '35') {
 864       if ($ref->{rate} == 0.16) {
 865         $form->{"pos_ustva_81b_kivi"} += $ref->{amount};
 866       } elsif ($ref->{rate} == 0.05) {
 867         $form->{"pos_ustva_86b_kivi"} += $ref->{amount};
 868       } elsif ($ref->{rate} == 0.19) {
 869         # pos_ustva says 16, but rate says 19
 870         # (pos_ustva should be tax dependent and not taxkeys dependent)
 871         # correction hotfix for this case:
 872         # bookings exists with 19% ->
 873         # move 19% bookings to the 19% position
 874         # Dont rely on dates of taxkeys
 875         $corr = 1;
 876         $form->{"81"} += $ref->{amount};
 877       }  elsif ($ref->{rate} == 0.07) {
 878         # pos_ustva says 5, but rate says 7
 879         # see comment above:
 880         # Dont rely on dates of taxkeys
 881         $corr = 1;
 882         $form->{"86"} += $ref->{amount};
 883       } else {die ("No valid tax rate for pos 35" . Dumper($ref)); }
 884     }
 885     # USTVA Pos 36 (Steuerkonten)
 886     if ($ref->{pos_ustva} eq '36') {
 887       if ($ref->{accno} =~ /^$accno_sixteen/) {
 888         $form->{"pos_ustva_811b_kivi"} += $ref->{amount};
 889       } elsif ($ref->{accno} =~ /^$accno_five/) {
 890         $form->{"pos_ustva_861b_kivi"} += $ref->{amount};
 891       } else { die ("No valid accno for pos 36" . Dumper($ref)); }
 892     }
 893   $form->{ $ref->{$category} } += $ref->{amount} unless $corr;
 894   }
 895
 896   $sth->finish;
 897
 898   $main::lxdebug->leave_sub();
 899
 900 }
 901
 902 sub set_FromTo {
 903   $main::lxdebug->enter_sub();
 904
 905   my ($self, $form) = @_;
 906
 907   # init some form vars
 908   my @anmeldungszeitraum =
 909     qw('0401' '0402' '0403'
 910        '0404' '0405' '0406'
 911        '0407' '0408' '0409'
 912        '0410' '0411' '0412'
 913        '0441' '0442' '0443' '0444');
 914
 915   foreach my $item (@anmeldungszeitraum) {
 916     $form->{$item} = "";
 917   }
 918
 919   #forgotten the year --> thisyear
 920   if ($form->{year} !~ m/^\d\d\d\d$/) {
 921       $form->{year} = substr(
 922           $form->datetonum(
 923               $form->current_date(\%::myconfig), \%::myconfig
 924           ),
 925           0, 4);
 926       $::lxdebug->message(LXDebug->DEBUG1,
 927                           qq|Actual year from Database: $form->{year}\n|);
 928   }
 929
 930   #
 931   # using dates in ISO-8601 format: yyyymmmdd  for Postgres...
 932   #
 933
 934   #yearly report
 935   if ($form->{period} eq "13") {
 936       $form->{fromdate} = "$form->{year}0101";
 937       $form->{todate}   = "$form->{year}1231";
 938   }
 939
 940   #quarter reports
 941   if ($form->{period} eq "41") {
 942       $form->{fromdate} = "$form->{year}0101";
 943       $form->{todate}   = "$form->{year}0331";
 944       $form->{'0441'}   = "X";
 945   }
 946   if ($form->{period} eq "42") {
 947       $form->{fromdate} = "$form->{year}0401";
 948       $form->{todate}   = "$form->{year}0630";
 949       $form->{'0442'}   = "X";
 950   }
 951   if ($form->{period} eq "43") {
 952       $form->{fromdate} = "$form->{year}0701";
 953       $form->{todate}   = "$form->{year}0930";
 954       $form->{'0443'}   = "X";
 955   }
 956   if ($form->{period} eq "44") {
 957       $form->{fromdate} = "$form->{year}1001";
 958       $form->{todate}   = "$form->{year}1231";
 959       $form->{'0444'}   = "X";
 960   }
 961
 962    #Monthly reports
 963   SWITCH: {
 964       $form->{period} eq "01" && do {
 965         $form->{fromdate} = "$form->{year}0101";
 966         $form->{todate}   = "$form->{year}0131";
 967         $form->{'0401'}   = "X";
 968         last SWITCH;
 969       };
 970       $form->{period} eq "02" && do {
 971         $form->{fromdate} = "$form->{year}0201";
 972
 973         #this works from 1901 to 2099, 1900 and 2100 fail.
 974         my $leap = ($form->{year} % 4 == 0) ? "29" : "28";
 975         $form->{todate} = "$form->{year}02$leap";
 976         $form->{"0402"} = "X";
 977         last SWITCH;
 978       };
 979       $form->{period} eq "03" && do {
 980         $form->{fromdate} = "$form->{year}0301";
 981         $form->{todate}   = "$form->{year}0331";
 982         $form->{"0403"}   = "X";
 983         last SWITCH;
 984       };
 985       $form->{period} eq "04" && do {
 986         $form->{fromdate} = "$form->{year}0401";
 987         $form->{todate}   = "$form->{year}0430";
 988         $form->{"0404"}   = "X";
 989         last SWITCH;
 990       };
 991       $form->{period} eq "05" && do {
 992         $form->{fromdate} = "$form->{year}0501";
 993         $form->{todate}   = "$form->{year}0531";
 994         $form->{"0405"}   = "X";
 995         last SWITCH;
 996       };
 997       $form->{period} eq "06" && do {
 998         $form->{fromdate} = "$form->{year}0601";
 999         $form->{todate}   = "$form->{year}0630";
1000         $form->{"0406"}   = "X";
1001         last SWITCH;
1002       };
1003       $form->{period} eq "07" && do {
1004         $form->{fromdate} = "$form->{year}0701";
1005         $form->{todate}   = "$form->{year}0731";
1006         $form->{"0407"}   = "X";
1007         last SWITCH;
1008       };
1009       $form->{period} eq "08" && do {
1010         $form->{fromdate} = "$form->{year}0801";
1011         $form->{todate}   = "$form->{year}0831";
1012         $form->{"0408"}   = "X";
1013         last SWITCH;
1014       };
1015       $form->{period} eq "09" && do {
1016         $form->{fromdate} = "$form->{year}0901";
1017         $form->{todate}   = "$form->{year}0930";
1018         $form->{"0409"}   = "X";
1019         last SWITCH;
1020       };
1021       $form->{period} eq "10" && do {
1022         $form->{fromdate} = "$form->{year}1001";
1023         $form->{todate}   = "$form->{year}1031";
1024         $form->{"0410"}   = "X";
1025         last SWITCH;
1026       };
1027       $form->{period} eq "11" && do {
1028         $form->{fromdate} = "$form->{year}1101";
1029         $form->{todate}   = "$form->{year}1130";
1030         $form->{"0411"}   = "X";
1031         last SWITCH;
1032       };
1033       $form->{period} eq "12" && do {
1034         $form->{fromdate} = "$form->{year}1201";
1035         $form->{todate}   = "$form->{year}1231";
1036         $form->{"0412"}   = "X";
1037         last SWITCH;
1038       };
1039     }
1040
1041   # Kontrollvariablen für die Templates
1042   $form->{"year$_"} = ($form->{year} >= $_ ) ? "1":"0" for 2007..2107;
1043
1044   $main::lxdebug->leave_sub();
1045 }
1046
1047 sub get_fiamt_vars {
1048     return @fiamt_finanzamt;
1049 }
1050
1051 sub get_oldconfig {
1052   $main::lxdebug->enter_sub();
1053
1054   my $ret = 0;
1055   my %oldkeys = (
1056       'steuernummer' => 'taxnumber',
1057       'elsterFFFF' => 'fa_bufa_nr',
1058       'FA_dauerfrist' => 'fa_dauerfrist',
1059       'FA_steuerberater_city' => 'fa_steuerberater_city',
1060       'FA_steuerberater_name' => 'fa_steuerberater_name',
1061       'FA_steuerberater_street' => 'fa_steuerberater_street',
1062       'FA_steuerberater_tel' => 'fa_steuerberater_tel',
1063       'FA_voranmeld' => 'fa_voranmeld',
1064       );
1065
1066   my $filename = $::lx_office_conf{paths}{userspath}."/finanzamt.ini";
1067   my $FACONF;
1068   return unless (open( $FACONF, "<", $filename));
1069
1070   while (<$FACONF>) {
1071     last if (/^\[/);
1072     next if (/^(\#|\s)/);
1073
1074     # remove comments
1075     s/\s#.*//g;
1076
1077     # remove any trailing whitespace
1078     s/^\s*(.*?)\s*$/$1/;
1079     my ($key, $value) = split(/=/, $_, 2);
1080
1081     $main::lxdebug->message(LXDebug->DEBUG2(), "oldkey: ".$key." val=".$value." newkey=".
1082                           $oldkeys{$key}." oval=".$::form->{$oldkeys{$key}});
1083     if ( $oldkeys{$key} && $::form->{$oldkeys{$key}} eq '' ) {
1084         $::form->{$oldkeys{$key}} = $::locale->{iconv_utf8}->convert($value);
1085         $main::lxdebug->message(LXDebug->DEBUG2(), "set ".$oldkeys{$key}."=".$::form->{$oldkeys{$key}});
1086         $ret = 1;
1087     }
1088   }
1089   $main::lxdebug->leave_sub();
1090   return $ret;
1091 }
1092
1093 sub get_config {
1094     $main::lxdebug->enter_sub();
1095     my $defaults   = SL::DB::Default->get;
1096     my @rd_config =  @fiamt_config;
1097     push @rd_config ,qw(accounting_method coa company address co_ustid duns);
1098     $::form->{$_} = $defaults->$_ for @rd_config;
1099
1100     if ( $::form->{taxnumber} eq '' || $::form->{fa_bufa_nr} eq '') {
1101         #alte finanzamt.ini lesen, ggf abspeichern
1102         if ( get_oldconfig() ) {
1103             get_finanzamt();
1104             save_config();
1105         }
1106     }
1107
1108     my $coa = $::form->{coa};
1109     $::form->{"COA_$coa"} = '1';
1110     $::form->{COA_Germany} = '1' if ($coa =~ m/^germany/i);
1111     $main::lxdebug->leave_sub();
1112 }
1113
1114 sub get_finanzamt {
1115     $main::lxdebug->enter_sub();
1116     if ( $::form->{fa_bufa_nr} && $::form->{fa_bufa_nr} ne '' ) {
1117         my $fiamt =  SL::DB::Finanzamt->_get_manager_class->get_first(
1118                  query => [ fa_bufa_nr => $::form->{fa_bufa_nr} ]);
1119         $::form->{$_} = $fiamt->$_ for @fiamt_finanzamt;
1120     }
1121     $main::lxdebug->leave_sub();
1122 }
1123
1124 sub save_config {
1125     $main::lxdebug->enter_sub();
1126     my $defaults  = SL::DB::Default->get;
1127     $defaults->$_($::form->{$_}) for @fiamt_config;
1128     $defaults->save;
1129     if ( $defaults->fa_bufa_nr ) {
1130         my $fiamt =  SL::DB::Finanzamt->_get_manager_class->get_first(
1131                  query => [ fa_bufa_nr => $defaults->fa_bufa_nr ]);
1132         $fiamt->$_($::form->{$_}) for @fiamt_finanzamt;
1133         $fiamt->save;
1134     }
1135     $main::lxdebug->leave_sub();
1136 }
1137
1138 1;