[otrs] GenericAgent.pm Help -- Multiple Body Searches (heh)

9 Sep 2004

      Sorry about the title... it's descriptive at least.

So I've got GenericAgent.pm running, and it works great.

My only complaint is that we are trying to classify messages as they come
in based on Subject first, then by the text in the body of the message.

The problem is, I have about 30 keywords for 5 different queues.  I don't
particularly want to create 30 different "items" within GenericAgent, but
things don't seem to work using this:

     'Unsubscribe Tickets' => {
         # get all tickets with these properties
         Queue => 'Support',
         States => ['new', 'open'],
         Body => ['%unsubscribe%', '%un-subscribe%', '%cancel%'],
         Locks => ['unlock'],
         # new ticket properties
         New => {
             Queue => 'Support::Unsubscribe',
             Note => {
                 From => 'MoveTicketsAgent',
                 Subject => 'Ticket Moved!',
                 Body => 'Moved ticket from "Support" to "Unsubscribe" because that I do.',
                 ArticleType => 'note-internal', # note-internal|note-external|note-report
             },
         },
     },

What would be nice is if I could search for multiple items within a ticket,
kind of like this:

     select * from tickets where (body like '%unsubscribe%' or body like
     '%un-subscribe%' or body like '%cancel%')

So I guess the issue is in otrs/Kernel/System/Ticket.pm on line 1386:

     foreach my $Key (keys %FieldSQLMapFullText) {
         if ($Param{$Key}) {
             $Param{$Key} =~ s/\*/%/gi;
             $SQLExt .= " AND $FieldSQLMapFullText{$Key} LIKE '".$Self->{DBObject}->Quote($Param{$Key})."'";
         }
     }

Well, I decided to fix it myself.  Here's the diff:

*** Ticket-orig.pm      2004-09-09 14:39:39.000000000 -0400
--- Ticket-Beckman.pm   2004-09-09 14:43:01.000000000 -0400
***************
*** 1385,1392 ****
       );
       foreach my $Key (keys %FieldSQLMapFullText) {
           if ($Param{$Key}) {
!             $Param{$Key} =~ s/\*/%/gi;
!             $SQLExt .= " AND $FieldSQLMapFullText{$Key} LIKE '".$Self->{DBObject}->Quote($Param{$Key})."'";
           }
       }
       # ticket free text
--- 1385,1402 ----
       );
       foreach my $Key (keys %FieldSQLMapFullText) {
           if ($Param{$Key}) {
!             if (ref($Param{$Key}) eq "ARRAY") {
!                 foreach (@{$Param{$Key}}) {
!                     s/\*/%/gi;
!                     push (@{$Param{MyKeys}}, "$FieldSQLMapFullText{$Key} LIKE '".$Self->{DBObject}->Quote($_)."'");
!                 }
!                 if ($Param{MyKeys}) {
!                     $SQLExt .= " AND (${\(join ' OR ' , @{$Param{MyKeys}})})";
!                 }
!             } else {
!                 $Param{$Key} =~ s/\*/%/gi;
!                 $SQLExt .= " AND $FieldSQLMapFullText{$Key} LIKE '".$Self->{DBObject}->Quote($Param{$Key})."'";
!             }
           }
       }
       # ticket free text

---------------------------------------------------------------------------
Peter Beckman                                                  Internet Guy
beckman@purplecow.com                             http://www.purplecow.com/
---------------------------------------------------------------------------