1 #=====================================================================
3 # Copyright (c) 2004 by Udo Spallek, Aachen
7 # Web: http://www.lx-office.org
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.
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., 675 Mass Ave, Cambridge, MA 02139, USA.
22 #======================================================================
24 #=====================================================================
28 sub create_steuernummer {
29 $main::lxdebug->enter_sub();
33 $patterncount = $form->{patterncount};
34 $delimiter = $form->{delimiter};
35 $elster_pattern = $form->{elster_pattern};
37 # rebuild steuernummer and elstersteuernummer
38 # es gibt eine gespeicherte steuernummer $form->{steuernummer}
39 # und die parts und delimiter
44 $steuernummer_new = $part;
45 $elstersteuernummer_new = $elster_FFFF;
46 $elstersteuernummer_new .= '0';
48 for ( $h = 1; $h < $patterncount; $h++) {
49 $steuernummer_new .= qq|$delimiter|;
50 for ( $i = 1; $i <= length($elster_pattern); $i++ ) {
51 $steuernummer_new .=$form->{"part_$h\_$i"};
52 $elstersteuernummer_new .=$form->{"part_$h\_$i"};
55 if ($form->{steuernummer} ne $steuernummer_new){
56 $form->{steuernummer} = $steuernummer_new;
57 $form->{elstersteuernummer} = $elstersteuernummer_new;
58 $form->{steuernummer_new} = $steuernummer_new;
60 $main::lxdebug->leave_sub();
61 return ($steuernummer_new, $elstersteuernummer_new);
65 sub steuernummer_input {
66 $main::lxdebug->enter_sub();
69 ($elsterland, $elsterFFFF, $steuernummer ) = @_;
71 $elster_land=$elsterland;
72 $elster_FFFF=$elsterFFFF;
73 $steuernummer = '0000000000' if ( $steuernummer eq '' );
74 # $steuernummer formatieren (nur Zahlen) -> $stnr
75 $stnr = $steuernummer;
78 #Pattern description Elstersteuernummer
79 my %elster_STNRformat = (
80 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3
81 'Hessen' => '0FF BBB UUUUP', # ' ' 3
82 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3
83 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2
84 'Berlin' => 'FF/BBB/UUUUP', # '/' 3
85 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3
86 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3
87 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3
88 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2
89 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3
90 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3
91 'Bremen' => 'FF BBB UUUUP', # ' ' 3
92 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3
93 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4
94 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3
95 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3
99 $elster_pattern = $elster_STNRformat{$elster_land};
100 my @elster_pattern = split(' ', $elster_pattern);
101 my $delimiter=' ';
102 my $patterncount = @elster_pattern;
103 if ($patterncount < 2) {
105 @elster_pattern = split('/', $elster_pattern);
107 $patterncount = @elster_pattern;
110 # no we have an array of patternparts and a delimiter
111 # create the first automated and fixed part and delimiter
113 print qq|<b><font size="+1">|;
116 $elster_pattern[0] eq 'FFF' && do {
117 $part= substr($elster_FFFF,1,4);
121 $elster_pattern[0] eq '0FF' && do {
122 $part= '0'.substr($elster_FFFF,2,4);
126 $elster_pattern[0] eq 'FF' && do {
127 $part= substr($elster_FFFF,2,4);
136 #now the rest of the Steuernummer ...
137 print qq|</b></font>|;
139 <input type=hidden name="elster_pattern" value="$elster_pattern">
140 <input type=hidden name="patterncount" value="$patterncount">
141 <input type=hidden name="patternlength" value="$patterncount">
142 <input type=hidden name="delimiter" value="$delimiter">
143 <input type=hidden name="part" value="$part">
150 for ( $h = 1; $h < $patterncount; $h++) {
151 print qq| $delimiter \n|;
152 for ( $i = 1; $i <= length($elster_pattern[$h] ); $i++ ) {
153 print qq|<select name="part_$h\_$i">\n|;
155 for ($j = 0; $j <= 9; $j++){
156 print qq| <option value="$j"|;
157 if ($steuernummer ne ''){
158 if ( $j eq substr($stnr, length($part) + $k, 1) ) {
162 print qq|>$j</option>\n|;
165 print qq|</select>\n|;
168 $main::lxdebug->leave_sub();
172 $main::lxdebug->enter_sub();
177 # Referenz wird übergeben, hash of hash wird nicht
178 # in neues Hash kopiert, sondern direkt über die Referenz verändert
179 # Prototyp für diese Konstruktion
181 my ($land, $elsterFFFF, $elster_init) = @_; #Referenz auf Hash von Hash übergeben
183 my $FFFF=$elsterFFFF;
186 $checked='checked' if ($elsterFFFF eq '' and $land eq '');
187 #if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
188 #$terminal='mozilla';
189 #} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
194 #if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
197 <script language="Javascript">
198 function update_auswahl()
200 var elsterBLAuswahl = document.verzeichnis.elsterland_new;
201 var elsterFAAuswahl = document.verzeichnis.elsterFFFF_new;
203 elsterFAAuswahl.options.length = 0; // dropdown aufräumen
207 foreach $elster_land ( sort keys %$elster_init ) {
209 if (elsterBLAuswahl.options[elsterBLAuswahl.selectedIndex].
210 value == "$elster_land")
214 my %elster_land_fa =();
216 for $FFFF ( keys %{ $elster_init->{$elster_land} } ) {
217 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
219 foreach $ffff ( sort {
220 $elster_land_fa{$a} cmp $elster_land_fa{$b}
221 } keys(%elster_land_fa)) {
223 elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
239 <select size="1" name="elsterland_new" onchange="update_auswahl()">|;
241 print qq|<option value="Auswahl" $checked>hier auswählen...</option>\n|;
243 foreach $elster_land ( sort keys %$elster_init ) {
245 <option value="$elster_land"|;
246 if ($elster_land eq $land and $checked eq '') {
249 print qq|>$elster_land</option>
257 my $elster_land = '';
258 $elster_land = ( $land ne '' ) ? $land:'';
260 for $FFFF ( keys % { $elster_init->{$elster_land} } ) {
261 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0]
269 <select size="1" name="elsterFFFF_new">|;
270 if ($elsterFFFF eq ''){
271 print qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
273 foreach $ffff ( sort {
274 $elster_land_fa{$a} cmp $elster_land_fa{$b}
275 } keys(%elster_land_fa)) {
278 <option value="$ffff"|;
279 if ( $ffff eq $elsterFFFF and $checked eq '') {
282 print qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
291 $main::lxdebug->leave_sub();
295 $main::lxdebug->enter_sub();
299 if ($ENV{HTTP_USER_AGENT}) {
302 print qq|<body><h2 class=info>Hinweis</h2>
308 <input type=button value="zurück" onClick="history.go(-1)">
316 if ($form->{error_function}) {
317 &{ $form->{error_function} }($msg);
319 die "Hinweis: $msg\n";
323 $main::lxdebug->leave_sub();
327 $main::lxdebug->enter_sub();
330 # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
331 # den Termin der nächsten USTVA anzeigt.
334 my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;
338 $today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
344 $yymmdd = "$year$month$day" * 1;
345 $mmdd = "$month$day" * 1;
348 #$ical = '...vcal format';
350 #if ($FA_voranmeld eq 'month'){
352 %liste = ("0110" => 'December',
354 "0310" => 'February',
361 "1010" => 'September',
368 $month += 1 if ( $day > 10 );
369 $month = sprintf("%02d", $month);
370 $stichtag = $year.$month."10";
371 $ust_va = $month."10";
374 foreach $date ( %liste ){
375 $ust_va = $liste{$date} if ($date eq $stichtag);
379 #} elsif ($FA_voranmeld eq 'quarter'){
384 #@stichtag = ('10.04.2004', '10.05.2004');
386 #@liste = ['0110', '0210', '0310', '0410', '0510', '0610', '0710', '0810', '0910',
387 # '1010', '1110', '1210', ];
389 #foreach $key (@liste){
390 # #if ($ddmm < ('0110' * 1));
392 # $stichtag = $liste[$key - 1] if ($ddmm > $key);
396 #$stichtag =~ /([\d]\d)(\d\d)$/
397 #$stichtag = "$1.$2.$yy"
399 $main::lxdebug->leave_sub();
400 return ($stichtag, $description, $tage_bis, $ical);
403 sub query_finanzamt {
404 $main::lxdebug->enter_sub();
407 my ($myconfig, $form) =@_;
408 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
409 #Test, if table finanzamt exist
410 my $table= 'finanzamt';
411 my $filename= "sql/$table.sql";
413 my $tst = $dbh->prepare("SELECT * FROM $table");
416 #There is no table, read the table from sql/finanzamt.sql
417 print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
418 $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
419 process_query($form, $dbh, $filename)||$self->error(DBI->errstr);
420 #execute second last call
421 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
437 'FA_PLZ_Grosskunden', # 9
438 'FA_PLZ_Postfach', # 10
441 'FA_Kontonummer_1', # 13
442 'FA_Bankbezeichnung_1', # 14
443 #'FA_BankIBAN_1', # 15
444 #'FA_BankBIC_1', # 16
445 #'FA_BankInhaber_BUFA_Nr_1', # 17
447 'FA_Kontonummer_2', # 19
448 'FA_Bankbezeichnung_2', # 20
449 #'FA_BankIBAN_2', # 21
450 #'FA_BankBIC_2', # 22
451 #'FA_BankInhaber_BUFA_Nr_2', # 23
452 'FA_Oeffnungszeiten', # 24
455 #'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
456 #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
459 my $field = join(', ', @vars);
461 my $query = "SELECT $field FROM finanzamt ORDER BY FA_Land_nr";
462 my $sth = $dbh->prepare($query) or $self->error($dbh->errstr);
463 $sth->execute || $form->dberror($query);
464 my $array_ref = $sth->fetchall_arrayref( );
466 foreach my $row ( @$array_ref ) {
467 my $FA_finanzamt = $row;
468 $land = 'Schleswig Holstein' if ( @$FA_finanzamt[0] eq '1' );
469 $land = 'Hamburg' if ( @$FA_finanzamt[0] eq '2' );
470 $land = 'Niedersachsen' if ( @$FA_finanzamt[0] eq '3' );
471 $land = 'Bremen' if ( @$FA_finanzamt[0] eq '4' );
472 $land = 'Nordrhein Westfalen' if ( @$FA_finanzamt[0] eq '5' );
473 $land = 'Hessen' if ( @$FA_finanzamt[0] eq '6' );
474 $land = 'Rheinland Pfalz' if ( @$FA_finanzamt[0] eq '7' );
475 $land = 'Baden Würtemberg' if ( @$FA_finanzamt[0] eq '8' );
476 $land = 'Bayern' if ( @$FA_finanzamt[0] eq '9' );
477 $land = 'Saarland' if ( @$FA_finanzamt[0] eq '10' );
478 $land = 'Berlin' if ( @$FA_finanzamt[0] eq '11' );
479 $land = 'Brandenburg' if ( @$FA_finanzamt[0] eq '12' );
480 $land = 'Mecklenburg Vorpommern' if ( @$FA_finanzamt[0] eq '13' );
481 $land = 'Sachsen' if ( @$FA_finanzamt[0] eq '14' );
482 $land = 'Sachsen Anhalt' if ( @$FA_finanzamt[0] eq '15' );
483 $land = 'Thüringen' if ( @$FA_finanzamt[0] eq '16' );
485 my $ffff = @$FA_finanzamt[1];
488 $rec->{$land} = $ffff;
493 $finanzamt{$land}{$ffff} = [ @$FA_finanzamt ] ;
499 $main::lxdebug->leave_sub();
506 $main::lxdebug->enter_sub();
508 # Copyright D. Simander -> SL::Form under Gnu GPL.
509 my ($form, $dbh, $filename) = @_;
511 # return unless (-f $filename);
513 open(FH, "$filename") or $form->error("$filename : $!\n");
519 # Remove DOS and Unix style line endings.
522 # don't add comments or empty lines
523 next if /^(--.*|\s+)$/;
525 for (my $i = 0; $i < length($_); $i++) {
526 my $char = substr($_, $i, 1);
528 # Are we inside a string?
530 if ($char eq $quote_chars[-1]) {
536 if (($char eq "'") || ($char eq "\"")) {
537 push(@quote_chars, $char);
539 } elsif ($char eq ";") {
540 # Query is complete. Send it.
542 $sth = $dbh->prepare($query);
543 $sth->execute || $form->dberror($query);
557 $main::lxdebug->leave_sub();