Billing and reporting (SQL queries inside!) - Page 2

indesigns · **Custom Billing Report - in development -** 14-11-2007, 10:51 PM

Like so many postings in kayako's forum ... where can I get the billing report? We love time tracking but if you can't get a report it is useless. Also reading through the forum that a report will be coming in V4 which there is no ETA. We went ahead in developing such a report. It is not completed but it something. We can sort by staff and start/from dates.

We believe in sharing and contributing and hoping others will do the same. NEED YOUR HELP.

Staff CP Instructions
==================
1. /modules/tickets/staff_reports.php
- Add Navigation

2. /local/en-us/staffmenu.js
- add following code
{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=timespen t"},

3. create a new php, coding below.
/modules/tickets/report_timespent.php
- new report for staff and admin users.

Still needs development and need your help
================================
1. only the staff and department filter be available for administrator types
2. department filter not working
3. create a new table for each User's Email Address that has time. It seems all other reports created by Kayako works this way.

CODING
======

Code:

<?php //======================================= //################################### // Custom Time Spend Report // // $RCSfile: report_timespend.php //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ // 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"]; $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)); // Get List of Tickets between the given time $_tickeidlist = $_timetrackidlist = array(); $query = "SELECT swtickettimetrack.timetrackid, swtickets.ticketid, swtickets.subject, swdepartments.departmentid, swtickets.ticketmaskid, swtickettimetrack.creatorstaffid, swtickettimetrack.timespent * 60 as timespent, swtickettimetrack.timebillable * 60 as timebillable, swtickettimetrack.forstaffid, swtickets.dateline FROM swtickettimetrack,swtickets,swdepartments WHERE  swdepartments.departmentid=swtickets.departmentid AND swtickets.ticketid=swtickettimetrack.ticketid AND  swtickettimetrack.dateline > '". intval($_datefrom) ."' AND swtickettimetrack.dateline < '". intval($_dateto) ."' "; if ($isAdmin && $_POST["filterstaffid"]) { $query .= " AND forstaffid = " . $_POST['filterstaffid']; } else { $query .= " AND forstaffid = " . $_SWIFT['staff']['staffid']; } $query .= ' ORDER BY swtickettimetrack.creatorstaffid,swtickets.ticketmaskid'; $dbCore->query($query); #echo $query; while ($dbCore->nextRecord()) { // $_timetrackidlist[] = $dbCore->Record["timetrackid"]; $_timetrackidlist[$dbCore->Record["timetrackid"]] = $dbCore->Record; } #$dbCore->query("SELECT * FROM `". TABLE_PREFIX ."ticketposts` WHERE `ticketid` IN (". buildIN($_ticketidlist) .");"); #while ($dbCore->nextRecord()) #{ # $_timetrackidlist[$dbCore->Record["ticketid"]]["posts"][$dbCore->Record["ticketpostid"]] = $dbCore->Record; #} 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;'. User's Email: EMAIL ADDRESS</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">Bill Date</td> <td align="left" valign="top">Ticket Subject</td> <td align="left" valign="top" width="100">'. $_SWIFT["language"]["f_ticketid"] .'</td> <td align="left" valign="top" width="200">'. $_SWIFT["language"]["f_department"] .'</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>'; //echo '<pre>'; print_r($_timetrackidlist); echo '</pre>'; $_timetrackidlist[] = 'last_element'; if (_is_array($_timetrackidlist)) { $_last = ''; $_billable = 0; $_spent = 0; $_total_billable = 0; $_total_spent = 0; foreach ($_timetrackidlist as $key=>$val) { #echo "<h2> $val[timetrackid] </h2>"; if ($_last['creatorstaffid'] != $val['creatorstaffid'] && !empty($_last)) { echo '<tr class="'. getRowBG() .'"> <td align="left" valign="top">'. edate($_SWIFT["settings"]["dt_datetimeformat"], $_last["dateline"]) .'</td> <td align="left" valign="top">' . $_last['subject'] . '</td> <td align="left" valign="top"><a href="index.php?_m=tickets&_a=viewticket&ticketid='. intval($_last["ticketid"]) .'" target="_blank">'. $_last["ticketmaskid"] .'</a>' . '<td align="left" valign="top">'. $_SWIFT["departmentcache"][$_last["departmentid"]]["title"] .'</td> <td align="center" valign="top">'. strColorDate($_spent) .'</td> <td align="center" valign="top">'. strColorDate($_billable) .'</td> </tr>'; echo '<tr class="tabletitlerow"> <td colspan="4" align="right" valign="top">'. $_SWIFT["language"]["ttotalworked"] .' and '. $_SWIFT["language"]["ttotalbilled"] .'</td> <td align="center" valign="top" width="10" nowrap>'. strColorDate($_total_spent) . '</td> <td align="center" valign="top" width="50" nowrap>'. strColorDate($_total_billable) .'</td> </tr>'; $_billable = $val['timebillable']; $_spent = $val['timespent']; $_total_billable = $_billable; $_total_spent = $_spent; #echo '<h4> swtchin user </h4>'; } else if ($_last['ticketid'] != $val['ticketid'] && !empty($_last)) { echo '<tr class="'. getRowBG() .'"> <td align="left" valign="top">'. edate($_SWIFT["settings"]["dt_datetimeformat"], $_last["dateline"]) .'</td> <td align="left" valign="top">' . $_last['subject'] . '</td> <td align="left" valign="top"><a href="index.php?_m=tickets&_a=viewticket&ticketid='. intval($_last["ticketid"]) .'" target="_blank">'. $_last["ticketmaskid"] .'</a>' . # iif($_SWIFT["settings"]["t_eticketid"]=="seq",$_last["ticketid"], $_last["ticketmaskid"]) .'</td> '<td align="left" valign="top">'. $_SWIFT["departmentcache"][$_last["departmentid"]]["title"] .'</td> <td align="center" valign="top">'. strColorDate($_spent) .'</td> <td align="center" valign="top">'. strColorDate($_billable) .'</td> </tr>'; #echo '<h4> switchin ticket </h4>'; $_billable = $val['timebillable']; $_spent = $val['timespent']; $_total_billable += $val['timebillable']; $_total_spent += $val['timespent']; } else { #echo "Before: ( $_last[ticketid], $val[ticketid])<br> [$_billable] [$_spent] [$_total_billable] [$_total_spent] <br>"; $_billable += $val['timebillable']; $_spent += $val['timespent']; $_total_billable += $val['timebillable']; $_total_spent += $val['timespent']; #echo "After: [$_billable] [$_spent] [$_total_billable] [$_total_spent] <br>"; } $_last = $val; } } echo '</table></td></tr></tbody></table><BR /><BR />'; $template->assign("backurl", "index.php?_m=tickets&_a=manage"); $interface->staffFooter(); ?>

Modern Advisors · 02-12-2007, 07:05 PM

Nice script. I went ahead and installed this system for a client of mine however there is an error with the report_timespent.php file.

Error:

" Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ',' or ';' in /home/smartfle/public_html/support/modules/tickets/report_timespent.php on line 110"

Line 110:

HTML Code:

<td align="left" valign="top" width="100">'. $_SWIFT["language"]["f_ticketid"] .'</td>

**bear** · 02-12-2007, 07:55 PM

The echo starts on this line, and ends at the end of that third line. Just below that is "User's", with a single quote in it. Removing that may fix it since I believe it's seeing it as a long string ending at line 110...

Quote:

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> '.
User's Email: EMAIL ADDRESS</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">Bill Date</td>
<td align="left" valign="top">Ticket Subject</td>
<td align="left" valign="top" width="100">'. $_SWIFT["language"]["f_ticketid"] .'</td>
<td align="left" valign="top" width="200">'. $_SWIFT["language"]["f_department"] .'</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>';

indesigns · **Custom Billing Report - UPDATE -** 04-12-2007, 05:59 PM

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

Code:

<?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;
}
?>

Jeremie · **Track Billing by user group -** 19-12-2007, 01:50 PM

Is there a way to to use this report to track time spent by user group?

Some of our clients have many people working for them and we group them into user groups, When we send invoices - we send them a bill based on how much time in total they spend with our helpdesk not on a per user basis.

Any help would be greatly appreciated.

Jeremie

indesigns · 19-12-2007, 07:49 PM

No but that is a good idea. The table results will be by user's email. If you want you can code it so that user groups can be an option.

If you do any coding, please share. We have spent alot of time building the report and believe strongly in sharing and contributing.

ciphersystems · **Running Queries -** 23-12-2007, 03:50 PM

I am new to MySQL, but where would we run all these Queries? We are running Kayako on a Windows 2003 Server with XAMPP and MySQL

craigbrass · 23-12-2007, 08:21 PM

You need to install phpMyAdmin | MySQL Database Administration Tool | www.phpmyadmin.net which you can use to run the SQL queries on the database.

magic7s · **My SQL Query with Custom Billing fields. -** 31-12-2007, 09:17 PM

I added a few lines for my own custom reports. customfieldid = 2 and 3 are the id numbers in MY sql database. Your mileage will very. Just thought I would share.

Code:

SELECT FROM_UNIXTIME(t1.dateline) AS time_spent_entry_ts,
  t1.timespent, 
  t1.timebillable, 
  t5.fullname AS staff_worked,
  t5.mobilenumber AS staff_phone,
  t5.email AS staff_email,
  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,
  t2.ticketid AS ticket_index,
  FROM_UNIXTIME(t2.dateline) AS ticket_posted_ts,
  t2.`subject` AS ticket_subject,
  t6.fieldvalue AS billing_name,
  t7.fieldvalue AS po_number
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)
LEFT JOIN swcustomfieldvalues AS t6 ON (t1.timetrackid = t6.typeid AND t6.customfieldid = '2')
LEFT JOIN swcustomfieldvalues AS t7 ON (t1.timetrackid = t7.typeid AND t7.customfieldid = '3');

**Jamie Edwards** · 01-01-2008, 12:59 PM

Hi Magic,

I just tried that out - works a treat. Thank you for sharing it.

Vincent · 12-02-2008, 08:37 PM

Hi,

I've tried installing the timespent report, it shows up in my "ticket-report" drop down menu, but gives me a blank page...

How can I solve that ?

Thanks,

Vincent

indesigns · 12-02-2008, 10:38 PM

This is all I have in my instructions. Did it a long time ago so I am hoping my instructions are correct.

Staff CP
=======
/modules/tickets/staff_reports.php
- Add Navigation

/local/en-us/staffmenu.js
add following code
{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=timespen t"},

/modules/tickets/report_timespent.php
- new report for staff and admin users.
-------------------------------------------------

What helped me when we were developing this was searching through this forum. I found answers for installing a new report. What we had to do at our end is build the report.

I know it has been successful. Talk to another person in this forum. If instructions are different, please post it here for others to see.

indesigns · 12-02-2008, 10:42 PM

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");

}

Vincent · 12-02-2008, 11:08 PM

Hi,

Sorry, I forgot to do the " /modules/tickets/staff_reports.php
- Add Navigation" part...

But still thanks for the quick reaction...

Vincent

jj1987 · 18-03-2008, 03:06 PM

Sorry to bump an older thread, but how do I add the navigation?

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SupportSuite reporting.	byronsmith	Presales Questions	5	04-08-2007 08:45 AM

Jeremie Offline New Member Posts: 1 Join Date: Jul 2004 Location: NY SupportSuite Owned License	Track Billing by user group - 19-12-2007, 01:50 PM Is there a way to to use this report to track time spent by user group? Some of our clients have many people working for them and we group them into user groups, When we send invoices - we send them a bill based on how much time in total they spend with our helpdesk not on a per user basis. Any help would be greatly appreciated. Jeremie

indesigns Offline New Member Posts: 7 Join Date: Nov 2007 SupportSuite Owned License	19-12-2007, 07:49 PM No but that is a good idea. The table results will be by user's email. If you want you can code it so that user groups can be an option. If you do any coding, please share. We have spent alot of time building the report and believe strongly in sharing and contributing.

ciphersystems Offline New Member Posts: 3 Join Date: Jul 2007 SupportSuite Owned License	Running Queries - 23-12-2007, 03:50 PM I am new to MySQL, but where would we run all these Queries? We are running Kayako on a Windows 2003 Server with XAMPP and MySQL

craigbrass Offline Senior Member Posts: 4,955 Join Date: Jun 2005 Location: Cumbria, UK SupportSuite Owned License	23-12-2007, 08:21 PM You need to install phpMyAdmin \| MySQL Database Administration Tool \| www.phpmyadmin.net which you can use to run the SQL queries on the database. Craig Brass - Kayako Forum Squatter (Note: I am NOT a staff member) Icon Headquarters - Its Elixir - Web2Messenger

Vincent Offline New Member Posts: 8 Join Date: Jan 2008 SupportSuite Owned License	12-02-2008, 08:37 PM Hi, I've tried installing the timespent report, it shows up in my "ticket-report" drop down menu, but gives me a blank page... How can I solve that ? Thanks, Vincent

indesigns Offline New Member Posts: 7 Join Date: Nov 2007 SupportSuite Owned License	12-02-2008, 10:42 PM 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"); }

Vincent Offline New Member Posts: 8 Join Date: Jan 2008 SupportSuite Owned License	12-02-2008, 11:08 PM Hi, Sorry, I forgot to do the " /modules/tickets/staff_reports.php - Add Navigation" part... But still thanks for the quick reaction... Vincent

jj1987 Offline New Member Posts: 17 Join Date: Feb 2008 SupportSuite Owned License	18-03-2008, 03:06 PM Sorry to bump an older thread, but how do I add the navigation?