X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=983f9bd0460f8ac619d5958d2af7b0139262f2cb;hb=3270e88361efc8ceff21314b489732229f0f98f7;hp=f9dd76d88503120cb63bcaaf4754fe49a6c7c064;hpb=32fc2e889d08ec0bba083bdbde271e131357a829;p=kivitendo-erp.git

diff --git a/SL/CA.pm b/SL/CA.pm
index f9dd76d88..983f9bd04 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
@@ -57,8 +57,12 @@ sub all_accounts {
   # 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 itime FROM defaults))";
+  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
@@ -66,7 +70,7 @@ sub all_accounts {
                           UNION SELECT id FROM gl WHERE transdate>= $closedto_sql
                         )) |;
   } else {  # Bilanzierung
-    $acc_cash_where = " AND ((select date_trunc('year', a.transdate::date)) >= $closedto_sql) ";
+    $acc_cash_where = " AND (a.transdate >= $closedto_sql) ";
   }
 
   my $query =
@@ -80,7 +84,7 @@ sub all_accounts {
     $amount{ $ref->{accno} } = $ref->{amount};
   }
 
-  my $where = "AND c.id = $chart_id" if ($chart_id ne '');
+  my $where = $chart_id ne '' ? "AND c.id = $chart_id" : '';
 
   $query = qq{
     SELECT
@@ -109,7 +113,7 @@ sub all_accounts {
     LEFT JOIN tax tx ON (tk.tax_id = tx.id)
     WHERE 1=1
     $where
-    GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno,
+    GROUP BY c.accno, c.id, c.description, c.charttype,
       c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from,
       c.datevautomatik
     ORDER BY c.accno
@@ -178,13 +182,6 @@ sub all_transactions {
 
   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);
-  map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
 
   my ($null, $department_id) = split(/--/, $form->{department});
   my ($dpt_where, $dpt_join, @department_values);
@@ -199,22 +196,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") {
-    $where = qq| (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= ? AND datepaid<= ? UNION SELECT id FROM ap WHERE datepaid>= ? AND datepaid<= ? UNION SELECT id FROM gl WHERE transdate>= ? AND transdate<= ?)) |;
-    @where_values = ();
-    push(@where_values, conv_date($form->{fromdate}));
-    push(@where_values, conv_date($form->{todate}));
-    push(@where_values, conv_date($form->{fromdate}));
-    push(@where_values, conv_date($form->{todate}));
-    push(@where_values, conv_date($form->{fromdate}));
-    push(@where_values, conv_date($form->{todate}));
- }
-
 
   if ($form->{accno}) {
 
@@ -284,7 +265,7 @@ sub all_transactions {
     # get 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|  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|FROM acc_trans ac, gl a | .
       $dpt_join .
@@ -385,8 +366,8 @@ sub all_transactions {
   }
 
   my $sort = grep({ $form->{sort} eq $_ } qw(transdate reference description)) ? $form->{sort} : 'transdate';
-
-  $query .= qq|ORDER BY $sort|;
+  my $sort2 = ($sort eq 'reference')?'transdate':'reference';
+  $query .= qq|ORDER BY $sort , $sort2 |;
   my $sth = prepare_execute_query($form, $dbh, $query, @values);
 
   #get detail information for each transaction