X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FGL.pm;h=4d75fa1bba9ff44455a016ef95859d1c0b36fe67;hb=996ddb70dc0d35be7a72e981ab38011a13d09484;hp=28c503043ffce5aa0c9d00049dd572e272f42139;hpb=e2305bab05a1affd9b2ca7ca93d0194da68e796d;p=kivitendo-erp.git

diff --git a/SL/GL.pm b/SL/GL.pm
index 28c503043..4d75fa1bb 100644
--- a/SL/GL.pm
+++ b/SL/GL.pm
@@ -120,7 +120,7 @@ sub post_transaction {
        WHERE id = ?|;
 
   @values = ($form->{reference}, $form->{description}, $form->{notes},
-             conv_date($form->{transdate}), $department_id, $form->{taxincluded},
+             conv_date($form->{transdate}), $department_id, $form->{taxincluded} ? 't' : 'f',
              $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}), $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f',
              conv_i($form->{id}));
   do_query($form, $dbh, $query, @values);
@@ -272,15 +272,9 @@ sub all_transactions {
   }
 
   if ($form->{category} ne 'X') {
-    $glwhere .=
-      qq| AND gl.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN
-                          (SELECT id FROM chart c2 WHERE c2.category = ?))|;
-    $arwhere .=
-      qq| AND ar.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN
-                          (SELECT id FROM chart c2 WHERE c2.category = ?))|;
-    $apwhere .=
-      qq| AND ap.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN
-                          (SELECT id FROM chart c2 WHERE c2.category = ?))|;
+    $glwhere .= qq| AND g.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = ?))|;
+    $arwhere .= qq| AND a.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = ?))|;
+    $apwhere .= qq| AND a.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = ?))|;
     push(@glvalues, $form->{category});
     push(@arvalues, $form->{category});
     push(@apvalues, $form->{category});
@@ -323,11 +317,30 @@ sub all_transactions {
 
   my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
 
-  my $sortorder;
-
-  if ($form->{sort}) {
-    $form->{sort} =~ s/[^a-zA-Z_]//g;
-    $sortorder = $form->{sort} . ",";
+  my %sort_columns =  (
+    'id'           => [ qw(id)                   ],
+    'transdate'    => [ qw(transdate id)         ],
+    'reference'    => [ qw(lower_reference id)   ],
+    'source'       => [ qw(lower_source id)      ],
+    'description'  => [ qw(lower_description id) ],
+    'accno'        => [ qw(accno transdate id)   ],
+    );
+  my %lowered_columns =  (
+    'reference'       => { 'gl' => 'g.reference',   'arap' => 'a.invnumber', },
+    'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
+    'description'     => { 'gl' => 'g.description', 'arap' => 'ct.name',     },
+    );
+
+  my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
+  my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
+  my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
+
+  my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
+  foreach my $spec (@{ $sort_columns{$sortkey} }) {
+    next if ($spec !~ m/^lower_(.*)$/);
+
+    my $column = $1;
+    map { $columns_for_sorting{$_} .= sprintf(', lower(%s) AS lower_%s', $lowered_columns{$column}->{$_}, $column) } qw(gl arap);
   }
 
   my $query =
@@ -336,6 +349,7 @@ sub all_transactions {
         g.description, ac.transdate, ac.source, ac.trans_id,
         ac.amount, c.accno, g.notes, t.chart_id, ac.oid
         $project_columns
+        $columns_for_sorting{gl}
       FROM gl g, acc_trans ac $project_join, chart c
       LEFT JOIN tax t ON (t.chart_id = c.id)
       WHERE $glwhere
@@ -348,6 +362,7 @@ sub all_transactions {
         ct.name, ac.transdate, ac.source, ac.trans_id,
         ac.amount, c.accno, a.notes, t.chart_id, ac.oid
         $project_columns
+        $columns_for_sorting{arap}
       FROM ar a, acc_trans ac $project_join, customer ct, chart c
       LEFT JOIN tax t ON (t.chart_id=c.id)
       WHERE $arwhere
@@ -361,6 +376,7 @@ sub all_transactions {
         ct.name, ac.transdate, ac.source, ac.trans_id,
         ac.amount, c.accno, a.notes, t.chart_id, ac.oid
         $project_columns
+        $columns_for_sorting{arap}
       FROM ap a, acc_trans ac $project_join, vendor ct, chart c
       LEFT JOIN tax t ON (t.chart_id=c.id)
       WHERE $apwhere
@@ -368,7 +384,7 @@ sub all_transactions {
         AND (a.vendor_id = ct.id)
         AND (a.id = ac.trans_id)
 
-      ORDER BY $sortorder transdate, trans_id, acoid, taxkey DESC|;
+      ORDER BY $sortorder, acoid $sortdir|;
 
   my @values = (@glvalues, @arvalues, @apvalues);