I'm creating a ticket report as a customization. In the my OTRS 4.x system can have ticket and task (creates via split functionality), But I'd like to count all tickets had been created in a period time, not count tasks. So my query would be
Code: Select all
SELECT Count(DISTINCT ticket.id) AS COUNT
FROM ticket
LEFT JOIN link_relation_copy
ON ticket.id = link_relation_copy.target_key
WHERE link_relation_copy.target_key IS NULL
AND ticket.create_time >= '2012-01-01'
AND ticket.create_time < '2014-01-01';
Solution 1: Check direct in database: tartget_key is varchar (link_relation table), but id is bigint (ticket table). A big question: why did OTRS design a different like that? So it will take a lot of time in query ticket table join to link_relation table, it has to convert bigint into varchar. Therefore I changed the target_key is bigint that improved a lot, but I don't know what impact to OTRS core?
Solution 2: Look in API of TicketSearch of OTRS core. Is it possible to filter the paramter can be adapt the report requirement? How can I set parameters
Thanks for your time can help or give me some idea
Vu Nguyen