X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FGL.pm;h=4d75fa1bba9ff44455a016ef95859d1c0b36fe67;hb=550a75a67c8cd48abdb0e92dc4b2f49c6a1d0879;hp=3d65eac46cda0d2a410af523242db54b28f911fa;hpb=e2e06cbb81c60cd462133b97024886766e4c167d;p=kivitendo-erp.git

diff --git a/SL/GL.pm b/SL/GL.pm
index 3d65eac46..4d75fa1bb 100644
--- 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}),
+             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);
 
@@ -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);
     }
 
@@ -269,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});
@@ -320,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 =
@@ -333,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
@@ -345,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
@@ -358,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
@@ -365,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);
 
@@ -563,7 +582,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)
@@ -573,7 +593,7 @@ sub transaction {
 
     # retrieve individual rows
     $query =
-      qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo,
+      qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, a.source,
            a.transdate, a.cleared, a.project_id, p.projectnumber,
            a.taxkey, t.rate AS taxrate, t.id,
            (SELECT c1.accno
@@ -638,4 +658,48 @@ sub transaction {
   $main::lxdebug->leave_sub();
 }
 
+sub storno {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $form, $myconfig, $id) = @_;
+
+  my ($query, $new_id, $storno_row, $acc_trans_rows);
+  my $dbh = $form->get_standard_dbh($myconfig);
+
+  $query = qq|SELECT nextval('glid')|;
+  ($new_id) = selectrow_query($form, $dbh, $query);
+
+  $query = qq|SELECT * FROM gl WHERE id = ?|;
+  $storno_row = selectfirst_hashref_query($form, $dbh, $query, $id);
+
+  $storno_row->{id}        = $new_id;
+  $storno_row->{storno_id} = $id;
+  $storno_row->{storno}    = 't';
+  $storno_row->{reference} = 'Storno-' . $storno_row->{reference};
+
+  delete @$storno_row{qw(itime mtime)};
+
+  $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); 
+
+  for my $row (@$rowref) {
+    delete @$row{qw(itime mtime)};
+    $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
+    $row->{trans_id}   = $new_id;
+    $row->{amount}    *= -1;
+    do_query($form, $dbh, $query, (values %$row));
+  }
+
+  $dbh->commit;
+
+  $main::lxdebug->leave_sub();
+}
+
 1;