Update to search messages
The developer hasn't posted regarding this, so I'm going to post the mod to allow you to search the message body (not just the subject).
//This sucks..mass scan! search anything that moves!
$qwhere.=" AND ( ticket.email LIKE '%$queryterm%'".
" OR ticket.name LIKE '%$queryterm%'".
//djtremors mod / fixed by Montclair
" OR responses.response LIKE '%$queryterm%'".
// Montclair mod to add searching of message body.
" OR messages.message LIKE '%$queryterm%'".
" OR ticket.subject LIKE '%$queryterm%'".
' ) ';
then further down...
//Montclair revised query with "lastupdated" calculated field & message body searching.
//Includes Assigned To mod, and corrected searching responses mod.
//Also moved original developer's code for attachments into the main query (saw no reason to have it separated.)
$qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,email,dept_name,responses.response'.
',status,ticket.source,isoverdue,ticket.created,pri.*'.
',GREATEST(ticket.created,IFNULL(ticket.updated,0),MAX(messages.created),MAX(IFNULL(messages.updated,0)),MAX(IFNULL(responses.created,0)),MAX(IFNULL(responses.updated,0))) AS lastupdated'.
',COUNT(attach_id) AS attachments'.
',messages.message';
$qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
' LEFT JOIN '.TICKET_RESPONSE_TABLE.' responses ON ticket.ticket_id=responses.ticket_id '.
' LEFT JOIN '.TICKET_MESSAGE_TABLE.' messages ON ticket.ticket_id=messages.ticket_id '.
' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id '.
' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON ticket.priority_id=pri.priority_id '.
' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() ';
//get ticket count based on the query so far..
//Montclair revision to reflect accurate ticket count.
$total=db_count("SELECT COUNT(DISTINCT ticket.ticket_id) $qfrom $qwhere");
//echo $total;
//pagenate
$pageNav=new Pagenate($total,$page,$pagelimit);
$pageNav->setURL('tickets.php',$qstr.'&sort='.urlencode($_REQUEST).'&order='.urlencode($_REQUEST));
//
//Ok..lets roll...create the actual query
//ADD attachment count crap..
//$qselect.=' ,count(attach_id) as attachments ';
//$qfrom.=' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ticket.ticket_id=attach.ticket_id ';
$qgroup=' GROUP BY ticket.ticket_id';
$query="$qselect $qfrom $qwhere $qgroup ORDER BY $order_by $order LIMIT ".$pageNav->getStart().",".$pageNav->getLimit();
The above includes additional fields for other mods which should have no ill effect if you have not implemented them.
I'll likely post all the mods I've assembled from others, and coded myself, and post it in a zip file later. As of now, I have a summary of the last message posted by each client displaying beneath each ticket.