Projekt

Allgemein

Profil

Herunterladen (21,9 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/USTVA.pm @ 423d39f9

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();

($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 = '&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

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">
0576299f Moritz Bunkus
<input type=hidden name="part" value="$part">
d319704a Moritz Bunkus
|;
my $h = 0;
my $i = 0;
my $j = 0;
$k = 0;

for ($h = 1; $h < $patterncount; $h++) {
print qq|&nbsp;$delimiter&nbsp;\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' ) {
0576299f Moritz Bunkus
print qq|
d319704a Moritz Bunkus
<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} }
081a4f97 Moritz Bunkus
keys(%elster_land_fa)
) {
d319704a Moritz Bunkus
print qq|
elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
$j++;
}
print qq|
}|;
}
0576299f Moritz Bunkus
print 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 '') {
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
0576299f Moritz Bunkus
</td>
d319704a Moritz Bunkus
<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} }
081a4f97 Moritz Bunkus
keys(%elster_land_fa)
) {
d319704a Moritz Bunkus
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>
0576299f Moritz Bunkus
</table>
d319704a Moritz Bunkus
</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
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";
6ab3da45 Udo Spallek
my @category_cent = qw(511 861 36 80 971 931 98 96 53 74
081a4f97 Moritz Bunkus
85 65 66 61 62 67 63 64 59 69 39 83
Z43 Z45 Z53 Z62 Z65 Z67);

6ab3da45 Udo Spallek
my @category_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93
081a4f97 Moritz Bunkus
95 94 42 60 45 52 73 84);
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
#
# Berechnung der USTVA Formularfelder
#
6ab3da45 Udo Spallek
82ee2234 Stephan Köhler
$form->{"51r"} = $form->{"511"};
$form->{"86r"} = $form->{"861"};
$form->{"97r"} = $form->{"971"};
$form->{"93r"} = $form->{"931"};
081a4f97 Moritz Bunkus
$form->{"Z43"} =
6ab3da45 Udo Spallek
$form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} +
$form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"};
82ee2234 Stephan Köhler
$form->{"Z45"} = $form->{"Z43"};
$form->{"Z53"} = $form->{"Z43"};
081a4f97 Moritz Bunkus
$form->{"Z62"} =
6ab3da45 Udo Spallek
$form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} -
$form->{"63"} - $form->{"64"} - $form->{"59"};
081a4f97 Moritz Bunkus
$form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
$form->{"83"} = $form->{"Z65"} - $form->{"39"};
6ab3da45 Udo Spallek
# Hier fehlen moeglicherweise noch einige Berechnungen!
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;
my $where = "1 = 1";
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}
|;
}
2ae29658 Udo Spallek
#########################################
# Method eq 'cash' = IST Versteuerung
#########################################
82ee2234 Stephan Köhler
2ae29658 Udo Spallek
if ($form->{method} eq 'cash') {
82ee2234 Stephan Köhler
$query = qq|
SELECT
2ae29658 Udo Spallek
-- Alle tatsaechlichen Zahlungseingaenge
-- im Voranmeldezeitraum erfassen
-- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
SUM( ac.amount *
82ee2234 Stephan Köhler
-- 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

2ae29658 Udo Spallek
UNION -- alle Ausgaben AP erfassen

SELECT
sum(ac.amount) AS amount, pos_ustva
FROM acc_trans ac
JOIN AP ON (AP.id = ac.trans_id )
JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '')
WHERE
1=1
$APwhere
$dpt_where
$project
GROUP BY pos_ustva

UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen

SELECT sum
(
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
WHEN c.link LIKE '%AP%' THEN ac.amount * 1
END
) 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
$dpt_from
AND NOT (c.link = 'AR' OR c.link = 'AP')
$project
GROUP BY c.$category
|;

} else {
#########################################
# 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
|;
}

$query = qq|
2ae29658 Udo Spallek
SELECT sum
(
CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
WHEN c.link LIKE '%AP%' THEN ac.amount * 1
END
) 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
|;
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);
$sth->execute || $form->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
2ae29658 Udo Spallek
# Bug 365 solved?!
6ab3da45 Udo Spallek
$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};
}
}
$sth->finish;

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

d319704a Moritz Bunkus
1;