1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
9 # Contributors: Benjamin Lee <benjaminlee@consultant.com>
10 # Jim Rawlings <jim@your-dba.com>
12 # This program is free software; you can redistribute it and/or modify
13 # it under the terms of the GNU General Public License as published by
14 # the Free Software Foundation; either version 2 of the License, or
15 # (at your option) any later version.
17 # This program is distributed in the hope that it will be useful,
18 # but WITHOUT ANY WARRANTY; without even the implied warranty of
19 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 # GNU General Public License for more details.
21 # You should have received a copy of the GNU General Public License
22 # along with this program; if not, write to the Free Software
23 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
24 #======================================================================
26 # backend code for reports
28 #======================================================================
33 sub yearend_statement {
34 my ($self, $myconfig, $form) = @_;
37 my $dbh = $form->dbconnect($myconfig);
39 # if todate < existing yearends, delete GL and yearends
40 my $query = qq|SELECT trans_id FROM yearend
41 WHERE transdate >= '$form->{todate}'|;
42 my $sth = $dbh->prepare($query);
43 $sth->execute || $form->dberror($query);
47 while (($id) = $sth->fetchrow_array) {
52 $query = qq|DELETE FROM gl
54 $sth = $dbh->prepare($query) || $form->dberror($query);
56 $query = qq|DELETE FROM acc_trans
58 my $ath = $dbh->prepare($query) || $form->dberror($query);
60 foreach $id (@trans_id) {
68 my @categories = qw(I E);
71 $form->{decimalplaces} *= 1;
73 &get_accounts($dbh, 0, $form->{fromdate}, $form->{todate}, $form, \@categories);
79 # now we got $form->{I}{accno}{ }
80 # and $form->{E}{accno}{ }
82 my %account = ( 'I' => { 'label' => 'income',
85 'E' => { 'label' => 'expense',
86 'labels' => 'expenses',
90 foreach $category (@categories) {
91 foreach $key (sort keys %{ $form->{$category} }) {
92 if ($form->{$category}{$key}{charttype} eq 'A') {
93 $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
99 # totals for income and expenses
100 $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
101 $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
103 # total for income/loss
104 $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
109 sub income_statement {
110 my ($self, $myconfig, $form) = @_;
112 # connect to database
113 my $dbh = $form->dbconnect($myconfig);
116 my @categories = qw(I E);
119 $form->{decimalplaces} *= 1;
121 if (! ($form->{fromdate} || $form->{todate})) {
122 if ($form->{fromyear} && $form->{frommonth}) {
123 ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{fromyear}, $form->{frommonth}, $form->{interval});
127 &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories, 1);
129 if (! ($form->{comparefromdate} || $form->{comparetodate})) {
130 if ($form->{compareyear} && $form->{comparemonth}) {
131 ($form->{comparefromdate}, $form->{comparetodate}) = $form->from_to($form->{compareyear}, $form->{comparemonth}, $form->{interval});
135 # if there are any compare dates
136 if ($form->{comparefromdate} || $form->{comparetodate}) {
139 &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories, 1);
147 # now we got $form->{I}{accno}{ }
148 # and $form->{E}{accno}{ }
150 my %account = ( 'I' => { 'label' => 'income',
151 'labels' => 'income',
153 'E' => { 'label' => 'expense',
154 'labels' => 'expenses',
160 foreach $category (@categories) {
162 foreach $key (sort keys %{ $form->{$category} }) {
163 # push description onto array
165 $str = ($form->{l_heading}) ? $form->{padding} : "";
167 if ($form->{$category}{$key}{charttype} eq "A") {
168 $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
170 if ($form->{$category}{$key}{charttype} eq "H") {
171 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
173 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
174 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
177 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
182 $str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
184 $account{$category}{subthis} = $form->{$category}{$key}{this};
185 $account{$category}{sublast} = $form->{$category}{$key}{last};
186 $account{$category}{subdescription} = $form->{$category}{$key}{description};
187 $account{$category}{subtotal} = 1;
189 $form->{$category}{$key}{this} = 0;
190 $form->{$category}{$key}{last} = 0;
192 next unless $form->{l_heading};
197 push(@{$form->{"$account{$category}{label}_account"}}, $str);
199 if ($form->{$category}{$key}{charttype} eq 'A') {
200 $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
204 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
206 # add amount or - for last period
208 $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
210 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
214 $str = ($form->{l_heading}) ? $form->{padding} : "";
215 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
216 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
217 push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
220 push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
227 # totals for income and expenses
228 $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
229 $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
231 # total for income/loss
232 $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
235 # total for income/loss
236 $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
238 # totals for income and expenses for last_period
239 $form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- ");
240 $form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
245 $form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- ");
246 $form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- ");
247 $form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- ");
253 my ($self, $myconfig, $form) = @_;
255 # connect to database
256 my $dbh = $form->dbconnect($myconfig);
259 my @categories = qw(A C L Q);
263 if (! $form->{asofdate}) {
264 if ($form->{asofyear} && $form->{asofmonth}) {
265 ($null, $form->{asofdate}) = $form->from_to($form->{asofyear}, $form->{asofmonth});
269 # if there are any dates construct a where
270 if ($form->{asofdate}) {
272 $form->{this_period} = "$form->{asofdate}";
273 $form->{period} = "$form->{asofdate}";
277 $form->{decimalplaces} *= 1;
279 &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories, 1);
281 if (! $form->{compareasofdate}) {
282 if ($form->{compareasofyear} && $form->{compareasofmonth}) {
283 ($null, $form->{compareasofdate}) = $form->from_to($form->{compareasofyear}, $form->{compareasofmonth});
287 # if there are any compare dates
288 if ($form->{compareasofdate}) {
291 &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories, 1);
293 $form->{last_period} = "$form->{compareasofdate}";
302 # now we got $form->{A}{accno}{ } assets
303 # and $form->{L}{accno}{ } liabilities
304 # and $form->{Q}{accno}{ } equity
305 # build asset accounts
310 my %account = ( 'A' => { 'label' => 'asset',
311 'labels' => 'assets',
313 'L' => { 'label' => 'liability',
314 'labels' => 'liabilities',
316 'Q' => { 'label' => 'equity',
317 'labels' => 'equity',
321 foreach $category (grep { !/C/ } @categories) {
323 foreach $key (sort keys %{ $form->{$category} }) {
325 $str = ($form->{l_heading}) ? $form->{padding} : "";
327 if ($form->{$category}{$key}{charttype} eq "A") {
328 $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
330 if ($form->{$category}{$key}{charttype} eq "H") {
331 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
333 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
334 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
337 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
341 $str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
343 $account{$category}{subthis} = $form->{$category}{$key}{this};
344 $account{$category}{sublast} = $form->{$category}{$key}{last};
345 $account{$category}{subdescription} = $form->{$category}{$key}{description};
346 $account{$category}{subtotal} = 1;
348 $form->{$category}{$key}{this} = 0;
349 $form->{$category}{$key}{last} = 0;
351 next unless $form->{l_heading};
356 # push description onto array
357 push(@{$form->{"$account{$category}{label}_account"}}, $str);
359 if ($form->{$category}{$key}{charttype} eq 'A') {
360 $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
364 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
367 $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
369 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
373 $str = ($form->{l_heading}) ? $form->{padding} : "";
374 if ($account{$category}{subtotal} && $form->{l_subtotal}) {
375 push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
376 push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
379 push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
386 # totals for assets, liabilities
387 $form->{total_assets_this_period} = $form->round_amount($form->{total_assets_this_period}, $form->{decimalplaces});
388 $form->{total_liabilities_this_period} = $form->round_amount($form->{total_liabilities_this_period}, $form->{decimalplaces});
389 $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period}, $form->{decimalplaces});
392 $form->{earnings_this_period} = $form->{total_assets_this_period} - $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
394 push(@{$form->{equity_this_period}}, $form->format_amount($myconfig, $form->{earnings_this_period}, $form->{decimalplaces}, "- "));
396 $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period} + $form->{earnings_this_period}, $form->{decimalplaces});
398 # add liability + equity
399 $form->{total_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period} + $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
403 # totals for assets, liabilities
404 $form->{total_assets_last_period} = $form->round_amount($form->{total_assets_last_period}, $form->{decimalplaces});
405 $form->{total_liabilities_last_period} = $form->round_amount($form->{total_liabilities_last_period}, $form->{decimalplaces});
406 $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period}, $form->{decimalplaces});
408 # calculate retained earnings
409 $form->{earnings_last_period} = $form->{total_assets_last_period} - $form->{total_liabilities_last_period} - $form->{total_equity_last_period};
411 push(@{$form->{equity_last_period}}, $form->format_amount($myconfig,$form->{earnings_last_period}, $form->{decimalplaces}, "- "));
413 $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period} + $form->{earnings_last_period}, $form->{decimalplaces});
415 # add liability + equity
416 $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period} + $form->{total_equity_last_period}, $form->{decimalplaces}, "- ");
421 $form->{total_liabilities_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_liabilities_last_period} != 0);
423 $form->{total_equity_last_period} = $form->format_amount($myconfig, $form->{total_equity_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_equity_last_period} != 0);
425 $form->{total_assets_last_period} = $form->format_amount($myconfig, $form->{total_assets_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_assets_last_period} != 0);
427 $form->{total_assets_this_period} = $form->format_amount($myconfig, $form->{total_assets_this_period}, $form->{decimalplaces}, "- ");
429 $form->{total_liabilities_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period}, $form->{decimalplaces}, "- ");
431 $form->{total_equity_this_period} = $form->format_amount($myconfig, $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
437 my ($dbh, $last_period, $fromdate, $todate, $form, $categories, $yearend) = @_;
442 ($null, $department_id) = split /--/, $form->{department};
443 ($null, $project_id) = split /--/, $form->{projectnumber};
451 my $projectwhere = "";
453 my $yearendwhere = "1 = 1";
456 my $category = "AND (";
457 foreach $item (@{ $categories }) {
458 $category .= qq|c.category = '$item' OR |;
460 $category =~ s/OR $/\)/;
464 $query = qq|SELECT accno, description, category
466 WHERE c.charttype = 'H'
470 if ($form->{accounttype} eq 'gifi')
472 $query = qq|SELECT g.accno, g.description, c.category
474 JOIN chart c ON (c.gifi_accno = g.accno)
475 WHERE c.charttype = 'H'
480 $sth = $dbh->prepare($query);
481 $sth->execute || $form->dberror($query);
483 my @headingaccounts = ();
484 while ($ref = $sth->fetchrow_hashref(NAME_lc))
486 $form->{$ref->{category}}{$ref->{accno}}{description} = "$ref->{description}";
487 $form->{$ref->{category}}{$ref->{accno}}{charttype} = "H";
488 $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
490 push @headingaccounts, $ref->{accno};
497 $where .= " AND ac.transdate >= '$fromdate'";
498 $projectwhere .= " AND transdate >= '$fromdate'";
499 if ($form->{method} eq 'cash') {
500 $subwhere .= " AND transdate >= '$fromdate'";
501 $glwhere = " AND ac.transdate >= '$fromdate'";
506 $where .= " AND ac.transdate <= '$todate'";
507 $projectwhere .= " AND transdate <= '$todate'";
508 $subwhere .= " AND transdate <= '$todate'";
509 $yearendwhere = "ac.transdate < '$todate'";
513 $ywhere = " AND ac.trans_id NOT IN
514 (SELECT trans_id FROM yearend)";
517 $ywhere = " AND ac.trans_id NOT IN
518 (SELECT trans_id FROM yearend
519 WHERE transdate >= '$fromdate')";
521 $ywhere = " AND ac.trans_id NOT IN
522 (SELECT trans_id FROM yearend
523 WHERE transdate >= '$fromdate'
524 AND transdate <= '$todate')";
529 $ywhere = " AND ac.trans_id NOT IN
530 (SELECT trans_id FROM yearend
531 WHERE transdate <= '$todate')";
538 JOIN department t ON (a.department_id = t.id)
541 AND t.id = $department_id
548 AND ac.project_id = $project_id
553 if ($form->{accounttype} eq 'gifi')
556 if ($form->{method} eq 'cash')
561 SELECT g.accno, sum(ac.amount) AS amount,
562 g.description, c.category
564 JOIN chart c ON (c.id = ac.chart_id)
565 JOIN ar a ON (a.id = ac.trans_id)
566 JOIN gifi g ON (g.accno = c.gifi_accno)
576 JOIN chart ON (chart_id = id)
577 WHERE link LIKE '%AR_paid%'
581 GROUP BY g.accno, g.description, c.category
585 SELECT '' AS accno, SUM(ac.amount) AS amount,
586 '' AS description, c.category
588 JOIN chart c ON (c.id = ac.chart_id)
589 JOIN ar a ON (a.id = ac.trans_id)
595 AND c.gifi_accno = ''
600 JOIN chart ON (chart_id = id)
601 WHERE link LIKE '%AR_paid%'
609 SELECT g.accno, sum(ac.amount) AS amount,
610 g.description, c.category
612 JOIN chart c ON (c.id = ac.chart_id)
613 JOIN ap a ON (a.id = ac.trans_id)
614 JOIN gifi g ON (g.accno = c.gifi_accno)
624 JOIN chart ON (chart_id = id)
625 WHERE link LIKE '%AP_paid%'
629 GROUP BY g.accno, g.description, c.category
633 SELECT '' AS accno, SUM(ac.amount) AS amount,
634 '' AS description, c.category
636 JOIN chart c ON (c.id = ac.chart_id)
637 JOIN ap a ON (a.id = ac.trans_id)
643 AND c.gifi_accno = ''
648 JOIN chart ON (chart_id = id)
649 WHERE link LIKE '%AP_paid%'
659 SELECT g.accno, sum(ac.amount) AS amount,
660 g.description, c.category
662 JOIN chart c ON (c.id = ac.chart_id)
663 JOIN gifi g ON (g.accno = c.gifi_accno)
664 JOIN gl a ON (a.id = ac.trans_id)
671 AND NOT (c.link = 'AR' OR c.link = 'AP')
673 GROUP BY g.accno, g.description, c.category
677 SELECT '' AS accno, SUM(ac.amount) AS amount,
678 '' AS description, c.category
680 JOIN chart c ON (c.id = ac.chart_id)
681 JOIN gl a ON (a.id = ac.trans_id)
688 AND c.gifi_accno = ''
689 AND NOT (c.link = 'AR' OR c.link = 'AP')
696 # this is for the yearend
702 SELECT g.accno, sum(ac.amount) AS amount,
703 g.description, c.category
705 JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
706 JOIN chart c ON (c.id = ac.chart_id)
707 JOIN gifi g ON (g.accno = c.accno)
713 GROUP BY g.accno, g.description, c.category
723 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
724 g.description AS description, c.category
726 JOIN ar a ON (a.id = ac.trans_id)
727 JOIN parts p ON (ac.parts_id = p.id)
728 JOIN chart c on (p.income_accno_id = c.id)
729 JOIN gifi g ON (g.accno = c.gifi_accno)
731 WHERE 1 = 1 $projectwhere
739 JOIN chart ON (chart_id = id)
740 WHERE link LIKE '%AR_paid%'
744 GROUP BY g.accno, g.description, c.category
748 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
749 g.description AS description, c.category
751 JOIN ap a ON (a.id = ac.trans_id)
752 JOIN parts p ON (ac.parts_id = p.id)
753 JOIN chart c on (p.expense_accno_id = c.id)
754 JOIN gifi g ON (g.accno = c.gifi_accno)
756 WHERE 1 = 1 $projectwhere
757 AND p.inventory_accno_id IS NULL
766 JOIN chart ON (chart_id = id)
767 WHERE link LIKE '%AP_paid%'
771 GROUP BY g.accno, g.description, c.category
775 SELECT g.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount,
776 g.description AS description, c.category
778 JOIN ap a ON (a.id = ac.trans_id)
779 JOIN parts p ON (ac.parts_id = p.id)
780 JOIN chart c on (p.expense_accno_id = c.id)
781 JOIN gifi g ON (g.accno = c.gifi_accno)
783 WHERE 1 = 1 $projectwhere
784 AND ac.assemblyitem = '0'
792 JOIN chart ON (chart_id = id)
793 WHERE link LIKE '%AP_paid%'
797 GROUP BY g.accno, g.description, c.category
806 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
809 AND t.department_id = $department_id
816 SELECT g.accno, SUM(ac.amount) AS amount,
817 g.description, c.category
819 JOIN chart c ON (c.id = ac.chart_id)
820 JOIN gifi g ON (c.gifi_accno = g.accno)
827 GROUP BY g.accno, g.description, c.category
831 SELECT '' AS accno, SUM(ac.amount) AS amount,
832 '' AS description, c.category
834 JOIN chart c ON (c.id = ac.chart_id)
840 AND c.gifi_accno = ''
847 # this is for the yearend
853 SELECT g.accno, sum(ac.amount) AS amount,
854 g.description, c.category
856 JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
857 JOIN chart c ON (c.id = ac.chart_id)
858 JOIN gifi g ON (g.accno = c.accno)
864 GROUP BY g.accno, g.description, c.category
875 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
876 g.description AS description, c.category
878 JOIN ar a ON (a.id = ac.trans_id)
879 JOIN parts p ON (ac.parts_id = p.id)
880 JOIN chart c on (p.income_accno_id = c.id)
881 JOIN gifi g ON (c.gifi_accno = g.accno)
883 WHERE 1 = 1 $projectwhere
888 GROUP BY g.accno, g.description, c.category
892 SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
893 g.description AS description, c.category
895 JOIN ap a ON (a.id = ac.trans_id)
896 JOIN parts p ON (ac.parts_id = p.id)
897 JOIN chart c on (p.expense_accno_id = c.id)
898 JOIN gifi g ON (c.gifi_accno = g.accno)
900 WHERE 1 = 1 $projectwhere
901 AND p.inventory_accno_id IS NULL
907 GROUP BY g.accno, g.description, c.category
911 SELECT g.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount,
912 g.description AS description, c.category
914 JOIN ap a ON (a.id = ac.trans_id)
915 JOIN parts p ON (ac.parts_id = p.id)
916 JOIN chart c on (p.expense_accno_id = c.id)
917 JOIN gifi g ON (c.gifi_accno = g.accno)
919 WHERE 1 = 1 $projectwhere
920 AND ac.assemblyitem = '0'
925 GROUP BY g.accno, g.description, c.category
931 } else { # standard account
933 if ($form->{method} eq 'cash')
938 SELECT c.accno, sum(ac.amount) AS amount,
939 c.description, c.category
941 JOIN chart c ON (c.id = ac.chart_id)
942 JOIN ar a ON (a.id = ac.trans_id)
952 JOIN chart ON (chart_id = id)
953 WHERE link LIKE '%AR_paid%'
958 GROUP BY c.accno, c.description, c.category
962 SELECT c.accno, sum(ac.amount) AS amount,
963 c.description, c.category
965 JOIN chart c ON (c.id = ac.chart_id)
966 JOIN ap a ON (a.id = ac.trans_id)
976 JOIN chart ON (chart_id = id)
977 WHERE link LIKE '%AP_paid%'
982 GROUP BY c.accno, c.description, c.category
986 SELECT c.accno, sum(ac.amount) AS amount,
987 c.description, c.category
989 JOIN chart c ON (c.id = ac.chart_id)
990 JOIN gl a ON (a.id = ac.trans_id)
997 AND NOT (c.link = 'AR' OR c.link = 'AP')
999 GROUP BY c.accno, c.description, c.category
1004 # this is for the yearend
1010 SELECT c.accno, sum(ac.amount) AS amount,
1011 c.description, c.category
1013 JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
1014 JOIN chart c ON (c.id = ac.chart_id)
1017 AND c.category = 'Q'
1020 GROUP BY c.accno, c.description, c.category
1032 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
1033 c.description AS description, c.category
1035 JOIN ar a ON (a.id = ac.trans_id)
1036 JOIN parts p ON (ac.parts_id = p.id)
1037 JOIN chart c on (p.income_accno_id = c.id)
1039 WHERE 1 = 1 $projectwhere
1041 AND c.category = 'I'
1047 JOIN chart ON (chart_id = id)
1048 WHERE link LIKE '%AR_paid%'
1053 GROUP BY c.accno, c.description, c.category
1057 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
1058 c.description AS description, c.category
1060 JOIN ap a ON (a.id = ac.trans_id)
1061 JOIN parts p ON (ac.parts_id = p.id)
1062 JOIN chart c on (p.expense_accno_id = c.id)
1064 WHERE 1 = 1 $projectwhere
1065 AND p.inventory_accno_id IS NULL
1066 AND p.assembly = '0'
1068 AND c.category = 'E'
1074 JOIN chart ON (chart_id = id)
1075 WHERE link LIKE '%AP_paid%'
1080 GROUP BY c.accno, c.description, c.category
1084 SELECT c.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount,
1085 c.description AS description, c.category
1087 JOIN ap a ON (a.id = ac.trans_id)
1088 JOIN parts p ON (ac.parts_id = p.id)
1089 JOIN chart c on (p.expense_accno_id = c.id)
1091 WHERE 1 = 1 $projectwhere
1092 AND ac.assemblyitem = '0'
1094 AND c.category = 'E'
1100 JOIN chart ON (chart_id = id)
1101 WHERE link LIKE '%AP_paid%'
1106 GROUP BY c.accno, c.description, c.category
1115 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
1118 AND t.department_id = $department_id
1125 SELECT c.accno, sum(ac.amount) AS amount,
1126 c.description, c.category
1128 JOIN chart c ON (c.id = ac.chart_id)
1135 GROUP BY c.accno, c.description, c.category
1140 # this is for the yearend
1146 SELECT c.accno, sum(ac.amount) AS amount,
1147 c.description, c.category
1149 JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
1150 JOIN chart c ON (c.id = ac.chart_id)
1153 AND c.category = 'Q'
1156 GROUP BY c.accno, c.description, c.category
1168 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
1169 c.description AS description, c.category
1171 JOIN ar a ON (a.id = ac.trans_id)
1172 JOIN parts p ON (ac.parts_id = p.id)
1173 JOIN chart c on (p.income_accno_id = c.id)
1175 WHERE 1 = 1 $projectwhere
1177 AND c.category = 'I'
1180 GROUP BY c.accno, c.description, c.category
1184 SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
1185 c.description AS description, c.category
1187 JOIN ap a ON (a.id = ac.trans_id)
1188 JOIN parts p ON (ac.parts_id = p.id)
1189 JOIN chart c on (p.expense_accno_id = c.id)
1191 WHERE 1 = 1 $projectwhere
1192 AND p.inventory_accno_id IS NULL
1193 AND p.assembly = '0'
1195 AND c.category = 'E'
1198 GROUP BY c.accno, c.description, c.category
1202 SELECT c.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount,
1203 c.description AS description, c.category
1205 JOIN ap a ON (a.id = ac.trans_id)
1206 JOIN parts p ON (ac.parts_id = p.id)
1207 JOIN chart c on (p.expense_accno_id = c.id)
1209 WHERE 1 = 1 $projectwhere
1210 AND ac.assemblyitem = '0'
1212 AND c.category = 'E'
1215 GROUP BY c.accno, c.description, c.category
1226 my $sth = $dbh->prepare($query);
1227 $sth->execute || $form->dberror($query);
1229 while ($ref = $sth->fetchrow_hashref(NAME_lc))
1232 if ($ref->{category} eq 'C') {
1233 $ref->{category} = 'A';
1236 # get last heading account
1237 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1238 $accno = pop @accno;
1239 if ($accno && ($accno ne $ref->{accno}) ) {
1242 $form->{$ref->{category}}{$accno}{last} += $ref->{amount};
1244 $form->{$ref->{category}}{$accno}{this} += $ref->{amount};
1248 $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
1249 $form->{$ref->{category}}{$ref->{accno}}{description} = $ref->{description};
1250 $form->{$ref->{category}}{$ref->{accno}}{charttype} = "A";
1254 $form->{$ref->{category}}{$ref->{accno}}{last} += $ref->{amount};
1256 $form->{$ref->{category}}{$ref->{accno}}{this} += $ref->{amount};
1262 # remove accounts with zero balance
1263 foreach $category (@{ $categories }) {
1264 foreach $accno (keys %{ $form->{$category} }) {
1265 $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $form->{decimalplaces});
1266 $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $form->{decimalplaces});
1268 delete $form->{$category}{$accno} if ($form->{$category}{$accno}{this} == 0 && $form->{$category}{$accno}{last} == 0);
1277 my ($self, $myconfig, $form) = @_;
1279 my $dbh = $form->dbconnect($myconfig);
1281 my ($query, $sth, $ref);
1287 my @headingaccounts = ();
1292 my $where = "1 = 1";
1293 my $invwhere = $where;
1295 ($null, $department_id) = split /--/, $form->{department};
1296 ($null, $project_id) = split /--/, $form->{projectnumber};
1298 if ($department_id) {
1300 JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
1303 AND t.department_id = $department_id
1308 # project_id only applies to getting transactions
1309 # it has nothing to do with a trial balance
1310 # but we use the same function to collect information
1314 AND ac.project_id = $project_id
1318 ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
1320 # get beginning balances
1321 if ($form->{fromdate}) {
1323 if ($form->{accounttype} eq 'gifi') {
1325 $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
1328 JOIN chart c ON (ac.chart_id = c.id)
1329 JOIN gifi g ON (c.gifi_accno = g.accno)
1331 WHERE ac.transdate < '$form->{fromdate}'
1334 GROUP BY g.accno, c.category, g.description
1339 $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
1342 JOIN chart c ON (ac.chart_id = c.id)
1344 WHERE ac.transdate < '$form->{fromdate}'
1347 GROUP BY c.accno, c.category, c.description
1352 $sth = $dbh->prepare($query);
1353 $sth->execute || $form->dberror($query);
1355 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1356 $balance{$ref->{accno}} = $ref->{amount};
1358 if ($ref->{amount} != 0 && $form->{all_accounts}) {
1359 $trb{$ref->{accno}}{description} = $ref->{description};
1360 $trb{$ref->{accno}}{charttype} = 'A';
1361 $trb{$ref->{accno}}{category} = $ref->{category};
1371 $query = qq|SELECT c.accno, c.description, c.category
1373 WHERE c.charttype = 'H'
1376 if ($form->{accounttype} eq 'gifi')
1378 $query = qq|SELECT g.accno, g.description, c.category
1380 JOIN chart c ON (c.gifi_accno = g.accno)
1381 WHERE c.charttype = 'H'
1385 $sth = $dbh->prepare($query);
1386 $sth->execute || $form->dberror($query);
1388 while ($ref = $sth->fetchrow_hashref(NAME_lc))
1390 $trb{$ref->{accno}}{description} = $ref->{description};
1391 $trb{$ref->{accno}}{charttype} = 'H';
1392 $trb{$ref->{accno}}{category} = $ref->{category};
1394 push @headingaccounts, $ref->{accno};
1400 if ($form->{fromdate} || $form->{todate}) {
1401 if ($form->{fromdate}) {
1402 $where .= " AND ac.transdate >= '$form->{fromdate}'";
1403 $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
1405 if ($form->{todate}) {
1406 $where .= " AND ac.transdate <= '$form->{todate}'";
1407 $invwhere .= " AND a.transdate <= '$form->{todate}'";
1412 if ($form->{accounttype} eq 'gifi') {
1414 $query = qq|SELECT g.accno, g.description, c.category,
1415 SUM(ac.amount) AS amount
1417 JOIN chart c ON (c.id = ac.chart_id)
1418 JOIN gifi g ON (c.gifi_accno = g.accno)
1423 GROUP BY g.accno, g.description, c.category
1434 SELECT g.accno, g.description, c.category,
1435 SUM(ac.sellprice * ac.qty) AS amount
1437 JOIN ar a ON (ac.trans_id = a.id)
1438 JOIN parts p ON (ac.parts_id = p.id)
1439 JOIN chart c ON (p.income_accno_id = c.id)
1440 JOIN gifi g ON (c.gifi_accno = g.accno)
1445 GROUP BY g.accno, g.description, c.category
1451 SELECT g.accno, g.description, c.category,
1452 SUM(ac.sellprice * ac.qty) AS amount
1454 JOIN ap a ON (ac.trans_id = a.id)
1455 JOIN parts p ON (ac.parts_id = p.id)
1456 JOIN chart c ON (p.expense_accno_id = c.id)
1457 JOIN gifi g ON (c.gifi_accno = g.accno)
1460 AND p.inventory_accno_id IS NULL
1461 AND p.assembly = '0'
1464 GROUP BY g.accno, g.description, c.category
1470 SELECT g.accno, g.description, c.category,
1471 SUM(ac.sellprice * ac.allocated) * -1 AS amount
1473 JOIN ap a ON (ac.trans_id = a.id)
1474 JOIN parts p ON (ac.parts_id = p.id)
1475 JOIN chart c ON (p.expense_accno_id = c.id)
1476 JOIN gifi g ON (c.gifi_accno = g.accno)
1479 AND ac.assemblyitem = '0'
1482 GROUP BY g.accno, g.description, c.category
1492 $query = qq|SELECT c.accno, c.description, c.category,
1493 SUM(ac.amount) AS amount
1495 JOIN chart c ON (c.id = ac.chart_id)
1500 GROUP BY c.accno, c.description, c.category
1511 SELECT c.accno, c.description, c.category,
1512 SUM(ac.sellprice * ac.qty) AS amount
1514 JOIN ar a ON (ac.trans_id = a.id)
1515 JOIN parts p ON (ac.parts_id = p.id)
1516 JOIN chart c ON (p.income_accno_id = c.id)
1521 GROUP BY c.accno, c.description, c.category
1527 SELECT c.accno, c.description, c.category,
1528 SUM(ac.sellprice * ac.qty) AS amount
1530 JOIN ap a ON (ac.trans_id = a.id)
1531 JOIN parts p ON (ac.parts_id = p.id)
1532 JOIN chart c ON (p.expense_accno_id = c.id)
1535 AND p.inventory_accno_id IS NULL
1536 AND p.assembly = '0'
1539 GROUP BY c.accno, c.description, c.category
1545 SELECT c.accno, c.description, c.category,
1546 SUM(ac.sellprice * ac.allocated) * -1 AS amount
1548 JOIN ap a ON (ac.trans_id = a.id)
1549 JOIN parts p ON (ac.parts_id = p.id)
1550 JOIN chart c ON (p.expense_accno_id = c.id)
1553 AND ac.assemblyitem = '0'
1556 GROUP BY c.accno, c.description, c.category
1566 $sth = $dbh->prepare($query);
1567 $sth->execute || $form->dberror($query);
1569 # prepare query for each account
1570 $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1572 JOIN chart c ON (c.id = ac.chart_id)
1578 AND c.accno = ?) AS debit,
1580 (SELECT SUM(ac.amount)
1582 JOIN chart c ON (c.id = ac.chart_id)
1588 AND c.accno = ?) AS credit
1591 if ($form->{accounttype} eq 'gifi') {
1593 $query = qq|SELECT (SELECT SUM(ac.amount) * -1
1595 JOIN chart c ON (c.id = ac.chart_id)
1601 AND c.gifi_accno = ?) AS debit,
1603 (SELECT SUM(ac.amount)
1605 JOIN chart c ON (c.id = ac.chart_id)
1611 AND c.gifi_accno = ?) AS credit|;
1615 $drcr = $dbh->prepare($query);
1617 # calculate debit and credit for the period
1618 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1619 $trb{$ref->{accno}}{description} = $ref->{description};
1620 $trb{$ref->{accno}}{charttype} = 'A';
1621 $trb{$ref->{accno}}{category} = $ref->{category};
1622 $trb{$ref->{accno}}{amount} += $ref->{amount};
1626 my ($debit, $credit);
1628 foreach my $accno (sort keys %trb) {
1631 $ref->{accno} = $accno;
1632 map { $ref->{$_} = $trb{$accno}{$_} } qw(description category charttype amount);
1634 $ref->{balance} = $form->round_amount($balance{$ref->{accno}}, 2);
1636 if ($trb{$accno}{charttype} eq 'A') {
1639 if ($ref->{amount} < 0) {
1640 $ref->{debit} = $ref->{amount} * -1;
1642 $ref->{credit} = $ref->{amount};
1644 next if $form->round_amount($ref->{amount}, 2) == 0;
1649 $drcr->execute($ref->{accno}, $ref->{accno});
1651 ($debit, $credit) = (0,0);
1652 while (($debit, $credit) = $drcr->fetchrow_array) {
1653 $ref->{debit} += $debit;
1654 $ref->{credit} += $credit;
1660 $ref->{debit} = $form->round_amount($ref->{debit}, 2);
1661 $ref->{credit} = $form->round_amount($ref->{credit}, 2);
1666 @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
1667 $accno = pop @accno;
1669 $trb{$accno}{debit} += $ref->{debit};
1670 $trb{$accno}{credit} += $ref->{credit};
1673 push @{ $form->{TB} }, $ref;
1679 # debits and credits for headings
1680 foreach $accno (@headingaccounts) {
1681 foreach $ref (@{ $form->{TB} }) {
1682 if ($accno eq $ref->{accno}) {
1683 $ref->{debit} = $trb{$accno}{debit};
1684 $ref->{credit} = $trb{$accno}{credit};
1693 my ($self, $myconfig, $form) = @_;
1695 # connect to database
1696 my $dbh = $form->dbconnect($myconfig);
1697 my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
1699 ($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month};
1701 $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
1704 my $where = "1 = 1";
1709 if ($form->{"$form->{ct}_id"}) {
1710 $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
1712 if ($form->{$form->{ct}}) {
1713 $name = $form->like(lc $form->{$form->{ct}});
1714 $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{$form->{ct}};
1719 if ($form->{department}) {
1720 ($null, $department_id) = split /--/, $form->{department};
1722 JOIN department d ON (a.department_id = d.id)
1725 $where .= qq| AND a.department_id = $department_id|;
1728 # select outstanding vendors or customers, depends on $ct
1729 my $query = qq|SELECT DISTINCT ct.id, ct.name, ct.language_code
1731 JOIN $form->{arap} a ON (a.$form->{ct}_id = ct.id)
1734 AND a.paid != a.amount
1735 AND (a.transdate <= '$form->{todate}')
1738 my $sth = $dbh->prepare($query);
1739 $sth->execute || $form->dberror;
1741 my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
1743 my %interval = ( 'Pg' => {
1744 'c0' => "(date '$form->{todate}' - interval '0 days')",
1745 'c30' => "(date '$form->{todate}' - interval '30 days')",
1746 'c60' => "(date '$form->{todate}' - interval '60 days')",
1747 'c90' => "(date '$form->{todate}' - interval '90 days')" },
1749 'c0' => "(date ('$form->{todate}') - 0 days)",
1750 'c30' => "(date ('$form->{todate}') - 30 days)",
1751 'c60' => "(date ('$form->{todate}') - 60 days)",
1752 'c90' => "(date ('$form->{todate}') - 90 days)" }
1755 $interval{Oracle} = $interval{PgPP} = $interval{Pg};
1758 # for each company that has some stuff outstanding
1760 while (($id, $null, $language_code) = $sth->fetchrow_array ) {
1763 SELECT c.id AS ctid, c.name,
1764 c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
1766 c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number,
1767 a.invnumber, a.transdate, a.till, a.ordnumber, a.notes,
1768 (a.amount - a.paid) as c0, 0.00 as c30, 0.00 as c60, 0.00 as c90,
1769 a.duedate, a.invoice, a.id,
1770 (SELECT $buysell FROM exchangerate e
1771 WHERE a.curr = e.curr
1772 AND e.transdate = a.transdate) AS exchangerate
1773 FROM $form->{arap} a
1774 JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
1775 WHERE a.paid != a.amount
1778 a.transdate <= $interval{$myconfig->{dbdriver}}{c0}
1779 AND a.transdate >= $interval{$myconfig->{dbdriver}}{c30}
1784 SELECT c.id AS ctid, c.name,
1785 c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
1787 c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number,
1788 a.invnumber, a.transdate, a.till, a.ordnumber, a.notes,
1789 0.00 as c0, (a.amount - a.paid) as c30, 0.00 as c60, 0.00 as c90,
1790 a.duedate, a.invoice, a.id,
1791 (SELECT $buysell FROM exchangerate e
1792 WHERE a.curr = e.curr
1793 AND e.transdate = a.transdate) AS exchangerate
1794 FROM $form->{arap} a
1795 JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
1796 WHERE a.paid != a.amount
1799 a.transdate < $interval{$myconfig->{dbdriver}}{c30}
1800 AND a.transdate >= $interval{$myconfig->{dbdriver}}{c60}
1805 SELECT c.id AS ctid, c.name,
1806 c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
1808 c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number,
1809 a.invnumber, a.transdate, a.till, a.ordnumber, a.notes,
1810 0.00 as c0, 0.00 as c30, (a.amount - a.paid) as c60, 0.00 as c90,
1811 a.duedate, a.invoice, a.id,
1812 (SELECT $buysell FROM exchangerate e
1813 WHERE a.curr = e.curr
1814 AND e.transdate = a.transdate) AS exchangerate
1815 FROM $form->{arap} a
1816 JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
1817 WHERE a.paid != a.amount
1820 a.transdate < $interval{$myconfig->{dbdriver}}{c60}
1821 AND a.transdate >= $interval{$myconfig->{dbdriver}}{c90}
1826 SELECT c.id AS ctid, c.name,
1827 c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
1829 c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number,
1830 a.invnumber, a.transdate, a.till, a.ordnumber, a.notes,
1831 0.00 as c0, 0.00 as c30, 0.00 as c60, (a.amount - a.paid) as c90,
1832 a.duedate, a.invoice, a.id,
1833 (SELECT $buysell FROM exchangerate e
1834 WHERE a.curr = e.curr
1835 AND e.transdate = a.transdate) AS exchangerate
1836 FROM $form->{arap} a
1837 JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
1838 WHERE a.paid != a.amount
1840 AND a.transdate < $interval{$myconfig->{dbdriver}}{c90}
1844 ctid, transdate, invnumber
1848 my $sth = $dbh->prepare($query);
1849 $sth->execute || $form->dberror;
1851 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1852 $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
1853 $ref->{module} = 'ps' if $ref->{till};
1854 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
1855 $ref->{language_code} = $language_code;
1856 push @{ $form->{AG} }, $ref;
1865 my $query = qq|SELECT *
1868 $sth = $dbh->prepare($query);
1869 $sth->execute || $form->dberror($query);
1871 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1872 push @{ $form->{all_language} }, $ref;
1883 my ($self, $myconfig, $form) = @_;
1885 # connect to database
1886 my $dbh = $form->dbconnect($myconfig);
1888 my $query = qq|SELECT name, email, cc, bcc
1890 WHERE ct.id = $form->{"$form->{ct}_id"}|;
1891 ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $dbh->selectrow_array($query);
1898 sub get_taxaccounts {
1899 my ($self, $myconfig, $form) = @_;
1901 # connect to database
1902 my $dbh = $form->dbconnect($myconfig);
1905 my $query = qq|SELECT c.accno, c.description, t.rate, c.link
1907 WHERE c.link LIKE '%CT_tax%'
1908 AND c.id = t.chart_id
1910 my $sth = $dbh->prepare($query);
1911 $sth->execute || $form->dberror;
1914 while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
1915 push @{ $form->{taxaccounts} }, $ref;
1919 # get gifi tax accounts
1920 my $query = qq|SELECT DISTINCT g.accno, g.description,
1922 FROM gifi g, chart c, tax t
1923 WHERE g.accno = c.gifi_accno
1924 AND c.id = t.chart_id
1925 AND c.link LIKE '%CT_tax%'
1926 GROUP BY g.accno, g.description
1928 my $sth = $dbh->prepare($query);
1929 $sth->execute || $form->dberror;
1931 while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
1932 push @{ $form->{gifi_taxaccounts} }, $ref;
1943 my ($self, $myconfig, $form) = @_;
1945 # connect to database
1946 my $dbh = $form->dbconnect($myconfig);
1948 my ($null, $department_id) = split /--/, $form->{department};
1951 my $where = "1 = 1";
1954 if ($department_id) {
1956 AND a.department_id = $department_id
1965 if ($form->{accno}) {
1966 if ($form->{accno} =~ /^gifi_/) {
1967 ($null, $accno) = split /_/, $form->{accno};
1968 $rate = $form->{"$form->{accno}_rate"};
1969 $accno = qq| AND ch.gifi_accno = '$accno'|;
1971 $accno = $form->{accno};
1972 $rate = $form->{"$form->{accno}_rate"};
1973 $accno = qq| AND ch.accno = '$accno'|;
1981 if ($form->{db} eq 'ar') {
1982 $table = "customer";
1985 if ($form->{db} eq 'ap') {
1990 my $transdate = "a.transdate";
1992 ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
1994 # if there are any dates construct a where
1995 if ($form->{fromdate} || $form->{todate}) {
1996 if ($form->{fromdate}) {
1997 $where .= " AND $transdate >= '$form->{fromdate}'";
1999 if ($form->{todate}) {
2000 $where .= " AND $transdate <= '$form->{todate}'";
2005 if ($form->{method} eq 'cash') {
2006 $transdate = "a.datepaid";
2008 my $todate = ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig);
2015 JOIN chart ON (chart_id = id)
2016 WHERE link LIKE '%${ARAP}_paid%'
2017 AND $transdate <= '$todate'
2018 AND a.paid = a.amount
2025 my $ml = ($form->{db} eq 'ar') ? 1 : -1;
2027 my %ordinal = ( 'transdate' => 3,
2032 my @a = qw(transdate invnumber name);
2033 my $sortorder = $form->sort_order(\@a, \%ordinal);
2035 $rate = 1 unless $rate;
2037 if ($form->{summary}) {
2039 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2040 a.invnumber, n.name, a.netamount,
2041 ac.amount * $ml AS tax,
2044 JOIN $form->{db} a ON (a.id = ac.trans_id)
2045 JOIN chart ch ON (ch.id = ac.chart_id)
2046 JOIN $table n ON (n.id = a.${table}_id)
2054 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2055 a.invnumber, n.name,
2056 sum(ac.sellprice * ac.qty) * $ml AS netamount,
2057 sum(ac.sellprice * ac.qty) * $rate * $ml AS tax,
2060 JOIN partstax pt ON (pt.parts_id = ac.parts_id)
2061 JOIN chart ch ON (ch.id = pt.chart_id)
2062 JOIN $form->{db} a ON (a.id = ac.trans_id)
2063 JOIN $table n ON (n.id = a.${table}_id)
2064 JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
2069 GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name,
2073 if ($form->{fromdate}) {
2074 # include open transactions from previous period
2079 SELECT a.id, '0' AS invoice, $transdate AS transdate,
2080 a.invnumber, n.name, a.netamount,
2081 ac.amount * $ml AS tax,
2084 JOIN $form->{db} a ON (a.id = ac.trans_id)
2085 JOIN chart ch ON (ch.id = ac.chart_id)
2086 JOIN $table n ON (n.id = a.${table}_id)
2087 WHERE a.datepaid >= '$form->{fromdate}'
2094 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2095 a.invnumber, n.name,
2096 sum(ac.sellprice * ac.qty) * $ml AS netamount,
2097 sum(ac.sellprice * ac.qty) * $rate * $ml AS tax,
2100 JOIN partstax pt ON (pt.parts_id = ac.parts_id)
2101 JOIN chart ch ON (ch.id = pt.chart_id)
2102 JOIN $form->{db} a ON (a.id = ac.trans_id)
2103 JOIN $table n ON (n.id = a.${table}_id)
2104 JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
2105 WHERE a.datepaid >= '$form->{fromdate}'
2109 GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name,
2118 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2119 a.invnumber, n.name, a.netamount,
2120 ac.amount * $ml AS tax,
2121 a.notes AS description, a.till
2123 JOIN $form->{db} a ON (a.id = ac.trans_id)
2124 JOIN chart ch ON (ch.id = ac.chart_id)
2125 JOIN $table n ON (n.id = a.${table}_id)
2133 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2134 a.invnumber, n.name,
2135 i.sellprice * i.qty * $ml AS netamount,
2136 i.sellprice * i.qty * $rate * $ml AS tax,
2137 i.description, a.till
2139 JOIN $form->{db} a ON (a.id = ac.trans_id)
2140 JOIN chart ch ON (ch.id = ac.chart_id)
2141 JOIN $table n ON (n.id = a.${table}_id)
2142 JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
2143 JOIN invoice i ON (i.trans_id = a.id)
2144 JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id)
2151 if ($form->{fromdate}) {
2156 SELECT a.id, '0' AS invoice, $transdate AS transdate,
2157 a.invnumber, n.name, a.netamount,
2158 ac.amount * $ml AS tax,
2159 a.notes AS description, a.till
2161 JOIN $form->{db} a ON (a.id = ac.trans_id)
2162 JOIN chart ch ON (ch.id = ac.chart_id)
2163 JOIN $table n ON (n.id = a.${table}_id)
2164 WHERE a.datepaid >= '$form->{fromdate}'
2171 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2172 a.invnumber, n.name,
2173 i.sellprice * i.qty * $ml AS netamount,
2174 i.sellprice * i.qty * $rate * $ml AS tax,
2175 i.description, a.till
2177 JOIN $form->{db} a ON (a.id = ac.trans_id)
2178 JOIN chart ch ON (ch.id = ac.chart_id)
2179 JOIN $table n ON (n.id = a.${table}_id)
2180 JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
2181 JOIN invoice i ON (i.trans_id = a.id)
2182 JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id)
2183 WHERE a.datepaid >= '$form->{fromdate}'
2193 if ($form->{report} =~ /nontaxable/) {
2195 if ($form->{summary}) {
2196 # only gather up non-taxable transactions
2197 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2198 a.invnumber, n.name, a.netamount, a.till
2200 JOIN $form->{db} a ON (a.id = ac.trans_id)
2201 JOIN $table n ON (n.id = a.${table}_id)
2204 AND a.netamount = a.amount
2206 GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
2211 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2212 a.invnumber, n.name,
2213 sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till
2215 JOIN $form->{db} a ON (a.id = ac.trans_id)
2216 JOIN $table n ON (n.id = a.${table}_id)
2220 a.${table}_id NOT IN (
2221 SELECT ${table}_id FROM ${table}tax t (${table}_id)
2223 ac.parts_id NOT IN (
2224 SELECT parts_id FROM partstax p (parts_id)
2228 GROUP BY a.id, a.invnumber, $transdate, n.name, a.till
2231 if ($form->{fromdate}) {
2236 SELECT a.id, '0' AS invoice, $transdate AS transdate,
2237 a.invnumber, n.name, a.netamount, a.till
2239 JOIN $form->{db} a ON (a.id = ac.trans_id)
2240 JOIN $table n ON (n.id = a.${table}_id)
2241 WHERE a.datepaid >= '$form->{fromdate}'
2243 AND a.netamount = a.amount
2245 GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
2250 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2251 a.invnumber, n.name,
2252 sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till
2254 JOIN $form->{db} a ON (a.id = ac.trans_id)
2255 JOIN $table n ON (n.id = a.${table}_id)
2256 WHERE a.datepaid >= '$form->{fromdate}'
2259 a.${table}_id NOT IN (
2260 SELECT ${table}_id FROM ${table}tax t (${table}_id)
2262 ac.parts_id NOT IN (
2263 SELECT parts_id FROM partstax p (parts_id)
2267 GROUP BY a.id, a.invnumber, $transdate, n.name, a.till
2274 # gather up details for non-taxable transactions
2275 $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
2276 a.invnumber, n.name, a.netamount,
2277 a.notes AS description, a.till
2279 JOIN $form->{db} a ON (a.id = ac.trans_id)
2280 JOIN $table n ON (n.id = a.${table}_id)
2283 AND a.netamount = a.amount
2285 GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
2290 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2291 a.invnumber, n.name,
2292 sum(ac.sellprice * ac.qty) * $ml AS netamount,
2293 ac.description, a.till
2295 JOIN $form->{db} a ON (a.id = ac.trans_id)
2296 JOIN $table n ON (n.id = a.${table}_id)
2300 a.${table}_id NOT IN (
2301 SELECT ${table}_id FROM ${table}tax t (${table}_id)
2303 ac.parts_id NOT IN (
2304 SELECT parts_id FROM partstax p (parts_id)
2308 GROUP BY a.id, a.invnumber, $transdate, n.name,
2309 ac.description, a.till
2312 if ($form->{fromdate}) {
2317 SELECT a.id, '0' AS invoice, $transdate AS transdate,
2318 a.invnumber, n.name, a.netamount,
2319 a.notes AS description, a.till
2321 JOIN $form->{db} a ON (a.id = ac.trans_id)
2322 JOIN $table n ON (n.id = a.${table}_id)
2323 WHERE a.datepaid >= '$form->{fromdate}'
2325 AND a.netamount = a.amount
2327 GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
2332 SELECT a.id, '1' AS invoice, $transdate AS transdate,
2333 a.invnumber, n.name,
2334 sum(ac.sellprice * ac.qty) * $ml AS netamount,
2335 ac.description, a.till
2337 JOIN $form->{db} a ON (a.id = ac.trans_id)
2338 JOIN $table n ON (n.id = a.${table}_id)
2339 WHERE a.datepaid >= '$form->{fromdate}'
2342 a.${table}_id NOT IN (
2343 SELECT ${table}_id FROM ${table}tax t (${table}_id)
2345 ac.parts_id NOT IN (
2346 SELECT parts_id FROM partstax p (parts_id)
2350 GROUP BY a.id, a.invnumber, $transdate, n.name,
2351 ac.description, a.till
2361 ORDER by $sortorder|;
2363 $sth = $dbh->prepare($query);
2364 $sth->execute || $form->dberror($query);
2366 while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2367 $ref->{tax} = $form->round_amount($ref->{tax}, 2);
2368 push @{ $form->{TR} }, $ref if $ref->{netamount} != 0;
2377 sub paymentaccounts {
2378 my ($self, $myconfig, $form) = @_;
2380 # connect to database, turn AutoCommit off
2381 my $dbh = $form->dbconnect_noauto($myconfig);
2383 my $ARAP = uc $form->{db};
2385 # get A(R|P)_paid accounts
2386 my $query = qq|SELECT accno, description
2388 WHERE link LIKE '%${ARAP}_paid%'
2390 my $sth = $dbh->prepare($query);
2391 $sth->execute || $form->dberror($query);
2393 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
2394 push @{ $form->{PR} }, $ref;
2398 $form->all_years($dbh, $myconfig);
2406 my ($self, $myconfig, $form) = @_;
2408 # connect to database, turn AutoCommit off
2409 my $dbh = $form->dbconnect_noauto($myconfig);
2412 if ($form->{db} eq 'ar') {
2413 $table = 'customer';
2416 if ($form->{db} eq 'ap') {
2427 if ($form->{department_id}) {
2429 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
2433 AND t.department_id = $form->{department_id}
2437 ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
2439 if ($form->{fromdate}) {
2440 $where .= " AND ac.transdate >= '$form->{fromdate}'";
2442 if ($form->{todate}) {
2443 $where .= " AND ac.transdate <= '$form->{todate}'";
2445 if (!$form->{fx_transaction}) {
2446 $where .= " AND ac.fx_transaction = '0'";
2449 if ($form->{description}) {
2450 $var = $form->like(lc $form->{description});
2451 $where .= " AND lower(c.name) LIKE '$var'";
2453 if ($form->{source}) {
2454 $var = $form->like(lc $form->{source});
2455 $where .= " AND lower(ac.source) LIKE '$var'";
2457 if ($form->{memo}) {
2458 $var = $form->like(lc $form->{memo});
2459 $where .= " AND lower(ac.memo) LIKE '$var'";
2462 my %ordinal = ( 'name' => 1,
2469 my @a = qw(name transdate employee);
2470 my $sortorder = $form->sort_order(\@a, \%ordinal);
2472 my $glwhere = $where;
2473 $glwhere =~ s/\(c.name\)/\(g.description\)/;
2475 # cycle through each id
2476 foreach my $accno (split(/ /, $form->{paymentaccounts})) {
2478 $query = qq|SELECT id, accno, description
2480 WHERE accno = '$accno'|;
2481 $sth = $dbh->prepare($query);
2482 $sth->execute || $form->dberror($query);
2484 my $ref = $sth->fetchrow_hashref(NAME_lc);
2485 push @{ $form->{PR} }, $ref;
2488 $query = qq|SELECT c.name, ac.transdate, sum(ac.amount) * $ml AS paid,
2489 ac.source, ac.memo, e.name AS employee, a.till, a.curr
2491 JOIN $form->{db} a ON (ac.trans_id = a.id)
2492 JOIN $table c ON (c.id = a.${table}_id)
2493 LEFT JOIN employee e ON (a.employee_id = e.id)
2495 WHERE ac.chart_id = $ref->{id}
2498 if ($form->{till}) {
2499 $query .= " AND a.invoice = '1'
2500 AND NOT a.till IS NULL";
2502 if ($myconfig->{role} eq 'user') {
2503 $query .= " AND e.login = '$form->{login}'";
2508 GROUP BY c.name, ac.transdate, ac.source, ac.memo,
2509 e.name, a.till, a.curr
2512 if (! $form->{till}) {
2513 # don't need gl for a till
2517 SELECT g.description, ac.transdate, sum(ac.amount) * $ml AS paid, ac.source,
2518 ac.memo, e.name AS employee, '' AS till, '' AS curr
2520 JOIN gl g ON (g.id = ac.trans_id)
2521 LEFT JOIN employee e ON (g.employee_id = e.id)
2523 WHERE ac.chart_id = $ref->{id}
2525 AND (ac.amount * $ml) > 0
2526 GROUP BY g.description, ac.transdate, ac.source, ac.memo, e.name
2532 ORDER BY $sortorder|;
2534 $sth = $dbh->prepare($query);
2535 $sth->execute || $form->dberror($query);
2537 while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
2538 push @{ $form->{$ref->{id}} }, $pr;