Billing and reporting (SQL queries inside!)

[innovate]

One major feature we would need is to be able to generate a billing report. I didn't see this option under "Reports", and the "ticket>advanced search>has billing notes" does not work.

Any ideas?

[innovate]

No comments or help with this? We are looking at this software as our problem management solution, but need to know how to run billing reports.

[innovate]

Has anyone been able to implement some sort of billing report?

[thebeaz]

Did you submit a help ticket with them?

[innovate]

No I did not, I wanted to know other's experiences with the billing features.

[innovate]

Is there any ETA for a "Billing Report"?

[Racked Hosting]

submit a ticket with them as thebeaz asked you to because it doesn't look like many need this feature.

I would like to add that even though I don't use the billing feature, I still feel you are pointing out a useful feature request.

[innovate]

I just don't understand the use of tracking billing on a ticket if you can't query billing information.

[nimitz1061]

Heck, you can't get billing information across multiple tickets from the same user as far as I can see.

Its useless as it is..

[RandyK]

Ticket submitted

[jmorby]

Originally Posted by Racked Hosting

submit a ticket with them as thebeaz asked you to because it doesn't look like many need this feature.

We would definitely use it if that feature existed .. at the moment we seem to be logging time sent (hours worked) on a project, but can't pull that info out

We would also love to see the ability to view web stats on our site based on all accesses, not just hits on a specific campaign.

The latter is something Cerberus / WebGroupMedia did reasonably well (well the LiveHelp client although it was never properly developed)

[efulfilment]

Dear all,

I've read through some of the threads regarding this topic.
Of course I agree that such a report is definetly needed via the front-end.
For the time being I prepared some SQL skripts, which helped me out. If you want to use them, you need to assign the users to a separate user-group per customer (e.g. customer 1, customer 2, aso).

Use the following SQL to extract a detailed report:

SELECT FROM_UNIXTIME(t1.dateline) AS time_spent_entry_ts,
  t1.timespent, t1.timebillable, t5.fullname AS staff_worked,
  t1.notes AS billing_entry_notes,
  t3.fullname AS ticket_requestor_name,
  t4.title AS ticket_requestor_group_name,
  t4.usergroupid AS ticket_requestor_group_id,
  t2.ticketmaskid AS ticket_id,
  FROM_UNIXTIME(t2.dateline) AS ticket_posted_ts,
  t2.`subject` AS ticket_subject
FROM swtickettimetrack AS t1
LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid)
LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid)
LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid)
LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid);

If you need a sum to bill your customer (mandant), you can use the following example extracting the sum for a respective month:

SELECT
  SUM(t1.timespent) AS time_spent,
  SUM(t1.timebillable) AS time_billed,
  MAX(t4.title) AS customer
FROM swtickettimetrack AS t1
LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid)
LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid)
LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid)
LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid)
WHERE t1.dateline BETWEEN UNIX_TIMESTAMP('2007-03-01') AND UNIX_TIMESTAMP('2007-03-31')
GROUP BY t4.usergroupid;

Please note that these queries are quite fast (< 0,1 sec.) on our system but they are not optimized to ensure a minimum run-time.

Hope that helped.

Regards,
Thomas

[supportskins]

Thank You for sharing it

[jerdman]

Based on what SQL Code 'efullfillment' provided I have modified the code to provide some output more helpful to our clients and our Accounting Dept.

We have a few problems when it comes to billing:
1) the swtickettimetrack table does not have a column to track IF time has been billed (a bool value). So we created a new ticket status - Billed. After a ticket has been billed for, our bookkeeper moves the ticket from Closed to Billed.
2) Grouping all the users. Doing this with User Groups is a PAIN. If new users are always added as they create tickets this becomes a maintenance nightmare. Since all the people we support are businesses who use the same mail system, I was able to GROUP everyone by assuming that each business SHARES the same domain name in the email address.

Below are the SQL queries for these reports grouped by Client. A customer is filtered by the unique domain each company would have.

Bi-Weekly Ticket Billing Detailed Report:

SELECT
LEFT( RIGHT( t2.email, LENGTH( t2.email ) - LOCATE( '@', t2.email ) ) , 9 ) AS CLIENT,
t2.ticketmaskid AS TicketID,
FROM_UNIXTIME( t1.dateline, '%Y-%m-%d' ) AS Date,
LEFT( t3.fullname, 18 ) AS Tech,
REPLACE( REPLACE( t1.notes, '\n', ' ' ),
CHAR(9), ' ') AS Notes,
REPLACE( LEFT( t2.SUBJECT, 45 ) , '\n', ' ' ) AS Subject,
LEFT( t2.fullname, 18 ) AS Requestor,
t1.timespent AS Tspent,
t1.timebillable AS Tbilled
FROM swtickettimetrack AS t1
LEFT JOIN swtickets AS t2 ON ( t1.ticketid = t2.ticketid )
LEFT JOIN swstaff AS t3 ON ( t1.forstaffid = t3.staffid )
WHERE t1.ticketID
IN ( SELECT ticketID FROM swtickettimetrack
WHERE dateline BETWEEN UNIX_TIMESTAMP( DATE_SUB( CURDATE( ) , INTERVAL 14 DAY ) ) AND UNIX_TIMESTAMP( CURDATE( ) +2 ) ) AND t2.ticketstatusid =3 AND t1.timebillable >0 ORDER BY Tech, Date, TicketID;


Bi-Weekly Ticket and Time Count:

SELECT
RIGHT(t1.email, LENGTH(t1.email) - LOCATE('@',t1.email)) AS Client,
COUNT(t1.ticketmaskid) as Count,
SUM(t2.timespent) AS Tspent,
SUM(t2.timebillable) AS Tbilled
FROM swtickets AS t1
LEFT JOIN swusers AS t3 ON (t1.userid = t3.userid)
LEFT JOIN swtickettimetrack AS t2 ON (t1.ticketid = t2.ticketid)
WHERE t1.dateline BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 14 day)) AND UNIX_TIMESTAMP(CURDATE())
GROUP BY Client
ORDER BY Count Desc;


Bi-Weekly DF Employee Ticket Activity (Ticket Activity, Time Spent, Time Billed, & Bonus):


SELECT
t1.forstaffid AS ID,
LEFT( t3.fullname, 18 ) AS Tech,
COUNT(t1.forstaffid) as Activity,
SUM(t1.timespent) AS Tspent,
SUM(t1.timebillable) AS Tbilled,
ROUND(SUM(t1.timebillable)/6,2) AS Bonus
FROM swtickettimetrack AS t1
LEFT JOIN swtickets AS t2 ON ( t1.ticketid = t2.ticketid )
LEFT JOIN swstaff AS t3 ON ( t1.forstaffid = t3.staffid )
WHERE t1.ticketID
IN ( SELECT ticketID FROM swtickettimetrack
WHERE dateline BETWEEN UNIX_TIMESTAMP( DATE_SUB( CURDATE( ) , INTERVAL 14 DAY ) ) AND UNIX_TIMESTAMP( CURDATE( ) +2 ) ) AND t2.ticketstatusid > 2 GROUP BY t1.forstaffid ORDER BY t1.forstaffid

Now that we have the correct SQL Queries, we can make CRON jobs that email the output of each report monthly to our billing department.
This is the script I used to email the output:

1st line (Use mysql to generate an HTML report and direct output ot a file:
mysql -H -D esupport3 -u root -pP@$$w0rdHere -e "SQL QUERY GOES HERE ALL IN ONE LINE" >> /var/spool/report/billing.html

2nd line (Use Mutt to email the HTML file as an attachment)
mutt -a /var/spool/report/billing.html -i /var/spool/report/msgbody.txt -s "Monthly Ticket Billing Report" person@customer.com < /dev/null


Save these lines to a file and call them by saving a file in /etc/crond.d:
Contents:
0 6 0 * 1 root /root/reports/billing >> /dev/null

Note: We run these report EVERY week. As the bookkeeper moves the closed tickets to Billed, they will not show up in next week's report.

- Let this also be a PLUG for reports in eSupport! It would be killer to just be able to create a scheduled job that would generate these types of reports that would run monthly and atuo-send! Also Monthly Ticket Report Summaries to the Clients! That would be another scheduled report to add!

Here is a script for that (it groups by domain in the email address)!

SELECT
FROM_UNIXTIME(t1.dateline, '%Y-%m-%d') as Date,
t1.ticketmaskid as TicketID, LEFT(t1.SUBJECT, 40) as Subject,
t1.fullname AS Name
FROM swtickets AS t1
LEFT JOIN swtickettimetrack AS t2 ON (t1.ticketid = t2.ticketid)
LEFT JOIN swusers AS t3 ON (t1.userid = t3.userid)
LEFT JOIN swstaff AS t5 ON (t2.forstaffid = t5.staffid)
WHERE t1.dateline BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 month)) AND UNIX_TIMESTAMP(CURDATE()) AND t1.email LIKE '%cust%'
GROUP BY TicketID
ORDER BY Date;


Final Note: Of course all this stuff was thrown together by me personally and will not be supported by Kayako staff. All code here is not guaranteed and you use at your own risk! I am not an SQL or Linux God so I will probably not be able to answer your questions, but I have put all the tool together for you. My server is currently CentOS 4.3 I did not have to install anything custom to get all this to work. Maybe I was just lucky.

[innovate]

I am configuring BIRT to connect to our hosted database, but can't seem to get the driver configured properly.

What information should I use for:

DB URL: jdbc:mysql://ourname.helpserver.com
User: when entering, i get an error that access is denied for (user)@(my external ip address)
PW
JNDI URL:


Thank you