Zum commit von gerade noch entsprechend employee korrekt ausgelesen und gldate vom...

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

diff --git a/SL/GL.pm b/SL/GL.pm
index 320be1e..e1cb7bc 100644 (file)
--- a/SL/GL.pm
+++ b/SL/GL.pm
@@ -231,19 +231,22 @@ sub all_transactions {
     push(@apvalues, '%' . $form->{source} . '%');
   }
 
+  # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren)
+  $form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/;
+
   if ($form->{datefrom}) {
-    $glwhere .= " AND ac.transdate >= ?";
-    $arwhere .= " AND ac.transdate >= ?";
-    $apwhere .= " AND ac.transdate >= ?";
+    $glwhere .= " AND ac.$form->{datesort} >= ?";
+    $arwhere .= " AND ac.$form->{datesort} >= ?";
+    $apwhere .= " AND ac.$form->{datesort} >= ?";
     push(@glvalues, $form->{datefrom});
     push(@arvalues, $form->{datefrom});
     push(@apvalues, $form->{datefrom});
   }
 
   if ($form->{dateto}) {
-    $glwhere .= " AND ac.transdate <= ?";
-    $arwhere .= " AND ac.transdate <= ?";
-    $apwhere .= " AND ac.transdate <= ?";
+    $glwhere .= " AND ac.$form->{datesort} <= ?";
+    $arwhere .= " AND ac.$form->{datesort} <= ?";
+    $apwhere .= " AND ac.$form->{datesort} <= ?";
     push(@glvalues, $form->{dateto});
     push(@arvalues, $form->{dateto});
     push(@apvalues, $form->{dateto});
@@ -257,6 +260,17 @@ sub all_transactions {
     push(@arvalues, '%' . $form->{description} . '%');
     push(@apvalues, '%' . $form->{description} . '%');
   }
+ 
+  if ($form->{employee} =~ /--/) {
+    ($form->{employee_id},$form->{employee_name}) = split(/--/,$form->{employee});
+  #if ($form->{employee_id}) {
+    $glwhere .= " AND g.employee_id = ? ";
+    $arwhere .= " AND a.employee_id = ? ";
+    $apwhere .= " AND a.employee_id = ? ";
+    push(@glvalues, conv_i($form->{employee_id}));
+    push(@arvalues, conv_i($form->{employee_id}));
+    push(@apvalues, conv_i($form->{employee_id}));
+  }
 
   if ($form->{notes}) {
     $glwhere .= " AND g.notes ILIKE ?";
@@ -312,7 +326,7 @@ sub all_transactions {
         qq|SELECT SUM(ac.amount)
            FROM acc_trans ac
            LEFT JOIN chart c ON (ac.chart_id = c.id)
-           WHERE (c.accno = ?) AND (ac.transdate < ?)|;
+           WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|;
       ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
     }
   }
@@ -322,6 +336,7 @@ sub all_transactions {
   my %sort_columns =  (
     'id'           => [ qw(id)                   ],
     'transdate'    => [ qw(transdate id)         ],
+    'gldate'       => [ qw(gldate id)         ],
     'reference'    => [ qw(lower_reference id)   ],
     'description'  => [ qw(lower_description id) ],
     'accno'        => [ qw(accno transdate id)   ],
@@ -331,9 +346,10 @@ sub all_transactions {
     'source'          => { 'gl' => 'ac.source',     'arap' => 'ac.source',   },
     'description'     => { 'gl' => 'g.description', 'arap' => 'ct.name',     },
     );
-
+  
+  # sortdir = sort direction (ascending or descending)
   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
-  my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
+  my $sortkey   = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort};  # default used to be transdate
   my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
 
   my %columns_for_sorting = ( 'gl' => '', 'arap' => '', );
@@ -347,11 +363,14 @@ sub all_transactions {
   $query =
     qq|SELECT
         ac.acc_trans_id, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link,
-        g.description, ac.transdate, ac.source, ac.trans_id,
-        ac.amount, c.accno, g.notes, t.chart_id
+        g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id,
+        ac.amount, c.accno, g.notes, t.chart_id,
+        CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
         $project_columns
         $columns_for_sorting{gl}
-      FROM gl g, acc_trans ac $project_join, chart c
+      FROM gl g
+      LEFT JOIN employee e ON (g.employee_id = e.id),
+      acc_trans ac $project_join, chart c
       LEFT JOIN tax t ON (t.chart_id = c.id)
       WHERE $glwhere
         AND (ac.chart_id = c.id)
@@ -360,11 +379,14 @@ sub all_transactions {
       UNION
 
       SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
-        ct.name, ac.transdate, ac.source, ac.trans_id,
-        ac.amount, c.accno, a.notes, t.chart_id
+        ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
+        ac.amount, c.accno, a.notes, t.chart_id,
+        CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
         $project_columns
         $columns_for_sorting{arap}
-      FROM ar a, acc_trans ac $project_join, customer ct, chart c
+      FROM ar a
+      LEFT JOIN employee e ON (a.employee_id = e.id),
+      acc_trans ac $project_join, customer ct, chart c
       LEFT JOIN tax t ON (t.chart_id=c.id)
       WHERE $arwhere
         AND (ac.chart_id = c.id)
@@ -374,11 +396,14 @@ sub all_transactions {
       UNION
 
       SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link,
-        ct.name, ac.transdate, ac.source, ac.trans_id,
-        ac.amount, c.accno, a.notes, t.chart_id
+        ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id,
+        ac.amount, c.accno, a.notes, t.chart_id,
+        CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee
         $project_columns
         $columns_for_sorting{arap}
-      FROM ap a, acc_trans ac $project_join, vendor ct, chart c
+      FROM ap a
+      LEFT JOIN employee e ON (a.employee_id = e.id),
+      acc_trans ac $project_join, vendor ct, chart c
       LEFT JOIN tax t ON (t.chart_id=c.id)
       WHERE $apwhere
         AND (ac.chart_id = c.id)
@@ -386,6 +411,7 @@ sub all_transactions {
         AND (a.id = ac.trans_id)
 
       ORDER BY $sortorder, acc_trans_id $sortdir|;
+#      ORDER BY gldate DESC, id DESC, acc_trans_id DESC
 
   my @values = (@glvalues, @arvalues, @apvalues);
 
@@ -707,7 +733,7 @@ sub storno {
   my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
 
   for my $row (@$rowref) {
-    delete @$row{qw(itime mtime)};
+    delete @$row{qw(itime mtime acc_trans_id)};
     $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
     $row->{trans_id}   = $new_id;
     $row->{amount}    *= -1;