[SOLVED] Report to differentiating open tickets mail/phone?

[amdkryn]

How to generate report differentiating open tickets via email and by phone?
Can be with a simple search or report. I activated the opening of tickets by email and want to know which option is being used more.

tks,

[amdkryn]

Someone? Any suggestions on how to differentiate simple search or report?

[Giulio Soleni]

Hi,
I do not think there is an out-of-the-box feature to distinguish tickets created by email from tickets created by the web console.
The shortest way imho is to define a dynamic fileld, let say tk_origin like a dropbox with two values 'email' and 'web' (default) and force via Postmaster Filter every incoming email to create a ticket with the tk_origin = 'email' attribute set. You may then take care to let your customers set the same attribute as 'web' (only condition allowed) when they create a tk from the web console, or else you simply may left everything as it already is ... therefore only tickets created via email will set the tk_origin attribute as 'email', while all other "ordinary" tickets will not set that attribute at all.

Maybe instead you may define a new state, let's say "new from email" of type "new" and again via Postmaster Filter set a filter that force all incoming email to create a ticket with the "new from email" state instead of the "new" state. However mind that dealing with custom states is always a little bit more difficult than using custom attribute... since you may easily incur in some possible errors or inconsistencies...

HTH

[crythias]

WebRequestCustomer is ticket_history_type.id=29
PhoneCallCustomer is ticket_history_type.id=14

to find ticket_history_type ids:
SELECT id,name FROM ticket_history_type

select count(*), history_type_id from ticket_history where history_type_id in (14,29) group by history_type_id

add numbers as needed for additional breakdowns.

[swype]

integrate a prefiled dynamic-field, with "phone" in the phone-view and add a postmaster-filter which set the field to "mail".
Than you can use the Statistic-Engine.

cheers
swype

PS:
a dropdown would be the best, controlled by an ACL for the AgentTicketPhone.

[amdkryn]

WebRequestCustomer is ticket_history_type.id=29
PhoneCallCustomer is ticket_history_type.id=14

to find ticket_history_type ids:
SELECT id,name FROM ticket_history_type

select count(*), history_type_id from ticket_history where history_type_id in (14,29) group by history_type_id

add numbers as needed for additional breakdowns.

Thanks for the help. From the options provided, that already meets my need. Could you help me to add the ticket number on the result so I validate the ticket open for the web?

[crythias]

Code: Select all

SELECT ticket.tn, tht.name FROM `ticket_history` th left join ticket on ticket.id = th.ticket_id left join ticket_history_type tht on tht.id = th.history_type_id WHERE th.history_type_id in (14,29);

[amdkryn]

Code: Select all

SELECT ticket.tn, tht.name FROM `ticket_history` th left join ticket on ticket.id = th.ticket_id left join ticket_history_type tht on tht.id = th.history_type_id WHERE th.history_type_id in (14,29);

I copied and pasted exactly this command but with the error below:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 1, SQL: 'SELECT ticket.tn, tht.name FROM `ticket_history` th left join ticket on ticket.id = th.ticket_id left join ticket_history_type tht on tht.id = th.history_type_id WHERE th.history_type_id in (14,29); LIMIT 10'

Any ideas on what it could be?

[crythias]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 1

in (14,29); LIMIT 10'

Yes, can you see it?
.

.

.

.
.
.
Options are: clear the limit and/or remove the semicolon

[amdkryn]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 1

in (14,29); LIMIT 10'

Yes, can you see it?
.
.
Options are: clear the limit and/or remove the semicolon

I interpreted that this limit would not generate error was my lack of attention, I apologize.

Thanks for the help, will mark the topic as solved.