X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=1a77102391f3db0662d554fd464c4bf2db4e9633;hb=f12fcb594dd079ca987edca36cd431a3a2ef155e;hp=a9638606397ba7a08c46dda76578a96e74effa1a;hpb=ee072e4f077213bf6f8792ca8f0a1afebbb6282f;p=kivitendo-erp.git diff --git a/SL/CA.pm b/SL/CA.pm index a96386063..1a7710239 100644 --- a/SL/CA.pm +++ b/SL/CA.pm @@ -42,7 +42,7 @@ sub all_accounts { my ($self, $myconfig, $form) = @_; - my $amount = (); + my %amount; # connect to database my $dbh = $form->dbconnect($myconfig); @@ -65,16 +65,83 @@ sub all_accounts { $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); @@ -141,31 +208,14 @@ sub all_transactions { 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'|; @@ -181,10 +231,10 @@ sub all_transactions { 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 |; } @@ -234,7 +284,7 @@ sub all_transactions { 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) @@ -248,7 +298,7 @@ sub all_transactions { 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) @@ -280,7 +330,7 @@ sub all_transactions { 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) @@ -294,7 +344,7 @@ sub all_transactions { 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) @@ -323,23 +373,32 @@ sub all_transactions { 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 @@ -348,9 +407,9 @@ sub all_transactions { 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 @@ -371,7 +430,7 @@ sub all_transactions { 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) @@ -386,7 +445,7 @@ sub all_transactions { 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)