From ac5b087ea2d9ba7428d367aaeb288534158fee9a Mon Sep 17 00:00:00 2001 From: christopherm Date: Fri, 1 Sep 2006 01:16:38 +0000 Subject: Initial Import git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/ledger-smb@1 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/RP.pm | 2103 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 2103 insertions(+) create mode 100755 LedgerSMB/RP.pm (limited to 'LedgerSMB/RP.pm') diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm new file mode 100755 index 00000000..946d4c69 --- /dev/null +++ b/LedgerSMB/RP.pm @@ -0,0 +1,2103 @@ +#===================================================================== +# LedgerSMB +# Small Medium Business Accounting software +# +# See COPYRIGHT file for copyright information +#====================================================================== +# +# This file has NOT undergone whitespace cleanup. +# +#====================================================================== +# +# 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; + $ath->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 L Q); + + my $null; + + if ($form->{asofdate}) { + if ($form->{asofyear} && $form->{asofmonth}) { + if ($form->{asofdate} !~ /\W/) { + $form->{asofdate} = "$form->{asofyear}$form->{asofmonth}$form->{asofdate}"; + } + } + } else { + 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}) { + if ($form->{compareasofdate} !~ /\W/) { + $form->{compareasofdate} = "$form->{compareasofyear}$form->{compareasofmonth}$form->{compareasofdate}"; + } + } + } else { + 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 (@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}); + + $form->{total_equity_last_period} = $form->format_amount($myconfig, $form->{total_equity_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_equity_last_period}); + + $form->{total_assets_last_period} = $form->format_amount($myconfig, $form->{total_assets_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_assets_last_period}); + + $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, $excludeyearend) = @_; + + 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 $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 ($form->{method} eq 'cash' && !$todate) { + ($todate) = $dbh->selectrow_array(qq|SELECT current_date FROM defaults|); + } + + if ($fromdate) { + if ($form->{method} eq 'cash') { + $subwhere .= " AND transdate >= '$fromdate'"; + $glwhere = " AND ac.transdate >= '$fromdate'"; + } else { + $where .= " AND ac.transdate >= '$fromdate'"; + } + } + + if ($todate) { + $where .= " AND ac.transdate <= '$todate'"; + $subwhere .= " AND transdate <= '$todate'"; + $yearendwhere = "ac.transdate < '$todate'"; + } + + if ($excludeyearend) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend)"; + + if ($todate) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend + WHERE transdate <= '$todate')"; + } + + 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 ($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 ($excludeyearend) { + + # 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 gl a ON (a.id = y.trans_id) + 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.gifi_accno) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where + $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_where + $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_where + $category + AND c.gifi_accno = '' + $project + GROUP BY c.category + |; + + if ($excludeyearend) { + + # 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 gl a ON (a.id = y.trans_id) + 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.gifi_accno) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $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_where + $category + AND NOT (c.link = 'AR' OR c.link = 'AP') + $project + GROUP BY c.accno, c.description, c.category + |; + + if ($excludeyearend) { + + # 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 gl a ON (a.id = y.trans_id) + 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 + |; + } + + } 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 ($excludeyearend) { + + # 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 gl a ON (a.id = y.trans_id) + 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 + |; + } + } + } + + my @accno; + my $accno; + my $ref; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + + # 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 + |; + } + + + 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, c.contra + 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, c.contra + |; + + } else { + + $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, + c.description, c.contra + 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, c.contra + |; + + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + $ref->{amount} = $form->round_amount($ref->{amount}, 2); + $balance{$ref->{accno}} = $ref->{amount}; + + if ($form->{all_accounts}) { + $trb{$ref->{accno}}{description} = $ref->{description}; + $trb{$ref->{accno}}{charttype} = 'A'; + $trb{$ref->{accno}}{category} = $ref->{category}; + $trb{$ref->{accno}}{contra} = $ref->{contra}; + } + + } + $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, c.contra + 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}; + $trb{$ref->{accno}}{contra} = $ref->{contra}; + + 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, c.contra + 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, c.contra + ORDER BY accno|; + + } else { + + $query = qq|SELECT c.accno, c.description, c.category, + SUM(ac.amount) AS amount, c.contra + 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, c.contra + 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}}{contra} = $ref->{contra}; + $trb{$ref->{accno}}{amount} += $ref->{amount}; + } + $sth->finish; + + my ($debit, $credit); + + foreach my $accno (sort keys %trb) { + $ref = (); + + $ref->{accno} = $accno; + for (qw(description category contra charttype amount)) { $ref->{$_} = $trb{$accno}{$_} } + + $ref->{balance} = $balance{$ref->{accno}}; + + 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); + + if (!$form->{all_accounts}) { + next if $form->round_amount($ref->{debit} + $ref->{credit}, 2) == 0; + } + } + + # 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'; + + my $query = qq|SELECT curr FROM defaults|; + ($form->{currencies}) = $dbh->selectrow_array($query); + + ($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month}; + + if (! $form->{todate}) { + $query = qq|SELECT current_date FROM defaults|; + ($form->{todate}) = $dbh->selectrow_array($query); + } + + my $where = "1 = 1"; + my $name; + my $null; + my $ref; + my $transdate = ($form->{overdue}) ? "duedate" : "transdate"; + + if ($form->{"$form->{ct}_id"}) { + $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|; + } else { + if ($form->{$form->{ct}} ne "") { + $name = $form->like(lc $form->{$form->{ct}}); + $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{$form->{ct}}; + } + } + + if ($form->{department}) { + ($null, $department_id) = split /--/, $form->{department}; + $where .= qq| AND a.department_id = $department_id|; + } + + # select outstanding vendors or customers, depends on $ct + $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) + 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 @ot = (); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @ot, $ref; + } + $sth->finish; + + 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 + $form->{currencies} ||= ":"; + + + $where = qq| + a.paid != a.amount + AND c.id = ? + AND a.curr = ?|; + + if ($department_id) { + $where .= qq| AND a.department_id = $department_id|; + } + + $query = ""; + my $union = ""; + + if ($form->{c0}) { + $query .= qq| + SELECT c.id AS ctid, c.$form->{ct}number, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as $form->{ct}phone, c.fax as $form->{ct}fax, + c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber, + a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, 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, a.curr, + (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 $where + AND ( + a.$transdate <= $interval{$myconfig->{dbdriver}}{c0} + AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c30} + ) +|; + + $union = qq| + UNION +|; + + } + + if ($form->{c30}) { + + $query .= qq| + + $union + + SELECT c.id AS ctid, c.$form->{ct}number, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as $form->{ct}phone, c.fax as $form->{ct}fax, + c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber, + a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, 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, a.curr, + (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 $where + AND ( + a.$transdate < $interval{$myconfig->{dbdriver}}{c30} + AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c60} + ) +|; + + $union = qq| + UNION +|; + + } + + if ($form->{c60}) { + + $query .= qq| + + $union + + SELECT c.id AS ctid, c.$form->{ct}number, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as $form->{ct}phone, c.fax as $form->{ct}fax, + c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber, + a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, 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, a.curr, + (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 $where + AND ( + a.$transdate < $interval{$myconfig->{dbdriver}}{c60} + AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c90} + ) +|; + + $union = qq| + UNION +|; + + } + + if ($form->{c90}) { + + $query .= qq| + + $union + + SELECT c.id AS ctid, c.$form->{ct}number, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as $form->{ct}phone, c.fax as $form->{ct}fax, + c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber, + a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, 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, a.curr, + (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 $where + AND a.$transdate < $interval{$myconfig->{dbdriver}}{c90} +|; + } + + $query .= qq| + + ORDER BY ctid, $transdate, invnumber|; + + $sth = $dbh->prepare($query) || $form->dberror($query); + + my @var = (); + + if ($form->{c0} + $form->{c30} + $form->{c60} + $form->{c90}) { + foreach $curr (split /:/, $form->{currencies}) { + + foreach $item (@ot) { + + @var = (); + for (qw(c0 c30 c60 c90)) { push @var, ($item->{id}, $curr) if $form->{$_} } + + $sth->execute(@var); + + 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} = $item->{language_code}; + push @{ $form->{AG} }, $ref; + } + $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); + my $ARAP = uc $form->{db}; + + # get tax accounts + my $query = qq|SELECT DISTINCT c.accno, c.description + FROM chart c + JOIN tax t ON (c.id = t.chart_id) + WHERE c.link LIKE '%${ARAP}_tax%' + 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 + FROM gifi g + JOIN chart c ON (c.gifi_accno= g.accno) + JOIN tax t ON (c.id = t.chart_id) + WHERE c.link LIKE '%${ARAP}_tax%' + 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; + + if ($form->{accno}) { + if ($form->{accno} =~ /^gifi_/) { + ($null, $accno) = split /_/, $form->{accno}; + $accno = qq| AND ch.gifi_accno = '$accno'|; + } else { + $accno = $form->{accno}; + $accno = qq| AND ch.accno = '$accno'|; + } + } + + 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}; + if (! $todate) { + ($todate) = $dbh->selectrow_array(qq|SELECT current_date FROM defaults|); + } + + $cashwhere = qq| + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = chart.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); + + if ($form->{summary}) { + + $query = qq|SELECT a.id, a.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 + $cashwhere + |; + + if ($form->{fromdate}) { + # include open transactions from previous period + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, a.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 + $cashwhere + |; + } + } + + + } 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 * $ml * + (SELECT tx.rate FROM tax tx WHERE tx.chart_id = ch.id AND (tx.validto > $transdate OR tx.validto IS NULL) ORDER BY validto LIMIT 1) 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 * $ml * + (SELECT tx.rate FROM tax tx WHERE tx.chart_id = ch.id AND (tx.validto > $transdate OR tx.validto IS NULL) ORDER BY validto LIMIT 1) 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 DISTINCT a.id, a.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.netamount = a.amount + $cashwhere + |; + + if ($form->{fromdate}) { + if ($cashwhere) { + $query .= qq| + UNION + + SELECT DISTINCT a.id, a.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.netamount = a.amount + $cashwhere + |; + } + } + + } 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); + if ($form->{report} =~ /nontaxable/) { + push @{ $form->{TR} }, $ref if $ref->{netamount}; + } else { + push @{ $form->{TR} }, $ref if $ref->{tax}; + } + } + + $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($myconfig, $dbh); + + $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} ne "") { + $var = $form->like(lc $form->{description}); + $where .= " AND lower(c.name) LIKE '$var'"; + } + if ($form->{source} ne "") { + $var = $form->like(lc $form->{source}); + $where .= " AND lower(ac.source) LIKE '$var'"; + } + if ($form->{memo} ne "") { + $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} ne "") { + $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} eq "") { +# 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; + + -- cgit v1.2.3