Billing feature

[alissonperes]

Hello All,

I'm searching for a way to use OTRS on my company but I need a "billing module".

What I need is a way to sum all acounted time on a ticket in OTRS and for each queue there is a diferent value for the time.

For an example, in my queue Security I have a value of 50 per hour, but in the service central the value ir 28 per hour.

What I need is to bill my customer acording to the time spent on each queue in total using the time accounting.

[reneeb]

For that, I would develop a stats module (in fact, I have a commercial addon that does something similar). It would be a static stats module. Parameters for that module: CustomerID and time span.

The accounted time is stored in an extra table....

[crythias]

viewtopic.php?f=60&t=12546&p=58304 might be of interest...

note that you might choose to include CASE WHEN

something like:

CASE q.name WHEN 'Security' THEN 50 WHEN 'Service Central' THEN 28 END

Note that the current SQL Query assumes the ticket is billed under one queue and has always been under that one queue. That you'd need to bill for time differently for one ticket under different queues is going to be ... different.

[alissonperes]

Thanks for the replies, does anyone know any material thar could help me create a new simple module in OTRS?

[alissonperes]

crythias, thanks for the query, it was very helful.

I'm using postgresql and had to change it, algo I changed the query a bit so thar I could get owner, state, ticket_id, service, sla and time_acconting.
The time_accounting is a sum function (so I get only one result per ticket and a sum of all time_accounting).

SELECT u.login AS owner,
ts.name AS state,
th.ticket_id AS ticket_id,
serv.name as service,
sla.solution_time as sla,
sum(ta.time_unit) as time_accounting
FROM ticket t
INNER JOIN (SELECT * FROM ticket_HISTORY WHERE ID IN (SELECT MAX(ID) FROM ticket_HISTORY WHERE ARTICLE_ID IS NOT NULL GROUP BY TICKET_ID) ORDER BY TICKET_ID) AS TH ON t.id = th.ticket_id
LEFT JOIN users u ON u.id = th.owner_id
LEFT JOIN ticket_state ts ON ts.id = th.state_id
LEFT JOIN time_accounting ta ON ta.ticket_id = t.id
left join service serv on serv.id = t.service_id
left join sla sla on sla.id = t.sla_id
group by u.login,
ts.name,
th.ticket_id,
serv.name,
sla.solution_time
order by th.ticket_id