- Edited
Osticket's 'overdue' status really threw us for a loop when we first started using it. We assumed it was like the other 'closed' or 'open' statuses, but it's not really. There is a 'isOverdue' field in the ticket table which it seems to me (I could be interpreting the code wrong) once it's set it can't be unset. You can close a ticket, re-open it, etc, but once it's gone past that duedate, it's toast. Since our cases are often ongoing and the ticket in 'good standing' as long as we have responded and are working with the partner we needed to change the overdue logic to something like we had with Request Tracker called 'stale' status. Not really wanting to do a new status with a bunch of additional logic around it I came up with tweaking the existing isOverdue flag to meet our needs.
These changes will alter the the 'isOverdue' flag so it is set solely based on time of last response to requestor. The grace period cofigured in the admin panel is used to determine how much time the ticket owner has to respond. The duedate is no longer used (it could be added back in to this logic as well, but we did not need it). As long as the ticket owner keeps responding within the grace period, the ticket will not be considered 'overdue.'
This requires changes to the way overdue is checked and additional logic to reset the overdue flag before the check and to be able to clear a single tickets overdue flag (on ticket close).
As always I would reccomend that you try this out with a test environment first and make sure it's working well for you before moving to your main OST setup.
$base_dir/include/class.ticket.php
First comment out the checkOverdue function and use this function instead
function checkOverdue(){
global $cfg;
if(!($hrs=$cfg->getGracePeriod()))
return 0;
$sec=$hrs*3600;
$sql='SELECT ticket_id FROM '.TICKET_TABLE.' WHERE status=\'open\''.
' AND ((TIME_TO_SEC(TIMEDIFF(NOW(),lastresponse))>='.$sec.'))'.
' AND ((TIME_TO_SEC(TIMEDIFF(NOW(),lastresponse))>=TIME_TO_SEC(TIMEDIFF(NOW(),lastmessage))))'.
' LIMIT 0 , 50';
//echo $sql;
if(($stale=db_query($sql)) && db_num_rows($stale)){
while(list($id)=db_fetch_row($stale)){
$ticket = new Ticket($id);
$ticket->markOverdue(true);
}
}
}
Add this additional function to remove all overdue flags (will be used during cron)
function resetOverdue(){
$sqlreset='SELECT ticket_id FROM '.TICKET_TABLE.' WHERE isoverdue=1';
if(($overdue=db_query($sqlreset)) && db_num_rows($overdue)){
while(list($id)=db_fetch_row($overdue)){
db_query('UPDATE '.TICKET_TABLE.' SET isoverdue=0,updated=NOW() WHERE ticket_id='.$id);
}
}
return true;
}
Add additional function to remove overdue flag for a ticket id on ticket update. This was a request from the team so that when they responded to an overdue ticket it immediately moved out of the overdue bucket... otherwise you would have to wait for the cron to run. It is called by the postResponse function (instructions below).
function clearOverdue($bitch=false){
global $cfg;
$sqlclear= 'UPDATE '.TICKET_TABLE.' SET isoverdue=0,updated=NOW() WHERE ticket_id='.db_input($this->getId());
if(db_query($sqlclear) && db_affected_rows()) {
return true;
}
else{
return false;
}
}
insert one line in postResponse() function
so that this
if(db_query($sql) && ($resp_id=db_insert_id())):
$this->onResponse(); //do house cleaning..
is changed to to this
if(db_query($sql) && ($resp_id=db_insert_id())):
//call my new clearOverdue function to remove overdue flag
$this->clearOverdue(true);
$this->onResponse(); //do house cleaning..
Then the cron needs to be updated so that is resets the overdue status and calls our checkOverdue.
$base_dir/include/class.cron.php
change the TicketMonitor function to use the new resetOverdue function in the ticket class
function TicketMonitor() {
require_once(INCLUDE_DIR.'class.ticket.php');
require_once(INCLUDE_DIR.'class.lock.php');
Ticket:(); //Clear all isoverdue flags
Ticket:(); //Make stale tickets overdue
TicketLock:(); //Remove expired locks
}
One tricky element here are the alerts that get set out to the ticket owner, admins, managers. If your cron is set to run every few minutes... you will get alerts set every few minutes for the same overdue tickets. We compromised to set the alert emails to run on the hr. I suppose the right way to do that would be to have another config field in the admin panel and pull the alert interval from there... but this works.
$base_dir/include/class.ticket.php
after this block
//Ok...we are ready to go....
$sentlist=array();
foreach( $recipients as $k=>$staff){
if(!$staff || !is_object($staff) || !$staff->isAvailable()) continue;
if(in_array($staff->getEmail(),$sentlist)) continue; //avoid duplicate emails.
$alert = str_replace("%staff",$staff->getFirstName(),$body);
add this
if(date("i") == "00"){$email->send($staff->getEmail(),null,$subj,$alert);}
Let me know how it goes.