public function get_timeSheet($start, $end, $users = null, $customers = null, $projects = null, $activities = null, $limit = false, $reverse_order = false, $filterCleared = null, $startRows = 0, $limitRows = 0, $countOnly = false)
{
// -1 for disabled, 0 for only not cleared entries
if (!is_numeric($filterCleared)) {
$filterCleared = -1;
if ($this->kga->getSettings()->isHideClearedEntries()) {
$filterCleared = 0;
}
}
$start = MySQL::SQLValue($start, MySQL::SQLVALUE_NUMBER);
$end = MySQL::SQLValue($end, MySQL::SQLVALUE_NUMBER);
$filterCleared = MySQL::SQLValue($filterCleared, MySQL::SQLVALUE_NUMBER);
$limit = MySQL::SQLValue($limit, MySQL::SQLVALUE_BOOLEAN);
$p = $this->kga['server_prefix'];
$whereClauses = $this->timeSheet_whereClausesFromFilters($users, $customers, $projects, $activities);
if (isset($this->kga['customer'])) {
$whereClauses[] = "project.internal = 0";
}
if ($start) {
$whereClauses[] = "(end > {$start} || end = 0)";
}
if ($end) {
$whereClauses[] = "start < {$end}";
}
if ($filterCleared > -1) {
$whereClauses[] = "cleared = {$filterCleared}";
}
if ($limit) {
if (!empty($limitRows)) {
$startRows = (int) $startRows;
$limit = "LIMIT {$startRows}, {$limitRows}";
} else {
$limit = "LIMIT " . $this->kga->getSettings()->getRowLimit();
}
} else {
$limit = "";
}
$select = "SELECT timeSheet.*, status.status, customer.name AS customerName, customer.customerID as customerID, activity.name AS activityName,\n project.name AS projectName, project.comment AS projectComment, user.name AS userName, user.alias AS userAlias ";
if ($countOnly) {
$select = "SELECT COUNT(*) AS total";
$limit = "";
}
$query = "{$select}\n FROM {$p}timeSheet AS timeSheet\n JOIN {$p}projects AS project USING (projectID)\n JOIN {$p}customers AS customer USING (customerID)\n JOIN {$p}users AS user USING(userID)\n JOIN {$p}statuses AS status USING(statusID)\n JOIN {$p}activities AS activity USING(activityID) " . (count($whereClauses) > 0 ? " WHERE " : " ") . implode(" AND ", $whereClauses) . ' ORDER BY start ' . ($reverse_order ? 'ASC ' : 'DESC ') . $limit . ';';
$result = $this->conn->Query($query);
if ($result === false) {
$this->logLastError('get_timeSheet');
}
if ($countOnly) {
$this->conn->MoveFirst();
$row = $this->conn->Row();
return $row->total;
}
$i = 0;
$arr = array();
$this->conn->MoveFirst();
while (!$this->conn->EndOfSeek()) {
$row = $this->conn->Row();
$arr[$i]['timeEntryID'] = $row->timeEntryID;
// Start time should not be less than the selected start time. This would confuse the user.
if ($start && $row->start <= $start) {
$arr[$i]['start'] = $start;
} else {
$arr[$i]['start'] = $row->start;
}
// End time should not be less than the selected start time. This would confuse the user.
if ($end && $row->end >= $end) {
$arr[$i]['end'] = $end;
} else {
$arr[$i]['end'] = $row->end;
}
if ($row->end != 0) {
// only calculate time after recording is complete
$arr[$i]['duration'] = $arr[$i]['end'] - $arr[$i]['start'];
$arr[$i]['formattedDuration'] = Kimai_Format::formatDuration($arr[$i]['duration']);
$arr[$i]['wage_decimal'] = $arr[$i]['duration'] / 3600 * $row->rate;
$fixedRate = (double) $row->fixedRate;
if ($fixedRate) {
$arr[$i]['wage'] = sprintf("%01.2f", $fixedRate);
} else {
$arr[$i]['wage'] = sprintf("%01.2f", $arr[$i]['wage_decimal']);
}
} else {
$arr[$i]['duration'] = null;
$arr[$i]['formattedDuration'] = null;
$arr[$i]['wage_decimal'] = null;
$arr[$i]['wage'] = null;
}
$arr[$i]['budget'] = $row->budget;
$arr[$i]['approved'] = $row->approved;
$arr[$i]['rate'] = $row->rate;
$arr[$i]['projectID'] = $row->projectID;
$arr[$i]['activityID'] = $row->activityID;
$arr[$i]['userID'] = $row->userID;
$arr[$i]['customerName'] = $row->customerName;
$arr[$i]['customerID'] = $row->customerID;
$arr[$i]['activityName'] = $row->activityName;
$arr[$i]['projectName'] = $row->projectName;
$arr[$i]['projectComment'] = $row->projectComment;
$arr[$i]['location'] = $row->location;
$arr[$i]['trackingNumber'] = $row->trackingNumber;
$arr[$i]['statusID'] = $row->statusID;
$arr[$i]['status'] = $row->status;
$arr[$i]['billable'] = $row->billable;
$arr[$i]['description'] = $row->description;
$arr[$i]['comment'] = $row->comment;
$arr[$i]['cleared'] = $row->cleared;
$arr[$i]['commentType'] = $row->commentType;
$arr[$i]['userAlias'] = $row->userAlias;
$arr[$i]['userName'] = $row->userName;
$i++;
}
return $arr;
}