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 #======================================================================
39 $main::lxdebug->enter_sub();
41 my ($self, $myconfig, $form) = @_;
44 my $dbh = $form->dbconnect($myconfig);
47 my @categories = qw(A C L Q);
49 # if there are any dates construct a where
50 if ($form->{asofdate}) {
52 $form->{this_period} = "$form->{asofdate}";
53 $form->{period} = "$form->{asofdate}";
57 $form->{decimalplaces} *= 1;
59 &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form,
62 # if there are any compare dates
63 if ($form->{compareasofdate}) {
66 &get_accounts($dbh, $last_period, "", $form->{compareasofdate},
69 $form->{last_period} = "$form->{compareasofdate}";
76 # now we got $form->{A}{accno}{ } assets
77 # and $form->{L}{accno}{ } liabilities
78 # and $form->{Q}{accno}{ } equity
79 # build asset accounts
85 'A' => { 'label' => 'asset',
89 'L' => { 'label' => 'liability',
90 'labels' => 'liabilities',
93 'Q' => { 'label' => 'equity',
98 foreach $category (grep { !/C/ } @categories) {
100 foreach $key (sort keys %{ $form->{$category} }) {
102 $str = ($form->{l_heading}) ? $form->{padding} : "";
104 if ($form->{$category}{$key}{charttype} eq "A") {
107 ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
108 : "$form->{$category}{$key}{description}";
110 if ($form->{$category}{$key}{charttype} eq "H") {
111 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
113 push(@{ $form->{"$account{$category}{label}_account"} },
114 "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
116 push(@{ $form->{"$account{$category}{label}_this_period"} },
117 $form->format_amount(
119 $account{$category}{subthis} * $account{$category}{ml},
120 $form->{decimalplaces}, $dash
124 push(@{ $form->{"$account{$category}{label}_last_period"} },
125 $form->format_amount(
127 $account{$category}{sublast} * $account{$category}{ml},
128 $form->{decimalplaces}, $dash
134 "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
136 $account{$category}{subthis} = $form->{$category}{$key}{this};
137 $account{$category}{sublast} = $form->{$category}{$key}{last};
138 $account{$category}{subdescription} =
139 $form->{$category}{$key}{description};
140 $account{$category}{subtotal} = 1;
142 $form->{$category}{$key}{this} = 0;
143 $form->{$category}{$key}{last} = 0;
145 next unless $form->{l_heading};
150 # push description onto array
151 push(@{ $form->{"$account{$category}{label}_account"} }, $str);
153 if ($form->{$category}{$key}{charttype} eq 'A') {
154 $form->{"total_$account{$category}{labels}_this_period"} +=
155 $form->{$category}{$key}{this} * $account{$category}{ml};
159 push(@{ $form->{"$account{$category}{label}_this_period"} },
160 $form->format_amount(
162 $form->{$category}{$key}{this} * $account{$category}{ml},
163 $form->{decimalplaces}, $dash
167 $form->{"total_$account{$category}{labels}_last_period"} +=
168 $form->{$category}{$key}{last} * $account{$category}{ml};
170 push(@{ $form->{"$account{$category}{label}_last_period"} },
171 $form->format_amount(
173 $form->{$category}{$key}{last} * $account{$category}{ml},
174 $form->{decimalplaces}, $dash
179 $str = ($form->{l_heading}) ? $form->{padding} : "";
180 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
181 push(@{ $form->{"$account{$category}{label}_account"} },
182 "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"
184 push(@{ $form->{"$account{$category}{label}_this_period"} },
185 $form->format_amount(
187 $account{$category}{subthis} * $account{$category}{ml},
188 $form->{decimalplaces}, $dash
192 push(@{ $form->{"$account{$category}{label}_last_period"} },
193 $form->format_amount(
195 $account{$category}{sublast} * $account{$category}{ml},
196 $form->{decimalplaces}, $dash
203 # totals for assets, liabilities
204 $form->{total_assets_this_period} =
205 $form->round_amount($form->{total_assets_this_period},
206 $form->{decimalplaces});
207 $form->{total_liabilities_this_period} =
208 $form->round_amount($form->{total_liabilities_this_period},
209 $form->{decimalplaces});
210 $form->{total_equity_this_period} =
211 $form->round_amount($form->{total_equity_this_period},
212 $form->{decimalplaces});
215 $form->{earnings_this_period} =
216 $form->{total_assets_this_period} -
217 $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
219 push(@{ $form->{equity_this_period} },
220 $form->format_amount($myconfig,
221 $form->{earnings_this_period},
222 $form->{decimalplaces}, "- "
225 $form->{total_equity_this_period} =
227 $form->{total_equity_this_period} + $form->{earnings_this_period},
228 $form->{decimalplaces});
230 # add liability + equity
231 $form->{total_this_period} =
232 $form->format_amount(
234 $form->{total_liabilities_this_period} + $form->{total_equity_this_period},
235 $form->{decimalplaces},
240 # totals for assets, liabilities
241 $form->{total_assets_last_period} =
242 $form->round_amount($form->{total_assets_last_period},
243 $form->{decimalplaces});
244 $form->{total_liabilities_last_period} =
245 $form->round_amount($form->{total_liabilities_last_period},
246 $form->{decimalplaces});
247 $form->{total_equity_last_period} =
248 $form->round_amount($form->{total_equity_last_period},
249 $form->{decimalplaces});
251 # calculate retained earnings
252 $form->{earnings_last_period} =
253 $form->{total_assets_last_period} -
254 $form->{total_liabilities_last_period} -
255 $form->{total_equity_last_period};
257 push(@{ $form->{equity_last_period} },
258 $form->format_amount($myconfig,
259 $form->{earnings_last_period},
260 $form->{decimalplaces}, "- "
263 $form->{total_equity_last_period} =
265 $form->{total_equity_last_period} + $form->{earnings_last_period},
266 $form->{decimalplaces});
268 # add liability + equity
269 $form->{total_last_period} =
270 $form->format_amount($myconfig,
271 $form->{total_liabilities_last_period} +
272 $form->{total_equity_last_period},
273 $form->{decimalplaces},
278 $form->{total_liabilities_last_period} =
279 $form->format_amount($myconfig,
280 $form->{total_liabilities_last_period},
281 $form->{decimalplaces}, "- ")
282 if ($form->{total_liabilities_last_period} != 0);
284 $form->{total_equity_last_period} =
285 $form->format_amount($myconfig,
286 $form->{total_equity_last_period},
287 $form->{decimalplaces}, "- ")
288 if ($form->{total_equity_last_period} != 0);
290 $form->{total_assets_last_period} =
291 $form->format_amount($myconfig,
292 $form->{total_assets_last_period},
293 $form->{decimalplaces}, "- ")
294 if ($form->{total_assets_last_period} != 0);
296 $form->{total_assets_this_period} =
297 $form->format_amount($myconfig,
298 $form->{total_assets_this_period},
299 $form->{decimalplaces}, "- ");
301 $form->{total_liabilities_this_period} =
302 $form->format_amount($myconfig,
303 $form->{total_liabilities_this_period},
304 $form->{decimalplaces}, "- ");
306 $form->{total_equity_this_period} =
307 $form->format_amount($myconfig,
308 $form->{total_equity_this_period},
309 $form->{decimalplaces}, "- ");
311 $main::lxdebug->leave_sub();
315 $main::lxdebug->enter_sub();
317 my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
319 my ($null, $department_id) = split /--/, $form->{department};
330 my $category = "AND (";
331 foreach $item (@{$categories}) {
332 $category .= qq|c.category = '$item' OR |;
334 $category =~ s/OR $/\)/;
337 $query = qq|SELECT c.accno, c.description, c.category
339 WHERE c.charttype = 'H'
343 if ($form->{accounttype} eq 'gifi') {
344 $query = qq|SELECT g.accno, g.description, c.category
346 JOIN chart c ON (c.gifi_accno = g.accno)
347 WHERE c.charttype = 'H'
352 $sth = $dbh->prepare($query);
353 $sth->execute || $form->dberror($query);
355 my @headingaccounts = ();
356 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
357 $form->{ $ref->{category} }{ $ref->{accno} }{description} =
358 "$ref->{description}";
359 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
360 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
362 push @headingaccounts, $ref->{accno};
368 if ($form->{method} eq 'cash') {
369 $subwhere .= " AND transdate >= '$fromdate'";
370 $glwhere = " AND ac.transdate >= '$fromdate'";
372 $where .= " AND ac.transdate >= '$fromdate'";
377 $where .= " AND ac.transdate <= '$todate'";
378 $subwhere .= " AND transdate <= '$todate'";
381 if ($department_id) {
383 JOIN department t ON (a.department_id = t.id)
386 AND t.id = $department_id
390 if ($form->{project_id}) {
392 AND ac.project_id = $form->{project_id}
396 if ($form->{accounttype} eq 'gifi') {
398 if ($form->{method} eq 'cash') {
402 SELECT g.accno, sum(ac.amount) AS amount,
403 g.description, c.category
405 JOIN chart c ON (c.id = ac.chart_id)
406 JOIN ar a ON (a.id = ac.trans_id)
407 JOIN gifi g ON (g.accno = c.gifi_accno)
416 JOIN chart ON (chart_id = id)
417 WHERE link LIKE '%AR_paid%'
421 GROUP BY g.accno, g.description, c.category
425 SELECT '' AS accno, SUM(ac.amount) AS amount,
426 '' AS description, c.category
428 JOIN chart c ON (c.id = ac.chart_id)
429 JOIN ar a ON (a.id = ac.trans_id)
434 AND c.gifi_accno = ''
439 JOIN chart ON (chart_id = id)
440 WHERE link LIKE '%AR_paid%'
448 SELECT g.accno, sum(ac.amount) AS amount,
449 g.description, c.category
451 JOIN chart c ON (c.id = ac.chart_id)
452 JOIN ap a ON (a.id = ac.trans_id)
453 JOIN gifi g ON (g.accno = c.gifi_accno)
462 JOIN chart ON (chart_id = id)
463 WHERE link LIKE '%AP_paid%'
467 GROUP BY g.accno, g.description, c.category
471 SELECT '' AS accno, SUM(ac.amount) AS amount,
472 '' AS description, c.category
474 JOIN chart c ON (c.id = ac.chart_id)
475 JOIN ap a ON (a.id = ac.trans_id)
480 AND c.gifi_accno = ''
485 JOIN chart ON (chart_id = id)
486 WHERE link LIKE '%AP_paid%'
496 SELECT g.accno, sum(ac.amount) AS amount,
497 g.description, c.category
499 JOIN chart c ON (c.id = ac.chart_id)
500 JOIN gifi g ON (g.accno = c.gifi_accno)
501 JOIN gl a ON (a.id = ac.trans_id)
507 AND NOT (c.link = 'AR' OR c.link = 'AP')
509 GROUP BY g.accno, g.description, c.category
513 SELECT '' AS accno, SUM(ac.amount) AS amount,
514 '' AS description, c.category
516 JOIN chart c ON (c.id = ac.chart_id)
517 JOIN gl a ON (a.id = ac.trans_id)
523 AND c.gifi_accno = ''
524 AND NOT (c.link = 'AR' OR c.link = 'AP')
529 if ($form->{project_id}) {
535 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
536 g.description AS description, c.category
538 JOIN ar a ON (a.id = ac.trans_id)
539 JOIN parts p ON (ac.parts_id = p.id)
540 JOIN chart c on (p.income_accno_id = c.id)
541 JOIN gifi g ON (g.accno = c.gifi_accno)
543 -- use transdate from subwhere
544 WHERE 1 = 1 $subwhere
551 JOIN chart ON (chart_id = id)
552 WHERE link LIKE '%AR_paid%'
556 GROUP BY g.accno, g.description, c.category
560 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
561 g.description AS description, c.category
563 JOIN ap a ON (a.id = ac.trans_id)
564 JOIN parts p ON (ac.parts_id = p.id)
565 JOIN chart c on (p.expense_accno_id = c.id)
566 JOIN gifi g ON (g.accno = c.gifi_accno)
568 WHERE 1 = 1 $subwhere
575 JOIN chart ON (chart_id = id)
576 WHERE link LIKE '%AP_paid%'
580 GROUP BY g.accno, g.description, c.category
586 if ($department_id) {
588 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
591 AND t.department_id = $department_id
598 SELECT g.accno, SUM(ac.amount) AS amount,
599 g.description, c.category
601 JOIN chart c ON (c.id = ac.chart_id)
602 JOIN gifi g ON (c.gifi_accno = g.accno)
608 GROUP BY g.accno, g.description, c.category
612 SELECT '' AS accno, SUM(ac.amount) AS amount,
613 '' AS description, c.category
615 JOIN chart c ON (c.id = ac.chart_id)
620 AND c.gifi_accno = ''
625 if ($form->{project_id}) {
631 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
632 g.description AS description, c.category
634 JOIN ar a ON (a.id = ac.trans_id)
635 JOIN parts p ON (ac.parts_id = p.id)
636 JOIN chart c on (p.income_accno_id = c.id)
637 JOIN gifi g ON (c.gifi_accno = g.accno)
639 -- use transdate from subwhere
640 WHERE 1 = 1 $subwhere
644 GROUP BY g.accno, g.description, c.category
648 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
649 g.description AS description, c.category
651 JOIN ap a ON (a.id = ac.trans_id)
652 JOIN parts p ON (ac.parts_id = p.id)
653 JOIN chart c on (p.expense_accno_id = c.id)
654 JOIN gifi g ON (c.gifi_accno = g.accno)
656 WHERE 1 = 1 $subwhere
660 GROUP BY g.accno, g.description, c.category
666 } else { # standard account
668 if ($form->{method} eq 'cash') {
672 SELECT c.accno, sum(ac.amount) AS amount,
673 c.description, c.category
675 JOIN chart c ON (c.id = ac.chart_id)
676 JOIN ar a ON (a.id = ac.trans_id)
685 JOIN chart ON (chart_id = id)
686 WHERE link LIKE '%AR_paid%'
691 GROUP BY c.accno, c.description, c.category
695 SELECT c.accno, sum(ac.amount) AS amount,
696 c.description, c.category
698 JOIN chart c ON (c.id = ac.chart_id)
699 JOIN ap a ON (a.id = ac.trans_id)
708 JOIN chart ON (chart_id = id)
709 WHERE link LIKE '%AP_paid%'
714 GROUP BY c.accno, c.description, c.category
718 SELECT c.accno, sum(ac.amount) AS amount,
719 c.description, c.category
721 JOIN chart c ON (c.id = ac.chart_id)
722 JOIN gl a ON (a.id = ac.trans_id)
728 AND NOT (c.link = 'AR' OR c.link = 'AP')
730 GROUP BY c.accno, c.description, c.category
733 if ($form->{project_id}) {
739 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
740 c.description AS description, c.category
742 JOIN ar a ON (a.id = ac.trans_id)
743 JOIN parts p ON (ac.parts_id = p.id)
744 JOIN chart c on (p.income_accno_id = c.id)
746 -- use transdate from subwhere
747 WHERE 1 = 1 $subwhere
754 JOIN chart ON (chart_id = id)
755 WHERE link LIKE '%AR_paid%'
760 GROUP BY c.accno, c.description, c.category
764 SELECT c.accno AS accno, SUM(ac.sellprice) AS amount,
765 c.description AS description, c.category
767 JOIN ap a ON (a.id = ac.trans_id)
768 JOIN parts p ON (ac.parts_id = p.id)
769 JOIN chart c on (p.expense_accno_id = c.id)
771 WHERE 1 = 1 $subwhere
778 JOIN chart ON (chart_id = id)
779 WHERE link LIKE '%AP_paid%'
784 GROUP BY c.accno, c.description, c.category
790 if ($department_id) {
792 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
795 AND t.department_id = $department_id
801 SELECT c.accno, sum(ac.amount) AS amount,
802 c.description, c.category
804 JOIN chart c ON (c.id = ac.chart_id)
810 GROUP BY c.accno, c.description, c.category
813 if ($form->{project_id}) {
819 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
820 c.description AS description, c.category
822 JOIN ar a ON (a.id = ac.trans_id)
823 JOIN parts p ON (ac.parts_id = p.id)
824 JOIN chart c on (p.income_accno_id = c.id)
826 -- use transdate from subwhere
827 WHERE 1 = 1 $subwhere
831 GROUP BY c.accno, c.description, c.category
835 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
836 c.description AS description, c.category
838 JOIN ap a ON (a.id = ac.trans_id)
839 JOIN parts p ON (ac.parts_id = p.id)
840 JOIN chart c on (p.expense_accno_id = c.id)
842 WHERE 1 = 1 $subwhere
846 GROUP BY c.accno, c.description, c.category
857 my $sth = $dbh->prepare($query);
858 $sth->execute || $form->dberror($query);
860 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
862 if ($ref->{category} eq 'C') {
863 $ref->{category} = 'A';
866 # get last heading account
867 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
871 $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
873 $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
877 $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
878 $form->{ $ref->{category} }{ $ref->{accno} }{description} =
880 $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
883 $form->{ $ref->{category} }{ $ref->{accno} }{last} += $ref->{amount};
885 $form->{ $ref->{category} }{ $ref->{accno} }{this} += $ref->{amount};
890 # remove accounts with zero balance
891 foreach $category (@{$categories}) {
892 foreach $accno (keys %{ $form->{$category} }) {
893 $form->{$category}{$accno}{last} =
894 $form->round_amount($form->{$category}{$accno}{last},
895 $form->{decimalplaces});
896 $form->{$category}{$accno}{this} =
897 $form->round_amount($form->{$category}{$accno}{this},
898 $form->{decimalplaces});
900 delete $form->{$category}{$accno}
901 if ( $form->{$category}{$accno}{this} == 0
902 && $form->{$category}{$accno}{last} == 0);
906 $main::lxdebug->leave_sub();
910 $main::lxdebug->enter_sub();
912 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
914 my ($null, $department_id) = split /--/, $form->{department};
926 if ($form->{method} eq 'cash') {
927 $subwhere .= " AND transdate >= '$fromdate'";
928 $glwhere = " AND ac.transdate >= '$fromdate'";
930 $where .= " AND ac.transdate >= '$fromdate'";
935 $where .= " AND ac.transdate <= '$todate'";
936 $subwhere .= " AND transdate <= '$todate'";
939 if ($department_id) {
941 JOIN department t ON (a.department_id = t.id)
944 AND t.id = $department_id
948 if ($form->{project_id}) {
950 AND ac.project_id = $form->{project_id}
954 if ($form->{method} eq 'cash') {
958 SELECT sum(ac.amount) AS amount,
961 JOIN chart c ON (c.id = ac.chart_id)
962 JOIN ar a ON (a.id = ac.trans_id)
970 JOIN chart ON (chart_id = id)
971 WHERE link LIKE '%AR_paid%'
980 SELECT sum(ac.amount) AS amount,
983 JOIN chart c ON (c.id = ac.chart_id)
984 JOIN ap a ON (a.id = ac.trans_id)
992 JOIN chart ON (chart_id = id)
993 WHERE link LIKE '%AP_paid%'
1002 SELECT sum(ac.amount) AS amount,
1005 JOIN chart c ON (c.id = ac.chart_id)
1006 JOIN gl a ON (a.id = ac.trans_id)
1011 AND NOT (c.link = 'AR' OR c.link = 'AP')
1013 GROUP BY c.$category
1016 if ($form->{project_id}) {
1022 SELECT SUM(ac.sellprice * ac.qty) AS amount,
1025 JOIN ar a ON (a.id = ac.trans_id)
1026 JOIN parts p ON (ac.parts_id = p.id)
1027 JOIN chart c on (p.income_accno_id = c.id)
1029 -- use transdate from subwhere
1030 WHERE 1 = 1 $subwhere
1031 AND c.category = 'I'
1037 JOIN chart ON (chart_id = id)
1038 WHERE link LIKE '%AR_paid%'
1043 GROUP BY c.$category
1047 SELECT SUM(ac.sellprice) AS amount,
1050 JOIN ap a ON (a.id = ac.trans_id)
1051 JOIN parts p ON (ac.parts_id = p.id)
1052 JOIN chart c on (p.expense_accno_id = c.id)
1054 WHERE 1 = 1 $subwhere
1055 AND c.category = 'E'
1061 JOIN chart ON (chart_id = id)
1062 WHERE link LIKE '%AP_paid%'
1067 GROUP BY c.$category
1073 if ($department_id) {
1075 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
1078 AND t.department_id = $department_id
1084 SELECT sum(ac.amount) AS amount,
1087 JOIN chart c ON (c.id = ac.chart_id)
1092 GROUP BY c.$category
1095 if ($form->{project_id}) {
1101 SELECT SUM(ac.sellprice * ac.qty) AS amount,
1104 JOIN ar a ON (a.id = ac.trans_id)
1105 JOIN parts p ON (ac.parts_id = p.id)
1106 JOIN chart c on (p.income_accno_id = c.id)
1108 -- use transdate from subwhere
1109 WHERE 1 = 1 $subwhere
1110 AND c.category = 'I'
1113 GROUP BY c.$category
1117 SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
1120 JOIN ap a ON (a.id = ac.trans_id)
1121 JOIN parts p ON (ac.parts_id = p.id)
1122 JOIN chart c on (p.expense_accno_id = c.id)
1124 WHERE 1 = 1 $subwhere
1125 AND c.category = 'E'
1128 GROUP BY c.$category
1138 my $sth = $dbh->prepare($query);
1139 $sth->execute || $form->dberror($query);
1141 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1142 if ($ref->{amount} < 0) {
1143 $ref->{amount} *= -1;
1145 if ($category eq "pos_bwa") {
1147 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
1149 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
1152 $form->{ $ref->{$category} } += $ref->{amount};
1157 $main::lxdebug->leave_sub();
1161 $main::lxdebug->enter_sub();
1163 my ($self, $myconfig, $form) = @_;
1165 my $dbh = $form->dbconnect($myconfig);
1167 my ($query, $sth, $ref);
1170 my ($null, $department_id) = split /--/, $form->{department};
1171 my @headingaccounts = ();
1176 my $where = "1 = 1";
1177 my $invwhere = $where;
1179 if ($department_id) {
1181 JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
1184 AND t.department_id = $department_id
1188 # project_id only applies to getting transactions
1189 # it has nothing to do with a trial balance
1190 # but we use the same function to collect information
1192 if ($form->{project_id}) {
1194 AND ac.project_id = $form->{project_id}
1198 # get beginning balances
1199 if ($form->{fromdate}) {
1201 if ($form->{accounttype} eq 'gifi') {
1203 $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
1206 JOIN chart c ON (ac.chart_id = c.id)
1207 JOIN gifi g ON (c.gifi_accno = g.accno)
1209 WHERE ac.transdate < '$form->{fromdate}'
1212 GROUP BY g.accno, c.category, g.description
1217 $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
1220 JOIN chart c ON (ac.chart_id = c.id)
1222 WHERE ac.transdate < '$form->{fromdate}'
1225 GROUP BY c.accno, c.category, c.description
1230 $sth = $dbh->prepare($query);
1231 $sth->execute || $form->dberror($query);
1233 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1234 $balance{ $ref->{accno} } = $ref->{amount};
1236 if ($ref->{amount} != 0 && $form->{all_accounts}) {
1237 $trb{ $ref->{accno} }{description} = $ref->{description};
1238 $trb{ $ref->{accno} }{charttype} = 'A';
1239 $trb{ $ref->{accno} }{category} = $ref->{category};
1248 $query = qq|SELECT c.accno, c.description, c.category
1250 WHERE c.charttype = 'H'
1253 if ($form->{accounttype} eq 'gifi') {
1254 $query = qq|SELECT g.accno, g.description, c.category
1256 JOIN chart c ON (c.gifi_accno = g.accno)
1257 WHERE c.charttype = 'H'
1261 $sth = $dbh->prepare($query);
1262 $sth->execute || $form->dberror($query);
1264 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1265 $trb{ $ref->{accno} }{description} = $ref->{description};
1266 $trb{ $ref->{accno} }{charttype} = 'H';
1267 $trb{ $ref->{accno} }{category} = $ref->{category};
1269 push @headingaccounts, $ref->{accno};
1276 if ($form->{fromdate} || $form->{todate}) {
1277 if ($form->{fromdate}) {
1278 $tofrom .= " AND ac.transdate >= '$form->{fromdate}'";
1279 $subwhere .= " AND transdate >= '$form->{fromdate}'";
1280 $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
1281 $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
1283 if ($form->{todate}) {
1284 $tofrom .= " AND ac.transdate <= '$form->{todate}'";
1285 $invwhere .= " AND a.transdate <= '$form->{todate}'";
1286 $subwhere .= " AND transdate <= '$form->{todate}'";
1287 $glwhere .= " AND ac.transdate <= '$form->{todate}'";
1291 $where .= qq| AND ((ac.trans_id in (SELECT id from ar)
1296 JOIN chart ON (chart_id = id)
1297 WHERE link LIKE '%AR_paid%'
1299 )) OR (ac.trans_id in (SELECT id from ap)
1304 JOIN chart ON (chart_id = id)
1305 WHERE link LIKE '%AP_paid%'
1307 )) OR (ac.trans_id in (SELECT id from gl)
1313 if ($form->{accounttype} eq 'gifi') {
1315 $query = qq|SELECT g.accno, g.description, c.category,
1316 SUM(ac.amount) AS amount
1318 JOIN chart c ON (c.id = ac.chart_id)
1319 JOIN gifi g ON (c.gifi_accno = g.accno)
1324 GROUP BY g.accno, g.description, c.category
1327 if ($form->{project_id}) {
1331 -- add project transactions from invoice
1335 SELECT g.accno, g.description, c.category,
1336 SUM(ac.sellprice * ac.qty) AS amount
1338 JOIN ar a ON (ac.trans_id = a.id)
1339 JOIN parts p ON (ac.parts_id = p.id)
1340 JOIN chart c ON (p.income_accno_id = c.id)
1341 JOIN gifi g ON (c.gifi_accno = g.accno)
1346 GROUP BY g.accno, g.description, c.category
1350 SELECT g.accno, g.description, c.category,
1351 SUM(ac.sellprice * ac.qty) * -1 AS amount
1353 JOIN ap a ON (ac.trans_id = a.id)
1354 JOIN parts p ON (ac.parts_id = p.id)
1355 JOIN chart c ON (p.expense_accno_id = c.id)
1356 JOIN gifi g ON (c.gifi_accno = g.accno)
1361 GROUP BY g.accno, g.description, c.category
1370 $query = qq|SELECT c.accno, c.description, c.category,
1371 SUM(ac.amount) AS amount
1373 JOIN chart c ON (c.id = ac.chart_id)
1378 GROUP BY c.accno, c.description, c.category
1381 if ($form->{project_id}) {
1385 -- add project transactions from invoice
1389 SELECT c.accno, c.description, c.category,
1390 SUM(ac.sellprice * ac.qty) AS amount
1392 JOIN ar a ON (ac.trans_id = a.id)
1393 JOIN parts p ON (ac.parts_id = p.id)
1394 JOIN chart c ON (p.income_accno_id = c.id)
1399 GROUP BY c.accno, c.description, c.category
1403 SELECT c.accno, c.description, c.category,
1404 SUM(ac.sellprice * ac.qty) * -1 AS amount
1406 JOIN ap a ON (ac.trans_id = a.id)
1407 JOIN parts p ON (ac.parts_id = p.id)
1408 JOIN chart c ON (p.expense_accno_id = c.id)
1413 GROUP BY c.accno, c.description, c.category
1422 $sth = $dbh->prepare($query);
1423 $sth->execute || $form->dberror($query);
1425 # prepare query for each account
1426 $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1428 JOIN chart c ON (c.id = ac.chart_id)
1434 AND c.accno = ?) AS debit,
1436 (SELECT SUM(ac.amount)
1438 JOIN chart c ON (c.id = ac.chart_id)
1444 AND c.accno = ?) AS credit
1447 if ($form->{accounttype} eq 'gifi') {
1449 $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1451 JOIN chart c ON (c.id = ac.chart_id)
1457 AND c.gifi_accno = ?) AS debit,
1459 (SELECT SUM(ac.amount)
1461 JOIN chart c ON (c.id = ac.chart_id)
1467 AND c.gifi_accno = ?) AS credit|;
1471 $drcr = $dbh->prepare($query);
1473 if ($form->{project_id}) {
1475 # prepare query for each account
1476 $query = qq|SELECT (SELECT SUM(ac.sellprice * ac.qty) * -1
1478 JOIN parts p ON (ac.parts_id = p.id)
1479 JOIN ap a ON (ac.trans_id = a.id)
1480 JOIN chart c ON (p.expense_accno_id = c.id)
1485 AND c.accno = ?) AS debit,
1487 (SELECT SUM(ac.sellprice * ac.qty)
1489 JOIN parts p ON (ac.parts_id = p.id)
1490 JOIN ar a ON (ac.trans_id = a.id)
1491 JOIN chart c ON (p.income_accno_id = c.id)
1496 AND c.accno = ?) AS credit
1499 $project_drcr = $dbh->prepare($query);
1503 # calculate the debit and credit in the period
1504 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1505 $trb{ $ref->{accno} }{description} = $ref->{description};
1506 $trb{ $ref->{accno} }{charttype} = 'A';
1507 $trb{ $ref->{accno} }{category} = $ref->{category};
1508 $trb{ $ref->{accno} }{amount} += $ref->{amount};
1512 my ($debit, $credit);
1514 foreach my $accno (sort keys %trb) {
1517 $ref->{accno} = $accno;
1518 map { $ref->{$_} = $trb{$accno}{$_} }
1519 qw(description category charttype amount);
1521 $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2);
1523 if ($trb{$accno}{charttype} eq 'A') {
1526 $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
1528 ($debit, $credit) = (0, 0);
1529 while (($debit, $credit) = $drcr->fetchrow_array) {
1530 $ref->{debit} += $debit;
1531 $ref->{credit} += $credit;
1535 if ($form->{project_id}) {
1538 $project_drcr->execute($ref->{accno}, $ref->{accno})
1539 || $form->dberror($query);
1541 ($debit, $credit) = (0, 0);
1542 while (($debit, $credit) = $project_drcr->fetchrow_array) {
1543 $ref->{debit} += $debit;
1544 $ref->{credit} += $credit;
1546 $project_drcr->finish;
1549 $ref->{debit} = $form->round_amount($ref->{debit}, 2);
1550 $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1555 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1556 $accno = pop @accno;
1558 $trb{$accno}{debit} += $ref->{debit};
1559 $trb{$accno}{credit} += $ref->{credit};
1562 push @{ $form->{TB} }, $ref;
1568 # debits and credits for headings
1569 foreach $accno (@headingaccounts) {
1570 foreach $ref (@{ $form->{TB} }) {
1571 if ($accno eq $ref->{accno}) {
1572 $ref->{debit} = $trb{$accno}{debit};
1573 $ref->{credit} = $trb{$accno}{credit};
1578 $main::lxdebug->leave_sub();
1582 $main::lxdebug->enter_sub();
1584 my ($self, $myconfig, $form) = @_;
1586 # connect to database
1587 my $dbh = $form->dbconnect($myconfig);
1588 my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
1590 $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1592 my $where = "1 = 1";
1595 if ($form->{"$form->{ct}_id"}) {
1596 $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
1598 if ($form->{ $form->{ct} }) {
1599 $name = $form->like(lc $form->{ $form->{ct} });
1600 $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{ $form->{ct} };
1605 if ($form->{department}) {
1606 ($null, $department_id) = split /--/, $form->{department};
1608 JOIN department d ON (a.department_id = d.id)
1611 $where .= qq| AND a.department_id = $department_id|;
1614 # select outstanding vendors or customers, depends on $ct
1615 my $query = qq|SELECT DISTINCT ct.id, ct.name
1616 FROM $form->{ct} ct, $form->{arap} a
1619 AND a.$form->{ct}_id = ct.id
1620 AND a.paid != a.amount
1621 AND (a.transdate <= '$form->{todate}')
1624 my $sth = $dbh->prepare($query);
1625 $sth->execute || $form->dberror;
1627 my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
1629 # for each company that has some stuff outstanding
1630 while (my ($id) = $sth->fetchrow_array) {
1634 -- between 0-30 days
1636 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1637 street, zipcode, city, country, contact, email,
1638 phone as customerphone, fax as customerfax, $form->{ct}number,
1639 "invnumber", "transdate",
1640 (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1641 "duedate", invoice, $form->{arap}.id,
1642 (SELECT $buysell FROM exchangerate
1643 WHERE $form->{arap}.curr = exchangerate.curr
1644 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1645 FROM $form->{arap}, $form->{ct}
1646 WHERE paid != amount
1647 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1648 AND $form->{ct}.id = $id
1650 transdate <= (date '$form->{todate}' - interval '0 days')
1651 AND transdate >= (date '$form->{todate}' - interval '30 days')
1656 -- between 31-60 days
1658 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1659 street, zipcode, city, country, contact, email,
1660 phone as customerphone, fax as customerfax, $form->{ct}number,
1661 "invnumber", "transdate",
1662 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1663 "duedate", invoice, $form->{arap}.id,
1664 (SELECT $buysell FROM exchangerate
1665 WHERE $form->{arap}.curr = exchangerate.curr
1666 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1667 FROM $form->{arap}, $form->{ct}
1668 WHERE paid != amount
1669 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1670 AND $form->{ct}.id = $id
1672 transdate < (date '$form->{todate}' - interval '30 days')
1673 AND transdate >= (date '$form->{todate}' - interval '60 days')
1678 -- between 61-90 days
1680 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1681 street, zipcode, city, country, contact, email,
1682 phone as customerphone, fax as customerfax, $form->{ct}number,
1683 "invnumber", "transdate",
1684 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1685 "duedate", invoice, $form->{arap}.id,
1686 (SELECT $buysell FROM exchangerate
1687 WHERE $form->{arap}.curr = exchangerate.curr
1688 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1689 FROM $form->{arap}, $form->{ct}
1690 WHERE paid != amount
1691 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1692 AND $form->{ct}.id = $id
1694 transdate < (date '$form->{todate}' - interval '60 days')
1695 AND transdate >= (date '$form->{todate}' - interval '90 days')
1702 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1703 street, zipcode, city, country, contact, email,
1704 phone as customerphone, fax as customerfax, $form->{ct}number,
1705 "invnumber", "transdate",
1706 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1707 "duedate", invoice, $form->{arap}.id,
1708 (SELECT $buysell FROM exchangerate
1709 WHERE $form->{arap}.curr = exchangerate.curr
1710 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1711 FROM $form->{arap}, $form->{ct}
1712 WHERE paid != amount
1713 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1714 AND $form->{ct}.id = $id
1715 AND transdate < (date '$form->{todate}' - interval '90 days')
1719 ctid, transdate, invnumber
1723 my $sth = $dbh->prepare($query);
1724 $sth->execute || $form->dberror;
1726 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1727 $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
1728 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1729 push @{ $form->{AG} }, $ref;
1741 $main::lxdebug->leave_sub();
1745 $main::lxdebug->enter_sub();
1747 my ($self, $myconfig, $form) = @_;
1749 # connect to database
1750 my $dbh = $form->dbconnect($myconfig);
1752 my $query = qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1754 WHERE ct.id = $form->{"$form->{ct}_id"}|;
1755 my $sth = $dbh->prepare($query);
1756 $sth->execute || $form->dberror;
1758 ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
1759 $sth->fetchrow_array;
1763 $main::lxdebug->leave_sub();
1766 sub get_taxaccounts {
1767 $main::lxdebug->enter_sub();
1769 my ($self, $myconfig, $form) = @_;
1771 # connect to database
1772 my $dbh = $form->dbconnect($myconfig);
1775 my $query = qq|SELECT c.accno, c.description, t.rate
1777 WHERE c.link LIKE '%CT_tax%'
1778 AND c.id = t.chart_id
1780 my $sth = $dbh->prepare($query);
1781 $sth->execute || $form->dberror;
1784 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1785 push @{ $form->{taxaccounts} }, $ref;
1789 # get gifi tax accounts
1790 $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description,
1792 FROM gifi g, chart c, tax t
1793 WHERE g.accno = c.gifi_accno
1794 AND c.id = t.chart_id
1795 AND c.link LIKE '%CT_tax%'
1796 GROUP BY g.accno, g.description
1798 $sth = $dbh->prepare($query);
1799 $sth->execute || $form->dberror;
1801 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1802 push @{ $form->{gifi_taxaccounts} }, $ref;
1808 $main::lxdebug->leave_sub();
1812 $main::lxdebug->enter_sub();
1814 my ($self, $myconfig, $form) = @_;
1816 # connect to database
1817 my $dbh = $form->dbconnect($myconfig);
1819 my ($null, $department_id) = split /--/, $form->{department};
1822 my $where = "1 = 1";
1824 if ($department_id) {
1826 AND a.department_id = $department_id
1832 if ($form->{accno}) {
1833 if ($form->{accno} =~ /^gifi_/) {
1834 ($null, $accno) = split /_/, $form->{accno};
1835 $rate = $form->{"$form->{accno}_rate"};
1836 $accno = qq| AND ch.gifi_accno = '$accno'|;
1838 $accno = $form->{accno};
1839 $rate = $form->{"$form->{accno}_rate"};
1840 $accno = qq| AND ch.accno = '$accno'|;
1847 if ($form->{db} eq 'ar') {
1848 $table = "customer";
1851 if ($form->{db} eq 'ap') {
1856 my $transdate = "a.transdate";
1858 if ($form->{method} eq 'cash') {
1859 $transdate = "a.datepaid";
1862 ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig);
1869 JOIN chart ON (chart_id = id)
1870 WHERE link LIKE '%${ARAP}_paid%'
1871 AND transdate <= '$todate'
1876 # if there are any dates construct a where
1877 if ($form->{fromdate} || $form->{todate}) {
1878 if ($form->{fromdate}) {
1879 $where .= " AND $transdate >= '$form->{fromdate}'";
1881 if ($form->{todate}) {
1882 $where .= " AND $transdate <= '$form->{todate}'";
1886 my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1888 my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1889 $sortorder = $form->{sort} if $form->{sort};
1891 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
1892 a.invnumber, n.name, a.netamount,
1893 ac.amount * $ml AS tax
1895 JOIN $form->{db} a ON (a.id = ac.trans_id)
1896 JOIN chart ch ON (ch.id = ac.chart_id)
1897 JOIN $table n ON (n.id = a.${table}_id)
1902 SELECT a.id, '1' AS invoice, $transdate AS transdate,
1903 a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1904 i.sellprice * i.qty * $rate * $ml AS tax
1906 JOIN $form->{db} a ON (a.id = ac.trans_id)
1907 JOIN chart ch ON (ch.id = ac.chart_id)
1908 JOIN $table n ON (n.id = a.${table}_id)
1909 JOIN ${table}tax t ON (t.${table}_id = n.id)
1910 JOIN invoice i ON (i.trans_id = a.id)
1911 JOIN partstax p ON (p.parts_id = i.parts_id)
1915 ORDER by $sortorder|;
1917 if ($form->{report} =~ /nontaxable/) {
1919 # only gather up non-taxable transactions
1920 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
1921 a.invnumber, n.name, a.netamount
1923 JOIN $form->{db} a ON (a.id = ac.trans_id)
1924 JOIN $table n ON (n.id = a.${table}_id)
1927 AND a.netamount = a.amount
1929 SELECT a.id, '1' AS invoice, $transdate AS transdate,
1930 a.invnumber, n.name, i.sellprice * i.qty AS netamount
1932 JOIN $form->{db} a ON (a.id = ac.trans_id)
1933 JOIN $table n ON (n.id = a.${table}_id)
1934 JOIN invoice i ON (i.trans_id = a.id)
1938 a.${table}_id NOT IN (
1939 SELECT ${table}_id FROM ${table}tax t (${table}_id)
1942 SELECT parts_id FROM partstax p (parts_id)
1945 GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
1946 ORDER by $sortorder|;
1949 my $sth = $dbh->prepare($query);
1950 $sth->execute || $form->dberror($query);
1952 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1953 push @{ $form->{TR} }, $ref;
1959 $main::lxdebug->leave_sub();
1962 sub paymentaccounts {
1963 $main::lxdebug->enter_sub();
1965 my ($self, $myconfig, $form) = @_;
1967 # connect to database, turn AutoCommit off
1968 my $dbh = $form->dbconnect_noauto($myconfig);
1970 my $ARAP = uc $form->{db};
1972 # get A(R|P)_paid accounts
1973 my $query = qq|SELECT c.accno, c.description
1975 WHERE c.link LIKE '%${ARAP}_paid%'|;
1976 my $sth = $dbh->prepare($query);
1977 $sth->execute || $form->dberror($query);
1979 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1980 push @{ $form->{PR} }, $ref;
1986 $main::lxdebug->leave_sub();
1990 $main::lxdebug->enter_sub();
1992 my ($self, $myconfig, $form) = @_;
1994 # connect to database, turn AutoCommit off
1995 my $dbh = $form->dbconnect_noauto($myconfig);
1998 if ($form->{db} eq 'ar') {
1999 $table = 'customer';
2002 if ($form->{db} eq 'ap') {
2010 if ($form->{department_id}) {
2012 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
2016 AND t.department_id = $form->{department_id}
2020 if ($form->{fromdate}) {
2021 $where .= " AND ac.transdate >= '$form->{fromdate}'";
2023 if ($form->{todate}) {
2024 $where .= " AND ac.transdate <= '$form->{todate}'";
2026 if (!$form->{fx_transaction}) {
2027 $where .= " AND ac.fx_transaction = '0'";
2032 if ($form->{reference}) {
2033 $reference = $form->like(lc $form->{reference});
2034 $invnumber = " AND lower(a.invnumber) LIKE '$reference'";
2035 $reference = " AND lower(g.reference) LIKE '$reference'";
2037 if ($form->{source}) {
2038 my $source = $form->like(lc $form->{source});
2039 $where .= " AND lower(ac.source) LIKE '$source'";
2041 if ($form->{memo}) {
2042 my $memo = $form->like(lc $form->{memo});
2043 $where .= " AND lower(ac.memo) LIKE '$memo'";
2046 my $sortorder = join ', ',
2047 $form->sort_columns(qw(name invnumber ordnumber transdate source));
2048 $sortorder = $form->{sort} if $form->{sort};
2050 # cycle through each id
2051 foreach my $accno (split(/ /, $form->{paymentaccounts})) {
2053 $query = qq|SELECT c.id, c.accno, c.description
2055 WHERE c.accno = '$accno'|;
2056 $sth = $dbh->prepare($query);
2057 $sth->execute || $form->dberror($query);
2059 my $ref = $sth->fetchrow_hashref(NAME_lc);
2060 push @{ $form->{PR} }, $ref;
2063 $query = qq|SELECT c.name, a.invnumber, a.ordnumber,
2064 ac.transdate, ac.amount * $ml AS paid, ac.source,
2065 a.invoice, a.id, ac.memo, '$form->{db}' AS module
2067 JOIN $form->{db} a ON (ac.trans_id = a.id)
2068 JOIN $table c ON (c.id = a.${table}_id)
2070 WHERE ac.chart_id = $ref->{id}
2075 SELECT g.description, g.reference, NULL AS ordnumber,
2076 ac.transdate, ac.amount * $ml AS paid, ac.source,
2077 '0' as invoice, g.id, ac.memo, 'gl' AS module
2079 JOIN gl g ON (g.id = ac.trans_id)
2081 WHERE ac.chart_id = $ref->{id}
2084 AND (ac.amount * $ml) > 0
2085 ORDER BY $sortorder|;
2087 $sth = $dbh->prepare($query);
2088 $sth->execute || $form->dberror($query);
2090 while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
2091 push @{ $form->{ $ref->{id} } }, $pr;
2099 $main::lxdebug->leave_sub();
2103 $main::lxdebug->enter_sub();
2105 my ($self, $myconfig, $form) = @_;
2107 # connect to database
2108 my $dbh = $form->dbconnect($myconfig);
2110 my $last_period = 0;
2111 my $category = "pos_bwa";
2113 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);
2115 $form->{decimalplaces} *= 1;
2117 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2120 # if there are any compare dates
2121 if ($form->{fromdate} || $form->{todate}) {
2123 if ($form->{fromdate}) {
2124 $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2127 $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2130 $kummfromdate = $form->{comparefromdate};
2131 $kummtodate = $form->{comparetodate};
2132 &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form,
2136 @periods = qw(jetzt kumm);
2137 @gesamtleistung = qw(1 2 3);
2138 @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 19 20);
2140 qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
2142 foreach $key (@periods) {
2143 $form->{ "$key" . "gesamtleistung" } = 0;
2144 $form->{ "$key" . "gesamtkosten" } = 0;
2147 foreach $category (@categories) {
2149 if (defined($form->{$category}{$key})) {
2150 $form->{"$key$category"} =
2151 $form->format_amount($myconfig,
2152 $form->round_amount($form->{$category}{$key}, 2
2153 ), $form->{decimalplaces}, '0');
2156 foreach $item (@gesamtleistung) {
2157 $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key};
2159 foreach $item (@gesamtkosten) {
2160 $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key};
2162 $form->{ "$key" . "rohertrag" } =
2163 $form->{ "$key" . "gesamtleistung" } - $form->{4}{$key};
2164 $form->{ "$key" . "betriebrohertrag" } =
2165 $form->{ "$key" . "rohertrag" } + $form->{5}{$key};
2166 $form->{ "$key" . "betriebsergebnis" } =
2167 $form->{ "$key" . "betriebrohertrag" } -
2168 $form->{ "$key" . "gesamtkosten" };
2169 $form->{ "$key" . "neutraleraufwand" } =
2170 $form->{30}{$key} + $form->{31}{$key};
2171 $form->{ "$key" . "neutralertrag" } =
2172 $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
2173 $form->{ "$key" . "ergebnisvorsteuern" } =
2174 $form->{ "$key" . "betriebsergebnis" } -
2175 $form->{ "$key" . "neutraleraufwand" } +
2176 $form->{ "$key" . "neutralertrag" };
2177 $form->{ "$key" . "ergebnis" } =
2178 $form->{ "$key" . "ergebnisvorsteuern" } + $form->{35}{$key};
2180 if ($form->{ "$key" . "gesamtleistung" } > 0) {
2181 foreach $category (@categories) {
2182 if (defined($form->{$category}{$key})) {
2183 $form->{ "$key" . "gl" . "$category" } =
2184 $form->format_amount(
2186 $form->round_amount(
2187 ($form->{$category}{$key} /
2188 $form->{ "$key" . "gesamtleistung" } * 100
2190 $form->{decimalplaces}
2191 ), $form->{decimalplaces}, '0');
2194 foreach $item (@ergebnisse) {
2195 $form->{ "$key" . "gl" . "$item" } =
2196 $form->format_amount($myconfig,
2197 $form->round_amount(
2198 ( $form->{ "$key" . "$item" } /
2199 $form->{ "$key" . "gesamtleistung" } * 100
2201 $form->{decimalplaces}
2202 ), $form->{decimalplaces}, '0');
2206 if ($form->{ "$key" . "gesamtkosten" } > 0) {
2207 foreach $category (@categories) {
2208 if (defined($form->{$category}{$key})) {
2209 $form->{ "$key" . "gk" . "$category" } =
2210 $form->format_amount($myconfig,
2211 $form->round_amount(
2212 ($form->{$category}{$key} /
2213 $form->{ "$key" . "gesamtkosten" } * 100
2215 $form->{decimalplaces}
2216 ), $form->{decimalplaces}, '0');
2219 foreach $item (@ergebnisse) {
2220 $form->{ "$key" . "gk" . "$item" } =
2221 $form->format_amount($myconfig,
2222 $form->round_amount(
2223 ($form->{ "$key" . "$item" } /
2224 $form->{ "$key" . "gesamtkosten" } * 100
2226 $form->{decimalplaces}
2227 ), $form->{decimalplaces}, '0');
2231 if ($form->{10}{$key} > 0) {
2232 foreach $category (@categories) {
2233 if (defined($form->{$category}{$key})) {
2234 $form->{ "$key" . "pk" . "$category" } =
2235 $form->format_amount(
2237 $form->round_amount(
2238 ($form->{$category}{$key} / $form->{10}{$key} * 100),
2239 $form->{decimalplaces}
2240 ), $form->{decimalplaces}, '0');
2243 foreach $item (@ergebnisse) {
2244 $form->{ "$key" . "pk" . "$item" } =
2245 $form->format_amount($myconfig,
2246 $form->round_amount(
2247 ($form->{ "$key" . "$item" } /
2248 $form->{10}{$key} * 100
2250 $form->{decimalplaces}
2251 ), $form->{decimalplaces}, '0');
2255 if ($form->{4}{$key} > 0) {
2256 foreach $category (@categories) {
2257 if (defined($form->{$category}{$key})) {
2258 $form->{ "$key" . "auf" . "$category" } =
2259 $form->format_amount(
2261 $form->round_amount(
2262 ($form->{$category}{$key} / $form->{4}{$key} * 100),
2263 $form->{decimalplaces}
2264 ), $form->{decimalplaces}, '0');
2267 foreach $item (@ergebnisse) {
2268 $form->{ "$key" . "auf" . "$item" } =
2269 $form->format_amount($myconfig,
2270 $form->round_amount(
2271 ($form->{ "$key" . "$item" } /
2272 $form->{4}{$key} * 100
2274 $form->{decimalplaces}
2275 ), $form->{decimalplaces}, '0');
2279 foreach $item (@ergebnisse) {
2280 $form->{ "$key" . "$item" } =
2281 $form->format_amount($myconfig,
2282 $form->round_amount($form->{ "$key" . "$item" },
2283 $form->{decimalplaces}
2284 ), $form->{decimalplaces}, '0');
2290 $main::lxdebug->leave_sub();
2294 $main::lxdebug->enter_sub();
2296 my ($self, $myconfig, $form) = @_;
2298 # connect to database
2299 my $dbh = $form->dbconnect($myconfig);
2301 my $last_period = 0;
2302 my $category = "pos_ustva";
2303 my @categories_cent = qw(51r 511 86r 861 97r 971 93r 931
2304 96 66 43 45 53 62 65 67);
2305 my @categories_euro = qw(48 51 86 91 97 93 94);
2306 $form->{decimalplaces} *= 1;
2308 foreach $item (@categories_cent) {
2309 $form->{"$item"} = 0;
2311 foreach $item (@categories_euro) {
2312 $form->{"$item"} = 0;
2315 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2318 # foreach $item (@categories_cent) {
2319 # if ($form->{$item}{"jetzt"} > 0) {
2320 # $form->{$item} = $form->{$item}{"jetzt"};
2321 # delete $form->{$item}{"jetzt"};
2324 # foreach $item (@categories_euro) {
2325 # if ($form->{$item}{"jetzt"} > 0) {
2326 # $form->{$item} = $form->{$item}{"jetzt"};
2327 # delete $form->{$item}{"jetzt"};
2328 # } foreach $item (@categories_cent) {
2329 # if ($form->{$item}{"jetzt"} > 0) {
2330 # $form->{$item} = $form->{$item}{"jetzt"};
2331 # delete $form->{$item}{"jetzt"};
2334 # foreach $item (@categories_euro) {
2335 # if ($form->{$item}{"jetzt"} > 0) {
2336 # $form->{$item} = $form->{$item}{"jetzt"};
2337 # delete $form->{$item}{"jetzt"};
2344 # Berechnung der USTVA Formularfelder
2346 $form->{"51r"} = $form->{"511"};
2347 $form->{"86r"} = $form->{"861"};
2348 $form->{"97r"} = $form->{"971"};
2349 $form->{"93r"} = $form->{"931"};
2350 #$form->{"96"} = $form->{"94"} * 0.16;
2352 $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} +
2354 $form->{"45"} = $form->{"43"};
2355 $form->{"53"} = $form->{"43"};
2356 $form->{"62"} = $form->{"43"} - $form->{"66"};
2357 $form->{"65"} = $form->{"43"} - $form->{"66"};
2358 $form->{"67"} = $form->{"43"} - $form->{"66"};
2360 foreach $item (@categories_cent) {
2362 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2), 2, '0');
2365 foreach $item (@categories_euro) {
2367 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0), 0, '0');
2372 $main::lxdebug->leave_sub();
2375 sub income_statement {
2376 $main::lxdebug->enter_sub();
2378 my ($self, $myconfig, $form) = @_;
2380 # connect to database
2381 my $dbh = $form->dbconnect($myconfig);
2383 my $last_period = 0;
2384 my $category = "pos_eur";
2385 my @categories_einnahmen = qw(1 2 3 4 5 6 7);
2386 my @categories_ausgaben =
2387 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);
2389 my @ergebnisse = qw(sumeura sumeurb guvsumme);
2391 $form->{decimalplaces} *= 1;
2393 foreach $item (@categories_einnahmen) {
2396 foreach $item (@categories_ausgaben) {
2400 foreach $item (@ergebnisse) {
2404 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate},
2407 foreach $item (@categories_einnahmen) {
2408 $form->{"eur${item}"} =
2409 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2410 $form->{"sumeura"} += $form->{$item};
2412 foreach $item (@categories_ausgaben) {
2413 $form->{"eur${item}"} =
2414 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2415 $form->{"sumeurb"} += $form->{$item};
2418 $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
2420 foreach $item (@ergebnisse) {
2422 $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2));
2424 $main::lxdebug->leave_sub();