Newer
Older
if(!$dept || !($email = $dept->getAutoRespEmail()))
$email = $cfg->getDefaultEmail();
//If enabled...send confirmation to user. ( New Message AutoResponse)
if($email && $tpl && ($msg=$tpl->getNewMessageAutorepMsgTemplate())) {
$msg = $this->replaceVars($msg,
array('signature' => ($dept && $dept->isPublic())?$dept->getSignature():''));
//Reply separator tag.
if($cfg->stripQuotedReply() && ($tag=$cfg->getReplySeparator()))
$msg['body'] ="\n$tag\n\n".$msg['body'];
$email->sendAutoReply($this->getEmail(), $msg['subj'], $msg['body']);
function onAssign($assignee, $comments, $alert=true) {
if($this->isClosed()) $this->reopen(); //Assigned tickets must be open - otherwise why assign?
//Assignee must be an object of type Staff or Team
if(!$assignee || !is_object($assignee)) return false;
$comments = $comments?$comments:'Ticket assignment';
$assigner = $thisstaff?$thisstaff:'SYSTEM (Auto Assignment)';
//Log an internal note - no alerts on the internal note.
$this->logNote('Ticket Assigned to '.$assignee->getName(), $comments, $assigner, false);
//See if we need to send alerts
if(!$alert || !$cfg->alertONAssignment()) return true; //No alerts!
$dept = $this->getDept();
//Get template.
if(!$dept || !($tpl = $dept->getTemplate()))
//Email to use!
if(!($email=$cfg->getAlertEmail()))
$email = $cfg->getDefaultEmail();
//recipients
$recipients=array();
if(!strcasecmp(get_class($assignee), 'Staff')) {
if($cfg->alertStaffONAssignment())
$recipients[] = $assignee;
} elseif(!strcasecmp(get_class($assignee), 'Team')) {
if($cfg->alertTeamMembersONAssignment() && ($members=$assignee->getMembers()))
$recipients+=$members;
elseif($cfg->alertTeamLeadONAssignment() && ($lead=$assignee->getTeamLead()))
$recipients[] = $lead;
}
if($email && $recipients && $tpl && ($msg=$tpl->getAssignedAlertMsgTemplate())) {
array('comments' => $comments,
'assignee' => $assignee,
'assigner' => $assigner
if(!is_object($staff) || !$staff->isAvailable() || in_array($staff->getEmail(),$sentlist)) continue;
$alert = str_replace('%{recipient}', $staff->getFirstName(), $msg['body']);
$email->sendAlert($staff->getEmail(), $msg['subj'], $alert);
function onOverdue($whine=true, $comments="") {
if($whine && ($sla=$this->getSLA()) && !$sla->alertOnOverdue())
$whine = false;
//check if we need to send alerts.
if(!$whine || !$cfg->alertONOverdueTicket())
return true;
$dept = $this->getDept();
//Get department-defined or default template.
if(!$dept || !($tpl = $dept->getTemplate()))
$tpl= $cfg->getDefaultTemplate();
//Email to use!
if(!($email=$cfg->getAlertEmail()))
$email =$cfg->getDefaultEmail();
//Get the message template
if($tpl && ($msg=$tpl->getOverdueAlertMsgTemplate()) && $email) {
$msg = $this->replaceVars($msg, array('comments' => $comments));
//recipients
$recipients=array();
//Assigned staff or team... if any
if($this->isAssigned() && $cfg->alertAssignedONTransfer()) {
if($this->getStaffId())
$recipients[]=$this->getStaff();
elseif($this->getTeamId() && ($team=$this->getTeam()) && ($members=$team->getMembers()))
$recipients=array_merge($recipients, $members);
} elseif($cfg->alertDeptMembersONOverdueTicket() && !$this->isAssigned()) {
//Only alerts dept members if the ticket is NOT assigned.
$recipients=array_merge($recipients, $members);
}
//Always alert dept manager??
if($cfg->alertDeptManagerONOverdueTicket() && $dept && ($manager=$dept->getManager()))
$recipients[]= $manager;
$sentlist=array();
foreach( $recipients as $k=>$staff){
if(!is_object($staff) || !$staff->isAvailable() || in_array($staff->getEmail(),$sentlist)) continue;
$alert = str_replace("%{recipient}", $staff->getFirstName(), $msg['body']);
$email->sendAlert($staff->getEmail(), $msg['subj'], $alert);
}
//ticket obj as variable = ticket number.
function asVar() {
return $this->getNumber();
if($tag && is_callable(array($this, 'get'.ucfirst($tag))))
return call_user_func(array($this, 'get'.ucfirst($tag)));
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
switch(strtolower($tag)) {
case 'phone_number':
return $this->getPhoneNumber();
break;
case 'auth_token':
return $this->getAuthToken();
break;
case 'client_link':
return sprintf('%s/view.php?t=%s&e=%s&a=%s',
$cfg->getBaseUrl(), $this->getNumber(), $this->getEmail(), $this->getAuthToken());
break;
case 'staff_link':
return sprintf('%s/scp/tickets.php?id=%d', $cfg->getBaseUrl(), $this->getId());
break;
case 'create_date':
return Format::date(
$cfg->getDateTimeFormat(),
Misc::db2gmtime($this->getCreateDate()),
$cfg->getTZOffset(),
$cfg->observeDaylightSaving());
break;
case 'due_date':
$duedate ='';
if($this->getDueDate())
$duedate = Format::date(
$cfg->getDateTimeFormat(),
Misc::db2gmtime($this->getDueDate()),
$cfg->getTZOffset(),
$cfg->observeDaylightSaving());
return $duedate;
break;
case 'close_date';
$closedate ='';
if($this->isClosed())
$duedate = Format::date(
$cfg->getDateTimeFormat(),
Misc::db2gmtime($this->getCloseDate()),
$cfg->getTZOffset(),
$cfg->observeDaylightSaving());
return $closedate;
break;
return false;
}
//Replace base variables.
function replaceVars($input, $vars = array()) {
global $ost;
$vars = array_merge($vars, array('ticket' => $this));
return $ost->replaceTemplateVariables($input, $vars);
}
function markUnAnswered() {
return (!$this->isAnswered() || $this->setAnsweredState(0));
}
function markAnswered() {
return ($this->isAnswered() || $this->setAnsweredState(1));
}
function markOverdue($whine=true) {
global $cfg;
if($this->isOverdue())
return true;
$sql='UPDATE '.TICKET_TABLE.' SET isoverdue=1, updated=NOW() '
.' WHERE ticket_id='.db_input($this->getId());
if(!db_query($sql) || !db_affected_rows())
return false;
$this->logEvent('overdue');
function clearOverdue() {
if(!$this->isOverdue())
return true;
$sql='UPDATE '.TICKET_TABLE.' SET isoverdue=0, updated=NOW() ';
//clear due date if it's in the past
if($this->getDueDate() && strtotime($this->getDueDate())<=time())
$sql.=', duedate=NULL';
$sql.=' WHERE ticket_id='.db_input($this->getId());
return (db_query($sql) && db_affected_rows());
}
//Dept Tranfer...with alert.. done by staff
function transfer($deptId, $comments, $alert = true) {
if(!$thisstaff || !$thisstaff->canTransferTickets())
$currentDept = $this->getDeptName(); //Current department
if(!$deptId || !$this->setDeptId($deptId))
return false;
// Reopen ticket if closed
if($this->isClosed()) $this->reopen();
$this->reload();
// Set SLA of the new department
if(!$this->getSLAId())
$this->selectSLAId();
/*** log the transfer comments as internal note - with alerts disabled - ***/
$title='Ticket transfered from '.$currentDept.' to '.$this->getDeptName();
$this->logNote($title, $comments, $thisstaff, false);
//Send out alerts if enabled AND requested
if(!$alert || !$cfg->alertONTransfer() || !($dept=$this->getDept())) return true; //no alerts!!
//Get template.
if(!($tpl = $dept->getTemplate()))
$tpl= $cfg->getDefaultTemplate();
//Email to use!
if(!($email=$cfg->getAlertEmail()))
$email =$cfg->getDefaultEmail();
//Get the message template
if($tpl && ($msg=$tpl->getTransferAlertMsgTemplate()) && $email) {
$msg = $this->replaceVars($msg, array('comments' => $comments, 'staff' => $thisstaff));
//recipients
$recipients=array();
//Assigned staff or team... if any
if($this->isAssigned() && $cfg->alertAssignedONTransfer()) {
if($this->getStaffId())
$recipients[]=$this->getStaff();
elseif($this->getTeamId() && ($team=$this->getTeam()) && ($members=$team->getMembers()))
$recipients+=$members;
} elseif($cfg->alertDeptMembersONTransfer() && !$this->isAssigned()) {
//Only alerts dept members if the ticket is NOT assigned.
$recipients+=$members;
}
//Always alert dept manager??
if($cfg->alertDeptManagerONTransfer() && $dept && ($manager=$dept->getManager()))
$recipients[]= $manager;
$sentlist=array();
foreach( $recipients as $k=>$staff){
if(!is_object($staff) || !$staff->isAvailable() || in_array($staff->getEmail(),$sentlist)) continue;
$alert = str_replace('%{recipient}',$staff->getFirstName(), $msg['body']);
$email->sendAlert($staff->getEmail(), $msg['subj'], $alert);
}
}
return true;
}
function assignToStaff($staff, $note, $alert=true) {
if(!is_object($staff) && !($staff=Staff::lookup($staff)))
return false;
if(!$this->setStaffId($staff->getId()))
return false;
$this->logEvent('assigned');
return true;
}
function assignToTeam($team, $note, $alert=true) {
if(!is_object($team) && !($team=Team::lookup($team)))
return false;
if(!$this->setTeamId($team->getId()))
return false;
//Clear - staff if it's a closed ticket
// staff_id is overloaded -> assigned to & closed by.
if($this->isClosed())
$this->setStaffId(0);
$this->logEvent('assigned');
return true;
}
//Assign ticket to staff or team - overloaded ID.
function assign($assignId, $note, $alert=true) {
global $thisstaff;
$rv=0;
$id=preg_replace("/[^0-9]/", "",$assignId);
if($assignId[0]=='t') {
$rv=$this->assignToTeam($id, $note, $alert);
} elseif($assignId[0]=='s' || is_numeric($assignId)) {
$alert=($alert && $thisstaff && $thisstaff->getId()==$id)?false:$alert; //No alerts on self assigned tickets!!!
//We don't care if a team is already assigned to the ticket - staff assignment takes precedence
$rv=$this->assignToStaff($id, $note, $alert);
}
return $rv;
}
//unassign primary assignee
function unassign() {
if(!$this->isAssigned()) //We can't release what is not assigned buddy!
return true;
//We can only unassigned OPEN tickets.
if($this->isClosed())
return false;
//Unassign staff (if any)
if($this->getStaffId() && !$this->setStaffId(0))
return false;
//unassign team (if any)
if($this->getTeamId() && !$this->setTeamId(0))
return false;
$this->reload();
return true;
}
function release() {
return $this->unassign();
}
//Insert message from client
function postMessage($message,$source='',$emsgid=null,$headers='',$newticket=false){
global $cfg;
if(!$this->getId()) return 0;
Peter Rotich
committed
//Strip quoted reply...on emailed replies
if(!strcasecmp($source, 'Email')
&& $cfg->stripQuotedReply()
&& ($tag=$cfg->getReplySeparator()) && strpos($message, $tag))
list($message)=split($tag, $message);
Peter Rotich
committed
# XXX: Refuse auto-response messages? (via email) XXX: No - but kill our auto-responder.
$sql='INSERT INTO '.TICKET_THREAD_TABLE.' SET created=NOW()'
.' ,thread_type="M" '
# XXX: Put Subject header into the 'title' field
.' ,body='.db_input(Format::striptags($message)) //Tags/code stripped...meaning client can not send in code..etc
.' ,source='.db_input($source?$source:$_SERVER['REMOTE_ADDR'])
.' ,ip_address='.db_input($_SERVER['REMOTE_ADDR']);
if(!db_query($sql) || !($msgid=db_insert_id())) return 0; //bail out....
$this->setLastMsgId($msgid);
if ($emsgid !== null) {
$sql='INSERT INTO '.TICKET_EMAIL_INFO_TABLE
.' SET message_id='.db_input($msgid)
.', email_mid='.db_input($emsgid)
.', headers='.db_input($headers);
Peter Rotich
committed
db_query($sql);
if($newticket) return $msgid; //Our work is done...
$autorespond = true;
if ($autorespond && $headers && TicketFilter::isAutoResponse(Mail_Parse::splitHeaders($headers)))
$autorespond=false;
$this->onMessage($autorespond); //must be called b4 sending alerts to staff.
$dept = $this->getDept();
if(!$dept || !($tpl = $dept->getTemplate()))
$tpl= $cfg->getDefaultTemplate();
if(!($email=$cfg->getAlertEmail()))
$email =$cfg->getDefaultEmail();
//If enabled...send alert to staff (New Message Alert)
if($cfg->alertONNewMessage() && $tpl && $email && ($msg=$tpl->getNewMessageAlertMsgTemplate())) {
$msg = $this->replaceVars($msg, array('message' => $message));
//Build list of recipients and fire the alerts.
$recipients=array();
//Last respondent.
if($cfg->alertLastRespondentONNewMessage() || $cfg->alertAssignedONNewMessage())
$recipients[]=$this->getLastRespondent();
//Assigned staff if any...could be the last respondent
if($this->isAssigned() && ($staff=$this->getStaff()))
$recipients[]=$staff;
//Dept manager
if($cfg->alertDeptManagerONNewMessage() && $dept && ($manager=$dept->getManager()))
$recipients[]=$manager;
$sentlist=array(); //I know it sucks...but..it works.
foreach( $recipients as $k=>$staff){
if(!$staff || !$staff->getEmail() || !$staff->isAvailable() || in_array($staff->getEmail(), $sentlist)) continue;
$alert = str_replace('%{recipient}', $staff->getFirstName(), $msg['body']);
$email->sendAlert($staff->getEmail(), $msg['subj'], $alert);
}
}
return $msgid;
}
function postCannedReply($canned, $msgId, $alert=true) {
if((!is_object($canned) && !($canned=Canned::lookup($canned))) || !$canned->isEnabled())
return false;
$files = array();
foreach ($canned->getAttachments() as $file)
$files[] = $file['id'];
$info = array('msgId' => $msgId,
'response' => $this->replaceVars($canned->getResponse()),
'cannedattachments' => $files);
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
if(!($respId=$this->postReply($info, $errors, false)))
return false;
$this->markUnAnswered();
if(!$alert) return $respId;
$dept = $this->getDept();
if(!($tpl = $dept->getTemplate()))
$tpl= $cfg->getDefaultTemplate();
if(!$dept || !($email=$dept->getEmail()))
$email = $cfg->getDefaultEmail();
if($tpl && ($msg=$tpl->getAutoReplyMsgTemplate()) && $email) {
if($dept && $dept->isPublic())
$signature=$dept->getSignature();
else
$signature='';
$msg = $this->replaceVars($msg, array('response' => $info['response'], 'signature' => $signature));
if($cfg->stripQuotedReply() && ($tag=$cfg->getReplySeparator()))
$msg['body'] ="\n$tag\n\n".$msg['body'];
$attachments =($cfg->emailAttachments() && $files)?$this->getAttachments($respId, 'R'):array();
$email->sendAutoReply($this->getEmail(), $msg['subj'], $msg['body'], $attachments);
function postReply($vars, &$errors, $alert = true) {
if(!$vars['msgId'])
$errors['msgId'] ='Missing messageId - internal error';
if(!$vars['response'])
$errors['response'] = 'Response message required';
$poster = $thisstaff?$thisstaff->getName():'SYSTEM (Canned Reply)';
$sql='INSERT INTO '.TICKET_THREAD_TABLE.' SET created=NOW() '
.' ,thread_type="R"'
.' ,pid='.db_input($vars['msgId'])
.' ,body='.db_input(Format::striptags($vars['response']))
.' ,staff_id='.db_input($thisstaff?$thisstaff->getId():0)
.' ,poster='.db_input($poster)
.' ,ip_address='.db_input($thisstaff?$thisstaff->getIP():'');
if(!db_query($sql) || !($respId=db_insert_id()))
return false;
//Set status - if checked.
if(isset($vars['reply_ticket_status']) && $vars['reply_ticket_status'])
$this->setStatus($vars['reply_ticket_status']);
/* We can NOT recover from attachment related failures at this point */
$attachments = array();
//Web based upload.. note that we're not "validating" attachments on response.
if($_FILES['attachments'] && ($files=Format::files($_FILES['attachments'])))
$attachments=$this->uploadAttachments($files, $respId, 'R');
if($vars['cannedattachments'] && is_array($vars['cannedattachments'])) {
foreach($vars['cannedattachments'] as $fileId)
if($fileId && $this->saveAttachment($fileId, $respId, 'R'))
$attachments[] = $fileId;
}
$this->onResponse(); //do house cleaning..
$this->reload();
/* email the user?? - if disabled - the bail out */
if(!$alert) return $respId;
if(!($tpl = $dept->getTemplate()))
$tpl= $cfg->getDefaultTemplate();
if(!$dept || !($email=$dept->getEmail()))
$email = $cfg->getDefaultEmail();
if($tpl && ($msg=$tpl->getReplyMsgTemplate()) && $email) {
if($thisstaff && $vars['signature']=='mine')
$signature=$thisstaff->getSignature();
elseif($vars['signature']=='dept' && $dept && $dept->isPublic())
$signature=$dept->getSignature();
else
$signature='';
$msg = $this->replaceVars($msg,
array('response' => $vars['response'], 'signature' => $signature, 'staff' => $thisstaff));
if($cfg->stripQuotedReply() && ($tag=$cfg->getReplySeparator()))
$attachments =($cfg->emailAttachments() && $attachments)?$this->getAttachments($respId,'R'):array();
//TODO: setup 5 param (options... e.g mid trackable on replies)
$email->send($this->getEmail(), $msg['subj'], $msg['body'], $attachments);
}
return $respId;
}
//Activity log - saved as internal notes WHEN enabled!!
function logActivity($title,$note){
global $cfg;
if(!$cfg || !$cfg->logTicketActivity())
return 0;
return $this->logNote($title, $note, 'SYSTEM', false);
// History log -- used for statistics generation (pretty reports)
function logEvent($state, $annul=null, $staff=null) {
global $thisstaff;
if ($staff === null) {
if ($thisstaff) $staff=$thisstaff->getUserName();
else $staff='SYSTEM'; # XXX: Security Violation ?
}
# Annul previous entries if requested (for instance, reopening a
# ticket will annul an 'closed' entry). This will be useful to
# easily prevent repeated statistics.
if ($annul) {
db_query('UPDATE '.TICKET_EVENT_TABLE.' SET annulled=1'
.' WHERE ticket_id='.db_input($this->getId())
.' AND state='.db_input($annul));
}
return db_query('INSERT INTO '.TICKET_EVENT_TABLE
.' SET ticket_id='.db_input($this->getId())
.', staff_id='.db_input($this->getStaffId())
.', team_id='.db_input($this->getTeamId())
.', dept_id='.db_input($this->getDeptId())
.', topic_id='.db_input($this->getTopicId())
.', timestamp=NOW(), state='.db_input($state)
.', staff='.db_input($staff))
&& db_affected_rows() == 1;
}
function logNote($title, $note, $poster='SYSTEM', $alert=true) {
return $this->postNote(
array('title' => $title, 'note' => $note),
$errors,
$poster,
$alert);
}
function postNote($vars, &$errors, $poster, $alert=true) {
global $cfg, $thisstaff;
if(!$vars || !is_array($vars))
$errors['err'] = 'Missing or invalid data';
elseif(!$vars['note'])
$errors['note'] = 'Note required';
if($errors) return false;
$staffId = 0;
if($poster && is_object($poster)) {
$staffId = $poster->getId();
$poster = $poster->getName();
}
//TODO: move to class.thread.php
$sql= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET created=NOW() '.
',thread_type="N"'.
',title='.db_input(Format::striptags($vars['title']?$vars['title']:'[No Title]')).
',body='.db_input(Format::striptags($vars['note'])).
',poster='.db_input($poster);
//echo $sql;
if(!db_query($sql) || !($id=db_insert_id()))
return false;
//Upload attachments IF ANY - TODO: validate attachment types??
if($_FILES['attachments'] && ($files=Format::files($_FILES['attachments'])))
$attachments = $this->uploadAttachments($files, $id, 'N');
//Set state: Error on state change not critical!
if(isset($vars['state']) && $vars['state']) {
if($this->setState($vars['state']))
$this->reload();
}
// If alerts are not enabled then return a success.
if(!$alert || !$cfg->alertONNewNote() || !($dept=$this->getDept()))
return $id;
//Note obj.
$note = Note::lookup($id, $this->getId());
if(!($tpl = $dept->getTemplate()))
$tpl= $cfg->getDefaultTemplate();
if(!($email=$cfg->getAlertEmail()))
$email =$cfg->getDefaultEmail();
if($tpl && ($msg=$tpl->getNoteAlertMsgTemplate()) && $email) {
$msg = $this->replaceVars($msg, array('note' => $note));
// Alert recipients
$recipients=array();
//Last respondent.
if($cfg->alertLastRespondentONNewNote())
$recipients[]=$this->getLastRespondent();
//Assigned staff if any...could be the last respondent
if($cfg->alertAssignedONNewNote() && $this->isAssigned() && $this->getStaffId())
$recipients[]=$this->getStaff();
//Dept manager
if($cfg->alertDeptManagerONNewNote() && $dept && $dept->getManagerId())
$recipients[]=$dept->getManager();
$attachments =($attachments)?$this->getAttachments($id, 'N'):array();
$sentlist=array();
foreach( $recipients as $k=>$staff) {
if(!$staff || !is_object($staff) || !$staff->getEmail() || !$staff->isAvailable()) continue;
if(in_array($staff->getEmail(),$sentlist) || ($staffId && $staffId==$staff->getId())) continue;
$alert = str_replace('%{recipient}',$staff->getFirstName(), $msg['body']);
$email->sendAlert($staff->getEmail(), $msg['subj'], $alert, $attachments);
//Print ticket... export the ticket thread as PDF.
function pdfExport($psize='Letter', $notes=false) {
$pdf = new Ticket2PDF($this, $psize, $notes);
$name='Ticket-'.$this->getExtId().'.pdf';
//Remember what the user selected - for autoselect on the next print.
$_SESSION['PAPER_SIZE'] = $psize;
//online based attached files.
function uploadAttachments($files, $refid, $type) {
$uploaded=array();
foreach($files as $file) {
if(!$file['error']
&& ($id=AttachmentFile::upload($file))
&& $this->saveAttachment($id, $refid, $type))
$uploaded[]=$id;
elseif($file['error']!=UPLOAD_ERR_NO_FILE) {
// log file upload errors as interal notes + syslog debug.
if($file['error'] && gettype($file['error'])=='string')
$error = $file['error'];
else
$error ='Error #'.$file['error'];
$this->logNote('File Upload Error', $error, 'SYSTEM', false);
$ost->logDebug('File Upload Error (Ticket #'.$this->getExtId().')', $error);
}
}
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
return $uploaded;
}
/*
Save attachment to the DB. uploads (above), email or json/xml.
@file is a mixed var - can be ID or file hash.
*/
function saveAttachment($file, $refid, $type) {
if(!$refid || !$type || !($fileId=is_numeric($file)?$file:AttachmentFile::save($file)))
return 0;
$sql ='INSERT INTO '.TICKET_ATTACHMENT_TABLE.' SET created=NOW() '
.' ,ticket_id='.db_input($this->getId())
.' ,file_id='.db_input($fileId)
.' ,ref_id='.db_input($refid)
.' ,ref_type='.db_input($type);
return (db_query($sql) && ($id=db_insert_id()))?$id:0;
}
function deleteAttachments(){
// Clear reference table
$res=db_query('DELETE FROM '.TICKET_ATTACHMENT_TABLE.' WHERE ticket_id='.db_input($this->getId()));
if ($res && db_affected_rows())
$deleted = AttachmentFile::deleteOrphans();
return $deleted;
}
function delete(){
$sql='DELETE FROM '.TICKET_TABLE.' WHERE ticket_id='.$this->getId().' LIMIT 1';
if(!db_query($sql) || !db_affected_rows())
return false;
db_query('DELETE FROM '.TICKET_THREAD_TABLE.' WHERE ticket_id='.db_input($this->getId()));
$this->deleteAttachments();
return true;
}
function update($vars, &$errors) {
global $cfg, $thisstaff;
if(!$cfg || !$thisstaff || !$thisstaff->canEditTickets())
return false;
$fields=array();
$fields['name'] = array('type'=>'string', 'required'=>1, 'error'=>'Name required');
$fields['email'] = array('type'=>'email', 'required'=>1, 'error'=>'Valid email required');
$fields['subject'] = array('type'=>'string', 'required'=>1, 'error'=>'Subject required');
$fields['topicId'] = array('type'=>'int', 'required'=>1, 'error'=>'Help topic required');
$fields['priorityId'] = array('type'=>'int', 'required'=>1, 'error'=>'Priority required');
$fields['slaId'] = array('type'=>'int', 'required'=>0, 'error'=>'Select SLA');
$fields['phone'] = array('type'=>'phone', 'required'=>0, 'error'=>'Valid phone # required');
$fields['duedate'] = array('type'=>'date', 'required'=>0, 'error'=>'Invalid date - must be MM/DD/YY');
$fields['note'] = array('type'=>'text', 'required'=>1, 'error'=>'Reason for the update required');
if(!Validator::process($fields, $vars, $errors) && !$errors['err'])
$errors['err'] = 'Missing or invalid data - check the errors and try again';
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
if($vars['duedate']) {
if($this->isClosed())
$errors['duedate']='Duedate can NOT be set on a closed ticket';
elseif(!$vars['time'] || strpos($vars['time'],':')===false)
$errors['time']='Select time';
elseif(strtotime($vars['duedate'].' '.$vars['time'])===false)
$errors['duedate']='Invalid duedate';
elseif(strtotime($vars['duedate'].' '.$vars['time'])<=time())
$errors['duedate']='Due date must be in the future';
}
//Make sure phone extension is valid
if($vars['phone_ext'] ) {
if(!is_numeric($vars['phone_ext']) && !$errors['phone'])
$errors['phone']='Invalid phone ext.';
elseif(!$vars['phone']) //make sure they just didn't enter ext without phone #
$errors['phone']='Phone number required';
}
if($errors) return false;
$sql='UPDATE '.TICKET_TABLE.' SET updated=NOW() '
.' ,email='.db_input($vars['email'])
.' ,name='.db_input(Format::striptags($vars['name']))
.' ,subject='.db_input(Format::striptags($vars['subject']))
.' ,phone="'.db_input($vars['phone'],false).'"'
.' ,phone_ext='.db_input($vars['phone_ext']?$vars['phone_ext']:NULL)
.' ,priority_id='.db_input($vars['priorityId'])
.' ,topic_id='.db_input($vars['topicId'])
.' ,sla_id='.db_input($vars['slaId'])
.' ,duedate='.($vars['duedate']?db_input(date('Y-m-d G:i',Misc::dbtime($vars['duedate'].' '.$vars['time']))):'NULL');
if($vars['duedate']) { //We are setting new duedate...
$sql.=' ,isoverdue=0';
}
$sql.=' WHERE ticket_id='.db_input($this->getId());
if(!db_query($sql) || !db_affected_rows())
return false;
if(!$vars['note'])
$vars['note']=sprintf('Ticket Updated by %s', $thisstaff->getName());
$this->logNote('Ticket Updated', $vars['note'], $thisstaff);
/*============== Static functions. Use Ticket::function(params); ==================*/
function getIdByExtId($extId, $email=null) {
if(!$extId || !is_numeric($extId))
return 0;
$sql ='SELECT ticket_id FROM '.TICKET_TABLE.' ticket '
.' WHERE ticketID='.db_input($extId);
if($email)
$sql.=' AND email='.db_input($email);
if(($res=db_query($sql)) && db_num_rows($res))
list($id)=db_fetch_row($res);
return $id;
}
function lookup($id) { //Assuming local ID is the only lookup used!
return ($id && is_numeric($id) && ($ticket= new Ticket($id)) && $ticket->getId()==$id)?$ticket:null;
}
function lookupByExtId($id, $email=null) {
return self::lookup(self:: getIdByExtId($id, $email));
function genExtRandID() {
global $cfg;
//We can allow collissions...extId and email must be unique ...so same id with diff emails is ok..
// But for clarity...we are going to make sure it is unique.
$id=Misc::randNumber(EXT_TICKET_ID_LEN);
if(db_num_rows(db_query('SELECT ticket_id FROM '.TICKET_TABLE.' WHERE ticketID='.db_input($id))))
return Ticket::genExtRandID();
return $id;
}
function getIdByMessageId($mid,$email) {
if(!$mid || !$email)
return 0;
$sql='SELECT ticket.ticket_id FROM '.TICKET_TABLE. ' ticket '.
' LEFT JOIN '.TICKET_THREAD_TABLE.' msg USING(ticket_id) '.
' INNER JOIN '.TICKET_EMAIL_INFO_TABLE.' emsg ON (msg.id = emsg.message_id) '.
' WHERE email_mid='.db_input($mid).' AND email='.db_input($email);
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
$id=0;
if(($res=db_query($sql)) && db_num_rows($res))
list($id)=db_fetch_row($res);
return $id;
}
function getOpenTicketsByEmail($email){
$sql='SELECT count(*) as open FROM '.TICKET_TABLE.' WHERE status='.db_input('open').' AND email='.db_input($email);
if(($res=db_query($sql)) && db_num_rows($res))
list($num)=db_fetch_row($res);
return $num;
}
/* Quick staff's tickets stats */
function getStaffStats($staff) {
global $cfg;
/* Unknown or invalid staff */
if(!$staff || (!is_object($staff) && !($staff=Staff::lookup($staff))) || !$staff->isStaff() || $cfg->getDBVersion())
return null;
$sql='SELECT count(open.ticket_id) as open, count(answered.ticket_id) as answered '
.' ,count(overdue.ticket_id) as overdue, count(assigned.ticket_id) as assigned, count(closed.ticket_id) as closed '
.' FROM '.TICKET_TABLE.' ticket '
.' LEFT JOIN '.TICKET_TABLE.' open
ON (open.ticket_id=ticket.ticket_id AND open.status=\'open\' AND open.isanswered=0) '
.' LEFT JOIN '.TICKET_TABLE.' answered
ON (answered.ticket_id=ticket.ticket_id AND answered.status=\'open\' AND answered.isanswered=1) '
.' LEFT JOIN '.TICKET_TABLE.' overdue
ON (overdue.ticket_id=ticket.ticket_id AND overdue.status=\'open\' AND overdue.isoverdue=1) '
.' LEFT JOIN '.TICKET_TABLE.' assigned
ON (assigned.ticket_id=ticket.ticket_id AND assigned.status=\'open\' AND assigned.staff_id='.db_input($staff->getId()).')'
.' LEFT JOIN '.TICKET_TABLE.' closed
ON (closed.ticket_id=ticket.ticket_id AND closed.status=\'closed\' AND closed.staff_id='.db_input($staff->getId()).')'
.' WHERE (ticket.staff_id='.db_input($staff->getId());
$sql.=' OR ticket.team_id IN('.implode(',', db_input(array_filter($teams))).')';
if(!$staff->showAssignedOnly() && ($depts=$staff->getDepts())) //Staff with limited access just see Assigned tickets.
$sql.=' OR ticket.dept_id IN('.implode(',', db_input($depts)).') ';
$sql.=')';
if(!$cfg || !($cfg->showAssignedTickets() || $staff->showAssignedTickets()))
$sql.=' AND (ticket.staff_id=0 OR ticket.staff_id='.db_input($staff->getId()).') ';