1 #=====================================================================
2 # SQL-Ledger Accounting
3 # Copyright (C) 1998-2002
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
9 # Contributors: Benjamin Lee <benjaminlee@consultant.com>
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
25 # backend code for reports
27 #======================================================================
32 sub income_statement {
33 my ($self, $myconfig, $form) = @_;
36 my $dbh = $form->dbconnect($myconfig);
39 my @categories = qw(I E);
42 $form->{decimalplaces} *= 1;
44 &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories);
46 # if there are any compare dates
47 if ($form->{comparefromdate} || $form->{comparetodate}) {
50 &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories);
58 # now we got $form->{I}{accno}{ }
59 # and $form->{E}{accno}{ }
61 my %account = ( 'I' => { 'label' => 'income',
64 'E' => { 'label' => 'expense',
65 'labels' => 'expenses',
71 foreach $category (@categories) {
73 foreach $key (sort keys %{ $form->{$category} }) {
74 # push description onto array
76 $str = ($form->{l_heading}) ? $form->{padding} : "";
78 if ($form->{$category}{$key}{charttype} eq "A") {
79 $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
81 if ($form->{$category}{$key}{charttype} eq "H") {
82 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
84 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
85 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
88 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
93 $str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
95 $account{$category}{subthis} = $form->{$category}{$key}{this};
96 $account{$category}{sublast} = $form->{$category}{$key}{last};
97 $account{$category}{subdescription} = $form->{$category}{$key}{description};
98 $account{$category}{subtotal} = 1;
100 $form->{$category}{$key}{this} = 0;
101 $form->{$category}{$key}{last} = 0;
103 next unless $form->{l_heading};
108 push(@{$form->{"$account{$category}{label}_account"}}, $str);
110 if ($form->{$category}{$key}{charttype} eq 'A') {
111 $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
115 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
117 # add amount or - for last period
119 $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
121 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
125 $str = ($form->{l_heading}) ? $form->{padding} : "";
126 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
127 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
128 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
131 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
138 # totals for income and expenses
139 $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
140 $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
142 # total for income/loss
143 $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
146 # total for income/loss
147 $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
149 # totals for income and expenses for last_period
150 $form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- ");
151 $form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
156 $form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- ");
157 $form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- ");
158 $form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- ");
165 my ($self, $myconfig, $form) = @_;
167 # connect to database
168 my $dbh = $form->dbconnect($myconfig);
171 my @categories = qw(A L Q);
173 # if there are any dates construct a where
174 if ($form->{asofdate}) {
176 $form->{this_period} = "$form->{asofdate}";
177 $form->{period} = "$form->{asofdate}";
181 $form->{decimalplaces} *= 1;
183 &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories);
185 # if there are any compare dates
186 if ($form->{compareasofdate}) {
189 &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories);
191 $form->{last_period} = "$form->{compareasofdate}";
200 # now we got $form->{A}{accno}{ } assets
201 # and $form->{L}{accno}{ } liabilities
202 # and $form->{Q}{accno}{ } equity
203 # build asset accounts
208 my %account = ( 'A' => { 'label' => 'asset',
209 'labels' => 'assets',
211 'L' => { 'label' => 'liability',
212 'labels' => 'liabilities',
214 'Q' => { 'label' => 'equity',
215 'labels' => 'equities',
219 foreach $category (@categories) {
221 foreach $key (sort keys %{ $form->{$category} }) {
223 $str = ($form->{l_heading}) ? $form->{padding} : "";
225 if ($form->{$category}{$key}{charttype} eq "A") {
226 $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
228 if ($form->{$category}{$key}{charttype} eq "H") {
229 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
231 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
232 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
235 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
239 $str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
241 $account{$category}{subthis} = $form->{$category}{$key}{this};
242 $account{$category}{sublast} = $form->{$category}{$key}{last};
243 $account{$category}{subdescription} = $form->{$category}{$key}{description};
244 $account{$category}{subtotal} = 1;
246 $form->{$category}{$key}{this} = 0;
247 $form->{$category}{$key}{last} = 0;
249 next unless $form->{l_heading};
254 # push description onto array
255 push(@{$form->{"$account{$category}{label}_account"}}, $str);
257 if ($form->{$category}{$key}{charttype} eq 'A') {
258 $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
262 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
265 $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
267 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
271 $str = ($form->{l_heading}) ? $form->{padding} : "";
272 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
273 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
274 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
277 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
284 # totals for assets, liabilities
285 $form->{total_assets_this_period} = $form->round_amount($form->{total_assets_this_period}, $form->{decimalplaces});
286 $form->{total_liabilities_this_period} = $form->round_amount($form->{total_liabilities_this_period}, $form->{decimalplaces});
289 # calculate retained earnings
290 $form->{earnings_this_period} = $form->{total_assets_this_period} - $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
292 push(@{$form->{equity_this_period}}, $form->format_amount($myconfig, $form->{earnings_this_period}, $form->{decimalplaces}, "- "));
294 $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period} + $form->{earnings_this_period}, $form->{decimalplaces});
296 # add liability + equity
297 $form->{total_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period} + $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
301 # totals for assets, liabilities
302 $form->{total_assets_last_period} = $form->round_amount($form->{total_assets_last_period}, $form->{decimalplaces});
303 $form->{total_liabilities_last_period} = $form->round_amount($form->{total_liabilities_last_period}, $form->{decimalplaces});
306 # calculate retained earnings
307 $form->{earnings_last_period} = $form->{total_assets_last_period} - $form->{total_liabilities_last_period} - $form->{total_equity_last_period};
309 push(@{$form->{equity_last_period}}, $form->format_amount($myconfig,$form->{earnings_last_period}, $form->{decimalplaces}, "- "));
311 $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period} + $form->{earnings_last_period}, $form->{decimalplaces});
313 # add liability + equity
314 $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period} + $form->{total_equity_last_period}, $form->{decimalplaces}, "- ");
319 $form->{total_liabilities_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_liabilities_last_period} != 0);
321 $form->{total_equity_last_period} = $form->format_amount($myconfig, $form->{total_equity_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_equity_last_period} != 0);
323 $form->{total_assets_last_period} = $form->format_amount($myconfig, $form->{total_assets_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_assets_last_period} != 0);
325 $form->{total_assets_this_period} = $form->format_amount($myconfig, $form->{total_assets_this_period}, $form->{decimalplaces}, "- ");
327 $form->{total_liabilities_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period}, $form->{decimalplaces}, "- ");
329 $form->{total_equity_this_period} = $form->format_amount($myconfig, $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
336 my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
339 my $where = "WHERE 1 = 1";
343 my $category = "AND (";
344 foreach $item (@{ $categories }) {
345 $category .= qq|c.category = '$item' OR |;
347 $category =~ s/OR $/\)/;
351 $query = qq|SELECT accno, description, category
353 WHERE c.charttype = 'H'
357 if ($form->{accounttype} eq 'gifi')
359 $query = qq|SELECT g.accno, g.description, c.category
361 JOIN chart c ON (c.gifi_accno = g.accno)
362 WHERE c.charttype = 'H'
367 $sth = $dbh->prepare($query);
368 $sth->execute || $form->dberror($query);
370 my @headingaccounts = ();
371 while ($ref = $sth->fetchrow_hashref(NAME_lc))
373 $form->{$ref->{category}}{$ref->{accno}}{description} = "$ref->{description}";
374 $form->{$ref->{category}}{$ref->{accno}}{charttype} = "H";
375 $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
377 push @headingaccounts, $ref->{accno};
383 $where .= " AND ac.transdate >= '$fromdate'" if $fromdate;
386 $where .= " AND ac.transdate <= '$todate'";
387 $subwhere = " AND transdate <= '$todate'";
391 if ($form->{project_id})
394 AND ac.project_id = $form->{project_id}
399 if ($form->{accounttype} eq 'gifi')
402 if ($form->{method} eq 'cash')
407 SELECT g.accno, sum(ac.amount) AS amount,
408 g.description, c.category
410 JOIN chart c ON (c.id = ac.chart_id)
411 JOIN ar a ON (a.id = ac.trans_id)
412 JOIN gifi g ON (g.accno = c.gifi_accno)
419 JOIN chart ON (chart_id = id)
420 WHERE link LIKE '%AR_paid%'
424 GROUP BY g.accno, g.description, c.category
428 SELECT '' AS accno, SUM(ac.amount) AS amount,
429 '' AS description, c.category
431 JOIN chart c ON (c.id = ac.chart_id)
432 JOIN ar a ON (a.id = ac.trans_id)
435 AND c.gifi_accno = ''
440 JOIN chart ON (chart_id = id)
441 WHERE link LIKE '%AR_paid%'
449 SELECT g.accno, sum(ac.amount) AS amount,
450 g.description, c.category
452 JOIN chart c ON (c.id = ac.chart_id)
453 JOIN ap a ON (a.id = ac.trans_id)
454 JOIN gifi g ON (g.accno = c.gifi_accno)
461 JOIN chart ON (chart_id = id)
462 WHERE link LIKE '%AP_paid%'
466 GROUP BY g.accno, g.description, c.category
470 SELECT '' AS accno, SUM(ac.amount) AS amount,
471 '' AS description, c.category
473 JOIN chart c ON (c.id = ac.chart_id)
474 JOIN ap a ON (a.id = ac.trans_id)
477 AND c.gifi_accno = ''
482 JOIN chart ON (chart_id = id)
483 WHERE link LIKE '%AP_paid%'
493 SELECT g.accno, sum(ac.amount) AS amount,
494 g.description, c.category
496 JOIN chart c ON (c.id = ac.chart_id)
497 JOIN gifi g ON (g.accno = c.gifi_accno)
498 JOIN gl a ON (a.id = ac.trans_id)
501 AND NOT (c.link = 'AR' OR c.link = 'AP')
503 GROUP BY g.accno, g.description, c.category
507 SELECT '' AS accno, SUM(ac.amount) AS amount,
508 '' AS description, c.category
510 JOIN chart c ON (c.id = ac.chart_id)
511 JOIN gl a ON (a.id = ac.trans_id)
514 AND c.gifi_accno = ''
515 AND NOT (c.link = 'AR' OR c.link = 'AP')
524 SELECT g.accno, SUM(ac.amount) AS amount,
525 g.description, c.category
527 JOIN chart c ON (c.id = ac.chart_id)
528 JOIN gifi g ON (c.gifi_accno = g.accno)
532 GROUP BY g.accno, g.description, c.category
536 SELECT '' AS accno, SUM(ac.amount) AS amount,
537 '' AS description, c.category
539 JOIN chart c ON (c.id = ac.chart_id)
542 AND c.gifi_accno = ''
551 if ($form->{method} eq 'cash')
557 SELECT c.accno, sum(ac.amount) AS amount,
558 c.description, c.category
560 JOIN chart c ON (c.id = ac.chart_id)
561 JOIN ar a ON (a.id = ac.trans_id)
568 JOIN chart ON (chart_id = id)
569 WHERE link LIKE '%AR_paid%'
574 GROUP BY c.accno, c.description, c.category
578 SELECT c.accno, sum(ac.amount) AS amount,
579 c.description, c.category
581 JOIN chart c ON (c.id = ac.chart_id)
582 JOIN ap a ON (a.id = ac.trans_id)
589 JOIN chart ON (chart_id = id)
590 WHERE link LIKE '%AP_paid%'
595 GROUP BY c.accno, c.description, c.category
599 SELECT c.accno, sum(ac.amount) AS amount,
600 c.description, c.category
602 JOIN chart c ON (c.id = ac.chart_id)
603 JOIN gl a ON (a.id = ac.trans_id)
606 AND NOT (c.link = 'AR' OR c.link = 'AP')
608 GROUP BY c.accno, c.description, c.category
615 SELECT c.accno, sum(ac.amount) AS amount,
616 c.description, c.category
618 JOIN chart c ON (c.id = ac.chart_id)
622 GROUP BY c.accno, c.description, c.category
634 my $sth = $dbh->prepare($query);
635 $sth->execute || $form->dberror($query);
637 while ($ref = $sth->fetchrow_hashref(NAME_lc))
640 if ($ref->{category} eq 'C') {
641 $ref->{category} = 'A';
644 # get last heading account
645 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
650 $form->{$ref->{category}}{$accno}{last} += $ref->{amount};
652 $form->{$ref->{category}}{$accno}{this} += $ref->{amount};
656 $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
657 $form->{$ref->{category}}{$ref->{accno}}{description} = $ref->{description};
658 $form->{$ref->{category}}{$ref->{accno}}{charttype} = "A";
662 $form->{$ref->{category}}{$ref->{accno}}{last} += $ref->{amount};
664 $form->{$ref->{category}}{$ref->{accno}}{this} += $ref->{amount};
670 # remove accounts with zero balance
671 foreach $category (@{ $categories }) {
672 foreach $accno (keys %{ $form->{$category} }) {
673 $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $form->{decimalplaces});
674 $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $form->{decimalplaces});
676 delete $form->{$category}{$accno} if ($form->{$category}{$accno}{this} == 0 && $form->{$category}{$accno}{last} == 0);
684 sub trial_balance_details {
685 my ($self, $myconfig, $form) = @_;
687 my $dbh = $form->dbconnect($myconfig);
689 my ($query, $sth, $ref);
693 my $where = "WHERE 1 = 1";
695 if ($form->{project_id}) {
697 AND a.project_id = $form->{project_id}
701 # get beginning balances
702 if ($form->{fromdate}) {
704 if ($form->{accounttype} eq 'gifi') {
706 $query = qq|SELECT g.accno, c.category, SUM(a.amount) AS amount,
709 JOIN chart c ON (a.chart_id = c.id)
710 JOIN gifi g ON (c.gifi_accno = g.accno)
712 AND a.transdate < '$form->{fromdate}'
713 GROUP BY g.accno, c.category, g.description
718 $query = qq|SELECT c.accno, c.category, SUM(a.amount) AS amount,
721 JOIN chart c ON (a.chart_id = c.id)
723 AND a.transdate < '$form->{fromdate}'
724 GROUP BY c.accno, c.category, c.description
728 $sth = $dbh->prepare($query);
729 $sth->execute || $form->dberror($query);
731 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
732 $balance{$ref->{accno}} = $ref->{amount};
734 if ($ref->{amount} != 0 && $form->{all_accounts}) {
735 $trb{$ref->{accno}}{description} = $ref->{description};
736 $trb{$ref->{accno}}{charttype} = 'A';
737 $trb{$ref->{accno}}{category} = $ref->{category};
747 $query = qq|SELECT c.accno, c.description, c.category
749 WHERE c.charttype = 'H'
752 if ($form->{accounttype} eq 'gifi')
754 $query = qq|SELECT g.accno, g.description, c.category
756 JOIN chart c ON (c.gifi_accno = g.accno)
757 WHERE c.charttype = 'H'
761 $sth = $dbh->prepare($query);
762 $sth->execute || $form->dberror($query);
764 my @headingaccounts = ();
765 while ($ref = $sth->fetchrow_hashref(NAME_lc))
767 $trb{$ref->{accno}}{description} = $ref->{description};
768 $trb{$ref->{accno}}{charttype} = 'H';
769 $trb{$ref->{accno}}{category} = $ref->{category};
771 push @headingaccounts, $ref->{accno};
777 if ($form->{fromdate} || $form->{todate}) {
778 if ($form->{fromdate}) {
779 $where .= " AND a.transdate >= '$form->{fromdate}'";
781 if ($form->{todate}) {
782 $where .= " AND a.transdate <= '$form->{todate}'";
787 if ($form->{accounttype} eq 'gifi') {
789 $query = qq|SELECT g.accno, g.description, c.category,
790 SUM(a.amount) AS amount
792 JOIN chart c ON (c.id = a.chart_id)
793 JOIN gifi g ON (c.gifi_accno = g.accno)
795 GROUP BY g.accno, g.description, c.category
799 SELECT '' AS accno, '' AS description, c.category,
800 SUM(a.amount) AS amount
802 JOIN chart c ON (c.id = a.chart_id)
804 AND c.gifi_accno = ''
810 $query = qq|SELECT c.accno, c.description, c.category,
811 SUM(a.amount) AS amount
813 JOIN chart c ON (c.id = a.chart_id)
815 GROUP BY c.accno, c.description, c.category
820 $sth = $dbh->prepare($query);
821 $sth->execute || $form->dberror($query);
824 # prepare query for each account
826 $query = qq|SELECT (SELECT SUM(a.amount) * -1
828 JOIN chart c ON (c.id = a.chart_id)
831 AND c.accno = ?) AS debit,
832 (SELECT SUM(a.amount)
834 JOIN chart c ON (c.id = a.chart_id)
837 AND c.accno = ?) AS credit
840 if ($form->{accounttype} eq 'gifi') {
842 $query = qq|SELECT (SELECT SUM(a.amount) * -1
844 JOIN chart c ON (c.id = a.chart_id)
847 AND c.gifi_accno = ?) AS debit,
848 (SELECT SUM(a.amount)
850 JOIN chart c ON (c.id = a.chart_id)
853 AND c.gifi_accno = ?) AS credit|;
857 $drcr = $dbh->prepare($query);
859 # calculate the debit and credit in the period
860 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
861 $trb{$ref->{accno}}{description} = $ref->{description};
862 $trb{$ref->{accno}}{charttype} = 'A';
863 $trb{$ref->{accno}}{category} = $ref->{category};
864 $trb{$ref->{accno}}{amount} += $ref->{amount};
869 my ($debit, $credit);
871 foreach my $accno (sort keys %trb) {
874 $ref->{accno} = $accno;
875 map { $ref->{$_} = $trb{$accno}{$_} } qw(description category charttype amount);
877 $ref->{balance} = $form->round_amount($balance{$ref->{accno}}, 2);
879 if ($trb{$accno}{charttype} eq 'A') {
881 $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
883 ($debit, $credit) = (0,0);
884 while (($debit, $credit) = $drcr->fetchrow_array) {
885 $ref->{debit} += $debit;
886 $ref->{credit} += $credit;
890 $ref->{debit} = $form->round_amount($ref->{debit}, 2);
891 $ref->{credit} = $form->round_amount($ref->{credit}, 2);
897 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
900 $trb{$accno}{debit} += $ref->{debit};
901 $trb{$accno}{credit} += $ref->{credit};
904 push @{ $form->{TB} }, $ref;
910 # debits and credits for headings
911 foreach $accno (@headingaccounts) {
912 foreach $ref (@{ $form->{TB} }) {
913 if ($accno eq $ref->{accno}) {
914 $ref->{debit} = $trb{$accno}{debit};
915 $ref->{credit} = $trb{$accno}{credit};
925 my ($self, $myconfig, $form) = @_;
927 # connect to database
928 my $dbh = $form->dbconnect($myconfig);
929 my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
931 $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
936 if ($form->{"$form->{ct}_id"}) {
937 $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
939 if ($form->{$form->{ct}}) {
940 $name = $form->like(lc $form->{$form->{ct}});
941 $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{$form->{ct}};
945 # select outstanding vendors or customers, depends on $ct
946 my $query = qq|SELECT DISTINCT ct.id, ct.name
947 FROM $form->{ct} ct, $form->{arap} a
949 AND a.$form->{ct}_id = ct.id
950 AND a.paid != a.amount
951 AND (a.transdate <= '$form->{todate}')
954 my $sth = $dbh->prepare($query);
955 $sth->execute || $form->dberror;
957 my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
959 # for each company that has some stuff outstanding
960 while ( my ($id) = $sth->fetchrow_array ) {
966 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
967 addr1, addr2, addr3, addr4, contact,
968 phone as customerphone, fax as customerfax, $form->{ct}number,
969 "invnumber", "transdate",
970 (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
971 "duedate", invoice, $form->{arap}.id,
972 (SELECT $buysell FROM exchangerate
973 WHERE $form->{arap}.curr = exchangerate.curr
974 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
975 FROM $form->{arap}, $form->{ct}
977 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
978 AND $form->{ct}.id = $id
980 transdate <= (date '$form->{todate}' - interval '0 days')
981 AND transdate >= (date '$form->{todate}' - interval '30 days')
986 -- between 31-60 days
988 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
989 addr1, addr2, addr3, addr4, contact,
990 phone as customerphone, fax as customerfax, $form->{ct}number,
991 "invnumber", "transdate",
992 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
993 "duedate", invoice, $form->{arap}.id,
994 (SELECT $buysell FROM exchangerate
995 WHERE $form->{arap}.curr = exchangerate.curr
996 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
997 FROM $form->{arap}, $form->{ct}
999 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1000 AND $form->{ct}.id = $id
1002 transdate < (date '$form->{todate}' - interval '30 days')
1003 AND transdate >= (date '$form->{todate}' - interval '60 days')
1008 -- between 61-90 days
1010 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1011 addr1, addr2, addr3, addr4, contact,
1012 phone as customerphone, fax as customerfax, $form->{ct}number,
1013 "invnumber", "transdate",
1014 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
1015 "duedate", invoice, $form->{arap}.id,
1016 (SELECT $buysell FROM exchangerate
1017 WHERE $form->{arap}.curr = exchangerate.curr
1018 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1019 FROM $form->{arap}, $form->{ct}
1020 WHERE paid != amount
1021 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1022 AND $form->{ct}.id = $id
1024 transdate < (date '$form->{todate}' - interval '60 days')
1025 AND transdate >= (date '$form->{todate}' - interval '90 days')
1032 SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
1033 addr1, addr2, addr3, addr4, contact,
1034 phone as customerphone, fax as customerfax, $form->{ct}number,
1035 "invnumber", "transdate",
1036 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
1037 "duedate", invoice, $form->{arap}.id,
1038 (SELECT $buysell FROM exchangerate
1039 WHERE $form->{arap}.curr = exchangerate.curr
1040 AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
1041 FROM $form->{arap}, $form->{ct}
1042 WHERE paid != amount
1043 AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
1044 AND $form->{ct}.id = $id
1045 AND transdate < (date '$form->{todate}' - interval '90 days')
1049 ctid, invnumber, transdate
1053 my $sth = $dbh->prepare($query);
1054 $sth->execute || $form->dberror;
1056 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1057 $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
1058 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1059 push @{ $form->{AG} }, $ref;
1074 my ($self, $myconfig, $form) = @_;
1076 # connect to database
1077 my $dbh = $form->dbconnect($myconfig);
1079 my $query = qq|SELECT name, email, cc, bcc
1081 WHERE ct.id = $form->{"$form->{ct}_id"}|;
1082 my $sth = $dbh->prepare($query);
1083 $sth->execute || $form->dberror;
1085 ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $sth->fetchrow_array;
1092 sub get_taxaccounts {
1093 my ($self, $myconfig, $form) = @_;
1095 # connect to database
1096 my $dbh = $form->dbconnect($myconfig);
1099 my $query = qq|SELECT accno, description
1101 WHERE link LIKE '%CT_tax%'
1103 my $sth = $dbh->prepare($query);
1104 $sth->execute || $form->dberror;
1106 while ( my ($accno, $description) = $sth->fetchrow_array ) {
1107 push @{ $form->{taxaccounts} }, "$accno--$description";
1111 # get gifi tax accounts
1112 my $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description
1113 FROM gifi g, chart c
1114 WHERE g.accno = c.gifi_accno
1115 AND c.link LIKE '%CT_tax%'
1117 my $sth = $dbh->prepare($query);
1118 $sth->execute || $form->dberror;
1120 while ( my ($accno, $description) = $sth->fetchrow_array ) {
1121 push @{ $form->{gifi_taxaccounts} }, "$accno--$description";
1132 my ($self, $myconfig, $form) = @_;
1134 # connect to database
1135 my $dbh = $form->dbconnect($myconfig);
1138 my $where = qq|WHERE ac.trans_id = a.id
1139 AND ac.chart_id = ch.id|;
1142 if ($form->{accno} =~ /^gifi_/) {
1143 my ($null, $accno) = split /_/, $form->{accno};
1144 $where .= qq| AND ch.gifi_accno = '$accno'|;
1146 $where .= qq| AND ch.accno = '$form->{accno}'|;
1151 if ($form->{db} eq 'ar') {
1152 $where .= " AND n.id = a.customer_id";
1153 $table = "customer";
1155 if ($form->{db} eq 'ap') {
1156 $where .= " AND n.id = a.vendor_id";
1160 my $transdate = ($form->{cashbased}) ? "a.datepaid" : "ac.transdate";
1161 if ($form->{cashbased}) {
1162 $where .= " AND a.amount = a.paid";
1165 # if there are any dates construct a where
1166 if ($form->{fromdate} || $form->{todate}) {
1167 if ($form->{fromdate}) {
1168 $where .= " AND $transdate >= '$form->{fromdate}'";
1170 if ($form->{todate}) {
1171 $where .= " AND $transdate <= '$form->{todate}'";
1175 my $query = qq|SELECT a.id, a.invoice, $transdate AS transdate, a.invnumber,
1176 n.name, a.netamount,|;
1177 my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
1178 $sortorder = $form->{sort} unless $sortorder;
1180 if ($form->{db} eq 'ar') {
1181 $query .= " ac.amount AS tax";
1183 if ($form->{db} eq 'ap') {
1184 $query .= " ac.amount * -1 AS tax";
1188 FROM acc_trans ac, "$form->{db}" a, "$table" n, chart ch
1190 ORDER by $sortorder|;
1192 my $sth = $dbh->prepare($query);
1193 $sth->execute || $form->dberror($query);
1195 while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1196 push @{ $form->{TR} }, $ref;
1205 sub paymentaccounts {
1206 my ($self, $myconfig, $form) = @_;
1208 # connect to database, turn AutoCommit off
1209 my $dbh = $form->dbconnect_noauto($myconfig);
1211 my $arap = uc $form->{db};
1214 # get A(R|P)_paid accounts
1215 my $query = qq|SELECT accno, description
1217 WHERE link LIKE '%$arap%'|;
1218 my $sth = $dbh->prepare($query);
1219 $sth->execute || $form->dberror($query);
1221 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1222 push @{ $form->{PR} }, $ref;
1232 my ($self, $myconfig, $form) = @_;
1234 # connect to database, turn AutoCommit off
1235 my $dbh = $form->dbconnect_noauto($myconfig);
1238 if ($form->{db} eq 'ar') {
1239 $table = 'customer';
1242 if ($form->{db} eq 'ap') {
1248 # cycle through each id
1249 foreach my $accno (split(/ /, $form->{paymentaccounts})) {
1251 $query = qq|SELECT id, accno, description
1253 WHERE accno = '$accno'|;
1254 $sth = $dbh->prepare($query);
1255 $sth->execute || $form->dberror($query);
1257 my $ref = $sth->fetchrow_hashref(NAME_lc);
1258 push @{ $form->{PR} }, $ref;
1261 $query = qq|SELECT c.name, a.invnumber, a.ordnumber,
1263 ac.amount * $ml AS paid, ac.source, a.invoice, a.id,
1264 '$form->{db}' AS module
1265 FROM $table c, acc_trans ac, $form->{db} a
1266 WHERE c.id = a.${table}_id
1267 AND ac.trans_id = a.id
1268 AND ac.chart_id = $ref->{id}|;
1270 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
1271 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
1275 SELECT g.description, g.reference, NULL AS ordnumber,
1277 ac.amount * $ml AS paid, ac.source, '0' as invoice, g.id,
1279 FROM gl g, acc_trans ac
1280 WHERE g.id = ac.trans_id
1281 AND ac.chart_id = $ref->{id}
1282 AND (ac.amount * $ml) > 0
1285 $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
1286 $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
1289 my $sortorder = join ', ', $form->sort_columns(qw(name invnumber ordnumber transdate source));
1291 $query .= " ORDER BY $sortorder";
1293 $sth = $dbh->prepare($query);
1294 $sth->execute || $form->dberror($query);
1296 while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
1297 push @{ $form->{$ref->{id}} }, $pr;