From 991369b17d679e2855f4d5086d0b9769dc2ae1aa Mon Sep 17 00:00:00 2001
From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= <bernd@kivitendo-premium.de>
Date: Fri, 22 Apr 2022 12:35:04 +0200
Subject: [PATCH] Volltext-Suche im Auftragsbericht

---
 SL/OE.pm                          | 19 +++++++++++++++++++
 bin/mozilla/oe.pl                 |  3 ++-
 locale/de/all                     |  1 +
 locale/en/all                     |  1 +
 templates/webpages/oe/search.html |  6 ++++++
 5 files changed, 29 insertions(+), 1 deletion(-)

diff --git a/SL/OE.pm b/SL/OE.pm
index d91c4c513..07fde7071 100644
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -318,6 +318,25 @@ SQL
     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 (
diff --git a/bin/mozilla/oe.pl b/bin/mozilla/oe.pl
index 7dcdfaa8b..d8593351b 100644
--- a/bin/mozilla/oe.pl
+++ b/bin/mozilla/oe.pl
@@ -1102,7 +1102,7 @@ sub orders {
                                                         reqdatefrom reqdateto projectnumber project_id periodic_invoices_active periodic_invoices_inactive
                                                         business_id shippingpoint taxzone_id reqdate_unset_or_old insertdatefrom insertdateto
                                                         order_probability_op order_probability_value expected_billing_date_from expected_billing_date_to
-                                                        parts_partnumber parts_description all department_id intnotes phone_notes);
+                                                        parts_partnumber parts_description all department_id intnotes phone_notes fulltext);
   push @hidden_variables, map { "cvar_$_->{name}" } @ct_searchable_custom_variables;
 
   my   @keys_for_url = grep { $form->{$_} } @hidden_variables;
@@ -1189,6 +1189,7 @@ sub orders {
   push @options, $locale->text('Part Description')        . " : $form->{parts_description}"               if $form->{parts_description};
   push @options, $locale->text('Part Number')             . " : $form->{parts_partnumber}"                if $form->{parts_partnumber};
   push @options, $locale->text('Phone Notes')             . " : $form->{phone_notes}"                     if $form->{phone_notes};
+  push @options, $locale->text('Full Text')               . " : $form->{fulltext}"                        if $form->{fulltext};
   if ( $form->{transdatefrom} or $form->{transdateto} ) {
     push @options, $locale->text('Order Date');
     push @options, $locale->text('From') . " " . $locale->date(\%myconfig, $form->{transdatefrom}, 1)     if $form->{transdatefrom};
diff --git a/locale/de/all b/locale/de/all
index ecb9551c8..a1358af61 100755
--- a/locale/de/all
+++ b/locale/de/all
@@ -1635,6 +1635,7 @@ $self->{texts} = {
   'Front page'                  => 'Hauptseite',
   'Full Access'                 => 'Vollzugriff',
   'Full Preview'                => 'Alles',
+  'Full Text'                   => 'Volltext',
   'Full access to all functions' => 'Vollzugriff auf alle Funktionen',
   'Function block'              => 'Funktionsblock',
   'Function block actions'      => 'Funktionsblockaktionen',
diff --git a/locale/en/all b/locale/en/all
index 035c4470c..4df79de04 100644
--- a/locale/en/all
+++ b/locale/en/all
@@ -1635,6 +1635,7 @@ $self->{texts} = {
   'Front page'                  => '',
   'Full Access'                 => '',
   'Full Preview'                => '',
+  'Full Text'                   => '',
   'Full access to all functions' => '',
   'Function block'              => '',
   'Function block actions'      => '',
diff --git a/templates/webpages/oe/search.html b/templates/webpages/oe/search.html
index 9453f6b49..7f85ff379 100644
--- a/templates/webpages/oe/search.html
+++ b/templates/webpages/oe/search.html
@@ -84,6 +84,12 @@
      <th align="right">[% 'Phone Notes' | $T8 %]</th>
      <td>[% L.input_tag('phone_notes', '', style=style) %]</td>
     </tr>
+    [%- IF type == 'sales_order' %]
+    <tr>
+     <th align="right">[% 'Full Text' | $T8 %]</th>
+     <td>[% L.input_tag('fulltext', '', style=style) %]</td>
+    </tr>
+    [%- END %]
     <tr>
      <th align="right">[% IF is_order %][% 'Order Date' | $T8 %][% ELSE %][% 'Quotation Date' | $T8 %][% END %] [% 'From' | $T8 %]</th>
      <td>
-- 
2.20.1