Berichte über Zahlungsein- und -ausgänge auf- und absteigend sortierbar gemacht.

[kivitendo-erp.git] / SL / GL.pm

diff --git a/SL/GL.pm b/SL/GL.pm
index 6cdcee9..51fbfac 100644 (file)
--- a/SL/GL.pm
+++ b/SL/GL.pm
@@ -109,16 +109,19 @@ sub post_transaction {
   my ($null, $department_id) = split(/--/, $form->{department});
   $department_id *= 1;
 
+  $form->{ob_transaction} *= 1;
+  $form->{cb_transaction} *= 1;
+
   $query =
     qq|UPDATE gl SET
          reference = ?, description = ?, notes = ?,
          transdate = ?, department_id = ?, taxincluded = ?,
-         storno = ?, storno_id = ?
+         storno = ?, storno_id = ?, ob_transaction = ?, cb_transaction = ?
        WHERE id = ?|;
 
   @values = ($form->{reference}, $form->{description}, $form->{notes},
              conv_date($form->{transdate}), $department_id, $form->{taxincluded},
-             $form->{storno} ? 't' : 'f', conv_i($form->{storno_id}),
+             $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);
 
@@ -153,11 +156,11 @@ sub post_transaction {
     if ($amount != 0) {
       $query =
         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
-                                  source, memo, project_id, taxkey)
+                                  source, memo, project_id, taxkey, ob_transaction, cb_transaction)
            VALUES (?, (SELECT id FROM chart WHERE accno = ?),
-                   ?, ?, ?, ?, ?, ?)|;
+                   ?, ?, ?, ?, ?, ?, ?, ?)|;
       @values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{transdate}),
-                 $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey);
+                 $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey, $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f');
       do_query($form, $dbh, $query, @values);
     }
 
@@ -320,11 +323,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 =
@@ -333,6 +355,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
@@ -345,6 +368,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
@@ -358,6 +382,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
@@ -365,7 +390,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);
 
@@ -563,7 +588,8 @@ sub transaction {
   if ($form->{id}) {
     $query =
       qq|SELECT g.reference, g.description, g.notes, g.transdate, g.storno, g.storno_id,
-           d.description AS department, e.name AS employee, g.taxincluded, g.gldate
+           d.description AS department, e.name AS employee, g.taxincluded, g.gldate, 
+         g.ob_transaction, g.cb_transaction
          FROM gl g
          LEFT JOIN department d ON (d.id = g.department_id)
          LEFT JOIN employee e ON (e.id = g.employee_id)
@@ -662,6 +688,9 @@ sub storno {
   $query = sprintf 'INSERT INTO gl (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
   do_query($form, $dbh, $query, (values %$storno_row));
 
+  $query = qq|UPDATE gl SET storno = 't' WHERE id = ?|;
+  do_query($form, $dbh, $query, $id);
+
   # now copy acc_trans entries
   $query = qq|SELECT * FROM acc_trans WHERE trans_id = ?|;
   my $rowref = selectall_hashref_query($form, $dbh, $query, $id);