This method also makes the values SQL-secure.
| public timeSheet_whereClausesFromFilters ( array $users, array $customers, array $projects, array $activities = [] ) : array | ||
| $users | array | list of IDs of users to include |
| $customers | array | list of IDs of customers to include |
| $projects | array | list of IDs of projects to include |
| $activities | array | list of IDs of activities to include |
| return | array | list of where clauses to include in the query |
/**
* returns expenses for specific user as multidimensional array
* @TODO: needs comments
* @param integer $users ID of user in table users
* @param integer $start
* @param integer $end
* @param integer $filterCleared
* @return array
* @author th
* @author Alexander Bauer
*/
public function get_expenses($start, $end, $users = null, $customers = null, $projects = null, $reverse_order = false, $filter_refundable = -1, $filterCleared = null, $startRows = 0, $limitRows = 0, $countOnly = false)
{
$conn = $this->conn;
$kga = $this->kga;
// -1 for disabled, 0 for only not cleared entries
if (!is_numeric($filterCleared)) {
$filterCleared = -1;
if ($kga->getSettings()->isHideClearedEntries()) {
$filterCleared = 0;
}
}
$start = MySQL::SQLValue($start, MySQL::SQLVALUE_NUMBER);
$end = MySQL::SQLValue($end, MySQL::SQLVALUE_NUMBER);
$p = $kga['server_prefix'];
$whereClauses = $this->dbLayer->timeSheet_whereClausesFromFilters($users, $customers, $projects);
if (isset($kga['customer'])) {
$whereClauses[] = "{$p}projects.internal = 0";
}
if (!empty($start)) {
$whereClauses[] = "timestamp >= {$start}";
}
if (!empty($end)) {
$whereClauses[] = "timestamp <= {$end}";
}
if ($filterCleared > -1) {
$whereClauses[] = "cleared = {$filterCleared}";
}
switch ($filter_refundable) {
case 0:
$whereClauses[] = "refundable > 0";
break;
case 1:
$whereClauses[] = "refundable <= 0";
break;
case -1:
default:
// return all expenses - refundable and non refundable
}
if (!empty($limitRows)) {
$startRows = (int) $startRows;
$limit = "LIMIT {$startRows}, {$limitRows}";
} else {
$limit = "";
}
$select = "SELECT expenseID, timestamp, multiplier, value, projectID, designation, userID, projectID,\n \t\t\t\t\tcustomerName, customerID, projectName, comment, refundable,\n \t\t\t\t\tcommentType, userName, cleared";
$where = empty($whereClauses) ? '' : "WHERE " . implode(" AND ", $whereClauses);
$orderDirection = $reverse_order ? 'ASC' : 'DESC';
if ($countOnly) {
$select = "SELECT COUNT(*) AS total";
$limit = "";
}
$query = "{$select}\n \t\t\tFROM {$p}expenses\n\t \t\tJoin {$p}projects USING(projectID)\n\t \t\tJoin {$p}customers USING(customerID)\n\t \t\tJoin {$p}users USING(userID)\n\t \t\t{$where}\n\t \t\tORDER BY timestamp {$orderDirection} {$limit}";
$conn->Query($query);
// return only the number of rows, ignoring LIMIT
if ($countOnly) {
$this->conn->MoveFirst();
$row = $this->conn->Row();
return $row->total;
}
$i = 0;
$arr = array();
$conn->MoveFirst();
// toArray();
while (!$conn->EndOfSeek()) {
$row = $conn->Row();
$arr[$i] = (array) $row;
$i++;
}
return $arr;
}
