diff options
Diffstat (limited to 'sql-ledger/SL/RP.pm')
-rw-r--r-- | sql-ledger/SL/RP.pm | 2551 |
1 files changed, 2551 insertions, 0 deletions
diff --git a/sql-ledger/SL/RP.pm b/sql-ledger/SL/RP.pm new file mode 100644 index 0000000..791b22b --- /dev/null +++ b/sql-ledger/SL/RP.pm @@ -0,0 +1,2551 @@ +#===================================================================== +# SQL-Ledger Accounting +# Copyright (C) 2001 +# +# Author: Dieter Simader +# Email: dsimader@sql-ledger.org +# Web: http://www.sql-ledger.org +# +# Contributors: Benjamin Lee <benjaminlee@consultant.com> +# Jim Rawlings <jim@your-dba.com> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 2 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. +#====================================================================== +# +# backend code for reports +# +#====================================================================== + +package RP; + + +sub yearend_statement { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + # if todate < existing yearends, delete GL and yearends + my $query = qq|SELECT trans_id FROM yearend + WHERE transdate >= '$form->{todate}'|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my @trans_id = (); + my $id; + while (($id) = $sth->fetchrow_array) { + push @trans_id, $id; + } + $sth->finish; + + $query = qq|DELETE FROM gl + WHERE id = ?|; + $sth = $dbh->prepare($query) || $form->dberror($query); + + $query = qq|DELETE FROM acc_trans + WHERE trans_id = ?|; + my $ath = $dbh->prepare($query) || $form->dberror($query); + + foreach $id (@trans_id) { + $sth->execute($id); + $ath->execute($id); + } + $sth->finish; + + + my $last_period = 0; + my @categories = qw(I E); + my $category; + + $form->{decimalplaces} *= 1; + + &get_accounts($dbh, 0, $form->{fromdate}, $form->{todate}, $form, \@categories); + + # disconnect + $dbh->disconnect; + + + # now we got $form->{I}{accno}{ } + # and $form->{E}{accno}{ } + + my %account = ( 'I' => { 'label' => 'income', + 'labels' => 'income', + 'ml' => 1 }, + 'E' => { 'label' => 'expense', + 'labels' => 'expenses', + 'ml' => -1 } + ); + + foreach $category (@categories) { + foreach $key (sort keys %{ $form->{$category} }) { + if ($form->{$category}{$key}{charttype} eq 'A') { + $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml}; + } + } + } + + + # totals for income and expenses + $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces}); + $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces}); + + # total for income/loss + $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period}; + +} + + +sub income_statement { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $last_period = 0; + my @categories = qw(I E); + my $category; + + $form->{decimalplaces} *= 1; + + if (! ($form->{fromdate} || $form->{todate})) { + if ($form->{fromyear} && $form->{frommonth}) { + ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{fromyear}, $form->{frommonth}, $form->{interval}); + } + } + + &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories, 1); + + if (! ($form->{comparefromdate} || $form->{comparetodate})) { + if ($form->{compareyear} && $form->{comparemonth}) { + ($form->{comparefromdate}, $form->{comparetodate}) = $form->from_to($form->{compareyear}, $form->{comparemonth}, $form->{interval}); + } + } + + # if there are any compare dates + if ($form->{comparefromdate} || $form->{comparetodate}) { + $last_period = 1; + + &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories, 1); + } + + + # disconnect + $dbh->disconnect; + + + # now we got $form->{I}{accno}{ } + # and $form->{E}{accno}{ } + + my %account = ( 'I' => { 'label' => 'income', + 'labels' => 'income', + 'ml' => 1 }, + 'E' => { 'label' => 'expense', + 'labels' => 'expenses', + 'ml' => -1 } + ); + + my $str; + + foreach $category (@categories) { + + foreach $key (sort keys %{ $form->{$category} }) { + # push description onto array + + $str = ($form->{l_heading}) ? $form->{padding} : ""; + + if ($form->{$category}{$key}{charttype} eq "A") { + $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}"; + } + if ($form->{$category}{$key}{charttype} eq "H") { + if ($account{$category}{subtotal} && $form->{l_subtotal}) { + $dash = "- "; + push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"); + push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + + if ($last_period) { + push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + } + + } + + $str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}"; + + $account{$category}{subthis} = $form->{$category}{$key}{this}; + $account{$category}{sublast} = $form->{$category}{$key}{last}; + $account{$category}{subdescription} = $form->{$category}{$key}{description}; + $account{$category}{subtotal} = 1; + + $form->{$category}{$key}{this} = 0; + $form->{$category}{$key}{last} = 0; + + next unless $form->{l_heading}; + + $dash = " "; + } + + push(@{$form->{"$account{$category}{label}_account"}}, $str); + + if ($form->{$category}{$key}{charttype} eq 'A') { + $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml}; + $dash = "- "; + } + + push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + + # add amount or - for last period + if ($last_period) { + $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml}; + + push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + } + } + + $str = ($form->{l_heading}) ? $form->{padding} : ""; + if ($account{$category}{subtotal} && $form->{l_subtotal}) { + push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"); + push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + + if ($last_period) { + push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + } + } + + } + + + # totals for income and expenses + $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces}); + $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces}); + + # total for income/loss + $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period}; + + if ($last_period) { + # total for income/loss + $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- "); + + # totals for income and expenses for last_period + $form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- "); + $form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- "); + + } + + + $form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- "); + $form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- "); + $form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- "); + +} + + +sub balance_sheet { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $last_period = 0; + my @categories = qw(A C L Q); + + my $null; + + if (! $form->{asofdate}) { + if ($form->{asofyear} && $form->{asofmonth}) { + ($null, $form->{asofdate}) = $form->from_to($form->{asofyear}, $form->{asofmonth}); + } + } + + # if there are any dates construct a where + if ($form->{asofdate}) { + + $form->{this_period} = "$form->{asofdate}"; + $form->{period} = "$form->{asofdate}"; + + } + + $form->{decimalplaces} *= 1; + + &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories, 1); + + if (! $form->{compareasofdate}) { + if ($form->{compareasofyear} && $form->{compareasofmonth}) { + ($null, $form->{compareasofdate}) = $form->from_to($form->{compareasofyear}, $form->{compareasofmonth}); + } + } + + # if there are any compare dates + if ($form->{compareasofdate}) { + + $last_period = 1; + &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories, 1); + + $form->{last_period} = "$form->{compareasofdate}"; + + } + + + # disconnect + $dbh->disconnect; + + + # now we got $form->{A}{accno}{ } assets + # and $form->{L}{accno}{ } liabilities + # and $form->{Q}{accno}{ } equity + # build asset accounts + + my $str; + my $key; + + my %account = ( 'A' => { 'label' => 'asset', + 'labels' => 'assets', + 'ml' => -1 }, + 'L' => { 'label' => 'liability', + 'labels' => 'liabilities', + 'ml' => 1 }, + 'Q' => { 'label' => 'equity', + 'labels' => 'equity', + 'ml' => 1 } + ); + + foreach $category (grep { !/C/ } @categories) { + + foreach $key (sort keys %{ $form->{$category} }) { + + $str = ($form->{l_heading}) ? $form->{padding} : ""; + + if ($form->{$category}{$key}{charttype} eq "A") { + $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}"; + } + if ($form->{$category}{$key}{charttype} eq "H") { + if ($account{$category}{subtotal} && $form->{l_subtotal}) { + $dash = "- "; + push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"); + push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + + if ($last_period) { + push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + } + } + + $str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}"; + + $account{$category}{subthis} = $form->{$category}{$key}{this}; + $account{$category}{sublast} = $form->{$category}{$key}{last}; + $account{$category}{subdescription} = $form->{$category}{$key}{description}; + $account{$category}{subtotal} = 1; + + $form->{$category}{$key}{this} = 0; + $form->{$category}{$key}{last} = 0; + + next unless $form->{l_heading}; + + $dash = " "; + } + + # push description onto array + push(@{$form->{"$account{$category}{label}_account"}}, $str); + + if ($form->{$category}{$key}{charttype} eq 'A') { + $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml}; + $dash = "- "; + } + + push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + + if ($last_period) { + $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml}; + + push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + } + } + + $str = ($form->{l_heading}) ? $form->{padding} : ""; + if ($account{$category}{subtotal} && $form->{l_subtotal}) { + push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}"); + push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + + if ($last_period) { + push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash)); + } + } + + } + + + # totals for assets, liabilities + $form->{total_assets_this_period} = $form->round_amount($form->{total_assets_this_period}, $form->{decimalplaces}); + $form->{total_liabilities_this_period} = $form->round_amount($form->{total_liabilities_this_period}, $form->{decimalplaces}); + $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period}, $form->{decimalplaces}); + + # calculate earnings + $form->{earnings_this_period} = $form->{total_assets_this_period} - $form->{total_liabilities_this_period} - $form->{total_equity_this_period}; + + push(@{$form->{equity_this_period}}, $form->format_amount($myconfig, $form->{earnings_this_period}, $form->{decimalplaces}, "- ")); + + $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period} + $form->{earnings_this_period}, $form->{decimalplaces}); + + # add liability + equity + $form->{total_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period} + $form->{total_equity_this_period}, $form->{decimalplaces}, "- "); + + + if ($last_period) { + # totals for assets, liabilities + $form->{total_assets_last_period} = $form->round_amount($form->{total_assets_last_period}, $form->{decimalplaces}); + $form->{total_liabilities_last_period} = $form->round_amount($form->{total_liabilities_last_period}, $form->{decimalplaces}); + $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period}, $form->{decimalplaces}); + + # calculate retained earnings + $form->{earnings_last_period} = $form->{total_assets_last_period} - $form->{total_liabilities_last_period} - $form->{total_equity_last_period}; + + push(@{$form->{equity_last_period}}, $form->format_amount($myconfig,$form->{earnings_last_period}, $form->{decimalplaces}, "- ")); + + $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period} + $form->{earnings_last_period}, $form->{decimalplaces}); + + # add liability + equity + $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period} + $form->{total_equity_last_period}, $form->{decimalplaces}, "- "); + + } + + + $form->{total_liabilities_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_liabilities_last_period} != 0); + + $form->{total_equity_last_period} = $form->format_amount($myconfig, $form->{total_equity_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_equity_last_period} != 0); + + $form->{total_assets_last_period} = $form->format_amount($myconfig, $form->{total_assets_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_assets_last_period} != 0); + + $form->{total_assets_this_period} = $form->format_amount($myconfig, $form->{total_assets_this_period}, $form->{decimalplaces}, "- "); + + $form->{total_liabilities_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period}, $form->{decimalplaces}, "- "); + + $form->{total_equity_this_period} = $form->format_amount($myconfig, $form->{total_equity_this_period}, $form->{decimalplaces}, "- "); + +} + + +sub get_accounts { + my ($dbh, $last_period, $fromdate, $todate, $form, $categories, $yearend) = @_; + + my $department_id; + my $project_id; + + ($null, $department_id) = split /--/, $form->{department}; + ($null, $project_id) = split /--/, $form->{projectnumber}; + + my $query; + my $dpt_where; + my $dpt_join; + my $project; + my $where = "1 = 1"; + my $glwhere = ""; + my $projectwhere = ""; + my $subwhere = ""; + my $yearendwhere = "1 = 1"; + my $item; + + my $category = "AND ("; + foreach $item (@{ $categories }) { + $category .= qq|c.category = '$item' OR |; + } + $category =~ s/OR $/\)/; + + + # get headings + $query = qq|SELECT accno, description, category + FROM chart c + WHERE c.charttype = 'H' + $category + ORDER by c.accno|; + + if ($form->{accounttype} eq 'gifi') + { + $query = qq|SELECT g.accno, g.description, c.category + FROM gifi g + JOIN chart c ON (c.gifi_accno = g.accno) + WHERE c.charttype = 'H' + $category + ORDER BY g.accno|; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my @headingaccounts = (); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) + { + $form->{$ref->{category}}{$ref->{accno}}{description} = "$ref->{description}"; + $form->{$ref->{category}}{$ref->{accno}}{charttype} = "H"; + $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno}; + + push @headingaccounts, $ref->{accno}; + } + + $sth->finish; + + + if ($fromdate) { + $where .= " AND ac.transdate >= '$fromdate'"; + $projectwhere .= " AND transdate >= '$fromdate'"; + if ($form->{method} eq 'cash') { + $subwhere .= " AND transdate >= '$fromdate'"; + $glwhere = " AND ac.transdate >= '$fromdate'"; + } + } + + if ($todate) { + $where .= " AND ac.transdate <= '$todate'"; + $projectwhere .= " AND transdate <= '$todate'"; + $subwhere .= " AND transdate <= '$todate'"; + $yearendwhere = "ac.transdate < '$todate'"; + } + + if ($yearend) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend)"; + + if ($fromdate) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend + WHERE transdate >= '$fromdate')"; + if ($todate) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend + WHERE transdate >= '$fromdate' + AND transdate <= '$todate')"; + } + } + + if ($todate) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend + WHERE transdate <= '$todate')"; + } + } + + if ($department_id) + { + $dpt_join = qq| + JOIN department t ON (a.department_id = t.id) + |; + $dpt_where = qq| + AND t.id = $department_id + |; + } + + if ($project_id) + { + $project = qq| + AND ac.project_id = $project_id + |; + } + + + if ($form->{accounttype} eq 'gifi') + { + + if ($form->{method} eq 'cash') + { + + $query = qq| + + SELECT g.accno, sum(ac.amount) AS amount, + g.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ar a ON (a.id = ac.trans_id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE $where + $ywhere + $dpt_where + $category + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AR_paid%' + $subwhere + ) + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT '' AS accno, SUM(ac.amount) AS amount, + '' AS description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ar a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $ywhere + $dpt_where + $category + AND c.gifi_accno = '' + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AR_paid%' + $subwhere + ) + $project + GROUP BY c.category + + UNION ALL + + SELECT g.accno, sum(ac.amount) AS amount, + g.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ap a ON (a.id = ac.trans_id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE $where + $ywhere + $dpt_where + $category + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_paid%' + $subwhere + ) + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT '' AS accno, SUM(ac.amount) AS amount, + '' AS description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ap a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $ywhere + $dpt_where + $category + AND c.gifi_accno = '' + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_paid%' + $subwhere + ) + $project + GROUP BY c.category + + UNION ALL + +-- add gl + + SELECT g.accno, sum(ac.amount) AS amount, + g.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gifi g ON (g.accno = c.gifi_accno) + JOIN gl a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $ywhere + $glwhere + $dpt_where + $category + AND NOT (c.link = 'AR' OR c.link = 'AP') + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT '' AS accno, SUM(ac.amount) AS amount, + '' AS description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gl a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $ywhere + $glwhere + $dpt_where + $category + AND c.gifi_accno = '' + AND NOT (c.link = 'AR' OR c.link = 'AP') + $project + GROUP BY c.category + |; + + if ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT g.accno, sum(ac.amount) AS amount, + g.description, c.category + FROM yearend y + JOIN acc_trans ac ON (ac.trans_id = y.trans_id) + JOIN chart c ON (c.id = ac.chart_id) + JOIN gifi g ON (g.accno = c.accno) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + |; + } + + if ($project_id) { + + $query .= qq| + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AR_paid%' + $subwhere + ) + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_paid%' + $subwhere + ) + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_paid%' + $subwhere + ) + $project + GROUP BY g.accno, g.description, c.category + |; + } + + } else { + + if ($department_id) + { + $dpt_join = qq| + JOIN dpt_trans t ON (t.trans_id = ac.trans_id) + |; + $dpt_where = qq| + AND t.department_id = $department_id + |; + + } + + $query = qq| + + SELECT g.accno, SUM(ac.amount) AS amount, + g.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $where + $ywhere + $dpt_from + $category + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT '' AS accno, SUM(ac.amount) AS amount, + '' AS description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $ywhere + $dpt_from + $category + AND c.gifi_accno = '' + $project + GROUP BY c.category + |; + + if ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT g.accno, sum(ac.amount) AS amount, + g.description, c.category + FROM yearend y + JOIN acc_trans ac ON (ac.trans_id = y.trans_id) + JOIN chart c ON (c.id = ac.chart_id) + JOIN gifi g ON (g.accno = c.accno) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + |; + } + + if ($project_id) + { + + $query .= qq| + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + |; + } + + } + + } else { # standard account + + if ($form->{method} eq 'cash') + { + + $query = qq| + + SELECT c.accno, sum(ac.amount) AS amount, + c.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ar a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $ywhere + $dpt_where + $category + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AR_paid%' + $subwhere + ) + + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno, sum(ac.amount) AS amount, + c.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN ap a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $ywhere + $dpt_where + $category + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_paid%' + $subwhere + ) + + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno, sum(ac.amount) AS amount, + c.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gl a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $ywhere + $glwhere + $dpt_from + $category + AND NOT (c.link = 'AR' OR c.link = 'AP') + $project + GROUP BY c.accno, c.description, c.category + |; + + if ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT c.accno, sum(ac.amount) AS amount, + c.description, c.category + FROM yearend y + JOIN acc_trans ac ON (ac.trans_id = y.trans_id) + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + |; + } + + + if ($project_id) + { + + $query .= qq| + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AR_paid%' + $subwhere + ) + + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_paid%' + $subwhere + ) + + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AP_paid%' + $subwhere + ) + + $project + GROUP BY c.accno, c.description, c.category + |; + } + + } else { + + if ($department_id) + { + $dpt_join = qq| + JOIN dpt_trans t ON (t.trans_id = ac.trans_id) + |; + $dpt_where = qq| + AND t.department_id = $department_id + |; + } + + + $query = qq| + + SELECT c.accno, sum(ac.amount) AS amount, + c.description, c.category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $ywhere + $dpt_where + $category + $project + GROUP BY c.accno, c.description, c.category + |; + + if ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT c.accno, sum(ac.amount) AS amount, + c.description, c.category + FROM yearend y + JOIN acc_trans ac ON (ac.trans_id = y.trans_id) + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + |; + } + + + if ($project_id) + { + + $query .= qq| + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + |; + + } + } + } + + my @accno; + my $accno; + my $ref; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) + { + + if ($ref->{category} eq 'C') { + $ref->{category} = 'A'; + } + + # get last heading account + @accno = grep { $_ le "$ref->{accno}" } @headingaccounts; + $accno = pop @accno; + if ($accno && ($accno ne $ref->{accno}) ) { + if ($last_period) + { + $form->{$ref->{category}}{$accno}{last} += $ref->{amount}; + } else { + $form->{$ref->{category}}{$accno}{this} += $ref->{amount}; + } + } + + $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno}; + $form->{$ref->{category}}{$ref->{accno}}{description} = $ref->{description}; + $form->{$ref->{category}}{$ref->{accno}}{charttype} = "A"; + + if ($last_period) + { + $form->{$ref->{category}}{$ref->{accno}}{last} += $ref->{amount}; + } else { + $form->{$ref->{category}}{$ref->{accno}}{this} += $ref->{amount}; + } + } + $sth->finish; + + + # remove accounts with zero balance + foreach $category (@{ $categories }) { + foreach $accno (keys %{ $form->{$category} }) { + $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $form->{decimalplaces}); + $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $form->{decimalplaces}); + + delete $form->{$category}{$accno} if ($form->{$category}{$accno}{this} == 0 && $form->{$category}{$accno}{last} == 0); + } + } + +} + + + +sub trial_balance { + my ($self, $myconfig, $form) = @_; + + my $dbh = $form->dbconnect($myconfig); + + my ($query, $sth, $ref); + my %balance = (); + my %trb = (); + my $null; + my $department_id; + my $project_id; + my @headingaccounts = (); + my $dpt_where; + my $dpt_join; + my $project; + + my $where = "1 = 1"; + my $invwhere = $where; + + ($null, $department_id) = split /--/, $form->{department}; + ($null, $project_id) = split /--/, $form->{projectnumber}; + + if ($department_id) { + $dpt_join = qq| + JOIN dpt_trans t ON (ac.trans_id = t.trans_id) + |; + $dpt_where = qq| + AND t.department_id = $department_id + |; + } + + + # project_id only applies to getting transactions + # it has nothing to do with a trial balance + # but we use the same function to collect information + + if ($project_id) { + $project = qq| + AND ac.project_id = $project_id + |; + } + + ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + + # get beginning balances + if ($form->{fromdate}) { + + if ($form->{accounttype} eq 'gifi') { + + $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount, + g.description + FROM acc_trans ac + JOIN chart c ON (ac.chart_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE ac.transdate < '$form->{fromdate}' + $dpt_where + $project + GROUP BY g.accno, c.category, g.description + |; + + } else { + + $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, + c.description + FROM acc_trans ac + JOIN chart c ON (ac.chart_id = c.id) + $dpt_join + WHERE ac.transdate < '$form->{fromdate}' + $dpt_where + $project + GROUP BY c.accno, c.category, c.description + |; + + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + $balance{$ref->{accno}} = $ref->{amount}; + + if ($ref->{amount} != 0 && $form->{all_accounts}) { + $trb{$ref->{accno}}{description} = $ref->{description}; + $trb{$ref->{accno}}{charttype} = 'A'; + $trb{$ref->{accno}}{category} = $ref->{category}; + } + + } + $sth->finish; + + } + + + # get headings + $query = qq|SELECT c.accno, c.description, c.category + FROM chart c + WHERE c.charttype = 'H' + ORDER by c.accno|; + + if ($form->{accounttype} eq 'gifi') + { + $query = qq|SELECT g.accno, g.description, c.category + FROM gifi g + JOIN chart c ON (c.gifi_accno = g.accno) + WHERE c.charttype = 'H' + ORDER BY g.accno|; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) + { + $trb{$ref->{accno}}{description} = $ref->{description}; + $trb{$ref->{accno}}{charttype} = 'H'; + $trb{$ref->{accno}}{category} = $ref->{category}; + + push @headingaccounts, $ref->{accno}; + } + + $sth->finish; + + + if ($form->{fromdate} || $form->{todate}) { + if ($form->{fromdate}) { + $where .= " AND ac.transdate >= '$form->{fromdate}'"; + $invwhere .= " AND a.transdate >= '$form->{fromdate}'"; + } + if ($form->{todate}) { + $where .= " AND ac.transdate <= '$form->{todate}'"; + $invwhere .= " AND a.transdate <= '$form->{todate}'"; + } + } + + + if ($form->{accounttype} eq 'gifi') { + + $query = qq|SELECT g.accno, g.description, c.category, + SUM(ac.amount) AS amount + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $where + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + |; + + if ($project_id) { + + $query .= qq| + + -- sold items + + UNION ALL + + SELECT g.accno, g.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ar a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.income_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $invwhere + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + -- bought services + + SELECT g.accno, g.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $invwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + -- COGS + + UNION ALL + + SELECT g.accno, g.description, c.category, + SUM(ac.sellprice * ac.allocated) * -1 AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $invwhere + AND ac.assemblyitem = '0' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + |; + } + + $query .= qq| + ORDER BY accno|; + + } else { + + $query = qq|SELECT c.accno, c.description, c.category, + SUM(ac.amount) AS amount + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + |; + + if ($project_id) { + + $query .= qq| + + -- sold items + + UNION ALL + + SELECT c.accno, c.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ar a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.income_accno_id = c.id) + $dpt_join + WHERE $invwhere + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + -- bought services + + SELECT c.accno, c.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + $dpt_join + WHERE $invwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + -- cogs + + UNION ALL + + SELECT c.accno, c.description, c.category, + SUM(ac.sellprice * ac.allocated) * -1 AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + $dpt_join + WHERE $invwhere + AND ac.assemblyitem = '0' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + |; + } + + $query .= qq| + ORDER BY accno|; + + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + # prepare query for each account + $query = qq|SELECT (SELECT SUM(ac.amount) * -1 + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $dpt_where + $project + AND ac.amount < 0 + AND c.accno = ?) AS debit, + + (SELECT SUM(ac.amount) + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $dpt_where + $project + AND ac.amount > 0 + AND c.accno = ?) AS credit + |; + + if ($form->{accounttype} eq 'gifi') { + + $query = qq|SELECT (SELECT SUM(ac.amount) * -1 + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $dpt_where + $project + AND ac.amount < 0 + AND c.gifi_accno = ?) AS debit, + + (SELECT SUM(ac.amount) + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $dpt_where + $project + AND ac.amount > 0 + AND c.gifi_accno = ?) AS credit|; + + } + + $drcr = $dbh->prepare($query); + + # calculate debit and credit for the period + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $trb{$ref->{accno}}{description} = $ref->{description}; + $trb{$ref->{accno}}{charttype} = 'A'; + $trb{$ref->{accno}}{category} = $ref->{category}; + $trb{$ref->{accno}}{amount} += $ref->{amount}; + } + $sth->finish; + + my ($debit, $credit); + + foreach my $accno (sort keys %trb) { + $ref = (); + + $ref->{accno} = $accno; + map { $ref->{$_} = $trb{$accno}{$_} } qw(description category charttype amount); + + $ref->{balance} = $form->round_amount($balance{$ref->{accno}}, 2); + + if ($trb{$accno}{charttype} eq 'A') { + if ($project_id) { + + if ($ref->{amount} < 0) { + $ref->{debit} = $ref->{amount} * -1; + } else { + $ref->{credit} = $ref->{amount}; + } + next if $form->round_amount($ref->{amount}, 2) == 0; + + } else { + + # get DR/CR + $drcr->execute($ref->{accno}, $ref->{accno}); + + ($debit, $credit) = (0,0); + while (($debit, $credit) = $drcr->fetchrow_array) { + $ref->{debit} += $debit; + $ref->{credit} += $credit; + } + $drcr->finish; + + } + + $ref->{debit} = $form->round_amount($ref->{debit}, 2); + $ref->{credit} = $form->round_amount($ref->{credit}, 2); + + } + + # add subtotal + @accno = grep { $_ le "$ref->{accno}" } @headingaccounts; + $accno = pop @accno; + if ($accno) { + $trb{$accno}{debit} += $ref->{debit}; + $trb{$accno}{credit} += $ref->{credit}; + } + + push @{ $form->{TB} }, $ref; + + } + + $dbh->disconnect; + + # debits and credits for headings + foreach $accno (@headingaccounts) { + foreach $ref (@{ $form->{TB} }) { + if ($accno eq $ref->{accno}) { + $ref->{debit} = $trb{$accno}{debit}; + $ref->{credit} = $trb{$accno}{credit}; + } + } + } + +} + + +sub aging { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir'; + + ($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month}; + + $form->{todate} = $form->current_date($myconfig) unless ($form->{todate}); + + + my $where = "1 = 1"; + my $name; + my $null; + my $ref; + + if ($form->{"$form->{ct}_id"}) { + $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|; + } else { + if ($form->{$form->{ct}}) { + $name = $form->like(lc $form->{$form->{ct}}); + $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{$form->{ct}}; + } + } + + my $dpt_join; + if ($form->{department}) { + ($null, $department_id) = split /--/, $form->{department}; + $dpt_join = qq| + JOIN department d ON (a.department_id = d.id) + |; + + $where .= qq| AND a.department_id = $department_id|; + } + + # select outstanding vendors or customers, depends on $ct + my $query = qq|SELECT DISTINCT ct.id, ct.name, ct.language_code + FROM $form->{ct} ct + JOIN $form->{arap} a ON (a.$form->{ct}_id = ct.id) + $dpt_join + WHERE $where + AND a.paid != a.amount + AND (a.transdate <= '$form->{todate}') + ORDER BY ct.name|; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror; + + my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell'; + + my %interval = ( 'Pg' => { + 'c0' => "(date '$form->{todate}' - interval '0 days')", + 'c30' => "(date '$form->{todate}' - interval '30 days')", + 'c60' => "(date '$form->{todate}' - interval '60 days')", + 'c90' => "(date '$form->{todate}' - interval '90 days')" }, + 'DB2' => { + 'c0' => "(date ('$form->{todate}') - 0 days)", + 'c30' => "(date ('$form->{todate}') - 30 days)", + 'c60' => "(date ('$form->{todate}') - 60 days)", + 'c90' => "(date ('$form->{todate}') - 90 days)" } + ); + + $interval{Oracle} = $interval{PgPP} = $interval{Pg}; + + + # for each company that has some stuff outstanding + my $id; + while (($id, $null, $language_code) = $sth->fetchrow_array ) { + + $query = qq| + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, a.notes, + (a.amount - a.paid) as c0, 0.00 as c30, 0.00 as c60, 0.00 as c90, + a.duedate, a.invoice, a.id, + (SELECT $buysell FROM exchangerate e + WHERE a.curr = e.curr + AND e.transdate = a.transdate) AS exchangerate + FROM $form->{arap} a + JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id) + WHERE a.paid != a.amount + AND c.id = $id + AND ( + a.transdate <= $interval{$myconfig->{dbdriver}}{c0} + AND a.transdate >= $interval{$myconfig->{dbdriver}}{c30} + ) + + UNION + + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, a.notes, + 0.00 as c0, (a.amount - a.paid) as c30, 0.00 as c60, 0.00 as c90, + a.duedate, a.invoice, a.id, + (SELECT $buysell FROM exchangerate e + WHERE a.curr = e.curr + AND e.transdate = a.transdate) AS exchangerate + FROM $form->{arap} a + JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id) + WHERE a.paid != a.amount + AND c.id = $id + AND ( + a.transdate < $interval{$myconfig->{dbdriver}}{c30} + AND a.transdate >= $interval{$myconfig->{dbdriver}}{c60} + ) + + UNION + + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, a.notes, + 0.00 as c0, 0.00 as c30, (a.amount - a.paid) as c60, 0.00 as c90, + a.duedate, a.invoice, a.id, + (SELECT $buysell FROM exchangerate e + WHERE a.curr = e.curr + AND e.transdate = a.transdate) AS exchangerate + FROM $form->{arap} a + JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id) + WHERE a.paid != a.amount + AND c.id = $id + AND ( + a.transdate < $interval{$myconfig->{dbdriver}}{c60} + AND a.transdate >= $interval{$myconfig->{dbdriver}}{c90} + ) + + UNION + + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, a.notes, + 0.00 as c0, 0.00 as c30, 0.00 as c60, (a.amount - a.paid) as c90, + a.duedate, a.invoice, a.id, + (SELECT $buysell FROM exchangerate e + WHERE a.curr = e.curr + AND e.transdate = a.transdate) AS exchangerate + FROM $form->{arap} a + JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id) + WHERE a.paid != a.amount + AND c.id = $id + AND a.transdate < $interval{$myconfig->{dbdriver}}{c90} + + ORDER BY + + ctid, transdate, invnumber + + |; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap}; + $ref->{module} = 'ps' if $ref->{till}; + $ref->{exchangerate} = 1 unless $ref->{exchangerate}; + $ref->{language_code} = $language_code; + push @{ $form->{AG} }, $ref; + } + + $sth->finish; + + } + $sth->finish; + + # get language + my $query = qq|SELECT * + FROM language + ORDER BY 2|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_language} }, $ref; + } + $sth->finish; + + # disconnect + $dbh->disconnect; + +} + + +sub get_customer { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT name, email, cc, bcc + FROM $form->{ct} ct + WHERE ct.id = $form->{"$form->{ct}_id"}|; + ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $dbh->selectrow_array($query); + + $dbh->disconnect; + +} + + +sub get_taxaccounts { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + # get tax accounts + my $query = qq|SELECT c.accno, c.description, t.rate, c.link + FROM chart c, tax t + WHERE c.link LIKE '%CT_tax%' + AND c.id = t.chart_id + ORDER BY c.accno|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror; + + my $ref = (); + while ($ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{taxaccounts} }, $ref; + } + $sth->finish; + + # get gifi tax accounts + my $query = qq|SELECT DISTINCT g.accno, g.description, + sum(t.rate) AS rate + FROM gifi g, chart c, tax t + WHERE g.accno = c.gifi_accno + AND c.id = t.chart_id + AND c.link LIKE '%CT_tax%' + GROUP BY g.accno, g.description + ORDER BY accno|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror; + + while ($ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{gifi_taxaccounts} }, $ref; + } + $sth->finish; + + $dbh->disconnect; + +} + + + +sub tax_report { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my ($null, $department_id) = split /--/, $form->{department}; + + # build WHERE + my $where = "1 = 1"; + my $cashwhere = ""; + + if ($department_id) { + $where .= qq| + AND a.department_id = $department_id + |; + } + + my $query; + my $sth; + my $accno; + my $rate; + + if ($form->{accno}) { + if ($form->{accno} =~ /^gifi_/) { + ($null, $accno) = split /_/, $form->{accno}; + $rate = $form->{"$form->{accno}_rate"}; + $accno = qq| AND ch.gifi_accno = '$accno'|; + } else { + $accno = $form->{accno}; + $rate = $form->{"$form->{accno}_rate"}; + $accno = qq| AND ch.accno = '$accno'|; + } + } + $rate *= 1; + + my $table; + my $ARAP; + + if ($form->{db} eq 'ar') { + $table = "customer"; + $ARAP = "AR"; + } + if ($form->{db} eq 'ap') { + $table = "vendor"; + $ARAP = "AP"; + } + + my $transdate = "a.transdate"; + + ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + + # if there are any dates construct a where + if ($form->{fromdate} || $form->{todate}) { + if ($form->{fromdate}) { + $where .= " AND $transdate >= '$form->{fromdate}'"; + } + if ($form->{todate}) { + $where .= " AND $transdate <= '$form->{todate}'"; + } + } + + + if ($form->{method} eq 'cash') { + $transdate = "a.datepaid"; + + my $todate = ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig); + + $cashwhere = qq| + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%${ARAP}_paid%' + AND $transdate <= '$todate' + AND a.paid = a.amount + ) + |; + + } + + + my $ml = ($form->{db} eq 'ar') ? 1 : -1; + + my %ordinal = ( 'transdate' => 3, + 'invnumber' => 4, + 'name' => 5 + ); + + my @a = qw(transdate invnumber name); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + $rate = 1 unless $rate; + + if ($form->{summary}) { + + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + sum(ac.sellprice * ac.qty) * $rate * $ml AS tax, + a.till + FROM invoice ac + JOIN partstax pt ON (pt.parts_id = ac.parts_id) + JOIN chart ch ON (ch.id = pt.chart_id) + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + WHERE $where + $accno + AND a.invoice = '1' + $cashwhere + GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name, + a.till + |; + + if ($form->{fromdate}) { + # include open transactions from previous period + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + sum(ac.sellprice * ac.qty) * $rate * $ml AS tax, + a.till + FROM invoice ac + JOIN partstax pt ON (pt.parts_id = ac.parts_id) + JOIN chart ch ON (ch.id = pt.chart_id) + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '1' + $cashwhere + GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name, + a.till + |; + } + } + + + } else { + + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + i.sellprice * i.qty * $ml AS netamount, + i.sellprice * i.qty * $rate * $ml AS tax, + i.description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + JOIN invoice i ON (i.trans_id = a.id) + JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id) + WHERE $where + $accno + AND a.invoice = '1' + $cashwhere + |; + + if ($form->{fromdate}) { + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + i.sellprice * i.qty * $ml AS netamount, + i.sellprice * i.qty * $rate * $ml AS tax, + i.description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + JOIN invoice i ON (i.trans_id = a.id) + JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '1' + $cashwhere + |; + } + } + } + + + if ($form->{report} =~ /nontaxable/) { + + if ($form->{summary}) { + # only gather up non-taxable transactions + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, a.till + |; + + if ($form->{fromdate}) { + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, a.till + |; + } + } + + } else { + + # gather up details for non-taxable transactions + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.notes, a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + ac.description, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, + ac.description, a.till + |; + + if ($form->{fromdate}) { + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.notes, a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + ac.description, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, + ac.description, a.till + |; + } + } + + } + } + + + $query .= qq| + ORDER by $sortorder|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) { + $ref->{tax} = $form->round_amount($ref->{tax}, 2); + push @{ $form->{TR} }, $ref if $ref->{netamount} != 0; + } + + $sth->finish; + $dbh->disconnect; + +} + + +sub paymentaccounts { + my ($self, $myconfig, $form) = @_; + + # connect to database, turn AutoCommit off + my $dbh = $form->dbconnect_noauto($myconfig); + + my $ARAP = uc $form->{db}; + + # get A(R|P)_paid accounts + my $query = qq|SELECT accno, description + FROM chart + WHERE link LIKE '%${ARAP}_paid%' + ORDER BY accno|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{PR} }, $ref; + } + $sth->finish; + + $form->all_years($dbh, $myconfig); + + $dbh->disconnect; + +} + + +sub payments { + my ($self, $myconfig, $form) = @_; + + # connect to database, turn AutoCommit off + my $dbh = $form->dbconnect_noauto($myconfig); + + my $ml = 1; + if ($form->{db} eq 'ar') { + $table = 'customer'; + $ml = -1; + } + if ($form->{db} eq 'ap') { + $table = 'vendor'; + } + + + my $query; + my $sth; + my $dpt_join; + my $where; + my $var; + + if ($form->{department_id}) { + $dpt_join = qq| + JOIN dpt_trans t ON (t.trans_id = ac.trans_id) + |; + + $where = qq| + AND t.department_id = $form->{department_id} + |; + } + + ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + + if ($form->{fromdate}) { + $where .= " AND ac.transdate >= '$form->{fromdate}'"; + } + if ($form->{todate}) { + $where .= " AND ac.transdate <= '$form->{todate}'"; + } + if (!$form->{fx_transaction}) { + $where .= " AND ac.fx_transaction = '0'"; + } + + if ($form->{description}) { + $var = $form->like(lc $form->{description}); + $where .= " AND lower(c.name) LIKE '$var'"; + } + if ($form->{source}) { + $var = $form->like(lc $form->{source}); + $where .= " AND lower(ac.source) LIKE '$var'"; + } + if ($form->{memo}) { + $var = $form->like(lc $form->{memo}); + $where .= " AND lower(ac.memo) LIKE '$var'"; + } + + my %ordinal = ( 'name' => 1, + 'transdate' => 2, + 'source' => 4, + 'employee' => 6, + 'till' => 7 + ); + + my @a = qw(name transdate employee); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $glwhere = $where; + $glwhere =~ s/\(c.name\)/\(g.description\)/; + + # cycle through each id + foreach my $accno (split(/ /, $form->{paymentaccounts})) { + + $query = qq|SELECT id, accno, description + FROM chart + WHERE accno = '$accno'|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + push @{ $form->{PR} }, $ref; + $sth->finish; + + $query = qq|SELECT c.name, ac.transdate, sum(ac.amount) * $ml AS paid, + ac.source, ac.memo, e.name AS employee, a.till, a.curr + FROM acc_trans ac + JOIN $form->{db} a ON (ac.trans_id = a.id) + JOIN $table c ON (c.id = a.${table}_id) + LEFT JOIN employee e ON (a.employee_id = e.id) + $dpt_join + WHERE ac.chart_id = $ref->{id} + $where|; + + if ($form->{till}) { + $query .= " AND a.invoice = '1' + AND NOT a.till IS NULL"; + + if ($myconfig->{role} eq 'user') { + $query .= " AND e.login = '$form->{login}'"; + } + } + + $query .= qq| + GROUP BY c.name, ac.transdate, ac.source, ac.memo, + e.name, a.till, a.curr + |; + + if (! $form->{till}) { +# don't need gl for a till + + $query .= qq| + UNION + SELECT g.description, ac.transdate, sum(ac.amount) * $ml AS paid, ac.source, + ac.memo, e.name AS employee, '' AS till, '' AS curr + FROM acc_trans ac + JOIN gl g ON (g.id = ac.trans_id) + LEFT JOIN employee e ON (g.employee_id = e.id) + $dpt_join + WHERE ac.chart_id = $ref->{id} + $glwhere + AND (ac.amount * $ml) > 0 + GROUP BY g.description, ac.transdate, ac.source, ac.memo, e.name + |; + + } + + $query .= qq| + ORDER BY $sortorder|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $pr = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{$ref->{id}} }, $pr; + } + $sth->finish; + + } + + $dbh->disconnect; + +} + + +1; + + |