Free Billing time query

[bear]

As previously discussed, I needed a way to grab all billable time from a specific department that I use for clients that pay by the hour, and it seems Kayako devs haven't yet done this. Just run this in phpMyAdmin to sum up all the billable time for one specific department (in this case, ID 4).

Need this by client ID? Change the last part "departmentid=4" to "userid=xxx" where "xxx" is the actual client id.

select sum(swtickettimetrack.timebillable) from swtickettimetrack,swtickets where swtickettimetrack.ticketid=swtickets.ticketid and swtickets.departmentid=4

Should be quite simple to port this to a php page for easy retrieval via browser, but I'd suggest making it a login type deal to access it.
Enjoy.

[bear]

To follow up, here's how to include this on a PHP page:

// tally billable time for dept 4
$result = "select sum(swtickettimetrack.timebillable) from swtickettimetrack,swtickets where swtickettimetrack.ticketid=swtickets.ticketid and swtickets.departmentid=4";
$totaltime = mysql_query($result);
echo "Department 4 time worked: " .(mysql_result($totaltime,0));
// end tally

Connection to the DB is handled via Kayako's call, as this was written to be included in the staff dashboard. I stuck it in the "today" block (see attached)

[craigbrass]

Thanks for sharing Bear! I know from reading other topics that many will find this useful.

[jnet]

thanks but the time calculation has to be solved too
I can put a watch in front of me and when I finish look at my watch
and record the time
there must be a timer there to start and then finish and the time is inserted automatically