Volltext-Suche im Auftragsbericht

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

diff --git a/SL/OE.pm b/SL/OE.pm
index b273d81..07fde70 100644 (file)
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -116,6 +116,13 @@ sub transactions {
     }
   }
 
+  my ($phone_notes_columns, $phone_notes_join);
+  $form->{phone_notes} = trim($form->{phone_notes});
+  if ($form->{phone_notes}) {
+    $phone_notes_columns = qq| , phone_notes.subject AS phone_notes_subject, phone_notes.body AS phone_notes_body |;
+    $phone_notes_join    = qq| JOIN notes phone_notes ON (o.id = phone_notes.trans_id AND phone_notes.trans_module LIKE 'oe') |;
+  }
+
   $query =
     qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
     qq|  o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
@@ -133,6 +140,7 @@ sub transactions {
     qq|  ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id,  | .
     qq|  tz.description AS taxzone | .
     $periodic_invoices_columns .
+    $phone_notes_columns .
     qq|  , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
     qq|FROM oe o | .
     qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
@@ -146,6 +154,7 @@ sub transactions {
     qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
     qq|LEFT JOIN department   ON (o.department_id = department.id) | .
     qq|$periodic_invoices_joins | .
+    $phone_notes_join .
     qq|WHERE (o.quotation = ?) |;
   push(@values, $quotation);
 
@@ -304,6 +313,30 @@ SQL
     push(@values, like($form->{intnotes}));
   }
 
+  if ($form->{phone_notes}) {
+    $query .= qq| AND (phone_notes.subject ILIKE ? OR phone_notes.body ILIKE ?)|;
+    push(@values, like($form->{phone_notes}), like($form->{phone_notes}));
+  }
+
+  $form->{fulltext} = trim($form->{fulltext});
+  if ($form->{fulltext}) {
+    my @fulltext_fields = qw(o.notes
+                             o.intnotes
+                             o.shippingpoint
+                             o.shipvia
+                             o.transaction_description
+                             o.quonumber
+                             o.ordnumber
+                             o.cusordnumber);
+    $query .= ' AND (';
+    $query .= join ' ILIKE ? OR ', @fulltext_fields;
+    $query .= ' ILIKE ?';
+    $query .= qq| OR EXISTS (SELECT files.id FROM files LEFT JOIN file_full_texts ON (file_full_texts.file_id = files.id) WHERE files.object_id = o.id AND files.object_type = 'sales_order' AND file_full_texts.full_text ILIKE ?)|;
+    $query .= qq| OR EXISTS (SELECT notes.id FROM notes WHERE notes.trans_id = o.id AND notes.trans_module LIKE 'oe' AND (notes.subject ILIKE ? OR notes.body ILIKE ?))|;
+    $query .= ')';
+    push(@values, like($form->{fulltext})) for 1 .. (scalar @fulltext_fields) + 3;
+  }
+
   if ($form->{parts_partnumber}) {
     $query .= <<SQL;
       AND EXISTS (