Free Billing and reporting (SQL queries inside!)

[indesigns]

PieterK,
The extra items in the dropdown are these them? I only copies the first few lines.

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>

I tried this and it did not work for me. I can see the page but it is not displaying everything. I see the Filter table and the first class=border table but there is nothing it it and it is now showing results. Also I thought it might be the start/end calendars but this did not work either.

please help.

[indesigns]

got it to work for 3.30.02. It was the start/end calendars. this is what I changed.

starting with line 32, Filter table - start/end calendar - replaced

<?
require_once ("./includes/functions_date.php");
$dateFormat = getCalendarDateFormats(false); // Don't want hour-granularity
if (empty($_POST["datefrom"]) && empty($_POST["dateto"]))
{
$_gdatefrom = stringFirstOfTheMonth($dateFormat["html"]);
$_gdateto = date($dateFormat["html"], 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', '<?=$dateFormat["cal"]?>');" />&nbsp;<img src="<?=$_SWIFT["themepath"]?>calendar.gif" id="datefromtrigger" style="cursor: pointer;" align="absmiddle" onclick="return showCalendar('datefrom', '<?=$dateFormat["cal"]?>');" /> <?=$_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', '<?=$dateFormat["cal"]?>');" />&nbsp;<img src="<?=$_SWIFT["themepath"]?>calendar.gif" id="datetotrigger" style="cursor: pointer;" align="absmiddle" onclick="return showCalendar('dateto', '<?=$dateFormat["cal"]?>');" />
</td>

starting with line 95 - results table - replaced

$_datefrom = floorDate(strtotime($_gdatefrom));
$_dateto = ceilDate(strtotime($_gdateto));

[hjanjua]

Can somebody please provide all the file and sql queries zipped in one folder? its easy to deploy that way?

[keithsmith]

Originally Posted by magic7s

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.

This is a great help to me. It only seems to pull one staff user group how can I change that?

Thanks
K

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

[tspore]

Originally Posted by magic7s

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.

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

This seems to work very well, but I seem to have done something wrong for the Customfieldvalues - Basically I setup a custom group - "Staff & User Ticket Creation"
And as entry 1 - I put in a field Called - company name - I switched the last code above to look at customfieldid -"1"
But it still shows up in that field as Null.


Can someone point me in the right direction here, for what I did wrong?

[tspore]

Ok I figured it out - Basically it needs to be a custom field in the tracking time field.

But now here is a request if someone knows how, How can I make it so that it only pulls the times from the "Closed" fields and ignores the "Billed" "Open" "on hold" tickets?

[John Haugeland]

(( I'm removing what I said because Jamie pointed out that I was wrong: this doesn't involve Kayako source, so what I said was meaningless. ))