#=====================================================================
# SQL-Ledger Accounting
-# Copyright (C) 1998-2002
+# Copyright (C) 2001
#
# Author: Dieter Simader
# Email: dsimader@sql-ledger.org
# Web: http://www.sql-ledger.org
#
# Contributors: Benjamin Lee <benjaminlee@consultant.com>
+# Jim Rawlings <jim@your-dba.com>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
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) = @_;
$form->{decimalplaces} *= 1;
- &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories);
+ 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);
+ &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories, 1);
}
}
-
+
# 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});
}
-
sub balance_sheet {
my ($self, $myconfig, $form) = @_;
my $dbh = $form->dbconnect($myconfig);
my $last_period = 0;
- my @categories = qw(A L Q);
+ 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->{decimalplaces} *= 1;
- &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories);
+ &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);
+ &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories, 1);
$form->{last_period} = "$form->{compareasofdate}";
'labels' => 'liabilities',
'ml' => 1 },
'Q' => { 'label' => 'equity',
- 'labels' => 'equities',
+ 'labels' => 'equity',
'ml' => 1 }
);
- foreach $category (@categories) {
+ foreach $category (grep { !/C/ } @categories) {
foreach $key (sort keys %{ $form->{$category} }) {
# 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 retained earnings
+ # 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}, "- "));
# 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};
$form->{total_liabilities_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period}, $form->{decimalplaces}, "- ");
$form->{total_equity_this_period} = $form->format_amount($myconfig, $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
-
-}
+}
sub get_accounts {
- my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_;
+ my ($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 $where = "WHERE 1 = 1";
- my $subwhere;
+ 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 (";
$sth->finish;
- $where .= " AND ac.transdate >= '$fromdate'" if $fromdate;
+ 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'";
- $subwhere = " AND 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 ($form->{project_id})
+ if ($project_id)
{
$project = qq|
- AND ac.project_id = $form->{project_id}
+ AND ac.project_id = $project_id
|;
}
JOIN chart c ON (c.id = ac.chart_id)
JOIN ar a ON (a.id = ac.trans_id)
JOIN gifi g ON (g.accno = c.gifi_accno)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_where
$category
AND ac.trans_id IN
(
$project
GROUP BY g.accno, g.description, c.category
- UNION
+ 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)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_where
$category
AND c.gifi_accno = ''
AND ac.trans_id IN
$project
GROUP BY c.category
- UNION
+ UNION ALL
SELECT g.accno, sum(ac.amount) AS amount,
g.description, c.category
JOIN chart c ON (c.id = ac.chart_id)
JOIN ap a ON (a.id = ac.trans_id)
JOIN gifi g ON (g.accno = c.gifi_accno)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_where
$category
AND ac.trans_id IN
(
$project
GROUP BY g.accno, g.description, c.category
- UNION
+ 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)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_where
$category
AND c.gifi_accno = ''
AND ac.trans_id IN
$project
GROUP BY c.category
- UNION
+ UNION ALL
-- add gl
JOIN chart c ON (c.id = ac.chart_id)
JOIN gifi g ON (g.accno = c.gifi_accno)
JOIN gl a ON (a.id = ac.trans_id)
- $where
+ $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
+ 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)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $glwhere
+ $dpt_where
$category
AND c.gifi_accno = ''
AND NOT (c.link = 'AR' OR c.link = 'AP')
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,
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN gifi g ON (c.gifi_accno = g.accno)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_from
$category
$project
GROUP BY g.accno, g.description, c.category
- UNION
+ 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)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_from
$category
AND c.gifi_accno = ''
$project
- GROUP by c.category
+ 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 {
+ } else { # standard account
if ($form->{method} eq 'cash')
{
-
$query = qq|
SELECT c.accno, sum(ac.amount) AS amount,
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN ar a ON (a.id = ac.trans_id)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_where
$category
AND ac.trans_id IN
(
$project
GROUP BY c.accno, c.description, c.category
- UNION
+ 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)
- $where
+ $dpt_join
+ WHERE $where
+ $ywhere
+ $dpt_where
$category
AND ac.trans_id IN
(
$project
GROUP BY c.accno, c.description, c.category
- UNION
+ 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)
- $where
+ $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
|;
-
- } else {
-
- $query = qq|
-
- SELECT c.accno, sum(ac.amount) AS amount,
+
+ if ($yearend) {
+
+ # this is for the yearend
+
+ $query .= qq|
+
+ UNION ALL
+
+ SELECT c.accno, sum(ac.amount) AS amount,
c.description, c.category
- FROM acc_trans ac
+ FROM yearend y
+ JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
JOIN chart c ON (c.id = ac.chart_id)
- $where
- $category
+ $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
+ )
- my @accno;
- my $accno;
- my $ref;
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $project
+ GROUP BY c.accno, c.description, c.category
- while ($ref = $sth->fetchrow_hashref(NAME_lc))
- {
+ 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) {
+ if ($accno && ($accno ne $ref->{accno}) ) {
if ($last_period)
{
$form->{$ref->{category}}{$accno}{last} += $ref->{amount};
-sub trial_balance_details {
+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 = "WHERE 1 = 1";
+ my $where = "1 = 1";
+ my $invwhere = $where;
+
+ ($null, $department_id) = split /--/, $form->{department};
+ ($null, $project_id) = split /--/, $form->{projectnumber};
- if ($form->{project_id}) {
- $where .= qq|
- AND a.project_id = $form->{project_id}
+ 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(a.amount) AS amount,
+ $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
g.description
- FROM acc_trans a
- JOIN chart c ON (a.chart_id = c.id)
+ FROM acc_trans ac
+ JOIN chart c ON (ac.chart_id = c.id)
JOIN gifi g ON (c.gifi_accno = g.accno)
- $where
- AND a.transdate < '$form->{fromdate}'
+ $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(a.amount) AS amount,
+ $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
c.description
- FROM acc_trans a
- JOIN chart c ON (a.chart_id = c.id)
- $where
- AND a.transdate < '$form->{fromdate}'
+ 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 = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- my @headingaccounts = ();
while ($ref = $sth->fetchrow_hashref(NAME_lc))
{
$trb{$ref->{accno}}{description} = $ref->{description};
if ($form->{fromdate} || $form->{todate}) {
if ($form->{fromdate}) {
- $where .= " AND a.transdate >= '$form->{fromdate}'";
+ $where .= " AND ac.transdate >= '$form->{fromdate}'";
+ $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
}
if ($form->{todate}) {
- $where .= " AND a.transdate <= '$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(a.amount) AS amount
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
+ 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)
- $where
+ $dpt_join
+ WHERE $where
+ $dpt_where
+ $project
GROUP BY g.accno, g.description, c.category
-
- UNION
+ |;
+
+ if ($project_id) {
- SELECT '' AS accno, '' AS description, c.category,
- SUM(a.amount) AS amount
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- $where
- AND c.gifi_accno = ''
- GROUP BY c.category
+ $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(a.amount) AS amount
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- $where
+ 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(a.amount) * -1
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- $where
- AND a.amount < 0
+ $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(a.amount)
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- $where
- AND a.amount > 0
+
+ (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(a.amount) * -1
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- $where
- AND a.amount < 0
+ $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(a.amount)
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- $where
- AND a.amount > 0
+
+ (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 the debit and credit in the period
+ $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;
foreach my $accno (sort keys %trb) {
$ref = ();
-
+
$ref->{accno} = $accno;
map { $ref->{$_} = $trb{$accno}{$_} } qw(description category charttype amount);
$ref->{balance} = $form->round_amount($balance{$ref->{accno}}, 2);
if ($trb{$accno}{charttype} eq 'A') {
- # get DR/CR
- $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query);
-
- ($debit, $credit) = (0,0);
- while (($debit, $credit) = $drcr->fetchrow_array) {
- $ref->{debit} += $debit;
- $ref->{credit} += $credit;
+ 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;
+
}
- $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;
$trb{$accno}{debit} += $ref->{debit};
$trb{$accno}{credit} += $ref->{credit};
}
-
+
push @{ $form->{TB} }, $ref;
}
}
-
sub aging {
my ($self, $myconfig, $form) = @_;
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"}|;
}
}
+ 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
- FROM $form->{ct} ct, $form->{arap} a
+ 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.$form->{ct}_id = ct.id
AND a.paid != a.amount
AND (a.transdate <= '$form->{todate}')
ORDER BY ct.name|;
$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
- while ( my ($id) = $sth->fetchrow_array ) {
+ my $id;
+ while (($id, $null, $language_code) = $sth->fetchrow_array ) {
$query = qq|
-
--- between 0-30 days
-
- SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
- addr1, addr2, addr3, addr4, contact,
- phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
- (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90",
- "duedate", invoice, $form->{arap}.id,
- (SELECT $buysell FROM exchangerate
- WHERE $form->{arap}.curr = exchangerate.curr
- AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
- WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
- AND $form->{ct}.id = $id
+ 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 (
- transdate <= (date '$form->{todate}' - interval '0 days')
- AND transdate >= (date '$form->{todate}' - interval '30 days')
+ a.transdate <= $interval{$myconfig->{dbdriver}}{c0}
+ AND a.transdate >= $interval{$myconfig->{dbdriver}}{c30}
)
UNION
--- between 31-60 days
-
- SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
- addr1, addr2, addr3, addr4, contact,
- phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
- 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
- "duedate", invoice, $form->{arap}.id,
- (SELECT $buysell FROM exchangerate
- WHERE $form->{arap}.curr = exchangerate.curr
- AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
- WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
- AND $form->{ct}.id = $id
+ 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 (
- transdate < (date '$form->{todate}' - interval '30 days')
- AND transdate >= (date '$form->{todate}' - interval '60 days')
+ a.transdate < $interval{$myconfig->{dbdriver}}{c30}
+ AND a.transdate >= $interval{$myconfig->{dbdriver}}{c60}
)
UNION
--- between 61-90 days
-
- SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
- addr1, addr2, addr3, addr4, contact,
- phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
- 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
- "duedate", invoice, $form->{arap}.id,
- (SELECT $buysell FROM exchangerate
- WHERE $form->{arap}.curr = exchangerate.curr
- AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
- WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
- AND $form->{ct}.id = $id
+ 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 (
- transdate < (date '$form->{todate}' - interval '60 days')
- AND transdate >= (date '$form->{todate}' - interval '90 days')
+ a.transdate < $interval{$myconfig->{dbdriver}}{c60}
+ AND a.transdate >= $interval{$myconfig->{dbdriver}}{c90}
)
UNION
--- over 90 days
-
- SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
- addr1, addr2, addr3, addr4, contact,
- phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
- 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
- "duedate", invoice, $form->{arap}.id,
- (SELECT $buysell FROM exchangerate
- WHERE $form->{arap}.curr = exchangerate.curr
- AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
- WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
- AND $form->{ct}.id = $id
- AND transdate < (date '$form->{todate}' - interval '90 days')
+ 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, invnumber, transdate
+ ctid, transdate, invnumber
|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ 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;
my $query = qq|SELECT name, email, cc, bcc
FROM $form->{ct} ct
WHERE ct.id = $form->{"$form->{ct}_id"}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror;
-
- ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $sth->fetchrow_array;
- $sth->finish;
+ ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $dbh->selectrow_array($query);
+
$dbh->disconnect;
}
my $dbh = $form->dbconnect($myconfig);
# get tax accounts
- my $query = qq|SELECT accno, description
- FROM chart
- WHERE link LIKE '%CT_tax%'
- ORDER BY accno|;
+ my $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;
- while ( my ($accno, $description) = $sth->fetchrow_array ) {
- push @{ $form->{taxaccounts} }, "$accno--$description";
+ my $ref = ();
+ while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{taxaccounts} }, $ref;
}
$sth->finish;
# get gifi tax accounts
- my $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description
- FROM gifi g, chart c
+ 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 ( my ($accno, $description) = $sth->fetchrow_array ) {
- push @{ $form->{gifi_taxaccounts} }, "$accno--$description";
+ while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ push @{ $form->{gifi_taxaccounts} }, $ref;
}
$sth->finish;
# connect to database
my $dbh = $form->dbconnect($myconfig);
+ my ($null, $department_id) = split /--/, $form->{department};
+
# build WHERE
- my $where = qq|WHERE ac.trans_id = a.id
- AND ac.chart_id = ch.id|;
-
+ my $where = "1 = 1";
+ my $cashwhere = "";
- if ($form->{accno} =~ /^gifi_/) {
- my ($null, $accno) = split /_/, $form->{accno};
- $where .= qq| AND ch.gifi_accno = '$accno'|;
- } else {
- $where .= qq| AND ch.accno = '$form->{accno}'|;
+ 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') {
- $where .= " AND n.id = a.customer_id";
$table = "customer";
+ $ARAP = "AR";
}
if ($form->{db} eq 'ap') {
- $where .= " AND n.id = a.vendor_id";
$table = "vendor";
+ $ARAP = "AP";
}
- my $transdate = ($form->{cashbased}) ? "a.datepaid" : "ac.transdate";
- if ($form->{cashbased}) {
- $where .= " AND a.amount = a.paid";
- }
+ 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->{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 $query = qq|SELECT a.id, a.invoice, $transdate AS transdate, a.invnumber,
- n.name, a.netamount,|;
- my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name));
- $sortorder = $form->{sort} unless $sortorder;
+ my %ordinal = ( 'transdate' => 3,
+ 'invnumber' => 4,
+ 'name' => 5
+ );
- if ($form->{db} eq 'ar') {
- $query .= " ac.amount AS tax";
- }
- if ($form->{db} eq 'ap') {
- $query .= " ac.amount * -1 AS tax";
+ 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|
- FROM acc_trans ac, "$form->{db}" a, "$table" n, chart ch
- $where
- ORDER by $sortorder|;
+ ORDER by $sortorder|;
- my $sth = $dbh->prepare($query);
+ $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{TR} }, $ref;
+ $ref->{tax} = $form->round_amount($ref->{tax}, 2);
+ push @{ $form->{TR} }, $ref if $ref->{netamount} != 0;
}
$sth->finish;
# connect to database, turn AutoCommit off
my $dbh = $form->dbconnect_noauto($myconfig);
- my $arap = uc $form->{db};
- $arap .= "_paid";
+ my $ARAP = uc $form->{db};
# get A(R|P)_paid accounts
my $query = qq|SELECT accno, description
FROM chart
- WHERE link LIKE '%$arap%'|;
+ 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;
}
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};
- my ($query, $sth);
+ 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})) {
push @{ $form->{PR} }, $ref;
$sth->finish;
- $query = qq|SELECT c.name, a.invnumber, a.ordnumber,
- ac.transdate,
- ac.amount * $ml AS paid, ac.source, a.invoice, a.id,
- '$form->{db}' AS module
- FROM $table c, acc_trans ac, $form->{db} a
- WHERE c.id = a.${table}_id
- AND ac.trans_id = a.id
- AND ac.chart_id = $ref->{id}|;
-
- $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+ $query = qq|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, g.reference, NULL AS ordnumber,
- ac.transdate,
- ac.amount * $ml AS paid, ac.source, '0' as invoice, g.id,
- 'gl' AS module
- FROM gl g, acc_trans ac
- WHERE g.id = ac.trans_id
- AND ac.chart_id = $ref->{id}
- AND (ac.amount * $ml) > 0
+ 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 .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
-
+ }
- my $sortorder = join ', ', $form->sort_columns(qw(name invnumber ordnumber transdate source));
-
- $query .= " ORDER BY $sortorder";
+ $query .= qq|
+ ORDER BY $sortorder|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
projects / freeside.git / blobdiff