Projekt

Allgemein

Profil

Herunterladen (24,6 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/USTVA.pm @ 5e59cc54

d319704a Moritz Bunkus
#=====================================================================
# Lx-Office ERP
# Copyright (c) 2004 by Udo Spallek, Aachen
#
# Author: Udo Spallek
# Email: udono@gmx.net
# Web: http://www.lx-office.org
#
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#======================================================================
# Utilities for ustva
#=====================================================================

package USTVA;

sub create_steuernummer {
$main::lxdebug->enter_sub();

$part = $form->{part};
$patterncount = $form->{patterncount};
$delimiter = $form->{delimiter};
$elster_pattern = $form->{elster_pattern};

# rebuild steuernummer and elstersteuernummer
# es gibt eine gespeicherte steuernummer $form->{steuernummer}
# und die parts und delimiter

my $h = 0;
my $i = 0;

$steuernummer_new = $part;
$elstersteuernummer_new = $elster_FFFF;
$elstersteuernummer_new .= '0';

for ($h = 1; $h < $patterncount; $h++) {
$steuernummer_new .= qq|$delimiter|;
for ($i = 1; $i <= length($elster_pattern); $i++) {
$steuernummer_new .= $form->{"part_$h\_$i"};
$elstersteuernummer_new .= $form->{"part_$h\_$i"};
}
}
if ($form->{steuernummer} ne $steuernummer_new) {
$form->{steuernummer} = $steuernummer_new;
$form->{elstersteuernummer} = $elstersteuernummer_new;
$form->{steuernummer_new} = $steuernummer_new;
}
$main::lxdebug->leave_sub();
return ($steuernummer_new, $elstersteuernummer_new);
}

sub steuernummer_input {
$main::lxdebug->enter_sub();

a205ed21 Udo Spallek
my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;
d319704a Moritz Bunkus
a205ed21 Udo Spallek
my $steuernummer_input = '';
d319704a Moritz Bunkus
$elster_land = $elsterland;
$elster_FFFF = $elsterFFFF;
$steuernummer = '0000000000' if ($steuernummer eq '');

# $steuernummer formatieren (nur Zahlen) -> $stnr
a205ed21 Udo Spallek
my $stnr = $steuernummer;
d319704a Moritz Bunkus
$stnr =~ s/\D+//g;

#Pattern description Elstersteuernummer
my %elster_STNRformat = (
a205ed21 Udo Spallek
'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3
'Hessen' => '0FF BBB UUUUP', # ' ' 3
'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3
'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2
'Berlin' => 'FF/BBB/UUUUP', # '/' 3
'Th?ringen' => 'FFF/BBB/UUUUP', # '/' 3
'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3
'Hamburg' => 'FF/BBB/UUUUP', # '/' 3
'Baden W?rtemberg' => 'FF/BBB/UUUUP', # '/' 2
'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3
'Saarland' => 'FFF/BBB/UUUUP', # '/' 3
'Bremen' => 'FF BBB UUUUP', # ' ' 3
'Bayern' => 'FFF/BBB/UUUUP', # '/' 3
'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4
'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3
'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3
d319704a Moritz Bunkus
);

#split the pattern
a205ed21 Udo Spallek
my $elster_pattern = $elster_STNRformat{$elster_land};
d319704a Moritz Bunkus
my @elster_pattern = split(' ', $elster_pattern);
my $delimiter = '&nbsp;';
my $patterncount = @elster_pattern;
if ($patterncount < 2) {
@elster_pattern = ();
@elster_pattern = split('/', $elster_pattern);
$delimiter = '/';
$patterncount = @elster_pattern;
}

# no we have an array of patternparts and a delimiter
# create the first automated and fixed part and delimiter

a205ed21 Udo Spallek
$steuernummer_input .= qq|<b><font size="+1">|;
my $part = '';
d319704a Moritz Bunkus
SWITCH: {
$elster_pattern[0] eq 'FFF' && do {
$part = substr($elster_FFFF, 1, 4);
a205ed21 Udo Spallek
$steuernummer_input .= qq|$part|;
d319704a Moritz Bunkus
last SWITCH;
};
$elster_pattern[0] eq '0FF' && do {
$part = '0' . substr($elster_FFFF, 2, 4);
a205ed21 Udo Spallek
$steuernummer_input .= qq|$part|;
d319704a Moritz Bunkus
last SWITCH;
};
$elster_pattern[0] eq 'FF' && do {
$part = substr($elster_FFFF, 2, 4);
a205ed21 Udo Spallek
$steuernummer_input .= qq|$part|;
d319704a Moritz Bunkus
last SWITCH;
};
1 == 1 && do {
a205ed21 Udo Spallek
$steuernummer_input .= qq|Fehler!|;
d319704a Moritz Bunkus
last SWITCH;
};
}

#now the rest of the Steuernummer ...
a205ed21 Udo Spallek
$steuernummer_input .= qq|</b></font>|;
$steuernummer_input .= qq|\n
d319704a Moritz Bunkus
<input type=hidden name="elster_pattern" value="$elster_pattern">
<input type=hidden name="patterncount" value="$patterncount">
<input type=hidden name="patternlength" value="$patterncount">
<input type=hidden name="delimiter" value="$delimiter">
0576299f Moritz Bunkus
<input type=hidden name="part" value="$part">
d319704a Moritz Bunkus
|;

a205ed21 Udo Spallek
my $k = 0;
d319704a Moritz Bunkus
a205ed21 Udo Spallek
for (my $h = 1; $h < $patterncount; $h++) {
$steuernummer_input .= qq|&nbsp;$delimiter&nbsp;\n|;
for (my $i = 1; $i <= length($elster_pattern[$h]); $i++) {
$steuernummer_input .= qq|<select name="part_$h\_$i">\n|;

for (my $j = 0; $j <= 9; $j++) {
$steuernummer_input .= qq| <option value="$j"|;
d319704a Moritz Bunkus
if ($steuernummer ne '') {
if ($j eq substr($stnr, length($part) + $k, 1)) {
a205ed21 Udo Spallek
$steuernummer_input .= qq| selected|;
d319704a Moritz Bunkus
}
}
a205ed21 Udo Spallek
$steuernummer_input .= qq|>$j</option>\n|;
d319704a Moritz Bunkus
}
$k++;
a205ed21 Udo Spallek
$steuernummer_input .= qq|</select>\n|;
d319704a Moritz Bunkus
}
}
a205ed21 Udo Spallek
d319704a Moritz Bunkus
$main::lxdebug->leave_sub();
a205ed21 Udo Spallek
return $steuernummer_input;
d319704a Moritz Bunkus
}

sub fa_auswahl {
$main::lxdebug->enter_sub();

a205ed21 Udo Spallek
# use SL::Form;
d319704a Moritz Bunkus
# Referenz wird ?bergeben, hash of hash wird nicht
# in neues Hash kopiert, sondern direkt ?ber die Referenz ver?ndert
# Prototyp f?r diese Konstruktion

a205ed21 Udo Spallek
my ($self, $land, $elsterFFFF, $elster_init) = @_;
d319704a Moritz Bunkus
my $terminal = '';
my $FFFF = $elsterFFFF;
my $ffff = '';
my $checked = '';
$checked = 'checked' if ($elsterFFFF eq '' and $land eq '');

#if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
#$terminal='mozilla';
#} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
#$terminal = 'lynx';
#}

#if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
a205ed21 Udo Spallek
my $fa_auswahl = qq|
d319704a Moritz Bunkus
<script language="Javascript">
function update_auswahl()
{
var elsterBLAuswahl = document.verzeichnis.elsterland_new;
var elsterFAAuswahl = document.verzeichnis.elsterFFFF_new;

elsterFAAuswahl.options.length = 0; // dropdown aufr?umen
|;

foreach $elster_land (sort keys %$elster_init) {
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
if (elsterBLAuswahl.options[elsterBLAuswahl.selectedIndex].
value == "$elster_land")
{
|;
my $j = 0;
my %elster_land_fa = ();
$FFFF = '';
for $FFFF (keys %{ $elster_init->{$elster_land} }) {
$elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
}
foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
081a4f97 Moritz Bunkus
keys(%elster_land_fa)
) {
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
$j++;
}
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
}|;
}
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
}
</script>

<table width="100%">
<tr>
<td>
Bundesland
</td>
<td>
<select size="1" name="elsterland_new" onchange="update_auswahl()">|;
if ($land eq '') {
a205ed21 Udo Spallek
$fa_auswahl .= qq|<option value="Auswahl" $checked>hier ausw?hlen...</option>\n|;
d319704a Moritz Bunkus
}
foreach $elster_land (sort keys %$elster_init) {
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
<option value="$elster_land"|;
if ($elster_land eq $land and $checked eq '') {
a205ed21 Udo Spallek
$fa_auswahl .= qq| selected|;
d319704a Moritz Bunkus
}
a205ed21 Udo Spallek
$fa_auswahl .= qq|>$elster_land</option>
d319704a Moritz Bunkus
|;
}
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
</td>
</tr>
|;

my $elster_land = '';
$elster_land = ($land ne '') ? $land : '';
%elster_land_fa = ();
for $FFFF (keys %{ $elster_init->{$elster_land} }) {
$elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
}

a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
<tr>
<td>Finanzamt
0576299f Moritz Bunkus
</td>
d319704a Moritz Bunkus
<td>
<select size="1" name="elsterFFFF_new">|;
if ($elsterFFFF eq '') {
a205ed21 Udo Spallek
$fa_auswahl .= qq|<option value="Auswahl" $checked>hier ausw?hlen...</option>|;
d319704a Moritz Bunkus
} else {
foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
081a4f97 Moritz Bunkus
keys(%elster_land_fa)
) {
d319704a Moritz Bunkus
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
<option value="$ffff"|;
if ($ffff eq $elsterFFFF and $checked eq '') {
a205ed21 Udo Spallek
$fa_auswahl .= qq| selected|;
d319704a Moritz Bunkus
}
a205ed21 Udo Spallek
$fa_auswahl .= qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
d319704a Moritz Bunkus
}
}
a205ed21 Udo Spallek
$fa_auswahl .= qq|
d319704a Moritz Bunkus
</td>
</tr>
0576299f Moritz Bunkus
</table>
d319704a Moritz Bunkus
</select>|;

$main::lxdebug->leave_sub();
a205ed21 Udo Spallek
return $fa_auswahl;
d319704a Moritz Bunkus
}

sub info {
$main::lxdebug->enter_sub();

my $msg = $_[0];

if ($ENV{HTTP_USER_AGENT}) {
$msg =~ s/\n/<br>/g;

print qq|<body><h2 class=info>Hinweis</h2>

<p><b>$msg</b>
<br>
<br>
<hr>
<input type=button value="zur?ck" onClick="history.go(-1)">
</body>
|;

exit;

} else {

if ($form->{error_function}) {
&{ $form->{error_function} }($msg);
} else {
die "Hinweis: $msg\n";
}
}

$main::lxdebug->leave_sub();
}

sub stichtag {
$main::lxdebug->enter_sub();

# noch nicht fertig
# soll mal eine Erinnerungsfunktion f?r USTVA Abgaben werden, die automatisch
# den Termin der n?chsten USTVA anzeigt.
#
#
my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;

#$today zerlegen:

#$today =today * 1;
$today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
$year = $1;
$month = $2;
$day = $3;
$yy = $year;
$mm = $month;
$yymmdd = "$year$month$day" * 1;
$mmdd = "$month$day" * 1;
$stichtag = '';

#$tage_bis = '1234';
#$ical = '...vcal format';

#if ($FA_voranmeld eq 'month'){

%liste = ("0110" => 'December',
"0210" => 'January',
"0310" => 'February',
"0410" => 'March',
"0510" => 'April',
"0610" => 'May',
"0710" => 'June',
"0810" => 'July',
"0910" => 'August',
"1010" => 'September',
"1110" => 'October',
"1210" => 'November');

#$mm += $dauerfrist
#$month *= 1;
$month += 1 if ($day > 10);
$month = sprintf("%02d", $month);
$stichtag = $year . $month . "10";
$ust_va = $month . "10";

foreach $date (%liste) {
$ust_va = $liste{$date} if ($date eq $stichtag);
}

#} elsif ($FA_voranmeld eq 'quarter'){
#1;

#}

#@stichtag = ('10.04.2004', '10.05.2004');

#@liste = ['0110', '0210', '0310', '0410', '0510', '0610', '0710', '0810', '0910',
# '1010', '1110', '1210', ];
#
#foreach $key (@liste){
# #if ($ddmm < ('0110' * 1));
# if ($ddmm ){}
# $stichtag = $liste[$key - 1] if ($ddmm > $key);
#
#}
#
#$stichtag =~ /([\d]\d)(\d\d)$/
#$stichtag = "$1.$2.$yy"
#$stichtag=$1;
$main::lxdebug->leave_sub();
return ($stichtag, $description, $tage_bis, $ical);
}

sub query_finanzamt {
$main::lxdebug->enter_sub();

a205ed21 Udo Spallek
my ($self, $myconfig, $form) = @_;

d319704a Moritz Bunkus
my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);

#Test, if table finanzamt exist
my $table = 'finanzamt';
my $filename = "sql/$table.sql";

my $tst = $dbh->prepare("SELECT * FROM $table");
$tst->execute;
if ($DBI::err) {

#There is no table, read the table from sql/finanzamt.sql
0576299f Moritz Bunkus
print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
d319704a Moritz Bunkus
$myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugef?gt...</p>|;
process_query($form, $dbh, $filename) || $self->error(DBI->errstr);

#execute second last call
my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
$dbh->disconnect();
}
$tst->finish();

#$dbh->disconnect();

my @vars = (
'FA_Land_Nr', # 0
'FA_BUFA_Nr', # 1
#'FA_Verteiler', # 2
'FA_Name', # 3
'FA_Strasse', # 4
'FA_PLZ', # 5
'FA_Ort', # 6
'FA_Telefon', # 7
'FA_Fax', # 8
'FA_PLZ_Grosskunden', # 9
'FA_PLZ_Postfach', # 10
'FA_Postfach', # 11
'FA_BLZ_1', # 12
'FA_Kontonummer_1', # 13
'FA_Bankbezeichnung_1', # 14
#'FA_BankIBAN_1', # 15
#'FA_BankBIC_1', # 16
#'FA_BankInhaber_BUFA_Nr_1', # 17
'FA_BLZ_2', # 18
'FA_Kontonummer_2', # 19
'FA_Bankbezeichnung_2', # 20
#'FA_BankIBAN_2', # 21
#'FA_BankBIC_2', # 22
#'FA_BankInhaber_BUFA_Nr_2', # 23
'FA_Oeffnungszeiten', # 24
'FA_Email', # 25
'FA_Internet' # 26
#'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
#'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
);

my $field = join(', ', @vars);

my $query = "SELECT $field FROM finanzamt ORDER BY FA_Land_nr";
my $sth = $dbh->prepare($query) or $self->error($dbh->errstr);
$sth->execute || $form->dberror($query);
my $array_ref = $sth->fetchall_arrayref();
my $land = '';
foreach my $row (@$array_ref) {
my $FA_finanzamt = $row;
$land = 'Schleswig Holstein' if (@$FA_finanzamt[0] eq '1');
$land = 'Hamburg' if (@$FA_finanzamt[0] eq '2');
$land = 'Niedersachsen' if (@$FA_finanzamt[0] eq '3');
$land = 'Bremen' if (@$FA_finanzamt[0] eq '4');
$land = 'Nordrhein Westfalen' if (@$FA_finanzamt[0] eq '5');
$land = 'Hessen' if (@$FA_finanzamt[0] eq '6');
$land = 'Rheinland Pfalz' if (@$FA_finanzamt[0] eq '7');
$land = 'Baden W?rtemberg' if (@$FA_finanzamt[0] eq '8');
$land = 'Bayern' if (@$FA_finanzamt[0] eq '9');
$land = 'Saarland' if (@$FA_finanzamt[0] eq '10');
$land = 'Berlin' if (@$FA_finanzamt[0] eq '11');
$land = 'Brandenburg' if (@$FA_finanzamt[0] eq '12');
$land = 'Mecklenburg Vorpommern' if (@$FA_finanzamt[0] eq '13');
$land = 'Sachsen' if (@$FA_finanzamt[0] eq '14');
$land = 'Sachsen Anhalt' if (@$FA_finanzamt[0] eq '15');
$land = 'Th?ringen' if (@$FA_finanzamt[0] eq '16');

my $ffff = @$FA_finanzamt[1];

my $rec = {};
$rec->{$land} = $ffff;

shift @$row;
shift @$row;

$finanzamt{$land}{$ffff} = [@$FA_finanzamt];
}

$sth->finish();
$dbh->disconnect();

$main::lxdebug->leave_sub();

return \%finanzamt;
}

sub process_query {
$main::lxdebug->enter_sub();

# Copyright D. Simander -> SL::Form under Gnu GPL.
my ($form, $dbh, $filename) = @_;

# return unless (-f $filename);

open(FH, "$filename") or $form->error("$filename : $!\n");
my $query = "";
my $sth;
my @quote_chars;

while (<FH>) {

# Remove DOS and Unix style line endings.
s/[\r\n]//g;

# don't add comments or empty lines
next if /^(--.*|\s+)$/;

for (my $i = 0; $i < length($_); $i++) {
my $char = substr($_, $i, 1);

# Are we inside a string?
if (@quote_chars) {
if ($char eq $quote_chars[-1]) {
pop(@quote_chars);
}
$query .= $char;

} else {
if (($char eq "'") || ($char eq "\"")) {
push(@quote_chars, $char);

} elsif ($char eq ";") {

# Query is complete. Send it.

$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$sth->finish;

$char = "";
$query = "";
}

$query .= $char;
}
}
}

close FH;

$main::lxdebug->leave_sub();
}

82ee2234 Stephan Köhler
sub ustva {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form) = @_;

# connect to database
my $dbh = $form->dbconnect($myconfig);

my $last_period = 0;
my $category = "pos_ustva";
a205ed21 Udo Spallek
my @category_cent = qw(
785ef602 Udo Spallek
511 861 36 80 971 931 98 96 53 74
85 65 66 61 62 67 63 64 59 69
39 83 811 891 Z43 Z45 Z53 Z62 Z65 Z67
a205ed21 Udo Spallek
);
081a4f97 Moritz Bunkus
a205ed21 Udo Spallek
my @category_euro = qw(
41 44 49 43 48 51
86 35 77 76 91 97
93 95 94 42 60 45
785ef602 Udo Spallek
52 73 84 81 89
a205ed21 Udo Spallek
);
82ee2234 Stephan Köhler
$form->{decimalplaces} *= 1;

6ab3da45 Udo Spallek
foreach $item (@category_cent) {
82ee2234 Stephan Köhler
$form->{"$item"} = 0;
}
6ab3da45 Udo Spallek
foreach $item (@category_euro) {
82ee2234 Stephan Köhler
$form->{"$item"} = 0;
}

&get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
081a4f97 Moritz Bunkus
$form, $category);
82ee2234 Stephan Köhler
b85b47c5 Udo Spallek
e8d3bc67 Udo Spallek
# 16%/19% Umstellung
# Umordnen der Kennziffern
b85b47c5 Udo Spallek
if ( $form->{year} < 2007) {
$form->{35} += $form->{81};
$form->{36} += $form->{811};
$form->{95} += $form->{89};
$form->{98} += $form->{891};
map { delete $form->{$_} } qw(81 811 89 891);
} else {
$form->{35} += $form->{51};
$form->{36} += $form->{511};
$form->{95} += $form->{97};
$form->{98} += $form->{971};
map { delete $form->{$_} } qw(51 511 97 971);
}

e8d3bc67 Udo Spallek
82ee2234 Stephan Köhler
#
785ef602 Udo Spallek
# Berechnung der USTVA Formularfelder laut Bogen 207
82ee2234 Stephan Köhler
#
6ab3da45 Udo Spallek
82ee2234 Stephan Köhler
$form->{"51r"} = $form->{"511"};
$form->{"86r"} = $form->{"861"};
$form->{"97r"} = $form->{"971"};
$form->{"93r"} = $form->{"931"};
785ef602 Udo Spallek
$form->{"Z43"} = $form->{"511"} + $form->{"811"} + $form->{"861"}
+ $form->{"36"} + $form->{"80"} + $form->{"971"}
+ $form->{"891"} + $form->{"931"} + $form->{"96"}
+ $form->{"98"};

82ee2234 Stephan Köhler
$form->{"Z45"} = $form->{"Z43"};
785ef602 Udo Spallek
$form->{"Z53"} = $form->{"Z45"} + $form->{"53"} + $form->{"74"}
+ $form->{"85"} + $form->{"65"};
$form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"}
- $form->{"62"} - $form->{"67"} - $form->{"63"}
- $form->{"64"} - $form->{"59"};
$form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
$form->{"83"} = $form->{"Z65"} - $form->{"39"};
6ab3da45 Udo Spallek
82ee2234 Stephan Köhler
$dbh->disconnect;

$main::lxdebug->leave_sub();
}

sub get_accounts_ustva {
$main::lxdebug->enter_sub();

my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;

my ($null, $department_id) = split /--/, $form->{department};

my $query;
my $dpt_where;
my $dpt_join;
my $project;
e8d3bc67 Udo Spallek
my $where = "";
82ee2234 Stephan Köhler
my $glwhere = "";
my $subwhere = "";
081a4f97 Moritz Bunkus
my $ARwhere = "";
2ae29658 Udo Spallek
my $APwhere = '';
081a4f97 Moritz Bunkus
my $arwhere = "";
82ee2234 Stephan Köhler
my $item;

if ($fromdate) {
if ($form->{method} eq 'cash') {
$subwhere .= " AND transdate >= '$fromdate'";
$glwhere = " AND ac.transdate >= '$fromdate'";
$ARwhere .= " AND acc.transdate >= '$fromdate'";
2ae29658 Udo Spallek
$APwhere .= " AND AP.transdate >= '$fromdate'";
82ee2234 Stephan Köhler
}
c1dafb84 Stephan Köhler
$where .= " AND ac.transdate >= '$fromdate'";
82ee2234 Stephan Köhler
}

if ($todate) {
$where .= " AND ac.transdate <= '$todate'";
081a4f97 Moritz Bunkus
$ARwhere .= " AND acc.transdate <= '$todate'";
82ee2234 Stephan Köhler
$subwhere .= " AND transdate <= '$todate'";
2ae29658 Udo Spallek
$APwhere .= " AND AP.transdate <= '$todate'";
82ee2234 Stephan Köhler
}

if ($department_id) {
$dpt_join = qq|
JOIN department t ON (a.department_id = t.id)
|;
$dpt_where = qq|
AND t.id = $department_id
|;
}

if ($form->{project_id}) {
$project = qq|
AND ac.project_id = $form->{project_id}
|;
}
e8d3bc67 Udo Spallek
############################################
# Method eq 'cash' = IST Versteuerung
############################################
# Betrifft nur die eingenommene Umsatzsteuer
#
############################################
82ee2234 Stephan Köhler
2ae29658 Udo Spallek
if ($form->{method} eq 'cash') {
82ee2234 Stephan Köhler
$query = qq|
e8d3bc67 Udo Spallek
SELECT
-- USTVA IST-Versteuerung
--
-- Alle tatsaechlichen _Zahlungseingaenge_
-- im Voranmeldezeitraum erfassen
-- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
SUM( ac.amount *
-- Bezahlt / Rechnungssumme
(
SELECT SUM(acc.amount)
FROM acc_trans acc
INNER JOIN chart c ON (acc.chart_id = c.id
AND c.link like '%AR_paid%')
WHERE
1=1
$ARwhere
AND acc.trans_id = ac.trans_id
)
/
(
SELECT amount FROM ar WHERE id = ac.trans_id
)
) AS amount,
tk.pos_ustva
FROM acc_trans ac
LEFT JOIN chart c ON (c.id = ac.chart_id)
LEFT JOIN ar ON (ar.id = ac.trans_id)
LEFT JOIN taxkeys tk ON (
tk.id = (
SELECT id FROM taxkeys
WHERE chart_id = ac.chart_id
-- AND taxkey_id = ac.taxkey
AND startdate <= COALESCE(ar.deliverydate, ar.transdate)
ORDER BY startdate DESC LIMIT 1
)
d72df9dc Udo Spallek
)
e8d3bc67 Udo Spallek
WHERE
1=1
-- Here no where, please. All Transactions ever should be
-- testet if they are paied in the USTVA report period.
GROUP BY tk.pos_ustva
|;
} elsif ($form->{method} eq 'accrual') {
#########################################
# Method eq 'accrual' = Soll Versteuerung
#########################################
82ee2234 Stephan Köhler
if ($department_id) {
$dpt_join = qq|
JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
|;
$dpt_where = qq|
AND t.department_id = $department_id
|;
}

e8d3bc67 Udo Spallek
82ee2234 Stephan Köhler
$query = qq|
e8d3bc67 Udo Spallek
-- Alle Einnahmen AR und pos_ustva erfassen
SELECT
- sum(ac.amount) AS amount,
tk.pos_ustva
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN ar ON (ar.id = ac.trans_id)
JOIN taxkeys tk ON (
tk.id = (
SELECT id FROM taxkeys
WHERE chart_id = ac.chart_id
AND startdate <= COALESCE(ar.deliverydate, ar.transdate)
ORDER BY startdate DESC LIMIT 1
)
)
$dpt_join
WHERE 1 = 1
$where
$dpt_where
$project
GROUP BY tk.pos_ustva
|;
} else {
$self->error("Unknown tax method: $form->{method}")

82ee2234 Stephan Köhler
}
e8d3bc67 Udo Spallek
#########################################
# Ausgaben und Gl Buchungen sind gleich
# f?r Ist- und Soll-Versteuerung
#########################################
$query .= qq|
UNION -- alle Ausgaben AP erfassen

SELECT
sum(ac.amount) AS amount,
tk.pos_ustva
FROM acc_trans ac
JOIN AP ON (AP.id = ac.trans_id )
JOIN chart c ON (c.id = ac.chart_id)
LEFT JOIN taxkeys tk ON (
tk.id = (
SELECT id FROM taxkeys
WHERE chart_id=ac.chart_id
--AND taxkey_id=ac.taxkey
AND startdate <= COALESCE(AP.transdate)
ORDER BY startdate DESC LIMIT 1
)
)
WHERE
1=1
$APwhere
$dpt_where
$project
GROUP BY tk.pos_ustva

UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen

SELECT sum
( - ac.amount) AS amount,
tk.pos_ustva
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN gl a ON (a.id = ac.trans_id)
LEFT JOIN taxkeys tk ON (
tk.id = (
SELECT id FROM taxkeys
WHERE chart_id=ac.chart_id
--AND taxkey_id=ac.taxkey
AND startdate <= COALESCE(ac.transdate)
ORDER BY startdate DESC LIMIT 1
)
)

$dpt_join
WHERE 1 = 1
$where
$dpt_from
$project
GROUP BY tk.pos_ustva
|;
82ee2234 Stephan Köhler
my @accno;
my $accno;
my $ref;
081a4f97 Moritz Bunkus
2ae29658 Udo Spallek
# Show all $query in Debuglevel LXDebug::QUERY
$callingdetails = (caller (0))[3];
$main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
82ee2234 Stephan Köhler
my $sth = $dbh->prepare($query);
e8d3bc67 Udo Spallek
82ee2234 Stephan Köhler
$sth->execute || $form->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
e8d3bc67 Udo Spallek
# Bug 365 solved?!
$ref->{amount} *= -1;
82ee2234 Stephan Köhler
if ($category eq "pos_bwa") {
if ($last_period) {
$form->{ $ref->{$category} }{kumm} += $ref->{amount};
} else {
$form->{ $ref->{$category} }{jetzt} += $ref->{amount};
}
} else {
$form->{ $ref->{$category} } += $ref->{amount};
}
}
e8d3bc67 Udo Spallek
82ee2234 Stephan Köhler
$sth->finish;

$main::lxdebug->leave_sub();
e8d3bc67 Udo Spallek
82ee2234 Stephan Köhler
}

a205ed21 Udo Spallek

d319704a Moritz Bunkus
1;