1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
15 # Contributors: Benjamin Lee <benjaminlee@consultant.com>
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # backend code for reports
33 #======================================================================
38 $main::lxdebug->enter_sub();
40 my ($self, $myconfig, $form) = @_;
43 my $dbh = $form->dbconnect($myconfig);
46 my @categories = qw(A C L Q);
48 # if there are any dates construct a where
49 if ($form->{asofdate}) {
51 $form->{this_period} = "$form->{asofdate}";
52 $form->{period} = "$form->{asofdate}";
56 $form->{decimalplaces} *= 1;
58 &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form,
61 # if there are any compare dates
62 if ($form->{compareasofdate}) {
65 &get_accounts($dbh, $last_period, "", $form->{compareasofdate},
68 $form->{last_period} = "$form->{compareasofdate}";
75 # now we got $form->{A}{accno}{ } assets
76 # and $form->{L}{accno}{ } liabilities
77 # and $form->{Q}{accno}{ } equity
78 # build asset accounts
84 'A' => { 'label' => 'asset',
88 'L' => { 'label' => 'liability',
89 'labels' => 'liabilities',
92 'Q' => { 'label' => 'equity',
97 foreach $category (grep { !/C/ } @categories) {
99 foreach $key (sort keys %{ $form->{$category} }) {
101 $str = ($form->{l_heading}) ? $form->{padding} : "";
103 if ($form->{$category}{$key}{charttype} eq "A") {
106 ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
107 : "$form->{$category}{$key}{description}";
109 if ($form->{$category}{$key}{charttype} eq "H") {
110 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
112 push(@{ $form->{"$account{$category}{label}_account"} },
113 "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
115 push(@{ $form->{"$account{$category}{label}_this_period"} },
116 $form->format_amount(
118 $account{$category}{subthis} * $account{$category}{ml},
119 $form->{decimalplaces}, $dash
123 push(@{ $form->{"$account{$category}{label}_last_period"} },
124 $form->format_amount(
126 $account{$category}{sublast} * $account{$category}{ml},
127 $form->{decimalplaces}, $dash
133 "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
135 $account{$category}{subthis} = $form->{$category}{$key}{this};
136 $account{$category}{sublast} = $form->{$category}{$key}{last};
137 $account{$category}{subdescription} =
138 $form->{$category}{$key}{description};
139 $account{$category}{subtotal} = 1;
141 $form->{$category}{$key}{this} = 0;
142 $form->{$category}{$key}{last} = 0;
144 next unless $form->{l_heading};
149 # push description onto array
150 push(@{ $form->{"$account{$category}{label}_account"} }, $str);
152 if ($form->{$category}{$key}{charttype} eq 'A') {
153 $form->{"total_$account{$category}{labels}_this_period"} +=
154 $form->{$category}{$key}{this} * $account{$category}{ml};
158 push(@{ $form->{"$account{$category}{label}_this_period"} },
159 $form->format_amount(
161 $form->{$category}{$key}{this} * $account{$category}{ml},
162 $form->{decimalplaces}, $dash
166 $form->{"total_$account{$category}{labels}_last_period"} +=
167 $form->{$category}{$key}{last} * $account{$category}{ml};
169 push(@{ $form->{"$account{$category}{label}_last_period"} },
170 $form->format_amount(
172 $form->{$category}{$key}{last} * $account{$category}{ml},
173 $form->{decimalplaces}, $dash
178 $str = ($form->{l_heading}) ? $form->{padding} : "";
179 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
180 push(@{ $form->{"$account{$category}{label}_account"} },
181 "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
183 push(@{ $form->{"$account{$category}{label}_this_period"} },
184 $form->format_amount(
186 $account{$category}{subthis} * $account{$category}{ml},
187 $form->{decimalplaces}, $dash
191 push(@{ $form->{"$account{$category}{label}_last_period"} },
192 $form->format_amount(
194 $account{$category}{sublast} * $account{$category}{ml},
195 $form->{decimalplaces}, $dash
202 # totals for assets, liabilities
203 $form->{total_assets_this_period} =
204 $form->round_amount($form->{total_assets_this_period},
205 $form->{decimalplaces});
206 $form->{total_liabilities_this_period} =
207 $form->round_amount($form->{total_liabilities_this_period},
208 $form->{decimalplaces});
209 $form->{total_equity_this_period} =
210 $form->round_amount($form->{total_equity_this_period},
211 $form->{decimalplaces});
214 $form->{earnings_this_period} =
215 $form->{total_assets_this_period} -
216 $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
218 push(@{ $form->{equity_this_period} },
219 $form->format_amount($myconfig,
220 $form->{earnings_this_period},
221 $form->{decimalplaces}, "- "
224 $form->{total_equity_this_period} =
226 $form->{total_equity_this_period} + $form->{earnings_this_period},
227 $form->{decimalplaces});
229 # add liability + equity
230 $form->{total_this_period} =
231 $form->format_amount(
233 $form->{total_liabilities_this_period} + $form->{total_equity_this_period},
234 $form->{decimalplaces},
239 # totals for assets, liabilities
240 $form->{total_assets_last_period} =
241 $form->round_amount($form->{total_assets_last_period},
242 $form->{decimalplaces});
243 $form->{total_liabilities_last_period} =
244 $form->round_amount($form->{total_liabilities_last_period},
245 $form->{decimalplaces});
246 $form->{total_equity_last_period} =
247 $form->round_amount($form->{total_equity_last_period},
248 $form->{decimalplaces});
250 # calculate retained earnings
251 $form->{earnings_last_period} =
252 $form->{total_assets_last_period} -
253 $form->{total_liabilities_last_period} -
254 $form->{total_equity_last_period};
256 push(@{ $form->{equity_last_period} },
257 $form->format_amount($myconfig,
258 $form->{earnings_last_period},
259 $form->{decimalplaces}, "- "
262 $form->{total_equity_last_period} =
264 $form->{total_equity_last_period} + $form->{earnings_last_period},
265 $form->{decimalplaces});
267 # add liability + equity
268 $form->{total_last_period} =
269 $form->format_amount($myconfig,
270 $form->{total_liabilities_last_period} +
271 $form->{total_equity_last_period},
272 $form->{decimalplaces},
277 $form->{total_liabilities_last_period} =
278 $form->format_amount($myconfig,
279 $form->{total_liabilities_last_period},
280 $form->{decimalplaces}, "- ")
281 if ($form->{total_liabilities_last_period} != 0);
283 $form->{total_equity_last_period} =
284 $form->format_amount($myconfig,
285 $form->{total_equity_last_period},
286 $form->{decimalplaces}, "- ")
287 if ($form->{total_equity_last_period} != 0);
289 $form->{total_assets_last_period} =
290 $form->format_amount($myconfig,
291 $form->{total_assets_last_period},
292 $form->{decimalplaces}, "- ")
293 if ($form->{total_assets_last_period} != 0);
295 $form->{total_assets_this_period} =
296 $form->format_amount($myconfig,
297 $form->{total_assets_this_period},
298 $form->{decimalplaces}, "- ");
300 $form->{total_liabilities_this_period} =
301 $form->format_amount($myconfig,
302 $form->{total_liabilities_this_period},
303 $form->{decimalplaces}, "- ");
305 $form->{total_equity_this_period} =
306 $form->format_amount($myconfig,
307 $form->{total_equity_this_period},
308 $form->{decimalplaces}, "- ");
310 $main::lxdebug->leave_sub();
314 $main::lxdebug->enter_sub();
316 my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
318 my ($null, $department_id) = split /--/, $form->{department};
329 my $category = "AND (";
330 foreach $item (@{$categories}) {
331 $category .= qq|c.category = '$item' OR |;
333 $category =~ s/OR $/\)/;
336 $query = qq|SELECT c.accno, c.description, c.category
338 WHERE c.charttype = 'H'
342 $sth = $dbh->prepare($query);
343 $sth->execute || $form->dberror($query);
345 my @headingaccounts = ();
346 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
347 $form->{ $ref->{category} }{ $ref->{accno} }{description} =
348 "$ref->{description}";
349 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
350 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
352 push @headingaccounts, $ref->{accno};
358 if ($form->{method} eq 'cash') {
359 $subwhere .= " AND transdate >= '$fromdate'";
360 $glwhere = " AND ac.transdate >= '$fromdate'";
362 $where .= " AND ac.transdate >= '$fromdate'";
367 $where .= " AND ac.transdate <= '$todate'";
368 $subwhere .= " AND transdate <= '$todate'";
371 if ($department_id) {
373 JOIN department t ON (a.department_id = t.id)
376 AND t.id = $department_id
380 if ($form->{project_id}) {
382 AND ac.project_id = $form->{project_id}
388 if ($form->{method} eq 'cash') {
392 SELECT c.accno, sum(ac.amount) AS amount,
393 c.description, c.category
395 JOIN chart c ON (c.id = ac.chart_id)
396 JOIN ar a ON (a.id = ac.trans_id)
405 JOIN chart ON (chart_id = id)
406 WHERE link LIKE '%AR_paid%'
411 GROUP BY c.accno, c.description, c.category
415 SELECT c.accno, sum(ac.amount) AS amount,
416 c.description, c.category
418 JOIN chart c ON (c.id = ac.chart_id)
419 JOIN ap a ON (a.id = ac.trans_id)
428 JOIN chart ON (chart_id = id)
429 WHERE link LIKE '%AP_paid%'
434 GROUP BY c.accno, c.description, c.category
438 SELECT c.accno, sum(ac.amount) AS amount,
439 c.description, c.category
441 JOIN chart c ON (c.id = ac.chart_id)
442 JOIN gl a ON (a.id = ac.trans_id)
448 AND NOT (c.link = 'AR' OR c.link = 'AP')
450 GROUP BY c.accno, c.description, c.category
453 if ($form->{project_id}) {
459 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
460 c.description AS description, c.category
462 JOIN ar a ON (a.id = ac.trans_id)
463 JOIN parts p ON (ac.parts_id = p.id)
464 JOIN chart c on (p.income_accno_id = c.id)
466 -- use transdate from subwhere
467 WHERE 1 = 1 $subwhere
474 JOIN chart ON (chart_id = id)
475 WHERE link LIKE '%AR_paid%'
480 GROUP BY c.accno, c.description, c.category
484 SELECT c.accno AS accno, SUM(ac.sellprice) AS amount,
485 c.description AS description, c.category
487 JOIN ap a ON (a.id = ac.trans_id)
488 JOIN parts p ON (ac.parts_id = p.id)
489 JOIN chart c on (p.expense_accno_id = c.id)
491 WHERE 1 = 1 $subwhere
498 JOIN chart ON (chart_id = id)
499 WHERE link LIKE '%AP_paid%'
504 GROUP BY c.accno, c.description, c.category
510 if ($department_id) {
512 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
515 AND t.department_id = $department_id
521 SELECT c.accno, sum(ac.amount) AS amount,
522 c.description, c.category
524 JOIN chart c ON (c.id = ac.chart_id)
530 GROUP BY c.accno, c.description, c.category
533 if ($form->{project_id}) {
539 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
540 c.description AS description, c.category
542 JOIN ar a ON (a.id = ac.trans_id)
543 JOIN parts p ON (ac.parts_id = p.id)
544 JOIN chart c on (p.income_accno_id = c.id)
546 -- use transdate from subwhere
547 WHERE 1 = 1 $subwhere
551 GROUP BY c.accno, c.description, c.category
555 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
556 c.description AS description, c.category
558 JOIN ap a ON (a.id = ac.trans_id)
559 JOIN parts p ON (ac.parts_id = p.id)
560 JOIN chart c on (p.expense_accno_id = c.id)
562 WHERE 1 = 1 $subwhere
566 GROUP BY c.accno, c.description, c.category
577 my $sth = $dbh->prepare($query);
578 $sth->execute || $form->dberror($query);
580 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
582 if ($ref->{category} eq 'C') {
583 $ref->{category} = 'A';
586 # get last heading account
587 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
591 $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
593 $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
597 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
598 $form->{ $ref->{category} }{ $ref->{accno} }{description} =
600 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
603 $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
605 $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
610 # remove accounts with zero balance
611 foreach $category (@{$categories}) {
612 foreach $accno (keys %{ $form->{$category} }) {
613 $form->{$category}{$accno}{last} =
614 $form->round_amount($form->{$category}{$accno}{last},
615 $form->{decimalplaces});
616 $form->{$category}{$accno}{this} =
617 $form->round_amount($form->{$category}{$accno}{this},
618 $form->{decimalplaces});
620 delete $form->{$category}{$accno}
621 if ( $form->{$category}{$accno}{this} == 0
622 && $form->{$category}{$accno}{last} == 0);
626 $main::lxdebug->leave_sub();
630 $main::lxdebug->enter_sub();
632 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
634 my ($null, $department_id) = split /--/, $form->{department};
647 if ($form->{method} eq 'cash') {
648 $subwhere .= " AND transdate >= '$fromdate'";
649 $glwhere = " AND ac.transdate >= '$fromdate'";
650 $prwhere = " AND ar.transdate >= '$fromdate'";
652 $where .= " AND ac.transdate >= '$fromdate'";
657 $where .= " AND ac.transdate <= '$todate'";
658 $subwhere .= " AND transdate <= '$todate'";
659 $prwhere .= " AND ar.transdate <= '$todate'";
662 if ($department_id) {
664 JOIN department t ON (a.department_id = t.id)
667 AND t.id = $department_id
671 if ($form->{project_id}) {
673 AND ac.project_id = $form->{project_id}
677 if ($form->{method} eq 'cash') {
681 SELECT sum(ac.amount) AS amount,
684 JOIN chart c ON (c.id = ac.chart_id)
685 JOIN ar a ON (a.id = ac.trans_id)
693 JOIN chart ON (chart_id = id)
694 WHERE link LIKE '%AR_paid%'
703 SELECT sum(ac.amount) AS amount,
706 JOIN chart c ON (c.id = ac.chart_id)
707 JOIN ap a ON (a.id = ac.trans_id)
715 JOIN chart ON (chart_id = id)
716 WHERE link LIKE '%AP_paid%'
725 SELECT sum(ac.amount) AS amount,
728 JOIN chart c ON (c.id = ac.chart_id)
729 JOIN gl a ON (a.id = ac.trans_id)
734 AND NOT (c.link = 'AR' OR c.link = 'AP')
739 if ($form->{project_id}) {
745 SELECT SUM(ac.sellprice * ac.qty) AS amount,
748 JOIN ar a ON (a.id = ac.trans_id)
749 JOIN parts p ON (ac.parts_id = p.id)
750 JOIN chart c on (p.income_accno_id = c.id)
759 JOIN chart ON (chart_id = id)
760 WHERE link LIKE '%AR_paid%'
769 SELECT SUM(ac.sellprice) AS amount,
772 JOIN ap a ON (a.id = ac.trans_id)
773 JOIN parts p ON (ac.parts_id = p.id)
774 JOIN chart c on (p.expense_accno_id = c.id)
783 JOIN chart ON (chart_id = id)
784 WHERE link LIKE '%AP_paid%'
795 if ($department_id) {
797 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
800 AND t.department_id = $department_id
806 SELECT sum(ac.amount) AS amount,
809 JOIN chart c ON (c.id = ac.chart_id)
817 if ($form->{project_id}) {
823 SELECT SUM(ac.sellprice * ac.qty) AS amount,
826 JOIN ar a ON (a.id = ac.trans_id)
827 JOIN parts p ON (ac.parts_id = p.id)
828 JOIN chart c on (p.income_accno_id = c.id)
838 SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
841 JOIN ap a ON (a.id = ac.trans_id)
842 JOIN parts p ON (ac.parts_id = p.id)
843 JOIN chart c on (p.expense_accno_id = c.id)
860 my $sth = $dbh->prepare($query);
861 $sth->execute || $form->dberror($query);
863 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
864 if ($ref->{amount} < 0) {
865 $ref->{amount} *= -1;
867 if ($category eq "pos_bwa") {
869 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
871 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
874 $form->{ $ref->{$category} } += $ref->{amount};
879 $main::lxdebug->leave_sub();
883 $main::lxdebug->enter_sub();
885 my ($self, $myconfig, $form) = @_;
887 my $dbh = $form->dbconnect($myconfig);
889 my ($query, $sth, $ref);
892 my ($null, $department_id) = split /--/, $form->{department};
893 my @headingaccounts = ();
899 my $invwhere = $where;
901 if ($department_id) {
903 JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
906 AND t.department_id = $department_id
910 # project_id only applies to getting transactions
911 # it has nothing to do with a trial balance
912 # but we use the same function to collect information
914 if ($form->{project_id}) {
916 AND ac.project_id = $form->{project_id}
920 # get beginning balances
921 if ($form->{fromdate}) {
923 $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
926 JOIN chart c ON (ac.chart_id = c.id)
928 WHERE ac.transdate < '$form->{fromdate}'
931 GROUP BY c.accno, c.category, c.description
934 $sth = $dbh->prepare($query);
935 $sth->execute || $form->dberror($query);
937 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
938 $balance{ $ref->{accno} } = $ref->{amount};
940 if ($ref->{amount} != 0 && $form->{all_accounts}) {
941 $trb{ $ref->{accno} }{description} = $ref->{description};
942 $trb{ $ref->{accno} }{charttype} = 'A';
943 $trb{ $ref->{accno} }{category} = $ref->{category};
952 $query = qq|SELECT c.accno, c.description, c.category
954 WHERE c.charttype = 'H'
957 $sth = $dbh->prepare($query);
958 $sth->execute || $form->dberror($query);
960 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
961 $trb{ $ref->{accno} }{description} = $ref->{description};
962 $trb{ $ref->{accno} }{charttype} = 'H';
963 $trb{ $ref->{accno} }{category} = $ref->{category};
965 push @headingaccounts, $ref->{accno};
972 if ($form->{fromdate} || $form->{todate}) {
973 if ($form->{fromdate}) {
974 $tofrom .= " AND ac.transdate >= '$form->{fromdate}'";
975 $subwhere .= " AND transdate >= '$form->{fromdate}'";
976 $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
977 $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
979 if ($form->{todate}) {
980 $tofrom .= " AND ac.transdate <= '$form->{todate}'";
981 $invwhere .= " AND a.transdate <= '$form->{todate}'";
982 $subwhere .= " AND transdate <= '$form->{todate}'";
983 $glwhere .= " AND ac.transdate <= '$form->{todate}'";
987 $where .= qq| AND ((ac.trans_id in (SELECT id from ar)
992 JOIN chart ON (chart_id = id)
993 WHERE link LIKE '%AR_paid%'
995 )) OR (ac.trans_id in (SELECT id from ap)
1000 JOIN chart ON (chart_id = id)
1001 WHERE link LIKE '%AP_paid%'
1003 )) OR (ac.trans_id in (SELECT id from gl)
1011 $query = qq|SELECT c.accno, c.description, c.category,
1012 SUM(ac.amount) AS amount
1014 JOIN chart c ON (c.id = ac.chart_id)
1019 GROUP BY c.accno, c.description, c.category
1022 if ($form->{project_id}) {
1026 -- add project transactions from invoice
1030 SELECT c.accno, c.description, c.category,
1031 SUM(ac.sellprice * ac.qty) AS amount
1033 JOIN ar a ON (ac.trans_id = a.id)
1034 JOIN parts p ON (ac.parts_id = p.id)
1035 JOIN chart c ON (p.income_accno_id = c.id)
1040 GROUP BY c.accno, c.description, c.category
1044 SELECT c.accno, c.description, c.category,
1045 SUM(ac.sellprice * ac.qty) * -1 AS amount
1047 JOIN ap a ON (ac.trans_id = a.id)
1048 JOIN parts p ON (ac.parts_id = p.id)
1049 JOIN chart c ON (p.expense_accno_id = c.id)
1054 GROUP BY c.accno, c.description, c.category
1063 $sth = $dbh->prepare($query);
1064 $sth->execute || $form->dberror($query);
1066 # prepare query for each account
1067 $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1069 JOIN chart c ON (c.id = ac.chart_id)
1075 AND c.accno = ?) AS debit,
1077 (SELECT SUM(ac.amount)
1079 JOIN chart c ON (c.id = ac.chart_id)
1085 AND c.accno = ?) AS credit
1088 $drcr = $dbh->prepare($query);
1090 if ($form->{project_id}) {
1092 # prepare query for each account
1093 $query = qq|SELECT (SELECT SUM(ac.sellprice * ac.qty) * -1
1095 JOIN parts p ON (ac.parts_id = p.id)
1096 JOIN ap a ON (ac.trans_id = a.id)
1097 JOIN chart c ON (p.expense_accno_id = c.id)
1102 AND c.accno = ?) AS debit,
1104 (SELECT SUM(ac.sellprice * ac.qty)
1106 JOIN parts p ON (ac.parts_id = p.id)
1107 JOIN ar a ON (ac.trans_id = a.id)
1108 JOIN chart c ON (p.income_accno_id = c.id)
1113 AND c.accno = ?) AS credit
1116 $project_drcr = $dbh->prepare($query);
1120 # calculate the debit and credit in the period
1121 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1122 $trb{ $ref->{accno} }{description} = $ref->{description};
1123 $trb{ $ref->{accno} }{charttype} = 'A';
1124 $trb{ $ref->{accno} }{category} = $ref->{category};
1125 $trb{ $ref->{accno} }{amount} += $ref->{amount};
1129 my ($debit, $credit);
1131 foreach my $accno (sort keys %trb) {
1134 $ref->{accno} = $accno;
1135 map { $ref->{$_} = $trb{$accno}{$_} }
1136 qw(description category charttype amount);
1138 $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1140 if ($trb{$accno}{charttype} eq 'A') {
1143 $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
1145 ($debit, $credit) = (0, 0);
1146 while (($debit, $credit) = $drcr->fetchrow_array) {
1147 $ref->{debit} += $debit;
1148 $ref->{credit} += $credit;
1152 if ($form->{project_id}) {
1155 $project_drcr->execute($ref->{accno}, $ref->{accno})
1156 || $form->dberror($query);
1158 ($debit, $credit) = (0, 0);
1159 while (($debit, $credit) = $project_drcr->fetchrow_array) {
1160 $ref->{debit} += $debit;
1161 $ref->{credit} += $credit;
1163 $project_drcr->finish;
1166 $ref->{debit} = $form->round_amount($ref->{debit}, 2);
1167 $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1172 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1173 $accno = pop @accno;
1175 $trb{$accno}{debit} += $ref->{debit};
1176 $trb{$accno}{credit} += $ref->{credit};
1179 push @{ $form->{TB} }, $ref;
1185 # debits and credits for headings
1186 foreach $accno (@headingaccounts) {
1187 foreach $ref (@{ $form->{TB} }) {
1188 if ($accno eq $ref->{accno}) {
1189 $ref->{debit} = $trb{$accno}{debit};
1190 $ref->{credit} = $trb{$accno}{credit};
1195 $main::lxdebug->leave_sub();
1199 $main::lxdebug->enter_sub();
1200 my ($self, $dbh, $form) = @_;
1201 my $query = qq|SELECT invnumber FROM $form->{arap} WHERE invnumber LIKE "Storno zu "|;
1202 my $sth = $dbh->prepare($query);
1203 while(my $ref = $sth->fetchrow_hashref()) {
1204 $ref->{invnumer} =~ s/Storno zu //g;
1205 $form->{storno}{$ref->{invnumber}} = 1;
1207 $main::lxdebug->leave_sub();
1211 $main::lxdebug->enter_sub();
1213 my ($self, $myconfig, $form) = @_;
1215 # connect to database
1216 my $dbh = $form->dbconnect($myconfig);
1217 my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
1219 $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1221 my $where = " 1 = 1 ";
1224 if ($form->{"$form->{ct}_id"}) {
1225 $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
1227 if ($form->{ $form->{ct} }) {
1228 $name = $form->like(lc $form->{ $form->{ct} });
1229 $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{ $form->{ct} };
1234 if ($form->{department}) {
1235 ($null, $department_id) = split /--/, $form->{department};
1237 JOIN department d ON (a.department_id = d.id)
1240 $where .= qq| AND a.department_id = $department_id|;
1243 # select outstanding vendors or customers, depends on $ct
1244 my $query = qq|SELECT DISTINCT ct.id, ct.name
1245 FROM $form->{ct} ct, $form->{arap} a
1248 AND a.$form->{ct}_id = ct.id
1249 AND a.paid != a.amount
1250 AND (a.transdate <= '$form->{todate}')
1253 my $sth = $dbh->prepare($query);
1254 $sth->execute || $form->dberror;
1256 my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
1258 # for each company that has some stuff outstanding
1259 while (my ($id) = $sth->fetchrow_array) {
1263 -- between 0-30 days
1265 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1266 street, zipcode, city, country, contact, email,
1267 phone as customerphone, fax as customerfax, $form->{ct}number,
1268 "invnumber", "transdate",
1269 (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1270 "duedate", invoice, $form->{arap}.id,
1271 (SELECT $buysell FROM exchangerate
1272 WHERE $form->{arap}.curr = exchangerate.curr
1273 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1274 FROM $form->{arap}, $form->{ct}
1275 WHERE paid != amount
1276 AND $form->{arap}.storno IS FALSE
1277 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1278 AND $form->{ct}.id = $id
1280 transdate <= (date '$form->{todate}' - interval '0 days')
1281 AND transdate >= (date '$form->{todate}' - interval '30 days')
1286 -- between 31-60 days
1288 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1289 street, zipcode, city, country, contact, email,
1290 phone as customerphone, fax as customerfax, $form->{ct}number,
1291 "invnumber", "transdate",
1292 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1293 "duedate", invoice, $form->{arap}.id,
1294 (SELECT $buysell FROM exchangerate
1295 WHERE $form->{arap}.curr = exchangerate.curr
1296 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1297 FROM $form->{arap}, $form->{ct}
1298 WHERE paid != amount
1299 AND $form->{arap}.storno IS FALSE
1300 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1301 AND $form->{ct}.id = $id
1303 transdate < (date '$form->{todate}' - interval '30 days')
1304 AND transdate >= (date '$form->{todate}' - interval '60 days')
1309 -- between 61-90 days
1311 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1312 street, zipcode, city, country, contact, email,
1313 phone as customerphone, fax as customerfax, $form->{ct}number,
1314 "invnumber", "transdate",
1315 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1316 "duedate", invoice, $form->{arap}.id,
1317 (SELECT $buysell FROM exchangerate
1318 WHERE $form->{arap}.curr = exchangerate.curr
1319 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1320 FROM $form->{arap}, $form->{ct}
1321 WHERE paid != amount
1322 AND $form->{arap}.storno IS FALSE
1323 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1324 AND $form->{ct}.id = $id
1326 transdate < (date '$form->{todate}' - interval '60 days')
1327 AND transdate >= (date '$form->{todate}' - interval '90 days')
1334 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1335 street, zipcode, city, country, contact, email,
1336 phone as customerphone, fax as customerfax, $form->{ct}number,
1337 "invnumber", "transdate",
1338 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1339 "duedate", invoice, $form->{arap}.id,
1340 (SELECT $buysell FROM exchangerate
1341 WHERE $form->{arap}.curr = exchangerate.curr
1342 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1343 FROM $form->{arap}, $form->{ct}
1344 WHERE paid != amount
1345 AND $form->{arap}.storno IS FALSE
1346 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1347 AND $form->{ct}.id = $id
1348 AND transdate < (date '$form->{todate}' - interval '90 days')
1352 ctid, transdate, invnumber
1356 my $sth = $dbh->prepare($query);
1357 $sth->execute || $form->dberror;
1359 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1360 $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
1361 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1362 push @{ $form->{AG} }, $ref;
1374 $main::lxdebug->leave_sub();
1378 $main::lxdebug->enter_sub();
1380 my ($self, $myconfig, $form) = @_;
1382 # connect to database
1383 my $dbh = $form->dbconnect($myconfig);
1385 my $query = qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1387 WHERE ct.id = $form->{"$form->{ct}_id"}|;
1388 my $sth = $dbh->prepare($query);
1389 $sth->execute || $form->dberror;
1391 ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1392 $sth->fetchrow_array;
1396 $main::lxdebug->leave_sub();
1399 sub get_taxaccounts {
1400 $main::lxdebug->enter_sub();
1402 my ($self, $myconfig, $form) = @_;
1404 # connect to database
1405 my $dbh = $form->dbconnect($myconfig);
1408 my $query = qq|SELECT c.accno, c.description, t.rate
1410 WHERE c.link LIKE '%CT_tax%'
1411 AND c.id = t.chart_id
1413 my $sth = $dbh->prepare($query);
1414 $sth->execute || $form->dberror;
1417 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1418 push @{ $form->{taxaccounts} }, $ref;
1424 $main::lxdebug->leave_sub();
1428 $main::lxdebug->enter_sub();
1430 my ($self, $myconfig, $form) = @_;
1432 # connect to database
1433 my $dbh = $form->dbconnect($myconfig);
1435 my ($null, $department_id) = split /--/, $form->{department};
1438 my $where = "1 = 1";
1440 if ($department_id) {
1442 AND a.department_id = $department_id
1448 if ($form->{accno}) {
1449 $accno = $form->{accno};
1450 $rate = $form->{"$form->{accno}_rate"};
1451 $accno = qq| AND ch.accno = '$accno'|;
1457 if ($form->{db} eq 'ar') {
1458 $table = "customer";
1461 if ($form->{db} eq 'ap') {
1466 my $transdate = "a.transdate";
1468 if ($form->{method} eq 'cash') {
1469 $transdate = "a.datepaid";
1472 ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig);
1479 JOIN chart ON (chart_id = id)
1480 WHERE link LIKE '%${ARAP}_paid%'
1481 AND transdate <= '$todate'
1486 # if there are any dates construct a where
1487 if ($form->{fromdate} || $form->{todate}) {
1488 if ($form->{fromdate}) {
1489 $where .= " AND $transdate >= '$form->{fromdate}'";
1491 if ($form->{todate}) {
1492 $where .= " AND $transdate <= '$form->{todate}'";
1496 my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1498 my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1499 $sortorder = $form->{sort} if $form->{sort};
1501 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
1502 a.invnumber, n.name, a.netamount,
1503 ac.amount * $ml AS tax
1505 JOIN $form->{db} a ON (a.id = ac.trans_id)
1506 JOIN chart ch ON (ch.id = ac.chart_id)
1507 JOIN $table n ON (n.id = a.${table}_id)
1512 SELECT a.id, '1' AS invoice, $transdate AS transdate,
1513 a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1514 i.sellprice * i.qty * $rate * $ml AS tax
1516 JOIN $form->{db} a ON (a.id = ac.trans_id)
1517 JOIN chart ch ON (ch.id = ac.chart_id)
1518 JOIN $table n ON (n.id = a.${table}_id)
1519 JOIN ${table}tax t ON (t.${table}_id = n.id)
1520 JOIN invoice i ON (i.trans_id = a.id)
1521 JOIN partstax p ON (p.parts_id = i.parts_id)
1525 ORDER by $sortorder|;
1527 if ($form->{report} =~ /nontaxable/) {
1529 # only gather up non-taxable transactions
1530 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
1531 a.invnumber, n.name, a.netamount
1533 JOIN $form->{db} a ON (a.id = ac.trans_id)
1534 JOIN $table n ON (n.id = a.${table}_id)
1537 AND a.netamount = a.amount
1539 SELECT a.id, '1' AS invoice, $transdate AS transdate,
1540 a.invnumber, n.name, i.sellprice * i.qty AS netamount
1542 JOIN $form->{db} a ON (a.id = ac.trans_id)
1543 JOIN $table n ON (n.id = a.${table}_id)
1544 JOIN invoice i ON (i.trans_id = a.id)
1548 a.${table}_id NOT IN (
1549 SELECT ${table}_id FROM ${table}tax t (${table}_id)
1552 SELECT parts_id FROM partstax p (parts_id)
1555 GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
1556 ORDER by $sortorder|;
1559 my $sth = $dbh->prepare($query);
1560 $sth->execute || $form->dberror($query);
1562 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1563 push @{ $form->{TR} }, $ref;
1569 $main::lxdebug->leave_sub();
1572 sub paymentaccounts {
1573 $main::lxdebug->enter_sub();
1575 my ($self, $myconfig, $form) = @_;
1577 # connect to database, turn AutoCommit off
1578 my $dbh = $form->dbconnect_noauto($myconfig);
1580 my $ARAP = uc $form->{db};
1582 # get A(R|P)_paid accounts
1583 my $query = qq|SELECT c.accno, c.description
1585 WHERE c.link LIKE '%${ARAP}_paid%'|;
1586 my $sth = $dbh->prepare($query);
1587 $sth->execute || $form->dberror($query);
1589 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1590 push @{ $form->{PR} }, $ref;
1596 $main::lxdebug->leave_sub();
1600 $main::lxdebug->enter_sub();
1602 my ($self, $myconfig, $form) = @_;
1604 # connect to database, turn AutoCommit off
1605 my $dbh = $form->dbconnect_noauto($myconfig);
1608 if ($form->{db} eq 'ar') {
1609 $table = 'customer';
1612 if ($form->{db} eq 'ap') {
1620 if ($form->{department_id}) {
1622 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
1626 AND t.department_id = $form->{department_id}
1630 if ($form->{fromdate}) {
1631 $where .= " AND ac.transdate >= '$form->{fromdate}'";
1633 if ($form->{todate}) {
1634 $where .= " AND ac.transdate <= '$form->{todate}'";
1636 if (!$form->{fx_transaction}) {
1637 $where .= " AND ac.fx_transaction = '0'";
1642 if ($form->{reference}) {
1643 $reference = $form->like(lc $form->{reference});
1644 $invnumber = " AND lower(a.invnumber) LIKE '$reference'";
1645 $reference = " AND lower(g.reference) LIKE '$reference'";
1647 if ($form->{source}) {
1648 my $source = $form->like(lc $form->{source});
1649 $where .= " AND lower(ac.source) LIKE '$source'";
1651 if ($form->{memo}) {
1652 my $memo = $form->like(lc $form->{memo});
1653 $where .= " AND lower(ac.memo) LIKE '$memo'";
1656 my $sortorder = join ', ',
1657 $form->sort_columns(qw(name invnumber ordnumber transdate source));
1658 $sortorder = $form->{sort} if $form->{sort};
1660 # cycle through each id
1661 foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1663 $query = qq|SELECT c.id, c.accno, c.description
1665 WHERE c.accno = '$accno'|;
1666 $sth = $dbh->prepare($query);
1667 $sth->execute || $form->dberror($query);
1669 my $ref = $sth->fetchrow_hashref(NAME_lc);
1670 push @{ $form->{PR} }, $ref;
1673 $query = qq|SELECT c.name, a.invnumber, a.ordnumber,
1674 ac.transdate, ac.amount * $ml AS paid, ac.source,
1675 a.invoice, a.id, ac.memo, '$form->{db}' AS module
1677 JOIN $form->{db} a ON (ac.trans_id = a.id)
1678 JOIN $table c ON (c.id = a.${table}_id)
1680 WHERE ac.chart_id = $ref->{id}
1685 SELECT g.description, g.reference, NULL AS ordnumber,
1686 ac.transdate, ac.amount * $ml AS paid, ac.source,
1687 '0' as invoice, g.id, ac.memo, 'gl' AS module
1689 JOIN gl g ON (g.id = ac.trans_id)
1691 WHERE ac.chart_id = $ref->{id}
1694 AND (ac.amount * $ml) > 0
1695 ORDER BY $sortorder|;
1697 $sth = $dbh->prepare($query);
1698 $sth->execute || $form->dberror($query);
1700 while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
1701 push @{ $form->{ $ref->{id} } }, $pr;
1709 $main::lxdebug->leave_sub();
1713 $main::lxdebug->enter_sub();
1715 my ($self, $myconfig, $form) = @_;
1717 # connect to database
1718 my $dbh = $form->dbconnect($myconfig);
1720 my $last_period = 0;
1721 my $category = "pos_bwa";
1723 qw(1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40);
1725 $form->{decimalplaces} *= 1;
1727 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1730 # if there are any compare dates
1731 if ($form->{fromdate} || $form->{todate}) {
1733 if ($form->{fromdate}) {
1734 $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1737 $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1740 $kummfromdate = $form->{comparefromdate};
1741 $kummtodate = $form->{comparetodate};
1742 &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form,
1746 @periods = qw(jetzt kumm);
1747 @gesamtleistung = qw(1 2 3);
1748 @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 19 20);
1750 qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1752 foreach $key (@periods) {
1753 $form->{ "$key" . "gesamtleistung" } = 0;
1754 $form->{ "$key" . "gesamtkosten" } = 0;
1756 foreach $category (@categories) {
1758 if (defined($form->{$category}{$key})) {
1759 $form->{"$key$category"} =
1760 $form->format_amount($myconfig,
1761 $form->round_amount($form->{$category}{$key}, 2
1763 $form->{decimalplaces},
1767 foreach $item (@gesamtleistung) {
1768 $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1770 foreach $item (@gesamtkosten) {
1771 $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1773 $form->{ "$key" . "rohertrag" } =
1774 $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1775 $form->{ "$key" . "betriebrohertrag" } =
1776 $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1777 $form->{ "$key" . "betriebsergebnis" } =
1778 $form->{ "$key" . "betriebrohertrag" } -
1779 $form->{ "$key" . "gesamtkosten" };
1780 $form->{ "$key" . "neutraleraufwand" } =
1781 $form->{30}{$key} + $form->{31}{$key};
1782 $form->{ "$key" . "neutralertrag" } =
1783 $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1784 $form->{ "$key" . "ergebnisvorsteuern" } =
1785 $form->{ "$key" . "betriebsergebnis" } -
1786 $form->{ "$key" . "neutraleraufwand" } +
1787 $form->{ "$key" . "neutralertrag" };
1788 $form->{ "$key" . "ergebnis" } =
1789 $form->{ "$key" . "ergebnisvorsteuern" } + $form->{35}{$key};
1791 if ($form->{ "$key" . "gesamtleistung" } > 0) {
1792 foreach $category (@categories) {
1793 if (defined($form->{$category}{$key})) {
1794 $form->{ "$key" . "gl" . "$category" } =
1795 $form->format_amount(
1797 $form->round_amount(
1798 ($form->{$category}{$key} /
1799 $form->{ "$key" . "gesamtleistung" } * 100
1801 $form->{decimalplaces}
1803 $form->{decimalplaces},
1807 foreach $item (@ergebnisse) {
1808 $form->{ "$key" . "gl" . "$item" } =
1809 $form->format_amount($myconfig,
1810 $form->round_amount(
1811 ( $form->{ "$key" . "$item" } /
1812 $form->{ "$key" . "gesamtleistung" } * 100
1814 $form->{decimalplaces}
1816 $form->{decimalplaces},
1821 if ($form->{ "$key" . "gesamtkosten" } > 0) {
1822 foreach $category (@categories) {
1823 if (defined($form->{$category}{$key})) {
1824 $form->{ "$key" . "gk" . "$category" } =
1825 $form->format_amount($myconfig,
1826 $form->round_amount(
1827 ($form->{$category}{$key} /
1828 $form->{ "$key" . "gesamtkosten" } * 100
1830 $form->{decimalplaces}
1832 $form->{decimalplaces},
1836 foreach $item (@ergebnisse) {
1837 $form->{ "$key" . "gk" . "$item" } =
1838 $form->format_amount($myconfig,
1839 $form->round_amount(
1840 ($form->{ "$key" . "$item" } /
1841 $form->{ "$key" . "gesamtkosten" } * 100
1843 $form->{decimalplaces}
1845 $form->{decimalplaces},
1850 if ($form->{10}{$key} > 0) {
1851 foreach $category (@categories) {
1852 if (defined($form->{$category}{$key})) {
1853 $form->{ "$key" . "pk" . "$category" } =
1854 $form->format_amount(
1856 $form->round_amount(
1857 ($form->{$category}{$key} / $form->{10}{$key} * 100),
1858 $form->{decimalplaces}
1860 $form->{decimalplaces},
1864 foreach $item (@ergebnisse) {
1865 $form->{ "$key" . "pk" . "$item" } =
1866 $form->format_amount($myconfig,
1867 $form->round_amount(
1868 ($form->{ "$key" . "$item" } /
1869 $form->{10}{$key} * 100
1871 $form->{decimalplaces}
1873 $form->{decimalplaces},
1878 if ($form->{4}{$key} > 0) {
1879 foreach $category (@categories) {
1880 if (defined($form->{$category}{$key})) {
1881 $form->{ "$key" . "auf" . "$category" } =
1882 $form->format_amount(
1884 $form->round_amount(
1885 ($form->{$category}{$key} / $form->{4}{$key} * 100),
1886 $form->{decimalplaces}
1888 $form->{decimalplaces},
1892 foreach $item (@ergebnisse) {
1893 $form->{ "$key" . "auf" . "$item" } =
1894 $form->format_amount($myconfig,
1895 $form->round_amount(
1896 ($form->{ "$key" . "$item" } /
1897 $form->{4}{$key} * 100
1899 $form->{decimalplaces}
1901 $form->{decimalplaces},
1906 foreach $item (@ergebnisse) {
1907 $form->{ "$key" . "$item" } =
1908 $form->format_amount($myconfig,
1909 $form->round_amount($form->{ "$key" . "$item" },
1910 $form->{decimalplaces}
1912 $form->{decimalplaces},
1919 $main::lxdebug->leave_sub();
1923 $main::lxdebug->enter_sub();
1925 my ($self, $myconfig, $form) = @_;
1927 # connect to database
1928 my $dbh = $form->dbconnect($myconfig);
1930 my $last_period = 0;
1931 my $category = "pos_ustva";
1932 my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
1933 96 66 43 45 53 62 65 67);
1934 my @categories_euro = qw(48 51 86 91 97 93 94);
1935 $form->{decimalplaces} *= 1;
1937 foreach $item (@categories_cent) {
1938 $form->{"$item"} = 0;
1940 foreach $item (@categories_euro) {
1941 $form->{"$item"} = 0;
1944 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
1947 # foreach $item (@categories_cent) {
1948 # if ($form->{$item}{"jetzt"} > 0) {
1949 # $form->{$item} = $form->{$item}{"jetzt"};
1950 # delete $form->{$item}{"jetzt"};
1953 # foreach $item (@categories_euro) {
1954 # if ($form->{$item}{"jetzt"} > 0) {
1955 # $form->{$item} = $form->{$item}{"jetzt"};
1956 # delete $form->{$item}{"jetzt"};
1957 # } foreach $item (@categories_cent) {
1958 # if ($form->{$item}{"jetzt"} > 0) {
1959 # $form->{$item} = $form->{$item}{"jetzt"};
1960 # delete $form->{$item}{"jetzt"};
1963 # foreach $item (@categories_euro) {
1964 # if ($form->{$item}{"jetzt"} > 0) {
1965 # $form->{$item} = $form->{$item}{"jetzt"};
1966 # delete $form->{$item}{"jetzt"};
1973 # Berechnung der USTVA Formularfelder
1975 $form->{"51r"} = $form->{"511"};
1976 $form->{"86r"} = $form->{"861"};
1977 $form->{"97r"} = $form->{"971"};
1978 $form->{"93r"} = $form->{"931"};
1980 #$form->{"96"} = $form->{"94"} * 0.16;
1982 $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
1984 $form->{"45"} = $form->{"43"};
1985 $form->{"53"} = $form->{"43"};
1986 $form->{"62"} = $form->{"43"} - $form->{"66"};
1987 $form->{"65"} = $form->{"43"} - $form->{"66"};
1988 $form->{"67"} = $form->{"43"} - $form->{"66"};
1990 foreach $item (@categories_cent) {
1992 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
1996 foreach $item (@categories_euro) {
1998 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
2004 $main::lxdebug->leave_sub();
2007 sub income_statement {
2008 $main::lxdebug->enter_sub();
2010 my ($self, $myconfig, $form) = @_;
2012 # connect to database
2013 my $dbh = $form->dbconnect($myconfig);
2015 my $last_period = 0;
2016 my $category = "pos_eur";
2017 my @categories_einnahmen = qw(1 2 3 4 5 6 7);
2018 my @categories_ausgaben =
2019 qw(8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31);
2021 my @ergebnisse = qw(sumeura sumeurb guvsumme);
2023 $form->{decimalplaces} *= 1;
2025 foreach $item (@categories_einnahmen) {
2028 foreach $item (@categories_ausgaben) {
2032 foreach $item (@ergebnisse) {
2036 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2039 foreach $item (@categories_einnahmen) {
2040 $form->{"eur${item}"} =
2041 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2042 $form->{"sumeura"} += $form->{$item};
2044 foreach $item (@categories_ausgaben) {
2045 $form->{"eur${item}"} =
2046 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2047 $form->{"sumeurb"} += $form->{$item};
2050 $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
2052 foreach $item (@ergebnisse) {
2054 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2056 $main::lxdebug->leave_sub();