my ($self, $myconfig, $form) = @_;
- my $amount = ();
+ my %amount;
# connect to database
my $dbh = $form->dbconnect($myconfig);
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- my $gifi = ();
+ my %gifi;
while (my ($accno, $description) = $sth->fetchrow_array) {
$gifi{$accno} = $description;
}
$sth->finish;
- $query = qq|SELECT c.id, c.accno, c.description, c.charttype, c.gifi_accno,
- c.category, c.link
- FROM chart c
- ORDER BY accno|;
+ $query = qq|
+
+ SELECT c.id,
+ c.accno,
+ c.description,
+ c.charttype,
+ c.gifi_accno,
+ c.category,
+ c.link,
+ c.pos_ustva,
+ c.pos_bwa,
+ c.pos_bilanz,
+ c.pos_eur,
+ c.valid_from,
+ c.datevautomatik,
+-- ( SELECT comma(taxkey_id)
+-- FROM taxkeys tk
+-- WHERE tk.chart_id = c.id
+-- AND c.taxkey_id = tk.taxkey_id
+-- ORDER BY c.id
+-- ) AS taxkey_id,
+
+ ( SELECT comma(taxkey)
+ FROM tax tx
+ WHERE tx.id in (
+ SELECT tk.tax_id from taxkeys tk
+ WHERE tk.chart_id = c.id
+ )
+ ORDER BY c.accno
+ ) AS taxkey,
+
+ ( SELECT comma(taxdescription)
+ FROM tax tx
+ WHERE tx.id in (
+ SELECT tk.tax_id from taxkeys tk
+ WHERE tk.chart_id = c.id
+ )
+ ORDER BY c.accno
+ ) AS taxdescription,
+
+ ( SELECT comma(taxnumber)
+ FROM tax tx
+ WHERE tx.id in (
+ SELECT tk.tax_id from taxkeys tk
+ WHERE tk.chart_id = c.id
+ )
+ ORDER BY c.accno
+ ) AS taxaccount,
+
+ ( SELECT comma(tk.pos_ustva)
+ FROM taxkeys tk
+ WHERE tk.chart_id = c.id
+ AND c.taxkey_id = tk.taxkey_id
+ ORDER BY c.id
+ ) AS tk_ustva,
+
+ ( SELECT comma(startdate)
+ FROM taxkeys tk
+ WHERE tk.chart_id = c.id
+ AND c.taxkey_id = tk.taxkey_id
+ ORDER BY c.id
+ ) AS startdate,
+
+ ( SELECT accno
+ FROM chart c2
+ WHERE c2.id = c.id
+ ) AS new_account
+ FROM chart c
+ ORDER BY accno
+ |;
+
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
if ($form->{todate}) {
$fromto .= " AND ac.transdate <= '$form->{todate}'";
$subwhere .= " AND transdate <= '$form->{todate}'";
+ $glwhere .= " AND ac.transdate <= '$form->{todate}'";
}
- if ($form->{eur}) {
- $AR_PAID = qq|AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AR_paid%'
- $subwhere
- )|;
- $AP_PAID = qq|AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AP_paid%'
- $subwhere
- )|;
- } else {
- $where .= $fromto;
- $AR_PAID = "";
- $AP_PAID = "";
- $glwhere = "";
- }
+
+ $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 $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
|;
}
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)
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)
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
$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)
}
1;
-
projects / kivitendo-erp.git / blobdiff