Newer
Older
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->send($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->send($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)));
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
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();
// Change to SLA of the new department
$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->send($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->send($staff->getEmail(), $msg['subj'], $alert);
}
}
return $msgid;
}
/* public */
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 Response)';
$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;
}
//Insert Internal Notes
function logNote($title, $note, $poster, $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->send($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);
}
}
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
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()));
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
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
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
$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['slaId'] = array('type'=>'int', 'required'=>1, 'error'=>'SLA required');
$fields['priorityId'] = array('type'=>'int', 'required'=>1, 'error'=>'Priority required');
$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';
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);
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
$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());
if(($teams=$staff->getTeams()))
$sql.=' OR ticket.team_id IN('.implode(',', array_filter($teams)).')';
if(!$staff->showAssignedOnly() && ($depts=$staff->getDepts())) //Staff with limited access just see Assigned tickets.
$sql.=' OR ticket.dept_id IN('.implode(',', $depts).') ';
$sql.=')';
if(!$cfg || !($cfg->showAssignedTickets() || $staff->showAssignedTickets()))
$sql.=' AND (ticket.staff_id=0 OR ticket.staff_id='.db_input($staff->getId()).') ';
return db_fetch_array(db_query($sql));
}
/* Quick client's tickets stats
@email - valid email.
*/
function getClientStats($email) {
if(!$email || !Validator::is_email($email))
return null;
$sql='SELECT count(open.ticket_id) as open, 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\') '
.' LEFT JOIN '.TICKET_TABLE.' closed
ON (closed.ticket_id=ticket.ticket_id AND closed.status=\'closed\')'
.' WHERE ticket.email='.db_input($email);
return db_fetch_array(db_query($sql));
}
/*
* The mother of all functions...You break it you fix it!
*
* $autorespond and $alertstaff overwrites config settings...
function create($vars, &$errors, $origin, $autorespond=true, $alertstaff=true) {
global $ost, $cfg, $thisclient, $_FILES;
//Check for 403
if ($vars['email'] && Validator::is_email($vars['email'])) {
//Make sure the email address is not banned
if(TicketFilter::isBanned($vars['email'])) {
$errors['err']='Ticket denied. Error #403';
$ost->logWarning('Ticket denied', 'Banned email - '.$vars['email']);
return 0;
}
//Make sure the open ticket limit hasn't been reached. (LOOP CONTROL)
if($cfg->getMaxOpenTickets()>0 && strcasecmp($origin,'staff')
&& ($client=Client::lookupByEmail($vars['email']))
&& ($openTickets=$client->getNumOpenTickets())
&& ($openTickets>=$cfg->getMaxOpenTickets()) ) {
$errors['err']="You've reached the maximum open tickets allowed.";
$ost->logWarning('Ticket denied -'.$vars['email'],
sprintf('Max open tickets (%d) reached for %s ',
$cfg->getMaxOpenTickets(), $vars['email']));
//Init ticket filters...
$ticket_filter = new TicketFilter($origin, $vars);
// Make sure email contents should not be rejected
if($ticket_filter
&& ($filter=$ticket_filter->shouldReject())) {
$errors['err']='Ticket denied. Error #403';
$ost->logWarning('Ticket denied',
sprintf('Ticket rejected ( %s) by filter "%s"',
$vars['email'], $filter->getName()));