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; }