Using phpMyAdmin to export tickets with custom fields

Started by tfs, March 01, 2012, 06:37:38 PM

Previous topic - Next topic

tfs

I've been working towards figuring out how to export my data and capture the custom fields, as well as to be able to add up the total hours field for export, which is the field that I use the "Tally" plugin to add up.  Here's the SQL query that I've got so far.

It probably won't work for you initially because it's unlikely you have the same custom fields as I do.  Here's the columns that are returned by the query...

Ticket_ID, Subject, Assigned_To, #Replies, Total_Hours, Owner_Name, Accounting_Status, Billing_Code, Notes, Last_Billing_Date, Last_Update

Here's an itemized list of what each column is about:


Ticket_ID: Returns the ID_TICKET field from the tickets table.

Subject: Returns the ticket subject from the tickets table.

Assigned_To: Looks into the members table and returns the name of the "Assigned To" member.

#Replies: Returns the num_replies field from the tickets table.

Total_Hours: Custom field #9 is added up and returned as "Total_Hours."  In our database I call the field "Item Hours."  It is a Floating (Fractional) number, Visible/editable in replies.  In that field we place the number of hours (in decimal format) for each reply.  This is returned as a total of the custom field, as "Total_Hours."

Owner_Name: Looks into the members table and returns the name of the ticket owner.

Accounting_Status: Custom field #11 is a drop down list that I return as "Accounting_Status."  I use the "CASE" function to convert the "atomic" values to the text values.

Billing_Code: Custom field #7 is a drop down list that I return as "Billing_Code"  It also uses the "CASE" function to convert the "atomic" values to the text values.

Notes: This one uses the IF() function to strip out NULL values from custom field #10 as "Notes."  There's probably room for improvement on this one, but I have yet to discover how to leverage the ISNULL() or IFNULL() functions to properly do this.  Custom field #10 is a Large Textbox type.

Last_Billing_Date: This one also uses the IF() function to strip out NULL values from custom field #13 as "Last_Billing_Date."  Custom field #13 is an Integer type.

Last_Update: Uses the "FROM_UNIXTIME() function to return the date/time value in a string date format.

Finally, the query avoids displaying closed tickets by using "smf_helpdesk_tickets.status <> '3'".

I run this query via phpMyAdmin, which has an export button at the bottom of the query window.  From there I export to CSV format, chosing the Custom option in order to include the columns names in the first row.  I can download it directly to Excel and the rest is history.



SELECT

smf_helpdesk_tickets.id_ticket as Ticket_ID,

smf_helpdesk_tickets.subject as Subject,

(select smf_members.real_name from smf_members where smf_helpdesk_tickets.id_member_assigned = id_member) as Assigned_To,

smf_helpdesk_tickets.num_replies as '#Replies',

if((select round(sum(smf_helpdesk_custom_fields_values.value),2) from smf_helpdesk_custom_fields_values where smf_helpdesk_custom_fields_values.id_field = 9 and smf_helpdesk_custom_fields_values.post_type = 2 and smf_helpdesk_custom_fields_values.id_post in (select id_msg from smf_helpdesk_ticket_replies where smf_helpdesk_ticket_replies.message_status = 0 and smf_helpdesk_ticket_replies.id_ticket = smf_helpdesk_tickets.id_ticket)) is null, 0, (select round(sum(smf_helpdesk_custom_fields_values.value),2) from smf_helpdesk_custom_fields_values where smf_helpdesk_custom_fields_values.id_field = 9 and smf_helpdesk_custom_fields_values.post_type = 2 and smf_helpdesk_custom_fields_values.id_post in (select id_msg from smf_helpdesk_ticket_replies where smf_helpdesk_ticket_replies.message_status = 0 and smf_helpdesk_ticket_replies.id_ticket = smf_helpdesk_tickets.id_ticket))) as Total_Hours,

(select smf_members.real_name from smf_members where smf_helpdesk_tickets.id_member_started = id_member) as Owner_Name,

(select case (select smf_helpdesk_custom_fields_values.value from smf_helpdesk_custom_fields_values where smf_helpdesk_tickets.id_ticket = smf_helpdesk_custom_fields_values.id_post and smf_helpdesk_custom_fields_values.id_field = 11 and smf_helpdesk_custom_fields_values.post_type = 1)
when 1 then 'New'
when 2 then 'Accountant Review'
when 3 then 'Customer Review'
when 4 then 'On Hold'
when 5 then 'Billed'
when 6 then 'Paid'
when 7 then 'Closed NOT Billed'
when 8 then 'Closed'
else 'Undefined' END) as Accounting_Status,

(select case (select smf_helpdesk_custom_fields_values.value from smf_helpdesk_custom_fields_values where smf_helpdesk_tickets.id_ticket = smf_helpdesk_custom_fields_values.id_post and smf_helpdesk_custom_fields_values.id_field = 7 and smf_helpdesk_custom_fields_values.post_type = 1)
when 1 then 'Contract'
when 2 then 'Contract-Limited'
when 3 then 'Donation'
when 4 then 'FrontRange'
when 5 then 'Hourly'
when 6 then 'No Charge'
when 7 then 'Warranty'
else 'Undefined' END) as Billing_Code,

if((select smf_helpdesk_custom_fields_values.value from smf_helpdesk_custom_fields_values where smf_helpdesk_tickets.id_ticket = smf_helpdesk_custom_fields_values.id_post and smf_helpdesk_custom_fields_values.id_field = 10 and smf_helpdesk_custom_fields_values.post_type = 1) is NULL, '', (select smf_helpdesk_custom_fields_values.value from smf_helpdesk_custom_fields_values where smf_helpdesk_tickets.id_ticket = smf_helpdesk_custom_fields_values.id_post and smf_helpdesk_custom_fields_values.id_field = 10 and smf_helpdesk_custom_fields_values.post_type = 1)) as Notes,

if((select smf_helpdesk_custom_fields_values.value from smf_helpdesk_custom_fields_values where smf_helpdesk_tickets.id_ticket = smf_helpdesk_custom_fields_values.id_post and smf_helpdesk_custom_fields_values.id_field = 13 and smf_helpdesk_custom_fields_values.post_type = 1)  is NULL, '', (select smf_helpdesk_custom_fields_values.value from smf_helpdesk_custom_fields_values where smf_helpdesk_tickets.id_ticket = smf_helpdesk_custom_fields_values.id_post and smf_helpdesk_custom_fields_values.id_field = 13 and smf_helpdesk_custom_fields_values.post_type = 1)) as Last_Billing_Date,

from_unixtime(last_updated) as Last_Update

FROM
smf_helpdesk_tickets

WHERE
smf_helpdesk_tickets.status <> '3'

ORDER BY smf_helpdesk_tickets.id_ticket

A good tree cannot bring forth evil fruit, neither can an evil tree bring forth good fruit.