[360, '6 minutes'], '0.25' => [900, '15 minutes'], '0.5' => [1800, '30 minutes']]; $increment_key = isset($increments[$_GET['billing_increment'] ?? '']) ? $_GET['billing_increment'] : '0.25'; [$increment, $increment_label] = $increments[$increment_key]; $default_rate = floatval($config_default_hourly_rate); $sql = mysqli_query($mysqli, " SELECT ticket_reply_id, ticket_reply, ticket_reply_type, ticket_reply_created_at, TIME_TO_SEC(ticket_reply_time_worked) AS seconds, ticket_reply_by, user_name, user_avatar, ticket_id, ticket_prefix, ticket_number, ticket_subject, ticket_billable, ticket_client_id, ticket_created_at, ticket_resolved_at, ticket_closed_at, ticket_invoice_id, client_name, client_abbreviation FROM ticket_replies JOIN tickets ON ticket_reply_ticket_id = ticket_id JOIN clients ON ticket_client_id = client_id LEFT JOIN users ON ticket_reply_by = user_id WHERE ticket_reply_archived_at IS NULL AND ticket_reply_time_worked IS NOT NULL AND TIME_TO_SEC(ticket_reply_time_worked) > 0 AND " . reportRangeSql('ticket_reply_created_at', $period) . " " . reportClientSql('ticket_client_id', $client_id) . " " . ($agent_id ? "AND ticket_reply_by = $agent_id" : '') . " " . ($billable_only ? "AND ticket_billable = 1" : '') . " ORDER BY client_name ASC, ticket_number ASC, ticket_reply_created_at ASC"); // Client > ticket > replies, with rounded totals at each level $clients = []; $replies = []; while ($row = mysqli_fetch_assoc($sql)) { $seconds = intval($row['seconds']); $billed = intval(ceil($seconds / $increment) * $increment); // The contract in force's rate (after hours for a ticket opened outside business hours), else the default $rate_info = contractTicketRate($row); $rate = $rate_info['rate']; $row['seconds'] = $seconds; $row['billed'] = $billed; $row['rate'] = $rate; // Only time on a billable ticket is worth anything on an invoice $row['to_bill'] = intval($row['ticket_billable']) ? $billed : 0; $row['value'] = $row['to_bill'] / 3600 * $rate; $row['text'] = trim(preg_replace("/\n{3,}/", "\n\n", str_replace(["\r\n", "\r"], "\n", html_entity_decode(strip_tags(str_ireplace(['
', '
', '
', '

', ''], "\n", (string) $row['ticket_reply'])), ENT_QUOTES, 'UTF-8')))); $replies[] = $row; $c = intval($row['ticket_client_id']); $t = intval($row['ticket_id']); $clients[$c] = $clients[$c] ?? ['name' => $row['client_name'], 'abbr' => $row['client_abbreviation'], 'rate' => $rate, 'default_rate' => $rate_info['source'] === 'default', 'rate_note' => $rate_info['contract_name'], 'rates' => [], 'tickets' => [], 'seconds' => 0, 'billed' => 0, 'to_bill' => 0, 'value' => 0.0]; $clients[$c]['tickets'][$t] = $clients[$c]['tickets'][$t] ?? ['row' => $row, 'replies' => [], 'seconds' => 0, 'billed' => 0, 'to_bill' => 0, 'value' => 0.0]; $clients[$c]['tickets'][$t]['replies'][] = $row; // More than one rate - a contract's after-hours rate, or work before and after a contract started $clients[$c]['rates'][sprintf('%.2f', $rate)] = true; foreach ([&$clients[$c], &$clients[$c]['tickets'][$t]] as &$level) { $level['seconds'] += $seconds; $level['billed'] += $billed; $level['to_bill'] += $row['to_bill']; $level['value'] += $row['value']; } unset($level); } $total_seconds = array_sum(array_column($replies, 'seconds')); $total_billed = array_sum(array_column($replies, 'billed')); $total_to_bill = array_sum(array_column($replies, 'to_bill')); $total_value = array_sum(array_column($replies, 'value')); $ticket_count = array_sum(array_map(function ($c) { return count($c['tickets']); }, $clients)); if (reportExporting()) { $columns = [ 'client_name' => ['label' => 'Client'], 'ticket' => ['label' => 'Ticket', 'csv' => function ($row) { return ticketReference($row); }], 'ticket_subject' => ['label' => 'Subject'], 'date' => ['label' => 'Date', 'csv' => function ($row) { return date('Y-m-d H:i', strtotime($row['ticket_reply_created_at'])); }], 'user_name' => ['label' => 'Agent'], 'ticket_reply_type' => ['label' => 'Reply'], 'billable' => ['label' => 'Billable ticket', 'csv' => function ($row) { return intval($row['ticket_billable']) ? 'Yes' : 'No'; }], 'seconds' => ['label' => 'Hours', 'type' => 'hours'], 'billed' => ['label' => "Hours rounded up to {$increment_label}", 'type' => 'hours'], 'to_bill' => ['label' => 'Hours to bill', 'type' => 'hours'], 'rate' => ['label' => 'Rate', 'type' => 'money'], 'value' => ['label' => 'Value', 'type' => 'money'], 'text' => ['label' => 'Work done'], ]; reportCsv('Time Detail ' . $period['start'] . ' to ' . $period['end'], $columns, $replies, ['client_name' => 'Total', 'seconds' => $total_seconds, 'billed' => $total_billed, 'to_bill' => $total_to_bill, 'value' => $total_value]); } require_once "includes/inc_report_layout.php"; $controls = reportSelectControl('billing_increment', $increment_key, ['0.1' => 'Round up to 6 minutes', '0.25' => 'Round up to 15 minutes', '0.5' => 'Round up to 30 minutes'], 'fa-stopwatch', '', 'col-sm-6 col-lg-auto') . reportSwitchControl('billable_only', $billable_only, 'Billable tickets only'); $hours = function ($seconds) { return number_format($seconds / 3600, 2); }; ?>
$period, 'client' => $client_id, 'agent' => $agent_id, 'controls' => $controls, ]); ?> 'Time logged', 'value' => reportHours($total_seconds, 2) . ' h', 'sub' => reportCount(count($replies)) . ' replies on ' . reportCount($ticket_count) . ' tickets'], ['label' => 'To bill', 'value' => reportHours($total_to_bill, 2) . ' h', 'sub' => 'On billable tickets, each reply rounded up to ' . escapeHtml($increment_label) . ''], ['label' => 'Value', 'value' => reportMoney($total_value), 'sub' => 'The hours to bill at each client\'s rate'], ['label' => 'Clients', 'value' => reportCount(count($clients)), 'sub' => 'With time in the period'], ]) ?>
$client) { ?>

ticket · h logged · h to bill 1) { ?>· rates vary · 0) { ?>· /h ·
Ticket and work done Agent Logged Rounded
Internal note
All clients - tickets h logged · h to bill ·
Only replies with time on them, by the date they were written. Each reply is rounded up to on its own, and the rounded figures on billable tickets are what is to bill.