Oh yes, here's the output comparing a ['%unsubscribe%','%un-subscribe'] and
just a plain '%unsubscribe%' in the body:
With an array of things to search:
SQL: SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st, queue sq ,
article at WHERE sq.id = st.queue_id AND st.id = at.ticket_id AND
st.ticket_state_id IN (1, 4) AND st.ticket_lock_id IN (1) AND st.queue_id IN (5)
AND (at.a_body LIKE '%unsubscribe%' OR at.a_body LIKE '%un-subscribe%')
ORDER BY st.create_time_unix DESC
With a string to search:
SQL: SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st, queue sq ,
article at WHERE sq.id = st.queue_id AND st.id = at.ticket_id AND
st.ticket_state_id IN (1, 4) AND st.ticket_lock_id IN (1) AND st.queue_id IN (5)
AND at.a_subject LIKE 'Warning: could%' ORDER BY st.create_time_unix DESC
Hope that helps.
On Thu, 9 Sep 2004, Peter Beckman wrote:
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
[EMAIL PROTECTED] http://www.purplecow.com/
---------------------------------------------------------------------------
_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Support oder Consulting f�r Ihr OTRS System?
=> http://www.otrs.de/
---------------------------------------------------------------------------
Peter Beckman Internet Guy
[EMAIL PROTECTED] http://www.purplecow.com/
---------------------------------------------------------------------------
_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Support oder Consulting f�r Ihr OTRS System?
=> http://www.otrs.de/
