<?php
/*********************************************************************
ajax.tickets.php
AJAX interface for tickets
Peter Rotich <peter@osticket.com>
Copyright (c) 2006-2012 osTicket
http://www.osticket.com
Released under the GNU General Public License WITHOUT ANY WARRANTY.
See LICENSE.TXT for details.
vim: expandtab sw=4 ts=4 sts=4:
**********************************************************************/
if(!defined('INCLUDE_DIR')) die('403');
include_once(INCLUDE_DIR.'class.ticket.php');
class TicketsAjaxAPI extends AjaxController {
function lookup() {
global $thisstaff;
if(!is_numeric($_REQUEST['q']))
return self::lookupByEmail();
$limit = isset($_REQUEST['limit']) ? (int) $_REQUEST['limit']:25;
$tickets=array();
$sql='SELECT DISTINCT ticketID, email'
.' FROM '.TICKET_TABLE
.' WHERE ticketID LIKE \''.db_input($_REQUEST['q'], false).'%\'';
$sql.=' AND ( staff_id='.db_input($thisstaff->getId());
if(($teams=$thisstaff->getTeams()) && count(array_filter($teams)))
$sql.=' OR team_id IN('.implode(',', array_filter($teams)).')';
if(!$thisstaff->showAssignedOnly() && ($depts=$thisstaff->getDepts()))
$sql.=' OR dept_id IN ('.implode(',', $depts).')';
$sql.=' ) '
.' ORDER BY created LIMIT '.$limit;
if(($res=db_query($sql)) && db_num_rows($res)) {
while(list($id, $email)=db_fetch_row($res))
$tickets[] = array('id'=>$id, 'email'=>$email, 'value'=>$id, 'info'=>"$id - $email");
}
return $this->json_encode($tickets);
}
function lookupByEmail() {
global $thisstaff;
$limit = isset($_REQUEST['limit']) ? (int) $_REQUEST['limit']:25;
$tickets=array();
$sql='SELECT email, count(ticket_id) as tickets '
.' FROM '.TICKET_TABLE
.' WHERE email LIKE \'%'.db_input(strtolower($_REQUEST['q']), false).'%\' ';
$sql.=' AND ( staff_id='.db_input($thisstaff->getId());
if(($teams=$thisstaff->getTeams()) && count(array_filter($teams)))
$sql.=' OR team_id IN('.implode(',', array_filter($teams)).')';
if(!$thisstaff->showAssignedOnly() && ($depts=$thisstaff->getDepts()))
$sql.=' OR dept_id IN ('.implode(',', $depts).')';
$sql.=' ) '
.' GROUP BY email '
.' ORDER BY created LIMIT '.$limit;
if(($res=db_query($sql)) && db_num_rows($res)) {
while(list($email, $count)=db_fetch_row($res))
$tickets[] = array('email'=>$email, 'value'=>$email, 'info'=>"$email ($count)");
}
return $this->json_encode($tickets);
}
function search() {
global $thisstaff;
$result=array();
$select = 'SELECT count(ticket.ticket_id) as tickets ';
$from = ' FROM '.TICKET_TABLE.' ticket ';
$where = ' WHERE 1 ';
//Access control.
$where.=' AND ( ticket.staff_id='.db_input($thisstaff->getId());
if(($teams=$thisstaff->getTeams()) && count(array_filter($teams)))
$where.=' OR ticket.team_id IN('.implode(',', array_filter($teams)).')';
if(!$thisstaff->showAssignedOnly() && ($depts=$thisstaff->getDepts()))
$where.=' OR ticket.dept_id IN ('.implode(',', $depts).')';
$where.=' ) ';
//Department
if($_REQUEST['deptId'])
$where.=' AND ticket.dept_id='.db_input($_REQUEST['deptId']);
//Status
switch(strtolower($_REQUEST['status'])) {
case 'open';
$where.=' AND ticket.status="open" ';
break;
case 'overdue':
$where.=' AND ticket.status="open" AND ticket.isoverdue=1 ';
break;
case 'closed':
$where.=' AND ticket.status="closed" ';
break;
}
//Assignee
if($_REQUEST['assignee'] && strcasecmp($_REQUEST['status'], 'closed')) {
$id=preg_replace("/[^0-9]/", "", $_REQUEST['assignee']);
$assignee = $_REQUEST['assignee'];
$where.= ' AND ( ';
if($assignee[0]=='t')
$where.=' (ticket.team_id='.db_input($id). ' AND ticket.status="open") ';
elseif($assignee[0]=='s')
$where.=' (ticket.staff_id='.db_input($id). ' AND ticket.status="open") ';
else
$where.=' (ticket.staff_id='.db_input($id). ' AND ticket.status="open") ';
if($_REQUEST['staffId'] && !$_REQUEST['status']) //Assigned TO + Closed By
$where.= ' OR (ticket.staff_id='.db_input($_REQUEST['staffId']). ' AND ticket.status="closed") ';
$where.= ' ) ';
} elseif($_REQUEST['staffId']) {
$where.=' AND (ticket.staff_id='.db_input($_REQUEST['staffId']).' AND ticket.status="closed") ';
}
//dates
$startTime =($_REQUEST['startDate'] && (strlen($_REQUEST['startDate'])>=8))?strtotime($_REQUEST['startDate']):0;
$endTime =($_REQUEST['endDate'] && (strlen($_REQUEST['endDate'])>=8))?strtotime($_REQUEST['endDate']):0;
if( ($startTime && $startTime>time()) or ($startTime>$endTime && $endTime>0))
$startTime=$endTime=0;
if($startTime)
$where.=' AND ticket.created>=FROM_UNIXTIME('.$startTime.')';
if($endTime)
$where.=' AND ticket.created<=FROM_UNIXTIME('.$endTime.')';
//Query
if($_REQUEST['query']) {
$queryterm=db_real_escape($_REQUEST['query'], false);
$from.=' LEFT JOIN '.TICKET_THREAD_TABLE.' thread ON (ticket.ticket_id=thread.ticket_id )';
$where.=" AND ( ticket.email LIKE '%$queryterm%'"
." OR ticket.name LIKE '%$queryterm%'"
." OR ticket.subject LIKE '%$queryterm%'"
." OR thread.title LIKE '%$queryterm%'"
." OR thread.body LIKE '%$queryterm%'"
.' )';
$groupby = 'GROUP BY ticket.ticket_id ';
}
$sql="$select $from $where $groupby";
if(($tickets=db_result(db_query($sql)))) {
$result['success'] =sprintf("Search criteria matched %s - <a href='tickets.php?%s'>view</a>",
($tickets>1?"$tickets tickets":"$tickets ticket"),
str_replace(array('&', '&'), array('&', '&'), $_SERVER['QUERY_STRING']));
} else {
$result['fail']='No tickets found matching your search criteria.';
}
return $this->json_encode($result);
}
function acquireLock($tid) {
global $cfg,$thisstaff;
if(!$tid or !is_numeric($tid) or !$thisstaff or !$cfg or !$cfg->getLockTime())
return 0;
if(!($ticket = Ticket::lookup($tid)) || !$ticket->checkStaffAccess($thisstaff))
return $this->json_encode(array('id'=>0, 'retry'=>false, 'msg'=>'Lock denied!'));
//is the ticket already locked?
if($ticket->isLocked() && ($lock=$ticket->getLock()) && !$lock->isExpired()) {
/*Note: Ticket->acquireLock does the same logic...but we need it here since we need to know who owns the lock up front*/
//Ticket is locked by someone else.??
if($lock->getStaffId()!=$thisstaff->getId())
return $this->json_encode(array('id'=>0, 'retry'=>false, 'msg'=>'Unable to acquire lock.'));
//Ticket already locked by staff...try renewing it.
$lock->renew(); //New clock baby!
} elseif(!($lock=$ticket->acquireLock($thisstaff->getId(),$cfg->getLockTime()))) {
//unable to obtain the lock..for some really weired reason!
//Client should watch for possible loop on retries. Max attempts?
return $this->json_encode(array('id'=>0, 'retry'=>true));
}
return $this->json_encode(array('id'=>$lock->getId(), 'time'=>$lock->getTime()));
}
function renewLock($tid, $id) {
global $thisstaff;
if(!$id or !is_numeric($id) or !$thisstaff)
return $this->json_encode(array('id'=>0, 'retry'=>true));
$lock= TicketLock::lookup($id);
if(!$lock || !$lock->getStaffId() || $lock->isExpired()) //Said lock doesn't exist or is is expired
return self::acquireLock($tid); //acquire the lock
if($lock->getStaffId()!=$thisstaff->getId()) //user doesn't own the lock anymore??? sorry...try to next time.
return $this->json_encode(array('id'=>0, 'retry'=>false)); //Give up...
//Renew the lock.
$lock->renew(); //Failure here is not an issue since the lock is not expired yet.. client need to check time!
return $this->json_encode(array('id'=>$lock->getId(), 'time'=>$lock->getTime()));
}
function releaseLock($tid, $id=0) {
global $thisstaff;
if($id && is_numeric($id)){ //Lock Id provided!
$lock = TicketLock::lookup($id, $tid);
//Already gone?
if(!$lock || !$lock->getStaffId() || $lock->isExpired()) //Said lock doesn't exist or is is expired
return 1;
//make sure the user actually owns the lock before releasing it.
return ($lock->getStaffId()==$thisstaff->getId() && $lock->release())?1:0;
}elseif($tid){ //release all the locks the user owns on the ticket.
return TicketLock::removeStaffLocks($thisstaff->getId(),$tid)?1:0;
}
return 0;
}
function previewTicket ($tid) {
global $thisstaff;
if(!$thisstaff || !($ticket=Ticket::lookup($tid)) || !$ticket->checkStaffAccess($thisstaff))
Http::response(404, 'No such ticket');
$staff=$ticket->getStaff();
$lock=$ticket->getLock();
$error=$msg=$warn=null;
if($lock && $lock->getStaffId()==$thisstaff->getId())
$warn.=' <span class="Icon lockedTicket">Ticket is locked by '.$lock->getStaffName().'</span>';
elseif($ticket->isOverdue())
$warn.=' <span class="Icon overdueTicket">Marked overdue!</span>';
ob_start();
echo sprintf(
'<div style="width:500px; padding: 2px 2px 0 5px;">
<h2>%s</h2><br>',Format::htmlchars($ticket->getSubject()));
if($error)
echo sprintf('<div id="msg_error">%s</div>',$error);
elseif($msg)
echo sprintf('<div id="msg_notice">%s</div>',$msg);
elseif($warn)
echo sprintf('<div id="msg_warning">%s</div>',$warn);
echo '<table border="0" cellspacing="" cellpadding="1" width="100%" class="ticket_info">';
$ticket_state=sprintf('<span>%s</span>',ucfirst($ticket->getStatus()));
if($ticket->isOpen()) {
if($ticket->isOverdue())
$ticket_state.=' — <span>Overdue</span>';
else
$ticket_state.=sprintf(' — <span>%s</span>',$ticket->getPriority());
}
echo sprintf('
<tr>
<th width="100">Ticket State:</th>
<td>%s</td>
</tr>
<tr>
<th>Create Date:</th>
<td>%s</td>
</tr>',$ticket_state,
Format::db_datetime($ticket->getCreateDate()));
if($ticket->isClosed()) {
echo sprintf('
<tr>
<th>Close Date:</th>
<td>%s <span class="faded">by %s</span></td>
</tr>',
Format::db_datetime($ticket->getCloseDate()),
($staff?$staff->getName():'staff')
);
} elseif($ticket->getDueDate()) {
echo sprintf('
<tr>
<th>Due Date:</th>
<td>%s</td>
</tr>',
Format::db_datetime($ticket->getDueDate()));
}
echo '</table>';
echo '<hr>
<table border="0" cellspacing="" cellpadding="1" width="100%" class="ticket_info">';
if($ticket->isOpen()) {
echo sprintf('
<tr>
<th width="100">Assigned To:</th>
<td>%s</td>
</tr>',$ticket->isAssigned()?implode('/', $ticket->getAssignees()):' <span class="faded">— Unassigned —</span>');
}
echo sprintf(
' <tr>
<th width="100">Department:</th>
<td>%s</td>
</tr>
<tr>
<th>Help Topic:</th>
<td>%s</td>
</tr>
<tr>
<th>From:</th>
<td>%s <span class="faded">%s</span></td>
</tr>',
Format::htmlchars($ticket->getDeptName()),
Format::htmlchars($ticket->getHelpTopic()),
Format::htmlchars($ticket->getName()),
$ticket->getEmail());
echo '
</table>';
$options[]=array('action'=>'Thread ('.$ticket->getThreadCount().')','url'=>"tickets.php?id=$tid");
if($ticket->getNumNotes())
$options[]=array('action'=>'Notes ('.$ticket->getNumNotes().')','url'=>"tickets.php?id=$tid#notes");
if($ticket->isOpen())
$options[]=array('action'=>'Reply','url'=>"tickets.php?id=$tid#reply");
if($thisstaff->canAssignTickets())
$options[]=array('action'=>($ticket->isAssigned()?'Reassign':'Assign'),'url'=>"tickets.php?id=$tid#assign");
if($thisstaff->canTransferTickets())
$options[]=array('action'=>'Transfer','url'=>"tickets.php?id=$tid#transfer");
$options[]=array('action'=>'Post Note','url'=>"tickets.php?id=$tid#note");
if($thisstaff->canEditTickets())
$options[]=array('action'=>'Edit Ticket','url'=>"tickets.php?id=$tid&a=edit");
if($options) {
echo '<ul class="tip_menu">';
foreach($options as $option)
echo sprintf('<li><a href="%s">%s</a></li>',$option['url'],$option['action']);
echo '</ul>';
}
echo '</div>';
$resp = ob_get_contents();
ob_end_clean();
return $resp;
}
}
?>