osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now
Extent API to Get ticket information
I recently upgraded from a 1.6x to 1.9.9 and found that considerable amounts of changes were made to the database structure of the system. I have a process I built that takes the tickets that have been created in the previous 5 minutes and sweeps them into my Dynamics CRM. Here is the SQL I used
$sql = "select t.ticket_id, t.number, t.subject, t.status_id, t.duedate, p.priority, c.topic, t.created
from ost_ticket t
left outer join ost_ticket_priority p on p.priority_id = t.priority_id
left outer join ost_help_topic c on c.topic_id = t.topic_id
left outer join ost_department d on d.dept_id = t.dept_id
where (t.`updated` between '" . $lastTime . "' and '" . date('Y-m-d H:i:s') . "'
or t.`created` between '" . $lastTime . "' and '" . date('Y-m-d H:i:s') . "')
and d.dept_name = '" . $department . "'";
where lastTime is the 5 minutes ago and department is the department I want to look for new or modified tickets.
I also have other SQL that gets the thread and attachments.
With the changes to the database, these queries no longer function. I've tried to review the way the code is pulling the information and it is incredibly complex and difficult to follow.
It would be great if osTicket had a full-fledged API that you could perform any type of CRUD operation on. I know their is API functionality but from what I've seen and read, it only supports creation and nothing else.