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();
31 $part = $form->{part};
32 $patterncount = $form->{patterncount};
33 $delimiter = $form->{delimiter};
34 $elster_pattern = $form->{elster_pattern};
36 # rebuild steuernummer and elstersteuernummer
37 # es gibt eine gespeicherte steuernummer $form->{steuernummer}
38 # und die parts und delimiter
43 $steuernummer_new = $part;
44 $elstersteuernummer_new = $elster_FFFF;
45 $elstersteuernummer_new .= '0';
47 for ($h = 1; $h < $patterncount; $h++) {
48 $steuernummer_new .= qq|$delimiter|;
49 for ($i = 1; $i <= length($elster_pattern); $i++) {
50 $steuernummer_new .= $form->{"part_$h\_$i"};
51 $elstersteuernummer_new .= $form->{"part_$h\_$i"};
54 if ($form->{steuernummer} ne $steuernummer_new) {
55 $form->{steuernummer} = $steuernummer_new;
56 $form->{elstersteuernummer} = $elstersteuernummer_new;
57 $form->{steuernummer_new} = $steuernummer_new;
59 $main::lxdebug->leave_sub();
60 return ($steuernummer_new, $elstersteuernummer_new);
63 sub steuernummer_input {
64 $main::lxdebug->enter_sub();
66 my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;
68 my $steuernummer_input = '';
70 $elster_land = $elsterland;
71 $elster_FFFF = $elsterFFFF;
72 $steuernummer = '0000000000' if ($steuernummer eq '');
74 # $steuernummer formatieren (nur Zahlen) -> $stnr
75 my $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 my $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) {
104 @elster_pattern = ();
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 $steuernummer_input .= qq|<b><font size="+1">|;
116 $elster_pattern[0] eq 'FFF' && do {
117 $part = substr($elster_FFFF, 1, 4);
118 $steuernummer_input .= qq|$part|;
121 $elster_pattern[0] eq '0FF' && do {
122 $part = '0' . substr($elster_FFFF, 2, 4);
123 $steuernummer_input .= qq|$part|;
126 $elster_pattern[0] eq 'FF' && do {
127 $part = substr($elster_FFFF, 2, 4);
128 $steuernummer_input .= qq|$part|;
132 $steuernummer_input .= qq|Fehler!|;
137 #now the rest of the Steuernummer ...
138 $steuernummer_input .= qq|</b></font>|;
139 $steuernummer_input .= qq|\n
140 <input type=hidden name="elster_pattern" value="$elster_pattern">
141 <input type=hidden name="patterncount" value="$patterncount">
142 <input type=hidden name="patternlength" value="$patterncount">
143 <input type=hidden name="delimiter" value="$delimiter">
144 <input type=hidden name="part" value="$part">
149 for (my $h = 1; $h < $patterncount; $h++) {
150 $steuernummer_input .= qq| $delimiter \n|;
151 for (my $i = 1; $i <= length($elster_pattern[$h]); $i++) {
152 $steuernummer_input .= qq|<select name="part_$h\_$i">\n|;
154 for (my $j = 0; $j <= 9; $j++) {
155 $steuernummer_input .= qq| <option value="$j"|;
156 if ($steuernummer ne '') {
157 if ($j eq substr($stnr, length($part) + $k, 1)) {
158 $steuernummer_input .= qq| selected|;
161 $steuernummer_input .= qq|>$j</option>\n|;
164 $steuernummer_input .= qq|</select>\n|;
168 $main::lxdebug->leave_sub();
170 return $steuernummer_input;
174 $main::lxdebug->enter_sub();
178 # Referenz wird übergeben, hash of hash wird nicht
179 # in neues Hash kopiert, sondern direkt über die Referenz verändert
180 # Prototyp für diese Konstruktion
182 my ($self, $land, $elsterFFFF, $elster_init) = @_;
185 my $FFFF = $elsterFFFF;
188 $checked = 'checked' if ($elsterFFFF eq '' and $land eq '');
190 #if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
191 #$terminal='mozilla';
192 #} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
196 #if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
198 <script language="Javascript">
199 function update_auswahl()
201 var elsterBLAuswahl = document.verzeichnis.elsterland_new;
202 var elsterFAAuswahl = document.verzeichnis.elsterFFFF_new;
204 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 { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
220 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 $fa_auswahl .= 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 '') {
247 $fa_auswahl .= qq| selected|;
249 $fa_auswahl .= qq|>$elster_land</option>
257 my $elster_land = '';
258 $elster_land = ($land ne '') ? $land : '';
259 %elster_land_fa = ();
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 $fa_auswahl .= qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
273 foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
274 keys(%elster_land_fa)
278 <option value="$ffff"|;
279 if ($ffff eq $elsterFFFF and $checked eq '') {
280 $fa_auswahl .= qq| selected|;
282 $fa_auswahl .= qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
291 $main::lxdebug->leave_sub();
296 $main::lxdebug->enter_sub();
300 if ($ENV{HTTP_USER_AGENT}) {
303 print qq|<body><h2 class=info>Hinweis</h2>
309 <input type=button value="zurück" onClick="history.go(-1)">
317 if ($form->{error_function}) {
318 &{ $form->{error_function} }($msg);
320 die "Hinweis: $msg\n";
324 $main::lxdebug->leave_sub();
328 $main::lxdebug->enter_sub();
331 # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
332 # den Termin der nächsten USTVA anzeigt.
335 my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;
340 $today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
346 $yymmdd = "$year$month$day" * 1;
347 $mmdd = "$month$day" * 1;
351 #$ical = '...vcal format';
353 #if ($FA_voranmeld eq 'month'){
355 %liste = ("0110" => 'December',
357 "0310" => 'February',
364 "1010" => 'September',
366 "1210" => 'November');
370 $month += 1 if ($day > 10);
371 $month = sprintf("%02d", $month);
372 $stichtag = $year . $month . "10";
373 $ust_va = $month . "10";
375 foreach $date (%liste) {
376 $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();
406 my ($self, $myconfig, $form) = @_;
408 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
410 #Test, if table finanzamt exist
411 my $table = 'finanzamt';
412 my $filename = "sql/$table.sql";
414 my $tst = $dbh->prepare("SELECT * FROM $table");
418 #There is no table, read the table from sql/finanzamt.sql
419 print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
420 $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
421 process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
423 #execute second last call
424 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
441 'FA_PLZ_Grosskunden', # 9
442 'FA_PLZ_Postfach', # 10
445 'FA_Kontonummer_1', # 13
446 'FA_Bankbezeichnung_1', # 14
447 #'FA_BankIBAN_1', # 15
448 #'FA_BankBIC_1', # 16
449 #'FA_BankInhaber_BUFA_Nr_1', # 17
451 'FA_Kontonummer_2', # 19
452 'FA_Bankbezeichnung_2', # 20
453 #'FA_BankIBAN_2', # 21
454 #'FA_BankBIC_2', # 22
455 #'FA_BankInhaber_BUFA_Nr_2', # 23
456 'FA_Oeffnungszeiten', # 24
459 #'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
460 #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
463 my $field = join(', ', @vars);
465 my $query = "SELECT $field FROM finanzamt ORDER BY FA_Land_nr";
466 my $sth = $dbh->prepare($query) or $self->error($dbh->errstr);
467 $sth->execute || $form->dberror($query);
468 my $array_ref = $sth->fetchall_arrayref();
470 foreach my $row (@$array_ref) {
471 my $FA_finanzamt = $row;
472 $land = 'Schleswig Holstein' if (@$FA_finanzamt[0] eq '1');
473 $land = 'Hamburg' if (@$FA_finanzamt[0] eq '2');
474 $land = 'Niedersachsen' if (@$FA_finanzamt[0] eq '3');
475 $land = 'Bremen' if (@$FA_finanzamt[0] eq '4');
476 $land = 'Nordrhein Westfalen' if (@$FA_finanzamt[0] eq '5');
477 $land = 'Hessen' if (@$FA_finanzamt[0] eq '6');
478 $land = 'Rheinland Pfalz' if (@$FA_finanzamt[0] eq '7');
479 $land = 'Baden Würtemberg' if (@$FA_finanzamt[0] eq '8');
480 $land = 'Bayern' if (@$FA_finanzamt[0] eq '9');
481 $land = 'Saarland' if (@$FA_finanzamt[0] eq '10');
482 $land = 'Berlin' if (@$FA_finanzamt[0] eq '11');
483 $land = 'Brandenburg' if (@$FA_finanzamt[0] eq '12');
484 $land = 'Mecklenburg Vorpommern' if (@$FA_finanzamt[0] eq '13');
485 $land = 'Sachsen' if (@$FA_finanzamt[0] eq '14');
486 $land = 'Sachsen Anhalt' if (@$FA_finanzamt[0] eq '15');
487 $land = 'Thüringen' if (@$FA_finanzamt[0] eq '16');
489 my $ffff = @$FA_finanzamt[1];
492 $rec->{$land} = $ffff;
497 $finanzamt{$land}{$ffff} = [@$FA_finanzamt];
503 $main::lxdebug->leave_sub();
509 $main::lxdebug->enter_sub();
511 # Copyright D. Simander -> SL::Form under Gnu GPL.
512 my ($form, $dbh, $filename) = @_;
514 # return unless (-f $filename);
516 open(FH, "$filename") or $form->error("$filename : $!\n");
523 # Remove DOS and Unix style line endings.
526 # don't add comments or empty lines
527 next if /^(--.*|\s+)$/;
529 for (my $i = 0; $i < length($_); $i++) {
530 my $char = substr($_, $i, 1);
532 # Are we inside a string?
534 if ($char eq $quote_chars[-1]) {
540 if (($char eq "'") || ($char eq "\"")) {
541 push(@quote_chars, $char);
543 } elsif ($char eq ";") {
545 # Query is complete. Send it.
547 $sth = $dbh->prepare($query);
548 $sth->execute || $form->dberror($query);
562 $main::lxdebug->leave_sub();
566 $main::lxdebug->enter_sub();
568 my ($self, $myconfig, $form) = @_;
570 # connect to database
571 my $dbh = $form->dbconnect($myconfig);
574 my $category = "pos_ustva";
575 my @category_cent = qw(
576 511 861 36 80 971 931 98 96 53 74
577 85 65 66 61 62 67 63 64 59 69
578 39 83 811 891 Z43 Z45 Z53 Z62 Z65 Z67
581 my @category_euro = qw(
588 $form->{decimalplaces} *= 1;
590 foreach $item (@category_cent) {
591 $form->{"$item"} = 0;
593 foreach $item (@category_euro) {
594 $form->{"$item"} = 0;
597 &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
601 # Berechnung der USTVA Formularfelder laut Bogen 207
604 $form->{"51r"} = $form->{"511"};
605 $form->{"86r"} = $form->{"861"};
606 $form->{"97r"} = $form->{"971"};
607 $form->{"93r"} = $form->{"931"};
609 $form->{"Z43"} = $form->{"511"} + $form->{"811"} + $form->{"861"}
610 + $form->{"36"} + $form->{"80"} + $form->{"971"}
611 + $form->{"891"} + $form->{"931"} + $form->{"96"}
614 $form->{"Z45"} = $form->{"Z43"};
616 $form->{"Z53"} = $form->{"Z45"} + $form->{"53"} + $form->{"74"}
617 + $form->{"85"} + $form->{"65"};
619 $form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"}
620 - $form->{"62"} - $form->{"67"} - $form->{"63"}
621 - $form->{"64"} - $form->{"59"};
623 $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
624 $form->{"83"} = $form->{"Z65"} - $form->{"39"};
628 $main::lxdebug->leave_sub();
631 sub get_accounts_ustva {
632 $main::lxdebug->enter_sub();
634 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
636 my ($null, $department_id) = split /--/, $form->{department};
651 if ($form->{method} eq 'cash') {
652 $subwhere .= " AND transdate >= '$fromdate'";
653 $glwhere = " AND ac.transdate >= '$fromdate'";
654 $ARwhere .= " AND acc.transdate >= '$fromdate'";
655 $APwhere .= " AND AP.transdate >= '$fromdate'";
657 $where .= " AND ac.transdate >= '$fromdate'";
661 $where .= " AND ac.transdate <= '$todate'";
662 $ARwhere .= " AND acc.transdate <= '$todate'";
663 $subwhere .= " AND transdate <= '$todate'";
664 $APwhere .= " AND AP.transdate <= '$todate'";
667 if ($department_id) {
669 JOIN department t ON (a.department_id = t.id)
672 AND t.id = $department_id
676 if ($form->{project_id}) {
678 AND ac.project_id = $form->{project_id}
681 #########################################
682 # Method eq 'cash' = IST Versteuerung
683 #########################################
685 if ($form->{method} eq 'cash') {
689 -- Alle tatsaechlichen Zahlungseingaenge
690 -- im Voranmeldezeitraum erfassen
691 -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
693 -- Bezahlt / Rechnungssumme
695 SELECT SUM(acc.amount)
697 INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%')
701 AND acc.trans_id = ac.trans_id
705 select amount from ar where id = ac.trans_id
710 JOIN chart c ON (c.id = ac.chart_id)
711 --JOIN ar ON (ar.id = ac.trans_id)
714 -- Here no where, please. All Transactions ever should be
715 -- testet if they are paied in the USTVA report period.
718 UNION -- alle Ausgaben AP erfassen
721 sum(ac.amount) AS amount, pos_ustva
723 JOIN AP ON (AP.id = ac.trans_id )
724 JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '')
732 UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
736 CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
737 WHEN c.link LIKE '%AP%' THEN ac.amount * 1
739 ) AS amount, c.$category
741 JOIN chart c ON (c.id = ac.chart_id)
742 JOIN gl a ON (a.id = ac.trans_id)
746 AND NOT (c.link = 'AR' OR c.link = 'AP')
752 #########################################
753 # Method eq 'accrual' = Soll Versteuerung
754 #########################################
756 if ($department_id) {
758 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
761 AND t.department_id = $department_id
768 CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
769 WHEN c.link LIKE '%AP%' THEN ac.amount * 1
771 ) AS amount, c.$category
773 JOIN chart c ON (c.id = ac.chart_id)
786 # Show all $query in Debuglevel LXDebug::QUERY
787 $callingdetails = (caller (0))[3];
788 $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
790 my $sth = $dbh->prepare($query);
791 $sth->execute || $form->dberror($query);
793 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
795 $ref->{amount} *= -1;
796 if ($category eq "pos_bwa") {
798 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
800 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
803 $form->{ $ref->{$category} } += $ref->{amount};
808 $main::lxdebug->leave_sub();