What is your original issue/pain point you want to solve?
I have a script that interfaces with a phone call database that uses the Zammad API to create tickets with time accounting articles. Many calls can be logged for a single ticket, so we need to merge these tickets manually to have an accurate accounting.
Currently, the time accountings remain on the ticket they were created on even though the article the time accountings are associated with have been merged with a different ticket.
Which are one or two concrete situations where this problem hurts the most?
When I merge two tickets and I see all articles are migrated to a single ticket, the time accountings remain with the empty ticket. This requires me to manually merge time accountings if I want to use this feature.
Why is it not solvable with the Zammad standard?
Ticket merging needs to be properly implemented across all standard features. Ticket Time Accounting is part of Zammad standard, so it should also behave consistently with the other ticket attributes.
What is your expectation/what do you want to achieve?
I want to assign the amount of time spent on phone calls (from multiple calls) to a single ticket, but I want to create separate tickets initially as this is a limitation of my automation (I can’t know in advance what ticket I should log the call to). Merging tickets seems like a convenient way to merge time from multiple phone calls into a single ticket without needing to manually enter the time for each call.
This feels a bit like a foreign key violation, though not quite.
The ticket_time_accounting table entry references both the ticket_article_id and the ticket_id. It’s just weird that the time_accounting object and the ticket_article object would reference two different tickets.
I can’t find a way to migrate existing time accountings using the API. My two options to work around this are to either detect inconsistencies and then delete and create through the API, or do direct database modifications to change the ticket ID of the time accounting objects to match the article’s ticket ID.
For those that are brave enough to do direct database corrections, here is a relatively simple PostgreSQL query that automatically corrects this.
dangerzone
UPDATE ticket_time_accountings
SET ticket_id = ticket_articles.ticket_id
FROM ticket_articles
WHERE ticket_time_accountings.ticket_article_id = ticket_articles.id
AND ticket_time_accountings.ticket_id != ticket_articles.ticket_id;
The idea here is to find all time_accounting objects that have ticket_ids and ticket_article_ids where the ticket referenced by the time accounting and the article id are different. When these discrepancies are found, it updates the time_accounting’s ticket_id to align with the article.
If you run this query in a cron job, you can have it self correct every few minutes. There are risks involved with this solution, so tread cautiously.
Update - This solution is not perfect because the total time for a ticket is saved on tickets.time_unit. Having the time_accountings yanked out from under Zammad like this leaves the old empty ticket with some ugly time_unit totals. We can correct these too with a 2nd really dangerous query.
extra dangerzone
UPDATE tickets
SET time_unit = subquery.time_unit_total
FROM (
SELECT t.id AS ticket_id, COALESCE(SUM(tta.time_unit), NULL) AS time_unit_total
FROM tickets t
LEFT JOIN ticket_time_accountings tta
ON t.id = tta.ticket_id
WHERE t.time_unit IS NOT NULL
GROUP BY t.id
) AS subquery
WHERE tickets.id = subquery.ticket_id
AND tickets.time_unit IS DISTINCT FROM subquery.time_unit_total;
Unfortunately, even after the 2nd query, Zammad will still display the total time accounted until the next time the ticket is changed and saved - I assume this has to do with object caching in ElasticSearch.
Instead of fiddling with database, use a 3rd party integration or setup a separate app/project to “sniff” the date you need. This way you will never mess with the source material.
Ex, simple project with sqlite, python, API/Webhook fetching, store data, look for fields such as new updates to “Last update” date, store article note, store time_accounting and set it up however you like, report to a growing excel sheet or a simple GUI to show DB records etc.
API Ref: Introduction — Zammad System Documentation documentation
