|
#=====================================================================
|
|
# 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();
|
|
|
|
($elsterland, $elsterFFFF, $steuernummer) = @_;
|
|
|
|
$elster_land = $elsterland;
|
|
$elster_FFFF = $elsterFFFF;
|
|
$steuernummer = '0000000000' if ($steuernummer eq '');
|
|
|
|
# $steuernummer formatieren (nur Zahlen) -> $stnr
|
|
$stnr = $steuernummer;
|
|
$stnr =~ s/\D+//g;
|
|
|
|
#Pattern description Elstersteuernummer
|
|
my %elster_STNRformat = (
|
|
'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
|
|
);
|
|
|
|
#split the pattern
|
|
$elster_pattern = $elster_STNRformat{$elster_land};
|
|
my @elster_pattern = split(' ', $elster_pattern);
|
|
my $delimiter = ' ';
|
|
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
|
|
|
|
print qq|<b><font size="+1">|;
|
|
$part = '';
|
|
SWITCH: {
|
|
$elster_pattern[0] eq 'FFF' && do {
|
|
$part = substr($elster_FFFF, 1, 4);
|
|
print qq|$part|;
|
|
last SWITCH;
|
|
};
|
|
$elster_pattern[0] eq '0FF' && do {
|
|
$part = '0' . substr($elster_FFFF, 2, 4);
|
|
print qq|$part|;
|
|
last SWITCH;
|
|
};
|
|
$elster_pattern[0] eq 'FF' && do {
|
|
$part = substr($elster_FFFF, 2, 4);
|
|
print qq|$part|;
|
|
last SWITCH;
|
|
};
|
|
1 == 1 && do {
|
|
print qq|Fehler!|;
|
|
last SWITCH;
|
|
};
|
|
}
|
|
|
|
#now the rest of the Steuernummer ...
|
|
print qq|</b></font>|;
|
|
print qq|\n
|
|
<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">
|
|
<input type=hidden name="part" value="$part">
|
|
|;
|
|
my $h = 0;
|
|
my $i = 0;
|
|
my $j = 0;
|
|
$k = 0;
|
|
|
|
for ($h = 1; $h < $patterncount; $h++) {
|
|
print qq| $delimiter \n|;
|
|
for ($i = 1; $i <= length($elster_pattern[$h]); $i++) {
|
|
print qq|<select name="part_$h\_$i">\n|;
|
|
|
|
for ($j = 0; $j <= 9; $j++) {
|
|
print qq| <option value="$j"|;
|
|
if ($steuernummer ne '') {
|
|
if ($j eq substr($stnr, length($part) + $k, 1)) {
|
|
print qq| selected|;
|
|
}
|
|
}
|
|
print qq|>$j</option>\n|;
|
|
}
|
|
$k++;
|
|
print qq|</select>\n|;
|
|
}
|
|
}
|
|
$main::lxdebug->leave_sub();
|
|
}
|
|
|
|
sub fa_auswahl {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
use SL::Form;
|
|
|
|
# Referenz wird ?bergeben, hash of hash wird nicht
|
|
# in neues Hash kopiert, sondern direkt ?ber die Referenz ver?ndert
|
|
# Prototyp f?r diese Konstruktion
|
|
|
|
my ($land, $elsterFFFF, $elster_init) =
|
|
@_; #Referenz auf Hash von Hash ?bergeben
|
|
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' ) {
|
|
print qq|
|
|
<br>
|
|
<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) {
|
|
print qq|
|
|
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} }
|
|
keys(%elster_land_fa)
|
|
) {
|
|
print qq|
|
|
elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
|
|
$j++;
|
|
}
|
|
print qq|
|
|
}|;
|
|
}
|
|
print qq|
|
|
}
|
|
</script>
|
|
|
|
<table width="100%">
|
|
<tr>
|
|
<td>
|
|
Bundesland
|
|
</td>
|
|
<td>
|
|
<select size="1" name="elsterland_new" onchange="update_auswahl()">|;
|
|
if ($land eq '') {
|
|
print qq|<option value="Auswahl" $checked>hier ausw?hlen...</option>\n|;
|
|
}
|
|
foreach $elster_land (sort keys %$elster_init) {
|
|
print qq|
|
|
<option value="$elster_land"|;
|
|
if ($elster_land eq $land and $checked eq '') {
|
|
print qq| selected|;
|
|
}
|
|
print qq|>$elster_land</option>
|
|
|;
|
|
}
|
|
print qq|
|
|
</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];
|
|
}
|
|
|
|
print qq|
|
|
<tr>
|
|
<td>Finanzamt
|
|
</td>
|
|
<td>
|
|
<select size="1" name="elsterFFFF_new">|;
|
|
if ($elsterFFFF eq '') {
|
|
print qq|<option value="Auswahl" $checked>hier ausw?hlen...</option>|;
|
|
} else {
|
|
foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
|
|
keys(%elster_land_fa)
|
|
) {
|
|
|
|
print qq|
|
|
<option value="$ffff"|;
|
|
if ($ffff eq $elsterFFFF and $checked eq '') {
|
|
print qq| selected|;
|
|
}
|
|
print qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
|
|
}
|
|
}
|
|
print qq|
|
|
</td>
|
|
</tr>
|
|
</table>
|
|
</select>|;
|
|
|
|
$main::lxdebug->leave_sub();
|
|
}
|
|
|
|
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();
|
|
|
|
my ($myconfig, $form) = @_;
|
|
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
|
|
print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
|
|
$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();
|
|
}
|
|
|
|
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";
|
|
my @categories_cent = qw(511 861 36 80 971 931 98 96 53 74
|
|
85 65 66 61 62 67 63 64 59 69 39 83
|
|
Z43 Z45 Z53 Z62 Z65 Z67);
|
|
|
|
my @categories_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93
|
|
95 94 42 60 45 52 73 84);
|
|
|
|
$form->{decimalplaces} *= 1;
|
|
|
|
foreach $item (@categories_cent) {
|
|
$form->{"$item"} = 0;
|
|
}
|
|
foreach $item (@categories_euro) {
|
|
$form->{"$item"} = 0;
|
|
}
|
|
|
|
&get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
|
|
$form, $category);
|
|
|
|
#
|
|
# Berechnung der USTVA Formularfelder
|
|
#
|
|
$form->{"51r"} = $form->{"511"};
|
|
$form->{"86r"} = $form->{"861"};
|
|
$form->{"97r"} = $form->{"971"};
|
|
$form->{"93r"} = $form->{"931"};
|
|
$form->{"Z43"} =
|
|
$form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} +
|
|
$form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"};
|
|
$form->{"Z45"} = $form->{"Z43"};
|
|
$form->{"Z53"} = $form->{"Z43"};
|
|
$form->{"Z62"} =
|
|
$form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} -
|
|
$form->{"63"} - $form->{"64"} - $form->{"59"};
|
|
$form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
|
|
$form->{"83"} = $form->{"Z65"} - $form->{"39"};
|
|
|
|
foreach $item (@categories_cent) {
|
|
$form->{$item} =
|
|
$form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
|
|
2, '0');
|
|
}
|
|
|
|
foreach $item (@categories_euro) {
|
|
$form->{$item} =
|
|
$form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
|
|
0, '0');
|
|
}
|
|
|
|
$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;
|
|
my $where = "1 = 1";
|
|
my $glwhere = "";
|
|
my $subwhere = "";
|
|
my $ARwhere = "";
|
|
my $arwhere = "";
|
|
my $item;
|
|
|
|
if ($fromdate) {
|
|
if ($form->{method} eq 'cash') {
|
|
$subwhere .= " AND transdate >= '$fromdate'";
|
|
$glwhere = " AND ac.transdate >= '$fromdate'";
|
|
$ARwhere .= " AND acc.transdate >= '$fromdate'";
|
|
}
|
|
$where .= " AND ac.transdate >= '$fromdate'";
|
|
}
|
|
|
|
if ($todate) {
|
|
$where .= " AND ac.transdate <= '$todate'";
|
|
$ARwhere .= " AND acc.transdate <= '$todate'";
|
|
$subwhere .= " AND transdate <= '$todate'";
|
|
}
|
|
|
|
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}
|
|
|;
|
|
}
|
|
|
|
if ($form->{method} eq 'cash') {
|
|
|
|
$query = qq|
|
|
|
|
SELECT
|
|
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,
|
|
c.pos_ustva
|
|
FROM acc_trans ac
|
|
JOIN chart c ON (c.id = ac.chart_id)
|
|
--JOIN ar ON (ar.id = ac.trans_id)
|
|
where
|
|
1=1
|
|
-- Here no where, please. All Transactions ever should be
|
|
-- testet if they are paied in the USTVA report period.
|
|
GROUP BY c.pos_ustva
|
|
UNION
|
|
|
|
SELECT sum(ac.amount) AS amount,
|
|
c.$category
|
|
FROM acc_trans ac
|
|
JOIN chart c ON (c.id = ac.chart_id)
|
|
JOIN ap a ON (a.id = ac.trans_id)
|
|
$dpt_join
|
|
WHERE $where
|
|
$dpt_where
|
|
AND ac.trans_id IN
|
|
(
|
|
SELECT trans_id
|
|
FROM acc_trans
|
|
JOIN chart ON (chart_id = id)
|
|
WHERE link LIKE '%AP_amount%'
|
|
$subwhere
|
|
)
|
|
|
|
$project
|
|
GROUP BY c.$category
|
|
|
|
UNION
|
|
|
|
SELECT sum(ac.amount) AS amount,
|
|
c.$category
|
|
FROM acc_trans ac
|
|
JOIN chart c ON (c.id = ac.chart_id)
|
|
JOIN gl a ON (a.id = ac.trans_id)
|
|
$dpt_join
|
|
WHERE $where
|
|
$glwhere
|
|
$dpt_from
|
|
AND NOT (c.link = 'AR' OR c.link = 'AP')
|
|
$project
|
|
GROUP BY c.$category
|
|
|
|
|;
|
|
|
|
if ($form->{project_id}) {
|
|
|
|
$query .= qq|
|
|
|
|
UNION
|
|
|
|
SELECT SUM(ac.sellprice * ac.qty) AS amount,
|
|
c.$category
|
|
FROM invoice ac
|
|
JOIN ar a ON (a.id = ac.trans_id)
|
|
JOIN parts p ON (ac.parts_id = p.id)
|
|
JOIN chart c on (p.income_accno_id = c.id)
|
|
$dpt_join
|
|
-- use transdate from subwhere
|
|
WHERE 1 = 1 $subwhere
|
|
AND c.category = 'I'
|
|
$dpt_where
|
|
AND ac.trans_id IN
|
|
(
|
|
SELECT trans_id
|
|
FROM acc_trans
|
|
JOIN chart ON (chart_id = id)
|
|
WHERE link LIKE '%AR_paid%'
|
|
$subwhere
|
|
)
|
|
|
|
$project
|
|
GROUP BY c.$category
|
|
|
|
UNION
|
|
|
|
SELECT SUM(ac.sellprice) AS amount,
|
|
c.$category
|
|
FROM invoice ac
|
|
JOIN ap a ON (a.id = ac.trans_id)
|
|
JOIN parts p ON (ac.parts_id = p.id)
|
|
JOIN chart c on (p.expense_accno_id = c.id)
|
|
$dpt_join
|
|
WHERE 1 = 1 $subwhere
|
|
AND c.category = 'E'
|
|
$dpt_where
|
|
AND ac.trans_id IN
|
|
(
|
|
SELECT trans_id
|
|
FROM acc_trans
|
|
JOIN chart ON (chart_id = id)
|
|
WHERE link LIKE '%AP_paid%'
|
|
$subwhere
|
|
)
|
|
|
|
$project
|
|
GROUP BY c.$category
|
|
|;
|
|
}
|
|
|
|
} else {
|
|
|
|
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
|
|
|;
|
|
}
|
|
|
|
$query = qq|
|
|
|
|
SELECT sum(ac.amount) AS amount,
|
|
c.$category
|
|
FROM acc_trans ac
|
|
JOIN chart c ON (c.id = ac.chart_id)
|
|
$dpt_join
|
|
WHERE $where
|
|
$dpt_where
|
|
$project
|
|
GROUP BY c.$category
|
|
|;
|
|
|
|
if ($form->{project_id}) {
|
|
|
|
$query .= qq|
|
|
|
|
UNION
|
|
|
|
SELECT SUM(ac.sellprice * ac.qty) AS amount,
|
|
c.$category
|
|
FROM invoice ac
|
|
JOIN ar a ON (a.id = ac.trans_id)
|
|
JOIN parts p ON (ac.parts_id = p.id)
|
|
JOIN chart c on (p.income_accno_id = c.id)
|
|
$dpt_join
|
|
-- use transdate from subwhere
|
|
WHERE 1 = 1 $subwhere
|
|
AND c.category = 'I'
|
|
$dpt_where
|
|
$project
|
|
GROUP BY c.$category
|
|
|
|
UNION
|
|
|
|
SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
|
|
c.$category
|
|
FROM invoice ac
|
|
JOIN ap a ON (a.id = ac.trans_id)
|
|
JOIN parts p ON (ac.parts_id = p.id)
|
|
JOIN chart c on (p.expense_accno_id = c.id)
|
|
$dpt_join
|
|
WHERE 1 = 1 $subwhere
|
|
AND c.category = 'E'
|
|
$dpt_where
|
|
$project
|
|
GROUP BY c.$category
|
|
|;
|
|
|
|
}
|
|
}
|
|
|
|
my @accno;
|
|
my $accno;
|
|
my $ref;
|
|
|
|
#print $query;
|
|
my $sth = $dbh->prepare($query);
|
|
$sth->execute || $form->dberror($query);
|
|
|
|
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
if ($ref->{amount} < 0) {
|
|
$ref->{amount} *= -1;
|
|
}
|
|
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};
|
|
}
|
|
}
|
|
$sth->finish;
|
|
|
|
$main::lxdebug->leave_sub();
|
|
}
|
|
|
|
1;
|