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), t.rate, c.accno
 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            (
 705             SELECT amount FROM ar WHERE id = ac.trans_id
 706            )
 707          ) AS amount,
 708          tk.pos_ustva
 709        FROM acc_trans ac
 710        LEFT JOIN chart c ON (c.id  = ac.chart_id)
 711        LEFT JOIN ar      ON (ar.id = ac.trans_id)
 712        LEFT JOIN tax t   ON (t.id = ac.tax_id)
 713        LEFT JOIN taxkeys tk ON (
 714          tk.id = (
 715            SELECT id FROM taxkeys
 716            WHERE chart_id   = ac.chart_id
 717              -- AND taxkey_id  = ac.taxkey
 718              AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
 719            ORDER BY startdate DESC LIMIT 1
 720          )
 721        )
 722        WHERE
 723        $acc_trans_where
 724        GROUP BY tk.pos_ustva, t.rate, c.accno
 725     |;
 726
 727   } elsif ($form->{accounting_method} eq 'accrual') {
 728     #########################################
 729     # Method eq 'accrual' = Soll Versteuerung
 730     #########################################
 731
 732     $query = qq|
 733        -- Alle Einnahmen AR und pos_ustva erfassen
 734        SELECT
 735          - sum(ac.amount) AS amount,
 736          tk.pos_ustva, t.rate, c.accno
 737        FROM acc_trans ac
 738        JOIN chart c ON (c.id = ac.chart_id)
 739        JOIN ar ON (ar.id = ac.trans_id)
 740        JOIN tax t ON (t.id = ac.tax_id)
 741        JOIN taxkeys tk ON (
 742          tk.id = (
 743            SELECT id FROM taxkeys
 744            WHERE chart_id   = ac.chart_id
 745              AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
 746            ORDER BY startdate DESC LIMIT 1
 747          )
 748        )
 749        $dpt_join
 750        WHERE 1 = 1
 751        $where
 752        GROUP BY tk.pos_ustva, t.rate, c.accno
 753   |;
 754
 755   } else {
 756
 757     $form->error("Unknown tax method: $form->{accounting_method}")
 758
 759   }
 760
 761   #########################################
 762   # Ausgaben und Gl Buchungen sind gleich
 763   # für Ist- und Soll-Versteuerung
 764   #########################################
 765   $query .= qq|
 766      UNION -- alle Ausgaben AP erfassen
 767
 768        SELECT
 769          sum(ac.amount) AS amount,
 770          tk.pos_ustva, t.rate, c.accno
 771        FROM acc_trans ac
 772        JOIN ap ON (ap.id = ac.trans_id )
 773        JOIN chart c ON (c.id = ac.chart_id)
 774        JOIN tax t ON (t.id = ac.tax_id)
 775        LEFT JOIN taxkeys tk ON (
 776            tk.id = (
 777              SELECT id FROM taxkeys
 778              WHERE 1=1
 779                AND chart_id=ac.chart_id
 780                --AND taxkey_id = ac.taxkey
 781                AND startdate <= COALESCE(AP.transdate)
 782              ORDER BY startdate DESC LIMIT 1
 783            )
 784        )
 785        WHERE
 786        1=1
 787        $where
 788        GROUP BY tk.pos_ustva, t.rate, c.accno
 789
 790      UNION -- Einnahmen direkter gl Buchungen erfassen
 791
 792        SELECT sum
 793          ( - ac.amount) AS amount,
 794          tk.pos_ustva, t.rate, c.accno
 795        FROM acc_trans ac
 796        JOIN chart c ON (c.id = ac.chart_id)
 797        JOIN gl a ON (a.id = ac.trans_id)
 798        JOIN tax t ON (t.id = ac.tax_id)
 799        LEFT JOIN taxkeys tk ON (
 800          tk.id = (
 801            SELECT id FROM taxkeys
 802            WHERE chart_id=ac.chart_id
 803              AND NOT $gltaxkey_where
 804              AND startdate <= COALESCE(ac.transdate)
 805            ORDER BY startdate DESC LIMIT 1
 806          )
 807        )
 808
 809        $dpt_join
 810        WHERE 1 = 1
 811        $where
 812        GROUP BY tk.pos_ustva, t.rate, c.accno
 813
 814
 815      UNION -- Ausgaben direkter gl Buchungen erfassen
 816
 817        SELECT sum
 818          (ac.amount) AS amount,
 819          tk.pos_ustva, t.rate, c.accno
 820        FROM acc_trans ac
 821        JOIN chart c ON (c.id = ac.chart_id)
 822        JOIN gl a ON (a.id = ac.trans_id)
 823        JOIN tax t ON (t.id = ac.tax_id)
 824        LEFT JOIN taxkeys tk ON (
 825          tk.id = (
 826            SELECT id FROM taxkeys
 827            WHERE chart_id=ac.chart_id
 828              AND $gltaxkey_where
 829              AND startdate <= COALESCE(ac.transdate)
 830            ORDER BY startdate DESC LIMIT 1
 831          )
 832        )
 833
 834        $dpt_join
 835        WHERE 1 = 1
 836        $where
 837        GROUP BY tk.pos_ustva, t.rate, c.accno
 838
 839   |;
 840
 841   # Show all $query in Debuglevel LXDebug::QUERY
 842   my $callingdetails = (caller (0))[3];
 843   $main::lxdebug->message(LXDebug->QUERY(), "$callingdetails \$query=\n $query");
 844
 845   my $sth = $dbh->prepare($query);
 846
 847   $sth->execute || $form->dberror($query);
 848   # ugly, but we need to use static accnos
 849   my ($accno_five, $accno_sixteen, $corr);
 850
 851   if ($form->{coa} eq 'Germany-DATEV-SKR03EU') {
 852     $accno_five     = 1773;
 853     $accno_sixteen  = 1775;
 854   } elsif (($form->{coa} eq 'Germany-DATEV-SKR04EU')) {
 855     $accno_five     = 3803; # SKR04
 856     $accno_sixteen  = 3805; # SKR04
 857   } else {die "wrong call"; }
 858
 859   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
 860     next unless $ref->{$category};
 861     $corr = 0;
 862     $ref->{amount} *= -1;
 863     # USTVA Pos 35
 864     if ($ref->{pos_ustva} eq '35') {
 865       if ($ref->{rate} == 0.16) {
 866         $form->{"pos_ustva_81b_kivi"} += $ref->{amount};
 867       } elsif ($ref->{rate} == 0.05) {
 868         $form->{"pos_ustva_86b_kivi"} += $ref->{amount};
 869       } elsif ($ref->{rate} == 0.19) {
 870         # pos_ustva says 16, but rate says 19
 871         # (pos_ustva should be tax dependent and not taxkeys dependent)
 872         # correction hotfix for this case:
 873         # bookings exists with 19% ->
 874         # move 19% bookings to the 19% position
 875         # Dont rely on dates of taxkeys
 876         $corr = 1;
 877         $form->{"81"} += $ref->{amount};
 878       }  elsif ($ref->{rate} == 0.07) {
 879         # pos_ustva says 5, but rate says 7
 880         # see comment above:
 881         # Dont rely on dates of taxkeys
 882         $corr = 1;
 883         $form->{"86"} += $ref->{amount};
 884       } else {die ("No valid tax rate for pos 35" . Dumper($ref)); }
 885     }
 886     # USTVA Pos 36 (Steuerkonten)
 887     if ($ref->{pos_ustva} eq '36') {
 888       if ($ref->{accno} =~ /^$accno_sixteen/) {
 889         $form->{"pos_ustva_811b_kivi"} += $ref->{amount};
 890       } elsif ($ref->{accno} =~ /^$accno_five/) {
 891         $form->{"pos_ustva_861b_kivi"} += $ref->{amount};
 892       } else { die ("No valid accno for pos 36" . Dumper($ref)); }
 893     }
 894   $form->{ $ref->{$category} } += $ref->{amount} unless $corr;
 895   }
 896
 897   $sth->finish;
 898
 899   $main::lxdebug->leave_sub();
 900
 901 }
 902
 903 sub set_FromTo {
 904   $main::lxdebug->enter_sub();
 905
 906   my ($self, $form) = @_;
 907
 908   # init some form vars
 909   my @anmeldungszeitraum =
 910     qw('0401' '0402' '0403'
 911        '0404' '0405' '0406'
 912        '0407' '0408' '0409'
 913        '0410' '0411' '0412'
 914        '0441' '0442' '0443' '0444');
 915
 916   foreach my $item (@anmeldungszeitraum) {
 917     $form->{$item} = "";
 918   }
 919
 920   #forgotten the year --> thisyear
 921   if ($form->{year} !~ m/^\d\d\d\d$/) {
 922       $form->{year} = substr(
 923           $form->datetonum(
 924               $form->current_date(\%::myconfig), \%::myconfig
 925           ),
 926           0, 4);
 927       $::lxdebug->message(LXDebug->DEBUG1,
 928                           qq|Actual year from Database: $form->{year}\n|);
 929   }
 930
 931   #
 932   # using dates in ISO-8601 format: yyyymmmdd  for Postgres...
 933   #
 934
 935   #yearly report
 936   if ($form->{period} eq "13") {
 937       $form->{fromdate} = "$form->{year}0101";
 938       $form->{todate}   = "$form->{year}1231";
 939   }
 940
 941   #quarter reports
 942   if ($form->{period} eq "41") {
 943       $form->{fromdate} = "$form->{year}0101";
 944       $form->{todate}   = "$form->{year}0331";
 945       $form->{'0441'}   = "X";
 946   }
 947   if ($form->{period} eq "42") {
 948       $form->{fromdate} = "$form->{year}0401";
 949       $form->{todate}   = "$form->{year}0630";
 950       $form->{'0442'}   = "X";
 951   }
 952   if ($form->{period} eq "43") {
 953       $form->{fromdate} = "$form->{year}0701";
 954       $form->{todate}   = "$form->{year}0930";
 955       $form->{'0443'}   = "X";
 956   }
 957   if ($form->{period} eq "44") {
 958       $form->{fromdate} = "$form->{year}1001";
 959       $form->{todate}   = "$form->{year}1231";
 960       $form->{'0444'}   = "X";
 961   }
 962
 963    #Monthly reports
 964   SWITCH: {
 965       $form->{period} eq "01" && do {
 966         $form->{fromdate} = "$form->{year}0101";
 967         $form->{todate}   = "$form->{year}0131";
 968         $form->{'0401'}   = "X";
 969         last SWITCH;
 970       };
 971       $form->{period} eq "02" && do {
 972         $form->{fromdate} = "$form->{year}0201";
 973
 974         #this works from 1901 to 2099, 1900 and 2100 fail.
 975         my $leap = ($form->{year} % 4 == 0) ? "29" : "28";
 976         $form->{todate} = "$form->{year}02$leap";
 977         $form->{"0402"} = "X";
 978         last SWITCH;
 979       };
 980       $form->{period} eq "03" && do {
 981         $form->{fromdate} = "$form->{year}0301";
 982         $form->{todate}   = "$form->{year}0331";
 983         $form->{"0403"}   = "X";
 984         last SWITCH;
 985       };
 986       $form->{period} eq "04" && do {
 987         $form->{fromdate} = "$form->{year}0401";
 988         $form->{todate}   = "$form->{year}0430";
 989         $form->{"0404"}   = "X";
 990         last SWITCH;
 991       };
 992       $form->{period} eq "05" && do {
 993         $form->{fromdate} = "$form->{year}0501";
 994         $form->{todate}   = "$form->{year}0531";
 995         $form->{"0405"}   = "X";
 996         last SWITCH;
 997       };
 998       $form->{period} eq "06" && do {
 999         $form->{fromdate} = "$form->{year}0601";
1000         $form->{todate}   = "$form->{year}0630";
1001         $form->{"0406"}   = "X";
1002         last SWITCH;
1003       };
1004       $form->{period} eq "07" && do {
1005         $form->{fromdate} = "$form->{year}0701";
1006         $form->{todate}   = "$form->{year}0731";
1007         $form->{"0407"}   = "X";
1008         last SWITCH;
1009       };
1010       $form->{period} eq "08" && do {
1011         $form->{fromdate} = "$form->{year}0801";
1012         $form->{todate}   = "$form->{year}0831";
1013         $form->{"0408"}   = "X";
1014         last SWITCH;
1015       };
1016       $form->{period} eq "09" && do {
1017         $form->{fromdate} = "$form->{year}0901";
1018         $form->{todate}   = "$form->{year}0930";
1019         $form->{"0409"}   = "X";
1020         last SWITCH;
1021       };
1022       $form->{period} eq "10" && do {
1023         $form->{fromdate} = "$form->{year}1001";
1024         $form->{todate}   = "$form->{year}1031";
1025         $form->{"0410"}   = "X";
1026         last SWITCH;
1027       };
1028       $form->{period} eq "11" && do {
1029         $form->{fromdate} = "$form->{year}1101";
1030         $form->{todate}   = "$form->{year}1130";
1031         $form->{"0411"}   = "X";
1032         last SWITCH;
1033       };
1034       $form->{period} eq "12" && do {
1035         $form->{fromdate} = "$form->{year}1201";
1036         $form->{todate}   = "$form->{year}1231";
1037         $form->{"0412"}   = "X";
1038         last SWITCH;
1039       };
1040     }
1041
1042   # Kontrollvariablen für die Templates
1043   $form->{"year$_"} = ($form->{year} >= $_ ) ? "1":"0" for 2007..2107;
1044
1045   $main::lxdebug->leave_sub();
1046 }
1047
1048 sub get_fiamt_vars {
1049     return @fiamt_finanzamt;
1050 }
1051
1052 sub get_oldconfig {
1053   $main::lxdebug->enter_sub();
1054
1055   my $ret = 0;
1056   my %oldkeys = (
1057       'steuernummer' => 'taxnumber',
1058       'elsterFFFF' => 'fa_bufa_nr',
1059       'FA_dauerfrist' => 'fa_dauerfrist',
1060       'FA_steuerberater_city' => 'fa_steuerberater_city',
1061       'FA_steuerberater_name' => 'fa_steuerberater_name',
1062       'FA_steuerberater_street' => 'fa_steuerberater_street',
1063       'FA_steuerberater_tel' => 'fa_steuerberater_tel',
1064       'FA_voranmeld' => 'fa_voranmeld',
1065       );
1066
1067   my $filename = $::lx_office_conf{paths}{userspath}."/finanzamt.ini";
1068   my $FACONF;
1069   return unless (open( $FACONF, "<", $filename));
1070
1071   while (<$FACONF>) {
1072     last if (/^\[/);
1073     next if (/^(\#|\s)/);
1074
1075     # remove comments
1076     s/\s#.*//g;
1077
1078     # remove any trailing whitespace
1079     s/^\s*(.*?)\s*$/$1/;
1080     my ($key, $value) = split(/=/, $_, 2);
1081
1082     $main::lxdebug->message(LXDebug->DEBUG2(), "oldkey: ".$key." val=".$value." newkey=".
1083                           $oldkeys{$key}." oval=".$::form->{$oldkeys{$key}});
1084     if ( $oldkeys{$key} && $::form->{$oldkeys{$key}} eq '' ) {
1085         $::form->{$oldkeys{$key}} = $::locale->{iconv_utf8}->convert($value);
1086         $main::lxdebug->message(LXDebug->DEBUG2(), "set ".$oldkeys{$key}."=".$::form->{$oldkeys{$key}});
1087         $ret = 1;
1088     }
1089   }
1090   $main::lxdebug->leave_sub();
1091   return $ret;
1092 }
1093
1094 sub get_config {
1095     $main::lxdebug->enter_sub();
1096     my $defaults   = SL::DB::Default->get;
1097     my @rd_config =  @fiamt_config;
1098     push @rd_config ,qw(accounting_method coa company address co_ustid duns);
1099     $::form->{$_} = $defaults->$_ for @rd_config;
1100
1101     if ( $::form->{taxnumber} eq '' || $::form->{fa_bufa_nr} eq '') {
1102         #alte finanzamt.ini lesen, ggf abspeichern
1103         if ( get_oldconfig() ) {
1104             get_finanzamt();
1105             save_config();
1106         }
1107     }
1108
1109     my $coa = $::form->{coa};
1110     $::form->{"COA_$coa"} = '1';
1111     $::form->{COA_Germany} = '1' if ($coa =~ m/^germany/i);
1112     $main::lxdebug->leave_sub();
1113 }
1114
1115 sub get_finanzamt {
1116     $main::lxdebug->enter_sub();
1117     if ( $::form->{fa_bufa_nr} && $::form->{fa_bufa_nr} ne '' ) {
1118         my $fiamt =  SL::DB::Finanzamt->_get_manager_class->get_first(
1119                  query => [ fa_bufa_nr => $::form->{fa_bufa_nr} ]);
1120         $::form->{$_} = $fiamt->$_ for @fiamt_finanzamt;
1121     }
1122     $main::lxdebug->leave_sub();
1123 }
1124
1125 sub save_config {
1126     $main::lxdebug->enter_sub();
1127     my $defaults  = SL::DB::Default->get;
1128     $defaults->$_($::form->{$_}) for @fiamt_config;
1129     $defaults->save;
1130     if ( $defaults->fa_bufa_nr ) {
1131         my $fiamt =  SL::DB::Finanzamt->_get_manager_class->get_first(
1132                  query => [ fa_bufa_nr => $defaults->fa_bufa_nr ]);
1133         $fiamt->$_($::form->{$_}) for @fiamt_finanzamt;
1134         $fiamt->save;
1135     }
1136     $main::lxdebug->leave_sub();
1137 }
1138
1139 1;