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