X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FOE.pm;h=a517e05d2a3fb8ea24d724569789b9964f19abe8;hb=1b7759ed7c474adef6eb75b6cc04431768fff8c3;hp=ab0eb94443fd9d7c01a1030711db835c25cb53a0;hpb=1f22556352ca6a32f921583b922ddb51e9760a4b;p=kivitendo-erp.git

diff --git a/SL/OE.pm b/SL/OE.pm
index ab0eb9444..a517e05d2 100644
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -100,6 +100,12 @@ sub transactions {
         FROM record_links rl1
         LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
         WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'
+        UNION
+        SELECT rl1.from_id, rl3.to_id
+        FROM record_links rl1
+        JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)
+        JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id)
+        WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar' AND rl3.to_table = 'ar'
       ) rl
       LEFT JOIN ar ON ar.id = rl.to_id
 
@@ -115,9 +121,11 @@ sub transactions {
     qq|  o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
     qq|  o.transaction_description, | .
     qq|  o.marge_total, o.marge_percent, | .
+    qq|  o.exchangerate, | .
     qq|  o.itime::DATE AS insertdate, | .
+    qq|  o.intnotes, | .
     qq|  department.description as department, | .
-    qq|  ex.$rate AS exchangerate, | .
+    qq|  ex.$rate AS daily_exchangerate, | .
     qq|  pt.description AS payment_terms, | .
     qq|  pr.projectnumber AS globalprojectnumber, | .
     qq|  e.name AS employee, s.name AS salesman, | .
@@ -289,6 +297,11 @@ SQL
     push @values, conv_date($form->{expected_billing_date_to});
   }
 
+  if ($form->{intnotes}) {
+    $query .= qq| AND o.intnotes ILIKE ?|;
+    push(@values, like($form->{intnotes}));
+  }
+
   if ($form->{parts_partnumber}) {
     $query .= <<SQL;
       AND EXISTS (
@@ -356,6 +369,7 @@ SQL
     "taxzone"                 => "tz.description",
     "payment_terms"           => "pt.description",
     "department"              => "department.description",
+    "intnotes"                => "o.intnotes",
   );
   if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
     $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}"  . ", o.itime ${sortdir}";
@@ -371,9 +385,15 @@ SQL
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     $ref->{billed_amount}    = $billed_amount{$ref->{id}};
     $ref->{billed_netamount} = $billed_netamount{$ref->{id}};
-    $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
-    $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
-    $ref->{exchangerate} = 1 unless $ref->{exchangerate};
+    if ($ref->{billed_amount} < 0) { # case: credit note(s) higher than invoices
+      $ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};
+      $ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};
+    } else {
+      $ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};
+      $ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};
+    }
+    $ref->{exchangerate} ||= $ref->{daily_exchangerate};
+    $ref->{exchangerate} ||= 1;
     push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
     $id{ $ref->{id} } = $ref->{id};
   }
@@ -599,6 +619,9 @@ sub _save {
         require SL::DB::Customer;
         my $customer = SL::DB::Manager::Customer->find_by(id => $form->{customer_id});
         die "Can't find customer" unless $customer;
+        die $main::locale->text("Error while creating project with project number of new order number, project number #1 already exists!", $form->{ordnumber})
+          if SL::DB::Manager::Project->find_by(projectnumber => $form->{ordnumber});
+
         my $new_project = SL::DB::Project->new(
           projectnumber     => $form->{ordnumber},
           description       => $customer->name,
@@ -609,7 +632,7 @@ sub _save {
         );
         $new_project->save;
         $form->{"globalproject_id"} = $new_project->id;
-      };
+      }
 
       CVar->get_non_editable_ic_cvars(form               => $form,
                                       dbh                => $dbh,
@@ -727,7 +750,7 @@ SQL
   $query =
     qq|UPDATE oe SET
          ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
-         customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
+         customer_id = ?, amount = ?, netamount = ?, reqdate = ?, tax_point = ?, taxincluded = ?,
          shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
          delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
          taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?,
@@ -738,7 +761,7 @@ SQL
   @values = ($form->{ordnumber} || '', $form->{quonumber},
              $form->{cusordnumber}, conv_date($form->{transdate}),
              conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
-             $amount, $netamount, conv_date($reqdate),
+             $amount, $netamount, conv_date($reqdate), conv_date($form->{tax_point}),
              $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
              $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
              $form->{currency}, $form->{closed} ? 't' : 'f',
@@ -1001,7 +1024,7 @@ sub _retrieve {
            o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
            (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
            o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
-           o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
+           o.closed, o.reqdate, o.tax_point, o.quonumber, o.department_id, o.cusordnumber,
            o.mtime, o.itime,
            d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
            o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
@@ -1080,7 +1103,7 @@ sub _retrieve {
       map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
     }    # if !@ids
 
-    my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
+    my $transdate = $form->{tax_point} ? $dbh->quote($form->{tax_point}) : $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
 
     $form->{taxzone_id} = 0 unless ($form->{taxzone_id});
     unshift @values, ($form->{taxzone_id}) x 2;
@@ -1181,7 +1204,7 @@ sub _retrieve {
       # get tax rates and description
       my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =
-        qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber | .
+        qq|SELECT c.accno, t.taxdescription, t.rate, t.id as tax_id, c.accno as taxnumber | .
         qq|FROM tax t | .
         qq|LEFT JOIN chart c on (c.id = t.chart_id) | .
         qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
@@ -1201,6 +1224,7 @@ sub _retrieve {
           $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
           $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
           $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
+          $form->{"$ptr->{accno}_tax_id"}      = $ptr->{tax_id};
           $form->{taxaccounts} .= "$ptr->{accno} ";
         }
 
@@ -1517,9 +1541,9 @@ sub order_details {
         # get parts and push them onto the stack
         my $sortorder = "";
         if ($form->{groupitems}) {
-          $sortorder = qq|ORDER BY pg.partsgroup, a.oid|;
+          $sortorder = qq|ORDER BY pg.partsgroup, a.position|;
         } else {
-          $sortorder = qq|ORDER BY a.oid|;
+          $sortorder = qq|ORDER BY a.position|;
         }
 
         $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
@@ -1578,17 +1602,13 @@ sub order_details {
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} },        $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} },  $form->{"${item}_rate"} * 100);
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} },      $form->{"${item}_taxnumber"});
+    push(@{ $form->{TEMPLATE_ARRAYS}->{tax_id} },         $form->{"${item}_tax_id"});
 
-    my $tax_objs     = SL::DB::Manager::Tax->get_objects_from_sql(
-      sql  => 'SELECT * from tax where chart_id = (SELECT id FROM chart WHERE accno = ?)',
-      args => [ $form->{"${item}_taxnumber"} ]
-    );
-    my $tax_obj;
-    if ( $tax_objs ) {
-      $tax_obj     = $tax_objs->[0];
+    if ( $form->{"${item}_tax_id"} ) {
+      my $tax_obj = SL::DB::Manager::Tax->find_by(id => $form->{"${item}_tax_id"}) or die "Can't find tax with id " . $form->{"${item}_tax_id"};
+      my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
+      push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
     }
-    my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
-    push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
   }
 
   $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);