X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAR.pm;h=a211b1fcf6001e81168632305f00760d18c1006d;hb=b735f45033d8daef70ee2c82365a86cc06281f4c;hp=410ac99aa113180df77268af5b677d6a822d8d6c;hpb=f36eb9fca78c4940b486b7dafa4ab636cdaf99cd;p=kivitendo-erp.git

diff --git a/SL/AR.pm b/SL/AR.pm
index 410ac99aa..a211b1fcf 100644
--- a/SL/AR.pm
+++ b/SL/AR.pm
@@ -38,6 +38,8 @@ use Data::Dumper;
 use SL::DBUtils;
 use SL::MoreCommon;
 
+our (%myconfig, $form);
+
 sub post_transaction {
   $main::lxdebug->enter_sub();
 
@@ -50,6 +52,7 @@ sub post_transaction {
   my @values;
 
   my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
+  $form->{defaultcurrency} = $form->get_default_currency($myconfig);
 
   # set exchangerate
   $form->{exchangerate} = ($form->{currency} eq $form->{defaultcurrency}) ? 1 :
@@ -83,9 +86,6 @@ sub post_transaction {
     $query = qq|SELECT c.accno, t.taxkey, t.rate FROM tax t LEFT JOIN chart c ON (c.id = t.chart_id) WHERE t.id = ? ORDER BY c.accno|;
     ($form->{AR_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) = selectrow_query($form, $dbh, $query, $form->{"tax_id_$i"});
 
-    $form->{AR_amounts}{"tax_$i"}{taxkey}     = $form->{"taxkey_$i"};
-    $form->{AR_amounts}{"amounts_$i"}{taxkey} = $form->{"taxkey_$i"};
-
     if ($form->{taxincluded} *= 1) {
       $tax = $form->{"korrektur_$i"}
         ? $form->{"tax_$i"}
@@ -101,18 +101,21 @@ sub post_transaction {
   }
 
   # adjust paidaccounts if there is no date in the last row
-  $form->{paidaccounts}-- unless $form->{"datepaid_$form->{paidaccounts}"};
-  $form->{paid} = 0;
-
-  # add payments
-  for $i (1 .. $form->{paidaccounts}) {
-    $form->{"paid_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}), 2);
-    $form->{paid}     += $form->{"paid_$i"};
-    $form->{datepaid}  = $form->{"datepaid_$i"};
-  }
+  # this does not apply to stornos, where the paid field is set manually
+  unless ($form->{storno}) {
+    $form->{paidaccounts}-- unless $form->{"datepaid_$form->{paidaccounts}"};
+    $form->{paid} = 0;
+
+    # add payments
+    for $i (1 .. $form->{paidaccounts}) {
+      $form->{"paid_$i"} = $form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}), 2);
+      $form->{paid}     += $form->{"paid_$i"};
+      $form->{datepaid}  = $form->{"datepaid_$i"};
+    }
 
-  $form->{amount} = $form->{netamount} + $form->{total_tax};
-  $form->{paid}   = $form->round_amount($form->{paid} * $form->{exchangerate}, 2);
+    $form->{amount} = $form->{netamount} + $form->{total_tax};
+  }
+  $form->{paid}   = $form->round_amount($form->{paid} * ($form->{exchangerate} || 1), 2);
 
   ($null, $form->{employee_id}) = split /--/, $form->{employee};
 
@@ -147,7 +150,7 @@ sub post_transaction {
 
   # update exchangerate
   $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0)
-    if ($form->{currency} ne $form->{defaultcurrency}) && $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
+    if ($form->{currency} ne $form->{defaultcurrency}) && !$form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
 
   if (!$payments_only) {
     $query =
@@ -166,19 +169,20 @@ sub post_transaction {
     for $i (1 .. $form->{rowcount}) {
       if ($form->{"amount_$i"} != 0) {
         my $project_id = conv_i($form->{"project_id_$i"});
-        $taxkey = $form->{AR_amounts}{"amounts_$i"}{taxkey};
 
         # insert detail records in acc_trans
         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey)
                      VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|;
-        @values = (conv_i($form->{id}), conv_i($form->{AR_amounts}{"amount_$i"}), conv_i($form->{"amount_$i"}), conv_date($form->{transdate}), $project_id, conv_i($taxkey));
+        @values = (conv_i($form->{id}), conv_i($form->{AR_amounts}{"amount_$i"}), conv_i($form->{"amount_$i"}), conv_date($form->{transdate}), $project_id,
+                   conv_i($form->{"taxkey_$i"}));
         do_query($form, $dbh, $query, @values);
 
         if ($form->{"tax_$i"} != 0) {
           # insert detail records in acc_trans
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey)
                        VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|;
-          @values = (conv_i($form->{id}), conv_i($form->{AR_amounts}{"tax_$i"}), conv_i($form->{"tax_$i"}), conv_date($form->{transdate}), $project_id, conv_i($taxkey));
+          @values = (conv_i($form->{id}), conv_i($form->{AR_amounts}{"tax_$i"}), conv_i($form->{"tax_$i"}), conv_date($form->{transdate}), $project_id,
+                     conv_i($form->{"taxkey_$i"}));
           do_query($form, $dbh, $query, @values);
         }
       }
@@ -189,6 +193,11 @@ sub post_transaction {
                  VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|;
     @values = (conv_i($form->{id}), $form->{AR_amounts}{receivables}, conv_i($form->{receivables}), conv_date($form->{transdate}), $form->{AR_amounts}{receivables});
     do_query($form, $dbh, $query, @values);
+
+  } else {
+    # Record paid amount.
+    $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
+    do_query($form, $dbh, $query,  $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
   }
 
   # add paid transactions
@@ -271,34 +280,34 @@ sub _delete_payments {
 
   my ($self, $form, $dbh) = @_;
 
-  my @delete_oids;
+  my @delete_acc_trans_ids;
 
   # Delete old payment entries from acc_trans.
   my $query =
-    qq|SELECT oid
+    qq|SELECT acc_trans_id
        FROM acc_trans
        WHERE (trans_id = ?) AND fx_transaction
 
        UNION
 
-       SELECT at.oid
+       SELECT at.acc_trans_id
        FROM acc_trans at
        LEFT JOIN chart c ON (at.chart_id = c.id)
        WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
-  push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
+  push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
 
   $query =
-    qq|SELECT at.oid
+    qq|SELECT at.acc_trans_id
        FROM acc_trans at
        LEFT JOIN chart c ON (at.chart_id = c.id)
        WHERE (trans_id = ?)
          AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
-       ORDER BY at.oid
+       ORDER BY at.acc_trans_id
        OFFSET 1|;
-  push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
+  push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
 
-  if (@delete_oids) {
-    $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
+  if (@delete_acc_trans_ids) {
+    $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
     do_query($form, $dbh, $query);
   }
 
@@ -340,6 +349,7 @@ sub post_payment {
 
   ($form->{defaultcurrency}) = selectrow_query($form, $dbh, qq|SELECT curr FROM defaults|);
   $form->{defaultcurrency}   = (split m/:/, $form->{defaultcurrency})[0];
+  $form->{currency}          = $form->{defaultcurrency} if ($form->{defaultcurrency} && ($form->{currency} =~ m/^\s*$/));
 
   $form->{exchangerate}      = $form->format_amount($myconfig, $form->{exchangerate});
 
@@ -350,7 +360,7 @@ sub post_payment {
        LEFT JOIN chart c ON (at.chart_id = c.id)
        WHERE (trans_id = ?)
          AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
-       ORDER BY at.oid
+       ORDER BY at.acc_trans_id
        LIMIT 1|;
 
   ($form->{ARselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
@@ -397,23 +407,37 @@ sub ar_transactions {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->get_standard_dbh($myconfig);
 
   my @values;
 
   my $query =
-    qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate, | .
+    qq|SELECT DISTINCT a.id, a.invnumber, a.ordnumber, a.transdate, | .
     qq|  a.duedate, a.netamount, a.amount, a.paid, | .
     qq|  a.invoice, a.datepaid, a.terms, a.notes, a.shipvia, | .
-    qq|  a.shippingpoint, a.storno, a.globalproject_id, | .
+    qq|  a.shippingpoint, a.storno, a.storno_id, a.globalproject_id, | .
+    qq|  a.marge_total, a.marge_percent, | .
     qq|  a.transaction_description, | .
     qq|  pr.projectnumber AS globalprojectnumber, | .
-    qq|  c.name, | .
-    qq|  e.name AS employee | .
+    qq|  c.name, c.customernumber, c.country, c.ustid, | .
+    qq|  e.name AS employee, | .
+    qq|  e2.name AS salesman, | .
+    qq|  tz.description AS taxzone, | .
+    qq|  pt.description AS payment_terms, | .
+    qq{  ( SELECT ch.accno || ' -- ' || ch.description
+           FROM acc_trans at
+           LEFT JOIN chart ch ON ch.id = at.chart_id
+           WHERE ch.link ~ 'AR[[:>:]]'
+            AND at.trans_id = a.id
+            LIMIT 1
+          ) AS charts } .
     qq|FROM ar a | .
     qq|JOIN customer c ON (a.customer_id = c.id) | .
     qq|LEFT JOIN employee e ON (a.employee_id = e.id) | .
-    qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id)|;
+    qq|LEFT JOIN employee e2 ON (a.salesman_id = e2.id) | .
+    qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id)| .
+    qq|LEFT JOIN tax_zones tz ON (tz.id = c.taxzone_id)| .
+    qq|LEFT JOIN payment_terms pt ON (pt.id = c.payment_id)|;
 
   my $where = "1 = 1";
   if ($form->{customer_id}) {
@@ -457,29 +481,20 @@ sub ar_transactions {
     }
   }
 
-  my @a = (transdate, invnumber, name);
+  my @a = qw(transdate invnumber name);
   push @a, "employee" if $form->{l_employee};
-  my $sortorder = join(', ', @a);
+  my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
+  my $sortorder = join(', ', map { "$_ $sortdir" } @a);
 
-  if (grep({ $_ eq $form->{sort} }
-           qw(id transdate duedate invnumber ordnumber name
-              datepaid employee shippingpoint shipvia))) {
-    $sortorder = $form->{sort};
+  if (grep({ $_ eq $form->{sort} } qw(id transdate duedate invnumber ordnumber name datepaid employee shippingpoint shipvia transaction_description))) {
+    $sortorder = $form->{sort} . " $sortdir";
   }
 
-  $query .= " WHERE $where ORDER by $sortorder";
-
-  my $sth = $dbh->prepare($query);
-  $sth->execute(@values) ||
-    $form->dberror($query . " (" . join(", ", @values) . ")");
+  $query .= " WHERE $where ORDER BY $sortorder";
 
-  $form->{AR} = [];
-  while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{AR} }, $ar;
-  }
+  my @result = selectall_hashref_query($form, $dbh, $query, @values);
 
-  $sth->finish;
-  $dbh->disconnect;
+  $form->{AR} = [ @result ];
 
   $main::lxdebug->leave_sub();
 }
@@ -604,7 +619,62 @@ sub setup_form {
   $form->{tax} = $taxamount;
 
   $form->{invtotal} = $totalamount + $totaltax;
+
+  $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 ar 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->{invnumber}  = 'Storno-' . $storno_row->{invnumber};
+  $storno_row->{amount}    *= -1;
+  $storno_row->{netamount} *= -1;
+  $storno_row->{paid}       = $storno_row->{amount};
+
+  delete @$storno_row{qw(itime mtime)};
+
+  $query = sprintf 'INSERT INTO ar (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
+  do_query($form, $dbh, $query, (values %$storno_row));
+
+  $query = qq|UPDATE ar SET paid = amount + paid, storno = 't' WHERE id = ?|;
+  do_query($form, $dbh, $query, $id);
+
+  # now copy acc_trans entries
+  $query = qq|SELECT a.*, c.link FROM acc_trans a LEFT JOIN chart c ON a.chart_id = c.id WHERE a.trans_id = ? ORDER BY a.acc_trans_id|;
+  my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
+
+  # kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/
+  while ($rowref->[-1]{link} =~ /paid/) {
+    splice(@$rowref, -2);
+  }
+
+  for my $row (@$rowref) {
+    delete @$row{qw(itime mtime link)};
+    $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;