#
#======================================================================
-
package CA;
use Data::Dumper;
my ($self, $myconfig, $form) = @_;
my $amount = ();
+
# connect to database
my $dbh = $form->dbconnect($myconfig);
$sth->execute || $form->dberror($query);
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $amount{$ref->{accno}} = $ref->{amount}
+ $amount{ $ref->{accno} } = $ref->{amount};
}
$sth->finish;
-
+
$query = qq|SELECT accno, description
FROM gifi|;
$sth = $dbh->prepare($query);
ORDER BY accno|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
-
+
while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
- $ca->{amount} = $amount{$ca->{accno}};
- $ca->{gifi_description} = $gifi{$ca->{gifi_accno}};
+ $ca->{amount} = $amount{ $ca->{accno} };
+ $ca->{gifi_description} = $gifi{ $ca->{gifi_accno} };
if ($ca->{amount} < 0) {
$ca->{debit} = $ca->{amount} * -1;
} else {
$main::lxdebug->leave_sub();
}
-
sub all_transactions {
$main::lxdebug->enter_sub();
my $fromdate_where;
my $todate_where;
-
+
my $where = '1 = 1';
+
# build WHERE clause from dates if any
-# if ($form->{fromdate}) {
-# $where .= " AND ac.transdate >= '$form->{fromdate}'";
-# }
-# if ($form->{todate}) {
-# $where .= " AND ac.transdate <= '$form->{todate}'";
-# }
-
+ # if ($form->{fromdate}) {
+ # $where .= " AND ac.transdate >= '$form->{fromdate}'";
+ # }
+ # if ($form->{todate}) {
+ # $where .= " AND ac.transdate <= '$form->{todate}'";
+ # }
+
if ($form->{fromdate}) {
- $fromto = " AND ac.transdate >= '$form->{fromdate}'";
- $subwhere .= " AND transdate >= '$form->{fromdate}'";
- $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
+ $fromto = " AND ac.transdate >= '$form->{fromdate}'";
+ $subwhere .= " AND transdate >= '$form->{fromdate}'";
+ $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
}
if ($form->{todate}) {
- $fromto .= " AND ac.transdate <= '$form->{todate}'";
+ $fromto .= " AND ac.transdate <= '$form->{todate}'";
$subwhere .= " AND transdate <= '$form->{todate}'";
}
WHERE link LIKE '%AP_paid%'
$subwhere
)|;
- } else {
- $where .= $fromto;
- $AR_PAID = "";
- $AP_PAID = "";
- $glwhere = "";
+ } else {
+ $where .= $fromto;
+ $AR_PAID = "";
+ $AP_PAID = "";
+ $glwhere = ""; # note! gl will be aliased as "a" later...
}
- my $sortorder = join ', ', $form->sort_columns(qw(transdate reference description));
- my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE : q|'0'|;
-
+ my $sortorder = join ', ',
+ $form->sort_columns(qw(transdate reference description));
+ my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
+
# Oracle workaround, use ordinal positions
- my %ordinal = ( transdate => 4,
- reference => 2,
- description => 3 );
+ my %ordinal = (transdate => 4,
+ reference => 2,
+ description => 3);
map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
-
my ($null, $department_id) = split /--/, $form->{department};
my $dpt_where;
my $dpt_join;
if ($department_id) {
$dpt_join = qq|
- JOIN department t ON (t.trans_id = ac.trans_id)
+ JOIN department t ON (t.id = a.department_id)
|;
- $dpt_where == qq|
- AND t.department_id = $department_id
+ $dpt_where = qq|
+ AND t.id = $department_id
|;
}
}
if ($form->{accno} || $form->{gifi_accno}) {
+
# get category for account
$query = qq|SELECT c.category
FROM chart c
$sth->execute || $form->dberror($query);
($form->{category}) = $sth->fetchrow_array;
$sth->finish;
-
+
if ($form->{fromdate}) {
# get beginning balance
if ($form->{project_id}) {
- $query .= qq|
+ $query .= qq|
UNION
- SELECT SUM(ac.sellprice)
+ SELECT SUM(ac.qty * ac.sellprice)
FROM invoice ac
JOIN ar a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
UNION
- SELECT SUM(ac.sellprice)
+ SELECT SUM(ac.qty * ac.sellprice)
FROM invoice ac
JOIN ap a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
$dpt_where
$project
|;
-
- if ($form->{project_id}) {
- $query .= qq|
+ if ($form->{project_id}) {
+
+ $query .= qq|
UNION
- SELECT SUM(ac.sellprice)
+ SELECT SUM(ac.qty * ac.sellprice)
FROM invoice ac
JOIN ar a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
UNION
- SELECT SUM(ac.sellprice)
+ SELECT SUM(ac.qty * ac.sellprice)
FROM invoice ac
JOIN ap a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
$project
|;
- }
+ }
}
-
+
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$query = "";
my $union = "";
- foreach my $id (@id) {
-
+ foreach my $id (@id) {
+
+ # NOTE:
+ # Postgres is really picky about the order of implicit CROSS JOINs with ','
+ # if you alias the tables and want to use the alias later in another JOIN.
+ # the alias you want to use has to be the most recent in the list, otherwise
+ # Postgres will overwrite the alias internally and complain.
+ # For this reason, in the next 3 SELECTs, the 'a' alias is last in the list.
+ # Don't change this, and if you do, substitute the ',' with CROSS JOIN
+ # ... that also works.
+
# get all transactions
$query .= qq|$union
- SELECT g.id, g.reference, g.description, ac.transdate,
+ SELECT a.id, a.reference, a.description, ac.transdate,
$false AS invoice, ac.amount, 'gl' as module
- FROM gl g, acc_trans ac $dpt_from
+ FROM acc_trans ac, gl a $dpt_join
WHERE $where
$glwhere
- $dpt_join
+ $dpt_where
$project
AND ac.chart_id = $id
- AND ac.trans_id = g.id
+ AND ac.trans_id = a.id
UNION
SELECT a.id, a.invnumber, c.name, ac.transdate,
a.invoice, ac.amount, 'ar' as module
- FROM ar a, acc_trans ac, customer c $dpt_from
+ FROM acc_trans ac, customer c, ar a $dpt_join
WHERE $where
- $dpt_join
+ $dpt_where
$project
AND ac.chart_id = $id
AND ac.trans_id = a.id
UNION
SELECT a.id, a.invnumber, v.name, ac.transdate,
a.invoice, ac.amount, 'ap' as module
- FROM ap a, acc_trans ac, vendor v $dpt_from
+ FROM acc_trans ac, vendor v, ap a $dpt_join
WHERE $where
- $dpt_join
+ $dpt_where
$project
AND ac.chart_id = $id
AND ac.trans_id = a.id
$AP_PAID
AND a.vendor_id = v.id
|;
- $union = qq|
+ $union = qq|
UNION ALL
|;
if ($form->{project_id}) {
$fromdate_where =~ s/ac\./a\./;
- $todate_where =~ s/ac\./a\./;
-
+ $todate_where =~ s/ac\./a\./;
+
$query .= qq|
UNION ALL
-
+
SELECT a.id, a.invnumber, c.name, a.transdate,
- a.invoice, ac.sellprice, 'ar' as module
+ a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module
FROM ar a
JOIN invoice ac ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
$todate_where
$dpt_where
$project
-
+
UNION ALL
-
+
SELECT a.id, a.invnumber, v.name, a.transdate,
- a.invoice, ac.sellprice, 'ap' as module
+ a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module
FROM ap a
JOIN invoice ac ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
$dpt_where
$project
|;
-
+
$fromdate_where =~ s/a\./ac\./;
- $todate_where =~ s/a\./ac\./;
-
+ $todate_where =~ s/a\./ac\./;
+
}
-
- $union = qq|
+
+ $union = qq|
UNION ALL
|;
}
$sth->execute || $form->dberror($query);
while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
-
+
# gl
if ($ca->{module} eq "gl") {
$ca->{module} = "gl";
}
if ($ca->{amount} < 0) {
- $ca->{debit} = $ca->{amount} * -1;
+ $ca->{debit} = $ca->{amount} * -1;
$ca->{credit} = 0;
} else {
$ca->{credit} = $ca->{amount};
- $ca->{debit} = 0;
+ $ca->{debit} = 0;
}
push @{ $form->{CA} }, $ca;
-
+
}
-
+
$sth->finish;
$dbh->disconnect;
}
1;
-
projects / kivitendo-erp.git / blobdiff