X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2FSL%2FRP.pm;fp=sql-ledger%2FSL%2FRP.pm;h=0000000000000000000000000000000000000000;hp=791b22bbab90a1e5fde454345b44d0d72a4490a4;hb=86b1b489a4ed2f9bc0cba6cafeab0d6eca5584dc;hpb=948b8acdd4b9b3864342062d0c397a11f57c5700 diff --git a/sql-ledger/SL/RP.pm b/sql-ledger/SL/RP.pm deleted file mode 100644 index 791b22bba..000000000 --- a/sql-ledger/SL/RP.pm +++ /dev/null @@ -1,2551 +0,0 @@ -#===================================================================== -# SQL-Ledger Accounting -# Copyright (C) 2001 -# -# Author: Dieter Simader -# Email: dsimader@sql-ledger.org -# Web: http://www.sql-ledger.org -# -# Contributors: Benjamin Lee -# Jim Rawlings -# -# 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; - -