Sortierung in Berichten über Ausgangsrechnungen/Debitorenbuchungen auch auf- und...

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

diff --git a/SL/GL.pm b/SL/GL.pm
index 27aa179..28c5030 100644 (file)
--- a/SL/GL.pm
+++ b/SL/GL.pm
@@ -70,13 +70,6 @@ sub post_transaction {
 
   my $i;
 
-  # check if debit and credit balances
-
-  if ($form->{storno}) {
-    $form->{reference}   = "Storno-" . $form->{reference};
-    $form->{description} = "Storno-" . $form->{description};
-  }
-
   # connect to database, turn off AutoCommit
   my $dbh = $form->dbconnect_noauto($myconfig);
 
@@ -116,14 +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 = ?
+         transdate = ?, department_id = ?, taxincluded = ?,
+         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->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f',
              conv_i($form->{id}));
   do_query($form, $dbh, $query, @values);
 
@@ -158,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);
     }
 
@@ -180,13 +178,16 @@ sub post_transaction {
     }
   }
 
+  if ($form->{storno} && $form->{storno_id}) {
+    do_query($form, $dbh, qq|UPDATE gl SET storno = 't' WHERE id = ?|, conv_i($form->{storno_id}));
+  }
+
   # commit and redirect
   my $rc = $dbh->commit;
   $dbh->disconnect;
   $main::lxdebug->leave_sub();
 
-  $rc;
-
+  return $rc;
 }
 
 sub all_transactions {
@@ -279,7 +280,7 @@ sub all_transactions {
                           (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 = ?))"|;
+                          (SELECT id FROM chart c2 WHERE c2.category = ?))|;
     push(@glvalues, $form->{category});
     push(@arvalues, $form->{category});
     push(@apvalues, $form->{category});
@@ -379,6 +380,8 @@ sub all_transactions {
   my $trans_id  = "";
   my $trans_id2 = "";
 
+  my ($i, $j, $k, $l, $ref, $ref2);
+
   $form->{GL} = [];
   while (my $ref0 = $sth->fetchrow_hashref(NAME_lc)) {
 
@@ -555,10 +558,16 @@ sub transaction {
   $query = qq|SELECT closedto, revtrans FROM defaults|;
   ($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
 
+  $query = qq|SELECT id, gldate
+              FROM gl
+              WHERE id = (SELECT max(id) FROM gl)|;
+  ($form->{previous_id}, $form->{previous_gldate}) = selectrow_query($form, $dbh, $query);
+
   if ($form->{id}) {
     $query =
-      qq|SELECT g.reference, g.description, g.notes, g.transdate,
-           d.description AS department, e.name AS employee, g.taxincluded, g.gldate
+      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, 
+         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)
@@ -568,7 +577,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
@@ -633,4 +642,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;