X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=f9dd76d88503120cb63bcaaf4754fe49a6c7c064;hb=b89137f814fb873ddc98c765b7a0ef17bed83ce1;hp=a537085c958499bf99e77bc27feb3d8f6f003af4;hpb=d7310fc27f9ddf770b35271055e7215943e7a071;p=kivitendo-erp.git

diff --git a/SL/CA.pm b/SL/CA.pm
index a537085c9..f9dd76d88 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,15 +46,27 @@ sub all_accounts {
 
   my ($self, $myconfig, $form, $chart_id) = @_;
 
-  my %amount;
+  my (%amount, $acc_cash_where);
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
-  if ($form->{method} eq "cash") {
-    $acc_cash_where = qq| AND (a.trans_id IN (SELECT id FROM ar WHERE datepaid>=(select date_trunc('year', current_date)) UNION SELECT id FROM ap WHERE datepaid>=(select date_trunc('year', current_date)) UNION SELECT id FROM gl WHERE transdate>=(select date_trunc('year', current_date)))) |;
-  } else {
-    $acc_cash_where = " AND ((select date_trunc('year', a.transdate::date)) >= (select date_trunc('year', current_date)))";
+  # 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))
+
+  my $closedto_sql = "COALESCE((SELECT closedto FROM defaults),(SELECT itime FROM defaults))";
+
+  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 ((select date_trunc('year', a.transdate::date)) >= $closedto_sql) ";
   }
 
   my $query =
@@ -104,7 +119,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;
@@ -145,7 +160,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 >= ?|;
@@ -163,7 +178,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,
@@ -236,16 +251,16 @@ sub all_transactions {
       ($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}' " : "";
+      $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}' " : "";
+      $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}' " : "";
+      $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});
     }
@@ -270,7 +285,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 .
@@ -282,7 +297,7 @@ 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 .
@@ -295,14 +310,14 @@ sub all_transactions {
 
       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)| .
+      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,
@@ -315,12 +330,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) | .
@@ -331,12 +357,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) | .
@@ -346,8 +371,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);
@@ -363,17 +387,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';
@@ -397,7 +421,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;