$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)) {
+ keys(%elster_land_fa)
+ ) {
print qq|
elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
$j++;
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)) {
+ keys(%elster_land_fa)
+ ) {
print qq|
<option value="$ffff"|;
$main::lxdebug->leave_sub();
}
-
sub ustva {
$main::lxdebug->enter_sub();
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);
+ 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;
$form->{"$item"} = 0;
}
-
&get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
- $form, $category);
-
+ $form, $category);
#
# Berechnung der USTVA Formularfelder
$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->{"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"};
-
+ $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');
+ $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');
+ $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
+ 0, '0');
}
$dbh->disconnect;
my $where = "1 = 1";
my $glwhere = "";
my $subwhere = "";
- my $ARwhere = "";
- my $arwhere = "";
+ my $ARwhere = "";
+ my $APwhere = '';
+ my $arwhere = "";
my $item;
if ($fromdate) {
$subwhere .= " AND transdate >= '$fromdate'";
$glwhere = " AND ac.transdate >= '$fromdate'";
$ARwhere .= " AND acc.transdate >= '$fromdate'";
- $where .= " AND ac.transdate >= '$fromdate'";
-
+ $APwhere .= " AND AP.transdate >= '$fromdate'";
}
+ $where .= " AND ac.transdate >= '$fromdate'";
}
if ($todate) {
$where .= " AND ac.transdate <= '$todate'";
- $ARwhere .= " AND acc.transdate <= '$todate'";
+ $ARwhere .= " AND acc.transdate <= '$todate'";
$subwhere .= " AND transdate <= '$todate'";
+ $APwhere .= " AND AP.transdate <= '$todate'";
}
if ($department_id) {
AND ac.project_id = $form->{project_id}
|;
}
+#########################################
+# Method eq 'cash' = IST Versteuerung
+#########################################
- if ($form->{method} eq 'cash') {
+ if ($form->{method} eq 'cash') {
$query = qq|
-
SELECT
- SUM( ac.amount *
+ -- Alle tatsaechlichen Zahlungseingaenge
+ -- im Voranmeldezeitraum erfassen
+ -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
+ SUM( ac.amount *
-- Bezahlt / Rechnungssumme
(
SELECT SUM(acc.amount)
-- 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 {
+ 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
+#########################################
if ($department_id) {
$dpt_join = qq|
}
$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
- |;
-
- }
+ 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
+ |;
}
my @accno;
my $accno;
my $ref;
- #print $query;
+
+ # Show all $query in Debuglevel LXDebug::QUERY
+ $callingdetails = (caller (0))[3];
+ $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
+
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- if ($ref->{amount} < 0) {
+# Bug 365 solved?!
+# if ($ref->{amount} < 0) {
$ref->{amount} *= -1;
- }
+# }
if ($category eq "pos_bwa") {
if ($last_period) {
$form->{ $ref->{$category} }{kumm} += $ref->{amount};
$main::lxdebug->leave_sub();
}
-
1;
projects / kivitendo-erp.git / blobdiff