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 #======================================================================
37 sub income_statement {
38 $main::lxdebug->enter_sub();
40 my ($self, $myconfig, $form) = @_;
43 my $dbh = $form->dbconnect($myconfig);
46 my @categories = qw(I E);
49 $form->{decimalplaces} *= 1;
51 &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories);
53 # if there are any compare dates
54 if ($form->{comparefromdate} || $form->{comparetodate}) {
57 &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories);
65 # now we got $form->{I}{accno}{ }
66 # and $form->{E}{accno}{ }
68 my %account = ( 'I' => { 'label' => 'income',
71 'E' => { 'label' => 'expense',
72 'labels' => 'expenses',
78 foreach $category (@categories) {
80 foreach $key (sort keys %{ $form->{$category} }) {
81 # push description onto array
83 $str = ($form->{l_heading}) ? $form->{padding} : "";
85 if ($form->{$category}{$key}{charttype} eq "A") {
86 $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
88 if ($form->{$category}{$key}{charttype} eq "H") {
89 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
91 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
92 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
95 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
100 $str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
102 $account{$category}{subthis} = $form->{$category}{$key}{this};
103 $account{$category}{sublast} = $form->{$category}{$key}{last};
104 $account{$category}{subdescription} = $form->{$category}{$key}{description};
105 $account{$category}{subtotal} = 1;
107 $form->{$category}{$key}{this} = 0;
108 $form->{$category}{$key}{last} = 0;
110 next unless $form->{l_heading};
115 push(@{$form->{"$account{$category}{label}_account"}}, $str);
117 if ($form->{$category}{$key}{charttype} eq 'A') {
118 $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
122 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
124 # add amount or - for last period
126 $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
128 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
132 $str = ($form->{l_heading}) ? $form->{padding} : "";
133 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
134 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
135 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
138 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
145 # totals for income and expenses
146 $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
147 $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
149 # total for income/loss
150 $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
153 # total for income/loss
154 $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
156 # totals for income and expenses for last_period
157 $form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- ");
158 $form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
163 $form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- ");
164 $form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- ");
165 $form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- ");
167 $main::lxdebug->leave_sub();
172 $main::lxdebug->enter_sub();
174 my ($self, $myconfig, $form) = @_;
176 # connect to database
177 my $dbh = $form->dbconnect($myconfig);
180 my @categories = qw(A C L Q);
182 # if there are any dates construct a where
183 if ($form->{asofdate}) {
185 $form->{this_period} = "$form->{asofdate}";
186 $form->{period} = "$form->{asofdate}";
190 $form->{decimalplaces} *= 1;
192 &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories);
194 # if there are any compare dates
195 if ($form->{compareasofdate}) {
198 &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories);
200 $form->{last_period} = "$form->{compareasofdate}";
209 # now we got $form->{A}{accno}{ } assets
210 # and $form->{L}{accno}{ } liabilities
211 # and $form->{Q}{accno}{ } equity
212 # build asset accounts
217 my %account = ( 'A' => { 'label' => 'asset',
218 'labels' => 'assets',
220 'L' => { 'label' => 'liability',
221 'labels' => 'liabilities',
223 'Q' => { 'label' => 'equity',
224 'labels' => 'equity',
228 foreach $category (grep { !/C/ } @categories) {
230 foreach $key (sort keys %{ $form->{$category} }) {
232 $str = ($form->{l_heading}) ? $form->{padding} : "";
234 if ($form->{$category}{$key}{charttype} eq "A") {
235 $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
237 if ($form->{$category}{$key}{charttype} eq "H") {
238 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
240 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
241 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
244 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
248 $str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
250 $account{$category}{subthis} = $form->{$category}{$key}{this};
251 $account{$category}{sublast} = $form->{$category}{$key}{last};
252 $account{$category}{subdescription} = $form->{$category}{$key}{description};
253 $account{$category}{subtotal} = 1;
255 $form->{$category}{$key}{this} = 0;
256 $form->{$category}{$key}{last} = 0;
258 next unless $form->{l_heading};
263 # push description onto array
264 push(@{$form->{"$account{$category}{label}_account"}}, $str);
266 if ($form->{$category}{$key}{charttype} eq 'A') {
267 $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
271 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
274 $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
276 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
280 $str = ($form->{l_heading}) ? $form->{padding} : "";
281 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
282 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
283 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
286 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
293 # totals for assets, liabilities
294 $form->{total_assets_this_period} = $form->round_amount($form->{total_assets_this_period}, $form->{decimalplaces});
295 $form->{total_liabilities_this_period} = $form->round_amount($form->{total_liabilities_this_period}, $form->{decimalplaces});
296 $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period}, $form->{decimalplaces});
299 $form->{earnings_this_period} = $form->{total_assets_this_period} - $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
301 push(@{$form->{equity_this_period}}, $form->format_amount($myconfig, $form->{earnings_this_period}, $form->{decimalplaces}, "- "));
303 $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period} + $form->{earnings_this_period}, $form->{decimalplaces});
305 # add liability + equity
306 $form->{total_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period} + $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
310 # totals for assets, liabilities
311 $form->{total_assets_last_period} = $form->round_amount($form->{total_assets_last_period}, $form->{decimalplaces});
312 $form->{total_liabilities_last_period} = $form->round_amount($form->{total_liabilities_last_period}, $form->{decimalplaces});
313 $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period}, $form->{decimalplaces});
315 # calculate retained earnings
316 $form->{earnings_last_period} = $form->{total_assets_last_period} - $form->{total_liabilities_last_period} - $form->{total_equity_last_period};
318 push(@{$form->{equity_last_period}}, $form->format_amount($myconfig,$form->{earnings_last_period}, $form->{decimalplaces}, "- "));
320 $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period} + $form->{earnings_last_period}, $form->{decimalplaces});
322 # add liability + equity
323 $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period} + $form->{total_equity_last_period}, $form->{decimalplaces}, "- ");
328 $form->{total_liabilities_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_liabilities_last_period} != 0);
330 $form->{total_equity_last_period} = $form->format_amount($myconfig, $form->{total_equity_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_equity_last_period} != 0);
332 $form->{total_assets_last_period} = $form->format_amount($myconfig, $form->{total_assets_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_assets_last_period} != 0);
334 $form->{total_assets_this_period} = $form->format_amount($myconfig, $form->{total_assets_this_period}, $form->{decimalplaces}, "- ");
336 $form->{total_liabilities_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period}, $form->{decimalplaces}, "- ");
338 $form->{total_equity_this_period} = $form->format_amount($myconfig, $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
340 $main::lxdebug->leave_sub();
345 $main::lxdebug->enter_sub();
347 my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
349 my ($null, $department_id) = split /--/, $form->{department};
360 my $category = "AND (";
361 foreach $item (@{ $categories }) {
362 $category .= qq|c.category = '$item' OR |;
364 $category =~ s/OR $/\)/;
368 $query = qq|SELECT c.accno, c.description, c.category
370 WHERE c.charttype = 'H'
374 if ($form->{accounttype} eq 'gifi')
376 $query = qq|SELECT g.accno, g.description, c.category
378 JOIN chart c ON (c.gifi_accno = g.accno)
379 WHERE c.charttype = 'H'
384 $sth = $dbh->prepare($query);
385 $sth->execute || $form->dberror($query);
387 my @headingaccounts = ();
388 while ($ref = $sth->fetchrow_hashref(NAME_lc))
390 $form->{$ref->{category}}{$ref->{accno}}{description} = "$ref->{description}";
391 $form->{$ref->{category}}{$ref->{accno}}{charttype} = "H";
392 $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
394 push @headingaccounts, $ref->{accno};
400 if ($form->{method} eq 'cash') {
401 $subwhere .= " AND transdate >= '$fromdate'";
402 $glwhere = " AND ac.transdate >= '$fromdate'";
404 $where .= " AND ac.transdate >= '$fromdate'";
409 $where .= " AND ac.transdate <= '$todate'";
410 $subwhere .= " AND transdate <= '$todate'";
417 JOIN department t ON (a.department_id = t.id)
420 AND t.id = $department_id
424 if ($form->{project_id})
427 AND ac.project_id = $form->{project_id}
432 if ($form->{accounttype} eq 'gifi')
435 if ($form->{method} eq 'cash')
440 SELECT g.accno, sum(ac.amount) AS amount,
441 g.description, c.category
443 JOIN chart c ON (c.id = ac.chart_id)
444 JOIN ar a ON (a.id = ac.trans_id)
445 JOIN gifi g ON (g.accno = c.gifi_accno)
454 JOIN chart ON (chart_id = id)
455 WHERE link LIKE '%AR_paid%'
459 GROUP BY g.accno, g.description, c.category
463 SELECT '' AS accno, SUM(ac.amount) AS amount,
464 '' AS description, c.category
466 JOIN chart c ON (c.id = ac.chart_id)
467 JOIN ar a ON (a.id = ac.trans_id)
472 AND c.gifi_accno = ''
477 JOIN chart ON (chart_id = id)
478 WHERE link LIKE '%AR_paid%'
486 SELECT g.accno, sum(ac.amount) AS amount,
487 g.description, c.category
489 JOIN chart c ON (c.id = ac.chart_id)
490 JOIN ap a ON (a.id = ac.trans_id)
491 JOIN gifi g ON (g.accno = c.gifi_accno)
500 JOIN chart ON (chart_id = id)
501 WHERE link LIKE '%AP_paid%'
505 GROUP BY g.accno, g.description, c.category
509 SELECT '' AS accno, SUM(ac.amount) AS amount,
510 '' AS description, c.category
512 JOIN chart c ON (c.id = ac.chart_id)
513 JOIN ap a ON (a.id = ac.trans_id)
518 AND c.gifi_accno = ''
523 JOIN chart ON (chart_id = id)
524 WHERE link LIKE '%AP_paid%'
534 SELECT g.accno, sum(ac.amount) AS amount,
535 g.description, c.category
537 JOIN chart c ON (c.id = ac.chart_id)
538 JOIN gifi g ON (g.accno = c.gifi_accno)
539 JOIN gl a ON (a.id = ac.trans_id)
545 AND NOT (c.link = 'AR' OR c.link = 'AP')
547 GROUP BY g.accno, g.description, c.category
551 SELECT '' AS accno, SUM(ac.amount) AS amount,
552 '' AS description, c.category
554 JOIN chart c ON (c.id = ac.chart_id)
555 JOIN gl a ON (a.id = ac.trans_id)
561 AND c.gifi_accno = ''
562 AND NOT (c.link = 'AR' OR c.link = 'AP')
567 if ($form->{project_id}) {
573 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
574 g.description AS description, c.category
576 JOIN ar a ON (a.id = ac.trans_id)
577 JOIN parts p ON (ac.parts_id = p.id)
578 JOIN chart c on (p.income_accno_id = c.id)
579 JOIN gifi g ON (g.accno = c.gifi_accno)
581 -- use transdate from subwhere
582 WHERE 1 = 1 $subwhere
589 JOIN chart ON (chart_id = id)
590 WHERE link LIKE '%AR_paid%'
594 GROUP BY g.accno, g.description, c.category
598 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
599 g.description AS description, c.category
601 JOIN ap a ON (a.id = ac.trans_id)
602 JOIN parts p ON (ac.parts_id = p.id)
603 JOIN chart c on (p.expense_accno_id = c.id)
604 JOIN gifi g ON (g.accno = c.gifi_accno)
606 WHERE 1 = 1 $subwhere
613 JOIN chart ON (chart_id = id)
614 WHERE link LIKE '%AP_paid%'
618 GROUP BY g.accno, g.description, c.category
627 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
630 AND t.department_id = $department_id
637 SELECT g.accno, SUM(ac.amount) AS amount,
638 g.description, c.category
640 JOIN chart c ON (c.id = ac.chart_id)
641 JOIN gifi g ON (c.gifi_accno = g.accno)
647 GROUP BY g.accno, g.description, c.category
651 SELECT '' AS accno, SUM(ac.amount) AS amount,
652 '' AS description, c.category
654 JOIN chart c ON (c.id = ac.chart_id)
659 AND c.gifi_accno = ''
664 if ($form->{project_id})
671 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
672 g.description AS description, c.category
674 JOIN ar a ON (a.id = ac.trans_id)
675 JOIN parts p ON (ac.parts_id = p.id)
676 JOIN chart c on (p.income_accno_id = c.id)
677 JOIN gifi g ON (c.gifi_accno = g.accno)
679 -- use transdate from subwhere
680 WHERE 1 = 1 $subwhere
684 GROUP BY g.accno, g.description, c.category
688 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
689 g.description AS description, c.category
691 JOIN ap a ON (a.id = ac.trans_id)
692 JOIN parts p ON (ac.parts_id = p.id)
693 JOIN chart c on (p.expense_accno_id = c.id)
694 JOIN gifi g ON (c.gifi_accno = g.accno)
696 WHERE 1 = 1 $subwhere
700 GROUP BY g.accno, g.description, c.category
706 } else { # standard account
708 if ($form->{method} eq 'cash')
713 SELECT c.accno, sum(ac.amount) AS amount,
714 c.description, c.category
716 JOIN chart c ON (c.id = ac.chart_id)
717 JOIN ar a ON (a.id = ac.trans_id)
726 JOIN chart ON (chart_id = id)
727 WHERE link LIKE '%AR_paid%'
732 GROUP BY c.accno, c.description, c.category
736 SELECT c.accno, sum(ac.amount) AS amount,
737 c.description, c.category
739 JOIN chart c ON (c.id = ac.chart_id)
740 JOIN ap a ON (a.id = ac.trans_id)
749 JOIN chart ON (chart_id = id)
750 WHERE link LIKE '%AP_paid%'
755 GROUP BY c.accno, c.description, c.category
759 SELECT c.accno, sum(ac.amount) AS amount,
760 c.description, c.category
762 JOIN chart c ON (c.id = ac.chart_id)
763 JOIN gl a ON (a.id = ac.trans_id)
769 AND NOT (c.link = 'AR' OR c.link = 'AP')
771 GROUP BY c.accno, c.description, c.category
774 if ($form->{project_id})
781 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
782 c.description AS description, c.category
784 JOIN ar a ON (a.id = ac.trans_id)
785 JOIN parts p ON (ac.parts_id = p.id)
786 JOIN chart c on (p.income_accno_id = c.id)
788 -- use transdate from subwhere
789 WHERE 1 = 1 $subwhere
796 JOIN chart ON (chart_id = id)
797 WHERE link LIKE '%AR_paid%'
802 GROUP BY c.accno, c.description, c.category
806 SELECT c.accno AS accno, SUM(ac.sellprice) AS amount,
807 c.description AS description, c.category
809 JOIN ap a ON (a.id = ac.trans_id)
810 JOIN parts p ON (ac.parts_id = p.id)
811 JOIN chart c on (p.expense_accno_id = c.id)
813 WHERE 1 = 1 $subwhere
820 JOIN chart ON (chart_id = id)
821 WHERE link LIKE '%AP_paid%'
826 GROUP BY c.accno, c.description, c.category
835 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
838 AND t.department_id = $department_id
845 SELECT c.accno, sum(ac.amount) AS amount,
846 c.description, c.category
848 JOIN chart c ON (c.id = ac.chart_id)
854 GROUP BY c.accno, c.description, c.category
857 if ($form->{project_id})
864 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
865 c.description AS description, c.category
867 JOIN ar a ON (a.id = ac.trans_id)
868 JOIN parts p ON (ac.parts_id = p.id)
869 JOIN chart c on (p.income_accno_id = c.id)
871 -- use transdate from subwhere
872 WHERE 1 = 1 $subwhere
876 GROUP BY c.accno, c.description, c.category
880 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
881 c.description AS description, c.category
883 JOIN ap a ON (a.id = ac.trans_id)
884 JOIN parts p ON (ac.parts_id = p.id)
885 JOIN chart c on (p.expense_accno_id = c.id)
887 WHERE 1 = 1 $subwhere
891 GROUP BY c.accno, c.description, c.category
903 my $sth = $dbh->prepare($query);
904 $sth->execute || $form->dberror($query);
906 while ($ref = $sth->fetchrow_hashref(NAME_lc))
909 if ($ref->{category} eq 'C') {
910 $ref->{category} = 'A';
913 # get last heading account
914 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
919 $form->{$ref->{category}}{$accno}{last} += $ref->{amount};
921 $form->{$ref->{category}}{$accno}{this} += $ref->{amount};
925 $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
926 $form->{$ref->{category}}{$ref->{accno}}{description} = $ref->{description};
927 $form->{$ref->{category}}{$ref->{accno}}{charttype} = "A";
931 $form->{$ref->{category}}{$ref->{accno}}{last} += $ref->{amount};
933 $form->{$ref->{category}}{$ref->{accno}}{this} += $ref->{amount};
939 # remove accounts with zero balance
940 foreach $category (@{ $categories }) {
941 foreach $accno (keys %{ $form->{$category} }) {
942 $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $form->{decimalplaces});
943 $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $form->{decimalplaces});
945 delete $form->{$category}{$accno} if ($form->{$category}{$accno}{this} == 0 && $form->{$category}{$accno}{last} == 0);
949 $main::lxdebug->leave_sub();
954 $main::lxdebug->enter_sub();
956 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
958 my ($null, $department_id) = split /--/, $form->{department};
973 if ($form->{method} eq 'cash') {
974 $subwhere .= " AND transdate >= '$fromdate'";
975 $glwhere = " AND ac.transdate >= '$fromdate'";
977 $where .= " AND ac.transdate >= '$fromdate'";
982 $where .= " AND ac.transdate <= '$todate'";
983 $subwhere .= " AND transdate <= '$todate'";
990 JOIN department t ON (a.department_id = t.id)
993 AND t.id = $department_id
997 if ($form->{project_id})
1000 AND ac.project_id = $form->{project_id}
1005 if ($form->{method} eq 'cash')
1010 SELECT sum(ac.amount) AS amount,
1013 JOIN chart c ON (c.id = ac.chart_id)
1014 JOIN ar a ON (a.id = ac.trans_id)
1022 JOIN chart ON (chart_id = id)
1023 WHERE link LIKE '%AR_paid%'
1028 GROUP BY c.$category
1032 SELECT sum(ac.amount) AS amount,
1035 JOIN chart c ON (c.id = ac.chart_id)
1036 JOIN ap a ON (a.id = ac.trans_id)
1044 JOIN chart ON (chart_id = id)
1045 WHERE link LIKE '%AP_paid%'
1050 GROUP BY c.$category
1054 SELECT sum(ac.amount) AS amount,
1057 JOIN chart c ON (c.id = ac.chart_id)
1058 JOIN gl a ON (a.id = ac.trans_id)
1063 AND NOT (c.link = 'AR' OR c.link = 'AP')
1065 GROUP BY c.$category
1068 if ($form->{project_id})
1075 SELECT SUM(ac.sellprice * ac.qty) AS amount,
1078 JOIN ar a ON (a.id = ac.trans_id)
1079 JOIN parts p ON (ac.parts_id = p.id)
1080 JOIN chart c on (p.income_accno_id = c.id)
1082 -- use transdate from subwhere
1083 WHERE 1 = 1 $subwhere
1084 AND c.category = 'I'
1090 JOIN chart ON (chart_id = id)
1091 WHERE link LIKE '%AR_paid%'
1096 GROUP BY c.$category
1100 SELECT SUM(ac.sellprice) AS amount,
1103 JOIN ap a ON (a.id = ac.trans_id)
1104 JOIN parts p ON (ac.parts_id = p.id)
1105 JOIN chart c on (p.expense_accno_id = c.id)
1107 WHERE 1 = 1 $subwhere
1108 AND c.category = 'E'
1114 JOIN chart ON (chart_id = id)
1115 WHERE link LIKE '%AP_paid%'
1120 GROUP BY c.$category
1129 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
1132 AND t.department_id = $department_id
1139 SELECT sum(ac.amount) AS amount,
1142 JOIN chart c ON (c.id = ac.chart_id)
1147 GROUP BY c.$category
1150 if ($form->{project_id})
1157 SELECT SUM(ac.sellprice * ac.qty) AS amount,
1160 JOIN ar a ON (a.id = ac.trans_id)
1161 JOIN parts p ON (ac.parts_id = p.id)
1162 JOIN chart c on (p.income_accno_id = c.id)
1164 -- use transdate from subwhere
1165 WHERE 1 = 1 $subwhere
1166 AND c.category = 'I'
1169 GROUP BY c.$category
1173 SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
1176 JOIN ap a ON (a.id = ac.trans_id)
1177 JOIN parts p ON (ac.parts_id = p.id)
1178 JOIN chart c on (p.expense_accno_id = c.id)
1180 WHERE 1 = 1 $subwhere
1181 AND c.category = 'E'
1184 GROUP BY c.$category
1195 my $sth = $dbh->prepare($query);
1196 $sth->execute || $form->dberror($query);
1198 while ($ref = $sth->fetchrow_hashref(NAME_lc))
1200 if ($ref->{amount} < 0) {
1201 $ref->{amount} *= -1;
1203 if ($category eq "pos_bwa") {
1206 $form->{$ref->{$category}}{kumm} += $ref->{amount};
1208 $form->{$ref->{$category}}{jetzt} += $ref->{amount};
1211 $form->{$ref->{$category}} += $ref->{amount};
1216 $main::lxdebug->leave_sub();
1220 $main::lxdebug->enter_sub();
1222 my ($self, $myconfig, $form) = @_;
1224 my $dbh = $form->dbconnect($myconfig);
1226 my ($query, $sth, $ref);
1229 my ($null, $department_id) = split /--/, $form->{department};
1230 my @headingaccounts = ();
1235 my $where = "1 = 1";
1236 my $invwhere = $where;
1238 if ($department_id) {
1240 JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
1243 AND t.department_id = $department_id
1248 # project_id only applies to getting transactions
1249 # it has nothing to do with a trial balance
1250 # but we use the same function to collect information
1252 if ($form->{project_id}) {
1254 AND ac.project_id = $form->{project_id}
1258 # get beginning balances
1259 if ($form->{fromdate}) {
1261 if ($form->{accounttype} eq 'gifi') {
1263 $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
1266 JOIN chart c ON (ac.chart_id = c.id)
1267 JOIN gifi g ON (c.gifi_accno = g.accno)
1269 WHERE ac.transdate < '$form->{fromdate}'
1272 GROUP BY g.accno, c.category, g.description
1277 $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
1280 JOIN chart c ON (ac.chart_id = c.id)
1282 WHERE ac.transdate < '$form->{fromdate}'
1285 GROUP BY c.accno, c.category, c.description
1290 $sth = $dbh->prepare($query);
1291 $sth->execute || $form->dberror($query);
1293 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1294 $balance{$ref->{accno}} = $ref->{amount};
1296 if ($ref->{amount} != 0 && $form->{all_accounts}) {
1297 $trb{$ref->{accno}}{description} = $ref->{description};
1298 $trb{$ref->{accno}}{charttype} = 'A';
1299 $trb{$ref->{accno}}{category} = $ref->{category};
1309 $query = qq|SELECT c.accno, c.description, c.category
1311 WHERE c.charttype = 'H'
1314 if ($form->{accounttype} eq 'gifi')
1316 $query = qq|SELECT g.accno, g.description, c.category
1318 JOIN chart c ON (c.gifi_accno = g.accno)
1319 WHERE c.charttype = 'H'
1323 $sth = $dbh->prepare($query);
1324 $sth->execute || $form->dberror($query);
1326 while ($ref = $sth->fetchrow_hashref(NAME_lc))
1328 $trb{$ref->{accno}}{description} = $ref->{description};
1329 $trb{$ref->{accno}}{charttype} = 'H';
1330 $trb{$ref->{accno}}{category} = $ref->{category};
1332 push @headingaccounts, $ref->{accno};
1339 if ($form->{fromdate} || $form->{todate}) {
1340 if ($form->{fromdate}) {
1341 $tofrom .= " AND ac.transdate >= '$form->{fromdate}'";
1342 $subwhere .= " AND transdate >= '$form->{fromdate}'";
1343 $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
1344 $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
1346 if ($form->{todate}) {
1347 $tofrom .= " AND ac.transdate <= '$form->{todate}'";
1348 $invwhere .= " AND a.transdate <= '$form->{todate}'";
1349 $subwhere .= " AND transdate <= '$form->{todate}'";
1350 $glwhere .= " AND ac.transdate <= '$form->{todate}'";
1354 $where .= qq| AND ((ac.trans_id in (SELECT id from ar)
1359 JOIN chart ON (chart_id = id)
1360 WHERE link LIKE '%AR_paid%'
1362 )) OR (ac.trans_id in (SELECT id from ap)
1367 JOIN chart ON (chart_id = id)
1368 WHERE link LIKE '%AP_paid%'
1370 )) OR (ac.trans_id in (SELECT id from gl)
1376 if ($form->{accounttype} eq 'gifi') {
1378 $query = qq|SELECT g.accno, g.description, c.category,
1379 SUM(ac.amount) AS amount
1381 JOIN chart c ON (c.id = ac.chart_id)
1382 JOIN gifi g ON (c.gifi_accno = g.accno)
1387 GROUP BY g.accno, g.description, c.category
1390 if ($form->{project_id}) {
1394 -- add project transactions from invoice
1398 SELECT g.accno, g.description, c.category,
1399 SUM(ac.sellprice * ac.qty) AS amount
1401 JOIN ar a ON (ac.trans_id = a.id)
1402 JOIN parts p ON (ac.parts_id = p.id)
1403 JOIN chart c ON (p.income_accno_id = c.id)
1404 JOIN gifi g ON (c.gifi_accno = g.accno)
1409 GROUP BY g.accno, g.description, c.category
1413 SELECT g.accno, g.description, c.category,
1414 SUM(ac.sellprice * ac.qty) * -1 AS amount
1416 JOIN ap a ON (ac.trans_id = a.id)
1417 JOIN parts p ON (ac.parts_id = p.id)
1418 JOIN chart c ON (p.expense_accno_id = c.id)
1419 JOIN gifi g ON (c.gifi_accno = g.accno)
1424 GROUP BY g.accno, g.description, c.category
1433 $query = qq|SELECT c.accno, c.description, c.category,
1434 SUM(ac.amount) AS amount
1436 JOIN chart c ON (c.id = ac.chart_id)
1441 GROUP BY c.accno, c.description, c.category
1444 if ($form->{project_id}) {
1448 -- add project transactions from invoice
1452 SELECT c.accno, c.description, c.category,
1453 SUM(ac.sellprice * ac.qty) AS amount
1455 JOIN ar a ON (ac.trans_id = a.id)
1456 JOIN parts p ON (ac.parts_id = p.id)
1457 JOIN chart c ON (p.income_accno_id = c.id)
1462 GROUP BY c.accno, c.description, c.category
1466 SELECT c.accno, c.description, c.category,
1467 SUM(ac.sellprice * ac.qty) * -1 AS amount
1469 JOIN ap a ON (ac.trans_id = a.id)
1470 JOIN parts p ON (ac.parts_id = p.id)
1471 JOIN chart c ON (p.expense_accno_id = c.id)
1476 GROUP BY c.accno, c.description, c.category
1485 $sth = $dbh->prepare($query);
1486 $sth->execute || $form->dberror($query);
1489 # prepare query for each account
1490 $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1492 JOIN chart c ON (c.id = ac.chart_id)
1498 AND c.accno = ?) AS debit,
1500 (SELECT SUM(ac.amount)
1502 JOIN chart c ON (c.id = ac.chart_id)
1508 AND c.accno = ?) AS credit
1511 if ($form->{accounttype} eq 'gifi') {
1513 $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1515 JOIN chart c ON (c.id = ac.chart_id)
1521 AND c.gifi_accno = ?) AS debit,
1523 (SELECT SUM(ac.amount)
1525 JOIN chart c ON (c.id = ac.chart_id)
1531 AND c.gifi_accno = ?) AS credit|;
1535 $drcr = $dbh->prepare($query);
1538 if ($form->{project_id}) {
1539 # prepare query for each account
1540 $query = qq|SELECT (SELECT SUM(ac.sellprice * ac.qty) * -1
1542 JOIN parts p ON (ac.parts_id = p.id)
1543 JOIN ap a ON (ac.trans_id = a.id)
1544 JOIN chart c ON (p.expense_accno_id = c.id)
1549 AND c.accno = ?) AS debit,
1551 (SELECT SUM(ac.sellprice * ac.qty)
1553 JOIN parts p ON (ac.parts_id = p.id)
1554 JOIN ar a ON (ac.trans_id = a.id)
1555 JOIN chart c ON (p.income_accno_id = c.id)
1560 AND c.accno = ?) AS credit
1563 $project_drcr = $dbh->prepare($query);
1567 # calculate the debit and credit in the period
1568 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1569 $trb{$ref->{accno}}{description} = $ref->{description};
1570 $trb{$ref->{accno}}{charttype} = 'A';
1571 $trb{$ref->{accno}}{category} = $ref->{category};
1572 $trb{$ref->{accno}}{amount} += $ref->{amount};
1576 my ($debit, $credit);
1578 foreach my $accno (sort keys %trb) {
1581 $ref->{accno} = $accno;
1582 map { $ref->{$_} = $trb{$accno}{$_} } qw(description category charttype amount);
1584 $ref->{balance} = $form->round_amount($balance{$ref->{accno}}, 2);
1586 if ($trb{$accno}{charttype} eq 'A') {
1588 $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
1590 ($debit, $credit) = (0,0);
1591 while (($debit, $credit) = $drcr->fetchrow_array) {
1592 $ref->{debit} += $debit;
1593 $ref->{credit} += $credit;
1597 if ($form->{project_id}) {
1599 $project_drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
1601 ($debit, $credit) = (0,0);
1602 while (($debit, $credit) = $project_drcr->fetchrow_array) {
1603 $ref->{debit} += $debit;
1604 $ref->{credit} += $credit;
1606 $project_drcr->finish;
1609 $ref->{debit} = $form->round_amount($ref->{debit}, 2);
1610 $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1615 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1616 $accno = pop @accno;
1618 $trb{$accno}{debit} += $ref->{debit};
1619 $trb{$accno}{credit} += $ref->{credit};
1622 push @{ $form->{TB} }, $ref;
1628 # debits and credits for headings
1629 foreach $accno (@headingaccounts) {
1630 foreach $ref (@{ $form->{TB} }) {
1631 if ($accno eq $ref->{accno}) {
1632 $ref->{debit} = $trb{$accno}{debit};
1633 $ref->{credit} = $trb{$accno}{credit};
1638 $main::lxdebug->leave_sub();
1643 $main::lxdebug->enter_sub();
1645 my ($self, $myconfig, $form) = @_;
1647 # connect to database
1648 my $dbh = $form->dbconnect($myconfig);
1649 my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
1651 $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1653 my $where = "1 = 1";
1656 if ($form->{"$form->{ct}_id"}) {
1657 $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
1659 if ($form->{$form->{ct}}) {
1660 $name = $form->like(lc $form->{$form->{ct}});
1661 $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{$form->{ct}};
1666 if ($form->{department}) {
1667 ($null, $department_id) = split /--/, $form->{department};
1669 JOIN department d ON (a.department_id = d.id)
1672 $where .= qq| AND a.department_id = $department_id|;
1675 # select outstanding vendors or customers, depends on $ct
1676 my $query = qq|SELECT DISTINCT ct.id, ct.name
1677 FROM $form->{ct} ct, $form->{arap} a
1680 AND a.$form->{ct}_id = ct.id
1681 AND a.paid != a.amount
1682 AND (a.transdate <= '$form->{todate}')
1685 my $sth = $dbh->prepare($query);
1686 $sth->execute || $form->dberror;
1688 my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
1690 # for each company that has some stuff outstanding
1691 while ( my ($id) = $sth->fetchrow_array ) {
1695 -- between 0-30 days
1697 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1698 street, zipcode, city, country, contact, email,
1699 phone as customerphone, fax as customerfax, $form->{ct}number,
1700 "invnumber", "transdate",
1701 (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
1702 "duedate", invoice, $form->{arap}.id,
1703 (SELECT $buysell FROM exchangerate
1704 WHERE $form->{arap}.curr = exchangerate.curr
1705 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1706 FROM $form->{arap}, $form->{ct}
1707 WHERE paid != amount
1708 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1709 AND $form->{ct}.id = $id
1711 transdate <= (date '$form->{todate}' - interval '0 days')
1712 AND transdate >= (date '$form->{todate}' - interval '30 days')
1717 -- between 31-60 days
1719 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1720 street, zipcode, city, country, contact, email,
1721 phone as customerphone, fax as customerfax, $form->{ct}number,
1722 "invnumber", "transdate",
1723 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
1724 "duedate", invoice, $form->{arap}.id,
1725 (SELECT $buysell FROM exchangerate
1726 WHERE $form->{arap}.curr = exchangerate.curr
1727 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1728 FROM $form->{arap}, $form->{ct}
1729 WHERE paid != amount
1730 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1731 AND $form->{ct}.id = $id
1733 transdate < (date '$form->{todate}' - interval '30 days')
1734 AND transdate >= (date '$form->{todate}' - interval '60 days')
1739 -- between 61-90 days
1741 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1742 street, zipcode, city, country, contact, email,
1743 phone as customerphone, fax as customerfax, $form->{ct}number,
1744 "invnumber", "transdate",
1745 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1746 "duedate", invoice, $form->{arap}.id,
1747 (SELECT $buysell FROM exchangerate
1748 WHERE $form->{arap}.curr = exchangerate.curr
1749 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1750 FROM $form->{arap}, $form->{ct}
1751 WHERE paid != amount
1752 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1753 AND $form->{ct}.id = $id
1755 transdate < (date '$form->{todate}' - interval '60 days')
1756 AND transdate >= (date '$form->{todate}' - interval '90 days')
1763 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1764 street, zipcode, city, country, contact, email,
1765 phone as customerphone, fax as customerfax, $form->{ct}number,
1766 "invnumber", "transdate",
1767 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1768 "duedate", invoice, $form->{arap}.id,
1769 (SELECT $buysell FROM exchangerate
1770 WHERE $form->{arap}.curr = exchangerate.curr
1771 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1772 FROM $form->{arap}, $form->{ct}
1773 WHERE paid != amount
1774 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1775 AND $form->{ct}.id = $id
1776 AND transdate < (date '$form->{todate}' - interval '90 days')
1780 ctid, transdate, invnumber
1784 my $sth = $dbh->prepare($query);
1785 $sth->execute || $form->dberror;
1787 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1788 $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
1789 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1790 push @{ $form->{AG} }, $ref;
1801 $main::lxdebug->leave_sub();
1806 $main::lxdebug->enter_sub();
1808 my ($self, $myconfig, $form) = @_;
1810 # connect to database
1811 my $dbh = $form->dbconnect($myconfig);
1813 my $query = qq|SELECT ct.name, ct.email, ct.cc, ct.bcc
1815 WHERE ct.id = $form->{"$form->{ct}_id"}|;
1816 my $sth = $dbh->prepare($query);
1817 $sth->execute || $form->dberror;
1819 ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $sth->fetchrow_array;
1823 $main::lxdebug->leave_sub();
1827 sub get_taxaccounts {
1828 $main::lxdebug->enter_sub();
1830 my ($self, $myconfig, $form) = @_;
1832 # connect to database
1833 my $dbh = $form->dbconnect($myconfig);
1836 my $query = qq|SELECT c.accno, c.description, t.rate
1838 WHERE c.link LIKE '%CT_tax%'
1839 AND c.id = t.chart_id
1841 my $sth = $dbh->prepare($query);
1842 $sth->execute || $form->dberror;
1845 while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
1846 push @{ $form->{taxaccounts} }, $ref;
1850 # get gifi tax accounts
1851 my $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description,
1853 FROM gifi g, chart c, tax t
1854 WHERE g.accno = c.gifi_accno
1855 AND c.id = t.chart_id
1856 AND c.link LIKE '%CT_tax%'
1857 GROUP BY g.accno, g.description
1859 my $sth = $dbh->prepare($query);
1860 $sth->execute || $form->dberror;
1862 while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
1863 push @{ $form->{gifi_taxaccounts} }, $ref;
1869 $main::lxdebug->leave_sub();
1875 $main::lxdebug->enter_sub();
1877 my ($self, $myconfig, $form) = @_;
1879 # connect to database
1880 my $dbh = $form->dbconnect($myconfig);
1882 my ($null, $department_id) = split /--/, $form->{department};
1885 my $where = "1 = 1";
1887 if ($department_id) {
1889 AND a.department_id = $department_id
1895 if ($form->{accno}) {
1896 if ($form->{accno} =~ /^gifi_/) {
1897 ($null, $accno) = split /_/, $form->{accno};
1898 $rate = $form->{"$form->{accno}_rate"};
1899 $accno = qq| AND ch.gifi_accno = '$accno'|;
1901 $accno = $form->{accno};
1902 $rate = $form->{"$form->{accno}_rate"};
1903 $accno = qq| AND ch.accno = '$accno'|;
1912 if ($form->{db} eq 'ar') {
1913 $table = "customer";
1916 if ($form->{db} eq 'ap') {
1921 my $transdate = "a.transdate";
1923 if ($form->{method} eq 'cash') {
1924 $transdate = "a.datepaid";
1926 my $todate = ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig);
1933 JOIN chart ON (chart_id = id)
1934 WHERE link LIKE '%${ARAP}_paid%'
1935 AND transdate <= '$todate'
1941 # if there are any dates construct a where
1942 if ($form->{fromdate} || $form->{todate}) {
1943 if ($form->{fromdate}) {
1944 $where .= " AND $transdate >= '$form->{fromdate}'";
1946 if ($form->{todate}) {
1947 $where .= " AND $transdate <= '$form->{todate}'";
1951 my $ml = ($form->{db} eq 'ar') ? 1 : -1;
1953 my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1954 $sortorder = $form->{sort} unless $sortorder;
1956 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
1957 a.invnumber, n.name, a.netamount,
1958 ac.amount * $ml AS tax
1960 JOIN $form->{db} a ON (a.id = ac.trans_id)
1961 JOIN chart ch ON (ch.id = ac.chart_id)
1962 JOIN $table n ON (n.id = a.${table}_id)
1967 SELECT a.id, '1' AS invoice, $transdate AS transdate,
1968 a.invnumber, n.name, i.sellprice * i.qty AS netamount,
1969 i.sellprice * i.qty * $rate * $ml AS tax
1971 JOIN $form->{db} a ON (a.id = ac.trans_id)
1972 JOIN chart ch ON (ch.id = ac.chart_id)
1973 JOIN $table n ON (n.id = a.${table}_id)
1974 JOIN ${table}tax t ON (t.${table}_id = n.id)
1975 JOIN invoice i ON (i.trans_id = a.id)
1976 JOIN partstax p ON (p.parts_id = i.parts_id)
1980 ORDER by $sortorder|;
1982 if ($form->{report} =~ /nontaxable/) {
1983 # only gather up non-taxable transactions
1984 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
1985 a.invnumber, n.name, a.netamount
1987 JOIN $form->{db} a ON (a.id = ac.trans_id)
1988 JOIN $table n ON (n.id = a.${table}_id)
1991 AND a.netamount = a.amount
1993 SELECT a.id, '1' AS invoice, $transdate AS transdate,
1994 a.invnumber, n.name, i.sellprice * i.qty AS netamount
1996 JOIN $form->{db} a ON (a.id = ac.trans_id)
1997 JOIN $table n ON (n.id = a.${table}_id)
1998 JOIN invoice i ON (i.trans_id = a.id)
2002 a.${table}_id NOT IN (
2003 SELECT ${table}_id FROM ${table}tax t (${table}_id)
2006 SELECT parts_id FROM partstax p (parts_id)
2009 GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty
2010 ORDER by $sortorder|;
2013 my $sth = $dbh->prepare($query);
2014 $sth->execute || $form->dberror($query);
2016 while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2017 push @{ $form->{TR} }, $ref;
2023 $main::lxdebug->leave_sub();
2027 sub paymentaccounts {
2028 $main::lxdebug->enter_sub();
2030 my ($self, $myconfig, $form) = @_;
2032 # connect to database, turn AutoCommit off
2033 my $dbh = $form->dbconnect_noauto($myconfig);
2035 my $ARAP = uc $form->{db};
2037 # get A(R|P)_paid accounts
2038 my $query = qq|SELECT c.accno, c.description
2040 WHERE c.link LIKE '%${ARAP}_paid%'|;
2041 my $sth = $dbh->prepare($query);
2042 $sth->execute || $form->dberror($query);
2044 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2045 push @{ $form->{PR} }, $ref;
2051 $main::lxdebug->leave_sub();
2056 $main::lxdebug->enter_sub();
2058 my ($self, $myconfig, $form) = @_;
2060 # connect to database, turn AutoCommit off
2061 my $dbh = $form->dbconnect_noauto($myconfig);
2064 if ($form->{db} eq 'ar') {
2065 $table = 'customer';
2068 if ($form->{db} eq 'ap') {
2077 if ($form->{department_id}) {
2079 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
2083 AND t.department_id = $form->{department_id}
2087 if ($form->{fromdate}) {
2088 $where .= " AND ac.transdate >= '$form->{fromdate}'";
2090 if ($form->{todate}) {
2091 $where .= " AND ac.transdate <= '$form->{todate}'";
2093 if (!$form->{fx_transaction}) {
2094 $where .= " AND ac.fx_transaction = '0'";
2099 if ($form->{reference}) {
2100 $reference = $form->like(lc $form->{reference});
2101 $invnumber = " AND lower(a.invnumber) LIKE '$reference'";
2102 $reference = " AND lower(g.reference) LIKE '$reference'";
2104 if ($form->{source}) {
2105 my $source = $form->like(lc $form->{source});
2106 $where .= " AND lower(ac.source) LIKE '$source'";
2108 if ($form->{memo}) {
2109 my $memo = $form->like(lc $form->{memo});
2110 $where .= " AND lower(ac.memo) LIKE '$memo'";
2114 my $sortorder = join ', ', $form->sort_columns(qw(name invnumber ordnumber transdate source));
2117 # cycle through each id
2118 foreach my $accno (split(/ /, $form->{paymentaccounts})) {
2120 $query = qq|SELECT c.id, c.accno, c.description
2122 WHERE c.accno = '$accno'|;
2123 $sth = $dbh->prepare($query);
2124 $sth->execute || $form->dberror($query);
2126 my $ref = $sth->fetchrow_hashref(NAME_lc);
2127 push @{ $form->{PR} }, $ref;
2131 $query = qq|SELECT c.name, a.invnumber, a.ordnumber,
2132 ac.transdate, ac.amount * $ml AS paid, ac.source,
2133 a.invoice, a.id, ac.memo, '$form->{db}' AS module
2135 JOIN $form->{db} a ON (ac.trans_id = a.id)
2136 JOIN $table c ON (c.id = a.${table}_id)
2138 WHERE ac.chart_id = $ref->{id}
2143 SELECT g.description, g.reference, NULL AS ordnumber,
2144 ac.transdate, ac.amount * $ml AS paid, ac.source,
2145 '0' as invoice, g.id, ac.memo, 'gl' AS module
2147 JOIN gl g ON (g.id = ac.trans_id)
2149 WHERE ac.chart_id = $ref->{id}
2152 AND (ac.amount * $ml) > 0
2153 ORDER BY $sortorder|;
2155 $sth = $dbh->prepare($query);
2156 $sth->execute || $form->dberror($query);
2158 while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
2159 push @{ $form->{$ref->{id}} }, $pr;
2167 $main::lxdebug->leave_sub();
2171 $main::lxdebug->enter_sub();
2173 my ($self, $myconfig, $form) = @_;
2175 # connect to database
2176 my $dbh = $form->dbconnect($myconfig);
2178 my $last_period = 0;
2179 my $category = "pos_bwa";
2180 my @categories = 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);
2182 $form->{decimalplaces} *= 1;
2184 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, $category);
2186 # if there are any compare dates
2187 if ($form->{fromdate} || $form->{todate}) {
2189 if ($form->{fromdate}) {
2190 $form->{fromdate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2193 $form->{todate} =~ /[0-9]*\.[0-9]*\.([0-9]*)/;
2196 $kummfromdate = $form->{comparefromdate};
2197 $kummtodate = $form->{comparetodate};
2198 &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, $category);
2203 @periods = qw(jetzt kumm);
2204 @gesamtleistung = qw(1 2 3);
2205 @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 19 20);
2206 @ergebnisse = qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
2209 foreach $key (@periods) {
2210 $form->{"$key"."gesamtleistung"} = 0;
2211 $form->{"$key"."gesamtkosten"} = 0;
2213 foreach $category (@categories){
2215 if (defined($form->{$category}{$key})) {
2216 $form->{"$key$category"} = $form->format_amount($myconfig, $form->round_amount( $form->{$category}{$key},2));
2219 foreach $item (@gesamtleistung) {
2220 $form->{"$key"."gesamtleistung"} += $form->{$item}{$key};
2222 foreach $item (@gesamtkosten) {
2223 $form->{"$key"."gesamtkosten"} += $form->{$item}{$key};
2225 $form->{"$key"."rohertrag"} = $form->{"$key"."gesamtleistung"} - $form->{4}{$key};
2226 $form->{"$key"."betriebrohertrag"} = $form->{"$key"."rohertrag"} + $form->{5}{$key};
2227 $form->{"$key"."betriebsergebnis"} = $form->{"$key"."betriebrohertrag"} - $form->{"$key"."gesamtkosten"};
2228 $form->{"$key"."neutraleraufwand"} = $form->{30}{$key} + $form->{31}{$key};
2229 $form->{"$key"."neutralertrag"} = $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
2230 $form->{"$key"."ergebnisvorsteuern"} = $form->{"$key"."betriebsergebnis"} - ($form->{"$key"."neutraleraufwand"} + $form->{"$key"."neutralertrag"});
2231 $form->{"$key"."ergebnis"} = $form->{"$key"."ergebnisvorsteuern"} + $form->{35}{$key};
2233 if ($form->{"$key"."gesamtleistung"} > 0) {
2234 foreach $category (@categories) {
2235 if (defined($form->{$category}{$key})) {
2236 $form->{"$key"."gl"."$category"} = $form->format_amount($myconfig, $form->round_amount( ($form->{$category}{$key}/$form->{"$key"."gesamtleistung"}*100),2));
2239 foreach $item (@ergebnisse) {
2240 $form->{"$key"."gl"."$item"} = $form->format_amount($myconfig, $form->round_amount( ($form->{"$key"."$item"}/$form->{"$key"."gesamtleistung"}*100),2));
2244 if ($form->{"$key"."gesamtkosten"} > 0) {
2245 foreach $category (@categories) {
2246 if (defined($form->{$category}{$key})) {
2247 $form->{"$key"."gk"."$category"} = $form->format_amount($myconfig, $form->round_amount( ($form->{$category}{$key}/$form->{"$key"."gesamtkosten"}*100),2));
2250 foreach $item (@ergebnisse) {
2251 $form->{"$key"."gk"."$item"} = $form->format_amount($myconfig, $form->round_amount( ($form->{"$key"."$item"}/$form->{"$key"."gesamtkosten"}*100),2));
2255 if ($form->{10}{$key} > 0) {
2256 foreach $category (@categories) {
2257 if (defined($form->{$category}{$key})) {
2258 $form->{"$key"."pk"."$category"} = $form->format_amount($myconfig, $form->round_amount( ($form->{$category}{$key}/$form->{10}{$key}*100),2));
2261 foreach $item (@ergebnisse) {
2262 $form->{"$key"."pk"."$item"} = $form->format_amount($myconfig, $form->round_amount( ($form->{"$key"."$item"}/$form->{10}{$key}*100),2));
2266 if ($form->{4}{$key} > 0) {
2267 foreach $category (@categories) {
2268 if (defined($form->{$category}{$key})) {
2269 $form->{"$key"."auf"."$category"} = $form->format_amount($myconfig, $form->round_amount( ($form->{$category}{$key}/$form->{4}{$key}*100),2));
2272 foreach $item (@ergebnisse) {
2273 $form->{"$key"."auf"."$item"} = $form->format_amount($myconfig, $form->round_amount( ($form->{"$key"."$item"}/$form->{4}{$key}*100),2));
2277 foreach $item (@ergebnisse) {
2278 $form->{"$key"."$item"} = $form->format_amount($myconfig, $form->round_amount( $form->{"$key"."$item"},2));
2285 $main::lxdebug->leave_sub();
2289 $main::lxdebug->enter_sub();
2291 my ($self, $myconfig, $form) = @_;
2293 # connect to database
2294 my $dbh = $form->dbconnect($myconfig);
2296 my $last_period = 0;
2297 my $category = "pos_ustva";
2298 my @categories_cent = qw(51r 86r 97r 93r 96 66 43 45 53 62 65 67);
2299 my @categories_euro = qw(48 51 86 91 97 93 94);
2300 $form->{decimalplaces} *= 1;
2302 foreach $item (@categories_cent) {
2303 $form->{"$item"} = 0;
2305 foreach $item (@categories_euro) {
2306 $form->{"$item"} = 0;
2309 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, $category);
2311 # foreach $item (@categories_cent) {
2312 # if ($form->{$item}{"jetzt"} > 0) {
2313 # $form->{$item} = $form->{$item}{"jetzt"};
2314 # delete $form->{$item}{"jetzt"};
2317 # foreach $item (@categories_euro) {
2318 # if ($form->{$item}{"jetzt"} > 0) {
2319 # $form->{$item} = $form->{$item}{"jetzt"};
2320 # delete $form->{$item}{"jetzt"};
2321 # } foreach $item (@categories_cent) {
2322 # if ($form->{$item}{"jetzt"} > 0) {
2323 # $form->{$item} = $form->{$item}{"jetzt"};
2324 # delete $form->{$item}{"jetzt"};
2327 # foreach $item (@categories_euro) {
2328 # if ($form->{$item}{"jetzt"} > 0) {
2329 # $form->{$item} = $form->{$item}{"jetzt"};
2330 # delete $form->{$item}{"jetzt"};
2337 $form->{"51r"} = $form->{"51"} * 0.16;
2338 $form->{"86r"} = $form->{"86"} * 0.07;
2339 $form->{"97r"} = $form->{"97"} * 0.16;
2340 $form->{"93r"} = $form->{"93"} * 0.07;
2341 $form->{"96"} = $form->{"94"} * 0.16;
2342 $form->{"43"} = $form->{"51r"} + $form->{"86r"} + $form->{"97r"} + $form->{"93r"} + $form->{"96"};
2343 $form->{"45"} = $form->{"43"};
2344 $form->{"53"} = $form->{"43"};
2345 $form->{"62"} = $form->{"43"} - $form->{"66"};
2346 $form->{"65"} = $form->{"43"} - $form->{"66"};
2347 $form->{"67"} = $form->{"43"} - $form->{"66"};
2350 foreach $item (@categories_cent) {
2351 $form->{$item} = $form->format_amount($myconfig, $form->round_amount( $form->{$item},2));
2354 foreach $item (@categories_euro) {
2355 $form->{$item} = $form->format_amount($myconfig, $form->round_amount( $form->{$item},0));
2360 $main::lxdebug->leave_sub();
2363 sub income_statement {
2364 $main::lxdebug->enter_sub();
2366 my ($self, $myconfig, $form) = @_;
2368 # connect to database
2369 my $dbh = $form->dbconnect($myconfig);
2371 my $last_period = 0;
2372 my $category = "pos_eur";
2373 my @categories_einnahmen = qw(1 2 3 4 5 6 7);
2374 my @categories_ausgaben = 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);
2376 my @ergebnisse = qw(sumeura sumeurb guvsumme);
2378 $form->{decimalplaces} *= 1;
2380 foreach $item (@categories_einnahmen) {
2383 foreach $item (@categories_ausgaben) {
2387 foreach $item (@ergebnisse) {
2391 &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, $category);
2393 foreach $item (@categories_einnahmen) {
2394 $form->{"eur${item}"} = $form->format_amount($myconfig, $form->round_amount( $form->{$item},2));
2395 $form->{"sumeura"} += $form->{$item};
2397 foreach $item (@categories_ausgaben) {
2398 $form->{"eur${item}"} = $form->format_amount($myconfig, $form->round_amount( $form->{$item},2));
2399 $form->{"sumeurb"} += $form->{$item};
2402 $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
2404 foreach $item (@ergebnisse) {
2405 $form->{$item} = $form->format_amount($myconfig, $form->round_amount( $form->{$item},2));
2407 $main::lxdebug->leave_sub();