X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=577d6b98a90e6c899ecb8d24447e1a55b1b21abd;hb=8eca5e84a3f8ebecfb93b593e4540edfa755dcc8;hp=a208e2e3e9b18e58f8006cce890626c31c99a548;hpb=6cbeab9a2d7691fa8fb69faab3f21a662df8740f;p=kivitendo-erp.git diff --git a/SL/CA.pm b/SL/CA.pm index a208e2e3e..577d6b98a 100644 --- a/SL/CA.pm +++ b/SL/CA.pm @@ -1,4 +1,4 @@ -#===================================================================== + #===================================================================== # LX-Office ERP # Copyright (C) 2004 # Based on SQL-Ledger Version 2.1.9 @@ -34,6 +34,9 @@ # #====================================================================== +use utf8; +use strict; + package CA; use Data::Dumper; use SL::DBUtils; @@ -43,28 +46,48 @@ sub all_accounts { my ($self, $myconfig, $form, $chart_id) = @_; - my %amount; + my (%amount, $acc_cash_where); # connect to database my $dbh = $form->dbconnect($myconfig); + # bug 1071 Warum sollte bei Erreichen eines neuen Jahres die Kontenübersicht nur noch die + # bereits bebuchten Konten anzeigen? + # Folgende Erweiterung: + # 1.) Gehe zurück bis zu dem Datum an dem die Bücher geschlossen wurden + # 2.) Falls die Bücher noch nie geschlossen wurden, gehe zurück bis zum Bearbeitungsstart + # COALESCE((SELECT closedto FROM defaults),(SELECT itime FROM defaults)) + # PROBLEM: Das date_trunc schneidet auf den 1.1.20XX ab und KEINE Buchungen werden angezeigt + # Lösung: date_trunc rausgeworfen und nicht mehr auf itime geprüft, sondern auf die erste Buchung + # in transdate jan 11.04.2011 + + my $closedto_sql = "COALESCE((SELECT closedto FROM defaults), + (SELECT transdate from acc_trans order by transdate limit 1))"; + + if ($form->{method} eq "cash") { # EÜR + $acc_cash_where = qq| AND (a.trans_id IN (SELECT id FROM ar WHERE datepaid>= $closedto_sql + UNION SELECT id FROM ap WHERE datepaid>= $closedto_sql + UNION SELECT id FROM gl WHERE transdate>= $closedto_sql + )) |; + } else { # Bilanzierung + $acc_cash_where = " AND (a.transdate >= $closedto_sql) "; + } + my $query = qq|SELECT c.accno, SUM(a.amount) AS amount | . qq|FROM chart c, acc_trans a | . qq|WHERE c.id = a.chart_id | . + qq|$acc_cash_where| . qq|GROUP BY c.accno|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + foreach my $ref (selectall_hashref_query($form, $dbh, $query)) { $amount{ $ref->{accno} } = $ref->{amount}; } - $sth->finish; - my $where = "AND c.id = $chart_id" if ($chart_id ne ''); + my $where = $chart_id ne '' ? "AND c.id = $chart_id" : ''; $query = qq{ - SELECT + SELECT c.accno, c.id, c.description, @@ -76,11 +99,11 @@ sub all_accounts { c.pos_eur, c.valid_from, c.datevautomatik, - comma(tk.startdate) AS startdate, - comma(tk.taxkey_id) AS taxkey, + comma(tk.startdate::text) AS startdate, + comma(tk.taxkey_id::text) AS taxkey, comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription, - comma(tx.taxnumber) AS taxaccount, - comma(tk.pos_ustva) AS tk_ustva, + comma(tx.taxnumber::text) AS taxaccount, + comma(tk.pos_ustva::text) AS tk_ustva, ( SELECT accno FROM chart c2 WHERE c2.id = c.id @@ -91,7 +114,7 @@ sub all_accounts { WHERE 1=1 $where GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno, - c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, + c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, c.datevautomatik ORDER BY c.accno }; @@ -100,7 +123,7 @@ sub all_accounts { $form->{CA} = []; - while (my $ca = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ca = $sth->fetchrow_hashref("NAME_lc")) { $ca->{amount} = $amount{ $ca->{accno} }; if ($ca->{amount} < 0) { $ca->{debit} = $ca->{amount} * -1; @@ -141,7 +164,7 @@ sub all_transactions { # $where .= " AND ac.transdate <= '$form->{todate}'"; # } - my (@values, @where_values, @subwhere_values); + my (@values, @where_values, @subwhere_values, $subwhere); if ($form->{fromdate}) { $where .= qq| AND ac.transdate >= ?|; $subwhere .= qq| AND transdate >= ?|; @@ -159,7 +182,7 @@ sub all_transactions { my $sortorder = join ', ', $form->sort_columns(qw(transdate reference description)); - my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|; + my $false = ($myconfig->{dbdriver} eq 'Pg') ? "FALSE" : q|'0'|; # Oracle workaround, use ordinal positions my %ordinal = (transdate => 4, @@ -180,15 +203,6 @@ sub all_transactions { $project = qq| AND ac.project_id = ? |; @project_values = (conv_i($form->{project_id})); } - my $acc_cash_where = ""; - my $ar_cash_where = ""; - my $ap_cash_where = ""; - - - if ($form->{method} eq "cash") { - $acc_cash_where = qq| AND (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM ap WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM gl WHERE transdate>='$form->{fromdate}' AND transdate<='$form->{todate}')) |; - $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |; - } if ($form->{accno}) { @@ -202,42 +216,40 @@ sub all_transactions { qq|SELECT SUM(ac.amount) AS amount FROM acc_trans ac JOIN chart c ON (ac.chart_id = c.id) - $dpt_join - WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction - $dpt_where + WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $project - AND c.accno = ? $acc_cash_where|; - + AND c.accno = ?|; + ($form->{beginning_balance}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); # get last transaction date my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|; + $query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ?|; ($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); # get old saldo - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|; ($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); #get old balance - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|; ($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|; ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); # get current saldo - my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|; + $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|; ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); #get current balance - my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|; ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); - my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|; ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); } } @@ -247,7 +259,7 @@ sub all_transactions { @values = (); 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 @@ -261,7 +273,7 @@ sub all_transactions { $query = qq|SELECT a.id, a.reference, a.description, ac.transdate, ac.chart_id, | . qq| $false AS invoice, ac.amount, 'gl' as module, | . - qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § . + qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § . qq|FROM acc_trans ac, gl a | . $dpt_join . qq|WHERE | . $where . $dpt_where . $project . @@ -273,28 +285,27 @@ sub all_transactions { qq|SELECT a.id, a.invnumber, c.name, ac.transdate, ac.chart_id, | . qq| a.invoice, ac.amount, 'ar' as module, | . - qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § . + qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § . qq|FROM acc_trans ac, customer c, ar a | . $dpt_join . qq|WHERE | . $where . $dpt_where . $project . qq| AND ac.chart_id = ? | . qq| AND ac.trans_id = a.id | . qq| AND a.customer_id = c.id | . - qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| . + qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)| . qq|UNION ALL | . qq|SELECT a.id, a.invnumber, v.name, ac.transdate, ac.chart_id, | . qq| a.invoice, ac.amount, 'ap' as module, | . - qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § . + qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § . qq|FROM acc_trans ac, vendor v, ap a | . $dpt_join . qq|WHERE | . $where . $dpt_where . $project . qq| AND ac.chart_id = ? | . qq| AND ac.trans_id = a.id | . - qq| AND a.vendor_id = v.id |; - qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| . - + qq| AND a.vendor_id = v.id | . + qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|; push(@values, @where_values, @department_values, @project_values, $id, @where_values, @department_values, @project_values, $id, @@ -307,12 +318,23 @@ sub all_transactions { $fromdate_where =~ s/ac\./a\./; $todate_where =~ s/ac\./a\./; +# strict check 20.10.2009 sschoeling +# the previous version contained the var $ar_ap_cash_where, which was ONLY set by +# RP->trial_balance() I tried to figure out which bizarre flow through the +# program would happen to set that var, so that it would be used here later on, +# (which would be nonsense, since you would normally load chart before +# claculating balance of said charts) and then decided that any mechanic that +# complex should fail anyway. + +# if anyone is missing a time check on charts, that broke arounf the time +# trial_balance was rewritten, this would be it + $query .= qq|UNION ALL | . qq|SELECT a.id, a.invnumber, c.name, a.transdate, | . qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module, | . - qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo § . + qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo § . qq|FROM ar a | . qq|JOIN invoice ac ON (ac.trans_id = a.id) | . qq|JOIN parts p ON (ac.parts_id = p.id) | . @@ -323,12 +345,11 @@ sub all_transactions { $todate_where . $dpt_where . $project . - $ar_ap_cash_where . qq|UNION ALL | . qq|SELECT a.id, a.invnumber, v.name, a.transdate, | . qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module, | . - qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo § . + qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo § . qq|FROM ap a | . qq|JOIN invoice ac ON (ac.trans_id = a.id) | . qq|JOIN parts p ON (ac.parts_id = p.id) | . @@ -338,8 +359,7 @@ sub all_transactions { $fromdate_where . $todate_where . $dpt_where . - $project . - $ar_ap_cash_where; + $project; push(@values, $id, @department_values, @project_values, $id, @department_values, @project_values); @@ -355,17 +375,17 @@ sub all_transactions { my $sort = grep({ $form->{sort} eq $_ } qw(transdate reference description)) ? $form->{sort} : 'transdate'; $query .= qq|ORDER BY $sort|; - $sth = prepare_execute_query($form, $dbh, $query, @values); + my $sth = prepare_execute_query($form, $dbh, $query, @values); #get detail information for each transaction - $trans_query = + my $trans_query = qq|SELECT accno, | . qq|amount, transdate FROM acc_trans LEFT JOIN chart ON (chart_id=chart.id) WHERE | . qq|trans_id = ? AND sign(amount) <> sign(?) AND chart_id <> ? AND transdate = ?|; my $trans_sth = $dbh->prepare($trans_query); $form->{CA} = []; - while (my $ca = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ca = $sth->fetchrow_hashref("NAME_lc")) { # ap if ($ca->{module} eq "ap") { $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap'; @@ -389,7 +409,7 @@ sub all_transactions { #get detail information for this transaction $trans_sth->execute($ca->{id}, $ca->{amount}, $ca->{chart_id}, $ca->{transdate}) || $form->dberror($trans_query . " (" . join(", ", $ca->{id}) . ")"); - while (my $trans = $trans_sth->fetchrow_hashref(NAME_lc)) { + while (my $trans = $trans_sth->fetchrow_hashref("NAME_lc")) { if (($ca->{transdate} eq $trans->{transdate}) && ($ca->{amount} * $trans->{amount} < 0)) { if ($trans->{amount} < 0) { $trans->{debit} = $trans->{amount} * -1; @@ -397,7 +417,7 @@ sub all_transactions { } else { $trans->{credit} = $trans->{amount}; $trans->{debit} = 0; - } + } push(@{ $ca->{GEGENKONTO} }, $trans); } else { next; @@ -405,7 +425,7 @@ sub all_transactions { } $ca->{index} = join "--", map { $ca->{$_} } qw(id reference description transdate); - +# $ca->{index} = $ca->{$form->{sort}}; push(@{ $form->{CA} }, $ca); }