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 #======================================================================
41 $main::lxdebug->enter_sub();
43 my ($self, $myconfig, $form) = @_;
46 my $dbh = $form->dbconnect($myconfig);
49 my @categories = qw(A C L Q);
51 # if there are any dates construct a where
52 if ($form->{asofdate}) {
53 $form->{period} = $form->{this_period} = conv_dateq($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} = conv_dateq($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 my $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};
330 my $category = qq| AND (| . join(" OR ", map({ "(c.category = " . $dbh->quote($_) . ")" } @{$categories})) . qq|) |;
334 qq|SELECT c.accno, c.description, c.category
336 WHERE (c.charttype = 'H')
340 $sth = prepare_execute_query($form, $dbh, $query);
342 my @headingaccounts = ();
343 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
344 $form->{ $ref->{category} }{ $ref->{accno} }{description} =
345 "$ref->{description}";
346 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
347 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
349 push @headingaccounts, $ref->{accno};
355 $fromdate = conv_dateq($fromdate);
356 if ($form->{method} eq 'cash') {
357 $subwhere .= " AND (transdate >= $fromdate)";
358 $glwhere = " AND (ac.transdate >= $fromdate)";
360 $where .= " AND (ac.transdate >= $fromdate)";
365 $todate = conv_dateq($todate);
366 $where .= " AND (ac.transdate <= $todate)";
367 $subwhere .= " AND (transdate <= $todate)";
370 if ($department_id) {
371 $dpt_join = qq| JOIN department t ON (a.department_id = t.id) |;
372 $dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|)|;
375 if ($form->{project_id}) {
376 $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
379 if ($form->{method} eq 'cash') {
381 qq|SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
383 JOIN chart c ON (c.id = ac.chart_id)
384 JOIN ar a ON (a.id = ac.trans_id)
393 JOIN chart ON (chart_id = id)
394 WHERE (link LIKE '%AR_paid%')
398 GROUP BY c.accno, c.description, c.category
402 SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
404 JOIN chart c ON (c.id = ac.chart_id)
405 JOIN ap a ON (a.id = ac.trans_id)
414 JOIN chart ON (chart_id = id)
415 WHERE (link LIKE '%AP_paid%')
419 GROUP BY c.accno, c.description, c.category
423 SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
425 JOIN chart c ON (c.id = ac.chart_id)
426 JOIN gl a ON (a.id = ac.trans_id)
432 AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
434 GROUP BY c.accno, c.description, c.category |;
436 if ($form->{project_id}) {
441 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
443 JOIN ar a ON (a.id = ac.trans_id)
444 JOIN parts p ON (ac.parts_id = p.id)
445 JOIN chart c on (p.income_accno_id = c.id)
447 -- use transdate from subwhere
448 WHERE (c.category = 'I')
455 JOIN chart ON (chart_id = id)
456 WHERE (link LIKE '%AR_paid%')
460 GROUP BY c.accno, c.description, c.category
464 SELECT c.accno AS accno, SUM(ac.sellprice) AS amount, c.description AS description, c.category
466 JOIN ap a ON (a.id = ac.trans_id)
467 JOIN parts p ON (ac.parts_id = p.id)
468 JOIN chart c on (p.expense_accno_id = c.id)
470 WHERE (c.category = 'E')
477 JOIN chart ON (chart_id = id)
478 WHERE link LIKE '%AP_paid%'
482 GROUP BY c.accno, c.description, c.category |;
485 } else { # if ($form->{method} eq 'cash')
486 if ($department_id) {
487 $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
488 $dpt_where = qq| AND t.department_id = $department_id |;
492 SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
494 JOIN chart c ON (c.id = ac.chart_id)
500 GROUP BY c.accno, c.description, c.category |;
502 if ($form->{project_id}) {
506 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, c.description AS description, c.category
508 JOIN ar a ON (a.id = ac.trans_id)
509 JOIN parts p ON (ac.parts_id = p.id)
510 JOIN chart c on (p.income_accno_id = c.id)
512 -- use transdate from subwhere
513 WHERE (c.category = 'I')
517 GROUP BY c.accno, c.description, c.category
521 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount, c.description AS description, c.category
523 JOIN ap a ON (a.id = ac.trans_id)
524 JOIN parts p ON (ac.parts_id = p.id)
525 JOIN chart c on (p.expense_accno_id = c.id)
527 WHERE (c.category = 'E')
531 GROUP BY c.accno, c.description, c.category |;
539 my $sth = prepare_execute_query($form, $dbh, $query);
541 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
543 if ($ref->{category} eq 'C') {
544 $ref->{category} = 'A';
547 # get last heading account
548 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
552 $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
554 $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
558 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
559 $form->{ $ref->{category} }{ $ref->{accno} }{description} =
561 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
564 $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
566 $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
571 # remove accounts with zero balance
572 foreach $category (@{$categories}) {
573 foreach $accno (keys %{ $form->{$category} }) {
574 $form->{$category}{$accno}{last} =
575 $form->round_amount($form->{$category}{$accno}{last},
576 $form->{decimalplaces});
577 $form->{$category}{$accno}{this} =
578 $form->round_amount($form->{$category}{$accno}{this},
579 $form->{decimalplaces});
581 delete $form->{$category}{$accno}
582 if ( $form->{$category}{$accno}{this} == 0
583 && $form->{$category}{$accno}{last} == 0);
587 $main::lxdebug->leave_sub();
591 $main::lxdebug->enter_sub();
593 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
595 my ($null, $department_id) = split /--/, $form->{department};
608 $fromdate = conv_dateq($fromdate);
609 if ($form->{method} eq 'cash') {
610 $subwhere .= " AND (transdate >= $fromdate)";
611 $glwhere = " AND (ac.transdate >= $fromdate)";
612 $prwhere = " AND (ar.transdate >= $fromdate)";
614 $where .= " AND (ac.transdate >= $fromdate)";
619 $todate = conv_dateq($todate);
620 $subwhere .= " AND (transdate <= $todate)";
621 $where .= " AND (ac.transdate <= $todate)";
622 $prwhere .= " AND (ar.transdate <= $todate)";
625 if ($department_id) {
626 $dpt_join = qq| JOIN department t ON (a.department_id = t.id) |;
627 $dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|) |;
630 if ($form->{project_id}) {
631 $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}) . qq|) |;
634 if ($form->{method} eq 'cash') {
637 SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
639 JOIN chart c ON (c.id = ac.chart_id)
640 JOIN ar a ON (a.id = ac.trans_id)
642 WHERE $where $dpt_where
643 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
649 SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
651 JOIN chart c ON (c.id = ac.chart_id)
652 JOIN ap a ON (a.id = ac.trans_id)
654 WHERE $where $dpt_where
655 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
661 SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
663 JOIN chart c ON (c.id = ac.chart_id)
664 JOIN gl a ON (a.id = ac.trans_id)
666 WHERE $where $dpt_where $glwhere
667 AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
674 if ($form->{project_id}) {
678 SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
680 JOIN ar a ON (a.id = ac.trans_id)
681 JOIN parts p ON (ac.parts_id = p.id)
682 JOIN chart c on (p.income_accno_id = c.id)
684 WHERE (c.category = 'I') $prwhere $dpt_where
685 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
691 SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category
693 JOIN ap a ON (a.id = ac.trans_id)
694 JOIN parts p ON (ac.parts_id = p.id)
695 JOIN chart c on (p.expense_accno_id = c.id)
697 WHERE (c.category = 'E') $prwhere $dpt_where
698 AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
704 } else { # if ($form->{method} eq 'cash')
705 if ($department_id) {
706 $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
707 $dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
711 SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
713 JOIN chart c ON (c.id = ac.chart_id)
718 GROUP BY c.$category |;
720 if ($form->{project_id}) {
724 SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
726 JOIN ar a ON (a.id = ac.trans_id)
727 JOIN parts p ON (ac.parts_id = p.id)
728 JOIN chart c on (p.income_accno_id = c.id)
730 WHERE (c.category = 'I')
738 SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
740 JOIN ap a ON (a.id = ac.trans_id)
741 JOIN parts p ON (ac.parts_id = p.id)
742 JOIN chart c on (p.expense_accno_id = c.id)
744 WHERE (c.category = 'E')
748 GROUP BY c.$category |;
756 my $sth = prepare_execute_query($form, $dbh, $query);
758 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
759 if ($category eq "pos_bwa") {
761 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
763 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
766 $form->{ $ref->{$category} } += $ref->{amount};
771 $main::lxdebug->leave_sub();
775 $main::lxdebug->enter_sub();
777 my ($self, $myconfig, $form) = @_;
779 my $dbh = $form->dbconnect($myconfig);
781 my ($query, $sth, $ref);
784 my ($null, $department_id) = split /--/, $form->{department};
785 my @headingaccounts = ();
791 my $invwhere = $where;
793 if ($department_id) {
794 $dpt_join = qq| JOIN dpt_trans t ON (ac.trans_id = t.trans_id) |;
795 $dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
798 # project_id only applies to getting transactions
799 # it has nothing to do with a trial balance
800 # but we use the same function to collect information
802 if ($form->{project_id}) {
803 $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}, 'NULL') . qq|) |;
806 # get beginning balances
808 qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
810 JOIN chart c ON (ac.chart_id = c.id)
812 WHERE (ac.transdate < (select date_trunc('year', date ?)))
815 GROUP BY c.accno, c.category, c.description |;
817 $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate});
819 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
821 if ($ref->{amount} != 0 || $form->{all_accounts}) {
822 $trb{ $ref->{accno} }{description} = $ref->{description};
823 $trb{ $ref->{accno} }{charttype} = 'A';
824 if ($ref->{category} ne "I" && $ref->{category} ne "E") {
825 if ($ref->{amount} > 0) {
826 $trb{ $ref->{accno} }{haben_eb} = $ref->{amount};
828 $trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1;
831 $trb{ $ref->{accno} }{category} = $ref->{category};
840 qq|SELECT c.accno, c.description, c.category
842 WHERE c.charttype = 'H'
845 $sth = prepare_execute_query($form, $dbh, $query);
847 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
848 $trb{ $ref->{accno} }{description} = $ref->{description};
849 $trb{ $ref->{accno} }{charttype} = 'H';
850 $trb{ $ref->{accno} }{category} = $ref->{category};
852 push @headingaccounts, $ref->{accno};
858 $saldowhere = " 1 = 1 ";
859 $sumwhere = " 1 = 1 ";
862 if ($form->{fromdate} || $form->{todate}) {
863 if ($form->{fromdate}) {
864 my $fromdate = conv_dateq($form->{fromdate});
865 $tofrom .= " AND (ac.transdate >= $fromdate)";
866 $subwhere .= " AND (transdate >= $fromdate)";
867 $sumsubwhere .= " AND (transdate >= (select date_trunc('year', date $fromdate))) ";
868 $saldosubwhere .= " AND (((c.category='I' OR c.category='E') AND transdate>=(select date_trunc('year', date $fromdate))) OR (c.category NOT IN ('I', 'E'))) ";
869 $invwhere .= " AND (a.transdate >= $fromdate)";
870 $glsaldowhere .= " AND (((c.category='I' OR c.category='E') AND ac.transdate>=(select date_trunc('year', date $fromdate))) OR (c.category NOT IN ('I', 'E'))) ";
871 $glwhere = " AND (ac.transdate >= $fromdate)";
872 $glsumwhere = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) ";
874 if ($form->{todate}) {
875 my $todate = conv_dateq($form->{todate});
876 $tofrom .= " AND (ac.transdate <= $todate)";
877 $invwhere .= " AND (a.transdate <= $todate)";
878 $saldosubwhere .= " AND (transdate <= $todate)";
879 $sumsubwhere .= " AND (transdate <= $todate)";
880 $subwhere .= " AND (transdate <= $todate)";
881 $glwhere .= " AND (ac.transdate <= $todate)";
882 $glsumwhere .= " AND (ac.transdate <= $todate) ";
883 $glsaldowhere .= " AND (ac.transdate <= $todate) ";
887 if ($form->{method} eq "cash") {
889 qq| AND ((ac.trans_id IN (SELECT id from ar) AND
894 JOIN chart ON (chart_id = id)
895 WHERE (link LIKE '%AR_paid%')
900 (ac.trans_id in (SELECT id from ap) AND
905 JOIN chart ON (chart_id = id)
906 WHERE (link LIKE '%AP_paid%')
911 (ac.trans_id in (SELECT id from gl)
915 qq| AND ((ac.trans_id IN (SELECT id from ar) AND
920 JOIN chart ON (chart_id = id)
921 WHERE (link LIKE '%AR_paid%')
926 (ac.trans_id in (SELECT id from ap) AND
931 JOIN chart ON (chart_id = id)
932 WHERE (link LIKE '%AP_paid%')
937 (ac.trans_id in (SELECT id from gl)
941 qq| AND ((ac.trans_id IN (SELECT id from ar) AND
946 JOIN chart ON (chart_id = id)
947 WHERE (link LIKE '%AR_paid%')
952 (ac.trans_id in (SELECT id from ap) AND
957 JOIN chart ON (chart_id = id)
958 WHERE (link LIKE '%AP_paid%')
963 (ac.trans_id in (SELECT id from gl)
969 $saldowhere .= $glsaldowhere;
970 $sumwhere .= $glsumwhere;
974 SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
976 JOIN chart c ON (c.id = ac.chart_id)
981 GROUP BY c.accno, c.description, c.category |;
983 if ($form->{project_id}) {
985 -- add project transactions from invoice
989 SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) AS amount
991 JOIN ar a ON (ac.trans_id = a.id)
992 JOIN parts p ON (ac.parts_id = p.id)
993 JOIN chart c ON (p.income_accno_id = c.id)
998 GROUP BY c.accno, c.description, c.category
1002 SELECT c.accno, c.description, c.category, SUM(ac.sellprice * ac.qty) * -1 AS amount
1004 JOIN ap a ON (ac.trans_id = a.id)
1005 JOIN parts p ON (ac.parts_id = p.id)
1006 JOIN chart c ON (p.expense_accno_id = c.id)
1011 GROUP BY c.accno, c.description, c.category
1015 $query .= qq| ORDER BY accno|;
1017 $sth = prepare_execute_query($form, $dbh, $query);
1019 # calculate the debit and credit in the period
1020 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1021 $trb{ $ref->{accno} }{description} = $ref->{description};
1022 $trb{ $ref->{accno} }{charttype} = 'A';
1023 $trb{ $ref->{accno} }{category} = $ref->{category};
1024 $trb{ $ref->{accno} }{amount} += $ref->{amount};
1028 # prepare query for each account
1029 my ($q_drcr, $drcr, $q_project_drcr, $project_drcr);
1033 (SELECT SUM(ac.amount) * -1
1035 JOIN chart c ON (c.id = ac.chart_id)
1041 AND (c.accno = ?)) AS debit,
1043 (SELECT SUM(ac.amount)
1045 JOIN chart c ON (c.id = ac.chart_id)
1051 AND c.accno = ?) AS credit,
1052 (SELECT SUM(ac.amount)
1054 JOIN chart c ON (ac.chart_id = c.id)
1059 AND c.accno = ?) AS saldo,
1061 (SELECT SUM(ac.amount)
1063 JOIN chart c ON (ac.chart_id = c.id)
1069 AND c.accno = ?) AS sum_credit,
1071 (SELECT SUM(ac.amount)
1073 JOIN chart c ON (ac.chart_id = c.id)
1079 AND c.accno = ?) AS sum_debit,
1081 (SELECT max(ac.transdate) FROM acc_trans ac
1082 JOIN chart c ON (ac.chart_id = c.id)
1087 AND c.accno = ?) AS last_transaction
1092 $drcr = prepare_query($form, $dbh, $q_drcr);
1094 if ($form->{project_id}) {
1095 # prepare query for each account
1098 (SELECT SUM(ac.sellprice * ac.qty) * -1
1100 JOIN parts p ON (ac.parts_id = p.id)
1101 JOIN ap a ON (ac.trans_id = a.id)
1102 JOIN chart c ON (p.expense_accno_id = c.id)
1107 AND c.accno = ?) AS debit,
1109 (SELECT SUM(ac.sellprice * ac.qty)
1111 JOIN parts p ON (ac.parts_id = p.id)
1112 JOIN ar a ON (ac.trans_id = a.id)
1113 JOIN chart c ON (p.income_accno_id = c.id)
1118 AND c.accno = ?) AS credit,
1120 (SELECT SUM(ac.amount)
1122 JOIN chart c ON (ac.chart_id = c.id)
1127 AND c.accno = ?) AS saldo,
1129 (SELECT SUM(ac.amount)
1131 JOIN chart c ON (ac.chart_id = c.id)
1137 AND c.accno = ?) AS sum_credit,
1139 (SELECT SUM(ac.amount)
1141 JOIN chart c ON (ac.chart_id = c.id)
1147 AND c.accno = ?) AS sum_debit,
1150 (SELECT max(ac.transdate) FROM acc_trans ac
1151 JOIN chart c ON (ac.chart_id = c.id)
1156 AND c.accno = ?) AS last_transaction
1159 $project_drcr = prepare_query($form, $dbh, $q_project_drcr);
1162 my ($debit, $credit, $saldo, $soll_saldo, $haben_saldo,$soll_kummuliert, $haben_kummuliert, $last_transaction);
1164 foreach my $accno (sort keys %trb) {
1167 $ref->{accno} = $accno;
1168 map { $ref->{$_} = $trb{$accno}{$_} }
1169 qw(description category charttype amount soll_eb haben_eb);
1171 $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1173 if ($trb{$accno}{charttype} eq 'A') {
1176 do_statement($form, $drcr, $q_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1178 ($debit, $credit, $saldo, $haben_saldo, $soll_saldo, $soll_kumuliert, $haben_kumuliert) = (0, 0, 0, 0, 0, 0, 0);
1179 $last_transaction = "";
1180 while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $drcr->fetchrow_array) {
1181 $ref->{debit} += $debit;
1182 $ref->{credit} += $credit;
1184 $ref->{haben_saldo} += $saldo;
1186 $ref->{soll_saldo} += $saldo * -1;
1188 $ref->{last_transaction} = $last_transaction;
1189 $ref->{soll_kumuliert} = $soll_kumuliert * -1;
1190 $ref->{haben_kumuliert} = $haben_kumuliert;
1194 if ($form->{project_id}) {
1197 do_statement($form, $project_drcr, $q_project_drcr, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno}, $ref->{accno});
1199 ($debit, $credit) = (0, 0);
1200 while (($debit, $credit, $saldo, $haben_kumuliert, $soll_kumuliert, $last_transaction) = $project_drcr->fetchrow_array) {
1201 $ref->{debit} += $debit;
1202 $ref->{credit} += $credit;
1204 $ref->{haben_saldo} += $saldo;
1206 $ref->{soll_saldo} += $saldo * -1;
1208 $ref->{soll_kumuliert} += $soll_kumuliert * -1;
1209 $ref->{haben_kumuliert} += $haben_kumuliert;
1211 $project_drcr->finish;
1214 $ref->{debit} = $form->round_amount($ref->{debit}, 2);
1215 $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1216 $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2);
1217 $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2);
1218 $ref->{haben_kumuliert} = $form->round_amount($ref->{haben_kumuliert}, 2);
1219 $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2);
1223 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1224 $accno = pop @accno;
1226 $trb{$accno}{debit} += $ref->{debit};
1227 $trb{$accno}{credit} += $ref->{credit};
1228 $trb{$accno}{soll_saldo} += $ref->{soll_saldo};
1229 $trb{$accno}{haben_saldo} += $ref->{haben_saldo};
1230 $trb{$accno}{soll_kumuliert} += $ref->{soll_kumuliert};
1231 $trb{$accno}{haben_kumuliert} += $ref->{haben_kumuliert};
1234 push @{ $form->{TB} }, $ref;
1240 # debits and credits for headings
1241 foreach $accno (@headingaccounts) {
1242 foreach $ref (@{ $form->{TB} }) {
1243 if ($accno eq $ref->{accno}) {
1244 $ref->{debit} = $trb{$accno}{debit};
1245 $ref->{credit} = $trb{$accno}{credit};
1246 $ref->{soll_saldo} = $trb{$accno}{soll_saldo};
1247 $ref->{haben_saldo} = $trb{$accno}{haben_saldo};
1248 $ref->{soll_kumuliert} = $trb{$accno}{soll_kumuliert};
1249 $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert}; }
1253 $main::lxdebug->leave_sub();
1257 $main::lxdebug->enter_sub();
1258 my ($self, $dbh, $form) = @_;
1259 my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
1260 my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
1261 my $sth = $dbh->prepare($query);
1262 while(my $ref = $sth->fetchrow_hashref()) {
1263 $ref->{invnumer} =~ s/Storno zu //g;
1264 $form->{storno}{$ref->{invnumber}} = 1;
1266 $main::lxdebug->leave_sub();
1270 $main::lxdebug->enter_sub();
1272 my ($self, $myconfig, $form) = @_;
1274 # connect to database
1275 my $dbh = $form->dbconnect($myconfig);
1277 my ($invoice, $arap, $buysell, $ct, $ct_id);
1279 if ($form->{ct} eq "customer") {
1290 $ct_id = "${ct}_id";
1292 $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1293 my $todate = conv_dateq($form->{todate});
1295 my $where = " 1 = 1 ";
1298 if ($form->{$ct_id}) {
1299 $where .= qq| AND (ct.id = | . conv_i($form->{$ct_id}) . qq|)|;
1300 } elsif ($form->{ $form->{ct} }) {
1301 $where .= qq| AND (ct.name ILIKE | . $dbh->quote('%' . $form->{$ct} . '%') . qq|)|;
1305 if ($form->{department}) {
1306 ($null, $department_id) = split /--/, $form->{department};
1307 $dpt_join = qq| JOIN department d ON (a.department_id = d.id) |;
1308 $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
1312 -- between 0-30 days
1314 SELECT ${ct}.id AS ctid, ${ct}.name,
1315 street, zipcode, city, country, contact, email,
1316 phone as customerphone, fax as customerfax, ${ct}number,
1317 "invnumber", "transdate",
1318 (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1319 "duedate", invoice, ${arap}.id,
1322 WHERE (${arap}.curr = exchangerate.curr)
1323 AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1325 WHERE (paid != amount)
1326 AND (${arap}.storno IS FALSE)
1327 AND (${arap}.${ct}_id = ${ct}.id)
1329 AND (transdate <= (date $todate - interval '0 days'))
1330 AND (transdate >= (date $todate - interval '30 days'))
1334 -- between 31-60 days
1336 SELECT ${ct}.id AS ctid, ${ct}.name,
1337 street, zipcode, city, country, contact, email,
1338 phone as customerphone, fax as customerfax, ${ct}number,
1339 "invnumber", "transdate",
1340 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1341 "duedate", invoice, ${arap}.id,
1344 WHERE (${arap}.curr = exchangerate.curr)
1345 AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1347 WHERE (paid != amount)
1348 AND (${arap}.storno IS FALSE)
1349 AND (${arap}.${ct}_id = ${ct}.id)
1351 AND (transdate < (date $todate - interval '30 days'))
1352 AND (transdate >= (date $todate - interval '60 days'))
1356 -- between 61-90 days
1358 SELECT ${ct}.id AS ctid, ${ct}.name,
1359 street, zipcode, city, country, contact, email,
1360 phone as customerphone, fax as customerfax, ${ct}number,
1361 "invnumber", "transdate",
1362 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1363 "duedate", invoice, ${arap}.id,
1366 WHERE (${arap}.curr = exchangerate.curr)
1367 AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1369 WHERE (paid != amount)
1370 AND (${arap}.storno IS FALSE)
1371 AND (${arap}.${ct}_id = ${ct}.id)
1373 AND (transdate < (date $todate - interval '60 days'))
1374 AND (transdate >= (date $todate - interval '90 days'))
1380 SELECT ${ct}.id AS ctid, ${ct}.name,
1381 street, zipcode, city, country, contact, email,
1382 phone as customerphone, fax as customerfax, ${ct}number,
1383 "invnumber", "transdate",
1384 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1385 "duedate", invoice, ${arap}.id,
1388 WHERE (${arap}.curr = exchangerate.curr)
1389 AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate
1391 WHERE (paid != amount)
1392 AND (${arap}.storno IS FALSE)
1393 AND (${arap}.${ct}_id = ${ct}.id)
1395 AND (transdate < (date $todate - interval '90 days'))
1397 ORDER BY ctid, transdate, invnumber |;
1399 my $sth_details = prepare_query($form, $dbh, $q_details);
1401 # select outstanding vendors or customers, depends on $ct
1403 qq|SELECT DISTINCT ct.id, ct.name
1404 FROM $ct ct, $arap a
1407 AND (a.${ct_id} = ct.id)
1408 AND (a.paid != a.amount)
1409 AND (a.transdate <= $todate)
1412 my $sth = prepare_execute_query($form, $dbh, $query);
1415 # for each company that has some stuff outstanding
1416 while (my ($id) = $sth->fetchrow_array) {
1417 do_statement($form, $sth_details, $q_details, $id, $id, $id, $id);
1419 while (my $ref = $sth_details->fetchrow_hashref(NAME_lc)) {
1420 $ref->{module} = ($ref->{invoice}) ? $invoice : $arap;
1421 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1422 push @{ $form->{AG} }, $ref;
1425 $sth_details->finish;
1434 $main::lxdebug->leave_sub();
1438 $main::lxdebug->enter_sub();
1440 my ($self, $myconfig, $form) = @_;
1442 # connect to database
1443 my $dbh = $form->dbconnect($myconfig);
1445 my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
1448 qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1451 ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1452 selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
1455 $main::lxdebug->leave_sub();
1458 sub get_taxaccounts {
1459 $main::lxdebug->enter_sub();
1461 my ($self, $myconfig, $form) = @_;
1463 # connect to database
1464 my $dbh = $form->dbconnect($myconfig);
1468 qq|SELECT c.accno, c.description, t.rate
1470 WHERE (c.link LIKE '%CT_tax%') AND (c.id = t.chart_id)
1472 $form->{taxaccounts} = selectall_hashref_quert($form, $dbh, $query);
1476 $main::lxdebug->leave_sub();
1480 $main::lxdebug->enter_sub();
1482 my ($self, $myconfig, $form) = @_;
1484 # connect to database
1485 my $dbh = $form->dbconnect($myconfig);
1487 my ($null, $department_id) = split /--/, $form->{department};
1490 my $where = "1 = 1";
1492 if ($department_id) {
1493 $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
1498 if ($form->{accno}) {
1499 $accno = $form->{accno};
1500 $rate = $form->{"$form->{accno}_rate"};
1501 $accno = qq| AND (ch.accno = | . $dbh->quote($accno) . qq|)|;
1507 if ($form->{db} eq 'ar') {
1508 $table = "customer";
1515 my $arap = lc($ARAP);
1517 my $transdate = "a.transdate";
1519 if ($form->{method} eq 'cash') {
1520 $transdate = "a.datepaid";
1522 my $todate = conv_dateq($form->{todate} ? $form->{todate} : $form->current_date($myconfig));
1529 JOIN chart ON (chart_id = id)
1530 WHERE (link LIKE '%${ARAP}_paid%')
1531 AND (transdate <= $todate)
1536 # if there are any dates construct a where
1537 $where .= " AND ($transdate >= " . conv_dateq($form->{fromdate}) . ") " if ($form->{fromdate});
1538 $where .= " AND ($transdate <= " . conv_dateq($form->{todate}) . ") " if ($form->{todate});
1540 my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1542 my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1543 $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
1545 if ($form->{report} !~ /nontaxable/) {
1547 qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,
1548 ac.amount * $ml AS tax
1550 JOIN ${arap} a ON (a.id = ac.trans_id)
1551 JOIN chart ch ON (ch.id = ac.chart_id)
1552 JOIN $table n ON (n.id = a.${table}_id)
1556 AND (a.invoice = '0')
1560 SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1561 i.sellprice * i.qty * $rate * $ml AS tax
1563 JOIN ${arap} a ON (a.id = ac.trans_id)
1564 JOIN chart ch ON (ch.id = ac.chart_id)
1565 JOIN $table n ON (n.id = a.${table}_id)
1566 JOIN ${table}tax t ON (t.${table}_id = n.id)
1567 JOIN invoice i ON (i.trans_id = a.id)
1568 JOIN partstax p ON (p.parts_id = i.parts_id)
1572 AND (a.invoice = '1')
1573 ORDER BY $sortorder|;
1575 # only gather up non-taxable transactions
1577 qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount
1579 JOIN ${arap} a ON (a.id = ac.trans_id)
1580 JOIN $table n ON (n.id = a.${table}_id)
1583 AND (a.invoice = '0')
1584 AND (a.netamount = a.amount)
1588 SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount
1590 JOIN ${arap} a ON (a.id = ac.trans_id)
1591 JOIN $table n ON (n.id = a.${table}_id)
1592 JOIN invoice i ON (i.trans_id = a.id)
1595 AND (a.invoice = '1')
1597 a.${table}_id NOT IN (SELECT ${table}_id FROM ${table}tax t (${table}_id))
1599 i.parts_id NOT IN (SELECT parts_id FROM partstax p (parts_id))
1601 GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
1602 ORDER by $sortorder|;
1605 $form->{TR} = selectall_hashref_query($form, $dbh, $query);
1609 $main::lxdebug->leave_sub();
1612 sub paymentaccounts {
1613 $main::lxdebug->enter_sub();
1615 my ($self, $myconfig, $form) = @_;
1617 # connect to database, turn AutoCommit off
1618 my $dbh = $form->dbconnect_noauto($myconfig);
1620 my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
1622 # get A(R|P)_paid accounts
1624 qq|SELECT accno, description
1626 WHERE link LIKE '%${ARAP}_paid%'|;
1627 $form->{PR} = selectall_hashref_query($form, $dbh, $query);
1631 $main::lxdebug->leave_sub();
1635 $main::lxdebug->enter_sub();
1637 my ($self, $myconfig, $form) = @_;
1639 # connect to database, turn AutoCommit off
1640 my $dbh = $form->dbconnect_noauto($myconfig);
1644 if ($form->{db} eq 'ar') {
1645 $table = 'customer';
1657 if ($form->{department_id}) {
1658 $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
1659 $where = qq| AND (t.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
1662 if ($form->{fromdate}) {
1663 $where .= " AND (ac.transdate >= " . $dbh->quote($form->{fromdate}) . ") ";
1665 if ($form->{todate}) {
1666 $where .= " AND (ac.transdate <= " . $dbh->quote($form->{todate}) . ") ";
1668 if (!$form->{fx_transaction}) {
1669 $where .= " AND ac.fx_transaction = '0'";
1674 if ($form->{reference}) {
1675 $reference = $dbh->quote('%' . $form->{reference} . '%');
1676 $invnumber = " AND (a.invnumber LIKE $reference)";
1677 $reference = " AND (g.reference LIKE $reference)";
1679 if ($form->{source}) {
1680 $where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
1682 if ($form->{memo}) {
1683 $where .= " AND (ac.memo ILIKE " . $dbh->quote('%' . $form->{memo} . '%') . ") ";
1686 my $sortorder = join(', ', qw(name invnumber ordnumber transdate source));
1687 $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(transdate invnumber name source memo)));
1689 $query = qq|SELECT id, accno, description FROM chart WHERE accno = ?|;
1690 my $sth = prepare_query($form, $dbh, $query);
1693 qq|SELECT c.name, a.invnumber, a.ordnumber,
1694 ac.transdate, ac.amount * $ml AS paid, ac.source,
1695 a.invoice, a.id, ac.memo, '${arap}' AS module
1697 JOIN $arap a ON (ac.trans_id = a.id)
1698 JOIN $table c ON (c.id = a.${table}_id)
1700 WHERE (ac.chart_id = ?)
1706 SELECT g.description, g.reference, NULL AS ordnumber,
1707 ac.transdate, ac.amount * $ml AS paid, ac.source,
1708 '0' as invoice, g.id, ac.memo, 'gl' AS module
1710 JOIN gl g ON (g.id = ac.trans_id)
1712 WHERE (ac.chart_id = ?)
1715 AND (ac.amount * $ml) > 0
1717 ORDER BY $sortorder|;
1718 my $sth_details = prepare_query($form, $dbh, $q_details);
1722 # cycle through each id
1723 foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1724 do_statement($form, $sth, $query, $accno);
1725 my $ref = $sth->fetchrow_hashref();
1726 push(@{ $form->{PR} }, $ref);
1729 $form->{ $ref->{id} } = [] unless ($form->{ $ref->{id} });
1731 do_statement($form, $sth_details, $q_details, $ref->{id}, $ref->{id});
1732 while (my $pr = $sth_details->fetchrow_hashref()) {
1733 push(@{ $form->{ $ref->{id} } }, $pr);
1735 $sth_details->finish();
1740 $main::lxdebug->leave_sub();
1744 $main::lxdebug->enter_sub();
1746 my ($self, $myconfig, $form) = @_;
1748 # connect to database
1749 my $dbh = $form->dbconnect($myconfig);
1751 my $last_period = 0;
1754 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);
1756 $form->{decimalplaces} *= 1;
1758 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_bwa");
1760 # if there are any compare dates
1761 if ($form->{fromdate} || $form->{todate}) {
1763 if ($form->{fromdate}) {
1764 $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1767 $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
1770 $kummfromdate = $form->{comparefromdate};
1771 $kummtodate = $form->{comparetodate};
1772 &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
1775 @periods = qw(jetzt kumm);
1776 @gesamtleistung = qw(1 2 3);
1777 @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 19 20);
1779 qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
1781 foreach $key (@periods) {
1782 $form->{ "$key" . "gesamtleistung" } = 0;
1783 $form->{ "$key" . "gesamtkosten" } = 0;
1785 foreach $category (@categories) {
1787 if (defined($form->{$category}{$key})) {
1788 $form->{"$key$category"} =
1789 $form->format_amount($myconfig,
1790 $form->round_amount($form->{$category}{$key}, 2
1792 $form->{decimalplaces},
1796 foreach $item (@gesamtleistung) {
1797 $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
1799 foreach $item (@gesamtkosten) {
1800 $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
1802 $form->{ "$key" . "rohertrag" } =
1803 $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
1804 $form->{ "$key" . "betriebrohertrag" } =
1805 $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
1806 $form->{ "$key" . "betriebsergebnis" } =
1807 $form->{ "$key" . "betriebrohertrag" } -
1808 $form->{ "$key" . "gesamtkosten" };
1809 $form->{ "$key" . "neutraleraufwand" } =
1810 $form->{30}{$key} + $form->{31}{$key};
1811 $form->{ "$key" . "neutralertrag" } =
1812 $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
1813 $form->{ "$key" . "ergebnisvorsteuern" } =
1814 $form->{ "$key" . "betriebsergebnis" } -
1815 $form->{ "$key" . "neutraleraufwand" } +
1816 $form->{ "$key" . "neutralertrag" };
1817 $form->{ "$key" . "ergebnis" } =
1818 $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};
1820 if ($form->{ "$key" . "gesamtleistung" } > 0) {
1821 foreach $category (@categories) {
1822 if (defined($form->{$category}{$key})) {
1823 $form->{ "$key" . "gl" . "$category" } =
1824 $form->format_amount(
1826 $form->round_amount(
1827 ($form->{$category}{$key} /
1828 $form->{ "$key" . "gesamtleistung" } * 100
1830 $form->{decimalplaces}
1832 $form->{decimalplaces},
1836 foreach $item (@ergebnisse) {
1837 $form->{ "$key" . "gl" . "$item" } =
1838 $form->format_amount($myconfig,
1839 $form->round_amount(
1840 ( $form->{ "$key" . "$item" } /
1841 $form->{ "$key" . "gesamtleistung" } * 100
1843 $form->{decimalplaces}
1845 $form->{decimalplaces},
1850 if ($form->{ "$key" . "gesamtkosten" } > 0) {
1851 foreach $category (@categories) {
1852 if (defined($form->{$category}{$key})) {
1853 $form->{ "$key" . "gk" . "$category" } =
1854 $form->format_amount($myconfig,
1855 $form->round_amount(
1856 ($form->{$category}{$key} /
1857 $form->{ "$key" . "gesamtkosten" } * 100
1859 $form->{decimalplaces}
1861 $form->{decimalplaces},
1865 foreach $item (@ergebnisse) {
1866 $form->{ "$key" . "gk" . "$item" } =
1867 $form->format_amount($myconfig,
1868 $form->round_amount(
1869 ($form->{ "$key" . "$item" } /
1870 $form->{ "$key" . "gesamtkosten" } * 100
1872 $form->{decimalplaces}
1874 $form->{decimalplaces},
1879 if ($form->{10}{$key} > 0) {
1880 foreach $category (@categories) {
1881 if (defined($form->{$category}{$key})) {
1882 $form->{ "$key" . "pk" . "$category" } =
1883 $form->format_amount(
1885 $form->round_amount(
1886 ($form->{$category}{$key} / $form->{10}{$key} * 100),
1887 $form->{decimalplaces}
1889 $form->{decimalplaces},
1893 foreach $item (@ergebnisse) {
1894 $form->{ "$key" . "pk" . "$item" } =
1895 $form->format_amount($myconfig,
1896 $form->round_amount(
1897 ($form->{ "$key" . "$item" } /
1898 $form->{10}{$key} * 100
1900 $form->{decimalplaces}
1902 $form->{decimalplaces},
1907 if ($form->{4}{$key} > 0) {
1908 foreach $category (@categories) {
1909 if (defined($form->{$category}{$key})) {
1910 $form->{ "$key" . "auf" . "$category" } =
1911 $form->format_amount(
1913 $form->round_amount(
1914 ($form->{$category}{$key} / $form->{4}{$key} * 100),
1915 $form->{decimalplaces}
1917 $form->{decimalplaces},
1921 foreach $item (@ergebnisse) {
1922 $form->{ "$key" . "auf" . "$item" } =
1923 $form->format_amount($myconfig,
1924 $form->round_amount(
1925 ($form->{ "$key" . "$item" } /
1926 $form->{4}{$key} * 100
1928 $form->{decimalplaces}
1930 $form->{decimalplaces},
1935 foreach $item (@ergebnisse) {
1936 $form->{ "$key" . "$item" } =
1937 $form->format_amount($myconfig,
1938 $form->round_amount($form->{ "$key" . "$item" },
1939 $form->{decimalplaces}
1941 $form->{decimalplaces},
1948 $main::lxdebug->leave_sub();
1952 $main::lxdebug->enter_sub();
1954 my ($self, $myconfig, $form) = @_;
1956 # connect to database
1957 my $dbh = $form->dbconnect($myconfig);
1959 my $last_period = 0;
1960 my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
1961 96 66 43 45 53 62 65 67);
1962 my @categories_euro = qw(48 51 86 91 97 93 94);
1963 $form->{decimalplaces} *= 1;
1965 foreach $item (@categories_cent) {
1966 $form->{"$item"} = 0;
1968 foreach $item (@categories_euro) {
1969 $form->{"$item"} = 0;
1972 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_ustva");
1974 # foreach $item (@categories_cent) {
1975 # if ($form->{$item}{"jetzt"} > 0) {
1976 # $form->{$item} = $form->{$item}{"jetzt"};
1977 # delete $form->{$item}{"jetzt"};
1980 # foreach $item (@categories_euro) {
1981 # if ($form->{$item}{"jetzt"} > 0) {
1982 # $form->{$item} = $form->{$item}{"jetzt"};
1983 # delete $form->{$item}{"jetzt"};
1984 # } foreach $item (@categories_cent) {
1985 # if ($form->{$item}{"jetzt"} > 0) {
1986 # $form->{$item} = $form->{$item}{"jetzt"};
1987 # delete $form->{$item}{"jetzt"};
1990 # foreach $item (@categories_euro) {
1991 # if ($form->{$item}{"jetzt"} > 0) {
1992 # $form->{$item} = $form->{$item}{"jetzt"};
1993 # delete $form->{$item}{"jetzt"};
2000 # Berechnung der USTVA Formularfelder
2002 $form->{"51r"} = $form->{"511"};
2003 $form->{"86r"} = $form->{"861"};
2004 $form->{"97r"} = $form->{"971"};
2005 $form->{"93r"} = $form->{"931"};
2007 #$form->{"96"} = $form->{"94"} * 0.16;
2009 $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
2011 $form->{"45"} = $form->{"43"};
2012 $form->{"53"} = $form->{"43"};
2013 $form->{"62"} = $form->{"43"} - $form->{"66"};
2014 $form->{"65"} = $form->{"43"} - $form->{"66"};
2015 $form->{"67"} = $form->{"43"} - $form->{"66"};
2017 foreach $item (@categories_cent) {
2019 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
2023 foreach $item (@categories_euro) {
2025 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
2031 $main::lxdebug->leave_sub();
2034 sub income_statement {
2035 $main::lxdebug->enter_sub();
2037 my ($self, $myconfig, $form) = @_;
2039 # connect to database
2040 my $dbh = $form->dbconnect($myconfig);
2042 my $last_period = 0;
2043 my @categories_einnahmen = qw(1 2 3 4 5 6 7);
2044 my @categories_ausgaben =
2045 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);
2047 my @ergebnisse = qw(sumeura sumeurb guvsumme);
2049 $form->{decimalplaces} *= 1;
2051 foreach $item (@categories_einnahmen) {
2054 foreach $item (@categories_ausgaben) {
2058 foreach $item (@ergebnisse) {
2062 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2065 foreach $item (@categories_einnahmen) {
2066 $form->{"eur${item}"} =
2067 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2068 $form->{"sumeura"} += $form->{$item};
2070 foreach $item (@categories_ausgaben) {
2071 $form->{"eur${item}"} =
2072 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2073 $form->{"sumeurb"} += $form->{$item};
2076 $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
2078 foreach $item (@ergebnisse) {
2080 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2082 $main::lxdebug->leave_sub();