X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=76d2b320a5580cb202d3f9beb4be9ee2a27d411a;hb=0a4891e33e1ee05dd0363d985fb1a2205e25c63c;hp=56036e7dea918251dbba8fd835cb5203e7cffff9;hpb=717d4a1334d916fc77ba09bc17e5999ada617b21;p=kivitendo-erp.git

diff --git a/SL/CA.pm b/SL/CA.pm
index 56036e7de..76d2b320a 100644
--- a/SL/CA.pm
+++ b/SL/CA.pm
@@ -41,7 +41,7 @@ use SL::DBUtils;
 sub all_accounts {
   $main::lxdebug->enter_sub();
 
-  my ($self, $myconfig, $form) = @_;
+  my ($self, $myconfig, $form, $chart_id) = @_;
 
   my %amount;
 
@@ -61,26 +61,45 @@ sub all_accounts {
   }
   $sth->finish;
 
-  $query =
-    qq!SELECT  c.accno, c.id, c.description, c.charttype, c.category, ! .
-    qq!  c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, ! .
-    qq!  c.datevautomatik, comma(tk.startdate) AS startdate, ! .
-    qq!  comma(tk.taxkey_id) AS taxkey, ! .
-    qq!  comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') ! .
-    qq!    AS taxdescription, ! .
-    qq!  comma(tx.taxnumber) AS taxaccount, comma(tk.pos_ustva) ! .
-    qq!    AS tk_ustva, ! .
-    qq!  ( SELECT accno FROM chart c2 WHERE c2.id = c.id ) AS new_account ! .
-    qq!FROM chart c ! .
-    qq!LEFT JOIN taxkeys tk ON (c.id = tk.chart_id) ! .
-    qq!LEFT JOIN tax tx ON (tk.tax_id = tx.id) ! .
-    qq!GROUP BY c.accno, c.id, c.description, c.charttype, ! .
-    qq!  c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, ! .
-    qq!  c.valid_from, c.datevautomatik ! .
-    qq!ORDER BY c.accno!;
+  my $where = "AND c.id = $chart_id" if ($chart_id ne '');
+
+  $query = qq{
+    SELECT 
+      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,
+      comma(tk.startdate) AS startdate,
+      comma(tk.taxkey_id) AS taxkey,
+      comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,
+      comma(tx.taxnumber) AS taxaccount,
+      comma(tk.pos_ustva) AS tk_ustva,
+      ( SELECT accno
+      FROM chart c2
+      WHERE c2.id = c.id
+      ) AS new_account
+    FROM chart c
+    LEFT JOIN taxkeys tk ON (c.id = tk.chart_id)
+    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,
+      c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from,      
+      c.datevautomatik
+    ORDER BY c.accno
+  };
+
   my $sth = prepare_execute_query($form, $dbh, $query);
 
   $form->{CA} = [];
+
   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
     $ca->{amount} = $amount{ $ca->{accno} };
     if ($ca->{amount} < 0) {
@@ -228,7 +247,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
@@ -247,29 +266,31 @@ sub all_transactions {
       $dpt_join .
       qq|WHERE | . $where . $dpt_where . $project .
       qq|  AND ac.chart_id = ? | .
-      qq|  AND ac.trans_id = a.id | .
+      qq| AND ac.trans_id = a.id | .
 
-      qq|UNION | .
+      qq|UNION ALL | .
 
       qq|SELECT a.id, a.invnumber, c.name, ac.transdate, | .
       qq|  a.invoice, ac.amount, 'ar' as module | .
       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 ac.chart_id = ? | .
+      qq| AND NOT a.storno | .
+      qq| AND ac.trans_id = a.id | .
+      qq| AND a.customer_id = c.id | .
 
-      qq|UNION | .
+      qq|UNION ALL | .
 
       qq|SELECT a.id, a.invnumber, v.name, ac.transdate, | .
       qq|  a.invoice, ac.amount, 'ap' as module | .
       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 ac.chart_id = ? | .
+      qq| AND ac.trans_id = a.id | .
+      qq| AND NOT a.storno | .
+      qq| AND a.vendor_id = v.id |;
 
     push(@values,
          @where_values, @department_values, @project_values, $id,
@@ -326,7 +347,9 @@ sub all_transactions {
     $union = qq|UNION ALL|;
   }
 
-  $query .= qq|ORDER BY | . $sortorder;
+  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);
 
   $form->{CA} = [];
@@ -349,6 +372,8 @@ sub all_transactions {
       $ca->{debit}  = 0;
     }
 
+    $ca->{index} = join "--", map { $ca->{$_} } qw(id reference description);
+
     push(@{ $form->{CA} }, $ca);
 
   }