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 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
604 $form->{"51r"} = $form->{"511"};
605 $form->{"86r"} = $form->{"861"};
606 $form->{"97r"} = $form->{"971"};
607 $form->{"93r"} = $form->{"931"};
608 $form->{"Z43"} = $form->{"511"} + $form->{"861"} + $form->{"36"}
609 + $form->{"80"} + $form->{"971"} + $form->{"931"}
610 + $form->{"96"} + $form->{"98"};
611 $form->{"Z45"} = $form->{"Z43"};
612 $form->{"Z53"} = $form->{"Z43"};
613 $form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"}
614 - $form->{"62"} - $form->{"63"} - $form->{"64"}
616 $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
617 $form->{"83"} = $form->{"Z65"} - $form->{"39"};
618 # Hier fehlen moeglicherweise noch einige Berechnungen!
622 $main::lxdebug->leave_sub();
625 sub get_accounts_ustva {
626 $main::lxdebug->enter_sub();
628 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
630 my ($null, $department_id) = split /--/, $form->{department};
645 if ($form->{method} eq 'cash') {
646 $subwhere .= " AND transdate >= '$fromdate'";
647 $glwhere = " AND ac.transdate >= '$fromdate'";
648 $ARwhere .= " AND acc.transdate >= '$fromdate'";
649 $APwhere .= " AND AP.transdate >= '$fromdate'";
651 $where .= " AND ac.transdate >= '$fromdate'";
655 $where .= " AND ac.transdate <= '$todate'";
656 $ARwhere .= " AND acc.transdate <= '$todate'";
657 $subwhere .= " AND transdate <= '$todate'";
658 $APwhere .= " AND AP.transdate <= '$todate'";
661 if ($department_id) {
663 JOIN department t ON (a.department_id = t.id)
666 AND t.id = $department_id
670 if ($form->{project_id}) {
672 AND ac.project_id = $form->{project_id}
675 #########################################
676 # Method eq 'cash' = IST Versteuerung
677 #########################################
679 if ($form->{method} eq 'cash') {
683 -- Alle tatsaechlichen Zahlungseingaenge
684 -- im Voranmeldezeitraum erfassen
685 -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
687 -- Bezahlt / Rechnungssumme
689 SELECT SUM(acc.amount)
691 INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%')
695 AND acc.trans_id = ac.trans_id
699 select amount from ar where id = ac.trans_id
704 JOIN chart c ON (c.id = ac.chart_id)
705 --JOIN ar ON (ar.id = ac.trans_id)
708 -- Here no where, please. All Transactions ever should be
709 -- testet if they are paied in the USTVA report period.
712 UNION -- alle Ausgaben AP erfassen
715 sum(ac.amount) AS amount, pos_ustva
717 JOIN AP ON (AP.id = ac.trans_id )
718 JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '')
726 UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
730 CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
731 WHEN c.link LIKE '%AP%' THEN ac.amount * 1
733 ) AS amount, c.$category
735 JOIN chart c ON (c.id = ac.chart_id)
736 JOIN gl a ON (a.id = ac.trans_id)
740 AND NOT (c.link = 'AR' OR c.link = 'AP')
746 #########################################
747 # Method eq 'accrual' = Soll Versteuerung
748 #########################################
750 if ($department_id) {
752 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
755 AND t.department_id = $department_id
762 CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
763 WHEN c.link LIKE '%AP%' THEN ac.amount * 1
765 ) AS amount, c.$category
767 JOIN chart c ON (c.id = ac.chart_id)
780 # Show all $query in Debuglevel LXDebug::QUERY
781 $callingdetails = (caller (0))[3];
782 $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
784 my $sth = $dbh->prepare($query);
785 $sth->execute || $form->dberror($query);
787 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
789 $ref->{amount} *= -1;
790 if ($category eq "pos_bwa") {
792 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
794 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
797 $form->{ $ref->{$category} } += $ref->{amount};
802 $main::lxdebug->leave_sub();