public function get_time_users($start, $end, $users = null, $customers = null, $projects = null, $activities = null)
{
$start = MySQL::SQLValue($start, MySQL::SQLVALUE_NUMBER);
$end = MySQL::SQLValue($end, MySQL::SQLVALUE_NUMBER);
$p = $this->kga['server_prefix'];
$whereClauses = $this->timeSheet_whereClausesFromFilters($users, $customers, $projects, $activities);
$whereClauses[] = "{$p}users.trash=0";
if ($start) {
$whereClauses[] = "end > {$start}";
}
if ($end) {
$whereClauses[] = "start < {$end}";
}
$query = "SELECT start, end, userID, (end - start) / 3600 * rate AS costs, fixedRate\n FROM {$p}timeSheet\n JOIN {$p}projects USING(projectID)\n JOIN {$p}customers USING(customerID)\n JOIN {$p}users USING(userID)\n JOIN {$p}activities USING(activityID) " . (count($whereClauses) > 0 ? " WHERE " : " ") . implode(" AND ", $whereClauses) . " ORDER BY start DESC;";
$result = $this->conn->Query($query);
if (!$result) {
$this->logLastError('get_time_users');
return array();
}
$rows = $this->conn->RecordsArray(MYSQLI_ASSOC);
if (!$rows) {
return array();
}
$arr = array();
$consideredStart = 0;
$consideredEnd = 0;
foreach ($rows as $row) {
if ($row['start'] <= $start && $row['end'] < $end) {
$consideredStart = $start;
$consideredEnd = $row['end'];
} elseif ($row['start'] <= $start && $row['end'] >= $end) {
$consideredStart = $start;
$consideredEnd = $end;
} elseif ($row['start'] > $start && $row['end'] < $end) {
$consideredStart = $row['start'];
$consideredEnd = $row['end'];
} elseif ($row['start'] > $start && $row['end'] >= $end) {
$consideredStart = $row['start'];
$consideredEnd = $end;
}
$time = (int) ($consideredEnd - $consideredStart);
$costs = (double) $row['costs'];
$fixedRate = (double) $row['fixedRate'];
if (isset($arr[$row['userID']])) {
$arr[$row['userID']]['time'] += $time;
if ($fixedRate > 0) {
$arr[$row['userID']]['costs'] += $fixedRate;
} else {
$arr[$row['userID']]['costs'] += $costs;
}
} else {
$arr[$row['userID']]['time'] = $time;
if ($fixedRate > 0) {
$arr[$row['userID']]['costs'] = $fixedRate;
} else {
$arr[$row['userID']]['costs'] = $costs;
}
}
}
return $arr;
}