I've had 'Accounted Time' activated for some time, and have been happy about it. That way my Support Engineers can add time spent on tickets quite easily. Another department here have been using the Time Accounting plug-in for registering their time. And both parties have been quite happy.
I've been quite unhappy with the fact that these two modules have not been integrated. So I have now solved this issue by adding a trigger to the MySQL database we use.
Below is the trigger we use:
Code: Select all
delimiter $$
CREATE TRIGGER accounted_time_to_accounting_table AFTER INSERT ON time_accounting FOR EACH ROW
BEGIN
INSERT INTO time_accounting_table (user_id, project_id, action_id, remark, time_start,time_end, period, created, article_id)
SELECT
time_accounting.create_by AS user_id,
24 AS project_id,
CASE
WHEN ticket.queue_id = 5 THEN 9
WHEN ticket.queue_id = 9 THEN 10
WHEN ticket.queue_id = 8 THEN 3
ELSE 7
END as action_id,
ticket.title AS remark,
DATE(time_accounting.create_time) AS time_start,
DATE(time_accounting.create_time) AS time_end,
time_accounting.time_unit / 60 AS period,
time_accounting.create_time AS created,
article_id
FROM
ticket,
time_accounting,
article
WHERE
time_accounting.article_id = article.id
AND
article.ticket_id = ticket.id
AND
time_accounting.article_id = NEW.article_id
END $$
delimiter;
- 24 is the id of the project
- 5, 9 and 8 are the id of the queues I want to use to add actions
- 9, 10, 3 and 7 are the id of the actions i want to use
Hope that me fixing my 'itch' helps you out!
Have a happy day!