Free Billing and reporting (SQL queries inside!)

[jj1987]

OK I figured out the nav.

In staffmenu.js, your code should look like this

var MENU_T_REPORTS =
[
    {"popup":1, "popupoff":[0,0], "pos":[0,0], "zindex":50, "size":[22,200], "itemoff":[21,0], "leveloff":[10,59], "delay":1000, "style":VSTYLE, "imgsize":[20,17], dynamic: true, https_fix_blank_doc:themepath+'blank.html'},
    {code:"Work Summary", "format":{"itemoff":[0,0], "image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=worksummary"},
//    {code:"Bill Report (Staff)", "format":{"image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=populararticles"},
//    {code:"Bill Report (User)", "format":{"image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=comments"},
    {code:"Department Summary", "format":{"image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=department"},
    {code:"Service Level Report", "format":{"image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=sla"},
//    {code:"Ticket Assignment Report", "format":{"image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=rating"},
//    {code:"Performance Report", "format":{"image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=rating"},
    {code:"Response Time Report", "format":{"image":themepath+"menu_reports.gif", "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=response"},
    // Added for the billable time report
    {code:"Time Spent Report", "format":{"image":themepath+"menu_reports.gif" , "oimage":themepath+"menu_reports.gif", "imgsize":[20,22]}, url:"index.php?_m=tickets&_a=reports&type=timespent"},
];

Next question, is it possible to define the link-
index.php?_m=tickets&_a=reports&type=timespent


I think it's defined with this edit-

This is what we have in /modules/tickets/staff_reports.php BEFORE ?> near the end.

/**
* ###############################################
* TIME SPENT SUMMARY
* ###############################################
*/
} else if ($_REQUEST["type"] == "timespent") {
require_once ("./modules/tickets/report_timespent.php");

}

This file is encoded for me, so I cannot access it.

[Torsten]

Hi,

I've got a small question about SupportSuite mysql database.
Does anyone of You know, how is the 'userid' from 'swusers' table connected with 'typeid' from 'swcustomfieldvalues' table. I need to know it, so I can make report, where there is address (in my case) joined with user name.
Thank You guyz for any help..

[Slowbob]

Does the below work in v3.20.02? I used to have it working, but I have recently reinstalled to V3.20.02 and after re-applying this mod it doesn't appear to work. It brings up the report, but won't bring back any results.

Is it just me?

Originally Posted by indesigns

Our programmer has done some more work to Custom Billing Report. Please look for instructions on a previous posting in this thread.

in staff cp

staff group viewer
==============
- can select start/from dates and gets report of hours worked/billable.

admin group viewer
===============
- can select start/from dates, staff member and/or departments. breaks report into each client user or per staff member.
- play around in this area. it gives you a report to bill clients and hours from your staff.

This is 95% completed and we are completed at our end. Both staff and admin get time spent report. Our programmer has spent many hours on developing this report. Please share and contribute. I know this report is in demand.

Please share and contribute.

Here is updated coding

<?php
//=======================================
//###################################
// Custom Time Spend Report
//
// $RCSfile: report_timespend.php
// original developed by indesigns.ca ($Date: 2007/12/01 09:31:59 $)
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Please share and contribute.  We can all benefit from this.
//###################################
//=======================================
if (!defined("INSWIFT")) {
 trigger_error("Unable to process $PHP_SELF", E_USER_ERROR);
}
 
$isAdmin = $_SWIFT["staff"]["isadmin"];
$_SWIFT['report_timespent']['lastvar']   = '';
$_SWIFT['report_timespent']['spent']     = 0;
$_SWIFT['report_timespent']['billable']  = 0;
 
$template->assign("calendar", true);
$interface->staffHeader($_SWIFT["language"]["tickets"]." &gt; ".$_SWIFT["language"]["reports"], 2);
renderTicketTree();
$interface->staffNavBar('<a href="index.php?_m=tickets&_a=manage" title="'.$_SWIFT["language"]["tickets"].'">'.$_SWIFT["language"]["tickets"].'</a> &raquo; '.'<a href="index.php?_m=tickets&_a=reports&type=worksummary" title="'.$_SWIFT["language"]["reports"].'">'.$_SWIFT["language"]["reports"].'</a> &raquo; '.'<a href="index.php?_m=tickets&_a=reports&type=timespent" title="'.$_SWIFT["language"]["timespent"].'">'.$_SWIFT["language"]["timespent"].' Report</a>', "", 2);
printInfoBox($infomessage);
printErrorBox($errormessage);
?>
<form name="swiftform" action="index.php" method="POST"><fieldset class="swiftfieldset">
<legend><?=$_SWIFT["language"]["filter"]?></legend>
<table width="100%"  border="0" cellspacing="1" cellpadding="3" class="tborder">
<tr>
<?
if (empty($_POST["datefrom"]) && empty($_POST["dateto"]))
{
 $_prevday = DATENOW-86400;
 $_gdatefrom = generateCalendarTimeline($_prevday);
 $_gdateto =generateCalendarTimeline(DATENOW);
} else {
 $_gdatefrom = $_POST["datefrom"];
 $_gdateto = $_POST["dateto"];
}
?></select></td>
<td class="row1"><?=$_SWIFT["language"]["fildate"]?><br>
<input type="text" name="datefrom" id="datefrom" size="12" readonly="1" value="<?=iif(!empty($_gdatefrom), $_gdatefrom)?>" class="swifttext" onclick="return showCalendar('datefrom', '<?=iif($_SWIFT["settings"]["dt_caltype"]=="us", "%m/%d/%Y", "%d/%m/%Y")?>');" />&nbsp;<img src="<?=$_SWIFT["themepath"]?>calendar.gif" id="datefromtrigger" style="cursor: pointer;" align="absmiddle" onclick="return showCalendar('datefrom', '<?=iif($_SWIFT["settings"]["dt_caltype"]=="us", "%m/%d/%Y", "%d/%m/%Y")?>');" /> <?=$_SWIFT["language"]["filto"]?> <input type="text" name="dateto" id="dateto" size="12" readonly="1" value="<?=iif(!empty($_gdateto), $_gdateto)?>" class="swifttext" onclick="return showCalendar('dateto', '<?=iif($_SWIFT["settings"]["dt_caltype"]=="us", "%m/%d/%Y", "%d/%m/%Y")?>');" />&nbsp;<img src="<?=$_SWIFT["themepath"]?>calendar.gif" id="datetotrigger" style="cursor: pointer;" align="absmiddle" onclick="return showCalendar('dateto', '<?=iif($_SWIFT["settings"]["dt_caltype"]=="us", "%m/%d/%Y", "%d/%m/%Y")?>');" /></td>
<?
if ($isAdmin) {
?>
 <td class="row2"><?=$_SWIFT["language"]["filstaff"]?><br>
 <select name="filterstaffid" class="swiftselect">
 <option value="0"<?=iif(empty($_POST["filterstaffid"]), " selected")?>><?=$_SWIFT["language"]["filnotselected"]?></option>
 <?
 foreach ($_SWIFT["staffcache"] as $key=>$val)
 {
  ?><option value="<?=$val["staffid"]?>"<?=iif($_POST["filterstaffid"]==$val["staffid"], " selected")?>><?=htmlspecialchars($val["fullname"])?></option><?
 }
 ?></select>
 </td>
 <td class="row2"><?=$_SWIFT["language"]["f_department"]?><br>
 <select name="filterdepartmentid" class="swiftselect">
 <option value="0"<?=iif(empty($_POST["filterdepartmentid"]), " selected")?>><?=$_SWIFT["language"]["filnotselected"]?></option>
 <?
 foreach ($_SWIFT["departmentcache"] as $key=>$val)
 {
  ?><option value="<?=$val["departmentid"]?>"<?=iif($_POST["filterdepartmentid"]==$val["departmentid"], " selected")?>><?=htmlspecialchars($val["title"])?></option><?
 }
 ?></select>
 </td>
<?
}
?>
<td width="100" class="row1" align="right" valign="bottom" nowrap><input type="submit" class="yellowbutton" name="filter" value="<?=$_SWIFT["language"]["filter"]?>"></td>
</tr>
</table>
</fieldset><BR />
<input type="hidden" name="_m" value="tickets"><input type="hidden" name="_a" value="reports"><input type="hidden" name="type" value="timespent">
</form>
<?
$_datefrom = getCalendarDateline($_gdatefrom);
$_dateto = getCalendarDateline($_gdateto);
$_dateto = mktime(23,59,59,date("m", $_dateto), date("d", $_dateto), date("Y", $_dateto));
$query = " SELECT swusers.fullname, max(swtickettimetrack.timetrackid) as timetrackid, swtickets.ticketid, swtickets.subject, swdepartments.departmentid, swtickets.ticketmaskid, swtickettimetrack.forstaffid, group_concat(swtickettimetrack.timespent)  as timespent, group_concat(swtickettimetrack.timebillable) as timebillable, max(swtickettimetrack.dateline) as dateline, swusers.fullname
                FROM swtickettimetrack,swtickets,swdepartments,swusers WHERE 
                swdepartments.departmentid=swtickets.departmentid AND
                swtickets.ticketid=swtickettimetrack.ticketid  AND 
                swtickets.userid = swusers.userid AND
                swtickettimetrack.dateline > '". intval($_datefrom) ."' AND swtickettimetrack.dateline < '". intval($_dateto) ."' ";
if ($isAdmin && $_POST["filterstaffid"]) {
    $query .= " AND forstaffid = " . $_POST['filterstaffid'];
} else if (!$isAdmin) {
    $query .= " AND forstaffid = " . $_SWIFT['staff']['staffid'];
}
if ($_POST["filterdepartmentid"]) {
    $query .= " AND swdepartments.departmentid = " . $_POST["filterdepartmentid"];
}
$query .= ' GROUP BY swtickets.ticketmaskid, swtickettimetrack.forstaffid ';
if ($isAdmin) {
    if ($_POST["filterstaffid"]) {
        $query .= ' ORDER BY swtickettimetrack.forstaffid,dateline ';
    } else {
        $query .= ' ORDER BY swusers.userid,dateline ';
    }
} else {
    $query .= ' ORDER BY dateline ';
}
$dbCore->query($query);
#echo $query;
echo '<table cellpadding="0" cellspacing="0" border="0" width="100%" class="tborder">
        <thead>
            <tr><td class="tcat" height="21" width="100%" colspan="" align="left" nowrap>&nbsp;'.
            'Time Spent Report' .
            #$_SWIFT["language"]["f_department"] .':  '. $_SWIFT["departmentcache"][$val["departmentid"]]["title"] .
            '</td></tr>
        </thead>
        <tbody><tr><td> 
 
        <table width="100%" border="0" cellpadding="3" cellspacing="1">
            <tr class="tabletitlerow">
                <td align="left" valign="top" width="200">'. $_SWIFT["language"]["f_date"] .'</td>
                <td align="left" valign="top" width="100">'. $_SWIFT["language"]["f_ticketmaskid"] .'</td>
                <td align="left" valign="top"> '. $_SWIFT["language"]["description"] .'</td>
                <td align="left" valign="top" width="200">'. $_SWIFT["language"]["f_staffworked"] .'</td>
                <td align="center" valign="top" width="10" nowrap>'. $_SWIFT["language"]["tworked"] .'</td>
                <td align="center" valign="top" width="50" nowrap>'. $_SWIFT["language"]["tbillable"] .'</td>
            </tr>';
while ($dbCore->nextRecord())
{
        $val = $dbCore->Record;
        if ($_POST["filterstaffid"] || !$isAdmin) {
            if ( $_SWIFT['report_timespent']['lastvar']['forstaffid'] != $val['forstaffid']) {
                if ( !empty( $_SWIFT['report_timespent']['lastvar']) ) {
                    display_total(&$_SWIFT);
                }
                display_header("Report for Staff: " . $_SWIFT["staffcache"][$val["forstaffid"]]["fullname"]);
            }
        } else {
            if ( $_SWIFT['report_timespent']['lastvar']['fullname'] != $val['fullname']) {
                if ( !empty( $_SWIFT['report_timespent']['lastvar']) ) {
                    display_total(&$_SWIFT);
                }
                display_header("Report for Client: " . $val['fullname']);
            }
        }
        display_entry(&$_SWIFT,$val);
        $_SWIFT['report_timespent']['lastvar'] = $val;
}
display_total(&$_SWIFT);
echo '</table></td></tr></tbody></table><BR /><BR />';
 
$template->assign("backurl", "index.php?_m=tickets&_a=manage");
$interface->staffFooter();
function display_header($msg) {
        echo '<tr><td class="tcat" height="21" colspan=6 width="100%" >&nbsp;' . $msg .'</td></tr>';
}
function display_total($_SWIFT) {
        echo '<tr class="tabletitlerow">
                <td colspan="4" align="right" valign="top"> Total time worked and total time billed: </td>
                <td align="center" valign="top" width="10" nowrap>'. strColorDate($_SWIFT['report_timespent']['spent']) . '</td>
                <td align="center" valign="top" width="50" nowrap>'. strColorDate($_SWIFT['report_timespent']['billable']) .'</td>
            </tr> <tr><td>&nbsp;</td></tr>';
        $_SWIFT['report_timespent']['billable'] = 0;
        $_SWIFT['report_timespent']['spent'] = 0;
}
function display_entry($_SWIFT,$val) {
                $_this_spent = array_sum(split(',',$val['timespent']));
                $_this_bill  = array_sum(split(',',$val['timebillable']));
                $_this_spent *= 60;
                $_this_bill *= 60;
                echo '<tr class="'. getRowBG() .'">
                            <td align="left" valign="top">'. edate($_SWIFT["settings"]["dt_datetimeformat"], $val["dateline"]) .'</td>
                            <td align="left" valign="top"><a href="index.php?_m=tickets&_a=viewticket&ticketid='. intval($val["ticketid"]) .'" target="_blank">'. $val["ticketmaskid"] .'</a>
                            <td align="left" valign="top">' . $val['subject'] . '</td>
                            <td align="left" valign="top">'. $_SWIFT["staffcache"][$val["forstaffid"]]["fullname"] .'</td>
                            <td align="center" valign="top">'. strColorDate($_this_spent) .'</td>
                            <td align="center" valign="top">'. strColorDate($_this_bill) .'</td>
                        </tr>';
                $_SWIFT['report_timespent']['billable'] += $_this_bill;
                $_SWIFT['report_timespent']['spent'] += $_this_spent;
}
?>

[markoshust]

if anyone is just looking for basic sql reports by department they can build off of, the following is what i used to retrieve basic info.

--------------------------------------------------
total by department
--------------------------------------------------
SELECT   d.title                                                                                              AS 'Department',
         COUNT(t.ticketid)                                                                                    AS 'Number of Tickets',
         (SELECT COUNT(t.ticketid)
          FROM   swtickets t
          WHERE  FROM_UNIXTIME(t.dateline) BETWEEN '2008-01-01 00:00:00' AND '2008-05-06 23:59:59') AS 'Total Tickets',
         ROUND(COUNT(t.ticketid) / (SELECT COUNT(t.ticketid)
                                    FROM   swtickets t
                                    WHERE  FROM_UNIXTIME(t.dateline) BETWEEN '2008-01-01 00:00:00' AND '2008-05-06 23:59:59') * 100,
               2) AS 'Percentage'
FROM     swtickets t
         LEFT JOIN swdepartments d
           ON t.departmentid = d.departmentid
WHERE    FROM_UNIXTIME(t.dateline) BETWEEN '2008-01-01 00:00:00' AND '2008-05-06 23:59:59'
GROUP BY t.departmentid
ORDER BY COUNT(t.ticketid) DESC;


--------------------------------------------------
time tracked tickets by department
--------------------------------------------------
SELECT   d.title AS 'Department',
         COUNT(t.ticketid) AS 'Number of Tickets',
         (SELECT   COUNT(t.ticketid)
          FROM     swtickettimetrack ttt
                   LEFT JOIN swtickets t ON ttt.ticketid = t.ticketid
          WHERE    FROM_UNIXTIME(t.dateline) BETWEEN '2008-01-01 00:00:00' AND '2008-05-06 23:59:59') AS 'Total Tickets',
         ROUND(COUNT(t.ticketid) /
                  (SELECT   COUNT(t.ticketid)
                   FROM     swtickettimetrack ttt
                            LEFT JOIN swtickets t ON ttt.ticketid = t.ticketid
                   WHERE    FROM_UNIXTIME(t.dateline) BETWEEN '2008-01-01 00:00:00' AND '2008-05-06 23:59:59') * 100, 2) AS 'Percentage',
         SUM(ttt.timespent) 'Time Spent',
         SUM(ttt.timebillable) 'Time Billable',
         ROUND(SUM(ttt.timespent) / COUNT(t.ticketid),2) as 'Average Time Spent Per Ticket'
FROM     swtickettimetrack ttt
         LEFT JOIN swtickets t ON ttt.ticketid = t.ticketid
         LEFT JOIN swdepartments d
           ON t.departmentid = d.departmentid
WHERE    FROM_UNIXTIME(t.dateline) BETWEEN '2008-01-01 00:00:00' AND '2008-05-06 23:59:59'
GROUP BY t.departmentid
ORDER BY COUNT(t.ticketid) DESC;

[D@vidC]

Hi,
I am new with kayako and want to use it in a multilingual environment, which is allready a pretty difficult thing to set up.

Now I would need plain and simple reporting without someone having to give in manually how long he spent working on a ticket. This would be for chat and ticketing.

Is there a way I can do this without having to create a million templates and groups and god knows what ? I just want to know how many chats one of my agents answered in one week/one month for example. The same goes for tickets and how long he spends on them on average.

Or is this something going to be implemented in the next version ?

A simple explanation, call it for dummy`s if you like , would be greatly appreciated.

Thanks

[AndrewP]

Hello Kayako users,

I am in the same predicament as most of you are in as we are looking for a way to report how long it takes our agents to respond, the duration of the ticket and by the type of product they needed support for. If there is not a solution we could easily implement, we are willing to pay a developer to create this tool for us.

If you have a solution or want to develop this with us, send me a PM or reply to the post.

Thanks!

[basform]

Hello,

Is there someone who can setup a final version of everything posted here? With a howto for the newbies?

Hope someone would be helpfull, thanks a lot for your help!

[DN_Matt]

Hey Everyone, I'm trying to grab a custom field that we have linked to the billing view as we do not have a flat bill type.

We have 3 different per hour bill charges
1) 150/hour
2) 200/hour
3) 250/hour

The option is specified when a technician enters his time into the system. Using the sql statements above, we're pulling most of what we need into the ticket, but we still need to display the custom field.

Is there a way I can get some assistance modifying the statements to include our custom field value? Thanks much Matt

[PieterK]

Does anyone know why my swtickettimetrack table would be empty? .. we have been using the system activly for over a month now .. most of the reports on this post is useless without data in this table. Do I need to switch something on?

[supportskins]

Have you entered any billing entries in your helpdesk?

[lacinda]

This mod is no longer working for me in 3.30.00 (after upgrading Kayako and then reapplying the mod). I can bring up the report, but there are no options available to filter billing entries. Any suggestions?

Thanks,
Lacinda

[PieterK]

I have the same problem .. anybody any ideas.. maybe we just missed something ?.. I'm running 3.1.1.61 STABLE

[PieterK]

I think I resolved this .. Lines 77 to 81 in my report_stats.php was comented out .. I removed the comments .. ((((<!---->)))) and now it works :-)

[indesigns]

we are having the same problem with 3.30.02. We were the ones who created the timespent report and everything I upgrade I go through the same procedures to add the new report. In 3.20.02 I had no problems.

PieterK,
where is report_stats.php location? did this solve your problem?

[PieterK]

indesigns .. I just followed the readme file that I downloaded with the v.1.2 .. it said copy to "modules/tickets/ folder of your Kayako install"

The extra items in the dropdown was commented out and I just removed the comments.