X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2Fold%2Fsql-ledger%2FSL%2FRP.pm;fp=sql-ledger%2Fold%2Fsql-ledger%2FSL%2FRP.pm;h=0000000000000000000000000000000000000000;hp=3f07bb52512bffb92fb3810273fc369c2b0c8ce5;hb=0554f5dec1d99c9be70b2a0b841b5327db917dbe;hpb=ac9a5336dd181bc617710a09effc3efa1a0d5932 diff --git a/sql-ledger/old/sql-ledger/SL/RP.pm b/sql-ledger/old/sql-ledger/SL/RP.pm deleted file mode 100644 index 3f07bb525..000000000 --- a/sql-ledger/old/sql-ledger/SL/RP.pm +++ /dev/null @@ -1,1310 +0,0 @@ -#===================================================================== -# 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; - -