#===================================================================== # SQL-Ledger Accounting # Copyright (C) 1998-2002 # # Author: Dieter Simader # Email: dsimader@sql-ledger.org # Web: http://www.sql-ledger.org # # Contributors: Benjamin Lee # # 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 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; &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories); # 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); } # 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 L Q); # 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); # if there are any compare dates if ($form->{compareasofdate}) { $last_period = 1; &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories); $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' => 'equities', 'ml' => 1 } ); foreach $category (@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}); # calculate retained 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}); # 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) = @_; my $query; my $where = "WHERE 1 = 1"; my $subwhere; 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; $where .= " AND ac.transdate >= '$fromdate'" if $fromdate; if ($todate) { $where .= " AND ac.transdate <= '$todate'"; $subwhere = " AND transdate <= '$todate'"; } if ($form->{project_id}) { $project = qq| AND ac.project_id = $form->{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) $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 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) $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 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) $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 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) $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 -- 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) $where $category AND NOT (c.link = 'AR' OR c.link = 'AP') $project GROUP BY g.accno, g.description, c.category UNION 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) $where $category AND c.gifi_accno = '' AND NOT (c.link = 'AR' OR c.link = 'AP') $project GROUP BY c.category |; } else { $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) $where $category $project GROUP BY g.accno, g.description, c.category UNION 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) $where $category AND c.gifi_accno = '' $project GROUP by c.category |; } } else { 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) $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 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) $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 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) $where $category AND NOT (c.link = 'AR' OR c.link = 'AP') $project GROUP BY c.accno, c.description, c.category |; } else { $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) $where $category $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) { 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_details { my ($self, $myconfig, $form) = @_; my $dbh = $form->dbconnect($myconfig); my ($query, $sth, $ref); my %balance = (); my %trb = (); my $where = "WHERE 1 = 1"; if ($form->{project_id}) { $where .= qq| AND a.project_id = $form->{project_id} |; } # get beginning balances if ($form->{fromdate}) { if ($form->{accounttype} eq 'gifi') { $query = qq|SELECT g.accno, c.category, SUM(a.amount) AS amount, g.description FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) JOIN gifi g ON (c.gifi_accno = g.accno) $where AND a.transdate < '$form->{fromdate}' GROUP BY g.accno, c.category, g.description |; } else { $query = qq|SELECT c.accno, c.category, SUM(a.amount) AS amount, c.description FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) $where AND a.transdate < '$form->{fromdate}' 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); my @headingaccounts = (); 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 a.transdate >= '$form->{fromdate}'"; } if ($form->{todate}) { $where .= " AND a.transdate <= '$form->{todate}'"; } } if ($form->{accounttype} eq 'gifi') { $query = qq|SELECT g.accno, g.description, c.category, SUM(a.amount) AS amount FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) JOIN gifi g ON (c.gifi_accno = g.accno) $where GROUP BY g.accno, g.description, c.category UNION SELECT '' AS accno, '' AS description, c.category, SUM(a.amount) AS amount FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) $where AND c.gifi_accno = '' GROUP BY c.category ORDER BY accno|; } else { $query = qq|SELECT c.accno, c.description, c.category, SUM(a.amount) AS amount FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) $where GROUP BY c.accno, c.description, c.category ORDER BY accno|; } $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); # prepare query for each account $query = qq|SELECT (SELECT SUM(a.amount) * -1 FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) $where AND a.amount < 0 AND c.accno = ?) AS debit, (SELECT SUM(a.amount) FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) $where AND a.amount > 0 AND c.accno = ?) AS credit |; if ($form->{accounttype} eq 'gifi') { $query = qq|SELECT (SELECT SUM(a.amount) * -1 FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) $where AND a.amount < 0 AND c.gifi_accno = ?) AS debit, (SELECT SUM(a.amount) FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) $where AND a.amount > 0 AND c.gifi_accno = ?) AS credit|; } $drcr = $dbh->prepare($query); # calculate the debit and credit in 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') { # get DR/CR $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query); ($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'; $form->{todate} = $form->current_date($myconfig) unless ($form->{todate}); my $where = "1 = 1"; my $name; 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}}; } } # select outstanding vendors or customers, depends on $ct my $query = qq|SELECT DISTINCT ct.id, ct.name FROM $form->{ct} ct, $form->{arap} a WHERE $where AND a.$form->{ct}_id = ct.id 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'; # for each company that has some stuff outstanding while ( my ($id) = $sth->fetchrow_array ) { $query = qq| -- between 0-30 days SELECT $form->{ct}.id AS ctid, $form->{ct}.name, addr1, addr2, addr3, addr4, contact, phone as customerphone, fax as customerfax, $form->{ct}number, "invnumber", "transdate", (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90", "duedate", invoice, $form->{arap}.id, (SELECT $buysell FROM exchangerate WHERE $form->{arap}.curr = exchangerate.curr AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate FROM $form->{arap}, $form->{ct} WHERE paid != amount AND $form->{arap}.$form->{ct}_id = $form->{ct}.id AND $form->{ct}.id = $id AND ( transdate <= (date '$form->{todate}' - interval '0 days') AND transdate >= (date '$form->{todate}' - interval '30 days') ) UNION -- between 31-60 days SELECT $form->{ct}.id AS ctid, $form->{ct}.name, addr1, addr2, addr3, addr4, contact, phone as customerphone, fax as customerfax, $form->{ct}number, "invnumber", "transdate", 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90", "duedate", invoice, $form->{arap}.id, (SELECT $buysell FROM exchangerate WHERE $form->{arap}.curr = exchangerate.curr AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate FROM $form->{arap}, $form->{ct} WHERE paid != amount AND $form->{arap}.$form->{ct}_id = $form->{ct}.id AND $form->{ct}.id = $id AND ( transdate < (date '$form->{todate}' - interval '30 days') AND transdate >= (date '$form->{todate}' - interval '60 days') ) UNION -- between 61-90 days SELECT $form->{ct}.id AS ctid, $form->{ct}.name, addr1, addr2, addr3, addr4, contact, phone as customerphone, fax as customerfax, $form->{ct}number, "invnumber", "transdate", 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90", "duedate", invoice, $form->{arap}.id, (SELECT $buysell FROM exchangerate WHERE $form->{arap}.curr = exchangerate.curr AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate FROM $form->{arap}, $form->{ct} WHERE paid != amount AND $form->{arap}.$form->{ct}_id = $form->{ct}.id AND $form->{ct}.id = $id AND ( transdate < (date '$form->{todate}' - interval '60 days') AND transdate >= (date '$form->{todate}' - interval '90 days') ) UNION -- over 90 days SELECT $form->{ct}.id AS ctid, $form->{ct}.name, addr1, addr2, addr3, addr4, contact, phone as customerphone, fax as customerfax, $form->{ct}number, "invnumber", "transdate", 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90", "duedate", invoice, $form->{arap}.id, (SELECT $buysell FROM exchangerate WHERE $form->{arap}.curr = exchangerate.curr AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate FROM $form->{arap}, $form->{ct} WHERE paid != amount AND $form->{arap}.$form->{ct}_id = $form->{ct}.id AND $form->{ct}.id = $id AND transdate < (date '$form->{todate}' - interval '90 days') ORDER BY ctid, invnumber, transdate |; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap}; $ref->{exchangerate} = 1 unless $ref->{exchangerate}; push @{ $form->{AG} }, $ref; } $sth->finish; } $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"}|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror; ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $sth->fetchrow_array; $sth->finish; $dbh->disconnect; } sub get_taxaccounts { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); # get tax accounts my $query = qq|SELECT accno, description FROM chart WHERE link LIKE '%CT_tax%' ORDER BY accno|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror; while ( my ($accno, $description) = $sth->fetchrow_array ) { push @{ $form->{taxaccounts} }, "$accno--$description"; } $sth->finish; # get gifi tax accounts my $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description FROM gifi g, chart c WHERE g.accno = c.gifi_accno AND c.link LIKE '%CT_tax%' ORDER BY accno|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror; while ( my ($accno, $description) = $sth->fetchrow_array ) { push @{ $form->{gifi_taxaccounts} }, "$accno--$description"; } $sth->finish; $dbh->disconnect; } sub tax_report { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); # build WHERE my $where = qq|WHERE ac.trans_id = a.id AND ac.chart_id = ch.id|; if ($form->{accno} =~ /^gifi_/) { my ($null, $accno) = split /_/, $form->{accno}; $where .= qq| AND ch.gifi_accno = '$accno'|; } else { $where .= qq| AND ch.accno = '$form->{accno}'|; } my $table; if ($form->{db} eq 'ar') { $where .= " AND n.id = a.customer_id"; $table = "customer"; } if ($form->{db} eq 'ap') { $where .= " AND n.id = a.vendor_id"; $table = "vendor"; } my $transdate = ($form->{cashbased}) ? "a.datepaid" : "ac.transdate"; if ($form->{cashbased}) { $where .= " AND a.amount = a.paid"; } # 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}'"; } } my $query = qq|SELECT a.id, a.invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount,|; my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name)); $sortorder = $form->{sort} unless $sortorder; if ($form->{db} eq 'ar') { $query .= " ac.amount AS tax"; } if ($form->{db} eq 'ap') { $query .= " ac.amount * -1 AS tax"; } $query .= qq| FROM acc_trans ac, "$form->{db}" a, "$table" n, chart ch $where ORDER by $sortorder|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{TR} }, $ref; } $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}; $arap .= "_paid"; # get A(R|P)_paid accounts my $query = qq|SELECT accno, description FROM chart WHERE link LIKE '%$arap%'|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{PR} }, $ref; } $sth->finish; $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, $sth); # 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, a.invnumber, a.ordnumber, ac.transdate, ac.amount * $ml AS paid, ac.source, a.invoice, a.id, '$form->{db}' AS module FROM $table c, acc_trans ac, $form->{db} a WHERE c.id = a.${table}_id AND ac.trans_id = a.id AND ac.chart_id = $ref->{id}|; $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate}; $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate}; $query .= qq| UNION SELECT g.description, g.reference, NULL AS ordnumber, ac.transdate, ac.amount * $ml AS paid, ac.source, '0' as invoice, g.id, 'gl' AS module FROM gl g, acc_trans ac WHERE g.id = ac.trans_id AND ac.chart_id = $ref->{id} AND (ac.amount * $ml) > 0 |; $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate}; $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate}; my $sortorder = join ', ', $form->sort_columns(qw(name invnumber ordnumber transdate source)); $query .= " 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;