Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

[resolved] Perpetual Overdue Notifications

Hello,

I am interested in a feature that would continually send email alerts for overdue tickets to the assigned staff, based on a scheduled cron job, until overdue tickets have been resolved.

Previously, I was using Lacoste's code, found here: https://github.com/osTicket/osTicket/pull/1756. But I noticed that after the upgrade to 1.10, this code no longer functions.

I'm wondering if anyone has created this functionality for 1.10? Maybe someone that knows a bit of php/sql could help me out to modify the code to get it to work properly?

The error received is as follows:

DB Error #1054

[SELECT ticket_id FROM xxx_ticket T1  INNER JOIN xxx_ticket_status status

                ON
(status.id=T1.status_id AND status.state="open")  LEFT JOIN xxx_sla T2 ON (T1.sla_id=T2.id AND
T2.isactive=1)  WHERE isoverdue=1  ORDER BY T1.created]

 

Unknown column 'T2.isactive' in 'on clause'<br />
<br />

---- Backtrace ----<br />

#0 (root)/include/mysqli.php(204):
osTicket->logDBError('DB Error #1054', '[SELECT ticket_...')<br />

#1 (root)/include/class.ticket.php(3687):
db_query('SELECT ticket_i...')<br />

#2 (root)/api/notify_overdue.php(9):
Ticket::triggerOverdue()<br />

#3 {main}

Comments

  • This does not appear to be a suggestion or feedback.  Moving thread to Mods and Customizations.
  • I was able to solve the problem and create perpetual notifications successfully by changing Lacoste's custom code to the following:

    // Find overdue tickets and send notify the owners

        function triggerOverdue() {

            $sql='SELECT ticket_id FROM '.TICKET_TABLE.' T1 '

                //.' INNER JOIN '.TICKET_STATUS_TABLE.' status

                //    ON (status.id=T1.status_id AND status.state="open") '

                //.' LEFT JOIN '.SLA_TABLE.' T2 ON (T1.sla_id=T2.id AND T2.isactive=1) '

                .' WHERE isoverdue=1 '

    .' AND status_id=1'

                .' ORDER BY T1.created';

            if(($res=db_query($sql)) && db_num_rows($res)) {

                while(list($id)=db_fetch_row($res)) {

                    if( $ticket=Ticket::lookup($id) ) 

    $ticket->notifyOverdue();

                }

            } else {

                //TODO: Trigger escalation on already overdue tickets - make sure last overdue event > grace_period.

            }

       }

        static function agentActions($agent, $options=array()) {

            if (!$agent)

                return;



            require STAFFINC_DIR.'templates/tickets-actions.tmpl.php';

        }

    }

    ?>
This discussion has been closed.